<?php
if (session_status() !== PHP_SESSION_ACTIVE) { session_start(); }
$configFile = __DIR__ . '/config.php';
if (!file_exists($configFile) && basename($_SERVER['SCRIPT_NAME'] ?? '') !== 'setup.php') {
    http_response_code(500);
    echo '<h1>Falta config.php</h1><p>Copie config.example.php como config.php y configure la conexión a MySQL.</p>';
    exit;
}
$config = file_exists($configFile) ? require $configFile : ['timezone'=>'America/Bogota','base_url'=>'','app_name'=>'Mandatos GE - Gestión Laboral'];
date_default_timezone_set($config['timezone'] ?? 'America/Bogota');

function cfg($key, $default=null) { global $config; return $config[$key] ?? $default; }
function base_url($path='') { $base = rtrim(cfg('base_url',''), '/'); return $base . '/' . ltrim($path, '/'); }
function e($v) { return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); }
function app_absolute_url($url=''){
    $url=trim((string)$url);
    if($url!=='' && preg_match('/^https?:\/\//i',$url)) return $url;
    $scheme=(!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS']!=='off')?'https':'http';
    $host=$_SERVER['HTTP_HOST'] ?? '';
    if($url==='') $url=$_SERVER['REQUEST_URI'] ?? '/';
    if(isset($url[0]) && $url[0]==='/') return $host ? $scheme.'://'.$host.$url : $url;
    $base=trim((string)cfg('base_url',''));
    if($base!=='') return rtrim($base,'/').'/'.ltrim($url,'/');
    $dir=rtrim(str_replace('\\','/',dirname($_SERVER['SCRIPT_NAME'] ?? '/')),'/');
    return $host ? $scheme.'://'.$host.($dir?'/'.ltrim($dir,'/'):'').'/'.ltrim($url,'/') : $url;
}
function whatsapp_clean_phone($phone,$country='57'){
    $digits=preg_replace('/\D+/','',(string)$phone);
    if($digits==='') return '';
    if(strlen($digits)===10) $digits=$country.$digits;
    if(strlen($digits)===12 && str_starts_with($digits,'57')) return $digits;
    if(strlen($digits)>10 && strlen($digits)<=15) return $digits;
    return $digits;
}
function whatsapp_web_url($phone='',$text=''){
    $phone=whatsapp_clean_phone($phone);
    $q=[]; if($phone!=='') $q['phone']=$phone; if(trim((string)$text)!=='') $q['text']=$text;
    return 'https://web.whatsapp.com/send'.($q ? '?'.http_build_query($q) : '');
}
function whatsapp_button_html($label,$phone='',$text='',$class='btn whatsapp-btn'){
    $url=whatsapp_web_url($phone,$text);
    return '<a class="'.e($class).'" target="_blank" rel="noopener" href="'.e($url).'">'.e($label).'</a>';
}
function whatsapp_phone_link_html($phone,$text=''){
    $phoneTrim=trim((string)$phone); if($phoneTrim==='') return '';
    $label=e($phoneTrim);
    $link='<a class="wa-phone-link" target="_blank" rel="noopener" href="'.e(whatsapp_web_url($phoneTrim,$text)).'">WhatsApp</a>';
    return '<span class="wa-phone-wrap"><span>'.$label.'</span>'.$link.'</span>';
}
function ficha_info_value_html($label,$value,$row=[]){
    if(mb_stripos((string)$label,'Teléfono')!==false){
        $msg='Buenos días '.trim((string)($row['nombre_completo'] ?? '')).'. Le escribimos desde Mandatos GE.';
        return whatsapp_phone_link_html($value,$msg) ?: e($value);
    }
    return e($value);
}
function money($v){ if($v === null || $v === '') return ''; return '$'.number_format((float)$v, 0, ',', '.'); }
function dt($v){ if(!$v) return ''; $ts=strtotime($v); return $ts ? date('d/m/Y', $ts) : ''; }
function dbdate($v){ return ($v ?? '') !== '' ? $v : null; }
function calc_quincena($date){
    if(!$date) return null;
    $ts = strtotime($date);
    if(!$ts) return null;
    return date('Y-m', $ts) . ((int)date('d', $ts) <= 15 ? '-Q1' : '-Q2');
}
function current_user(){ return $_SESSION['user'] ?? null; }
function is_super(){ $u=current_user(); return $u && $u['rol']==='super_admin'; }
function is_admin_general(){ $u=current_user(); return $u && $u['rol']==='admin_general'; }
function can_manage_all_companies(){ $u=current_user(); return $u && in_array($u['rol'], ['super_admin','admin_general'], true); }
function can_write(){ $u=current_user(); return $u && in_array($u['rol'], ['super_admin','admin_general','admin_empresa'], true); }
function csrf_token(){ if(empty($_SESSION['csrf'])) $_SESSION['csrf']=bin2hex(random_bytes(32)); return $_SESSION['csrf']; }
function csrf_field(){ return '<input type="hidden" name="csrf" value="'.e(csrf_token()).'">'; }
function check_csrf(){ if(($_POST['csrf'] ?? '') !== ($_SESSION['csrf'] ?? '')) { http_response_code(419); exit('Token de seguridad inválido. Regrese e intente nuevamente.'); } }

function back_button_html(){ $url=e(base_url('index.php?page=dashboard')); return '<button type="button" class="btn secondary back-btn" onclick="history.length>1?history.back():window.location.href=\''.$url.'\'">← Atrás</button>'; }
function delete_button($type,$id,$return=''){
    if(!can_write()) return '';
    $url=base_url('index.php?page=delete_record&type='.rawurlencode($type).'&id='.(int)$id.'&return='.rawurlencode($return ?: ($_SERVER['REQUEST_URI'] ?? 'index.php')));
    return '<a class="btn danger mini delete-link no-print" href="'.e($url).'" onclick="return confirm(\'¿Eliminar este registro? Esta acción no se puede deshacer.\')">Eliminar</a>';
}

function flash($type,$msg){ $_SESSION['flash'][]=['type'=>$type,'msg'=>$msg]; }
function show_flash(){ if(empty($_SESSION['flash'])) return; foreach($_SESSION['flash'] as $f){ echo '<div class="alert '.e($f['type']).'">'.e($f['msg']).'</div>'; } unset($_SESSION['flash']); }

function db(){
    static $pdo=null;
    if($pdo) return $pdo;
    $db = cfg('db');
    $dsn = 'mysql:host='.$db['host'].';dbname='.$db['name'].';charset='.($db['charset'] ?? 'utf8mb4');
    $pdo = new PDO($dsn, $db['user'], $db['pass'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
    return $pdo;
}
function column_exists($table, $column){
    $st=db()->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME=? AND COLUMN_NAME=?');
    $st->execute([$table,$column]);
    return (int)$st->fetchColumn() > 0;
}
function ensure_schema_v10(){
    static $done=false; if($done) return; $done=true;
    try{
        if(!column_exists('collaborators','salario_modalidad')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN salario_modalidad VARCHAR(40) NULL AFTER estado");
        }
        if(!column_exists('novelties','documento_text')){
            db()->exec("ALTER TABLE novelties ADD COLUMN documento_text VARCHAR(80) NULL AFTER collaborator_id");
        }
        if(!column_exists('novelties','colaborador_text')){
            db()->exec("ALTER TABLE novelties ADD COLUMN colaborador_text VARCHAR(255) NULL AFTER documento_text");
        }
        if(!column_exists('companies','logo_nombre')){
            db()->exec("ALTER TABLE companies ADD COLUMN logo_nombre VARCHAR(255) NULL AFTER estado");
        }
        if(!column_exists('companies','logo_ruta')){
            db()->exec("ALTER TABLE companies ADD COLUMN logo_ruta VARCHAR(255) NULL AFTER logo_nombre");
        }
        if(!column_exists('collaborators','fecha_expedicion_documento')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN fecha_expedicion_documento DATE NULL AFTER numero_documento");
        }
        if(!column_exists('collaborators','foto_nombre')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN foto_nombre VARCHAR(255) NULL AFTER fecha_expedicion_documento");
        }
        if(!column_exists('collaborators','foto_ruta')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN foto_ruta VARCHAR(255) NULL AFTER foto_nombre");
        }
        if(!column_exists('collaborators','estudio_seguridad_id')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN estudio_seguridad_id INT UNSIGNED NULL");
        }
        if(!column_exists('collaborators','estudio_seguridad_resultado')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN estudio_seguridad_resultado VARCHAR(80) NULL");
        }
        if(!column_exists('collaborators','estudio_seguridad_fecha')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN estudio_seguridad_fecha DATE NULL");
        }
        if(!column_exists('collaborators','estudio_seguridad_nombre')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN estudio_seguridad_nombre VARCHAR(255) NULL");
        }
        if(!column_exists('collaborators','estudio_seguridad_ruta')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN estudio_seguridad_ruta VARCHAR(255) NULL");
        }
        if(!column_exists('collaborators','estudio_seguridad_observacion')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN estudio_seguridad_observacion TEXT NULL");
        }
        if(!column_exists('collaborators','aceptacion_condiciones_estado')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN aceptacion_condiciones_estado VARCHAR(80) NULL");
        }
        if(!column_exists('collaborators','aceptacion_condiciones_fecha')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN aceptacion_condiciones_fecha DATE NULL");
        }
        if(!column_exists('collaborators','aceptacion_condiciones_nombre')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN aceptacion_condiciones_nombre VARCHAR(255) NULL");
        }
        if(!column_exists('collaborators','aceptacion_condiciones_ruta')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN aceptacion_condiciones_ruta VARCHAR(255) NULL");
        }
        if(!column_exists('collaborators','aceptacion_condiciones_observacion')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN aceptacion_condiciones_observacion TEXT NULL");
        }
        db()->exec("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");
        db()->exec("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");
        if(!column_exists('companies','direccion')){
            db()->exec("ALTER TABLE companies ADD COLUMN direccion VARCHAR(255) NULL AFTER ciudad");
        }
        if(!column_exists('collaborators','direccion')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN direccion VARCHAR(255) NULL AFTER cargo");
        }
        if(!column_exists('collaborators','telefono')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN telefono VARCHAR(80) NULL AFTER direccion");
        }
        if(!column_exists('collaborators','correo_electronico')){
            db()->exec("ALTER TABLE collaborators ADD COLUMN correo_electronico VARCHAR(160) NULL AFTER telefono");
        }
        db()->exec("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");
        db()->exec("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");
        db()->exec("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");
        db()->exec("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");
        db()->exec("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");
        db()->exec("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");

    }catch(Throwable $e){ /* el diagnóstico o migración manual mostrará el detalle */ }
}

function ensure_security_link_schema_v14(){
    static $done=false; if($done) return; $done=true;
    $columns=[
        ['collaborators','fecha_expedicion_documento','DATE NULL'],
        ['collaborators','direccion','VARCHAR(255) NULL'],
        ['collaborators','telefono','VARCHAR(80) NULL'],
        ['collaborators','correo_electronico','VARCHAR(160) NULL'],
        ['collaborators','estudio_seguridad_id','INT UNSIGNED NULL'],
        ['collaborators','estudio_seguridad_resultado','VARCHAR(80) NULL'],
        ['collaborators','estudio_seguridad_fecha','DATE NULL'],
        ['collaborators','estudio_seguridad_nombre','VARCHAR(255) NULL'],
        ['collaborators','estudio_seguridad_ruta','VARCHAR(500) NULL'],
        ['collaborators','estudio_seguridad_observacion','TEXT NULL'],
        ['collaborators','aceptacion_condiciones_estado','VARCHAR(80) NULL'],
        ['collaborators','aceptacion_condiciones_fecha','DATE NULL'],
        ['collaborators','aceptacion_condiciones_nombre','VARCHAR(255) NULL'],
        ['collaborators','aceptacion_condiciones_ruta','VARCHAR(500) NULL'],
        ['collaborators','aceptacion_condiciones_observacion','TEXT NULL'],
    ];
    foreach($columns as [$table,$column,$definition]){
        try{ if(!column_exists($table,$column)){ db()->exec("ALTER TABLE `$table` ADD COLUMN `$column` $definition"); } }catch(Throwable $e){ }
    }
    try{
        db()->exec("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(500) 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)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }catch(Throwable $e){ }
    try{
        db()->exec("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,
            KEY idx_security_checks_study (study_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    }catch(Throwable $e){ }
}

function require_login(){ if(!current_user()){ header('Location: '.base_url('index.php?page=login')); exit; } ensure_schema_v10(); ensure_security_link_schema_v14(); }
function user_restricts_company(){ $u=current_user(); return $u && !can_manage_all_companies() && !empty($u['company_id']); }
function can_read_company($company_id){ $u=current_user(); if(!$u) return false; return can_manage_all_companies() || empty($u['company_id']) || (int)$u['company_id']===(int)$company_id; }
function company_scope_label(){ $u=current_user(); if(!$u) return ''; if(is_super()) return 'Todas las empresas · Superadministrador'; if(is_admin_general()) return 'Todas las empresas · Administrador general'; if(empty($u['company_id'])) return 'Todas las empresas · Consulta general'; return $u['company_name'] ?? 'Empresa asignada'; }
function require_write(){ require_login(); if(!can_write()){ http_response_code(403); exit('No tiene permisos de escritura.'); } }
function audit($accion,$tabla=null,$registro_id=null,$detalle=null,$company_id=null){
    try{
        $u=current_user();
        db()->prepare('INSERT INTO audit_logs(user_id, company_id, accion, tabla_afectada, registro_id, detalle, ip, user_agent) VALUES(?,?,?,?,?,?,?,?)')
            ->execute([$u['id'] ?? null, $company_id ?: ($u['company_id'] ?? null), $accion, $tabla, $registro_id, $detalle, $_SERVER['REMOTE_ADDR'] ?? null, substr($_SERVER['HTTP_USER_AGENT'] ?? '',0,255)]);
    }catch(Throwable $e){ }
}
function user_company_filter_sql($alias=''){
    $u=current_user();
    if(!$u || !user_restricts_company()) return ['sql'=>'', 'params'=>[]];
    $prefix = $alias ? $alias.'.' : '';
    return ['sql'=>' AND '.$prefix.'company_id = ? ', 'params'=>[$u['company_id']]];
}
function get_companies(){
    $u=current_user();
    if($u && user_restricts_company()){
        $st=db()->prepare('SELECT * FROM companies WHERE id=? ORDER BY nombre'); $st->execute([$u['company_id']]); return $st->fetchAll();
    }
    return db()->query('SELECT * FROM companies ORDER BY nombre')->fetchAll();
}
function get_company($id){
    if(!$id) return null;
    $st=db()->prepare('SELECT * FROM companies WHERE id=? LIMIT 1'); $st->execute([(int)$id]);
    $c=$st->fetch(); return $c ?: null;
}
function public_file_url($relative){ if(!$relative) return ''; return base_url($relative); }
function mandatos_logo_url(){ return base_url('assets/mandatos_logo.png'); }
function mandatos_logo_sidebar_url(){ return base_url('assets/mandatos_logo1.png'); }
function company_logo_url($company_id){ $c=get_company($company_id); return $c && !empty($c['logo_ruta']) ? public_file_url($c['logo_ruta']) : ''; }
function company_options($selected=null){
    $html=''; foreach(get_companies() as $c){ $sel=((string)$selected===(string)$c['id'])?' selected':''; $html.='<option value="'.e($c['id']).'"'.$sel.'>'.e($c['nombre']).'</option>'; } return $html;
}
function status_pill($status){
    $s=(string)$status; $class='gray';
    if(in_array($s,['Activo','Activa','Cerrada','Registrada'],true)) $class='ok';
    if(in_array($s,['En revisión','Suspendido','En selección'],true)) $class='warn';
    if(in_array($s,['Retirado','Inactivo','Inactiva','Anulada'],true)) $class='red';
    return '<span class="pill '.$class.'">'.e($s).'</span>';
}
function option_list($items, $selected=null, $blank=null){
    $html=''; if($blank !== null) $html.='<option value="">'.e($blank).'</option>';
    foreach($items as $s){ $sel=((string)$selected===(string)$s)?' selected':''; $html.='<option value="'.e($s).'"'.$sel.'>'.e($s).'</option>'; }
    return $html;
}
function collaborator_name_expr(){ return "COALESCE(NULLIF(nombre_completo,''), TRIM(CONCAT(COALESCE(primer_nombre,''),' ',COALESCE(segundo_nombre,''),' ',COALESCE(primer_apellido,''),' ',COALESCE(segundo_apellido,''))))"; }
function distinct_options($table, $column, $selected=null, $blank='Todos'){
    $u=current_user(); $sql="SELECT DISTINCT $column val FROM $table WHERE $column IS NOT NULL AND $column<>''"; $params=[];
    if($u && user_restricts_company() && in_array($table,['collaborators','novelties'],true)){ $sql.=' AND company_id=?'; $params[]=$u['company_id']; }
    $sql.=" ORDER BY $column"; $st=db()->prepare($sql); $st->execute($params); $vals=array_map(fn($r)=>$r['val'],$st->fetchAll());
    return option_list($vals,$selected,$blank);
}
function collaborator_options($company_id=null, $selected=null){
    $u=current_user(); $sql='SELECT id,company_id,nombre_completo,numero_documento FROM collaborators WHERE 1=1'; $params=[];
    if($u && user_restricts_company()){ $sql.=' AND company_id=?'; $params[]=$u['company_id']; }
    elseif($company_id){ $sql.=' AND company_id=?'; $params[]=$company_id; }
    $sql.=' ORDER BY numero_documento, nombre_completo LIMIT 3000'; $st=db()->prepare($sql); $st->execute($params);
    $html='<option value="">Todos / sin filtro</option>';
    foreach($st->fetchAll() as $c){ $sel=((string)$selected===(string)$c['id'])?' selected':''; $html.='<option data-company="'.e($c['company_id']).'" value="'.e($c['id']).'"'.$sel.'>'.e($c['numero_documento'].' - '.$c['nombre_completo']).'</option>'; }
    return $html;
}
function upload_file($field, $company_id, $collaborator_id=null, $novelty_id=null){
    if(empty($_FILES[$field]) || $_FILES[$field]['error'] === UPLOAD_ERR_NO_FILE) return [null,null];
    $f=$_FILES[$field];
    if($f['error'] !== UPLOAD_ERR_OK) throw new RuntimeException('No fue posible cargar el soporte.');
    $max=(int)cfg('max_upload_mb',8)*1024*1024; if($f['size']>$max) throw new RuntimeException('El archivo supera el tamaño máximo permitido.');
    $allowed=['application/pdf','image/jpeg','image/png','application/vnd.openxmlformats-officedocument.wordprocessingml.document','application/msword','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet','text/csv'];
    $mime=mime_content_type($f['tmp_name']);
    if(!in_array($mime,$allowed,true)) throw new RuntimeException('Tipo de archivo no permitido. Use PDF, imagen, Word, Excel o CSV.');
    $ext=pathinfo($f['name'],PATHINFO_EXTENSION);
    $safe=bin2hex(random_bytes(12)).($ext?'.'.$ext:'');
    $dir=rtrim(cfg('upload_dir'),'/').'/company_'.$company_id.'/'.date('Y/m');
    if(!is_dir($dir)) mkdir($dir,0775,true);
    $dest=$dir.'/'.$safe; move_uploaded_file($f['tmp_name'],$dest);
    $rel=str_replace(rtrim(__DIR__,'/').'/', '', $dest);
    db()->prepare('INSERT INTO documents(company_id, collaborator_id, novelty_id, tipo_documento, nombre_original, ruta, mime_type, size_bytes, created_by) VALUES(?,?,?,?,?,?,?,?,?)')
        ->execute([$company_id,$collaborator_id,$novelty_id,'Soporte',$f['name'],$rel,$mime,$f['size'],current_user()['id'] ?? null]);
    return [$f['name'],$rel];
}
function header_html($title){ require_login(); $u=current_user(); $page=$_GET['page'] ?? 'dashboard'; ?>
<!doctype html><html lang="es"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1"><title><?= e($title) ?> - <?= e(cfg('app_name')) ?></title><link rel="stylesheet" href="<?= e(base_url('assets/style.css?v='.filemtime(__DIR__.'/assets/style.css'))) ?>"></head><body><div class="layout"><aside class="sidebar"><div class="brand-panel"><img src="<?= e(mandatos_logo_sidebar_url()) ?>" alt="Mandatos" class="brand-logo"><div class="brand-title">Mandatos GE</div><div class="subtitle">Gestión Laboral y Novedades de Personal</div></div><nav class="nav"><small>Operación</small>
<a class="<?= $page==='dashboard'?'active':'' ?>" href="<?= e(base_url('index.php?page=dashboard')) ?>">Panel principal</a>
<a class="<?= $page==='collaborators'?'active':'' ?>" href="<?= e(base_url('index.php?page=collaborators')) ?>">Colaboradores</a>
<a class="<?= basename($_SERVER['SCRIPT_NAME'] ?? '')==='contracts.php'?'active':'' ?>" href="<?= e(base_url('contracts.php')) ?>">Contratos</a>
<a class="<?= $page==='liquidations'?'active':'' ?>" href="<?= e(base_url('index.php?page=liquidations')) ?>">Liquidaciones</a>
<a class="<?= basename($_SERVER['SCRIPT_NAME'] ?? '')==='security_studies.php'?'active':'' ?>" href="<?= e(base_url('security_studies.php')) ?>">Estudios de seguridad</a>
<a class="<?= $page==='novelties'?'active':'' ?>" href="<?= e(base_url('index.php?page=novelties')) ?>">Novedades</a>
<a class="<?= $page==='reports'?'active':'' ?>" href="<?= e(base_url('index.php?page=reports')) ?>">Informes</a>
<a class="<?= $page==='indicators'?'active':'' ?>" href="<?= e(base_url('index.php?page=indicators')) ?>">Indicadores</a>
<a class="<?= $page==='import'?'active':'' ?>" href="<?= e(base_url('index.php?page=import')) ?>">Importar CSV</a>
<?php if(is_super()): ?><small>Administración</small>
<a class="<?= $page==='companies'?'active':'' ?>" href="<?= e(base_url('index.php?page=companies')) ?>">Empresas</a>
<a class="<?= $page==='users'?'active':'' ?>" href="<?= e(base_url('index.php?page=users')) ?>">Usuarios</a>
<a class="<?= $page==='audit'?'active':'' ?>" href="<?= e(base_url('index.php?page=audit')) ?>">Auditoría</a><?php endif; ?>
<div class="logout"><a href="<?= e(base_url('index.php?page=logout')) ?>">Cerrar sesión</a></div></nav></aside><main class="main"><div class="topbar no-print"><div class="topbar-left"><?= back_button_html() ?><div><h1><?= e($title) ?></h1><div class="scope-chip"><?= e(company_scope_label()) ?></div></div></div><div class="userbox"><?= e($u['nombre'] ?? '') ?><br><span><?= e($u['rol'] ?? '') ?></span></div></div><div class="content-scroll"><?php show_flash(); }
function footer_html(){ ?><script>
function copyTable(id){
  const table=document.getElementById(id); if(!table){return;}
  const rows=[...table.querySelectorAll('tr')].map(tr=>[...tr.children].filter(td=>!td.classList.contains('no-print')).map(td=>td.innerText.replace(/\s+/g,' ').trim()).join('\t')).join('\n');
  if(navigator.clipboard){navigator.clipboard.writeText(rows).then(()=>alert('Tabla copiada. Puede pegarla en Word o Excel.'));}
  else {const ta=document.createElement('textarea'); ta.value=rows; document.body.appendChild(ta); ta.select(); document.execCommand('copy'); ta.remove(); alert('Tabla copiada.');}
}
function printReport(){ window.print(); }
function normalizeSortValue(text){
  text=(text||'').trim();
  const m=text.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/); if(m){ return `${m[3]}-${m[2].padStart(2,'0')}-${m[1].padStart(2,'0')}`; }
  const money=text.replace(/[$.\s]/g,'').replace(',', '.'); if(money!=='' && !isNaN(money)){ return parseFloat(money); }
  return text.toLocaleLowerCase('es');
}
function makeSortableTables(){
  document.querySelectorAll('table.sortable, .table-wrap table, #tabla-informe, #tabla-colaboradores').forEach(table=>{
    if(table.dataset.sortReady==='1') return; table.dataset.sortReady='1';
    const headers=table.querySelectorAll('thead th'); if(!headers.length) return;
    headers.forEach((th,idx)=>{
      if(th.classList.contains('no-sort')) return;
      th.classList.add('sortable-th'); th.title='Ordenar / filtrar por esta columna';
      th.addEventListener('click',()=>{
        const tbody=table.tBodies[0]; if(!tbody) return;
        const asc=th.dataset.asc!=='1'; headers.forEach(h=>h.dataset.asc=''); th.dataset.asc=asc?'1':'0';
        const rows=[...tbody.rows];
        rows.sort((a,b)=>{
          const av=normalizeSortValue(a.cells[idx]?.innerText||''); const bv=normalizeSortValue(b.cells[idx]?.innerText||'');
          if(typeof av==='number' && typeof bv==='number') return asc?av-bv:bv-av;
          return asc ? String(av).localeCompare(String(bv),'es',{numeric:true}) : String(bv).localeCompare(String(av),'es',{numeric:true});
        });
        rows.forEach(r=>tbody.appendChild(r));
      });
    });
  });
}
function filterCollaboratorsByCompany(){
  const company=document.getElementById('companySelect'); const coll=document.getElementById('collaboratorSelect'); if(!company||!coll) return;
  const selectedCompany=company.value;
  [...coll.options].forEach(opt=>{ if(!opt.value){opt.hidden=false; return;} opt.hidden=(selectedCompany && opt.dataset.company!==selectedCompany); });
  if(coll.selectedOptions[0] && coll.selectedOptions[0].hidden){ coll.value=''; }
}
function addContractButtonsToCollaborators(){
  // Desactivado: el enlace a contrato solo debe mostrarse donde esté impreso por PHP
  // en la columna Acciones de Colaboradores o en la ficha del colaborador.
  // Evita que aparezca "Contrato" junto al nombre en Novedades y evita duplicados.
  return;
}
function openFloating(url,name='consulta'){ window.open(url,name,'width=1120,height=760,left=80,top=60,menubar=no,toolbar=yes,location=yes,status=yes,scrollbars=yes,resizable=yes'); return false; }
function enhanceCollaboratorCombos(){
  document.querySelectorAll('select.combo-collaborator, select#collaboratorSelect').forEach(sel=>{
    if(sel.dataset.comboReady==='1') return; sel.dataset.comboReady='1';
    const originalName=sel.getAttribute('name'); if(!originalName) return;
    const wasRequired=sel.required;
    const hidden=document.createElement('input'); hidden.type='hidden'; hidden.name=originalName; hidden.value=sel.value;
    sel.removeAttribute('name'); sel.required=false;
    const input=document.createElement('input'); input.type='text'; input.className='combo-input'; input.placeholder='Escriba o seleccione colaborador'; if(wasRequired){ input.required=true; }
    const listId='dl_'+Math.random().toString(36).slice(2); input.setAttribute('list',listId);
    const dl=document.createElement('datalist'); dl.id=listId;
    [...sel.options].forEach(opt=>{ if(!opt.value) return; const o=document.createElement('option'); o.value=opt.textContent.trim(); o.dataset.value=opt.value; dl.appendChild(o); if(opt.selected){ input.value=opt.textContent.trim(); hidden.value=opt.value; } });
    input.addEventListener('input',()=>{ const val=input.value.trim(); const match=[...sel.options].find(o=>o.textContent.trim()===val || o.value===val); hidden.value=match?match.value:''; if(match){sel.value=match.value;} });
    input.addEventListener('change',()=>{ const val=input.value.trim(); const match=[...sel.options].find(o=>o.textContent.trim()===val || o.value===val); hidden.value=match?match.value:''; if(match){sel.value=match.value; sel.dispatchEvent(new Event('change'));} });
    sel.style.display='none'; sel.insertAdjacentElement('afterend',dl); sel.insertAdjacentElement('afterend',input); sel.insertAdjacentElement('afterend',hidden);
  });
}
document.addEventListener('DOMContentLoaded',()=>{ makeSortableTables(); filterCollaboratorsByCompany(); enhanceCollaboratorCombos(); const c=document.getElementById('companySelect'); if(c){c.addEventListener('change',()=>{filterCollaboratorsByCompany();});} });
</script></div></main></div></body></html><?php }
