#!/usr/bin/env python3
"""
ETHICA → admin.send_contacts SYNC · Opus 20avr · Doctrine #13 cause racine
Écrit les HCPs Ethica avec email valide vers admin.send_contacts
Évite doublons via WHERE NOT EXISTS
"""
import psycopg2, time, sys, os

DB = "host=10.1.0.3 dbname=adx_system user=admin password=admin123"
BATCH = int(sys.argv[1]) if len(sys.argv) > 1 else 5000
LOG = "/var/log/weval/ethica-sync-send_contacts.log"

def log(msg):
    line = f"[{time.strftime('%Y-%m-%d %H:%M:%S')}] {msg}"
    print(line)
    os.makedirs(os.path.dirname(LOG), exist_ok=True)
    with open(LOG, "a") as f:
        f.write(line + "\n")

def main():
    conn = psycopg2.connect(DB)
    cur = conn.cursor()
    
    # Count gap
    cur.execute("""
        SELECT COUNT(*) FROM ethica.medecins_real e
        WHERE e.email IS NOT NULL AND e.email != '' AND e.email != 'N/A'
          AND NOT EXISTS (
            SELECT 1 FROM admin.send_contacts sc 
            WHERE sc.email = e.email AND sc.source = 'ethica_hcp'
          )
    """)
    gap = cur.fetchone()[0]
    log(f"GAP: {gap} HCPs Ethica avec email not-in send_contacts")
    
    if gap == 0:
        log("NOTHING TO SYNC")
        conn.close()
        return
    
    # Bulk INSERT via SELECT (atomic, efficient)
    cur.execute("""
        INSERT INTO admin.send_contacts (email, first_name, last_name, country, source, created_at, status)
        SELECT DISTINCT ON (e.email)
          e.email,
          COALESCE(NULLIF(split_part(e.nom, ' ', 1), ''), '') AS first_name,
          COALESCE(NULLIF(substring(e.nom from position(' ' in e.nom)+1), ''), e.nom) AS last_name,
          COALESCE(e.pays, 'MA') AS country,
          'ethica_hcp' AS source,
          NOW(),
          'active'
        FROM ethica.medecins_real e
        WHERE e.email IS NOT NULL AND e.email != '' AND e.email != 'N/A'
          AND NOT EXISTS (
            SELECT 1 FROM admin.send_contacts sc 
            WHERE sc.email = e.email AND sc.source = 'ethica_hcp'
          )
        LIMIT %s
        ON CONFLICT DO NOTHING
    """, (BATCH,))
    
    inserted = cur.rowcount
    conn.commit()
    log(f"INSERTED {inserted} rows from ethica.medecins_real to admin.send_contacts")
    
    # Verify
    cur.execute("SELECT COUNT(*) FROM admin.send_contacts WHERE source = 'ethica_hcp'")
    total = cur.fetchone()[0]
    cur.execute("SELECT MAX(created_at) FROM admin.send_contacts WHERE source = 'ethica_hcp'")
    latest = cur.fetchone()[0]
    log(f"POST-SYNC: total ethica_hcp = {total}, latest = {latest}")
    
    # Doctrine #4 honesty: report dedup count (skipped email already present via other source)
    cur.execute("""
        SELECT COUNT(*) FROM ethica.medecins_real e
        WHERE e.email IS NOT NULL AND e.email != '' AND e.email != 'N/A'
          AND NOT EXISTS (SELECT 1 FROM admin.send_contacts sc WHERE sc.email = e.email AND sc.source = 'ethica_hcp')
          AND EXISTS (SELECT 1 FROM admin.send_contacts sc2 WHERE sc2.email = e.email)
    """)
    dedup = cur.fetchone()[0]
    if dedup > 0:
        log(f"DEDUP: {dedup} HCPs skipped (email already in send_contacts via another source = OK, reachable)")
    
    conn.close()
    log("DONE")

if __name__ == "__main__":
    main()