<?php 
require_once 'config.php';
include 'header.php';

// ✅ Session check
if (!isset($_SESSION['user_id'])) {
    die("Unauthorized access");
}

$total = 0;
$rows = [];

// ✅ सुरक्षित query (Prepared Statement)
$stmt = $conn->prepare("
    SELECT a.assignment_no,
           DATE_FORMAT(a.assignment_date, '%m/%d/%Y') as assignment_date_mdy,
           COUNT(ad.id) as totalCount
    FROM assignments a
    LEFT JOIN assignment_details ad ON ad.assignment_id = a.id
    WHERE a.deleted = 0 
      AND a.user_id = ? 
      AND a.assignment_status_id = 1
    GROUP BY a.id
");

$stmt->bind_param("i", $_SESSION['user_id']);
$stmt->execute();
$result = $stmt->get_result();

// ✅ collect data first
while ($row = $result->fetch_assoc()) {
    $rows[] = $row;
    $total += $row['totalCount'];
}
?>

<div class="container-area">
  <div class="pt-4 pb-5">
    <div class="container">
      <div class="row document-card">

        <div class="col-md-12">
          <h3 class="m-0">
            New Assignments 
            <span class="total_count">(<?= $total ?>)</span>
          </h3>
          <hr />
        </div>

        <?php if (!empty($rows)): ?>
            <?php foreach($rows as $row): ?>
            <div class="col-md-2 col-sm-3">
              <div class="card animate w-100">
                <div class="card-body text-center">
                  <a href="data.php?assignment_no=<?= urlencode($row['assignment_no']) ?>" 
                     class="d-flex justify-content-center align-items-center">

                    <div>
                      <h6><?= htmlspecialchars($row['assignment_date_mdy']) ?></h6>
                      <h3><?= htmlspecialchars($row['assignment_no']) ?></h3>
                      <h6>(<?= (int)$row['totalCount'] ?>)</h6>
                    </div>

                  </a>
                </div>
              </div>
            </div>
            <?php endforeach; ?>
        <?php else: ?>
            <div class="col-md-12">
                <p>No assignments found.</p>
            </div>
        <?php endif; ?>

      </div>
    </div>
  </div>
</div>

<?php include 'footer.php'; ?>