"""Customer 360 mart and retention queue using SQLite + pandas."""

from pathlib import Path
import sqlite3

import pandas as pd


DATA_DIR = Path("public/project-assets/data")
OUT = DATA_DIR / "customer_360_retention_queue.csv"
SNAPSHOT_DATE = pd.Timestamp("2026-07-01")


def main() -> None:
    customers = pd.read_csv(DATA_DIR / "customers.csv")
    orders = pd.read_csv(DATA_DIR / "orders_raw.csv")
    orders = orders.drop_duplicates("order_id")
    orders = orders.loc[orders["status"].eq("Completed")].copy()

    with sqlite3.connect(":memory:") as database:
        customers.to_sql("customers", database, index=False)
        orders.to_sql("orders", database, index=False)
        customer_360 = pd.read_sql_query(
            """
            SELECT
              c.customer_id,
              c.region,
              c.segment,
              c.marketing_source,
              COUNT(o.order_id) AS order_frequency,
              ROUND(COALESCE(SUM(o.order_value_gbp), 0), 2) AS lifetime_value_gbp,
              ROUND(COALESCE(AVG(o.order_value_gbp), 0), 2) AS average_order_value_gbp,
              MAX(o.order_date) AS last_order_date
            FROM customers c
            LEFT JOIN orders o ON o.customer_id = c.customer_id
            GROUP BY c.customer_id, c.region, c.segment, c.marketing_source
            """,
            database,
        )

    customer_360["last_order_date"] = pd.to_datetime(customer_360["last_order_date"])
    customer_360["recency_days"] = (SNAPSHOT_DATE - customer_360["last_order_date"]).dt.days
    customer_360["value_band"] = pd.qcut(
        customer_360["lifetime_value_gbp"].rank(method="first"),
        4,
        labels=["Low", "Developing", "High", "Highest"],
    )
    customer_360["retention_priority"] = (
        (customer_360["order_frequency"] >= 4)
        & (customer_360["recency_days"] > 120)
    )
    customer_360["priority_score"] = (
        customer_360["lifetime_value_gbp"].rank(pct=True) * 60
        + customer_360["recency_days"].rank(pct=True) * 40
    ).round(1)

    queue = customer_360.loc[customer_360["retention_priority"]].sort_values(
        ["priority_score", "lifetime_value_gbp"], ascending=False
    )
    queue.to_csv(OUT, index=False)
    print(f"Customer mart rows: {len(customer_360):,}")
    print(f"High-value at-risk customers: {len(queue):,}")
    print(queue.head(15).to_string(index=False))
    print(f"Retention queue written to {OUT}")


if __name__ == "__main__":
    main()