-- Migration: Advanced Recurring Expenses Architecture
-- Phase 1: Contract, Schedule, Obligation, Posting, Allocation layers

-- ============================================================================
-- 1. RECURRING EXPENSE CONTRACTS
-- ============================================================================
CREATE TABLE IF NOT EXISTS recurring_expense_contracts (
  id TEXT PRIMARY KEY,
  template_id TEXT REFERENCES recurring_expense_templates(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  vendor_id TEXT,
  contract_date DATE NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE,
  renewal_terms TEXT NOT NULL DEFAULT 'manual_renew' CHECK (renewal_terms IN ('auto_renew', 'manual_renew', 'term_end')),
  escalation_policy TEXT NOT NULL DEFAULT 'none' CHECK (escalation_policy IN ('none', 'fixed_percent', 'indexed')),
  escalation_value NUMERIC(8,4),
  allocation_strategy TEXT NOT NULL DEFAULT 'none' CHECK (allocation_strategy IN ('none', 'branch', 'cost_center', 'project', 'department')),
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'expired', 'terminated', 'under_review')),
  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_contracts_template_id ON recurring_expense_contracts(template_id);
CREATE INDEX IF NOT EXISTS idx_recurring_contracts_status ON recurring_expense_contracts(status);
CREATE INDEX IF NOT EXISTS idx_recurring_contracts_dates ON recurring_expense_contracts(start_date, end_date);

-- ============================================================================
-- 2. RECURRING SCHEDULES
-- ============================================================================
CREATE TABLE IF NOT EXISTS recurring_schedules (
  id TEXT PRIMARY KEY,
  template_id TEXT NOT NULL UNIQUE REFERENCES recurring_expense_templates(id) ON DELETE CASCADE,
  contract_id TEXT REFERENCES recurring_expense_contracts(id) ON DELETE SET NULL,
  current_period_start DATE,
  current_period_end DATE,
  next_scheduled_date DATE NOT NULL,
  last_generated_date DATE,
  missed_generations INTEGER NOT NULL DEFAULT 0,
  last_retry_at TIMESTAMPTZ,
  retry_count INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'archived')),
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_recurring_schedules_next_date ON recurring_schedules(next_scheduled_date);
CREATE INDEX IF NOT EXISTS idx_recurring_schedules_status ON recurring_schedules(status);

-- ============================================================================
-- 3. EXPENSE BASIS & OBLIGATIONS
-- ============================================================================
CREATE TABLE IF NOT EXISTS expense_obligations (
  id TEXT PRIMARY KEY,
  document_id TEXT REFERENCES expense_documents(id) ON DELETE CASCADE,
  contract_id TEXT REFERENCES recurring_expense_contracts(id) ON DELETE SET NULL,
  template_id TEXT REFERENCES recurring_expense_templates(id) ON DELETE SET NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  gross_amount NUMERIC(18,4) NOT NULL,
  currency_code TEXT,
  basis TEXT NOT NULL DEFAULT 'cash' CHECK (basis IN ('cash', 'accrual', 'prepaid')),
  recognition_date DATE,
  settlement_date DATE,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'recognized', 'settled', 'partially_settled', 'reversed')),
  reversal_obligation_id TEXT REFERENCES expense_obligations(id) ON DELETE SET NULL,
  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 (document_id, contract_id)
);

CREATE INDEX IF NOT EXISTS idx_expense_obligations_document ON expense_obligations(document_id);
CREATE INDEX IF NOT EXISTS idx_expense_obligations_contract ON expense_obligations(contract_id);
CREATE INDEX IF NOT EXISTS idx_expense_obligations_status ON expense_obligations(status);
CREATE INDEX IF NOT EXISTS idx_expense_obligations_dates ON expense_obligations(period_start, period_end);

-- ============================================================================
-- 4. EXPENSE POSTINGS (Journal Entry Bridge)
-- ============================================================================
CREATE TABLE IF NOT EXISTS expense_postings (
  id TEXT PRIMARY KEY,
  obligation_id TEXT NOT NULL REFERENCES expense_obligations(id) ON DELETE CASCADE,
  document_id TEXT REFERENCES expense_documents(id) ON DELETE CASCADE,
  journal_entry_id TEXT,
  debit_account TEXT NOT NULL,
  credit_account TEXT NOT NULL,
  amount NUMERIC(18,4) NOT NULL,
  posting_date DATE NOT NULL,
  posting_type TEXT NOT NULL DEFAULT 'regular' CHECK (posting_type IN ('regular', 'reversal', 'adjustment')),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'posted', 'reversed', 'cancelled')),
  reversal_posting_id TEXT REFERENCES expense_postings(id) ON DELETE SET NULL,
  reversal_reason 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_expense_postings_obligation ON expense_postings(obligation_id);
CREATE INDEX IF NOT EXISTS idx_expense_postings_document ON expense_postings(document_id);
CREATE INDEX IF NOT EXISTS idx_expense_postings_journal_entry ON expense_postings(journal_entry_id);
CREATE INDEX IF NOT EXISTS idx_expense_postings_status ON expense_postings(status);
CREATE INDEX IF NOT EXISTS idx_expense_postings_posting_date ON expense_postings(posting_date);

-- ============================================================================
-- 5. EXPENSE ALLOCATIONS
-- ============================================================================
CREATE TABLE IF NOT EXISTS expense_allocations (
  id TEXT PRIMARY KEY,
  obligation_id TEXT NOT NULL REFERENCES expense_obligations(id) ON DELETE CASCADE,
  allocation_type TEXT NOT NULL CHECK (allocation_type IN ('branch', 'cost_center', 'project', 'department', 'employee')),
  allocation_target_id TEXT NOT NULL,
  allocation_target_name TEXT,
  percentage NUMERIC(5,2) NOT NULL CHECK (percentage > 0 AND percentage <= 100),
  amount NUMERIC(18,4) NOT NULL,
  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_expense_allocations_obligation ON expense_allocations(obligation_id);
CREATE INDEX IF NOT EXISTS idx_expense_allocations_type_target ON expense_allocations(allocation_type, allocation_target_id);

-- ============================================================================
-- 6. PREPAID EXPENSE AMORTIZATION SCHEDULE
-- ============================================================================
CREATE TABLE IF NOT EXISTS prepaid_amortization_schedules (
  id TEXT PRIMARY KEY,
  obligation_id TEXT NOT NULL REFERENCES expense_obligations(id) ON DELETE CASCADE,
  period_number INTEGER NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  amount NUMERIC(18,4) NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'posted', 'reversed')),
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (obligation_id, period_number)
);

CREATE INDEX IF NOT EXISTS idx_prepaid_schedules_obligation ON prepaid_amortization_schedules(obligation_id);
CREATE INDEX IF NOT EXISTS idx_prepaid_schedules_status ON prepaid_amortization_schedules(status);

-- ============================================================================
-- 7. ESCALATION RULES
-- ============================================================================
CREATE TABLE IF NOT EXISTS expense_escalation_rules (
  id TEXT PRIMARY KEY,
  contract_id TEXT NOT NULL REFERENCES recurring_expense_contracts(id) ON DELETE CASCADE,
  escalation_type TEXT NOT NULL CHECK (escalation_type IN ('fixed_percent', 'indexed', 'tiered')),
  escalation_value NUMERIC(8,4),
  index_source TEXT, -- 'cpi', 'custom', 'manual'
  effective_date DATE NOT NULL,
  end_date DATE,
  status TEXT NOT NULL DEFAULT 'active',
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_escalation_rules_contract ON expense_escalation_rules(contract_id);

-- ============================================================================
-- 8. PAYMENT SCHEDULE (Separate from Obligation)
-- ============================================================================
CREATE TABLE IF NOT EXISTS expense_payment_schedules (
  id TEXT PRIMARY KEY,
  obligation_id TEXT NOT NULL REFERENCES expense_obligations(id) ON DELETE CASCADE,
  due_date DATE NOT NULL,
  amount NUMERIC(18,4) NOT NULL,
  payment_method TEXT DEFAULT 'bank',
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'partially_paid', 'paid', 'overdue', 'cancelled')),
  paid_amount NUMERIC(18,4) NOT NULL DEFAULT 0,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_payment_schedules_obligation ON expense_payment_schedules(obligation_id);
CREATE INDEX IF NOT EXISTS idx_payment_schedules_due_date ON expense_payment_schedules(due_date);
CREATE INDEX IF NOT EXISTS idx_payment_schedules_status ON expense_payment_schedules(status);

-- ============================================================================
-- 9. EXPENSE AUDIT LOG
-- ============================================================================
CREATE TABLE IF NOT EXISTS expense_audit_log (
  id TEXT PRIMARY KEY,
  entity_type TEXT NOT NULL CHECK (entity_type IN ('template', 'contract', 'document', 'obligation', 'posting', 'payment')),
  entity_id TEXT NOT NULL,
  action TEXT NOT NULL,
  old_values JSONB,
  new_values JSONB,
  actor_id TEXT NOT NULL,
  actor_name TEXT,
  reason TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_expense_audit_entity ON expense_audit_log(entity_type, entity_id);
CREATE INDEX IF NOT EXISTS idx_expense_audit_created ON expense_audit_log(created_at DESC);

-- ============================================================================
-- 10. Duplicate Prevention Tracking
-- ============================================================================
CREATE TABLE IF NOT EXISTS recurring_generation_log (
  id TEXT PRIMARY KEY,
  template_id TEXT NOT NULL REFERENCES recurring_expense_templates(id) ON DELETE CASCADE,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  generated_document_ids TEXT[] NOT NULL DEFAULT '{}',
  status TEXT NOT NULL DEFAULT 'success' CHECK (status IN ('success', 'failed', 'partial')),
  error_message TEXT,
  generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (template_id, period_start, period_end)
);

CREATE INDEX IF NOT EXISTS idx_generation_log_template ON recurring_generation_log(template_id);
CREATE INDEX IF NOT EXISTS idx_generation_log_period ON recurring_generation_log(period_start, period_end);