"""Additive Sprint 6H-B capture, version and private-media schema."""

from __future__ import annotations

import sqlite3

SCHEMA_VERSION = "sprint6h_b_mobile_capture_v1"

DDL = """
CREATE TABLE IF NOT EXISTS capture_entries (
    id TEXT PRIMARY KEY,
    root_id TEXT NOT NULL,
    version INTEGER NOT NULL CHECK(version >= 1),
    capture_type TEXT NOT NULL CHECK(capture_type IN ('symptom','medication','supplement','event','measurement','photo')),
    occurred_at TEXT NOT NULL,
    ended_at TEXT,
    payload_json TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active','corrected','withdrawn')),
    corrects_entry_id TEXT REFERENCES capture_entries(id),
    withdraws_entry_id TEXT REFERENCES capture_entries(id),
    source TEXT NOT NULL DEFAULT 'dashboard_v5_mobile_capture',
    created_at TEXT NOT NULL,
    UNIQUE(root_id, version)
);
CREATE INDEX IF NOT EXISTS idx_capture_entries_occurred ON capture_entries(occurred_at, capture_type);
CREATE INDEX IF NOT EXISTS idx_capture_entries_root ON capture_entries(root_id, version);
CREATE TABLE IF NOT EXISTS capture_action_log (
    idempotency_key TEXT PRIMARY KEY,
    request_version INTEGER NOT NULL,
    action_hash TEXT NOT NULL,
    entry_id TEXT NOT NULL REFERENCES capture_entries(id),
    processed_at TEXT NOT NULL
);
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 ('image/jpeg','image/png')),
    byte_size INTEGER NOT NULL CHECK(byte_size > 0 AND byte_size <= 12582912),
    width INTEGER NOT NULL CHECK(width > 0),
    height INTEGER NOT NULL CHECK(height > 0),
    media_name TEXT NOT NULL,
    thumbnail_name TEXT NOT NULL,
    description TEXT,
    body_region TEXT,
    created_at TEXT NOT NULL,
    UNIQUE(entry_id, sha256)
);
CREATE TABLE IF NOT EXISTS dashboard_schema_migrations (
    version TEXT PRIMARY KEY,
    applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
"""


def apply_schema(connection: sqlite3.Connection) -> None:
    connection.execute("PRAGMA foreign_keys=ON")
    connection.executescript(DDL)
    connection.execute(
        "INSERT OR IGNORE INTO dashboard_schema_migrations(version) VALUES(?)",
        (SCHEMA_VERSION,),
    )


def assert_schema(connection: sqlite3.Connection) -> None:
    required = {
        "capture_entries",
        "capture_action_log",
        "capture_attachments",
        "dashboard_schema_migrations",
    }
    present = {
        row[0]
        for row in connection.execute(
            "SELECT name FROM sqlite_master WHERE type='table'"
        )
    }
    if not required <= present:
        raise RuntimeError("Sprint 6H-B schema missing")
    if (
        connection.execute(
            "SELECT 1 FROM dashboard_schema_migrations WHERE version=?",
            (SCHEMA_VERSION,),
        ).fetchone()
        is None
    ):
        raise RuntimeError("Sprint 6H-B migration marker missing")