CREATE DATABASE IF NOT EXISTS m2mdb;
USE m2mdb;

-- =============================
--  Tabla de logs
-- =============================
CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(100) NOT NULL,
    entity VARCHAR(50) NOT NULL,
    entity_id INT NOT NULL,
    description TEXT DEFAULT NULL,
    old_data TEXT,
    new_data TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- =============================
--  Tabla de roles
-- =============================
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO roles(name) VALUES
('Administrador'),('Project Manager'),('Stack Holder');

-- =============================
-- 1. Tabla de usuarios
-- =============================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    username VARCHAR(150) NOT NULL UNIQUE COLLATE utf8mb4_bin,
    email VARCHAR(200) NOT NULL UNIQUE COLLATE utf8mb4_bin,
    password VARCHAR(255) NOT NULL,
    isActive TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    role_id INT NOT NULL,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

-- =============================
-- 1. Tabla de clientes
-- =============================
CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name_contact VARCHAR(50) NOT NULL,
    name_company VARCHAR(50) NOT NULL,
    phone_contact VARCHAR(10) NOT NULL UNIQUE COLLATE utf8mb4_bin,
    email_contact VARCHAR(100) NULL UNIQUE COLLATE utf8mb4_bin,
    isActive TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO clients(name_contact, name_company, phone_contact, email_contact) VALUES
('Daniel Marquez', 'Fibremex S.A de C.V', '8711121958', 'daniel@fibremex.mx'),
('Jose Antonio Perez', 'Sefinsa S.A de C.V', '8714196993', 'jose@sefinsa.mx');

-- =============================
-- 2. Tabla de proyectos
-- =============================
CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    status ENUM('pendiente', 'en_progreso', 'completado', 'cancelado') DEFAULT 'pendiente',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    priority ENUM('baja', 'media', 'alta') DEFAULT 'baja',
    budget DECIMAL(15, 2) DEFAULT 0.00,
    current_cost DECIMAL(15,2) DEFAULT 0.00,
    isActive TINYINT(1) NOT NULL DEFAULT 1,

    user_created INT,
    FOREIGN KEY (user_created) REFERENCES users(id) ON DELETE CASCADE,
    client_id INT,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

CREATE TABLE user_project (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    project_id INT NOT NULL,
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,

    UNIQUE(user_id, project_id) -- evita duplicados
);

-- =============================
-- 3. Tabla de procesos / etapas / sub procesos (jerarquía)
-- =============================
CREATE TABLE stages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT DEFAULT NULL,
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('abierto', 'cerrado') DEFAULT 'abierto',
    status_traffic_light ENUM('verde', 'amarillo', 'rojo', 'gris') DEFAULT 'gris',
    deletable BOOLEAN NOT NULL DEFAULT TRUE,
    
    project_id INT NOT NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,

    parent_id INT DEFAULT NULL,
    FOREIGN KEY (parent_id) REFERENCES stages(id) ON DELETE CASCADE
);

-- =============================
-- 4. Documentos adjuntos a procesos
-- =============================
CREATE TABLE evidence (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_path VARCHAR(255) NOT NULL,
    file_name VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    name_document VARCHAR(255) DEFAULT NULL,
    description_document TEXT DEFAULT NULL,

    stage_id INT NOT NULL,
    FOREIGN KEY (stage_id) REFERENCES stages(id) ON DELETE CASCADE,
    uploaded_by INT NOT NULL,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE
);

-- =============================
-- 5. Documentos temporales (pendientes de revisión)
-- =============================
CREATE TABLE temporary_evidence (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_path VARCHAR(255) NOT NULL,
    file_name VARCHAR(255),
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    review TINYINT(1) NOT NULL DEFAULT 0,
    name_document VARCHAR(255) DEFAULT NULL,
    description_document TEXT DEFAULT NULL,
    
    stage_id INT NOT NULL,
    FOREIGN KEY (stage_id) REFERENCES stages(id) ON DELETE CASCADE,
    uploaded_by INT NOT NULL,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE protected_urls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    token VARCHAR(255) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    resource_id INT NOT NULL UNIQUE,
    FOREIGN KEY (resource_id) REFERENCES projects(id) ON DELETE CASCADE
);