-- =====================================================================
-- LMCC - YouTube Sermon Archive module
-- Migration script
--
-- Run this ONCE against the live LMCC database (phpMyAdmin in cPanel,
-- or `mysql -u USER -p DBNAME < migration_youtube_module.sql`).
--
-- What this does:
--   1. Creates youtube_channel   - one cached row describing the LMCC
--                                  YouTube channel (discovered via the
--                                  API, not hand-entered).
--   2. Creates youtube_videos    - cached copy of every uploaded video,
--                                  keyed by YouTube's own video ID so
--                                  re-running sync never duplicates rows.
--   3. Creates youtube_sync_log  - one row per sync attempt (manual,
--                                  cron, or connection test), so the
--                                  admin dashboard has real history
--                                  instead of a single "last sync" cell.
--
-- This is a NEW module with no prior YouTube tables anywhere in the
-- existing LMCC schema (confirmed by audit), so nothing is altered,
-- renamed, or migrated from an older table - only created.
--
-- Safe to run more than once: every CREATE TABLE uses IF NOT EXISTS,
-- and nothing here touches members, finance_records, users, projects,
-- events, audit_logs, or any other existing LMCC table or its data.
-- =====================================================================

-- ---------------------------------------------------------------------
-- 1. youtube_channel - single-row cache of the discovered channel
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS youtube_channel (
    id                  INT UNSIGNED NOT NULL PRIMARY KEY DEFAULT 1,
    channel_id          VARCHAR(64)  NULL,
    channel_title       VARCHAR(255) NULL,
    channel_handle      VARCHAR(100) NULL,
    channel_description TEXT         NULL,
    thumbnail_url       VARCHAR(500) NULL,
    uploads_playlist_id VARCHAR(64)  NULL,
    featured_video_id   VARCHAR(32)  NULL,   -- manual override; NULL = auto (latest)
    last_discovered_at  DATETIME     NULL,
    last_sync_at        DATETIME     NULL,   -- last sync attempt (success or fail)
    last_sync_success_at DATETIME    NULL,   -- last sync that completed without error
    last_sync_video_count INT UNSIGNED NOT NULL DEFAULT 0,
    last_error          TEXT         NULL,
    last_error_at       DATETIME     NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT chk_youtube_channel_singleton CHECK (id = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed the single settings row if it doesn't exist yet, so the app can
-- always UPDATE ... WHERE id = 1 without a separate "does it exist" check.
INSERT IGNORE INTO youtube_channel (id) VALUES (1);

-- ---------------------------------------------------------------------
-- 2. youtube_videos - cached copy of every synced video
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS youtube_videos (
    id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    video_id           VARCHAR(32)  NOT NULL,
    channel_id         VARCHAR(64)  NULL,
    playlist_id        VARCHAR(64)  NULL,
    playlist_position  INT UNSIGNED NULL,
    title              VARCHAR(500) NOT NULL,
    description        TEXT         NULL,
    thumbnail_url      VARCHAR(500) NULL,
    published_at       DATETIME     NULL,
    duration_iso8601   VARCHAR(20)  NULL,     -- raw e.g. PT1H2M10S
    duration_seconds   INT UNSIGNED NOT NULL DEFAULT 0,
    broadcast_status   ENUM('none','live','upcoming') NOT NULL DEFAULT 'none',
    was_live           TINYINT(1)   NOT NULL DEFAULT 0, -- past live stream (has actualStartTime)
    category           VARCHAR(50)  NOT NULL DEFAULT 'Sermon',
    is_hidden          TINYINT(1)   NOT NULL DEFAULT 0,
    is_featured        TINYINT(1)   NOT NULL DEFAULT 0, -- mirrors youtube_channel.featured_video_id for fast lookups
    last_synced_at     DATETIME     NULL,
    created_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_video_id (video_id),
    FULLTEXT KEY idx_video_search (title, description),
    INDEX idx_published_at (published_at),
    INDEX idx_is_hidden (is_hidden),
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 3. youtube_sync_log - one row per sync/test attempt (history)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS youtube_sync_log (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    run_type       ENUM('manual','cron','test_connection') NOT NULL DEFAULT 'manual',
    status         ENUM('success','error') NOT NULL,
    videos_synced  INT UNSIGNED NOT NULL DEFAULT 0,
    api_calls_used INT UNSIGNED NOT NULL DEFAULT 0,
    duration_ms    INT UNSIGNED NOT NULL DEFAULT 0,
    message        TEXT NULL,
    triggered_by   INT UNSIGNED NULL,   -- users.id, NULL for cron
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Keep the log table from growing forever on shared hosting: a simple
-- cap enforced from PHP (see YouTubeSync::pruneSyncLog()) rather than
-- an event scheduler, since Truehost shared MySQL does not reliably
-- allow MySQL EVENTs.