-- Schema migrations for money agents

CREATE TABLE IF NOT EXISTS logs (
  id SERIAL PRIMARY KEY,
  worker_id INT,
  job_id INT,
  message TEXT,
  level TEXT,
  ts TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS jobs (
  id SERIAL PRIMARY KEY,
  status TEXT DEFAULT 'pending',
  payload TEXT,
  amount NUMERIC(12,2) DEFAULT 0.0,
  started_at TIMESTAMP,
  finished_at TIMESTAMP,
  worker_id INT
);

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='jobs' AND column_name='status') THEN
    EXECUTE 'ALTER TABLE jobs ADD COLUMN status TEXT DEFAULT ''pending''';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='jobs' AND column_name='payload') THEN
    EXECUTE 'ALTER TABLE jobs ADD COLUMN payload TEXT';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='jobs' AND column_name='amount') THEN
    EXECUTE 'ALTER TABLE jobs ADD COLUMN amount NUMERIC(12,2) DEFAULT 0.0';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='jobs' AND column_name='started_at') THEN
    EXECUTE 'ALTER TABLE jobs ADD COLUMN started_at TIMESTAMP';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='jobs' AND column_name='finished_at') THEN
    EXECUTE 'ALTER TABLE jobs ADD COLUMN finished_at TIMESTAMP';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='jobs' AND column_name='worker_id') THEN
    EXECUTE 'ALTER TABLE jobs ADD COLUMN worker_id INT';
  END IF;
END$$;

CREATE TABLE IF NOT EXISTS transactions (
  id SERIAL PRIMARY KEY,
  job_id INT,
  amount NUMERIC(12,2) NOT NULL DEFAULT 0.0,
  service TEXT,
  status TEXT,
  ts TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE VIEW revenue_v AS
SELECT COALESCE(SUM(amount), 0)::NUMERIC(20,2) AS revenue
FROM transactions
WHERE status = 'success';

-- Seed starter jobs
INSERT INTO jobs (status, payload, amount)
VALUES
('pending', 'curl https://api.stripe.com/v1/charges -X GET', 0.00),
('pending', 'curl https://api.paypal.com/v1/payments/payment -X GET', 0.00),
('pending', 'curl https://cash.app/ -I', 0.00),
('pending', 'curl https://api.stripe.com/v1/customers -X GET', 0.00),
('pending', 'curl https://api.paypal.com/v1/invoicing/invoices -X GET', 0.00)
ON CONFLICT DO NOTHING;