#!/usr/bin/env python3
import copy,json,re
from pathlib import Path
from compare import ROOT,equal,cases_for

def sql_without_literals(sql):
    def literal(m):
        v=m[0][1:-1]
        return v if re.fullmatch(r'-?\d+',v) else '<LITERAL>'
    sql=re.sub(r"'(?:\\.|''|[^'\\])*'",literal,sql)
    return sql.replace('`','').replace('"','').lower()

def scope_ok(where,tenant,cid,table):
    parts=re.split(r'\s+and\s+',where.strip())
    wanted={'store_id':tenant,'comment_id':cid};got={}
    for p in parts:
        m=re.fullmatch(r'\s*(?:'+table+r'\.)?(store_id|comment_id)\s*=\s*(\d+)\s*',p)
        if not m:return False
        if m[1] in got:return False
        got[m[1]]=int(m[2])
    return got==wanted

def audit_dml(sql,tenant,cid,file_id):
    clean=sql_without_literals(sql)
    m=re.fullmatch(r'delete\s+from\s+yoshop_comment_image\s+where\s+(.+)',clean)
    if m:return scope_ok(m[1],tenant,cid,'yoshop_comment_image')
    m=re.fullmatch(r'update\s+yoshop_comment\s+set\s+(.+?)\s+where\s+(.+)',clean)
    if m:
        if not scope_ok(m[2],tenant,cid,'yoshop_comment'):return False
        cols=[]
        for part in m[1].split(','):
            a=re.fullmatch(r'\s*(\w+)\s*=\s*(?:-?\d+|<literal>)\s*',part)
            if not a:return False
            cols.append(a[1])
        return len(cols)==len(set(cols)) and set(cols)<={'score','content','is_picture','sort','status','is_delete','update_time'}
    m=re.fullmatch(r'insert\s+into\s+yoshop_comment_image\s+set\s+(.+)',clean)
    if m:
        vals={}
        for part in m[1].split(','):
            a=re.fullmatch(r'\s*(\w+)\s*=\s*(\d+)\s*',part)
            if not a or a[1] in vals:return False
            vals[a[1]]=int(a[2])
    else:
        m=re.fullmatch(r'insert\s+into\s+yoshop_comment_image\s*\(([^)]+)\)\s*values\s*\(([^)]+)\)',clean)
        if not m:return False
        keys=[k.strip() for k in m[1].split(',')];values=[v.strip() for v in m[2].split(',')]
        if len(keys)!=len(values) or len(keys)!=len(set(keys)) or not all(re.fullmatch(r'\d+',v) for v in values):return False
        vals=dict(zip(keys,map(int,values)))
    return set(vals)=={'image_id','comment_id','store_id','create_time'} and vals['image_id']==file_id and vals['comment_id']==cid and vals['store_id']==tenant and vals['create_time']>0

def stable(row,ignored=()):return {k:v for k,v in row.items() if k not in ignored}

def audit():
    report={}
    for lane in ('B','C'):
        d=ROOT/'private-evidence'/lane;fixture=json.loads((d/'fixture-journal.json').read_text());cases=cases_for(d);prev=fixture['after'];counts={'DML':0,'DDL':0,'business_SELECT':0,'metadata':0,'own_row_checkpoints':0}
        sqls=[json.loads(l) for l in (d/'private-sql.jsonl').read_text().splitlines()]
        for record in sqls:
            sql=record['sql'];s=record['scenario'];case=cases[s];module=case['module'];own=fixture['owned'][module];tenant=2147001111 if module=='store' else 2147001112
            if re.match(r'^(insert|update|delete)\b',sql,re.I):
                assert case['action'] in ('edit','delete'),s
                assert audit_dml(sql,tenant,own['comment'][0],own['upload_file']),s
                counts['DML']+=1
            elif re.match(r'^(alter|drop|create|truncate|grant|revoke)\b',sql,re.I):raise AssertionError('DDL '+s)
            elif re.match(r'^show\b',sql,re.I) or re.search(r'\bsyscolumns\b|\bsysobjects\b|user_tab_columns',sql,re.I):counts['metadata']+=1
            elif re.match(r'^select\b',sql,re.I):counts['business_SELECT']+=1
        assert counts['DML']==12,lane
        for line in (d/'private-wire.jsonl').read_text().splitlines():
            w=json.loads(line);s=w['scenario'];case=cases[s];now=w['original'];module=case['module'];own=fixture['owned'][module];cid=own['comment'][0];tenant=2147001111 if module=='store' else 2147001112;action=case['action']
            for t in ('user','goods','order','order_goods','upload_file'):equal(prev[t],now[t],s+'.base_'+t)
            if action not in ('edit','delete'):equal(prev,now,s+'.readonly_seven_tables')
            else:
                old_comments={int(r['comment_id']):r for r in prev['comment']};new_comments={int(r['comment_id']):r for r in now['comment']};assert old_comments.keys()==new_comments.keys()
                for k in old_comments:
                    if k!=cid:equal(old_comments[k],new_comments[k],s+'.other_comment')
                target=new_comments[cid];expected=copy.deepcopy(old_comments[cid])
                if action=='edit':
                    form=case['params']['form']
                    for k in ('score','content','status','sort'):expected[k]=type(expected[k])(form[k])
                    expected['is_picture']=type(expected['is_picture'])(int(bool(form['imageIds'])))
                else:expected['is_delete']=type(expected['is_delete'])(1)
                equal(stable(target,('update_time',)),stable(expected,('update_time',)),s+'.modified_comment')
                assert type(target['update_time']) in (int,str) and int(target['update_time'])>0
                old_imgs=[r for r in prev['comment_image'] if int(r['comment_id'])!=cid];new_imgs=[r for r in now['comment_image'] if int(r['comment_id'])!=cid]
                equal(old_imgs,new_imgs,s+'.other_image_rows')
                target_imgs=[r for r in now['comment_image'] if int(r['comment_id'])==cid]
                if action=='delete':equal(prev['comment_image'],now['comment_image'],s+'.soft_delete_preserves_images')
                else:
                    ids=case['params']['form']['imageIds'];assert len(ids)==len(target_imgs)
                    for im,fid in zip(target_imgs,ids):assert int(im['image_id'])==fid and int(im['store_id'])==tenant and int(im['comment_id'])==cid and int(im['id'])>0 and int(im['create_time'])>0
            prev=now;counts['own_row_checkpoints']+=7
        equal(prev,json.loads((d/'read-GREEN.json').read_text())['after'],lane+'.final_rows')
        assert fixture['before_protect']==fixture['after_protect']
        report[lane]=counts
    return report

if __name__=='__main__':
    report=audit();(ROOT/'sql-audit.json').write_text(json.dumps(report,indent=2)+'\n');print(json.dumps(report))