"""
AutoPOE2 - Offline Wiki & Game Data Database Bridge (Zero-RAM Dependency)
Ưu tiên `poe2_ggpk.db` (trích từ Content.ggpk), fallback `poe2_wiki.db`.
"""

import json
import sqlite3
from pathlib import Path
from typing import Any, Dict, List, Optional

BASE_DIR = Path(__file__).resolve().parent.parent.parent
WIKI_DB_PATH = BASE_DIR / "assets" / "game_data" / "poe2_wiki.db"
GGPK_DB_PATH = BASE_DIR / "assets" / "game_data" / "poe2_ggpk.db"
DB_PATH = WIKI_DB_PATH


def resolve_wiki_db_path() -> Path:
    """Prefer GGPK-extracted SQLite when projection tables already exist."""
    if GGPK_DB_PATH.is_file():
        try:
            conn = sqlite3.connect(f"file:{GGPK_DB_PATH.as_posix()}?mode=ro", uri=True)
            try:
                row = conn.execute(
                    "SELECT 1 FROM sqlite_master WHERE type='table' AND name='base_items'"
                ).fetchone()
            finally:
                conn.close()
            if row:
                return GGPK_DB_PATH
        except sqlite3.Error:
            pass
    return WIKI_DB_PATH


class Poe2WikiDB:
    def __init__(self, db_path: Optional[Path] = None):
        self.db_path = db_path or resolve_wiki_db_path()
        self._conn: Optional[sqlite3.Connection] = None

    def connect(self) -> sqlite3.Connection:
        if self._conn is None:
            if not self.db_path.exists():
                raise FileNotFoundError(
                    f"Không tìm thấy file CSDL wiki tại: {self.db_path}. "
                    "Chạy tools/extract_ggpk_to_db.py (Content.ggpk) hoặc tools/sync_poe2_wiki_data.py."
                )
            self._conn = sqlite3.connect(str(self.db_path), check_same_thread=False)
            self._conn.row_factory = sqlite3.Row
        return self._conn

    def close(self):
        if self._conn is not None:
            self._conn.close()
            self._conn = None

    def get_item_info(self, name_or_id: str) -> Optional[Dict[str, Any]]:
        """
        Tra cứu thông tin BaseItemType theo tên hoặc ID.
        Trả về: id, name, item_class, width, height, drop_level, stack_size, is_currency.
        """
        conn = self.connect()
        cursor = conn.cursor()
        search = name_or_id.strip()
        search_lower = search.lower()

        # 1. Tìm chính xác theo ID hoặc name_lower
        cursor.execute(
            "SELECT * FROM base_items WHERE id = ? OR name_lower = ? LIMIT 1",
            (search, search_lower)
        )
        row = cursor.fetchone()

        # 2. Nếu không thấy, tìm kiếm mờ (prefix/substring)
        if not row:
            cursor.execute(
                "SELECT * FROM base_items WHERE name_lower LIKE ? ORDER BY LENGTH(name) ASC LIMIT 1",
                (f"%{search_lower}%",)
            )
            row = cursor.fetchone()

        if row:
            return {
                "id": row["id"],
                "name": row["name"],
                "item_class": row["item_class"],
                "width": row["width"],
                "height": row["height"],
                "inventory_slots": row["width"] * row["height"],
                "drop_level": row["drop_level"],
                "stack_size": row["stack_size"],
                "is_currency": bool(row["is_currency"])
            }
        return None

    def get_area_info(self, name_or_code: str) -> Optional[Dict[str, Any]]:
        """
        Tra cứu thông tin WorldArea theo tên khu vực hoặc Area Code.
        Trả về: id, name, act, area_level, has_waypoint, is_town, connections.
        """
        conn = self.connect()
        cursor = conn.cursor()
        search = name_or_code.strip()
        search_lower = search.lower()

        cursor.execute(
            "SELECT * FROM world_areas WHERE id = ? OR name_lower = ? LIMIT 1",
            (search, search_lower)
        )
        row = cursor.fetchone()

        if not row:
            cursor.execute(
                "SELECT * FROM world_areas WHERE name_lower LIKE ? ORDER BY LENGTH(name) ASC LIMIT 1",
                (f"%{search_lower}%",)
            )
            row = cursor.fetchone()

        if row:
            connections: list[Any] = []
            try:
                connections = json.loads(row["connections"]) if row["connections"] else []
            except Exception:
                pass

            return {
                "id": row["id"],
                "name": row["name"],
                "act": row["act"],
                "area_level": row["area_level"],
                "has_waypoint": bool(row["has_waypoint"]),
                "is_town": bool(row["is_town"]),
                "connections": connections
            }
        return None

    def get_unique_info(self, unique_name: str) -> Optional[Dict[str, Any]]:
        """Tra cứu thông tin trang bị Unique"""
        conn = self.connect()
        cursor = conn.cursor()
        search_lower = unique_name.strip().lower()

        cursor.execute(
            "SELECT * FROM uniques WHERE name_lower = ? OR name_lower LIKE ? LIMIT 1",
            (search_lower, f"%{search_lower}%")
        )
        row = cursor.fetchone()
        if row:
            base_info = self.get_item_info(row["base_item"]) if row["base_item"] else None
            return {
                "id": row["id"],
                "name": row["name"],
                "base_item": row["base_item"],
                "base_info": base_info
            }
        return None

    def calculate_inventory_footprint(self, item_names: List[str]) -> Dict[str, Any]:
        """
        Tính toán tổng số ô chiếm dụng trong balo cho danh sách vật phẩm.
        Hỗ trợ giải thuật Inventory Tetris tự động gom đồ.
        """
        total_slots = 0
        details = []
        for name in item_names:
            info = self.get_item_info(name)
            if info:
                w, h = info["width"], info["height"]
                slots = w * h
                total_slots += slots
                details.append({
                    "name": info["name"],
                    "width": w,
                    "height": h,
                    "slots": slots,
                    "class": info["item_class"]
                })
            else:
                total_slots += 1
                details.append({
                    "name": name,
                    "width": 1,
                    "height": 1,
                    "slots": 1,
                    "class": "Unknown"
                })

        return {
            "total_slots_needed": total_slots,
            "items": details,
            "fits_in_standard_grid": total_slots <= 60 # POE inventory = 12 cols x 5 rows = 60 slots
        }

# Global singleton instance
_wiki_db_instance = None

def get_wiki_db() -> Poe2WikiDB:
    global _wiki_db_instance
    if _wiki_db_instance is None:
        _wiki_db_instance = Poe2WikiDB()
    return _wiki_db_instance