"""Хранилище SQLite. Все запросы параметризованы; одноразовые токены гасятся атомарно."""
from __future__ import annotations

import os
import sqlite3
from contextlib import contextmanager
from typing import Iterator

SCHEMA_VERSION = 2          # 2: регистрация больше не хранит пароль (его задают после подтверждения почты)

_SCHEMA = """
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    pw_hash TEXT NOT NULL,
    created_at INTEGER NOT NULL
);
-- Ожидание подтверждения почты: пользователь появляется только после перехода по ссылке и выбора пароля.
CREATE TABLE IF NOT EXISTS registrations (
    email TEXT PRIMARY KEY,
    token_hash TEXT NOT NULL UNIQUE,
    expires_at INTEGER NOT NULL,
    created_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS reset_tokens (
    token_hash TEXT PRIMARY KEY,
    user_id INTEGER NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
    expires_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS sessions (
    token_hash TEXT PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at INTEGER NOT NULL,
    created_at INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS sessions_user ON sessions(user_id);
CREATE INDEX IF NOT EXISTS registrations_expires ON registrations(expires_at);
CREATE INDEX IF NOT EXISTS reset_tokens_expires ON reset_tokens(expires_at);
CREATE INDEX IF NOT EXISTS sessions_expires ON sessions(expires_at);
CREATE TABLE IF NOT EXISTS user_state (
    user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    data TEXT NOT NULL,
    version INTEGER NOT NULL,
    updated_at INTEGER NOT NULL
);
"""

_COUNTABLE = frozenset({"users", "registrations", "reset_tokens", "sessions", "user_state"})


class Store:
    def __init__(self, path: str) -> None:
        self._path = path
        os.makedirs(os.path.dirname(os.path.abspath(path)), exist_ok=True)
        self._init_schema()
        try:
            os.chmod(path, 0o600)
        except OSError:
            pass                                  # Windows и некоторые файловые системы не поддерживают права

    @contextmanager
    def _tx(self, immediate: bool = False) -> Iterator[sqlite3.Connection]:
        conn = sqlite3.connect(self._path, timeout=5, isolation_level=None)
        conn.row_factory = sqlite3.Row
        try:
            conn.execute("PRAGMA foreign_keys = ON")
            conn.execute("BEGIN IMMEDIATE" if immediate else "BEGIN")
            try:
                yield conn
            except BaseException:
                conn.execute("ROLLBACK")
                raise
            conn.execute("COMMIT")
        finally:
            conn.close()

    def _init_schema(self) -> None:
        conn = sqlite3.connect(self._path, timeout=5, isolation_level=None)
        try:
            conn.execute("PRAGMA journal_mode = WAL")
            version = conn.execute("PRAGMA user_version").fetchone()[0]
            if version < 2:
                # Ожидающие регистрации одноразовы и недолговечны: старые (с паролем) просто отбрасываем
                conn.execute("DROP TABLE IF EXISTS registrations")
            conn.executescript(_SCHEMA)
            conn.execute(f"PRAGMA user_version = {SCHEMA_VERSION}")
        finally:
            conn.close()

    # --- пользователи ---
    def create_user(self, email: str, pw_hash: str, now: float) -> int | None:
        try:
            with self._tx(immediate=True) as conn:
                return conn.execute(
                    "INSERT INTO users (email, pw_hash, created_at) VALUES (?, ?, ?)", (email, pw_hash, int(now))
                ).lastrowid
        except sqlite3.IntegrityError:
            return None

    def user_by_email(self, email: str) -> sqlite3.Row | None:
        with self._tx() as conn:
            return conn.execute("SELECT id, email, pw_hash FROM users WHERE email = ?", (email,)).fetchone()

    def set_password(self, user_id: int, pw_hash: str, expect_pw_hash: str | None = None) -> bool:
        """С expect_pw_hash меняет пароль, только если он всё ещё прежний (перехеширование не затирает сброс пароля)."""
        with self._tx(immediate=True) as conn:
            if expect_pw_hash is None:
                cursor = conn.execute("UPDATE users SET pw_hash = ? WHERE id = ?", (pw_hash, user_id))
            else:
                cursor = conn.execute("UPDATE users SET pw_hash = ? WHERE id = ? AND pw_hash = ?", (pw_hash, user_id, expect_pw_hash))
            return cursor.rowcount > 0

    def delete_user(self, user_id: int) -> None:
        """Удаляет пользователя вместе с сессиями, токенами и прогрессом (каскад внешних ключей)."""
        with self._tx(immediate=True) as conn:
            conn.execute("DELETE FROM users WHERE id = ?", (user_id,))

    # --- регистрация ---
    def save_registration(self, email: str, token_hash: str, expires_at: float, now: float) -> None:
        with self._tx(immediate=True) as conn:
            conn.execute(
                "INSERT OR REPLACE INTO registrations (email, token_hash, expires_at, created_at) VALUES (?, ?, ?, ?)",
                (email, token_hash, int(expires_at), int(now)),
            )

    def peek_registration(self, token_hash: str, now: float) -> str | None:
        with self._tx() as conn:
            row = conn.execute(
                "SELECT email FROM registrations WHERE token_hash = ? AND expires_at > ?", (token_hash, int(now))
            ).fetchone()
            return row["email"] if row else None

    def activate_registration(self, token_hash: str, pw_hash: str, now: float) -> bool:
        """Гасит ссылку и создаёт пользователя одной транзакцией. False: ссылка недействительна или адрес уже занят."""
        with self._tx(immediate=True) as conn:
            row = conn.execute(
                "DELETE FROM registrations WHERE token_hash = ? AND expires_at > ? RETURNING email", (token_hash, int(now))
            ).fetchone()
            if row is None:
                return False
            try:
                conn.execute(
                    "INSERT INTO users (email, pw_hash, created_at) VALUES (?, ?, ?)", (row["email"], pw_hash, int(now))
                )
            except sqlite3.IntegrityError:
                return False                      # неудавшийся INSERT не отменяет транзакцию: ссылка всё равно погашена
            return True

    # --- сброс пароля ---
    def save_reset_token(self, user_id: int, token_hash: str, expires_at: float) -> None:
        with self._tx(immediate=True) as conn:
            conn.execute("DELETE FROM reset_tokens WHERE user_id = ?", (user_id,))
            conn.execute(
                "INSERT INTO reset_tokens (token_hash, user_id, expires_at) VALUES (?, ?, ?)",
                (token_hash, user_id, int(expires_at)),
            )

    def peek_reset_token(self, token_hash: str, now: float) -> sqlite3.Row | None:
        with self._tx() as conn:
            return conn.execute(
                "SELECT u.id AS id, u.email AS email FROM reset_tokens t JOIN users u ON u.id = t.user_id "
                "WHERE t.token_hash = ? AND t.expires_at > ?",
                (token_hash, int(now)),
            ).fetchone()

    def reset_password(self, token_hash: str, pw_hash: str, now: float) -> sqlite3.Row | None:
        """Гасит токен, меняет пароль и завершает все сессии одной транзакцией. None: токен недействителен."""
        with self._tx(immediate=True) as conn:
            row = conn.execute(
                "DELETE FROM reset_tokens WHERE token_hash = ? AND expires_at > ? RETURNING user_id", (token_hash, int(now))
            ).fetchone()
            if row is None:
                return None
            conn.execute("UPDATE users SET pw_hash = ? WHERE id = ?", (pw_hash, row["user_id"]))
            conn.execute("DELETE FROM sessions WHERE user_id = ?", (row["user_id"],))
            return conn.execute("SELECT id, email FROM users WHERE id = ?", (row["user_id"],)).fetchone()

    # --- сессии ---
    def create_session(self, user_id: int, token_hash: str, expires_at: float, now: float, keep: int = 10,
                       expect_pw_hash: str | None = None) -> bool:
        """expect_pw_hash защищает от гонки: если пароль сменили после проверки, сессия не создаётся."""
        with self._tx(immediate=True) as conn:
            if expect_pw_hash is not None:
                current = conn.execute("SELECT pw_hash FROM users WHERE id = ?", (user_id,)).fetchone()
                if current is None or current["pw_hash"] != expect_pw_hash:
                    return False
            conn.execute(
                "INSERT INTO sessions (token_hash, user_id, expires_at, created_at) VALUES (?, ?, ?, ?)",
                (token_hash, user_id, int(expires_at), int(now)),
            )
            conn.execute(
                "DELETE FROM sessions WHERE user_id = ? AND token_hash NOT IN "
                "(SELECT token_hash FROM sessions WHERE user_id = ? ORDER BY created_at DESC, rowid DESC LIMIT ?)",
                (user_id, user_id, keep),
            )
            return True

    def session_user(self, token_hash: str, now: float) -> sqlite3.Row | None:
        with self._tx() as conn:
            return conn.execute(
                "SELECT u.id AS id, u.email AS email FROM sessions s JOIN users u ON u.id = s.user_id "
                "WHERE s.token_hash = ? AND s.expires_at > ?",
                (token_hash, int(now)),
            ).fetchone()

    def delete_session(self, token_hash: str) -> None:
        with self._tx(immediate=True) as conn:
            conn.execute("DELETE FROM sessions WHERE token_hash = ?", (token_hash,))

    def delete_user_sessions(self, user_id: int) -> None:
        with self._tx(immediate=True) as conn:
            conn.execute("DELETE FROM sessions WHERE user_id = ?", (user_id,))

    # --- состояние прогресса ---
    def get_state(self, user_id: int) -> tuple[int, str] | None:
        with self._tx() as conn:
            row = conn.execute("SELECT version, data FROM user_state WHERE user_id = ?", (user_id,)).fetchone()
            return (row["version"], row["data"]) if row else None

    def put_state(self, user_id: int, expected_version: int, data: str, now: float) -> int | None:
        """Записывает состояние, если версия совпала (оптимистичная блокировка). None = конфликт."""
        with self._tx(immediate=True) as conn:
            row = conn.execute("SELECT version FROM user_state WHERE user_id = ?", (user_id,)).fetchone()
            current = row["version"] if row else 0
            if current != expected_version:
                return None
            new_version = current + 1
            conn.execute(
                "INSERT INTO user_state (user_id, data, version, updated_at) VALUES (?, ?, ?, ?) "
                "ON CONFLICT(user_id) DO UPDATE SET data = excluded.data, version = excluded.version, updated_at = excluded.updated_at",
                (user_id, data, new_version, int(now)),
            )
            return new_version

    # --- обслуживание ---
    def purge_expired(self, now: float) -> None:
        with self._tx(immediate=True) as conn:
            for table in ("registrations", "reset_tokens", "sessions"):
                conn.execute(f"DELETE FROM {table} WHERE expires_at <= ?", (int(now),))   # имена таблиц - константы выше

    def count(self, table: str) -> int:
        if table not in _COUNTABLE:
            raise ValueError("неизвестная таблица")
        with self._tx() as conn:
            return conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]