"""Durable Journey-container persistence, isolated from Activity migrations."""

from __future__ import annotations

import hashlib
import re
import sqlite3
import uuid
from contextlib import contextmanager
from datetime import datetime, timedelta, timezone
from importlib import resources
from pathlib import Path
from typing import Iterator

from .contracts import Journey, JourneyActivityIntent, JourneyDateIntent, JourneyDateIntentKind

MIGRATION_PACKAGE = "mountain_twin.journey.migrations"


class JourneyMigrationChecksumMismatchError(RuntimeError):
    pass


class JourneySqliteDatabase:
    """Journey migration host sharing an explicit local Aventurro SQLite file."""

    def __init__(self, path: str | Path):
        self.path = str(path)

    def connect(self) -> sqlite3.Connection:
        connection = sqlite3.connect(self.path, isolation_level=None)
        connection.row_factory = sqlite3.Row
        connection.execute("PRAGMA foreign_keys = ON")
        # AV-054 (D6): requests wait for a write in progress instead of failing.
        connection.execute("PRAGMA busy_timeout = 5000")
        if self.path != ":memory:":
            connection.execute("PRAGMA journal_mode = WAL")
        apply_journey_migrations(connection)
        return connection


def apply_journey_migrations(connection: sqlite3.Connection) -> None:
    """Apply Journey-owned migrations without sharing Activity's version ledger."""

    connection.execute(
        """CREATE TABLE IF NOT EXISTS journey_schema_migrations (
            version INTEGER PRIMARY KEY,
            checksum TEXT NOT NULL,
            applied_at TEXT NOT NULL
        )"""
    )
    for version, name, sql in _migration_resources():
        checksum = hashlib.sha256(sql.encode()).hexdigest()
        applied = connection.execute(
            "SELECT checksum FROM journey_schema_migrations WHERE version = ?", (version,)
        ).fetchone()
        if applied is not None:
            if applied["checksum"] != checksum:
                raise JourneyMigrationChecksumMismatchError(
                    f"Journey migration {version:03d} checksum differs from applied migration"
                )
            continue
        quoted_checksum = checksum.replace("'", "''")
        applied_at = _now().replace("'", "''")
        connection.executescript(
            "BEGIN IMMEDIATE;\n"
            + sql
            + "\nINSERT INTO journey_schema_migrations(version, checksum, applied_at) VALUES "
            + f"({version}, '{quoted_checksum}', '{applied_at}');\nCOMMIT;"
        )


def journey_schema_version(connection: sqlite3.Connection) -> int:
    row = connection.execute(
        "SELECT COALESCE(MAX(version), 0) AS version FROM journey_schema_migrations"
    ).fetchone()
    return int(row["version"])


def _migration_resources() -> tuple[tuple[int, str, str], ...]:
    candidates = []
    for resource in resources.files(MIGRATION_PACKAGE).iterdir():
        match = re.fullmatch(r"(\d{3})_[a-z0-9_]+\.sql", resource.name)
        if match:
            candidates.append((int(match[1]), resource.name, resource.read_text(encoding="utf-8")))
    return tuple(sorted(candidates))


@contextmanager
def _transaction(connection: sqlite3.Connection) -> Iterator[None]:
    connection.execute("BEGIN IMMEDIATE")
    try:
        yield
    except Exception:
        connection.rollback()
        raise
    else:
        connection.commit()


class JourneySqliteRepository:
    """Explicit SQL repository for Journey container metadata only."""

    def __init__(self, database: JourneySqliteDatabase):
        self._connection = database.connect()

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

    def create(self, journey: Journey, *, creation_request_id: str | None = None) -> Journey:
        if journey.owner_id is None:
            raise ValueError("durable Journey requires an owner ID")
        if creation_request_id is not None:
            _validate_identifier(creation_request_id, "creation request")
        with _transaction(self._connection):
            self._connection.execute(
                """INSERT INTO journeys(
                       journey_id, owner_id, creation_request_id, title, destination_label,
                       country_label, date_kind, date_year, date_month, date_start, date_end,
                       activity_intent, created_at, updated_at
                   ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                _journey_values(journey, creation_request_id),
            )
        return journey

    def get(self, *, owner_id: str, journey_id: str) -> Journey:
        _validate_identifier(owner_id, "owner")
        _validate_identifier(journey_id, "journey")
        row = self._connection.execute(
            "SELECT * FROM journeys WHERE owner_id = ? AND journey_id = ?", (owner_id, journey_id)
        ).fetchone()
        if row is None:
            raise KeyError(journey_id)
        return _journey_from_row(row)

    def list(self, *, owner_id: str) -> tuple[Journey, ...]:
        _validate_identifier(owner_id, "owner")
        rows = self._connection.execute(
            "SELECT * FROM journeys WHERE owner_id = ? ORDER BY updated_at DESC, journey_id",
            (owner_id,),
        ).fetchall()
        return tuple(_journey_from_row(row) for row in rows)

    def get_by_creation_request(self, *, owner_id: str, creation_request_id: str) -> Journey | None:
        _validate_identifier(owner_id, "owner")
        _validate_identifier(creation_request_id, "creation request")
        row = self._connection.execute(
            "SELECT * FROM journeys WHERE owner_id = ? AND creation_request_id = ?",
            (owner_id, creation_request_id),
        ).fetchone()
        return None if row is None else _journey_from_row(row)

    def update_metadata(self, journey: Journey) -> Journey:
        if journey.owner_id is None:
            raise ValueError("durable Journey requires an owner ID")
        with _transaction(self._connection):
            cursor = self._connection.execute(
                """UPDATE journeys
                      SET title = ?, destination_label = ?, country_label = ?, date_kind = ?,
                          date_year = ?, date_month = ?, date_start = ?, date_end = ?,
                          activity_intent = ?, updated_at = ?
                    WHERE owner_id = ? AND journey_id = ?""",
                _metadata_update_values(journey),
            )
            if cursor.rowcount != 1:
                raise KeyError(journey.journey_id)
        return journey

    def set_completed(self, *, owner_id: str, journey_id: str, completed: bool) -> Journey:
        """AV-027: the owner marks the Journey as done (now) or takes it back
        to upcoming (NULL). Only this explicit call changes it; updated_at
        is left alone, so the Journey keeps its place in the list."""
        _validate_identifier(owner_id, "owner")
        _validate_identifier(journey_id, "journey")
        with _transaction(self._connection):
            cursor = self._connection.execute(
                "UPDATE journeys SET completed_at = ? WHERE owner_id = ? AND journey_id = ?",
                (_now() if completed else None, owner_id, journey_id),
            )
            if cursor.rowcount != 1:
                raise KeyError(journey_id)
        return self.get(owner_id=owner_id, journey_id=journey_id)

    def delete(self, *, owner_id: str, journey_id: str) -> None:
        """Delete a durable Journey with every route revision and plan
        version attached to it, in one transaction (docs/
        journey_edit_delete_v0_1_design.md section 1). The FKs from
        migration 002 have no ON DELETE CASCADE and are circular
        (journeys.current_route_revision_id/current_plan_id point back at
        journey_routes/journey_plans), so rows go in dependency order:
        clear the current pointers, then plans, then routes, then the
        Journey itself -- with foreign_keys enforced at every step."""
        _validate_identifier(owner_id, "owner")
        _validate_identifier(journey_id, "journey")
        with _transaction(self._connection):
            cursor = self._connection.execute(
                """UPDATE journeys SET current_plan_id = NULL, current_route_revision_id = NULL
                    WHERE owner_id = ? AND journey_id = ?""",
                (owner_id, journey_id),
            )
            if cursor.rowcount != 1:
                raise KeyError(journey_id)
            self._connection.execute(
                "DELETE FROM journey_plans WHERE journey_id = ?", (journey_id,)
            )
            # AV-053: an imported route's source file goes with its revisions.
            self._connection.execute(
                """DELETE FROM journey_route_imports WHERE route_revision_id IN
                       (SELECT route_revision_id FROM journey_routes WHERE journey_id = ?)""",
                (journey_id,),
            )
            self._connection.execute(
                "DELETE FROM journey_routes WHERE journey_id = ?", (journey_id,)
            )
            # The calendar's own rows (travel legs, migration 005; lodging and
            # other events, migration 011) go with their Journey.
            self._connection.execute(
                "DELETE FROM journey_travel_legs WHERE journey_id = ?", (journey_id,)
            )
            self._connection.execute(
                "DELETE FROM journey_calendar_events WHERE journey_id = ?", (journey_id,)
            )
            self._connection.execute(
                "DELETE FROM journeys WHERE owner_id = ? AND journey_id = ?", (owner_id, journey_id)
            )
            # AV-054 E2: a tombstone for synchronising devices (30 days).
            self._connection.execute(
                "INSERT OR REPLACE INTO journey_deletions(journey_id, owner_id, deleted_at)"
                " VALUES (?, ?, ?)",
                (journey_id, owner_id, _now()),
            )
            self._connection.execute(
                "DELETE FROM journey_deletions WHERE deleted_at < ?",
                ((datetime.now(timezone.utc) - timedelta(days=30)).isoformat(),),
            )


def new_journey_id() -> str:
    return f"journey-v0_1:{uuid.uuid4()}"


def _journey_values(journey: Journey, creation_request_id: str | None) -> tuple[object, ...]:
    date_values = _date_values(journey.date_intent)
    return (
        journey.journey_id,
        journey.owner_id,
        creation_request_id,
        journey.title,
        journey.destination_label,
        journey.country_label,
        *date_values,
        None if journey.activity_intent is None else journey.activity_intent.value,
        journey.created_at,
        journey.updated_at,
    )


def _metadata_update_values(journey: Journey) -> tuple[object, ...]:
    return (
        journey.title,
        journey.destination_label,
        journey.country_label,
        *_date_values(journey.date_intent),
        None if journey.activity_intent is None else journey.activity_intent.value,
        journey.updated_at,
        journey.owner_id,
        journey.journey_id,
    )


def _date_values(intent: JourneyDateIntent) -> tuple[object, ...]:
    return (
        intent.kind.value,
        intent.year,
        intent.month,
        None if intent.start_date is None else intent.start_date.isoformat(),
        None if intent.end_date is None else intent.end_date.isoformat(),
    )


def _journey_from_row(row: sqlite3.Row) -> Journey:
    kind = JourneyDateIntentKind(row["date_kind"])
    if kind is JourneyDateIntentKind.UNSPECIFIED:
        date_intent = JourneyDateIntent.unspecified()
    elif kind is JourneyDateIntentKind.MONTH:
        date_intent = JourneyDateIntent.month_intent(row["date_year"], row["date_month"])
    else:
        date_intent = JourneyDateIntent.date_range(
            datetime.fromisoformat(row["date_start"]).date(),
            datetime.fromisoformat(row["date_end"]).date(),
        )
    return Journey(
        journey_id=row["journey_id"],
        title=row["title"],
        # Present since migration 002 (mountain_twin/journey/migrations/
        # 002_route_persistence.sql); NULL for a Journey with no attached
        # route/plan yet, exactly like current_analysis_run_id below.
        current_plan_id=row["current_plan_id"],
        owner_id=row["owner_id"],
        destination_label=row["destination_label"],
        country_label=row["country_label"],
        date_intent=date_intent,
        activity_intent=None
        if row["activity_intent"] is None
        else JourneyActivityIntent(row["activity_intent"]),
        created_at=row["created_at"],
        updated_at=row["updated_at"],
        current_route_revision_id=row["current_route_revision_id"],
        completed_at=row["completed_at"],
    )


def _validate_identifier(value: str, label: str) -> None:
    if not value or value != value.strip() or len(value) > 256:
        raise ValueError(f"{label} identifier is malformed")


def _now() -> str:
    return datetime.now(timezone.utc).isoformat()