#!/usr/bin/env python3
"""Link Subscription → Address via legacy service.delivery_id → delivery → Address."""
import pymysql
import psycopg2

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"}

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

def main():
    log("=== Link Subscriptions → Addresses ===")

    # Legacy: service → delivery_id, delivery → account_id + address
    mc = pymysql.connect(**LEGACY)
    cur = mc.cursor(pymysql.cursors.DictCursor)
    cur.execute("""
        SELECT s.id as service_id, s.delivery_id, d.account_id, d.address1, d.city
        FROM service s
        JOIN delivery d ON s.delivery_id = d.id
        WHERE s.status = 1
    """)
    svc_delivery = {r["service_id"]: r for r in cur.fetchall()}
    mc.close()
    log("  {} active services with delivery".format(len(svc_delivery)))

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

    # Subscription legacy_service_id → name
    pgc.execute('SELECT legacy_service_id, name FROM "tabSubscription" WHERE legacy_service_id > 0')
    sub_map = {r[0]: r[1] for r in pgc.fetchall()}

    # Address lookup: find address by customer + address_line1 match
    # Build: (customer_name, address_line1) → address.name
    pgc.execute("""
        SELECT a.name, a.address_line1, a.city, dl.link_name as customer
        FROM "tabAddress" a
        JOIN "tabDynamic Link" dl ON dl.parent = a.name AND dl.parenttype = 'Address' AND dl.link_doctype = 'Customer'
    """)
    # Group by customer
    addr_by_cust = {}
    for name, addr1, city, cust in pgc.fetchall():
        addr_by_cust.setdefault(cust, []).append((name, addr1 or "", city or ""))

    # Customer legacy_account_id → name
    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()}

    updated = 0
    for svc_id, sub_name in sub_map.items():
        info = svc_delivery.get(svc_id)
        if not info:
            continue

        cust_name = cust_map.get(info["account_id"])
        if not cust_name:
            continue

        # Find best matching address for this customer
        addrs = addr_by_cust.get(cust_name, [])
        if not addrs:
            continue

        # Try exact match on address1
        legacy_addr = (info.get("address1") or "").strip().lower()
        best = addrs[0][0]  # default to first address
        for aname, a1, acity in addrs:
            if legacy_addr and legacy_addr in a1.lower():
                best = aname
                break

        pgc.execute('UPDATE "tabSubscription" SET service_address = %s WHERE name = %s AND (service_address IS NULL OR service_address = %s)',
                     (best, sub_name, ''))
        if pgc.rowcount > 0:
            updated += 1

        if updated % 2000 == 0 and updated > 0:
            pg.commit()
            log("  updated={}".format(updated))

    pg.commit()
    pg.close()
    log("\nSubscriptions linked to Address: {}".format(updated))

if __name__ == "__main__":
    main()