<?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">
    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script>
        $(document).ready(function () {
            const topics = <?= json_encode($topics); ?>;
            $('#category_id_question').change(function () {
                const categoryId = $(this).val();
                const filteredTopics = topics.filter(topic => topic.category_id == categoryId);
                $('#topic_id').empty().append('<option value="">Select Topic</option>');
                filteredTopics.forEach(topic => {
                    $('#topic_id').append(`<option value="${topic.topic_id}">${topic.topic_name}</option>`);
                });
            });
        });
    </script>
    <script src="https://unpkg.com/@dotlottie/player-component@latest/dist/dotlottie-player.mjs" type="module"></script>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="Responsive Admin &amp; Dashboard Template based on Bootstrap 5">
    <meta name="author" content="AdminKit">
    <meta name="keywords"
        content="adminkit, bootstrap, bootstrap 5, admin, dashboard, template, responsive, css, sass, html, theme, front-end, ui kit, web">

    <link rel="preconnect" href="https://fonts.gstatic.com">
    <link rel="shortcut icon" href="img/icons/icon-48x48.png" />

    <link rel="canonical" href="https://demo-basic.adminkit.io/" />
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">

    <title>kijani quizes</title>

    <link href="css/app.css" rel="stylesheet">
    <link href="https://fonts.googleapis.com/css2?family=Inter:wght@300;400;600&display=swap" rel="stylesheet">

    <style>
        @keyframes spin {
            from {
                transform: rotate(0deg);
            }

            to {
                transform: rotate(360deg);
            }
        }

        .fa-spin {
            animation: spin 2s infinite linear;
        }
    </style>
</head>

<body>
    <div class="wrapper">
        <nav id="sidebar" class="sidebar js-sidebar">
            <div class="sidebar-content js-simplebar">
                <a class="sidebar-brand" href="index.php">
                    <span class="align-middle">Kijani Admin</span>
                </a>

                <ul class="sidebar-nav">
                    <li class="sidebar-header">
                        Pages
                    </li>

                    <li class="sidebar-item  ">
                        <a class="sidebar-link" href="index.php">
                            <i class="align-middle" data-feather="sliders"></i> <span
                                class="align-middle">Dashboard</span>
                        </a>
                    </li>

                    <li class="sidebar-item ">
                        <a class="sidebar-link" href="create_questions.php">
                            <i class="align-middle" data-feather="book"></i> <span class="align-middle">create
                                questions</span>
                        </a>
                    </li>
					<li class="sidebar-item">
                        <a class="sidebar-link" href="view_results.php">
                            <i class="align-middle" data-feather="book"></i>
                            <span class="align-middle">view quiz Results</span>
                        </a>
                    </li>
                    
                    <li class="sidebar-item">
                        <a class="sidebar-link" href="edit_questions.php">
                            <i class="align-middle" data-feather="book"></i>
                            <span class="align-middle">Edit Quiz</span>
                        </a>
                    </li>
                    <li class="sidebar-item">
                        <a class="sidebar-link" href="users.php">
                            <i class="align-middle" data-feather="user"></i>
                            <span class="align-middle">Users</span>
                        </a>
                    </li>
                    <li class="sidebar-item active">
                        <a class="sidebar-link" href="bulk_upload.php">
                            <i class="align-middle" data-feather="book"></i>
                            <span class="align-middle">Bulk upload</span>
                        </a>
                    </li>

            
                </ul>


            </div>
        </nav>

        <div class="main">
            <nav class="navbar navbar-expand navbar-light navbar-bg">
                <a class="sidebar-toggle js-sidebar-toggle">
                    <i class="hamburger align-self-center"></i>
                </a>

                <div class="navbar-collapse collapse">
                    <ul class="navbar-nav navbar-align">
                         
                        <li class="nav-item dropdown">
                            <a class="nav-icon dropdown-toggle d-inline-block d-sm-none" href="#"
                                data-bs-toggle="dropdown">
                                <i class="align-middle" data-feather="settings"></i>
                            </a>

                            <a class="nav-link dropdown-toggle d-none d-sm-inline-block" href="#"
                                data-bs-toggle="dropdown">
                                <img src="img/avatars/avatar.jpg" class="avatar img-fluid rounded me-1"
                                    alt="Charles Hall" /> <span class="text-dark">user</span>
                            </a>
                            <div class="dropdown-menu dropdown-menu-end">
                                <a class="dropdown-item" href="pages-profile.html"><i class="align-middle me-1"
                                        data-feather="user"></i> Profile</a>
                                <a class="dropdown-item" href="#"><i class="align-middle me-1"
                                        data-feather="pie-chart"></i> Analytics</a>
                                <div class="dropdown-divider"></div>
                                <a class="dropdown-item" href="index.html"><i class="align-middle me-1"
                                        data-feather="settings"></i> Settings & Privacy</a>
                                <a class="dropdown-item" href="#"><i class="align-middle me-1"
                                        data-feather="help-circle"></i> Help Center</a>
                                <div class="dropdown-divider"></div>
                                <a class="dropdown-item" href="#">Log out</a>
                            </div>
                        </li>
                    </ul>
                </div>
            </nav>

            <main class="content">

                <div class="container-fluid p-0">

				<div class="container-fluid   p-3 card bg-white col-md-12">
    <h1 class="card-title mb-3">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-info">Upload</button>
    </form></div>
    <div class="container-fluid   p-3 card bg-white col-md-12">
    <h2 class="card-title">Instructions</h2>
    <h5>User & Category Upload</h5>
    <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>
    <h5>Questions & Answers Upload</h5>
    <p class="font-weight-light">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> 
                    </div>
            </main>

            <footer class="footer">
                <div class="container-fluid">
                    <div class="row text-muted">
                        <div class="col-6 text-start">
                            <p class="mb-0">
                                <a class="text-muted" href="https://adminkit.io/" target="_blank"><strong>kijani
                                        forestry</strong></a> - <a class="text-muted" href=" "
                                    target="_blank"><strong>jonathan</strong></a> &copy;
                            </p>
                        </div>
                        <div class="col-6 text-end">
                            <ul class="list-inline">
                                <li class="list-inline-item">
                                    <a class="text-muted" href="https://adminkit.io/" target="_blank">Support</a>
                                </li>
                                <li class="list-inline-item">
                                    <a class="text-muted" href="https://adminkit.io/" target="_blank">Help Center</a>
                                </li>
                                <li class="list-inline-item">
                                    <a class="text-muted" href="https://adminkit.io/" target="_blank">Privacy</a>
                                </li>
                                <li class="list-inline-item">
                                    <a class="text-muted" href="https://adminkit.io/" target="_blank">Terms</a>
                                </li>
                            </ul>
                        </div>
                    </div>
                </div>
            </footer>
        </div>
    </div>

    <script src="js/app.js"></script>

     
     
    

</body>

</html>