"""Additive Sprint 6I-C guided document reconciliation schema."""
from __future__ import annotations
import sqlite3

SCHEMA_VERSION = "sprint6i_c_guided_document_reconciliation_v1"

DDL = """
CREATE TABLE IF NOT EXISTS document_reconciliation (
    document_id INTEGER PRIMARY KEY REFERENCES dokumente(id),
    queue_bucket TEXT NOT NULL CHECK(queue_bucket IN (
      'now_reviewable','original_missing','technical_blocked','automatically_prepared','no_action')),
    reason_code TEXT NOT NULL,
    priority INTEGER NOT NULL DEFAULT 0 CHECK(priority BETWEEN 0 AND 100),
    open_decisions INTEGER NOT NULL DEFAULT 0 CHECK(open_decisions BETWEEN 0 AND 1000),
    byte_duplicate_of INTEGER REFERENCES dokumente(id),
    exact_match_count INTEGER NOT NULL DEFAULT 0 CHECK(exact_match_count >= 0),
    conflict_count INTEGER NOT NULL DEFAULT 0 CHECK(conflict_count >= 0),
    prepared_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_document_reconciliation_queue
  ON document_reconciliation(queue_bucket,priority DESC,open_decisions DESC,document_id);

CREATE TABLE IF NOT EXISTS document_section_relations (
    id TEXT PRIMARY KEY,
    document_id INTEGER NOT NULL REFERENCES dokumente(id),
    page_number INTEGER NOT NULL CHECK(page_number >= 1),
    compared_document_id INTEGER NOT NULL REFERENCES dokumente(id),
    compared_page_number INTEGER NOT NULL CHECK(compared_page_number >= 1),
    relation TEXT NOT NULL CHECK(relation IN ('identical','near_match')),
    similarity REAL NOT NULL CHECK(similarity >= 0 AND similarity <= 1),
    section_hash TEXT NOT NULL,
    created_at TEXT NOT NULL,
    UNIQUE(document_id,page_number,compared_document_id,compared_page_number)
);
CREATE INDEX IF NOT EXISTS idx_document_section_relations_document
  ON document_section_relations(document_id,relation,page_number);

CREATE VIRTUAL TABLE IF NOT EXISTS health_document_repeated_fts USING fts5(
  document_id UNINDEXED, chunk_no UNINDEXED, title, category, institution, document_type, content,
  tokenize='unicode61 remove_diacritics 2'
);

CREATE TABLE IF NOT EXISTS document_candidate_matches (
    candidate_id TEXT PRIMARY KEY REFERENCES document_candidates(id),
    match_status TEXT NOT NULL CHECK(match_status IN (
      'exact_match','format_unit_match','value_conflict','not_present','ambiguous',
      'repeated_exact','supporting_reference','non_transferable')),
    target_area TEXT NOT NULL CHECK(target_area IN (
      'laboratory','medication','appointment','document_information','metadata')),
    normalized_parameter TEXT,
    normalized_date TEXT,
    normalized_value TEXT,
    normalized_unit TEXT,
    reference_text TEXT,
    database_match_count INTEGER NOT NULL DEFAULT 0 CHECK(database_match_count >= 0),
    workbook_match_count INTEGER NOT NULL DEFAULT 0 CHECK(workbook_match_count >= 0),
    existing_database_value TEXT,
    existing_workbook_value TEXT,
    comparison_digest TEXT NOT NULL,
    compared_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_document_candidate_matches_status
  ON document_candidate_matches(match_status,target_area,candidate_id);

CREATE TABLE IF NOT EXISTS document_candidate_review_events (
    action_id TEXT PRIMARY KEY,
    candidate_id TEXT NOT NULL REFERENCES document_candidates(id),
    decision TEXT NOT NULL CHECK(decision IN (
      'correct','corrected','reject','already_present','defer','stage','transfer')),
    original_value TEXT NOT NULL,
    corrected_value TEXT,
    corrected_unit TEXT,
    processed_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS document_transfer_staging (
    id TEXT PRIMARY KEY,
    candidate_id TEXT NOT NULL UNIQUE REFERENCES document_candidates(id),
    document_id INTEGER NOT NULL REFERENCES dokumente(id),
    target_area TEXT NOT NULL CHECK(target_area IN ('laboratory','medication','appointment')),
    status TEXT NOT NULL CHECK(status IN (
      'reviewed_pending_preview','ready_for_transfer','transferred','rejected','blocked')),
    operation TEXT NOT NULL CHECK(operation IN ('create','augment','link_only')),
    old_value_json TEXT NOT NULL,
    new_value_json TEXT NOT NULL,
    source_page INTEGER NOT NULL,
    expected_text_version INTEGER NOT NULL CHECK(expected_text_version>=1),
    expected_candidate_revision INTEGER NOT NULL CHECK(expected_candidate_revision>=1),
    expected_reconciliation_revision INTEGER NOT NULL CHECK(expected_reconciliation_revision>=0),
    idempotency_key TEXT NOT NULL UNIQUE,
    reviewed_action_id TEXT NOT NULL,
    transfer_action_id TEXT UNIQUE,
    canonical_row_id INTEGER,
    created_at TEXT NOT NULL,
    transferred_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_document_transfer_staging_status
  ON document_transfer_staging(status,target_area,document_id);

CREATE TABLE IF NOT EXISTS document_reconciliation_runs (
    run_id TEXT PRIMARY KEY,
    prepared_documents INTEGER NOT NULL,
    now_reviewable INTEGER NOT NULL,
    original_missing INTEGER NOT NULL,
    technical_blocked INTEGER NOT NULL,
    byte_duplicates INTEGER NOT NULL,
    identical_repetitions INTEGER NOT NULL,
    conflicting_candidates INTEGER NOT NULL,
    exact_matches INTEGER NOT NULL,
    user_decisions INTEGER NOT NULL,
    aggregate_digest TEXT NOT NULL,
    created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS dashboard_schema_migrations (
    version TEXT PRIMARY KEY,
    applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
"""

REQUIRED_TABLES = {
    "document_reconciliation","document_section_relations","health_document_repeated_fts","document_candidate_matches",
    "document_candidate_review_events","document_transfer_staging",
    "document_reconciliation_runs","dashboard_schema_migrations",
}


def _add_column(connection: sqlite3.Connection,table: str,column: str,declaration: str) -> None:
    columns={str(row[1]) for row in connection.execute(f"PRAGMA table_info({table})")}
    if column not in columns:connection.execute(f"ALTER TABLE {table} ADD COLUMN {column} {declaration}")


def apply_schema(connection: sqlite3.Connection) -> None:
    connection.execute("PRAGMA foreign_keys=ON")
    _add_column(connection,"document_candidates","source_text_version","INTEGER NOT NULL DEFAULT 1")
    _add_column(connection,"document_candidates","candidate_fingerprint","TEXT")
    _add_column(connection,"document_candidates","candidate_revision","INTEGER NOT NULL DEFAULT 1")
    _add_column(connection,"document_processing","reconciliation_revision","INTEGER NOT NULL DEFAULT 0")
    connection.executescript(DDL)
    connection.execute("CREATE INDEX IF NOT EXISTS idx_document_candidate_fingerprint ON document_candidates(candidate_type,candidate_fingerprint)")
    connection.execute(
        "INSERT OR IGNORE INTO dashboard_schema_migrations(version,applied_at) VALUES(?,strftime('%Y-%m-%dT%H:%M:%fZ','now'))",
        (SCHEMA_VERSION,),
    )


def assert_schema(connection: sqlite3.Connection) -> None:
    present={str(row[0]) for row in connection.execute(
        "SELECT name FROM sqlite_master WHERE type IN ('table','view')")}
    if not REQUIRED_TABLES <= present:
        raise RuntimeError("Sprint 6I-C schema missing")
    candidate_columns={str(row[1]) for row in connection.execute("PRAGMA table_info(document_candidates)")}
    processing_columns={str(row[1]) for row in connection.execute("PRAGMA table_info(document_processing)")}
    if not {"source_text_version","candidate_fingerprint","candidate_revision"}<=candidate_columns or "reconciliation_revision" not in processing_columns:
        raise RuntimeError("Sprint 6I-C additive columns missing")
    if connection.execute(
        "SELECT 1 FROM dashboard_schema_migrations WHERE version=?",(SCHEMA_VERSION,)
    ).fetchone() is None:
        raise RuntimeError("Sprint 6I-C migration marker missing")