<?php
include 'config.php';

$existingCategories = [];
$existingUsers = [];
$updatedUsers = [];
$invalidEmails = [];

if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_FILES["file"])) {
    $fileType = $_POST['file_type'];
    $file = $_FILES["file"]["tmp_name"];
    $handle = fopen($file, "r");

    if ($handle !== FALSE) {
        fgetcsv($handle); // Skip the first row (column headers)

        if ($fileType == "user_category") {
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                $email = trim($data[0]);
                $category = trim($data[1]);

                // Validate email domain
                if (!filter_var($email, FILTER_VALIDATE_EMAIL) || !str_ends_with($email, '@kijaniforestry.com')) {
                    $invalidEmails[] = $email;
                    continue;
                }

                // Insert category if not exists
                $stmt = $conn->prepare("INSERT INTO categories (category_name) VALUES (?) ON DUPLICATE KEY UPDATE category_name=category_name");
                $stmt->bind_param("s", $category);
                $stmt->execute();
                if ($stmt->affected_rows == 0) {
                    $existingCategories[] = $category;
                }
                $stmt->close();

                // Get category ID
                $stmt = $conn->prepare("SELECT category_id FROM categories WHERE category_name = ?");
                $stmt->bind_param("s", $category);
                $stmt->execute();
                $stmt->bind_result($category_id);
                $stmt->fetch();
                $stmt->close();

                // Check if user exists in the specified category
                $stmt = $conn->prepare("SELECT user_id FROM users WHERE email = ? AND category_id = ?");
                $stmt->bind_param("si", $email, $category_id);
                $stmt->execute();
                $stmt->store_result();
                if ($stmt->num_rows > 0) {
                    $existingUsers[] = $email;
                    $stmt->close();
                } else {
                    $stmt->close();
                    // Check if user exists in any category
                    $stmt = $conn->prepare("SELECT user_id FROM users WHERE email = ?");
                    $stmt->bind_param("s", $email);
                    $stmt->execute();
                    $stmt->store_result();
                    if ($stmt->num_rows > 0) {
                        $stmt->bind_result($user_id);
                        $stmt->fetch();
                        $stmt->close();

                        // Update user with new category
                        $stmt = $conn->prepare("INSERT INTO users (user_id, email, category_id) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE category_id = ?");
                        $stmt->bind_param("isii", $user_id, $email, $category_id, $category_id);
                        $stmt->execute();
                        $updatedUsers[] = $email;
                        $stmt->close();
                    } else {
                        // Insert new user
                        $stmt->close();
                        $stmt = $conn->prepare("INSERT INTO users (email, category_id) VALUES (?, ?)");
                        $stmt->bind_param("si", $email, $category_id);
                        $stmt->execute();
                        $stmt->close();
                    }
                }
            }
            $message = "User and category data uploaded successfully.";
        } elseif ($fileType == "questions_answers") {
            while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                $question = $data[0];
                $category = $data[1];
                $topic = $data[2];
                $options = array_slice($data, 3, 4); // Options are from column 4 to 7
                $correctOption = $data[7];

                // Insert category if not exists
                $stmt = $conn->prepare("INSERT INTO categories (category_name) VALUES (?) ON DUPLICATE KEY UPDATE category_name=category_name");
                $stmt->bind_param("s", $category);
                $stmt->execute();
                if ($stmt->affected_rows == 0) {
                    $existingCategories[] = $category;
                }
                $stmt->close();

                // Get category ID
                $stmt = $conn->prepare("SELECT category_id FROM categories WHERE category_name = ?");
                $stmt->bind_param("s", $category);
                $stmt->execute();
                $stmt->bind_result($category_id);
                $stmt->fetch();
                $stmt->close();

                // Insert topic if not exists
                $stmt = $conn->prepare("INSERT INTO topics (topic_name, category_id) VALUES (?, ?) ON DUPLICATE KEY UPDATE topic_name=topic_name");
                $stmt->bind_param("si", $topic, $category_id);
                $stmt->execute();
                $stmt->close();

                // Get topic ID
                $stmt = $conn->prepare("SELECT topic_id FROM topics WHERE topic_name = ?");
                $stmt->bind_param("s", $topic);
                $stmt->execute();
                $stmt->bind_result($topic_id);
                $stmt->fetch();
                $stmt->close();

                // Insert question
                $stmt = $conn->prepare("INSERT INTO questions (question_text, topic_id) VALUES (?, ?)");
                $stmt->bind_param("si", $question, $topic_id);
                $stmt->execute();
                $question_id = $stmt->insert_id;
                $stmt->close();

                // Insert options
                foreach ($options as $index => $option) {
                    $is_correct = ($correctOption == "Option " . ($index + 1)) ? 1 : 0;
                    $stmt = $conn->prepare("INSERT INTO options (question_id, option_text, is_correct) VALUES (?, ?, ?)");
                    $stmt->bind_param("isi", $question_id, $option, $is_correct);
                    $stmt->execute();
                    $stmt->close();
                }
            }
            $message = "Questions and answers uploaded successfully.";
        }
        fclose($handle);
    }
}

$conn->close();
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Bulk Upload</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
<div class="container mt-5">
    <h1>Bulk Upload</h1>
    <?php if (isset($message)): ?>
        <div class="alert alert-success"><?= $message ?></div>
    <?php endif; ?>
    <?php if (!empty($existingCategories)): ?>
        <div class="alert alert-info">The following categories already existed: <?= implode(", ", $existingCategories) ?></div>
    <?php endif; ?>
    <?php if (!empty($existingUsers) && empty($updatedUsers)): ?>
        <div class="alert alert-info">The following users already existed in their respective categories: <?= implode(", ", $existingUsers) ?></div>
    <?php endif; ?>
    <?php if (!empty($updatedUsers)): ?>
        <div class="alert alert-info">The following users were updated with new categories: <?= implode(", ", $updatedUsers) ?></div>
    <?php endif; ?>
    <?php if (!empty($invalidEmails)): ?>
        <div class="alert alert-danger">The following emails are invalid or do not belong to @kijaniforestry.com: <?= implode(", ", $invalidEmails) ?></div>
    <?php endif; ?>
    <form method="post" enctype="multipart/form-data">
        <div class="form-group">
            <label for="file_type">Select Upload Type:</label>
            <select id="file_type" name="file_type" class="form-control" required>
                <option value="user_category">User & Category</option>
                <option value="questions_answers">Questions & Answers</option>
            </select>
        </div>
        <div class="form-group">
            <label for="file">Select CSV File:</label>
            <input type="file" id="file" name="file" class="form-control-file" accept=".csv" required>
        </div>
        <button type="submit" class="btn btn-primary">Upload</button>
    </form>
    <hr>
    <h2>Instructions</h2>
    <h3>User & Category Upload</h3>
    <p>The CSV file should have two columns:</p>
    <ul>
        <li><strong>Email:</strong> The email address of the user.</li>
        <li><strong>Category:</strong> The category the user belongs to.</li>
    </ul>
    <h3>Questions & Answers Upload</h3>
    <p>The CSV file should have the following columns:</p>
    <ul>
        <li><strong>Question:</strong> The text of the question.</li>
        <li><strong>Category:</strong> The category the question belongs to.</li>
        

        <li><strong>Topic:</strong> The topic the question belongs to.</li>
        <li><strong>Option 1:</strong> The first answer option.</li>
        <li><strong>Option 2:</strong> The second answer option.</li>
        <li><strong>Option 3:</strong> The third answer option.</li>
        <li><strong>Option 4:</strong> The fourth answer option.</li>
        <li><strong>Correct Option:</strong> The correct answer option (e.g., Option 1, Option 2, etc.).</li>
    </ul>
</div>
</body>
</html>
