"""Write catalog, HashCache, dat tables and HUD projections into SQLite WAL."""
from __future__ import annotations

import json
import sqlite3
from datetime import datetime, timezone
from pathlib import Path
from typing import Any, Iterable

from .ggpk_fs import GgpkEntry


def snake(name: str) -> str:
    out = []
    for i, ch in enumerate(name):
        if ch.isupper() and i and (name[i - 1].islower() or (i + 1 < len(name) and name[i + 1].islower())):
            out.append("_")
        out.append(ch.lower())
    return "".join(out).replace("__", "_")


def connect(db_path: Path) -> sqlite3.Connection:
    db_path.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(str(db_path))
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA synchronous=NORMAL")
    conn.execute("PRAGMA foreign_keys=ON")
    return conn


def init_catalog_schema(conn: sqlite3.Connection) -> None:
    conn.executescript(
        """
        CREATE TABLE IF NOT EXISTS extract_meta (
            key TEXT PRIMARY KEY,
            value TEXT
        );
        CREATE TABLE IF NOT EXISTS ggpk_files (
            path TEXT PRIMARY KEY,
            tag TEXT NOT NULL,
            offset INTEGER NOT NULL,
            record_length INTEGER NOT NULL,
            data_size INTEGER NOT NULL,
            sha256 BLOB
        );
        CREATE TABLE IF NOT EXISTS hashcache_files (
            name TEXT PRIMARY KEY,
            size INTEGER NOT NULL,
            filetime INTEGER NOT NULL,
            sha256 BLOB NOT NULL
        );
        CREATE TABLE IF NOT EXISTS logical_files (
            path TEXT PRIMARY KEY,
            hash INTEGER,
            bundle_name TEXT,
            bundle_offset INTEGER,
            size INTEGER
        );
        """
    )


def upsert_meta(conn: sqlite3.Connection, items: dict[str, Any]) -> None:
    conn.executemany(
        "INSERT OR REPLACE INTO extract_meta(key, value) VALUES (?, ?)",
        [(k, "" if v is None else str(v)) for k, v in items.items()],
    )


def insert_ggpk_files(conn: sqlite3.Connection, entries: Iterable[GgpkEntry]) -> int:
    rows = [
        (e.path, e.tag, e.offset, e.record_length, e.data_size, e.sha256 or None)
        for e in entries
    ]
    conn.execute("DELETE FROM ggpk_files")
    conn.executemany(
        "INSERT INTO ggpk_files(path, tag, offset, record_length, data_size, sha256) VALUES (?,?,?,?,?,?)",
        rows,
    )
    return len(rows)


def insert_hashcache(conn: sqlite3.Connection, records: Iterable[Any]) -> int:
    rows = [(r.name, r.size, r.filetime, r.sha256) for r in records]
    conn.execute("DELETE FROM hashcache_files")
    conn.executemany(
        "INSERT INTO hashcache_files(name, size, filetime, sha256) VALUES (?,?,?,?)",
        rows,
    )
    return len(rows)


def _i64(value: int) -> int:
    """Store unsigned 64-bit hashes in SQLite's signed INTEGER range."""
    n = int(value) & 0xFFFFFFFFFFFFFFFF
    return n - (1 << 64) if n >= (1 << 63) else n


def insert_logical_files(conn: sqlite3.Connection, files: Iterable[Any]) -> int:
    rows = [(f.path, _i64(f.hash), f.bundle_name, f.offset, f.size) for f in files]
    conn.execute("DELETE FROM logical_files")
    conn.executemany(
        "INSERT INTO logical_files(path, hash, bundle_name, bundle_offset, size) VALUES (?,?,?,?,?)",
        rows,
    )
    return len(rows)


def _sql_cell(value: Any) -> Any:
    if isinstance(value, (list, dict)):
        return json.dumps(value, ensure_ascii=False)
    if isinstance(value, bool):
        return int(value)
    if isinstance(value, int) and (value >= (1 << 63) or value < -(1 << 63)):
        return _i64(value)
    return value


def _sql_type(value: Any) -> str:
    if isinstance(value, bool):
        return "INTEGER"
    if isinstance(value, int):
        return "INTEGER"
    if isinstance(value, float):
        return "REAL"
    return "TEXT"


def write_dat_table(conn: sqlite3.Connection, table_name: str, rows: list[dict[str, Any]]) -> str:
    sql_name = "dat_" + snake(table_name)
    conn.execute(f'DROP TABLE IF EXISTS "{sql_name}"')
    if not rows:
        conn.execute(f'CREATE TABLE "{sql_name}" (_row INTEGER)')
        return sql_name
    cols = list(rows[0].keys())
    sample = rows[0]
    defs = []
    for c in cols:
        if c == "_row":
            defs.append("_row INTEGER")
        else:
            defs.append(f'"{c}" {_sql_type(sample[c])}')
    conn.execute(f'CREATE TABLE "{sql_name}" ({", ".join(defs)})')
    placeholders = ",".join("?" * len(cols))
    col_sql = ",".join(f'"{c}"' for c in cols)
    packed = []
    for rec in rows:
        packed.append(
            tuple(_sql_cell(rec.get(c)) for c in cols)
        )
    conn.executemany(f'INSERT INTO "{sql_name}" ({col_sql}) VALUES ({placeholders})', packed)
    return sql_name


def write_projections(conn: sqlite3.Connection) -> None:
    conn.executescript(
        """
        DROP TABLE IF EXISTS base_items;
        DROP TABLE IF EXISTS world_areas;
        DROP TABLE IF EXISTS skill_gems;
        DROP TABLE IF EXISTS uniques;
        DROP TABLE IF EXISTS item_classes;
        DROP TABLE IF EXISTS mods;
        DROP TABLE IF EXISTS monsters;
        DROP TABLE IF EXISTS soul_cores;
        DROP TABLE IF EXISTS map_tablets;
        """
    )
    if _has_table(conn, "dat_item_classes") and _has_cols(conn, "dat_item_classes", "Id", "Name"):
        conn.execute(
            """
            CREATE TABLE item_classes AS
            SELECT _row, Id AS id, Name AS name, lower(Name) AS name_lower
            FROM dat_item_classes
            """
        )
    if _has_table(conn, "dat_base_item_types") and _has_cols(conn, "dat_base_item_types", "Id", "Name"):
        bit_cols = _table_cols(conn, "dat_base_item_types")
        has_class = (
            "ItemClass" in bit_cols
            and _has_table(conn, "dat_item_classes")
            and _has_cols(conn, "dat_item_classes", "Id")
        )
        join_class = "LEFT JOIN dat_item_classes ic ON ic._row = b.ItemClass" if has_class else ""
        class_expr = "ic.Id" if has_class else (
            "CAST(b.ItemClass AS TEXT)" if "ItemClass" in bit_cols else "NULL"
        )
        width_expr = "b.Width" if "Width" in bit_cols else "1"
        height_expr = "b.Height" if "Height" in bit_cols else "1"
        drop_expr = "b.DropLevel" if "DropLevel" in bit_cols else "0"
        conn.execute(
            f"""
            CREATE TABLE base_items AS
            SELECT
                b.Id AS id,
                b.Name AS name,
                lower(b.Name) AS name_lower,
                {class_expr} AS item_class,
                {width_expr} AS width,
                {height_expr} AS height,
                {drop_expr} AS drop_level,
                1 AS stack_size,
                CASE WHEN {class_expr} IN ('StackableCurrency','Currency') THEN 1 ELSE 0 END AS is_currency
            FROM dat_base_item_types b
            {join_class}
            """
        )
        conn.execute("CREATE INDEX IF NOT EXISTS idx_base_items_name_lower ON base_items(name_lower)")
        if _has_table(conn, "dat_currency_items") and _has_cols(
            conn, "dat_currency_items", "BaseItemType", "StackSize"
        ):
            conn.execute(
                """
                UPDATE base_items SET stack_size = (
                    SELECT ci.StackSize FROM dat_currency_items ci
                    JOIN dat_base_item_types b ON b._row = ci.BaseItemType
                    WHERE b.Id = base_items.id
                )
                WHERE id IN (
                    SELECT b.Id FROM dat_currency_items ci
                    JOIN dat_base_item_types b ON b._row = ci.BaseItemType
                )
                """
            )
            conn.execute(
                """
                UPDATE base_items SET is_currency = 1
                WHERE id IN (
                    SELECT b.Id FROM dat_currency_items ci
                    JOIN dat_base_item_types b ON b._row = ci.BaseItemType
                )
                """
            )
    if _has_table(conn, "dat_world_areas") and _has_cols(conn, "dat_world_areas", "Id", "Name"):
        wcols = _table_cols(conn, "dat_world_areas")
        act = "Act" if "Act" in wcols else "NULL"
        level = "AreaLevel" if "AreaLevel" in wcols else "NULL"
        wp = "CASE WHEN HasWaypoint THEN 1 ELSE 0 END" if "HasWaypoint" in wcols else "0"
        town = "CASE WHEN IsTown THEN 1 ELSE 0 END" if "IsTown" in wcols else "0"
        conn_expr = "Connections" if "Connections" in wcols else "NULL"
        conn.execute(
            f"""
            CREATE TABLE world_areas AS
            SELECT
                Id AS id,
                Name AS name,
                lower(Name) AS name_lower,
                {act} AS act,
                {level} AS area_level,
                {wp} AS has_waypoint,
                {town} AS is_town,
                {conn_expr} AS connections
            FROM dat_world_areas
            """
        )
        conn.execute("CREATE INDEX IF NOT EXISTS idx_world_areas_name_lower ON world_areas(name_lower)")
    if (
        _has_table(conn, "dat_skill_gems")
        and _has_table(conn, "dat_base_item_types")
        and _has_cols(conn, "dat_skill_gems", "BaseItemType")
        and _has_cols(conn, "dat_base_item_types", "Id")
    ):
        gcols = _table_cols(conn, "dat_skill_gems")
        str_req = "g.StrengthRequirementPercent" if "StrengthRequirementPercent" in gcols else "NULL"
        dex_req = "g.DexterityRequirementPercent" if "DexterityRequirementPercent" in gcols else "NULL"
        int_req = "g.IntelligenceRequirementPercent" if "IntelligenceRequirementPercent" in gcols else "NULL"
        conn.execute(
            f"""
            CREATE TABLE skill_gems AS
            SELECT
                b.Id AS id,
                b.Id AS base_item_id,
                {str_req} AS str_req,
                {dex_req} AS dex_req,
                {int_req} AS int_req
            FROM dat_skill_gems g
            JOIN dat_base_item_types b ON b._row = g.BaseItemType
            """
        )
    if _has_table(conn, "dat_unique_stash_layout"):
        ucols = _table_cols(conn, "dat_unique_stash_layout")
        if (
            "WordsKey" in ucols
            and _has_table(conn, "dat_words")
            and _has_cols(conn, "dat_words", "Text")
        ):
            conn.execute(
                """
                CREATE TABLE uniques AS
                SELECT
                    CAST(u._row AS TEXT) AS id,
                    w.Text AS name,
                    lower(w.Text) AS name_lower,
                    '' AS base_item
                FROM dat_unique_stash_layout u
                LEFT JOIN dat_words w ON w._row = u.WordsKey
                WHERE w.Text IS NOT NULL AND w.Text != ''
                """
            )
        elif "Name" in ucols:
            conn.execute(
                """
                CREATE TABLE uniques AS
                SELECT
                    CAST(_row AS TEXT) AS id,
                    CAST(Name AS TEXT) AS name,
                    lower(CAST(Name AS TEXT)) AS name_lower,
                    '' AS base_item
                FROM dat_unique_stash_layout
                """
            )
    if _has_table(conn, "dat_mods") and _has_cols(conn, "dat_mods", "Id"):
        mcols = _table_cols(conn, "dat_mods")
        name_expr = "Name" if "Name" in mcols else "Id"
        level_expr = "Level" if "Level" in mcols else "NULL"
        domain_expr = "Domain" if "Domain" in mcols else "NULL"
        conn.execute(
            f"""
            CREATE TABLE mods AS
            SELECT _row, Id AS id, {name_expr} AS name, {level_expr} AS level, {domain_expr} AS domain
            FROM dat_mods
            """
        )
    if _has_table(conn, "dat_monster_varieties") and _has_cols(conn, "dat_monster_varieties", "Id"):
        cols = _table_cols(conn, "dat_monster_varieties")
        name_expr = "Name" if "Name" in cols else "Id"
        speed_expr = "MovementSpeed" if "MovementSpeed" in cols else "NULL"
        conn.execute(
            f"""
            CREATE TABLE monsters AS
            SELECT _row, Id AS id, {name_expr} AS name, {speed_expr} AS movement_speed
            FROM dat_monster_varieties
            """
        )
    if (
        _has_table(conn, "dat_soul_cores")
        and _has_table(conn, "dat_base_item_types")
        and _has_cols(conn, "dat_soul_cores", "BaseItemType")
        and _has_cols(conn, "dat_base_item_types", "Id", "Name")
    ):
        scols = _table_cols(conn, "dat_soul_cores")
        req = "s.RequiredLevel" if "RequiredLevel" in scols else "NULL"
        bound = "CASE WHEN s.IsSocketBound THEN 1 ELSE 0 END" if "IsSocketBound" in scols else "0"
        conn.execute(
            f"""
            CREATE TABLE soul_cores AS
            SELECT
                b.Id AS id,
                b.Name AS name,
                lower(b.Name) AS name_lower,
                {req} AS required_level,
                {bound} AS is_socket_bound
            FROM dat_soul_cores s
            JOIN dat_base_item_types b ON b._row = s.BaseItemType
            WHERE b.Name IS NOT NULL AND b.Name != ''
            """
        )
        conn.execute("CREATE INDEX IF NOT EXISTS idx_soul_cores_name_lower ON soul_cores(name_lower)")
    if (
        _has_table(conn, "dat_endgame_map_tablets")
        and _has_table(conn, "dat_base_item_types")
        and _has_cols(conn, "dat_endgame_map_tablets", "BaseItemType")
        and _has_cols(conn, "dat_base_item_types", "Id", "Name")
    ):
        conn.execute(
            """
            CREATE TABLE map_tablets AS
            SELECT
                b.Id AS id,
                b.Name AS name,
                lower(b.Name) AS name_lower
            FROM dat_endgame_map_tablets t
            JOIN dat_base_item_types b ON b._row = t.BaseItemType
            WHERE b.Name IS NOT NULL AND b.Name != ''
            """
        )
    upsert_meta(conn, {"projected_at": datetime.now(timezone.utc).isoformat()})


def _has_table(conn: sqlite3.Connection, name: str) -> bool:
    row = conn.execute(
        "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?",
        (name,),
    ).fetchone()
    return row is not None


def _table_cols(conn: sqlite3.Connection, name: str) -> set[str]:
    return {r[1] for r in conn.execute(f'PRAGMA table_info("{name}")')}


def _has_cols(conn: sqlite3.Connection, table: str, *names: str) -> bool:
    cols = _table_cols(conn, table)
    return all(n in cols for n in names)