#!/usr/bin/env python3
from pathlib import Path
import re, sys
sql = Path(__file__).resolve().parents[1] / 'database/migrations/001_core.sql'
text = sql.read_text(encoding='utf-8')
tables = {}
for m in re.finditer(r'CREATE TABLE IF NOT EXISTS (\w+) \((.*?)\) ENGINE=', text, re.S):
    t, body = m.group(1), m.group(2)
    cols = {}
    for line in body.splitlines():
        line = line.strip().rstrip(',')
        cm = re.match(r'(\w+)\s+([A-Z]+(?:\s+UNSIGNED)?)', line)
        if cm and cm.group(1).lower() not in {'primary','unique','key','constraint'}:
            cols[cm.group(1)] = cm.group(2)
    tables[t] = cols
errors = []
for m in re.finditer(r'CONSTRAINT\s+\w+\s+FOREIGN KEY \((\w+)\) REFERENCES (\w+)\((\w+)\)', text):
    col, rt, rc = m.group(1), m.group(2), m.group(3)
    # find table containing constraint by scanning backwards
    prefix = text[:m.start()]
    ct = list(re.finditer(r'CREATE TABLE IF NOT EXISTS (\w+) \(', prefix))[-1].group(1)
    lt = tables.get(ct, {}).get(col)
    rtpe = tables.get(rt, {}).get(rc)
    if lt != rtpe:
        errors.append(f'{ct}.{col} {lt} -> {rt}.{rc} {rtpe}')
if errors:
    print('\n'.join(errors)); sys.exit(1)
print(f'OK: {len(tables)} tables, FK types compatible')