"""Static contract checks only. Never connects to a database or executes SQL."""
import json
import re
from pathlib import Path

ROOT = Path(__file__).resolve().parent
sql = (ROOT / '01-schema-review.sql').read_text()
config = (ROOT / '02-config-review.sql').read_text()
tables = re.findall(r'CREATE TABLE `([^`]+)` \((.*?)\) ENGINE=InnoDB[^;]+;', sql, re.S)
expected = {'ydy_tchef_sync_map', 'ydy_tchef_sync_task', 'ydy_tchef_sync_task_item', 'ydy_tchef_dish_snapshot'}
assert {name for name, _ in tables} == expected and len(tables) == 4
lifecycle = {
    'is_delete': "tinyint(4) unsigned NOT NULL DEFAULT '0'",
    'create_by': "varchar(36) NOT NULL DEFAULT ''",
    'create_time': 'datetime NOT NULL DEFAULT CURRENT_TIMESTAMP',
    'update_time': 'datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
}
checks = []
for name, body in tables:
    cols = re.findall(r'^  `([^`]+)`', body, re.M)
    assert len(cols) == len(set(cols)), name
    for col, decl in lifecycle.items():
        assert f'`{col}` {decl}' in body, (name, col)
    for _, col_list in re.findall(r'(?:UNIQUE )?KEY `([^`]+)` \(([^)]+)\)', body):
        assert set(re.findall(r'`([^`]+)`', col_list)) <= set(cols), name
    checks.append({'table': name, 'columnCount': len(cols), 'lifecycle': 'passed', 'indexColumnReferences': 'passed'})
adds = re.findall(r'ADD COLUMN `([^`]+)`', sql)
assert adds == ['condiment_state','name_english','description','cuisine_name','flavor_name','cooking_time_seconds']
assert 'ADD COLUMN `update_time`' not in sql
assert 'PREPARE tchef_update_time_stmt' not in sql
assert 'UNIQUE KEY `uk_active_remote` (`active_remote_id`, `remote_kind`, `connection_key`, `local_scope`)' in sql
assert 'UNIQUE KEY `uk_primary_dish` (`primary_local_id`, `connection_key`, `local_scope`)' in sql
assert 'UNIQUE KEY `uk_idempotency` (`idempotency_key`)' in sql
# Pure-Python model of nullable-key behavior; application must maintain helper values atomically.
def remote_key(row):
    remote, kind, conn, scope, local, active, deleted, primary = row
    return (remote,kind,conn,scope) if active and not deleted else None
def primary_key(row):
    remote, kind, conn, scope, local, active, deleted, primary = row
    return (local,conn,scope) if active and not deleted and kind == 'dish' and primary else None
def allowed(rows):
    return all(len(keys)==len(set(keys)) for keys in ([k for k in map(fn,rows) if k is not None] for fn in [remote_key,primary_key]))
a=('r1','dish','c1','s1',100,1,0,1)
fixtures=[
    ('duplicate remote rejected', [a,('r1','dish','c1','s1',101,1,0,1)],False),
    ('second primary rejected', [a,('r2','dish','c1','s1',100,1,0,1)],False),
    ('historical mapping allowed', [a,('r1','dish','c1','s1',101,0,0,1)],True),
    ('different scope allowed', [a,('r1','dish','c1','s2',100,1,0,1)],True),
    ('different kind allowed', [a,('r1','condiment','c1','s1',100,1,0,0)],True),
    ('soft-deleted history allowed', [a,('r1','dish','c1','s1',101,1,1,1)],True),
]
for label,rows,result in fixtures:
    assert allowed(rows)==result,label
for text in [sql,config]:
    code='\n'.join(l for l in text.splitlines() if not l.lstrip().startswith('--'))
    assert not re.search(r'\b(DROP|TRUNCATE|DELETE\s+FROM)\b',code,re.I)
    assert not re.search(r'\b[A-Za-z0-9]{35,}\b',code)
assert '"enable":0' in config and '"accessKey":"","secretKey":""' in config
assert 'JSON_OBJECT(' not in config
assert 'GENERATED ALWAYS' not in sql
assert not re.search(r'`\w+`\s+json\b', sql, re.I)
assert '@tchef_restaurant_id IS NOT NULL' in config and 'NOT EXISTS' in config
assert 'INSERT INTO `ydy_config`' not in sql
assert 'is_auto_feed`' not in sql and 'CREATE TABLE `ydy_tchef_food_profile`' not in sql
# Actual planned indexed identifier byte lengths stay under even 767-byte legacy limit.
assert max(128+2+64+64,8+64+64,128+2+64+64,128+64+64+8) < 767
report={'mode':'static-only','databaseConnected':False,'sqlExecuted':False,'engineSyntaxVerified':False,
        'plannedAddedColumns':adds,'newTableCount':4,'tableChecks':checks,
        'uniquenessModelCases':[{'name':label,'passed':True} for label,_,_ in fixtures],
        'configDefault':'disabled, empty credentials, restaurant scope unset',
        'limitations':['Targets MySQL 5.6 LONGTEXT and application-maintained nullable unique keys','Target schema not queried','No SQL-engine execution or performance test']}
(ROOT/'validation.json').write_text(json.dumps(report,ensure_ascii=False,indent=2)+'\n')
print(json.dumps({'addedColumns':len(adds),'newTables':len(tables),'uniquenessModelCases':len(fixtures),'staticChecks':'passed','sqlExecuted':False},ensure_ascii=False))