use crate::error::Result;
use rusqlite::Connection;

pub fn configure(conn: &Connection) -> Result<()> {
    conn.execute_batch(
        r#"
        PRAGMA foreign_keys = ON;
        PRAGMA journal_mode = WAL;
        PRAGMA synchronous = NORMAL;
        PRAGMA busy_timeout = 5000;
        PRAGMA temp_store = MEMORY;
        "#,
    )?;
    Ok(())
}

pub fn migrate(conn: &Connection) -> Result<()> {
    let version: i64 = conn.pragma_query_value(None, "user_version", |row| row.get(0))?;

    if version >= 6 {
        return Ok(());
    }

    if version == 0 {
        conn.execute_batch(
            r#"
            BEGIN;

            CREATE TABLE IF NOT EXISTS media (
                id INTEGER PRIMARY KEY,
                media_kind TEXT NOT NULL CHECK(media_kind IN ('anime', 'manga', 'show', 'movie')),
                title_display TEXT NOT NULL,
                title_romaji TEXT,
                title_english TEXT,
                title_native TEXT,
                synopsis TEXT,
                format TEXT,
                status TEXT,
                season TEXT,
                season_year INTEGER,
                episodes INTEGER,
                chapters INTEGER,
                volumes INTEGER,
                country_of_origin TEXT,
                cover_image TEXT,
                banner_image TEXT,
                provider_rating REAL,
                nsfw INTEGER NOT NULL DEFAULT 0,
                tags_json TEXT NOT NULL DEFAULT '[]' CHECK(json_valid(tags_json)),
                genres_json TEXT NOT NULL DEFAULT '[]' CHECK(json_valid(genres_json)),
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE IF NOT EXISTS media_alias (
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                alias TEXT NOT NULL,
                normalized_alias TEXT NOT NULL,
                UNIQUE(media_id, normalized_alias)
            );

            CREATE INDEX IF NOT EXISTS media_alias_normalized_idx
                ON media_alias(normalized_alias);
            CREATE INDEX IF NOT EXISTS media_kind_idx
                ON media(media_kind);
            CREATE INDEX IF NOT EXISTS media_season_year_idx
                ON media(season_year);

            CREATE TABLE IF NOT EXISTS media_external_id (
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                media_kind TEXT NOT NULL CHECK(media_kind IN ('anime', 'manga', 'show', 'movie')),
                source TEXT NOT NULL,
                source_id TEXT NOT NULL,
                url TEXT,
                UNIQUE(source, media_kind, source_id),
                UNIQUE(media_id, source)
            );

            CREATE TABLE IF NOT EXISTS source_record (
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                media_kind TEXT NOT NULL CHECK(media_kind IN ('anime', 'manga', 'show', 'movie')),
                source TEXT NOT NULL,
                source_id TEXT NOT NULL,
                url TEXT,
                remote_updated_at TEXT,
                fetched_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                raw_json TEXT CHECK(raw_json IS NULL OR json_valid(raw_json)),
                payload_hash TEXT,
                UNIQUE(source, media_kind, source_id)
            );

            CREATE TABLE IF NOT EXISTS field_provenance (
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                field_name TEXT NOT NULL,
                source TEXT NOT NULL,
                source_id TEXT NOT NULL,
                score REAL NOT NULL,
                reason TEXT NOT NULL,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY(media_id, field_name)
            );

            CREATE TABLE IF NOT EXISTS sync_state (
                source TEXT NOT NULL,
                scope TEXT NOT NULL,
                cursor_json TEXT,
                last_success_at TEXT,
                last_error TEXT,
                last_page INTEGER,
                mode TEXT NOT NULL DEFAULT 'full',
                PRIMARY KEY(source, scope)
            );

            CREATE VIRTUAL TABLE IF NOT EXISTS media_fts USING fts5(
                media_id UNINDEXED,
                title_display,
                aliases,
                synopsis,
                tokenize = 'unicode61 remove_diacritics 2'
            );

            PRAGMA user_version = 3;
            COMMIT;
            "#,
        )?;
    } else if version == 1 {
        conn.execute_batch(
            r#"
            BEGIN;
            ALTER TABLE media ADD COLUMN cover_image TEXT;
            ALTER TABLE media ADD COLUMN banner_image TEXT;
            ALTER TABLE media ADD COLUMN provider_rating REAL;

            CREATE TABLE IF NOT EXISTS media_external_id_v3 (
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                media_kind TEXT NOT NULL CHECK(media_kind IN ('anime', 'manga')),
                source TEXT NOT NULL,
                source_id TEXT NOT NULL,
                url TEXT,
                UNIQUE(source, media_kind, source_id),
                UNIQUE(media_id, source)
            );

            INSERT OR IGNORE INTO media_external_id_v3 (media_id, media_kind, source, source_id, url)
            SELECT
                media_external_id.media_id,
                media.media_kind,
                media_external_id.source,
                media_external_id.source_id,
                media_external_id.url
            FROM media_external_id
            INNER JOIN media ON media.id = media_external_id.media_id;

            DROP TABLE media_external_id;
            ALTER TABLE media_external_id_v3 RENAME TO media_external_id;

            CREATE TABLE IF NOT EXISTS source_record_v3 (
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                media_kind TEXT NOT NULL CHECK(media_kind IN ('anime', 'manga')),
                source TEXT NOT NULL,
                source_id TEXT NOT NULL,
                url TEXT,
                remote_updated_at TEXT,
                fetched_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                raw_json TEXT CHECK(raw_json IS NULL OR json_valid(raw_json)),
                payload_hash TEXT,
                UNIQUE(source, media_kind, source_id)
            );

            INSERT OR IGNORE INTO source_record_v3 (
                media_id,
                media_kind,
                source,
                source_id,
                url,
                remote_updated_at,
                fetched_at,
                raw_json,
                payload_hash
            )
            SELECT
                source_record.media_id,
                media.media_kind,
                source_record.source,
                source_record.source_id,
                source_record.url,
                source_record.remote_updated_at,
                source_record.fetched_at,
                source_record.raw_json,
                source_record.payload_hash
            FROM source_record
            INNER JOIN media ON media.id = source_record.media_id;

            DROP TABLE source_record;
            ALTER TABLE source_record_v3 RENAME TO source_record;

            CREATE TABLE IF NOT EXISTS field_provenance (
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                field_name TEXT NOT NULL,
                source TEXT NOT NULL,
                source_id TEXT NOT NULL,
                score REAL NOT NULL,
                reason TEXT NOT NULL,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY(media_id, field_name)
            );

            PRAGMA user_version = 3;
            COMMIT;
            "#,
        )?;
    } else if version == 2 {
        conn.execute_batch(
            r#"
            BEGIN;

            CREATE TABLE IF NOT EXISTS media_external_id_v3 (
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                media_kind TEXT NOT NULL CHECK(media_kind IN ('anime', 'manga')),
                source TEXT NOT NULL,
                source_id TEXT NOT NULL,
                url TEXT,
                UNIQUE(source, media_kind, source_id),
                UNIQUE(media_id, source)
            );

            INSERT OR IGNORE INTO media_external_id_v3 (media_id, media_kind, source, source_id, url)
            SELECT
                media_external_id.media_id,
                media.media_kind,
                media_external_id.source,
                media_external_id.source_id,
                media_external_id.url
            FROM media_external_id
            INNER JOIN media ON media.id = media_external_id.media_id;

            DROP TABLE media_external_id;
            ALTER TABLE media_external_id_v3 RENAME TO media_external_id;

            CREATE TABLE IF NOT EXISTS source_record_v3 (
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                media_kind TEXT NOT NULL CHECK(media_kind IN ('anime', 'manga')),
                source TEXT NOT NULL,
                source_id TEXT NOT NULL,
                url TEXT,
                remote_updated_at TEXT,
                fetched_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                raw_json TEXT CHECK(raw_json IS NULL OR json_valid(raw_json)),
                payload_hash TEXT,
                UNIQUE(source, media_kind, source_id)
            );

            INSERT OR IGNORE INTO source_record_v3 (
                media_id,
                media_kind,
                source,
                source_id,
                url,
                remote_updated_at,
                fetched_at,
                raw_json,
                payload_hash
            )
            SELECT
                source_record.media_id,
                media.media_kind,
                source_record.source,
                source_record.source_id,
                source_record.url,
                source_record.remote_updated_at,
                source_record.fetched_at,
                source_record.raw_json,
                source_record.payload_hash
            FROM source_record
            INNER JOIN media ON media.id = source_record.media_id;

            DROP TABLE source_record;
            ALTER TABLE source_record_v3 RENAME TO source_record;

            CREATE TABLE IF NOT EXISTS field_provenance (
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                field_name TEXT NOT NULL,
                source TEXT NOT NULL,
                source_id TEXT NOT NULL,
                score REAL NOT NULL,
                reason TEXT NOT NULL,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY(media_id, field_name)
            );

            PRAGMA user_version = 3;
            COMMIT;
            "#,
        )?;
    }

    if version < 4 {
        conn.execute_batch(
            r#"
            BEGIN;

            CREATE TABLE IF NOT EXISTS media_v4 (
                id INTEGER PRIMARY KEY,
                media_kind TEXT NOT NULL CHECK(media_kind IN ('anime', 'manga', 'show', 'movie')),
                title_display TEXT NOT NULL,
                title_romaji TEXT,
                title_english TEXT,
                title_native TEXT,
                synopsis TEXT,
                format TEXT,
                status TEXT,
                season TEXT,
                season_year INTEGER,
                episodes INTEGER,
                chapters INTEGER,
                volumes INTEGER,
                country_of_origin TEXT,
                cover_image TEXT,
                banner_image TEXT,
                provider_rating REAL,
                nsfw INTEGER NOT NULL DEFAULT 0,
                tags_json TEXT NOT NULL DEFAULT '[]' CHECK(json_valid(tags_json)),
                genres_json TEXT NOT NULL DEFAULT '[]' CHECK(json_valid(genres_json)),
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            );

            INSERT OR IGNORE INTO media_v4 (
                id, media_kind, title_display, title_romaji, title_english, title_native,
                synopsis, format, status, season, season_year, episodes, chapters, volumes,
                country_of_origin, cover_image, banner_image, provider_rating, nsfw,
                tags_json, genres_json, created_at, updated_at
            )
            SELECT
                id, media_kind, title_display, title_romaji, title_english, title_native,
                synopsis, format, status, season, season_year, episodes, chapters, volumes,
                country_of_origin, cover_image, banner_image, provider_rating, nsfw,
                tags_json, genres_json, created_at, updated_at
            FROM media;

            DROP TABLE media;
            ALTER TABLE media_v4 RENAME TO media;

            CREATE TABLE IF NOT EXISTS media_external_id_v4 (
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                media_kind TEXT NOT NULL CHECK(media_kind IN ('anime', 'manga', 'show', 'movie')),
                source TEXT NOT NULL,
                source_id TEXT NOT NULL,
                url TEXT,
                UNIQUE(source, media_kind, source_id),
                UNIQUE(media_id, source)
            );

            INSERT OR IGNORE INTO media_external_id_v4 (media_id, media_kind, source, source_id, url)
            SELECT media_id, media_kind, source, source_id, url FROM media_external_id;

            DROP TABLE media_external_id;
            ALTER TABLE media_external_id_v4 RENAME TO media_external_id;

            CREATE TABLE IF NOT EXISTS source_record_v4 (
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                media_kind TEXT NOT NULL CHECK(media_kind IN ('anime', 'manga', 'show', 'movie')),
                source TEXT NOT NULL,
                source_id TEXT NOT NULL,
                url TEXT,
                remote_updated_at TEXT,
                fetched_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                raw_json TEXT CHECK(raw_json IS NULL OR json_valid(raw_json)),
                payload_hash TEXT,
                UNIQUE(source, media_kind, source_id)
            );

            INSERT OR IGNORE INTO source_record_v4 (
                media_id, media_kind, source, source_id, url, remote_updated_at, fetched_at, raw_json, payload_hash
            )
            SELECT media_id, media_kind, source, source_id, url, remote_updated_at, fetched_at, raw_json, payload_hash
            FROM source_record;

            DROP TABLE source_record;
            ALTER TABLE source_record_v4 RENAME TO source_record;

            CREATE INDEX IF NOT EXISTS media_alias_normalized_idx
                ON media_alias(normalized_alias);
            CREATE INDEX IF NOT EXISTS media_kind_idx
                ON media(media_kind);
            CREATE INDEX IF NOT EXISTS media_season_year_idx
                ON media(season_year);

            PRAGMA user_version = 4;
            COMMIT;
            "#,
        )?;
    }

    if version < 5 {
        conn.execute_batch(
            r#"
            BEGIN;

            CREATE TABLE IF NOT EXISTS episode (
                id INTEGER PRIMARY KEY,
                source TEXT NOT NULL,
                source_id TEXT NOT NULL,
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                media_kind TEXT NOT NULL CHECK(media_kind IN ('anime', 'manga', 'show', 'movie')),
                season_number INTEGER,
                episode_number INTEGER,
                absolute_number INTEGER,
                title_display TEXT,
                title_original TEXT,
                synopsis TEXT,
                air_date TEXT,
                runtime_minutes INTEGER,
                thumbnail_url TEXT,
                raw_titles_json TEXT CHECK(raw_titles_json IS NULL OR json_valid(raw_titles_json)),
                raw_json TEXT CHECK(raw_json IS NULL OR json_valid(raw_json)),
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(source, media_kind, source_id, media_id)
            );

            CREATE INDEX IF NOT EXISTS episode_media_id_idx ON episode(media_id);
            CREATE INDEX IF NOT EXISTS episode_source_idx ON episode(source);
            CREATE INDEX IF NOT EXISTS episode_absolute_number_idx ON episode(media_id, absolute_number);
            CREATE INDEX IF NOT EXISTS episode_season_episode_idx ON episode(media_id, season_number, episode_number);

            PRAGMA user_version = 5;
            COMMIT;
            "#,
        )?;
    }

    if version < 6 {
        conn.execute_batch(
            r#"
            BEGIN;

            -- The v5 episode table used source-specific rows directly.
            -- The new design has canonical episode + episode_source_record.
            -- We must recreate episode with the canonical schema.
            DROP TABLE IF EXISTS episode;
            DROP TABLE IF EXISTS episode_source_record;

            CREATE TABLE episode (
                id INTEGER PRIMARY KEY,
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                season_number INTEGER,
                episode_number INTEGER,
                absolute_number INTEGER,
                title_display TEXT,
                title_original TEXT,
                titles_json TEXT CHECK(titles_json IS NULL OR json_valid(titles_json)),
                synopsis TEXT,
                air_date TEXT,
                runtime_minutes INTEGER,
                thumbnail_url TEXT,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            );

            CREATE TABLE episode_source_record (
                id INTEGER PRIMARY KEY,
                episode_id INTEGER REFERENCES episode(id) ON DELETE CASCADE,
                source TEXT NOT NULL,
                source_id TEXT NOT NULL,
                media_id INTEGER NOT NULL REFERENCES media(id) ON DELETE CASCADE,
                media_kind TEXT NOT NULL CHECK(media_kind IN ('anime', 'manga', 'show', 'movie')),
                season_number INTEGER,
                episode_number INTEGER,
                absolute_number INTEGER,
                title_display TEXT,
                title_original TEXT,
                titles_json TEXT CHECK(titles_json IS NULL OR json_valid(titles_json)),
                synopsis TEXT,
                air_date TEXT,
                runtime_minutes INTEGER,
                thumbnail_url TEXT,
                raw_json TEXT CHECK(raw_json IS NULL OR json_valid(raw_json)),
                fetched_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(source, source_id, media_id)
            );

            CREATE INDEX episode_source_record_episode_id_idx ON episode_source_record(episode_id);
            CREATE INDEX episode_source_record_media_id_idx ON episode_source_record(media_id);
            CREATE INDEX episode_source_record_source_idx ON episode_source_record(source);
            CREATE INDEX episode_source_record_abs_num_idx ON episode_source_record(media_id, absolute_number);
            CREATE INDEX episode_source_record_season_ep_idx
                ON episode_source_record(media_id, season_number, episode_number);
            CREATE INDEX episode_media_id_idx ON episode(media_id);
            CREATE INDEX episode_absolute_number_idx ON episode(media_id, absolute_number);
            CREATE INDEX episode_season_episode_idx ON episode(media_id, season_number, episode_number);

            PRAGMA user_version = 6;
            COMMIT;
            "#,
        )?;
    }

    Ok(())
}
