-- =====================================================
-- 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 2 - Média por disciplina
SELECT
    d.nome AS disciplina,
    ROUND(AVG(n.nota), 2) AS media_disciplina
FROM notas n
JOIN disciplinas d ON n.id_disciplina = d.id_disciplina
GROUP BY d.id_disciplina
ORDER BY d.nome;