\
from typing import List, Dict

from app.config import Settings


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


def obtener_usuarios_moodle(connection, settings: Settings) -> List[Dict]:
    user_table = f"{settings.moodle_prefix}user"
    data_table = f"{settings.moodle_prefix}user_info_data"
    field_table = f"{settings.moodle_prefix}user_info_field"

    scope_having = ""
    if settings.sync_scope == "eligible_only":
        scope_having = """
        HAVING celular IS NOT NULL
           AND TRIM(celular) <> ''
           AND consentimiento_comunicaciones = '1'
        """
    elif settings.sync_scope == "with_phone":
        scope_having = """
        HAVING celular IS NOT NULL
           AND TRIM(celular) <> ''
        """

    sql = f"""
        SELECT
            u.id AS moodle_user_id,
            u.username,
            u.firstname,
            u.lastname,
            u.email,
            MAX(
                CASE
                    WHEN f.shortname = 'celular'
                    THEN d.data
                END
            ) AS celular,
            MAX(
                CASE
                    WHEN f.shortname = 'consentimiento_comunicaciones'
                    THEN d.data
                END
            ) AS consentimiento_comunicaciones
        FROM {_q(settings.moodle_db)}.{_q(user_table)} u
        LEFT JOIN {_q(settings.moodle_db)}.{_q(data_table)} d
            ON d.userid = u.id
        LEFT JOIN {_q(settings.moodle_db)}.{_q(field_table)} f
            ON f.id = d.fieldid
        WHERE u.deleted = 0
          AND u.suspended = 0
          AND u.username <> 'guest'
        GROUP BY
            u.id,
            u.username,
            u.firstname,
            u.lastname,
            u.email
        {scope_having}
        ORDER BY u.id
    """

    with connection.cursor() as cursor:
        cursor.execute(sql)
        return list(cursor.fetchall())


def validar_campos_perfil(connection, settings: Settings) -> Dict[str, int]:
    field_table = f"{settings.moodle_prefix}user_info_field"
    sql = f"""
        SELECT shortname, id
        FROM {_q(settings.moodle_db)}.{_q(field_table)}
        WHERE shortname IN ('celular', 'consentimiento_comunicaciones')
    """
    with connection.cursor() as cursor:
        cursor.execute(sql)
        return {row["shortname"]: row["id"] for row in cursor.fetchall()}