/*
  LIMPIEZA SEGURA DE NÓMINA - GEOSTARK CRUD NÓMINA - V2 SIN ERROR 1137
  Objetivo:
    1) Borrar TODAS las corridas de nómina de prueba.
    2) Borrar TODOS los períodos de nómina y sus novedades.
    3) Borrar trabajadores DEMO y sus datos dependientes.
    4) Conservar trabajadores reales, cargos reales, turnos, usuarios, conceptos, BCV y parámetros legales.

  CORRECCIÓN V2:
    - Esta versión evita el error MySQL 1137: Can't reopen table.
    - La versión anterior usaba tablas temporales y MySQL no permite reabrir la misma temporal varias veces dentro de ciertos UNION/consultas.

  IMPORTANTE:
    - Haz backup completo de la base antes de ejecutar.
    - Por defecto NO ejecuta la limpieza. Primero muestra conteos.
    - Para ejecutar de verdad cambia @CONFIRMAR_LIMPIEZA_NOMINA de 'NO' a 'SI'.
*/

SET SQL_SAFE_UPDATES = 0;

/* ============================================================
   0) CONFIRMACIÓN MANUAL
   Cambia a 'SI' únicamente cuando hayas revisado los SELECT.
   ============================================================ */
SET @CONFIRMAR_LIMPIEZA_NOMINA := 'SI';

/* ============================================================
   1) TABLAS TEMPORALES DE ALCANCE
   - zz_limpieza_nomina_periodos: todos los períodos de nómina actuales.
   - zz_limpieza_nomina_corridas: corridas asociadas a esos períodos.
   - zz_limpieza_nomina_demo_personas: trabajadores claramente demo.
   Nota v2: usa tablas auxiliares reales, no TEMPORARY TABLE, para evitar el error MySQL 1137 “Can't reopen table”.
   ============================================================ */
DROP TABLE IF EXISTS zz_limpieza_nomina_periodos;
CREATE TABLE zz_limpieza_nomina_periodos (
  id INT PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO zz_limpieza_nomina_periodos (id)
SELECT id
FROM periodos_nomina;

DROP TABLE IF EXISTS zz_limpieza_nomina_corridas;
CREATE TABLE zz_limpieza_nomina_corridas (
  id INT PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO zz_limpieza_nomina_corridas (id)
SELECT c.id
FROM nomina_corridas c
WHERE c.periodo_id IN (SELECT id FROM zz_limpieza_nomina_periodos);

DROP TABLE IF EXISTS zz_limpieza_nomina_demo_personas;
CREATE TABLE zz_limpieza_nomina_demo_personas (
  id INT PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO zz_limpieza_nomina_demo_personas (id)
SELECT p.id
FROM personas p
LEFT JOIN cargos c ON c.id_cargo = p.id_cargo
WHERE UPPER(COALESCE(p.cedula,'')) LIKE 'DEMO-NOM-%'
   OR UPPER(COALESCE(p.nombre,'')) LIKE '%DEMO%'
   OR UPPER(COALESCE(p.apellido,'')) LIKE '%DEMO%'
   OR UPPER(COALESCE(p.direccion,'')) = 'PRUEBA'
   OR UPPER(COALESCE(c.cargo,'')) LIKE '%DEMO%';

/* ============================================================
   2) REVISIÓN ANTES DE BORRAR
   Si algún conteo o trabajador no coincide con lo esperado, NO continúes.
   ============================================================ */
SELECT 'PERIODOS A BORRAR' AS revision, p.*
FROM periodos_nomina p
JOIN zz_limpieza_nomina_periodos t ON t.id = p.id
ORDER BY p.id;

SELECT 'CORRIDAS A BORRAR' AS revision, c.id, c.periodo_id, p.codigo AS periodo_codigo,
       c.fecha_calculo, c.estado, c.tasa_bcv_usd, c.cestaticket_usd_mensual
FROM nomina_corridas c
JOIN zz_limpieza_nomina_corridas t ON t.id = c.id
LEFT JOIN periodos_nomina p ON p.id = c.periodo_id
ORDER BY c.id;

SELECT 'TRABAJADORES DEMO A BORRAR' AS revision,
       p.id, p.cedula, p.nombre, p.apellido, p.estatus_laboral, p.fecha_ingreso,
       p.salario_base, p.moneda_salario, c.cargo
FROM personas p
LEFT JOIN cargos c ON c.id_cargo = p.id_cargo
JOIN zz_limpieza_nomina_demo_personas t ON t.id = p.id
ORDER BY p.id;

SELECT 'nomina_movimientos por corridas' AS tabla, COUNT(*) AS registros
FROM nomina_movimientos m JOIN zz_limpieza_nomina_corridas t ON t.id = m.corrida_id
UNION ALL
SELECT 'nomina_detalle por corridas', COUNT(*)
FROM nomina_detalle d JOIN zz_limpieza_nomina_corridas t ON t.id = d.corrida_id
UNION ALL
SELECT 'nomina_corridas', COUNT(*)
FROM nomina_corridas c JOIN zz_limpieza_nomina_corridas t ON t.id = c.id
UNION ALL
SELECT 'novedades_periodo por periodos', COUNT(*)
FROM novedades_periodo n JOIN zz_limpieza_nomina_periodos t ON t.id = n.periodo_id
UNION ALL
SELECT 'novedades_periodo_conceptos_manual por periodos', COUNT(*)
FROM novedades_periodo_conceptos_manual n JOIN zz_limpieza_nomina_periodos t ON t.id = n.periodo_id
UNION ALL
SELECT 'periodos_nomina', COUNT(*)
FROM periodos_nomina p JOIN zz_limpieza_nomina_periodos t ON t.id = p.id
UNION ALL
SELECT 'personas DEMO', COUNT(*)
FROM personas p JOIN zz_limpieza_nomina_demo_personas t ON t.id = p.id
UNION ALL
SELECT 'asistencia DEMO', COUNT(*)
FROM asistencia a JOIN zz_limpieza_nomina_demo_personas t ON t.id = a.persona_id
UNION ALL
SELECT 'carga_familiar DEMO', COUNT(*)
FROM carga_familiar cf JOIN zz_limpieza_nomina_demo_personas t ON t.id = cf.id_persona
UNION ALL
SELECT 'persona_documentos DEMO', COUNT(*)
FROM persona_documentos pd JOIN zz_limpieza_nomina_demo_personas t ON t.id = pd.persona_id
UNION ALL
SELECT 'persona_suspensiones DEMO', COUNT(*)
FROM persona_suspensiones ps JOIN zz_limpieza_nomina_demo_personas t ON t.id = ps.persona_id
UNION ALL
SELECT 'prestaciones DEMO', COUNT(*)
FROM nomina_prestaciones_eventos pe JOIN zz_limpieza_nomina_demo_personas t ON t.id = pe.persona_id
UNION ALL
SELECT 'vacaciones DEMO', COUNT(*)
FROM nomina_vacaciones_eventos ve JOIN zz_limpieza_nomina_demo_personas t ON t.id = ve.persona_id;

/* ============================================================
   3) FRENO DE SEGURIDAD
   Si @CONFIRMAR_LIMPIEZA_NOMINA sigue en 'NO', el script se detiene aquí.
   ============================================================ */
DROP PROCEDURE IF EXISTS gs_confirmar_limpieza_nomina;
DELIMITER $$
CREATE PROCEDURE gs_confirmar_limpieza_nomina()
BEGIN
  IF @CONFIRMAR_LIMPIEZA_NOMINA <> 'SI' THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Limpieza NO ejecutada. Revisa los SELECT y cambia @CONFIRMAR_LIMPIEZA_NOMINA a SI para ejecutar.';
  END IF;
END$$
DELIMITER ;

CALL gs_confirmar_limpieza_nomina();
DROP PROCEDURE IF EXISTS gs_confirmar_limpieza_nomina;

/* ============================================================
   4) BACKUP DE FILAS AFECTADAS
   No reemplaza el backup completo de la BD, pero deja copia de las filas que se borrarán.
   Si estas tablas ya existen, MySQL emitirá warning y no las sobrescribirá.
   ============================================================ */
CREATE TABLE IF NOT EXISTS backup_limpieza_20260514_periodos_nomina AS
SELECT p.* FROM periodos_nomina p JOIN zz_limpieza_nomina_periodos t ON t.id = p.id;

CREATE TABLE IF NOT EXISTS backup_limpieza_20260514_nomina_corridas AS
SELECT c.* FROM nomina_corridas c JOIN zz_limpieza_nomina_corridas t ON t.id = c.id;

CREATE TABLE IF NOT EXISTS backup_limpieza_20260514_nomina_detalle AS
SELECT d.* FROM nomina_detalle d JOIN zz_limpieza_nomina_corridas t ON t.id = d.corrida_id;

CREATE TABLE IF NOT EXISTS backup_limpieza_20260514_nomina_movimientos AS
SELECT m.* FROM nomina_movimientos m JOIN zz_limpieza_nomina_corridas t ON t.id = m.corrida_id;

CREATE TABLE IF NOT EXISTS backup_limpieza_20260514_novedades_periodo AS
SELECT n.* FROM novedades_periodo n JOIN zz_limpieza_nomina_periodos t ON t.id = n.periodo_id;

CREATE TABLE IF NOT EXISTS backup_limpieza_20260514_novedades_manual AS
SELECT n.* FROM novedades_periodo_conceptos_manual n JOIN zz_limpieza_nomina_periodos t ON t.id = n.periodo_id;

CREATE TABLE IF NOT EXISTS backup_limpieza_20260514_personas_demo AS
SELECT p.* FROM personas p JOIN zz_limpieza_nomina_demo_personas t ON t.id = p.id;

CREATE TABLE IF NOT EXISTS backup_limpieza_20260514_asistencia_demo AS
SELECT a.* FROM asistencia a JOIN zz_limpieza_nomina_demo_personas t ON t.id = a.persona_id;

CREATE TABLE IF NOT EXISTS backup_limpieza_20260514_prestaciones_demo AS
SELECT pe.* FROM nomina_prestaciones_eventos pe JOIN zz_limpieza_nomina_demo_personas t ON t.id = pe.persona_id;

CREATE TABLE IF NOT EXISTS backup_limpieza_20260514_vacaciones_afectadas AS
SELECT ve.*
FROM nomina_vacaciones_eventos ve
WHERE ve.persona_id IN (SELECT id FROM zz_limpieza_nomina_demo_personas)
   OR ve.periodo_id IN (SELECT id FROM zz_limpieza_nomina_periodos)
   OR ve.corrida_id IN (SELECT id FROM zz_limpieza_nomina_corridas);

/* ============================================================
   5) LIMPIEZA TRANSACCIONAL
   Mantiene FOREIGN_KEY_CHECKS activo. Si algo está mal, fallará y podrás hacer ROLLBACK.
   Nota: la tabla v4 se crea ANTES de iniciar la transacción porque MySQL hace commit implícito en DDL.
   ============================================================ */

/* 5.1 Historial de estados de corrida, si existe por v4.
   Esta tabla se crea si no existe para evitar error en instalaciones que aún no la tengan. */
CREATE TABLE IF NOT EXISTS nomina_corrida_estado_historial (
  id INT AUTO_INCREMENT PRIMARY KEY,
  corrida_id INT NOT NULL,
  estado_anterior VARCHAR(30) NULL,
  estado_nuevo VARCHAR(30) NOT NULL,
  usuario VARCHAR(120) NULL,
  motivo VARCHAR(255) NULL,
  ip VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_corrida (corrida_id),
  KEY idx_estado_fecha (estado_nuevo, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

START TRANSACTION;

DELETE h
FROM nomina_corrida_estado_historial h
JOIN zz_limpieza_nomina_corridas t ON t.id = h.corrida_id;

/* 5.2 Auditoría relacionada con corridas/períodos que se están borrando. */
DELETE a
FROM nomina_auditoria a
JOIN zz_limpieza_nomina_corridas t ON t.id = a.entidad_id
WHERE LOWER(a.entidad) IN ('nomina_corrida','nomina_corridas','corrida','corridas');

DELETE a
FROM nomina_auditoria a
JOIN zz_limpieza_nomina_periodos t ON t.id = a.entidad_id
WHERE LOWER(a.entidad) IN ('periodo','periodos','periodo_nomina','periodos_nomina');

/* 5.3 Nómina calculada. */
DELETE m
FROM nomina_movimientos m
JOIN zz_limpieza_nomina_corridas t ON t.id = m.corrida_id;

DELETE d
FROM nomina_detalle d
JOIN zz_limpieza_nomina_corridas t ON t.id = d.corrida_id;

/* Vacaciones vinculadas a períodos/corridas borradas o trabajadores demo. */
DELETE ve
FROM nomina_vacaciones_eventos ve
WHERE ve.persona_id IN (SELECT id FROM zz_limpieza_nomina_demo_personas)
   OR ve.periodo_id IN (SELECT id FROM zz_limpieza_nomina_periodos)
   OR ve.corrida_id IN (SELECT id FROM zz_limpieza_nomina_corridas);

DELETE c
FROM nomina_corridas c
JOIN zz_limpieza_nomina_corridas t ON t.id = c.id;

/* 5.4 Novedades y períodos. */
DELETE nm
FROM novedades_periodo_conceptos_manual nm
JOIN zz_limpieza_nomina_periodos t ON t.id = nm.periodo_id;

DELETE n
FROM novedades_periodo n
JOIN zz_limpieza_nomina_periodos t ON t.id = n.periodo_id;

DELETE p
FROM periodos_nomina p
JOIN zz_limpieza_nomina_periodos t ON t.id = p.id;

/* 5.5 Datos dependientes de trabajadores DEMO. */
DELETE m
FROM nomina_movimientos m
JOIN zz_limpieza_nomina_demo_personas t ON t.id = m.persona_id;

DELETE d
FROM nomina_detalle d
JOIN zz_limpieza_nomina_demo_personas t ON t.id = d.persona_id;

DELETE nm
FROM novedades_periodo_conceptos_manual nm
JOIN zz_limpieza_nomina_demo_personas t ON t.id = nm.persona_id;

DELETE n
FROM novedades_periodo n
JOIN zz_limpieza_nomina_demo_personas t ON t.id = n.persona_id;

DELETE pe
FROM nomina_prestaciones_eventos pe
JOIN zz_limpieza_nomina_demo_personas t ON t.id = pe.persona_id;

DELETE a
FROM asistencia a
JOIN zz_limpieza_nomina_demo_personas t ON t.id = a.persona_id;

DELETE cf
FROM carga_familiar cf
JOIN zz_limpieza_nomina_demo_personas t ON t.id = cf.id_persona;

DELETE pd
FROM persona_documentos pd
JOIN zz_limpieza_nomina_demo_personas t ON t.id = pd.persona_id;

DELETE ps
FROM persona_suspensiones ps
JOIN zz_limpieza_nomina_demo_personas t ON t.id = ps.persona_id;

DELETE p
FROM personas p
JOIN zz_limpieza_nomina_demo_personas t ON t.id = p.id;

/* 5.6 Borra el cargo DEMO solo si ya no está asignado a nadie. */
DELETE c
FROM cargos c
LEFT JOIN personas p ON p.id_cargo = c.id_cargo
WHERE p.id IS NULL
  AND UPPER(COALESCE(c.cargo,'')) LIKE '%DEMO%';

COMMIT;

/* ============================================================
   6) RESETEO DE AUTO_INCREMENT EN TABLAS QUE DEBEN QUEDAR VACÍAS
   No se resetea personas ni asistencia porque conservan datos reales.
   ============================================================ */
ALTER TABLE nomina_corrida_estado_historial AUTO_INCREMENT = 1;
ALTER TABLE nomina_movimientos AUTO_INCREMENT = 1;
ALTER TABLE nomina_detalle AUTO_INCREMENT = 1;
ALTER TABLE nomina_corridas AUTO_INCREMENT = 1;
ALTER TABLE novedades_periodo_conceptos_manual AUTO_INCREMENT = 1;
ALTER TABLE novedades_periodo AUTO_INCREMENT = 1;
ALTER TABLE periodos_nomina AUTO_INCREMENT = 1;

/* ============================================================
   7) VERIFICACIÓN FINAL
   Debe quedar 0 en períodos/corridas/novedades/movimientos.
   Los DEMO también deben quedar 0.
   ============================================================ */
SELECT 'periodos_nomina restantes' AS revision, COUNT(*) AS registros FROM periodos_nomina
UNION ALL
SELECT 'nomina_corridas restantes', COUNT(*) FROM nomina_corridas
UNION ALL
SELECT 'nomina_detalle restantes', COUNT(*) FROM nomina_detalle
UNION ALL
SELECT 'nomina_movimientos restantes', COUNT(*) FROM nomina_movimientos
UNION ALL
SELECT 'novedades_periodo restantes', COUNT(*) FROM novedades_periodo
UNION ALL
SELECT 'novedades_periodo_conceptos_manual restantes', COUNT(*) FROM novedades_periodo_conceptos_manual
UNION ALL
SELECT 'trabajadores DEMO restantes', COUNT(*)
FROM personas p
LEFT JOIN cargos c ON c.id_cargo = p.id_cargo
WHERE UPPER(COALESCE(p.cedula,'')) LIKE 'DEMO-NOM-%'
   OR UPPER(COALESCE(p.nombre,'')) LIKE '%DEMO%'
   OR UPPER(COALESCE(p.apellido,'')) LIKE '%DEMO%'
   OR UPPER(COALESCE(p.direccion,'')) = 'PRUEBA'
   OR UPPER(COALESCE(c.cargo,'')) LIKE '%DEMO%';

SELECT 'Limpieza segura finalizada. Revisa los conteos finales.' AS resultado;

/* Limpieza de tablas auxiliares del script.
   Las tablas de respaldo backup_limpieza_20260514_* se conservan. */
DROP TABLE IF EXISTS zz_limpieza_nomina_periodos;
DROP TABLE IF EXISTS zz_limpieza_nomina_corridas;
DROP TABLE IF EXISTS zz_limpieza_nomina_demo_personas;

/* ============================================================
   OPCIONAL - NO EJECUTAR SALVO QUE QUIERAS BORRAR HISTÓRICO LEGAL DE PRUEBA
   Si generaste prestaciones/vacaciones de prueba para trabajadores reales y quieres iniciar desde cero,
   evalúa estas líneas manualmente, una por una, después de hacer backup.

   -- DELETE FROM nomina_prestaciones_eventos;
   -- DELETE FROM nomina_vacaciones_eventos;
   -- ALTER TABLE nomina_prestaciones_eventos AUTO_INCREMENT = 1;
   -- ALTER TABLE nomina_vacaciones_eventos AUTO_INCREMENT = 1;
   ============================================================ */
