"""MIT. Execute SQL only inside the macOS eval sandbox, against read-only SQLite."""
from collections import Counter
import sqlite3
from pathlib import Path


ALLOWED = {sqlite3.SQLITE_SELECT, sqlite3.SQLITE_READ, sqlite3.SQLITE_FUNCTION, sqlite3.SQLITE_RECURSIVE}
BLOCKED_FUNCTIONS = {'load_extension', 'readfile', 'writefile'}


def database(config, changed=False):
    path = Path('changed.sqlite' if changed else 'base.sqlite')
    path.unlink(missing_ok=True)
    conn = sqlite3.connect(path)
    conn.executescript(config['schema_sql'] + config['seed_sql'])
    if changed:
        conn.executescript(config['changed_seed_sql'])
    conn.commit()
    conn.close()
    conn = sqlite3.connect(path.resolve().as_uri() + '?mode=ro', uri=True)
    conn.enable_load_extension(False)
    conn.set_authorizer(lambda action, a, b, db, trigger: sqlite3.SQLITE_OK if action in ALLOWED and not (action == sqlite3.SQLITE_FUNCTION and (b or '').lower() in BLOCKED_FUNCTIONS) else sqlite3.SQLITE_DENY)
    conn.setlimit(sqlite3.SQLITE_LIMIT_LENGTH, 1000000)
    conn.setlimit(sqlite3.SQLITE_LIMIT_SQL_LENGTH, 20000)
    return conn


def execute(conn, sql):
    if not isinstance(sql, str) or not sql.strip() or len(sql) > 20000:
        raise ValueError('missing or oversized SQL')
    steps = [0]
    def progress():
        steps[0] += 1000
        return int(steps[0] > 1000000)
    conn.set_progress_handler(progress, 1000)
    rows = conn.execute(sql).fetchmany(10001)
    if len(rows) > 10000:
        raise ValueError('row limit')
    return [tuple(round(x, 6) if isinstance(x, float) else x for x in row) for row in rows]


def score(item, arguments, config):
    attempts = arguments.get('attempts', [arguments])
    sqls = [a.get('sql') if isinstance(a, dict) else None for a in attempts]
    if not sqls or len(sqls) > 2:
        sqls = [None]
    valid = [True] * len(sqls)
    correct = [True] * len(sqls)
    for changed in (False, True):
        conn = database(config, changed)
        try:
            gold = execute(conn, item['gold_sql'])
            for index, sql in enumerate(sqls):
                try:
                    actual = execute(conn, sql)
                    correct[index] &= actual == gold if item['ordered'] else Counter(actual) == Counter(gold)
                except (sqlite3.Error, ValueError, TypeError):
                    valid[index] = correct[index] = False
        finally:
            conn.close()
    # Headline is first-pass execution accuracy, so repair never hides an error.
    secondary = {
        'invalid_sql_before_retry': {'score': int(not valid[0])},
        'invalid_sql_after_retry': {'score': int(not valid[-1])},
        'accuracy_after_retry': {'score': int(correct[-1])},
    }
    # Count correct questions by tier; denominators are frozen in eval.json.
    for tier in ('easy', 'medium', 'hard'):
        secondary[tier + '_correct'] = {'score': int(item['tier'] == tier and correct[0])}
    return {'score': int(correct[0]), 'secondary': secondary}