#!/usr/bin/env python3
"""Reproducible calculations for the synthetic spreadsheet-analysis case.

Reads only input.csv (same directory). Refunded rows are excluded from all
paid revenue figures; they are reported separately as refunded order value.

Formulas
--------
paid_revenue        = SUM(amount WHERE status == "paid")
refunded_value      = SUM(amount WHERE status == "refunded")
paid_order_count    = COUNT(rows WHERE status == "paid")
paid_order_average  = paid_revenue / paid_order_count
channel_paid_revenue[c] = SUM(amount WHERE status == "paid" AND channel == c)
channel_paid_orders[c]  = COUNT(rows WHERE status == "paid" AND channel == c)
channel_share[c]         = channel_paid_revenue[c] / paid_revenue
"""

import csv
from collections import defaultdict
from pathlib import Path

HERE = Path(__file__).resolve().parent
INPUT = HERE / "input.csv"


def load_rows(path):
    with path.open(newline="") as f:
        return list(csv.DictReader(f))


def main():
    rows = load_rows(INPUT)
    assert rows, "input.csv is empty"

    paid = [r for r in rows if r["status"] == "paid"]
    refunded = [r for r in rows if r["status"] == "refunded"]

    paid_revenue = sum(int(r["amount"]) for r in paid)
    refunded_value = sum(int(r["amount"]) for r in refunded)
    paid_count = len(paid)
    paid_average = paid_revenue / paid_count if paid_count else 0.0

    revenue_by_channel = defaultdict(int)
    orders_by_channel = defaultdict(int)
    for r in paid:
        revenue_by_channel[r["channel"]] += int(r["amount"])
        orders_by_channel[r["channel"]] += 1

    channels = sorted(revenue_by_channel)

    print("rows_total:", len(rows))
    print("paid_order_count:", paid_count)
    print("refunded_order_count:", len(refunded))
    print("paid_revenue:", paid_revenue)
    print("refunded_order_value:", refunded_value)
    print("paid_order_average:", round(paid_average, 2))
    print("revenue_by_channel:")
    for c in channels:
        share = revenue_by_channel[c] / paid_revenue if paid_revenue else 0.0
        print(
            f"  {c}: revenue={revenue_by_channel[c]} "
            f"orders={orders_by_channel[c]} share={share:.4f}"
        )

    # Reconciliation check: paid + refunded must equal the raw column total.
    raw_total = sum(int(r["amount"]) for r in rows)
    assert paid_revenue + refunded_value == raw_total, "reconciliation failed"
    print("reconciliation_ok:", paid_revenue + refunded_value == raw_total)


if __name__ == "__main__":
    main()