from __future__ import annotations

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

from models import Identity, InventoryItem, Plan, CraftStep


def utcnow() -> str:
    return datetime.now(timezone.utc).isoformat(timespec="seconds")


class Database:
    def __init__(self, path: Path):
        self.path = Path(path)
        self.path.parent.mkdir(parents=True, exist_ok=True)
        self.conn = sqlite3.connect(self.path)
        self.conn.row_factory = sqlite3.Row
        self._migrate()

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

    def _ensure_column(self, table: str, column: str, definition: str) -> None:
        columns = {row[1] for row in self.conn.execute(f"PRAGMA table_info({table})")}
        if column not in columns:
            self.conn.execute(f"ALTER TABLE {table} ADD COLUMN {column} {definition}")

    def _migrate(self) -> None:
        self.conn.executescript(
            """
            PRAGMA foreign_keys = ON;
            CREATE TABLE IF NOT EXISTS profiles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                server TEXT NOT NULL DEFAULT '',
                realm TEXT NOT NULL DEFAULT '',
                active_import_id INTEGER,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                UNIQUE(name, server)
            );
            CREATE TABLE IF NOT EXISTS imports (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                profile_id INTEGER NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
                imported_at TEXT NOT NULL,
                source_file TEXT NOT NULL,
                source_hash TEXT NOT NULL,
                declared_count INTEGER,
                parsed_count INTEGER NOT NULL,
                warning_count INTEGER NOT NULL DEFAULT 0
            );
            CREATE TABLE IF NOT EXISTS items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                import_id INTEGER NOT NULL REFERENCES imports(id) ON DELETE CASCADE,
                profile_id INTEGER NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
                item_id TEXT,
                full_name TEXT NOT NULL,
                category TEXT NOT NULL,
                base TEXT NOT NULL,
                prefix TEXT NOT NULL DEFAULT '',
                suffix TEXT NOT NULL DEFAULT '',
                legendary INTEGER NOT NULL DEFAULT 0,
                quality TEXT NOT NULL,
                upgrade INTEGER NOT NULL DEFAULT 0,
                level INTEGER NOT NULL DEFAULT 0,
                shelf TEXT,
                html_type TEXT,
                parser_notes TEXT,
                raw_prefix TEXT,
                raw_base TEXT,
                raw_suffix TEXT,
                main_image TEXT,
                prefix_image TEXT,
                suffix_image TEXT
            );
            CREATE INDEX IF NOT EXISTS idx_items_profile_import ON items(profile_id, import_id);
            CREATE INDEX IF NOT EXISTS idx_items_identity ON items(category, base, prefix, suffix, legendary);
            CREATE TABLE IF NOT EXISTS saved_plans (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                profile_id INTEGER NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
                name TEXT NOT NULL,
                target_json TEXT NOT NULL,
                plan_json TEXT NOT NULL,
                created_at TEXT NOT NULL
            );
            CREATE TABLE IF NOT EXISTS encyclopedia_stats (
                cache_key TEXT PRIMARY KEY,
                url TEXT NOT NULL,
                category TEXT NOT NULL,
                prefix TEXT NOT NULL DEFAULT '',
                base TEXT NOT NULL,
                suffix TEXT NOT NULL DEFAULT '',
                quality TEXT NOT NULL,
                upgrade INTEGER NOT NULL DEFAULT 0,
                legendary INTEGER NOT NULL DEFAULT 0,
                player_level INTEGER NOT NULL DEFAULT 80,
                item_name TEXT,
                item_category TEXT,
                damage TEXT,
                defense TEXT,
                features TEXT,
                requirements TEXT,
                price TEXT,
                sell_price TEXT,
                mana TEXT,
                nanites TEXT,
                zone_item TEXT,
                zone_prefix TEXT,
                zone_suffix TEXT,
                zone_total TEXT,
                rarity_item TEXT,
                rarity_prefix TEXT,
                rarity_suffix TEXT,
                rarity_total TEXT,
                raw_text TEXT,
                main_image_url TEXT,
                prefix_image_url TEXT,
                suffix_image_url TEXT,
                fetched_at TEXT NOT NULL
            );
            CREATE INDEX IF NOT EXISTS idx_encyclopedia_lookup
                ON encyclopedia_stats(category, base, prefix, suffix, legendary, quality, upgrade, player_level);
            """
        )
        self._ensure_column("items", "main_image", "TEXT")
        self._ensure_column("items", "prefix_image", "TEXT")
        self._ensure_column("items", "suffix_image", "TEXT")
        self._ensure_column("encyclopedia_stats", "main_image_url", "TEXT")
        self._ensure_column("encyclopedia_stats", "prefix_image_url", "TEXT")
        self._ensure_column("encyclopedia_stats", "suffix_image_url", "TEXT")

        # v0.9 could cache a downloaded page even when the HTML parser failed
        # to read the generated item. Remove those malformed entries so the
        # corrected parser fetches them again instead of displaying stale
        # ``(brak nazwy)`` records forever.
        self.conn.execute(
            "DELETE FROM encyclopedia_stats "
            "WHERE TRIM(COALESCE(item_name, '')) = '' "
            "OR TRIM(COALESCE(item_category, '')) = ''"
        )
        self.conn.commit()

    def profiles(self) -> list[sqlite3.Row]:
        return list(self.conn.execute("SELECT * FROM profiles ORDER BY name COLLATE NOCASE, server"))

    def get_profile(self, profile_id: int) -> sqlite3.Row | None:
        return self.conn.execute("SELECT * FROM profiles WHERE id=?", (profile_id,)).fetchone()

    def upsert_profile(self, name: str, server: str, realm: str) -> int:
        now = utcnow()
        row = self.conn.execute("SELECT id FROM profiles WHERE name=? AND server=?", (name, server)).fetchone()
        if row:
            self.conn.execute("UPDATE profiles SET realm=?, updated_at=? WHERE id=?", (realm, now, row["id"]))
            self.conn.commit()
            return int(row["id"])
        cur = self.conn.execute(
            "INSERT INTO profiles(name, server, realm, created_at, updated_at) VALUES(?,?,?,?,?)",
            (name, server, realm, now, now),
        )
        self.conn.commit()
        return int(cur.lastrowid)

    def import_inventory(self, profile_id: int, items: Iterable[InventoryItem], metadata: dict) -> int:
        items = list(items)
        now = utcnow()
        cur = self.conn.execute(
            """INSERT INTO imports(profile_id, imported_at, source_file, source_hash,
               declared_count, parsed_count, warning_count) VALUES(?,?,?,?,?,?,?)""",
            (
                profile_id,
                now,
                metadata.get("source_file", ""),
                metadata.get("source_hash", ""),
                metadata.get("declared_count"),
                metadata.get("parsed_count", len(items)),
                len(metadata.get("warnings") or []),
            ),
        )
        import_id = int(cur.lastrowid)
        rows = []
        for item in items:
            ident = item.identity
            rows.append((
                import_id, profile_id, item.item_id, item.full_name, ident.category, ident.base,
                ident.prefix, ident.suffix, 1 if ident.legendary else 0, item.quality,
                item.upgrade, item.level, item.shelf, item.html_type, item.parser_notes,
                item.raw_prefix, item.raw_base, item.raw_suffix,
                item.main_image, item.prefix_image, item.suffix_image,
            ))
        self.conn.executemany(
            """INSERT INTO items(import_id, profile_id, item_id, full_name, category, base,
               prefix, suffix, legendary, quality, upgrade, level, shelf, html_type,
               parser_notes, raw_prefix, raw_base, raw_suffix, main_image, prefix_image, suffix_image)
               VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
            rows,
        )
        self.conn.execute(
            "UPDATE profiles SET active_import_id=?, updated_at=? WHERE id=?",
            (import_id, now, profile_id),
        )
        self.conn.commit()
        return import_id

    def imports(self, profile_id: int) -> list[sqlite3.Row]:
        return list(self.conn.execute(
            "SELECT * FROM imports WHERE profile_id=? ORDER BY imported_at DESC, id DESC", (profile_id,)
        ))

    def set_active_import(self, profile_id: int, import_id: int) -> None:
        self.conn.execute("UPDATE profiles SET active_import_id=?, updated_at=? WHERE id=?", (import_id, utcnow(), profile_id))
        self.conn.commit()

    def active_items(self, profile_id: int) -> list[InventoryItem]:
        profile = self.get_profile(profile_id)
        if not profile or not profile["active_import_id"]:
            return []
        return self.items_for_import(profile_id, int(profile["active_import_id"]))

    def items_for_import(self, profile_id: int, import_id: int) -> list[InventoryItem]:
        rows = self.conn.execute(
            "SELECT * FROM items WHERE profile_id=? AND import_id=? ORDER BY id", (profile_id, import_id)
        )
        return [self._row_to_item(row) for row in rows]

    def _row_to_item(self, row: sqlite3.Row) -> InventoryItem:
        return InventoryItem(
            db_id=int(row["id"]), profile_id=int(row["profile_id"]), import_id=int(row["import_id"]),
            item_id=row["item_id"] or "", full_name=row["full_name"],
            identity=Identity(row["category"], row["base"], row["prefix"], row["suffix"], bool(row["legendary"])),
            quality=row["quality"], upgrade=int(row["upgrade"]), level=int(row["level"]),
            shelf=row["shelf"] or "", html_type=row["html_type"] or "", parser_notes=row["parser_notes"] or "",
            raw_prefix=row["raw_prefix"] or "", raw_base=row["raw_base"] or "", raw_suffix=row["raw_suffix"] or "",
            main_image=row["main_image"] or "", prefix_image=row["prefix_image"] or "", suffix_image=row["suffix_image"] or "",
        )

    def save_plan(self, profile_id: int, name: str, plan: Plan) -> int:
        target = {
            "category": plan.target.category, "base": plan.target.base,
            "prefix": plan.target.prefix, "suffix": plan.target.suffix,
            "legendary": plan.target.legendary,
        }
        plan_data = {
            "target": target,
            "steps": [
                {
                    "left": step.left.key(), "right": step.right.key(), "result": step.result.key(),
                    "left_source": step.left_source, "right_source": step.right_source,
                    "result_level": step.result_level,
                }
                for step in plan.steps
            ],
            "consumed_item_ids": plan.consumed_item_ids,
            "missing": [m.key() for m in plan.missing],
            "result_level": plan.result_level,
            "notes": plan.notes,
            "craft_depth": plan.craft_depth,
        }
        cur = self.conn.execute(
            "INSERT INTO saved_plans(profile_id, name, target_json, plan_json, created_at) VALUES(?,?,?,?,?)",
            (profile_id, name, json.dumps(target, ensure_ascii=False), json.dumps(plan_data, ensure_ascii=False), utcnow()),
        )
        self.conn.commit()
        return int(cur.lastrowid)

    def saved_plans(self, profile_id: int) -> list[sqlite3.Row]:
        return list(self.conn.execute(
            "SELECT * FROM saved_plans WHERE profile_id=? ORDER BY created_at DESC, id DESC", (profile_id,)
        ))

    def delete_saved_plan(self, plan_id: int) -> None:
        self.conn.execute("DELETE FROM saved_plans WHERE id=?", (plan_id,))
        self.conn.commit()
    def get_encyclopedia_stats(self, cache_key: str):
        return self.conn.execute(
            "SELECT * FROM encyclopedia_stats WHERE cache_key=? "
            "AND TRIM(COALESCE(item_name, '')) <> '' "
            "AND TRIM(COALESCE(item_category, '')) <> ''",
            (cache_key,),
        ).fetchone()

    def upsert_encyclopedia_stats(self, stats) -> None:
        data = stats.to_dict() if hasattr(stats, "to_dict") else dict(stats)
        if not str(data.get("item_name") or "").strip() or not str(data.get("item_category") or "").strip():
            raise ValueError("Nie można zapisać niekompletnych statystyk bez nazwy i kategorii przedmiotu.")
        columns = [
            "cache_key", "url", "category", "prefix", "base", "suffix", "quality",
            "upgrade", "legendary", "player_level", "item_name", "item_category",
            "damage", "defense", "features", "requirements", "price", "sell_price",
            "mana", "nanites", "zone_item", "zone_prefix", "zone_suffix", "zone_total",
            "rarity_item", "rarity_prefix", "rarity_suffix", "rarity_total", "raw_text",
            "main_image_url", "prefix_image_url", "suffix_image_url", "fetched_at",
        ]
        values = []
        for column in columns:
            value = data.get(column)
            if column == "legendary":
                value = 1 if value else 0
            values.append(value)
        placeholders = ",".join("?" for _ in columns)
        updates = ",".join(f"{c}=excluded.{c}" for c in columns if c != "cache_key")
        self.conn.execute(
            f"INSERT INTO encyclopedia_stats({','.join(columns)}) VALUES({placeholders}) "
            f"ON CONFLICT(cache_key) DO UPDATE SET {updates}",
            values,
        )
        self.conn.commit()

    def encyclopedia_stats_rows(self) -> list[sqlite3.Row]:
        return list(self.conn.execute(
            "SELECT * FROM encyclopedia_stats "
            "WHERE TRIM(COALESCE(item_name, '')) <> '' "
            "AND TRIM(COALESCE(item_category, '')) <> '' "
            "ORDER BY fetched_at DESC, item_name COLLATE NOCASE"
        ))

    def encyclopedia_cache_count(self) -> int:
        row = self.conn.execute(
            "SELECT COUNT(*) AS n FROM encyclopedia_stats "
            "WHERE TRIM(COALESCE(item_name, '')) <> '' "
            "AND TRIM(COALESCE(item_category, '')) <> ''"
        ).fetchone()
        return int(row["n"] if row else 0)

