"""Generate rehearsal DDL only; no data mutation or production executable package."""
from pathlib import Path
import json,re,hashlib,collections
ROOT=Path(__file__).resolve().parents[1]
def ident(s):
    if not re.fullmatch(r'[A-Za-z0-9_]+',s):raise ValueError('identifier')
    return '"'+s.upper()+'"'
def lit(s):
    return "'"+str(s).replace("'","''")+"'"
def enum_values(t):
    v=re.findall(r"'((?:[^'\\]|\\.|'')*)'",t)
    if 'enum('+','.join(lit(x) for x in v)+')'!=t:raise ValueError('unreviewed enum escapes')
    return v
BOUNDS={'tinyint':(-128,127,255),'smallint':(-32768,32767,65535),'mediumint':(-8388608,8388607,16777215),'int':(-2147483648,2147483647,4294967295),'bigint':(-9223372036854775808,9223372036854775807,18446744073709551615)}
def target(c):
    t=c['DATA_TYPE'];u='unsigned' in c['COLUMN_TYPE']
    if t=='tinyint':return 'SMALLINT'
    if t=='smallint':return 'INT' if u else 'SMALLINT'
    if t=='mediumint':return 'INT'
    if t=='int':return 'BIGINT' if u else 'INT'
    if t=='bigint':return 'DECIMAL(20,0)' if u else 'BIGINT'
    if t=='decimal':return 'DECIMAL('+c['NUMERIC_PRECISION']+','+c['NUMERIC_SCALE']+')'
    if t=='float':return 'REAL'
    if t=='double':return 'DOUBLE'
    if t in ('char','varchar','enum'):return ('CHAR' if t=='char' else 'VARCHAR')+'('+c['CHARACTER_MAXIMUM_LENGTH']+' CHAR)'
    if t.endswith('text'):return 'CLOB'
    if t=='date':return 'DATE'
    if t in ('datetime','timestamp'):return 'TIMESTAMP('+str(c['DATETIME_PRECISION'] or 0)+')'
    if t=='time':return 'TIME('+str(c['DATETIME_PRECISION'] or 0)+')'
    raise ValueError('unmapped '+t)
def name(kind,s):return kind+'_'+hashlib.sha256(s.encode()).hexdigest()[:24].upper()
def build(db):
    m=json.loads((ROOT/'private'/('metadata-'+db+'_stage.json')).read_text())
    base=json.loads((ROOT/'private'/('final-'+db)/'baseline.json').read_text())
    tables={t['TABLE_NAME']:t for t in m['tables']}; cols=collections.defaultdict(list);indexes=collections.defaultdict(list)
    for c in m['columns']:cols[c['TABLE_NAME']].append(c)
    for i in m['indexes']:indexes[(i['TABLE_NAME'],i['INDEX_NAME'])].append(i)
    statements=[];post=[];mapping=[];ixmap=[];pending=[]
    for table,cc in cols.items():
        defs=[];src={c['name']:c for c in base['metadata'][table]}
        for c in cc:
            cn=c['COLUMN_NAME'];t=c['DATA_TYPE'];auto='auto_increment' in c['EXTRA'];d=src[cn]['default'];dt=target(c);piece=ident(cn)+' '+dt
            if auto:piece+=' IDENTITY('+str(tables[table]['AUTO_INCREMENT'] or 1)+',1)'
            if d is not None and not auto:
                if re.fullmatch(r'CURRENT_TIMESTAMP(?:\(\d+\))?',d,re.I):piece+=' DEFAULT '+d.upper()
                elif t in BOUNDS or t in ('decimal','float','double'):
                    if not re.fullmatch(r'-?\d+(?:\.\d+)?(?:[Ee][+-]?\d+)?',d):raise ValueError('numeric default')
                    piece+=' DEFAULT '+d
                else:
                    if t in ('date','datetime','timestamp') and (d.startswith('0000') or '-00' in d[:10]):raise ValueError('zero default')
                    piece+=' DEFAULT '+lit(d)
            if 'on update CURRENT_TIMESTAMP' in c['EXTRA']:piece+=' ON UPDATE SYSDATE'
            if c['IS_NULLABLE']=='NO':piece+=' NOT NULL'
            cond=None;u='unsigned' in c['COLUMN_TYPE']
            if t in BOUNDS:
                lower,upper,umax=BOUNDS[t];lower,upper=(0,umax) if u else (lower,upper)
                cond=ident(cn)+' BETWEEN '+str(lower)+' AND '+str(upper)
            elif u:cond=ident(cn)+'>=0'
            if cond and not auto:piece+=' CHECK('+cond+')'
            if cond and auto:
                v=':NEW.'+ident(cn);inv=v+'<'+str(lower)+' OR '+v+'>'+str(upper)
                post.append({'kind':'identity_range','table':table,'sql':'CREATE TRIGGER '+ident(name('U',table+'.'+cn))+' BEFORE INSERT ON '+ident(table)+' FOR EACH ROW BEGIN IF '+inv+" THEN RAISE_APPLICATION_ERROR(-20001,'identity range'); END IF; END;"})
            if t=='enum':piece+=' CHECK('+ident(cn)+' IN ('+','.join(lit(v) for v in enum_values(c['COLUMN_TYPE']))+'))'
            if t in ('float','double') and re.match(r'\w+\(\d+,\d+\)',c['COLUMN_TYPE']):pending.append({'table':table,'column':cn,'issue':'fixed_scale_float_rounding_probe_required'})
            if c['COLLATION_NAME'] and c['COLLATION_NAME'].endswith('_bin'):pending.append({'table':table,'column':cn,'issue':'binary_collation_query_semantics_required'})
            defs.append(piece);mapping.append({**c,'COLUMN_DEFAULT':d,'COLUMN_COMMENT':src[cn]['comment'],'TARGET_TYPE':dt,'TARGET_IDENTITY_SEED':int(tables[table]['AUTO_INCREMENT'] or 1) if auto else None})
        pk=indexes[(table,'PRIMARY')];defs.append('PRIMARY KEY('+','.join(ident(i['COLUMN_NAME']) for i in pk)+')')
        statements.append({'kind':'table','table':table,'sql':'CREATE TABLE '+ident(table)+'(\n'+',\n'.join(defs)+'\n)'})
    seen={}
    for (table,ix),ii in sorted(indexes.items(),key=lambda x:(int(x[1][0]['NON_UNIQUE']),x[0][1]!='PRIMARY',x[0])):
        sig=(table,tuple((i['COLUMN_NAME'],i['SUB_PART']) for i in ii));unique=ii[0]['NON_UNIQUE']=='0'
        if sig in seen:
            ixmap.append({'table':table,'source':ix,'source_index':ix,'target':seen[sig],'reuse':True,'equivalent_reuse':True,'source_unique':unique});continue
        if ix=='PRIMARY':seen[sig]='PRIMARY';ixmap.append({'table':table,'source':ix,'source_index':ix,'target':'PRIMARY','reuse':False,'equivalent_reuse':False,'source_unique':True});continue
        targetix=name('I',table+'.'+ix);seen[sig]=targetix;fields=[]
        for i in ii:
            if i['INDEX_TYPE']!='BTREE' or i['COLLATION'] not in ('A',None):raise ValueError('unreviewed index')
            v=ident(i['COLUMN_NAME']);c=next(c for c in cols[table] if c['COLUMN_NAME']==i['COLUMN_NAME'])
            if i['SUB_PART']:v='CAST(SUBSTR('+v+',1,'+i['SUB_PART']+') AS VARCHAR('+i['SUB_PART']+' CHAR))'
            if c['COLLATION_NAME'] and c['COLLATION_NAME'].endswith('_bin'):
                chars=int(i['SUB_PART'] or c['CHARACTER_MAXIMUM_LENGTH'])
                v='CAST(HEX(RTRIM('+v+')) AS VARCHAR('+str(chars*8)+' CHAR))'
            fields.append(v)
        post.append({'kind':'index','table':table,'sql':'CREATE '+('UNIQUE ' if unique else '')+'INDEX '+ident(targetix)+' ON '+ident(table)+'('+','.join(fields)+')'})
        ixmap.append({'table':table,'source':ix,'source_index':ix,'target':targetix,'reuse':False,'equivalent_reuse':False,'source_unique':unique})

    for table,cc in base['metadata'].items():
        for col in cc:
            if col['comment']:
                post.append({'kind':'comment','table':table,'sql':'COMMENT ON COLUMN '+ident(table)+'.'+ident(col['name'])+' IS '+lit(col['comment'])})
    out=ROOT/'private'/('final-schema-'+db);out.mkdir(mode=0o700,exist_ok=True)
    for f,obj in [('statements.json',statements+post),('columns.json',mapping),('indexes.json',ixmap),('pending.json',pending)]:
        (out/f).write_text(json.dumps(obj,ensure_ascii=False,indent=2))
    print(json.dumps({'db':db,'tables':len(statements),'columns':len(mapping),'post_statements':len(post),'pending_contract_items':len(pending)}))
if __name__=='__main__':
    for db in ('zhct','zhct2'):build(db)