-- ################################################## --
                            -- ##################### TABELAS #################### --
                            -- ################################################## --


CREATE TABLE ALUNO
(
    COD_ALUNO INT NOT NULL PRIMARY KEY,
    NOME VARCHAR(30) NOT NULL,
    CPF VARCHAR(11),
    DATA_NASCIMENTO DATE NOT NULL,
    EMAIL VARCHAR(30) NOT NULL,
    SENHA VARCHAR(30) NOT NULL,
    SALDO FLOAT DEFAULT 0
);

CREATE TABLE PROFESSOR
(
    COD_PROFESSOR INT NOT NULL PRIMARY KEY,
    NOME VARCHAR(30) NOT NULL,
    CPF VARCHAR(11) NOT NULL,
    DATA_NASCIMENTO DATE NOT NULL,
    EMAIL VARCHAR(30) NOT NULL,
    SENHA VARCHAR(30) NOT NULL,
    SALDO FLOAT DEFAULT 0,
    DATA_ULTIMO_PAGAMENTO DATE DEFAULT '2000-01-01'
);

CREATE TABLE CURSO
(
    COD_CURSO INT NOT NULL PRIMARY KEY,
    NOME VARCHAR(60) NOT NULL,
    DESCRICAO VARCHAR(300),
    DURACAO INT DEFAULT 0,
    PRECO FLOAT,
    NUMERO_MODULOS INT DEFAULT 0,
    PUBLICADO BOOLEAN DEFAULT FALSE,
    DISPONIBILIDADE BOOLEAN DEFAULT FALSE,
    COD_PROFESSOR INT NOT NULL REFERENCES PROFESSOR(COD_PROFESSOR) ON DELETE CASCADE
);

CREATE TABLE ALUNO_CURSO
(
    COD_ALUNO_CURSO SERIAL PRIMARY KEY,
    DATA_COMPRA DATE,
    NOTA_AVALIACAO FLOAT,
    COD_ALUNO INT NOT NULL REFERENCES ALUNO(COD_ALUNO) ON DELETE CASCADE,
    COD_CURSO INT NOT NULL REFERENCES CURSO(COD_CURSO) ON DELETE CASCADE
);

CREATE TABLE MODULO
(
    COD_MODULO SERIAL PRIMARY KEY,
    NOME VARCHAR(100),
    DESCRICAO VARCHAR(300),
    DURACAO INT,
    STATUS BOOLEAN DEFAULT FALSE,
    COD_CURSO INT NOT NULL REFERENCES CURSO(COD_CURSO) ON DELETE CASCADE
);

CREATE TABLE PRE_REQUESITO
(
    COD_PRE_REQUESITO INT NOT NULL PRIMARY KEY,
    COD_MODULO INT NOT NULL REFERENCES MODULO(COD_MODULO) ON DELETE CASCADE
);

CREATE TABLE DISCIPLINA
(
    COD_DISCIPLINA SERIAL PRIMARY KEY,
    NOME VARCHAR(100),
    DESCRICAO VARCHAR(300),
    COD_MODULO INT NOT NULL REFERENCES MODULO(COD_MODULO) ON DELETE CASCADE
    -- TEM QUE TIRAR
    COD_PROFESSOR INT NOT NULL REFERENCES PROFESSOR(COD_PROFESSOR) ON DELETE CASCADE
    -- TEM QUE TIRAR

);

CREATE TABLE VIDEO_AULA
(
    COD_VIDEO_AULA SERIAL PRIMARY KEY,
    NOME VARCHAR(30) NOT NULL,
    DESCRICAO VARCHAR(300),
    DURACAO FLOAT,
    COD_DISCIPLINA INT NOT NULL REFERENCES DISCIPLINA(COD_DISCIPLINA) ON DELETE CASCADE
);

CREATE TABLE ALUNO_VIDEOS_ASSISTIDOS
(
    COD_ALUNO_VIDEO_ASSISTIDO SERIAL PRIMARY KEY,
    COD_ALUNO INT NOT NULL REFERENCES ALUNO(COD_ALUNO) ON DELETE CASCADE,
    COD_VIDEO_AULA INT NOT NULL REFERENCES VIDEO_AULA(COD_VIDEO_AULA) ON DELETE CASCADE
);

CREATE TABLE QUESTAO
(
    COD_QUESTAO INT NOT NULL PRIMARY KEY,
    TEXTO VARCHAR(500),
    COD_DISCIPLINA INT NOT NULL REFERENCES DISCIPLINA(COD_DISCIPLINA) ON DELETE CASCADE
);

CREATE TABLE QUESTIONARIO
(
    COD_QUESTIONARIO INT NOT NULL PRIMARY KEY,
    NOME VARCHAR(30),
    COD_DISCIPLINA INT NOT NULL REFERENCES DISCIPLINA(COD_DISCIPLINA) ON DELETE CASCADE
);

CREATE TABLE QUESTAO_QUESTIONARIO
(
    COD_QUESTAO_QUESTIONARIO INT NOT NULL PRIMARY KEY,
    COD_QUESTAO INT NOT NULL REFERENCES QUESTAO(COD_QUESTAO) ON DELETE CASCADE,
    COD_QUESTIONARIO INT NOT NULL REFERENCES QUESTIONARIO(COD_QUESTIONARIO) ON DELETE CASCADE
);

CREATE TABLE QUESTAO_ALUNO
(
    COD_QUESTAO_ALUNO INT NOT NULL PRIMARY KEY,
    RESPOSTAR_ALUNO VARCHAR(500),
    RESPOSTA_CORRETA BOOLEAN
);