#!/usr/bin/env python3
from pathlib import Path
import re,json,sys
ROOT=Path(__file__).resolve().parents[1]
php=(ROOT/'app/enterprise_clinical_go_live_program_250.php').read_text(encoding='utf-8')
sql=(ROOT/'database/migrations/241_core_2_5_0_clinical_go_live_execution.sql').read_text(encoding='utf-8')
def parse(s):
 out={}
 for m in re.finditer(r'CREATE TABLE IF NOT EXISTS\s+([A-Za-z0-9_]+)\s*\((.*?)\)\s*ENGINE=',s,re.S|re.I):
  cols=[]
  for line in m.group(2).splitlines():
   line=line.strip().rstrip(',')
   if not line or re.match(r'(?i)(PRIMARY|UNIQUE|KEY|CONSTRAINT|FOREIGN|FULLTEXT|SPATIAL)\b',line):continue
   cm=re.match(r'`?([A-Za-z0-9_]+)`?\s+',line)
   if cm:cols.append(cm.group(1))
  out[m.group(1)]=cols
 return out
p=parse(php);q=parse(sql);errors=[]
for table in sorted(set(p)|set(q)):
 if p.get(table)!=q.get(table):errors.append({'table':table,'php_columns':p.get(table),'sql_columns':q.get(table)})
result={'php_tables':len(p),'sql_tables':len(q),'errors':errors}
print(json.dumps(result,ensure_ascii=False,indent=2));sys.exit(1 if errors or len(p)!=9 or len(q)!=9 else 0)