#!/usr/bin/env python3
"""Static verification for the PHP backend (use when PHP/MySQL are not available):
   1. schema sanity (FK targets, index columns, seed inserts)
   2. every SQL string in PHP: tables exist, alias.column exist, INSERT/UPDATE columns exist
   3. number of ? placeholders == number of bound values for literal q()/one()/all() calls
   4. every error key used by fail()/throw has a translation
   5. every route called from JS exists on the server
Run:  python3 tests/verify_static.py"""
import re,glob,json,os,sys
ROOT=os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
rd=lambda p:open(os.path.join(ROOT,p),encoding='utf-8').read()
errors=[];warns=[]
# ---------- 1. schema ----------
sql=re.sub(r'--[^\n]*','',rd('database.sql'))
stmts=[s.strip() for s in sql.split(';') if s.strip()]
def split_top(s):
    out=[];d=0;cur='';q=None
    for ch in s:
        if q:
            cur+=ch
            if ch==q: q=None
            continue
        if ch in "'\"": q=ch;cur+=ch;continue
        if ch=='(':d+=1
        if ch==')':d-=1
        if ch==',' and d==0: out.append(cur);cur=''
        else: cur+=ch
    out.append(cur);return out
schema={};fks=[];idx=[]
for s in stmts:
    m=re.match(r'CREATE TABLE (\w+)\s*\((.*)\)\s*ENGINE',s,re.S)
    if not m: continue
    t,body=m.group(1),m.group(2);cols=set()
    for part in split_top(body):
        part=part.strip(); w=part.split('(')[0].split()[0].upper() if part else ''
        if w in('PRIMARY','FOREIGN','UNIQUE','INDEX','KEY','FULLTEXT','CONSTRAINT'):
            for g in re.findall(r'FOREIGN KEY\s*\((\w+)\)\s*REFERENCES\s+(\w+)\s*\((\w+)\)',part,re.I): fks.append((t,)+g)
            for g in re.findall(r'(?:UNIQUE|INDEX|KEY|FULLTEXT|PRIMARY KEY)\s*\(([^)]*)\)',part,re.I): idx.append((t,[c.strip() for c in g.split(',')]))
        else: cols.add(part.split()[0])
    # inline UNIQUE etc already handled; inline column-level FK none
    schema[t]=cols
for t,c,rt,rc in fks:
    if rt not in schema: errors.append(f'FK {t}.{c} -> missing table {rt}')
    elif rc not in schema[rt]: errors.append(f'FK {t}.{c} -> missing column {rt}.{rc}')
    if c not in schema[t]: errors.append(f'FK column {t}.{c} missing')
for t,cs in idx:
    for c in cs:
        if c not in schema[t]: errors.append(f'index on {t}: unknown column {c}')
for s in stmts:
    m=re.match(r'INSERT (?:IGNORE )?INTO (\w+)\s*\(([^)]*)\)',s)
    if m:
        if m.group(1) not in schema: errors.append('seed into unknown table '+m.group(1))
        else:
            for c in [x.strip() for x in m.group(2).split(',')]:
                if c not in schema[m.group(1)]: errors.append(f'seed {m.group(1)}: unknown column {c}')
# ---------- PHP lexing helpers ----------
def read_str(src,i):
    q=src[i];j=i+1;buf=''
    while j<len(src) and src[j]!=q:
        if src[j]=='\\' : buf+=src[j:j+2];j+=2;continue
        buf+=src[j];j+=1
    return buf,j+1
def top_level_count(arg):
    d=0;n=1;q=None;i=0;seen=False
    while i<len(arg):
        ch=arg[i]
        if q:
            if ch=='\\': i+=2;continue
            if ch==q:q=None
            i+=1;continue
        if ch in "'\"": q=ch;seen=True
        elif ch in '([{': d+=1;seen=True
        elif ch in ')]}': d-=1
        elif ch==',' and d==0: n+=1
        elif not ch.isspace(): seen=True
        i+=1
    return n if seen else 0
KW={'WHERE','ON','LEFT','RIGHT','INNER','JOIN','SET','GROUP','ORDER','LIMIT','USING','AND','OR','VALUES','SELECT','FROM','AS','UNION','OFFSET','HAVING','DUPLICATE','FOR','STRAIGHT_JOIN'}
SQLSTART=re.compile(r'^\s*(SELECT|INSERT|UPDATE|DELETE|SHOW)\b',re.I)
files=sorted(glob.glob(os.path.join(ROOT,'app/**/*.php'),recursive=True)+glob.glob(os.path.join(ROOT,'*.php'))+glob.glob(os.path.join(ROOT,'public/api/*.php')))
nsql=0;ncalls=0
for f in files:
    src=open(f,encoding='utf-8').read();rel=os.path.relpath(f,ROOT)
    def line(pos): return src.count('\n',0,pos)+1
    # find all string literals that look like SQL
    i=0;n=len(src)
    while i<n:
        c=src[i]
        if src.startswith('//',i) or c=='#':
            while i<n and src[i]!='\n':i+=1
        elif src.startswith('/*',i): i=src.index('*/',i)+2
        elif c in '\'"':
            s,j=read_str(src,i)
            if SQLSTART.match(s) and len(s)>12:
                nsql+=1;ln=line(i);dyn=bool(re.search(r'\{\$|\$\w',s)) if c=='"' else False
                t=re.sub(r"\{\$[^}]*\}|\$\w+(?:\[[^\]]*\])?",'§',s) if c=='"' else s
                t=re.sub(r'ON DUPLICATE KEY UPDATE.*','',t,flags=re.I|re.S)
                t=t.replace("\\'","'").replace('\\"','"').replace('\\$','$')
                # tables
                al={}
                for kw,tab in re.findall(r'\b(FROM|JOIN|INTO|UPDATE)\s+`?(\w+)`?',t,re.I):
                    if tab=='§' or tab.upper() in KW: continue
                    if tab not in schema: errors.append(f'{rel}:{ln}: unknown table {tab}')
                for kw,tab,a in re.findall(r'\b(FROM|JOIN|UPDATE)\s+`?(\w+)`?(?:\s+(?:AS\s+)?(\w+))?',t,re.I):
                    if tab in schema:
                        al.setdefault(tab,set()).add(tab)
                        if a and a.upper() not in KW: al.setdefault(a,set()).add(tab)
                for a,col in re.findall(r'\b([a-z_]\w*)\.([a-z_]\w*)\b',re.sub(r"'[^']*'",'',t),re.I):
                    if a in al:
                        if not any(col in schema[tb] for tb in al[a]): errors.append(f'{rel}:{ln}: {a}.{col} not a column of {sorted(al[a])}')
                m=re.match(r'\s*INSERT\s+(?:IGNORE\s+)?INTO\s+(\w+)\s*\(([^)]*)\)',t,re.I)
                if m and m.group(1) in schema:
                    for cc in [x.strip() for x in m.group(2).split(',') if x.strip()]:
                        if cc not in schema[m.group(1)]: errors.append(f'{rel}:{ln}: INSERT {m.group(1)}: unknown column {cc}')
                m=re.match(r'\s*UPDATE\s+(\w+)\s+SET\s+(.*?)(?:\s+WHERE\b|$)',t,re.I|re.S)
                if m and m.group(1) in schema:
                    for part in split_top(m.group(2)):
                        mm=re.match(r'\s*`?(\w+)`?\s*=',part)
                        if mm and mm.group(1)!='§' and mm.group(1) not in schema[m.group(1)]: errors.append(f'{rel}:{ln}: UPDATE {m.group(1)}: unknown column {mm.group(1)}')
            i=j
        else: i+=1
    # ---------- 3. placeholder counts ----------
    for m in re.finditer(r'\b(q|one|all)\(',src):
        j=m.end()
        if j<n and src[j] in '\'"':
            s,k=read_str(src,j)
            if not SQLSTART.match(s): continue
            if src[j]=='"' and re.search(r'\{\$|\$\w',s): continue
            while src[k].isspace():k+=1
            ph=len(re.findall(r'\?',re.sub(r"'[^']*'",'',s)))
            if src[k]==')': got=0
            elif src[k]==',':
                k+=1
                while src[k].isspace():k+=1
                if src[k]!='[': continue
                d=0;e=k;qq=None
                while e<n:
                    ch=src[e]
                    if qq:
                        if ch=='\\': e+=2;continue
                        if ch==qq: qq=None
                    elif ch in "'\"": qq=ch
                    elif ch in '[(': d+=1
                    elif ch in '])':
                        d-=1
                        if d==0: break
                    e+=1
                inner=src[k+1:e]
                if '...' in inner: continue
                got=top_level_count(inner)
            else: continue
            ncalls+=1
            if ph!=got: errors.append(f'{rel}:{line(m.start())}: {ph} placeholders but {got} bound values: {s[:70]}')
# ---------- 4. error keys translated ----------
fa=json.load(open(os.path.join(ROOT,'public/assets/i18n/fa.json')));en=json.load(open(os.path.join(ROOT,'public/assets/i18n/en.json')))
php=''.join(open(f,encoding='utf-8').read() for f in files)
keys=set(re.findall(r"fail\('([\w]+)'",php))|set(re.findall(r"RuntimeException\('([\w]+)'\)",php))
for k in sorted(keys):
    if k not in fa or k not in en: errors.append(f'error key "{k}" has no fa/en translation')
for k in set(fa)^set(en): warns.append(f'translation key only in one language: {k}')
# ---------- 5. JS -> server routes ----------
routes=set(re.findall(r"\$R\['([^']+)'\]=",php))|{'auth/csrf'}
js=''.join(open(f,encoding='utf-8').read() for f in glob.glob(os.path.join(ROOT,'public/assets/js/*.js')))
for r in sorted(set(re.findall(r"\b(?:api|tryApi)\('([a-z_]+/[\w/]+)'",js))|set(re.findall(r"[?&]r=([a-z_]+/[\w/]+)",js))):
    if r not in routes: errors.append(f'JS calls unknown route {r}')
print(f'schema: {len(schema)} tables, {len(fks)} foreign keys | php SQL strings checked: {nsql} | bound-value checks: {ncalls} | routes: {len(routes)}')
for w in warns: print('WARN',w)
for e in errors: print('ERROR',e)
print('RESULT:', 'OK' if not errors else f'{len(errors)} problem(s)')
sys.exit(1 if errors else 0)