-- mail_summary schema.
-- Seeds a fresh Postgres container on first boot.
-- The backend also runs equivalent idempotent CREATE TABLE IF NOT EXISTS at startup.

CREATE TABLE IF NOT EXISTS analysis_jobs (
    id                   BIGSERIAL PRIMARY KEY,
    status               TEXT NOT NULL DEFAULT 'pending',
    -- pending | extracting | filtering | analyzing | completed | failed
    mailbox_scope        TEXT NOT NULL DEFAULT 'both',
    -- inbox | sent | both
    date_from            DATE NOT NULL,
    date_to              DATE NOT NULL,
    model                TEXT NOT NULL DEFAULT '',
    inbox_zip_name       TEXT,
    sent_zip_name        TEXT,
    inbox_count          INTEGER NOT NULL DEFAULT 0,
    sent_count           INTEGER NOT NULL DEFAULT 0,
    filtered_inbox_count INTEGER NOT NULL DEFAULT 0,
    filtered_sent_count  INTEGER NOT NULL DEFAULT 0,
    progress_pct         INTEGER NOT NULL DEFAULT 0,
    progress_label       TEXT NOT NULL DEFAULT '',
    progress_detail      TEXT NOT NULL DEFAULT '',
    error_message        TEXT,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    started_at           TIMESTAMPTZ,
    completed_at         TIMESTAMPTZ,
    CONSTRAINT analysis_jobs_status_chk CHECK (
        status IN ('pending', 'extracting', 'filtering', 'analyzing', 'completed', 'failed')
    ),
    CONSTRAINT analysis_jobs_scope_chk CHECK (
        mailbox_scope IN ('inbox', 'sent', 'both')
    ),
    CONSTRAINT analysis_jobs_date_chk CHECK (date_from <= date_to)
);

CREATE INDEX IF NOT EXISTS idx_analysis_jobs_created_at
    ON analysis_jobs (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_analysis_jobs_status
    ON analysis_jobs (status);

-- Mail metadata + cleaned body for the selected analysis window.
-- Prefer storing identifiers/summary over full raw .eml content.
CREATE TABLE IF NOT EXISTS mails (
    id                BIGSERIAL PRIMARY KEY,
    job_id            BIGINT NOT NULL REFERENCES analysis_jobs(id) ON DELETE CASCADE,
    mailbox           TEXT NOT NULL,
    -- inbox | sent
    message_id        TEXT,
    thread_key        TEXT,
    subject           TEXT NOT NULL DEFAULT '',
    from_addr         TEXT NOT NULL DEFAULT '',
    to_addrs          TEXT NOT NULL DEFAULT '',
    cc_addrs          TEXT NOT NULL DEFAULT '',
    mailed_at         TIMESTAMPTZ,
    attachment_names  JSONB NOT NULL DEFAULT '[]'::jsonb,
    body_text         TEXT,
    body_purged       BOOLEAN NOT NULL DEFAULT FALSE,
    summary           JSONB,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT mails_mailbox_chk CHECK (mailbox IN ('inbox', 'sent'))
);

CREATE INDEX IF NOT EXISTS idx_mails_job_id ON mails (job_id);
CREATE INDEX IF NOT EXISTS idx_mails_job_mailed_at ON mails (job_id, mailed_at);
CREATE INDEX IF NOT EXISTS idx_mails_thread_key ON mails (job_id, thread_key);
CREATE INDEX IF NOT EXISTS idx_mails_message_id ON mails (job_id, message_id);

-- Final weekly work report for a job.
CREATE TABLE IF NOT EXISTS weekly_reports (
    id              BIGSERIAL PRIMARY KEY,
    job_id          BIGINT NOT NULL UNIQUE REFERENCES analysis_jobs(id) ON DELETE CASCADE,
    model           TEXT NOT NULL DEFAULT '',
    scope_note      TEXT NOT NULL DEFAULT '',
    report          JSONB NOT NULL DEFAULT '{}'::jsonb,
    report_markdown TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_weekly_reports_created_at
    ON weekly_reports (created_at DESC);