"""Bounded synthetic SQLite teaching tool; not a production database gateway."""
from __future__ import annotations
import csv
import math
import sqlite3
from pathlib import Path

SOURCE=Path(__file__).resolve().parent.parent/'commerce-sql'/'orders.csv'
COLUMNS={'order_id','ordered_at','status','order_total_paise'}
FUNCTIONS={'sum','count','min','max','avg','coalesce','round','abs'}


class QueryRejected(ValueError):pass


def query(sql,params=(),*,row_limit=25,vm_budget=20000):
    """Limits are trusted application configuration, not model-selected arguments."""
    if not isinstance(sql,str) or not sql.strip() or len(sql)>10000:
        raise QueryRejected('sql_shape_or_length')
    if not isinstance(params,tuple) or len(params)>20:
        raise QueryRejected('parameter_shape')
    if any(type(v) not in (str,int,float,type(None)) or
           (isinstance(v,str) and len(v)>1000) or
           (type(v) is int and not -(2**63)<=v<2**63) or
           (type(v) is float and not math.isfinite(v)) for v in params):
        raise QueryRejected('parameter_value')
    if type(row_limit) is not int or not 1<=row_limit<=100:
        raise QueryRejected('row_limit_configuration')
    if type(vm_budget) is not int or not 100<=vm_budget<=1000000:
        raise QueryRejected('vm_budget_configuration')
    db=sqlite3.connect(':memory:')
    try:
        db.execute('CREATE TABLE orders(order_id TEXT PRIMARY KEY,ordered_at TEXT,status TEXT,order_total_paise INTEGER)')
        with SOURCE.open(encoding='utf-8',newline='') as handle:
            rows=list(csv.DictReader(handle))
        db.executemany('INSERT INTO orders VALUES(?,?,?,?)',[
            (r['order_id'],r['ordered_at'],r['status'],int(r['order_total_paise'])) for r in rows])
        db.commit()
        db.execute('PRAGMA query_only=ON')
        db.setlimit(sqlite3.SQLITE_LIMIT_LENGTH,100000)
        db.setlimit(sqlite3.SQLITE_LIMIT_SQL_LENGTH,10000)
        db.setlimit(sqlite3.SQLITE_LIMIT_COLUMN,30)
        db.setlimit(sqlite3.SQLITE_LIMIT_EXPR_DEPTH,30)
        db.setlimit(sqlite3.SQLITE_LIMIT_COMPOUND_SELECT,5)
        db.setlimit(sqlite3.SQLITE_LIMIT_VARIABLE_NUMBER,20)

        def authorize(action,arg1,arg2,database,trigger):
            if action==sqlite3.SQLITE_SELECT:return sqlite3.SQLITE_OK
            # SQLite 3.42 reports no database name for COUNT(*)'s empty-column read.
            # This fresh connection has one trusted table and cannot attach/create objects.
            if action==sqlite3.SQLITE_READ and arg1=='orders' and (
                (database=='main' and arg2 in COLUMNS) or
                (database in ('main',None) and arg2=='')):
                return sqlite3.SQLITE_OK
            if action==sqlite3.SQLITE_FUNCTION and str(arg2).lower() in FUNCTIONS:
                return sqlite3.SQLITE_OK
            return sqlite3.SQLITE_DENY

        steps=0
        def progress():
            nonlocal steps
            steps+=100
            return int(steps>=vm_budget)

        db.set_authorizer(authorize)
        db.set_progress_handler(progress,100)
        try:
            cursor=db.execute(sql,params)
            result=cursor.fetchmany(row_limit+1)
            if len(result)>row_limit:raise QueryRejected('result_row_limit')
            if any(isinstance(v,(str,bytes)) and len(v)>1000 for row in result for v in row):
                raise QueryRejected('result_cell_limit')
            if sum(len(str(v)) for row in result for v in row)>20000:
                raise QueryRejected('result_size_limit')
            return {'columns':[d[0] for d in cursor.description],
                    'rows':[list(row) for row in result],
                    'limits':'Synthetic read-only data; query execution is not metric validation.'}
        except sqlite3.Error as error:
            reason='work_budget_exceeded' if steps>=vm_budget else 'query_rejected'
            raise QueryRejected(reason) from error
    finally:
        db.close()