<?php
// /exam/admin/bulk_upload_questions.php
session_start();
require_once "../includes/db.php";
if (!isset($_SESSION['admin'])) { header("Location: index.php"); exit; }

set_time_limit(300);

/* -----------------------
   Guard: exam_id
------------------------ */
if (!isset($_GET['exam_id']) || !is_numeric($_GET['exam_id'])) {
    $_SESSION['error'] = "Please select an exam before bulk upload.";
    header("Location: dashboard.php"); exit;
}
$exam_id = (int)$_GET['exam_id'];

/* -----------------------
   Helpers
------------------------ */
function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }

function ensureUploadsDir(): string {
    $dir = realpath(__DIR__ . "/../uploads");
    if ($dir === false) {
        $dir = __DIR__ . "/../uploads";
        if (!is_dir($dir)) mkdir($dir, 0777, true);
    }
    return rtrim($dir, "/");
}

/** Accept 1–4, A–D, or exact match to option text */
function normalizeCorrectFlexible(array $r): ?int {
    $v = trim((string)($r['correct_option'] ?? ''));
    if ($v === '') return null;
    $u = strtoupper($v);

    if (in_array($u, ['A','B','C','D'], true)) return ['A'=>1,'B'=>2,'C'=>3,'D'=>4][$u];
    if (ctype_digit($u)) { $n = (int)$u; if ($n >= 1 && $n <= 4) return $n; }

    $opts = [
        1 => trim((string)($r['option_1'] ?? '')),
        2 => trim((string)($r['option_2'] ?? '')),
        3 => trim((string)($r['option_3'] ?? '')),
        4 => trim((string)($r['option_4'] ?? '')),
    ];
    foreach ($opts as $i => $text) {
        if ($text !== '' && trim($text) === trim((string)$r['correct_option'])) return $i;
    }
    return null;
}

/** Robust CSV reader: auto-detect delimiter, handle BOM, header aliases, blank lines */
function csvToArray($filePath){
    if (!is_readable($filePath)) return [];

    // Detect delimiter (comma/semicolon/tab)
    $sample = file_get_contents($filePath, false, null, 0, 4096);
    $delims = ["," => substr_count($sample, ","), ";" => substr_count($sample, ";"), "\t" => substr_count($sample, "\t")];
    arsort($delims);
    $delimiter = array_key_first($delims) ?: ",";

    $fh = fopen($filePath, 'r'); if (!$fh) return [];

    // Strip UTF-8 BOM on first line
    $first = fgets($fh);
    if ($first === false) { fclose($fh); return []; }
    $first = preg_replace('/^\xEF\xBB\xBF/', '', $first);

    // Replay stream for fgetcsv
    $tmp = fopen('php://temp', 'r+');
    fwrite($tmp, $first);
    stream_copy_to_stream($fh, $tmp);
    rewind($tmp);
    fclose($fh);

    // First row (maybe header)
    $maybeHeader = fgetcsv($tmp, 0, $delimiter);
    if ($maybeHeader === false) { fclose($tmp); return []; }

    $norm = function($x){
        $x = trim((string)$x);
        $x = preg_replace('/\s+/', '_', $x);
        return strtolower($x);
    };
    $header = array_map($norm, $maybeHeader);

    // Header aliases → expected names
    $alias = [
        'question'         => 'question_text',
        'questiontext'     => 'question_text',
        'question_body'    => 'question_text',
        'question_image'   => 'question_file',
        'image'            => 'question_file',
        'img'              => 'question_file',

        'a'                => 'option_1',
        'b'                => 'option_2',
        'c'                => 'option_3',
        'd'                => 'option_4',
        'option_a'         => 'option_1',
        'option_b'         => 'option_2',
        'option_c'         => 'option_3',
        'option_d'         => 'option_4',

        'a_file'           => 'option_1_file',
        'b_file'           => 'option_2_file',
        'c_file'           => 'option_3_file',
        'd_file'           => 'option_4_file',
        'option_a_file'    => 'option_1_file',
        'option_b_file'    => 'option_2_file',
        'option_c_file'    => 'option_3_file',
        'option_d_file'    => 'option_4_file',

        'ans'              => 'correct_option',
        'answer'           => 'correct_option',
        'correct'          => 'correct_option',
    ];
    foreach ($header as &$h) { if (isset($alias[$h])) $h = $alias[$h]; }
    unset($h);

    $known = ['question_text','question_file','option_1','option_1_file','option_2','option_2_file','option_3','option_3_file','option_4','option_4_file','correct_option'];
    $rows = [];

    if (count(array_intersect($header, $known)) < 3) {
        // Not a header → treat as data and synthesize minimal header
        $header = ['question_text','option_1','option_2','option_3','option_4','correct_option'];
        $row = [];
        foreach ($header as $i=>$k) { $row[$k] = $maybeHeader[$i] ?? ''; }
        $rows[] = $row;
    }

    while (($data = fgetcsv($tmp, 0, $delimiter)) !== false) {
        $allEmpty = true;
        foreach ($data as $v) { if (trim((string)$v) !== '') { $allEmpty = false; break; } }
        if ($allEmpty) continue;

        $row = [];
        foreach ($header as $i=>$k) { $row[$k] = $data[$i] ?? ''; }
        $rows[] = $row;
    }
    fclose($tmp);
    return $rows;
}

/** Download a URL to /uploads and return stored filename (relative) */
function saveFromUrl(string $url): ?string {
    $url = trim($url); if ($url === '') return null;
    if (!preg_match('~^https?://~i', $url)) return null;

    $uploads = ensureUploadsDir();
    $ext = strtolower(pathinfo(parse_url($url, PHP_URL_PATH), PATHINFO_EXTENSION));
    if ($ext === '') $ext = 'bin';
    $fname = time().'_'.bin2hex(random_bytes(4)).'.'.$ext;
    $dest  = $uploads.'/'.$fname;

    $ctx  = stream_context_create(['http'=>['timeout'=>15]]);
    $data = @file_get_contents($url, false, $ctx);
    if ($data === false || strlen($data) > 12*1024*1024) return null;

    if (@file_put_contents($dest, $data) !== false) return $fname;
    return null;
}

/* ---- Subfolder-aware file handling ---- */

// Normalize a user path, allow subfolders, block traversal
function safe_rel_path(string $p): string {
    $p = str_replace('\\', '/', trim($p));
    $p = ltrim($p, '/'); // no absolute
    $parts = [];
    foreach (explode('/', $p) as $seg) {
        if ($seg === '' || $seg === '.') continue;
        if ($seg === '..') { if ($parts) array_pop($parts); continue; }
        $parts[] = $seg;
    }
    return implode('/', $parts); // relative
}

// Ensure a subdir exists inside uploads for a given relative path
function ensureUploadsSubdir(string $relPath, string $uploadsRoot): void {
    $dir = dirname($relPath);
    if ($dir === '.' || $dir === '') return;
    $full = $uploadsRoot . '/' . $dir;
    if (!is_dir($full)) mkdir($full, 0755, true);
}

// Find a file inside a directory tree (for ZIPs with nested folders)
function findInDir(string $root, string $relOrBase): ?string {
    $target = safe_rel_path($relOrBase);
    if ($target !== '' && is_file($root . '/' . $target)) return $root . '/' . $target;
    $base = basename($target);
    $it = new RecursiveIteratorIterator(
        new RecursiveDirectoryIterator($root, FilesystemIterator::SKIP_DOTS),
        RecursiveIteratorIterator::SELF_FIRST
    );
    foreach ($it as $file) {
        if ($file->isFile() && $file->getFilename() === $base) return $file->getPathname();
    }
    return null;
}

/** Resolve CSV file ref to a stored filename in /uploads (keeps subfolders) */
function resolveFileRef(?string $ref, ?string $assetsDir = null): ?string {
    $ref = trim((string)$ref);
    if ($ref === '') return null;

    $uploads = ensureUploadsDir();

    // URLs → download
    if (preg_match('~^https?://~i', $ref)) return saveFromUrl($ref);

    // relative path inside uploads (allow subfolders)
    $rel = safe_rel_path($ref);
    if ($rel !== '' && is_file($uploads . '/' . $rel)) return $rel;

    // From assets ZIP (preserve subfolders if provided)
    if ($assetsDir) {
        $src = findInDir($assetsDir, $rel);
        if ($src && is_file($src)) {
            $destRel = $rel !== '' ? $rel :
                (time().'_'.bin2hex(random_bytes(4)).'.'.(pathinfo($src, PATHINFO_EXTENSION) ?: 'bin'));
            ensureUploadsSubdir($destRel, $uploads);
            $destFull = $uploads . '/' . $destRel;
            if (@copy($src, $destFull)) return $destRel;
        }
    }

    // Fallback: basename already in uploads root
    if (is_file($uploads . '/' . basename($rel))) return basename($rel);

    return null;
}

/* -----------------------
   CSV Template download
------------------------ */
if (isset($_GET['template'])) {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="questions_template.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['question_text','question_file','option_1','option_1_file','option_2','option_2_file','option_3','option_3_file','option_4','option_4_file','correct_option']);
    fputcsv($out, ['What is 2 + 2?','',
                   '3','',
                   '4','',
                   '5','',
                   '22','',
                   '2' /* or A/B/C/D or exact option text e.g. "4" */]);
    fclose($out);
    exit;
}

/* -----------------------
   Fetch exam details
------------------------ */
$stmt = $conn->prepare("SELECT title, course_name, unit_exam FROM exams WHERE id=?");
$stmt->bind_param("i", $exam_id);
$stmt->execute();
$examRes = $stmt->get_result();
if (!$examRes || !$examRes->num_rows) {
    $_SESSION['error'] = "Exam not found.";
    header("Location: dashboard.php"); exit;
}
$exam = $examRes->fetch_assoc();
$stmt->close();

/* -----------------------
   Import
------------------------ */
$report = [];
$successCount = 0;
$skipCount = 0;
$errorCount = 0;

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['csv_file'])) {

    // Optional: unpack assets ZIP (images/audio, may include subfolders)
    $assetsDir = null;
    if (!empty($_FILES['assets_zip']) && $_FILES['assets_zip']['error'] === UPLOAD_ERR_OK) {
        $tmpZip = $_FILES['assets_zip']['tmp_name'];
        $base   = ensureUploadsDir() . '/bulk_' . time() . '_' . bin2hex(random_bytes(3));
        if (!is_dir($base)) mkdir($base, 0777, true);
        $z = new ZipArchive();
        if ($z->open($tmpZip) === true) {
            $z->extractTo($base);
            $z->close();
            $assetsDir = $base;
        }
    }

    if ($_FILES['csv_file']['error'] !== UPLOAD_ERR_OK) {
        $report[] = ['row'=>0,'status'=>'error','msg'=>'CSV upload failed.'];
    } else {
        $tmp = $_FILES['csv_file']['tmp_name'];
        $rows = csvToArray($tmp);

        if (empty($rows)) {
            $report[] = ['row'=>0,'status'=>'error','msg'=>'No rows found in CSV.'];
        } else {
            $sql = "INSERT INTO questions
                    (exam_id, question_text, question_file,
                     option_1, option_1_file,
                     option_2, option_2_file,
                     option_3, option_3_file,
                     option_4, option_4_file,
                     correct_option)
                    VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
            $ins = $conn->prepare($sql);
            if (!$ins) {
                $report[] = ['row'=>0,'status'=>'error','msg'=>'Prepare failed: '.$conn->error];
            } else {
                $conn->begin_transaction();
                foreach ($rows as $i=>$r) {
                    $rowNum = $i + 1;

                    $q_text  = trim($r['question_text']  ?? '');
                    $q_fileS = trim($r['question_file']  ?? '');
                    $o1      = trim($r['option_1']       ?? '');
                    $o1_file = trim($r['option_1_file']  ?? '');
                    $o2      = trim($r['option_2']       ?? '');
                    $o2_file = trim($r['option_2_file']  ?? '');
                    $o3      = trim($r['option_3']       ?? '');
                    $o3_file = trim($r['option_3_file']  ?? '');
                    $o4      = trim($r['option_4']       ?? '');
                    $o4_file = trim($r['option_4_file']  ?? '');
                    $corr    = normalizeCorrectFlexible($r);

                    // Validation: allow image-only question or text question
                    if ($q_text === '' && $q_fileS === '') {
                        $report[] = ['row'=>$rowNum,'status'=>'skip','msg'=>'Missing question_text and question_file'];
                        $skipCount++; continue;
                    }

                    // Require at least 2 options
                    $optionCount = 0;
                    foreach ([$o1,$o2,$o3,$o4] as $opt) { if ($opt !== '') $optionCount++; }
                    if ($optionCount < 2) {
                        $report[] = ['row'=>$rowNum,'status'=>'skip','msg'=>'Need at least 2 options (provide any two of option_1..option_4 or A..D)'];
                        $skipCount++; continue;
                    }

                    if ($corr === null) {
                        $report[] = ['row'=>$rowNum,'status'=>'skip','msg'=>'Invalid correct_option (use 1-4, A-D, or exact option text)'];
                        $skipCount++; continue;
                    }

                    // Resolve files (URL, assets ZIP with subfolders, or existing uploads)
                    $q_file = resolveFileRef($q_fileS, $assetsDir);
                    $o1f    = resolveFileRef($o1_file,  $assetsDir);
                    $o2f    = resolveFileRef($o2_file,  $assetsDir);
                    $o3f    = resolveFileRef($o3_file,  $assetsDir);
                    $o4f    = resolveFileRef($o4_file,  $assetsDir);

                    // Insert row
                    $ins->bind_param(
                        "issssssssssi",
                        $exam_id, $q_text, $q_file,
                        $o1, $o1f,
                        $o2, $o2f,
                        $o3, $o3f,
                        $o4, $o4f,
                        $corr
                    );

                    if (!$ins->execute()) {
                        $report[] = ['row'=>$rowNum,'status'=>'error','msg'=>$ins->error];
                        $errorCount++;
                    } else {
                        $report[] = ['row'=>$rowNum,'status'=>'ok','msg'=>'Inserted'];
                        $successCount++;
                    }
                }
                $conn->commit();
                $ins->close();
            }
        }
    }
}

/* -----------------------
   Page
------------------------ */
?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>Bulk Upload Questions</title>
  <link href="../assets/css/bootstrap.min.css" rel="stylesheet">
</head>
<body class="bg-light">
    <?php include __DIR__ . '/../includes/topbar.php'; ?>
<div class="container py-4">
  <h3 class="mb-2">Bulk Upload — Questions</h3>
  <div class="text-muted mb-3">
    Exam: <strong><?php echo h($exam['title']); ?></strong> —
    <?php echo h($exam['course_name']); ?> /
    <?php echo h($exam['unit_exam']); ?>
  </div>

  <div class="mb-3">
    <a class="btn btn-outline-primary btn-sm" href="?exam_id=<?php echo (int)$exam_id; ?>&template=1">Download CSV template</a>
    <a class="btn btn-secondary btn-sm" href="add_question.php?exam_id=<?php echo (int)$exam_id; ?>">Single question form</a>
    <a class="btn btn-outline-dark btn-sm" href="dashboard.php">Back to dashboard</a>
  </div>

  <div class="card">
    <div class="card-body">
      <form method="post" enctype="multipart/form-data">
        <div class="mb-3">
          <label class="form-label">CSV File</label>
          <input class="form-control" type="file" name="csv_file" accept=".csv" required>
          <div class="form-text">
            Columns (aliases allowed): <code>question_text, question_file, option_1, option_1_file, option_2, option_2_file, option_3, option_3_file, option_4, option_4_file, correct_option</code>.<br>
            Aliases like <code>question,image,A,B,C,D,answer,a_file..d_file</code> also work. Delimiter auto-detected (comma/semicolon/tab).<br>
            <strong>correct_option</strong>: <code>1–4</code>, <code>A–D</code>, or the exact option text.
          </div>
        </div>

        <div class="mb-3">
          <label class="form-label">Assets ZIP (optional)</label>
          <input class="form-control" type="file" name="assets_zip" accept=".zip">
          <div class="form-text">
            Include images/audio (may be in subfolders). Reference by relative path in CSV, e.g., <code>flags/lk.png</code>.
          </div>
        </div>

        <button class="btn btn-success">Upload & Import</button>
      </form>
    </div>
  </div>

  <?php if (!empty($report)): ?>
    <div class="card mt-3">
      <div class="card-header">Import Summary</div>
      <div class="card-body">
        <p>
          Inserted: <strong class="text-success"><?php echo (int)$successCount; ?></strong> &nbsp;•&nbsp;
          Skipped: <strong class="text-warning"><?php echo (int)$skipCount; ?></strong> &nbsp;•&nbsp;
          Errors: <strong class="text-danger"><?php echo (int)$errorCount; ?></strong>
        </p>
        <div class="table-responsive">
          <table class="table table-sm">
            <thead><tr><th># Row</th><th>Status</th><th>Message</th></tr></thead>
            <tbody>
            <?php foreach ($report as $r): ?>
              <tr>
                <td><?php echo (int)$r['row']; ?></td>
                <td>
                  <?php if ($r['status']==='ok'): ?>
                    <span class="badge bg-success">OK</span>
                  <?php elseif ($r['status']==='skip'): ?>
                    <span class="badge bg-warning text-dark">Skip</span>
                  <?php else: ?>
                    <span class="badge bg-danger">Error</span>
                  <?php endif; ?>
                </td>
                <td><?php echo h($r['msg']); ?></td>
              </tr>
            <?php endforeach; ?>
            </tbody>
          </table>
        </div>
      </div>
    </div>
  <?php endif; ?>

</div>
</body>
</html>
