-- ============================================
-- CONSULTAS ÚTEIS - DUAL CREDIT 2026
-- ============================================

-- 1. Listar todas as disciplinas disponíveis para 2026 - 1º Semestre
SELECT 
    dcs.id,
    u.name AS universidade,
    dcs.code AS codigo,
    dcs.name AS disciplina,
    dcs.credits AS creditos,
    dcs.price_individual AS preco_avulso,
    dcs.price_hall_member AS preco_membro,
    dcs.installments_members AS parcelas,
    dcs.enrollment_deadline AS prazo_inscricao,
    dcs.start_date AS data_inicio,
    dcs.status
FROM dual_credit_subjects dcs
INNER JOIN universities u ON dcs.university_id = u.id
WHERE dcs.year = 2026 
  AND dcs.semester = '1'
  AND dcs.status = 'active'
ORDER BY u.name, dcs.code;

-- 2. Contar disciplinas por universidade
SELECT 
    u.name AS universidade,
    COUNT(*) AS total_disciplinas,
    SUM(dcs.credits) AS total_creditos
FROM dual_credit_subjects dcs
INNER JOIN universities u ON dcs.university_id = u.id
WHERE dcs.year = 2026 AND dcs.semester = '1'
GROUP BY u.name
ORDER BY total_disciplinas DESC;

-- 3. Disciplinas com pré-requisitos
SELECT 
    u.name AS universidade,
    dcs.code AS codigo,
    dcs.name AS disciplina,
    dcs.prerequisite AS pre_requisito
FROM dual_credit_subjects dcs
INNER JOIN universities u ON dcs.university_id = u.id
WHERE dcs.year = 2026 
  AND dcs.semester = '1'
  AND dcs.prerequisite IS NOT NULL
ORDER BY u.name, dcs.code;

-- 4. Disciplinas por faixa de preço
SELECT 
    CASE 
        WHEN dcs.price_individual < 3000 THEN 'Até R$ 3.000'
        WHEN dcs.price_individual < 4000 THEN 'R$ 3.000 - R$ 4.000'
        WHEN dcs.price_individual < 5000 THEN 'R$ 4.000 - R$ 5.000'
        WHEN dcs.price_individual < 6000 THEN 'R$ 5.000 - R$ 6.000'
        ELSE 'Acima de R$ 6.000'
    END AS faixa_preco,
    COUNT(*) AS quantidade
FROM dual_credit_subjects dcs
WHERE dcs.year = 2026 AND dcs.semester = '1'
GROUP BY faixa_preco
ORDER BY MIN(dcs.price_individual);

-- 5. Disciplinas mais caras e mais baratas
(SELECT 
    'Mais Cara' AS tipo,
    u.name AS universidade,
    dcs.code AS codigo,
    dcs.name AS disciplina,
    dcs.price_individual AS preco
FROM dual_credit_subjects dcs
INNER JOIN universities u ON dcs.university_id = u.id
WHERE dcs.year = 2026 AND dcs.semester = '1'
ORDER BY dcs.price_individual DESC
LIMIT 5)
UNION ALL
(SELECT 
    'Mais Barata' AS tipo,
    u.name AS universidade,
    dcs.code AS codigo,
    dcs.name AS disciplina,
    dcs.price_individual AS preco
FROM dual_credit_subjects dcs
INNER JOIN universities u ON dcs.university_id = u.id
WHERE dcs.year = 2026 AND dcs.semester = '1'
ORDER BY dcs.price_individual ASC
LIMIT 5);

-- 6. Distribuição por número de créditos
SELECT 
    dcs.credits AS creditos,
    COUNT(*) AS quantidade,
    ROUND(AVG(dcs.price_individual), 2) AS preco_medio
FROM dual_credit_subjects dcs
WHERE dcs.year = 2026 AND dcs.semester = '1'
GROUP BY dcs.credits
ORDER BY dcs.credits;

-- 7. Disciplinas com inscrições abertas (baseado na data limite)
SELECT 
    u.name AS universidade,
    dcs.code AS codigo,
    dcs.name AS disciplina,
    dcs.enrollment_deadline AS prazo_inscricao,
    DATEDIFF(dcs.enrollment_deadline, CURDATE()) AS dias_restantes
FROM dual_credit_subjects dcs
INNER JOIN universities u ON dcs.university_id = u.id
WHERE dcs.year = 2026 
  AND dcs.semester = '1'
  AND dcs.status = 'active'
  AND dcs.enrollment_deadline >= CURDATE()
ORDER BY dias_restantes ASC;

-- 8. Comparação de preços entre aluno avulso e membro de Hall
SELECT 
    u.name AS universidade,
    dcs.code AS codigo,
    dcs.name AS disciplina,
    dcs.price_individual AS preco_avulso,
    dcs.price_hall_member AS preco_membro,
    (dcs.price_individual - dcs.price_hall_member) AS economia,
    ROUND(((dcs.price_individual - dcs.price_hall_member) / dcs.price_individual * 100), 2) AS percentual_desconto
FROM dual_credit_subjects dcs
INNER JOIN universities u ON dcs.university_id = u.id
WHERE dcs.year = 2026 
  AND dcs.semester = '1'
  AND dcs.price_individual > dcs.price_hall_member
ORDER BY economia DESC;

-- 9. Estatísticas gerais por universidade
SELECT 
    u.name AS universidade,
    COUNT(*) AS total_disciplinas,
    SUM(dcs.credits) AS total_creditos,
    MIN(dcs.price_individual) AS preco_minimo,
    MAX(dcs.price_individual) AS preco_maximo,
    ROUND(AVG(dcs.price_individual), 2) AS preco_medio,
    MIN(dcs.installments_members) AS min_parcelas,
    MAX(dcs.installments_members) AS max_parcelas
FROM dual_credit_subjects dcs
INNER JOIN universities u ON dcs.university_id = u.id
WHERE dcs.year = 2026 AND dcs.semester = '1'
GROUP BY u.name
ORDER BY u.name;

-- 10. Buscar disciplinas por palavra-chave no nome
SELECT 
    u.name AS universidade,
    dcs.code AS codigo,
    dcs.name AS disciplina,
    dcs.credits AS creditos,
    dcs.price_hall_member AS preco_membro
FROM dual_credit_subjects dcs
INNER JOIN universities u ON dcs.university_id = u.id
WHERE dcs.year = 2026 
  AND dcs.semester = '1'
  AND dcs.status = 'active'
  AND (
    dcs.name LIKE '%Business%' OR
    dcs.name LIKE '%Communication%' OR
    dcs.name LIKE '%Psychology%' OR
    dcs.name LIKE '%History%'
  )
ORDER BY u.name, dcs.name;

-- 11. Disciplinas de uma universidade específica (Southwestern)
SELECT 
    dcs.code AS codigo,
    dcs.name AS disciplina,
    dcs.credits AS creditos,
    dcs.prerequisite AS pre_requisito,
    dcs.price_hall_member AS preco,
    dcs.installments_members AS parcelas
FROM dual_credit_subjects dcs
INNER JOIN universities u ON dcs.university_id = u.id
WHERE dcs.year = 2026 
  AND dcs.semester = '1'
  AND u.code = 'SWAU'
  AND dcs.status = 'active'
ORDER BY dcs.code;

-- 12. Relatório de inscrições (quando houver dados)
SELECT 
    u.name AS universidade,
    dcs.code AS codigo,
    dcs.name AS disciplina,
    dcs.max_students AS vagas_totais,
    dcs.enrolled_count AS inscritos,
    CASE 
        WHEN dcs.max_students IS NULL THEN 'Ilimitado'
        ELSE CONCAT(dcs.max_students - dcs.enrolled_count, ' vagas')
    END AS vagas_disponiveis,
    CASE 
        WHEN dcs.max_students IS NULL THEN 0
        ELSE ROUND((dcs.enrolled_count / dcs.max_students * 100), 2)
    END AS percentual_ocupacao
FROM dual_credit_subjects dcs
INNER JOIN universities u ON dcs.university_id = u.id
WHERE dcs.year = 2026 
  AND dcs.semester = '1'
  AND dcs.status = 'active'
ORDER BY percentual_ocupacao DESC;

-- 13. Disciplinas que estão lotadas
SELECT 
    u.name AS universidade,
    dcs.code AS codigo,
    dcs.name AS disciplina,
    dcs.max_students AS vagas,
    dcs.enrolled_count AS inscritos
FROM dual_credit_subjects dcs
INNER JOIN universities u ON dcs.university_id = u.id
WHERE dcs.year = 2026 
  AND dcs.semester = '1'
  AND dcs.max_students IS NOT NULL
  AND dcs.enrolled_count >= dcs.max_students
ORDER BY u.name, dcs.code;

-- 14. Valor total se um aluno se inscrever em todas as disciplinas de uma universidade
SELECT 
    u.name AS universidade,
    COUNT(*) AS total_disciplinas,
    SUM(dcs.credits) AS total_creditos,
    SUM(dcs.price_individual) AS valor_total_avulso,
    SUM(dcs.price_hall_member) AS valor_total_membro,
    SUM(dcs.price_individual) - SUM(dcs.price_hall_member) AS economia_total
FROM dual_credit_subjects dcs
INNER JOIN universities u ON dcs.university_id = u.id
WHERE dcs.year = 2026 AND dcs.semester = '1'
GROUP BY u.name
ORDER BY u.name;

-- 15. Disciplinas por área de conhecimento (baseado em padrões do código)
SELECT 
    CASE 
        WHEN dcs.code LIKE 'BIB%' OR dcs.code LIKE 'BIBL%' OR dcs.code LIKE 'BINT%' OR dcs.code LIKE 'BIOT%' OR dcs.code LIKE 'THE%' THEN 'Estudos Bíblicos/Teologia'
        WHEN dcs.code LIKE 'BUS%' OR dcs.code LIKE 'BUAD%' OR dcs.code LIKE 'ECO%' THEN 'Negócios/Economia'
        WHEN dcs.code LIKE 'COM%' OR dcs.code LIKE 'COMM%' THEN 'Comunicação'
        WHEN dcs.code LIKE 'ENG%' OR dcs.code LIKE 'ENGL%' THEN 'Inglês/Literatura'
        WHEN dcs.code LIKE 'HIS%' OR dcs.code LIKE 'HIST%' THEN 'História'
        WHEN dcs.code LIKE 'MAT%' OR dcs.code LIKE 'MATH%' THEN 'Matemática'
        WHEN dcs.code LIKE 'PSY%' OR dcs.code LIKE 'PSYC%' THEN 'Psicologia'
        WHEN dcs.code LIKE 'BIO%' OR dcs.code LIKE 'BIOL%' THEN 'Ciências Biológicas'
        WHEN dcs.code LIKE 'ART%' OR dcs.code LIKE 'ARTV%' OR dcs.code LIKE 'MUSC%' THEN 'Artes'
        WHEN dcs.code LIKE 'SOC%' OR dcs.code LIKE 'SOCI%' THEN 'Sociologia'
        WHEN dcs.code LIKE 'PHL%' OR dcs.code LIKE 'PHIL%' THEN 'Filosofia'
        ELSE 'Outras'
    END AS area_conhecimento,
    COUNT(*) AS quantidade,
    ROUND(AVG(dcs.price_hall_member), 2) AS preco_medio
FROM dual_credit_subjects dcs
WHERE dcs.year = 2026 AND dcs.semester = '1'
GROUP BY area_conhecimento
ORDER BY quantidade DESC;

