#!/usr/bin/env python3
"""
Checks that every `Database::exec|insert|one|all|value(<sql literal>, [<params>])` call passes exactly as many parameters as the
SQL has `?` placeholders. Only literal SQL + literal array calls are checked; dynamic ones are reported as "skipped".
Also verifies INSERT column / value counts. Run: python3 tests/tools/sql_params_check.py
"""
import os, re, sys

root = os.path.abspath(os.path.join(os.path.dirname(__file__), '..', '..'))
files = []
for base, dirs, fs in os.walk(root):
    dirs[:] = [d for d in dirs if d not in ('vendor', '.git', 'tests')]
    files += [os.path.join(base, f) for f in fs if f.endswith('.php')]


def read_arg(src, i):
    """Read one argument starting at i; return (text, next_index, ended_with_close_paren)."""
    depth, j, n = 0, i, len(src)
    while j < n:
        c = src[j]
        if c in '\'"':
            q = c; j += 1
            while j < n and src[j] != q:
                j += 2 if src[j] == '\\' else 1
            j += 1; continue
        if c in '([{': depth += 1
        elif c in ')]}':
            if depth == 0: return src[i:j], j, True
            depth -= 1
        elif c == ',' and depth == 0:
            return src[i:j], j + 1, False
        j += 1
    return src[i:], n, True


def split_top(src):
    """Split array body on top-level commas."""
    parts, depth, cur, j, n = [], 0, [], 0, len(src)
    while j < n:
        c = src[j]
        if c in '\'"':
            q = c; cur.append(c); j += 1
            while j < n and src[j] != q:
                if src[j] == '\\': cur.append(src[j]); j += 1
                cur.append(src[j]); j += 1
            cur.append(q); j += 1; continue
        if c in '([{': depth += 1
        elif c in ')]}': depth -= 1
        if c == ',' and depth == 0:
            parts.append(''.join(cur).strip()); cur = []
        else:
            cur.append(c)
        j += 1
    tail = ''.join(cur).strip()
    if tail: parts.append(tail)
    return [p for p in parts if p]


def sql_text(arg):
    """Concatenate the string literals of an expression like 'a' . 'b' . "c"; return None when it has variables other than literals."""
    arg = arg.strip()
    pieces, j, n = [], 0, len(arg)
    while j < n:
        c = arg[j]
        if c in '\'"':
            q = c; j += 1; buf = []
            while j < n and arg[j] != q:
                if arg[j] == '\\' and j + 1 < n: buf.append(arg[j + 1]); j += 2; continue
                buf.append(arg[j]); j += 1
            j += 1
            s = ''.join(buf)
            if q == '"' and re.search(r'\$\w+|\{\$', s):
                s = re.sub(r'\{\$[^}]*\}|\$\w+', 'X', s)   # interpolated identifiers (table/where fragments)
            pieces.append(s)
        elif c in ' \t\r\n.': j += 1
        elif arg.startswith('self::', j):
            return None
        else:
            return None
    return ''.join(pieces)


checked = skipped = bad = 0
for path in files:
    src = open(path, encoding='utf-8').read()
    for m in re.finditer(r'Database::(exec|insert|one|all|value)\(', src):
        sql_arg, nxt, closed = read_arg(src, m.end())
        sql = sql_text(sql_arg)
        line = src[:m.start()].count('\n') + 1
        rel = os.path.relpath(path, root)
        if sql is None:
            skipped += 1; continue
        # remove quoted SQL string literals inside the statement (e.g. 'pending') before counting placeholders
        sql_nolit = re.sub(r"'(?:[^'\\]|\\.)*'", "''", sql)
        qmarks = sql_nolit.count('?')
        params = []
        if not closed:
            p_arg, _, _ = read_arg(src, nxt)
            p_arg = p_arg.strip()
            if p_arg.startswith('[') and p_arg.endswith(']'):
                params = split_top(p_arg[1:-1])
            else:
                skipped += 1; continue
        checked += 1
        if qmarks != len(params):
            bad += 1
            print(f'MISMATCH {rel}:{line}: SQL has {qmarks} placeholder(s), call passes {len(params)} param(s)')
        ins = re.search(r'INSERT\s+(?:IGNORE\s+)?INTO\s+\w+\s*\(([^)]*)\)\s*VALUES\s*\((.*)\)\s*(?:ON DUPLICATE|$)', sql_nolit, re.S | re.I)
        if ins:
            cols = [c for c in ins.group(1).split(',') if c.strip()]
            vals = split_top(ins.group(2))
            if len(cols) != len(vals):
                bad += 1
                print(f'INSERT   {rel}:{line}: {len(cols)} column(s) but {len(vals)} value(s)')
print(f'sql calls checked: {checked}, skipped (dynamic): {skipped}, problems: {bad}')
sys.exit(1 if bad else 0)