"""Execute the companion synthetic SQL and test its measurement contract.
Place product-analytics.sql in this file's folder, then run:
    python product-analytics.py
Uses Python's standard-library sqlite3 and unittest. No external service.
"""
from pathlib import Path
import json
import sqlite3
import unittest

SQL_PATH = Path(__file__).with_suffix('.sql')

def database() -> sqlite3.Connection:
    db = sqlite3.connect(':memory:')
    db.row_factory = sqlite3.Row
    db.executescript(SQL_PATH.read_text(encoding='utf-8'))
    return db

def analyze(db: sqlite3.Connection) -> dict:
    conflicts = db.execute('SELECT event_id FROM event_conflicts').fetchall()
    if conflicts:
        raise ValueError('Conflicting repeated event IDs: ' + ', '.join(row[0] for row in conflicts))
    if db.execute('SELECT COUNT(*) FROM parameters').fetchone()[0] != 1:
        raise ValueError('Exactly one reporting cutoff is required')
    row=db.execute('''SELECT COUNT(*) mature_users,COUNT(activation_day) activated_users,
         COALESCE(SUM(qualifying_orders>0),0) buyers,
         COALESCE(SUM(qualifying_cents),0) qualifying_cents FROM cohort_result''').fetchone()
    return dict(row)

class AnalyticsChecks(unittest.TestCase):
    def setUp(self): self.db=database()
    def tearDown(self): self.db.close()
    def test_baseline(self):
        self.assertEqual(analyze(self.db),dict(mature_users=6,activated_users=5,buyers=3,qualifying_cents=15000))
    def test_exact_duplicate(self):
        baseline=analyze(self.db)
        self.db.execute("INSERT INTO activation_events VALUES('e1','u1',1)")
        self.assertEqual(analyze(self.db),baseline)
    def test_conflicting_id(self):
        self.db.execute("INSERT INTO activation_events VALUES('e1','u2',1)")
        with self.assertRaisesRegex(ValueError,'Conflicting'): analyze(self.db)
    def test_window_boundary(self):
        baseline=analyze(self.db)
        self.db.execute("INSERT INTO orders VALUES('boundary','u6',12,'paid',9900)")
        self.assertEqual(analyze(self.db),baseline)
    def test_unactivated_purchase(self):
        baseline=analyze(self.db)
        self.db.execute("INSERT INTO orders VALUES('unactivated','u4',3,'paid',9900)")
        self.assertEqual(analyze(self.db),baseline)
    def test_immature_user(self):
        users=[r[0] for r in self.db.execute('SELECT user_id FROM cohort_result')]
        self.assertNotIn('u7',users); self.assertNotIn('u8',users)
    def test_exact_maturity(self):
        self.db.execute("INSERT INTO users VALUES('u9',7)")
        self.assertEqual(analyze(self.db)['mature_users'],7)
    def test_pre_activation_and_refund(self):
        row=self.db.execute("SELECT qualifying_orders,qualifying_cents FROM cohort_result WHERE user_id='u3'").fetchone()
        self.assertEqual(tuple(row),(0,0))
    def test_second_activation_not_fanout(self):
        self.db.execute("INSERT INTO orders VALUES('later-valid','u6',8,'paid',1000)")
        self.assertEqual(analyze(self.db)['qualifying_cents'],16000)
        self.assertEqual(analyze(self.db)['buyers'],4)
    def test_cutoff_validation(self):
        self.db.execute('INSERT INTO parameters VALUES(20)')
        with self.assertRaisesRegex(ValueError,'Exactly one'): analyze(self.db)
    def test_no_mature_users(self):
        self.db.execute('UPDATE parameters SET as_of_day=0')
        self.assertEqual(analyze(self.db),dict(mature_users=0,activated_users=0,buyers=0,qualifying_cents=0))

if __name__=='__main__':
    with database() as db:
        print(json.dumps({'summary':analyze(db),'users':[dict(row) for row in db.execute('SELECT * FROM cohort_result ORDER BY user_id')]},indent=2))
    unittest.main()