import logging
import re
import time

from db import direct_connect

logger = logging.getLogger(__name__)

_DEFAULT_REGEX = [
    r"^\s*[\*\-]{3,}\s*$",
    r"^\s*[·•◦‣⁃]\s*[·•◦‣⁃]\s*[·•◦‣⁃]\s*$",
    r"^\s*~{2,}\s*$",
    r"^\s*={3,}\s*$",
    r"^\s*#{3,}\s*$",
    r"^\s*[oO0]{1,3}\s*$",
    r"^\s*[-–—]\s*[oO0]\s*[-–—]\s*$",
    r"^\s*[<>]+\s*[·•*]\s*[<>]+\s*$",
]
_DEFAULT_CSS = [
    "hr",
    "separator",
    "section-break",
    "divider",
    "break",
    "chapterbreak",
    "scene-break",
    "scenebreak",
]


def _exec(sql: str, conn) -> None:
    with conn.cursor() as cur:
        cur.execute(sql)


def _run_once(conn, name: str, fn) -> bool:
    """Run fn(conn) only if name has not been recorded in schema_migrations.
    Returns True if the migration was executed, False if it was skipped."""
    with conn.cursor() as cur:
        cur.execute("SELECT 1 FROM schema_migrations WHERE name = %s", (name,))
        if cur.fetchone():
            logger.info("%s — skipped (already applied)", name)
            return False
    t0 = time.time()
    fn(conn)
    with conn.cursor() as cur:
        cur.execute(
            "INSERT INTO schema_migrations (name) VALUES (%s) ON CONFLICT DO NOTHING",
            (name,),
        )
    conn.commit()
    logger.info("%s — executed in %dms", name, int((time.time() - t0) * 1000))
    return True


def migrate_create_library(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS library (
            id                  SERIAL PRIMARY KEY,
            filename            VARCHAR(600) UNIQUE NOT NULL,
            media_type          VARCHAR(10)  NOT NULL DEFAULT 'epub',
            title               VARCHAR(500),
            author              VARCHAR(255),
            publisher           VARCHAR(255),
            series              VARCHAR(500),
            series_index        INTEGER      DEFAULT 0,
            publication_status  VARCHAR(100),
            has_cover           BOOLEAN      DEFAULT FALSE,
            description         TEXT         DEFAULT '',
            source_url          VARCHAR(1000),
            publish_date        DATE,
            archived            BOOLEAN      DEFAULT FALSE,
            want_to_read        BOOLEAN      DEFAULT FALSE,
            needs_review        BOOLEAN      DEFAULT FALSE,
            created_at          TIMESTAMP    DEFAULT NOW(),
            updated_at          TIMESTAMP    DEFAULT NOW()
        )
        """,
        conn,
    )


def migrate_create_book_tags(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS book_tags (
            id        SERIAL PRIMARY KEY,
            filename  VARCHAR(600) NOT NULL REFERENCES library(filename) ON DELETE CASCADE,
            tag       VARCHAR(255) NOT NULL,
            tag_type  VARCHAR(20)  NOT NULL,
            UNIQUE (filename, tag, tag_type)
        )
        """,
        conn,
    )
    _exec("CREATE INDEX IF NOT EXISTS idx_book_tags_filename ON book_tags (filename)", conn)


def migrate_create_reading_progress(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS reading_progress (
            id          SERIAL PRIMARY KEY,
            filename    VARCHAR(600) UNIQUE NOT NULL REFERENCES library(filename) ON DELETE CASCADE,
            cfi         TEXT,
            page        INTEGER,
            progress    INTEGER DEFAULT 0,
            updated_at  TIMESTAMP DEFAULT NOW()
        )
        """,
        conn,
    )


def migrate_create_reading_sessions(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS reading_sessions (
            id        SERIAL PRIMARY KEY,
            filename  VARCHAR(600) NOT NULL REFERENCES library(filename) ON DELETE CASCADE,
            read_at   TIMESTAMP DEFAULT NOW()
        )
        """,
        conn,
    )
    _exec("CREATE INDEX IF NOT EXISTS idx_reading_sessions_filename ON reading_sessions (filename)", conn)


def migrate_create_library_cover_cache(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS library_cover_cache (
            filename    VARCHAR(600) PRIMARY KEY REFERENCES library(filename) ON DELETE CASCADE,
            mime_type   VARCHAR(100) NOT NULL,
            thumb_webp  BYTEA        NOT NULL,
            updated_at  TIMESTAMP DEFAULT NOW()
        )
        """,
        conn,
    )


def migrate_create_credentials(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS credentials (
            id          SERIAL PRIMARY KEY,
            site        VARCHAR(255) UNIQUE NOT NULL,
            username    TEXT NOT NULL,
            password    TEXT NOT NULL,
            updated_at  TIMESTAMP DEFAULT NOW()
        )
        """,
        conn,
    )
    _exec("ALTER TABLE credentials ALTER COLUMN username TYPE TEXT", conn)
    _exec("ALTER TABLE credentials ALTER COLUMN password TYPE TEXT", conn)


def migrate_create_break_patterns(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS break_patterns (
            id           SERIAL PRIMARY KEY,
            pattern_type VARCHAR(20)  NOT NULL,
            pattern      TEXT         NOT NULL,
            enabled      BOOLEAN      DEFAULT TRUE,
            is_default   BOOLEAN      DEFAULT FALSE,
            created_at   TIMESTAMP    DEFAULT NOW(),
            UNIQUE (pattern_type, pattern)
        )
        """,
        conn,
    )


def migrate_seed_break_patterns(conn) -> None:
    with conn.cursor() as cur:
        for pat in _DEFAULT_REGEX:
            re.compile(pat)
            cur.execute(
                """
                INSERT INTO break_patterns (pattern_type, pattern, is_default)
                VALUES ('regex', %s, TRUE)
                ON CONFLICT (pattern_type, pattern) DO NOTHING
                """,
                (pat,),
            )
        for pat in _DEFAULT_CSS:
            cur.execute(
                """
                INSERT INTO break_patterns (pattern_type, pattern, is_default)
                VALUES ('css_class', %s, TRUE)
                ON CONFLICT (pattern_type, pattern) DO NOTHING
                """,
                (pat,),
            )


def migrate_create_backup_log(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS backup_log (
            id           SERIAL PRIMARY KEY,
            status       VARCHAR(20)  NOT NULL,
            files_count  INTEGER,
            size_bytes   BIGINT,
            error_msg    TEXT,
            started_at   TIMESTAMP    DEFAULT NOW(),
            finished_at  TIMESTAMP
        )
        """,
        conn,
    )


def migrate_backup_log_scanned_files(conn) -> None:
    _exec("ALTER TABLE backup_log ADD COLUMN IF NOT EXISTS scanned_files INTEGER", conn)


def migrate_add_rating(conn) -> None:
    _exec("ALTER TABLE library ADD COLUMN IF NOT EXISTS rating SMALLINT NOT NULL DEFAULT 0", conn)


def migrate_create_bookmarks(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS bookmarks (
            id            SERIAL PRIMARY KEY,
            filename      VARCHAR(600) NOT NULL REFERENCES library(filename) ON DELETE CASCADE,
            chapter_index INTEGER      NOT NULL DEFAULT 0,
            scroll_frac   REAL         NOT NULL DEFAULT 0,
            chapter_title VARCHAR(500) NOT NULL DEFAULT '',
            note          TEXT         NOT NULL DEFAULT '',
            created_at    TIMESTAMPTZ  DEFAULT NOW()
        )
        """,
        conn,
    )
    _exec("CREATE INDEX IF NOT EXISTS idx_bookmarks_filename ON bookmarks (filename)", conn)


def migrate_remove_cover_missing_tag(conn) -> None:
    _exec("DELETE FROM book_tags WHERE tag = 'Cover Missing' AND tag_type = 'tag'", conn)


def migrate_create_perf_indexes(conn) -> None:
    _exec(
        """
        CREATE INDEX IF NOT EXISTS idx_library_sort_coalesce
        ON library (
            (COALESCE(publisher, '')),
            (COALESCE(author, '')),
            (COALESCE(series, '')),
            series_index,
            (COALESCE(title, ''))
        )
        """,
        conn,
    )
    _exec("CREATE INDEX IF NOT EXISTS idx_library_needs_review ON library (needs_review)", conn)
    _exec("CREATE INDEX IF NOT EXISTS idx_library_archived ON library (archived)", conn)
    _exec(
        """
        CREATE INDEX IF NOT EXISTS idx_reading_sessions_filename_readat
        ON reading_sessions (filename, read_at DESC)
        """,
        conn,
    )
    _exec(
        """
        CREATE INDEX IF NOT EXISTS idx_book_tags_filename_tag
        ON book_tags (filename, tag)
        """,
        conn,
    )


def migrate_series_suffix(conn) -> None:
    _exec(
        """
        ALTER TABLE library
        ADD COLUMN IF NOT EXISTS series_suffix VARCHAR(10) NOT NULL DEFAULT ''
        """,
        conn,
    )


def migrate_create_builder_drafts(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS builder_drafts (
            id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            title       VARCHAR(500)  NOT NULL,
            author      VARCHAR(255)  NOT NULL,
            publisher   VARCHAR(255)  NOT NULL DEFAULT '',
            source_url  VARCHAR(1000) NOT NULL DEFAULT '',
            chapters    JSONB         NOT NULL DEFAULT '[]',
            created_at  TIMESTAMP     DEFAULT NOW(),
            updated_at  TIMESTAMP     DEFAULT NOW()
        )
        """,
        conn,
    )


def migrate_create_authors(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS authors (
            id          SERIAL PRIMARY KEY,
            name        VARCHAR(255) UNIQUE NOT NULL,
            url         VARCHAR(1000),
            created_at  TIMESTAMP DEFAULT NOW(),
            updated_at  TIMESTAMP DEFAULT NOW()
        )
        """,
        conn,
    )


def migrate_rename_hiatus(conn) -> None:
    _exec("UPDATE library SET publication_status = 'Long-Term Hold' WHERE publication_status = 'Hiatus'", conn)


def migrate_add_storage_type(conn) -> None:
    _exec(
        "ALTER TABLE library ADD COLUMN IF NOT EXISTS storage_type VARCHAR(10) NOT NULL DEFAULT 'file'",
        conn,
    )


def migrate_create_book_images(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS book_images (
            sha256      CHAR(64)     PRIMARY KEY,
            ext         VARCHAR(10)  NOT NULL,
            media_type  VARCHAR(100) NOT NULL,
            size_bytes  INTEGER      NOT NULL DEFAULT 0
        )
        """,
        conn,
    )


def migrate_create_book_chapters(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS book_chapters (
            id            SERIAL PRIMARY KEY,
            filename      VARCHAR(600) NOT NULL REFERENCES library(filename) ON DELETE CASCADE,
            chapter_index INTEGER      NOT NULL,
            title         VARCHAR(500) NOT NULL DEFAULT '',
            content       TEXT         NOT NULL DEFAULT '',
            content_tsv   TSVECTOR,
            UNIQUE (filename, chapter_index)
        )
        """,
        conn,
    )
    _exec(
        "CREATE INDEX IF NOT EXISTS idx_book_chapters_filename ON book_chapters (filename, chapter_index)",
        conn,
    )
    _exec(
        "CREATE INDEX IF NOT EXISTS idx_book_chapters_tsv ON book_chapters USING GIN (content_tsv)",
        conn,
    )


def migrate_rebuild_chapter_tsv_with_title(conn) -> None:
    """Rebuild content_tsv to include chapter title. Runs once via schema_migrations tracking."""
    _exec(
        """
        UPDATE book_chapters
        SET content_tsv = to_tsvector('simple',
            COALESCE(title, '') || ' ' ||
            regexp_replace(COALESCE(content, ''), '<[^>]*>', ' ', 'g'))
        """,
        conn,
    )


def migrate_create_app_settings(conn) -> None:
    _exec(
        """
        CREATE TABLE IF NOT EXISTS app_settings (
            id           INTEGER PRIMARY KEY DEFAULT 1,
            develop_mode BOOLEAN NOT NULL DEFAULT FALSE,
            CONSTRAINT single_row CHECK (id = 1)
        )
        """,
        conn,
    )
    _exec("INSERT INTO app_settings (id, develop_mode) VALUES (1, FALSE) ON CONFLICT DO NOTHING", conn)


def migrate_app_settings_break_image(conn) -> None:
    _exec("ALTER TABLE app_settings ADD COLUMN IF NOT EXISTS break_image_sha256 VARCHAR(64) DEFAULT NULL", conn)
    _exec("ALTER TABLE app_settings ADD COLUMN IF NOT EXISTS break_image_ext    VARCHAR(10) DEFAULT NULL", conn)


def migrate_series_volume(conn) -> None:
    _exec(
        """
        ALTER TABLE library
        ADD COLUMN IF NOT EXISTS series_volume VARCHAR(20) NOT NULL DEFAULT ''
        """,
        conn,
    )


def run_migrations() -> None:
    t_start = time.time()
    logger.info("Starting migrations...")
    conn = direct_connect()
    try:
        # Bootstrap: create schema_migrations table (always idempotent, no tracking needed).
        with conn:
            with conn.cursor() as cur:
                cur.execute(
                    """
                    CREATE TABLE IF NOT EXISTS schema_migrations (
                        name        VARCHAR(200) PRIMARY KEY,
                        applied_at  TIMESTAMP DEFAULT NOW()
                    )
                    """
                )

        executed = sum([
            _run_once(conn, "create_library",               migrate_create_library),
            _run_once(conn, "create_book_tags",             migrate_create_book_tags),
            _run_once(conn, "create_reading_progress",      migrate_create_reading_progress),
            _run_once(conn, "create_reading_sessions",      migrate_create_reading_sessions),
            _run_once(conn, "create_library_cover_cache",   migrate_create_library_cover_cache),
            _run_once(conn, "create_credentials",           migrate_create_credentials),
            _run_once(conn, "create_break_patterns",        migrate_create_break_patterns),
            _run_once(conn, "create_backup_log",            migrate_create_backup_log),
            _run_once(conn, "create_perf_indexes",          migrate_create_perf_indexes),
            _run_once(conn, "seed_break_patterns",          migrate_seed_break_patterns),
            _run_once(conn, "add_rating",                   migrate_add_rating),
            _run_once(conn, "remove_cover_missing_tag",     migrate_remove_cover_missing_tag),
            _run_once(conn, "create_bookmarks",             migrate_create_bookmarks),
            _run_once(conn, "series_suffix",                migrate_series_suffix),
            _run_once(conn, "create_builder_drafts",        migrate_create_builder_drafts),
            _run_once(conn, "create_authors",               migrate_create_authors),
            _run_once(conn, "rename_hiatus",                migrate_rename_hiatus),
            _run_once(conn, "add_storage_type",             migrate_add_storage_type),
            _run_once(conn, "create_book_images",           migrate_create_book_images),
            _run_once(conn, "create_book_chapters",         migrate_create_book_chapters),
            _run_once(conn, "rebuild_chapter_tsv_with_title", migrate_rebuild_chapter_tsv_with_title),
            _run_once(conn, "create_app_settings",          migrate_create_app_settings),
            _run_once(conn, "app_settings_break_image",     migrate_app_settings_break_image),
            _run_once(conn, "series_volume",                migrate_series_volume),
            _run_once(conn, "backup_log_scanned_files",     migrate_backup_log_scanned_files),
        ])
    finally:
        conn.close()
    elapsed = time.time() - t_start
    if executed == 0:
        logger.info("Migrations complete in %.1fs — all already applied", elapsed)
    else:
        logger.info("Migrations complete in %.1fs — %d executed", elapsed, executed)