"""Additive Sprint 6I-B local Apple-media schema."""
from __future__ import annotations
import sqlite3

SCHEMA_VERSION = "sprint6i_b_local_apple_media_v1"
ALLOWED_MIME = "'image/heic','image/heif','image/jpeg','image/png','video/quicktime','video/mp4'"

CAPTURE_TABLE = f"""
CREATE TABLE IF NOT EXISTS capture_attachments (
    id TEXT PRIMARY KEY,
    entry_id TEXT NOT NULL REFERENCES capture_entries(id),
    sha256 TEXT NOT NULL,
    mime_type TEXT NOT NULL CHECK(mime_type IN ({ALLOWED_MIME})),
    media_kind TEXT NOT NULL DEFAULT 'photo' CHECK(media_kind IN ('photo','video')),
    byte_size INTEGER NOT NULL CHECK(byte_size > 0 AND byte_size <= 67108864),
    width INTEGER NOT NULL CHECK(width > 0 AND width <= 16000),
    height INTEGER NOT NULL CHECK(height > 0 AND height <= 16000),
    frame_count INTEGER NOT NULL DEFAULT 1 CHECK(frame_count BETWEEN 1 AND 1800),
    auxiliary_count INTEGER NOT NULL DEFAULT 0 CHECK(auxiliary_count BETWEEN 0 AND 3),
    duration_seconds REAL,
    container TEXT,
    codec TEXT,
    media_name TEXT NOT NULL,
    thumbnail_name TEXT NOT NULL,
    proxy_name TEXT,
    preview_sha256 TEXT,
    proxy_sha256 TEXT,
    preview_status TEXT NOT NULL DEFAULT 'ready' CHECK(preview_status IN ('ready','failed')),
    decoder_name TEXT NOT NULL DEFAULT 'Pillow',
    decoder_version TEXT NOT NULL DEFAULT 'legacy',
    probe_version TEXT,
    unconfirmed_captured_at TEXT,
    review_status TEXT NOT NULL DEFAULT 'unverified' CHECK(review_status IN ('unverified','verified','rejected')),
    explicit_pair_id TEXT,
    description TEXT,
    body_region TEXT,
    created_at TEXT NOT NULL,
    UNIQUE(entry_id, sha256)
);
"""

DOCUMENT_TABLE = f"""
CREATE TABLE IF NOT EXISTS document_media (
    document_id INTEGER PRIMARY KEY REFERENCES dokumente(id) ON DELETE CASCADE,
    media_kind TEXT NOT NULL CHECK(media_kind IN ('photo','video')),
    mime_type TEXT NOT NULL CHECK(mime_type IN ({ALLOWED_MIME})),
    original_sha256 TEXT NOT NULL,
    byte_size INTEGER NOT NULL CHECK(byte_size > 0 AND byte_size <= 67108864),
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    frame_count INTEGER NOT NULL DEFAULT 1,
    auxiliary_count INTEGER NOT NULL DEFAULT 0,
    duration_seconds REAL,
    container TEXT,
    codec TEXT,
    preview_name TEXT,
    proxy_name TEXT,
    preview_sha256 TEXT,
    proxy_sha256 TEXT,
    preview_status TEXT NOT NULL CHECK(preview_status IN ('ready','failed')),
    decoder_name TEXT NOT NULL,
    decoder_version TEXT NOT NULL,
    probe_version TEXT,
    unconfirmed_captured_at TEXT,
    created_at TEXT NOT NULL
);
"""


def _columns(connection: sqlite3.Connection, table: str) -> set[str]:
    return {str(row[1]) for row in connection.execute(f"PRAGMA table_info({table})")}


def _upgrade_capture_attachments(connection: sqlite3.Connection) -> None:
    if not _columns(connection, "capture_attachments"):
        connection.executescript(CAPTURE_TABLE)
        return
    if "media_kind" in _columns(connection, "capture_attachments"):
        return
    connection.execute("ALTER TABLE capture_attachments RENAME TO capture_attachments_sprint6h_b")
    connection.executescript(CAPTURE_TABLE)
    connection.execute(
        """INSERT INTO capture_attachments(
        id,entry_id,sha256,mime_type,media_kind,byte_size,width,height,frame_count,auxiliary_count,
        duration_seconds,container,codec,media_name,thumbnail_name,proxy_name,preview_status,
        decoder_name,decoder_version,review_status,description,body_region,created_at)
        SELECT id,entry_id,sha256,mime_type,'photo',byte_size,width,height,1,0,
        NULL,CASE mime_type WHEN 'image/jpeg' THEN 'jpeg' ELSE 'png' END,NULL,
        media_name,thumbnail_name,NULL,'ready','Pillow','legacy','unverified',description,body_region,created_at
        FROM capture_attachments_sprint6h_b"""
    )
    connection.execute("DROP TABLE capture_attachments_sprint6h_b")


def apply_schema(connection: sqlite3.Connection) -> None:
    connection.execute("PRAGMA foreign_keys=ON")
    _upgrade_capture_attachments(connection)
    connection.executescript(DOCUMENT_TABLE)
    connection.execute("CREATE INDEX IF NOT EXISTS idx_capture_media_kind_date ON capture_attachments(media_kind,created_at)")
    connection.execute("CREATE INDEX IF NOT EXISTS idx_capture_media_review ON capture_attachments(review_status,media_kind)")
    connection.execute("CREATE TABLE IF NOT EXISTS dashboard_schema_migrations(version TEXT PRIMARY KEY, applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP)")
    connection.execute("INSERT OR IGNORE INTO dashboard_schema_migrations(version) VALUES(?)", (SCHEMA_VERSION,))


def assert_schema(connection: sqlite3.Connection) -> None:
    required = {"media_kind","proxy_name","decoder_version","review_status","unconfirmed_captured_at"}
    if not required <= _columns(connection, "capture_attachments") or not _columns(connection, "document_media"):
        raise RuntimeError("Sprint 6I-B media schema missing")
    if connection.execute("SELECT 1 FROM dashboard_schema_migrations WHERE version=?", (SCHEMA_VERSION,)).fetchone() is None:
        raise RuntimeError("Sprint 6I-B migration marker missing")