"""
Import payment methods from legacy: Stripe, Paysafe/Bambora tokens, Bank PPA.
Creates Payment Method records linked to Customer.

Run inside erpnext-backend-1:
  /home/frappe/frappe-bench/env/bin/python /home/frappe/frappe-bench/import_payment_methods.py
"""
import frappe
import pymysql
import 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()
frappe.local.flags.ignore_permissions = True
print("Connected:", frappe.local.site)

legacy = pymysql.connect(
    host="legacy-db", user="facturation", password="VD67owoj",
    database="gestionclient", cursorclass=pymysql.cursors.DictCursor
)

# Build account_id → Customer name mapping
cust_map = {}
for r in frappe.db.sql('SELECT name, legacy_account_id FROM "tabCustomer" WHERE legacy_account_id > 0', as_dict=True):
    cust_map[int(r['legacy_account_id'])] = r['name']
print(f"  {len(cust_map)} customers mapped")

# Clear existing
existing = frappe.db.sql('SELECT COUNT(*) FROM "tabPayment Method"')[0][0]
if existing:
    frappe.db.sql('DELETE FROM "tabPayment Method"')
    frappe.db.commit()
    print(f"  Cleared {existing} existing Payment Methods")

T0 = time.time()
created = 0

# ── 1. Stripe customers ──
print("\n=== Stripe Payment Methods ===")
with legacy.cursor() as cur:
    cur.execute("""
        SELECT id, stripe_id, stripe_ppa, stripe_ppa_nocc
        FROM account
        WHERE stripe_id IS NOT NULL AND stripe_id != '' AND status IN (1, 2)
    """)
    stripe_rows = cur.fetchall()

for r in stripe_rows:
    cust = cust_map.get(r['id'])
    if not cust:
        continue
    doc = frappe.get_doc({
        "doctype": "Payment Method",
        "customer": cust,
        "provider": "Stripe",
        "is_active": 1,
        "is_auto_ppa": 1 if r['stripe_ppa'] else 0,
        "stripe_customer_id": r['stripe_id'],
        "stripe_ppa_enabled": 1 if r['stripe_ppa'] else 0,
        "stripe_ppa_nocc": 1 if r['stripe_ppa_nocc'] else 0,
        "legacy_account_id": r['id'],
    })
    doc.insert(ignore_if_duplicate=True)
    created += 1

frappe.db.commit()
print(f"  {created} Stripe methods created [{time.time()-T0:.0f}s]")

# ── 2. Paysafe/Bambora tokens ──
print("\n=== Paysafe Payment Methods ===")
t0 = time.time()
paysafe_count = 0
with legacy.cursor() as cur:
    cur.execute("SELECT account_id, profile_id, card_id, token, initial_transaction FROM account_profile")
    paysafe_rows = cur.fetchall()

for r in paysafe_rows:
    cust = cust_map.get(r['account_id'])
    if not cust:
        continue
    doc = frappe.get_doc({
        "doctype": "Payment Method",
        "customer": cust,
        "provider": "Paysafe",
        "is_active": 1,
        "is_auto_ppa": 1,
        "paysafe_profile_id": r['profile_id'],
        "paysafe_card_id": r['card_id'],
        "paysafe_token": r['token'],
        "paysafe_initial_txn": r['initial_transaction'],
        "legacy_account_id": r['account_id'],
    })
    doc.insert(ignore_if_duplicate=True)
    paysafe_count += 1

frappe.db.commit()
print(f"  {paysafe_count} Paysafe methods created [{time.time()-t0:.0f}s]")
created += paysafe_count

# ── 3. Bank PPA (no Stripe, no Paysafe) ──
print("\n=== Bank PPA Payment Methods ===")
t0 = time.time()
bank_count = 0

# Get accounts with PPA enabled but no Stripe
with legacy.cursor() as cur:
    cur.execute("""
        SELECT a.id, a.ppa_name, a.ppa_code, a.ppa_branch, a.ppa_account,
               a.ppa_amount, a.ppa_amount_buffer, a.ppa_fixed
        FROM account a
        LEFT JOIN account_profile ap ON ap.account_id = a.id
        WHERE a.ppa = 1 AND a.status IN (1, 2)
          AND (a.stripe_ppa = 0 OR a.stripe_ppa IS NULL)
          AND ap.id IS NULL
    """)
    bank_rows = cur.fetchall()

for r in bank_rows:
    cust = cust_map.get(r['id'])
    if not cust:
        continue
    doc = frappe.get_doc({
        "doctype": "Payment Method",
        "customer": cust,
        "provider": "Bank Draft",
        "is_active": 1,
        "is_auto_ppa": 1,
        "ppa_name": r['ppa_name'] or '',
        "ppa_institution": r['ppa_code'] or '',
        "ppa_branch": r['ppa_branch'] or '',
        "ppa_account": r['ppa_account'] or '',
        "ppa_amount": float(r['ppa_amount'] or 0),
        "ppa_buffer": float(r['ppa_amount_buffer'] or 0),
        "legacy_account_id": r['id'],
    })
    doc.insert(ignore_if_duplicate=True)
    bank_count += 1

frappe.db.commit()
print(f"  {bank_count} Bank PPA methods created [{time.time()-t0:.0f}s]")
created += bank_count

legacy.close()

# ── Verify ──
print(f"\n=== Summary ===")
dist = frappe.db.sql('SELECT provider, COUNT(*) FROM "tabPayment Method" GROUP BY provider ORDER BY COUNT(*) DESC')
for r in dist:
    print(f"  {r[0]:15s} {r[1]}")
print(f"  Total: {created} Payment Methods [{time.time()-T0:.0f}s]")

frappe.destroy()
print("Done!")