#!/usr/bin/env python3
"""Fix mega enrichment: add slug to vendors and 10Gtek transceivers."""
import subprocess, os, uuid, re, time

def esc(v):
    return str(v).replace("'", "''")

def slugify(s):
    return re.sub(r"[^a-z0-9]+", "-", s.lower()).strip("-")

def run_sql(sql):
    r = subprocess.run(
        ["psql", "-h", "localhost", "-p", "5433", "-U", "tip", "-d", "transceiver_db", "-c", sql],
        capture_output=True, text=True,
        env={**os.environ, "PGPASSWORD": "***REDACTED***"}
    )
    if "ERROR" in r.stderr:
        print(f"ERR: {r.stderr.strip()[:200]}")
    return r

def query_val(sql):
    r = subprocess.run(
        ["psql", "-h", "localhost", "-p", "5433", "-U", "tip", "-d", "transceiver_db", "-t", "-A", "-c", sql],
        capture_output=True, text=True,
        env={**os.environ, "PGPASSWORD": "***REDACTED***"}
    )
    return r.stdout.strip()

VENDORS = [
    ("10Gtek", "manufacturer", "https://www.10gtek.com", "CN"),
    ("FS.COM", "manufacturer", "https://www.fs.com", "CN"),
    ("ProLabs", "manufacturer", "https://www.prolabs.com", "GB"),
    ("Champion ONE", "manufacturer", "https://www.championone.com", "US"),
    ("Axiom Memory", "manufacturer", "https://www.axiomupgrades.com", "US"),
    ("Approved Networks", "manufacturer", "https://www.approvednetworks.com", "US"),
    ("AddOn Networks", "manufacturer", "https://www.addonnetworks.com", "US"),
    ("FluxLight", "manufacturer", "https://fluxlight.com", "US"),
    ("NADDOD", "manufacturer", "https://www.naddod.com", "CN"),
    ("Innolight", "manufacturer", "https://www.innolight.com", "CN"),
    ("Eoptolink", "manufacturer", "https://www.eoptolink.com", "CN"),
    ("Hisense Broadband", "manufacturer", "https://www.hisense-broadband.com", "CN"),
    ("Source Photonics", "manufacturer", "https://www.sourcephotonics.com", "US"),
    ("Lumentum", "manufacturer", "https://www.lumentum.com", "US"),
    ("II-VI/Coherent", "manufacturer", "https://www.coherent.com", "US"),
    ("Broadcom/Avago", "manufacturer", "https://www.broadcom.com", "US"),
    ("Intel", "manufacturer", "https://www.intel.com", "US"),
    ("Mellanox", "manufacturer", "https://network.nvidia.com", "IL"),
    ("Finisar", "manufacturer", "https://www.coherent.com", "US"),
    ("Molex", "manufacturer", "https://www.molex.com", "US"),
    ("Oplink", "manufacturer", "https://www.oplink.com", "US"),
    ("MACOM", "manufacturer", "https://www.macom.com", "US"),
    ("Accelink", "manufacturer", "https://www.accelink.com", "CN"),
    ("HG Genuine", "manufacturer", "https://www.hggenuine.com", "CN"),
    ("Gigalight", "manufacturer", "https://www.gigalight.com", "CN"),
    ("QSFPTEK", "manufacturer", "https://www.qsfptek.com", "CN"),
    ("Edgeium", "manufacturer", "https://edgeium.com", "US"),
    ("Precision OT", "manufacturer", "https://www.precisionot.com", "US"),
    ("SintronTech/Optech", "manufacturer", "https://sintrontech.com", "TW"),
    ("Optcore", "manufacturer", "https://www.optcore.net", "CN"),
    ("Hummingbird Networks", "manufacturer", "https://www.hummingbirdnetworks.com", "US"),
]

print(f"Inserting {len(VENDORS)} vendors with slugs...")
ok = 0
for name, vtype, url, country in VENDORS:
    vid = str(uuid.uuid4())
    slug = slugify(name)
    sql = (f"INSERT INTO vendors (id, name, slug, type, website, country) "
           f"VALUES ('{vid}', '{esc(name)}', '{slug}', '{vtype}', '{esc(url)}', '{country}') "
           f"ON CONFLICT (name) DO NOTHING;")
    r = run_sql(sql)
    if "ERROR" not in r.stderr:
        ok += 1
print(f"Vendors inserted: {ok}/{len(VENDORS)}")

# Get 10Gtek vendor ID
v10g = query_val("SELECT id FROM vendors WHERE name='10Gtek';")
print(f"10Gtek vendor ID: {v10g}")

if v10g:
    PRODUCTS = [
        ("ASF-GE-T", "1000BASE-T SFP", 1, None, "100m", "RJ-45", "CAT5e", "COM", 1.0),
        ("ASF85-24-X2-D", "1000BASE-SX SFP", 1, "850nm", "550m", "LC Duplex", "MMF", "COM", 0.8),
        ("ASF13-24-20-D", "1000BASE-LX SFP", 1, "1310nm", "20km", "LC Duplex", "SMF", "COM", 0.8),
        ("ASF13-24-40-D", "1000BASE-LHX SFP", 1, "1310nm", "40km", "LC Duplex", "SMF", "COM", 1.0),
        ("ASF15-24-40-D", "1000BASE-EX SFP", 1, "1550nm", "40km", "LC Duplex", "SMF", "COM", 1.0),
        ("ASF15-24-80-D", "1000BASE-ZX SFP", 1, "1550nm", "80km", "LC Duplex", "SMF", "COM", 1.5),
        ("ASF15-24-100-D", "1000BASE-EZX-100", 1, "1550nm", "100km", "LC Duplex", "SMF", "COM", 2.0),
        ("ASF15-24-120-D", "1000BASE-EZX-120", 1, "1550nm", "120km", "LC Duplex", "SMF", "COM", 2.5),
        ("AMQ28-SR4-M1", "100G QSFP28 SR4", 100, "850nm", "100m", "MPO-12", "MMF OM4", "COM", 2.5),
        ("ALQ28-IR4-02", "100G QSFP28 IR4", 100, "1310nm", "2km", "MPO-12", "SMF", "COM", 3.0),
        ("ALQ28-CW4-02", "100G QSFP28 CWDM4", 100, "CWDM4", "2km", "LC Duplex", "SMF", "COM", 3.0),
        ("ALQ28-LR4-10", "100G QSFP28 LR4", 100, "LWDM4", "10km", "LC Duplex", "SMF", "COM", 3.5),
        ("ALQ28-LR4-20", "100G QSFP28 ELR4+", 100, "LWDM4", "20km", "LC Duplex", "SMF", "COM", 4.0),
        ("ALQ28-ER4-30", "100G QSFP28 ER4", 100, "LWDM4", "30km", "LC Duplex", "SMF", "COM", 4.5),
        ("AZS85-S28-M1", "25G SFP28 SR", 25, "850nm", "100m", "LC Duplex", "MMF OM4", "COM", 1.0),
        ("AZS13-S28-02", "25G SFP28 IR", 25, "1310nm", "2km", "LC Duplex", "SMF", "COM", 1.5),
        ("AZS13-S28-10", "25G SFP28 LR", 25, "1310nm", "10km", "LC Duplex", "SMF", "COM", 1.5),
        ("AZS13-S28-20", "25G SFP28 LR20", 25, "1310nm", "20km", "LC Duplex", "SMF", "COM", 2.0),
        ("AZS13-S28-40", "25G SFP28 ER", 25, "1310nm", "40km", "LC Duplex", "SMF", "COM", 2.0),
    ]
    ok = 0
    for pn, name, speed, wl, reach, conn, fiber, temp, power in PRODUCTS:
        tid = str(uuid.uuid4())
        slug = slugify(f"10gtek-{pn}")
        wl_v = f"'{wl}'" if wl else "NULL"
        sql = (f"INSERT INTO transceivers (id, vendor_id, slug, part_number, standard_name, "
               f"speed_gbps, wavelengths, reach_label, connector, fiber_type, temp_range, "
               f"power_consumption_w, product_page_url) VALUES ("
               f"'{tid}', '{v10g}', '{slug}', '{esc(pn)}', '{esc(name)}', "
               f"{speed}, {wl_v}, '{esc(reach)}', '{esc(conn)}', '{esc(fiber)}', '{temp}', "
               f"{power}, 'https://www.10gtek.com') ON CONFLICT DO NOTHING;")
        r = run_sql(sql)
        if "ERROR" not in r.stderr:
            ok += 1
    print(f"10Gtek transceivers inserted: {ok}/{len(PRODUCTS)}")

# Restart API
subprocess.run(["bash", "-c", "cd /opt/tip && pm2 restart tip-api"], capture_output=True)

# Final counts
for q in [
    "SELECT 'vendors=' || count(*) FROM vendors",
    "SELECT 'transceivers=' || count(*) FROM transceivers",
    "SELECT 'market_data=' || count(*) FROM market_data",
    "SELECT 'form_factors=' || count(*) FROM form_factors",
    "SELECT 'lifecycle=' || count(*) FROM technology_lifecycle",
    "SELECT 'dwdm=' || count(*) FROM dwdm_channels",
    "SELECT 'cwdm=' || count(*) FROM cwdm_channels",
    "SELECT 'compat=' || count(*) FROM compatibility",
]:
    print(query_val(q))

print(f"{time.strftime('%H:%M:%S')}: FIX DONE")
