from __future__ import annotations

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

from .models import NormalizedEpisode, NormalizedMovie, NormalizedServer
from .utils import strip_season_from_title


class MovieDatabase:
    def __init__(self, db_path: str):
        Path(db_path).parent.mkdir(parents=True, exist_ok=True)
        self.conn = sqlite3.connect(db_path)
        self.conn.row_factory = sqlite3.Row
        self.init_schema()

    def close(self) -> None:
        self.conn.close()

    def init_schema(self) -> None:
        self.conn.executescript(
            """
            PRAGMA foreign_keys = ON;
            CREATE TABLE IF NOT EXISTS movies (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                merge_key TEXT NOT NULL UNIQUE,
                tmdb_id TEXT,
                media_type TEXT NOT NULL,
                title TEXT NOT NULL,
                original_title TEXT,
                year TEXT,
                imdb_id TEXT,
                poster_url TEXT,
                backdrop_url TEXT,
                runtime TEXT,
                content TEXT,
                genres_json TEXT,
                countries_json TEXT,
                actors_json TEXT,
                directors_json TEXT,
                updated_at TEXT DEFAULT CURRENT_TIMESTAMP
            );
            CREATE TABLE IF NOT EXISTS movie_sources (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                movie_id INTEGER REFERENCES movies(id) ON DELETE CASCADE,
                source TEXT NOT NULL,
                source_slug TEXT NOT NULL,
                raw_json TEXT NOT NULL,
                crawl_status TEXT NOT NULL DEFAULT 'pending',
                retry_count INTEGER NOT NULL DEFAULT 0,
                last_error TEXT,
                updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(source, source_slug)
            );
            CREATE TABLE IF NOT EXISTS servers (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                movie_id INTEGER NOT NULL REFERENCES movies(id) ON DELETE CASCADE,
                source TEXT NOT NULL,
                name TEXT NOT NULL,
                priority INTEGER NOT NULL DEFAULT 0,
                raw_json TEXT NOT NULL DEFAULT '{}',
                updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(movie_id, source, name)
            );
            CREATE TABLE IF NOT EXISTS episodes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                movie_id INTEGER NOT NULL REFERENCES movies(id) ON DELETE CASCADE,
                server_id INTEGER REFERENCES servers(id) ON DELETE SET NULL,
                source TEXT NOT NULL,
                source_episode_id TEXT,
                title TEXT,
                filename TEXT NOT NULL,
                season INTEGER NOT NULL DEFAULT 1,
                episode INTEGER NOT NULL DEFAULT 1,
                origin_m3u8_url TEXT NOT NULL,
                cleaned_m3u8_path TEXT,
                strm_path TEXT,
                access_key TEXT NOT NULL UNIQUE,
                crawl_status TEXT NOT NULL DEFAULT 'pending',
                retry_count INTEGER NOT NULL DEFAULT 0,
                last_error TEXT,
                raw_json TEXT NOT NULL DEFAULT '{}',
                updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(movie_id, source, filename, origin_m3u8_url)
            );
            CREATE INDEX IF NOT EXISTS idx_episodes_identity
                ON episodes(movie_id, source, filename, season, episode);
            """
        )
        self.conn.commit()

    def upsert_movie(self, movie: NormalizedMovie) -> int:
        payload = (
            movie.merge_key,
            movie.tmdb_id,
            movie.media_type,
            movie.title,
            movie.original_title,
            movie.year,
            movie.imdb_id,
            movie.poster_url,
            movie.backdrop_url,
            movie.runtime,
            movie.content,
            json.dumps(movie.genres, ensure_ascii=False),
            json.dumps(movie.countries, ensure_ascii=False),
            json.dumps(movie.actors, ensure_ascii=False),
            json.dumps(movie.directors, ensure_ascii=False),
        )
        self.conn.execute(
            """
            INSERT INTO movies (
                merge_key, tmdb_id, media_type, title, original_title, year, imdb_id,
                poster_url, backdrop_url, runtime, content, genres_json, countries_json,
                actors_json, directors_json
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(merge_key) DO UPDATE SET
                title=CASE WHEN excluded.tmdb_id != '' THEN excluded.title ELSE COALESCE(NULLIF(movies.title, ''), excluded.title) END,
                original_title=CASE WHEN excluded.tmdb_id != '' THEN excluded.original_title ELSE COALESCE(NULLIF(movies.original_title, ''), excluded.original_title) END,
                year=CASE WHEN excluded.tmdb_id != '' THEN excluded.year ELSE COALESCE(NULLIF(movies.year, ''), excluded.year) END,
                imdb_id=COALESCE(NULLIF(excluded.imdb_id, ''), movies.imdb_id),
                poster_url=CASE WHEN excluded.tmdb_id != '' THEN excluded.poster_url ELSE COALESCE(NULLIF(movies.poster_url, ''), excluded.poster_url) END,
                backdrop_url=CASE WHEN excluded.tmdb_id != '' THEN excluded.backdrop_url ELSE COALESCE(NULLIF(movies.backdrop_url, ''), excluded.backdrop_url) END,
                runtime=CASE WHEN excluded.tmdb_id != '' THEN excluded.runtime ELSE COALESCE(NULLIF(movies.runtime, ''), excluded.runtime) END,
                content=CASE WHEN excluded.tmdb_id != '' THEN excluded.content ELSE COALESCE(NULLIF(movies.content, ''), excluded.content) END,
                genres_json=CASE WHEN excluded.tmdb_id != '' THEN excluded.genres_json ELSE COALESCE(NULLIF(movies.genres_json, '[]'), excluded.genres_json) END,
                countries_json=COALESCE(NULLIF(movies.countries_json, '[]'), excluded.countries_json),
                actors_json=COALESCE(NULLIF(movies.actors_json, '[]'), excluded.actors_json),
                directors_json=COALESCE(NULLIF(movies.directors_json, '[]'), excluded.directors_json),
                updated_at=CURRENT_TIMESTAMP
            """,
            payload,
        )
        row = self.conn.execute("SELECT id FROM movies WHERE merge_key = ?", (movie.merge_key,)).fetchone()
        movie_id = int(row["id"])
        current = self.conn.execute("SELECT title FROM movies WHERE id = ?", (movie_id,)).fetchone()
        if current:
            clean_title = strip_season_from_title(current["title"] or "")
            if clean_title != current["title"]:
                self.conn.execute("UPDATE movies SET title = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?", (movie.title, movie_id))
        self.conn.execute(
            """
            INSERT INTO movie_sources (movie_id, source, source_slug, raw_json, crawl_status)
            VALUES (?, ?, ?, ?, 'success')
            ON CONFLICT(source, source_slug) DO UPDATE SET
                movie_id=excluded.movie_id,
                raw_json=excluded.raw_json,
                crawl_status='success',
                last_error=NULL,
                updated_at=CURRENT_TIMESTAMP
            """,
            (movie_id, movie.source, movie.source_slug, json.dumps(movie.raw, ensure_ascii=False)),
        )
        self.conn.commit()
        return movie_id

    def apply_canonical_movie(self, movie_id: int, movie: NormalizedMovie) -> NormalizedMovie:
        row = self.conn.execute("SELECT * FROM movies WHERE id = ?", (movie_id,)).fetchone()
        if not row:
            return movie
        movie.title = row["title"] or movie.title
        movie.original_title = row["original_title"] or movie.original_title
        movie.year = row["year"] or movie.year
        movie.media_type = row["media_type"] or movie.media_type
        movie.tmdb_id = row["tmdb_id"] or movie.tmdb_id
        movie.imdb_id = row["imdb_id"] or movie.imdb_id
        movie.poster_url = row["poster_url"] or movie.poster_url
        movie.backdrop_url = row["backdrop_url"] or movie.backdrop_url
        movie.runtime = row["runtime"] or movie.runtime
        movie.content = row["content"] or movie.content
        movie.genres = json.loads(row["genres_json"] or "[]")
        movie.countries = json.loads(row["countries_json"] or "[]")
        movie.actors = json.loads(row["actors_json"] or "[]")
        movie.directors = json.loads(row["directors_json"] or "[]")
        return movie

    def mark_source_failed(self, source: str, slug: str, error: str) -> None:
        self.conn.execute(
            """
            INSERT INTO movie_sources (movie_id, source, source_slug, raw_json, crawl_status, retry_count, last_error)
            VALUES (NULL, ?, ?, '{}', 'failed', 1, ?)
            ON CONFLICT(source, source_slug) DO UPDATE SET
                crawl_status='failed',
                retry_count=retry_count + 1,
                last_error=excluded.last_error,
                updated_at=CURRENT_TIMESTAMP
            """,
            (source, slug, error),
        )
        self.conn.commit()

    def upsert_server(self, movie_id: int, source: str, server: NormalizedServer) -> int:
        self.conn.execute(
            """
            INSERT INTO servers (movie_id, source, name, priority, raw_json)
            VALUES (?, ?, ?, ?, ?)
            ON CONFLICT(movie_id, source, name) DO UPDATE SET
                priority=excluded.priority,
                raw_json=excluded.raw_json,
                updated_at=CURRENT_TIMESTAMP
            """,
            (movie_id, source, server.name, server.priority, json.dumps(server.raw, ensure_ascii=False)),
        )
        row = self.conn.execute(
            "SELECT id FROM servers WHERE movie_id = ? AND source = ? AND name = ?",
            (movie_id, source, server.name),
        ).fetchone()
        self.conn.commit()
        return int(row["id"])

    def upsert_episode(
        self,
        movie_id: int,
        server_id: int,
        source: str,
        episode: NormalizedEpisode,
        status: str = "pending",
    ) -> int:
        existing = self.conn.execute(
            """
            SELECT id, access_key, origin_m3u8_url, crawl_status FROM episodes
            WHERE movie_id = ? AND source = ? AND filename = ? AND season = ? AND episode = ?
            ORDER BY updated_at DESC, id DESC
            LIMIT 1
            """,
            (movie_id, source, episode.filename, episode.season, episode.episode),
        ).fetchone()
        if existing:
            link_changed = existing["origin_m3u8_url"] != episode.m3u8_url
            next_status = "pending" if link_changed else existing["crawl_status"]
            self.conn.execute(
                """
                UPDATE episodes
                SET server_id = ?, source_episode_id = ?, title = ?, season = ?, episode = ?,
                    origin_m3u8_url = ?, crawl_status = ?, raw_json = ?,
                    last_error = CASE WHEN ? THEN NULL ELSE last_error END,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
                """,
                (
                    server_id,
                    episode.source_episode_id,
                    episode.title,
                    episode.season,
                    episode.episode,
                    episode.m3u8_url,
                    next_status,
                    json.dumps(episode.raw, ensure_ascii=False),
                    1 if link_changed else 0,
                    existing["id"],
                ),
            )
            self.conn.commit()
            return int(existing["id"])

        access_key = str(uuid4())
        self.conn.execute(
            """
            INSERT INTO episodes (
                movie_id, server_id, source, source_episode_id, title, filename, season,
                episode, origin_m3u8_url, access_key, crawl_status, raw_json
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(movie_id, source, filename, origin_m3u8_url) DO UPDATE SET
                server_id=excluded.server_id,
                source_episode_id=excluded.source_episode_id,
                title=excluded.title,
                season=excluded.season,
                episode=excluded.episode,
                crawl_status=excluded.crawl_status,
                raw_json=excluded.raw_json,
                updated_at=CURRENT_TIMESTAMP
            """,
            (
                movie_id,
                server_id,
                source,
                episode.source_episode_id,
                episode.title,
                episode.filename,
                episode.season,
                episode.episode,
                episode.m3u8_url,
                access_key,
                status,
                json.dumps(episode.raw, ensure_ascii=False),
            ),
        )
        row = self.conn.execute(
            """
            SELECT id FROM episodes
            WHERE movie_id = ? AND source = ? AND filename = ? AND origin_m3u8_url = ?
            """,
            (movie_id, source, episode.filename, episode.m3u8_url),
        ).fetchone()
        self.conn.commit()
        return int(row["id"])

    def update_episode_files(
        self,
        episode_id: int,
        cleaned_m3u8_path: str | None,
        strm_path: str | None,
        status: str,
        error: str | None = None,
    ) -> None:
        self.conn.execute(
            """
            UPDATE episodes
            SET cleaned_m3u8_path = ?, strm_path = ?, crawl_status = ?,
                last_error = ?, retry_count = retry_count + CASE WHEN ? = 'failed' THEN 1 ELSE 0 END,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
            """,
            (cleaned_m3u8_path, strm_path, status, error, status, episode_id),
        )
        self.conn.commit()

    def get_episode(self, episode_id: int) -> sqlite3.Row:
        row = self.conn.execute("SELECT * FROM episodes WHERE id = ?", (episode_id,)).fetchone()
        if not row:
            raise KeyError(f"Episode not found: {episode_id}")
        return row