CREATE TABLE IF NOT EXISTS recurring_expense_templates (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  expense_item_id TEXT,
  expense_account_code TEXT NOT NULL,
  payable_account_code TEXT NOT NULL DEFAULT '2130',
  default_amount NUMERIC(18,4) NOT NULL,
  currency_code TEXT,
  frequency TEXT NOT NULL CHECK (frequency IN ('monthly', 'yearly')),
  interval_count INTEGER NOT NULL DEFAULT 1 CHECK (interval_count > 0),
  start_date DATE NOT NULL,
  next_run_date DATE NOT NULL,
  day_of_month INTEGER CHECK (day_of_month BETWEEN 1 AND 31),
  month_of_year INTEGER CHECK (month_of_year BETWEEN 1 AND 12),
  end_date DATE,
  approval_policy TEXT NOT NULL DEFAULT 'single_step' CHECK (approval_policy IN ('none', 'single_step', 'two_step')),
  auto_submit_for_approval BOOLEAN NOT NULL DEFAULT FALSE,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'closed')),
  notes TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_by TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_recurring_expense_templates_next_run ON recurring_expense_templates (next_run_date);
CREATE INDEX IF NOT EXISTS idx_recurring_expense_templates_status ON recurring_expense_templates (status);

CREATE TABLE IF NOT EXISTS expense_documents (
  id TEXT PRIMARY KEY,
  template_id TEXT REFERENCES recurring_expense_templates(id) ON DELETE SET NULL,
  expense_item_id TEXT,
  expense_account_code TEXT NOT NULL,
  payable_account_code TEXT NOT NULL DEFAULT '2130',
  period_year INTEGER NOT NULL,
  period_month INTEGER NOT NULL CHECK (period_month BETWEEN 1 AND 12),
  due_date DATE NOT NULL,
  description TEXT NOT NULL,
  amount NUMERIC(18,4) NOT NULL,
  currency_code TEXT,
  payment_method TEXT NOT NULL DEFAULT 'bank' CHECK (payment_method IN ('cash', 'bank', 'credit', 'mixed')),
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'pending_approval', 'approved', 'rejected', 'posted', 'partially_paid', 'paid', 'cancelled')),
  paid_amount NUMERIC(18,4) NOT NULL DEFAULT 0,
  submitted_by TEXT,
  submitted_at TIMESTAMPTZ,
  approved_by TEXT,
  approved_at TIMESTAMPTZ,
  rejected_by TEXT,
  rejected_at TIMESTAMPTZ,
  rejected_reason TEXT,
  posted_by TEXT,
  posted_at TIMESTAMPTZ,
  journal_entry_id TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_by TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (template_id, period_year, period_month)
);

CREATE INDEX IF NOT EXISTS idx_expense_documents_status ON expense_documents (status);
CREATE INDEX IF NOT EXISTS idx_expense_documents_due_date ON expense_documents (due_date);

CREATE TABLE IF NOT EXISTS expense_payments (
  id TEXT PRIMARY KEY,
  document_id TEXT NOT NULL REFERENCES expense_documents(id) ON DELETE CASCADE,
  payment_date DATE NOT NULL,
  amount NUMERIC(18,4) NOT NULL,
  payment_method TEXT NOT NULL CHECK (payment_method IN ('cash', 'bank', 'credit', 'mixed')),
  account_code TEXT NOT NULL,
  reference_number TEXT,
  notes TEXT,
  created_by TEXT NOT NULL,
  journal_entry_id TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_expense_payments_document ON expense_payments (document_id);