<?php
require_once __DIR__ . '/auth.php';
require_login();
if (!perms_can_import_candidates(current_user())) { http_response_code(403); echo 'Forbidden'; exit; }

$user       = current_user();
$isSuper    = perms_is_super($user);
$facilityId = $user['facility_id'];

$msg = '';
$err = '';
$preview = [];

// ---------- DOWNLOAD TEMPLATE ----------
if (isset($_GET['download_template']) && $_GET['download_template'] === '1') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=osce_candidates_template_' . date('Ymd_His') . '.csv');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Facility', 'Candidate Number', 'Name', 'Cohort Year', 'Section', 'Circuit Type', 'Barcode']);
    fputcsv($out, ['Main OSCE Centre', '2024GMD01', 'John Doe', '2024', 'A', 'Standard', '']);
    fclose($out);
    exit;
}

function find_or_use_facility_id(PDO $pdo, bool $isSuper, ?int $current, string $facilityNameOrCode): array {
    $key = trim($facilityNameOrCode);
    if ($isSuper && $key !== '') {
        // Allow lookup by code OR name
        $stmt = $pdo->prepare('SELECT id, code FROM facilities WHERE code = ? OR name = ? LIMIT 1');
        $stmt->execute([strtoupper($key), $key]);
        $row = $stmt->fetch();
        if ($row) return [(int)$row['id'], $row['code']];
        // create new
        $pdo->prepare('INSERT INTO facilities (name) VALUES (?)')->execute([$key]);
        return [(int)$pdo->lastInsertId(), null];
    }
    if (!empty($current)) {
        $stmt = $pdo->prepare('SELECT code FROM facilities WHERE id = ?');
        $stmt->execute([$current]);
        $row = $stmt->fetch();
        return [(int)$current, $row['code'] ?? null];
    }
    $row = $pdo->query('SELECT id, code FROM facilities ORDER BY id ASC LIMIT 1')->fetch();
    if ($row) return [(int)$row['id'], $row['code']];
    $pdo->prepare('INSERT INTO facilities (name) VALUES (?)')->execute(['Default OSCE Centre']);
    return [(int)$pdo->lastInsertId(), null];
}

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

    if ($_FILES['csv_file']['error'] !== UPLOAD_ERR_OK) {
        $err = 'Error uploading file.';
    } elseif ($_FILES['csv_file']['size'] > 5 * 1024 * 1024) {
        $err = 'File too large (max 5 MB).';
    } else {
        $tmpName = $_FILES['csv_file']['tmp_name'];
        $handle = fopen($tmpName, 'r');
        if (!$handle) {
            $err = 'Cannot open uploaded file.';
        } else {
            $header = fgetcsv($handle);
            if (!$header) {
                $err = 'Empty or invalid CSV.';
            } else {
                $norm = array_map('strtolower', array_map('trim', $header));
                $idx = [
                    'facility' => array_search('facility', $norm),
                    'candno'   => array_search('candidate number', $norm),
                    'name'     => array_search('name', $norm),
                    'year'     => array_search('cohort year', $norm),
                    'section'  => array_search('section', $norm),
                    'circuit'  => array_search('circuit type', $norm),
                    'barcode'  => array_search('barcode', $norm),
                ];

                if ($idx['candno'] === false || $idx['name'] === false) {
                    $err = 'CSV must contain at least "Candidate Number" and "Name" columns.';
                } else {
                    $imported = $updated = $skipped = 0;

                    try {
                        $pdo->beginTransaction();
                        while (($data = fgetcsv($handle)) !== false) {
                            if (count(array_filter($data, fn($v) => trim((string)$v) !== '')) === 0) continue;

                            $facilityHint = $idx['facility'] !== false ? ($data[$idx['facility']] ?? '') : '';
                            $candNo   = v_clean_string($data[$idx['candno']] ?? '', 50);
                            $candName = v_clean_string($data[$idx['name']]   ?? '', 100);
                            $year     = $idx['year']    !== false ? v_clean_string($data[$idx['year']],    10) : '';
                            $section  = $idx['section'] !== false ? v_clean_string($data[$idx['section']], 50) : '';
                            $circuit  = $idx['circuit'] !== false ? v_clean_string($data[$idx['circuit']], 50) : '';
                            $barcode  = $idx['barcode'] !== false ? v_clean_string($data[$idx['barcode']], 50) : '';

                            if ($candNo === '' || $candName === '') { $skipped++; continue; }

                            [$facId, $facCode] = find_or_use_facility_id($pdo, $isSuper, $facilityId, (string)$facilityHint);

                            if ($barcode === '') {
                                $barcode = barcode_from_candidate($candNo, $facCode, $facId);
                            } else {
                                $barcode = code39_sanitize($barcode);
                            }
                            if ($barcode === '') { $skipped++; continue; }

                            // upsert by (facility_id, candidate_number)
                            $check = $pdo->prepare('SELECT id, barcode FROM candidates WHERE candidate_number = ? AND facility_id = ?');
                            $check->execute([$candNo, $facId]);
                            $existing = $check->fetch();

                            if ($existing) {
                                $pdo->prepare('UPDATE candidates SET name=?, section=?, circuit_type=?, cohort_year=?, barcode=?, deleted_at=NULL WHERE id=?')
                                    ->execute([$candName, $section ?: null, $circuit ?: null, $year ?: null, $barcode, $existing['id']]);
                                $updated++;
                            } else {
                                // ensure unique barcode (auto-suffix on collision)
                                $tries = 0; $final = $barcode;
                                while ($tries < 20) {
                                    try {
                                        $pdo->prepare('INSERT INTO candidates (facility_id, candidate_number, name, section, circuit_type, cohort_year, barcode, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, NOW())')
                                            ->execute([$facId, $candNo, $candName, $section ?: null, $circuit ?: null, $year ?: null, $final]);
                                        break;
                                    } catch (PDOException $e) {
                                        if (str_contains($e->getMessage(), 'Duplicate') && str_contains($e->getMessage(), 'barcode')) {
                                            $tries++; $final = $barcode . '-' . ($tries + 1);
                                            continue;
                                        }
                                        throw $e;
                                    }
                                }
                                $imported++;
                            }

                            $preview[] = [
                                'facility' => $facilityHint !== '' ? $facilityHint : 'Facility ID ' . $facId,
                                'candidate_number' => $candNo,
                                'name' => $candName,
                                'year' => $year,
                                'section' => $section,
                                'circuit' => $circuit,
                                'barcode' => $barcode,
                            ];
                        }
                        $pdo->commit();
                        audit_log($pdo, 'candidates_import', ['imported'=>$imported,'updated'=>$updated,'skipped'=>$skipped]);

                        $parts = [];
                        if ($imported > 0) $parts[] = "$imported inserted";
                        if ($updated > 0)  $parts[] = "$updated updated";
                        if ($skipped > 0)  $parts[] = "$skipped skipped";
                        $msg = $parts ? 'Import complete: ' . implode(', ', $parts) . '.' : 'No valid rows found.';
                    } catch (Throwable $e) {
                        $pdo->rollBack();
                        error_log('candidates_import: ' . $e->getMessage());
                        $err = 'Import failed; no changes saved.';
                    }
                }
            }
            fclose($handle);
        }
    }
}

require_once __DIR__ . '/header.php';
?>
<h2>Import Candidates (CSV)</h2>

<?php if ($msg): ?><div class="alert alert-success"><?php echo h($msg); ?></div><?php endif; ?>
<?php if ($err): ?><div class="alert alert-danger"><?php echo h($err); ?></div><?php endif; ?>

<p>
    <a href="candidates_import.php?download_template=1" class="btn">⬇ Download CSV Template</a>
</p>

<p>Columns: <code>Facility, Candidate Number, Name, Cohort Year, Section, Circuit Type, Barcode</code></p>
<ul>
    <li><strong>Facility</strong>: optional (super admin). Match by name or short code (e.g. <code>MED</code>). Created if missing.</li>
    <li><strong>Candidate Number</strong>: required (e.g. <code>2024GMD01</code>).</li>
    <li><strong>Name</strong>: required.</li>
    <li><strong>Cohort Year, Section, Circuit Type</strong>: optional metadata, printed on barcode labels.</li>
    <li><strong>Barcode</strong>: optional. If blank, auto-generated as <code>{FACILITY_CODE}-{CANDIDATE_NUMBER}</code> (e.g. <code>MED-2024GMD01</code>).</li>
</ul>

<form method="post" enctype="multipart/form-data">
    <?php echo csrf_field(); ?>
    <div class="field mb-2">
        <label>Select CSV file (max 5 MB)</label>
        <input type="file" name="csv_file" accept=".csv,text/csv" required>
    </div>
    <button type="submit" class="btn btn-primary">Upload &amp; Import</button>
</form>

<?php if ($preview): ?>
    <h3 class="mt-3">Processed Rows</h3>
    <div class="table-wrap">
    <table class="data">
        <thead><tr><th>Facility</th><th>Candidate #</th><th>Name</th><th>Year</th><th>Section</th><th>Circuit</th><th>Barcode</th></tr></thead>
        <tbody>
        <?php foreach ($preview as $p): ?>
            <tr>
                <td data-label="Facility"><?php echo h($p['facility']); ?></td>
                <td data-label="Candidate #"><?php echo h($p['candidate_number']); ?></td>
                <td data-label="Name"><?php echo h($p['name']); ?></td>
                <td data-label="Year"><?php echo h($p['year']); ?></td>
                <td data-label="Section"><?php echo h($p['section']); ?></td>
                <td data-label="Circuit"><?php echo h($p['circuit']); ?></td>
                <td data-label="Barcode"><code><?php echo h($p['barcode']); ?></code></td>
            </tr>
        <?php endforeach; ?>
        </tbody>
    </table>
    </div>
<?php endif; ?>

<?php require __DIR__ . '/footer.php'; ?>
