"""Lie les serveurs non-prod (r/t/d) a leur equivalent prod (p/i) par convention SANEF.

Regle : v[r|t|d]XXXX matche v[p|i]XXXX avec XXXX identique.
Ex: vrpaians1 <-> vppaians1

Ecrit dans server_correspondance (source='auto').

Usage:
    python tools/link_prod_nonprod.py [--dry-run]
"""
import os
import argparse
from sqlalchemy import create_engine, text

DATABASE_URL = os.getenv("DATABASE_URL_DEMO") or os.getenv("DATABASE_URL") \
    or "postgresql://patchcenter:PatchCenter2026!@localhost:5432/patchcenter_demo"

NONPROD_PREFIXES = {"r": "recette", "t": "test", "d": "dev"}
PROD_PREFIXES = ["p", "i"]  # p=prod, i=integration (prod chez SANEF)


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--dry-run", action="store_true")
    args = parser.parse_args()

    engine = create_engine(DATABASE_URL)
    print(f"[INFO] DB: {DATABASE_URL.split('@')[-1]}")
    conn = engine.connect().execution_options(isolation_level="AUTOCOMMIT")

    # Tous les serveurs non-prod (2eme lettre r/t/d)
    rows = conn.execute(text("""
        SELECT id, hostname FROM servers
        WHERE LENGTH(hostname) >= 3
          AND LOWER(SUBSTRING(hostname, 1, 1)) = 'v'
          AND LOWER(SUBSTRING(hostname, 2, 1)) IN ('r','t','d')
        ORDER BY hostname
    """)).fetchall()
    print(f"[INFO] {len(rows)} serveurs non-prod (v[rtd]*)")

    # Cache hostname -> id pour lookup rapide
    all_servers = {}
    for r in conn.execute(text("SELECT id, LOWER(hostname) as h FROM servers")).fetchall():
        all_servers[r.h] = r.id

    stats = {"linked": 0, "already_linked": 0, "no_prod_match": 0}
    no_match = []

    for r in rows:
        h = r.hostname.lower()
        env_char = h[1]  # r/t/d
        rest = h[2:]     # reste apres v + r/t/d

        prod_id = None
        prod_hostname = None
        for pchar in PROD_PREFIXES:
            candidate = f"v{pchar}{rest}"
            if candidate in all_servers:
                prod_id = all_servers[candidate]
                prod_hostname = candidate
                break

        if not prod_id:
            no_match.append(r.hostname)
            stats["no_prod_match"] += 1
            continue

        # Existe deja ?
        existing = conn.execute(text(
            "SELECT id FROM server_correspondance WHERE prod_server_id=:p AND nonprod_server_id=:n"
        ), {"p": prod_id, "n": r.id}).fetchone()
        if existing:
            stats["already_linked"] += 1
            continue

        env_name = NONPROD_PREFIXES.get(env_char, env_char)
        if args.dry_run:
            print(f"  DRY: {r.hostname} ({env_name}) <-> {prod_hostname} (prod)")
        else:
            conn.execute(text("""
                INSERT INTO server_correspondance
                (prod_server_id, nonprod_server_id, environment_code, source, note)
                VALUES (:p, :n, :env, 'auto', 'Auto-link convention v[rtd]XXX -> v[pi]XXX')
            """), {"p": prod_id, "n": r.id, "env": env_name})
        stats["linked"] += 1

    conn.close()
    print(f"\n[DONE] Liens: {stats['linked']} | Deja lies: {stats['already_linked']} "
          f"| Sans match prod: {stats['no_prod_match']}")
    if no_match and len(no_match) < 50:
        print(f"\n[INFO] Non-prod sans equivalent prod ({len(no_match)}):")
        for h in no_match[:30]:
            print(f"  {h}")


if __name__ == "__main__":
    main()