# -*- coding: utf-8 -*-
"""SQLite 数据库层（Repository）。

设计原则：
- 所有数据库访问集中在本模块，UI 层不直接写 SQL。
- 单例连接 + 线程锁：Qt 信号回调可能在任意线程触发写操作，用锁保证一致。
- 外键开启，删除级联，保证资产库引用完整性。
- 时间戳为 POSIX float，排序稳定。
"""
from __future__ import annotations

import json
import os
import sqlite3
import threading
from pathlib import Path
from typing import Iterable

from .models import Asset, AssetType, Chapter, Series, Tag, now

SCHEMA_VERSION = 1

_SCHEMA = """
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS series (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT NOT NULL,
    author     TEXT DEFAULT '',
    status     TEXT NOT NULL DEFAULT 'ongoing',
    cover_path TEXT DEFAULT '',
    created_at REAL NOT NULL,
    updated_at REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS chapters (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    series_id  INTEGER NOT NULL REFERENCES series(id) ON DELETE CASCADE,
    volume     INTEGER,
    number     INTEGER,
    title      TEXT DEFAULT '',
    description TEXT DEFAULT '',
    created_at REAL NOT NULL,
    UNIQUE(series_id, volume, number)
);

CREATE TABLE IF NOT EXISTS assets (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    series_id   INTEGER REFERENCES series(id) ON DELETE SET NULL,
    chapter_id  INTEGER REFERENCES chapters(id) ON DELETE SET NULL,
    type        TEXT NOT NULL,
    title       TEXT NOT NULL,
    description TEXT DEFAULT '',
    file_path   TEXT DEFAULT '',
    content     TEXT DEFAULT '',
    page_index  INTEGER,
    tags        TEXT DEFAULT '[]',
    sort_order  INTEGER NOT NULL DEFAULT 0,
    created_at  REAL NOT NULL,
    updated_at  REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS tags (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT NOT NULL UNIQUE,
    color      TEXT DEFAULT '#4A90D9',
    created_at REAL NOT NULL
);

CREATE TABLE IF NOT EXISTS meta (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_assets_type    ON assets(type);
CREATE INDEX IF NOT EXISTS idx_assets_series  ON assets(series_id);
CREATE INDEX IF NOT EXISTS idx_assets_chapter ON assets(chapter_id);
CREATE INDEX IF NOT EXISTS idx_assets_updated ON assets(updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_chapters_series ON chapters(series_id, volume, number);
CREATE INDEX IF NOT EXISTS idx_series_status  ON series(status);
"""


class Database:
    """SQLite 仓储单例。

    用法::

        db = Database(db_path)          # 或 Database.get() 复用
        asset = db.create_asset(Asset(...))
        items = db.list_assets(asset_type='comic_page', limit=50, offset=0)
    """

    _instance: "Database | None" = None
    _instance_lock = threading.Lock()

    def __init__(self, db_path: str | os.PathLike | None = None) -> None:
        path = Path(db_path) if db_path else self._default_db_path()
        path.parent.mkdir(parents=True, exist_ok=True)
        self.db_path = str(path)
        self._lock = threading.RLock()
        self._conn = sqlite3.connect(self.db_path, check_same_thread=False)
        self._conn.row_factory = sqlite3.Row
        self._conn.execute("PRAGMA foreign_keys = ON")
        self._init_schema()

    # ------------------------------------------------------------------
    @classmethod
    def get(cls, db_path: str | os.PathLike | None = None) -> "Database":
        """获取（或创建）全局单例。"""
        with cls._instance_lock:
            if cls._instance is None:
                cls._instance = cls(db_path)
            return cls._instance

    @classmethod
    def reset(cls, db_path: str | os.PathLike | None = None) -> "Database":
        """测试用：丢弃旧实例，重建连接。"""
        with cls._instance_lock:
            if cls._instance is not None:
                try:
                    cls._instance._conn.close()
                except Exception:
                    pass
                cls._instance = None
            cls._instance = cls(db_path)
            return cls._instance

    @staticmethod
    def _default_db_path() -> Path:
        root = Path(__file__).resolve().parent.parent
        return root / "data" / "library.db"

    # ------------------------------------------------------------------
    # Schema
    # ------------------------------------------------------------------
    def _init_schema(self) -> None:
        with self._lock, self._conn:
            self._conn.executescript(_SCHEMA)
            self._conn.execute(
                "INSERT OR IGNORE INTO meta(key, value) VALUES ('schema_version', ?)",
                (str(SCHEMA_VERSION),),
            )

    def execute(self, sql: str, params: Iterable = ()) -> sqlite3.Cursor:
        with self._lock, self._conn:
            return self._conn.execute(sql, tuple(params))

    def query(self, sql: str, params: Iterable = ()) -> list[sqlite3.Row]:
        with self._lock:
            cur = self._conn.execute(sql, tuple(params))
            return cur.fetchall()

    def query_one(self, sql: str, params: Iterable = ()) -> sqlite3.Row | None:
        rows = self.query(sql, params)
        return rows[0] if rows else None

    # ==================================================================
    # Series
    # ==================================================================
    def create_series(self, s: Series) -> Series:
        ts = now()
        s.created_at = s.created_at or ts
        s.updated_at = ts
        with self._lock, self._conn:
            cur = self._conn.execute(
                "INSERT INTO series(name, author, status, cover_path, created_at, updated_at)"
                " VALUES (?,?,?,?,?,?)",
                (s.name, s.author, s.status, s.cover_path, s.created_at, s.updated_at),
            )
            s.id = cur.lastrowid
        return s

    def get_series(self, series_id: int) -> Series | None:
        row = self.query_one("SELECT * FROM series WHERE id = ?", (series_id,))
        return _row_to_series(row) if row else None

    def list_series(self) -> list[Series]:
        rows = self.query("SELECT * FROM series ORDER BY updated_at DESC")
        return [_row_to_series(r) for r in rows]

    def update_series(self, s: Series) -> Series:
        if s.id is None:
            return self.create_series(s)
        s.updated_at = now()
        with self._lock, self._conn:
            self._conn.execute(
                "UPDATE series SET name=?, author=?, status=?, cover_path=?, updated_at=? WHERE id=?",
                (s.name, s.author, s.status, s.cover_path, s.updated_at, s.id),
            )
        return s

    def delete_series(self, series_id: int) -> int:
        """删除系列；资产保留但解除关联（不丢数据）。返回受影响资产数。"""
        with self._lock, self._conn:
            cnt = self.query_one("SELECT COUNT(*) AS c FROM assets WHERE series_id=?", (series_id,))
            self._conn.execute("DELETE FROM series WHERE id=?", (series_id,))
        return int(cnt["c"]) if cnt else 0

    # ==================================================================
    # Chapters
    # ==================================================================
    def create_chapter(self, c: Chapter) -> Chapter:
        c.created_at = c.created_at or now()
        with self._lock, self._conn:
            cur = self._conn.execute(
                "INSERT INTO chapters(series_id, volume, number, title, description, created_at)"
                " VALUES (?,?,?,?,?,?)",
                (c.series_id, c.volume, c.number, c.title, c.description, c.created_at),
            )
            c.id = cur.lastrowid
        return c

    def list_chapters(self, series_id: int | None = None) -> list[Chapter]:
        if series_id is None:
            rows = self.query("SELECT * FROM chapters ORDER BY series_id, volume, number, title")
        else:
            rows = self.query(
                "SELECT * FROM chapters WHERE series_id=? ORDER BY volume, number, title",
                (series_id,),
            )
        return [_row_to_chapter(r) for r in rows]

    def update_chapter(self, c: Chapter) -> Chapter:
        if c.id is None:
            return self.create_chapter(c)
        with self._lock, self._conn:
            self._conn.execute(
                "UPDATE chapters SET series_id=?, volume=?, number=?, title=?, description=? WHERE id=?",
                (c.series_id, c.volume, c.number, c.title, c.description, c.id),
            )
        return c

    def delete_chapter(self, chapter_id: int) -> None:
        with self._lock, self._conn:
            self._conn.execute("DELETE FROM chapters WHERE id=?", (chapter_id,))

    # ==================================================================
    # Tags
    # ==================================================================
    def create_tag(self, name: str, color: str = "#4A90D9") -> Tag:
        name = name.strip()
        if not name:
            raise ValueError("标签名不能为空")
        t = Tag(name=name, color=color)
        with self._lock, self._conn:
            try:
                cur = self._conn.execute(
                    "INSERT INTO tags(name, color, created_at) VALUES (?,?,?)",
                    (name, color, now()),
                )
                t.id = cur.lastrowid
            except sqlite3.IntegrityError:
                existing = self.query_one("SELECT * FROM tags WHERE name=?", (name,))
                t = _row_to_tag(existing) if existing else t
        return t

    def list_tags(self) -> list[Tag]:
        rows = self.query("SELECT * FROM tags ORDER BY name")
        return [_row_to_tag(r) for r in rows]

    def tag_color(self, name: str) -> str:
        row = self.query_one("SELECT color FROM tags WHERE name=?", (name,))
        return row["color"] if row else "#4A90D9"

    def delete_tag(self, name: str) -> None:
        with self._lock, self._conn:
            self._conn.execute("DELETE FROM tags WHERE name=?", (name,))

    # ==================================================================
    # Assets
    # ==================================================================
    def create_asset(self, a: Asset) -> Asset:
        ts = now()
        a.created_at = a.created_at or ts
        a.updated_at = ts
        if a.title.strip() == "":
            raise ValueError("资产标题不能为空")
        with self._lock, self._conn:
            cur = self._conn.execute(
                "INSERT INTO assets(series_id, chapter_id, type, title, description, file_path,"
                " content, page_index, tags, sort_order, created_at, updated_at)"
                " VALUES (?,?,?,?,?,?,?,?,?,?,?,?)",
                (a.series_id, a.chapter_id, a.type.value, a.title, a.description, a.file_path,
                 a.content, a.page_index, json.dumps(a.tags, ensure_ascii=False),
                 a.sort_order, a.created_at, a.updated_at),
            )
            a.id = cur.lastrowid
        self._sync_tags(a)
        return a

    def get_asset(self, asset_id: int) -> Asset | None:
        row = self.query_one("SELECT * FROM assets WHERE id=?", (asset_id,))
        return _row_to_asset(row) if row else None

    def update_asset(self, a: Asset) -> Asset:
        if a.id is None:
            return self.create_asset(a)
        a.updated_at = now()
        with self._lock, self._conn:
            self._conn.execute(
                "UPDATE assets SET series_id=?, chapter_id=?, type=?, title=?, description=?,"
                " file_path=?, content=?, page_index=?, tags=?, sort_order=?, updated_at=? WHERE id=?",
                (a.series_id, a.chapter_id, a.type.value, a.title, a.description, a.file_path,
                 a.content, a.page_index, json.dumps(a.tags, ensure_ascii=False),
                 a.sort_order, a.updated_at, a.id),
            )
        self._sync_tags(a)
        return a

    def delete_asset(self, asset_id: int, delete_file: bool = False) -> None:
        a = self.get_asset(asset_id)
        with self._lock, self._conn:
            self._conn.execute("DELETE FROM assets WHERE id=?", (asset_id,))
        if delete_file and a and a.file_path:
            p = Path(a.file_path)
            if p.is_file():
                try:
                    p.unlink()
                except OSError:
                    pass

    def _sync_tags(self, a: Asset) -> None:
        """确保资产引用的标签在 tags 表中存在（懒注册）。"""
        for name in a.tags:
            name = name.strip()
            if not name:
                continue
            with self._lock:
                exists = self.query_one("SELECT 1 FROM tags WHERE name=?", (name,))
            if not exists:
                self.create_tag(name)

    def list_assets(
        self,
        asset_type: AssetType | str | None = None,
        series_id: int | None = None,
        chapter_id: int | None = None,
        tag: str | None = None,
        search: str | None = None,
        limit: int = 500,
        offset: int = 0,
        order_by: str = "updated_at",
        reverse: bool = True,
    ) -> list[Asset]:
        """带过滤的分页查询。

        order_by 仅允许白名单列，避免 SQL 注入。
        """
        sql = ["SELECT a.* FROM assets a"]
        conds: list[str] = []
        params: list[object] = []

        if asset_type is not None:
            t = asset_type if isinstance(asset_type, AssetType) else AssetType.from_string(asset_type)
            conds.append("a.type = ?")
            params.append(t.value)
        if series_id is not None:
            conds.append("a.series_id = ?")
            params.append(series_id)
        if chapter_id is not None:
            conds.append("a.chapter_id = ?")
            params.append(chapter_id)
        if tag:
            conds.append("EXISTS (SELECT 1 FROM json_each(a.tags) WHERE json_each.value = ?)")
            params.append(tag)
        if search:
            like = f"%{search}%"
            conds.append(
                "(a.title LIKE ? OR a.description LIKE ? OR a.content LIKE ?"
                " OR EXISTS (SELECT 1 FROM json_each(a.tags) WHERE json_each.value LIKE ?))"
            )
            params.extend([like, like, like, like])

        if conds:
            sql.append("WHERE " + " AND ".join(conds))

        allowed = {"id", "title", "type", "created_at", "updated_at", "sort_order",
                   "page_index", "series_id", "chapter_id"}
        col = order_by if order_by in allowed else "updated_at"
        sql.append(f"ORDER BY a.{col} {'DESC' if reverse else 'ASC'}")
        sql.append("LIMIT ? OFFSET ?")
        params.extend([limit, offset])

        rows = self.query(" ".join(sql), params)
        return [_row_to_asset(r) for r in rows]

    def count_assets(self, **filters: object) -> int:
        """与 list_assets 相同的过滤，只返回总数（分页用）。"""
        asset_type = filters.pop("asset_type", None)
        series_id = filters.pop("series_id", None)
        chapter_id = filters.pop("chapter_id", None)
        tag = filters.pop("tag", None)
        search = filters.pop("search", None)

        conds: list[str] = []
        params: list[object] = []
        if asset_type is not None:
            t = asset_type if isinstance(asset_type, AssetType) else AssetType.from_string(asset_type)
            conds.append("type = ?")
            params.append(t.value)
        if series_id is not None:
            conds.append("series_id = ?")
            params.append(series_id)
        if chapter_id is not None:
            conds.append("chapter_id = ?")
            params.append(chapter_id)
        if tag:
            conds.append("EXISTS (SELECT 1 FROM json_each(tags) WHERE json_each.value = ?)")
            params.append(tag)
        if search:
            like = f"%{search}%"
            conds.append("(title LIKE ? OR description LIKE ? OR content LIKE ?"
                         " OR EXISTS (SELECT 1 FROM json_each(tags) WHERE json_each.value LIKE ?))")
            params.extend([like, like, like, like])

        sql = "SELECT COUNT(*) AS c FROM assets"
        if conds:
            sql += " WHERE " + " AND ".join(conds)
        row = self.query_one(sql, params)
        return int(row["c"]) if row else 0

    # ==================================================================
    # 统计
    # ==================================================================
    def stats(self) -> dict[str, int]:
        """首页概览统计。"""
        out: dict[str, int] = {}
        row = self.query_one("SELECT COUNT(*) AS c FROM assets")
        out["assets"] = int(row["c"]) if row else 0
        row = self.query_one("SELECT COUNT(*) AS c FROM series")
        out["series"] = int(row["c"]) if row else 0
        row = self.query_one("SELECT COUNT(*) AS c FROM chapters")
        out["chapters"] = int(row["c"]) if row else 0
        row = self.query_one("SELECT COUNT(*) AS c FROM tags")
        out["tags"] = int(row["c"]) if row else 0
        for t in AssetType:
            r = self.query_one("SELECT COUNT(*) AS c FROM assets WHERE type=?", (t.value,))
            out[t.value] = int(r["c"]) if r else 0
        return out

    # ==================================================================
    # 备份 / 恢复 / 导出
    # ==================================================================
    def backup(self, backup_dir: str | os.PathLike) -> str:
        """复制数据库文件为 .bak（WAL 模式下需先 checkpoint）。"""
        d = Path(backup_dir)
        d.mkdir(parents=True, exist_ok=True)
        ts = now()
        dest = d / f"library_{ts:.0f}.db"
        with self._lock, self._conn:
            self._conn.execute("PRAGMA wal_checkpoint(FULL)")
            # 用 sqlite 原生备份 API，保证一致性
            tmp = d / (dest.name + ".tmp")
            src = sqlite3.connect(self.db_path)
            try:
                src.backup(sqlite3.connect(str(tmp)))
            finally:
                src.close()
        tmp.replace(dest)
        return str(dest)

    def export_json(self, path: str | os.PathLike) -> str:
        """全库导出为可读 JSON（含系列/章节/资产/标签），便于迁移。"""
        out = {
            "app": "comic-asset-manager",
            "schema_version": SCHEMA_VERSION,
            "exported_at": now(),
            "series": [s.to_dict() for s in self.list_series()],
            "chapters": [c.to_dict() for c in self.list_chapters()],
            "tags": [t.to_dict() for t in self.list_tags()],
            "assets": [a.to_dict() for a in self.list_assets(limit=10**7, order_by="id", reverse=False)],
        }
        p = Path(path)
        p.parent.mkdir(parents=True, exist_ok=True)
        with open(p, "w", encoding="utf-8") as f:
            json.dump(out, f, ensure_ascii=False, indent=2)
        return str(p)

    def close(self) -> None:
        with self._lock:
            self._conn.close()


# ---------------------------------------------------------------------------
# Row <-> dataclass 转换
# ---------------------------------------------------------------------------
def _row_to_series(r: sqlite3.Row) -> Series:
    return Series(id=r["id"], name=r["name"], author=r["author"] or "",
                  status=r["status"], cover_path=r["cover_path"] or "",
                  created_at=r["created_at"], updated_at=r["updated_at"])


def _row_to_chapter(r: sqlite3.Row) -> Chapter:
    return Chapter(id=r["id"], series_id=r["series_id"], volume=r["volume"],
                   number=r["number"], title=r["title"] or "",
                   description=r["description"] or "", created_at=r["created_at"])


def _row_to_tag(r: sqlite3.Row) -> Tag:
    return Tag(id=r["id"], name=r["name"], color=r["color"] or "#4A90D9",
               created_at=r["created_at"])


def _row_to_asset(r: sqlite3.Row) -> Asset:
    try:
        tags = json.loads(r["tags"] or "[]")
    except (ValueError, TypeError):
        tags = []
    return Asset(
        id=r["id"], series_id=r["series_id"], chapter_id=r["chapter_id"],
        type=AssetType.from_string(r["type"]), title=r["title"],
        description=r["description"] or "", file_path=r["file_path"] or "",
        content=r["content"] or "", page_index=r["page_index"],
        tags=tags if isinstance(tags, list) else [tags],
        sort_order=r["sort_order"], created_at=r["created_at"], updated_at=r["updated_at"],
    )


def row_to_asset(r: sqlite3.Row) -> Asset:  # 对外暴露（导入器用）
    return _row_to_asset(r)