"""Verify the public spending-concentration finding used in Richie Lin's portfolio.
Run: python3 verify-spending.py wholesale-customers.csv
Python standard library only. This companion reproduces spending totals, not K-means.
Dataset: Cardoso, M. (2013), Wholesale customers, UCI, https://doi.org/10.24432/C5030X
License: CC BY 4.0, https://creativecommons.org/licenses/by/4.0/
The CSV source is unchanged. This verification script was added for the portfolio.
"""
import csv
import sqlite3
import sys
from pathlib import Path

columns = ['Fresh', 'Milk', 'Grocery', 'Frozen', 'Detergents_Paper', 'Delicassen']
path = Path(sys.argv[1]) if len(sys.argv) > 1 else Path(__file__).with_name('wholesale-customers.csv')
with path.open(newline='') as stream:
    rows = [{key: int(value) for key, value in row.items()} for row in csv.DictReader(stream)]
assert len(rows) == 440, 'Expected 440 source records'
assert all(row[key] >= 0 for row in rows for key in columns), 'Negative spending found'
customer_totals = [sum(row[key] for key in columns) for row in rows]
category_total = sum(sum(row[key] for row in rows) for key in columns)
assert sum(customer_totals) == category_total == 14619500

with sqlite3.connect(':memory:') as db:
    db.execute('CREATE TABLE spending (customer_id INTEGER, category TEXT, amount INTEGER, PRIMARY KEY(customer_id, category))')
    db.executemany('INSERT INTO spending VALUES (?, ?, ?)', ((i+1, key, row[key]) for i, row in enumerate(rows) for key in columns))
    total, count = db.execute('SELECT SUM(amount), COUNT(DISTINCT customer_id) FROM spending').fetchone()
    top_spend = db.execute('SELECT SUM(total) FROM (SELECT customer_id, SUM(amount) AS total FROM spending GROUP BY customer_id ORDER BY total DESC, customer_id LIMIT 88)').fetchone()[0]
assert total == category_total and count == 440
assert top_spend == 6272473
print(f'Records: {count}; annual spend: {total:,} monetary units')
print(f'Top 88 customers: {top_spend:,} / {total:,} = {top_spend/total:.4%}')
print('PASS: customer totals, category totals, SQL totals, and the 42.9% finding reconcile.')