<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <style>
        body {
            font-family: Arial, sans-serif;
            background-color: #f4f4f4;
            margin: 0;
            padding: 20px;
        }

        form {
            background-color: #ffffff;
            padding: 20px;
            border-radius: 5px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
            max-width: 600px;
            margin: auto;
        }

        label {
            display: block;
            margin: 10px 0 5px;
        }

        input[type="text"], input[type="file"], input[type="submit"], select {
            width: 100%;
            padding: 10px;
            margin: 5px 0 15px;
            border: 1px solid #cccccc;
            border-radius: 4px;
        }

        input[type="submit"] {
            background-color: #007BFF;
            color: white;
            border: none;
            cursor: pointer;
        }

        input[type="submit"]:hover {
            background-color: #0056b3;
        }
    </style>
</head>
<?php
require('fpdf/fpdf.php'); // Include FPDF library

// Database connection details
$host = 'localhost'; // Hostname
$dbname = 'oscotech_cbt'; // Database name
$username = 'oscotech_oscopt'; // Database username
$password = 'oscopt'; // Database password

try {
    // Create a new PDO instance
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Check if the form is submitted (after "Upload Questions" button is clicked)
    if (isset($_POST['upload'])) {
        // Get form data
        $year = strtoupper($_POST['year']);
        $level = strtoupper($_POST['level']);
	$dept= strtoupper($_POST['dept']);
        $courseTitle = strtoupper($_POST['course_title']);
        $courseCode = strtoupper($_POST['course_code']);

        // Create question_level string
        $question_level = $year . $dept . $level . $courseCode;
        $question_level_score = $question_level . 'score';

        // Check if the question_level table exists
        $tableExistsQuery = "SHOW TABLES LIKE :table_name";
        $stmt = $pdo->prepare($tableExistsQuery);
        $stmt->execute([':table_name' => $question_level]);
        $tableExists = $stmt->fetchColumn();

        if (!$tableExists) {
            // Create new question_level table
            $createTableQuery = "
                CREATE TABLE IF NOT EXISTS $question_level (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    qst TEXT NOT NULL,
                    opt1 VARCHAR(255) NOT NULL,
                    opt2 VARCHAR(255) NOT NULL,
                    opt3 VARCHAR(255) NOT NULL,
                    opt4 VARCHAR(255) NOT NULL,
                    ans VARCHAR(255) NOT NULL
                )";
            $pdo->exec($createTableQuery);

            // Create corresponding score table
            $createScoreTableQuery = "
                CREATE TABLE IF NOT EXISTS $question_level_score (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    matric VARCHAR(20) NOT NULL,
					fname VARCHAR(100) NOT NULL,
                    score INT NOT NULL,
					scoreb INT NOT NULL,
                    dept VARCHAR(200) NOT NULL
                )";
            $pdo->exec($createScoreTableQuery);
        }

        // Process CSV upload and insert into the new question_level table
        $csvFile = $_FILES['csv_file']['tmp_name'];

        if (($handle = fopen($csvFile, 'r')) !== FALSE) {
            // Prepare query to insert into the new question_level table
            $insertQuery = "
                INSERT INTO $question_level (qst, opt1, opt2, opt3, opt4, ans)
                VALUES (:question, :option1, :option2, :option3, :option4, :correct_option)
            ";

            // Insert questions into the new table
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                $question = $data[1];
                $option1 = $data[2];
                $option2 = $data[3];
                $option3 = $data[4];
                $option4 = $data[5];
                $correctOption = $data[6];

                // Insert question into the new question_level table
                $stmt = $pdo->prepare($insertQuery);
                $stmt->execute([
                    ':question' => $question,
                    ':option1' => $option1,
                    ':option2' => $option2,
                    ':option3' => $option3,
                    ':option4' => $option4,
                    ':correct_option' => $correctOption,
                ]);
            }

            fclose($handle);
            echo "Questions uploaded successfully to the table '$question_level'!";

            // Insert details into question_log table
            $logQuery = "
                INSERT INTO question_log (year, dept, level, course_title, course_code, question_level, question_level_score)
                VALUES (:year, :dept, :level, :course_title, :course_code, :question_level, :question_level_score)
            ";
            $logStmt = $pdo->prepare($logQuery);
            $logStmt->execute([
                ':year' => $year,
		':dept' => $dept,
                ':level' => $level,
                ':course_title' => $courseTitle,
                ':course_code' => $courseCode,
                ':question_level' => $question_level,
				':question_level_score' => $question_level_score
            ]);
            echo "Details saved to question_log successfully!";
				
        } else {
            echo "Failed to open CSV file.";
        }
    }
} catch (PDOException $e) {
    // Handle any errors
    echo "Error: " . $e->getMessage();
}
?>

<!-- HTML Form for admin to upload CSV file and enter course details -->
<form action="" method="post" enctype="multipart/form-data">
    <label>Year:</label>
    <input type="text" name="year" value="2024" required><br>

    <label>Class Level:</label>
        <select name="level" id="level" required>
        <option value="">--Select Level--</option>
        <option value="NDI">NDI</option>
        <option value="NDII">NDII</option>
        <option value="HNDI">HNDI</option>
        <option value="HNDII">HNDII</option>
    </select>
    <br>

    <label>Department:</label>
        <select name="dept" id="dept" required>
        <option value="">--Select Department--</option>
      <option value="HNDIGENERASTUDIES">HNDIGENERASTUDIES</option>
       <option value="HNDIIGENERASTUDIES">HNDIIGENERASTUDIES</option>
        <option value="NDIGENERASTUDIES">NDIGENERASTUDIES</option>
         <option value="NDIIGENERASTUDIES">NDIIGENERASTUDIES</option>

         <option value="Automobile">Automobile</option>
             <option value="Entreprenuship">Entreprenuship</option>

		<option value="Barbing">Barbing</option>
 		<option value="Beeds">Beeds</option>
		<option value="Bricklying">Bricklying</option>
		<option value="Carpentry">Carpentry</option>
		<option value="Eventplanning">Eventplanning</option>
		<option value="Catering">Catering</option>
		<option value="Computerrepair">Computerrepair</option>
		<option value="Electrical">Electrical</option>
		<option value="Fashion">Fashion</option>
                <option value="Welding">Welding</option>
                <option value="GSM">GSM</option>
                <option value="HatMaking">HatMaking</option>
                <option value="household">household</option>
                <option value="music">music</option>
                <option value="Painting">Painting</option>
                
                <option value="Plumbering">Plumbering</option>     
                <option value="Refrigerator">Refrigerator</option>
                <option value="shoemaking">shoemaking</option>
                <option value="Textile">Textile</option>
                
                <option value="Hairdressing">Hairdressing</option>
           <!--     <option value="Science Laboratory Technology">Science Laboratory Technology</option>
                
               
                <option value="Electronics/Telecommunication (Option)">Electronics/Telecommunication (Option)</option>
                <option value="Power/Machine(Option)">Power/Machine(Option)</option>
                
                 <option value="(Microbiology (Option)">(Microbiology (Option)</option>
                <option value="Chemistry (Option)">Chemistry (Option)</option>
                <option value="Physics with Electronics (Option)">Physics with Electronics (Option)</option>
                
                <option value="Marketing">Marketing</option>
                <option value="Taxation">Taxation</option>
                
               
                <option value="Photography">Photography</option>
                <option value="Arts and Design">Arts and Design</option>
                 <option value="Computer Science">Computer Science</option>
                
                <option value="Computer Engineering">Computer Engineering</option>
                <option value="Bio-chemistry">Biochemistry</option>
                
                 <option value="Environmental Biology">Environmental Biology</option>
                <option value="Agricultural Technology">Agricultural Technology</option>
                <option value="Library and Information Science">Library and Information Science</option>
                
                <option value="Office Technology and Management">Office Technology and Management</option>
                <option value="Multimedia Technology">Multimedia Technology</option> -->
                
                                
            </select>
    
    <br>

    <label>Course Title:</label>
    <input type="text" name="course_title" required><br>

    <label>Course Code:</label>
    <input type="text" name="course_code" required><br>

    <label>Upload CSV File:</label>
    <input type="file" name="csv_file" accept=".csv" required><br>

    <input type="submit" name="upload" value="Upload Questions">
</form>
