from __future__ import annotations

import shutil
import sqlite3
from pathlib import Path

import pytest

from game_data_capture.storage.database import MigrationError, StorageDatabase


def _migrations(tmp_path: Path) -> Path:
    source = Path(__file__).parents[3] / "migrations" / "001_initial.sql"
    destination = tmp_path / "migrations"
    destination.mkdir()
    shutil.copy(source, destination / source.name)
    return destination


def test_connection_pragmas_and_private_modes(tmp_path: Path) -> None:
    root = (tmp_path / "utility").resolve()
    database = StorageDatabase(root, _migrations(tmp_path).resolve())
    database.migrate()
    with database.connect() as connection:
        connection.execute("BEGIN IMMEDIATE")
        connection.execute("CREATE TABLE IF NOT EXISTS live_wal(value INTEGER)")
        assert connection.execute("PRAGMA foreign_keys").fetchone()[0] == 1
        assert connection.execute("PRAGMA journal_mode").fetchone()[0] == "wal"
        assert connection.execute("PRAGMA busy_timeout").fetchone()[0] == 5000
        assert connection.execute("PRAGMA synchronous").fetchone()[0] == 2
        for path in (
            database.path.with_name("capture.sqlite3-wal"),
            database.path.with_name("capture.sqlite3-shm"),
        ):
            if path.exists():
                assert path.stat().st_mode & 0o077 == 0
        connection.execute("COMMIT")
    assert root.stat().st_mode & 0o777 == 0o700
    assert database.path.stat().st_mode & 0o777 == 0o600


def test_database_symlink_is_rejected(tmp_path: Path) -> None:
    root = (tmp_path / "utility").resolve()
    root.mkdir()
    outside = tmp_path / "outside"
    outside.write_text("x")
    (root / "capture.sqlite3").symlink_to(outside)
    with pytest.raises(MigrationError):
        StorageDatabase(root, _migrations(tmp_path).resolve()).connect()


def test_migration_history_and_failure_rollback(tmp_path: Path) -> None:
    root = (tmp_path / "utility").resolve()
    migrations = _migrations(tmp_path).resolve()
    database = StorageDatabase(root, migrations)
    database.migrate()
    first = migrations / "001_initial.sql"
    first.write_text(first.read_text() + "\n-- changed\n")
    with pytest.raises(MigrationError):
        database.migrate()
    first.write_text((Path(__file__).parents[3] / "migrations" / "001_initial.sql").read_text())
    bad = migrations / "002_bad.sql"
    bad.write_text("CREATE TABLE partial_test(value INTEGER); BAD SQL;")
    with pytest.raises(sqlite3.OperationalError):
        database.migrate()
    with database.connect() as connection:
        assert connection.execute("SELECT count(*) FROM schema_migrations").fetchone()[0] == 1
        assert (
            connection.execute(
                "SELECT name FROM sqlite_master WHERE name='partial_test'"
            ).fetchone()
            is None
        )
    bad.write_text("CREATE TABLE partial_test(value INTEGER);")
    database.migrate()
    database.migrate()
    with database.connect() as connection:
        assert connection.execute("SELECT count(*) FROM schema_migrations").fetchone()[0] == 2


def test_migration_removal_rename_and_duplicate_rejected(tmp_path: Path) -> None:
    root = (tmp_path / "utility").resolve()
    migrations = _migrations(tmp_path).resolve()
    database = StorageDatabase(root, migrations)
    database.migrate()
    path = migrations / "001_initial.sql"
    path.rename(migrations / "001_renamed.sql")
    with pytest.raises(MigrationError):
        database.migrate()
    (migrations / "001_renamed.sql").rename(path)
    path.unlink()
    with pytest.raises(MigrationError):
        database.migrate()
    shutil.copy(Path(__file__).parents[3] / "migrations" / "001_initial.sql", path)
    shutil.copy(path, migrations / "001_duplicate.sql")
    with pytest.raises(MigrationError):
        database.migrate()


def test_corpus_migration_preserves_populated_initial_database(tmp_path: Path) -> None:
    root = (tmp_path / "utility").resolve()
    migrations = _migrations(tmp_path).resolve()
    database = StorageDatabase(root, migrations)
    database.migrate()
    with database.connect() as connection:
        connection.execute(
            "INSERT INTO blobs(sha256, byte_size, media_type, created_at) VALUES (?, 1, ?, ?)",
            ("0" * 64, "image/png", "time"),
        )
        connection.execute(
            "INSERT INTO captures(capture_id, blob_sha256, state, received_at, receipt_token) "
            "VALUES (?, ?, ?, ?, ?)",
            ("capture-0001", "0" * 64, "stored", "time", "receipt-0001"),
        )
    source = Path(__file__).parents[3] / "migrations" / "002_capture_corpus.sql"
    shutil.copy(source, migrations / source.name)
    database.migrate()
    with database.connect() as connection:
        assert connection.execute("SELECT count(*) FROM captures").fetchone()[0] == 1
        assert connection.execute("SELECT count(*) FROM capture_labels").fetchone()[0] == 0
        assert tuple(
            connection.execute(
                "SELECT sha256, byte_size, media_type, created_at FROM blobs"
            ).fetchone()
        ) == ("0" * 64, 1, "image/png", "time")
        assert tuple(
            connection.execute(
                "SELECT capture_id, blob_sha256, state, received_at, receipt_token, "
                "failure_reason, revision FROM captures"
            ).fetchone()
        ) == ("capture-0001", "0" * 64, "stored", "time", "receipt-0001", None, 0)
        assert connection.execute("SELECT count(*) FROM schema_migrations").fetchone()[0] == 2
        migration = connection.execute(
            "SELECT filename, checksum FROM schema_migrations WHERE version = 2"
        ).fetchone()
        assert migration is not None
        assert migration["filename"] == "002_capture_corpus.sql"
        assert isinstance(migration["checksum"], str) and len(migration["checksum"]) == 64
        names = {row["name"] for row in connection.execute("SELECT name FROM sqlite_master")}
        assert {
            "captures_blob_sha256",
            "jobs_capture_id",
            "ocr_runs_capture_id",
            "runtime_failures_capture_subject",
            "lineage_child_id",
            "lineage_parent_id",
        }.issubset(names)