"""Credential-free booking and reminder domain logic for the aiogram template.

The core deliberately uses only the Python standard library so it can be tested
without a Telegram token or network access. Timestamps are stored in UTC and
rendered in the configured IANA timezone at the edges.
"""

from __future__ import annotations

import sqlite3
import threading
from dataclasses import dataclass
from datetime import datetime, timedelta, timezone
from pathlib import Path
from zoneinfo import ZoneInfo


UTC = timezone.utc


def parse_local_datetime(value: str, timezone_name: str) -> datetime:
    """Parse an ISO/local value and return an aware UTC datetime."""
    raw = value.strip().replace(" ", "T", 1)
    parsed = datetime.fromisoformat(raw)
    if parsed.tzinfo is None:
        parsed = parsed.replace(tzinfo=ZoneInfo(timezone_name))
    return parsed.astimezone(UTC).replace(microsecond=0)


def iso_utc(value: datetime) -> str:
    """Serialize an aware datetime as a stable UTC ISO string."""
    if value.tzinfo is None:
        raise ValueError("datetime must be timezone-aware")
    return value.astimezone(UTC).replace(microsecond=0).isoformat().replace("+00:00", "Z")


def display_time(value: str, timezone_name: str) -> str:
    """Render a stored UTC ISO timestamp in the selected local timezone."""
    parsed = datetime.fromisoformat(value.replace("Z", "+00:00"))
    return parsed.astimezone(ZoneInfo(timezone_name)).strftime("%a %d %b, %H:%M %Z")


@dataclass(frozen=True)
class Slot:
    id: int
    starts_at: str
    capacity: int
    booked: int

    @property
    def remaining(self) -> int:
        return max(0, self.capacity - self.booked)


@dataclass(frozen=True)
class Booking:
    id: int
    slot_id: int
    starts_at: str
    user_id: int
    user_name: str
    status: str


class BookingStore:
    """Small SQLite repository with transaction-safe capacity checks."""

    def __init__(self, path: str | Path = "bookings.sqlite3") -> None:
        self._lock = threading.RLock()
        self._connection = sqlite3.connect(path, check_same_thread=False)
        self._connection.row_factory = sqlite3.Row
        self._connection.execute("PRAGMA foreign_keys = ON")
        self._connection.execute("PRAGMA journal_mode = WAL")
        self._connection.executescript(
            """
            CREATE TABLE IF NOT EXISTS slots (
                id INTEGER PRIMARY KEY,
                starts_at TEXT NOT NULL UNIQUE,
                capacity INTEGER NOT NULL CHECK (capacity > 0)
            );
            CREATE TABLE IF NOT EXISTS bookings (
                id INTEGER PRIMARY KEY,
                slot_id INTEGER NOT NULL REFERENCES slots(id) ON DELETE CASCADE,
                user_id INTEGER NOT NULL,
                user_name TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'booked'
                    CHECK (status IN ('booked', 'cancelled')),
                created_at TEXT NOT NULL,
                UNIQUE(slot_id, user_id)
            );
            CREATE INDEX IF NOT EXISTS bookings_user_idx
                ON bookings(user_id, status);
            CREATE TABLE IF NOT EXISTS reminders (
                slot_id INTEGER NOT NULL REFERENCES slots(id) ON DELETE CASCADE,
                user_id INTEGER NOT NULL,
                kind TEXT NOT NULL,
                sent_at TEXT NOT NULL,
                PRIMARY KEY(slot_id, user_id, kind)
            );
            """
        )
        self._connection.commit()

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

    def add_slot(self, starts_at: datetime, capacity: int = 1) -> int:
        if capacity < 1:
            raise ValueError("capacity must be positive")
        stamp = iso_utc(starts_at)
        with self._lock:
            cursor = self._connection.execute(
                "INSERT OR IGNORE INTO slots(starts_at, capacity) VALUES (?, ?)",
                (stamp, capacity),
            )
            self._connection.commit()
            if cursor.lastrowid:
                return int(cursor.lastrowid)
            row = self._connection.execute(
                "SELECT id FROM slots WHERE starts_at = ?", (stamp,)
            ).fetchone()
            assert row is not None
            return int(row["id"])

    def list_available(self, now: datetime, limit: int = 12) -> list[Slot]:
        if limit < 1:
            return []
        stamp = iso_utc(now)
        with self._lock:
            rows = self._connection.execute(
                """
                SELECT s.id, s.starts_at, s.capacity,
                       COUNT(CASE WHEN b.status = 'booked' THEN 1 END) AS booked
                FROM slots AS s
                LEFT JOIN bookings AS b ON b.slot_id = s.id
                WHERE s.starts_at > ?
                GROUP BY s.id
                HAVING booked < s.capacity
                ORDER BY s.starts_at
                LIMIT ?
                """,
                (stamp, limit),
            ).fetchall()
        return [Slot(int(r["id"]), r["starts_at"], int(r["capacity"]), int(r["booked"])) for r in rows]

    def book(self, slot_id: int, user_id: int, user_name: str, now: datetime) -> tuple[bool, str]:
        stamp = iso_utc(now)
        with self._lock:
            self._connection.execute("BEGIN IMMEDIATE")
            try:
                slot = self._connection.execute(
                    "SELECT capacity FROM slots WHERE id = ?", (slot_id,)
                ).fetchone()
                if slot is None:
                    self._connection.rollback()
                    return False, "slot_not_found"
                existing = self._connection.execute(
                    "SELECT status FROM bookings WHERE slot_id = ? AND user_id = ?",
                    (slot_id, user_id),
                ).fetchone()
                if existing is not None and existing["status"] == "booked":
                    self._connection.rollback()
                    return False, "already_booked"
                booked = self._connection.execute(
                    "SELECT COUNT(*) AS count FROM bookings WHERE slot_id = ? AND status = 'booked'",
                    (slot_id,),
                ).fetchone()["count"]
                if int(booked) >= int(slot["capacity"]):
                    self._connection.rollback()
                    return False, "sold_out"
                if existing is None:
                    self._connection.execute(
                        "INSERT INTO bookings(slot_id, user_id, user_name, created_at) VALUES (?, ?, ?, ?)",
                        (slot_id, user_id, user_name[:120], stamp),
                    )
                else:
                    self._connection.execute(
                        "UPDATE bookings SET user_name = ?, status = 'booked', created_at = ? "
                        "WHERE slot_id = ? AND user_id = ?",
                        (user_name[:120], stamp, slot_id, user_id),
                    )
                self._connection.commit()
                return True, "booked"
            except Exception:
                self._connection.rollback()
                raise

    def cancel(self, slot_id: int, user_id: int) -> bool:
        with self._lock:
            cursor = self._connection.execute(
                "UPDATE bookings SET status = 'cancelled' "
                "WHERE slot_id = ? AND user_id = ? AND status = 'booked'",
                (slot_id, user_id),
            )
            self._connection.commit()
            return cursor.rowcount == 1

    def for_user(self, user_id: int, now: datetime) -> list[Booking]:
        with self._lock:
            rows = self._connection.execute(
                """
                SELECT b.id, b.slot_id, s.starts_at, b.user_id, b.user_name, b.status
                FROM bookings AS b JOIN slots AS s ON s.id = b.slot_id
                WHERE b.user_id = ? AND b.status = 'booked' AND s.starts_at > ?
                ORDER BY s.starts_at
                """,
                (user_id, iso_utc(now)),
            ).fetchall()
        return [Booking(int(r["id"]), int(r["slot_id"]), r["starts_at"], int(r["user_id"]), r["user_name"], r["status"]) for r in rows]

    def claim_reminders(self, now: datetime, lead_minutes: int = 30) -> list[Booking]:
        """Atomically claim each upcoming reminder once across worker restarts."""
        start = iso_utc(now)
        end = iso_utc(now + timedelta(minutes=lead_minutes))
        claimed: list[Booking] = []
        with self._lock:
            rows = self._connection.execute(
                """
                SELECT b.id, b.slot_id, s.starts_at, b.user_id, b.user_name, b.status
                FROM bookings AS b JOIN slots AS s ON s.id = b.slot_id
                LEFT JOIN reminders AS r ON r.slot_id = b.slot_id
                    AND r.user_id = b.user_id AND r.kind = 'upcoming'
                WHERE b.status = 'booked' AND s.starts_at > ? AND s.starts_at <= ?
                    AND r.slot_id IS NULL
                ORDER BY s.starts_at
                """,
                (start, end),
            ).fetchall()
            for row in rows:
                self._connection.execute(
                    "INSERT OR IGNORE INTO reminders(slot_id, user_id, kind, sent_at) VALUES (?, ?, 'upcoming', ?)",
                    (row["slot_id"], row["user_id"], start),
                )
                if self._connection.execute("SELECT changes()").fetchone()[0] == 1:
                    claimed.append(Booking(int(row["id"]), int(row["slot_id"]), row["starts_at"], int(row["user_id"]), row["user_name"], row["status"]))
            self._connection.commit()
        return claimed