<?php
// modules/coordinador/expedientes.php
require_once __DIR__ . '/../../config/app.php';
require_once __DIR__ . '/../../includes/auth.php';
require_once __DIR__ . '/../../includes/functions.php';
require_once __DIR__ . '/../../includes/flash.php';
requireRol(ROL_COORDINADOR);

$db        = getDB();
$user      = currentUser();
$idInst    = instId();
$pageTitle = 'Expedientes PPP';

// ── Filtros ───────────────────────────────────────────────────────
$filPeriodo = (int)($_GET['periodo'] ?? 0);
$filEstado  = $_GET['estado'] ?? '';
$filBusca   = trim($_GET['busca'] ?? '');

// Períodos disponibles
$stPer    = $db->query("SELECT id_periodo, nombre FROM periodos_academicos WHERE 1=1" . instCond() . " ORDER BY fecha_inicio DESC");
$periodos = $stPer ? $stPer->fetchAll() : [];

// ── WHERE base ────────────────────────────────────────────────────
$where  = ["p.estado NOT IN ('borrador','rechazado')"];
$params = [];
if ($idInst)    { $where[] = 'c.id_instituto = :inst'; $params[':inst'] = $idInst; }
if ($filPeriodo){ $where[] = 'p.id_periodo = :per';    $params[':per']  = $filPeriodo; }
if ($filBusca)  { $where[] = "(u.nombres LIKE :b OR u.apellidos LIKE :b OR u.cedula LIKE :b)"; $params[':b'] = "%{$filBusca}%"; }
$whereSQL = 'WHERE ' . implode(' AND ', $where);

// ── Query principal — soporta PPP y Convalidación ─────────────────
$stExp = $db->prepare("
    SELECT
        p.id_practica,
        p.estado     AS estado_practica,
        /* Detectar convalidacion por campo O por existencia de solicitud (doble protección) */
        CASE WHEN p.modalidad = 'convalidacion' OR conv.id_solicitud IS NOT NULL
             THEN 'convalidacion' ELSE 'practica_laboral' END AS modalidad_real,
        p.modalidad,
        u.nombres, u.apellidos, u.cedula,
        c.nombre     AS carrera,
        per.nombre   AS periodo,
        COALESCE(em.razon_social, p.emp_nueva_nombre) AS empresa,

        /* ── Docs PPP (practica_laboral) ── */
        SUM(CASE WHEN p.modalidad != 'convalidacion' AND dpg.estado = 'aprobado'  THEN 1 ELSE 0 END) AS ppp_aprobados,
        SUM(CASE WHEN p.modalidad != 'convalidacion' AND dpg.estado = 'enviado'   THEN 1 ELSE 0 END) AS ppp_enviados,
        SUM(CASE WHEN p.modalidad != 'convalidacion' AND dpg.estado = 'observado' THEN 1 ELSE 0 END) AS ppp_observados,
        MAX(CASE WHEN p.modalidad != 'convalidacion' THEN dpg.updated_at END)                        AS ppp_actividad,

        /* ── Docs Convalidación (CON-001..005) ── */
        SUM(CASE WHEN p.modalidad = 'convalidacion' AND cd.estado = 'aprobado'  THEN 1 ELSE 0 END)  AS con_aprobados,
        SUM(CASE WHEN p.modalidad = 'convalidacion' AND cd.estado = 'enviado'   THEN 1 ELSE 0 END)  AS con_enviados,
        SUM(CASE WHEN p.modalidad = 'convalidacion' AND cd.estado = 'observado' THEN 1 ELSE 0 END)  AS con_observados,
        MAX(CASE WHEN p.modalidad = 'convalidacion' THEN cd.updated_at END)                          AS con_actividad,

        /* ── Calificadores ── */
        CONCAT(ut.apellidos, ' ', ut.nombres) AS tutor_academico,
        p.nombre_tutor_empresa                AS tutor_empresa,
        CONCAT(ug.apellidos, ' ', ug.nombres) AS gestor_ppp,
        p.nota_final,

        /* ── Docs Personales (Hoja de Vida, Cédula, Papeleta) ── */
        (SELECT COUNT(*) FROM documentos_personales_estudiante dpe
         WHERE dpe.id_estudiante = e.id_estudiante AND dpe.estado = 'subido') AS pers_subidos

    FROM   practicas p
    JOIN   estudiantes e   ON e.id_estudiante = p.id_estudiante
    JOIN   usuarios u      ON u.id_usuario    = e.id_usuario
    JOIN   carreras c      ON c.id_carrera    = e.id_carrera
    JOIN   periodos_academicos per ON per.id_periodo = p.id_periodo
    LEFT JOIN empresas em  ON em.id_empresa   = p.id_empresa
    /* Docs PPP */
    LEFT JOIN documentos_ppp_gestion dpg
           ON dpg.id_practica = p.id_practica AND p.modalidad != 'convalidacion'
    /* Docs Convalidación */
    LEFT JOIN convalidaciones conv ON conv.id_practica = p.id_practica
    LEFT JOIN convalidacion_documentos cd
           ON cd.id_solicitud = conv.id_solicitud AND p.modalidad = 'convalidacion'
    /* Tutor académico */
    LEFT JOIN tutores t    ON t.id_tutor    = p.id_tutor
    LEFT JOIN usuarios ut  ON ut.id_usuario = t.id_usuario
    /* Gestor PPP */
    LEFT JOIN usuarios ug  ON ug.id_usuario = p.calificado_por
    $whereSQL
    GROUP BY p.id_practica
    ORDER BY COALESCE(MAX(dpg.updated_at), MAX(cd.updated_at)) DESC, u.apellidos ASC
");
$stExp->execute($params);
$rawExp = $stExp->fetchAll(PDO::FETCH_ASSOC);

// ── Normalizar según modalidad ────────────────────────────────────
$expedientes = [];
foreach ($rawExp as $e) {
    $esConval              = ($e['modalidad_real'] === 'convalidacion');
    $persSubidos           = $esConval ? 0 : (int)($e['pers_subidos'] ?? 0);
    $e['es_conval']        = $esConval;
    $e['total_docs']       = $esConval ? 5 : 14; // 11 PPP + 3 personales
    $e['docs_aprobados']   = ($esConval ? (int)($e['con_aprobados']  ?? 0) : (int)($e['ppp_aprobados']  ?? 0)) + $persSubidos;
    $e['docs_enviados']    = $esConval ? (int)($e['con_enviados']   ?? 0) : (int)($e['ppp_enviados']   ?? 0);
    $e['docs_observados']  = $esConval ? (int)($e['con_observados'] ?? 0) : (int)($e['ppp_observados'] ?? 0);
    $e['ultima_actividad'] = $esConval ? ($e['con_actividad'] ?? null)    : ($e['ppp_actividad'] ?? null);
    $expedientes[]         = $e;
}

// ── Filtro post-query por estado ──────────────────────────────────
if ($filEstado === 'completo')   { $expedientes = array_filter($expedientes, fn($e) => $e['docs_aprobados'] == $e['total_docs']); }
if ($filEstado === 'pendiente')  { $expedientes = array_filter($expedientes, fn($e) => $e['docs_enviados']   > 0); }
if ($filEstado === 'observado')  { $expedientes = array_filter($expedientes, fn($e) => $e['docs_observados'] > 0); }
if ($filEstado === 'incompleto') { $expedientes = array_filter($expedientes, fn($e) => $e['docs_aprobados']  < $e['total_docs']); }

// ── Stats ─────────────────────────────────────────────────────────
$totalExp  = count($expedientes);
$completos = count(array_filter($expedientes, fn($e) => $e['docs_aprobados'] == $e['total_docs']));
$conPend   = count(array_filter($expedientes, fn($e) => $e['docs_enviados']   > 0));
$conObs    = count(array_filter($expedientes, fn($e) => $e['docs_observados'] > 0));
?>
<?php $paginaActual = 'expedientes'; ?>
<!DOCTYPE html>
<html lang="es">
<head>
  <?php include __DIR__.'/../../includes/head.php'; ?>
  <link rel="stylesheet" href="<?= BASE_URL ?>/assets/css/sidebar.css">
  <link rel="stylesheet" href="<?= BASE_URL ?>/assets/css/sidebar-coordinador.css">
  <style>
    .modal-badge { display:inline-flex;align-items:center;gap:4px;font-size:10px;font-weight:700;
                   padding:3px 10px;border-radius:20px;text-transform:uppercase;letter-spacing:.4px;
                   border:1px solid;white-space:nowrap; }
    .badge-ppp   { background:#eff6ff;color:#1d4ed8;border-color:#bfdbfe; }
    .badge-conval{ background:#fff7ed;color:#c2410c;border-color:#fed7aa; }
  </style>
</head>
<body>
<?php include __DIR__.'/../../includes/navbar.php'; ?>

<div class="app-shell">
  <?php include __DIR__ . '/../../includes/sidebar-coordinador.php'; ?>

  <main class="main-area">
    <?= flashHtml() ?>

    <div class="page-header d-flex justify-content-between align-items-center flex-wrap gap-2 mb-4">
      <div>
        <h4 class="mb-1"><i class="fa fa-folder-open me-2" style="color:#f7b93e;"></i>Expedientes PPP — Vista del Coordinador</h4>
        <p class="text-muted mb-0" style="font-size:14px;">Revisa y aprueba los documentos de cada estudiante. El historial es permanente.</p>
      </div>
    </div>

    <!-- Stats -->
    <div class="row g-3 mb-4">
      <div class="col-6 col-md-3">
        <div class="card border-0 shadow-sm text-center py-3">
          <div style="font-size:26px;font-weight:800;color:#1e293b;"><?= $totalExp ?></div>
          <div style="font-size:11px;text-transform:uppercase;letter-spacing:.05em;color:#6b7280;">Expedientes</div>
        </div>
      </div>
      <div class="col-6 col-md-3">
        <div class="card border-0 shadow-sm text-center py-3">
          <div style="font-size:26px;font-weight:800;color:#16a34a;"><?= $completos ?></div>
          <div style="font-size:11px;text-transform:uppercase;letter-spacing:.05em;color:#6b7280;">Completos</div>
        </div>
      </div>
      <div class="col-6 col-md-3">
        <div class="card border-0 shadow-sm text-center py-3">
          <div style="font-size:26px;font-weight:800;color:#2563eb;"><?= $conPend ?></div>
          <div style="font-size:11px;text-transform:uppercase;letter-spacing:.05em;color:#6b7280;">Por revisar</div>
        </div>
      </div>
      <div class="col-6 col-md-3">
        <div class="card border-0 shadow-sm text-center py-3">
          <div style="font-size:26px;font-weight:800;color:#dc2626;"><?= $conObs ?></div>
          <div style="font-size:11px;text-transform:uppercase;letter-spacing:.05em;color:#6b7280;">Con observación</div>
        </div>
      </div>
    </div>

    <!-- Filtros -->
    <form method="GET" class="d-flex gap-2 flex-wrap mb-3 align-items-center">
      <input type="text" name="busca" placeholder="Buscar por nombre o cédula..."
             value="<?= htmlspecialchars($filBusca) ?>"
             class="form-control" style="max-width:260px;">
      <select name="periodo" class="form-select" style="max-width:200px;">
        <option value="">Todos los períodos</option>
        <?php foreach ($periodos as $p): ?>
          <option value="<?= $p['id_periodo'] ?>" <?= $filPeriodo == $p['id_periodo'] ? 'selected' : '' ?>>
            <?= htmlspecialchars($p['nombre']) ?>
          </option>
        <?php endforeach; ?>
      </select>
      <select name="estado" class="form-select" style="max-width:210px;">
        <option value="">Todos los estados</option>
        <option value="pendiente"  <?= $filEstado==='pendiente'  ?'selected':'' ?>>Con docs por revisar</option>
        <option value="observado"  <?= $filEstado==='observado'  ?'selected':'' ?>>Con observaciones</option>
        <option value="completo"   <?= $filEstado==='completo'   ?'selected':'' ?>>Expediente completo</option>
        <option value="incompleto" <?= $filEstado==='incompleto' ?'selected':'' ?>>Incompleto</option>
      </select>
      <button type="submit" class="btn btn-outline-secondary btn-sm px-3">
        <i class="fa fa-filter me-1"></i> Filtrar
      </button>
      <a href="expedientes.php" class="btn btn-outline-secondary btn-sm">
        <i class="fa fa-xmark me-1"></i> Limpiar
      </a>
    </form>

    <!-- Tabla -->
    <div class="card shadow-sm border-0">
      <div class="card-body p-0">
        <div style="overflow-x:auto;">
        <table class="table table-hover mb-0" style="font-size:13px;min-width:900px;">
          <thead style="background:#f8f9fa;">
            <tr style="font-size:11px;text-transform:uppercase;letter-spacing:.05em;color:#6b7280;">
              <th class="px-3 py-3">Estudiante</th>
              <th class="py-3">Modalidad</th>
              <th class="py-3">Carrera</th>
              <th class="py-3">Período</th>
              <th class="py-3 text-center">✅ Apr.</th>
              <th class="py-3 text-center">⏳ Rev.</th>
              <th class="py-3 text-center">⚠ Obs.</th>
              <th class="py-3 text-center">📋 Faltan</th>
              <th class="py-3">Progreso</th>
              <th class="py-3">Calificadores</th>
              <th class="py-3">Últ. actividad</th>
              <th class="py-3"></th>
            </tr>
          </thead>
          <tbody>
            <?php if (empty($expedientes)): ?>
              <tr>
                <td colspan="12" class="text-center text-muted py-5">
                  <i class="fa fa-inbox fa-2x mb-2 d-block" style="color:#d1d5db;"></i>
                  No hay expedientes que coincidan con los filtros.
                </td>
              </tr>
            <?php endif; ?>

            <?php foreach ($expedientes as $exp):
              $esConval  = (bool)$exp['es_conval']; // ya normalizado
              $totalDocs = (int)$exp['total_docs'];
              $apr       = (int)$exp['docs_aprobados'];
              $env       = (int)$exp['docs_enviados'];
              $obs       = (int)$exp['docs_observados'];
              $pct       = $totalDocs > 0 ? round($apr / $totalDocs * 100) : 0;
              $pColor    = $pct == 100 ? '#16a34a' : ($pct >= 50 ? '#2563eb' : '#d97706');
              $sinSub    = max(0, $totalDocs - $apr - $env - $obs);
              $tAcad     = trim($exp['tutor_academico'] ?? '');
              $tEmp      = trim($exp['tutor_empresa']   ?? '');
              $tGest     = trim($exp['gestor_ppp']      ?? '');
            ?>
              <tr>
                <!-- Estudiante -->
                <td class="px-3 py-2">
                  <strong><?= htmlspecialchars($exp['apellidos'] . ' ' . $exp['nombres']) ?></strong>
                  <div style="font-size:11px;color:#9ca3af;"><?= htmlspecialchars($exp['cedula']) ?></div>
                </td>

                <!-- Modalidad -->
                <td class="py-2">
                  <?php if ($esConval): ?>
                    <span class="modal-badge badge-conval"><i class="fa fa-rotate"></i> CONV PPP</span>
                  <?php else: ?>
                    <span class="modal-badge badge-ppp"><i class="fa fa-briefcase"></i> PPP</span>
                  <?php endif; ?>
                </td>

                <!-- Carrera -->
                <td class="py-2" style="color:#6b7280;font-size:12px;"><?= htmlspecialchars($exp['carrera']) ?></td>

                <!-- Período -->
                <td class="py-2" style="font-size:12px;color:#6b7280;"><?= htmlspecialchars($exp['periodo']) ?></td>

                <!-- Aprobados -->
                <td class="py-2 text-center">
                  <span class="badge" style="background:#dcfce7;color:#15803d;font-size:11px;">
                    <?= $apr ?>/<?= $totalDocs ?>
                  </span>
                </td>

                <!-- Por revisar -->
                <td class="py-2 text-center">
                  <?php if ($env > 0): ?>
                    <span class="badge" style="background:#dbeafe;color:#1d4ed8;font-size:11px;"><?= $env ?></span>
                  <?php else: ?><span style="color:#d1d5db;">—</span><?php endif; ?>
                </td>

                <!-- Observados -->
                <td class="py-2 text-center">
                  <?php if ($obs > 0): ?>
                    <span class="badge" style="background:#fee2e2;color:#dc2626;font-size:11px;"><?= $obs ?></span>
                  <?php else: ?><span style="color:#d1d5db;">—</span><?php endif; ?>
                </td>

                <!-- Faltan -->
                <td class="py-2 text-center">
                  <?php if ($sinSub > 0): ?>
                    <span class="badge" style="background:#fef3c7;color:#d97706;font-size:11px;"><?= $sinSub ?></span>
                  <?php else: ?>
                    <span style="color:#16a34a;font-size:14px;">✓</span>
                  <?php endif; ?>
                </td>

                <!-- Progreso -->
                <td class="py-2" style="min-width:100px;">
                  <div style="height:6px;background:#e5e7eb;border-radius:4px;overflow:hidden;margin-bottom:3px;">
                    <div style="height:100%;width:<?= $pct ?>%;background:<?= $pColor ?>;border-radius:4px;"></div>
                  </div>
                  <span style="font-size:10px;color:<?= $pColor ?>;font-weight:600;"><?= $pct ?>%</span>
                </td>

                <!-- Calificadores -->
                <td class="py-2" style="font-size:11px;min-width:150px;line-height:1.7;">
                  <?php if ($tAcad && $tAcad !== ' '): ?>
                    <div title="Tutor académico" style="color:#1d4ed8;">
                      <i class="fa fa-user-tie fa-xs me-1"></i><?= htmlspecialchars($tAcad) ?>
                    </div>
                  <?php endif; ?>
                  <?php if ($tEmp): ?>
                    <div title="Tutor empresa" style="color:#047857;">
                      <i class="fa fa-building fa-xs me-1"></i><?= htmlspecialchars($tEmp) ?>
                    </div>
                  <?php endif; ?>
                  <?php if ($tGest && $tGest !== ' '): ?>
                    <div title="Gestor PPP" style="color:#7c3aed;">
                      <i class="fa fa-star fa-xs me-1"></i><?= htmlspecialchars($tGest) ?>
                    </div>
                  <?php elseif (!$tAcad && !$tEmp): ?>
                    <span style="color:#d1d5db;font-style:italic;">Sin asignar</span>
                  <?php endif; ?>
                </td>

                <!-- Última actividad -->
                <td class="py-2" style="font-size:11px;color:#9ca3af;white-space:nowrap;">
                  <?= $exp['ultima_actividad'] ? date('d/m/Y H:i', strtotime($exp['ultima_actividad'])) : '—' ?>
                </td>

                <!-- Acción -->
                <td class="py-2 text-center">
                  <a href="revisar_expediente.php?practica=<?= $exp['id_practica'] ?>"
                     class="btn btn-sm btn-outline-primary px-2"
                     title="Revisar expediente"
                     style="font-size:14px;line-height:1;">
                    <i class="fa fa-eye"></i>
                  </a>
                </td>
              </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
        </div>
      </div>
    </div>

  </main>
</div>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.7.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.3.3/js/bootstrap.bundle.min.js"></script>
</body>
</html>