-- Phase 2: Recurring Expenses - Background Scheduler Service
-- Tables for managing automated document generation and scheduler status

-- Table to track scheduled jobs for each recurring expense template
CREATE TABLE IF NOT EXISTS recurring_schedule_jobs (
  id TEXT PRIMARY KEY,
  template_id TEXT NOT NULL,
  contract_id TEXT,
  
  -- Next scheduled generation date
  next_run_date TIMESTAMPTZ NOT NULL,
  
  -- Status of the schedule
  status TEXT NOT NULL DEFAULT 'active', -- active, paused, completed, cancelled
  
  -- Retry information
  retry_count INT DEFAULT 0,
  max_retries INT DEFAULT 3,
  last_error TEXT,
  
  -- Execution tracking
  last_run_date TIMESTAMPTZ,
  total_runs INT DEFAULT 0,
  
  -- Holiday and weekend handling
  skip_weekends BOOLEAN DEFAULT false,
  skip_holidays BOOLEAN DEFAULT false,
  holiday_shift_days INT DEFAULT 0, -- shift by N days if falls on holiday
  
  -- Document generation settings
  auto_create_obligation BOOLEAN DEFAULT false,
  obligation_basis TEXT DEFAULT 'accrual', -- cash, accrual, prepaid
  auto_create_posting BOOLEAN DEFAULT false,
  
  -- Audit trail
  created_by TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_by TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  
  -- Raw/extensible field for future features
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  
  -- Foreign keys
  CONSTRAINT fk_template FOREIGN KEY (template_id) REFERENCES recurring_expense_templates(id) ON DELETE CASCADE,
  CONSTRAINT fk_contract FOREIGN KEY (contract_id) REFERENCES recurring_expense_contracts(id) ON DELETE SET NULL
);

CREATE INDEX idx_schedule_jobs_next_run ON recurring_schedule_jobs(next_run_date) WHERE status = 'active';
CREATE INDEX idx_schedule_jobs_template ON recurring_schedule_jobs(template_id);
CREATE INDEX idx_schedule_jobs_contract ON recurring_schedule_jobs(contract_id);
CREATE INDEX idx_schedule_jobs_status ON recurring_schedule_jobs(status);

-- Table to log all background job executions
CREATE TABLE IF NOT EXISTS background_job_logs (
  id TEXT PRIMARY KEY,
  job_id TEXT NOT NULL,
  
  -- What was executed
  job_type TEXT NOT NULL, -- 'generate_document', 'apply_escalation', 'process_prepaid_amortization', etc.
  
  -- Execution details
  started_at TIMESTAMPTZ NOT NULL,
  completed_at TIMESTAMPTZ,
  status TEXT NOT NULL DEFAULT 'pending', -- pending, running, success, failed, retry
  
  -- Result tracking
  generated_documents INT DEFAULT 0,
  failed_documents INT DEFAULT 0,
  
  -- Error handling
  error_message TEXT,
  error_stack TEXT,
  
  -- Metadata
  created_by TEXT, -- System or user ID if manual trigger
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  
  -- Foreign key
  CONSTRAINT fk_job FOREIGN KEY (job_id) REFERENCES recurring_schedule_jobs(id) ON DELETE CASCADE
);

CREATE INDEX idx_job_logs_job_id ON background_job_logs(job_id);
CREATE INDEX idx_job_logs_status ON background_job_logs(status);
CREATE INDEX idx_job_logs_started_at ON background_job_logs(started_at DESC);

-- Table to prevent duplicate document generation
CREATE TABLE IF NOT EXISTS recurring_generation_duplicates (
  id TEXT PRIMARY KEY,
  job_id TEXT NOT NULL,
  template_id TEXT NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  
  -- Generation attempt info
  generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  attempts INT DEFAULT 1,
  
  -- To prevent race conditions
  generated_document_id TEXT,
  
  CONSTRAINT fk_job_dup FOREIGN KEY (job_id) REFERENCES recurring_schedule_jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_template_dup FOREIGN KEY (template_id) REFERENCES recurring_expense_templates(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX idx_unique_generation ON recurring_generation_duplicates(job_id, template_id, period_start, period_end);

-- Table for escalation rule execution tracking
CREATE TABLE IF NOT EXISTS escalation_executions (
  id TEXT PRIMARY KEY,
  template_id TEXT NOT NULL,
  contract_id TEXT,
  
  -- What was escalated
  escalation_type TEXT NOT NULL, -- 'fixed_percent', 'indexed', 'tiered'
  escalation_rule_id TEXT,
  
  -- When it happened
  execution_date DATE NOT NULL,
  new_amount DECIMAL(15,2) NOT NULL,
  previous_amount DECIMAL(15,2),
  increase_percent DECIMAL(5,2),
  
  -- Result
  status TEXT NOT NULL DEFAULT 'completed', -- completed, pending, failed
  error_message TEXT,
  
  -- Audit
  created_by TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  
  CONSTRAINT fk_esc_template FOREIGN KEY (template_id) REFERENCES recurring_expense_templates(id) ON DELETE CASCADE,
  CONSTRAINT fk_esc_contract FOREIGN KEY (contract_id) REFERENCES recurring_expense_contracts(id) ON DELETE SET NULL
);

CREATE INDEX idx_escalation_date ON escalation_executions(execution_date);
CREATE INDEX idx_escalation_template ON escalation_executions(template_id);
CREATE INDEX idx_escalation_status ON escalation_executions(status);

-- Scheduler status table (single row)
CREATE TABLE IF NOT EXISTS scheduler_status (
  id TEXT PRIMARY KEY DEFAULT 'scheduler-main',
  
  -- Overall scheduler state
  is_running BOOLEAN DEFAULT false,
  is_healthy BOOLEAN DEFAULT true,
  
  -- Last execution info
  last_run_at TIMESTAMPTZ,
  last_check_at TIMESTAMPTZ,
  next_check_at TIMESTAMPTZ,
  
  -- Statistics
  total_jobs INT DEFAULT 0,
  active_jobs INT DEFAULT 0,
  completed_jobs INT DEFAULT 0,
  failed_jobs INT DEFAULT 0,
  
  -- Health metrics
  average_job_duration_ms INT,
  last_error_message TEXT,
  error_count INT DEFAULT 0,
  
  -- Configuration
  check_interval_seconds INT DEFAULT 300, -- Run every 5 minutes
  enable_auto_generation BOOLEAN DEFAULT true,
  enable_escalation BOOLEAN DEFAULT true,
  enable_amortization BOOLEAN DEFAULT true,
  
  -- Audit
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  raw JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- Insert initial scheduler status
INSERT INTO scheduler_status (id, is_running, is_healthy)
VALUES ('scheduler-main', false, true)
ON CONFLICT (id) DO NOTHING;

-- Indexes for performance optimization
CREATE INDEX idx_recurring_jobs_active ON recurring_schedule_jobs(status, next_run_date) WHERE status = 'active';

-- Grant permissions (if needed for specific roles)
-- GRANT SELECT, INSERT, UPDATE ON recurring_schedule_jobs TO app_user;
-- GRANT SELECT, INSERT ON background_job_logs TO app_user;
-- GRANT SELECT ON scheduler_status TO app_user;