"""
FreeExile Skill & Gem Database Service (PoE2 Multi-Tier Tag Architecture).
Manages SQLite persistence, tag validation, PoE2 combo synergies, and build archetype matching.
Follows Clean Architecture, high-density queries, and zero-leak connection pooling.
"""

from __future__ import annotations
import json
import os
import sqlite3
from contextlib import contextmanager
from typing import Any, Dict, List, Optional, Set, Tuple

from server.world.martial_types import (
    SkillTag,
    TagCategory,
    AttributeAffinity,
    ComboRole,
    FiveElements,
    MartialSect,
    WeaponCategory,
)
from server.world.skill_tag_catalog import (
    get_canonical_skill_tags,
    get_canonical_build_archetypes,
    get_canonical_passive_nodes,
)
from server.world.martial_catalog import (
    create_standard_active_skills,
    create_standard_augment_sigils,
)


class SkillDatabaseService:
    def __init__(self, db_path: Optional[str] = None):
        if not db_path:
            base_dir = os.path.abspath(os.path.join(os.path.dirname(__file__), "..", "..", "data"))
            os.makedirs(base_dir, exist_ok=True)
            db_path = os.path.join(base_dir, "martial_database.db")
        self.db_path = db_path
        if self.db_path == ":memory:":
            self._memory_conn = sqlite3.connect(":memory:")
            self._memory_conn.row_factory = sqlite3.Row
            self._memory_conn.execute("PRAGMA foreign_keys = ON")
        else:
            self._memory_conn = None
        self._init_database()

    @contextmanager
    def _get_connection(self):
        if self._memory_conn is not None:
            yield self._memory_conn
            self._memory_conn.commit()
        else:
            conn = sqlite3.connect(self.db_path)
            conn.row_factory = sqlite3.Row
            conn.execute("PRAGMA foreign_keys = ON")
            try:
                yield conn
                conn.commit()
            except Exception:
                conn.rollback()
                raise
            finally:
                conn.close()

    def close(self) -> None:
        if self._memory_conn is not None:
            self._memory_conn.close()
            self._memory_conn = None

    def _init_database(self) -> None:
        """Khởi tạo toàn bộ cấu trúc bảng SQLite chuẩn mực."""
        with self._get_connection() as conn:
            conn.executescript("""
                CREATE TABLE IF NOT EXISTS skill_tags (
                    tag_id TEXT PRIMARY KEY,
                    category TEXT NOT NULL,
                    name_vi TEXT NOT NULL,
                    name_en TEXT NOT NULL,
                    description TEXT NOT NULL,
                    affected_modifiers TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS skills (
                    skill_id INTEGER PRIMARY KEY,
                    skill_name TEXT NOT NULL,
                    element TEXT NOT NULL,
                    sect TEXT NOT NULL,
                    attribute_affinity TEXT NOT NULL,
                    weapon_requirement TEXT NOT NULL,
                    base_damage REAL NOT NULL,
                    energy_cost INTEGER NOT NULL,
                    cooldown_ms INTEGER NOT NULL,
                    cast_time_ms INTEGER NOT NULL,
                    animation_lock_ms INTEGER NOT NULL,
                    combo_role TEXT NOT NULL,
                    upside_desc TEXT NOT NULL,
                    downside_desc TEXT NOT NULL,
                    tags_json TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS support_gems (
                    sigil_id INTEGER PRIMARY KEY,
                    sigil_name TEXT NOT NULL,
                    augment_type TEXT NOT NULL,
                    attribute_affinity TEXT NOT NULL,
                    required_tags_json TEXT NOT NULL,
                    excluded_tags_json TEXT NOT NULL,
                    damage_multiplier REAL NOT NULL,
                    energy_cost_multiplier REAL NOT NULL,
                    projectile_bonus INTEGER NOT NULL,
                    aoe_radius_multiplier REAL NOT NULL,
                    upside_desc TEXT NOT NULL,
                    downside_desc TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS build_archetypes (
                    archetype_id TEXT PRIMARY KEY,
                    name_vi TEXT NOT NULL,
                    name_en TEXT NOT NULL,
                    primary_attribute TEXT NOT NULL,
                    element TEXT NOT NULL,
                    playstyle_summary TEXT NOT NULL,
                    core_tags_json TEXT NOT NULL,
                    recommended_skills_json TEXT NOT NULL,
                    recommended_gems_json TEXT NOT NULL,
                    combo_guide TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS passive_nodes (
                    node_id INTEGER PRIMARY KEY,
                    name_vi TEXT NOT NULL,
                    affected_tags_json TEXT NOT NULL,
                    damage_multiplier REAL NOT NULL,
                    crit_chance_bonus REAL NOT NULL,
                    attack_speed_bonus REAL NOT NULL,
                    area_radius_multiplier REAL NOT NULL,
                    special_mechanic TEXT NOT NULL
                );
            """)

    def seed_canonical_data(self, force: bool = False) -> None:
        """Nạp dữ liệu mẫu hạt giống chuẩn mực vào database."""
        with self._get_connection() as conn:
            cur = conn.cursor()
            if not force:
                cur.execute("SELECT COUNT(*) as cnt FROM skill_tags")
                if cur.fetchone()["cnt"] > 0:
                    return

            cur.execute("DELETE FROM skill_tags")
            cur.execute("DELETE FROM skills")
            cur.execute("DELETE FROM support_gems")
            cur.execute("DELETE FROM build_archetypes")
            cur.execute("DELETE FROM passive_nodes")

            # 1. Tags
            for tag_def in get_canonical_skill_tags().values():
                cur.execute(
                    "INSERT INTO skill_tags VALUES (?, ?, ?, ?, ?, ?)",
                    (tag_def.tag.value, tag_def.category.value, tag_def.name_vi,
                     tag_def.name_en, tag_def.description, json.dumps(tag_def.affected_modifiers))
                )

            # 2. Skills
            for s in create_standard_active_skills().values():
                tags_str = [t.value for t in s.tags]
                cur.execute(
                    "INSERT INTO skills VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    (s.skill_id, s.skill_name, s.element.value, s.sect.value,
                     s.attribute_affinity.value, s.weapon_requirement.value,
                     s.base_damage, s.energy_cost, s.cooldown_ms, s.cast_time_ms,
                     s.animation_lock_ms, s.combo_role.value, s.upside_desc,
                     s.downside_desc, json.dumps(tags_str))
                )

            # 3. Support Gems
            for g in create_standard_augment_sigils().values():
                req_tags = [t.value for t in g.required_tags]
                ex_tags = [t.value for t in g.excluded_tags] if hasattr(g, "excluded_tags") else []
                cur.execute(
                    "INSERT INTO support_gems VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    (g.sigil_id, g.sigil_name, g.augment_type.value,
                     g.attribute_affinity.value if hasattr(g, "attribute_affinity") else "UNIVERSAL",
                     json.dumps(req_tags), json.dumps(ex_tags), g.damage_multiplier,
                     g.energy_cost_multiplier, g.projectile_bonus, g.aoe_radius_multiplier,
                     g.upside_desc, g.downside_desc)
                )

            # 4. Archetypes
            for a in get_canonical_build_archetypes().values():
                core_tags = [t.value for t in a.core_tags]
                cur.execute(
                    "INSERT INTO build_archetypes VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                    (a.archetype_id, a.name_vi, a.name_en, a.primary_attribute.value,
                     a.element.value, a.playstyle_summary, json.dumps(core_tags),
                     json.dumps(a.recommended_skills), json.dumps(a.recommended_gems), a.combo_guide)
                )

            # 5. Passive Bone Nodes
            for p in get_canonical_passive_nodes():
                aff_tags = [t.value for t in p.affected_tags]
                cur.execute(
                    "INSERT INTO passive_nodes VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                    (p.node_id, p.name_vi, json.dumps(aff_tags), p.damage_multiplier,
                     p.crit_chance_bonus, p.attack_speed_bonus, p.area_radius_multiplier, p.special_mechanic)
                )

    def get_skill(self, skill_id: int) -> Optional[Dict[str, Any]]:
        """Lấy thông tin chiêu thức theo ID."""
        with self._get_connection() as conn:
            row = conn.execute("SELECT * FROM skills WHERE skill_id = ?", (skill_id,)).fetchone()
            if not row:
                return None
            res = dict(row)
            res["tags"] = json.loads(res["tags_json"])
            return res

    def search_skills(
        self,
        tags: Optional[Set[SkillTag]] = None,
        element: Optional[FiveElements] = None,
        attribute: Optional[AttributeAffinity] = None,
        weapon: Optional[WeaponCategory] = None,
        combo_role: Optional[ComboRole] = None,
    ) -> List[Dict[str, Any]]:
        """Truy vấn tìm kiếm chiêu thức theo ma trận lọc linh hoạt."""
        query = "SELECT * FROM skills WHERE 1=1"
        params: List[Any] = []
        if element:
            query += " AND element = ?"
            params.append(element.value)
        if attribute:
            query += " AND attribute_affinity = ?"
            params.append(attribute.value)
        if weapon and weapon != WeaponCategory.ANY:
            query += " AND (weapon_requirement = ? OR weapon_requirement = 'ANY')"
            params.append(weapon.value)
        if combo_role:
            query += " AND combo_role = ?"
            params.append(combo_role.value)

        with self._get_connection() as conn:
            rows = conn.execute(query, params).fetchall()
            results: List[Dict[str, Any]] = []
            for r in rows:
                item = dict(r)
                item["tags"] = json.loads(item["tags_json"])
                if tags:
                    tag_vals = {t.value for t in tags}
                    if not tag_vals.issubset(set(item["tags"])):
                        continue
                results.append(item)
            return results

    def validate_gem_link(self, skill_id: int, sigil_id: int) -> Tuple[bool, str]:
        """Kiểm tra tính hợp lệ khi liên kết Gem Hỗ Trợ vào Chiêu Thức."""
        skill = self.get_skill(skill_id)
        if not skill:
            return False, f"Không tìm thấy chiêu thức {skill_id}"
        with self._get_connection() as conn:
            row = conn.execute("SELECT * FROM support_gems WHERE sigil_id = ?", (sigil_id,)).fetchone()
            if not row:
                return False, f"Không tìm thấy Dị Biến Tà Ấn {sigil_id}"
            gem = dict(row)
            req_tags = set(json.loads(gem["required_tags_json"]))
            ex_tags = set(json.loads(gem["excluded_tags_json"]))
            skill_tags = set(skill["tags"])

            if ex_tags and (ex_tags & skill_tags):
                conflict = list(ex_tags & skill_tags)
                return False, f"Dị Biến Tà Ấn '{gem['sigil_name']}' xung đột với tag: {conflict}"
            if req_tags and not req_tags.issubset(skill_tags):
                missing = list(req_tags - skill_tags)
                return False, f"Dị Biến Tà Ấn '{gem['sigil_name']}' thiếu tag bắt buộc: {missing}"
            return True, f"Liên kết Dị Biến Tà Ấn '{gem['sigil_name']}' thành công"

    def evaluate_combo_synergy(self, starter_id: int, finisher_id: int) -> Dict[str, Any]:
        """Đánh giá hiệp đồng Combo PoE2 giữa đòn Starter và đòn Finisher."""
        starter = self.get_skill(starter_id)
        finisher = self.get_skill(finisher_id)
        if not starter or not finisher:
            return {"synergy_active": False, "reason": "Chiêu thức không tồn tại"}

        s_tags = set(starter["tags"])
        f_tags = set(finisher["tags"])
        synergies: List[str] = []
        multiplier = 1.0

        # Combo Starter -> Finisher logic
        has_starter = SkillTag.COMBO_STARTER.value in s_tags or starter["combo_role"] == ComboRole.COMBO_STARTER.value
        has_finisher = SkillTag.COMBO_FINISHER.value in f_tags or finisher["combo_role"] == ComboRole.COMBO_FINISHER.value
        if has_starter and has_finisher:
            multiplier += 1.5
            synergies.append("Chuỗi Combo PoE2: Khai Chiêu Mồi Bẫy -> Kích Nổ Tuyệt Sát (+150% Sát thương bộc phát)")

        # Element Synergies
        if SkillTag.COLD.value in s_tags and SkillTag.SLAM.value in f_tags:
            multiplier += 0.5
            synergies.append("Hiệp đồng Băng Phách & Trọng Kích: Đập vỡ mục tiêu đóng băng gây nổ vụn băng (Shatter)")
        if SkillTag.POISON.value in s_tags and SkillTag.AOE.value in f_tags and SkillTag.CORPSE.value in f_tags:
            multiplier += 0.6
            synergies.append("Hiệp đồng Hủ Độc & Nổ Xác: Kích nổ dịch độc lây lan toàn bản đồ (Toxin Detonation)")
        if SkillTag.ARMOR_BREAK.value in s_tags and (
            SkillTag.PHYSICAL.value in f_tags
            or SkillTag.PHYSIQUE_ALIGNED.value in f_tags
            or SkillTag.MELEE.value in f_tags
        ):
            multiplier += 0.4
            synergies.append("Hiệp đồng Phá Giáp & Cương Kình: Xé toạc da thịt gây Xuất Huyết gấp đôi (Armor Shred)")

        return {
            "synergy_active": len(synergies) > 0,
            "combo_multiplier": multiplier,
            "synergies": synergies,
            "starter_name": starter["skill_name"],
            "finisher_name": finisher["skill_name"],
        }

    def match_build_archetypes(
        self,
        skill_ids: List[int],
        gem_ids: List[int]
    ) -> List[Dict[str, Any]]:
        """Đối chiếu loadout của người chơi với 6 Hướng Build kinh điển."""
        combined_tags: Set[str] = set()
        for sid in skill_ids:
            s = self.get_skill(sid)
            if s:
                combined_tags.update(s["tags"])

        with self._get_connection() as conn:
            rows = conn.execute("SELECT * FROM build_archetypes").fetchall()
            results = []
            for r in rows:
                item = dict(r)
                core_tags = set(json.loads(item["core_tags_json"]))
                intersection = combined_tags & core_tags
                score = len(intersection) / len(core_tags) if core_tags else 0.0
                if score >= 0.35: # Khớp ít nhất 35% tag cốt lõi
                    results.append({
                        "archetype_id": item["archetype_id"],
                        "name_vi": item["name_vi"],
                        "name_en": item["name_en"],
                        "element": item["element"],
                        "primary_attribute": item["primary_attribute"],
                        "match_score_percent": round(score * 100, 1),
                        "matched_tags": list(intersection),
                        "playstyle_summary": item["playstyle_summary"],
                        "combo_guide": item["combo_guide"],
                    })
            results.sort(key=lambda x: x["match_score_percent"], reverse=True)
            return results

    def calculate_passive_bonus(self, skill_id: int, node_ids: List[int]) -> Dict[str, float]:
        """Tính toán tổng chỉ số khuếch đại từ các điểm Huyết Cốt Ma Đồ dựa trên Tag."""
        skill = self.get_skill(skill_id)
        if not skill or not node_ids:
            return {"damage_multiplier": 1.0, "attack_speed_bonus": 0.0, "crit_chance_bonus": 0.0, "area_radius_multiplier": 1.0}

        s_tags = set(skill["tags"])
        dmg_mult = 1.0
        spd_bonus = 0.0
        crit_bonus = 0.0
        area_mult = 1.0

        with self._get_connection() as conn:
            placeholders = ",".join("?" * len(node_ids))
            rows = conn.execute(f"SELECT * FROM passive_nodes WHERE node_id IN ({placeholders})", node_ids).fetchall()
            for r in rows:
                p_tags = set(json.loads(r["affected_tags_json"]))
                if p_tags & s_tags: # Có ít nhất 1 tag trùng khớp
                    dmg_mult *= r["damage_multiplier"]
                    spd_bonus += r["attack_speed_bonus"]
                    crit_bonus += r["crit_chance_bonus"]
                    area_mult *= r["area_radius_multiplier"]

        return {
            "damage_multiplier": round(dmg_mult, 4),
            "attack_speed_bonus": round(spd_bonus, 4),
            "crit_chance_bonus": round(crit_bonus, 4),
            "area_radius_multiplier": round(area_mult, 4),
        }