-- ============================================================================================
-- PRODUCTION MIGRATION — Notetaker Agent, Phase 1 (capture pipeline)
-- ============================================================================================
-- capture_sessions: one attempt to get audio for a meeting (a bot may crash and rejoin, or a
-- user may upload a recording after the fact — either way it's a capture_session row).
-- transcript_segments: the hot table (~700 rows/meeting-hour per CONVENE-SPECIFICATION.md §4.3),
-- BIGINT PK not the ULID-style ids used elsewhere in that spec, matching every other hot table
-- in this codebase (transcript_chunks/vector work is deferred — see D-01, not needed until
-- cross-meeting dedupe is built).
--
-- Also adds `pipeline_step` to fact_meetings so a retry after a partial failure resumes rather
-- than re-running (and re-billing) transcription — see D-02 / CONVENE-SPECIFICATION.md §5.4.
--
-- Idempotent — safe to re-run.
-- Deploy: mysql -u <user> -p `stacie_Aggie_v1.0` < db/meetings_capture.sql
-- ============================================================================================

USE `stacie_Aggie_v1.0`;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_meetings' AND COLUMN_NAME = 'pipeline_step');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE fact_meetings ADD COLUMN pipeline_step VARCHAR(30) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS capture_sessions (
    id                   BIGINT AUTO_INCREMENT PRIMARY KEY,
    meeting_id           BIGINT       NOT NULL,
    provider             VARCHAR(20)  NOT NULL,
    provider_bot_id      VARCHAR(255) NULL,
    attempt              INT          NOT NULL DEFAULT 1,
    status               VARCHAR(20)  NOT NULL DEFAULT 'STARTING',
    started_at           TIMESTAMP(6) NULL,
    ended_at             TIMESTAMP(6) NULL,
    audio_uri            VARCHAR(1024) NULL,
    error                VARCHAR(1000) NULL,
    transcribe_job_id    VARCHAR(64)  NULL,
    created_at           TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),

    UNIQUE KEY uq_capture_attempt (meeting_id, attempt),
    INDEX ix_capture_meeting (meeting_id),
    CONSTRAINT fk_capture_meeting FOREIGN KEY (meeting_id)
        REFERENCES fact_meetings(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS transcript_segments (
    id              BIGINT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
    meeting_id      BIGINT       NOT NULL,
    session_id      BIGINT       NULL,
    seq             INT          NOT NULL,
    speaker_label   VARCHAR(50)  NULL,
    participant_id  BIGINT       NULL,
    content         MEDIUMTEXT   NOT NULL,
    start_ms        INT          NOT NULL,
    end_ms          INT          NOT NULL,
    confidence      FLOAT        NULL,
    words           JSON         NULL,
    source          VARCHAR(10)  NOT NULL DEFAULT 'batch',
    is_final        TINYINT      NOT NULL DEFAULT 1,
    edited_by       INT          NULL,
    edited_at       TIMESTAMP(6) NULL,
    created_at      TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),

    UNIQUE KEY uq_segment (meeting_id, source, seq),
    INDEX ix_segments_time (meeting_id, start_ms),
    FULLTEXT INDEX ft_segments_content (content),
    CONSTRAINT fk_segments_meeting FOREIGN KEY (meeting_id)
        REFERENCES fact_meetings(id) ON DELETE CASCADE
) ENGINE=InnoDB;