SELECT
    CONCAT(
	CASE
            WHEN ROW_NUMBER() OVER (ORDER BY id_chave_curso) = 1 THEN CONCAT('{','"autor": "Victor Pellegrini Mammana",','"data":','"',DATE_FORMAT(CURDATE(), '%Y%m%d'),'",', '"cursos_20250327": [')
            ELSE ''
        END,
        '{',
          '"id_chave_curso":', id_chave_curso, ',',
	  '"URL":"', COALESCE((select nome_url FROM URLs WHERE id_chave_url = id_url),"não declarado"), '",',
	  '"imagem_representativa": "path/nome_arquivo_imagem.png", ',
	  CASE WHEN cursos.id_cidade is not NULL THEN 
	  CONCAT(
	  '"cidade_original":{',
          '"nome_cidade":"', REPLACE((SELECT nome_cidade FROM cidades WHERE id_chave_cidade = cursos.id_cidade), '"', '\"'), '",',
          '"sigla_estado":"', REPLACE((SELECT sigla_estado FROM estados WHERE codigo_ibge = (SELECT id_estado FROM cidades WHERE id_chave_cidade = cursos.id_cidade)), '"', '\"'), '",',
          '"latitude":', (SELECT latitude FROM cidades WHERE id_chave_cidade = cursos.id_cidade), ',',
	'"longitude":', (SELECT longitude FROM cidades WHERE id_chave_cidade = cursos.id_cidade), '},') ELSE '' END,
	  CASE WHEN id_url is not NULL THEN CONCAT('"url": "',(SELECT REPLACE(nome_url, '"','\"') FROM URLs WHERE id_chave_url = id_url ), '",') ELSE '' END,
          '"instituicao":{',
              '"id_chave_instituicao":', id_chave_instituicao, ',',
              '"nome_instituicao":"', REPLACE(nome_instituicao, '"', '\"'), '",',
              '"logo":"', REPLACE(logo, '"', '\"'), '",',
              '"sigla":"', REPLACE(sigla, '"', '\"'), '"',
   '},',
          '"campos":[',
              GROUP_CONCAT(
                  DISTINCT CONCAT(
                      '{',
                        '"nome_campo":"', REPLACE(tc.nome_tipo_classificacao, '"', '\"'), '",',
                        '"caption_mostrado":"', REPLACE(COALESCE(tc.caption_substituto, 'Sem título'), '"', '\"'), '",',
			'"serve_para_busca":"', REPLACE(COALESCE(tc.serve_para_busca,'não declarado'),'"','\"'), '",',
			'"mostra_caption":"', REPLACE(COALESCE(tc.mostra_caption,'não declarado'),'"','\"'), '",',
			'"mostra_valor":"', REPLACE(COALESCE(tc.mostra_valor,'não declarado'),'"','\"'), '",',
			'"verso":"', REPLACE(COALESCE(tc.verso,'não declarado'),'"','\"'), '",',
                        '"valor":"',REPLACE(REPLACE(
            IF(LEFT(COALESCE(t.titulo, "Não Declarado"), 1) = CHAR(8),
            SUBSTRING(COALESCE(t.titulo, "Não Declarado"), 2),
            COALESCE(t.titulo, "Não Declarado")
        ),
        CONCAT(' ', CHAR(8)),
        ''
        ),CHAR(8),""), '"' 

                      '}'
                  )
                  ORDER BY tc.ordem, tc.id_chave_tipo_classificacao SEPARATOR ','
              ),
          ']',
        '}',
	CASE
		WHEN ROW_NUMBER() OVER (ORDER BY id_chave_curso) = COUNT(*) OVER () THEN '] }'
		ELSE ','
	END 
    ) 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
GROUP BY
    id_chave_curso,
    id_chave_instituicao,
    nome_instituicao,
    logo,
    sigla;


