"""Fix GL entries to match updated invoice item income_accounts."""
import frappe, os, sys, time
sys.stdout = os.fdopen(sys.stdout.fileno(), 'w', 1)
os.chdir("/home/frappe/frappe-bench/sites")
frappe.init(site="erp.gigafibre.ca", sites_path=".")
frappe.connect()
print("Connected:", frappe.local.site)

DEFAULT_INCOME = "Autres produits d'exploitation - T"

print("=== Update GL Entries ===")
t0 = time.time()

# Get dominant income account per invoice (highest total amount)
print("  Getting dominant account per invoice...")
dom = frappe.db.sql("""
    SELECT parent, income_account, SUM(ABS(base_net_amount)) as total
    FROM "tabSales Invoice Item"
    WHERE income_account != %s
    GROUP BY parent, income_account
""", (DEFAULT_INCOME,))

inv_best = {}
for inv_name, acct, total in dom:
    total = float(total or 0)
    if inv_name not in inv_best or total > inv_best[inv_name][1]:
        inv_best[inv_name] = (acct, total)

print(f"  {len(inv_best)} invoices with mapped accounts")

# Batch update GL entries
by_acct = {}
for inv_name, (acct, _) in inv_best.items():
    by_acct.setdefault(acct, []).append(inv_name)

gl_updated = 0
for acct_name, inv_names in by_acct.items():
    for i in range(0, len(inv_names), 1000):
        batch = inv_names[i:i+1000]
        placeholders = ','.join(['%s'] * len(batch))
        frappe.db.sql(
            f"""UPDATE "tabGL Entry" SET account = %s
                WHERE voucher_type = 'Sales Invoice'
                AND voucher_no IN ({placeholders})
                AND account = %s""",
            [acct_name] + batch + [DEFAULT_INCOME]
        )
        frappe.db.commit()
    gl_updated += len(inv_names)
    print(f"    {acct_name}: {len(inv_names)} invoices")

print(f"  Total: {gl_updated} invoices [{time.time()-t0:.0f}s]")

# Verify
print("\n=== Verify ===")
r = frappe.db.sql("""
    SELECT account, COUNT(*) as cnt, ROUND(SUM(credit)::numeric, 2) as total_credit
    FROM "tabGL Entry"
    WHERE voucher_type = 'Sales Invoice' AND credit > 0
    GROUP BY account ORDER BY total_credit DESC LIMIT 25
""")
print("  GL credit entries by account (Sales Invoice):")
for row in r:
    print(f"    {row[1]:>10}  ${row[2]:>14}  {row[0]}")

# Drop temp table if exists
frappe.db.sql("DROP TABLE IF EXISTS _tmp_sku_income_map")
frappe.db.commit()

frappe.destroy()
print("\nDone!")