"""Generic customer/account/transaction reference fixture. No vendor tool required."""
import json
import sqlite3


def run():
    db = sqlite3.connect(':memory:')
    db.execute('PRAGMA foreign_keys = ON')
    db.executescript('''
    CREATE TABLE customer (id INTEGER PRIMARY KEY);
    CREATE TABLE account (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customer(id));
    CREATE TABLE payment_transaction (id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL REFERENCES account(id), amount_cents INTEGER NOT NULL CHECK(amount_cents > 0));
    INSERT INTO customer VALUES (1), (2);
    INSERT INTO account VALUES (10, 1), (20, 2);
    INSERT INTO payment_transaction VALUES (100, 10, 1250), (101, 20, 2750);
    ''')
    rejected = False
    try:
        db.execute('INSERT INTO payment_transaction VALUES (102, 999, 500)')
    except sqlite3.IntegrityError:
        rejected = True
    count, total = db.execute('SELECT COUNT(*), SUM(amount_cents) FROM payment_transaction').fetchone()
    orphans = db.execute('SELECT COUNT(*) FROM payment_transaction t LEFT JOIN account a ON a.id=t.account_id WHERE a.id IS NULL').fetchone()[0]
    assert rejected and count == 2 and total == 4000 and orphans == 0
    return {'customers': 2, 'accounts': 2, 'valid_transactions': count, 'invalid_account_transaction_rejected': rejected, 'orphans': orphans, 'total_cents': total}


if __name__ == '__main__':
    print(json.dumps(run(), sort_keys=True))
