banco de dados escola

create table aluno(
    id_aluno serial not null primary key,
    nome_aluno varchar(255) not null,
    nascimento_aluno date not null,
    matricula_aluno varchar(10) not null
);

create table escola(
    id_escola serial not null primary key,
    nome varchar(255) not null,
    numero int not null,
    complemento varchar(255),
    cep varchar(8) not null
);

create table turma(
    id_turma serial not null primary key,
    ano int not null,
    sigla char(1) not null,
    e_medio bool not null,
    id_escola int not null references escola(id_escola)
);

create table aluno_turma(
    id_aluno_turma serial not null primary key,
    id_aluno int not null references aluno(id_aluno),
    id_turma int not null references turma(id_turma),
    data date default now()
);

create table professor(
    id_professor serial not null primary key,
    nascimento_professor date not null,
    cpf varchar(11) not null,
    matricula_professor varchar(10) not null
);

create table disciplina(
    id_disciplina serial not null primary key,
    descricao varchar(255) not null,
    ano int not null,
    e_medio bool not null
);

create table aula(
    id_aula serial not null primary key,
    id_turma int not null references turma(id_turma),
    id_professor int not null references professor(id_professor),
    id_disciplina int not null references disciplina(id_disciplina),
    ano_letivo int default extract(YEAR from now())
);

create table prova(
    id_prova serial not null primary key,
    id_disciplina int references disciplina(id_disciplina),
    finalizada bool default false
);


create table aplicacao(
    id_aplicacao serial not null primary key,
    id_aula int not null references aula(id_aula),
    id_prova int not null references prova(id_prova),
    data_aplicacao date not null
);

create table questao(
    id_questao serial not null primary key,
    enunciado varchar(500) not null,
    id_disciplina int not null references disciplina(id_disciplina),
    finalizada bool default false
);

create table alternativa(
    id_alternativa serial not null primary key,
    descricao varchar(255)
);

create table gabarito (
    id_gabarito serial not null primary key,
    id_prova int not null references prova(id_prova),
    id_questao int not null references questao(id_questao),
    id_alternativa int not null references alternativa(id_alternativa),
    e_certo bool not null
);

create table resposta(
    id_resposta serial not null primary key,
    id_aluno int not null references aluno(id_aluno),
    id_gabarito int not null references gabarito(id_gabarito)
);


-- TRIGGRERS

create function validate_aluno_turma() returns trigger as $$
begin
    if extract(year from new.data) in
    (select extract(year from data) from aluno_turma where new.id_aluno = id_aluno) then
        if TG_OP = 'UPDATE' then
            if extract(year from new.data) = extract(year from old.data) then
                return new;
            end if;
        end if;

        raise exception 'um aluno so pode ser matriculado uma vez por ano';
    end if;

    return new;
end;
$$ language plpgsql;

create trigger verifiar_aluno_turma before insert or update on aluno_turma
    for each row execute procedure validate_aluno_turma();

insert into aluno values(default, 'luan', '2000-09-26', '123');
insert into escola values(default, 'ifpi', 1, null, '2234');
insert into turma values(default, 1, 'a', true, 1);

-- test trigger um ano por ano
insert into aluno_turma values(default, 1, 1, now());
insert into aluno_turma values(default, 1, 1, '2019-06-5'); -- esperado error !
insert into aluno_turma values(default, 1, 1, '2018-06-5');
update aluno_turma set data='2019-05-1' where id_aluno_turma=1; -- espeardo: passar !
update aluno_turma set data='2018-12-31' where id_aluno_turma=1; -- espado: error !

-- AULA
create or replace function validate_aula() returns trigger as $$
declare
    ano_turma int;
    ano_disciplina int;
    e_medio_turma boolean;
    e_medio_disciplina boolean;
begin
    select ano, e_medio into ano_turma, e_medio_turma from turma where id_turma = new.id_turma;
    select ano, e_medio into ano_disciplina, e_medio_disciplina from disciplina where id_disciplina = new.id_disciplina;

    if ano_turma != ano_disciplina then
        raise exception 'Ano da disciplina tem que ser igual ao da turma';
    elsif e_medio_disciplina != e_medio_turma then
        raise exception 'Grau (medio / fundamental) da disciplina tem que ser igual ao da turma';
    end if;
    new.ano_letivo := ano_turma;
    return new;

end;
$$ language plpgsql;


create trigger verificar_aula before insert or update on aula
    for each row execute procedure validate_aula();

-- tests
insert into disciplina values
    (1, 'matemtaica', 2, false)
    (2, 'matemtaica', 1, false)
    (3, 'matemtaica', 1, true);

insert into professor values(1, '1993-02-10', '00132101', '123prof');

insert into aula values(1, 1, 1, 1); -- error ano;
insert into aula values(1, 1, 1, 2); -- error medio;
insert into aula values(1, 1, 1, 3); -- ok!