import hashlib
import json
import re
from pathlib import Path

ROOT = Path(__file__).resolve().parent
TENANTS = {2147001571, 2147001572}

def split_sql(text):
    values = []; start = 0; quoted = False; i = 0
    while i < len(text):
        c = text[i]
        if c == "'":
            if quoted and i + 1 < len(text) and text[i + 1] == "'": i += 2; continue
            if i == 0 or text[i - 1] != '\\': quoted = not quoted
        if c == ',' and not quoted: values.append(text[start:i].strip()); start = i + 1
        i += 1
    assert not quoted
    values.append(text[start:].strip())
    return values

def literal(text):
    if re.fullmatch(r"'?\d+'?", text): return int(text.strip("'"))
    assert text.startswith("'") and text.endswith("'"), 'Unexpected SQL expression'
    return text[1:-1].replace("''", "'").replace("\\'", "'")

def inserted(sql):
    m = re.fullmatch(r'INSERT INTO [`"]?yoshop_(\w+)[`"]?\s+SET\s+(.+)', sql, re.I)
    if m:
        out = {}
        for item in split_sql(m[2]):
            pair = re.fullmatch(r'[`"]?(\w+)[`"]?\s*=\s*(.+)', item)
            assert pair and pair[1].lower() not in out
            out[pair[1].lower()] = literal(pair[2])
        return m[1].lower(), out
    m = re.fullmatch(r'INSERT INTO [`"]?yoshop_(\w+)[`"]?\s*\((.+?)\)\s*VALUES\s*\((.+)\)', sql, re.I)
    assert m, 'Unknown INSERT form'
    columns = [s.strip(' `"').lower() for s in split_sql(m[2])]; values = split_sql(m[3])
    assert len(columns) == len(values) == len(set(columns))
    return m[1].lower(), dict(zip(columns, map(literal, values)))

def audit(sql, original):
    actions = {int(r['id']): r for r in original['action']}
    addresses = {int(r['id']): r for r in original['address']}
    if sql.startswith('INSERT'):
        table, values = inserted(sql)
        assert table in ['sign_action', 'sign_address', 'upload_file']
        if table == 'sign_action':
            assert values['store_id'] in TENANTS and values['create_by'] == values['store_id']
            assert 'CDXSG10C91E' in values['title']
        elif table == 'sign_address':
            assert values['action_id'] in actions
            assert values['create_by'] == int(actions[values['action_id']]['store_id'])
            assert 'CDXSG10C91E' in values['address']
        else:
            assert values['store_id'] in TENANTS and values['storage'] == 'local'
            assert values['file_path'].startswith('aizhct/poc/' + str(values['store_id']) + '/')
            action, address = map(int, values['file_name'][:-4].split('_'))
            assert action in actions and address in addresses
            assert int(addresses[address]['action_id']) == action
            assert int(actions[action]['store_id']) == values['store_id']
    else:
        m = re.fullmatch(r'UPDATE [`"]?yoshop_(\w+)[`"]?\s+SET\s+(.+?)\s+WHERE\s+(.+)', sql, re.I)
        assert m and m[1].lower() in ['sign_action', 'sign_address']
        table = m[1].lower()
        pk = re.search(r'(?<!\w)[`"]?id[`"]?\s*=\s*\x27?(\d+)\b', m[3], re.I)
        assert pk, 'No exact owned PK predicate'
        rows = actions if table == 'sign_action' else addresses
        assert int(pk[1]) in rows
        if table == 'sign_action':
            tenant = re.search(r'[`"]?store_id[`"]?\s*=\s*\x27?(\d+)\b', m[3], re.I)
            assert tenant and int(tenant[1]) == int(rows[int(pk[1])]['store_id'])
    return {'table': table, 'own_scope': True, 'sha256': hashlib.sha256(sql.encode()).hexdigest()}

def categories(rows):
    out = {'all_observed_SQL': len(rows), 'business_SELECT': 0, 'metadata_SQL': 0, 'identity_or_other_SQL': 0, 'DML': 0, 'DDL': 0, 'business_SELECT_by_table': {}}
    for row in rows:
        sql = row['sql'].strip()
        if re.match(r'^(INSERT|UPDATE|DELETE|MERGE|REPLACE)\b', sql, re.I): out['DML'] += 1
        elif re.match(r'^(CREATE|DROP|ALTER)\b', sql, re.I): out['DDL'] += 1
        else:
            table = re.search(r'\b(?:FROM|JOIN)\s+[`"]?yoshop_(sign_action|sign_address|upload_file|store_setting|wxapp_setting)\b', sql, re.I)
            if sql.upper().startswith('SELECT') and table:
                out['business_SELECT'] += 1
                out['business_SELECT_by_table'][table[1].lower()] = out['business_SELECT_by_table'].get(table[1].lower(), 0) + 1
            elif re.match(r'^SHOW\b', sql, re.I) or re.search(r'USER_TAB_COLUMNS|USER_CONSTRAINTS|USER_CONS_COLUMNS|SP_TABLEDEF', sql, re.I): out['metadata_SQL'] += 1
            else: out['identity_or_other_SQL'] += 1
    return out

def main():
    result = {}
    for lane in ['B', 'C']:
        original = json.loads((ROOT / ('final-audit-' + lane + '.json')).read_text())['exact_original_rows']
        source = ROOT / 'private-evidence' / lane / 'private-sql.jsonl'
        rows = [json.loads(line) for line in source.read_text().splitlines()]
        writes = [r for r in rows if re.match(r'^\s*(INSERT|UPDATE|DELETE|MERGE|REPLACE|CREATE|DROP|ALTER)\b', r['sql'], re.I)]
        proofs = [dict(scenario=r['scenario'], **audit(r['sql'], original)) for r in writes]
        assert len(proofs) == (26 if lane == 'B' else 40)
        assert not any(r['scenario'].startswith('PILOT_SG_F_') for r in writes)
        result[lane] = {'business_DML': len(proofs), 'private_raw_sha256': hashlib.sha256(source.read_bytes()).hexdigest(), 'exact_field_and_PK_scope_proofs': proofs, 'observed_SQL_totals': categories(rows), 'observed_SQL_by_phase': {phase: categories([r for r in rows if r['scenario'].startswith('PILOT_SG_' + phase + '_')]) for phase in ['R', 'G', 'F']}}
    (ROOT / 'sql-scope-proof.json').write_text(json.dumps(result, ensure_ascii=False, indent=2) + '\n')
    print(json.dumps({k: {'business_DML': v['business_DML'], 'all_exact_scope': True} for k, v in result.items()}))

if __name__ == '__main__': main()