import json
import re
from pathlib import Path

P=Path(__file__).resolve().parent
BUSINESS={'staff','staff_store_user','staff_face','nation','sport','nurtrition','nutrition_meals','nutrition_lack','config_enum','store_user','store_user_role','store_user_relation','store_role','store_role_menu','store_menu','store_menu_api','store_api','user','upload_file','meal_recommend'}
SCOPED={'staff','staff_store_user','staff_face','nurtrition','nutrition_meals','nutrition_lack'}


def audit(rows):
    assert len(rows)%3==0
    out={'framework_plans':0,'actual_prepare':0,'actual_execute':0,'business_SELECT':0,'HTTP_business_SELECT':0,'Auth_preparation_business_SELECT':0,'metadata_SELECT':0,'metadata_CALL':0,'metadata_SHOW':0,'DML':0,'DDL':0,'scenario_counts':{}}
    for index in range(0,len(rows),3):
        plan,prepared,execution=rows[index:index+3]
        assert plan['layer'].startswith('framework original bind plan')
        assert prepared['layer']=='actual PDO.prepare' and execution['layer']=='actual PDOStatement prepare/bindValue/execute'
        sql=plan['sql_input']
        assert sql==prepared['queryString']==execution['queryString']
        assert plan['scenario']==prepared['scenario']==execution['scenario']
        assert prepared['prepared'] is True and execution['success'] is True
        assert execution['execute_array'] is None
        expected={}
        binds=plan['bind_plan']
        for key,value in (enumerate(binds) if isinstance(binds,list) else binds.items()):
            parameter=str(key+1) if isinstance(binds,list) else ':'+key
            expected[parameter]=value if isinstance(value,list) else [value,2]
        assert set(expected)==set(execution['bindValue'])
        for key,(value,typ) in expected.items():
            got=execution['bindValue'][key]
            assert got['ok'] is True and got['pdo_type']==typ and type(got['value']) is type(value) and got['value']==value
        plain=re.sub(r"'(?:''|\\.|[^'\\])*'",'?',sql)
        assert not re.search(r';|--|/\*|\*/|#|\b(?:INSERT|UPDATE|DELETE|REPLACE|MERGE|CREATE|ALTER|DROP|TRUNCATE|GRANT|REVOKE|INTO|OUTFILE|DUMPFILE|LOAD|DO|EXEC|EXECUTE|SLEEP|BENCHMARK)\b',plain,re.I)
        operation=execution['operation'];scenario=plan['scenario']
        tables=set(re.findall(r'\byoshop_([a-z_0-9]+)',plain,re.I))
        assert tables<=BUSINESS
        if operation=='metadata_CALL':
            assert sql=='CALL SP_TABLEDEF(?, ?)' and plan['procedure'] is False
            assert expected['1']==['AIZHCT_APP',2]
            assert expected['2'][1]==2 and expected['2'][0].startswith('YOSHOP_') and expected['2'][0][7:].lower() in BUSINESS
        elif operation=='metadata_SHOW':
            assert re.fullmatch(r'SHOW FULL COLUMNS FROM `yoshop_[a-z_]+`',sql) and not expected
        else:
            assert operation=='SELECT' and re.match(r'^SELECT\b',sql,re.I)
            if tables:
                identifiers=plain.replace('`','').replace('"','')
                assert not re.search(r'\b\w+\s*\.\s*yoshop_',identifiers,re.I), 'No explicit cross-schema business table in this batch'
                module='terminal' if '_TERMINAL_' in scenario else 'store'
                if scenario.startswith('PILOT_RR_'):
                    tenant=2147001112 if module=='terminal' else 2147001111
                    for table in tables&SCOPED:
                        match=re.search(r'\b'+table+r'\.store_id\s*=\s*(:\w+)',identifiers,re.I)
                        # Actual SQL qualifies with full physical yoshop_ table name.
                        if not match:match=re.search(r'\byoshop_'+table+r'\.store_id\s*=\s*(:\w+)',identifiers,re.I)
                        assert match and execution['bindValue'][match.group(1)]['value']==tenant
                    if tables&{'nation','sport'}:
                        assert len(expected)==1 and next(iter(expected.values()))[0]==0
                    if tables=={'config_enum'}:
                        assert next(iter(expected.values()))[0] in [6,7,8,9,10,11,'6','7','8','9','10','11']
                out['business_SELECT']+=1
                if scenario.startswith('PILOT_RR_'):out['HTTP_business_SELECT']+=1
                else:
                    assert scenario.startswith('AUTH_PREPARATION')
                    out['Auth_preparation_business_SELECT']+=1
            else:out['metadata_SELECT']+=1
        if operation in ['metadata_CALL','metadata_SHOW']:out[operation]+=1
        out['framework_plans']+=1;out['actual_prepare']+=1;out['actual_execute']+=1
        out['scenario_counts'][scenario]=out['scenario_counts'].get(scenario,0)+1
    return out


def all_audits():
    out={}
    for lane in 'BC':
        out[lane]={}
        for phase,name in [('original','pdo-native.jsonl'),('final','pdo-native-final.jsonl')]:
            rows=[json.loads(line) for line in (P/'private-evidence'/lane/name).read_text().splitlines()]
            out[lane][phase]=audit(rows)
    return out


if __name__=='__main__':
    result=all_audits();(P/'sql-audit.json').write_text(json.dumps(result,ensure_ascii=False,indent=2)+'\n')
    print(json.dumps({lane:{phase:{k:v for k,v in r.items() if k!='scenario_counts'} for phase,r in phases.items()} for lane,phases in result.items()}))