"""Synthetic SQLite control demonstration, not an AI/product security test.

No network, credentials, existing files or command-line SQL. In-memory only.
The fixed statements demonstrate separate controls; this is NOT a sandbox
for arbitrary untrusted SQL. Do not expose this connection to end users.
"""
import json
import sqlite3


def main():
    db = sqlite3.connect(':memory:')
    passed = []
    try:
        db.executescript('''
            CREATE TABLE report_orders(id INTEGER PRIMARY KEY, cents INTEGER);
            INSERT INTO report_orders VALUES (1,12500),(2,2500),(3,3000);
            CREATE TABLE private_notes(note TEXT);
            INSERT INTO private_notes VALUES ('synthetic restricted note');
        ''')
        db.execute('PRAGMA query_only=ON')
        assert db.execute('SELECT SUM(cents) FROM report_orders').fetchone()[0] == 18000
        passed.append('approved aggregate returns 18000 cents')

        # A separate read policy: query_only does not limit readable tables.
        def authorizer(action, arg1, arg2, database_name, trigger_name):
            if action == sqlite3.SQLITE_READ and arg1 == 'private_notes':
                return sqlite3.SQLITE_DENY
            return sqlite3.SQLITE_OK

        db.set_authorizer(authorizer)
        for label, sql in [
            ('restricted read denied', 'SELECT note FROM private_notes'),
            ('update denied', 'UPDATE report_orders SET cents=0'),
            ('insert denied', 'INSERT INTO report_orders VALUES (4,1)'),
            ('delete denied', 'DELETE FROM report_orders'),
            ('table creation denied', 'CREATE TABLE extra(id INTEGER)'),
        ]:
            try:
                db.execute(sql).fetchall()
            except sqlite3.DatabaseError as error:
                expected = 'prohibited' if label == 'restricted read denied' else 'readonly'
                assert expected in str(error).lower(), (label, str(error))
                passed.append(label)
            else:
                raise AssertionError(label)

        assert db.execute('SELECT SUM(cents) FROM report_orders').fetchone()[0] == 18000
        passed.append('denied writes leave approved total unchanged')
        assert db.execute('SELECT COUNT(*), SUM(cents) FROM report_orders LIMIT 1').fetchone() == (3, 18000)
        passed.append('LIMIT 1 returns one aggregate row describing all three inputs')

        # Deterministic VM-work budget, not a wall-clock or billing limit.
        callbacks = 0
        def budget():
            nonlocal callbacks
            callbacks += 1
            return 1 if callbacks >= 10 else 0

        db.set_progress_handler(budget, 100)
        try:
            db.execute('''WITH RECURSIVE n(x) AS
                (VALUES(1) UNION ALL SELECT x+1 FROM n WHERE x<1000000)
                SELECT SUM(x) FROM n''').fetchall()
        except sqlite3.OperationalError as error:
            assert 'interrupted' in str(error).lower() and callbacks == 10
            passed.append('recursive statement interrupted by work budget')
        else:
            raise AssertionError('work budget did not interrupt')
        finally:
            db.set_progress_handler(None, 0)

        assert db.execute('SELECT COUNT(*) FROM report_orders').fetchone()[0] == 3
        passed.append('connection can run an approved query after interruption')
        print(json.dumps({'sqlite_version': sqlite3.sqlite_version,
                          'checks_passed': len(passed), 'checks': passed}, indent=2))
    finally:
        db.close()


if __name__ == '__main__':
    main()
