from __future__ import annotations

import sqlite3
from pathlib import Path
from typing import Any, Iterable
from contextlib import contextmanager


def connect(db_path: Path) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path, timeout=30, isolation_level=None)
    conn.row_factory = sqlite3.Row
    _configure(conn)
    return conn


def _configure(conn: sqlite3.Connection) -> None:
    conn.execute("PRAGMA foreign_keys = ON;")
    conn.execute("PRAGMA journal_mode = WAL;")
    conn.execute("PRAGMA synchronous = NORMAL;")


def init_db(db_path: Path) -> None:
    db_path.parent.mkdir(parents=True, exist_ok=True)
    conn = connect(db_path)
    try:
        _apply_schema(conn)
    finally:
        conn.close()


def _apply_schema(conn: sqlite3.Connection) -> None:
    conn.executescript(
        """
        CREATE TABLE IF NOT EXISTS users (
          id TEXT PRIMARY KEY,
          email TEXT NOT NULL UNIQUE,
          username TEXT NOT NULL UNIQUE,
          password_salt_b64 TEXT NOT NULL,
          password_iterations INTEGER NOT NULL,
          password_hash_b64 TEXT NOT NULL,
          is_admin INTEGER NOT NULL DEFAULT 0,
          is_bot INTEGER NOT NULL DEFAULT 0,
          bot_verified INTEGER NOT NULL DEFAULT 0,
          is_banned INTEGER NOT NULL DEFAULT 0,
          ban_reason TEXT,
          warned_reason TEXT,
          warned_at INTEGER,
          password_reset_required INTEGER NOT NULL DEFAULT 0,
          created_at INTEGER NOT NULL
        );

        CREATE TABLE IF NOT EXISTS user_settings (
          user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
          receive_everyone_mentions INTEGER NOT NULL DEFAULT 0
        );

        CREATE TABLE IF NOT EXISTS password_reset_otps (
          user_id TEXT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
          otp_hash_hex TEXT NOT NULL,
          expires_at INTEGER NOT NULL,
          used_at INTEGER,
          created_by_admin_id TEXT NOT NULL REFERENCES users(id),
          created_at INTEGER NOT NULL
        );

        CREATE TABLE IF NOT EXISTS sessions (
          id TEXT PRIMARY KEY,
          user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          token_hash_hex TEXT NOT NULL UNIQUE,
          kind TEXT NOT NULL,
          created_at INTEGER NOT NULL,
          expires_at INTEGER NOT NULL,
          revoked_at INTEGER,
          last_seen_at INTEGER
        );

        CREATE TABLE IF NOT EXISTS conversations (
          id TEXT PRIMARY KEY,
          kind TEXT NOT NULL,
          name TEXT,
          created_by TEXT NOT NULL REFERENCES users(id),
          created_at INTEGER NOT NULL
        );

        CREATE TABLE IF NOT EXISTS conversation_members (
          conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
          user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          is_admin INTEGER NOT NULL DEFAULT 0,
          is_banned INTEGER NOT NULL DEFAULT 0,
          joined_at INTEGER NOT NULL,
          PRIMARY KEY (conversation_id, user_id)
        );

        CREATE TABLE IF NOT EXISTS attachments (
          id TEXT PRIMARY KEY,
          user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          filename TEXT NOT NULL,
          content_type TEXT,
          size INTEGER NOT NULL,
          sha256_hex TEXT NOT NULL,
          storage_path TEXT NOT NULL,
          created_at INTEGER NOT NULL
        );

        CREATE TABLE IF NOT EXISTS messages (
          id TEXT PRIMARY KEY,
          conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
          sender_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          kind TEXT NOT NULL,
          text TEXT,
          attachment_id TEXT REFERENCES attachments(id),
          is_disappearing INTEGER NOT NULL DEFAULT 0,
          created_at INTEGER NOT NULL,
          edited_at INTEGER,
          deleted_at INTEGER,
          deleted_by TEXT REFERENCES users(id),
          contains_everyone_mention INTEGER NOT NULL DEFAULT 0
        );

        CREATE TABLE IF NOT EXISTS message_edits (
          id TEXT PRIMARY KEY,
          message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
          editor_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          old_text TEXT,
          new_text TEXT,
          edited_at INTEGER NOT NULL
        );

        CREATE TABLE IF NOT EXISTS message_deletion_audit (
          id TEXT PRIMARY KEY,
          message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
          conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
          deleted_by TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          deleted_at INTEGER NOT NULL
        );

        CREATE TABLE IF NOT EXISTS reactions (
          message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
          user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          emoji TEXT NOT NULL,
          created_at INTEGER NOT NULL,
          PRIMARY KEY (message_id, user_id, emoji)
        );

        CREATE TABLE IF NOT EXISTS uploads (
          id TEXT PRIMARY KEY,
          user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          filename TEXT NOT NULL,
          content_type TEXT,
          total_size INTEGER NOT NULL,
          overall_sha256_hex TEXT NOT NULL,
          chunk_size INTEGER NOT NULL,
          num_chunks INTEGER NOT NULL,
          tmp_dir TEXT NOT NULL,
          state TEXT NOT NULL,
          created_at INTEGER NOT NULL,
          finalized_at INTEGER
        );

        CREATE TABLE IF NOT EXISTS upload_chunks (
          upload_id TEXT NOT NULL REFERENCES uploads(id) ON DELETE CASCADE,
          chunk_index INTEGER NOT NULL,
          size INTEGER NOT NULL,
          sha256_hex TEXT NOT NULL,
          received_at INTEGER NOT NULL,
          PRIMARY KEY (upload_id, chunk_index)
        );

        CREATE TABLE IF NOT EXISTS unifiedpush_devices (
          id TEXT PRIMARY KEY,
          user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          endpoint TEXT NOT NULL,
          created_at INTEGER NOT NULL,
          last_seen_at INTEGER
        );

        CREATE TABLE IF NOT EXISTS webpush_subscriptions (
          id TEXT PRIMARY KEY,
          user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          endpoint TEXT NOT NULL,
          p256dh TEXT NOT NULL,
          auth TEXT NOT NULL,
          created_at INTEGER NOT NULL,
          last_seen_at INTEGER
        );

        CREATE TABLE IF NOT EXISTS push_events (
          id TEXT PRIMARY KEY,
          user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          target_kind TEXT NOT NULL,
          target_id TEXT NOT NULL,
          payload_json TEXT NOT NULL,
          created_at INTEGER NOT NULL,
          sent_at INTEGER,
          error TEXT
        );

        CREATE TABLE IF NOT EXISTS logs (
          id TEXT PRIMARY KEY,
          level TEXT NOT NULL,
          event TEXT NOT NULL,
          details_json TEXT,
          created_at INTEGER NOT NULL
        );

        CREATE TABLE IF NOT EXISTS metrics_counters (
          key TEXT PRIMARY KEY,
          value INTEGER NOT NULL,
          updated_at INTEGER NOT NULL
        );

        CREATE TABLE IF NOT EXISTS integrations (
          id TEXT PRIMARY KEY,
          kind TEXT NOT NULL,
          name TEXT NOT NULL,
          bot_user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          target_conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
          enabled INTEGER NOT NULL DEFAULT 1,
          config_json TEXT NOT NULL,
          created_at INTEGER NOT NULL,
          updated_at INTEGER NOT NULL
        );
        """
    )


@contextmanager
def transaction(conn: sqlite3.Connection):
    conn.execute("BEGIN")
    try:
        yield
    except Exception:
        conn.execute("ROLLBACK")
        raise
    else:
        conn.execute("COMMIT")


def fetch_one(conn: sqlite3.Connection, query: str, params: Iterable[Any] = ()) -> Any:
    cur = conn.execute(query, tuple(params))
    return cur.fetchone()


def fetch_all(conn: sqlite3.Connection, query: str, params: Iterable[Any] = ()) -> list[Any]:
    cur = conn.execute(query, tuple(params))
    return cur.fetchall()
