from __future__ import annotations

import sqlite3
import threading
from datetime import UTC, datetime
from pathlib import Path
from typing import Any

SCHEMA = """
CREATE TABLE IF NOT EXISTS events (
    id TEXT PRIMARY KEY,
    created_at TEXT NOT NULL,
    session_id TEXT NOT NULL,
    classes TEXT NOT NULL,
    max_confidence REAL NOT NULL,
    detection_count INTEGER NOT NULL,
    status TEXT NOT NULL,
    notification_channels TEXT NOT NULL,
    handled_at TEXT
);
CREATE TABLE IF NOT EXISTS delivery_stats (
    channel TEXT PRIMARY KEY,
    delivered INTEGER NOT NULL DEFAULT 0,
    failed INTEGER NOT NULL DEFAULT 0,
    tests INTEGER NOT NULL DEFAULT 0,
    last_status TEXT NOT NULL DEFAULT 'idle',
    last_delivery_at TEXT,
    last_test_at TEXT,
    last_error TEXT
);
"""


def utc_now_iso() -> str:
    """Current UTC time as a sortable ISO string with a Z suffix."""
    return (
        datetime.now(UTC)
        .isoformat(timespec="seconds")
        .replace("+00:00", "Z")
    )


class Database:
    """Single shared SQLite connection guarded by a lock.

    The API service is low-write and multi-threaded (FastAPI threadpool,
    alert dispatch executor), so one connection with ``check_same_thread=False``
    plus a lock is simpler and safer on Windows than per-operation connections.
    """

    def __init__(self, path: str | Path) -> None:
        resolved = Path(path)
        resolved.parent.mkdir(parents=True, exist_ok=True)
        self._connection = sqlite3.connect(
            str(resolved),
            check_same_thread=False,
        )
        self._connection.row_factory = sqlite3.Row
        self._lock = threading.Lock()
        self._connection.execute("PRAGMA journal_mode=WAL")
        self._connection.execute("PRAGMA busy_timeout=5000")
        self._connection.execute("PRAGMA synchronous=NORMAL")
        self._connection.executescript(SCHEMA)
        self._connection.commit()

    def execute(
        self,
        statement: str,
        parameters: tuple[Any, ...] = (),
    ) -> sqlite3.Cursor:
        with self._lock:
            cursor = self._connection.execute(statement, parameters)
            self._connection.commit()
            return cursor

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


class DeliveryStatsStore:
    """Persistent per-channel delivery counters (read-through / write-through).

    Each ``record`` call applies an atomic UPSERT carrying 0/1 increments and
    the new ``last_*`` values, so there is no in-memory copy to drift from disk.
    """

    def __init__(self, db: Database) -> None:
        self._db = db

    def get_all(self) -> dict[str, dict[str, Any]]:
        rows = self._db.execute(
            "SELECT * FROM delivery_stats",
        ).fetchall()
        return {row["channel"]: dict(row) for row in rows}

    def record(
        self,
        channel: str,
        *,
        success: bool,
        error: str | None = None,
        test: bool = False,
    ) -> None:
        now = utc_now_iso()
        self._db.execute(
            """
            INSERT INTO delivery_stats (
                channel, delivered, failed, tests, last_status,
                last_delivery_at, last_test_at, last_error
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(channel) DO UPDATE SET
                delivered = delivered + excluded.delivered,
                failed = failed + excluded.failed,
                tests = tests + excluded.tests,
                last_status = excluded.last_status,
                last_delivery_at = COALESCE(
                    excluded.last_delivery_at,
                    delivery_stats.last_delivery_at
                ),
                last_test_at = COALESCE(
                    excluded.last_test_at,
                    delivery_stats.last_test_at
                ),
                last_error = excluded.last_error
            """,
            (
                channel,
                1 if success else 0,
                0 if success else 1,
                1 if test else 0,
                "success" if success else "failed",
                now if not test else None,
                now if test else None,
                None if success else error,
            ),
        )