-- Meetings PR1: batch core for the meeting-transcription redesign — record/upload a
-- meeting, RunPod worker (parakeet + pyannote community-1) returns a speaker-labeled
-- transcript, Claude generates minutes/summaries. Live rough transcript (Phase 2) and
-- voice enrollment auto-naming (Phase 3) columns are included now to avoid re-migrating.
-- Run on local + live BEFORE deploying the code.
-- Live PG < 9.5: CREATE TABLE IF NOT EXISTS is fine; CREATE INDEX must be plain (no
-- IF NOT EXISTS), no ON CONFLICT. Re-run cleanup: DROP TABLE IF EXISTS ...; DROP INDEX ...

-- One row per meeting (recorded in-browser or uploaded file).
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
    status           varchar(24)  NOT NULL DEFAULT 'created', -- created|uploading|uploaded|queued|processing|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,                  -- Phase 2: rough live text saved at stop
    error_message    text,
    created          timestamp    NOT NULL DEFAULT now(),
    updated          timestamp
);

-- One row per worker job (transcribe now; enroll in Phase 3). 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
);

-- Canonical transcript per completed job. segments_json is the viewer payload
-- ([{s,e,speaker,text}], no per-word data — a 2h meeting is ~200-500 KB, safe for
-- PHP 5.4 json_decode). Full word-level JSON stays on DISK at words_path and is
-- never decoded in PHP. plain_text feeds ILIKE search + the Claude prompt.
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",...} manual renames + auto-names
    words_path       varchar(255),
    created          timestamp    NOT NULL DEFAULT now()
);

-- Claude-generated artifacts (minutes | actions | summary), newest kept per kind.
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()
);

-- Phase 3 (enrolled voices for auto-naming). Created now, empty until used.
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()
);

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);
