"""SQLite 数据库管理

管理漫画、章节、页面的下载状态，支持断点续传和去重。
"""

import sqlite3
from pathlib import Path

SCHEMA_SQL = """\
CREATE TABLE IF NOT EXISTS manga (
    id          TEXT NOT NULL,
    source      TEXT NOT NULL,
    title       TEXT NOT NULL,
    alt_title   TEXT,
    author      TEXT,
    cover_url   TEXT,
    description TEXT,
    tags        TEXT,
    url         TEXT NOT NULL,
    created_at  TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at  TEXT NOT NULL DEFAULT (datetime('now')),
    PRIMARY KEY (id, source)
);

CREATE TABLE IF NOT EXISTS chapter (
    id            TEXT NOT NULL,
    manga_id      TEXT NOT NULL,
    manga_source  TEXT NOT NULL,
    title         TEXT NOT NULL,
    chapter_number REAL NOT NULL,
    chapter_type  TEXT NOT NULL DEFAULT 'chapter',
    url           TEXT NOT NULL,
    page_count    INTEGER,
    status        TEXT NOT NULL DEFAULT 'pending',
    download_path TEXT,
    created_at    TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at    TEXT NOT NULL DEFAULT (datetime('now')),
    PRIMARY KEY (id, manga_source),
    FOREIGN KEY (manga_id, manga_source) REFERENCES manga(id, source)
);

CREATE INDEX IF NOT EXISTS idx_chapter_manga ON chapter(manga_id, manga_source);
CREATE INDEX IF NOT EXISTS idx_chapter_status ON chapter(status);

CREATE TABLE IF NOT EXISTS page (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    chapter_id     TEXT NOT NULL,
    chapter_source TEXT NOT NULL,
    page_number    INTEGER NOT NULL,
    url            TEXT NOT NULL,
    local_path     TEXT,
    status         TEXT NOT NULL DEFAULT 'pending',
    created_at     TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(chapter_id, chapter_source, page_number),
    FOREIGN KEY (chapter_id, chapter_source) REFERENCES chapter(id, manga_source)
);

CREATE INDEX IF NOT EXISTS idx_page_chapter ON page(chapter_id, chapter_source);

CREATE TABLE IF NOT EXISTS subscription (
    manga_id       TEXT NOT NULL,
    source         TEXT NOT NULL,
    subscribed_at  TEXT NOT NULL DEFAULT (datetime('now')),
    last_checked   TEXT,
    last_chapter_id TEXT,
    auto_push      INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (manga_id, source),
    FOREIGN KEY (manga_id, source) REFERENCES manga(id, source)
);
"""


class Database:
    """SQLite 状态存储。同步操作，在 async 下载批次之间调用。"""

    def __init__(self, db_path: str | Path = "kobo_manga.db"):
        self.db_path = Path(db_path)
        self.conn: sqlite3.Connection | None = None

    def initialize(self) -> None:
        """创建连接并初始化表结构。"""
        self.conn = sqlite3.connect(str(self.db_path))
        self.conn.row_factory = sqlite3.Row
        self.conn.execute("PRAGMA journal_mode=WAL")
        self.conn.execute("PRAGMA foreign_keys=ON")
        self.conn.executescript(SCHEMA_SQL)
        self._migrate()
        self.conn.commit()

    def _migrate(self) -> None:
        """增量 schema 迁移。"""
        cursor = self.conn.execute("PRAGMA table_info(chapter)")
        columns = {row[1] for row in cursor.fetchall()}
        if "chapter_type" not in columns:
            self.conn.execute(
                "ALTER TABLE chapter ADD COLUMN chapter_type TEXT NOT NULL DEFAULT 'chapter'"
            )

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

    def __enter__(self) -> "Database":
        self.initialize()
        return self

    def __exit__(self, *args) -> None:
        self.close()