SET @latitude = -15.799645;
SET @longitude = -47.8601;
SET @distancia = 50;

-- CTE para filtrar cidades próximas
WITH cidades_proximas AS (
    SELECT
        c.id_chave_cidade,
        c.nome_cidade,
        e.sigla_estado,
        (
            6371 * ACOS(
                COS(RADIANS(@latitude)) * COS(RADIANS(c.latitude)) *
                COS(RADIANS(c.longitude) - RADIANS(@longitude)) +
                SIN(RADIANS(@latitude)) * SIN(RADIANS(c.latitude))
            )
        ) AS distancia_km,
        c.latitude,
        c.longitude
    FROM cidades c
    JOIN estados e ON c.id_estado = e.codigo_ibge
    WHERE 
        c.latitude BETWEEN (@latitude - (@distancia / 111)) AND (@latitude + (@distancia / 111))
        AND c.longitude BETWEEN (@longitude - (@distancia / (111 * COS(RADIANS(@latitude)))))
                            AND (@longitude + (@distancia / (111 * COS(RADIANS(@latitude)))))
    HAVING distancia_km <= @distancia
    ORDER BY distancia_km
),

-- Frases coletadas
frases_coletadas AS (
    SELECT
        f.id_titulo,
        REPLACE(
            TRIM(
                REPLACE(
                    GROUP_CONCAT(
                        CONCAT(
                            ct.caracter_antes,
                            CASE
                                WHEN tt.nome_tipo_token = 'sigla' THEN UPPER(t.acentuada)
                                WHEN tt.nome_tipo_token IN ('preposicao', 'conjuncao') THEN LOWER(t.acentuada)
                                ELSE CONCAT(
                                    UPPER(SUBSTRING(t.acentuada, 1, 1)),
                                    LOWER(SUBSTRING(t.acentuada, 2))
                                )
                            END,
                            ct.caracter_depois
                        )
                        ORDER BY ordem
                        SEPARATOR ''
                    ),
                    CONCAT(' ', CHAR(8)),
                    ''
                )
            ),
            '  ',
            ' '
        ) AS titulo
    FROM frases f
    JOIN tokens t ON f.id_token = t.id_chave_token
    LEFT JOIN classificacoes_tokens ct ON BINARY t.acentuada = ct.nome_token
    LEFT JOIN tipos_tokens tt ON ct.id_tipo_token = tt.id_chave_tipo_token
    GROUP BY f.id_titulo
)

-- Query Principal
SELECT
    CONCAT(
        '<div id=\"curso_', id_chave_curso, '\" class=\"linha\">',
        '<div class=\"logo\">', '<img src=\"../imagens/', logo, '\" alt=\"logo\">', '</div>',
        '<div class=\"nome_instituicao\">', nome_instituicao, '</div>',
        GROUP_CONCAT(
            CONCAT(
                '<div class=\"celula\">',
                '<div class=\"caption\" data-tipo-classificacao=\"', tc.nome_tipo_classificacao, '\" ',
                'data-titulo=\"', COALESCE(t.titulo, 'Sem título'), '\" ',
                'data-companion=\"curso_', id_chave_curso, '\">', tc.nome_tipo_classificacao, '</div>',
                '<div>', COALESCE(t.titulo, 'Sem título'), '</div>',
                '</div>'
            )
            SEPARATOR ''
        ),
        '<div class=\"distancia\" data-latitude=\"', cp.latitude, '\" data-longitude=\"', cp.longitude, '\">',
        CAST(cp.distancia_km AS UNSIGNED), ' km </div>',
        '</div>'
    ) AS curso_final,
    cp.nome_cidade,
    cp.sigla_estado,
    cp.latitude,
    cp.longitude
FROM cursos
JOIN instituicoes ON id_instituicao = id_chave_instituicao
JOIN campos_cursos cc ON cc.id_curso = id_chave_curso
LEFT JOIN frases_coletadas t ON t.id_titulo = cc.id_titulo
LEFT JOIN tipos_classificacoes tc ON cc.id_tipo_classificacao = tc.id_chave_tipo_classificacao
-- Garantia de que o curso está associado às cidades próximas
JOIN campos_cursos cc_cidade ON cc_cidade.id_curso = cc.id_curso
JOIN frases_coletadas t_cidade ON t_cidade.id_titulo = cc_cidade.id_titulo
JOIN campos_cursos cc_estado ON cc_estado.id_curso = cc.id_curso
JOIN frases_coletadas t_estado ON t_estado.id_titulo = cc_estado.id_titulo
JOIN cidades_proximas cp ON (
    t_cidade.titulo = cp.nome_cidade AND t_estado.titulo = cp.sigla_estado
)
GROUP BY id_chave_curso, cp.nome_cidade, cp.sigla_estado, cp.latitude, cp.longitude
ORDER BY id_chave_curso;

