"""Import historique patching depuis Planning Patching 2026.xlsx.

Regle detection serveur patche : cellule hostname (col A) avec fond VERT.

Pour chaque sheet hebdo S02..S52 :
 - Extrait num semaine depuis le nom de sheet ('S16' -> 16)
 - Col A: hostname
 - Col N (index 13): date (datetime ou string)
 - Col O (index 14) si present: heure
 - Ajoute patch_history(server_id, date_patch, status='ok', notes='Semaine X')

Usage:
    python tools/import_patch_history_xlsx.py <xlsx> [--dry-run] [--sheets S16,S17]
"""
import os
import re
import argparse
from datetime import datetime, time, timedelta
from sqlalchemy import create_engine, text

try:
    import openpyxl
except ImportError:
    print("[ERR] pip install openpyxl")
    raise

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

# Codes couleur Excel "vert" (Office / standard)
GREEN_HEX_PREFIXES = ("00FF", "92D0", "C6EF", "A9D0", "B7E1", "55B5", "70AD",
                      "00B0", "00A0", "008", "00B5", "00C8")


def is_green(cell):
    """Detecte si la cellule a un fond vert."""
    if cell.fill is None or cell.fill.fgColor is None:
        return False
    fc = cell.fill.fgColor
    rgb = None
    if fc.type == "rgb" and fc.rgb:
        rgb = fc.rgb.upper()
    elif fc.type == "theme":
        # Themes Office : 9 = green1, 6 = accent2, etc. (approximatif)
        if fc.theme in (9, 6, 4):
            return True
    if rgb and len(rgb) >= 6:
        # AARRGGBB -> prendre le RRGGBB
        rr = rgb[-6:-4]; gg = rgb[-4:-2]; bb = rgb[-2:]
        try:
            r, g, b = int(rr, 16), int(gg, 16), int(bb, 16)
            # Vert dominant (G > R, G > B, G > 120)
            return g > 120 and g > r + 30 and g > b + 30
        except ValueError:
            return False
    return False


def parse_week_num(sheet_name):
    m = re.search(r"[Ss](\d{1,2})", sheet_name)
    return int(m.group(1)) if m else None


def week_to_date(year, week):
    """Retourne date du lundi de la semaine ISO."""
    d = datetime.strptime(f"{year}-W{week:02d}-1", "%Y-W%W-%w")
    return d.date()


def parse_hour(val):
    """Parse une cellule heure: '14:00', '14h', '14H30', datetime.time..."""
    if val is None:
        return None
    if isinstance(val, time):
        return val
    if isinstance(val, datetime):
        return val.time()
    s = str(val).strip().lower().replace("h", ":")
    m = re.match(r"(\d{1,2})(?::(\d{2}))?", s)
    if not m:
        return None
    hh = int(m.group(1))
    mm = int(m.group(2) or 0)
    if 0 <= hh < 24 and 0 <= mm < 60:
        return time(hh, mm)
    return None


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("xlsx_path")
    parser.add_argument("--year", type=int, default=2026)
    parser.add_argument("--sheets", default="", help="Liste de sheets filtrees, ex: S15,S16")
    parser.add_argument("--dry-run", action="store_true")
    args = parser.parse_args()

    engine = create_engine(DATABASE_URL)
    conn = engine.connect().execution_options(isolation_level="AUTOCOMMIT")

    wb = openpyxl.load_workbook(args.xlsx_path, data_only=True)
    target_sheets = set(s.strip().upper() for s in args.sheets.split(",") if s.strip()) if args.sheets else None

    # Cache servers: hostname -> id
    hosts = {}
    for r in conn.execute(text("SELECT id, hostname FROM servers")).fetchall():
        hosts[r.hostname.lower()] = r.id

    stats = {"sheets": 0, "patched": 0, "inserted": 0, "no_server": 0, "skipped": 0}

    for sname in wb.sheetnames:
        wk = parse_week_num(sname)
        if wk is None or not (1 <= wk <= 53):
            continue
        if target_sheets and sname.upper() not in target_sheets:
            continue
        ws = wb[sname]
        stats["sheets"] += 1
        default_date = week_to_date(args.year, wk)

        for row_idx in range(2, ws.max_row + 1):
            hn_cell = ws.cell(row=row_idx, column=1)
            hn = hn_cell.value
            if not hn:
                continue
            hn = str(hn).strip().split(".")[0].lower()
            if not any(c.isalpha() for c in hn):
                continue
            if not is_green(hn_cell):
                continue
            stats["patched"] += 1

            sid = hosts.get(hn)
            if not sid:
                stats["no_server"] += 1
                continue

            # Date col N (14), heure col O (15) si present
            date_val = ws.cell(row=row_idx, column=14).value
            hour_val = ws.cell(row=row_idx, column=15).value
            dt_base = None
            if isinstance(date_val, datetime):
                dt_base = date_val
            elif date_val:
                try:
                    dt_base = datetime.strptime(str(date_val).split()[0], "%d/%m/%Y")
                except Exception:
                    dt_base = None
            if not dt_base:
                dt_base = datetime.combine(default_date, time(0, 0))
            hr = parse_hour(hour_val)
            if hr:
                dt_base = datetime.combine(dt_base.date(), hr)

            note = f"Semaine {wk:02d}"
            if args.dry_run:
                print(f"  DRY [{sname}] {hn:25s} -> {dt_base.isoformat()} ({note})")
            else:
                try:
                    # Evite doublons exacts (server+date)
                    existing = conn.execute(text(
                        "SELECT id FROM patch_history WHERE server_id=:sid AND date_patch=:dt"
                    ), {"sid": sid, "dt": dt_base}).fetchone()
                    if existing:
                        stats["skipped"] += 1
                        continue
                    conn.execute(text("""
                        INSERT INTO patch_history (server_id, date_patch, status, notes)
                        VALUES (:sid, :dt, 'ok', :note)
                    """), {"sid": sid, "dt": dt_base, "note": note})
                    stats["inserted"] += 1
                except Exception as e:
                    print(f"  [ERR] {hn}: {str(e)[:120]}")
                    stats["skipped"] += 1

    conn.close()
    print(f"\n[DONE] Sheets: {stats['sheets']} | Patches detectes: {stats['patched']} "
          f"| Inserts: {stats['inserted']} | Sans serveur: {stats['no_server']} "
          f"| Skip: {stats['skipped']}")


if __name__ == "__main__":
    main()