-- ============================================================================
-- MEETING TRANSCRIPTION — COMBINED LIVE INSTALL
--
-- Exactly the 11 incremental meeting migrations (2026_07_06 .. 2026_07_09)
-- consolidated into ONE file for the live deploy: final-state CREATEs with the
-- later ALTER columns folded in. Column order matches what the incremental
-- files produce, verified by schema-diff against the dev DB (which ran the
-- originals).
--
-- >>> Run EITHER this file OR the 11 originals — NEVER BOTH on the same DB. <<<
-- Dev already ran the originals; live runs only this file:
--     psql -U postgres -d scapetime -f 2026_07_13_meetings_combined.sql
--
-- Live PG < 9.5-safe: CREATE TABLE IF NOT EXISTS, plain CREATE INDEX (no
-- IF NOT EXISTS), no ON CONFLICT, no pgvector. Everything runs in one
-- transaction — any failure rolls the whole install back (a duplicate-index
-- error is the loud symptom of running it on a DB that already has the
-- originals; nothing will be half-applied).
--
-- The originals' one data statement (vectors_v2's DELETE purge of old-model
-- vectors) is intentionally omitted: it only mattered on dev, where bge-384
-- vectors predated the qwen3-1024 model. A fresh install has no rows to purge.
-- ============================================================================

BEGIN;

-- ---------------------------------------------------------------------------
-- meetings — one row per meeting (recorded, uploaded, or bot-joined).
-- Folded in: bot columns (07_08_bot), series_id (07_09_attendees),
-- todo_digest_at (07_09_insight_todo).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meetings (
    id               varchar(32)  NOT NULL PRIMARY KEY,
    company          varchar(32)  NOT NULL,
    created_by       varchar(32),
    title            varchar(255),
    meeting_date     varchar(32),
    source           varchar(16)  NOT NULL DEFAULT 'record',  -- record | upload | bot
    status           varchar(24)  NOT NULL DEFAULT 'created', -- created|uploading|uploaded|queued|processing|live|transcribed|error
    duration_seconds integer,
    audio_path       varchar(255),          -- files/company/<company>/meetings/<id>/audio.<ext>
    audio_mime       varchar(64),
    audio_size_bytes bigint,
    live_transcript  text,                  -- rough live text, saved as it streams
    error_message    text,
    created          timestamp    NOT NULL DEFAULT now(),
    updated          timestamp,
    bot_session_id   varchar(64),           -- online-meeting bot session
    bot_turns_json   text,                  -- platform active-speaker timeline
    todo_digest_at   timestamp,             -- one-shot guard for the finalize digest
    series_id        varchar(128),          -- recurring-meeting identity (calendar UID)
    part_durations   text                   -- JSON seconds per audio part (resumed recordings)
);

-- ---------------------------------------------------------------------------
-- meeting_jobs — one row per worker job. webhook_token authorizes BOTH the
-- worker's audio download and its result push for exactly this job.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_jobs (
    id                 varchar(32)  NOT NULL PRIMARY KEY,
    meeting_id         varchar(32)  NOT NULL,
    company            varchar(32)  NOT NULL,
    kind               varchar(16)  NOT NULL DEFAULT 'transcribe',
    provider           varchar(16)  NOT NULL DEFAULT 'runpod',
    provider_job_id    varchar(64),
    status             varchar(16)  NOT NULL DEFAULT 'queued', -- queued|sent|running|complete|error
    webhook_token      varchar(64)  NOT NULL,
    payload_json       text,                  -- what we sent (minus secrets), for debugging
    error_message      text,
    attempts           integer      NOT NULL DEFAULT 0,
    result_received_at timestamp,
    created            timestamp    NOT NULL DEFAULT now(),
    updated            timestamp
);

-- ---------------------------------------------------------------------------
-- meeting_transcripts — canonical transcript per completed job. segments_json
-- is the viewer payload (no per-word data); word-level JSON stays on disk at
-- words_path and is never decoded in PHP. plain_text feeds ILIKE + Claude.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_transcripts (
    id               varchar(32)  NOT NULL PRIMARY KEY,
    meeting_id       varchar(32)  NOT NULL,
    company          varchar(32)  NOT NULL,
    engine           varchar(64),
    language         varchar(8),
    segments_json    text,
    plain_text       text,
    speaker_map_json text,                   -- {"SPEAKER_00":"Eric",...} renames + auto-names
    words_path       varchar(255),
    created          timestamp    NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- meeting_summaries — Claude-generated artifacts (minutes | actions | summary).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_summaries (
    id               varchar(32)  NOT NULL PRIMARY KEY,
    meeting_id       varchar(32)  NOT NULL,
    company          varchar(32)  NOT NULL,
    kind             varchar(16)  NOT NULL,
    model            varchar(64),
    content          text,
    created          timestamp    NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- meeting_speakers — enrolled voices for auto-naming.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_speakers (
    id                varchar(32)  NOT NULL PRIMARY KEY,
    company           varchar(32)  NOT NULL,
    person_name       varchar(128) NOT NULL,
    user_id           varchar(32),
    engine            varchar(32),            -- wespeaker
    embedding_json    text,
    sample_audio_path varchar(255),
    active            integer      NOT NULL DEFAULT 1,
    created           timestamp    NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- meeting_insights — live insights; the insight row IS the personal todo.
-- Folded in: converted_ref (07_09_insight_convert), done_at/done_by
-- (07_09_insight_todo).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_insights (
    id               varchar(32)  NOT NULL PRIMARY KEY,
    meeting_id       varchar(32)  NOT NULL,
    company          varchar(32)  NOT NULL,
    kind             varchar(16)  NOT NULL,            -- action | decision | question
    content          text         NOT NULL,
    owner_name       varchar(128),                     -- speaker name as heard ('' if none)
    owner_user_id    varchar(32),                      -- resolved Scapetime user
    source_offset_ms integer,                          -- where in the meeting it surfaced
    status           varchar(16)  NOT NULL DEFAULT 'open',  -- open|dismissed|converted|done
    created          timestamp    NOT NULL DEFAULT now(),
    updated          timestamp,
    converted_ref    varchar(32),                      -- incident id when escalated
    done_at          timestamp,
    done_by          varchar(32)
);

-- ---------------------------------------------------------------------------
-- meeting_distribution — debrief send log, one row per recipient per send.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_distribution (
    id              varchar(32)  NOT NULL PRIMARY KEY,
    meeting_id      varchar(32)  NOT NULL,
    company         varchar(32)  NOT NULL,
    kind            varchar(16)  NOT NULL DEFAULT 'debrief',
    recipient_name  varchar(128),
    recipient_email varchar(200) NOT NULL,
    user_id         varchar(32),                    -- linked Scapetime user, if any
    status          varchar(16)  NOT NULL,          -- sent | failed
    error_message   text,
    sent_by         varchar(32),
    created         timestamp    NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- meeting_entities — resolved mentions (property/user/crew spoken in the
-- meeting), anchored to the transcript by offset_ms.
-- meeting_entity_alias — learning table: 'match' teaches spoken names,
-- 'block' records rejections so a wrong chip never returns.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_entities (
    id           varchar(32)  NOT NULL PRIMARY KEY,
    meeting_id   varchar(32)  NOT NULL,
    company      varchar(32)  NOT NULL,
    entity_type  varchar(16)  NOT NULL,            -- property | user | crew | truck
    entity_id    varchar(32)  NOT NULL,
    entity_name  varchar(200),                     -- display name at detection time
    mention_text varchar(200),                     -- the words as heard
    offset_ms    integer,
    confidence   numeric(4,2),
    status       varchar(16)  NOT NULL DEFAULT 'auto',  -- auto | confirmed | rejected
    created      timestamp    NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS meeting_entity_alias (
    id          varchar(32)  NOT NULL PRIMARY KEY,
    company     varchar(32)  NOT NULL,
    entity_type varchar(16)  NOT NULL,
    entity_id   varchar(32)  NOT NULL,
    alias       varchar(200) NOT NULL,             -- normalized heard-text
    kind        varchar(8)   NOT NULL DEFAULT 'match',  -- match | block
    created_by  varchar(32),
    created     timestamp    NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- meeting_attendees — one row per person per meeting, from whichever source
-- saw them first: calendar | roster | manual | voice.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_attendees (
    id           varchar(32)  NOT NULL PRIMARY KEY,
    meeting_id   varchar(32)  NOT NULL,
    company      varchar(32)  NOT NULL,
    source       varchar(16)  NOT NULL,             -- calendar | roster | manual | voice
    display_name varchar(200) NOT NULL,
    email        varchar(200),
    user_id      varchar(32),                       -- resolved Scapetime user
    joined_at    timestamp,
    left_at      timestamp,
    created      timestamp    NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- meeting_property_vectors — embeddings of property NAMES for entity-aware
-- search. Populate after deploy via /meeting/embedproperties per company.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_property_vectors (
    property_id varchar(32)  NOT NULL PRIMARY KEY,
    company     varchar(32)  NOT NULL,
    name        varchar(200) NOT NULL,
    vec         text         NOT NULL,
    created     timestamp    NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- meeting_segment_vectors — semantic-search vectors; rows are merged WINDOWS
-- (multi-segment spans). vec = base64 little-endian float32[1024]
-- (Qwen3-Embedding-0.6B, L2-normalized); similarity = dot product in PHP
-- (no pgvector on this box, ever).
-- Folded in: snippet/speaker (07_09_vectors_v2).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_segment_vectors (
    meeting_id varchar(32) NOT NULL,
    seg_index  integer     NOT NULL,
    company    varchar(32) NOT NULL,
    s          numeric(9,2),
    e          numeric(9,2),
    vec        text        NOT NULL,
    created    timestamp   NOT NULL DEFAULT now(),
    snippet    text,
    speaker    varchar(64),
    PRIMARY KEY (meeting_id, seg_index)
);

-- ---------------------------------------------------------------------------
-- meeting_words — word-level timings per segment ({w,s,e}); the anchor layer
-- for word hover/seek, highlights, and comments. (07_13_meeting_words)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_words (
    meeting_id varchar(32) NOT NULL,
    seg_index  integer     NOT NULL,
    company    varchar(32) NOT NULL,
    words_json text        NOT NULL,   -- [{w,s,e}] for this segment, seconds
    created    timestamp   NOT NULL DEFAULT now(),
    PRIMARY KEY (meeting_id, seg_index)
);

-- ---------------------------------------------------------------------------
-- meeting_marks — highlights ('highlight') and word-anchored notes ('note');
-- anchor = seg_index + word span, quote/start_ms as durable fallback.
-- (07_13_meeting_marks)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_marks (
    id         varchar(32)  NOT NULL PRIMARY KEY,
    meeting_id varchar(32)  NOT NULL,
    company    varchar(32)  NOT NULL,
    kind       varchar(12)  NOT NULL DEFAULT 'highlight',   -- highlight | note
    seg_index  integer      NOT NULL,
    w_start    integer      NOT NULL DEFAULT -1,
    w_end      integer      NOT NULL DEFAULT -1,
    quote      varchar(400),
    start_ms   integer,
    body       text,
    created_by varchar(32),
    created    timestamp    NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- meeting_edits — audit log of transcript edits (segment text / speaker
-- overrides): who changed what, from what, to what. (07_14_meeting_edits)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_edits (
    id          varchar(32) NOT NULL PRIMARY KEY,
    meeting_id  varchar(32) NOT NULL,
    company     varchar(32) NOT NULL,
    seg_index   integer     NOT NULL,
    old_text    text,
    new_text    text,
    old_speaker varchar(64),
    new_speaker varchar(64),
    edited_by   varchar(32),
    created     timestamp   NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- meeting_voice_obs — per-chunk enrolled-voiceprint verdicts for live audio
-- (both record page and bot meetings); strong matches also upsert a
-- source='voice' attendee. (07_14_meeting_voice_obs)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS meeting_voice_obs (
    id            varchar(32) NOT NULL PRIMARY KEY,
    meeting_id    varchar(32) NOT NULL,
    company       varchar(32) NOT NULL,
    source        varchar(8)  NOT NULL,            -- bot | live
    offset_ms     integer     NOT NULL DEFAULT 0,
    dur_ms        integer     NOT NULL DEFAULT 0,
    platform_name varchar(128),                    -- bot active-speaker label ('' on record path)
    voice_name    varchar(128),                    -- best enrolled match
    voice_user_id varchar(32),
    score         numeric(4,3),
    created       timestamp   NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- Indexes (plain CREATE INDEX — errors loudly if any already exist, which
-- means you are running this on a DB that already has the originals; the
-- transaction rolls everything back).
-- ---------------------------------------------------------------------------
CREATE INDEX idx_meetings_company        ON meetings (company);
CREATE INDEX idx_meeting_jobs_meeting    ON meeting_jobs (meeting_id);
CREATE UNIQUE INDEX idx_meeting_jobs_token ON meeting_jobs (webhook_token);
CREATE INDEX idx_meeting_tx_meeting      ON meeting_transcripts (meeting_id);
CREATE INDEX idx_meeting_sum_meeting     ON meeting_summaries (meeting_id);
CREATE INDEX idx_meeting_spk_company     ON meeting_speakers (company);
CREATE INDEX idx_meeting_insights_meeting ON meeting_insights (meeting_id);
CREATE INDEX idx_meeting_insights_owner  ON meeting_insights (owner_user_id, status);
CREATE INDEX idx_meeting_distribution_meeting ON meeting_distribution (meeting_id);
CREATE INDEX idx_meeting_entities_meeting ON meeting_entities (meeting_id);
CREATE INDEX idx_meeting_entities_entity  ON meeting_entities (entity_type, entity_id);
CREATE INDEX idx_meeting_entity_alias_company ON meeting_entity_alias (company);
CREATE INDEX idx_meeting_attendees_meeting ON meeting_attendees (meeting_id);
CREATE INDEX idx_meeting_attendees_user    ON meeting_attendees (company, user_id);
CREATE INDEX idx_mpv_company ON meeting_property_vectors (company);
CREATE INDEX idx_msv_company ON meeting_segment_vectors (company);
CREATE INDEX idx_mw_company  ON meeting_words (company);
CREATE INDEX idx_mmk_meeting ON meeting_marks (meeting_id);
CREATE INDEX idx_medits_meeting ON meeting_edits (meeting_id);
CREATE INDEX idx_mvo_meeting  ON meeting_voice_obs (meeting_id);

COMMIT;
