"""Original InternshipDeadlines educational example. Python 3.10+; standard library only.
Run: python revenue_audit.py
Synthetic records; no network or disk database access. Amounts are integer cents.
"""
import json
import sqlite3
import unittest

def database():
    db = sqlite3.connect(':memory:')
    db.execute('PRAGMA foreign_keys = ON')
    db.executescript('''
      CREATE TABLE orders (id TEXT PRIMARY KEY, status TEXT NOT NULL
        CHECK(status IN ('complete','cancelled')), cents INTEGER NOT NULL
        CHECK(typeof(cents) = 'integer' AND cents >= 0));
      CREATE TABLE lines (id TEXT PRIMARY KEY, order_id TEXT NOT NULL
        REFERENCES orders(id));
      CREATE TABLE refunds (id TEXT PRIMARY KEY, order_id TEXT NOT NULL
        REFERENCES orders(id), cents INTEGER NOT NULL
        CHECK(typeof(cents) = 'integer' AND cents >= 0));
    ''')
    db.executemany('INSERT INTO orders VALUES (?,?,?)', [
      ('A','complete',10000), ('B','complete',8000),
      ('C','cancelled',12000), ('D','complete',8000)])
    db.executemany('INSERT INTO lines VALUES (?,?)', [
      ('A1','A'), ('A2','A'), ('B1','B'), ('C1','C'), ('C2','C'), ('D1','D')])
    db.executemany('INSERT INTO refunds VALUES (?,?,?)', [('R1','A',500), ('R2','A',1500)])
    return db

def report(db):
    # Aggregate the many-side BEFORE joining. Never deduplicate by dollar amount.
    row = db.execute('''
      WITH refund_totals AS (
        SELECT order_id, SUM(cents) AS refunded FROM refunds GROUP BY order_id
      )
      SELECT COUNT(*), COALESCE(SUM(o.cents),0),
        COALESCE(SUM(COALESCE(r.refunded,0)),0),
        COALESCE(SUM(o.cents-COALESCE(r.refunded,0)),0)
      FROM orders o LEFT JOIN refund_totals r ON r.order_id=o.id
      WHERE o.status='complete'
    ''').fetchone()
    return dict(zip(('completed_orders','gross_cents','refund_cents','net_cents'), row))

def wrong_totals(db):
    joined = db.execute("SELECT SUM(o.cents) FROM orders o JOIN lines l ON l.order_id=o.id WHERE o.status='complete'").fetchone()[0]
    distinct = db.execute("SELECT SUM(DISTINCT cents) FROM orders WHERE status='complete'").fetchone()[0]
    return {'wrong_joined_gross_cents': joined, 'wrong_distinct_gross_cents': distinct}

class RevenueTests(unittest.TestCase):
    def setUp(self): self.db = database()
    def tearDown(self): self.db.close()
    def test_expected_result(self):
        self.assertEqual(report(self.db), {'completed_orders':3,'gross_cents':26000,'refund_cents':2000,'net_cents':24000})
    def test_wrong_methods_are_detected(self):
        self.assertEqual(wrong_totals(self.db), {'wrong_joined_gross_cents':36000,'wrong_distinct_gross_cents':18000})
    def test_duplicate_order_key_is_rejected(self):
        with self.assertRaises(sqlite3.IntegrityError):
            self.db.execute("INSERT INTO orders VALUES ('A','complete',10000)")
    def test_cancelled_order_refund_does_not_change_cohort(self):
        self.db.execute("INSERT INTO refunds VALUES ('RC','C',12000)")
        self.assertEqual(report(self.db)['net_cents'],24000)
    def test_additional_refund_is_not_multiplied_by_lines(self):
        self.db.execute("INSERT INTO refunds VALUES ('R3','A',1000)")
        self.assertEqual(report(self.db)['net_cents'],23000)
    def test_equal_value_orders_are_legitimate(self):
        self.db.execute("INSERT INTO orders VALUES ('E','complete',8000)")
        self.assertEqual(report(self.db)['gross_cents'],34000)
    def test_empty_completed_cohort(self):
        self.db.execute("UPDATE orders SET status='cancelled'")
        self.assertEqual(report(self.db)['net_cents'],0)
    def test_unknown_order_and_negative_refund_rejected(self):
        with self.assertRaises(sqlite3.IntegrityError):
            self.db.execute("INSERT INTO refunds VALUES ('RX','missing',100)")
        with self.assertRaises(sqlite3.IntegrityError):
            self.db.execute("INSERT INTO refunds VALUES ('RN','A',-1)")

if __name__ == '__main__':
    db = database()
    print(json.dumps({'correct':report(db), 'wrong':wrong_totals(db)}, indent=2))
    db.close()
    result = unittest.TextTestRunner(verbosity=2).run(unittest.defaultTestLoader.loadTestsFromTestCase(RevenueTests))
    raise SystemExit(0 if result.wasSuccessful() else 1)