CREATE TABLE recordings (
    meeting_id           UUID        PRIMARY KEY REFERENCES meetings (id) ON DELETE CASCADE,
                                     -- PK + FK → meetings(id). Enforces 1:1 at the DB level.

    -- TEXT + CHECK rather than a PG enum — volatile 8-state machine;
    -- enums can never drop values once added
    status               TEXT        NOT NULL DEFAULT 'active'
                                     CHECK (status IN (
                                         'active',               -- Recording in progress
                                         'stopping',             -- User/timeout triggered stop
                                         'draining_ml',          -- ML session drain initiated
                                         'awaiting_gap_upload',  -- Gap plan computed, waiting for client chunks
                                         'composing_audio',      -- GCS object composition
                                         'post_processing',      -- Summary, topics, tasks generation
                                         'completed',            -- Terminal success
                                         'failed'                -- Terminal failure
                                     )),
    ml_session_id        TEXT,       -- AssemblyAI session ref for pre-warm and drain

    -- Audio config captured at session start
    sample_rate          INTEGER     NOT NULL,  -- Carried through to chunk storage and composition
    channel_count        SMALLINT    NOT NULL,
    mime_type            TEXT        NOT NULL,  -- e.g. "audio/webm;codecs=opus"

    -- Sequence tracking — powers gap detection + AudioStoredProgressEvent
    -- (emitted every 10s so the client can trim its OPFS buffer)
    last_audio_sequence  BIGINT      NOT NULL DEFAULT 0,  -- Highest sequence received from browser
    last_stored_sequence BIGINT      NOT NULL DEFAULT 0,  -- Highest sequence written to GCS
                                                           -- Gap between these two drives recovery
    gap_plan             JSONB,      -- [{start_sequence, end_sequence}]
                                     -- Write-once-read-few → JSONB over a normalized table
                                     -- Serves GET /meetings/{id}/recording/missing-chunks

    started_at           TIMESTAMPTZ NOT NULL,
    stopped_at           TIMESTAMPTZ,           -- NULL while active
    duration_ms          BIGINT,                -- Computed at stop from the delta
    created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Partial — concurrent session guard on every StartRecordingCommand;
-- index-only scan over a tiny active subset
CREATE INDEX idx_recordings_active ON recordings (status) WHERE status = 'active';

CREATE TABLE recording_status_history (
    id             UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    recording_id   UUID        NOT NULL REFERENCES recordings (meeting_id) ON DELETE CASCADE,
    status         TEXT        NOT NULL,
    status_message TEXT,       -- Optional detail (e.g. error reason on failure)
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Chronological ordering for debug and audit queries
CREATE INDEX idx_recording_status_history_recording_id
    ON recording_status_history (recording_id, created_at);

CREATE TABLE audio_objects (
    meeting_id      UUID        PRIMARY KEY REFERENCES meetings (id) ON DELETE CASCADE,
    storage_path    TEXT        NOT NULL,
    duration_ms     BIGINT,      -- Total audio duration for playback UI
    size_bytes      BIGINT,      -- GCS composed file size — used for HTTP Range calculations
    mime_type       TEXT,        -- Audio format for playback response headers
    audio_version   INTEGER     NOT NULL DEFAULT 1,
                                 -- Incremented on recomposition (e.g. after gap recovery)
                                 -- Transcriptions carry this value to identify which audio they cover
    checksum_sha256 TEXT,        -- SHA-256 for tiered integrity validation
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE meeting_speaker_labels (
    meeting_id    UUID        NOT NULL REFERENCES meetings (id) ON DELETE CASCADE,
    speaker_label TEXT        NOT NULL,  -- Raw diarizer label, e.g. "Speaker 1"
    person_id     UUID        NOT NULL REFERENCES people (id) ON DELETE CASCADE,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (meeting_id, speaker_label)
);

CREATE INDEX idx_meeting_speaker_labels_person_id ON meeting_speaker_labels (person_id);

CREATE TABLE people (
    id                  UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id             UUID        NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    display_name        TEXT        NOT NULL,
    full_name           TEXT,
    title               TEXT,
    role                TEXT,
    email               TEXT,
    company             TEXT,
    tags                JSONB,
    voice_model_status  TEXT        DEFAULT 'untrained'
                                    CHECK (voice_model_status IN (
                                        'untrained',  -- No voice data collected
                                        'training',   -- Model training in progress
                                        'ready',      -- Embeddings available for matching
                                        'failed'      -- Training failed; user can retry
                                    )),
    voice_confidence    REAL,       -- 0–1 aggregate confidence across training samples
                                    -- REAL not DECIMAL: ML scores are approximate floats
    voice_vector        vector(192),  -- ECAPA-TDNN embedding; see ADR 0008
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (user_id, display_name)
);

CREATE INDEX idx_people_voice_vector_hnsw ON people USING hnsw (voice_vector vector_cosine_ops);

CREATE TABLE transcriptions (
    id             UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    meeting_id     UUID        NOT NULL REFERENCES meetings (id) ON DELETE CASCADE,
    status         TEXT        NOT NULL
                               CHECK (status IN (
                                   'pending',
                                   'transcribing',
                                   'synthesizing',         -- post-transcription: summary, topics, talking points
                                   'completed',
                                   'failed'
                               )),
    status_message TEXT,
    is_degraded    BOOLEAN     NOT NULL DEFAULT false,
    audio_version  INTEGER,    -- Matches audio_objects.audio_version
                               -- A meeting can have multiple transcriptions (live + batch reprocess);
                               -- this identifies which composed audio each one covers
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_transcriptions_meeting_id ON transcriptions (meeting_id);

CREATE TABLE transcript_segments (
    id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    transcription_id UUID        NOT NULL REFERENCES transcriptions (id) ON DELETE CASCADE,
    person_id        UUID        REFERENCES people (id) ON DELETE SET NULL,
    speaker_label    TEXT,
    text             TEXT        NOT NULL,
    start_ms         BIGINT      NOT NULL,            -- Millisecond offset; integer not decimal
    end_ms           BIGINT      NOT NULL DEFAULT 0,
    confidence       REAL        NOT NULL DEFAULT 0,  -- ML score; approximate float
    is_final         BOOLEAN     NOT NULL DEFAULT true,
    is_highlighted   BOOLEAN     NOT NULL DEFAULT false,
    feature_vector   vector(192),  -- ECAPA-TDNN embedding; matches people.voice_vector, see ADR 0008
    source_sequence  BIGINT,     -- ML-assigned monotonic sequence per live session
                                 -- A final segment with the same value replaces the interim one
                                 -- NULL for batch upload segments
    revision         SMALLINT    NOT NULL DEFAULT 1   -- Incremented on interim → final transition
                                                       -- Used by the UI for in-place replacement without layout shift
);

CREATE INDEX idx_transcript_time ON transcript_segments (transcription_id, start_ms);
CREATE INDEX idx_transcript_segments_feature_vector_hnsw
    ON transcript_segments USING hnsw (feature_vector vector_cosine_ops);
-- Hot-path for live segment revision: find the existing segment for this source_sequence to update
CREATE INDEX idx_transcript_segments_source_sequence
    ON transcript_segments (transcription_id, source_sequence)
    WHERE source_sequence IS NOT NULL;

CREATE TABLE synthesis (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    meeting_id UUID        NOT NULL UNIQUE REFERENCES meetings (id) ON DELETE CASCADE,
    headline   TEXT,
    summary    TEXT,
    key_points JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Used to efficiently fetch synthesis alongside a meeting
CREATE INDEX idx_synthesis_meeting_id ON synthesis (meeting_id);

CREATE TABLE topics (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    meeting_id UUID        NOT NULL REFERENCES meetings (id) ON DELETE CASCADE,
    title      TEXT        NOT NULL,
    summary    TEXT,
    is_final   BOOLEAN     NOT NULL DEFAULT false,  -- false during live generation; true after post-meeting synthesis
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ
);

CREATE INDEX idx_topics_meeting_id ON topics (meeting_id);
CREATE INDEX idx_topics_active     ON topics (meeting_id) WHERE deleted_at IS NULL;

CREATE TABLE topic_segments (
    topic_id     UUID        NOT NULL REFERENCES topics (id) ON DELETE CASCADE,
    segment_id   UUID        NOT NULL REFERENCES transcript_segments (id) ON DELETE CASCADE,
    PRIMARY KEY (topic_id, segment_id)
);

CREATE INDEX idx_topic_segments_segment_id ON topic_segments (segment_id);

CREATE TABLE talking_points (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    meeting_id UUID        NOT NULL REFERENCES meetings (id) ON DELETE CASCADE,
    topic_id   UUID        REFERENCES topics (id) ON DELETE SET NULL,
    content    TEXT        NOT NULL,
    is_final   BOOLEAN     NOT NULL DEFAULT false,  -- false during live generation; true after post-meeting synthesis
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ
);

CREATE INDEX idx_talking_points_meeting_id ON talking_points (meeting_id);
CREATE INDEX idx_talking_points_topic_id   ON talking_points (topic_id);
-- Post-meeting reconciliation soft-deletes live drafts and inserts finals;
-- active queries only scan non-deleted rows
CREATE INDEX idx_talking_points_active ON talking_points (meeting_id) WHERE deleted_at IS NULL;

CREATE TABLE talking_point_segments (
    talking_point_id UUID        NOT NULL REFERENCES talking_points (id) ON DELETE CASCADE,
    segment_id       UUID        NOT NULL REFERENCES transcript_segments (id) ON DELETE CASCADE,
    PRIMARY KEY (talking_point_id, segment_id)
);

CREATE INDEX idx_talking_point_segments_segment_id ON talking_point_segments (segment_id);

CREATE TABLE tasks (
    id             UUID                    PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id        UUID                    NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    content        TEXT                    NOT NULL,
    status         TEXT                    NOT NULL DEFAULT 'pending'
                                           CHECK (status IN ('pending', 'completed')),
    source         public.task_source_enum NOT NULL DEFAULT 'system',
                   -- 'system' = ML-generated; 'user' = user-created or user-edited
                   -- Promotion from 'system' → 'user' is the reconciliation signal
    due_date       DATE,
    assigned_to    UUID                    REFERENCES people (id) ON DELETE SET NULL,
    meeting_id     UUID                    REFERENCES meetings (id) ON DELETE SET NULL,
    parent_task_id UUID                    REFERENCES tasks (id) ON DELETE CASCADE,
    deleted_at     TIMESTAMPTZ,
    created_at     TIMESTAMPTZ             NOT NULL DEFAULT now(),
    updated_at     TIMESTAMPTZ             NOT NULL DEFAULT now()
);

-- Partial — active tasks are the dominant query path
CREATE INDEX idx_tasks_active         ON tasks (user_id, status, due_date) WHERE deleted_at IS NULL;
CREATE INDEX idx_tasks_meeting_id     ON tasks (meeting_id);
CREATE INDEX idx_tasks_assigned_to    ON tasks (assigned_to);
CREATE INDEX idx_tasks_parent_task_id ON tasks (parent_task_id);

-- 'live' covers meetings created from a live recording session
-- Append-only — PG enums support adding values but never removing them
CREATE TYPE public.meeting_source_enum AS ENUM ('recording', 'upload', 'text', 'anecdotal', 'live');

CREATE TABLE meetings (
    id          UUID                       PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID                       NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    title       TEXT                       NOT NULL,
    source_type public.meeting_source_enum NOT NULL,
    start_time  TIMESTAMPTZ                NOT NULL,
    end_time    TIMESTAMPTZ,
    notes       TEXT,  -- Live scratchpad, auto-saved with 500ms debounce via PATCH /meetings/{id}
                       -- Distinct from the `notes` table (freeform notes about people and meetings)
                       -- Inline here to avoid a join on the hot-path PK lookup during active recording
    deleted_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ                NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ                NOT NULL DEFAULT now()
);

-- Meeting list always filters non-deleted; partial index eliminates soft-deleted row scans entirely
CREATE INDEX idx_meetings_user_active ON meetings (user_id, created_at DESC) WHERE deleted_at IS NULL;

-- Composite PK (key, user_id) — the only access pattern is lookup by this pair;
-- no surrogate UUID needed
CREATE TABLE idempotency_keys (
    key             TEXT        NOT NULL,
    user_id         UUID        NOT NULL REFERENCES users (id) ON DELETE CASCADE,
                                -- User-scoped: the same key from two different users won't conflict
    response_status SMALLINT    NOT NULL,  -- Cached HTTP status code for replay
    response_body   JSONB       NOT NULL,  -- Cached response body
    expires_at      TIMESTAMPTZ NOT NULL,  -- 24h default TTL
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (key, user_id)
);

-- Used by the periodic cleanup job: DELETE WHERE expires_at < now()
CREATE INDEX idx_idempotency_keys_expires_at ON idempotency_keys (expires_at);