<?php
include('conexion.php');
include_once('nomina_helpers.php');
include_once('nomina_legal_helpers.php');
include_once('nomina_prestaciones_helpers.php');
include_once('nomina_prestaciones_master_helpers.php');
if (session_status() !== PHP_SESSION_ACTIVE) { session_start(); }
if (!isset($_SESSION['usuario'])) { header('Location: login.php'); exit; }
nomina_ensure_cargos_schema($conexion);
nomina_ensure_prestaciones_schema($conexion);
nomina_pm_ensure_schema($conexion);

$fechaCorte = nomina_date_to_iso($_GET['fecha_corte'] ?? date('Y-m-d')) ?: date('Y-m-d');
$tasaRef = nomina_normalize_decimal($_GET['tasa_bcv_ref'] ?? 0, 0);
$bcvInfo = nomina_bcv_auto_refresh($conexion, false);
$bcvLatest = $bcvInfo['latest'] ?? null;
if ($tasaRef <= 0 && $bcvLatest && isset($bcvLatest['rate_value'])) { $tasaRef = (float)$bcvLatest['rate_value']; }
if ($tasaRef <= 0) { $tasaRef = 1; }
$utilidadesDiasRef = nomina_normalize_decimal($_GET['utilidades_dias_ref'] ?? 30, 30);
$personas = nomina_fetch_active_people($conexion);
$invalidHireDatePeople = function_exists('nomina_pm_invalid_hire_date_people') ? nomina_pm_invalid_hire_date_people($conexion, $fechaCorte) : [];
$invalidHireDateIds = []; foreach ($invalidHireDatePeople as $iw) { $invalidHireDateIds[(int)$iw['id']] = true; }
$orphanSnapshotCounts = function_exists('nomina_pm_count_orphan_snapshots') ? nomina_pm_count_orphan_snapshots($conexion) : [];
$mojibakeCounts = function_exists('nomina_pm_count_mojibake_rows') ? nomina_pm_count_mojibake_rows($conexion) : [];
$balances = [];
$workersPending = 0; $pendingEvents = 0; $pendingBs = 0.0; $withHistory = 0;
foreach ($personas as $persona) {
    if (isset($invalidHireDateIds[(int)$persona['id']])) { continue; }
    $bal = nomina_prestaciones_calculate_balance($conexion, $persona, $fechaCorte, $tasaRef, $utilidadesDiasRef);
    $balances[(int)$persona['id']] = $bal;
    if (nomina_prestaciones_has_pending_base($bal)) { $workersPending++; $pendingEvents += (int)$bal['missing_quarters'] + (int)$bal['missing_annuals']; $pendingBs += (float)$bal['pending_control_bs']; }
    if (count(nomina_pm_fetch_salary_history($conexion, (int)$persona['id'], 1)) > 0) { $withHistory++; }
}
$rateCount = nomina_pm_count_interest_rates($conexion);
$salaryCount = nomina_pm_recent_salary_history_count($conexion);
$eventCount = nomina_pm_count_registered_events($conexion);
$interestCount = 0; $interestSum = 0.0;
$res = mysqli_query($conexion, "SELECT COUNT(*) AS c, COALESCE(SUM(interes_bs),0) AS s FROM nomina_prestaciones_intereses WHERE estado='REGISTRADO'");
if ($res && ($row = mysqli_fetch_assoc($res))) { $interestCount = (int)$row['c']; $interestSum = (float)$row['s']; }
$missingRates = nomina_pm_missing_rate_months_from_events($conexion, $fechaCorte);
$recentEvents = nomina_prestaciones_fetch_recent_events($conexion, 20);
$rates = nomina_pm_fetch_interest_rates($conexion, 12);
?>
<!doctype html>
<html lang="es">
<head>
<meta charset="utf-8">
<title>Auditoría prestaciones sociales</title>
<meta name="viewport" content="width=device-width,initial-scale=1">
<style>
body{font-family:Arial,Helvetica,sans-serif;background:#f2f5f9;color:#243b53;margin:0}.wrap{max-width:1380px;margin:0 auto;padding:24px}.card{background:#fff;border:1px solid #d9e2ec;border-radius:18px;padding:20px;margin-bottom:18px;box-shadow:0 8px 24px rgba(15,23,42,.05)}.top{display:flex;justify-content:space-between;gap:14px;align-items:flex-start;flex-wrap:wrap}.top h1{margin:0;font-size:22px}.muted,.mini{color:#6b7c93}.mini{font-size:12px}.btn{display:inline-block;background:#1f4aa8;color:#fff;text-decoration:none;border:none;border-radius:14px;padding:12px 18px;font-weight:700;cursor:pointer}.btn.secondary{background:#fff;color:#1f4aa8;border:1px solid #b7c6e3}.btn.ghost{background:#eef3ff;color:#1f4aa8}.stats,.filters{display:grid;gap:14px}.stats{grid-template-columns:repeat(6,1fr)}.filters{grid-template-columns:1fr 1fr 1fr auto}.field label{display:block;font-weight:700;margin-bottom:6px}.field input{width:100%;box-sizing:border-box;border:1px solid #cbd5e1;border-radius:12px;padding:11px 12px;font-size:15px}.table-wrap{overflow:auto}.table{width:100%;border-collapse:separate;border-spacing:0}.table th,.table td{padding:11px 10px;border-bottom:1px solid #e5edf5;text-align:left;vertical-align:top}.table th{background:#f8fafc;font-size:13px}.stat{border:1px solid #d9e2ec;border-radius:14px;padding:14px}.stat .v{font-size:25px;font-weight:800;margin-bottom:6px}.tot{font-weight:800}.note{padding:12px 14px;border-radius:12px;background:#f8fafc;border:1px dashed #cbd5e1;color:#486581}.warn{color:#92400e}.good{color:#166534}.danger{color:#991b1b}@media(max-width:1000px){.filters,.stats{grid-template-columns:1fr}}@media print{.no-print{display:none}.wrap{max-width:none;padding:0}.card{box-shadow:none;break-inside:avoid}}
</style>
</head>
<body>
<div class="wrap">
  <div class="card top">
    <div><h1>Auditoría de prestaciones sociales v7.3</h1><div class="muted">Control de preparación legal: historial salarial, eventos base, tasas BCV de interés, intereses y pendientes por trabajador.</div></div>
    <div class="no-print"><a class="btn secondary" href="nomina_prestaciones.php">Prestaciones</a> <a class="btn ghost" href="nomina_tasas_interes_bcv.php">Tasas BCV</a> <a class="btn ghost" href="nomina_reporte_legal_trabajador.php">Reporte legal</a> <button class="btn" onclick="window.print()">Imprimir</button></div>
  </div>
  <div class="card no-print">
    <form class="filters" method="get">
      <div class="field"><label>Fecha corte</label><input type="date" name="fecha_corte" value="<?php echo h($fechaCorte); ?>"></div>
      <div class="field"><label>Tasa BCV ref.</label><input name="tasa_bcv_ref" value="<?php echo h(number_format($tasaRef,4,'.','')); ?>"></div>
      <div class="field"><label>Días utilidades ref.</label><input name="utilidades_dias_ref" value="<?php echo h((string)$utilidadesDiasRef); ?>"></div>
      <div class="field"><label>&nbsp;</label><button class="btn" type="submit">Actualizar</button></div>
    </form>
  </div>
  <div class="card"><div class="stats">
    <div class="stat"><div class="v"><?php echo count($personas); ?></div><div class="mini">Trabajadores activos</div></div>
    <div class="stat"><div class="v <?php echo count($invalidHireDatePeople) ? 'danger':'good'; ?>"><?php echo count($invalidHireDatePeople); ?></div><div class="mini">Sin fecha ingreso válida</div></div>
    <div class="stat"><div class="v <?php echo $workersPending ? 'warn':'good'; ?>"><?php echo (int)$workersPending; ?></div><div class="mini">Con base pendiente</div></div>
    <div class="stat"><div class="v"><?php echo (int)$salaryCount; ?></div><div class="mini">Registros historial salarial</div></div>
    <div class="stat"><div class="v"><?php echo (int)$rateCount; ?></div><div class="mini">Tasas BCV cargadas</div></div>
    <div class="stat"><div class="v"><?php echo h(number_format($interestSum,2,',','.')); ?></div><div class="mini">Intereses registrados Bs</div></div>
  </div>
  <?php if ($missingRates): ?><div class="note" style="margin-top:14px;background:#fffaf2;border-color:#fde68a;">Faltan tasas BCV para estos meses: <b><?php echo h(implode(', ', array_slice($missingRates,0,24))); ?><?php echo count($missingRates)>24?'...':''; ?></b>.</div><?php else: ?><div class="note" style="margin-top:14px;background:#eefaf5;border-color:#86efac;">No se detectan meses de tasa pendientes para el capital registrado.</div><?php endif; ?>
  <?php if (!empty($invalidHireDatePeople)): ?><div class="note" style="margin-top:14px;background:#fff7f7;border-color:#fecaca;"><b>Bloqueo auditor:</b> hay <?php echo count($invalidHireDatePeople); ?> trabajadores activos sin fecha de ingreso válida. No deben generarse prestaciones hasta corregir la ficha.</div><?php endif; ?>
  <?php $orphanTotal = array_sum($orphanSnapshotCounts ?: []); if ($orphanTotal > 0): ?><div class="note" style="margin-top:14px;background:#fffaf2;border-color:#fde68a;"><b>Snapshots huérfanos:</b> <?php echo (int)$orphanTotal; ?> registros asociados a corridas inexistentes. Usa la limpieza desde Prestaciones o el SQL v7.3.</div><?php endif; ?>
  <?php $mojiTotal = array_sum($mojibakeCounts ?: []); if ($mojiTotal > 0): ?><div class="note" style="margin-top:14px;background:#fffaf2;border-color:#fde68a;"><b>Textos con posible UTF/mojibake:</b> <?php echo (int)$mojiTotal; ?> filas. Conviene reparar antes de imprimir reportes legales.</div><?php endif; ?>
  </div>
  <?php if (!empty($invalidHireDatePeople)): ?>
  <div class="card"><h2 style="margin-top:0;color:#991b1b;">Trabajadores bloqueados por fecha de ingreso</h2><div class="table-wrap"><table class="table"><thead><tr><th>Trabajador</th><th>Cédula</th><th>Cargo</th><th>Fecha ingreso</th><th>Acción</th></tr></thead><tbody><?php foreach ($invalidHireDatePeople as $iw): ?><tr><td><b><?php echo h(trim(($iw['apellido']??'').' '.($iw['nombre']??''))); ?></b></td><td><?php echo h($iw['cedula']??''); ?></td><td><?php echo h($iw['cargo']??''); ?></td><td class="danger"><?php echo h($iw['fecha_ingreso']??''); ?></td><td><a class="btn secondary" href="editar.php?id=<?php echo (int)$iw['id']; ?>">Corregir ficha</a></td></tr><?php endforeach; ?></tbody></table></div></div>
  <?php endif; ?>
  <div class="card"><h2 style="margin-top:0;">Trabajadores con pendientes de control base</h2><div class="table-wrap"><table class="table"><thead><tr><th>Trabajador</th><th>Ingreso</th><th>Pendientes</th><th>Base favorable Bs</th><th>Registrado Bs</th><th>Pendiente Bs</th><th>Historial salarial</th><th></th></tr></thead><tbody>
    <?php foreach ($balances as $pid=>$bal): if (!nomina_prestaciones_has_pending_base($bal)) { continue; } $p=$bal['persona']; $hist=count(nomina_pm_fetch_salary_history($conexion,(int)$pid,1)); ?>
      <tr><td><b><?php echo h(trim(($p['apellido']??'').' '.($p['nombre']??''))); ?></b><div class="mini"><?php echo h($p['cedula']??''); ?></div></td><td><?php echo h($p['fecha_ingreso']??''); ?></td><td><?php echo (int)$bal['missing_quarters']; ?> trimestrales / <?php echo (int)$bal['missing_annuals']; ?> anuales</td><td class="tot"><?php echo h(number_format((float)$bal['favorable_base_bs'],2,',','.')); ?></td><td><?php echo h(number_format((float)$bal['guarantee_registered_bs'],2,',','.')); ?></td><td class="warn tot"><?php echo h(number_format((float)$bal['pending_control_bs'],2,',','.')); ?></td><td><?php echo $hist ? '<span class="good">Sí</span>' : '<span class="warn">No</span>'; ?></td><td><a class="btn secondary" href="nomina_prestaciones.php?persona_id=<?php echo (int)$pid; ?>&fecha_corte=<?php echo h(urlencode($fechaCorte)); ?>&tasa_bcv_ref=<?php echo h(urlencode((string)$tasaRef)); ?>">Preparar</a></td></tr>
    <?php endforeach; ?>
    <?php if (!$workersPending): ?><tr><td colspan="8"><div class="note">No hay trabajadores con base pendiente a esta fecha de corte.</div></td></tr><?php endif; ?>
  </tbody></table></div></div>
  <div class="card"><h2 style="margin-top:0;">Últimas tasas BCV de interés</h2><div class="table-wrap"><table class="table"><thead><tr><th>Mes</th><th>Activa %</th><th>Pasiva %</th><th>Promedio %</th><th>Fuente</th></tr></thead><tbody><?php foreach ($rates as $r): ?><tr><td><b><?php echo h($r['periodo_ym']); ?></b></td><td><?php echo h(number_format((float)$r['tasa_activa_anual'],4,',','.')); ?></td><td><?php echo h(number_format((float)$r['tasa_pasiva_anual'],4,',','.')); ?></td><td class="tot"><?php echo h(number_format((float)$r['tasa_promedio_anual'],4,',','.')); ?></td><td><?php echo h($r['fuente_url']??''); ?></td></tr><?php endforeach; ?><?php if (!$rates): ?><tr><td colspan="5"><div class="note">No hay tasas cargadas.</div></td></tr><?php endif; ?></tbody></table></div></div>
  <div class="card"><h2 style="margin-top:0;">Registros recientes de prestaciones</h2><div class="table-wrap"><table class="table"><thead><tr><th>Fecha</th><th>Trabajador</th><th>Tipo</th><th>Ref.</th><th>Días</th><th>Monto Bs</th><th>Observación</th></tr></thead><tbody><?php foreach ($recentEvents as $ev): ?><tr><td><?php echo h($ev['fecha_documento']); ?></td><td><b><?php echo h(trim(($ev['apellido']??'').' '.($ev['nombre']??''))); ?></b><div class="mini"><?php echo h($ev['cedula']??''); ?></div></td><td><?php echo h(nomina_prestaciones_event_type_label($ev['tipo'])); ?></td><td><?php echo h($ev['periodo_ref']); ?></td><td><?php echo h(number_format((float)$ev['dias'],2,',','.')); ?></td><td class="tot"><?php echo h(number_format((float)$ev['monto_bs'],2,',','.')); ?></td><td><?php echo h($ev['observacion']??''); ?></td></tr><?php endforeach; ?><?php if (!$recentEvents): ?><tr><td colspan="7"><div class="note">Sin eventos recientes.</div></td></tr><?php endif; ?></tbody></table></div></div>
</div>
</body>
</html>
