# Step 1: Get per-customer transaction summaries by month
txns_by_customer = (
    pl.Transaction.select(
        pl.attr.sender.account_id,
        pl.attr.created_at.year(),
        pl.attr.created_at.month(),
        pl.attr.amount.sum(),
        pl.attr.id.count(),
    )
    .filter_by(pl.attr.type == "payment", pl.attr.status == "processed")
    .group_by(
        pl.attr.sender.account_id,
        pl.attr.created_at.year(),
        pl.attr.created_at.month(),
    )
    .all()
)
 
# Step 2: Get account creation dates for cohort assignment
account_ids = list({row.sender.account_id for row in txns_by_customer})
signup_month = {}
if account_ids:
    accounts = (
        pl.Account.filter_by(pl.attr.id == "|".join(account_ids))
        .select(pl.attr.id, pl.attr.created_at)
        .all()
    )
    for acct in accounts:
        d = str(acct.created_at)
        dt = datetime.fromisoformat(d.replace("Z", "+00:00"))
        signup_month[acct.id] = f"{dt.year}-{dt.month}"
 
# Step 3: Aggregate into cohorts client-side
cohorts = {}
for row in txns_by_customer:
    aid = row.sender.account_id
    signup = signup_month.get(aid, "unknown")
    active = f"{row['year(created_at)']}-{row['month(created_at)']}"
    key = f"{signup}|{active}"
 
    if key not in cohorts:
        cohorts[key] = {
            "signup": signup,
            "active": active,
            "customers": set(),
            "revenue": 0,
            "count": 0,
        }
    cohorts[key]["customers"].add(aid)
    cohorts[key]["revenue"] += row["sum(amount)"] or 0
    cohorts[key]["count"] += row["count(id)"] or 0
 
# Analyze retention by cohort
print("Cohort Analysis:")
for c in cohorts.values():
    print(
        f"Cohort {c['signup']} in {c['active']}: {len(c['customers'])} active customers, "
        f"${c['revenue']:,.2f} revenue"
    )

# Get first transaction date for each customer
first_transactions = (
    pl.Transaction.select(pl.attr.sender.account_id, pl.attr.created_at.min())
    .filter_by(pl.attr.type == "payment")
    .group_by(pl.attr.sender.account_id)
    .all()
)
 
# Create lookup of first transaction dates
first_txn_dates = {
    row.sender.account_id: row["min(created_at)"] for row in first_transactions
}
 
# Analyze transactions by new vs returning
thirty_days_ago = (datetime.now(timezone.utc) - timedelta(days=30)).isoformat()
 
recent_transactions = pl.Transaction.filter_by(
    pl.attr.type == "payment",
    pl.attr.status == "processed",
    pl.attr.created_at >= thirty_days_ago,
).all()
 
new_customer_revenue = 0
returning_customer_revenue = 0
new_count = 0
returning_count = 0
 
for txn in recent_transactions:
    first_date = first_txn_dates.get(txn.sender.account_id)
 
    if first_date and str(first_date) >= thirty_days_ago:
        new_customer_revenue += txn.amount or 0
        new_count += 1
    else:
        returning_customer_revenue += txn.amount or 0
        returning_count += 1
 
print("Last 30 Days:")
print(f"New Customers: ${new_customer_revenue:,.2f} from {new_count} transactions")
print(
    f"Returning Customers: ${returning_customer_revenue:,.2f} from {returning_count} transactions"
)

# Analyze when customers make purchases
customer_timing = (
    pl.Transaction.select(
        pl.attr.sender.account_id,
        pl.attr.created_at.dayofweek(),
        pl.attr.created_at.hour(),
        pl.attr.id.count(),
    )
    .filter_by(pl.attr.type == "payment", pl.attr.status == "processed")
    .group_by(
        pl.attr.sender.account_id,
        pl.attr.created_at.dayofweek(),
        pl.attr.created_at.hour(),
    )
    .all()
)
 
# Identify peak purchase times per customer
from collections import defaultdict
 
customer_peaks = defaultdict(list)
 
for row in customer_timing:
    customer_peaks[row.sender.account_id].append(
        {
            "day": row["dayofweek(created_at)"],
            "hour": row["hour(created_at)"],
            "count": row["count(id)"],
        }
    )
 
# Find most common purchase time per customer
for account_id, times in customer_peaks.items():
    peak = max(times, key=lambda x: x["count"])
    days = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
    print(f"{account_id}: Most active {days[peak['day']]} at {peak['hour']}:00")

# Analyze payment method usage per customer
customer_methods = (
    pl.Transaction.select(
        pl.attr.sender.account_id,
        pl.attr.sender.method_id,
        pl.attr.id.count(),
        pl.attr.amount.sum(),
    )
    .filter_by(pl.attr.type == "payment", pl.attr.status == "processed")
    .group_by(pl.attr.sender.account_id, pl.attr.sender.method_id)
    .all()
)
 
# Identify preferred payment method
from collections import defaultdict
 
customer_prefs = defaultdict(list)
 
for row in customer_methods:
    customer_prefs[row.sender.account_id].append(
        {
            "method": row.sender.method_id,
            "count": row["count(id)"],
            "amount": row["sum(amount)"],
        }
    )
 
for account_id, methods in customer_prefs.items():
    preferred = sorted(methods, key=lambda x: x["count"], reverse=True)[0]
    print(f"{account_id}: Prefers {preferred['method']} ({preferred['count']} uses)")

# Identify churn risk factors
churn_analysis = (
    pl.Transaction.select(
        pl.attr.sender.account_id,
        pl.attr.amount.sum(),
        pl.attr.id.count(),
        pl.attr.created_at.max(),
        pl.attr.amount.avg(),
        pl.attr.created_at.min(),
    )
    .filter_by(pl.attr.type == "payment", pl.attr.status == "processed")
    .group_by(pl.attr.sender.account_id)
    .all()
)
 
# Score churn risk
high_risk_customers = []
 
for customer in churn_analysis:
    last_purchase = str(customer["max(created_at)"])
    first_purchase = str(customer["min(created_at)"])
    days_since_last = (datetime.now() - datetime.fromisoformat(last_purchase.replace("Z", "+00:00")).replace(tzinfo=None)).days
    customer_age_days = (datetime.now() - datetime.fromisoformat(first_purchase.replace("Z", "+00:00")).replace(tzinfo=None)).days
    total_value = customer["sum(amount)"] or 0
    txn_count = customer["count(id)"] or 0
 
    if customer_age_days <= 0:
        continue
 
    expected_frequency = customer_age_days / txn_count
 
    if days_since_last > (expected_frequency * 2) and total_value > 1000:
        risk_score = min(days_since_last / expected_frequency, 10)
        high_risk_customers.append(
            {
                "account_id": customer.sender.account_id,
                "risk_score": risk_score,
                "value": total_value,
                "days_since_last": days_since_last,
            }
        )
 
high_risk_customers.sort(key=lambda x: x["risk_score"] * x["value"], reverse=True)
 
print("Top 10 At-Risk High-Value Customers:")
for i, customer in enumerate(high_risk_customers[:10], 1):
    print(f"{i}. {customer['account_id']}")
    print(f"   Value: ${customer['value']:,.2f}")
    print(f"   Days since last: {customer['days_since_last']}")
    print(f"   Risk score: {customer['risk_score']:.1f}")

# Good: Includes context
complete = (
    pl.Transaction.select(
        pl.attr.sender.account_id,
        pl.attr.amount.sum(),  # Total spend
        pl.attr.id.count(),  # Number of transactions
        pl.attr.amount.avg(),  # Average order value
    )
    .group_by(pl.attr.sender.account_id)
    .all()
)
 
# Incomplete: Missing context
incomplete = (
    pl.Transaction.select(
        pl.attr.sender.account_id,
        pl.attr.amount.sum(),  # Just total - need frequency too
    )
    .group_by(pl.attr.sender.account_id)
    .all()
)

# Always include last transaction date
ltv_with_recency = (
    pl.Transaction.select(
        pl.attr.sender.account_id,
        pl.attr.amount.sum(),
        pl.attr.id.count(),
        pl.attr.created_at.max(),  # Last transaction date
        pl.attr.created_at.min(),  # First transaction date
    )
    .filter_by(pl.attr.type == "payment", pl.attr.status == "processed")
    .group_by(pl.attr.sender.account_id)
    .all()
)

from datetime import datetime, timedelta
 
# Segment by value and activity
segments = {
    "champions": [],  # High value, recently active
    "at_risk": [],  # High value, not recently active
    "promising": [],  # Medium value, recently active
    "hibernating": [],  # Low value, not recently active
}
 
threshold_date = datetime.now() - timedelta(days=60)
high_value_threshold = 5000
 
for customer in ltv_data:
    is_recent = customer["max(created_at)"] >= threshold_date
    is_high_value = customer["sum(amount)"] >= high_value_threshold
 
    if is_high_value and is_recent:
        segments["champions"].append(customer)
    elif is_high_value and not is_recent:
        segments["at_risk"].append(customer)
    elif not is_high_value and is_recent:
        segments["promising"].append(customer)
    else:
        segments["hibernating"].append(customer)
 
# Different actions per segment
print("Champions: Reward with VIP benefits")
print("At Risk: Re-engagement campaign")
print("Promising: Upsell campaign")
print("Hibernating: Win-back offer")