<?php
$page_title = 'นำเข้า CSV';
require_once __DIR__ . '/../includes/functions.php';
requireLogin();

$student_id = intval($_GET['student_id'] ?? 0);

if (isset($_GET['template'])) {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=import_template.csv');
    echo "\xEF\xBB\xBF";
    $cols = $student_id ? ['course_code','course_name','credits','grade','course_type','semester','academic_year','lecture','practice','self_study','notes','group_id'] : ['student_code','course_code','course_name','credits','grade','course_type','semester','academic_year','lecture','practice','self_study','notes','group_id'];
    echo implode(',', $cols) . "\n";
    if ($student_id) {
        echo "1012301,การเขียนโปรแกรมเบื้องต้น,3,A,เรียนปกติ,1,2569,2,1,3,,\n";
    } else {
        echo "STU001,1012301,การเขียนโปรแกรมเบื้องต้น,3,A,เรียนปกติ,1,2569,2,1,3,,\n";
    }
    exit;
}

if ($student_id) {
    requireStudentAccess($student_id);
    $student = getStudent($student_id);
    if (!$student) { echo '<div class="alert alert-danger">ไม่พบนักศึกษา</div>'; require_once __DIR__ . '/../includes/footer.php'; exit; }
}

require_once __DIR__ . '/../includes/header.php';

$results = [];
$total_success = 0;
$total_error = 0;

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['csv_file']) && $_FILES['csv_file']['error'] === UPLOAD_ERR_OK) {
    $tmp = $_FILES['csv_file']['tmp_name'];
    // Read all rows at once
    $all_rows = [];
    $handle = fopen($tmp, 'r');
    if ($handle) {
        while (($r = fgetcsv($handle)) !== false) $all_rows[] = $r;
        fclose($handle);
    }
    if (empty($all_rows)) { $results[] = 'ไฟล์ CSV ว่างเปล่า'; $total_error++; }
    else {
        $first = $all_rows[0];
        $first[0] = preg_replace('/^\xEF\xBB\xBF/', '', $first[0]);
        $first = array_map(function($v) { return trim($v, " \t\n\r\0\x0B\""); }, $first);
        // Auto-detect header: if first cell starts with a digit, it's data (no header)
        $has_header = !preg_match('/^\d/', $first[0]);
        if ($has_header) {
            $header_norm = array_map(function($v) {
                return preg_replace('/[^a-z0-9_]/', '', str_replace(' ', '_', strtolower(trim($v))));
            }, $first);
            $results[] = "พบหัวคอลัมน์: " . implode(', ', $first);
            $col_map = [];
            $expected = $student_id ? ['course_code','course_name','credits','grade','course_type','semester','academic_year','lecture','practice','self_study','notes','group_id'] : ['student_code','course_code','course_name','credits','grade','course_type','semester','academic_year','lecture','practice','self_study','notes','group_id'];
            foreach ($expected as $e) {
                $idx = array_search($e, $header_norm);
                if ($idx !== false) $col_map[$e] = $idx;
            }
            array_shift($all_rows);
        } else {
            $results[] = "ไม่พบแถวหัวคอลัมน์ — ใช้ตำแหน่งคอลัมน์ตามลำดับ";
            if ($student_id) {
                $col_map = ['course_code' => 0, 'course_name' => 1, 'credits' => 2, 'grade' => 3, 'course_type' => 4, 'semester' => 5, 'academic_year' => 6, 'lecture' => 7, 'practice' => 8, 'self_study' => 9, 'notes' => 10, 'group_id' => 11];
            } else {
                $col_map = ['student_code' => 0, 'course_code' => 1, 'course_name' => 2, 'credits' => 3, 'grade' => 4, 'course_type' => 5, 'semester' => 6, 'academic_year' => 7, 'lecture' => 8, 'practice' => 9, 'self_study' => 10, 'notes' => 11, 'group_id' => 12];
            }
        }
        $db = getDB();
        foreach ($all_rows as $idx => $row) {
            $line = $idx + 1;
            $c = fn($k) => $col_map[$k] ?? null;
            if ($student_id) {
                $student = getStudent($student_id);
                $student_code = $student['student_code'];
            } else {
                $student_code = trim($row[$c('student_code')] ?? '');
            }
            $course_code = trim($row[$c('course_code')] ?? '');
            $course_name = trim($row[$c('course_name')] ?? '');
            $credits = floatval($row[$c('credits')] ?? 0);
            $grade = trim($row[$c('grade')] ?? '');
            $course_type_col = trim($row[$c('course_type')] ?? '');
            $semester = trim($row[$c('semester')] ?? '');
            $academic_year = trim($row[$c('academic_year')] ?? '');
            $lecture = intval($row[$c('lecture')] ?? 0);
            $practice = intval($row[$c('practice')] ?? 0);
            $self_study = intval($row[$c('self_study')] ?? 0);
            $notes = trim($row[$c('notes')] ?? '');
            $group_id = intval($row[$c('group_id')] ?? 0);

            if (empty($course_code) || empty($course_name) || $credits <= 0) {
                $results[] = "แถวที่ $line: ข้าม (ข้อมูลไม่ครบ: course_code, course_name, credits)";
                $total_error++;
                continue;
            }

            try {
                if (!$student_id) {
                    $stmt = $db->prepare("SELECT id, curriculum_id, advisor_id FROM students WHERE student_code = ?");
                    $stmt->execute([$student_code]);
                    $student = $stmt->fetch();
                    if (!$student) {
                        $results[] = "แถวที่ $line: ไม่พบรหัสนักศึกษา '$student_code'";
                        $total_error++;
                        continue;
                    }
                    if (isAdvisor() && $student['advisor_id'] != getCurrentUserId()) {
                        $results[] = "แถวที่ $line: นักศึกษา '$student_code' ไม่อยู่ในที่ปรึกษาของคุณ";
                        $total_error++;
                        continue;
                    }
                }

                $curriculum_course = findCourseInCurriculum($student['curriculum_id'], $course_code);
                $course_id = null;
                if ($curriculum_course) {
                    $course_id = $curriculum_course['id'];
                } elseif ($group_id > 0) {
                    $stmt = $db->prepare("SELECT id FROM courses WHERE group_id = ? AND code = ?");
                    $stmt->execute([$group_id, $course_code]);
                    $existing = $stmt->fetch();
                    if ($existing) {
                        $course_id = $existing['id'];
                    } else {
                        $stmt = $db->prepare("INSERT INTO courses (group_id, code, name_th, credits, lecture_hours, practice_hours, self_study_hours, sort_order) VALUES (?, ?, ?, ?, ?, ?, ?, 0)");
                        $stmt->execute([$group_id, $course_code, $course_name, $credits, $lecture, $practice, $self_study]);
                        $course_id = $db->lastInsertId();
                    }
                }

                $stmt = $db->prepare("INSERT INTO student_courses (student_id, course_id, course_code, course_name_th, credits, lecture_hours, practice_hours, self_study_hours, grade, course_type, semester, academic_year, source_type, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'manual', ?)");
                $stmt->execute([$student['id'], $course_id, $course_code, $course_name, $credits, $lecture, $practice, $self_study, $grade ?: null, $course_type_col ?: null, $semester ?: null, $academic_year ?: null, $notes]);
                $results[] = "แถวที่ $line: ✓ $student_code - $course_code ($course_name)";
                $total_success++;
            } catch (PDOException $e) {
                $results[] = "แถวที่ $line: ผิดพลาด - " . $e->getMessage();
                $total_error++;
            }
        }
    }
}
?>
<div class="page-heading">
    <div>
        <h4><i class="bi bi-upload"></i> นำเข้ารายวิชาจากไฟล์ CSV</h4>
        <div class="text-muted small">รองรับไฟล์ .csv ตามรูปแบบคอลัมน์ที่ระบบกำหนด</div>
    </div>
    <a href="<?= $student_id ? 'view.php?id=' . $student_id : 'list.php' ?>" class="btn btn-secondary"><i class="bi bi-arrow-left"></i> กลับ</a>
</div>

<?php if ($student_id): ?>
<div class="alert alert-info">กำลังนำเข้าสำหรับนักศึกษา: <strong><?= htmlspecialchars($student['student_code'] ?? '') ?> - <?= htmlspecialchars($student['name_th'] ?? '') ?></strong> (ไม่ต้องระบุ student_code ใน CSV)</div>
<?php endif; ?>

<div class="row g-4">
    <div class="col-md-6">
        <div class="card">
            <div class="card-header bg-primary text-white">
                <h5 class="mb-0"><i class="bi bi-filetype-csv"></i> เลือกไฟล์สำหรับนำเข้า</h5>
            </div>
            <div class="card-body">
                <form method="POST" enctype="multipart/form-data">
                    <div class="mb-3">
                        <label class="form-label">เลือกไฟล์ CSV</label>
                        <input type="file" name="csv_file" class="form-control" accept=".csv" required>
                    </div>
                    <div class="mb-3">
                        <label class="form-label">รูปแบบไฟล์</label>
                        <p class="text-muted small mb-2">ไฟล์ CSV ต้องมีหัวคอลัมน์ด้านล่างนี้ (คั่นด้วย comma):</p>
                        <code class="d-block p-2 bg-light rounded small"><?= $student_id ? 'course_code,course_name,credits,grade,course_type,semester,academic_year,lecture,practice,self_study,notes,group_id' : 'student_code,course_code,course_name,credits,grade,course_type,semester,academic_year,lecture,practice,self_study,notes,group_id' ?></code>
                        <p class="text-muted small mt-2 mb-0">คอลัมน์ที่จำเป็น: <strong><?= $student_id ? 'course_code, course_name, credits' : 'student_code, course_code, course_name, credits' ?></strong></p>
                    </div>
                    <button type="submit" class="btn btn-primary"><i class="bi bi-cloud-upload"></i> นำเข้า</button>
                </form>
            </div>
        </div>
        <div class="card mt-3">
            <div class="card-body">
                <p class="mb-1 fw-bold">📥 ดาวน์โหลด Template</p>
                <a href="import_csv.php?template=1<?= $student_id ? '&student_id=' . $student_id : '' ?>" class="btn btn-outline-success btn-sm"><i class="bi bi-download"></i> template.csv</a>
            </div>
        </div>
    </div>

    <div class="col-md-6">
        <?php if ($total_success > 0 || $total_error > 0): ?>
        <div class="card">
            <div class="card-header bg-<?= $total_error > 0 ? 'warning' : 'success' ?> text-white d-flex justify-content-between">
                <span><i class="bi bi-list-check"></i> ผลลัพธ์</span>
                <span>สำเร็จ <?= $total_success ?> | ล้มเหลว <?= $total_error ?></span>
            </div>
            <div class="card-body" style="max-height:500px;overflow-y:auto">
                <ul class="list-unstyled mb-0 small">
                    <?php foreach ($results as $r): ?>
                    <li class="mb-1 <?= str_starts_with($r, 'แถว') && str_contains($r, '✓') ? 'text-success' : (str_starts_with($r, 'แถว') ? 'text-danger' : 'text-muted') ?>"><?= htmlspecialchars($r) ?></li>
                    <?php endforeach; ?>
                </ul>
            </div>
        </div>
        <?php endif; ?>
    </div>
</div>

<?php require_once __DIR__ . '/../includes/footer.php'; ?>
