gigafibre-fsm/scripts/migration/import_payments.py

#!/usr/bin/env python3
"""Import legacy payments (24 months) as Payment Entry + references to Sales Invoices."""
import pymysql
import psycopg2
import uuid
from datetime import datetime, timezone

LEGACY = {"host": "legacy-db", "user": "facturation", "password": "VD67owoj",
          "database": "gestionclient", "connect_timeout": 30, "read_timeout": 600}
PG = {"host": "db", "port": 5432, "user": "postgres", "password": "123",
      "dbname": "_eb65bdc0c4b1b2d6"}

ADMIN = "Administrator"
COMPANY = "TARGO"

# Legacy type → ERPNext mode_of_payment
MODE_MAP = {
    "ppa": "Bank Draft",
    "paiement direct": "Bank Draft",
    "carte credit": "Credit Card",
    "cheque": "Cheque",
    "comptant": "Cash",
    "reversement": "Bank Draft",
    "credit": "Credit Note",
    "credit targo": "Credit Note",
    "credit facture": "Credit Note",
}

def uid(p=""):
    return p + uuid.uuid4().hex[:10]

def ts_to_dt(t):
    if not t or t <= 0: return None
    try: return datetime.fromtimestamp(int(t), tz=timezone.utc).strftime("%Y-%m-%d %H:%M:%S")
    except: return None

def ts_to_date(t):
    if not t or t <= 0: return None
    try: return datetime.fromtimestamp(int(t), tz=timezone.utc).strftime("%Y-%m-%d")
    except: return None

def log(msg):
    print(msg, flush=True)

def main():
    log("=== Import Payments (24 months) ===")

    mc = pymysql.connect(**LEGACY)
    cur = mc.cursor(pymysql.cursors.DictCursor)
    cutoff = int(datetime.now(timezone.utc).timestamp()) - (24 * 30 * 86400)

    cur.execute("SELECT * FROM payment WHERE date_orig >= %s AND type != 'credit' AND type != 'credit targo' AND type != 'credit facture' ORDER BY id", (cutoff,))
    payments = cur.fetchall()
    log("  {} payments (excl credits)".format(len(payments)))

    # Payment items (links to invoices)
    pay_ids = [p["id"] for p in payments]
    items_by_pay = {}
    chunk = 10000
    for s in range(0, len(pay_ids), chunk):
        batch = pay_ids[s:s+chunk]
        cur.execute("SELECT * FROM payment_item WHERE payment_id IN ({})".format(",".join(["%s"]*len(batch))), batch)
        for r in cur.fetchall():
            items_by_pay.setdefault(r["payment_id"], []).append(r)
    mc.close()
    log("  {} payment-invoice links loaded".format(sum(len(v) for v in items_by_pay.values())))

    pg = psycopg2.connect(**PG)
    pgc = pg.cursor()

    # Mappings
    pgc.execute('SELECT legacy_account_id, name FROM "tabCustomer" WHERE legacy_account_id > 0')
    cust_map = {r[0]: r[1] for r in pgc.fetchall()}

    pgc.execute('SELECT legacy_invoice_id, name FROM "tabSales Invoice" WHERE legacy_invoice_id > 0')
    inv_map = {r[0]: r[1] for r in pgc.fetchall()}

    pgc.execute("SELECT name FROM \"tabAccount\" WHERE account_type = 'Receivable' AND company = %s AND is_group = 0 LIMIT 1", (COMPANY,))
    receivable = pgc.fetchone()[0]

    pgc.execute("SELECT name FROM \"tabAccount\" WHERE account_type = 'Bank' AND company = %s AND is_group = 0 LIMIT 1", (COMPANY,))
    bank_row = pgc.fetchone()
    bank_acct = bank_row[0] if bank_row else "Banque - T"

    # Check existing
    pgc.execute("SELECT name FROM \"tabPayment Entry\" WHERE name LIKE 'PE-%'")
    existing = set(r[0] for r in pgc.fetchall())

    # Add custom field for legacy payment id
    try:
        pgc.execute("""
            INSERT INTO "tabCustom Field" (name, creation, modified, modified_by, owner, docstatus, idx,
                dt, label, fieldname, fieldtype, insert_after)
            VALUES (%s, %s, %s, %s, %s, 0, 0,
                'Payment Entry', 'Legacy Payment ID', 'legacy_payment_id', 'Int', 'naming_series')
        """, (uid("CF-"), ts_to_dt(int(datetime.now(timezone.utc).timestamp())),
              ts_to_dt(int(datetime.now(timezone.utc).timestamp())), ADMIN, ADMIN))
        pg.commit()
        log("  Created legacy_payment_id custom field")
    except:
        pg.rollback()
        log("  legacy_payment_id field already exists")

    # Check if column exists
    pgc.execute("""SELECT column_name FROM information_schema.columns
                   WHERE table_name = 'tabPayment Entry' AND column_name = 'legacy_payment_id'""")
    has_legacy_col = pgc.fetchone() is not None

    pay_ok = pay_skip = pay_err = ref_ok = 0
    now_ts = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S.%f")

    for i, p in enumerate(payments):
        pe_name = "PE-{}".format(uid())
        if pe_name in existing:
            pay_skip += 1
            continue

        cust_name = cust_map.get(p["account_id"])
        if not cust_name:
            pay_err += 1
            continue

        posting_date = ts_to_date(p["date_orig"]) or "2025-01-01"
        amount = round(abs(float(p["amount"] or 0)), 2)
        if amount <= 0:
            pay_skip += 1
            continue

        mode = MODE_MAP.get(p.get("type", ""), "Bank Draft")
        ref = p.get("reference") or ""
        memo = p.get("memo") or ""

        try:
            cols = """name, creation, modified, modified_by, owner, docstatus, idx,
                    naming_series, payment_type, posting_date, company,
                    mode_of_payment, party_type, party,
                    paid_from, paid_to, paid_amount, received_amount,
                    base_paid_amount, base_received_amount,
                    target_exchange_rate, source_exchange_rate,
                    reference_no, reference_date, remarks,
                    status"""
            vals = [pe_name, now_ts, now_ts, ADMIN, ADMIN, 0, 0,
                    'ACC-PAY-.YYYY.-', 'Receive', posting_date, COMPANY,
                    mode, 'Customer', cust_name,
                    bank_acct, receivable, amount, amount,
                    amount, amount,
                    1, 1,
                    ref[:140] if ref else None, posting_date, memo[:140] if memo else None,
                    'Draft']

            if has_legacy_col:
                cols += ", legacy_payment_id"
                vals.append(p["id"])

            placeholders = ",".join(["%s"] * len(vals))
            pgc.execute('INSERT INTO "tabPayment Entry" ({}) VALUES ({})'.format(cols, placeholders), vals)

            # Payment Entry References (link to invoices)
            for j, pi in enumerate(items_by_pay.get(p["id"], [])):
                inv_name = inv_map.get(pi.get("invoice_id"))
                if not inv_name:
                    continue
                alloc = round(abs(float(pi.get("amount") or 0)), 2)
                pgc.execute("""
                    INSERT INTO "tabPayment Entry Reference" (
                        name, creation, modified, modified_by, owner, docstatus, idx,
                        reference_doctype, reference_name, allocated_amount,
                        total_amount, outstanding_amount, exchange_rate,
                        parent, parentfield, parenttype
                    ) VALUES (%s, %s, %s, %s, %s, 0, %s,
                              'Sales Invoice', %s, %s,
                              %s, %s, 1,
                              %s, 'references', 'Payment Entry')
                """, (uid("PER-"), now_ts, now_ts, ADMIN, ADMIN, j+1,
                      inv_name, alloc, alloc, alloc,
                      pe_name))
                ref_ok += 1

            pay_ok += 1

        except Exception as e:
            pay_err += 1
            pg.rollback()
            if pay_err <= 10:
                log("  ERR pay#{} -> {}".format(p["id"], str(e)[:100]))
            continue

        if pay_ok % 2000 == 0:
            pg.commit()
            log("  [{}/{}] pay={} refs={} skip={} err={}".format(
                i+1, len(payments), pay_ok, ref_ok, pay_skip, pay_err))

    pg.commit()

    # Fix creation dates
    log("")
    log("--- Fixing Payment Entry dates ---")
    mc = pymysql.connect(**LEGACY)
    cur = mc.cursor(pymysql.cursors.DictCursor)
    cur.execute("SELECT id, date_orig FROM payment WHERE date_orig >= %s ORDER BY id", (cutoff,))
    pay_dates = {r["id"]: r["date_orig"] for r in cur.fetchall()}
    mc.close()

    if has_legacy_col:
        pgc.execute('SELECT name, legacy_payment_id FROM "tabPayment Entry" WHERE legacy_payment_id > 0')
        for name, pid in pgc.fetchall():
            dt = ts_to_dt(pay_dates.get(pid))
            if dt:
                pgc.execute('UPDATE "tabPayment Entry" SET creation = %s, modified = %s WHERE name = %s',
                             (dt, dt, name))
        pg.commit()
        log("  Dates fixed")

    pg.close()
    log("")
    log("=" * 60)
    log("Payments: {} created, {} skipped, {} errors".format(pay_ok, pay_skip, pay_err))
    log("Invoice refs: {}".format(ref_ok))
    log("=" * 60)

if __name__ == "__main__":
    main()