gigafibre-fsm/scripts/migration/fix_reversement.py

"""
Fix: Remove 'reversement' Payment Entries that were incorrectly imported.
These are system-generated reversal payments in legacy — NOT real customer payments.
They double-count with the credit note PLE entries we created in fix_reversals.py.
"""
import frappe, os, time
os.chdir("/home/frappe/frappe-bench/sites")
frappe.init(site="erp.gigafibre.ca", sites_path=".")
frappe.connect()
print("Connected:", frappe.local.site)

t0 = time.time()

# Find all Payment Entries that came from 'reversement' type payments
# These have legacy_payment_id set, and we can identify them via legacy DB
# But simpler: check which PEs are allocated to invoices that are targets of our reversal matches

# First, let's find reversement PEs by checking legacy
import pymysql
legacy = pymysql.connect(
    host="legacy-db", user="facturation", password="*******",
    database="gestionclient", cursorclass=pymysql.cursors.DictCursor
)

with legacy.cursor() as cur:
    cur.execute("""
        SELECT id FROM payment
        WHERE type = 'reversement'
          AND date_orig >= UNIX_TIMESTAMP('2024-04-08')
    """)
    rev_ids = [str(r['id']) for r in cur.fetchall()]
legacy.close()
print("Legacy reversement payments: {}".format(len(rev_ids)))

# Map to ERPNext PE names (PE-{hex10})
pe_names = []
for rid in rev_ids:
    pe_names.append("PE-{:010x}".format(int(rid)))

# Verify these exist
existing = frappe.db.sql("""
    SELECT name FROM "tabPayment Entry"
    WHERE name IN ({})
""".format(",".join(["'{}'".format(n) for n in pe_names])))
existing_names = [r[0] for r in existing]
print("Existing reversement PEs in ERPNext: {}".format(len(existing_names)))

if not existing_names:
    print("Nothing to delete.")
    exit()

# Load into temp table for efficient joins
frappe.db.sql("DROP TABLE IF EXISTS _tmp_rev_pe")
frappe.db.commit()
frappe.db.sql("""
    CREATE TABLE _tmp_rev_pe (
        pe_name VARCHAR(140) PRIMARY KEY
    )
""")
frappe.db.commit()

for i in range(0, len(existing_names), 5000):
    batch = existing_names[i:i+5000]
    values = ",".join(["('{}')".format(n) for n in batch])
    frappe.db.sql("INSERT INTO _tmp_rev_pe (pe_name) VALUES {}".format(values))
    frappe.db.commit()

# Delete PLE entries for these payment entries
deleted_ple = frappe.db.sql("""
    DELETE FROM "tabPayment Ledger Entry"
    WHERE voucher_type = 'Payment Entry'
      AND voucher_no IN (SELECT pe_name FROM _tmp_rev_pe)
""")
frappe.db.commit()
ple_count = frappe.db.sql("""
    SELECT COUNT(*) FROM "tabPayment Ledger Entry"
    WHERE voucher_type = 'Payment Entry'
      AND voucher_no IN (SELECT pe_name FROM _tmp_rev_pe)
""")[0][0]
print("Remaining PLE for reversement PEs: {} (should be 0)".format(ple_count))

# Delete GL entries for these payment entries
frappe.db.sql("""
    DELETE FROM "tabGL Entry"
    WHERE voucher_type = 'Payment Entry'
      AND voucher_no IN (SELECT pe_name FROM _tmp_rev_pe)
""")
frappe.db.commit()

# Delete Payment Entry References
frappe.db.sql("""
    DELETE FROM "tabPayment Entry Reference"
    WHERE parent IN (SELECT pe_name FROM _tmp_rev_pe)
""")
frappe.db.commit()

# Delete Payment Entries themselves
frappe.db.sql("""
    DELETE FROM "tabPayment Entry"
    WHERE name IN (SELECT pe_name FROM _tmp_rev_pe)
""")
frappe.db.commit()
print("Deleted {} reversement Payment Entries and their GL/PLE".format(len(existing_names)))

# Recalculate outstanding on ALL invoices that were targets of reversals
# (These are the invoices that had both a payment AND a credit PLE)
frappe.db.sql("""
    UPDATE "tabSales Invoice" si
    SET outstanding_amount = COALESCE((
        SELECT SUM(ple.amount)
        FROM "tabPayment Ledger Entry" ple
        WHERE ple.against_voucher_type = 'Sales Invoice'
          AND ple.against_voucher_no = si.name
          AND ple.delinked = 0
    ), si.grand_total)
    WHERE si.docstatus = 1 AND si.is_return != 1
""")
frappe.db.commit()

# Update statuses
frappe.db.sql("""UPDATE "tabSales Invoice" SET status = 'Paid'
    WHERE docstatus = 1 AND is_return != 1 AND ROUND(outstanding_amount::numeric, 2) = 0""")
frappe.db.sql("""UPDATE "tabSales Invoice" SET status = 'Overdue'
    WHERE docstatus = 1 AND is_return != 1 AND outstanding_amount > 0.005
      AND COALESCE(due_date, posting_date) < CURRENT_DATE""")
frappe.db.sql("""UPDATE "tabSales Invoice" SET status = 'Unpaid'
    WHERE docstatus = 1 AND is_return != 1 AND outstanding_amount > 0.005
      AND COALESCE(due_date, posting_date) >= CURRENT_DATE""")
frappe.db.sql("""UPDATE "tabSales Invoice" SET status = 'Credit Note Issued'
    WHERE docstatus = 1 AND is_return != 1 AND outstanding_amount < -0.005""")
frappe.db.commit()

# Cleanup
frappe.db.sql("DROP TABLE IF EXISTS _tmp_rev_pe")
frappe.db.commit()

# Verification
print("\n=== Verification ===")
outstanding = frappe.db.sql("""
    SELECT
        ROUND(SUM(CASE WHEN outstanding_amount > 0.005 THEN outstanding_amount ELSE 0 END)::numeric, 2) as owed,
        ROUND(SUM(CASE WHEN outstanding_amount < -0.005 THEN outstanding_amount ELSE 0 END)::numeric, 2) as overpaid
    FROM "tabSales Invoice" WHERE docstatus = 1
""", as_dict=True)[0]
print("  Outstanding: ${} owed | ${} overpaid".format(outstanding['owed'], outstanding['overpaid']))

statuses = frappe.db.sql("""
    SELECT status, COUNT(*) as cnt
    FROM "tabSales Invoice" WHERE docstatus = 1
    GROUP BY status ORDER BY cnt DESC
""", as_dict=True)
print("\n  Status breakdown:")
for s in statuses:
    print("    {}: {}".format(s['status'], s['cnt']))

# GL balance check
gl = frappe.db.sql("""
    SELECT
        ROUND(SUM(debit)::numeric, 2) as total_debit,
        ROUND(SUM(credit)::numeric, 2) as total_credit
    FROM "tabGL Entry"
""", as_dict=True)[0]
print("\n  GL Balance: debit={} credit={} diff={}".format(
    gl['total_debit'], gl['total_credit'],
    round(float(gl['total_debit'] or 0) - float(gl['total_credit'] or 0), 2)))

elapsed = time.time() - t0
print("\nDone in {:.0f}s".format(elapsed))