"""Synthetic, offline query/result contract. Python 3 standard library only.
No LLM, network, credentials, arbitrary SQL, or production database.
The caller supplies a trusted principal; this is not an authentication system.
"""
import json
import sqlite3
from decimal import Decimal


def fixture():
    db = sqlite3.connect(':memory:')
    db.row_factory = sqlite3.Row
    db.executescript('''
      CREATE TABLE invoices (
        id TEXT PRIMARY KEY, tenant TEXT, account TEXT,
        due_date TEXT, status TEXT, amount_cents INTEGER
      );
      INSERT INTO invoices VALUES
        ('I1','north','A','2026-10-01','open',10000),
        ('I2','north','A','2026-10-02','open',2500),
        ('I3','north','A','2026-10-03','open',5000),
        ('I4','north','A','2026-10-04','paid',90000),
        ('I5','south','A','2026-10-01','open',80000),
        ('I6','north','B','2026-10-01','open',70000);
    ''')
    db.execute('PRAGMA query_only = ON')
    return db


def failure(call_id, status):
    return {'call_id': call_id, 'status': status, 'rows': []}


def lookup(db, principal, account, call_id, limit=2):
    # Principal is supplied by a trusted server session, never model arguments.
    if account not in principal['accounts']:
        return failure(call_id, 'denied')
    if type(limit) is not int or not 1 <= limit <= 100:
        return failure(call_id, 'invalid_request')
    params = (principal['tenant'], account)
    # One snapshot for aggregate and preview; a LIMIT does not cap query cost.
    try:
        db.execute('BEGIN')
        total = db.execute('''
            SELECT COUNT(*) AS n, COALESCE(SUM(amount_cents), 0) AS cents
            FROM invoices WHERE tenant = ? AND account = ? AND status = 'open'
        ''', params).fetchone()
        rows = [dict(row) for row in db.execute('''
            SELECT id, due_date, amount_cents FROM invoices
            WHERE tenant = ? AND account = ? AND status = 'open'
            ORDER BY due_date, id LIMIT ?
        ''', params + (limit,))]
        db.commit()
    except sqlite3.Error:
        db.rollback()
        return failure(call_id, 'database_error')
    return {
        'call_id': call_id, 'status': 'empty' if total['n'] == 0 else 'ok',
        'scope': {'account': account, 'status': 'open', 'currency': 'USD'},
        'matched_rows': total['n'], 'returned_rows': len(rows),
        'total_cents': total['cents'], 'complete': len(rows) == total['n'],
        'rows': rows,
    }


def render(result, expected_call_id):
    if result['call_id'] != expected_call_id:
        raise ValueError('Result belongs to a different tool call')
    if result['status'] == 'empty':
        return 'No open invoices matched this authorized account.'
    if result['status'] != 'ok':
        return 'No verified answer is available (' + result['status'] + ').'
    amount = Decimal(result['total_cents']) / Decimal(100)
    return (f"{result['matched_rows']} open invoices total USD {amount:.2f}. "
            f"Showing {result['returned_rows']} rows; complete={result['complete']}.")


def checks():
    db = fixture()
    user = {'tenant': 'north', 'accounts': ['A', 'EMPTY', "A' OR 1=1 --"]}
    result = lookup(db, user, 'A', 'call-1')
    assert result['matched_rows'] == 3 and result['total_cents'] == 17500
    assert [r['id'] for r in result['rows']] == ['I1', 'I2']
    assert result['returned_rows'] == 2 and result['complete'] is False
    assert sum(r['amount_cents'] for r in result['rows']) == 12500
    assert render(result, 'call-1') == '3 open invoices total USD 175.00. Showing 2 rows; complete=False.'
    assert lookup(db, user, 'A', 'call-2', 10)['complete'] is True
    empty = lookup(db, user, 'EMPTY', 'call-3')
    assert empty['status'] == 'empty' and empty['total_cents'] == 0
    assert 'No open invoices' in render(empty, 'call-3')
    assert lookup(db, user, 'B', 'call-4')['status'] == 'denied'
    assert lookup(db, user, "A' OR 1=1 --", 'call-5')['status'] == 'empty'
    assert lookup(db, user, 'A', 'call-6', 0)['status'] == 'invalid_request'
    try:
        render(result, 'stale-call')
        raise AssertionError('Mismatched result accepted')
    except ValueError:
        pass
    # Injected failure state: verifies presentation, not real timeout cancellation.
    assert 'No verified answer' in render(failure('call-7', 'timeout'), 'call-7')
    broken = sqlite3.connect(':memory:')
    assert lookup(broken, user, 'A', 'call-8')['status'] == 'database_error'
    broken.close()
    try:
        db.execute("UPDATE invoices SET amount_cents = 0")
        raise AssertionError('Query-only connection allowed a write')
    except sqlite3.OperationalError:
        pass
    db.close()
    print(json.dumps(result, indent=2))
    print(render(result, 'call-1'))
    print('PASS: 15 contract checks (synthetic local SQLite; no model evaluation).')


if __name__ == '__main__':
    checks()