-- ══════════════════════════════════════════════════════════════════
-- CoreAsist — Mejoras de Seguridad y Estructura de BD
-- Ejecutar DESPUÉS de importar la BD base (woraciow6_gestorcj10)
-- ══════════════════════════════════════════════════════════════════

-- ────────────────────────────────────────────────────────────────
-- 1. TABLA: usuarios — Mejoras críticas
-- ────────────────────────────────────────────────────────────────

-- Agregar columna trabajador_id con UNIQUE (un empleado = un usuario)
ALTER TABLE `usuarios`
    ADD COLUMN IF NOT EXISTS `trabajador_id` INT NULL DEFAULT NULL
        COMMENT 'FK empleados.id_empleado — único por empleado' AFTER `empleado_id`,
    ADD CONSTRAINT `uq_usuario_trabajador` UNIQUE KEY (`trabajador_id`);

-- Agregar intentos fallidos de login (rate limiting en BD)
ALTER TABLE `usuarios`
    ADD COLUMN IF NOT EXISTS `failed_attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0
        COMMENT 'Intentos de login fallidos consecutivos' AFTER `ultimo_acceso`,
    ADD COLUMN IF NOT EXISTS `locked_until` DATETIME NULL DEFAULT NULL
        COMMENT 'Bloqueado hasta esta fecha/hora' AFTER `failed_attempts`,
    ADD COLUMN IF NOT EXISTS `password_changed_at` DATETIME NULL DEFAULT NULL
        COMMENT 'Última vez que cambió la contraseña' AFTER `locked_until`,
    ADD COLUMN IF NOT EXISTS `remember_token` VARCHAR(100) NULL DEFAULT NULL
        COMMENT 'Token para "recordar sesión"' AFTER `password_changed_at`;

-- Índices de seguridad
ALTER TABLE `usuarios`
    ADD INDEX IF NOT EXISTS `idx_usuario_username` (`username`),
    ADD INDEX IF NOT EXISTS `idx_usuario_email` (`email`),
    ADD INDEX IF NOT EXISTS `idx_usuario_estado` (`estado`);

-- ────────────────────────────────────────────────────────────────
-- 2. TABLA: login_attempts — Auditoría de intentos de acceso
-- ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `login_attempts` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username`     VARCHAR(100)    NOT NULL COMMENT 'Username o email intentado',
    `ip_address`   VARCHAR(45)     NOT NULL,
    `user_agent`   VARCHAR(255)    DEFAULT NULL,
    `exitoso`      TINYINT(1)      NOT NULL DEFAULT 0,
    `motivo_fallo` VARCHAR(100)    DEFAULT NULL COMMENT 'credenciales|bloqueado|inactivo',
    `usuario_id`   INT             DEFAULT NULL COMMENT 'FK usuarios.id si se encontró',
    `fecha`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_la_username` (`username`),
    INDEX `idx_la_ip`       (`ip_address`),
    INDEX `idx_la_fecha`    (`fecha`),
    INDEX `idx_la_exitoso`  (`exitoso`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Registro de todos los intentos de login al sistema';

-- ────────────────────────────────────────────────────────────────
-- 3. TABLA: sesiones_activas — Control de sesiones simultáneas
-- ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `sesiones_activas` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `usuario_id`     INT             NOT NULL,
    `session_hash`   VARCHAR(128)    NOT NULL COMMENT 'SHA-256 del session_id',
    `ip_address`     VARCHAR(45)     NOT NULL,
    `user_agent`     VARCHAR(255)    DEFAULT NULL,
    `ultimo_acceso`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `creada_en`      TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `activa`         TINYINT(1)      NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_session_hash` (`session_hash`),
    INDEX `idx_sa_usuario` (`usuario_id`),
    INDEX `idx_sa_activa`  (`activa`),
    CONSTRAINT `fk_sa_usuario`
        FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Control de sesiones activas por usuario';

-- ────────────────────────────────────────────────────────────────
-- 4. TABLA: csrf_tokens — Tokens CSRF persistentes (opcional)
-- ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `csrf_tokens` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `token`      VARCHAR(128)    NOT NULL,
    `usuario_id` INT             DEFAULT NULL,
    `ip_address` VARCHAR(45)     NOT NULL,
    `usado`      TINYINT(1)      NOT NULL DEFAULT 0,
    `expira_en`  TIMESTAMP       NOT NULL,
    `creado_en`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_csrf_token` (`token`),
    INDEX `idx_csrf_usuario` (`usuario_id`),
    INDEX `idx_csrf_expira`  (`expira_en`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Tokens CSRF para formularios (respaldo de la sesión)';

-- ────────────────────────────────────────────────────────────────
-- 5. TABLA: notificaciones — Sistema de notificaciones interno
-- ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `notificaciones` (
    `id`              INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `usuario_id`      INT             NOT NULL COMMENT 'FK usuarios.id destinatario',
    `tipo`            ENUM('info','success','warning','danger') NOT NULL DEFAULT 'info',
    `titulo`          VARCHAR(150)    NOT NULL,
    `mensaje`         TEXT            NOT NULL,
    `url_accion`      VARCHAR(300)    DEFAULT NULL COMMENT 'Link de acción relacionado',
    `leida`           TINYINT(1)      NOT NULL DEFAULT 0,
    `fecha_lectura`   DATETIME        DEFAULT NULL,
    `creado_por`      INT             DEFAULT NULL COMMENT 'FK usuarios.id que la generó (NULL=sistema)',
    `fecha_creacion`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_notif_usuario` (`usuario_id`),
    INDEX `idx_notif_leida`   (`leida`),
    INDEX `idx_notif_fecha`   (`fecha_creacion`),
    CONSTRAINT `fk_notif_usuario`
        FOREIGN KEY (`usuario_id`) REFERENCES `usuarios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Notificaciones internas del sistema';

-- ────────────────────────────────────────────────────────────────
-- 6. MEJORA: ausentismos — agregar columna grupo para fechas múltiples
-- ────────────────────────────────────────────────────────────────
ALTER TABLE `ausentismos`
    ADD COLUMN IF NOT EXISTS `grupo_registro` VARCHAR(36) NULL DEFAULT NULL
        COMMENT 'UUID del grupo cuando se registran fechas múltiples en lote' AFTER `id_ausentismo`,
    ADD INDEX IF NOT EXISTS `idx_aus_grupo` (`grupo_registro`);

-- ────────────────────────────────────────────────────────────────
-- 7. PROCEDIMIENTO: verificar solapamiento desde la BD
-- ────────────────────────────────────────────────────────────────
DROP PROCEDURE IF EXISTS `sp_verificar_solapamiento`;

DELIMITER $$
CREATE PROCEDURE `sp_verificar_solapamiento`(
    IN p_empleado_id   INT,
    IN p_fecha_inicio  DATE,
    IN p_fecha_fin     DATE,
    IN p_exclude_id    INT  -- 0 para ninguno
)
BEGIN
    SELECT
        a.id_ausentismo,
        a.tipo_ausentismo,
        a.fecha_inicio,
        a.fecha_fin,
        a.estado
    FROM ausentismos a
    WHERE a.id_empleado = p_empleado_id
      AND a.estado NOT IN ('Rechazado', 'Cancelado')
      AND a.fecha_inicio <= p_fecha_fin
      AND a.fecha_fin    >= p_fecha_inicio
      AND (p_exclude_id = 0 OR a.id_ausentismo != p_exclude_id);
END $$
DELIMITER ;

-- ────────────────────────────────────────────────────────────────
-- 8. VISTA mejorada: vista_ausentismos_completa
-- ────────────────────────────────────────────────────────────────
CREATE OR REPLACE VIEW `vista_ausentismos_completa` AS
SELECT
    a.id_ausentismo,
    a.grupo_registro,
    a.id_empleado,
    CONCAT(e.nombre1, ' ', e.apellido1) AS nombre_empleado,
    CONCAT(e.nombre1, ' ', COALESCE(e.nombre2,''), ' ', e.apellido1, ' ', COALESCE(e.apellido2,'')) AS nombre_completo_empleado,
    e.cedula,
    hc.cargo,
    hc.centro_trabajo,
    hc.tipo_contrato,
    a.tipo_ausentismo,
    a.causa,
    a.es_medica,
    a.fecha_inicio,
    a.fecha_fin,
    a.horas,
    a.dias_calculados,
    a.dias_editables,
    a.dias_finales,
    a.motivo,
    a.observaciones,
    a.documento_nombre,
    a.documento_tipo,
    a.estado,
    a.fecha_registro,
    a.fecha_aprobacion,
    a.comentarios_aprobacion,
    CONCAT(uc.nombre,' ',uc.apellido) AS registrado_por,
    CONCAT(ua.nombre,' ',ua.apellido) AS aprobado_por_nombre
FROM ausentismos a
INNER JOIN empleados e    ON e.id_empleado = a.id_empleado
LEFT JOIN historial_contratos hc ON hc.id_empleado = e.id_empleado AND hc.activo = 1
LEFT JOIN usuarios uc     ON uc.id = a.creado_por
LEFT JOIN usuarios ua     ON ua.id = a.aprobado_por
ORDER BY a.fecha_registro DESC;

-- ────────────────────────────────────────────────────────────────
-- 9. EVENTO: limpiar login_attempts antiguos (> 90 días)
-- ────────────────────────────────────────────────────────────────
DROP EVENT IF EXISTS `ev_limpiar_login_attempts`;

CREATE EVENT `ev_limpiar_login_attempts`
    ON SCHEDULE EVERY 1 DAY
    STARTS CURRENT_TIMESTAMP
    DO
        DELETE FROM login_attempts WHERE fecha < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- ────────────────────────────────────────────────────────────────
-- 10. PERMISOS: crear usuario de BD con mínimos privilegios
--     (Ejecutar como root, cambiar la contraseña)
-- ────────────────────────────────────────────────────────────────
-- CREATE USER IF NOT EXISTS 'coreasist_app'@'localhost' IDENTIFIED BY 'CAMBIAR_ESTE_PASSWORD';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON woraciow6_gestorcj10.* TO 'coreasist_app'@'localhost';
-- REVOKE DROP, ALTER, CREATE, INDEX ON woraciow6_gestorcj10.* FROM 'coreasist_app'@'localhost';
-- FLUSH PRIVILEGES;

SELECT 'Migraciones de seguridad aplicadas correctamente.' AS resultado;