CREATE TABLE IF NOT EXISTS calendar_catalog_versions (
    id bigserial PRIMARY KEY,
    version text NOT NULL UNIQUE,
    status text NOT NULL CHECK (status IN ('DRAFT','APPROVED','ACTIVE','RETIRED')),
    coverage_start_jalali_year integer NOT NULL,
    coverage_end_jalali_year integer NOT NULL,
    source_uri text NOT NULL,
    source_version text NOT NULL,
    source_checksum text NOT NULL,
    imported_by text NOT NULL,
    reviewed_by text,
    imported_at timestamptz NOT NULL DEFAULT now(),
    reviewed_at timestamptz,
    activated_at timestamptz,
    CHECK (coverage_end_jalali_year >= coverage_start_jalali_year)
);
-- statement-break
CREATE UNIQUE INDEX IF NOT EXISTS calendar_catalog_one_active_idx
    ON calendar_catalog_versions ((status)) WHERE status='ACTIVE';
-- statement-break
CREATE TABLE IF NOT EXISTS calendar_events (
    id bigserial PRIMARY KEY,
    catalog_version_id bigint NOT NULL REFERENCES calendar_catalog_versions(id) ON DELETE CASCADE,
    event_key text NOT NULL,
    title_fa text NOT NULL,
    title_en text,
    start_date date NOT NULL,
    end_date date NOT NULL,
    recurrence_rule text,
    event_type text NOT NULL CHECK (event_type IN ('PUBLIC_HOLIDAY','RELIGIOUS','NATIONAL','CULTURAL','COMMERCIAL','SEASONAL','EDUCATION','ECONOMIC','EMERGENCY_CLOSURE')),
    tone text NOT NULL CHECK (tone IN ('CELEBRATORY','JOYFUL','NEUTRAL','SOLEMN','MOURNING','MEMORIAL')),
    commercial_posture text NOT NULL CHECK (commercial_posture IN ('PROMOTE','CAUTION','NO_PROMOTION')),
    official_holiday boolean NOT NULL DEFAULT false,
    source_uri text NOT NULL,
    source_version text NOT NULL,
    source_checksum text,
    inclusion_rationale text NOT NULL,
    geographic_scope text NOT NULL DEFAULT 'IR',
    merchant_categories jsonb NOT NULL DEFAULT '[]'::jsonb,
    behavioral_channels jsonb NOT NULL DEFAULT '[]'::jsonb,
    lead_days integer NOT NULL DEFAULT 0 CHECK (lead_days BETWEEN 0 AND 90),
    lag_days integer NOT NULL DEFAULT 0 CHECK (lag_days BETWEEN 0 AND 90),
    relevance_bps integer NOT NULL CHECK (relevance_bps BETWEEN 0 AND 10000),
    confidence_bps integer NOT NULL CHECK (confidence_bps BETWEEN 0 AND 10000),
    created_at timestamptz NOT NULL DEFAULT now(),
    CHECK (end_date >= start_date),
    CHECK (tone NOT IN ('MOURNING','MEMORIAL') OR commercial_posture <> 'PROMOTE'),
    UNIQUE (catalog_version_id,event_key)
);
-- statement-break
CREATE INDEX IF NOT EXISTS calendar_events_active_range_idx
    ON calendar_events (start_date,end_date,catalog_version_id);
-- statement-break
CREATE TABLE IF NOT EXISTS calendar_catalog_audit (
    id bigserial PRIMARY KEY,
    catalog_version_id bigint NOT NULL REFERENCES calendar_catalog_versions(id) ON DELETE CASCADE,
    action text NOT NULL CHECK (action IN ('IMPORTED','APPROVED','ACTIVATED','RETIRED')),
    actor text NOT NULL,
    occurred_at timestamptz NOT NULL DEFAULT now()
);
-- statement-break
CREATE TABLE IF NOT EXISTS ai_insight_snapshots (
    id text PRIMARY KEY,
    merchant_id bigint NOT NULL REFERENCES merchants(id),
    terminal_id bigint REFERENCES terminals(id),
    terminal_source_key text,
    module text NOT NULL CHECK (module IN ('overview','payments','fees','peers','transactions','growth')),
    revision integer NOT NULL CHECK (revision > 0),
    fingerprint text NOT NULL,
    status text NOT NULL CHECK (status IN ('QUEUED','GENERATING','READY','PARTIAL','FAILED')),
    as_of_date date NOT NULL,
    data_through_date date NOT NULL,
    freshness text NOT NULL CHECK (freshness IN ('FRESH','LAGGING','STALE')),
    data_lag_days integer NOT NULL CHECK (data_lag_days >= 0),
    confidence text NOT NULL CHECK (confidence IN ('HIGH','MEDIUM','LOW','INSUFFICIENT_DATA')),
    catalog_version text NOT NULL,
    analytics_version text NOT NULL,
    prompt_version text NOT NULL,
    schema_version text NOT NULL,
    model text NOT NULL,
    deterministic_evidence jsonb NOT NULL,
    ai_response jsonb,
    failure_code text,
    input_tokens bigint NOT NULL DEFAULT 0 CHECK (input_tokens >= 0),
    output_tokens bigint NOT NULL DEFAULT 0 CHECK (output_tokens >= 0),
    provider_requests bigint NOT NULL DEFAULT 0 CHECK (provider_requests >= 0),
    latency_ms bigint NOT NULL DEFAULT 0 CHECK (latency_ms >= 0),
    estimated_cost text,
    manual_refresh boolean NOT NULL DEFAULT false,
    tehran_refresh_day date,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz
);
-- statement-break
CREATE UNIQUE INDEX IF NOT EXISTS ai_insight_current_fingerprint_idx
    ON ai_insight_snapshots (merchant_id,coalesce(terminal_source_key,''),module,fingerprint)
    WHERE manual_refresh=false AND status IN ('QUEUED','GENERATING','READY','PARTIAL');
-- statement-break
CREATE UNIQUE INDEX IF NOT EXISTS ai_insight_manual_refresh_idx
    ON ai_insight_snapshots (merchant_id,coalesce(terminal_source_key,''),module,tehran_refresh_day)
    WHERE manual_refresh=true;
-- statement-break
CREATE INDEX IF NOT EXISTS ai_insight_latest_scope_idx
    ON ai_insight_snapshots (merchant_id,terminal_source_key,module,revision DESC,created_at DESC);
-- statement-break
CREATE TABLE IF NOT EXISTS ai_insight_jobs (
    id text PRIMARY KEY,
    snapshot_id text NOT NULL REFERENCES ai_insight_snapshots(id) ON DELETE CASCADE,
    merchant_id bigint NOT NULL REFERENCES merchants(id),
    module text NOT NULL,
    trigger_reason text NOT NULL CHECK (trigger_reason IN ('BACKFILL','NIGHTLY','IMPORT','CALENDAR','ANOMALY','MANUAL')),
    status text NOT NULL CHECK (status IN ('QUEUED','RUNNING','COMPLETED','FAILED')),
    idempotency_key text,
    request_hash text,
    attempts integer NOT NULL DEFAULT 0 CHECK (attempts >= 0),
    scheduled_at timestamptz NOT NULL DEFAULT now(),
    lease_token text,
    lease_expires_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    completed_at timestamptz,
    UNIQUE (merchant_id,idempotency_key)
);
-- statement-break
CREATE INDEX IF NOT EXISTS ai_insight_jobs_pending_idx
    ON ai_insight_jobs (scheduled_at,created_at) WHERE status IN ('QUEUED','RUNNING');
-- statement-break
CREATE TABLE IF NOT EXISTS ai_insight_events (
    id bigserial PRIMARY KEY,
    job_id text NOT NULL REFERENCES ai_insight_jobs(id) ON DELETE CASCADE,
    merchant_id bigint NOT NULL REFERENCES merchants(id),
    event_type text NOT NULL,
    payload jsonb NOT NULL,
    occurred_at timestamptz NOT NULL DEFAULT now()
);
-- statement-break
CREATE INDEX IF NOT EXISTS ai_insight_events_replay_idx
    ON ai_insight_events (job_id,id) INCLUDE (occurred_at);
-- statement-break
INSERT INTO schema_migrations(version) VALUES (4) ON CONFLICT DO NOTHING;