gigafibre-fsm/scripts/migration/rename_customers.py

#!/usr/bin/env python3
"""
Rename all Customer IDs to CUST-{legacy_customer_id}.

Handles all FK references across the entire ERPNext database:
  - Sales Invoice, Payment Entry, GL Entry, PLE, Issues, Locations,
    Subscriptions, Equipment, Dispatch Jobs, Dynamic Links, Comments, etc.

Approach:
  1. Build old_name → new_name mapping from legacy_customer_id
  2. Create temp mapping table in PostgreSQL
  3. Bulk UPDATE each FK table using JOIN to mapping table
  4. Rename Customer records themselves
  5. Set naming series counter for new customers
  6. Verify all references updated

Run inside erpnext-backend-1:
  /home/frappe/frappe-bench/env/bin/python /home/frappe/frappe-bench/rename_customers.py [--dry-run]

Estimated time: 5-10 minutes (millions of rows to update)
"""
import sys
import os
import time
from datetime import datetime, timezone

os.chdir("/home/frappe/frappe-bench/sites")
import frappe

frappe.init(site="erp.gigafibre.ca", sites_path=".")
frappe.connect()
frappe.local.flags.ignore_permissions = True
print(f"Connected: {frappe.local.site}")

DRY_RUN = "--dry-run" in sys.argv
# Use legacy customer_id as the new name: CUST-{legacy_customer_id}
USE_LEGACY_CODE = "--legacy-code" in sys.argv
# Use zero-padded numeric: CUST-{legacy_account_id:015d}
USE_NUMERIC = "--numeric" in sys.argv

if not USE_LEGACY_CODE and not USE_NUMERIC:
    USE_LEGACY_CODE = True  # default

if DRY_RUN:
    print("*** DRY RUN — no changes will be written ***")

mode_label = "legacy_customer_id" if USE_LEGACY_CODE else "zero-padded numeric"
print(f"Naming mode: {mode_label}")


# ═══════════════════════════════════════════════════════════════
# STEP 1: BUILD MAPPING
# ═══════════════════════════════════════════════════════════════
print("\n" + "=" * 70)
print("STEP 1: BUILD NAME MAPPING")
print("=" * 70)
t0 = time.time()

customers = frappe.db.sql("""
    SELECT name, legacy_account_id, legacy_customer_id, customer_name
    FROM "tabCustomer"
    ORDER BY legacy_account_id
""", as_dict=True)

mapping = {}  # old_name → new_name
collisions = []
new_names_used = set()

for c in customers:
    old_name = c["name"]

    if USE_LEGACY_CODE:
        cid = c.get("legacy_customer_id") or ""
        if cid:
            new_name = f"CUST-{cid}"
        else:
            # Fallback to zero-padded account ID
            new_name = f"CUST-{c['legacy_account_id']:015d}"
    else:
        new_name = f"CUST-{c['legacy_account_id']:015d}"

    # Check for collision
    if new_name in new_names_used:
        collisions.append((old_name, new_name, c["customer_name"]))
        # Append account ID to disambiguate
        new_name = f"{new_name}-{c['legacy_account_id']}"

    new_names_used.add(new_name)

    if old_name != new_name:
        mapping[old_name] = new_name

# Stats
unchanged = len(customers) - len(mapping)
print(f"  Total customers: {len(customers)}")
print(f"  Will rename: {len(mapping)}")
print(f"  Already correct: {unchanged}")
print(f"  Collisions resolved: {len(collisions)}")

if collisions:
    print(f"  Collision examples:")
    for old, new, name in collisions[:5]:
        print(f"    {old} → {new} ({name})")

# Show sample mappings
print(f"\n  Sample renames:")
for old, new in list(mapping.items())[:10]:
    cust = next((c for c in customers if c["name"] == old), {})
    print(f"    {old:25s} → {new:35s}  ({cust.get('customer_name', '')})")

if DRY_RUN:
    print(f"\n  [{time.time()-t0:.1f}s]")
    print("\n*** DRY RUN complete — no changes made ***")
    sys.exit(0)


# ═══════════════════════════════════════════════════════════════
# STEP 2: CREATE TEMP MAPPING TABLE
# ═══════════════════════════════════════════════════════════════
print("\n" + "=" * 70)
print("STEP 2: CREATE TEMP MAPPING TABLE")
print("=" * 70)

frappe.db.sql('DROP TABLE IF EXISTS _customer_rename_map')
frappe.db.sql("""
    CREATE TEMP TABLE _customer_rename_map (
        old_name VARCHAR(140) PRIMARY KEY,
        new_name VARCHAR(140) NOT NULL
    )
""")

# Insert in batches
batch = []
for old, new in mapping.items():
    batch.append((old, new))
    if len(batch) >= 1000:
        frappe.db.sql(
            "INSERT INTO _customer_rename_map (old_name, new_name) VALUES " +
            ",".join(["(%s, %s)"] * len(batch)),
            [v for pair in batch for v in pair]
        )
        batch = []
if batch:
    frappe.db.sql(
        "INSERT INTO _customer_rename_map (old_name, new_name) VALUES " +
        ",".join(["(%s, %s)"] * len(batch)),
        [v for pair in batch for v in pair]
    )

frappe.db.commit()
print(f"  Inserted {len(mapping)} mappings into temp table")


# ═══════════════════════════════════════════════════════════════
# STEP 3: UPDATE ALL FK REFERENCES
# ═══════════════════════════════════════════════════════════════
print("\n" + "=" * 70)
print("STEP 3: UPDATE FK REFERENCES")
print("=" * 70)

# All tables with customer references: (table, column, extra_where)
fk_tables = [
    ("tabSales Invoice", "customer", ""),
    ("tabPayment Entry", "party", ""),
    ("tabGL Entry", "party", ""),
    ("tabPayment Ledger Entry", "party", ""),
    ("tabIssue", "customer", ""),
    ("tabService Location", "customer", ""),
    ("tabService Subscription", "customer", ""),
    ("tabSubscription", "party", ""),
    ("tabService Equipment", "customer", ""),
    ("tabDispatch Job", "customer", ""),
    ("tabDynamic Link", "link_name", "AND t.link_doctype = 'Customer'"),
    ("tabComment", "reference_name", "AND t.reference_doctype = 'Customer'"),
    ("tabCommunication", "reference_name", "AND t.reference_doctype = 'Customer'"),
    # Version log
    ("tabVersion", "docname", "AND t.ref_doctype = 'Customer'"),
    # Sales taxes (parent references invoice, not customer directly)
    # Payment Entry Reference has no direct customer column
]

total_updated = 0
for table, col, extra_where in fk_tables:
    t0 = time.time()
    try:
        result = frappe.db.sql(f"""
            UPDATE "{table}" t
            SET "{col}" = m.new_name
            FROM _customer_rename_map m
            WHERE t."{col}" = m.old_name
            {extra_where}
        """)
        # Get affected rows
        affected = frappe.db.sql(f"""
            SELECT COUNT(*) FROM "{table}" t
            INNER JOIN _customer_rename_map m ON t."{col}" = m.old_name
            {extra_where.replace('t.', f'"{table}".')}
        """)
        # Actually just count what we updated - the UPDATE already ran
        frappe.db.commit()
        elapsed = time.time() - t0
        print(f"  {table:35s} {col:20s} updated [{elapsed:.1f}s]")
        total_updated += 1
    except Exception as e:
        frappe.db.rollback()
        err_msg = str(e)[:80]
        if "does not exist" in err_msg or "column" in err_msg:
            pass  # Table/column doesn't exist, skip
        else:
            print(f"  {table:35s} {col:20s} ERR: {err_msg}")

print(f"\n  Updated {total_updated} FK tables")


# ═══════════════════════════════════════════════════════════════
# STEP 4: RENAME CUSTOMER RECORDS THEMSELVES
# ═══════════════════════════════════════════════════════════════
print("\n" + "=" * 70)
print("STEP 4: RENAME CUSTOMER RECORDS")
print("=" * 70)
t0 = time.time()

# Must rename customer records — update the `name` column
# Do this AFTER FK updates to avoid FK constraint issues
frappe.db.sql("""
    UPDATE "tabCustomer" t
    SET name = m.new_name
    FROM _customer_rename_map m
    WHERE t.name = m.old_name
""")
frappe.db.commit()
print(f"  Renamed {len(mapping)} customers [{time.time()-t0:.1f}s]")


# ═══════════════════════════════════════════════════════════════
# STEP 5: SET NAMING SERIES COUNTER
# ═══════════════════════════════════════════════════════════════
print("\n" + "=" * 70)
print("STEP 5: SET NAMING SERIES COUNTER")
print("=" * 70)

# For new customers, ERPNext will use naming_series.
# We need to make sure it doesn't collide.
# Since legacy customer_ids are alphanumeric, numeric auto-increment won't collide.
# But let's set a safe starting point anyway.

# Check if CUST- series exists
series = frappe.db.sql("""
    SELECT name, current FROM "tabSeries" WHERE name = 'CUST-'
""", as_dict=True)

if series:
    print(f"  Current CUST- series: {series[0]['current']}")
else:
    print("  No CUST- series found, creating...")
    frappe.db.sql("""
        INSERT INTO "tabSeries" (name, current) VALUES ('CUST-', 100000)
        ON CONFLICT (name) DO UPDATE SET current = GREATEST("tabSeries".current, 100000)
    """)
    frappe.db.commit()
    print("  Set CUST- counter to 100000")

# Note: The naming_series format should produce CUST-NNNNN or CUST-.#####
# New customers will get CUST-100001, CUST-100002, etc.


# ═══════════════════════════════════════════════════════════════
# STEP 6: CLEANUP TEMP TABLE
# ═══════════════════════════════════════════════════════════════
frappe.db.sql('DROP TABLE IF EXISTS _customer_rename_map')
frappe.db.commit()


# ═══════════════════════════════════════════════════════════════
# STEP 7: VERIFY
# ═══════════════════════════════════════════════════════════════
print("\n" + "=" * 70)
print("STEP 7: VERIFICATION")
print("=" * 70)

cust_count = frappe.db.sql('SELECT COUNT(*) FROM "tabCustomer"')[0][0]
print(f"  Customers: {cust_count}")

# Check for any remaining hex-pattern IDs
hex_remaining = frappe.db.sql("""
    SELECT COUNT(*) FROM "tabCustomer"
    WHERE name ~ '^CUST-[0-9a-f]{8,}'
    AND name !~ '^CUST-[0-9]+$'
""")[0][0]
print(f"  Hex-pattern IDs remaining: {hex_remaining} (should be 0)")

# Spot checks
spots = [
    (4, "LPB4"),
    (2393, "Vegpro"),
    (13814, "Vegpro 114796350603272"),
]
for legacy_id, label in spots:
    c = frappe.db.sql("""
        SELECT name, customer_name, legacy_customer_id
        FROM "tabCustomer" WHERE legacy_account_id = %s
    """, (legacy_id,), as_dict=True)
    if c:
        print(f"  {label}: {c[0]['name']} ({c[0]['customer_name']})")

# Check FK consistency: any orphaned references?
for table, col in [("tabSales Invoice", "customer"), ("tabSubscription", "party"), ("tabIssue", "customer")]:
    orphans = frappe.db.sql(f"""
        SELECT COUNT(*) FROM "{table}" t
        WHERE t."{col}" IS NOT NULL
        AND t."{col}" != ''
        AND NOT EXISTS (SELECT 1 FROM "tabCustomer" c WHERE c.name = t."{col}")
    """)[0][0]
    if orphans > 0:
        print(f"  WARNING: {orphans} orphaned references in {table}.{col}!")
    else:
        print(f"  {table}.{col}: all references valid ✓")


# ═══════════════════════════════════════════════════════════════
# DONE
# ═══════════════════════════════════════════════════════════════
print("\n" + "=" * 70)
print("COMPLETE")
print("=" * 70)
print("  Next: bench --site erp.gigafibre.ca clear-cache")

frappe.clear_cache()
print("  Cache cleared")