from __future__ import annotations

import json
import sqlite3
from pathlib import Path
from typing import Any

from app.config import DATABASE_PATH, DRAMAS_JSON_PATH


def _as_str(v: Any) -> str | None:
    if v is None:
        return None
    return str(v)


def _extract_tables(raw: list) -> dict[str, list[dict]]:
    out: dict[str, list[dict]] = {}
    for item in raw:
        if isinstance(item, dict) and item.get("type") == "table":
            name = item.get("name")
            if isinstance(name, str):
                out[name] = list(item.get("data") or [])
    return out


def _connect() -> sqlite3.Connection:
    DATABASE_PATH.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(str(DATABASE_PATH), check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA journal_mode = WAL")
    return conn


def _create_schema(conn: sqlite3.Connection) -> None:
    conn.executescript(
        """
        CREATE TABLE IF NOT EXISTS dramas (
            id TEXT PRIMARY KEY,
            book_id TEXT,
            tmdb_id TEXT,
            title TEXT,
            description TEXT,
            cover_img TEXT,
            category TEXT,
            platform TEXT,
            type TEXT,
            language TEXT,
            created_at TEXT
        );

        CREATE TABLE IF NOT EXISTS episodes (
            id TEXT PRIMARY KEY,
            drama_id TEXT NOT NULL,
            chapter_id TEXT,
            chapter_index TEXT,
            chapter_name TEXT,
            season_number TEXT,
            episode_number TEXT,
            video_url TEXT,
            chapter_img TEXT,
            created_at TEXT,
            FOREIGN KEY (drama_id) REFERENCES dramas(id)
        );

        CREATE TABLE IF NOT EXISTS episode_sources (
            id TEXT PRIMARY KEY,
            episode_id TEXT NOT NULL,
            quality TEXT,
            server TEXT,
            video_url TEXT,
            license_key TEXT,
            created_at TEXT,
            FOREIGN KEY (episode_id) REFERENCES episodes(id)
        );

        CREATE INDEX IF NOT EXISTS idx_episodes_drama ON episodes(drama_id);
        CREATE INDEX IF NOT EXISTS idx_episodes_drama_chapter ON episodes(drama_id, chapter_index);
        CREATE INDEX IF NOT EXISTS idx_sources_episode ON episode_sources(episode_id);

        CREATE TABLE IF NOT EXISTS settings (
            key_name TEXT PRIMARY KEY,
            key_value TEXT
        );
        """
    )
    conn.commit()


def _row_counts(conn: sqlite3.Connection) -> tuple[int, int, int]:
    d = conn.execute("SELECT COUNT(*) FROM dramas").fetchone()[0]
    e = conn.execute("SELECT COUNT(*) FROM episodes").fetchone()[0]
    s = conn.execute("SELECT COUNT(*) FROM episode_sources").fetchone()[0]
    return int(d), int(e), int(s)


def import_from_json(conn: sqlite3.Connection, path: Path) -> None:
    if not path.is_file():
        raise FileNotFoundError(f"Dramas JSON not found: {path}")

    with path.open("r", encoding="utf-8") as f:
        raw = json.load(f)

    tables = _extract_tables(raw)
    dramas = tables.get("dramas") or []
    episodes = tables.get("episodes") or []
    episode_sources = tables.get("episode_sources") or []
    settings_rows = tables.get("settings") or []

    conn.execute("PRAGMA foreign_keys = OFF")
    conn.execute("DELETE FROM episode_sources")
    conn.execute("DELETE FROM episodes")
    conn.execute("DELETE FROM dramas")
    conn.execute("DELETE FROM settings")
    conn.commit()

    drama_rows = []
    for r in dramas:
        drama_rows.append(
            (
                _as_str(r.get("id")),
                _as_str(r.get("book_id")),
                _as_str(r.get("tmdb_id")),
                r.get("title"),
                r.get("description"),
                r.get("cover_img"),
                _as_str(r.get("category")),
                _as_str(r.get("platform")),
                _as_str(r.get("type")),
                _as_str(r.get("language")),
                _as_str(r.get("created_at")),
            )
        )
    conn.executemany(
        """
        INSERT INTO dramas (
            id, book_id, tmdb_id, title, description, cover_img,
            category, platform, type, language, created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """,
        drama_rows,
    )

    episode_rows = []
    for r in episodes:
        episode_rows.append(
            (
                _as_str(r.get("id")),
                _as_str(r.get("drama_id")),
                _as_str(r.get("chapter_id")),
                _as_str(r.get("chapter_index")),
                r.get("chapter_name"),
                _as_str(r.get("season_number")),
                _as_str(r.get("episode_number")),
                r.get("video_url"),
                r.get("chapter_img"),
                _as_str(r.get("created_at")),
            )
        )
    conn.executemany(
        """
        INSERT INTO episodes (
            id, drama_id, chapter_id, chapter_index, chapter_name,
            season_number, episode_number, video_url, chapter_img, created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """,
        episode_rows,
    )

    source_rows = []
    for r in episode_sources:
        source_rows.append(
            (
                _as_str(r.get("id")),
                _as_str(r.get("episode_id")),
                _as_str(r.get("quality")),
                _as_str(r.get("server")),
                r.get("video_url"),
                r.get("license_key"),
                _as_str(r.get("created_at")),
            )
        )
    conn.executemany(
        """
        INSERT INTO episode_sources (
            id, episode_id, quality, server, video_url, license_key, created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?)
        """,
        source_rows,
    )

    setting_pairs = []
    for r in settings_rows:
        setting_pairs.append((_as_str(r.get("key_name")), _as_str(r.get("key_value"))))
    if setting_pairs:
        conn.executemany(
            "INSERT INTO settings (key_name, key_value) VALUES (?, ?)",
            setting_pairs,
        )

    conn.commit()
    conn.execute("PRAGMA foreign_keys = ON")


def init_database(force_reload: bool = False) -> None:
    conn = _connect()
    try:
        _create_schema(conn)
        d, e, s = _row_counts(conn)
        needs_import = force_reload or d == 0
        if needs_import:
            import_from_json(conn, DRAMAS_JSON_PATH)
            d, e, s = _row_counts(conn)
        print(
            f"[catalog] dramas={d} episodes={e} episode_sources={s} "
            f"db={DATABASE_PATH} json={DRAMAS_JSON_PATH}"
        )
    finally:
        conn.close()


def get_connection() -> sqlite3.Connection:
    return _connect()