import re

with open(r'c:\fuentes\proyectos delphi\RAUL_ASENCIO\GESTION\sql\PASTELERIAS-struct.sql', 'r', encoding='utf-8', errors='ignore') as f:
    content = f.read()

# Find all tables and their columns
# CREATE TABLE `table_name` ( ... );
table_defs = {}
table_pattern = re.compile(r'CREATE TABLE `([^`]+)` \((.*?)\) ENGINE=', re.DOTALL)
for match in table_pattern.finditer(content):
    table_name = match.group(1)
    body = match.group(2)
    cols = set()
    for line in body.split('\n'):
        line = line.strip()
        col_match = re.match(r'^`([^`]+)`', line)
        if col_match:
            cols.add(col_match.group(1))
    table_defs[table_name] = cols

# Find all triggers
trigger_pattern = re.compile(r'CREATE.*?TRIGGER `([^`]+)` (BEFORE|AFTER) (INSERT|UPDATE|DELETE) ON `([^`]+)` FOR EACH ROW\s*(.*?)(?:END|DELIMITER)', re.DOTALL | re.IGNORECASE)

issues = []
for match in trigger_pattern.finditer(content):
    trg_name = match.group(1)
    action = match.group(3).upper()
    tbl_name = match.group(4)
    body = match.group(5)
    
    if tbl_name not in table_defs:
        continue
    
    cols = table_defs[tbl_name]
    
    # Find NEW.`xxx` or NEW.xxx
    new_refs = set(re.findall(r'NEW\.`?([a-zA-Z0-9_]+)`?', body, re.IGNORECASE))
    old_refs = set(re.findall(r'OLD\.`?([a-zA-Z0-9_]+)`?', body, re.IGNORECASE))
    
    bad_new = [c for c in new_refs if c not in cols]
    bad_old = [c for c in old_refs if c not in cols]
    
    if bad_new or bad_old:
        issues.append({
            'trigger': trg_name,
            'table': tbl_name,
            'action': action,
            'bad_new': bad_new,
            'bad_old': bad_old
        })

print(f"Total issues found: {len(issues)}")
for iss in issues:
    print(iss)