from typing import Dict, Iterable, List, Sequence

from app.config import Settings


BATCH_SIZE = 800


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


def _chunks(values: Sequence[int], size: int = BATCH_SIZE):
    for i in range(0, len(values), size):
        yield values[i:i + size]


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


def obtener_matriculas_moodle(
    connection,
    settings: Settings,
    moodle_user_ids: Iterable[int],
) -> List[Dict]:
    user_ids = sorted({int(x) for x in moodle_user_ids})
    if not user_ids:
        return []

    ue_table = f"{settings.moodle_prefix}user_enrolments"
    enrol_table = f"{settings.moodle_prefix}enrol"
    lastaccess_table = f"{settings.moodle_prefix}user_lastaccess"
    completion_table = f"{settings.moodle_prefix}course_completions"

    records: List[Dict] = []

    for batch in _chunks(user_ids):
        placeholders = ",".join(["%s"] * len(batch))

        sql = f"""
            SELECT
                ue.userid AS moodle_user_id,
                e.courseid AS moodle_course_id,
                MIN(NULLIF(ue.timecreated, 0)) AS fecha_matricula_ts,
                MAX(COALESCE(NULLIF(ula.timeaccess, 0), 0)) AS ultimo_acceso_ts,
                MAX(COALESCE(NULLIF(cc.timecompleted, 0), 0)) AS fecha_finalizacion_ts

            FROM {_q(settings.moodle_db)}.{_q(ue_table)} ue

            INNER JOIN {_q(settings.moodle_db)}.{_q(enrol_table)} e
                ON e.id = ue.enrolid

            LEFT JOIN {_q(settings.moodle_db)}.{_q(lastaccess_table)} ula
                ON ula.userid = ue.userid
               AND ula.courseid = e.courseid

            LEFT JOIN {_q(settings.moodle_db)}.{_q(completion_table)} cc
                ON cc.userid = ue.userid
               AND cc.course = e.courseid

            WHERE ue.userid IN ({placeholders})
              AND ue.status = 0
              AND e.status = 0

            GROUP BY
                ue.userid,
                e.courseid

            ORDER BY
                ue.userid,
                e.courseid
        """

        with connection.cursor() as cursor:
            cursor.execute(sql, batch)
            records.extend(cursor.fetchall())

    return records


def obtener_cursos_moodle(
    connection,
    settings: Settings,
    moodle_course_ids: Iterable[int],
) -> List[Dict]:
    course_ids = sorted({int(x) for x in moodle_course_ids if int(x) > 0})
    if not course_ids:
        return []

    course_table = f"{settings.moodle_prefix}course"
    records: List[Dict] = []

    for batch in _chunks(course_ids):
        placeholders = ",".join(["%s"] * len(batch))

        sql = f"""
            SELECT
                c.id AS moodle_course_id,
                c.shortname,
                c.fullname,
                c.startdate AS fecha_inicio_ts,
                c.enddate AS fecha_fin_ts,
                c.visible
            FROM {_q(settings.moodle_db)}.{_q(course_table)} c
            WHERE c.id IN ({placeholders})
              AND c.id <> 1
            ORDER BY c.id
        """

        with connection.cursor() as cursor:
            cursor.execute(sql, batch)
            records.extend(cursor.fetchall())

    return records