"""Run an optional SQLite reference over this synthetic project. Python 3.10+.
Run: python run_sql.py [analysis.sql]
Only local files and an in-memory database are used. Read SQL before executing it.
The import validates basic numeric/date shapes; analysis.sql contains population,
identity, conflict and calculation logic. This is a teaching tool, not a general ETL.
"""
from pathlib import Path
from datetime import datetime, timezone
import csv, json, math, sqlite3, sys
PROFILE = {'subscriptions': [('month', 'month'), ('account_id', 'text'), ('subscription_id', 'text'), ('recurring_amount', 'number'), ('billing_interval', 'text'), ('currency', 'text'), ('status', 'text')], 'accounts': [('account_id', 'text'), ('had_positive_usd_before_january', 'flag')]}

def load(directory, connection):
    rules=json.loads((directory/'rules.json').read_text())
    if rules.get('synthetic') is not True:raise ValueError('This runner is for the synthetic case only')
    connection.execute('CREATE TABLE case_rules(key TEXT PRIMARY KEY, value TEXT NOT NULL)')
    connection.executemany('INSERT INTO case_rules VALUES (?,?)',[(k,json.dumps(v) if isinstance(v,(list,dict,bool)) else str(v)) for k,v in rules.items()])
    for table, fields in PROFILE.items():
        connection.execute('CREATE TABLE "'+table+'" ('+', '.join('"'+name+'" '+('REAL' if kind=='number' else 'TEXT') for name,kind in fields)+')')
        with (directory/(table+'.csv')).open(newline='',encoding='utf-8') as f:
            reader=csv.DictReader(f)
            if reader.fieldnames!=[name for name,_ in fields]:raise ValueError('Unexpected headers: '+table)
            prepared=[]
            for row in reader:
                if None in row or any(v is None for v in row.values()):raise ValueError('Inconsistent CSV row')
                values=[]
                for name,kind in fields:
                    value=row[name]
                    if value=='':values.append(None);continue
                    if kind=='number':
                        value=float(value)
                        if not math.isfinite(value) or value<0:raise ValueError('Invalid nonnegative number: '+name)
                    elif kind=='timestamp':
                        parsed=datetime.fromisoformat(value.replace('Z','+00:00'))
                        if parsed.tzinfo is None:raise ValueError('Explicit time zone required')
                        value=parsed.astimezone(timezone.utc).isoformat()
                    elif kind=='date':
                        if datetime.strptime(value,'%Y-%m-%d').strftime('%Y-%m-%d')!=value:raise ValueError('Use YYYY-MM-DD')
                    elif kind=='month':
                        if datetime.strptime(value,'%Y-%m').strftime('%Y-%m')!=value:raise ValueError('Use YYYY-MM')
                    elif kind=='flag' and value not in ('true','false'):raise ValueError('Use true/false')
                    values.append(value)
                prepared.append(values)
        connection.executemany('INSERT INTO "'+table+'" VALUES ('+','.join('?' for _ in fields)+')',prepared)

def main():
    directory=Path(__file__).resolve().parent
    target=Path(sys.argv[1]) if len(sys.argv)>1 else directory/'analysis.sql'
    try:
        with sqlite3.connect(':memory:') as con:
            con.row_factory=sqlite3.Row
            load(directory,con)
            con.executescript(target.read_text(encoding='utf-8'))
            errors=[dict(row) for row in con.execute('SELECT * FROM exceptions')]
            if errors:
                print(json.dumps({'input_exceptions':errors},indent=2),file=sys.stderr);return 2
            row=con.execute('SELECT * FROM report').fetchone()
            if row is None:raise ValueError('The report produced no row')
            print(json.dumps(dict(row),indent=2,allow_nan=False))
    except (OSError,ValueError,KeyError,TypeError,sqlite3.Error) as exc:
        print(f'Cannot run the local SQL analysis: {exc}',file=sys.stderr);return 2
    return 0
if __name__=='__main__':raise SystemExit(main())