"""Derive the 62-column Food proposal from already-captured real metadata; DB0."""
import hashlib,json,re
from pathlib import Path
root=Path(__file__).parent
cols={}
for lane in ('B','C'):
 raw=json.loads((root/'private'/(lane+'-readonly')/'read-only.json').read_text())
 cols[lane]=[c for c in raw['schema']['columns'] if c['table_name'].lower()=='ydy_foods_composition']
names=[c['column_name'].upper() for c in cols['C']]
assert len(names)==62 and len(set(names))==62
keep=['column_name','data_type','column_type','data_length','data_precision','data_scale','datetime_precision','character_maximum_length','char_length','char_used','nullable','is_nullable','column_default','data_default','extra','column_key','column_id','ordinal_position']
fields=[]
for c in cols['C']:
 name=c['column_name'].upper();assert re.fullmatch('[A-Z_][A-Z_0-9]*',name)
 b=next(b for b in cols['B'] if b['column_name'].upper()==name)
 fields.append({'name':name,'B':{k:b[k] for k in keep if k in b},'C':{k:c[k] for k in keep if k in c},'change_proposed':name in ('CREATE_TIME','UPDATE_TIME')})
non=[f for f in fields if f['name']!='UPDATE_TIME'];other=[f for f in fields if not f['change_proposed']]
contract={'status':'DERIVED_FROM_REAL_METADATA_NOT_FRESH_NATURAL_DATE_AUDIT; NO_SQL_EXECUTION','official_source':'store/db/v1.1.0.sql','official_source_sha256':'4bedf062abf338a29ee1c24860a3abc407d7088eb7b6fd35f0c3f87882450da2','columns':fields,'non_UPDATE_TIME_fields61':[f['name'] for f in non],'other60_immutable_fields':[f['name'] for f in other],'actual_LOB_fields':[f['name'] for f in fields if f['C']['data_type'] in ('CLOB','BLOB','LONGVARCHAR','TEXT')],'actual_trigger_comparison_groups':{'numeric':sum(f['C']['data_type'] in ('DECIMAL','BIGINT','SMALLINT','TINYINT') for f in non),'varchar':sum(f['C']['data_type']=='VARCHAR' for f in non),'other_datetime':sum(f['C']['data_type']=='DATETIME' for f in non)},'fresh_before_after_required':['complete62 raw native typed rows','other60 per-row typed hashes','DATE0 cast mapping old/new for every ID and two fields','calendar/NULL/zero/nonzero fraction counts','table GET_DDL','full USER_INDEXES + USER_IND_COLUMNS + PK pointers','FK/dependencies','trigger collision0/current trigger0']}
(root/'food-date-field-contract.json').write_text(json.dumps(contract,ensure_ascii=False,indent=2)+'\n')
clauses=[]
for f in non:
 n=f['name'];typ=f['C']['data_type']
 if typ=='VARCHAR':
  clauses.append(f"    (NVL(LENGTHB(:NEW.{n}),-1) <> NVL(LENGTHB(:OLD.{n}),-1) OR NVL(RAWTOHEX(CHARTOBIN(:NEW.{n})),'#') <> NVL(RAWTOHEX(CHARTOBIN(:OLD.{n})),'#'))")
 else:
  assert typ in ('DECIMAL','BIGINT','SMALLINT','TINYINT','DATETIME')
  clauses.append(f'    ((:NEW.{n} IS NULL AND :OLD.{n} IS NOT NULL) OR (:NEW.{n} IS NOT NULL AND :OLD.{n} IS NULL) OR (:NEW.{n} IS NOT NULL AND :OLD.{n} IS NOT NULL AND :NEW.{n} <> :OLD.{n}))')
sql='''-- SOURCE PROPOSAL ONLY. Root alone may approve/execute after fresh62/map/index closure.
ALTER TABLE ZHCT_APP.YDY_FOODS_COMPOSITION MODIFY (
 CREATE_TIME DATETIME(0) DEFAULT CURRENT_TIMESTAMP(0) NOT NULL,
 UPDATE_TIME DATETIME(0) DEFAULT CURRENT_TIMESTAMP(0) NOT NULL
);
CREATE TRIGGER ZHCT_APP.TRG_YDY_FOODS_COMPOSITION_UT
BEFORE UPDATE ON ZHCT_APP.YDY_FOODS_COMPOSITION
FOR EACH ROW
BEGIN
 IF NOT UPDATING('UPDATE_TIME') THEN
  IF
'''+ '\n OR\n'.join(clauses)+'''
  THEN
   :NEW.UPDATE_TIME := TO_TIMESTAMP(TO_CHAR(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS');
  END IF;
 END IF;
END;
/
'''
(root/'food-date-proposal.sql').write_text(sql)
print(json.dumps({'DB':0,'metadata_fields':len(fields),'non_update_fields':len(non),'other60':len(other),'LOB_fields':contract['actual_LOB_fields'],'comparison_groups':contract['actual_trigger_comparison_groups'],'proposal_sha256':hashlib.sha256(sql.encode()).hexdigest()}))