"""Tag servers avec OS en fin de support -> licence_support='obsolete'.

Regles EOL (end of life) appliquees :
 - RHEL 5, 6, 7          (7.9 EOL 2024-06-30)
 - CentOS 5, 6, 7, 8
 - Oracle Linux 5, 6, 7
 - Windows Server 2003, 2008, 2012, 2012 R2
 - Debian 8, 9, 10       (Debian 10 EOL 2024-06-30)
 - Ubuntu 12.04, 14.04, 16.04, 18.04
 - SUSE < 12

N'affecte que licence_support (pas etat). Le patching existant exclut deja
les serveurs avec licence_support='obsolete'.

Usage:
    python tools/tag_obsolete_os.py [--dry-run] [--overwrite]
"""
import os
import re
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"

# OS deja en EOL complet (licence_support='obsolete')
EOL_PATTERNS = [
    (r"red\s*hat.*(?:release\s+)?[56]\b", "RHEL 5/6"),
    (r"red\s*hat.*(?:release\s+)?7\b", "RHEL 7 (EOL 2024-06)"),
    (r"centos.*(?:release\s+)?[5678]\b", "CentOS 5/6/7/8"),
    (r"oracle.*linux.*(?:release\s+)?[567]\b", "Oracle Linux 5/6/7"),
    (r"windows.*2003", "Windows 2003"),
    (r"windows.*2008", "Windows 2008"),
    (r"windows.*2012", "Windows 2012"),
    (r"debian.*(?:gnu/linux\s+)?[89]\b", "Debian 8/9"),
    (r"debian.*(?:gnu/linux\s+)?10\b", "Debian 10"),
    (r"ubuntu.*1[2468]\.04", "Ubuntu 12.04/14.04/16.04/18.04"),
    (r"suse.*(?:enterprise.*)?(?:server.*)?(?:sp\d\s*)?1[01]\b", "SUSE 10/11"),
]

# OS avec support etendu (ELS) - licence_support='els'
ELS_PATTERNS = [
    # RHEL 7 est en ELS jusqu'en 2028 pour les clients payants
    # On peut basculer certains vers ELS au lieu de obsolete au cas par cas
]


def classify(os_version):
    if not os_version:
        return (None, None)
    v = os_version.lower()
    for pattern, label in ELS_PATTERNS:
        if re.search(pattern, v):
            return ("els", label)
    for pattern, label in EOL_PATTERNS:
        if re.search(pattern, v):
            return ("obsolete", label)
    return (None, None)


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--dry-run", action="store_true")
    parser.add_argument("--overwrite", action="store_true",
                        help="Remplace licence_support='active' par 'obsolete' (sinon skip les active)")
    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")

    # Etend le CHECK constraint pour accepter 'obsolete' (le reste du code l'utilise)
    print("[INFO] Etend CHECK licence_support (active/obsolete/els/missing)...")
    conn.execute(text("ALTER TABLE servers DROP CONSTRAINT IF EXISTS servers_licence_support_check"))
    conn.execute(text(
        "ALTER TABLE servers ADD CONSTRAINT servers_licence_support_check "
        "CHECK (licence_support IN ('active', 'obsolete', 'els', 'missing') OR licence_support IS NULL)"
    ))
    # Normalise les anciennes valeurs 'eol' -> 'obsolete' (code/filtre utilise 'obsolete')
    n_eol = conn.execute(text(
        "UPDATE servers SET licence_support='obsolete' WHERE licence_support='eol'"
    )).rowcount
    if n_eol:
        print(f"[INFO] Normalise {n_eol} 'eol' -> 'obsolete'")

    rows = conn.execute(text("""
        SELECT id, hostname, os_version, licence_support
        FROM servers
        WHERE os_version IS NOT NULL AND os_version != ''
    """)).fetchall()
    print(f"[INFO] {len(rows)} serveurs avec os_version")

    stats = {"tagged": 0, "already_obsolete": 0, "not_eol": 0, "skipped_active": 0}
    by_label = {}

    for r in rows:
        target, label = classify(r.os_version)
        if not target:
            stats["not_eol"] += 1
            continue
        if r.licence_support == target:
            stats["already_obsolete"] += 1
            continue
        # Si deja 'active' et qu'on veut passer en obsolete/els, skip sauf --overwrite
        if r.licence_support == "active" and not args.overwrite:
            stats["skipped_active"] += 1
            if args.dry_run:
                print(f"  SKIP (--overwrite pour forcer): {r.hostname:25s} "
                      f"[active] -> {target} ({label}) | {r.os_version[:60]}")
            continue

        by_label[f"{target} - {label}"] = by_label.get(f"{target} - {label}", 0) + 1
        if args.dry_run:
            print(f"  DRY: {r.hostname:25s} [{r.licence_support or 'NULL'}] -> {target} "
                  f"({label}) | {r.os_version[:60]}")
        else:
            conn.execute(text("UPDATE servers SET licence_support=:t WHERE id=:sid"),
                         {"t": target, "sid": r.id})
        stats["tagged"] += 1

    conn.close()
    print(f"\n[DONE] Tagges obsolete: {stats['tagged']} | "
          f"Deja obsolete: {stats['already_obsolete']} | "
          f"Non-EOL: {stats['not_eol']} | "
          f"Skip (active/els): {stats['skipped_active']}")
    if by_label:
        print("\nRepartition par famille EOL :")
        for label, n in sorted(by_label.items(), key=lambda x: -x[1]):
            print(f"  {label:40s} {n}")


if __name__ == "__main__":
    main()
