-- City Briefing Agent — initial schema (Phase 12)
-- Two tables only: city_briefs (one row per show) and city_briefs_runs
-- (per-run stage status + observability). This service never writes to any
-- other DB. Email-related columns are intentionally absent (Phase 13+).

CREATE OR REPLACE FUNCTION city_briefs_uuid()
RETURNS UUID AS $$
BEGIN
    RETURN md5(random()::text || clock_timestamp()::text)::uuid;
END;
$$ LANGUAGE plpgsql VOLATILE;

-- ── city_briefs: one record per show, lifecycle enforced by status CHECK ─
CREATE TABLE IF NOT EXISTS city_briefs (
    brief_id              UUID PRIMARY KEY DEFAULT city_briefs_uuid(),

    show_id               TEXT NOT NULL,
    show_date             DATE NOT NULL,
    city                  TEXT NOT NULL,
    country               TEXT NOT NULL,
    venue                 TEXT,
    capacity              INTEGER,

    comedian_id           TEXT NOT NULL DEFAULT 'max-amini',

    generated_at          TIMESTAMPTZ,
    published_at          TIMESTAMPTZ,

    previous_show_date    DATE,
    previous_show_venue   TEXT,
    previous_tickets_sold INTEGER,

    city_intelligence     JSONB,
    recent_news           JSONB,
    verified_facts        JSONB,
    removed_facts         JSONB,

    generated_topics      JSONB,    -- 20 GPT-4o ideas
    ranked_topics         JSONB,    -- top-10 ranked by Claude
    crowd_work            JSONB,    -- final set the HTML consumes
    recent_transcripts    JSONB,    -- Max Amini's recent YouTube uploads + transcripts

    competitor_warnings   JSONB,

    public_url            TEXT,

    status                TEXT NOT NULL DEFAULT 'pending'
                          CHECK (status IN
                            ('pending','generating','generated','deploying','published','failed')),

    generation_duration   INTEGER,  -- ms
    error                 TEXT,

    created_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at             TIMESTAMPTZ NOT NULL DEFAULT now(),

    -- Hard duplicate-proof: one brief per show ever (idempotency re-run).
    UNIQUE (show_id)
);

CREATE INDEX IF NOT EXISTS city_briefs_status_idx     ON city_briefs (status);
CREATE INDEX IF NOT EXISTS city_briefs_show_date_idx  ON city_briefs (show_date);

-- ── city_briefs_runs: observability per orchestration run ────────────────
-- One row per run-state per show. Stage columns mirror the pipeline flowchart;
-- each stage is one of 'pending','running','ok','failed','skipped'.
CREATE TABLE IF NOT EXISTS city_briefs_runs (
    brief_run_id          UUID PRIMARY KEY DEFAULT city_briefs_uuid(),

    show_id               TEXT,
    show_date             DATE,

    hub_lookup              TEXT NOT NULL DEFAULT 'pending',
    previous_show_lookup    TEXT NOT NULL DEFAULT 'pending',
    historical_sales_lookup TEXT NOT NULL DEFAULT 'pending',
    voice_profile_load      TEXT NOT NULL DEFAULT 'pending',
    youtube_transcripts     TEXT NOT NULL DEFAULT 'pending',
    perplexity_city         TEXT NOT NULL DEFAULT 'pending',
    perplexity_news         TEXT NOT NULL DEFAULT 'pending',
    openai_fact_check       TEXT NOT NULL DEFAULT 'pending',
    openai_generation       TEXT NOT NULL DEFAULT 'pending',
    claude_ranking          TEXT NOT NULL DEFAULT 'pending',
    html_generation         TEXT NOT NULL DEFAULT 'pending',
    vps_deployment          TEXT NOT NULL DEFAULT 'pending',
    url_verification        TEXT NOT NULL DEFAULT 'pending',
    og_verification         TEXT NOT NULL DEFAULT 'pending',

    hub_lookup_error              TEXT,
    previous_show_lookup_error    TEXT,
    historical_sales_lookup_error TEXT,
    voice_profile_load_error      TEXT,
    youtube_transcripts_error     TEXT,
    perplexity_city_error         TEXT,
    perplexity_news_error         TEXT,
    openai_fact_check_error       TEXT,
    openai_generation_error       TEXT,
    claude_ranking_error          TEXT,
    html_generation_error         TEXT,
    vps_deployment_error          TEXT,
    url_verification_error        TEXT,
    og_verification_error         TEXT,

    started_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at          TIMESTAMPTZ,
    status                TEXT NOT NULL DEFAULT 'running'
                            CHECK (status IN ('running','ok','failed','partial')),
    duration              INTEGER,    -- ms
    error                 TEXT,
    retry_count           INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS city_briefs_runs_show_id_idx ON city_briefs_runs (show_id);
CREATE INDEX IF NOT EXISTS city_briefs_runs_status_idx  ON city_briefs_runs (status);

-- ── updated_at trigger for city_briefs ──────────────────────────────────
CREATE OR REPLACE FUNCTION city_briefs_touch_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_city_briefs_touch ON city_briefs;
CREATE TRIGGER trg_city_briefs_touch
    BEFORE UPDATE ON city_briefs
    FOR EACH ROW
    EXECUTE FUNCTION city_briefs_touch_updated_at();