SET @latitude = -15.8040064;
SET @longitude = -47.8707712;
SET @distancia = 50;

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
)

SELECT
    CONCAT(
        '{',
          '"id_chave_curso":', id_chave_curso, ',',
          '"id_chave_instituicao":', id_chave_instituicao, ',',
          '"sigla":"', REPLACE(sigla, '"', '\"'), '",',
          '"nome_cidade":"', REPLACE(cp.nome_cidade, '"', '\"'), '",',
          '"sigla_estado":"', REPLACE(cp.sigla_estado, '"', '\"'), '",',
          '"latitude":', cp.latitude, ',',
          '"longitude":', cp.longitude, ',',
          '"distancia_km":', CAST(cp.distancia_km AS UNSIGNED), ',',
          '"instituicao":{',
              '"id":', id_chave_instituicao, ',',
              '"nome":"', REPLACE(nome_instituicao, '"', '\"'), '",',
              '"logo":"', REPLACE(logo, '"', '\"'), '"',
          '},',
          '"campos":[',
              GROUP_CONCAT(
                  DISTINCT CONCAT(
                      '{',
                        '"tipo":"', REPLACE(tc.nome_tipo_classificacao, '"', '\"'), '",',
                        '"caption":"', REPLACE(COALESCE(tc.caption_substituto, 'Sem título'), '"', '\"'), '",',
                        '"valor":"', REPLACE(COALESCE(t.titulo, 'Sem título'), '"', '\"'), '",',
                        '"isolamento_valor":"', REPLACE(COALESCE(tc.isolamento_valor, ''), '"', '\"'), '",',
                        '"limita_largura":"', REPLACE(COALESCE(tc.limita_largura, ''), '"', '\"'), '",',
                        '"mostra_caption_tamanho":"', REPLACE(COALESCE(tc.mostra_caption_tamanho, ''), '"', '\"'), '",',
                        '"mostra_valor_tamanho":"', REPLACE(COALESCE(tc.mostra_valor_tamanho, ''), '"', '\"'), '",',
                        '"bold_valor":"', REPLACE(COALESCE(tc.bold_valor, ''), '"', '\"'), '",',
                        '"color_valor":"', REPLACE(COALESCE(tc.color_valor, ''), '"', '\"'), '",',
                        '"border_valor":"', REPLACE(COALESCE(tc.border_valor, ''), '"', '\"'), '",',
                        '"border_radius_valor":"', REPLACE(COALESCE(tc.border_radius_valor, ''), '"', '\"'), '",',
                        '"background_valor":"', REPLACE(COALESCE(tc.background_valor, ''), '"', '\"'), '",',
                        '"padding_left_valor":"', REPLACE(COALESCE(tc.padding_left_valor, ''), '"', '\"'), '",',
                        '"padding_right_valor":"', REPLACE(COALESCE(tc.padding_right_valor, ''), '"', '\"'), '",',
                        '"padding_top_valor":"', REPLACE(COALESCE(tc.padding_top_valor, ''), '"', '\"'), '",',
                        '"padding_bottom_valor":"', REPLACE(COALESCE(tc.padding_bottom_valor, ''), '"', '\"'), '",',
                        '"margin_left_valor":"', REPLACE(COALESCE(tc.margin_left_valor, ''), '"', '\"'), '",',
                        '"margin_right_valor":"', REPLACE(COALESCE(tc.margin_right_valor, ''), '"', '\"'), '",',
                        '"margin_top_valor":"', REPLACE(COALESCE(tc.margin_top_valor, ''), '"', '\"'), '",',
                        '"margin_bottom_valor":"', REPLACE(COALESCE(tc.margin_bottom_valor, ''), '"', '\"'), '",',
                        '"junta_caption_valor":"', REPLACE(COALESCE(tc.junta_caption_valor, ''), '"', '\"'), '",',
                        '"caracter_depois_valor":"', REPLACE(COALESCE(tc.caracter_depois_valor, ''), '"', '\"'), '",',
                        '"forca_quebra_linha":"', REPLACE(COALESCE(tc.forca_quebra_linha, ''), '"', '\"'), '"',
                      '}'
                  )
                  ORDER BY tc.ordem, tc.id_chave_tipo_classificacao SEPARATOR ','
              ),
          ']',
        '}'
    ) AS curso_json
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
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,
    id_chave_instituicao,
    nome_instituicao,
    logo,
    sigla,
    cp.nome_cidade,
    cp.sigla_estado,
    cp.latitude,
    cp.longitude,
    cp.distancia_km
ORDER BY distancia_km;

