"""Apply the user's 2026-10-24 explicit decisions only to owned *_stage copies."""
from mysql_baseline import ROOT,q,CMD,ident
from pathlib import Path
import json,gzip,subprocess,sys,collections
FIXED='39DCAD32-991D-E7E8-300C-6416E153A0FB'
def rows(db,table):
 b=json.loads((ROOT/'private'/('upgraded-'+db)/'baseline.json').read_text());names=[c['name'] for c in b['metadata'][table]]
 with gzip.open(ROOT/'private'/('upgraded-'+db)/(table+'.hex.jsonl.gz'),'rt') as f:
  return [dict(zip(names,[None if x is None else bytes.fromhex(x).decode('utf8') for x in json.loads(l)])) for l in f]
def lit(x):
 if x is None:return 'NULL'
 return "CONVERT(0x"+str(x).encode().hex()+" USING utf8mb4)"
for db in ('zhct','zhct2'):
 out=ROOT/'private'/('approved-normalization-'+db+'.json')
 if out.exists():raise SystemExit('Plan exists; review prior state, do not rerun blindly')
 b=json.loads((ROOT/'private'/('upgraded-'+db)/'baseline.json').read_text());meta=json.loads((ROOT/'private'/('metadata-'+db+'_stage.json')).read_text())
 user=[x for x in rows(db,'ydy_user') if x['uuid']==FIXED];restaurants=rows(db,'ydy_restaurants')
 assert len(user)==1 and user[0]['id']=='2' and user[0]['status']=='1' and user[0]['type']=='1'
 assert len(restaurants)==1 and restaurants[0]['id']=='1' and restaurants[0]['is_delete']=='2' and restaurants[0]['status']=='1'
 assert not any(x['user_uuid']==FIXED and x['type']=='3' for x in rows(db,'ydy_user_data'))
 seed=next(x['AUTO_INCREMENT'] for x in meta['tables'] if x['TABLE_NAME']=='ydy_user_data');assert seed and int(seed)>0
 plan={'source_database':db+'_stage','target_database':db+'_stage','approval_date':'2026-10-24','zero_date_cells':[],'nullable_changes':[],
       'permission_insert':{'table':'ydy_user_data','row':{'id':seed,'user_uuid':FIXED,'data_uuid':restaurants[0]['uuid'],'staff_uuid_str':None,'type':'3'}},
       'auto_increment_change':{'table':'ydy_user_data','before':seed,'after':str(int(seed)+1)}}
 for table in ('ydy_menu','ydy_metabolism_pal','ydy_station','ydy_user'):
  for c in b['metadata'][table]:
   if c['data_type'] not in ('date','datetime','timestamp'):continue
   for row in rows(db,table):
    v=row[c['name']]
    if v is not None and (v[:4]=='0000' or v[5:7]=='00' or v[8:10]=='00'):
     assert v in ('0000-00-00','0000-00-00 00:00:00')
     plan['zero_date_cells'].append({'table':table,'pk':{'id':row['id']},'column':c['name'],'before':v,'after':None})
   if c['nullable']=='NO' and any(x['table']==table and x['column']==c['name'] for x in plan['zero_date_cells']):
    plan['nullable_changes'].append({'table':table,'column':c['name'],'before':'NO','after':'YES'})
 assert len(plan['zero_date_cells'])==1435 and len(plan['nullable_changes'])==3
 out.write_text(json.dumps(plan,ensure_ascii=False,indent=2));out.chmod(0o600)
 (ROOT/'private'/('metadata-before-normalization-'+db+'.json')).write_text(json.dumps(meta))
 sql=['USE '+ident(db+'_stage')+';','SET NAMES utf8mb4;',"SET SESSION sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';"]
 for change in plan['nullable_changes']:
  c=next(c for c in b['metadata'][change['table']] if c['name']==change['column']);assert c['extra']=='' and c['default'] is None
  comment="'"+c['comment'].replace("'","''")+"'"
  sql.append('ALTER TABLE '+ident(change['table'])+' MODIFY '+ident(c['name'])+' '+c['type']+' NULL DEFAULT NULL COMMENT '+comment+';')
 sql+=['DELIMITER //','CREATE PROCEDURE front_approved_normalization_20261024()','BEGIN','DECLARE N INT;','DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;','START TRANSACTION;',
       'SELECT COUNT(*) INTO N FROM ydy_user WHERE id=2 AND uuid='+lit(FIXED)+' AND status=1 AND type=1;',"IF N<>1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='fixed user changed'; END IF;",
       'SELECT COUNT(*) INTO N FROM ydy_restaurants WHERE id=1 AND uuid='+lit(restaurants[0]['uuid'])+' AND status=1 AND is_delete=2;',"IF N<>1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='restaurant changed'; END IF;",
       'SELECT COUNT(*) INTO N FROM ydy_user_data WHERE user_uuid='+lit(FIXED)+' AND type=3;',"IF N<>0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='permission already exists'; END IF;"]
 groups=collections.defaultdict(list)
 for x in plan['zero_date_cells']:groups[(x['table'],x['column'],x['before'])].append(x['pk']['id'])
 for (table,col,old),ids in groups.items():
  assert all(x.isdigit() for x in ids)
  sql+=['UPDATE '+ident(table)+' SET '+ident(col)+'=NULL WHERE '+ident(col)+'='+lit(old)+' AND id IN ('+','.join(ids)+');',"IF ROW_COUNT()<>"+str(len(ids))+" THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='zero date count changed'; END IF;"]
 row=plan['permission_insert']['row'];sql+=['INSERT INTO ydy_user_data('+','.join(ident(x) for x in row)+') VALUES('+','.join(lit(x) for x in row.values())+');',"IF ROW_COUNT()<>1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='permission insert failed'; END IF;",'COMMIT;','END//','DELIMITER ;','CALL front_approved_normalization_20261024();','DROP PROCEDURE front_approved_normalization_20261024;']
 text='\n'.join(sql)+'\n';sp=ROOT/'private'/('approved-normalization-'+db+'.sql');sp.write_text(text);sp.chmod(0o600)
 if '--apply' in sys.argv:
  p=subprocess.run(CMD,input=text.encode(),stdout=subprocess.PIPE,stderr=subprocess.PIPE)
  (ROOT/'private'/('approved-normalization-'+db+'.log')).write_bytes(p.stdout+p.stderr)
  if p.returncode:raise SystemExit('Normalization failed; inspect private log, do not rerun')
 print(json.dumps({'database':db+'_stage','zero_date_cells':1435,'new_permission_rows':1,'nullable_columns':3,'applied':'--apply' in sys.argv}))