"""Async SQLite connection management and schema."""

from __future__ import annotations

import asyncio
import logging
from collections.abc import AsyncIterator
from contextlib import asynccontextmanager
from pathlib import Path
from typing import Any

import aiosqlite

logger = logging.getLogger(__name__)

SCHEMA_VERSION = 1

_SCHEMA = """
CREATE TABLE IF NOT EXISTS jobs (
    id TEXT PRIMARY KEY,
    source_url TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN
        ('PENDING','RUNNING','PAUSED','COMPLETED','FAILED','CANCELLED')),
    owner_id INTEGER NOT NULL,
    owner_chat_id INTEGER NOT NULL,
    correlation_id TEXT NOT NULL,
    created_at TEXT NOT NULL,
    started_at TEXT,
    finished_at TEXT,
    total_pages INTEGER NOT NULL DEFAULT 0,
    processed_pages INTEGER NOT NULL DEFAULT 0,
    total_videos INTEGER NOT NULL DEFAULT 0,
    processed_videos INTEGER NOT NULL DEFAULT 0,
    downloaded_videos INTEGER NOT NULL DEFAULT 0,
    uploaded_videos INTEGER NOT NULL DEFAULT 0,
    failed_videos INTEGER NOT NULL DEFAULT 0,
    current_stage TEXT NOT NULL DEFAULT 'queued',
    error_message TEXT,
    updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_jobs_status_created ON jobs(status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_jobs_owner ON jobs(owner_id, created_at DESC);

CREATE TABLE IF NOT EXISTS pages (
    id TEXT PRIMARY KEY,
    job_id TEXT NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
    page_url TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN
        ('PENDING','PROCESSING','PROCESSED','FAILED','SKIPPED')),
    discovered_at TEXT NOT NULL,
    processed_at TEXT,
    http_status INTEGER,
    error_message TEXT,
    UNIQUE(job_id, page_url)
);
CREATE INDEX IF NOT EXISTS idx_pages_job_status ON pages(job_id, status);

CREATE TABLE IF NOT EXISTS videos (
    id TEXT PRIMARY KEY,
    job_id TEXT NOT NULL REFERENCES jobs(id) ON DELETE CASCADE,
    source_url TEXT NOT NULL UNIQUE,
    page_url TEXT NOT NULL,
    title TEXT NOT NULL,
    caption TEXT,
    media_type TEXT NOT NULL DEFAULT 'video',
    file_size INTEGER,
    status TEXT NOT NULL CHECK (status IN
        ('PENDING','DOWNLOADING','DOWNLOADED','UPLOADING','UPLOADED','FAILED',
         'SKIPPED','CANCELLED','UPLOAD_UNKNOWN')),
    telegram_message_id INTEGER,
    created_at TEXT NOT NULL,
    uploaded_at TEXT,
    updated_at TEXT NOT NULL,
    media_url TEXT,
    thumbnail_url TEXT,
    duration_seconds REAL,
    local_path TEXT,
    error_message TEXT,
    attempts INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_videos_job_status ON videos(job_id, status);
CREATE INDEX IF NOT EXISTS idx_videos_created ON videos(created_at DESC);

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


class Database:
    def __init__(self, path: Path | str) -> None:
        self.path = Path(path) if path != ":memory:" else Path(":memory:")
        self._conn: aiosqlite.Connection | None = None
        self._lock = asyncio.Lock()
        self._closed = False

    @property
    def connection(self) -> aiosqlite.Connection:
        if self._conn is None:
            raise RuntimeError("database is not connected")
        return self._conn

    async def connect(self) -> None:
        if self._conn is not None:
            return
        self._closed = False
        if str(self.path) != ":memory:":
            self.path.parent.mkdir(parents=True, exist_ok=True)
        self._conn = await aiosqlite.connect(str(self.path))
        self._conn.row_factory = aiosqlite.Row
        await self._conn.execute("PRAGMA foreign_keys = ON")
        await self._conn.execute("PRAGMA journal_mode = WAL")
        await self._conn.execute("PRAGMA synchronous = NORMAL")
        await self._conn.execute("PRAGMA busy_timeout = 5000")
        await self._conn.executescript(_SCHEMA)
        await self._conn.execute(f"PRAGMA user_version = {SCHEMA_VERSION}")
        await self._conn.commit()
        logger.info("SQLite database ready: %s", self.path)

    async def close(self) -> None:
        if self._conn is None or self._closed:
            return
        self._closed = True
        async with self._lock:
            await self._conn.commit()
            await self._conn.close()
            self._conn = None

    @asynccontextmanager
    async def transaction(self) -> AsyncIterator[aiosqlite.Connection]:
        if self._conn is None:
            raise RuntimeError("database is not connected")
        async with self._lock:
            await self._conn.execute("BEGIN IMMEDIATE")
            try:
                yield self._conn
            except BaseException:
                await self._conn.rollback()
                raise
            else:
                await self._conn.commit()

    async def fetch_one(self, sql: str, parameters: tuple[Any, ...] = ()) -> aiosqlite.Row | None:
        if self._conn is None:
            raise RuntimeError("database is not connected")
        async with self._lock:
            cursor = await self._conn.execute(sql, parameters)
            row = await cursor.fetchone()
            await cursor.close()
            return row

    async def fetch_all(self, sql: str, parameters: tuple[Any, ...] = ()) -> list[aiosqlite.Row]:
        if self._conn is None:
            raise RuntimeError("database is not connected")
        async with self._lock:
            cursor = await self._conn.execute(sql, parameters)
            rows = await cursor.fetchall()
            await cursor.close()
            return list(rows)