<?php
declare(strict_types=1);

require_once __DIR__ . '/../../app/bootstrap.php';
require_login();

if (!(has_role('ADMIN') || has_role('DIRECTOR') || has_role('SECRETARIA'))) {
  http_response_code(403);
  die("Acceso no permitido");
}

$pdo = db();
function h($v): string { return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); }

// Si tenés can() lo usa; si no, cae a roles
if (function_exists('can')) {
  if (!can('BOLETIN_GENERAR')) { http_response_code(403); die("Sin permiso"); }
}

// Config
const NOTA_APROBACION = 60.0; // porcentaje mínimo para aprobar (0-100)

// Flash
$msg = $_SESSION['flash_msg'] ?? null;
$err = $_SESSION['flash_err'] ?? null;
unset($_SESSION['flash_msg'], $_SESSION['flash_err']);

$institucion = trim((string)($_GET['institucion'] ?? 'ENC'));
$anio        = (int)($_GET['anio'] ?? (int)date('Y'));
$etapa       = (int)($_GET['etapa_id'] ?? 0); // etapa_id
$nivel       = trim((string)($_GET['nivel'] ?? ''));
$curso       = trim((string)($_GET['curso'] ?? ''));
$secc        = trim((string)($_GET['seccion'] ?? ''));
$turno       = trim((string)($_GET['turno'] ?? ''));
$q           = trim((string)($_GET['q'] ?? ''));

/* =========================
   Instituciones
========================= */
$instituciones = [];
try {
  $instituciones = $pdo->query("
    SELECT DISTINCT institucion
    FROM matriculas
    WHERE institucion IS NOT NULL AND institucion <> ''
    ORDER BY institucion
  ")->fetchAll(PDO::FETCH_COLUMN);
} catch (Throwable $e) {
  $instituciones = ['ENC'];
}

if (empty($instituciones)) {
  $instituciones = ['ENC'];
}

if (!in_array($institucion, array_map('strval', $instituciones), true)) {
  $institucion = (string)$instituciones[0];
}

/* =========================
   Combos por institución
========================= */
$niveles = [];
$cursos = [];
$secciones = [];
$turnos = [];

try {
  $st = $pdo->prepare("
    SELECT DISTINCT nivel
    FROM matriculas
    WHERE institucion = ?
    ORDER BY nivel
  ");
  $st->execute([$institucion]);
  $niveles = $st->fetchAll(PDO::FETCH_COLUMN) ?: [];
} catch (Throwable $e) {}

try {
  $st = $pdo->prepare("
    SELECT DISTINCT curso
    FROM matriculas
    WHERE institucion = ?
    ORDER BY curso
  ");
  $st->execute([$institucion]);
  $cursos = $st->fetchAll(PDO::FETCH_COLUMN) ?: [];
} catch (Throwable $e) {}

try {
  $st = $pdo->prepare("
    SELECT DISTINCT seccion
    FROM matriculas
    WHERE institucion = ?
      AND seccion IS NOT NULL
      AND seccion <> ''
    ORDER BY seccion
  ");
  $st->execute([$institucion]);
  $secciones = $st->fetchAll(PDO::FETCH_COLUMN) ?: [];
} catch (Throwable $e) {}

try {
  $st = $pdo->prepare("
    SELECT DISTINCT turno
    FROM matriculas
    WHERE institucion = ?
    ORDER BY turno
  ");
  $st->execute([$institucion]);
  $turnos = $st->fetchAll(PDO::FETCH_COLUMN) ?: [];
} catch (Throwable $e) {}

/* =========================
   Etapas
========================= */
$etapas = [];
try {
  $etapas = $pdo->query("SELECT id, nombre FROM etapas ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
} catch (Throwable $e) {
  $etapas = [
    ['id'=>1,'nombre'=>'Etapa 1'],
    ['id'=>2,'nombre'=>'Etapa 2'],
  ];
}

/* =========================
   Helpers de boletín
========================= */
function upsert_boletin(PDO $pdo, int $matriculaId, int $anio, int $etapaId, array $detalles, ?int $emitidoPor = null, bool $emitir = false): int {
  $sumPct = 0.0; $cnt = 0;
  foreach ($detalles as $d) {
    if ($d['porcentaje'] !== null) { $sumPct += (float)$d['porcentaje']; $cnt++; }
  }
  $promGeneral = $cnt > 0 ? round($sumPct / $cnt, 2) : null;

  $tiene = 0; $reprob = 0;
  foreach ($detalles as $d) {
    if ($d['porcentaje'] === null) continue;
    $tiene++;
    if ((float)$d['porcentaje'] < NOTA_APROBACION) $reprob++;
  }

  $condFinal = null;
  if ($tiene > 0) $condFinal = ($reprob === 0) ? 'PROMOVIDO' : 'PENDIENTE';

  $pdo->beginTransaction();

  $st = $pdo->prepare("
    SELECT id, estado
    FROM boletines
    WHERE matricula_id = ?
      AND anio_lectivo = ?
      AND etapa_id = ?
    LIMIT 1
  ");
  $st->execute([$matriculaId, $anio, $etapaId]);
  $row = $st->fetch(PDO::FETCH_ASSOC);

  if ($row) {
    $bid = (int)$row['id'];
    $nuevoEstado = $emitir ? 'EMITIDO' : 'BORRADOR';

    $upd = $pdo->prepare("
      UPDATE boletines
      SET promedio_general = ?,
          condicion_final = ?,
          estado = ?,
          emitido_en = IF(?, NOW(), emitido_en),
          emitido_por = IF(?, ?, emitido_por)
      WHERE id = ?
    ");
    $upd->execute([
      $promGeneral,
      $condFinal,
      $nuevoEstado,
      $emitir ? 1 : 0,
      $emitir ? 1 : 0,
      $emitidoPor,
      $bid
    ]);
  } else {
    $ins = $pdo->prepare("
      INSERT INTO boletines (matricula_id, anio_lectivo, etapa_id, estado, promedio_general, condicion_final, emitido_en, emitido_por)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    ");
    $ins->execute([
      $matriculaId,
      $anio,
      $etapaId,
      $emitir ? 'EMITIDO' : 'BORRADOR',
      $promGeneral,
      $condFinal,
      $emitir ? date('Y-m-d H:i:s') : null,
      $emitir ? $emitidoPor : null,
    ]);
    $bid = (int)$pdo->lastInsertId();
  }

  $insDet = $pdo->prepare("
    INSERT INTO boletin_detalle (boletin_id, materia_id, promedio, condicion, observacion)
    VALUES (?, ?, ?, ?, NULL)
    ON DUPLICATE KEY UPDATE
      promedio = VALUES(promedio),
      condicion = VALUES(condicion)
  ");

  foreach ($detalles as $d) {
    $porc = $d['porcentaje'] !== null ? (float)$d['porcentaje'] : null;
    $cond = $d['condicion'];
    $insDet->execute([$bid, (int)$d['materia_id'], $porc, $cond]);
  }

  $pdo->commit();
  return $bid;
}

function calcular_detalles_boletin(PDO $pdo, int $matriculaId, int $anio, int $etapaId): array {
  $sql = "
    SELECT
      c.materia_id,
      m.nombre AS materia,
      SUM(COALESCE(ca.puntaje,0)) AS sum_puntaje,
      SUM(COALESCE(e.puntaje_max,0)) AS sum_max
    FROM inscripciones_clase ic
    JOIN clases c ON c.id = ic.clase_id
    JOIN materias m ON m.id = c.materia_id
    LEFT JOIN evaluaciones e ON e.clase_id = c.id
    LEFT JOIN calificaciones ca
      ON ca.evaluacion_id = e.id
     AND ca.estudiante_id = (SELECT estudiante_id FROM matriculas WHERE id = ? LIMIT 1)
    WHERE ic.matricula_id = ?
      AND c.anio_lectivo = ?
      AND c.etapa_id = ?
      AND c.activo = 1
    GROUP BY c.materia_id, m.nombre
    ORDER BY m.nombre
  ";
  $st = $pdo->prepare($sql);
  $st->execute([$matriculaId, $matriculaId, $anio, $etapaId]);
  $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];

  $out = [];
  foreach ($rows as $r) {
    $sumMax = (float)($r['sum_max'] ?? 0);
    $sumPun = (float)($r['sum_puntaje'] ?? 0);

    $pct = null;
    if ($sumMax > 0) {
      $pct = round(($sumPun / $sumMax) * 100.0, 2);
    }

    $cond = null;
    if ($pct !== null) {
      $cond = ($pct >= NOTA_APROBACION) ? 'APROBADO' : 'REPROBADO';
    }

    $out[] = [
      'materia_id' => (int)$r['materia_id'],
      'materia'    => (string)$r['materia'],
      'porcentaje' => $pct,
      'condicion'  => $cond,
      'sum_puntaje'=> $sumPun,
      'sum_max'    => $sumMax,
    ];
  }
  return $out;
}

/* =========================
   POST acciones
========================= */
$uid = (int)($_SESSION['uid'] ?? 0);

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
  csrf_check();

  $action       = (string)($_POST['action'] ?? '');
  $institucionP = trim((string)($_POST['institucion'] ?? $institucion));
  $anioP        = (int)($_POST['anio'] ?? $anio);
  $etapaP       = (int)($_POST['etapa_id'] ?? 0);
  $matId        = (int)($_POST['matricula_id'] ?? 0);

  try {
    if ($institucionP === '') throw new RuntimeException("Institución inválida.");
    if ($anioP <= 0) throw new RuntimeException("Año inválido.");
    if ($etapaP <= 0) throw new RuntimeException("Elegí etapa.");
    if ($matId <= 0) throw new RuntimeException("Matrícula inválida.");

    // Validar que la matrícula pertenezca a la institución elegida
    $stMat = $pdo->prepare("
      SELECT id
      FROM matriculas
      WHERE id = ?
        AND institucion = ?
      LIMIT 1
    ");
    $stMat->execute([$matId, $institucionP]);
    if (!$stMat->fetchColumn()) {
      throw new RuntimeException("La matrícula no pertenece a la institución seleccionada.");
    }

    $det = calcular_detalles_boletin($pdo, $matId, $anioP, $etapaP);
    if (empty($det)) {
      throw new RuntimeException("No se encontraron materias/inscripciones para esa matrícula en esa etapa.");
    }

    if ($action === 'generar') {
      $bid = upsert_boletin($pdo, $matId, $anioP, $etapaP, $det, null, false);
      $_SESSION['flash_msg'] = "✅ Boletín generado/actualizado (BORRADOR). ID: {$bid}";
    } elseif ($action === 'emitir') {
      $bid = upsert_boletin($pdo, $matId, $anioP, $etapaP, $det, $uid, true);
      $_SESSION['flash_msg'] = "✅ Boletín EMITIDO. ID: {$bid}";
    } else {
      throw new RuntimeException("Acción inválida.");
    }

  } catch (Throwable $e) {
    $_SESSION['flash_err'] = "❌ " . $e->getMessage();
  }

  header("Location: /Comercio/public/admin/boletines.php?" . http_build_query([
    'institucion' => $institucionP,
    'anio'        => $anioP,
    'etapa_id'    => $etapaP,
    'nivel'       => $nivel,
    'curso'       => $curso,
    'seccion'     => $secc,
    'turno'       => $turno,
    'q'           => $q,
  ]));
  exit;
}

/* =========================
   Listar matrículas según filtros
========================= */
$params = [
  ':institucion' => $institucion,
  ':anio'        => $anio
];
$where = "m.institucion = :institucion AND m.anio_lectivo = :anio AND m.estado = 'ACTIVO'";

if ($nivel !== '') { $where .= " AND m.nivel = :nivel"; $params[':nivel'] = $nivel; }
if ($curso !== '') { $where .= " AND m.curso = :curso"; $params[':curso'] = $curso; }
if ($secc !== '')  { $where .= " AND m.seccion = :secc"; $params[':secc'] = $secc; }
if ($turno !== '') { $where .= " AND m.turno = :turno"; $params[':turno'] = $turno; }
if ($q !== '')     { $where .= " AND (e.apellidos LIKE :q OR e.nombres LIKE :q OR e.ci LIKE :q)"; $params[':q'] = "%{$q}%"; }

$matriculas = [];
try {
  $st = $pdo->prepare("
    SELECT
      m.id AS matricula_id,
      m.institucion,
      m.nivel,
      m.curso,
      COALESCE(m.seccion,'') AS seccion,
      m.turno,
      e.ci,
      e.apellidos,
      e.nombres
    FROM matriculas m
    JOIN estudiantes e ON e.id = m.estudiante_id
    WHERE $where
    ORDER BY m.nivel, m.curso, m.seccion, e.apellidos, e.nombres
    LIMIT 200
  ");
  $st->execute($params);
  $matriculas = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
} catch (Throwable $e) {
  $matriculas = [];
}

$title = "Boletines";
include __DIR__ . '/../../app/views/header.php';
?>

<style>
  .card-pro{border-radius:16px;border:1px solid rgba(0,0,0,.06);background:#fff;box-shadow:0 8px 22px rgba(0,0,0,.04);}
  .soft-table{border-radius:16px;overflow:hidden;border:1px solid rgba(0,0,0,.06);}
  .soft-table thead th{background:rgba(248,249,250,1);font-size:.85rem;color:#6c757d;font-weight:800;white-space:nowrap;}
  .chip{display:inline-flex;align-items:center;gap:6px;padding:5px 10px;border-radius:999px;font-size:.82rem;border:1px solid rgba(0,0,0,.08);background:#fff;}
</style>

<div class="d-flex justify-content-between align-items-start flex-wrap gap-2 mb-3">
  <div>
    <h1 class="h5 mb-1">Boletines</h1>
    <div class="text-muted small">Generar / emitir boletín por etapa. Promedio en porcentaje.</div>
  </div>
  <a class="btn btn-outline-secondary btn-sm" href="/Comercio/public/dashboard.php">← Dashboard</a>
</div>

<?php if ($err): ?><div class="alert alert-danger"><?= h($err) ?></div><?php endif; ?>
<?php if ($msg): ?><div class="alert alert-info"><?= h($msg) ?></div><?php endif; ?>

<div class="card-pro p-3 mb-3">
  <form method="get" class="row g-2 align-items-end">
    <div class="col-6 col-md-2">
      <label class="form-label">Institución</label>
      <select class="form-select" name="institucion" onchange="this.form.submit()">
        <?php foreach ($instituciones as $inst): ?>
          <option value="<?= h((string)$inst) ?>" <?= $institucion === (string)$inst ? 'selected' : '' ?>>
            <?= h((string)$inst) ?>
          </option>
        <?php endforeach; ?>
      </select>
    </div>

    <div class="col-6 col-md-2">
      <label class="form-label">Año</label>
      <input class="form-control" type="number" name="anio" value="<?= (int)$anio ?>">
    </div>

    <div class="col-6 col-md-2">
      <label class="form-label">Etapa</label>
      <select class="form-select" name="etapa_id" required>
        <option value="0">— Elegir —</option>
        <?php foreach ($etapas as $e): ?>
          <option value="<?= (int)$e['id'] ?>" <?= (int)$e['id'] === $etapa ? 'selected' : '' ?>>
            <?= h($e['nombre'] ?? ('Etapa '.(int)$e['id'])) ?>
          </option>
        <?php endforeach; ?>
      </select>
    </div>

    <div class="col-6 col-md-2">
      <label class="form-label">Nivel</label>
      <select class="form-select" name="nivel">
        <option value="">Todos</option>
        <?php foreach ($niveles as $n): ?>
          <option value="<?= h((string)$n) ?>" <?= $nivel === (string)$n ? 'selected' : '' ?>><?= h((string)$n) ?></option>
        <?php endforeach; ?>
      </select>
    </div>

    <div class="col-6 col-md-2">
      <label class="form-label">Curso</label>
      <select class="form-select" name="curso">
        <option value="">Todos</option>
        <?php foreach ($cursos as $c): ?>
          <option value="<?= h((string)$c) ?>" <?= $curso === (string)$c ? 'selected' : '' ?>><?= h((string)$c) ?></option>
        <?php endforeach; ?>
      </select>
    </div>

    <div class="col-6 col-md-1">
      <label class="form-label">Sección</label>
      <select class="form-select" name="seccion">
        <option value="">Todas</option>
        <?php foreach ($secciones as $s): ?>
          <option value="<?= h((string)$s) ?>" <?= $secc === (string)$s ? 'selected' : '' ?>><?= h((string)$s) ?></option>
        <?php endforeach; ?>
      </select>
    </div>

    <div class="col-6 col-md-1">
      <label class="form-label">Turno</label>
      <select class="form-select" name="turno">
        <option value="">Todos</option>
        <?php foreach ($turnos as $t): ?>
          <option value="<?= h((string)$t) ?>" <?= $turno === (string)$t ? 'selected' : '' ?>><?= h((string)$t) ?></option>
        <?php endforeach; ?>
      </select>
    </div>

    <div class="col-12 col-md-6">
      <label class="form-label">Buscar alumno (CI / Apellido / Nombre)</label>
      <input class="form-control" name="q" value="<?= h($q) ?>" placeholder="Ej: 1234567 o González">
    </div>

    <div class="col-12 col-md-6 d-flex gap-2">
      <button class="btn btn-primary w-100"><i class="bi bi-search me-1"></i>Buscar</button>
      <a class="btn btn-outline-secondary" href="/Comercio/public/admin/boletines.php">Limpiar</a>
    </div>
  </form>
</div>

<div class="card-pro p-3">
  <div class="d-flex flex-wrap gap-2 mb-3">
    <span class="chip"><b>Institución:</b> <?= h($institucion) ?></span>
    <span class="chip"><b>Año:</b> <?= (int)$anio ?></span>
    <?php if ($etapa > 0): ?><span class="chip"><b>Etapa:</b> <?= (int)$etapa ?></span><?php endif; ?>
    <span class="chip"><b>Matrículas listadas:</b> <?= count($matriculas) ?></span>
  </div>

  <?php if ($etapa <= 0): ?>
    <div class="alert alert-warning mb-0">Elegí una etapa para habilitar generación/emitir.</div>
  <?php elseif (empty($matriculas)): ?>
    <div class="alert alert-warning mb-0">No hay matrículas activas con esos filtros.</div>
  <?php else: ?>

    <div class="soft-table table-responsive">
      <table class="table table-sm align-middle mb-0">
        <thead>
          <tr>
            <th>Alumno</th>
            <th>Curso</th>
            <th>Turno</th>
            <th>Institución</th>
            <th style="width:330px;">Acciones</th>
          </tr>
        </thead>
        <tbody>
        <?php foreach ($matriculas as $m): ?>
          <tr>
            <td class="fw-semibold">
              <?= h(($m['apellidos'] ?? '').' '.($m['nombres'] ?? '')) ?>
              <div class="text-muted small">CI: <?= h($m['ci'] ?? '-') ?></div>
            </td>
            <td><?= h($m['nivel'].' · '.$m['curso'].' '.($m['seccion'] ?? '')) ?></td>
            <td><?= h($m['turno']) ?></td>
            <td><?= h($m['institucion']) ?></td>
            <td class="text-end">
              <form method="post" class="d-inline">
                <input type="hidden" name="csrf" value="<?= h(csrf_token()) ?>">
                <input type="hidden" name="institucion" value="<?= h($institucion) ?>">
                <input type="hidden" name="anio" value="<?= (int)$anio ?>">
                <input type="hidden" name="etapa_id" value="<?= (int)$etapa ?>">
                <input type="hidden" name="matricula_id" value="<?= (int)$m['matricula_id'] ?>">
                <input type="hidden" name="action" value="generar">
                <button class="btn btn-sm btn-outline-primary">
                  <i class="bi bi-arrow-repeat me-1"></i>Generar
                </button>
              </form>

              <form method="post" class="d-inline ms-1">
                <input type="hidden" name="csrf" value="<?= h(csrf_token()) ?>">
                <input type="hidden" name="institucion" value="<?= h($institucion) ?>">
                <input type="hidden" name="anio" value="<?= (int)$anio ?>">
                <input type="hidden" name="etapa_id" value="<?= (int)$etapa ?>">
                <input type="hidden" name="matricula_id" value="<?= (int)$m['matricula_id'] ?>">
                <input type="hidden" name="action" value="emitir">
                <button class="btn btn-sm btn-success">
                  <i class="bi bi-check2-circle me-1"></i>Emitir
                </button>
              </form>

              <a class="btn btn-sm btn-outline-secondary ms-1"
                 href="/Comercio/public/reportes/boletin_pdf.php?matricula_id=<?= (int)$m['matricula_id'] ?>&anio=<?= (int)$anio ?>&etapa_id=<?= (int)$etapa ?>"
                 target="_blank">
                <i class="bi bi-printer me-1"></i>Imprimir
              </a>
            </td>
          </tr>
        <?php endforeach; ?>
        </tbody>
      </table>
    </div>

    <div class="text-muted small mt-2">
      Nota: el promedio se calcula como <b>SUM(puntaje)/SUM(puntaje_max)*100</b>. Aprobación: <?= (float)NOTA_APROBACION ?>%.
    </div>

  <?php endif; ?>
</div>

<?php include __DIR__ . '/../../app/views/footer.php'; ?>