#!/usr/bin/env python3
"""
One-off helper: allocate existing customer_payments (abonos) to the oldest
unpaid credit sales for each customer.

This fixes historical data recorded before the customer-payment endpoint
started creating sale_payments rows automatically.

Run against a tenant database, e.g.:
    DATABASE_URL=postgresql://postgres@localhost/tenant_refaccionaria_la_casita \
        python3 scripts/allocate_existing_customer_payments.py
"""
import os
import sys
import psycopg2

DB_URL = os.environ.get('DATABASE_URL')
if not DB_URL:
    print('DATABASE_URL is required')
    sys.exit(1)

conn = psycopg2.connect(DB_URL)
cur = conn.cursor()

cur.execute("""
    SELECT customer_id, SUM(amount) as total_paid
    FROM customer_payments
    GROUP BY customer_id
    ORDER BY customer_id
""")
customer_payments = cur.fetchall()

inserted = 0
for customer_id, total_paid in customer_payments:
    total_paid = float(total_paid or 0)
    if total_paid <= 0:
        continue

    cur.execute("""
        SELECT s.id,
               s.total - COALESCE(SUM(sp.amount), 0) as balance
        FROM sales s
        LEFT JOIN sale_payments sp ON sp.sale_id = s.id
        WHERE s.customer_id = %s
          AND s.sale_type = 'credit'
          AND s.status = 'completed'
        GROUP BY s.id, s.total, s.created_at
        HAVING s.total - COALESCE(SUM(sp.amount), 0) > 0
        ORDER BY s.created_at
    """, (customer_id,))

    remaining = round(total_paid, 2)
    for sale_id, balance in cur.fetchall():
        if remaining <= 0:
            break
        pay = round(min(remaining, float(balance)), 2)
        cur.execute("""
            INSERT INTO sale_payments (sale_id, method, amount, reference)
            VALUES (%s, 'efectivo', %s, %s)
        """, (sale_id, pay, f'Abono cliente #{customer_id} (reproceso)'))
        remaining = round(remaining - pay, 2)
        inserted += 1

    if remaining > 0:
        print(f'  Warning: customer {customer_id} has ${remaining} not allocated to any sale')

conn.commit()
cur.close()
conn.close()
print(f'Allocated existing customer payments into {inserted} sale_payments rows.')