#!/usr/bin/env python3
"""Static schema contract validator for Baltique OS Core 2.6.1.

The validator intentionally does not connect to a live database.  It verifies
that the supplied baseline dump and reference migrations together provide the
columns used by the 2.6.1 runtime.
"""
from __future__ import annotations

import re
import sys
from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
BUILD = ROOT.parent
DUMP_CANDIDATES = [
    BUILD / "base_unzip" / "2026.07.11 BaltiqueOS v2.0" / "Baza danych" / "gvpctwyblt_mysql_db.sql",
    BUILD / "base_unzip" / "2026.07.11 BaltiqueOS v2.0" / "Baza danych" / "gvpctwyblt_mysql_db.sql",
]
MIG_242_CANDIDATES = [
    BUILD / "full" / "database" / "migrations" / "242_core_2_6_0_enterprise_hospital_command_center.sql",
    ROOT / "database" / "migrations" / "242_core_2_6_0_enterprise_hospital_command_center.sql",
]
MIG_243 = ROOT / "database" / "migrations" / "243_core_2_6_1_verified_floorplans_runtime_ui_room_governance.sql"
PHP_SCHEMA = ROOT / "app" / "enterprise_authoritative_floorplans_261.php"
SNAPSHOT = ROOT / "docs" / "SCHEMA_BASELINE_CONTRACT_2_6_1.json"

checks: list[tuple[str, bool, str]] = []

def check(key: str, ok: bool, detail: str = "") -> None:
    checks.append((key, ok, detail))


def first_existing(paths: list[Path]) -> Path | None:
    for p in paths:
        if p.is_file():
            return p
    return None


def parse_create_tables(sql: str) -> dict[str, set[str]]:
    out: dict[str, set[str]] = {}
    pattern = re.compile(r"CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?`?([A-Za-z0-9_]+)`?\s*\((.*?)\)\s*ENGINE\s*=", re.I | re.S)
    for m in pattern.finditer(sql):
        table = m.group(1)
        body = m.group(2)
        cols = set(re.findall(r"^\s*`?([A-Za-z_][A-Za-z0-9_]*)`?\s+(?:BIGINT|INT|TINYINT|SMALLINT|MEDIUMINT|VARCHAR|CHAR|TEXT|LONGTEXT|MEDIUMTEXT|JSON|DATE|DATETIME|TIMESTAMP|DECIMAL|FLOAT|DOUBLE|BLOB|LONGBLOB|VARBINARY|ENUM|SET)\b", body, re.I | re.M))
        out[table] = cols
    return out


def parse_alter_columns(sql: str) -> dict[str, set[str]]:
    out: dict[str, set[str]] = {}
    for m in re.finditer(r"ALTER\s+TABLE\s+`?([A-Za-z0-9_]+)`?\s+(.*?);", sql, re.I | re.S):
        table = m.group(1)
        body = m.group(2)
        cols = set(re.findall(r"ADD\s+COLUMN\s+(?:IF\s+NOT\s+EXISTS\s+)?`?([A-Za-z_][A-Za-z0-9_]*)`?", body, re.I))
        out.setdefault(table, set()).update(cols)
    return out


def merged_schema(*sql_texts: str) -> dict[str, set[str]]:
    schema: dict[str, set[str]] = {}
    for text in sql_texts:
        for t, cols in parse_create_tables(text).items():
            schema.setdefault(t, set()).update(cols)
        for t, cols in parse_alter_columns(text).items():
            schema.setdefault(t, set()).update(cols)
    return schema


dump_path = first_existing(DUMP_CANDIDATES)
mig242_path = first_existing(MIG_242_CANDIDATES)
snapshot_ok = SNAPSHOT.is_file()
source_mode = "live_build_sources" if dump_path is not None and mig242_path is not None else "packaged_snapshot"
check("baseline_contract_available", (dump_path is not None and mig242_path is not None) or snapshot_ok, source_mode)
check("migration_243_available", MIG_243.is_file(), str(MIG_243))
check("php_schema_available", PHP_SCHEMA.is_file(), str(PHP_SCHEMA))

if not (((dump_path is not None and mig242_path is not None) or snapshot_ok) and MIG_243.is_file() and PHP_SCHEMA.is_file()):
    for key, ok, detail in checks:
        print(("PASS" if ok else "FAIL") + "\t" + key + ("\t" + detail if detail else ""))
    print(f"SUMMARY\t{sum(1 for _,ok,_ in checks if ok)}/{len(checks)} PASS")
    sys.exit(1)

mig243 = MIG_243.read_text(encoding="utf-8", errors="ignore")
php = PHP_SCHEMA.read_text(encoding="utf-8", errors="ignore")
if dump_path is not None and mig242_path is not None:
    baseline = dump_path.read_text(encoding="utf-8", errors="ignore")
    mig242 = mig242_path.read_text(encoding="utf-8", errors="ignore")
    schema = merged_schema(baseline, mig242, mig243)
else:
    import json
    snapshot = json.loads(SNAPSHOT.read_text(encoding="utf-8"))
    schema = {str(t): set(map(str, cols)) for t, cols in snapshot.get("tables", {}).items()}
    for t, cols in parse_create_tables(mig243).items():
        schema.setdefault(t, set()).update(cols)
    for t, cols in parse_alter_columns(mig243).items():
        schema.setdefault(t, set()).update(cols)

expected: dict[str, set[str]] = {
    "hospital_sites": {"id","code","name","status","visible_on_board","is_planned","authoritative_site_key","staff_visibility_mode","source_catalog_version","visual_style_key","visual_style_locked"},
    "hospital_floors": {"id","site_id","code","name","status","background_id","viewbox_width","viewbox_height","source_document_sha256","source_document_name","source_revision","catalog_version","verification_status","geometry_status","preview_image_path","operational_image_path","visual_style_key","visual_style_locked","source_declared_area_m2","sort_order"},
    "hospital_rooms": {"id","site_id","floor_id","code","name","room_type","area_label","status","metadata_json","clinical_function","source_room_code","source_room_name","source_area_m2","source_document_sha256","source_revision","catalog_version","verification_status","geometry_status","manual_override","archived_at","archived_by_user_id","revision_reason","sort_order"},
    "hospital_floorplan_backgrounds": {"id","site_id","floor_id","original_name","stored_name","storage_path","render_image_path","status","content_sha256","source_document_sha256","catalog_version","verification_status","visual_style_key"},
    "hospital_floorplan_polygons": {"id","site_id","floor_id","room_id","code","label","points","room_type","active","visible","is_visible","geometry_status","source_document_sha256","catalog_version","verification_status"},
    "enterprise_floorplan_sources": {"id","source_key","site_key","floor_code","document_name","document_sha256","revision_label","source_date","verification_status","notes","metadata_json","created_at","updated_at"},
    "enterprise_floorplan_catalog_versions": {"id","catalog_key","version","catalog_sha256","visual_style_key","visual_style_locked","sites_count","floors_count","rooms_count","status","applied_by_user_id","applied_at","metadata_json"},
    "enterprise_hospital_room_revisions": {"id","room_id","action_key","reason","before_json","after_json","user_id","created_at"},
    "enterprise_floorplan_verification_events": {"id","site_id","floor_id","room_id","event_key","source_sha256","evidence_json","user_id","created_at"},
    "enterprise_command_center_object_positions": {"id","site_id","floor_id","object_type","object_id","room_id","x","y","width","height","source_mode","locked","metadata_json","created_at","updated_at"},
    "app_settings": {"setting_key","setting_value"},
    "system_modules": {"module_key","module_label","status","route_path"},
    "system_module_routes": {"module_key","route_path","status"},
}

for table, cols in expected.items():
    actual = schema.get(table, set())
    check(f"table:{table}", bool(actual), f"columns={len(actual)}")
    missing = sorted(cols - actual)
    check(f"columns:{table}", not missing, "missing=" + ",".join(missing) if missing else f"verified={len(cols)}")

# Verify that the runtime creates the same four canonical 2.6.1 tables.
for table in [
    "enterprise_floorplan_sources",
    "enterprise_floorplan_catalog_versions",
    "enterprise_hospital_room_revisions",
    "enterprise_floorplan_verification_events",
]:
    check(f"php_runtime_table:{table}", table in php)

# Verify that PHP schema repair contains every canonical ALTER column used by runtime.
for table in ["hospital_sites","hospital_floors","hospital_rooms","hospital_floorplan_backgrounds","hospital_floorplan_polygons"]:
    for col in sorted(parse_alter_columns(mig243).get(table,set())):
        check(f"php_runtime_column:{table}.{col}", f"'{col}'" in php or f'"{col}"' in php)

# Critical safety contract around room lifecycle.
for needle, key in [
    ("function balt261_save_manual_room", "room_save_api"),
    ("function balt261_archive_room", "room_archive_api"),
    ("function balt261_restore_room", "room_restore_api"),
    ("function balt261_delete_room_permanently", "room_delete_api"),
    ("Zweryfikowanego pomieszczenia źródłowego nie można trwale usunąć", "source_room_delete_guard"),
    ("balt261_room_operational_references", "operational_reference_guard"),
]:
    check(key, needle in php)

failed = [c for c in checks if not c[1]]
for key, ok, detail in checks:
    print(("PASS" if ok else "FAIL") + "\t" + key + ("\t" + detail if detail else ""))
print(f"SUMMARY\t{len(checks)-len(failed)}/{len(checks)} PASS")
sys.exit(1 if failed else 0)