import subprocess,json,time
from pathlib import Path
from mysql_encoding import PROBE_SQL,validate_encoding,consume_probe
r=Path(__file__).resolve().parents[1]
container='foodsafe2-mysql8-stage-20261023'
def query(sql):
 if not sql.lstrip().upper().startswith('SELECT '):raise ValueError('SELECT only')
 cmd=['docker','exec',container,'mysql','--defaults-extra-file=/run/secrets/client.cnf','--default-character-set=utf8mb4','--database=foodsafe2','--batch','--raw','--quick','--skip-column-names','--max-allowed-packet=1G','-e',PROBE_SQL+'\n'+sql]
 p=subprocess.run(cmd,capture_output=True,check=True)
 rows=[json.loads(l)for l in p.stdout.decode('utf8','strict').splitlines() if l]
 if not rows:raise RuntimeError('No MySQL encoding probe')
 validate_encoding(rows[0])
 return rows[1:]
def metadata(name,columns,tail):
 expr=','.join("'%s',%s"%(c,c)for c in columns.split(','))
 return query('SELECT JSON_OBJECT('+expr+') '+tail)
x={'encoding':query(PROBE_SQL)[0]}
validate_encoding(x['encoding'])
x['identity']=query("SELECT JSON_OBJECT('db',DATABASE(),'version',VERSION(),'event_scheduler',@@event_scheduler)")
if x['identity'][0]['db']!='foodsafe2' or x['identity'][0]['event_scheduler']!='OFF':raise RuntimeError('Identity gate')
x['tables']=metadata('tables','TABLE_NAME,ENGINE,AUTO_INCREMENT,TABLE_COLLATION',"FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() ORDER BY TABLE_NAME")
x['columns']=metadata('columns','TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME,COLUMN_TYPE,DATA_TYPE,IS_NULLABLE,COLUMN_DEFAULT,EXTRA,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_NAME,COLLATION_NAME,COLUMN_COMMENT,GENERATION_EXPRESSION',"FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() ORDER BY TABLE_NAME,ORDINAL_POSITION")
x['indexes']=metadata('indexes','TABLE_NAME,INDEX_NAME,NON_UNIQUE,SEQ_IN_INDEX,COLUMN_NAME,SUB_PART,INDEX_TYPE,EXPRESSION',"FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() ORDER BY TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX")
x['foreign_keys']=query("SELECT JSON_OBJECT('TABLE_NAME',k.TABLE_NAME,'CONSTRAINT_NAME',k.CONSTRAINT_NAME,'ORDINAL_POSITION',k.ORDINAL_POSITION,'COLUMN_NAME',k.COLUMN_NAME,'REFERENCED_TABLE_NAME',k.REFERENCED_TABLE_NAME,'REFERENCED_COLUMN_NAME',k.REFERENCED_COLUMN_NAME,'UPDATE_RULE',r.UPDATE_RULE,'DELETE_RULE',r.DELETE_RULE) FROM information_schema.KEY_COLUMN_USAGE k JOIN information_schema.REFERENTIAL_CONSTRAINTS r ON r.CONSTRAINT_SCHEMA=k.CONSTRAINT_SCHEMA AND r.CONSTRAINT_NAME=k.CONSTRAINT_NAME AND r.TABLE_NAME=k.TABLE_NAME WHERE k.TABLE_SCHEMA=DATABASE() AND k.REFERENCED_TABLE_NAME IS NOT NULL ORDER BY k.TABLE_NAME,k.CONSTRAINT_NAME,k.ORDINAL_POSITION")
for key,table,cols,sch in [('routines','ROUTINES','ROUTINE_NAME,ROUTINE_TYPE','ROUTINE_SCHEMA'),('triggers','TRIGGERS','TRIGGER_NAME,EVENT_OBJECT_TABLE','TRIGGER_SCHEMA'),('events','EVENTS','EVENT_NAME,STATUS','EVENT_SCHEMA')]:x[key]=metadata(key,cols,'FROM information_schema.'+table+' WHERE '+sch+'=DATABASE()')
x['counts']={}
parts=[]
parts=[]
for t in x['tables']:
 name=t['TABLE_NAME'];parts.append("SELECT JSON_OBJECT('table', '"+name+"', 'n', COUNT(*)) FROM `"+name.replace('`','``')+"`")
for row in query(' UNION ALL '.join(parts)):x['counts'][row['table']]=str(row['n'])
checks={'role_type':"SELECT JSON_OBJECT('invalid_count',COUNT(*)) FROM roles WHERE type IS NULL OR type<0 OR type>255",'device_types':"SELECT JSON_OBJECT('vendor_kind',v.vendor_kind,'device_type',v.device_type,'n',v.n) FROM (SELECT LOWER(TRIM(COALESCE(vendor,''))) vendor_kind,LOWER(TRIM(COALESCE(device_type,''))) device_type,COUNT(*) n FROM device_iot_configs GROUP BY LOWER(TRIM(COALESCE(vendor,''))),LOWER(TRIM(COALESCE(device_type,'')))) v",'box_scope':"SELECT JSON_OBJECT('unresolved_box_count',COUNT(*)) FROM aibox_box b LEFT JOIN (SELECT aibox_id,COUNT(DISTINCT school_id) school_count,COUNT(DISTINCT canteen_id) canteen_count,SUM(canteen_id IS NULL) null_canteen_count FROM aibox_channels GROUP BY aibox_id)c ON c.aibox_id=b.id WHERE COALESCE(c.school_count,0)<>1 OR COALESCE(c.canteen_count,0)<>1 OR COALESCE(c.null_canteen_count,0)<>0",'channel_scope':"SELECT JSON_OBJECT('invalid_count',COUNT(*)) FROM aibox_channels c LEFT JOIN schools s ON s.id=c.school_id LEFT JOIN canteens k ON k.id=c.canteen_id WHERE s.id IS NULL OR k.id IS NULL OR NOT(k.school_id<=>c.school_id)"}
x['prechecks']={k:query(v) for k,v in checks.items()}
(r/'private/mysql-original-baseline.json').write_text(json.dumps(x,ensure_ascii=False,indent=2))
summary={'identity':x['identity'],'table_count':len(x['tables']),'column_count':len(x['columns']),'foreign_key_columns':len(x['foreign_keys']),'total_rows':sum(map(int,x['counts'].values())),'critical_counts':{k:x['counts'][k]for k in ['morning_checks','audit_logs','aibox_check_record','sampling_records','sample_uploads']},'prechecks':x['prechecks'],'special_objects':{k:len(x[k])for k in ['routines','triggers','events']}}
(r/'evidence/mysql-original-summary.json').write_text(json.dumps(summary,ensure_ascii=False,indent=2));print(json.dumps(summary,ensure_ascii=False))