-- =====================================================
-- PROJETO SQL - BANCO DE DADOS DE UMA ESCOLA
-- Compatível com MyCompiler (SQLite)
-- Execute uma única vez, de cima para baixo.
-- =====================================================

PRAGMA foreign_keys = ON;  -- faz o SQLite respeitar as chaves estrangeiras

-- =====================================================
-- ETAPA 2 - CRIAÇÃO DAS TABELAS
-- =====================================================

-- Áreas do conhecimento
CREATE TABLE areas (
    id_area INTEGER PRIMARY KEY AUTOINCREMENT,
    nome    TEXT NOT NULL
);

-- 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)
);

-- 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 (liga aluno + disciplina + professor + 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),
    nota          REAL    NOT NULL CHECK (nota BETWEEN 0 AND 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 - CADASTRO DAS ÁREAS
-- =====================================================

INSERT INTO areas (nome) VALUES
    ('Linguagens e suas Tecnologias'),           -- id 1
    ('Matemática e suas Tecnologias'),           -- id 2
    ('Ciências da Natureza e suas Tecnologias'), -- id 3
    ('Ciências Humanas e Sociais Aplicadas');    -- id 4


-- =====================================================
-- ETAPA 4 - DISCIPLINAS, PROFESSORES, TURMAS E ALUNOS
-- Ordem: areas -> disciplinas -> professores -> turmas -> alunos
-- (quem recebe chave estrangeira vem depois de quem a fornece)
-- =====================================================

-- Disciplinas (id_area conforme a tabela areas)
INSERT INTO disciplinas (nome, id_area) VALUES
    ('Língua Portuguesa', 1),  -- id 1
    ('Língua Inglesa',    1),  -- id 2
    ('Matemática',        2),  -- id 3
    ('Biologia',          3),  -- id 4
    ('Física',            3),  -- id 5
    ('História',          4),  -- id 6
    ('Geografia',         4),  -- id 7
    ('Sociologia',        4);  -- id 8

-- Professores (dados fictícios)
INSERT INTO professores (nome, email) VALUES
    ('Ana Souza',    'ana.souza@escola.fake'),     -- id 1 (Linguagens)
    ('Carlos Lima',  'carlos.lima@escola.fake'),   -- id 2 (Matemática)
    ('Marina Alves', 'marina.alves@escola.fake'),  -- id 3 (Natureza)
    ('Roberto Dias', 'roberto.dias@escola.fake');  -- id 4 (Humanas)

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

-- Alunos (dados fictícios)
INSERT INTO alunos (nome, id_turma) VALUES
    ('Bruno Martins',  1),  -- id 1
    ('Carla Ferreira', 1),  -- id 2
    ('Diego Rocha',    2),  -- id 3
    ('Elisa Nunes',    2),  -- id 4
    ('Felipe Costa',   3),  -- id 5
    ('Gabriela Pires', 3);  -- id 6

-- Conferências

-- =====================================================
-- ETAPA 5 - REGISTRO DAS NOTAS
-- Colunas: id_aluno, id_disciplina, id_professor, bimestre, nota
-- =====================================================

INSERT INTO notas (id_aluno, id_disciplina, id_professor, bimestre, nota) VALUES
    (1, 1, 1, 1, 8.5),
    (1, 1, 1, 2, 7.0),
    (1, 3, 2, 1, 5.5),
    (1, 3, 2, 2, 6.5),
    (1, 4, 3, 1, 9.0),
    (2, 1, 1, 1, 4.5),
    (2, 1, 1, 2, 6.0),
    (2, 3, 2, 1, 7.5),
    (2, 6, 4, 1, 8.0),
    (3, 2, 1, 1, 6.5),
    (3, 2, 1, 2, 7.0),
    (3, 5, 3, 1, 5.0),
    (3, 7, 4, 1, 9.5),
    (4, 3, 2, 3, 6.0),
    (4, 4, 3, 3, 4.0),
    (4, 8, 4, 3, 7.5),
    (5, 1, 1, 4, 9.0),
    (5, 5, 3, 4, 8.0),
    (5, 6, 4, 4, 6.5),
    (6, 3, 2, 4, 10.0),
    (6, 8, 4, 4, 5.5);


-- PRINT 3 - Relatório acadêmico final
SELECT
    a.nome  AS aluno,
    t.nome  AS turma,
    d.nome  AS disciplina,
    ar.nome AS area,
    ROUND(AVG(n.nota), 2) AS media,
    COUNT(n.id_nota)      AS qtd_notas,
    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;