<?php
require_once('source/session.php');
require_once('source/error.php');
require_once('source/dbconfig.php');
require_once('source/function.php');
require_once('source/restrict_user.php');

// Get admin info
$admin_name = "";
$rows = sql($DBH, "SELECT * FROM tbl_admin where status = ?", array("Active"), "rows");
foreach($rows as $row) {
    $admin_name = $row['username'];
}

// Handle query messages for UI
if(isset($_GET['msg'])) {
    if($_GET['msg'] == "refered") {
        $_SESSION['success_message'] = "Referred successfully";
    } else if($_GET['msg'] == "failed") {
        $_SESSION['error_message'] = "Something went wrong, please try again";
    }
}

// Generate and serve sample CSV - MUST BE CHECKED BEFORE ANY OTHER PROCESSING
if(isset($_GET['download_sample'])) {
    // Create sample data matching your database structure
    $sample_data = [
        ['John Doe', 'STD001', '5', '4', '5', '4', '5', '4', '5', '4', '5', '4'],
        ['Jane Smith', 'STD002', '4', '5', '4', '5', '4', '5', '4', '5', '4', '5'],
        ['Mike Johnson', 'STD003', '3', '4', '3', '4', '3', '4', '3', '4', '3', '4'],
        ['Sarah Williams', 'STD004', '5', '5', '5', '5', '5', '5', '5', '5', '5', '5'],
        ['David Brown', 'STD005', '4', '3', '4', '3', '4', '3', '4', '3', '4', '3']
    ];
    
    // Headers matching your database columns
    $headers = [
        'Student Name',
        'Registration Number',
        'Creativity (1-5)',
        'Fluency (1-5)',
        'Honesty (1-5)',
        'Leadership (1-5)',
        'Neatness (1-5)',
        'Politeness (1-5)',
        'Punctuality (1-5)',
        'Self Control (1-5)',
        'Sense of Responsibility (1-5)',
        'Sociability (1-5)'
    ];
    
    // Set headers for CSV download
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=SKILLS_SAMPLE.csv');
    
    // Open output stream
    $output = fopen('php://output', 'w');
    
    // Add BOM for UTF-8
    fwrite($output, "\xEF\xBB\xBF");
    
    // Write headers
    fputcsv($output, $headers);
    
    // Write sample data
    foreach($sample_data as $row) {
        fputcsv($output, $row);
    }
    
    fclose($output);
    exit(); // STOP HERE - don't process anything else
}

/*
 * CSV import handler for Skills
 * Based on actual database columns from your INSERT statement
 * Columns: name, registration_no, Creativity, Fluency, Honesty, Leadership, 
 * Neatness, Politeness, Punctuality, SelfControl, SenseOfResponsibility, 
 * Sociability, class, subclass, subject, year, teacher_no, csv_no, term
 */
if(isset($_POST['importSubmit'])) {
    $teacher_no = strip_tags($_POST['teacher_no']);
    $s_term = strip_tags($_POST['term']);
    $s_year = strip_tags($_POST['year']);

    try {
        // Get teacher's class and subclass
        $stmt = $DBH->prepare("SELECT class, subclass FROM tbl_user WHERE hash = ?");
        $stmt->execute([$teacher_no]);
        $teacher_data = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($teacher_data) {
            $s_class = $teacher_data['class'];
            $s_subclass = $teacher_data['subclass'];
        } else {
            throw new Exception("Teacher not found");
        }

        // Get subject from teacher's details if needed
        $s_subject = ""; // You might need to get this from somewhere

        // Unique import identifier for this upload batch
        $key = mt_rand(100000, 999999);

        // Allowed file types
        $allowedTypes = array('text/x-comma-separated-values','text/comma-separated-values',
            'application/octet-stream','application/vnd.ms-excel','application/x-csv',
            'text/x-csv','text/csv','application/csv','application/excel',
            'application/vnd.msexcel','text/plain');

        if(!empty($_FILES['file']['name'])) {
            $file_ext = strtolower(pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION));
            
            if(in_array($file_ext, ['csv', 'xlsx', 'xls'])) {
                if(is_uploaded_file($_FILES['file']['tmp_name'])) {
                    
                    $rows = [];
                    
                    // Handle CSV file
                    if($file_ext == 'csv') {
                        $csvFile = fopen($_FILES['file']['tmp_name'], 'r');
                        while(($line = fgetcsv($csvFile)) !== FALSE) {
                            $rows[] = $line;
                        }
                        fclose($csvFile);
                    } else {
                        // Handle Excel file (if you have PhpSpreadsheet installed)
                        // For now, we'll just process CSV
                        throw new Exception("Please convert Excel file to CSV format first");
                    }

                    $success_count = 0;
                    $update_count = 0;
                    $insert_count = 0;
                    $error_count = 0;
                    $error_details = [];
                    $row_index = 0;

                    foreach($rows as $line) {
                        $row_index++;
                        try {
                            // Skip header row
                            if($row_index == 1) continue;
                            
                            // Based on your INSERT: need at least 12 columns
                            if(count($line) < 12) {
                                $error_details[] = "Row {$row_index}: Need at least 12 columns for skills import";
                                $error_count++;
                                continue;
                            }

                            // Map CSV columns based on your INSERT statement
                            $name = trim($line[0]);                    // Column 0: name
                            $registration_no = trim($line[1]);        // Column 1: registration_no
                            $Creativity = trim($line[2]);             // Column 2: Creativity
                            $Fluency = trim($line[3]);                // Column 3: Fluency
                            $Honesty = trim($line[4]);                // Column 4: Honesty
                            $Leadership = trim($line[5]);             // Column 5: Leadership
                            $Neatness = trim($line[6]);               // Column 6: Neatness
                            $Politeness = trim($line[7]);             // Column 7: Politeness
                            $Punctuality = trim($line[8]);            // Column 8: Punctuality
                            $SelfControl = trim($line[9]);            // Column 9: SelfControl
                            $SenseOfResponsibility = trim($line[10]); // Column 10: SenseOfResponsibility
                            $Sociability = trim($line[11]);           // Column 11: Sociability

                            // Validate required fields
                            if(empty($name) || empty($registration_no)) {
                                $error_details[] = "Row {$row_index}: Missing name or registration number";
                                $error_count++;
                                continue;
                            }

                            // Validate ratings (1-5)
                            $ratings = [$Creativity, $Fluency, $Honesty, $Leadership, 
                                       $Neatness, $Politeness, $Punctuality, $SelfControl, 
                                       $SenseOfResponsibility, $Sociability];
                            
                            $valid_ratings = true;
                            foreach($ratings as $rating) {
                                if($rating < 1 || $rating > 5) {
                                    $valid_ratings = false;
                                    break;
                                }
                            }
                            
                            if(!$valid_ratings) {
                                $error_details[] = "Row {$row_index}: Ratings must be between 1 and 5";
                                $error_count++;
                                continue;
                            }

                            // Check if a record exists for this student in SAME YEAR, TERM, and CLASS
                            // This prevents duplicate entries for the same student in same term
                            $check_stmt = $DBH->prepare("
                                SELECT id FROM skills 
                                WHERE registration_no = ? 
                                AND year = ?
                                AND term = ?
                                AND class = ?
                                AND teacher_no = ?
                                LIMIT 1
                            ");
                            $check_stmt->execute([
                                $registration_no, 
                                $s_year, 
                                $s_term,
                                $s_class,
                                $teacher_no
                            ]);
                            $existing = $check_stmt->fetch(PDO::FETCH_ASSOC);

                            if ($existing && isset($existing['id'])) {
                                // UPDATE existing record - same student, same year/term/class/teacher
                                $update_stmt = $DBH->prepare("
                                    UPDATE skills SET
                                        name = ?, 
                                        Creativity = ?, 
                                        Fluency = ?, 
                                        Honesty = ?, 
                                        Leadership = ?, 
                                        Neatness = ?, 
                                        Politeness = ?, 
                                        Punctuality = ?, 
                                        SelfControl = ?, 
                                        SenseOfResponsibility = ?, 
                                        Sociability = ?,
                                        subclass = ?, 
                                        subject = ?, 
                                        csv_no = ?,
                                        updated_at = NOW()
                                    WHERE id = ?
                                ");
                                $update_result = $update_stmt->execute([
                                    $name, 
                                    $Creativity, $Fluency, $Honesty, $Leadership,
                                    $Neatness, $Politeness, $Punctuality, $SelfControl,
                                    $SenseOfResponsibility, $Sociability,
                                    $s_subclass, $s_subject, $key,
                                    $existing['id']
                                ]);
                                
                                if($update_result) {
                                    $update_count++;
                                    $success_count++;
                                } else {
                                    $error_details[] = "Row {$row_index}: Failed to update existing record";
                                    $error_count++;
                                }
                                
                            } else {
                                // INSERT new record - student doesn't have skills for this year/term/class/teacher yet
                                $insert_stmt = $DBH->prepare("
                                    INSERT INTO skills
                                        (name, registration_no, 
                                        Creativity, Fluency, Honesty, Leadership, 
                                        Neatness, Politeness, Punctuality, SelfControl, 
                                        SenseOfResponsibility, Sociability,
                                        class, subclass, subject, year, teacher_no, csv_no, term)
                                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                                ");
                                $insert_result = $insert_stmt->execute([
                                    $name, $registration_no,
                                    $Creativity, $Fluency, $Honesty, $Leadership,
                                    $Neatness, $Politeness, $Punctuality, $SelfControl,
                                    $SenseOfResponsibility, $Sociability,
                                    $s_class, $s_subclass, $s_subject, $s_year, $teacher_no, $key, $s_term
                                ]);
                                
                                if($insert_result) {
                                    $insert_count++;
                                    $success_count++;
                                } else {
                                    $error_details[] = "Row {$row_index}: Failed to insert new record";
                                    $error_count++;
                                }
                            }

                        } catch(PDOException $e) {
                            $error_details[] = "Row {$row_index}: Database error - " . $e->getMessage();
                            $error_count++;
                        } catch(Exception $e) {
                            $error_details[] = "Row {$row_index}: Error - " . $e->getMessage();
                            $error_count++;
                        }
                    }

                    // Prepare session messages
                    if($success_count > 0) {
                        $message = "Successfully processed {$success_count} skills records";
                        if($update_count > 0 || $insert_count > 0) {
                            $message .= " (";
                            if($update_count > 0) {
                                $message .= "{$update_count} updated";
                            }
                            if($insert_count > 0) {
                                $message .= ($update_count > 0 ? ", " : "") . "{$insert_count} new";
                            }
                            $message .= ")";
                        }
                        
                        $_SESSION['success_message'] = $message;

                        if($error_count > 0) {
                            $_SESSION['warning_message'] = "{$error_count} records had errors";
                            $_SESSION['error_details'] = $error_details;
                        }

                        // Store key in session for reference
                        $_SESSION['last_csv_no'] = $key;

                        $qstring = "?status=success&val=" . $key . "&records=" . $success_count;
                    } else {
                        $_SESSION['error_message'] = "No records were imported. Please check your CSV file format.";
                        if(!empty($error_details)) {
                            $_SESSION['error_details'] = $error_details;
                        }
                        $qstring = "?status=error&type=no_records";
                    }
                } else {
                    $_SESSION['error_message'] = "File upload failed";
                    $qstring = "?status=error&type=upload_failed";
                }
            } else {
                $_SESSION['error_message'] = "Invalid file type. Please upload a CSV or Excel file.";
                $qstring = "?status=error&type=invalid_file";
            }
        } else {
            $_SESSION['error_message'] = "Please select a file to upload.";
            $qstring = "?status=error&type=no_file";
        }
    } catch(Exception $e) {
        $_SESSION['error_message'] = "Error: " . $e->getMessage();
        $qstring = "?status=error&type=exception";
    }

    header("Location: post_skills.php" . $qstring);
    exit();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <title>VERBUM DEI INT'L COLLEGE - Upload Skills</title>
  <link rel="icon" type="image/png" href="https://portal.vda.com.ng/coll_admin/coll_logo.jpg">

  <!-- Bootstrap 5 CSS -->
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">

  <!-- Font Awesome -->
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">

  <!-- Custom CSS -->
  <style>
    :root {
      --primary-color: #1976d2;
      --secondary-color: #64b5f6;
      --accent-color: #bbdefb;
      --dark-color: #0d47a1;
      --light-color: #e3f2fd;
    }
    body {
      font-family: 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;
      background-color: var(--light-color);
      padding-top: 56px;
    }
    .sidebar {
      background: linear-gradient(180deg, var(--dark-color) 0%, var(--primary-color) 100%);
      position: fixed;
      top: 56px;
      left: -250px;
      width: 250px;
      height: calc(100vh - 56px);
      transition: all 0.3s;
      z-index: 1000;
      overflow-y: auto;
    }
    .sidebar.active { left: 0; }
    .sidebar .nav-link { color: rgba(255,255,255,0.85); font-weight:600; padding:.75rem 1rem; margin-bottom:.2rem; transition: all .2s; }
    .sidebar .nav-link:hover { color:#fff; background-color: rgba(255,255,255,0.15); }
    .sidebar .nav-link.active { color:#fff; background-color: var(--secondary-color); box-shadow:0 2px 5px rgba(0,0,0,.1); }
    .main-content { margin-left:0; width:100%; transition: all .3s; min-height: calc(100vh - 56px); }
    @media (min-width:992px) { .sidebar { left:0 } .main-content { margin-left:250px; width:calc(100% - 250px); } body { padding-top:0; } }
    .navbar { background-color: var(--primary-color); box-shadow: 0 0.15rem 1.75rem 0 rgba(33,37,41,0.1); position: fixed; top:0; width:100%; z-index:1030; color:white; }
    .card { border:none; border-radius:.5rem; box-shadow: 0 0.15rem 1.75rem 0 rgba(58,59,69,0.1); margin-bottom:1.5rem; transition: transform .2s, box-shadow .2s; }
    .card:hover { transform: translateY(-2px); box-shadow:0 0.5rem 1.5rem rgba(0,0,0,.1); }
    .card-header { background-color: var(--accent-color); border-bottom: 1px solid var(--secondary-color); font-weight:700; color: var(--dark-color); }
    .admin-info { display:flex; align-items:center; background-color: rgba(255,255,255,0.2); padding:8px 12px; border-radius:.5rem; color:white; }
    #importFrm { display: none; animation: slideDown .3s ease; }
    @keyframes slideDown { from { opacity:0; transform: translateY(-10px); } to { opacity:1; transform: translateY(0); } }
    .uploaded-year { background: linear-gradient(135deg, var(--primary-color), var(--secondary-color)); color:white; padding:15px; border-radius:8px; text-align:center; transition: all .3s ease; text-decoration:none; display:block; }
    .uploaded-year:hover { transform: translateY(-3px); box-shadow:0 5px 15px rgba(0,0,0,0.2); }
    .skill-category { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color:white; border-radius:8px; padding:15px; margin-bottom:15px; }
    .footer { background-color: var(--dark-color); color:white; padding:20px 0; margin-top:40px; }
    .powered-by { display:flex; align-items:center; justify-content:flex-end; gap:10px; }
    .powered-by img { height:30px; }
    .file-upload-area { border:2px dashed var(--secondary-color); border-radius:10px; padding:40px; text-align:center; background-color: rgba(100,181,246,0.1); cursor:pointer; transition: all .3s; }
    .file-upload-area:hover { background-color: rgba(100,181,246,0.2); border-color: var(--primary-color); }
    .file-upload-area i { font-size:48px; color:var(--primary-color); margin-bottom:20px; }
    .skill-columns { display: grid; grid-template-columns: repeat(auto-fill, minmax(200px, 1fr)); gap: 10px; margin-top: 15px; }
    .skill-item { background: rgba(100,181,246,0.1); padding: 8px 12px; border-radius: 5px; border-left: 3px solid var(--primary-color); }
    .update-logic { background-color: #e8f4fd; border-left: 4px solid var(--primary-color); padding: 15px; border-radius: 5px; margin-top: 20px; }
    .error-details { max-height: 200px; overflow-y: auto; font-size: 0.9em; }
  </style>
</head>

<body>
  <!-- Top Navigation -->
  <nav class="navbar navbar-expand-lg navbar-dark">
    <div class="container-fluid">
      <button id="sidebarToggle" class="btn btn-link d-lg-none me-2">
        <i class="fas fa-bars"></i>
      </button>
      <a class="navbar-brand d-none d-lg-block" href="#">
        VERBUM DEI INT'L COLLEGE
      </a>
      <div class="ms-auto d-flex align-items-center">
        <div class="admin-info me-3">
          <span class="admin-name"><?php echo htmlspecialchars($admin_name); ?></span>
          <span class="admin-access">(Administrator)</span>
          <i class="fas fa-user-circle admin-icon"></i>
        </div>
        <a href="logout.php" class="btn btn-sm btn-outline-light">
          <i class="fas fa-sign-out-alt"></i>
        </a>
      </div>
    </div>
  </nav>

  <!-- Sidebar -->
  <nav id="sidebar" class="sidebar">
    <div class="position-sticky pt-3">
      <div class="text-center mb-4 d-none d-lg-block">
        <img src="https://portal.vda.com.ng/coll_admin/coll_logo.jpg" alt="School Logo" class="rounded-circle" width="80">
        <h5 class="mt-2 text-white">VERBUM DEI INT'L COLLEGE</h5>
        <p class="text-white-50 small">Admin Dashboard</p>
      </div>

      <ul class="nav flex-column">
        <li class="nav-item"><a class="nav-link" href="main.php"><i class="fas fa-fw fa-tachometer-alt"></i> Admin Centre</a></li>
        <li class="nav-item"><a class="nav-link" href="post_attendance.php"><i class="fas fa-fw fa-calendar-check"></i> Upload Attendance</a></li>
        <li class="nav-item"><a class="nav-link active" href="post_skills.php"><i class="fas fa-fw fa-chart-bar"></i> Upload Skills</a></li>
      </ul>
    </div>
  </nav>

  <!-- Main Content -->
  <main class="main-content">
    <div class="container-fluid px-4 pt-4">
      <!-- Page Heading -->
      <div class="d-sm-flex align-items-center justify-content-between mb-4">
        <h1 class="h3 mb-0 text-gray-800">Upload Student's Skills & Affective Domains</h1>
        <div class="d-none d-sm-inline-block">
          <span class="badge bg-light text-dark p-2">
            <i class="fas fa-phone-alt me-1"></i> Support: MENDOH - 08032929756
          </span>
        </div>
      </div>

      <!-- Messages -->
      <?php if (isset($_SESSION['success_message'])): ?>
        <div class="alert alert-success alert-dismissible fade show" role="alert">
          <i class="fas fa-check-circle me-2"></i>
          <?php echo $_SESSION['success_message']; ?>
          <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
        </div>
        <?php unset($_SESSION['success_message']); ?>
      <?php endif; ?>

      <?php if (isset($_SESSION['warning_message'])): ?>
        <div class="alert alert-warning alert-dismissible fade show" role="alert">
          <i class="fas fa-exclamation-triangle me-2"></i>
          <?php echo $_SESSION['warning_message']; ?>
          <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
        </div>
        <?php unset($_SESSION['warning_message']); ?>
      <?php endif; ?>

      <?php if (isset($_SESSION['error_message'])): ?>
        <div class="alert alert-danger alert-dismissible fade show" role="alert">
          <i class="fas fa-times-circle me-2"></i>
          <?php echo $_SESSION['error_message']; ?>
          <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
        </div>
        <?php unset($_SESSION['error_message']); ?>
      <?php endif; ?>

      <!-- Error Details (if any) -->
      <?php if (isset($_SESSION['error_details'])): ?>
        <div class="alert alert-danger alert-dismissible fade show" role="alert">
          <h6 class="alert-heading"><i class="fas fa-exclamation-circle me-2"></i>Import Errors:</h6>
          <div class="error-details mt-2">
            <ul class="mb-0">
              <?php foreach($_SESSION['error_details'] as $error): ?>
                <li><?php echo htmlspecialchars($error); ?></li>
              <?php endforeach; ?>
            </ul>
          </div>
          <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
        </div>
        <?php unset($_SESSION['error_details']); ?>
      <?php endif; ?>

      <!-- Upload Form Card -->
      <div class="row">
        <div class="col-lg-12">
          <div class="card shadow mb-4">
            <div class="card-header py-3">
              <h6 class="m-0 font-weight-bold text-primary">
                <i class="fas fa-chart-bar me-2"></i>Upload Student's Skills & Affective Domains
                <button type="button" id="toggleImportBtn" class="btn btn-sm btn-outline-primary float-end">
                  <i class="fas fa-upload me-1"></i> Import Data
                </button>
              </h6>
            </div>
            <div class="card-body">
              <!-- Import Form -->
              <form action="post_skills.php" method="post" enctype="multipart/form-data" id="importFrm">
                <div class="mb-4">
                  <div class="file-upload-area" onclick="document.getElementById('file').click();">
                    <i class="fas fa-cloud-upload-alt"></i>
                    <h5>Click to upload or drag and drop</h5>
                    <p class="text-muted">CSV or Excel files only</p>
                    <input type="file" name="file" class="form-control d-none" id="file" accept=".csv,.xlsx,.xls" required>
                    <div id="fileName" class="mt-2 text-primary"></div>
                  </div>
                </div>

                <div class="row g-3">
                  <div class="col-md-3">
                    <label for="term" class="form-label">Term</label>
                    <select name="term" class="form-select" id="term" required>
                      <option value="1st Term">1st Term</option>
                      <option value="2nd Term">2nd Term</option>
                      <option value="3rd Term">3rd Term</option>
                    </select>
                  </div>

                  <div class="col-md-3">
                    <label for="year" class="form-label">Academic Year</label>
                    <select name="year" class="form-select" id="year" required>
                      <?php
                      $rows1 = sql($DBH, "SELECT * FROM tbl_session where id >= ?", array(1), "rows");
                      foreach($rows1 as $row1) {
                        $sch_session = $row1['session'];
                        echo "<option value='".htmlspecialchars($sch_session)."'>".htmlspecialchars($sch_session)."</option>";
                      }
                      ?>
                    </select>
                  </div>

                  <div class="col-md-4">
                    <label for="teacher_no" class="form-label">Class Teacher</label>
                    <select name="teacher_no" class="form-select" id="teacher_no" required>
                      <?php
                      $rows1 = sql($DBH, "SELECT * FROM tbl_user where teacher = ?", array("true"), "rows");
                      foreach($rows1 as $row1) {
                        $sch_hash = $row1['hash'];
                        $sch_name = $row1['fullname'];
                        $sc_class = $row1['class'];
                        echo "<option value='".htmlspecialchars($sch_hash)."'>".htmlspecialchars($sc_class . " - " . $sch_name)."</option>";
                      }
                      ?>
                    </select>
                  </div>

                  <div class="col-md-2 d-flex align-items-end">
                    <button type="submit" class="btn btn-primary w-100" name="importSubmit">
                      <i class="fas fa-upload me-1"></i> UPLOAD
                    </button>
                  </div>
                </div>

                <div class="update-logic mt-4">
                  <h6><i class="fas fa-sync-alt me-2 text-primary"></i>Update Logic:</h6>
                  <p class="mb-1"><strong>✓ UPDATE:</strong> If student already has skills record for <strong>same Year + Term + Class + Teacher</strong></p>
                  <p class="mb-0"><strong>✓ INSERT NEW:</strong> If student has no skills record for this Year/Term/Class/Teacher combination</p>
                  <p class="mt-2 text-muted small"><i class="fas fa-info-circle me-1"></i>Prevents duplicate records for same student from same teacher in same term</p>
                </div>
              </form>

              <div class="mt-4">
                <a href="?download_sample=1" class="btn btn-outline-danger me-2">
                  <i class="fas fa-file-excel me-1"></i> Download Sample CSV
                </a>
                <small class="text-muted ms-2">(Contains exact format needed for import)</small>
              </div>

              <!-- Required CSV Format -->
              <div class="mt-4">
                <h6 class="text-primary mb-3"><i class="fas fa-file-csv me-2"></i>Required CSV Format:</h6>
                <div class="skill-columns">
                  <div class="skill-item">1. Student Name</div>
                  <div class="skill-item">2. Registration Number</div>
                  <div class="skill-item">3. Creativity (1-5)</div>
                  <div class="skill-item">4. Fluency (1-5)</div>
                  <div class="skill-item">5. Honesty (1-5)</div>
                  <div class="skill-item">6. Leadership (1-5)</div>
                  <div class="skill-item">7. Neatness (1-5)</div>
                  <div class="skill-item">8. Politeness (1-5)</div>
                  <div class="skill-item">9. Punctuality (1-5)</div>
                  <div class="skill-item">10. Self Control (1-5)</div>
                  <div class="skill-item">11. Sense of Responsibility (1-5)</div>
                  <div class="skill-item">12. Sociability (1-5)</div>
                </div>
              </div>

              <div class="alert alert-warning mt-4">
                <i class="fas fa-exclamation-triangle me-2"></i>
                <strong>Important:</strong> Use rating scale 1-5 where 1=Poor, 2=Fair, 3=Good, 4=Very Good, 5=Excellent
              </div>
            </div>
          </div>
        </div>
      </div>

      <!-- Uploaded Skills Section -->
      <div class="card shadow mb-4">
        <div class="card-header py-3" style="background-color: var(--accent-color);">
          <h6 class="m-0 font-weight-bold text-primary">
            <i class="fas fa-history me-2"></i>Previously Uploaded Skills Records
          </h6>
        </div>
        <div class="card-body" style="background-color: var(--light-color);">
          <div class="row">
            <?php
            $STH = $DBH->prepare("SELECT count(*) FROM skills WHERE id >= ?");
            $STH->execute(array(1));
            $count = $STH->fetchColumn();

            if($count >= 1) {
              $rows = sql($DBH, "SELECT DISTINCT year FROM skills ORDER BY year DESC", array(), "rows");
              foreach($rows as $row) {
                $t_year = $row['year'];
                
                // Get count for this year
                $count_stmt = $DBH->prepare("SELECT COUNT(*) as total FROM skills WHERE year = ?");
                $count_stmt->execute([$t_year]);
                $year_count = $count_stmt->fetch(PDO::FETCH_ASSOC)['total'];
                
                // Get distinct terms for this year
                $term_stmt = $DBH->prepare("SELECT DISTINCT term FROM skills WHERE year = ?");
                $term_stmt->execute([$t_year]);
                $terms = $term_stmt->fetchAll(PDO::FETCH_COLUMN);
                $term_text = implode(", ", $terms);
                
                // Get count of unique students for this year
                $student_stmt = $DBH->prepare("SELECT COUNT(DISTINCT registration_no) as students FROM skills WHERE year = ?");
                $student_stmt->execute([$t_year]);
                $student_count = $student_stmt->fetch(PDO::FETCH_ASSOC)['students'];
                
                echo '
                <div class="col-md-3 mb-3">
                  <a href="view_skills.php?year='.htmlspecialchars($t_year).'" target="_blank" class="uploaded-year">
                    <i class="fas fa-chart-bar fa-2x mb-2"></i><br>
                    <strong>'.htmlspecialchars($t_year).'</strong><br>
                    <small>'.$year_count.' records</small><br>
                    <small>'.$student_count.' students</small><br>
                    <small class="badge bg-light text-dark mt-1">'.$term_text.'</small>
                  </a>
                </div>';
              }
            } else {
              echo '<div class="col-12 text-center py-5">
                <i class="fas fa-chart-line fa-3x mb-3 text-muted"></i>
                <h5 class="text-muted">No Skills Records Found</h5>
                <p class="text-muted">Upload your first skills assessment using the form above.</p>
              </div>';
            }
            ?>
          </div>
        </div>
      </div>
    </div>
  </main>

  <!-- Footer -->
  <footer class="footer">
    <div class="container-fluid">
      <div class="row align-items-center">
        <div class="col-md-6">
          <span class="copyright-text">Copyright &copy; VERBUM DEI INT'L COLLEGE <?php echo date("Y"); ?></span>
        </div>
        <div class="col-md-6">
          <div class="powered-by">
            <a href="https://dataswift.com.ng" target="_blank" rel="noopener noreferrer" class="powered-by-link text-white text-decoration-none">
              <span>Powered by DataSwift</span>
              <img src="datalogo.png" alt="DataSwift Logo">
            </a>
          </div>
        </div>
      </div>
    </div>
  </footer>

  <!-- Bootstrap 5 JS Bundle with Popper -->
  <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>

  <!-- jQuery -->
  <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>

  <!-- JavaScript -->
  <script>
  // Toggle sidebar on mobile
  document.getElementById('sidebarToggle').addEventListener('click', function() {
    document.getElementById('sidebar').classList.toggle('active');
    document.querySelector('.main-content').classList.toggle('active');
  });

  // Toggle import form with proper event handling
  $(document).ready(function() {
    // Toggle import form
    $('#toggleImportBtn').click(function(e) {
        e.stopPropagation(); // Prevent event bubbling
        $('#importFrm').slideToggle();
    });

    // Prevent form from closing when clicking inside it
    $('#importFrm').click(function(e) {
        e.stopPropagation();
    });

    // File input styling
    $('#file').change(function() {
        var fileName = $(this).val().split('\\').pop();
        if(fileName) {
            $('#fileName').html('<i class="fas fa-file me-2"></i><strong>' + fileName + '</strong>');
        }
    });

    // Drag and drop functionality
    $('.file-upload-area').on('dragover', function(e) {
        e.preventDefault();
        e.stopPropagation();
        $(this).css({
            'border-color': '#1976d2',
            'background-color': 'rgba(25, 118, 210, 0.1)'
        });
    });

    $('.file-upload-area').on('dragleave', function(e) {
        e.preventDefault();
        e.stopPropagation();
        $(this).css({
            'border-color': '#64b5f6',
            'background-color': 'rgba(100, 181, 246, 0.1)'
        });
    });

    $('.file-upload-area').on('drop', function(e) {
        e.preventDefault();
        e.stopPropagation();
        $(this).css({
            'border-color': '#64b5f6',
            'background-color': 'rgba(100, 181, 246, 0.1)'
        });
        
        var files = e.originalEvent.dataTransfer.files;
        $('#file')[0].files = files;
        
        var fileName = files[0].name;
        $('#fileName').html('<i class="fas fa-file me-2"></i><strong>' + fileName + '</strong>');
    });
  });
  </script>
</body>
</html>