-- Migración v10 consolidada - Mandatos GE Gestión Laboral
-- Consolida módulos de colaboradores, novedades, informes, liquidaciones, contratos y estudios de seguridad.
-- Ejecutar en phpMyAdmin sobre la base de datos del aplicativo.

-- Campos complementarios en empresas
SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='companies' AND COLUMN_NAME='direccion');
SET @sql := IF(@col_exists=0, 'ALTER TABLE companies ADD COLUMN direccion VARCHAR(255) NULL AFTER ciudad', 'SELECT "companies.direccion ya existe" AS mensaje');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='companies' AND COLUMN_NAME='logo_nombre');
SET @sql := IF(@col_exists=0, 'ALTER TABLE companies ADD COLUMN logo_nombre VARCHAR(255) NULL AFTER estado', 'SELECT "companies.logo_nombre ya existe" AS mensaje');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='companies' AND COLUMN_NAME='logo_ruta');
SET @sql := IF(@col_exists=0, 'ALTER TABLE companies ADD COLUMN logo_ruta VARCHAR(255) NULL AFTER logo_nombre', 'SELECT "companies.logo_ruta ya existe" AS mensaje');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Campos complementarios en colaboradores
SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='collaborators' AND COLUMN_NAME='salario_modalidad');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN salario_modalidad VARCHAR(40) NULL AFTER estado', 'SELECT "collaborators.salario_modalidad ya existe" AS mensaje');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='collaborators' AND COLUMN_NAME='fecha_expedicion_documento');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN fecha_expedicion_documento DATE NULL AFTER numero_documento', 'SELECT "collaborators.fecha_expedicion_documento ya existe" AS mensaje');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='collaborators' AND COLUMN_NAME='direccion');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN direccion VARCHAR(255) NULL AFTER cargo', 'SELECT "collaborators.direccion ya existe" AS mensaje');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='collaborators' AND COLUMN_NAME='telefono');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN telefono VARCHAR(80) NULL AFTER direccion', 'SELECT "collaborators.telefono ya existe" AS mensaje');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='collaborators' AND COLUMN_NAME='correo_electronico');
SET @sql := IF(@col_exists=0, 'ALTER TABLE collaborators ADD COLUMN correo_electronico VARCHAR(160) NULL AFTER telefono', 'SELECT "collaborators.correo_electronico ya existe" AS mensaje');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Campos de respaldo en novedades
SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='novelties' AND COLUMN_NAME='documento_text');
SET @sql := IF(@col_exists=0, 'ALTER TABLE novelties ADD COLUMN documento_text VARCHAR(80) NULL AFTER collaborator_id', 'SELECT "novelties.documento_text ya existe" AS mensaje');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='novelties' AND COLUMN_NAME='colaborador_text');
SET @sql := IF(@col_exists=0, 'ALTER TABLE novelties ADD COLUMN colaborador_text VARCHAR(255) NULL AFTER documento_text', 'SELECT "novelties.colaborador_text ya existe" AS mensaje');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE novelties n
LEFT JOIN collaborators c ON c.id = n.collaborator_id
SET n.documento_text = COALESCE(NULLIF(n.documento_text,''), c.numero_documento),
    n.colaborador_text = COALESCE(NULLIF(n.colaborador_text,''), c.nombre_completo)
WHERE n.collaborator_id IS NOT NULL;

-- Contratos y documentos automáticos
CREATE TABLE IF NOT EXISTS contract_templates (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    tipo_documento VARCHAR(120) NOT NULL,
    nombre VARCHAR(180) NOT NULL,
    descripcion TEXT NULL,
    nombre_original VARCHAR(255) NOT NULL,
    ruta VARCHAR(500) NOT NULL,
    estado VARCHAR(40) NOT NULL DEFAULT 'Activo',
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_tpl_company_tipo (company_id, tipo_documento),
    CONSTRAINT fk_contract_templates_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_contract_templates_created FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_contract_templates_updated FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS generated_contracts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    collaborator_id INT UNSIGNED NOT NULL,
    template_id INT UNSIGNED NOT NULL,
    tipo_documento VARCHAR(120) NOT NULL,
    nombre_archivo VARCHAR(255) NOT NULL,
    ruta VARCHAR(500) NOT NULL,
    reemplazos_json TEXT NULL,
    total_reemplazos INT UNSIGNED NOT NULL DEFAULT 0,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_gen_company_collaborator (company_id, collaborator_id),
    KEY idx_gen_template (template_id),
    CONSTRAINT fk_generated_contracts_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_generated_contracts_collaborator FOREIGN KEY (collaborator_id) REFERENCES collaborators(id) ON DELETE CASCADE,
    CONSTRAINT fk_generated_contracts_template FOREIGN KEY (template_id) REFERENCES contract_templates(id) ON DELETE RESTRICT,
    CONSTRAINT fk_generated_contracts_created FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Liquidaciones
CREATE TABLE IF NOT EXISTS liquidations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    collaborator_id INT UNSIGNED NOT NULL,
    tipo_liquidacion VARCHAR(80) NOT NULL,
    fecha_calculo DATE NOT NULL,
    fecha_inicio DATE NULL,
    fecha_fin DATE NULL,
    salario_base DECIMAL(14,2) NULL,
    salario_modalidad VARCHAR(40) NULL,
    promedio_variable DECIMAL(14,2) NULL,
    auxilio_transporte DECIMAL(14,2) NULL,
    ibl_prestaciones DECIMAL(14,2) NULL,
    ibl_vacaciones DECIMAL(14,2) NULL,
    dias_base INT NULL,
    dias_vacaciones DECIMAL(10,2) NULL,
    dias_pendientes_vacaciones DECIMAL(10,2) NULL,
    valor_vacaciones DECIMAL(14,2) NULL,
    valor_prima DECIMAL(14,2) NULL,
    valor_cesantias DECIMAL(14,2) NULL,
    valor_intereses_cesantias DECIMAL(14,2) NULL,
    valor_salarios_pendientes DECIMAL(14,2) NULL,
    valor_deducciones DECIMAL(14,2) NULL,
    total_bruto DECIMAL(14,2) NULL,
    total_neto DECIMAL(14,2) NULL,
    estado VARCHAR(40) NOT NULL DEFAULT 'Borrador',
    observaciones TEXT NULL,
    parametros_json TEXT NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_liq_company_collaborator (company_id, collaborator_id),
    KEY idx_liq_tipo_fecha (tipo_liquidacion, fecha_calculo),
    CONSTRAINT fk_liq_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_liq_collaborator FOREIGN KEY (collaborator_id) REFERENCES collaborators(id) ON DELETE CASCADE,
    CONSTRAINT fk_liq_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_liq_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS liquidation_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    liquidation_id INT UNSIGNED NOT NULL,
    concepto VARCHAR(160) NOT NULL,
    base DECIMAL(14,2) NULL,
    dias DECIMAL(10,2) NULL,
    tasa DECIMAL(10,4) NULL,
    valor DECIMAL(14,2) NOT NULL DEFAULT 0,
    deduccion DECIMAL(14,2) NULL,
    neto DECIMAL(14,2) NULL,
    observacion TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_liq_items_liq FOREIGN KEY (liquidation_id) REFERENCES liquidations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vacation_periods (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    collaborator_id INT UNSIGNED NOT NULL,
    liquidation_id INT UNSIGNED NULL,
    periodo_num INT NULL,
    fecha_inicio_periodo DATE NULL,
    fecha_fin_periodo DATE NULL,
    dias_causados DECIMAL(10,2) NULL,
    dias_disfrutados DECIMAL(10,2) NULL,
    dias_compensados DECIMAL(10,2) NULL,
    dias_pendientes DECIMAL(10,2) NULL,
    modalidad VARCHAR(80) NULL,
    observaciones TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_vac_company_collaborator (company_id, collaborator_id),
    CONSTRAINT fk_vac_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_vac_collaborator FOREIGN KEY (collaborator_id) REFERENCES collaborators(id) ON DELETE CASCADE,
    CONSTRAINT fk_vac_liq FOREIGN KEY (liquidation_id) REFERENCES liquidations(id) ON DELETE SET NULL,
    CONSTRAINT fk_vac_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payroll_variables (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    collaborator_id INT UNSIGNED NOT NULL,
    periodo VARCHAR(7) NOT NULL,
    concepto VARCHAR(140) NOT NULL,
    valor DECIMAL(14,2) NOT NULL DEFAULT 0,
    factor_salarial TINYINT(1) NOT NULL DEFAULT 1,
    observaciones TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_payvar_company_collaborator (company_id, collaborator_id, periodo),
    CONSTRAINT fk_payvar_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_payvar_collaborator FOREIGN KEY (collaborator_id) REFERENCES collaborators(id) ON DELETE CASCADE,
    CONSTRAINT fk_payvar_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Estudios de seguridad
CREATE TABLE IF NOT EXISTS security_studies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id INT UNSIGNED NOT NULL,
    collaborator_id INT UNSIGNED NULL,
    fecha_estudio DATE NOT NULL,
    nombre_consulta VARCHAR(255) NOT NULL,
    tipo_documento VARCHAR(40) NULL,
    numero_documento VARCHAR(80) NOT NULL,
    fecha_expedicion_documento DATE NULL,
    tipo_prospecto VARCHAR(80) NULL,
    observacion TEXT NULL,
    concepto VARCHAR(120) NULL,
    estado VARCHAR(40) NOT NULL DEFAULT 'Borrador',
    documento_generado_ruta VARCHAR(255) NULL,
    total_reemplazos INT NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_security_company_doc (company_id, numero_documento),
    KEY idx_security_collaborator (collaborator_id),
    CONSTRAINT fk_security_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_security_collaborator FOREIGN KEY (collaborator_id) REFERENCES collaborators(id) ON DELETE SET NULL,
    CONSTRAINT fk_security_created FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_security_updated FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS security_study_checks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    study_id INT UNSIGNED NOT NULL,
    categoria VARCHAR(120) NULL,
    clave VARCHAR(80) NOT NULL,
    item VARCHAR(255) NOT NULL,
    fuente_url TEXT NULL,
    resultado VARCHAR(120) NULL,
    observacion TEXT NULL,
    evidencia_url TEXT NULL,
    fecha_validacion DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_security_checks_study FOREIGN KEY (study_id) REFERENCES security_studies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- v11 no requiere columnas nuevas adicionales.
-- Las mejoras de indicadores, botones de eliminación, filtros escribibles, ventanas flotantes
-- y variaciones transitorias de liquidación usan las tablas existentes y parametros_json.
