from dataclasses import dataclass
from datetime import datetime, timezone
from typing import Dict, Iterable, List, Optional, Set, Tuple

from app.config import Settings


@dataclass
class AcademicSyncStats:
    cursos_encontrados: int = 0
    cursos_insertados: int = 0
    cursos_actualizados: int = 0

    matriculas_encontradas: int = 0
    matriculas_insertadas: int = 0
    matriculas_actualizadas: int = 0
    matriculas_omitidas: int = 0

    finalizadas: int = 0
    con_ultimo_acceso: int = 0


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


def _utc_datetime(timestamp_value) -> Optional[datetime]:
    if timestamp_value is None:
        return None

    try:
        ts = int(timestamp_value)
    except (TypeError, ValueError):
        return None

    if ts <= 0:
        return None

    return datetime.fromtimestamp(ts, tz=timezone.utc).replace(tzinfo=None)


def _existing_external_ids(
    connection,
    settings: Settings,
    table_name: str,
    external_column: str,
    ids: Iterable[int],
) -> Set[int]:
    ids = sorted({int(x) for x in ids})
    if not ids:
        return set()

    result: Set[int] = set()
    batch_size = 800

    for i in range(0, len(ids), batch_size):
        batch = ids[i:i + batch_size]
        placeholders = ",".join(["%s"] * len(batch))

        sql = f"""
            SELECT {external_column}
            FROM {_q(settings.connect_db)}.{_q(table_name)}
            WHERE origen = 'moodle'
              AND {external_column} IN ({placeholders})
        """

        with connection.cursor() as cursor:
            cursor.execute(sql, batch)
            result.update(int(row[external_column]) for row in cursor.fetchall())

    return result


def sincronizar_cursos(
    connection,
    settings: Settings,
    cursos: Iterable[Dict],
    dry_run: bool = False,
) -> AcademicSyncStats:
    cursos = list(cursos)
    stats = AcademicSyncStats(cursos_encontrados=len(cursos))

    external_ids = [int(c["moodle_course_id"]) for c in cursos]
    existing = _existing_external_ids(
        connection,
        settings,
        "dhc_cursos",
        "external_course_id",
        external_ids,
    )

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

    sql = f"""
        INSERT INTO {table} (
            origen,
            external_course_id,
            shortname,
            fullname,
            fecha_inicio,
            fecha_fin,
            estado,
            fecha_sincronizacion
        )
        VALUES (
            'moodle',
            %s, %s, %s, %s, %s, %s, UTC_TIMESTAMP()
        )
        ON DUPLICATE KEY UPDATE
            shortname = VALUES(shortname),
            fullname = VALUES(fullname),
            fecha_inicio = VALUES(fecha_inicio),
            fecha_fin = VALUES(fecha_fin),
            estado = VALUES(estado),
            fecha_sincronizacion = UTC_TIMESTAMP()
    """

    now_utc = datetime.now(timezone.utc).replace(tzinfo=None)
    values = []

    for c in cursos:
        external_id = int(c["moodle_course_id"])
        fecha_inicio = _utc_datetime(c.get("fecha_inicio_ts"))
        fecha_fin = _utc_datetime(c.get("fecha_fin_ts"))

        if not bool(c.get("visible", 1)):
            estado = "oculto"
        elif fecha_fin is not None and fecha_fin < now_utc:
            estado = "finalizado"
        else:
            estado = "activo"

        if external_id in existing:
            stats.cursos_actualizados += 1
        else:
            stats.cursos_insertados += 1

        values.append(
            (
                external_id,
                c.get("shortname"),
                c.get("fullname"),
                fecha_inicio,
                fecha_fin,
                estado,
            )
        )

    if dry_run or not values:
        return stats

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

    return stats


def _contact_map(connection, settings: Settings) -> Dict[int, int]:
    sql = f"""
        SELECT id, external_user_id
        FROM {_q(settings.connect_db)}.`dhc_contactos`
        WHERE origen = 'moodle'
          AND external_user_id IS NOT NULL
          AND estado = 'activo'
    """
    with connection.cursor() as cursor:
        cursor.execute(sql)
        return {
            int(row["external_user_id"]): int(row["id"])
            for row in cursor.fetchall()
        }


def _course_map(connection, settings: Settings) -> Dict[int, int]:
    sql = f"""
        SELECT id, external_course_id
        FROM {_q(settings.connect_db)}.`dhc_cursos`
        WHERE origen = 'moodle'
          AND external_course_id IS NOT NULL
    """
    with connection.cursor() as cursor:
        cursor.execute(sql)
        return {
            int(row["external_course_id"]): int(row["id"])
            for row in cursor.fetchall()
        }


def _existing_enrolments(
    connection,
    settings: Settings,
    pairs: List[Tuple[int, int]],
) -> Set[Tuple[int, int]]:
    if not pairs:
        return set()

    contact_ids = sorted({contact_id for contact_id, _ in pairs})
    result: Set[Tuple[int, int]] = set()
    batch_size = 800

    for i in range(0, len(contact_ids), batch_size):
        batch = contact_ids[i:i + batch_size]
        placeholders = ",".join(["%s"] * len(batch))

        sql = f"""
            SELECT contacto_id, curso_id
            FROM {_q(settings.connect_db)}.`dhc_matriculas`
            WHERE contacto_id IN ({placeholders})
        """

        with connection.cursor() as cursor:
            cursor.execute(sql, batch)
            result.update(
                (int(row["contacto_id"]), int(row["curso_id"]))
                for row in cursor.fetchall()
            )

    return result


def sincronizar_matriculas(
    connection,
    settings: Settings,
    matriculas: Iterable[Dict],
    dry_run: bool = False,
) -> AcademicSyncStats:
    matriculas = list(matriculas)
    stats = AcademicSyncStats(matriculas_encontradas=len(matriculas))

    contacts = _contact_map(connection, settings)
    courses = _course_map(connection, settings)

    pairs: List[Tuple[int, int]] = []
    resolved_rows = []

    for m in matriculas:
        moodle_user_id = int(m["moodle_user_id"])
        moodle_course_id = int(m["moodle_course_id"])

        contacto_id = contacts.get(moodle_user_id)
        curso_id = courses.get(moodle_course_id)

        if contacto_id is None or curso_id is None:
            stats.matriculas_omitidas += 1
            continue

        pairs.append((contacto_id, curso_id))
        resolved_rows.append((m, contacto_id, curso_id))

    existing = _existing_enrolments(connection, settings, pairs)
    table = f"{_q(settings.connect_db)}.`dhc_matriculas`"

    sql = f"""
        INSERT INTO {table} (
            contacto_id,
            curso_id,
            fecha_matricula,
            primer_acceso,
            ultimo_acceso,
            progreso,
            finalizado,
            fecha_finalizacion,
            estado,
            fecha_sincronizacion
        )
        VALUES (
            %s, %s, %s, NULL, %s, 0.00, %s, %s, %s, UTC_TIMESTAMP()
        )
        ON DUPLICATE KEY UPDATE
            fecha_matricula = VALUES(fecha_matricula),
            ultimo_acceso = VALUES(ultimo_acceso),
            finalizado = VALUES(finalizado),
            fecha_finalizacion = VALUES(fecha_finalizacion),
            estado = VALUES(estado),
            fecha_sincronizacion = UTC_TIMESTAMP()
    """

    values = []

    for m, contacto_id, curso_id in resolved_rows:
        fecha_matricula = _utc_datetime(m.get("fecha_matricula_ts"))
        ultimo_acceso = _utc_datetime(m.get("ultimo_acceso_ts"))
        fecha_finalizacion = _utc_datetime(m.get("fecha_finalizacion_ts"))

        finalizado = 1 if fecha_finalizacion is not None else 0

        if finalizado:
            estado = "finalizado"
            stats.finalizadas += 1
        elif ultimo_acceso is not None:
            estado = "activo"
            stats.con_ultimo_acceso += 1
        else:
            estado = "matriculado"

        pair = (contacto_id, curso_id)

        if pair in existing:
            stats.matriculas_actualizadas += 1
        else:
            stats.matriculas_insertadas += 1

        values.append(
            (
                contacto_id,
                curso_id,
                fecha_matricula,
                ultimo_acceso,
                finalizado,
                fecha_finalizacion,
                estado,
            )
        )

    if dry_run or not values:
        return stats

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

    return stats