<?php
// Shared helper for letters
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli("localhost","root","","edencamp_eden_exam");
$db->set_charset("utf8mb4");

function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }
function inst(){  // EDIT these 4 lines to your institute identity
  return [
    'name'    => 'Eden Campus',
    'address' => 'No. 123, Main Street, Negombo, Sri Lanka',
    'phone'   => '+94 77 535 3526',
    'email'   => 'info@edencampus.lk'
  ];
}
function fetch_context(mysqli $db, int $student_id, ?int $batch_id = null): array {
  // student
  $st = $db->prepare("SELECT s.*,
           COALESCE(s.center_reg_no, s.reference_no, s.registration_no) AS registration_no,
           COALESCE(s.nic_passport, s.nic, s.nic_number) AS nic,
           COALESCE(NULLIF(TRIM(CONCAT_WS(', ', s.address1, s.address2, s.city, s.country)),''), s.address) AS full_address,
           s.email, s.mobile
         FROM studentss s WHERE s.id=? LIMIT 1");
  $st->bind_param("i",$student_id); $st->execute();
  $student = $st->get_result()->fetch_assoc() ?: [];
  $st->close();
  if (!$student) throw new Exception("Student not found.");

  // batch: if not supplied, get latest one
  if (!$batch_id) {
    $st = $db->prepare("SELECT e.batch_id
                          FROM enrollments e
                         WHERE e.student_id=?
                         ORDER BY e.id DESC LIMIT 1");
    $st->bind_param("i",$student_id); $st->execute();
    $batch_id = (int)($st->get_result()->fetch_column() ?? 0);
    $st->close();
  }
  if ($batch_id <= 0) throw new Exception("Batch not found for student.");

  // batch + course
  $st = $db->prepare("SELECT b.*, c.id AS course_id,
                             COALESCE(c.code, c.course_code) AS course_code,
                             c.course_name, COALESCE(c.duration, c.duration_months) AS duration_months
                        FROM batches b
                        JOIN courses c ON c.id=b.course_id
                       WHERE b.id=? LIMIT 1");
  $st->bind_param("i",$batch_id); $st->execute();
  $rc = $st->get_result()->fetch_assoc() ?: [];
  $st->close();
  if (!$rc) throw new Exception("Batch/Course not found.");

  // enrollment date (fallbacks)
  $st = $db->prepare("SELECT COALESCE(e.enrollment_date, e.enrolled_on, e.created_at, CURDATE()) AS enrolled
                        FROM enrollments e
                       WHERE e.student_id=? AND e.batch_id=? LIMIT 1");
  $st->bind_param("ii",$student_id,$batch_id); $st->execute();
  $enrolled = $st->get_result()->fetch_column() ?: date('Y-m-d');
  $st->close();

  return [
    'student' => $student,
    'course'  => $rc,
    'batch'   => $rc,
    'enroll'  => ['date'=>$enrolled],
    'batch_id'=> $batch_id
  ];
}

// Fetch results for completion letter
function fetch_results(mysqli $db, array $student): array {
  $rows = [];

  $student_id      = (int)($student['id'] ?? 0);
  $center_reg_no   = trim((string)($student['center_reg_no'] ?? ''));
  $reference_no    = trim((string)($student['reference_no'] ?? ''));
  $registration_no = trim((string)($student['registration_no'] ?? ''));

  $conds = [];
  $types = '';
  $params = [];

  if ($student_id > 0) {
    $conds[] = "r.student_id = ?";
    $types .= 'i';
    $params[] = $student_id;
  }

  foreach ([$center_reg_no, $reference_no, $registration_no] as $ref) {
    if ($ref !== '') {
      $conds[] = "r.reference_no = ?";
      $types .= 's';
      $params[] = $ref;
    }
  }

  if (!$conds) return [];

  $whereInner = str_replace("r.", "", implode(" OR ", $conds));
  $whereOuter = implode(" OR ", $conds);

  $sql = "
    SELECT r.module_name, r.score, r.total, r.grade, r.date_taken
    FROM results r
    INNER JOIN (
      SELECT module_name, MAX(id) AS max_id
      FROM results
      WHERE $whereInner
      GROUP BY module_name
    ) x ON x.max_id = r.id
    WHERE $whereOuter
    ORDER BY r.date_taken ASC, r.id ASC
  ";

  $st = $db->prepare($sql);
  if (!$st) {
    throw new Exception('Prepare failed in fetch_results: ' . $db->error);
  }

  $bindTypes  = $types . $types;
  $bindParams = array_merge($params, $params);

  $st->bind_param($bindTypes, ...$bindParams);
  $st->execute();

  $res = $st->get_result();
  while ($r = $res->fetch_assoc()) {
    $rows[] = $r;
  }

  $st->close();
  return $rows;
}

function print_css(){ ?>
  <style>
    :root{ --ink:#0f172a; --muted:#475569; }
    body{ font-family: system-ui, -apple-system, "Segoe UI", Roboto, "Helvetica Neue", Arial, "Noto Sans", "Liberation Sans", "Apple Color Emoji","Segoe UI Emoji","Segoe UI Symbol"; color:var(--ink); }
    .a4{ width:210mm; margin:0 auto; padding:18mm; background:#fff; box-shadow:0 0 0.5mm rgba(0,0,0,.08); }
    .hdr{ display:flex; justify-content:space-between; align-items:center; border-bottom:2px solid #e5e7eb; padding-bottom:6mm; margin-bottom:6mm; }
    .hdr .name{ font-size:20px; font-weight:700; letter-spacing:.2px; }
    .muted{ color:var(--muted); }
    .title{ text-transform:uppercase; letter-spacing:.8px; font-weight:700; margin:6mm 0 3mm }
    .kv{ margin:2mm 0 }
    .sig-row{ display:flex; justify-content:space-between; margin-top:14mm }
    .sig{ width:45%; text-align:center; }
    .btnbar{ position:sticky; top:0; background:#f8fafc; padding:8px 12px; border-bottom:1px solid #e5e7eb }
    .btn{ display:inline-block; padding:.5rem .9rem; border:1px solid #cbd5e1; border-radius:.375rem; background:#fff; text-decoration:none; color:#0f172a }
    table{ width:100%; border-collapse:collapse; }
    th,td{ border:1px solid #e5e7eb; padding:6px 8px; }
    thead th{ background:#f1f5f9; }
    @media print{
      .btnbar{ display:none }
      .a4{ box-shadow:none; padding:18mm }
      @page{ size:A4; margin:18mm; }
    }
  </style>
<?php }
