"""Reviewed path/connection and migration statements, not student SQL orchestration."""

import sqlite3
import stat
from contextlib import contextmanager
from pathlib import Path

APPLICATION_ID = 1146507280
SCHEMA = {
    1: (
        "CREATE TABLE evaluations(id TEXT PRIMARY KEY, payload TEXT NOT NULL)",
        "CREATE TABLE audit_events(seq INTEGER PRIMARY KEY AUTOINCREMENT, evaluation_id TEXT NOT NULL REFERENCES evaluations(id), finding_id TEXT, event TEXT NOT NULL, actor_id TEXT NOT NULL, revision INTEGER NOT NULL)",
    ),
    2: (
        "CREATE TABLE findings(id TEXT PRIMARY KEY, evaluation_id TEXT NOT NULL REFERENCES evaluations(id), payload TEXT NOT NULL, revision INTEGER NOT NULL CHECK(revision >= 1))",
    ),
}


def checked_path(path: Path, *, exists: bool) -> Path:
    if not isinstance(path, Path) or ".." in path.parts or path.suffix != ".db":
        raise ValueError("Choose an explicit owned .db path without parent traversal")
    if path.drive and not path.is_absolute():
        raise ValueError("Drive-relative database paths are ambiguous")
    if any(":" in part for part in path.parts if part not in (path.anchor, path.drive)):
        raise ValueError("Database path cannot use alternate streams")
    reserved = {
        "CON",
        "PRN",
        "AUX",
        "NUL",
        *(f"COM{i}" for i in range(1, 10)),
        *(f"LPT{i}" for i in range(1, 10)),
    }
    if any(
        part.split(".", 1)[0].upper() in reserved or part.endswith((" ", "."))
        for part in path.parts
        if part not in (path.anchor, path.drive)
    ):
        raise ValueError("Reserved or ambiguous database paths are refused")
    path = path.absolute()
    for component in [*path.parents, path]:
        if not component.exists() and not component.is_symlink():
            continue
        info = component.lstat()
        if stat.S_ISLNK(info.st_mode) or getattr(info, "st_file_attributes", 0) & 0x400:
            raise ValueError("Known links/reparse points are refused")
    if not path.parent.is_dir():
        raise ValueError("Database parent must already exist")
    if exists:
        if not path.is_file() or path.stat().st_size > 16 * 1024 * 1024:
            raise ValueError("Use an existing regular bounded owned database")
    elif path.exists():
        raise ValueError("NEW database path already exists")
    return path


@contextmanager
def connection(path: Path):
    path = checked_path(path, exists=True)
    database = sqlite3.connect(
        path.as_uri() + "?mode=rw", uri=True, timeout=2, isolation_level=None
    )
    try:
        database.execute("PRAGMA foreign_keys=ON")
        yield database
    finally:
        database.close()


def create_v1(path: Path) -> None:
    """Owned historical fixture for the actual v1→v2 migration lesson."""
    path = checked_path(path, exists=False)
    with path.open("xb"):
        pass
    with connection(path) as database:
        database.execute("BEGIN IMMEDIATE")
        try:
            for statement in SCHEMA[1]:
                database.execute(statement)
            database.execute(f"PRAGMA application_id={APPLICATION_ID}")
            database.execute("PRAGMA user_version=1")
            database.commit()
        except BaseException:
            database.rollback()
            raise