SET @latitude = -15.8040064;
SET @longitude = -47.8707712;
SET @distancia = 10;
SET collation_connection = 'utf8_general_ci';

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
)

-- Query Principal
SELECT
    CONCAT(
        '<div id="curso_', id_chave_curso, '" class="linha">',
        '<div class="logo" style="width: ',largura_logo_instituicao,'">', '<img src="../imagens/', logo, '" alt="logo">', '</div>',
        '<div class="nome_instituicao" data-id-instituicao="',id_chave_instituicao,'">', nome_instituicao, '</div>',
        GROUP_CONCAT( DISTINCT
            CONCAT(
       		CASE
			WHEN ordem = 1 THEN '<div class="flip-card"><div class="flip-card-inner"><div class="flip-card-front is-visible"><img class="imagem_curso" src="../imagens/tecnico-eletronica.jpg" alt="Imagem"></div><div class="flip-card-back not-visible"><div class="detalhes">'
			ELSE ''
		END,
         '<div class="celula" style="',tc.isolamento_valor, CASE WHEN tc.limita_largura <> 'nao_limita' THEN concat(' width: ',tc.limita_largura,';') ELSE '' END,'">',
		CASE 
			WHEN tc.mostra_caption_tamanho <> 'nao_mostra' THEN concat
									(
										'<div class="caption" data-tipo-classificacao="', tc.nome_tipo_classificacao, '" ',
										'data-titulo="', COALESCE(t.titulo, 'Sem título'), '" ',
										'data-companion="curso_', id_chave_curso, '" style="font-size: ',tc.mostra_caption_tamanho,';">', tc.caption_substituto, '</div>'
									)
			ELSE ''
		END,
		CASE
			WHEN tc.mostra_valor_tamanho <> 'nao_mostra' THEN concat
									(
								                '<div class="valor" style="',
										'font-size: '			, tc.mostra_valor_tamanho	,'; ',
										'font-weight: '			, tc.bold_valor			,'; ',
										'color: '			, tc.color_valor		,'; ',
										'border: '			, tc.border_valor		,'; ',
										'border-radius: '		, tc.border_radius_valor	,'; ',
										'background-color: '		, tc.background_valor		,'; ',
										'padding-left: '		, tc.padding_left_valor		,'; ',
										'padding-right: '		, tc.padding_right_valor	,'; ',
										'padding-top: '			, tc.padding_top_valor		,'; ',
										'padding-bottom: '		, tc.padding_bottom_valor	,'; ',
										'margin-left: '			, tc.margin_left_valor		,'; ',
										'margin-right: '		, tc.margin_right_valor		,'; ',
										'margin-top: '			, tc.margin_top_valor		,'; ',
										'margin-bottom: '		, tc.margin_bottom_valor	,'; ',
										'">', 
										CASE 											
											WHEN tc.junta_caption_valor = 'sim' THEN concat(tc.caption_substituto,': ')
											ELSE '' 
										END,
										COALESCE(t.titulo, 'Sem título'),tc.caracter_depois_valor, '</div>'
									)
			ELSE ''
		END,
                '</div>',
		CASE 
			WHEN tc.forca_quebra_linha = 'sim' THEN '<div class="forca_quebra"></div>'
			ELSE ''
		END
            )
            ORDER BY tc.ordem, tc.id_chave_tipo_classificacao SEPARATOR ''
        ),
        '</div></div></div></div>', # fecha flip-card
        '<div class="distancia" data-latitude="', cp.latitude, '" data-longitude="', cp.longitude, '">Distância: ',
        CAST(cp.distancia_km AS UNSIGNED), ' km </div>',
'utf8mb4_general_ci'        '</div>'
    ) AS curso_final,
    id_chave_curso,
    cp.nome_cidade,
    cp.sigla_estado,
    cp.latitude,
    cp.longitude,
    id_chave_instituicao,
    sigla
FROM cursos
JOIN instituicoes ON id_instituicao = id_chave_instituicao 
JOIN campos_cursos cc ON cc.id_curso = id_chave_curso
LEFT JOIN frases_opt 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_opt 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_opt 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, cp.distancia_km

ORDER BY distancia_km;
