-- ============================================================
-- 01_schema.sql  –  Creación de base de datos y tablas
-- Sistema de Control de Cambios Empresariales
-- ============================================================

CREATE DATABASE IF NOT EXISTS hramirez_CCambios
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE hramirez_CCambios;

-- -----------------------------------------------------------
-- roles
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS roles (
    id    INT          NOT NULL AUTO_INCREMENT,
    nombre VARCHAR(50) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_roles_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- usuarios
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS usuarios (
    id              INT          NOT NULL AUTO_INCREMENT,
    nombre_completo VARCHAR(150) NOT NULL,
    correo          VARCHAR(150) NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    puesto          VARCHAR(100) NOT NULL,
    rol_id          INT          NOT NULL,
    activo          TINYINT(1)   NOT NULL DEFAULT 1,
    creado_en       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_usuarios_correo (correo),
    CONSTRAINT fk_usuarios_rol FOREIGN KEY (rol_id) REFERENCES roles (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- sistemas
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS sistemas (
    id          INT          NOT NULL AUTO_INCREMENT,
    nombre      VARCHAR(150) NOT NULL,
    descripcion TEXT,
    activo      TINYINT(1)   NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_sistemas_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- usuario_sistema  (N:M  usuarios <-> sistemas)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS usuario_sistema (
    id         INT NOT NULL AUTO_INCREMENT,
    usuario_id INT NOT NULL,
    sistema_id INT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_usuario_sistema (usuario_id, sistema_id),
    CONSTRAINT fk_us_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios (id),
    CONSTRAINT fk_us_sistema FOREIGN KEY (sistema_id) REFERENCES sistemas  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- cambios
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS cambios (
    id                          INT  NOT NULL AUTO_INCREMENT,
    sistema_id                  INT  NOT NULL,
    usuario_solicitante_id      INT  NOT NULL,
    numero_incidencia           INT       NOT NULL,
    descripcion_breve           TEXT      NOT NULL,
    urgente                     TINYINT(1) NOT NULL DEFAULT 0,
    fecha_pruebas               DATE      NOT NULL,
    -- Transacción 2
    descripcion_tecnica         TEXT,
    fecha_propuesta_produccion  DATE,
    desarrollador_id            INT,
    archivo_nombre              VARCHAR(255),
    archivo_cambio              LONGBLOB,
    -- Transacción 3
    fecha_real_aplicacion       DATE,
    usuario_valido_id           INT,
    estado_cambio               ENUM('Cambio Correcto','Cambio Incorrecto'),
    observaciones               TEXT,
    administrador_libero_id     INT,
    -- Control
    estatus                     ENUM('Solicitado','Programado','Liberado') NOT NULL DEFAULT 'Solicitado',
    creado_en                   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en              DATETIME ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_cambios_sistema     FOREIGN KEY (sistema_id)                REFERENCES sistemas  (id),
    CONSTRAINT fk_cambios_solicitante FOREIGN KEY (usuario_solicitante_id)    REFERENCES usuarios  (id),
    CONSTRAINT fk_cambios_dev         FOREIGN KEY (desarrollador_id)          REFERENCES usuarios  (id),
    CONSTRAINT fk_cambios_valido      FOREIGN KEY (usuario_valido_id)         REFERENCES usuarios  (id),
    CONSTRAINT fk_cambios_admin       FOREIGN KEY (administrador_libero_id)   REFERENCES usuarios  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;