-- ============================================================================================
-- PRODUCTION MIGRATION — Notetaker Agent, Phase 0
-- ============================================================================================
-- Extends the EXISTING fact_meetings table (used today by manual entry + Fireflies sync, see
-- fact_meetings.sql / the Activity Timeline meeting-linking feature) with the bot-capture
-- lifecycle fields the Notetaker Agent needs, rather than creating a competing "meetings"
-- table. `status` keeps its existing calendar meaning (Scheduled/Completed/Cancelled); the new
-- `bot_status` column is a separate lifecycle exclusively for bot-captured meetings so the two
-- integrations never collide on one column's meaning.
--
-- Also adds meeting_participants: structured, speaker-labelled attendance for bot-captured
-- meetings, distinct from fact_meetings.attendees (a free-text/legacy column).
--
-- See "Added Requirement/Notetaker Agent/CONVENE-SPECIFICATION.md" for the full design and
-- PENDING-DECISIONS.md for the decisions this schema encodes (D-01, D-02, D-07, D-10, D-12).
-- Transcript segments, notes, action items, and proposed actions land in later migrations once
-- the capture pipeline (Phase 1) and approval gate (Phase 2) are built.
--
-- Idempotent — safe to re-run.
-- Deploy: mysql -u <user> -p `stacie_Aggie_v1.0` < db/meetings_core.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 = 'owner_user_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE fact_meetings ADD COLUMN owner_user_id INT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TABLE IF NOT EXISTS meeting_participants (
    id                     BIGINT AUTO_INCREMENT PRIMARY KEY,
    meeting_id             BIGINT       NOT NULL,
    user_id                INT          NULL,
    display_name           VARCHAR(255) NOT NULL,
    email                  VARCHAR(320) NULL,
    is_host                TINYINT      NOT NULL DEFAULT 0,
    is_external             TINYINT      NOT NULL DEFAULT 0,
    speaker_label          VARCHAR(50)  NULL,
    resolution_confidence  FLOAT        NULL,
    joined_at              TIMESTAMP(6) NULL,
    left_at                TIMESTAMP(6) NULL,
    talk_time_ms           INT          NOT NULL DEFAULT 0,
    word_count             INT          NOT NULL DEFAULT 0,

    INDEX ix_meeting_participants_meeting (meeting_id),
    INDEX ix_meeting_participants_email   (email),
    CONSTRAINT fk_meeting_participants_meeting FOREIGN KEY (meeting_id)
        REFERENCES fact_meetings(id) ON DELETE CASCADE
) ENGINE=InnoDB;