CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE faculties (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE documents (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL,
    faculty_id INT NOT NULL,
    category_id INT NOT NULL,
    code_document VARCHAR(255) UNIQUE,
    status ENUM('pending', 'registered') NOT NULL DEFAULT 'pending',
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    duration_unit ENUM('days', 'weeks', 'months', 'years') NOT NULL,
    duration_value INT NOT NULL,
    CONSTRAINT fk_documents_faculties FOREIGN KEY (faculty_id) REFERENCES faculties(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_documents_categories FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE professors (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    professor_code VARCHAR(255) NOT NULL UNIQUE,
    faculty_id INT NOT NULL,
    CONSTRAINT fk_professors_faculties FOREIGN KEY (faculty_id) REFERENCES faculties(id) ON DELETE RESTRICT ON UPDATE CASCADE 
);

CREATE TABLE document_responsibles (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    document_id INT NOT NULL,
    professor_id INT NOT NULL,
    CONSTRAINT fk_document_responsibles_documents FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_document_responsibles_professors FOREIGN KEY (professor_id) REFERENCES professors(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    personal_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    faculty_id INT NOT NULL,
    CONSTRAINT fk_estudents_faculties FOREIGN KEY (faculty_id) REFERENCES faculties(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE collaborators (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    collaborator_type ENUM('professor', 'estudent', 'both'),
    student_id INT NULL,
    professor_id INT NOT NULL,
    CONSTRAINT fk_collaborators_estudents FOREIGN KEY (estudent_id) REFERENCES estudents(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_collaborators_professors FOREIGN KEY (professor_id) REFERENCES professors(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE documents_collaborators (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    document_id INT NOT NULL,
    collaborator_id INT NOT NULL,
    CONSTRAINT fk_documents_collaborators_documents FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_documents_collaborators_collaborators FOREIGN KEY (collaborator_id) REFERENCES collaborators(id) ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE documents_audits (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    document_id INT NOT NULL,
    modified_field VARCHAR(255) NOT NULL,
    old_value TEXT NOT NULL,
    new_value TEXT NOT NULL,
    modified_by INT NOT NULL,
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT fk_documents_audits_documents FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_documents_audits_users FOREIGN KEY (modified_by) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE location_documents (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    document_id INT NOT NULL,
    location_type ENUM('physical', 'digital') NOT NULL,
    path_or_description TEXT NOT NULL,
    CONSTRAINT fk_location_documents_documents FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE document_history (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    document_id INT NOT NULL,
    action ENUM('created', 'updated', 'deleted') NOT NULL,
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    user_id INT NOT NULL,
    note TEXT NOT NULL,
    CONSTRAINT fk_document_history_documents FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_document_history_users FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE
);
