"""Service campagnes — logique metier patching"""
from datetime import datetime, date, timedelta
from sqlalchemy import text


def list_campaigns(db, year=None, status=None):
    where = ["1=1"]
    params = {}
    if year:
        where.append("c.year = :year"); params["year"] = year
    if status:
        where.append("c.status = :status"); params["status"] = status
    wc = " AND ".join(where)
    return db.execute(text(f"""
        SELECT c.*, u.display_name as created_by_name,
            (SELECT COUNT(*) FROM patch_sessions ps WHERE ps.campaign_id = c.id) as session_count,
            (SELECT COUNT(*) FROM patch_sessions ps WHERE ps.campaign_id = c.id AND ps.status = 'patched') as patched_count,
            (SELECT COUNT(*) FROM patch_sessions ps WHERE ps.campaign_id = c.id AND ps.status = 'failed') as failed_count,
            (SELECT COUNT(*) FROM patch_sessions ps WHERE ps.campaign_id = c.id AND ps.status = 'pending') as pending_count,
            (SELECT COUNT(*) FROM patch_sessions ps WHERE ps.campaign_id = c.id AND ps.status = 'excluded') as excluded_count
        FROM campaigns c
        LEFT JOIN users u ON c.created_by = u.id
        WHERE {wc} ORDER BY c.year DESC, c.week_code DESC
    """), params).fetchall()


def get_campaign(db, campaign_id):
    return db.execute(text("""
        SELECT c.*, u.display_name as created_by_name
        FROM campaigns c LEFT JOIN users u ON c.created_by = u.id
        WHERE c.id = :id
    """), {"id": campaign_id}).fetchone()


def get_campaign_sessions(db, campaign_id):
    return db.execute(text("""
        SELECT ps.*, s.hostname, s.fqdn, s.os_family, s.os_version, s.tier,
            s.etat, s.ssh_method, s.licence_support, s.machine_type,
            s.pref_patch_jour, s.pref_patch_heure,
            d.name as domaine, e.name as environnement, z.name as zone_name,
            u.display_name as intervenant_name
        FROM patch_sessions ps
        JOIN servers s ON ps.server_id = s.id
        LEFT JOIN domain_environments de ON s.domain_env_id = de.id
        LEFT JOIN domains d ON de.domain_id = d.id
        LEFT JOIN environments e ON de.environment_id = e.id
        LEFT JOIN zones z ON s.zone_id = z.id
        LEFT JOIN users u ON ps.intervenant_id = u.id
        WHERE ps.campaign_id = :cid
        ORDER BY CASE ps.status
            WHEN 'in_progress' THEN 1 WHEN 'pending' THEN 2 WHEN 'prereq_ok' THEN 3
            WHEN 'patched' THEN 4 WHEN 'failed' THEN 5 WHEN 'reported' THEN 6
            WHEN 'excluded' THEN 7 WHEN 'cancelled' THEN 8 ELSE 9 END,
            d.name,
            CASE WHEN e.name != 'Production' THEN 0 ELSE 1 END,
            s.hostname
    """), {"cid": campaign_id}).fetchall()


def get_campaign_stats(db, campaign_id):
    return db.execute(text("""
        SELECT
            COUNT(*) as total,
            COUNT(*) FILTER (WHERE status = 'patched') as patched,
            COUNT(*) FILTER (WHERE status = 'failed') as failed,
            COUNT(*) FILTER (WHERE status = 'pending') as pending,
            COUNT(*) FILTER (WHERE status = 'in_progress') as in_progress,
            COUNT(*) FILTER (WHERE status = 'skipped') as skipped,
            COUNT(*) FILTER (WHERE status = 'excluded') as excluded,
            COUNT(*) FILTER (WHERE status = 'reported') as reported,
            COUNT(*) FILTER (WHERE status = 'cancelled') as cancelled,
            COUNT(*) FILTER (WHERE intervenant_id IS NOT NULL AND status NOT IN ('excluded','cancelled')) as assigned,
            COUNT(*) FILTER (WHERE intervenant_id IS NULL AND status NOT IN ('excluded','cancelled')) as unassigned,
            COUNT(*) FILTER (WHERE ps.server_id IN (SELECT s2.id FROM servers s2 JOIN zones z2 ON s2.zone_id = z2.id WHERE z2.name = 'DMZ') AND status NOT IN ('excluded','cancelled')) as dmz
        FROM patch_sessions ps WHERE ps.campaign_id = :cid
    """), {"cid": campaign_id}).fetchone()


def get_planning_for_week(db, year, week_number):
    return db.execute(text("""
        SELECT pp.*, d.name as domain_name
        FROM patch_planning pp
        LEFT JOIN domains d ON pp.domain_code = d.code
        WHERE pp.year = :y AND pp.week_number = :wn AND pp.status = 'open'
        ORDER BY pp.domain_code
    """), {"y": year, "wn": week_number}).fetchall()


def _week_dates(year, week_number):
    """Retourne lun, mar, mer, jeu de la semaine ISO"""
    jan4 = date(year, 1, 4)
    start_of_w1 = jan4 - timedelta(days=jan4.isoweekday() - 1)
    monday = start_of_w1 + timedelta(weeks=week_number - 1)
    return monday, monday + timedelta(1), monday + timedelta(2), monday + timedelta(3)


def get_servers_for_planning(db, year, week_number):
    planning = get_planning_for_week(db, year, week_number)
    if not planning:
        return [], []

    domain_envs = []
    for p in planning:
        if p.domain_code == 'DMZ':
            continue
        if p.env_scope == 'prod':
            domain_envs.append(("d.code = :dc_{0} AND e.name = 'Production'".format(len(domain_envs)), p.domain_code))
        elif p.env_scope == 'hprod':
            domain_envs.append(("d.code = :dc_{0} AND e.name != 'Production'".format(len(domain_envs)), p.domain_code))
        elif p.env_scope == 'prod_pilot':
            domain_envs.append(("d.code = :dc_{0}".format(len(domain_envs)), p.domain_code))
        else:
            domain_envs.append(("d.code = :dc_{0}".format(len(domain_envs)), p.domain_code))

    if not domain_envs:
        return [], planning

    or_clauses = []
    params = {}
    for i, (clause, dc) in enumerate(domain_envs):
        or_clauses.append(clause)
        params[f"dc_{i}"] = dc
    # DMZ = par zone, pas par domaine
    or_clauses.append("z.name = 'DMZ'")

    where = f"""
        s.etat = 'Production' AND s.patch_os_owner = 'secops'
        AND s.licence_support IN ('active', 'els') AND s.os_family = 'linux'
        AND ({' OR '.join(or_clauses)})
    """

    servers = db.execute(text(f"""
        SELECT s.id, s.hostname, s.fqdn, s.os_family, s.os_version, s.tier,
            s.licence_support, s.ssh_method, s.machine_type,
            s.pref_patch_jour, s.pref_patch_heure, s.default_intervenant_id, s.app_group,
            d.name as domaine, d.code as domain_code, e.name as environnement
        FROM servers s
        LEFT JOIN domain_environments de ON s.domain_env_id = de.id
        LEFT JOIN domains d ON de.domain_id = d.id
        LEFT JOIN environments e ON de.environment_id = e.id
        LEFT JOIN zones z ON s.zone_id = z.id
        WHERE {where}
        ORDER BY e.name, d.name, s.hostname
    """), params).fetchall()

    return servers, planning


def _generate_slots():
    """Genere les creneaux horaires de la journee: 09h00-12h30 + 14h00-16h45 par pas de 15min"""
    slots = []
    # Matin: 09h00 a 12h15 (dernier debut = 12h15, fin 12h30)
    h, m = 9, 0
    while h < 12 or (h == 12 and m <= 15):
        slots.append(f"{h:02d}h{m:02d}")
        m += 15
        if m >= 60:
            m = 0; h += 1
    # Apres-midi: 14h00 a 16h30 (dernier debut = 16h30, fin 16h45)
    h, m = 14, 0
    while h < 16 or (h == 16 and m <= 30):
        slots.append(f"{h:02d}h{m:02d}")
        m += 15
        if m >= 60:
            m = 0; h += 1
    return slots


DAILY_SLOTS = _generate_slots()  # 27 slots par jour


def create_campaign_from_planning(db, year, week_number, label, user_id, excluded_ids=None):
    servers, planning = get_servers_for_planning(db, year, week_number)
    if not servers:
        return None

    wc = f"S{week_number:02d}"
    lun, mar, mer, jeu = _week_dates(year, week_number)

    row = db.execute(text("""
        INSERT INTO campaigns (week_code, year, label, status, date_start, date_end, created_by)
        VALUES (:wc, :y, :label, 'draft', :ds, :de, :uid)
        RETURNING id
    """), {"wc": wc, "y": year, "label": label, "ds": lun, "de": jeu, "uid": user_id}).fetchone()
    cid = row.id
    excluded = set(excluded_ids or [])

    # Separer hors-prod et prod
    hprod_servers = [s for s in servers if s.environnement != 'Production' and s.id not in excluded]
    prod_servers = [s for s in servers if s.environnement == 'Production' and s.id not in excluded]
    excluded_servers = [s for s in servers if s.id in excluded]

    # Trier par app_group pour grouper les serveurs du meme applicatif
    hprod_servers.sort(key=lambda s: (s.app_group or '', s.hostname))
    prod_servers.sort(key=lambda s: (s.app_group or '', s.hostname))

    # Attribuer les creneaux
    # Hors-prod: lundi + mardi
    hprod_jours = [lun, mar]
    slot_idx = 0
    jour_idx = 0
    for s in hprod_servers:
        jour = hprod_jours[jour_idx]
        heure = DAILY_SLOTS[slot_idx]

        # Preference serveur
        if s.pref_patch_jour and s.pref_patch_jour != 'indifferent':
            jour_map = {"lundi": lun, "mardi": mar}
            jour = jour_map.get(s.pref_patch_jour, jour)
        if s.pref_patch_heure and s.pref_patch_heure != 'indifferent':
            heure = s.pref_patch_heure

        default_op = s.default_intervenant_id if hasattr(s, 'default_intervenant_id') else None
        forced = True if default_op else False

        db.execute(text("""
            INSERT INTO patch_sessions (campaign_id, server_id, status, date_prevue, heure_prevue,
                intervenant_id, forced_assignment, assigned_at)
            VALUES (:cid, :sid, 'pending', :dp, :hp, :oid, :forced, CASE WHEN :oid IS NOT NULL THEN now() END)
            ON CONFLICT (campaign_id, server_id) DO NOTHING
        """), {"cid": cid, "sid": s.id, "dp": jour, "hp": heure,
               "oid": default_op, "forced": forced})

        slot_idx += 1
        if slot_idx >= len(DAILY_SLOTS):
            slot_idx = 0
            jour_idx = min(jour_idx + 1, len(hprod_jours) - 1)

    # Prod: mercredi + jeudi
    prod_jours = [mer, jeu]
    slot_idx = 0
    jour_idx = 0
    for s in prod_servers:
        jour = prod_jours[jour_idx]
        heure = DAILY_SLOTS[slot_idx]

        if s.pref_patch_jour and s.pref_patch_jour != 'indifferent':
            jour_map = {"mercredi": mer, "jeudi": jeu}
            jour = jour_map.get(s.pref_patch_jour, jour)
        if s.pref_patch_heure and s.pref_patch_heure != 'indifferent':
            heure = s.pref_patch_heure

        default_op = s.default_intervenant_id if hasattr(s, 'default_intervenant_id') else None
        forced = True if default_op else False

        db.execute(text("""
            INSERT INTO patch_sessions (campaign_id, server_id, status, date_prevue, heure_prevue,
                intervenant_id, forced_assignment, assigned_at)
            VALUES (:cid, :sid, 'pending', :dp, :hp, :oid, :forced, CASE WHEN :oid IS NOT NULL THEN now() END)
            ON CONFLICT (campaign_id, server_id) DO NOTHING
        """), {"cid": cid, "sid": s.id, "dp": jour, "hp": heure,
               "oid": default_op, "forced": forced})

        slot_idx += 1
        if slot_idx >= len(DAILY_SLOTS):
            slot_idx = 0
            jour_idx = min(jour_idx + 1, len(prod_jours) - 1)

    # Exclus
    for s in excluded_servers:
        db.execute(text("""
            INSERT INTO patch_sessions (campaign_id, server_id, status)
            VALUES (:cid, :sid, 'excluded')
            ON CONFLICT (campaign_id, server_id) DO NOTHING
        """), {"cid": cid, "sid": s.id})

    # Appliquer les regles d'assignation par defaut puis propager par app_group
    _apply_default_assignments(db, cid)

    count = db.execute(text(
        "SELECT COUNT(*) FROM patch_sessions WHERE campaign_id = :cid AND status != 'excluded'"
    ), {"cid": cid}).scalar()
    db.execute(text("UPDATE campaigns SET total_servers = :c WHERE id = :cid"),
               {"c": count, "cid": cid})

    db.commit()
    return cid


def _apply_default_assignments(db, campaign_id):
    """Applique les regles d'assignation par defaut (table default_assignments).
    Priorite: server > app_type > app_group > domain > zone"""
    rules = db.execute(text("""
        SELECT da.rule_type, da.rule_value, da.user_id
        FROM default_assignments da
        JOIN users u ON da.user_id = u.id AND u.is_active = true
        ORDER BY da.priority ASC, da.rule_type
    """)).fetchall()

    for rule in rules:
        if rule.rule_type == 'server':
            db.execute(text("""
                UPDATE patch_sessions ps SET intervenant_id = :uid, forced_assignment = true, assigned_at = now()
                FROM servers s
                WHERE ps.server_id = s.id AND ps.campaign_id = :cid
                    AND LOWER(s.hostname) = LOWER(:val)
                    AND ps.intervenant_id IS NULL AND ps.status = 'pending'
            """), {"cid": campaign_id, "uid": rule.user_id, "val": rule.rule_value})

        elif rule.rule_type == 'app_type':
            db.execute(text("""
                UPDATE patch_sessions ps SET intervenant_id = :uid, forced_assignment = true, assigned_at = now()
                FROM servers s
                LEFT JOIN server_specifics ss ON ss.server_id = s.id
                WHERE ps.server_id = s.id AND ps.campaign_id = :cid
                    AND UPPER(ss.app_type) = UPPER(:val)
                    AND ps.intervenant_id IS NULL AND ps.status = 'pending'
            """), {"cid": campaign_id, "uid": rule.user_id, "val": rule.rule_value})

        elif rule.rule_type == 'app_group':
            db.execute(text("""
                UPDATE patch_sessions ps SET intervenant_id = :uid, forced_assignment = true, assigned_at = now()
                FROM servers s
                WHERE ps.server_id = s.id AND ps.campaign_id = :cid
                    AND s.app_group = :val
                    AND ps.intervenant_id IS NULL AND ps.status = 'pending'
            """), {"cid": campaign_id, "uid": rule.user_id, "val": rule.rule_value})

        elif rule.rule_type == 'domain':
            db.execute(text("""
                UPDATE patch_sessions ps SET intervenant_id = :uid, forced_assignment = true, assigned_at = now()
                FROM servers s
                LEFT JOIN domain_environments de ON s.domain_env_id = de.id
                LEFT JOIN domains d ON de.domain_id = d.id
                WHERE ps.server_id = s.id AND ps.campaign_id = :cid
                    AND d.code = :val
                    AND ps.intervenant_id IS NULL AND ps.status = 'pending'
            """), {"cid": campaign_id, "uid": rule.user_id, "val": rule.rule_value})

        elif rule.rule_type == 'zone':
            db.execute(text("""
                UPDATE patch_sessions ps SET intervenant_id = :uid, forced_assignment = true, assigned_at = now()
                FROM servers s
                LEFT JOIN zones z ON s.zone_id = z.id
                WHERE ps.server_id = s.id AND ps.campaign_id = :cid
                    AND z.name = :val
                    AND ps.intervenant_id IS NULL AND ps.status = 'pending'
            """), {"cid": campaign_id, "uid": rule.user_id, "val": rule.rule_value})

    # Ensuite propager par app_group (meme operateur pour recette+prod)
    _auto_link_app_groups(db, campaign_id)


def _auto_link_app_groups(db, campaign_id):
    """Propage les intervenants entre recette et prod du meme app_group"""
    assigned = db.execute(text("""
        SELECT ps.intervenant_id, s.app_group
        FROM patch_sessions ps
        JOIN servers s ON ps.server_id = s.id
        WHERE ps.campaign_id = :cid AND ps.intervenant_id IS NOT NULL AND s.app_group IS NOT NULL
        GROUP BY ps.intervenant_id, s.app_group
    """), {"cid": campaign_id}).fetchall()

    for a in assigned:
        db.execute(text("""
            UPDATE patch_sessions ps SET intervenant_id = :oid, assigned_at = now()
            FROM servers s
            WHERE ps.server_id = s.id AND ps.campaign_id = :cid
                AND s.app_group = :ag AND ps.intervenant_id IS NULL AND ps.status = 'pending'
        """), {"cid": campaign_id, "oid": a.intervenant_id, "ag": a.app_group})


def exclude_session(db, session_id, reason, detail, username):
    db.execute(text("""
        UPDATE patch_sessions SET
            status = 'excluded', exclusion_reason = :reason,
            exclusion_detail = :detail, excluded_by = :by, excluded_at = now()
        WHERE id = :id
    """), {"id": session_id, "reason": reason, "detail": detail, "by": username})
    _recalc_total(db, session_id)
    db.commit()


def restore_session(db, session_id):
    db.execute(text("""
        UPDATE patch_sessions SET
            status = 'pending', exclusion_reason = NULL,
            exclusion_detail = NULL, excluded_by = NULL, excluded_at = NULL
        WHERE id = :id
    """), {"id": session_id})
    _recalc_total(db, session_id)
    db.commit()


def _recalc_total(db, session_id):
    row = db.execute(text("SELECT campaign_id FROM patch_sessions WHERE id = :id"),
                     {"id": session_id}).fetchone()
    if row:
        count = db.execute(text(
            "SELECT COUNT(*) FROM patch_sessions WHERE campaign_id = :cid AND status NOT IN ('excluded','cancelled')"
        ), {"cid": row.campaign_id}).scalar()
        db.execute(text("UPDATE campaigns SET total_servers = :c WHERE id = :cid"),
                   {"c": count, "cid": row.campaign_id})


def assign_operator(db, session_id, operator_id, forced=False):
    """Assigne un operateur a un serveur + auto-assigne le meme groupe applicatif"""
    db.execute(text("""
        UPDATE patch_sessions SET intervenant_id = :oid, assigned_at = now(),
            forced_assignment = :forced
        WHERE id = :id
    """), {"id": session_id, "oid": operator_id, "forced": forced})

    # Auto-assigner les serveurs du meme app_group dans cette campagne
    row = db.execute(text("""
        SELECT ps.campaign_id, s.app_group FROM patch_sessions ps
        JOIN servers s ON ps.server_id = s.id WHERE ps.id = :id
    """), {"id": session_id}).fetchone()
    if row and row.app_group:
        db.execute(text("""
            UPDATE patch_sessions ps SET intervenant_id = :oid, assigned_at = now()
            FROM servers s
            WHERE ps.server_id = s.id AND ps.campaign_id = :cid
                AND s.app_group = :ag AND ps.intervenant_id IS NULL
                AND ps.status = 'pending'
        """), {"cid": row.campaign_id, "oid": operator_id, "ag": row.app_group})

    db.commit()


def unassign_operator(db, session_id):
    """Desassigne un operateur"""
    db.execute(text("""
        UPDATE patch_sessions SET intervenant_id = NULL, assigned_at = NULL, forced_assignment = false
        WHERE id = :id
    """), {"id": session_id})
    db.commit()


def is_forced(db, session_id):
    """Verifie si l'assignation est forcee"""
    row = db.execute(text("SELECT forced_assignment FROM patch_sessions WHERE id = :id"),
                     {"id": session_id}).fetchone()
    return row.forced_assignment if row else False


def get_operator_count(db, campaign_id, operator_id):
    """Nombre de serveurs pris par un operateur dans cette campagne"""
    return db.execute(text("""
        SELECT COUNT(*) FROM patch_sessions
        WHERE campaign_id = :cid AND intervenant_id = :oid AND status NOT IN ('excluded','cancelled')
    """), {"cid": campaign_id, "oid": operator_id}).scalar()


def get_operator_limit(db, campaign_id, operator_id):
    """Limite pour un operateur dans cette campagne (0=illimite)"""
    row = db.execute(text("""
        SELECT max_servers FROM campaign_operator_limits
        WHERE campaign_id = :cid AND user_id = :uid
    """), {"cid": campaign_id, "uid": operator_id}).fetchone()
    return row.max_servers if row else 0


def set_operator_limit(db, campaign_id, operator_id, max_servers, note=None):
    """Definit la limite pour un operateur dans cette campagne"""
    db.execute(text("""
        INSERT INTO campaign_operator_limits (campaign_id, user_id, max_servers, note)
        VALUES (:cid, :uid, :max, :note)
        ON CONFLICT (campaign_id, user_id) DO UPDATE SET max_servers = EXCLUDED.max_servers, note = EXCLUDED.note
    """), {"cid": campaign_id, "uid": operator_id, "max": max_servers, "note": note})
    db.commit()


def get_campaign_operator_limits(db, campaign_id):
    """Retourne les limites de tous les operateurs pour une campagne"""
    return db.execute(text("""
        SELECT col.*, u.display_name
        FROM campaign_operator_limits col
        JOIN users u ON col.user_id = u.id
        WHERE col.campaign_id = :cid ORDER BY u.display_name
    """), {"cid": campaign_id}).fetchall()


def update_session_schedule(db, session_id, date_prevue, heure_prevue):
    """Coordinateur ajuste la date/heure d'un serveur"""
    db.execute(text("""
        UPDATE patch_sessions SET date_prevue = :dp, heure_prevue = :hp
        WHERE id = :id
    """), {"id": session_id, "dp": date_prevue or None, "hp": heure_prevue or None})
    db.commit()


def validate_prereq(db, session_id, ssh, satellite, rollback, rollback_justif, username):
    db.execute(text("""
        UPDATE patch_sessions SET
            prereq_ssh = :ssh, prereq_satellite = :sat,
            rollback_method = :rb, rollback_justif = :rbj,
            prereq_validated = CASE WHEN :ssh = 'ok' AND :sat = 'ok' AND :rb IS NOT NULL THEN true ELSE false END,
            prereq_validated_by = :by, prereq_validated_at = now(), prereq_date = now()
        WHERE id = :id
    """), {"id": session_id, "ssh": ssh, "sat": satellite,
           "rb": rollback or None, "rbj": rollback_justif or None, "by": username})
    db.commit()


def get_prereq_stats(db, campaign_id):
    return db.execute(text("""
        SELECT
            COUNT(*) FILTER (WHERE status = 'pending') as total_pending,
            COUNT(*) FILTER (WHERE status = 'pending' AND prereq_validated = true) as prereq_ok,
            COUNT(*) FILTER (WHERE status = 'pending' AND prereq_validated = false AND prereq_date IS NOT NULL) as prereq_ko,
            COUNT(*) FILTER (WHERE status = 'pending' AND prereq_date IS NULL) as prereq_todo,
            COUNT(*) FILTER (WHERE status = 'pending' AND prereq_ssh = 'ok') as ssh_ok,
            COUNT(*) FILTER (WHERE status = 'pending' AND prereq_satellite = 'ok') as sat_ok,
            COUNT(*) FILTER (WHERE status = 'pending' AND rollback_method IS NOT NULL) as rollback_ok,
            COUNT(*) FILTER (WHERE status = 'pending' AND prereq_disk_ok = true) as disk_ok
        FROM patch_sessions WHERE campaign_id = :cid
    """), {"cid": campaign_id}).fetchone()


def can_plan_campaign(db, campaign_id):
    pending_not_validated = db.execute(text("""
        SELECT COUNT(*) FROM patch_sessions
        WHERE campaign_id = :cid AND status = 'pending' AND prereq_validated = false
    """), {"cid": campaign_id}).scalar()
    return pending_not_validated == 0


def update_campaign_status(db, campaign_id, new_status):
    db.execute(text("UPDATE campaigns SET status = :s WHERE id = :id"),
               {"s": new_status, "id": campaign_id})
    db.commit()