Esquema do banco de dados

BEGIN;

CREATE TABLE IF NOT EXISTS students (
    id SERIAL, 
    name TEXT,
    init_date TIMESTAMP, -- data de ingresso no c3sl
    gender TEXT,
    skin_color TEXT, -- cor
    ethnicity TEXT, -- etnia
    grr TEXT UNIQUE,
    semester INT,
    email_ufpr TEXT,
    email_inf TEXT,
    phone TEXT,
    avg_fml_income INT, -- renda média familiar do bolsista (valor)
    email TEXT,
    is_pos BOOLEAN,
    is_capes BOOLEAN,
    pos_name TEXT,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    rg TEXT,
    "comprovanteDeMatricula" TEXT,
    ctps TEXT,
    "fotoCartao" TEXT,
    image TEXT,
    exited_at TIMESTAMP,
    is_trash BOOLEAN,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS professor (
    id SERIAL, 
    name TEXT,
    init_date TIMESTAMP, -- data de ingresso no c3sl
    gender TEXT,
    skin_color TEXT, -- cor
    ethnicity TEXT, -- etnia
    registration TEXT UNIQUE, -- número de registro de funcionário
    email TEXT,
    image TEXT,
    exited_at TIMESTAMP,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    is_trash BOOLEAN,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS technician (
    id SERIAL, 
    name TEXT,
    init_date TIMESTAMP, -- data de ingresso no c3sl
    gender TEXT,
    skin_color TEXT, -- cor
    ethnicity TEXT, -- etnia
    registration TEXT UNIQUE,
    email TEXT,
    image TEXT,
    exited_at TIMESTAMP,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    is_trash BOOLEAN,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS scholarship_type( -- tipo de bolsa
    id SERIAL,
    type TEXT UNIQUE, -- tipo de bolsa (bacharelado, mestrado, doutorado)
    category TEXT, -- categoria da bolsa (1-inicial; 2-2o ano C3SL; 3-3o ano curso; ...)
    amount INT, -- valor da bolsa
    is_national BOOLEAN, -- booleano
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    is_trash BOOLEAN,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS student_scholarships ( -- relaciona as tabelas student e scholarship_type
    id SERIAL,
    student_id INT,
    scholarship_type_id INT,
    starting_date TIMESTAMP DEFAULT now(),
    ending_date TIMESTAMP,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    FOREIGN KEY (student_id) REFERENCES students(id)  ON DELETE CASCADE,
    FOREIGN KEY (scholarship_type_id) REFERENCES scholarship_type(id) ON DELETE CASCADE,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS clt ( 
    id SERIAL, 
    name TEXT,
    init_date TIMESTAMP, -- data de ingresso no c3sl
    gender TEXT,
    skin_color TEXT,
    ethnicity TEXT,
    education_degree TEXT, -- escolaridade
    is_student BOOLEAN, 
    email TEXT,
    image TEXT,
    exited_at TIMESTAMP,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    is_trash BOOLEAN,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS projects ( 
    id SERIAL,
    name TEXT UNIQUE,
    starting_date TIMESTAMP, -- data de início do projeto
    ending_date TIMESTAMP, -- data de finalização do projeto. Valor nulo caso o projeto esteja em andamento
    status TEXT,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    is_trash BOOLEAN,
    value INT,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS documents ( -- tabela de documentos (guardados no S3)
    id SERIAL,
    name TEXT UNIQUE,
    type TEXT, -- tipo de documento (relatório, formulário, ...) 
    bucket_key TEXT UNIQUE, --nome/chave do arquivo no bucket
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    is_trash BOOLEAN,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS user_system ( 
    id SERIAL,
    name TEXT UNIQUE,
    email TEXT UNIQUE,
    password TEXT, 
    is_active BOOLEAN, -- booleano que define se o usuário está ativo
    register_date TIMESTAMP, -- data em que o cadastro foi feito
    "isEmailVerified" BOOLEAN,
    verification_date TIMESTAMP,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    last_login TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS ps_form ( -- relaciona as tabelas technician e projects
    id SERIAL,
    was_transfered BOOLEAN,
    is_email_verified BOOLEAN,
    name TEXT,
    grr TEXT,
    email TEXT,
    is_ufpr BOOLEAN,
    email_ufpr TEXT,
    email_inf TEXT,
    course TEXT,
    done_prog1 BOOLEAN,
    done_prog2 BOOLEAN,
    done_alg1 BOOLEAN,
    done_bd BOOLEAN,
    done_redes2 BOOLEAN,
    done_ihc BOOLEAN,
    semester INT,
    rg TEXT,
    motivation_letter TEXT,
    academic_background TEXT,
    curriculo TEXT,
    declaracao_nao_beneficiario TEXT,
    notes TEXT,
    ethnicity TEXT,
    skin_color TEXT,
    avg_fml_income INT,
    gender TEXT,
    is_pos BOOLEAN,
    is_capes BOOLEAN,
    pos_name TEXT,
    in_interview BOOLEAN,
    confirmed_presence BOOLEAN,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS ps ( -- relaciona as tabelas technician e projects
    id SERIAL,
    name TEXT UNIQUE,
    edital TEXT,
    starting_date TIMESTAMP, 
    ending_date TIMESTAMP, 
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS ps_relation ( -- relaciona as tabelas technician e projects
    id SERIAL,
    participant_id INT,
    ps_id INT, 
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    FOREIGN KEY (participant_id) REFERENCES ps_form(id) ON DELETE CASCADE,
    FOREIGN KEY (ps_id) REFERENCES ps(id) ON DELETE CASCADE, 
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS permissions (
    id SERIAL,
    name TEXT UNIQUE, -- nome da permissão do usuário
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS partners (
    id SERIAL,
    name TEXT UNIQUE,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    is_trash BOOLEAN,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS user_permissions ( -- relaciona as tabelas user_system e permissions
    id SERIAL, 
    id_permission INT,
    id_system_user INT,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    PRIMARY KEY (id),
    FOREIGN KEY (id_permission) REFERENCES permissions (id) ON DELETE CASCADE,
    FOREIGN KEY (id_system_user) REFERENCES user_system (id) ON DELETE CASCADE
);

-- cria as permissões para funcionar

-- Inserir a permissão 'admin' se não existir
INSERT INTO permissions (name, "createdAt", "updatedAt")
SELECT 'admin', NOW(), NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM permissions WHERE name = 'admin'
);

-- Inserir a permissão 'common' se não existir
INSERT INTO permissions (name, "createdAt", "updatedAt")
SELECT 'common', NOW(), NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM permissions WHERE name = 'common'
);

INSERT INTO permissions (name, "createdAt", "updatedAt")
SELECT 'manager', NOW(), NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM permissions WHERE name = 'manager'
);


CREATE TABLE IF NOT EXISTS partners_project (
    id SERIAL,
    partner_id INT,
    project_id INT,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    FOREIGN KEY (partner_id) REFERENCES partners(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS student_project_participation ( -- relaciona as tabelas student e projects
    id SERIAL,
    project_id INT,
    student_id INT,
    starting_date TIMESTAMP DEFAULT now(),
    ending_date TIMESTAMP,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS professor_project_participation ( -- relaciona as tabelas professor e projects
    id SERIAL,
    project_id INT,
    professor_id INT,
    starting_date TIMESTAMP DEFAULT now(),
    ending_date TIMESTAMP,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    FOREIGN KEY (professor_id) REFERENCES professor(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS technician_project_participation ( -- relaciona as tabelas technician e projects
    id SERIAL,
    project_id INT,
    technician_id INT,
    starting_date TIMESTAMP DEFAULT now(),
    ending_date TIMESTAMP,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    FOREIGN KEY (technician_id) REFERENCES technician(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS clt_project_participation ( -- relaciona as tabelas clt e projects
    id SERIAL,
    project_id INT,
    clt_id INT,
    starting_date TIMESTAMP DEFAULT now(),
    ending_date TIMESTAMP,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    FOREIGN KEY (clt_id) REFERENCES clt(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS partner_student (
    id SERIAL,
    partner_id INT,
    student_id INT,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    FOREIGN KEY (partner_id) REFERENCES partners(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS scholarhip_ps_relation (
    id SERIAL,
    ps_id INT,
    scholarship_id INT,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    FOREIGN KEY (ps_id) REFERENCES ps(id) ON DELETE CASCADE,
    FOREIGN KEY (scholarship_id) REFERENCES scholarship_type(id) ON DELETE CASCADE,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS ps_evaluation (
    id SERIAL,
    ps_name TEXT,
    general_experience TEXT,
    clearness TEXT,
    respect TEXT,
    information_clearness INT,
    information_clearness_comment TEXT,
    ease INT,
    ease_comment TEXT,
    communication INT,
    communication_comment TEXT,
    time_spent INT,
    time_spent_comment TEXT,
    transparency INT,
    transparency_comment TEXT,
    liked_most TEXT,
    improvement_sugestion TEXT,
    confusing TEXT,
    comment TEXT,
    consent BOOLEAN,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS document_permission (
    id SERIAL,
    permission_id INT,
    document_id INT,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS student_financing_project (
    id SERIAL,
    financing_project_id INT,
    student_id INT,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    FOREIGN KEY (financing_project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS logs (
  id SERIAL,
  ip TEXT,
  method TEXT,
  url TEXT,
  status_code INT,
  size INT,
  referer TEXT,
  answer_bytes INT,
  user_agent TEXT,
  "createdAt" TIMESTAMP DEFAULT now() DEFAULT now(),
  "updatedAt" TIMESTAMP DEFAULT now() DEFAULT now(),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS student_contract (
  id SERIAL,
  starting_date TIMESTAMP,
  ending_date TIMESTAMP,
  signed_date TIMESTAMP, -- data que foi assinado, se nao foi assinado ainda é nulo
  contract TEXT,
  is_trash BOOLEAN DEFAULT FALSE,
  "createdAt" TIMESTAMP DEFAULT now(),
  "updatedAt" TIMESTAMP DEFAULT now(),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS student_contract_relation (
  id SERIAL,
  student_id INT,
  contract_id INT,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (contract_id) REFERENCES student_contract(id) ON DELETE CASCADE,
  "createdAt" TIMESTAMP DEFAULT now(),
  "updatedAt" TIMESTAMP DEFAULT now(),
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS contract_amendments ( -- tabela de termos aditivos do projeto (guardados no S3)
    id SERIAL,
    name TEXT UNIQUE,
    project_ending_date DATE, -- novo prazo do projeto
    bucket_key TEXT UNIQUE, --nome/chave do arquivo no bucket
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    is_trash BOOLEAN,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS project_contract_amendments ( -- tabela de relação entre termos aditivos e projeto
    id SERIAL,
    project_id INT,
    contract_id INT UNIQUE, -- 1 : n entre projeto e contrato (cada projeto pode ter varios contratos, mas um contrato so pode ser ligado a um projeto)
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (contract_id) REFERENCES contract_amendments(id) ON DELETE CASCADE,
    "createdAt" TIMESTAMP DEFAULT now(),
    "updatedAt" TIMESTAMP DEFAULT now(),
    PRIMARY KEY (id)
);