<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$db = null;

// try include db.php from the real EdenRMS includes folder
$dbFile = __DIR__ . '/../../includes/db.php';
if (is_file($dbFile)) {
  require_once $dbFile;
}

// pick existing connection created by includes/db.php
$db = $conn ?? ($mysqli ?? null);

// fallback only if db.php didn't provide a connection
if (!$db) {
  $db = new mysqli("localhost","root","","edencamp_edencampus");
}

$db->set_charset("utf8mb4");


/* ---------------- core helpers ---------------- */
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 ordinal_date(?string $iso=null): string {
  $ts = $iso ? strtotime($iso) : time();
  $d = (int)date('j',$ts);
  $suffix = 'th';
  if (!in_array($d % 100, [11,12,13], true)) {
    $map = ['th','st','nd','rd','th','th','th','th','th','th'];
    $suffix = $map[$d % 10];
  }
  return date('F j', $ts) . $suffix . ', ' . date('Y', $ts);
}

function colExists(mysqli $db, string $table, string $col): bool {
  $st = $db->prepare("SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = ?
      AND COLUMN_NAME = ?
    LIMIT 1");
  $st->bind_param("ss", $table, $col);
  $st->execute();
  $st->store_result();
  $ok = $st->num_rows > 0;
  $st->close();
  return $ok;
}

function pickCol(mysqli $db, string $table, array $candidates, ?string $fallback = null): ?string {
  foreach ($candidates as $c) {
    if (colExists($db, $table, $c)) return $c;
  }
  return $fallback;
}


/* ---------------- data fetchers ---------------- */
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) AS registration_no,
  COALESCE(s.nic_passport, '') AS nic,
  COALESCE(NULLIF(TRIM(CONCAT_WS(', ', s.address1, s.address2, s.city, s.country)),''), '') 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 (dynamic columns)
$codeCol = pickCol($db, 'courses', ['code','course_code','course_code_name'], null);
$durCol  = pickCol($db, 'courses', ['duration_months','duration','course_duration'], null);
$nameCol = pickCol($db, 'courses', ['course_name','name','title'], 'course_name');

$selCode = $codeCol ? "c.`$codeCol` AS course_code" : "'' AS course_code";
$selDur  = $durCol  ? "c.`$durCol` AS duration_months" : "NULL AS duration_months";
$selName = "c.`$nameCol` AS course_name";

$sql = "SELECT b.*, c.id AS course_id,
        $selCode,
        $selName,
        $selDur,
        c.modules
  FROM batches b
  JOIN courses c ON c.id = b.course_id
  WHERE b.id = ?
  LIMIT 1";

$st = $db->prepare($sql);
$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, 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
  ];
}

function fetch_results(mysqli $db, array $student, array $courseRow = []): 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);

  // only FINAL EXAM rows
  $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)
        AND LOWER(COALESCE(module_name,'')) LIKE '%final%'
      GROUP BY module_name
    ) x ON x.max_id = r.id
    WHERE ($whereOuter)
      AND LOWER(COALESCE(r.module_name,'')) LIKE '%final%'
    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();

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

  // Map Unit 01, Unit 02 ... to actual course module titles
  $modules = course_modules($db, $courseRow);

  foreach ($rawRows as $r) {
    $rawName = trim((string)($r['module_name'] ?? ''));
    $displayName = $rawName;

    if (preg_match('/unit[\s\-_]*0*([0-9]+)/i', $rawName, $m)) {
      $idx = (int)$m[1] - 1; // Unit 01 => index 0
      if (isset($modules[$idx]) && trim($modules[$idx]) !== '') {
        $displayName = $modules[$idx];
      }
    }

    $rows[] = [
      'module_name' => $displayName,
      'score'       => $r['score'] ?? '',
      'total'       => $r['total'] ?? '',
      'grade'       => $r['grade'] ?? '',
      'date_taken'  => $r['date_taken'] ?? '',
    ];
  }

  return $rows;
}

/* Try to obtain modules from courses.modules (JSON or CSV) or course_modules table; else fallback list */
function course_modules(mysqli $db, array $courseRow): array {
  $mods = [];
  // 1) inline modules column (json or csv)
  if (!empty($courseRow['modules'])) {
    $raw = (string)$courseRow['modules'];
    $j = json_decode($raw, true);
    if (is_array($j)) {
      foreach ($j as $one) {
        $t = is_array($one) ? trim((string)($one['title'] ?? $one['name'] ?? '')) : trim((string)$one);
        if ($t!=='') $mods[] = $t;
      }
    } else {
      foreach (explode(',', $raw) as $t) { $t = trim($t); if ($t!=='') $mods[] = $t; }
    }
  }
  // 2) course_modules table
  if (!$mods && !empty($courseRow['course_id'])) {
    $st = $db->prepare("SELECT title FROM course_modules WHERE course_id=? ORDER BY sort_order ASC, id ASC");
    $st->bind_param("i", $courseRow['course_id']); $st->execute();
    $rs = $st->get_result();
    while ($r = $rs->fetch_assoc()) { $t = trim((string)($r['title'] ?? '')); if ($t!=='') $mods[]=$t; }
    $st->close();
  }
  // 3) fallback list
  if (!$mods) {
    $mods = [
      'Fundamentals of Computing and Digital Systems',
      'Office Productivity and Business Tools',
      'Computer Hardware and Systems Architecture',
      'Cybersecurity and Network Engineering',
      'Web Design and Front-End Development',
      'Internet and Cloud Technologies',
      'Multimedia and Interactive Technologies',
      'Introduction to Data Science and Artificial Intelligence',
      'Software Engineering Principles',
      'Programming with Python & Java',
      'Object-Oriented Programming with C#',
      'IT Project Management and Professional Practice',
    ];
  }
  return $mods;
}

/* ---------------- presentation ---------------- */
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 }

/* ---------------- Word download plumbing ---------------- */
function sanitize_filename(string $s): string {
  $s = preg_replace('/[^\p{L}\p{N}\s\-\_\.\(\)&,]+/u', '', $s) ?: 'document';
  $s = trim(preg_replace('/\s+/', ' ', $s));
  return $s;
}

function build_download_url(array $extra): string {
  $qs = array_merge($_GET, $extra);
  $path = strtok($_SERVER['REQUEST_URI'], '?');
  return $path . '?' . http_build_query($qs);
}

/**
 * Renders the letter for the browser OR streams a .doc download if ?download=word|doc|msword
 * $title: <title> and visual letter title (you can also print a separate H1 in your content)
 * $content_html: the inner HTML for the letter body (you can wrap with .hdr, .title etc.)
 * $suggested_filename: base filename (without extension)
 */
function render_letter_page(string $title, string $content_html, string $suggested_filename): void {
  $mode = strtolower((string)($_GET['download'] ?? $_GET['format'] ?? ''));
  $file = sanitize_filename($suggested_filename) . '.doc';

  if (in_array($mode, ['word','doc','msword'], true)) {
    // Stream as Word (HTML) — opens in MS Word as a .doc
    header('Content-Type: application/msword; charset=UTF-8');
    header('X-Content-Type-Options: nosniff');
    header('Content-Disposition: attachment; filename="'.$file.'"');
    echo "<!doctype html><html><head><meta charset='utf-8'><title>".h($title)."</title>";
      ob_start(); print_css(); $css = ob_get_clean(); echo $css;
    echo "</head><body><div class='a4'>{$content_html}</div></body></html>";
    exit;
  }

  // Normal browser render (with toolbar)
  echo "<!doctype html><html><head><meta charset='utf-8'><title>".h($title)."</title>";
    print_css();
  echo "</head><body>";
    echo "<div class='btnbar'>";
      echo "<a class='btn' href='".h(build_download_url(['download'=>'word']))."'>Download Word</a> ";
      echo "<a class='btn' href='javascript:window.print()'>Print</a>";
    echo "</div>";
    echo "<div class='a4'>{$content_html}</div>";
  echo "</body></html>";
}

/* ---------------- tiny grading helper (optional) ---------------- */
function pct_to_grade(?float $pct): string {
  if ($pct === null) return '—';
  return $pct>=85?'A':($pct>=75?'B':($pct>=65?'C':($pct>=50?'D':'F')));
}
