db_mk_certus

-- =================================================================
-- CREACIÓN DE LA BASE DE DATOS
-- =================================================================
DROP DATABASE IF EXISTS db_mk_certus;
CREATE DATABASE db_mk_certus;
USE db_mk_certus;

-- =================================================================
-- TABLA [1]: roles
-- Almacena los diferentes roles de usuario (ej. Administrador, Vendedor, Cliente).
-- =================================================================
CREATE TABLE roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    role_description TEXT
);

-- =================================================================
-- TABLA [2]: permissions
-- Almacena permisos específicos que se pueden asignar a los roles.
-- =================================================================
CREATE TABLE permissions (
    permission_id INT AUTO_INCREMENT PRIMARY KEY,
    permission_name VARCHAR(50) NOT NULL UNIQUE,
    permission_description TEXT
);

-- =================================================================
-- TABLA [3]: roles_permissions (Pivote)
-- Tabla intermedia para una relación Muchos a Muchos entre roles y permisos.
-- =================================================================
CREATE TABLE roles_permissions (
    role_permission_id INT AUTO_INCREMENT PRIMARY KEY,
    id_role INT,
    id_permission INT,

    FOREIGN KEY (id_role) REFERENCES roles(role_id) ON DELETE CASCADE,
    FOREIGN KEY (id_permission) REFERENCES permissions(permission_id) ON DELETE CASCADE
);

-- =================================================================
-- TABLA [4]: users
-- Almacena la información de autenticación de los usuarios.
-- =================================================================
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    -- CORRECCIÓN: Se elimina id_role para evitar redundancia. La relación se maneja en 'users_roles'.
    user_email VARCHAR(100) NOT NULL UNIQUE,
    user_password VARCHAR(255) NOT NULL, -- Aumentado para hashes de contraseñas seguras
    user_created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    user_updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    user_status BOOLEAN DEFAULT TRUE
);

-- =================================================================
-- TABLA [5]: category_career
-- Almacena las categorías de los trabajos o proyectos (ej. Diseño Gráfico, Programación).
-- =================================================================
CREATE TABLE category_career (
    category_career_id INT AUTO_INCREMENT PRIMARY KEY,
    category_career_name VARCHAR(100) NOT NULL,
    category_career_description VARCHAR(255) -- Aumentado para descripciones más largas
);

-- =================================================================
-- TABLA [6]: people
-- Almacena los datos personales de los usuarios, vinculados a una cuenta.
-- =================================================================
CREATE TABLE people (
    person_id INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT NOT NULL UNIQUE, -- Un perfil por usuario
    person_name VARCHAR(100) NOT NULL,
    person_lastname VARCHAR(100) NOT NULL,
    person_dni CHAR(8) UNIQUE,
    person_mobile_phone VARCHAR(15),
    person_gender CHAR(1),
    person_institute_campus VARCHAR(100),
    person_institutional_email VARCHAR(100) UNIQUE,
    person_institutional_career VARCHAR(100),
    person_current_term INT,

    FOREIGN KEY (id_user) REFERENCES users(user_id) ON DELETE CASCADE
);

-- =================================================================
-- TABLA [7]: works
-- Almacena los trabajos o servicios ofrecidos por los vendedores.
-- =================================================================
CREATE TABLE works (
    work_id INT AUTO_INCREMENT PRIMARY KEY,
    id_seller_user INT NOT NULL,
    id_work_category INT,
    work_title VARCHAR(150) NOT NULL,
    work_description TEXT,
    work_price DECIMAL(10,2) NOT NULL,
    work_image_url TEXT,
    work_published_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    work_updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -- CORRECCIÓN: El valor por defecto debe existir en la lista ENUM.
    work_status ENUM('PUBLICADO', 'EN REVISIÓN', 'RECHAZADO') DEFAULT 'EN REVISIÓN',
    work_is_deleted BOOLEAN NOT NULL DEFAULT FALSE,

    FOREIGN KEY (id_seller_user) REFERENCES users(user_id),
    FOREIGN KEY (id_work_category) REFERENCES category_career(category_career_id) ON DELETE SET NULL
);

-- =================================================================
-- TABLA [8]: orders
-- Cabecera de las órdenes de compra.
-- =================================================================
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT NOT NULL, -- El usuario que realiza la compra
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (id_user) REFERENCES users(user_id)
);

-- =================================================================
-- TABLA [9]: paypal_payments
-- Almacena los detalles de una transacción de pago realizada con PayPal.
-- =================================================================
CREATE TABLE paypal_payments (
    paypal_payment_id INT AUTO_INCREMENT PRIMARY KEY,
    id_internal_order INT NOT NULL,
    paypal_payment_payer_email VARCHAR(255),
    paypal_payment_status VARCHAR(50) NOT NULL,
    paypal_payment_transaction_id VARCHAR(255) UNIQUE,
    paypal_payment_amount DECIMAL(10, 2) NOT NULL,
    paypal_payment_currency CHAR(3) NOT NULL,
    paypal_payment_transaction_type VARCHAR(50),
    paypal_payment_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    paypal_payment_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (id_internal_order) REFERENCES orders(order_id)
);

-- =================================================================
-- TABLA [10]: order_details
-- Detalle de cada orden, con los trabajos comprados.
-- =================================================================
CREATE TABLE order_details (
    order_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    id_order INT NOT NULL,
    id_work INT NOT NULL,
    order_detail_quantity INT NOT NULL DEFAULT 1,
    order_detail_unit_price DECIMAL(10, 2) NOT NULL,

    FOREIGN KEY (id_order) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (id_work) REFERENCES works(work_id)
);

-- =================================================================
-- TABLA [11]: comments
-- Almacena comentarios en los trabajos, con soporte para respuestas anidadas.
-- =================================================================
CREATE TABLE comments (
    comment_id INT AUTO_INCREMENT PRIMARY KEY,
    id_work INT NOT NULL,
    id_user INT NOT NULL,
    id_parent_comment INT NULL, -- Para respuestas a otros comentarios
    comment_body TEXT NOT NULL,
    comment_is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    comment_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    comment_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (id_work) REFERENCES works(work_id) ON DELETE CASCADE,
    FOREIGN KEY (id_user) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (id_parent_comment) REFERENCES comments(comment_id) ON DELETE CASCADE
);

-- =================================================================
-- TABLA [12]: ratings
-- Almacena las calificaciones y reseñas que los compradores dan a los trabajos.
-- =================================================================
CREATE TABLE ratings (
    rating_id INT AUTO_INCREMENT PRIMARY KEY,
    id_work INT NOT NULL,
    id_user INT NOT NULL, -- Usuario que califica
    id_order INT, -- Orden asociada a la calificación
    rating_score TINYINT UNSIGNED NOT NULL CHECK (rating_score BETWEEN 1 AND 5),
    rating_comment TEXT NULL,
    rating_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (id_work) REFERENCES works(work_id) ON DELETE CASCADE,
    FOREIGN KEY (id_user) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (id_order) REFERENCES orders(order_id) ON DELETE SET NULL
);

-- =================================================================
-- TABLA [13]: social_logins
-- Vincula cuentas de usuario con proveedores de inicio de sesión social (Google, Facebook, etc.).
-- =================================================================
CREATE TABLE social_logins (
    social_login_id INT AUTO_INCREMENT PRIMARY KEY,
    -- CORRECCIÓN: El nombre de la columna debe ser consistente.
    id_user INT NOT NULL,
    social_login_provider VARCHAR(50) NOT NULL,
    social_login_provider_user_id VARCHAR(255) NOT NULL,
    social_login_linked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- CORRECCIÓN: Se usa el nombre de columna correcto 'id_user'.
    FOREIGN KEY (id_user) REFERENCES users(user_id) ON DELETE CASCADE,

    UNIQUE KEY uq_social_provider_user (social_login_provider, social_login_provider_user_id),
    -- CORRECCIÓN: Se usa el nombre de columna correcto 'id_user'.
    UNIQUE KEY uq_social_user_provider (id_user, social_login_provider)
);

-- =================================================================
-- TABLA [14]: users_roles (Pivote)
-- Tabla para la relación Muchos a Muchos entre usuarios y roles.
-- Esta es la forma correcta de asignar roles a usuarios.
-- =================================================================
CREATE TABLE users_roles (
    id_user INT NOT NULL,
    id_role INT NOT NULL,

    PRIMARY KEY (id_user, id_role),
    FOREIGN KEY (id_user) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (id_role) REFERENCES roles(role_id) ON DELETE CASCADE
);

-- =================================================================
-- FIN DEL SCRIPT
-- =================================================================