"""Independent synthetic portfolio study. No employer or customer records.
Run: python3 quality-analysis.py (beside quality-demo.csv and quality-analysis.sql).
"""
import csv
import sqlite3
from pathlib import Path
root = Path(__file__).parent
with sqlite3.connect(':memory:') as db:
    db.execute('CREATE TABLE production (week TEXT, cell TEXT, units INTEGER, rejects INTEGER)')
    with (root / 'quality-demo.csv').open() as handle:
        rows = list(csv.DictReader(handle))
    for row in rows:
        units, rejects = int(row['units']), int(row['rejects'])
        assert 0 <= rejects <= units and units > 0
        db.execute('INSERT INTO production VALUES (?, ?, ?, ?)', (row['week'], row['cell'], units, rejects))
    assert len({(r['week'], r['cell']) for r in rows}) == len(rows)
    for result in db.execute((root / 'quality-analysis.sql').read_text()):
        print(result)