Tul xxx Tul

Viewing: coolabora.sql

-- Crear base de datos
CREATE DATABASE IF NOT EXISTS coolabora
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;
USE coolabora;

-- Roles del sistema (admin, empleado, talento, etc.)
CREATE TABLE roles (
    id            TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(50) NOT NULL UNIQUE,
    description   VARCHAR(255),
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Usuarios del portal
CREATE TABLE users (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id           TINYINT UNSIGNED NOT NULL,
    email             VARCHAR(120) NOT NULL UNIQUE,
    password_hash     VARCHAR(255) NOT NULL,
    name              VARCHAR(120) NOT NULL,
    status            ENUM('active','inactive','blocked') DEFAULT 'active',
    last_login_at     DATETIME,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
);

-- Información de empleados (ligados o no a un usuario)
CREATE TABLE empleados (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             BIGINT UNSIGNED NULL,
    documento_tipo      VARCHAR(20) DEFAULT 'CC',
    documento_numero    VARCHAR(30) NOT NULL,
    nombre              VARCHAR(120) NOT NULL,
    cargo               VARCHAR(100),
    area                VARCHAR(80),
    email               VARCHAR(120) DEFAULT NULL,
    salario_mensual     DECIMAL(12,2) DEFAULT 0,
    fecha_ingreso       DATE,
    fecha_nacimiento    DATE,
    telefono            VARCHAR(30),
    direccion           VARCHAR(180),
    ciudad              VARCHAR(80),
    contacto_nombre     VARCHAR(120),
    contacto_telefono   VARCHAR(30),
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_empleado_documento UNIQUE (documento_tipo, documento_numero),
    CONSTRAINT fk_empleado_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY uq_empleado_email (email)
);

CREATE INDEX idx_empleados_area ON empleados(area);
CREATE INDEX idx_empleados_nombre ON empleados(nombre);

-- Novedades de nómina (horas extra, incapacidades, descuentos, etc.)
CREATE TABLE novedades_nomina (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empleado_id         BIGINT UNSIGNED NOT NULL,
    tipo                VARCHAR(80) NOT NULL,
    categoria           ENUM('devengo','deduccion') NOT NULL,
    periodo             VARCHAR(7) NOT NULL COMMENT 'Formato YYYY-MM',
    cantidad            DECIMAL(10,2) DEFAULT 0,
    valor_unitario      DECIMAL(12,2) DEFAULT 0,
    valor_total         DECIMAL(14,2) AS (cantidad * valor_unitario) STORED,
    descripcion         TEXT,
    estado              ENUM('registrada','en_revision','aprobada','rechazada') DEFAULT 'registrada',
    registrada_por      BIGINT UNSIGNED,
    creada_en           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    actualizada_en      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_novedad_empleado FOREIGN KEY (empleado_id) REFERENCES empleados(id) ON DELETE CASCADE,
    CONSTRAINT fk_novedad_usuario FOREIGN KEY (registrada_por) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_novedad_periodo (periodo),
    INDEX idx_novedad_estado (estado)
);

-- Solicitudes de autoservicio
CREATE TABLE solicitudes_autoservicio (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empleado_id         BIGINT UNSIGNED NOT NULL,
    tipo                VARCHAR(80) NOT NULL,
    estado              ENUM('Pendiente','En revisión','Aprobado','Rechazado','Cerrado') DEFAULT 'Pendiente',
    prioridad           ENUM('baja','media','alta') DEFAULT 'media',
    descripcion         TEXT,
    comentarios_rrhh    TEXT,
    fecha_solicitud     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_objetivo      DATE,
    resuelta_por        BIGINT UNSIGNED NULL,
    resuelta_en         DATETIME,
    colaborador_nombre  VARCHAR(120),
    colaborador_email   VARCHAR(120),
    CONSTRAINT fk_solicitud_empleado FOREIGN KEY (empleado_id) REFERENCES empleados(id) ON DELETE CASCADE,
    CONSTRAINT fk_solicitud_resuelta FOREIGN KEY (resuelta_por) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_solicitud_estado (estado),
    INDEX idx_solicitud_tipo (tipo)
);

CREATE TABLE solicitudes_historial (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    solicitud_id    BIGINT UNSIGNED NOT NULL,
    accion          VARCHAR(150) NOT NULL,
    nota            TEXT,
    actor_id        BIGINT UNSIGNED NULL,
    creado_en       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_historial_solicitud FOREIGN KEY (solicitud_id) REFERENCES solicitudes_autoservicio(id) ON DELETE CASCADE,
    CONSTRAINT fk_historial_actor FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_historial_solicitud (solicitud_id)
);

-- Evaluaciones de desempeño
CREATE TABLE evaluaciones_desempeno (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empleado_id         BIGINT UNSIGNED NOT NULL,
    evaluador_id        BIGINT UNSIGNED NULL,
    periodo             VARCHAR(10) NOT NULL COMMENT 'Ej: 2025-Q2',
    resultado           DECIMAL(5,2),
    fortalezas          TEXT,
    oportunidades       TEXT,
    comentarios         TEXT,
    evaluado_en         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_eval_empleado FOREIGN KEY (empleado_id) REFERENCES empleados(id) ON DELETE CASCADE,
    CONSTRAINT fk_eval_evaluador FOREIGN KEY (evaluador_id) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY uq_eval_periodo (empleado_id, periodo)
);

-- Capacitaciones
CREATE TABLE capacitaciones (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titulo              VARCHAR(160) NOT NULL,
    descripcion         TEXT,
    modalidad           ENUM('presencial','virtual','mixta') DEFAULT 'virtual',
    responsable         VARCHAR(120),
    fecha_inicio        DATE NOT NULL,
    fecha_fin           DATE,
    cupo_maximo         SMALLINT UNSIGNED,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE capacitacion_inscripciones (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    capacitacion_id     BIGINT UNSIGNED NOT NULL,
    empleado_id         BIGINT UNSIGNED NOT NULL,
    estado              ENUM('inscrito','confirmado','completado','cancelado') DEFAULT 'inscrito',
    observaciones       TEXT,
    inscrito_en         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ci_capacitacion FOREIGN KEY (capacitacion_id) REFERENCES capacitaciones(id) ON DELETE CASCADE,
    CONSTRAINT fk_ci_empleado FOREIGN KEY (empleado_id) REFERENCES empleados(id) ON DELETE CASCADE,
    UNIQUE KEY uq_capacitacion_empleado (capacitacion_id, empleado_id)
);

-- Asistencia
CREATE TABLE asistencia_registros (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empleado_id         BIGINT UNSIGNED NOT NULL,
    fecha               DATE NOT NULL,
    hora_entrada        TIME,
    hora_salida         TIME,
    horas_trabajadas    DECIMAL(5,2),
    horas_extra         DECIMAL(5,2),
    observaciones       TEXT,
    registrado_en       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_asistencia_empleado FOREIGN KEY (empleado_id) REFERENCES empleados(id) ON DELETE CASCADE,
    UNIQUE KEY uq_asistencia_fecha (empleado_id, fecha)
);

CREATE TABLE asistencia_incidencias (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empleado_id         BIGINT UNSIGNED NOT NULL,
    fecha               DATE NOT NULL,
    tipo                VARCHAR(60) NOT NULL,
    minutos             SMALLINT UNSIGNED DEFAULT 0,
    notas               TEXT,
    reportada_por       BIGINT UNSIGNED,
    reportada_en        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_incidencia_empleado FOREIGN KEY (empleado_id) REFERENCES empleados(id) ON DELETE CASCADE,
    CONSTRAINT fk_incidencia_usuario FOREIGN KEY (reportada_por) REFERENCES users(id) ON DELETE SET NULL
);

-- Indicadores de talento
CREATE TABLE talento_indicadores (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    periodo         VARCHAR(10) NOT NULL,
    indicador       VARCHAR(80) NOT NULL,
    valor           DECIMAL(10,2) NOT NULL,
    observaciones   TEXT,
    registrado_en   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_indicador_periodo (periodo, indicador)
);

-- Beneficios corporativos
CREATE TABLE beneficios (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre          VARCHAR(120) NOT NULL,
    descripcion     TEXT,
    estado          ENUM('activo','inactivo') DEFAULT 'activo',
    creado_en       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE beneficios_empleados (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    beneficio_id        BIGINT UNSIGNED NOT NULL,
    empleado_id         BIGINT UNSIGNED NOT NULL,
    estado              ENUM('vigente','finalizado') DEFAULT 'vigente',
    asignado_en         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    finalizado_en       TIMESTAMP NULL,
    CONSTRAINT fk_be_beneficio FOREIGN KEY (beneficio_id) REFERENCES beneficios(id) ON DELETE CASCADE,
    CONSTRAINT fk_be_empleado FOREIGN KEY (empleado_id) REFERENCES empleados(id) ON DELETE CASCADE,
    UNIQUE KEY uq_beneficio_empleado (beneficio_id, empleado_id, estado)
);

-- Auditoría básica
CREATE TABLE audit_logs (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    usuario_id      BIGINT UNSIGNED,
    entidad         VARCHAR(80) NOT NULL,
    entidad_id      BIGINT UNSIGNED,
    accion          ENUM('create','update','delete','login','logout') NOT NULL,
    payload         JSON,
    registrado_en   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_audit_usuario FOREIGN KEY (usuario_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_audit_entidad (entidad, entidad_id),
    INDEX idx_audit_usuario (usuario_id)
);

-- Datos seed mínimos
INSERT INTO roles (name, description) VALUES
  ('admin', 'Administradores de Coolabora'),
  ('empleado', 'Colaboradores con acceso al autoservicio'),
  ('talento', 'Equipo de talento humano');

INSERT INTO users (role_id, email, password_hash, name)
VALUES
  (1, 'admin@coolabora.com', '$2y$10$xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', 'Carolina Ruiz'),
  (2, 'laura@coolabora.com', '$2y$10$yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy', 'Laura Gómez');

INSERT INTO empleados (user_id, documento_tipo, documento_numero, nombre, cargo, area, email, salario_mensual, fecha_ingreso, fecha_nacimiento, telefono, direccion, ciudad, contacto_nombre, contacto_telefono)
VALUES
  (2, 'CC', '1012456789', 'Laura Gómez', 'Contadora', 'Finanzas', 'laura@empresa.com', 2500000, '2021-02-10', '1993-04-18', '3001112233', 'Cra 45 #80-15', 'Bogotá D.C.', 'Paula Gómez', '3005551122');
User / IP	: 216.73.216.159
Host / Server	: 45.84.207.204 / aircan.me
System	: Linux lt-bnk-web1726.main-hosting.eu 5.14.0-611.36.1.el9_7.x86_64 #1 SMP PREEMPT_DYNAMIC Tue Mar 3 11:23:52 EST 2026 x86_64