SELECT
    CONCAT(
          '"id_chave_curso"|', COALESCE(id_chave_curso, 'ausente'), '|',
	  '"URL"|"', COALESCE((select COALESCE(nome_url,'ausente') FROM URLs WHERE id_chave_url = id_url),"não declarado"), '"|',
	  CONCAT(
          '"nome_cidade"|"', COALESCE(REPLACE((SELECT COALESCE(nome_cidade, 'sem_cidade') FROM cidades WHERE id_chave_cidade = cursos.id_cidade), '"', '\"'), 'sem_cidade'), '"|"nao"|',
          '"sigla_estado"|"', COALESCE(REPLACE((SELECT COALESCE(sigla_estado, 'sem_estado') FROM estados WHERE codigo_ibge = (SELECT id_estado FROM cidades WHERE id_chave_cidade = cursos.id_cidade)), '"', '\"'),'sem_estado'), '"|"nao"|',
          '"latitude"|', COALESCE((SELECT COALESCE(latitude,'sem_latitude') FROM cidades WHERE id_chave_cidade = cursos.id_cidade),'sem_latitude'), '|"nao"|',
	  '"longitude"|', COALESCE((SELECT COALESCE(longitude,'sem_longitude') FROM cidades WHERE id_chave_cidade = cursos.id_cidade), 'sem_longitude'), '|"nao"|'),
	  CONCAT('"url"|"',COALESCE((SELECT REPLACE(COALESCE(nome_url,'ausente'), '"','\"') FROM URLs WHERE id_chave_url = id_url ), 'sem_url'), '"|'),
              '"id_chave_instituicao"|', COALESCE(id_chave_instituicao,'ausente'), '|"nao"|',
              '"nome_instituicao"|"', REPLACE(COALESCE(nome_instituicao,'ausente'), '"', '\"'), '"|"sim"|',
              '"logo"|"', REPLACE(COALESCE(logo,'ausente'), '"', '\"'), '"|"sim"|',
              '"sigla"|"', REPLACE(COALESCE(sigla,'ausente'), '"', '\"'), '"|"sim"|',
              GROUP_CONCAT(
                  DISTINCT CASE WHEN COALESCE(tc.mostra_valor,'nao') = 'sim' THEN  CONCAT(
                        '"nome_campo"|"', REPLACE(COALESCE(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),""), '"' 

		) ELSE '' END
                  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
GROUP BY
    id_chave_curso,
    id_chave_instituicao,
    nome_instituicao,
    logo,
    sigla;


