db_certusone

-- --------------- CREATE DATABASE db_certusone -- ---------------

DROP DATABASE IF EXISTS db_certusone;
CREATE DATABASE db_certusone;
USE db_certusone;
-- --------------- CREATE TABLES -- ---------------

-- [1] TABLE roles
CREATE TABLE roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL,
    role_description TEXT
);


-- [2] TABLE permissions
CREATE TABLE permissions (
    permission_id INT AUTO_INCREMENT PRIMARY KEY,
    permission_name VARCHAR(50) NOT NULL,
    permission_description TEXT
);


-- [3] Tabla roles_permissions
CREATE TABLE roles_permissions (
    role_permission_id INT AUTO_INCREMENT PRIMARY KEY,
    id_role INT, -- FK
    id_permission INT, -- FK

    --  Claves foráneas:
    FOREIGN KEY (id_role) REFERENCES roles(role_id),
    FOREIGN KEY (id_permission) REFERENCES permissions(permission_id)
);


-- [4] TABLE users
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    id_role INT,-- FK
    user_name VARCHAR(100) NOT NULL,
    user_last_name VARCHAR(100) NOT NULL,
    user_email VARCHAR(100) NOT NULL,
    user_password VARCHAR(200) NOT NULL,
    user_created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    user_updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    user_status BOOLEAN DEFAULT TRUE,

    -- Claves foráneas:
    FOREIGN KEY (id_role) REFERENCES roles(role_id)
);


-- [5] TABLE user_profile
CREATE TABLE user_profile (
    profile_id INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT UNIQUE,
    profile_biography VARCHAR(200),
    profile_interests VARCHAR(200),
    profile_social_links JSON,
    profile_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    profile_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,


    -- Claves foráneas
    FOREIGN KEY (id_user) REFERENCES users(user_id)
);


-- [6] TABLE mentor
CREATE TABLE mentor (
    mentor_id INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT,
    average_rating DECIMAL(3,2),
    is_available BOOLEAN DEFAULT TRUE,
    mentor_status ENUM('pending_review', 'approved', 'rejected') DEFAULT 'pending_review',
    approved_by_admin_id INT, -- quien revisó
    admin_notes TEXT,
    mentor_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    mentor_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Claves foráneas
    FOREIGN KEY (id_user) REFERENCES users(user_id)
);



-- [7] TABLE mentor_resources
CREATE TABLE mentor_resources (
    resource_id INT AUTO_INCREMENT PRIMARY KEY,
    id_mentor INT,
    resource_title VARCHAR(100) NOT NULL,
    resource_description TEXT,
    resource_type ENUM('pdf', 'image') NOT NULL,
    resource_file_url TEXT NOT NULL,
    resource_file_size_kb INT CHECK (resource_file_size_kb <= 50),
    resource_visibility ENUM('private', 'public', 'protected') DEFAULT 'private',
    resource_status ENUM('pending_review', 'approved', 'rejected') DEFAULT 'pending_review',
    resource_uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resource_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (id_mentor) REFERENCES mentor(mentor_id)
);


-- [8] TABLE careers
CREATE TABLE careers (
    career_id INT AUTO_INCREMENT PRIMARY KEY,
    career_name VARCHAR(100) NOT NULL,
    career_description TEXT
);


-- [9] TABLE mentor_requests
CREATE TABLE mentor_requests (
    request_id INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT NOT NULL, -- FK
    id_career INT NOT NULL, -- FK
    request_first_name VARCHAR(100) NOT NULL,
    request_last_name VARCHAR(100) NOT NULL,
    request_dni CHAR(8) UNIQUE,
    request_phone_number VARCHAR(15),
    request_gender CHAR(1),
    request_campus VARCHAR(100),
    request_institutional_email VARCHAR(100),
    request_institutional_term INT,

    request_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    request_created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    request_reviewed_at DATETIME NULL,
    request_reviewed_by INT NULL, -- getCurrentIdAdmin()

    -- Claves foráneas
    FOREIGN KEY (id_user) REFERENCES users(user_id),
    FOREIGN KEY (id_career) REFERENCES careers(career_id),
    FOREIGN KEY (request_reviewed_by) REFERENCES users(user_id)
);



-- [10] TABLE sessions
CREATE TABLE sessions (
    session_id INT AUTO_INCREMENT PRIMARY KEY,
    session_date DATE NOT NULL,
    session_start_time TIME NOT NULL,
    session_end_time TIME NOT NULL,
    session_topic VARCHAR(255),
    session_status ENUM('pending', 'done', 'canceled') DEFAULT 'pending',
    session_conection_url TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    id_user INT NOT NULL,


    -- Claves foráneas
    FOREIGN KEY (id_user) REFERENCES users(user_id)
);


-- [11] TABLE ratings
CREATE TABLE ratings (
    rating_id INT AUTO_INCREMENT PRIMARY KEY,
    id_session INT NOT NULL,
    id_user INT NOT NULL,
    rating_score TINYINT UNSIGNED CHECK (rating_score BETWEEN 1 AND 5),
    rating_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    rating_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,


    -- Claves foráneas
    FOREIGN KEY (id_session) REFERENCES sessions(session_id),
    FOREIGN KEY (id_user) REFERENCES users(user_id)
);


-- [12] TABLE categories
CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    category_parent_id INT DEFAULT NULL,
    category_body TEXT,
    category_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    category_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    category_status ENUM('active', 'hidden', 'deleted') DEFAULT 'active',


    -- Claves foráneas
    FOREIGN KEY (category_parent_id) REFERENCES categories(category_id)
);


-- [13] TABLE posts
CREATE TABLE posts (
    post_id INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT NOT NULL,
    id_category INT NOT NULL,
    post_title VARCHAR(255) NOT NULL,
    post_body TEXT NOT NULL,
    post_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    post_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    post_status ENUM('active', 'closed', 'deleted') DEFAULT 'active',


    -- Claves foráneas
    FOREIGN KEY (id_user) REFERENCES users(user_id),
    FOREIGN KEY (id_category) REFERENCES categories(category_id)
);



-- [14] TABLE comments
CREATE TABLE comments (
    comment_id INT AUTO_INCREMENT PRIMARY KEY,
    id_post INT NOT NULL,
    id_user INT NOT NULL,
    comment_body TEXT NOT NULL,
    comment_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    comment_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    comment_status ENUM('visible', 'hidden', 'deleted') DEFAULT 'visible',


    -- Claves foráneas
    FOREIGN KEY (id_post) REFERENCES posts(post_id),
    FOREIGN KEY (id_user) REFERENCES users(user_id)
);