"""Governed sales KPI pipeline using pandas and SQLite."""

from pathlib import Path
import sqlite3

import pandas as pd


DATA_DIR = Path("public/project-assets/data")
OUT = DATA_DIR / "executive_sales_kpis.csv"


def main() -> None:
    customers = pd.read_csv(DATA_DIR / "customers.csv", parse_dates=["signup_date"])
    raw_orders = pd.read_csv(DATA_DIR / "orders_raw.csv", parse_dates=["order_date"])

    quality = {
        "raw_rows": len(raw_orders),
        "duplicate_order_ids": int(raw_orders.duplicated("order_id").sum()),
        "missing_channels": int(raw_orders["channel"].isna().sum()),
    }

    orders = raw_orders.drop_duplicates("order_id").copy()
    orders["channel"] = orders["channel"].fillna("Unknown").str.strip().replace("", "Unknown")
    orders = orders.loc[orders["status"].eq("Completed")]
    orders["month"] = orders["order_date"].dt.to_period("M").astype(str)

    with sqlite3.connect(":memory:") as database:
        customers.to_sql("customers", database, index=False, if_exists="replace")
        orders.to_sql("orders", database, index=False, if_exists="replace")
        monthly_kpis = pd.read_sql_query(
            """
            WITH monthly_customer AS (
              SELECT
                substr(order_date, 1, 7) AS month,
                customer_id,
                COUNT(*) AS orders,
                SUM(order_value_gbp) AS revenue_gbp
              FROM orders
              GROUP BY 1, 2
            )
            SELECT
              month,
              SUM(orders) AS completed_orders,
              COUNT(DISTINCT customer_id) AS active_customers,
              ROUND(SUM(revenue_gbp), 2) AS revenue_gbp,
              ROUND(SUM(revenue_gbp) / NULLIF(SUM(orders), 0), 2) AS aov_gbp,
              ROUND(100.0 * SUM(CASE WHEN orders >= 2 THEN 1 ELSE 0 END)
                    / COUNT(*), 1) AS same_month_repeat_customer_pct
            FROM monthly_customer
            GROUP BY month
            ORDER BY month
            """,
            database,
        )

    monthly_kpis.to_csv(OUT, index=False)
    print("Data-quality controls:", quality)
    print(monthly_kpis.tail(6).to_string(index=False))
    print(f"Governed monthly KPI table written to {OUT}")


if __name__ == "__main__":
    main()