from __future__ import annotations

import logging


def fetch_sites_mapping(conn):
    sql = """
        SELECT
            id,
            nom,
            nom_poligono,
            etat_administratif
        FROM analyses_sites
        WHERE nom_poligono IS NOT NULL
          AND nom_poligono <> ''
    """
    mapping: dict[str, list[dict]] = {}
    with conn.cursor(dictionary=True) as cur:
        cur.execute(sql)
        rows = cur.fetchall()

    for row in rows:
        key = (row["nom_poligono"] or "").strip()
        if not key:
            continue
        mapping.setdefault(key, []).append(row)
    return mapping


def update_site_current_state(
    conn,
    logger: logging.Logger,
    id_site: int,
    plan: str | None,
    toxines: str | None,
    etat_administratif: int,
    date_changement_etat,
):
    etat_val = 1 if etat_administratif else 0
    sql = """
        UPDATE analyses_sites
           SET plan = %(plan)s,
               toxines = %(toxines)s,
               etat_administratif = %(etat_administratif)s,
               date_changement_etat = %(date_changement_etat)s
         WHERE id = %(id_site)s
    """
    params = {
        "plan": plan,
        "toxines": toxines,
        "etat_administratif": etat_val,
        "date_changement_etat": date_changement_etat,
        "id_site": id_site,
    }
    try:
        with conn.cursor() as cur:
            cur.execute(sql, params)
    except Exception as exc:
        logger.error(
            "Erreur lors de la mise à jour de l'état pour le site id=%s : %s",
            id_site,
            exc,
        )
        raise


def insert_history_if_new(
    conn,
    logger: logging.Logger,
    id_site: int,
    date_document,
    heure_document,
    plan: str | None,
    toxines: str | None,
    etat_administratif: int,
    date_changement_etat,
):
    if date_changement_etat is None:
        return

    etat_val = 1 if etat_administratif else 0
    check_sql = """
        SELECT
            id_historique,
            date_document,
            heure_document,
            plan,
            toxines,
            etat_administratif
          FROM analyses_sites_historique
         WHERE id_site = %(id_site)s
           AND date_changement_etat = %(date_changement_etat)s
         LIMIT 1
    """
    update_sql = """
        UPDATE analyses_sites_historique
           SET date_document = %(date_document)s,
               heure_document = %(heure_document)s,
               plan = %(plan)s,
               toxines = %(toxines)s,
               etat_administratif = %(etat_administratif)s
         WHERE id_historique = %(id_historique)s
    """
    insert_sql = """
        INSERT INTO analyses_sites_historique (
            id_site,
            date_document,
            heure_document,
            plan,
            toxines,
            etat_administratif,
            date_changement_etat,
            created_at
        ) VALUES (
            %(id_site)s,
            %(date_document)s,
            %(heure_document)s,
            %(plan)s,
            %(toxines)s,
            %(etat_administratif)s,
            %(date_changement_etat)s,
            NOW()
        )
    """
    params_check = {
        "id_site": id_site,
        "date_changement_etat": date_changement_etat,
    }

    try:
        with conn.cursor(dictionary=True) as cur:
            cur.execute(check_sql, params_check)
            row = cur.fetchone()
            if row:
                params_update = {
                    "id_historique": row["id_historique"],
                    "date_document": date_document,
                    "heure_document": heure_document,
                    "plan": plan,
                    "toxines": toxines,
                    "etat_administratif": etat_val,
                }
                history_changed = (
                    row.get("date_document") != date_document
                    or row.get("heure_document") != heure_document
                    or row.get("plan") != plan
                    or row.get("toxines") != toxines
                    or int(row.get("etat_administratif") or 0) != etat_val
                )
                if history_changed:
                    cur.execute(update_sql, params_update)
                return

            cur.execute(insert_sql, {
                "id_site": id_site,
                "date_document": date_document,
                "heure_document": heure_document,
                "plan": plan,
                "toxines": toxines,
                "etat_administratif": etat_val,
                "date_changement_etat": date_changement_etat,
            })
    except Exception as exc:
        logger.error(
            "Erreur lors de l'insertion historique pour le site id=%s : %s",
            id_site,
            exc,
        )
        raise