-- ============================================================
-- GEOSTARK NOMINA - V8 VACACIONES COMPLETAS
-- Estructura compatible MariaDB/MySQL. No borra datos.
-- ============================================================

CREATE TABLE IF NOT EXISTS nomina_vacaciones_eventos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  persona_id INT NOT NULL,
  fecha_documento DATE NOT NULL,
  fecha_inicio DATE DEFAULT NULL,
  fecha_fin DATE DEFAULT NULL,
  tipo VARCHAR(30) NOT NULL DEFAULT 'DISFRUTE',
  ciclo_anual INT DEFAULT NULL,
  es_historico TINYINT(1) NOT NULL DEFAULT 0,
  dias_disfrute DECIMAL(10,2) NOT NULL DEFAULT 0,
  dias_bono DECIMAL(10,2) NOT NULL DEFAULT 0,
  monto_disfrute_bs DECIMAL(18,2) NOT NULL DEFAULT 0,
  monto_bono_bs DECIMAL(18,2) NOT NULL DEFAULT 0,
  periodo_id INT DEFAULT NULL,
  corrida_id INT DEFAULT NULL,
  observacion VARCHAR(255) DEFAULT NULL,
  estado VARCHAR(20) NOT NULL DEFAULT 'REGISTRADO',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_persona_fecha (persona_id, fecha_documento),
  KEY idx_persona_ciclo (persona_id, ciclo_anual),
  KEY idx_periodo (periodo_id),
  KEY idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='nomina_vacaciones_eventos' AND COLUMN_NAME='fuente_salario')=0,
'ALTER TABLE nomina_vacaciones_eventos ADD COLUMN fuente_salario VARCHAR(40) NULL AFTER estado','SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='nomina_vacaciones_eventos' AND COLUMN_NAME='salario_mensual_bs')=0,
'ALTER TABLE nomina_vacaciones_eventos ADD COLUMN salario_mensual_bs DECIMAL(18,4) NOT NULL DEFAULT 0 AFTER fuente_salario','SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='nomina_vacaciones_eventos' AND COLUMN_NAME='salario_diario_bs')=0,
'ALTER TABLE nomina_vacaciones_eventos ADD COLUMN salario_diario_bs DECIMAL(18,4) NOT NULL DEFAULT 0 AFTER salario_mensual_bs','SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='nomina_vacaciones_eventos' AND COLUMN_NAME='tasa_bcv_usada')=0,
'ALTER TABLE nomina_vacaciones_eventos ADD COLUMN tasa_bcv_usada DECIMAL(18,6) NOT NULL DEFAULT 0 AFTER salario_diario_bs','SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='nomina_vacaciones_eventos' AND COLUMN_NAME='salario_moneda')=0,
'ALTER TABLE nomina_vacaciones_eventos ADD COLUMN salario_moneda VARCHAR(10) NULL AFTER tasa_bcv_usada','SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='nomina_vacaciones_eventos' AND COLUMN_NAME='salario_monto_original')=0,
'ALTER TABLE nomina_vacaciones_eventos ADD COLUMN salario_monto_original DECIMAL(18,4) NOT NULL DEFAULT 0 AFTER salario_moneda','SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='nomina_vacaciones_eventos' AND COLUMN_NAME='bono_extra_usd_mensual')=0,
'ALTER TABLE nomina_vacaciones_eventos ADD COLUMN bono_extra_usd_mensual DECIMAL(18,4) NOT NULL DEFAULT 0 AFTER salario_monto_original','SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='nomina_vacaciones_eventos' AND COLUMN_NAME='origen_registro')=0,
'ALTER TABLE nomina_vacaciones_eventos ADD COLUMN origen_registro VARCHAR(40) NOT NULL DEFAULT ''V8_VACACIONES'' AFTER bono_extra_usd_mensual','SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='nomina_vacaciones_eventos' AND COLUMN_NAME='soporte_referencia')=0,
'ALTER TABLE nomina_vacaciones_eventos ADD COLUMN soporte_referencia VARCHAR(255) NULL AFTER origen_registro','SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='nomina_vacaciones_eventos' AND COLUMN_NAME='snapshot_json')=0,
'ALTER TABLE nomina_vacaciones_eventos ADD COLUMN snapshot_json JSON NULL AFTER soporte_referencia','SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='nomina_vacaciones_eventos' AND COLUMN_NAME='created_by')=0,
'ALTER TABLE nomina_vacaciones_eventos ADD COLUMN created_by VARCHAR(120) NULL AFTER snapshot_json','SELECT 1'); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

CREATE TABLE IF NOT EXISTS nomina_vacaciones_auditoria (
  id INT AUTO_INCREMENT PRIMARY KEY,
  fecha_evento DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  usuario VARCHAR(120) NULL,
  accion VARCHAR(80) NOT NULL,
  persona_id INT NULL,
  vacaciones_evento_id INT NULL,
  detalle TEXT NULL,
  ip VARCHAR(64) NULL,
  KEY idx_fecha (fecha_evento),
  KEY idx_persona (persona_id),
  KEY idx_accion (accion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SELECT 'V8 vacaciones estructura lista' AS resultado;
