from __future__ import annotations

import json
import sqlite3
import threading
from contextlib import contextmanager
from datetime import datetime, timezone
from pathlib import Path
from typing import Any, Iterator
from uuid import uuid4


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


class Database:
    def __init__(self, path: Path):
        self.path = path
        self.path.parent.mkdir(parents=True, exist_ok=True)
        self._lock = threading.RLock()

    @contextmanager
    def connection(self) -> Iterator[sqlite3.Connection]:
        with self._lock:
            connection = sqlite3.connect(self.path, timeout=15)
            connection.row_factory = sqlite3.Row
            connection.execute("PRAGMA busy_timeout = 15000")
            connection.execute("PRAGMA foreign_keys = ON")
            try:
                yield connection
                connection.commit()
            finally:
                connection.close()

    def initialize(self) -> None:
        with self.connection() as db:
            # The admin and public widget may run in separate Passenger apps.
            # WAL permits concurrent readers while one process writes.
            db.execute("PRAGMA journal_mode = WAL")
            db.execute("PRAGMA synchronous = NORMAL")
            db.executescript(
                """
                CREATE TABLE IF NOT EXISTS journeys (
                    key TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    description TEXT NOT NULL DEFAULT '',
                    draft_version INTEGER NOT NULL,
                    published_version INTEGER,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS journey_versions (
                    journey_key TEXT NOT NULL,
                    version INTEGER NOT NULL,
                    state TEXT NOT NULL CHECK(state IN ('draft', 'published', 'archived')),
                    definition_json TEXT NOT NULL,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL,
                    PRIMARY KEY (journey_key, version),
                    FOREIGN KEY (journey_key) REFERENCES journeys(key) ON DELETE CASCADE
                );

                CREATE TABLE IF NOT EXISTS sessions (
                    id TEXT PRIMARY KEY,
                    root_journey_key TEXT NOT NULL,
                    journey_key TEXT NOT NULL,
                    version INTEGER NOT NULL,
                    mode TEXT NOT NULL,
                    current_node TEXT,
                    variables_json TEXT NOT NULL,
                    stack_json TEXT NOT NULL,
                    status TEXT NOT NULL,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS events (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    session_id TEXT NOT NULL,
                    event_type TEXT NOT NULL,
                    payload_json TEXT NOT NULL,
                    created_at TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS settings (
                    key TEXT PRIMARY KEY,
                    value_json TEXT NOT NULL,
                    updated_at TEXT NOT NULL
                );

                CREATE TABLE IF NOT EXISTS taxonomy_terms (
                    taxonomy TEXT NOT NULL CHECK(taxonomy IN ('category', 'tag', 'brand')),
                    slug TEXT NOT NULL,
                    name_fa TEXT NOT NULL,
                    aliases_json TEXT NOT NULL DEFAULT '[]',
                    source TEXT NOT NULL CHECK(source IN ('manual', 'woocommerce')),
                    updated_at TEXT NOT NULL,
                    PRIMARY KEY (taxonomy, slug)
                );
                """
            )

    @staticmethod
    def _dumps(value: Any) -> str:
        return json.dumps(value, ensure_ascii=False, separators=(",", ":"), sort_keys=True)

    @staticmethod
    def _loads(value: str) -> Any:
        return json.loads(value)

    def count_journeys(self) -> int:
        with self.connection() as db:
            return int(db.execute("SELECT COUNT(*) FROM journeys").fetchone()[0])

    def create_journey(self, definition: dict[str, Any]) -> dict[str, Any]:
        journey = definition["journey"]
        key = str(journey["key"])
        now = utc_now()
        with self.connection() as db:
            if db.execute("SELECT 1 FROM journeys WHERE key = ?", (key,)).fetchone():
                raise ValueError("Journey با این key از قبل وجود دارد.")
            db.execute(
                """
                INSERT INTO journeys(key, name, description, draft_version, published_version, created_at, updated_at)
                VALUES(?, ?, ?, 1, NULL, ?, ?)
                """,
                (key, str(journey["name"]), str(journey.get("description", "")), now, now),
            )
            db.execute(
                """
                INSERT INTO journey_versions(journey_key, version, state, definition_json, created_at, updated_at)
                VALUES(?, 1, 'draft', ?, ?, ?)
                """,
                (key, self._dumps(definition), now, now),
            )
        return self.get_journey(key)

    def list_journeys(self) -> list[dict[str, Any]]:
        with self.connection() as db:
            rows = db.execute(
                "SELECT * FROM journeys ORDER BY updated_at DESC, name ASC"
            ).fetchall()
            return [self._journey_summary(db, row) for row in rows]

    def _journey_summary(self, db: sqlite3.Connection, row: sqlite3.Row) -> dict[str, Any]:
        draft = db.execute(
            "SELECT definition_json FROM journey_versions WHERE journey_key = ? AND version = ?",
            (row["key"], row["draft_version"]),
        ).fetchone()
        draft_definition = self._loads(draft["definition_json"]) if draft else {}
        published_definition: dict[str, Any] | None = None
        if row["published_version"]:
            published = db.execute(
                "SELECT definition_json FROM journey_versions WHERE journey_key = ? AND version = ?",
                (row["key"], row["published_version"]),
            ).fetchone()
            if published:
                published_definition = self._loads(published["definition_json"])
        changed = published_definition is None or self._dumps(draft_definition) != self._dumps(published_definition)
        return {
            "key": row["key"],
            "name": row["name"],
            "description": row["description"],
            "draft_version": row["draft_version"],
            "published_version": row["published_version"],
            "status": "published" if row["published_version"] else "draft",
            "has_unpublished_changes": changed,
            "node_count": len(draft_definition.get("nodes", [])),
            "updated_at": row["updated_at"],
            "created_at": row["created_at"],
        }

    def get_journey(self, key: str, version: int | None = None, mode: str = "draft") -> dict[str, Any]:
        with self.connection() as db:
            row = db.execute("SELECT * FROM journeys WHERE key = ?", (key,)).fetchone()
            if row is None:
                raise KeyError(key)
            selected_version = version
            if selected_version is None:
                selected_version = (
                    row["published_version"] if mode == "published" else row["draft_version"]
                )
            if selected_version is None:
                raise KeyError(f"No {mode} version for {key}")
            version_row = db.execute(
                "SELECT * FROM journey_versions WHERE journey_key = ? AND version = ?",
                (key, selected_version),
            ).fetchone()
            if version_row is None:
                raise KeyError(f"{key}@{selected_version}")
            return {
                **self._journey_summary(db, row),
                "version": selected_version,
                "version_state": version_row["state"],
                "definition": self._loads(version_row["definition_json"]),
            }

    def update_draft(self, key: str, definition: dict[str, Any]) -> dict[str, Any]:
        now = utc_now()
        journey = definition["journey"]
        if str(journey["key"]) != key:
            raise ValueError("journey.key با آدرس Journey مطابقت ندارد.")
        with self.connection() as db:
            row = db.execute("SELECT * FROM journeys WHERE key = ?", (key,)).fetchone()
            if row is None:
                raise KeyError(key)
            db.execute(
                """
                UPDATE journey_versions SET definition_json = ?, updated_at = ?
                WHERE journey_key = ? AND version = ?
                """,
                (self._dumps(definition), now, key, row["draft_version"]),
            )
            db.execute(
                "UPDATE journeys SET name = ?, description = ?, updated_at = ? WHERE key = ?",
                (str(journey["name"]), str(journey.get("description", "")), now, key),
            )
        return self.get_journey(key)

    def publish(self, key: str) -> dict[str, Any]:
        now = utc_now()
        with self.connection() as db:
            row = db.execute("SELECT * FROM journeys WHERE key = ?", (key,)).fetchone()
            if row is None:
                raise KeyError(key)
            draft_version = int(row["draft_version"])
            draft = db.execute(
                "SELECT definition_json FROM journey_versions WHERE journey_key = ? AND version = ?",
                (key, draft_version),
            ).fetchone()
            if draft is None:
                raise KeyError(f"{key}@{draft_version}")
            if row["published_version"]:
                db.execute(
                    """
                    UPDATE journey_versions SET state = 'archived', updated_at = ?
                    WHERE journey_key = ? AND version = ?
                    """,
                    (now, key, row["published_version"]),
                )
            db.execute(
                """
                UPDATE journey_versions SET state = 'published', updated_at = ?
                WHERE journey_key = ? AND version = ?
                """,
                (now, key, draft_version),
            )
            next_draft = draft_version + 1
            db.execute(
                """
                INSERT INTO journey_versions(journey_key, version, state, definition_json, created_at, updated_at)
                VALUES(?, ?, 'draft', ?, ?, ?)
                """,
                (key, next_draft, draft["definition_json"], now, now),
            )
            db.execute(
                """
                UPDATE journeys SET draft_version = ?, published_version = ?, updated_at = ?
                WHERE key = ?
                """,
                (next_draft, draft_version, now, key),
            )
        return self.get_journey(key)

    def duplicate_journey(self, source_key: str, new_key: str, new_name: str) -> dict[str, Any]:
        source = self.get_journey(source_key)
        definition = deepcopy_dict(source["definition"])
        definition["journey"]["key"] = new_key
        definition["journey"]["name"] = new_name
        return self.create_journey(definition)

    def create_session(self, journey_key: str, mode: str, definition: dict[str, Any], version: int) -> dict[str, Any]:
        session_id = uuid4().hex
        now = utc_now()
        current_node = str(definition["journey"]["start"])
        with self.connection() as db:
            db.execute(
                """
                INSERT INTO sessions(
                    id, root_journey_key, journey_key, version, mode, current_node,
                    variables_json, stack_json, status, created_at, updated_at
                ) VALUES(?, ?, ?, ?, ?, ?, '{}', '[]', 'active', ?, ?)
                """,
                (session_id, journey_key, journey_key, version, mode, current_node, now, now),
            )
        self.log_event(session_id, "journey_started", {"journey_key": journey_key, "version": version, "mode": mode})
        return self.get_session(session_id)

    def get_session(self, session_id: str) -> dict[str, Any]:
        with self.connection() as db:
            row = db.execute("SELECT * FROM sessions WHERE id = ?", (session_id,)).fetchone()
            if row is None:
                raise KeyError(session_id)
            return {
                "id": row["id"],
                "root_journey_key": row["root_journey_key"],
                "journey_key": row["journey_key"],
                "version": row["version"],
                "mode": row["mode"],
                "current_node": row["current_node"],
                "variables": self._loads(row["variables_json"]),
                "stack": self._loads(row["stack_json"]),
                "status": row["status"],
                "created_at": row["created_at"],
                "updated_at": row["updated_at"],
            }

    def update_session(
        self,
        session_id: str,
        *,
        journey_key: str,
        version: int,
        current_node: str | None,
        variables: dict[str, Any],
        stack: list[dict[str, Any]],
        status: str,
    ) -> None:
        with self.connection() as db:
            db.execute(
                """
                UPDATE sessions
                SET journey_key = ?, version = ?, current_node = ?, variables_json = ?,
                    stack_json = ?, status = ?, updated_at = ?
                WHERE id = ?
                """,
                (
                    journey_key,
                    version,
                    current_node,
                    self._dumps(variables),
                    self._dumps(stack),
                    status,
                    utc_now(),
                    session_id,
                ),
            )

    def log_event(self, session_id: str, event_type: str, payload: dict[str, Any]) -> None:
        with self.connection() as db:
            db.execute(
                "INSERT INTO events(session_id, event_type, payload_json, created_at) VALUES(?, ?, ?, ?)",
                (session_id, event_type, self._dumps(payload), utc_now()),
            )

    def event_count(self) -> int:
        with self.connection() as db:
            return int(db.execute("SELECT COUNT(*) FROM events").fetchone()[0])

    def get_session_events(self, session_id: str) -> list[dict[str, Any]]:
        with self.connection() as db:
            rows = db.execute(
                "SELECT event_type, payload_json, created_at FROM events WHERE session_id = ? ORDER BY id ASC",
                (session_id,),
            ).fetchall()
            return [
                {
                    "event_type": row["event_type"],
                    "payload": self._loads(row["payload_json"]),
                    "created_at": row["created_at"],
                }
                for row in rows
            ]

    def get_setting(self, key: str, default: Any = None) -> Any:
        with self.connection() as db:
            row = db.execute("SELECT value_json FROM settings WHERE key = ?", (key,)).fetchone()
            return default if row is None else self._loads(row["value_json"])

    def set_setting(self, key: str, value: Any) -> Any:
        now = utc_now()
        with self.connection() as db:
            db.execute(
                """
                INSERT INTO settings(key, value_json, updated_at) VALUES(?, ?, ?)
                ON CONFLICT(key) DO UPDATE SET value_json = excluded.value_json, updated_at = excluded.updated_at
                """,
                (key, self._dumps(value), now),
            )
        return value

    def list_terms(self, taxonomy: str | None = None) -> list[dict[str, Any]]:
        with self.connection() as db:
            rows = db.execute(
                "SELECT * FROM taxonomy_terms WHERE (? IS NULL OR taxonomy = ?) ORDER BY taxonomy, name_fa, slug",
                (taxonomy, taxonomy),
            ).fetchall()
        return [{"taxonomy": row["taxonomy"], "slug": row["slug"],
                 "name_fa": row["name_fa"], "aliases": self._loads(row["aliases_json"]),
                 "source": row["source"], "updated_at": row["updated_at"]} for row in rows]

    def save_term(self, taxonomy: str, slug: str, name_fa: str,
                  aliases: list[str], source: str = "manual") -> dict[str, Any]:
        with self.connection() as db:
            db.execute(
                """INSERT INTO taxonomy_terms(taxonomy, slug, name_fa, aliases_json, source, updated_at)
                   VALUES(?, ?, ?, ?, ?, ?)
                   ON CONFLICT(taxonomy, slug) DO UPDATE SET
                     name_fa=excluded.name_fa, aliases_json=excluded.aliases_json,
                     source=taxonomy_terms.source, updated_at=excluded.updated_at""",
                (taxonomy, slug, name_fa, self._dumps(aliases), source, utc_now()),
            )
        return next(term for term in self.list_terms(taxonomy) if term["slug"] == slug)

    def sync_terms(self, taxonomy: str, terms: list[dict[str, str]]) -> int:
        """Preserve admin-maintained names and aliases while refreshing source terms."""
        with self.connection() as db:
            for term in terms:
                db.execute(
                    """INSERT INTO taxonomy_terms(taxonomy, slug, name_fa, aliases_json, source, updated_at)
                       VALUES(?, ?, ?, '[]', 'woocommerce', ?)
                       ON CONFLICT(taxonomy, slug) DO UPDATE SET
                         source='woocommerce', updated_at=excluded.updated_at""",
                    (taxonomy, term["slug"], term["name_fa"], utc_now()),
                )
        return len(terms)

    def import_terms(self, terms: list[dict[str, Any]]) -> int:
        """Replace manually supplied labels/aliases as one transaction."""
        with self.connection() as db:
            for term in terms:
                db.execute(
                    """INSERT INTO taxonomy_terms(taxonomy, slug, name_fa, aliases_json, source, updated_at)
                       VALUES(?, ?, ?, ?, 'manual', ?)
                       ON CONFLICT(taxonomy, slug) DO UPDATE SET
                         name_fa=excluded.name_fa, aliases_json=excluded.aliases_json,
                         updated_at=excluded.updated_at""",
                    (term["taxonomy"], term["slug"], term["name_fa"],
                     self._dumps(term["aliases"]), utc_now()),
                )
        return len(terms)

    def delete_term(self, taxonomy: str, slug: str) -> None:
        with self.connection() as db:
            cursor = db.execute("DELETE FROM taxonomy_terms WHERE taxonomy = ? AND slug = ?", (taxonomy, slug))
            if cursor.rowcount == 0:
                raise KeyError(slug)


def deepcopy_dict(value: dict[str, Any]) -> dict[str, Any]:
    return json.loads(json.dumps(value, ensure_ascii=False))
