from __future__ import annotations

import sqlite3
from contextlib import contextmanager
from pathlib import Path
from typing import Iterator

from app.config import Settings
from app.models import utc_now_iso


def ensure_db(settings: Settings) -> None:
    db_path = Path(settings.db_path)
    db_path.parent.mkdir(parents=True, exist_ok=True)
    with sqlite3.connect(db_path) as conn:
        conn.row_factory = sqlite3.Row
        conn.executescript(
            """
            PRAGMA foreign_keys = ON;

            CREATE TABLE IF NOT EXISTS applications (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              customer_id INTEGER,
              customer_name TEXT NOT NULL,
              chat_id INTEGER,
              category TEXT NOT NULL,
              description TEXT NOT NULL,
              attachments_json TEXT NOT NULL DEFAULT '[]',
              status TEXT NOT NULL DEFAULT 'new',
              external_sync_status TEXT NOT NULL DEFAULT 'not_sent',
              external_id TEXT,
              external_error TEXT,
              source TEXT NOT NULL DEFAULT 'bot',
              created_at TEXT NOT NULL,
              updated_at TEXT NOT NULL
            );

            CREATE INDEX IF NOT EXISTS idx_applications_status
              ON applications(status);
            CREATE INDEX IF NOT EXISTS idx_applications_created_at
              ON applications(created_at);

            CREATE TABLE IF NOT EXISTS application_events (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              application_id INTEGER NOT NULL,
              event_type TEXT NOT NULL,
              actor TEXT NOT NULL,
              details_json TEXT NOT NULL DEFAULT '{}',
              created_at TEXT NOT NULL,
              FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE
            );
            CREATE INDEX IF NOT EXISTS idx_events_application_id
              ON application_events(application_id);

            CREATE TABLE IF NOT EXISTS bot_chats (
              chat_id INTEGER PRIMARY KEY,
              chat_type TEXT NOT NULL,
              title TEXT,
              username TEXT,
              last_seen_at TEXT NOT NULL,
              is_active INTEGER NOT NULL DEFAULT 1
            );

            CREATE TABLE IF NOT EXISTS users (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              username TEXT NOT NULL UNIQUE,
              password_hash TEXT NOT NULL,
              role TEXT NOT NULL,
              created_at TEXT NOT NULL,
              updated_at TEXT NOT NULL
            );
            """
        )
        conn.commit()


class Database:
    def __init__(self, db_path: str) -> None:
        self._db_path = db_path

    def connect(self) -> sqlite3.Connection:
        conn = sqlite3.connect(self._db_path)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA foreign_keys = ON;")
        return conn

    @contextmanager
    def transaction(self) -> Iterator[sqlite3.Connection]:
        conn = self.connect()
        try:
            yield conn
            conn.commit()
        except Exception:
            conn.rollback()
            raise
        finally:
            conn.close()


def ensure_admin_user_seed(db: Database, username: str, password_hash: str, role: str = "admin") -> None:
    now = utc_now_iso()
    with db.connect() as conn:
        row = conn.execute("SELECT id FROM users WHERE username = ?", (username,)).fetchone()
        if row is None:
            conn.execute(
                """
                INSERT INTO users (username, password_hash, role, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?)
                """,
                (username, password_hash, role, now, now),
            )
        else:
            conn.execute(
                "UPDATE users SET password_hash = ?, role = ?, updated_at = ? WHERE username = ?",
                (password_hash, role, now, username),
            )
        conn.commit()