#!/usr/bin/env python3
"""
Static schema checks (no MySQL needed):
  * statement splitting identical to bin/migrate.php (quotes + comments aware)
  * every table has a primary key and uses InnoDB; no FLOAT/DOUBLE columns; FK targets exist (and are created earlier)
  * every column named in an INSERT/UPDATE inside the PHP code exists in that table
  * seed.sql / hardening.sql only touch existing tables and columns
Run: python3 tests/tools/schema_check.py
"""
import os, re, sys

root = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..'))


def split_sql(sql):
    out, buf, quote, i, n = [], [], None, 0, len(sql)
    while i < n:
        c, d = sql[i], sql[i + 1] if i + 1 < n else ''
        if quote:
            buf.append(c)
            if c == '\\' and quote != '`': buf.append(d); i += 1
            elif c == quote:
                if d == quote: buf.append(d); i += 1
                else: quote = None
        elif c == '-' and d == '-':
            while i < n and sql[i] != '\n': i += 1
            buf.append('\n'); continue
        elif c == '/' and d == '*':
            j = sql.find('*/', i + 2); i = n if j < 0 else j + 1
        elif c in '\'"`': quote = c; buf.append(c)
        elif c == ';':
            s = ''.join(buf).strip()
            if s: out.append(s)
            buf = []
        else: buf.append(c)
        i += 1
    s = ''.join(buf).strip()
    if s: out.append(s)
    return out


errors = []
schema = open(os.path.join(root, 'database/schema.sql')).read()
stmts = split_sql(schema)
tables = {}   # name -> set(columns)
order = []
for st in stmts:
    m = re.match(r'CREATE TABLE (\w+) \((.*)\)\s*ENGINE=(\w+)', st, re.S)
    if not m:
        if not re.match(r'(SET)\b', st): errors.append(f'unrecognised statement: {st[:60]}')
        continue
    name, body, engine = m.groups()
    if engine != 'InnoDB': errors.append(f'{name}: engine {engine}')
    cols = set()
    for line in re.split(r',\n', body):
        line = line.strip()
        cm = re.match(r'^(\w+)\s+(?:BIGINT|INT|SMALLINT|TINYINT|VARCHAR|CHAR|DECIMAL|DATETIME|TEXT|JSON|ENUM)', line)
        if cm and cm.group(1) not in ('PRIMARY', 'UNIQUE', 'KEY', 'CONSTRAINT'): cols.add(cm.group(1))
    if re.search(r'\b(FLOAT|DOUBLE|REAL)\b', body, re.I): errors.append(f'{name}: floating-point column')
    if 'PRIMARY KEY' not in body: errors.append(f'{name}: no primary key')
    for fk in re.finditer(r'FOREIGN KEY \((\w+)\) REFERENCES (\w+) \((\w+)\)', body):
        col, tgt, tcol = fk.groups()
        if col not in cols: errors.append(f'{name}: FK column {col} undefined')
        if tgt not in tables: errors.append(f'{name}: FK target {tgt} not created before this table')
        elif tcol not in tables[tgt]: errors.append(f'{name}: FK target column {tgt}.{tcol} undefined')
    for idx in re.finditer(r'(?:UNIQUE )?KEY \w+ \(([^)]*)\)', body):
        for c in idx.group(1).split(','):
            c = c.strip()
            if c and c not in cols: errors.append(f'{name}: index column {c} undefined')
    tables[name] = cols
    order.append(name)

# ---- column usage in PHP SQL literals
def php_sql_literals():
    for base, dirs, fs in os.walk(root):
        dirs[:] = [d for d in dirs if d not in ('vendor', '.git', 'tests')]
        for f in fs:
            if not f.endswith('.php'): continue
            src = open(os.path.join(base, f), encoding='utf-8').read()
            for m in re.finditer(r"'((?:[^'\\]|\\.)*)'", src):
                s = m.group(1).replace("\\'", "'")
                if re.match(r'\s*(INSERT|UPDATE|DELETE)\b', s, re.I) or 'INSERT INTO' in s or re.match(r'\s*SELECT\b', s):
                    yield os.path.relpath(os.path.join(base, f), root), s

generated = {'funding_transactions': {'blocking_token_id'}}
for rel, s in php_sql_literals():
    ins = re.search(r'INSERT\s+(?:IGNORE\s+)?INTO\s+(\w+)\s*\(([^)]*)\)', s, re.I)
    if ins:
        t = ins.group(1)
        if t not in tables: errors.append(f'{rel}: INSERT into unknown table {t}'); continue
        for c in [x.strip() for x in ins.group(2).split(',')]:
            if c not in tables[t]: errors.append(f'{rel}: INSERT {t}.{c} - no such column')
    up = re.match(r'\s*UPDATE\s+(\w+)\s+SET\s+(.*?)(?:\s+WHERE\b|$)', s, re.I | re.S)
    if up:
        t = up.group(1)
        if t not in tables: errors.append(f'{rel}: UPDATE unknown table {t}'); continue
        for c in re.findall(r'(?:^|,)\s*(\w+)\s*=', re.sub(r'\([^()]*\)', '()', up.group(2))):
            if c not in tables[t]: errors.append(f'{rel}: UPDATE {t}.{c} - no such column')
    for t in re.findall(r'(?<!KEY )\b(?:FROM|JOIN|INTO|UPDATE)\s+(\w+)', s):
        if re.match(r'[a-z_]+$', t) and t not in tables and t not in ('information_schema', 'dual', 'x', 'table') and not t.isupper():
            errors.append(f'{rel}: SQL mentions unknown table {t}')

# ---- seed + hardening
for fname in ('seed.sql', 'hardening.sql'):
    for st in split_sql(open(os.path.join(root, 'database', fname)).read()):
        ins = re.match(r'INSERT\s+IGNORE\s+INTO\s+(\w+)\s*\(([^)]*)\)', st, re.I)
        if ins:
            t = ins.group(1)
            if t not in tables: errors.append(f'{fname}: unknown table {t}')
            else:
                for c in [x.strip() for x in ins.group(2).split(',')]:
                    if c not in tables[t]: errors.append(f'{fname}: {t}.{c} no such column')
        trg = re.match(r'CREATE TRIGGER \w+ BEFORE (?:UPDATE|DELETE) ON (\w+)', st)
        if trg:
            t = trg.group(1)
            if t not in tables: errors.append(f'{fname}: trigger on unknown table {t}')
            for c in re.findall(r'NEW\.(\w+)', st):
                if c not in tables.get(t, set()) | generated.get(t, set()): errors.append(f'{fname}: trigger {t} touches unknown column {c}')
        elif not ins: errors.append(f'{fname}: unrecognised statement {st[:50]}')

n_seed = len(split_sql(open(os.path.join(root, 'database/seed.sql')).read()))
for e in errors: print('ERROR', e)
print(f'{len(order)} tables, {len(stmts)} baseline statements, {n_seed} seed statements: {len(errors)} problem(s)')
sys.exit(1 if errors else 0)