#!/usr/bin/env python3
"""Validate Baltique OS Core 2.6.0 Hospital Command Center against a MySQL dump.

Usage:
  python tools/validate_core_2_6_0_schema_contract.py /path/to/database.sql

The validator checks the actual source tables consumed by the Command Center and
new tables defined by migration 242. It does not mutate the database.
"""
from __future__ import annotations
import re
import sys
from pathlib import Path

SOURCE_CONTRACT: dict[str, set[str]] = {
    "app_settings": {"setting_key", "setting_value"},
    "hospital_sites": {"id", "code", "name", "status"},
    "hospital_floors": {"id", "site_id", "code", "name", "background_id", "viewbox_width", "viewbox_height"},
    "hospital_rooms": {"id", "site_id", "floor_id", "code", "name", "room_type", "status"},
    "hospital_floorplan_polygons": {"id", "site_id", "floor_id", "room_id", "code", "label", "points", "room_type", "z_index"},
    "hospital_floorplan_backgrounds": {"id", "floor_id", "storage_path", "render_image_path"},
    "theatre_v2_beds": {"id", "hospital_room_id", "bed_code", "status", "patient_id", "operative_case_id", "active", "updated_at"},
    "patients": {"id", "public_code", "first_name", "last_name", "email", "phone", "date_of_birth"},
    "operative_cases": {"id", "patient_id", "procedure_id", "site_id", "scheduled_at", "surgery_date", "status", "go_status", "hospital_room_id"},
    "enterprise_case_spine": {"id", "patient_id", "operative_case_id", "primary_site_id"},
    "enterprise_work_items": {"id", "case_spine_id", "patient_id", "operative_case_id", "work_label", "role_key", "owner_user_id", "priority", "status", "due_at"},
    "or_live_patient_state": {"id", "live_date", "operative_case_id", "patient_id", "current_stage", "current_room_id", "gate_status", "readiness_status", "delay_minutes"},
    "or_live_room_status": {"id", "live_date", "room_id", "room_label", "occupancy_status", "current_case_id", "current_patient_id", "updated_at"},
    "or_live_alerts": {"id", "operative_case_id", "patient_id", "severity", "title", "status"},
    "enterprise_pacu_discharge_episodes": {"id", "case_spine_id", "patient_id", "operative_case_id", "pacu_status", "discharge_status"},
    "inventory_items": {"id", "name", "active", "min_stock_alert"},
    "inventory_lots": {"id", "inventory_item_id", "lot_number", "serial_number", "qty_on_hand", "status"},
    "inventory_stock": {"id", "inventory_item_id", "qty_on_hand", "qty_reserved", "reorder_level"},
}

NEW_CONTRACT: dict[str, set[str]] = {
    "enterprise_command_center_role_presets": {"id", "role_key", "preset_key", "preset_label", "layers_json", "priorities_json", "object_permissions_json", "active"},
    "enterprise_command_center_user_preferences": {"id", "user_id", "site_id", "floor_id", "preset_key", "layers_json", "auto_refresh_seconds"},
    "enterprise_command_center_object_positions": {"id", "site_id", "floor_id", "object_type", "object_id", "room_id", "x", "y", "width", "height"},
    "enterprise_command_center_snapshots": {"id", "snapshot_key", "site_id", "floor_id", "role_key", "operational_date", "payload_hash", "payload_json", "expires_at"},
    "enterprise_command_center_action_log": {"id", "action_uid", "user_id", "role_key", "site_id", "floor_id", "patient_id", "operative_case_id", "work_item_id", "action_key", "action_status"},
    "enterprise_command_center_handoffs": {"id", "handoff_uid", "shift_date", "site_id", "floor_id", "role_key", "title", "priority", "status"},
}


def parse_tables(sql: str) -> dict[str, set[str]]:
    tables: 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=|;)", re.I | re.S)
    for match in pattern.finditer(sql):
        name = match.group(1)
        body = match.group(2)
        cols: set[str] = set()
        for line in body.splitlines():
            line = line.strip()
            cm = re.match(r"`([A-Za-z0-9_]+)`\s+", line)
            if not cm:
                cm = re.match(r"([A-Za-z0-9_]+)\s+(?:BIGINT|INT|VARCHAR|CHAR|TEXT|LONGTEXT|JSON|DATE|DATETIME|DECIMAL|TINYINT|ENUM|BOOLEAN|FLOAT|DOUBLE)\b", line, re.I)
            if cm:
                cols.add(cm.group(1))
        tables[name] = cols
    return tables


def check(contract: dict[str, set[str]], tables: dict[str, set[str]], label: str) -> list[str]:
    failures: list[str] = []
    for table, required in contract.items():
        if table not in tables:
            failures.append(f"{label}: missing table {table}")
            continue
        missing = sorted(required - tables[table])
        if missing:
            failures.append(f"{label}: {table} missing columns: {', '.join(missing)}")
        else:
            print(f"PASS {label} {table} ({len(required)} required columns)")
    return failures


def main() -> int:
    if len(sys.argv) != 2:
        print(__doc__.strip(), file=sys.stderr)
        return 2
    dump = Path(sys.argv[1])
    if not dump.is_file():
        print(f"Dump not found: {dump}", file=sys.stderr)
        return 2
    sql = dump.read_text(encoding="utf-8", errors="ignore")
    tables = parse_tables(sql)
    failures = check(SOURCE_CONTRACT, tables, "source")

    migration = Path(__file__).resolve().parents[1] / "database/migrations/242_core_2_6_0_enterprise_hospital_command_center.sql"
    migration_tables = parse_tables(migration.read_text(encoding="utf-8", errors="ignore"))
    failures.extend(check(NEW_CONTRACT, migration_tables, "migration"))

    if failures:
        for failure in failures:
            print("FAIL", failure)
        print(f"SUMMARY FAIL {len(failures)} issue(s)")
        return 1
    print(f"SUMMARY PASS source_tables={len(SOURCE_CONTRACT)} new_tables={len(NEW_CONTRACT)}")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())