#!/usr/bin/env python3
from __future__ import annotations
import re, sys
from pathlib import Path
ROOT=Path(__file__).resolve().parents[1]
MIG=(ROOT/'database/migrations/247_core_2_7_2_procurement_email_patient_ai.sql').read_text(encoding='utf-8')
PHP='\n'.join((ROOT/p).read_text(encoding='utf-8') for p in [
 'app/enterprise_procurement_automation_272.php','app/enterprise_patient_ai_qa_272.php','app/enterprise_release_272.php'
])

def split_defs(body:str):
    out=[]; cur=[]; depth=0; quote=None; esc=False
    for ch in body:
        if quote:
            cur.append(ch)
            if esc: esc=False
            elif ch=='\\': esc=True
            elif ch==quote: quote=None
            continue
        if ch in "'\"`": quote=ch; cur.append(ch); continue
        if ch=='(': depth+=1
        elif ch==')': depth-=1
        if ch==',' and depth==0:
            out.append(''.join(cur).strip()); cur=[]
        else: cur.append(ch)
    if cur: out.append(''.join(cur).strip())
    return out

tables={}
for m in re.finditer(r'CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS\s+`?([a-zA-Z0-9_]+)`?\s*\((.*?)\)\s*ENGINE=',MIG,re.I|re.S):
    table=m.group(1); body=m.group(2); cols=[]
    for d in split_defs(body):
        mm=re.match(r'`?([a-zA-Z0-9_]+)`?\s+',d)
        if not mm: continue
        name=mm.group(1)
        if name.upper() in {'PRIMARY','UNIQUE','KEY','CONSTRAINT','FOREIGN','CHECK','INDEX','FULLTEXT','SPATIAL'}: continue
        cols.append(name)
    tables[table]=set(cols)

expected_proc={
'enterprise_procurement_suppliers','enterprise_procurement_supplier_contacts','enterprise_procurement_item_mappings','enterprise_procurement_routing_rules','enterprise_procurement_requests','enterprise_procurement_request_items','enterprise_purchase_orders','enterprise_purchase_order_items','enterprise_purchase_order_dispatches','enterprise_purchase_order_events','enterprise_supplier_acknowledgements','enterprise_procurement_runs'}
expected_ai={'enterprise_patient_ai_knowledge_items','enterprise_patient_ai_conversations','enterprise_patient_ai_messages','enterprise_patient_ai_escalations','enterprise_patient_ai_provider_runs','enterprise_patient_ai_feedback','enterprise_patient_ai_events'}
issues=[]
if set(tables)!=(expected_proc|expected_ai):
    issues.append(f'table set mismatch missing={sorted((expected_proc|expected_ai)-set(tables))} extra={sorted(set(tables)-(expected_proc|expected_ai))}')

# Extract literal INSERT and UPDATE column references from PHP source.
for m in re.finditer(r'INSERT\s+INTO\s+([a-zA-Z0-9_]+)\s*\(([^)]+)\)',PHP,re.I|re.S):
    table=m.group(1)
    if table not in tables: continue
    cols=[c.strip().strip('`') for c in m.group(2).split(',')]
    bad=[c for c in cols if c and c not in tables[table]]
    if bad: issues.append(f'INSERT {table}: unknown columns {bad}')

for m in re.finditer(r'UPDATE\s+([a-zA-Z0-9_]+)\s+SET\s+(.*?)(?:\s+WHERE\s+|["\'])',PHP,re.I|re.S):
    table=m.group(1)
    if table not in tables: continue
    segment=m.group(2)
    cols=[]
    for cm in re.finditer(r'(?:^|,)\s*`?([a-zA-Z0-9_]+)`?\s*=',segment): cols.append(cm.group(1))
    bad=[c for c in cols if c not in tables[table]]
    if bad: issues.append(f'UPDATE {table}: unknown columns {bad}')

# Required safety/traceability columns.
required={
 'enterprise_procurement_suppliers':{'email_to','approval_status','verified_at','auto_email_enabled','require_manual_approval'},
 'enterprise_purchase_orders':{'status','snapshot_sha256','public_token_hash','public_token_expires_at','approved_by'},
 'enterprise_purchase_order_dispatches':{'idempotency_key','mail_queue_id','automatic_flag','snapshot_sha256'},
 'enterprise_supplier_acknowledgements':{'response_status','source_ip','created_at'},
 'enterprise_patient_ai_knowledge_items':{'approval_status','risk_class','auto_answer_allowed','external_ai_allowed','source_sha256'},
 'enterprise_patient_ai_messages':{'message_sha256','risk_class','answer_mode','knowledge_ids_json','external_processing_consent'},
 'enterprise_patient_ai_escalations':{'target_role','priority','reason_code','status','work_item_id'},
 'enterprise_patient_ai_provider_runs':{'prompt_sha256','response_sha256','status','provider','model'},
}
for table,cols in required.items():
    missing=cols-tables.get(table,set())
    if missing: issues.append(f'{table}: missing required columns {sorted(missing)}')

for t in sorted(tables): print(f'PASS table {t} columns={len(tables[t])}')
if issues:
    for x in issues: print('FAIL',x)
    print(f'FAILED schema contract {len(issues)} issue(s)',file=sys.stderr); sys.exit(1)
print(f'PASS schema contract 2.7.2 tables={len(tables)}')