"Final project: Tallybook's invoicing database and API"

import sqlite3
from decimal import Decimal, ROUND_HALF_UP

import pandas as pd

base = "https://academy.cloudtechanalytics.com/datasets/invoicing/"
customers = pd.read_csv(base + "customers.csv")
raw = pd.read_csv(base + "invoices_raw.csv", dtype=str, keep_default_na=False).drop_duplicates()
lines = pd.read_csv(base + "invoice_lines.csv", dtype={"unit_price": str})

def kobo(naira):
    return int((Decimal(naira.replace("₦", "").replace(",", "")) * 100).quantize(Decimal("1"), rounding=ROUND_HALF_UP))

bad = set(lines.loc[lines["quantity"] <= 0, "invoice_id"])
clean = raw[raw["customer_id"].isin(set(customers["customer_id"])) & raw["issue_date"].str.match(r"^\d{4}-\d{2}-\d{2}
quot;)
            & (raw["discount_pct"].astype(int) <= 20) & (raw["due_date"] >= raw["issue_date"]) & ~raw["invoice_id"].isin(bad)]

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (customer_id TEXT PRIMARY KEY, vat_exempt INTEGER);
CREATE TABLE invoices (invoice_id TEXT PRIMARY KEY, customer_id TEXT, discount_pct INTEGER, paid_kobo INTEGER);
CREATE TABLE invoice_lines (invoice_id TEXT, quantity INTEGER, unit_price_kobo INTEGER);
""")
db.executemany("INSERT INTO customers VALUES (?, ?)", customers[["customer_id", "vat_exempt"]].itertuples(index=False))
db.executemany("INSERT INTO invoices VALUES (?, ?, ?, ?)", [(r.invoice_id, r.customer_id, int(r.discount_pct), kobo(r.amount_paid)) for r in clean.itertuples()])
db.executemany("INSERT INTO invoice_lines VALUES (?, ?, ?)", [(r.invoice_id, r.quantity, kobo(r.unit_price)) for r in lines[lines["invoice_id"].isin(clean["invoice_id"])].itertuples()])

rows = db.execute("""
    SELECT i.invoice_id, i.discount_pct, c.vat_exempt, i.paid_kobo, SUM(l.quantity * l.unit_price_kobo) AS subtotal
    FROM invoices i JOIN customers c USING (customer_id) JOIN invoice_lines l USING (invoice_id)
    GROUP BY i.invoice_id
""").fetchall()

def rk(x):
    return int(Decimal(x).quantize(Decimal("1"), rounding=ROUND_HALF_UP))

invoiced = paid = 0
for invoice_id, discount, exempt, paid_kobo, subtotal in rows:
    after = subtotal - rk(Decimal(subtotal) * discount / 100)
    invoiced += after + (0 if exempt else rk(after * Decimal("0.075")))
    paid += paid_kobo
print(f"Invoices: {len(rows)}")
print(f"Invoiced:    ₦{invoiced / 100:,.2f}")
print(f"Paid:        ₦{paid / 100:,.2f}")
print(f"Outstanding: ₦{(invoiced - paid) / 100:,.2f}")

Invoices: 1133
Invoiced:    ₦138,718,871.36
Paid:        ₦104,078,481.37
Outstanding: ₦34,640,389.99