-- scheduled-reports (wave-15)
-- Creates report_schedules table for recurring report delivery configurations.
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS report_schedules (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id       UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  created_by      UUID REFERENCES users(id) ON DELETE SET NULL,
  name            TEXT NOT NULL,
  report_type     TEXT NOT NULL CHECK (report_type IN (
    'revenue','invoices','payments','time','expenses',
    'profitability','revenue_forecast','leads','proposals',
    'ar_aging','bad_debt','audit','api_usage',
    'vat','pnl','cashflow','advance_tax','withholding',
    'bituach_leumi','uniform_format'
  )),
  format          TEXT NOT NULL DEFAULT 'xlsx' CHECK (format IN ('xlsx','pdf')),
  frequency       TEXT NOT NULL CHECK (frequency IN ('daily','weekly','monthly','quarterly')),
  day_of_week     INTEGER CHECK (day_of_week IS NULL OR (day_of_week BETWEEN 0 AND 6)),
  day_of_month    INTEGER CHECK (day_of_month IS NULL OR (day_of_month BETWEEN 1 AND 28)),
  time_of_day     TEXT NOT NULL DEFAULT '08:00',
  period_type     TEXT NOT NULL DEFAULT 'previous' CHECK (period_type IN ('previous','current','ytd')),
  report_params   JSONB NOT NULL DEFAULT '{}',
  recipients      JSONB NOT NULL DEFAULT '[]',
  is_active       BOOLEAN NOT NULL DEFAULT true,
  next_run_at     TIMESTAMPTZ,
  last_run_at     TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS idx_report_schedules_active ON report_schedules (tenant_id, is_active, next_run_at);
