gigafibre-fsm/scripts/migration/migrate_phase5.py

#!/usr/bin/env python3
"""
Phase 5: Opening Balance — Create Journal Entry with customer balances.
Direct PostgreSQL. Detached.
Log: /tmp/migrate_phase5.log
"""
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": 300}
PG = {"host": "db", "port": 5432, "user": "postgres", "password": "123",
      "dbname": "_eb65bdc0c4b1b2d6"}

ADMIN = "Administrator"
COMPANY = "TARGO"
OPENING_DATE = "2026-03-28"  # Date de l'opening balance

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

def now():
    return datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S.%f")

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

def main():
    ts = now()
    log("=== Phase 5: Opening Balance ===")

    # 1. Read legacy balances (excluding credit-type payments)
    log("Reading legacy balances...")
    mc = pymysql.connect(**LEGACY)
    cur = mc.cursor(pymysql.cursors.DictCursor)
    cur.execute("""
        SELECT a.id as account_id,
          ROUND(
            COALESCE((SELECT SUM(total_amt) FROM invoice WHERE account_id = a.id AND billing_status = 1), 0) -
            COALESCE((SELECT SUM(amount) FROM payment WHERE account_id = a.id
                      AND type NOT IN ('credit', 'credit targo', 'credit facture')), 0)
          , 2) as balance
        FROM account a
        WHERE a.status IN (1, 2)
        HAVING balance > 0.50 OR balance < -0.50
        ORDER BY a.id
    """)
    balances = cur.fetchall()
    mc.close()

    total_debit = sum(b["balance"] for b in balances if b["balance"] > 0)
    total_credit = sum(abs(b["balance"]) for b in balances if b["balance"] < 0)
    log("  {} clients with balance".format(len(balances)))
    log("  Total AR (debit):  ${:,.2f}".format(total_debit))
    log("  Total credit:      ${:,.2f}".format(total_credit))

    # 2. Connect ERPNext PG
    log("Connecting to ERPNext PostgreSQL...")
    pg = psycopg2.connect(**PG)
    pgc = pg.cursor()

    # Customer mapping
    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()}

    # Get receivable account
    pgc.execute("""SELECT name FROM "tabAccount" WHERE account_type = 'Receivable'
                   AND company = 'TARGO' AND is_group = 0 LIMIT 1""")
    row = pgc.fetchone()
    if not row:
        log("ERROR: No receivable account found!")
        return
    receivable_acct = row[0]
    log("  Receivable account: {}".format(receivable_acct))

    # Get temporary opening account
    pgc.execute("""SELECT name FROM "tabAccount" WHERE account_name LIKE '%Temporary%Opening%'
                   AND company = 'TARGO' LIMIT 1""")
    row = pgc.fetchone()
    if not row:
        # Use equity opening account
        pgc.execute("""SELECT name FROM "tabAccount"
                       WHERE account_name LIKE '%Opening%' AND company = 'TARGO'
                       AND root_type = 'Equity' LIMIT 1""")
        row = pgc.fetchone()
    if not row:
        log("ERROR: No opening balance account found! Creating one...")
        ob_name = uid("ACCT-")
        pgc.execute("""SELECT name FROM "tabAccount" WHERE account_name = 'Equity'
                       AND company = 'TARGO' AND is_group = 1 LIMIT 1""")
        equity_parent = pgc.fetchone()
        if not equity_parent:
            pgc.execute("""SELECT name FROM "tabAccount" WHERE root_type = 'Equity'
                           AND company = 'TARGO' AND is_group = 1 LIMIT 1""")
            equity_parent = pgc.fetchone()
        parent = equity_parent[0] if equity_parent else "Equity - T"

        pgc.execute("""
            INSERT INTO "tabAccount" (name, creation, modified, modified_by, owner, docstatus, idx,
                account_name, parent_account, root_type, account_type, company, is_group, lft, rgt)
            VALUES (%s, %s, %s, %s, %s, 0, 0,
                'Temporary Opening', %s, 'Equity', 'Temporary', %s, 0, 0, 0)
        """, (ob_name, ts, ts, ADMIN, ADMIN, parent, COMPANY))
        pg.commit()
        opening_acct = ob_name
        log("  Created Temporary Opening account: {}".format(ob_name))
    else:
        opening_acct = row[0]
        log("  Opening account: {}".format(opening_acct))

    # 3. Create Journal Entry
    je_name = uid("JE-OB-")
    log("")
    log("Creating Journal Entry: {}".format(je_name))
    log("  Date: {}".format(OPENING_DATE))

    pgc.execute("""
        INSERT INTO "tabJournal Entry" (
            name, creation, modified, modified_by, owner, docstatus, idx,
            title, voucher_type, naming_series, posting_date, company,
            is_opening, remark, user_remark, total_debit, total_credit,
            multi_currency
        ) VALUES (
            %s, %s, %s, %s, %s, 0, 0,
            'Opening Balance - Legacy Migration', 'Opening Entry', 'ACC-JV-.YYYY.-',
            %s, %s, 'Yes',
            'Opening balance from legacy CRM/Billing migration',
            'Soldes clients migrés du système legacy Facturation PHP/MariaDB',
            %s, %s, 0
        )
    """, (je_name, ts, ts, ADMIN, ADMIN, OPENING_DATE, COMPANY,
          round(total_debit + total_credit, 2), round(total_debit + total_credit, 2)))

    # 4. Create Journal Entry Account lines
    line_idx = 0
    ok = 0
    skip = 0

    for b in balances:
        cust_name = cust_map.get(b["account_id"])
        if not cust_name:
            skip += 1
            continue

        line_idx += 1
        amount = round(abs(b["balance"]), 2)

        if b["balance"] > 0:
            # Customer owes money → debit receivable
            debit = amount
            credit = 0
        else:
            # Customer has credit → credit receivable
            debit = 0
            credit = amount

        pgc.execute("""
            INSERT INTO "tabJournal Entry Account" (
                name, creation, modified, modified_by, owner, docstatus, idx,
                account, account_type, party_type, party,
                debit_in_account_currency, debit, credit_in_account_currency, credit,
                account_currency, cost_center, is_advance,
                parent, parentfield, parenttype
            ) VALUES (
                %s, %s, %s, %s, %s, 0, %s,
                %s, 'Receivable', 'Customer', %s,
                %s, %s, %s, %s,
                'CAD', 'Main - T', 'No',
                %s, 'accounts', 'Journal Entry'
            )
        """, (uid("JEA-"), ts, ts, ADMIN, ADMIN, line_idx,
              receivable_acct, cust_name,
              debit, debit, credit, credit,
              je_name))
        ok += 1

    # Balancing entry → Opening account
    line_idx += 1
    net = round(total_debit - total_credit, 2)
    if net > 0:
        bal_debit = 0
        bal_credit = net
    else:
        bal_debit = abs(net)
        bal_credit = 0

    pgc.execute("""
        INSERT INTO "tabJournal Entry Account" (
            name, creation, modified, modified_by, owner, docstatus, idx,
            account, debit_in_account_currency, debit, credit_in_account_currency, credit,
            account_currency, cost_center, is_advance,
            parent, parentfield, parenttype
        ) VALUES (
            %s, %s, %s, %s, %s, 0, %s,
            %s, %s, %s, %s, %s,
            'CAD', 'Main - T', 'No',
            %s, 'accounts', 'Journal Entry'
        )
    """, (uid("JEA-"), ts, ts, ADMIN, ADMIN, line_idx,
          opening_acct, bal_debit, bal_debit, bal_credit, bal_credit,
          je_name))

    pg.commit()
    pg.close()

    log("")
    log("=" * 60)
    log("Journal Entry: {} (DRAFT — review before submitting)".format(je_name))
    log("  {} customer lines, {} skipped (no matching customer)".format(ok, skip))
    log("  Total debit:  ${:,.2f}".format(total_debit))
    log("  Total credit: ${:,.2f}".format(total_credit))
    log("  Net AR:       ${:,.2f}".format(net))
    log("=" * 60)
    log("")
    log("Next: Review in ERPNext, then Submit when ready.")
    log("      bench --site erp.gigafibre.ca clear-cache")

if __name__ == "__main__":
    main()