"""Synthetic CSV -> validated SQLite records -> reconciled business report.
Run with Python 3. Standard library only; uses a disposable in-memory database.
"""
import csv
import io
import sqlite3

SAMPLE = '''order_id,customer,amount_paise
O1,Asha,10000
O2,Ravi,20000
O2,Ravi,20000
O3,Asha,not-a-number
O1,Asha,99999
'''

def run(text=SAMPLE):
    db=sqlite3.connect(':memory:')
    db.execute('CREATE TABLE orders (id TEXT PRIMARY KEY, customer TEXT NOT NULL, amount INTEGER NOT NULL CHECK(amount>0))')
    seen={}; rejected=[]; duplicates=0
    rows=csv.DictReader(io.StringIO(text))
    if rows.fieldnames != ['order_id','customer','amount_paise']:
        db.close(); raise ValueError('expected order_id,customer,amount_paise header')
    try:
        for number,row in enumerate(rows,start=2):
            if None in row or any(v is None for v in row.values()):
                rejected.append((number,'malformed row'));continue
            order_id=row['order_id'].strip();customer=row['customer'].strip()
            try: amount=int(row['amount_paise'])
            except (ValueError,TypeError):
                rejected.append((number,'invalid amount'));continue
            if not order_id or not customer or amount<=0:
                rejected.append((number,'missing identity or nonpositive amount'));continue
            payload=(customer,amount)
            if order_id in seen:
                if seen[order_id]==payload:duplicates+=1
                else:rejected.append((number,'conflicting identifier'))
                continue
            with db:
                db.execute('INSERT INTO orders VALUES (?,?,?)',(order_id,customer,amount))
            seen[order_id]=payload
        count,total=db.execute('SELECT COUNT(*),COALESCE(SUM(amount),0) FROM orders').fetchone()
        before=(count,total)
        try:
            with db:
                db.execute('INSERT INTO orders VALUES (?,?,?)',('O4','Mina',4000))
                db.execute('INSERT INTO orders VALUES (?,?,?)',('O5','Mina',-1))
        except sqlite3.IntegrityError:pass
        after=db.execute('SELECT COUNT(*),COALESCE(SUM(amount),0) FROM orders').fetchone()
        assert before==after, 'failed batch must roll back the valid first insert too'
        db.execute('CREATE TABLE deliveries(order_id TEXT, attempt INTEGER)')
        db.executemany('INSERT INTO deliveries VALUES (?,?)',[('O1',1),('O1',2),('O2',1)])
        joined_total=db.execute('SELECT SUM(o.amount) FROM orders o JOIN deliveries d ON o.id=d.order_id').fetchone()[0]
        return {'accepted':count,'duplicates':duplicates,'rejected':rejected,
                'total_paise':total,'naive_join_total_paise':joined_total,'rollback_preserved_total':before==after}
    finally:db.close()

if __name__=='__main__':
    print(run())