\
from dataclasses import dataclass
from typing import Dict, Iterable, Set

from app.config import Settings
from app.utils.phone import normalizar_telefono


@dataclass
class SyncStats:
    encontrados: int = 0
    con_celular: int = 0
    con_consentimiento: int = 0
    telefonos_validos: int = 0
    telefonos_invalidos: int = 0
    insertados: int = 0
    actualizados: int = 0
    omitidos: int = 0


def _q(identifier: str) -> str:
    return f"`{identifier}`"


def _to_optin(value: object) -> int:
    return 1 if str(value or "").strip().lower() in {"1", "true", "yes", "si", "sí"} else 0


def _existing_external_ids(connection, settings: Settings, ids: Iterable[int]) -> Set[int]:
    ids = list(ids)
    if not ids:
        return set()

    table = f"{_q(settings.connect_db)}.`dhc_contactos`"
    placeholders = ",".join(["%s"] * len(ids))
    sql = f"""
        SELECT external_user_id
        FROM {table}
        WHERE origen = 'moodle'
          AND external_user_id IN ({placeholders})
    """
    with connection.cursor() as cursor:
        cursor.execute(sql, ids)
        return {int(row["external_user_id"]) for row in cursor.fetchall()}


def sincronizar_contactos(
    connection,
    settings: Settings,
    usuarios: Iterable[Dict],
    dry_run: bool = False,
) -> SyncStats:
    usuarios = list(usuarios)
    stats = SyncStats(encontrados=len(usuarios))

    existing = _existing_external_ids(
        connection,
        settings,
        [int(u["moodle_user_id"]) for u in usuarios],
    )

    table = f"{_q(settings.connect_db)}.`dhc_contactos`"

    upsert_sql = f"""
        INSERT INTO {table} (
            origen,
            external_user_id,
            username,
            nombres,
            apellidos,
            email,
            telefono_original,
            telefono_e164,
            whatsapp_optin,
            fecha_optin,
            estado,
            fecha_sincronizacion
        )
        VALUES (
            'moodle',
            %s, %s, %s, %s, %s,
            %s, %s, %s,
            CASE WHEN %s = 1 THEN NOW() ELSE NULL END,
            'activo',
            NOW()
        )
        ON DUPLICATE KEY UPDATE
            username = VALUES(username),
            nombres = VALUES(nombres),
            apellidos = VALUES(apellidos),
            email = VALUES(email),
            telefono_original = VALUES(telefono_original),
            telefono_e164 = VALUES(telefono_e164),
            fecha_optin = CASE
                WHEN whatsapp_optin = 0 AND VALUES(whatsapp_optin) = 1
                    THEN NOW()
                WHEN VALUES(whatsapp_optin) = 0
                    THEN NULL
                ELSE fecha_optin
            END,
            whatsapp_optin = VALUES(whatsapp_optin),
            estado = 'activo',
            fecha_sincronizacion = NOW()
    """

    values = []

    for u in usuarios:
        celular_raw = str(u.get("celular") or "").strip() or None
        optin = _to_optin(u.get("consentimiento_comunicaciones"))

        if celular_raw:
            stats.con_celular += 1
        if optin:
            stats.con_consentimiento += 1

        e164 = normalizar_telefono(
            celular_raw,
            country_code=settings.default_country_code,
        )

        if celular_raw and e164:
            stats.telefonos_validos += 1
        elif celular_raw and not e164:
            stats.telefonos_invalidos += 1

        # En el modo seguro inicial, solo ingresan contactos realmente elegibles.
        if settings.sync_scope == "eligible_only" and (not e164 or not optin):
            stats.omitidos += 1
            continue

        external_id = int(u["moodle_user_id"])

        if external_id in existing:
            stats.actualizados += 1
        else:
            stats.insertados += 1

        values.append(
            (
                external_id,
                u.get("username"),
                u.get("firstname"),
                u.get("lastname"),
                u.get("email"),
                celular_raw,
                e164,
                optin,
                optin,
            )
        )

    if dry_run or not values:
        return stats

    try:
        with connection.cursor() as cursor:
            cursor.executemany(upsert_sql, values)
        connection.commit()
    except Exception:
        connection.rollback()
        raise

    return stats