<?php
declare(strict_types=1);
require_once __DIR__ . "/../config/db.php";
require_once __DIR__ . "/../config/auth.php";

require_login();

// Block Coverage Analysis
$stmt = $pdo->prepare("
  SELECT 
    b.id, b.name, d.name AS district_name,
    COUNT(DISTINCT s.id) AS total_schools,
    COUNT(DISTINCT v.school_id) AS visited_schools,
    ROUND(100 * COUNT(DISTINCT v.school_id) / COUNT(DISTINCT s.id), 1) AS coverage_pct,
    COUNT(DISTINCT v.id) AS total_visits,
    MAX(v.visit_date) AS last_visit
  FROM blocks b
  LEFT JOIN districts d ON d.id = b.district_id
  LEFT JOIN schools s ON s.gp_id IN (SELECT id FROM gps WHERE block_id = b.id)
  LEFT JOIN visits v ON v.school_id = s.id
  GROUP BY b.id
  ORDER BY coverage_pct DESC, b.name ASC
");
$stmt->execute();
$blocks = $stmt->fetchAll(PDO::FETCH_ASSOC);

$page_title = "Block Coverage";
require_once __DIR__ . "/../public/partials/header.php";
?>

<div class="d-flex justify-content-between align-items-center mb-3">
  <div>
    <h5 class="mb-0">Block Coverage Report</h5>
    <div class="text-muted small">Schools visited vs total schools by block</div>
  </div>
</div>

<div class="card card-soft shadow-soft p-3">
  <div class="table-responsive">
    <table class="table table-hover align-middle mb-0 small">
      <thead>
        <tr>
          <th>District</th>
          <th>Block</th>
          <th>Total Schools</th>
          <th>Visited Schools</th>
          <th>Coverage %</th>
          <th>Total Visits</th>
          <th>Last Visit</th>
        </tr>
      </thead>
      <tbody>
        <?php if (!$blocks): ?>
          <tr><td colspan="7" class="text-muted text-center py-3">No data.</td></tr>
        <?php endif; ?>

        <?php foreach ($blocks as $b): 
          $coverage = (float)($b['coverage_pct'] ?? 0);
          $coverage_class = $coverage >= 80 ? 'bg-success' : ($coverage >= 50 ? 'bg-warning' : 'bg-danger');
        ?>
        <tr>
          <td><?= htmlspecialchars($b['district_name'] ?? 'N/A') ?></td>
          <td class="fw-semibold"><?= htmlspecialchars($b['name']) ?></td>
          <td><?= (int)$b['total_schools'] ?></td>
          <td class="fw-semibold"><?= (int)$b['visited_schools'] ?></td>
          <td>
            <div class="d-flex align-items-center gap-2">
              <div class="progress" style="min-width: 80px; height: 0.5rem;">
                <div class="progress-bar <?= $coverage_class ?>" style="width: <?= $coverage ?>%"></div>
              </div>
              <span><?= (float)$b['coverage_pct'] ?>%</span>
            </div>
          </td>
          <td><?= (int)$b['total_visits'] ?></td>
          <td class="text-nowrap text-muted small"><?= $b['last_visit'] ? htmlspecialchars($b['last_visit']) : '—' ?></td>
        </tr>
        <?php endforeach; ?>
      </tbody>
    </table>
  </div>
</div>

<?php require_once __DIR__ . "/../public/partials/footer.php"; ?>