CREATE OR REPLACE VIEW public.vw_turma_exame AS
 SELECT tr.id,
    tr.created,
    tr.removed,
    tr.updated,
    tr.deleted_at,
    tr.data_inicial,
    tr.hora,
    tr.ativo,
    tr.turma_autorizada,
    tr.turma_inicio,
    tr.turma_final,
    tr.turma_status,
    sl.capacidade,
    sl.descricao,
    cl.cnpj AS clinica_cnpj,
    cl.codigogib,
    cl.nomefantasia AS clinica_nome,
    pr.id AS id_profissional,
    pr.tipo AS tipo_profissional,
    pr.nome AS nome_profissional,
    pr.cpf AS cpf_profissional,
    ( SELECT count(*) AS count
           FROM tb_exame
             JOIN tb_aluno al ON tb_exame.fk_aluno = al.id
          WHERE tb_exame.fk_turma = tr.id AND tb_exame.removed = false AND al.removed = false) AS qtdalunos,
    pr.crmcrp AS crp_profissional
   FROM tb_clinica_turma tr
     LEFT JOIN tb_clinica_sala sl ON tr.fk_sala = sl.id
     LEFT JOIN tb_profissional pr ON tr.fk_profissional = pr.id
     LEFT JOIN tb_clinica cl ON sl.fk_clinica = cl.id
     LEFT JOIN tb_user usr ON pr.fk_usuario = usr.id
     LEFT JOIN tb_usuario_coleta usc ON usr.fk_usuario_coleta = usc.id;

-- Permissions

ALTER TABLE public.vw_turma_exame OWNER TO postgres;