from __future__ import annotations

import sqlite3
from dataclasses import dataclass
from datetime import datetime
from pathlib import Path

SCHEMA = """
CREATE TABLE IF NOT EXISTS alerts (
    key TEXT PRIMARY KEY,
    source TEXT NOT NULL,
    title TEXT NOT NULL,
    first_seen TEXT NOT NULL,
    last_alerted TEXT NOT NULL,
    reminded INTEGER NOT NULL DEFAULT 0,
    open INTEGER NOT NULL DEFAULT 1
)
"""


@dataclass(frozen=True)
class AlertRecord:
    key: str
    source: str
    title: str
    first_seen: datetime
    last_alerted: datetime
    reminded: bool
    open: bool


class StateStore:
    def __init__(self, path: Path) -> None:
        path.parent.mkdir(parents=True, exist_ok=True)
        self._conn = sqlite3.connect(path)
        self._conn.row_factory = sqlite3.Row
        self._conn.execute(SCHEMA)

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

    def load(self) -> dict[str, AlertRecord]:
        rows = self._conn.execute("SELECT * FROM alerts").fetchall()
        return {
            row["key"]: AlertRecord(
                key=row["key"],
                source=row["source"],
                title=row["title"],
                first_seen=datetime.fromisoformat(row["first_seen"]),
                last_alerted=datetime.fromisoformat(row["last_alerted"]),
                reminded=bool(row["reminded"]),
                open=bool(row["open"]),
            )
            for row in rows
        }

    def save(self, records: list[AlertRecord]) -> None:
        if not records:
            return
        with self._conn:
            self._conn.executemany(
                """
                INSERT INTO alerts (key, source, title, first_seen, last_alerted, reminded, open)
                VALUES (?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(key) DO UPDATE SET
                    source = excluded.source,
                    title = excluded.title,
                    first_seen = excluded.first_seen,
                    last_alerted = excluded.last_alerted,
                    reminded = excluded.reminded,
                    open = excluded.open
                """,
                [
                    (
                        r.key,
                        r.source,
                        r.title,
                        r.first_seen.isoformat(),
                        r.last_alerted.isoformat(),
                        int(r.reminded),
                        int(r.open),
                    )
                    for r in records
                ],
            )