"""SQLite-backed store data: products, delivery fees, orders and support tickets."""

from __future__ import annotations

import json
import re
import sqlite3
import threading
import unicodedata
from collections.abc import Iterator
from contextlib import contextmanager
from datetime import datetime, timezone
from pathlib import Path
from typing import Any

SCHEMA = """
CREATE TABLE IF NOT EXISTS store_info (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS products (
    sku       TEXT PRIMARY KEY,
    name      TEXT NOT NULL,
    category  TEXT NOT NULL,
    price_dzd INTEGER NOT NULL,
    colors    TEXT NOT NULL,
    stock     TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS delivery_zones (
    code          INTEGER PRIMARY KEY,
    name          TEXT NOT NULL,
    home_dzd      INTEGER NOT NULL,
    stop_desk_dzd INTEGER NOT NULL,
    days          TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS delivery_aliases (
    alias TEXT PRIMARY KEY,
    code  INTEGER NOT NULL REFERENCES delivery_zones(code)
);
CREATE TABLE IF NOT EXISTS orders (
    id                  TEXT PRIMARY KEY,
    customer_first_name TEXT NOT NULL,
    phone_last4         TEXT NOT NULL,
    status              TEXT NOT NULL,
    items               TEXT NOT NULL,
    total_dzd           INTEGER NOT NULL,
    wilaya              TEXT NOT NULL,
    delivery_type       TEXT NOT NULL,
    created_at          TEXT NOT NULL,
    shipped_at          TEXT,
    expected_delivery   TEXT,
    tracking_code       TEXT
);
CREATE TABLE IF NOT EXISTS support_tickets (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id    INTEGER NOT NULL,
    username   TEXT,
    reason     TEXT NOT NULL,
    created_at TEXT NOT NULL,
    status     TEXT NOT NULL DEFAULT 'open'
);
"""

ORDER_ID_RE = re.compile(r"^[A-Z]{2}-\d{5}$")
_ARABIC_FOLD = str.maketrans("أإآى", "اااي")
_ARABIC_DIACRITICS = re.compile(r"[ً-ْٰـ]")


def normalize(text: str) -> str:
    """Lowercase, strip Latin accents and Arabic diacritics, fold hamza and alef variants.

    Folding is applied to both the stored aliases and the query, so "الجزائر" and the
    common spelling "الجزاير" match the same wilaya.
    """
    text = unicodedata.normalize("NFKC", text).strip().lower()
    text = _ARABIC_DIACRITICS.sub("", text)
    text = text.translate(_ARABIC_FOLD)
    decomposed = unicodedata.normalize("NFD", text)
    text = "".join(ch for ch in decomposed if unicodedata.category(ch) != "Mn")
    text = re.sub(r"^(wilaya\s+(de\s+|d['’])?|ولاية\s+)", "", text)
    text = re.sub(r"[^\w\s-]", " ", text)
    return re.sub(r"\s+", " ", text).strip()


class Store:
    """Thread-safe access to the store database."""

    def __init__(self, db_path: Path | str) -> None:
        self._db_path = str(db_path)
        self._lock = threading.Lock()
        self._conn = sqlite3.connect(self._db_path, check_same_thread=False)
        self._conn.row_factory = sqlite3.Row
        self._conn.execute("PRAGMA foreign_keys = ON")
        self._conn.executescript(SCHEMA)

    def close(self) -> None:
        with self._lock:
            self._conn.close()

    @contextmanager
    def _tx(self) -> Iterator[sqlite3.Connection]:
        with self._lock:
            try:
                yield self._conn
                self._conn.commit()
            except Exception:
                self._conn.rollback()
                raise

    # ------------------------------------------------------------------ seeding

    def is_empty(self) -> bool:
        with self._lock:
            row = self._conn.execute("SELECT COUNT(*) AS n FROM products").fetchone()
        return row["n"] == 0

    def load_catalog(self, catalog_path: Path | str, *, replace: bool = False) -> None:
        data = json.loads(Path(catalog_path).read_text(encoding="utf-8"))
        with self._tx() as conn:
            if replace:
                for table in ("delivery_aliases", "delivery_zones", "products", "orders", "store_info"):
                    conn.execute(f"DELETE FROM {table}")
            for key, value in data.get("store", {}).items():
                conn.execute("INSERT OR REPLACE INTO store_info(key, value) VALUES (?, ?)", (key, str(value)))
            for p in data.get("products", []):
                conn.execute(
                    "INSERT OR REPLACE INTO products(sku, name, category, price_dzd, colors, stock) VALUES (?, ?, ?, ?, ?, ?)",
                    (p["sku"], p["name"], p["category"], int(p["price_dzd"]), json.dumps(p["colors"]), json.dumps(p["stock"])),
                )
            for z in data.get("delivery", []):
                conn.execute(
                    "INSERT OR REPLACE INTO delivery_zones(code, name, home_dzd, stop_desk_dzd, days) VALUES (?, ?, ?, ?, ?)",
                    (int(z["code"]), z["name"], int(z["home_dzd"]), int(z["stop_desk_dzd"]), z["days"]),
                )
                for alias in {z["name"], *z.get("aliases", [])}:
                    conn.execute(
                        "INSERT OR REPLACE INTO delivery_aliases(alias, code) VALUES (?, ?)",
                        (normalize(alias), int(z["code"])),
                    )
            for o in data.get("orders", []):
                conn.execute(
                    """INSERT OR REPLACE INTO orders(id, customer_first_name, phone_last4, status, items, total_dzd,
                       wilaya, delivery_type, created_at, shipped_at, expected_delivery, tracking_code)
                       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                    (
                        o["id"].upper(),
                        o["customer_first_name"],
                        o["phone_last4"],
                        o["status"],
                        json.dumps(o["items"]),
                        int(o["total_dzd"]),
                        o["wilaya"],
                        o["delivery_type"],
                        o["created_at"],
                        o.get("shipped_at"),
                        o.get("expected_delivery"),
                        o.get("tracking_code"),
                    ),
                )

    # ------------------------------------------------------------------ queries

    def store_info(self) -> dict[str, str]:
        with self._lock:
            rows = self._conn.execute("SELECT key, value FROM store_info").fetchall()
        return {r["key"]: r["value"] for r in rows}

    def search_products(self, query: str = "", size: str | None = None, limit: int = 5) -> list[dict[str, Any]]:
        limit = max(1, min(limit, 10))
        with self._lock:
            rows = self._conn.execute("SELECT * FROM products ORDER BY category, price_dzd").fetchall()

        terms = [t for t in normalize(query).split() if len(t) > 1]
        size_key = size.strip().upper() if size else None
        scored: list[tuple[int, dict[str, Any]]] = []
        for row in rows:
            colors: list[str] = json.loads(row["colors"])
            haystack = normalize(f"{row['sku']} {row['name']} {row['category']} {' '.join(colors)}")
            score = sum(1 for t in terms if t in haystack) if terms else 1
            if score == 0:
                continue
            stock: dict[str, int] = json.loads(row["stock"])
            item: dict[str, Any] = {
                "sku": row["sku"],
                "name": row["name"],
                "category": row["category"],
                "price_dzd": row["price_dzd"],
                "colors": colors,
                "sizes_in_stock": [s for s, qty in stock.items() if qty > 0],
                "sizes_out_of_stock": [s for s, qty in stock.items() if qty <= 0],
            }
            if size_key is not None:
                item["requested_size"] = size_key
                item["requested_size_available"] = stock.get(size_key, 0) > 0
            scored.append((score, item))
        scored.sort(key=lambda pair: pair[0], reverse=True)
        return [item for _, item in scored[:limit]]

    def delivery_fee(self, wilaya: str) -> dict[str, Any] | None:
        key = wilaya.strip()
        with self._lock:
            if key.isdigit():
                zone = self._conn.execute("SELECT * FROM delivery_zones WHERE code = ?", (int(key),)).fetchone()
            else:
                zone = self._conn.execute(
                    """SELECT z.* FROM delivery_aliases a JOIN delivery_zones z ON z.code = a.code
                       WHERE a.alias = ?""",
                    (normalize(key),),
                ).fetchone()
        if zone is None:
            return None
        return {
            "wilaya": zone["name"],
            "code": zone["code"],
            "home_delivery_dzd": zone["home_dzd"],
            "stop_desk_dzd": zone["stop_desk_dzd"],
            "estimated_days": zone["days"],
        }

    def covered_wilayas(self) -> list[str]:
        with self._lock:
            rows = self._conn.execute("SELECT name FROM delivery_zones ORDER BY code").fetchall()
        return [r["name"] for r in rows]

    def order_status(self, order_id: str, phone_last4: str) -> dict[str, Any] | None:
        """Return the order only when the phone digits match, so order numbers cannot be enumerated."""
        oid = order_id.strip().upper().replace(" ", "")
        digits = re.sub(r"\D", "", phone_last4)[-4:]
        if not ORDER_ID_RE.match(oid) or len(digits) != 4:
            return None
        with self._lock:
            row = self._conn.execute("SELECT * FROM orders WHERE id = ?", (oid,)).fetchone()
        if row is None or row["phone_last4"] != digits:
            return None

        items = json.loads(row["items"])
        names = self._product_names([i["sku"] for i in items])
        return {
            "order_id": row["id"],
            "customer_first_name": row["customer_first_name"],
            "status": row["status"],
            "items": [{"name": names.get(i["sku"], i["sku"]), "size": i["size"], "color": i["color"], "qty": i["qty"]} for i in items],
            "total_dzd": row["total_dzd"],
            "wilaya": row["wilaya"],
            "delivery_type": row["delivery_type"],
            "created_at": row["created_at"],
            "shipped_at": row["shipped_at"],
            "expected_delivery": row["expected_delivery"],
            "tracking_code": row["tracking_code"],
        }

    def _product_names(self, skus: list[str]) -> dict[str, str]:
        if not skus:
            return {}
        placeholders = ",".join("?" for _ in skus)
        with self._lock:
            rows = self._conn.execute(f"SELECT sku, name FROM products WHERE sku IN ({placeholders})", skus).fetchall()
        return {r["sku"]: r["name"] for r in rows}

    def create_ticket(self, chat_id: int, username: str | None, reason: str) -> int:
        created_at = datetime.now(timezone.utc).isoformat(timespec="seconds")
        with self._tx() as conn:
            cur = conn.execute(
                "INSERT INTO support_tickets(chat_id, username, reason, created_at) VALUES (?, ?, ?, ?)",
                (chat_id, username, reason[:500], created_at),
            )
            if cur.lastrowid is None:
                raise RuntimeError("ticket insert returned no id")
            return cur.lastrowid


def open_store(db_path: Path, catalog_path: Path) -> Store:
    """Open the database and load the demo catalog the first time."""
    db_path.parent.mkdir(parents=True, exist_ok=True)
    store = Store(db_path)
    if store.is_empty():
        store.load_catalog(catalog_path)
    return store