-- financing_plans: tracks a deferred/no-interest financing arrangement
CREATE TABLE IF NOT EXISTS financing_plans (
  id                SERIAL PRIMARY KEY,
  name              TEXT NOT NULL,
  total_amount      NUMERIC(12, 2) NOT NULL,
  due_date          DATE NOT NULL,          -- must be paid off by this date
  assigned_paycheck INTEGER,               -- 1, 2, or NULL (split across both)
  active            BOOLEAN NOT NULL DEFAULT TRUE,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- financing_payments: one row per paycheck period for each active plan
CREATE TABLE IF NOT EXISTS financing_payments (
  id          SERIAL PRIMARY KEY,
  plan_id     INTEGER NOT NULL REFERENCES financing_plans(id) ON DELETE CASCADE,
  paycheck_id INTEGER NOT NULL REFERENCES paychecks(id) ON DELETE CASCADE,
  amount      NUMERIC(12, 2) NOT NULL,     -- calculated at generation time
  paid        BOOLEAN NOT NULL DEFAULT FALSE,
  paid_at     TIMESTAMPTZ,
  UNIQUE (plan_id, paycheck_id)
);