-- ==========================================
-- BANCO DE DADOS ESCOLA - PROJETO SQL
-- Alunos: Miguel Gomes Puppio e Giulia Vieira Reis
-- Senac São José dos Campos - 3º ano Ensino Médio Técnico em Informática
-- Professor: Lucas
-- ==========================================

-- ETAPA 1 - Planejamento (sem código SQL)
-- Tabelas: areas, disciplinas, professores, turmas, alunos e notas.
-- Relacionamentos:
--   disciplinas.id_area      -> areas.id_area
--   alunos.id_turma          -> turmas.id_turma
--   notas.id_aluno           -> alunos.id_aluno
--   notas.id_disciplina      -> disciplinas.id_disciplina
--   notas.id_professor       -> professores.id_professor

-- ------------------------------------------
-- Limpeza: permite executar o arquivo várias vezes sem duplicar dados.
-- A ordem é inversa à da criação (primeiro as tabelas que dependem das outras).
-- ------------------------------------------
DROP TABLE IF EXISTS notas;
DROP TABLE IF EXISTS alunos;
DROP TABLE IF EXISTS turmas;
DROP TABLE IF EXISTS professores;
DROP TABLE IF EXISTS disciplinas;
DROP TABLE IF EXISTS areas;

-- ==========================================
-- ETAPA 2 - Criando as tabelas
-- ==========================================

-- Áreas do conhecimento (as tabelas sem chave estrangeira são criadas primeiro)
CREATE TABLE areas (
    id_area INTEGER PRIMARY KEY AUTOINCREMENT,  -- identificador único da área
    nome TEXT NOT NULL                          -- nome da área (obrigatório)
);

-- Disciplinas: cada uma pertence a uma área
CREATE TABLE disciplinas (
    id_disciplina INTEGER PRIMARY KEY AUTOINCREMENT,
    nome TEXT NOT NULL,
    id_area INTEGER NOT NULL,
    FOREIGN KEY (id_area) REFERENCES areas (id_area)  -- liga a disciplina à área
);

-- Professores
CREATE TABLE professores (
    id_professor INTEGER PRIMARY KEY AUTOINCREMENT,
    nome TEXT NOT NULL,
    email TEXT NOT NULL
);

-- Turmas
CREATE TABLE turmas (
    id_turma INTEGER PRIMARY KEY AUTOINCREMENT,
    nome TEXT NOT NULL,
    ano_escolar INTEGER NOT NULL
);

-- Alunos: cada aluno pertence a uma turma
CREATE TABLE alunos (
    id_aluno INTEGER PRIMARY KEY AUTOINCREMENT,
    nome TEXT NOT NULL,
    id_turma INTEGER NOT NULL,
    FOREIGN KEY (id_turma) REFERENCES turmas (id_turma)
);

-- Notas: cada nota pertence a um aluno, uma disciplina, um professor e um bimestre
CREATE TABLE notas (
    id_nota INTEGER PRIMARY KEY AUTOINCREMENT,
    id_aluno INTEGER NOT NULL,
    id_disciplina INTEGER NOT NULL,
    id_professor INTEGER NOT NULL,
    bimestre INTEGER NOT NULL CHECK (bimestre BETWEEN 1 AND 4),  -- só aceita 1, 2, 3 ou 4
    nota REAL NOT NULL CHECK (nota >= 0 AND nota <= 10),         -- só aceita de 0 a 10
    FOREIGN KEY (id_aluno) REFERENCES alunos (id_aluno),
    FOREIGN KEY (id_disciplina) REFERENCES disciplinas (id_disciplina),
    FOREIGN KEY (id_professor) REFERENCES professores (id_professor)
);

-- ==========================================
-- ETAPA 3 - Cadastrando as áreas
-- ==========================================

-- Um único INSERT com vários registros: cada par de parênteses é uma linha.
-- As áreas vêm primeiro porque as disciplinas dependem delas (id_area).
INSERT INTO areas (nome) VALUES
('Linguagens e suas Tecnologias'),
('Matemática e suas Tecnologias'),
('Ciências da Natureza e suas Tecnologias'),
('Ciências Humanas e Sociais Aplicadas');

-- Conferência: devem aparecer as 4 áreas (IDs 1 a 4)
SELECT * FROM areas;

-- ==========================================
-- ETAPA 4 - Disciplinas, professores, turmas e alunos
-- Ordem: areas -> disciplinas -> professores -> turmas -> alunos
-- (turmas devem existir antes dos alunos, pois alunos usa id_turma)
-- ==========================================

-- Disciplinas (id_area: 1 Linguagens, 2 Matemática, 3 Natureza, 4 Humanas)
INSERT INTO disciplinas (nome, id_area) VALUES
('Língua Portuguesa', 1),
('Língua Inglesa', 1),
('Matemática', 2),
('Biologia', 3),
('Física', 3),
('História', 4),
('Geografia', 4),
('Sociologia', 4);

-- Conferência das disciplinas, já mostrando o nome da área
SELECT d.id_disciplina, d.nome AS disciplina, d.id_area, ar.nome AS area
FROM disciplinas d
JOIN areas ar ON d.id_area = ar.id_area;

-- Professores (1 Linguagens, 2 Matemática, 3 Ciências da Natureza, 4 Humanas)
INSERT INTO professores (nome, email) VALUES
('Carlos Silva', 'carlos.silva@escola.com'),
('Ana Souza', 'ana.souza@escola.com'),
('Marcos Oliveira', 'marcos.oliveira@escola.com'),
('Juliana Costa', 'juliana.costa@escola.com');

-- Conferência dos professores
SELECT * FROM professores;

-- Turmas
INSERT INTO turmas (nome, ano_escolar) VALUES
('1º Ano A', 1),
('2º Ano A', 2),
('3º Ano A', 3);

-- Conferência das turmas
SELECT * FROM turmas;

-- Alunos (id_turma: 1 = 1º Ano A, 2 = 2º Ano A, 3 = 3º Ano A)
INSERT INTO alunos (nome, id_turma) VALUES
('João Pedro', 1),
('Maria Clara', 1),
('Lucas Santos', 2),
('Beatriz Lima', 2),
('Enzo Ribeiro', 3),
('Nicoly Silva', 3);

-- Conferência dos alunos, já mostrando o nome da turma
SELECT al.id_aluno, al.nome AS aluno, al.id_turma, t.nome AS turma
FROM alunos al
JOIN turmas t ON al.id_turma = t.id_turma;

-- ==========================================
-- ETAPA 5 - Registrando as notas
-- Campos: (id_aluno, id_disciplina, id_professor, bimestre, nota)
-- Há notas abaixo de 6, notas de 7 ou mais, e alguns alunos têm mais de uma
-- nota na mesma disciplina (bimestres diferentes), o que permite conferir médias.
-- ==========================================
INSERT INTO notas (id_aluno, id_disciplina, id_professor, bimestre, nota) VALUES
-- 18 notas originais
(1, 1, 1, 1, 8.5),
(1, 3, 2, 1, 5.0),
(2, 4, 3, 1, 9.0),
(2, 6, 4, 1, 7.5),
(3, 1, 1, 2, 4.5),
(3, 3, 2, 2, 6.0),
(4, 5, 3, 2, 8.0),
(4, 7, 4, 2, 9.5),
(5, 8, 4, 3, 10.0),
(5, 2, 1, 3, 7.0),
(6, 3, 2, 3, 3.5),
(6, 4, 3, 3, 6.5),
(1, 6, 4, 4, 8.0),
(2, 1, 1, 4, 9.5),
(3, 5, 3, 4, 5.5),
(4, 8, 4, 4, 7.0),
(5, 3, 2, 4, 8.5),
(6, 7, 4, 4, 9.0),
-- notas extras (mesmo aluno e disciplina em outros bimestres)
(1, 1, 1, 2, 7.0),   -- João Pedro, Língua Portuguesa, 2º bimestre
(1, 1, 1, 3, 6.5),   -- João Pedro, Língua Portuguesa, 3º bimestre
(1, 3, 2, 2, 6.0),   -- João Pedro, Matemática, 2º bimestre
(3, 3, 2, 3, 7.5),   -- Lucas Santos, Matemática, 3º bimestre
(2, 4, 3, 2, 8.5),   -- Maria Clara, Biologia, 2º bimestre
(6, 3, 2, 4, 5.5);   -- Nicoly Silva, Matemática, 4º bimestre

-- Conferência: mostra todas as notas (24 registros)
-- id_nota = identificador da nota | id_aluno = quem tirou | id_disciplina = em qual matéria
-- id_professor = quem lançou | bimestre = período (1 a 4) | nota = valor de 0 a 10
SELECT * FROM notas;

-- ==========================================
-- ETAPA 6 - Relatório com JOIN
-- JOIN combina tabelas; ON diz qual campo liga uma tabela à outra.
-- A área aparece abreviada (CASE) apenas para a saída caber na tela do MyCompiler.
-- ==========================================
SELECT
    a.nome  AS Aluno,
    t.nome  AS Turma,
    d.nome  AS Disciplina,
    CASE ar.id_area
        WHEN 1 THEN 'Linguagens'
        WHEN 2 THEN 'Matemática'
        WHEN 3 THEN 'Natureza'
        ELSE 'Humanas'
    END AS Area_Conhecimento,
    p.nome  AS Professor,
    n.bimestre AS Bim,
    n.nota  AS Nota
FROM notas n
JOIN alunos a        ON n.id_aluno = a.id_aluno          -- nota -> aluno
JOIN turmas t        ON a.id_turma = t.id_turma          -- aluno -> turma
JOIN disciplinas d   ON n.id_disciplina = d.id_disciplina -- nota -> disciplina
JOIN areas ar        ON d.id_area = ar.id_area           -- disciplina -> área
JOIN professores p   ON n.id_professor = p.id_professor  -- nota -> professor
ORDER BY a.nome, d.nome, n.bimestre;

-- ==========================================
-- ETAPA 7 - Filtros com WHERE
-- WHERE filtra linhas; ORDER BY ordena o resultado.
-- ==========================================

-- (1) Notas do primeiro bimestre (com nomes em vez de apenas IDs)
SELECT a.nome AS Aluno, d.nome AS Disciplina, n.bimestre AS Bimestre, n.nota AS Nota
FROM notas n
JOIN alunos a      ON n.id_aluno = a.id_aluno
JOIN disciplinas d ON n.id_disciplina = d.id_disciplina
WHERE n.bimestre = 1;

-- (2) Alunos com notas abaixo de 6 (da menor para a maior nota)
SELECT a.nome AS Aluno, d.nome AS Disciplina, n.bimestre AS Bimestre, n.nota AS Nota
FROM notas n
JOIN alunos a      ON n.id_aluno = a.id_aluno
JOIN disciplinas d ON n.id_disciplina = d.id_disciplina
WHERE n.nota < 6
ORDER BY n.nota;

-- (3) Notas entre 7 e 10 em ordem decrescente (BETWEEN inclui 7 e 10)
SELECT a.nome AS Aluno, d.nome AS Disciplina, n.bimestre AS Bimestre, n.nota AS Nota
FROM notas n
JOIN alunos a      ON n.id_aluno = a.id_aluno
JOIN disciplinas d ON n.id_disciplina = d.id_disciplina
WHERE n.nota BETWEEN 7 AND 10
ORDER BY n.nota DESC;

-- ==========================================
-- ETAPA 8 - Médias com AVG e GROUP BY
-- AVG é uma função de agregação que calcula a média; GROUP BY agrupa as linhas.
-- ROUND(..., 2) deixa a média com duas casas decimais.
-- Atenção: só entram no cálculo as notas que foram cadastradas.
-- ==========================================

-- Média geral de cada disciplina
SELECT d.nome AS Disciplina, ROUND(AVG(n.nota), 2) AS Media_Geral
FROM notas n
JOIN disciplinas d ON n.id_disciplina = d.id_disciplina
GROUP BY d.id_disciplina
ORDER BY d.nome;

-- Média de cada aluno em cada disciplina
SELECT a.nome AS Aluno, d.nome AS Disciplina, ROUND(AVG(n.nota), 2) AS Media
FROM notas n
JOIN alunos a      ON n.id_aluno = a.id_aluno
JOIN disciplinas d ON n.id_disciplina = d.id_disciplina
GROUP BY a.id_aluno, d.id_disciplina
ORDER BY a.nome, d.nome;

-- Conferência manual: João Pedro em Língua Portuguesa tem 3 notas
-- (8,5 + 7,0 + 6,5) / 3 = 7,33

-- ==========================================
-- ETAPA 9 - Relatório acadêmico final
-- AVG = média das notas | COUNT = quantas notas foram consideradas
-- CASE = define a situação: média >= 6 é Aprovado, senão Recuperação.
-- A área aparece abreviada (CASE) apenas para a saída caber na tela.
-- Limitação: se faltarem notas de alguns bimestres, a média é apenas parcial
-- (considera só as notas cadastradas), então a situação pode mudar quando
-- as demais notas forem lançadas.
-- ==========================================
SELECT
    a.nome  AS Aluno,
    t.nome  AS Turma,
    d.nome  AS Disciplina,
    CASE ar.id_area
        WHEN 1 THEN 'Linguagens'
        WHEN 2 THEN 'Matemática'
        WHEN 3 THEN 'Natureza'
        ELSE 'Humanas'
    END AS Area,
    ROUND(AVG(n.nota), 2) AS Media,
    COUNT(n.id_nota)      AS Qtd,
    CASE
        WHEN AVG(n.nota) >= 6 THEN 'Aprovado'
        ELSE 'Recuperação'
    END AS Situacao
FROM notas n
JOIN alunos a      ON n.id_aluno = a.id_aluno
JOIN turmas t      ON a.id_turma = t.id_turma
JOIN disciplinas d ON n.id_disciplina = d.id_disciplina
JOIN areas ar      ON d.id_area = ar.id_area
GROUP BY a.id_aluno, d.id_disciplina
ORDER BY a.nome, d.nome;