<?php
$servername = "localhost";
$username = "venmobdc_Auto_DataBase_User";
$password = "hB_nf_W-^SIh";
$dbname = "venmobdc_Auto_Database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // Sanitize and validate inputs
    $subjectCategory = isset($_POST['subjectCategory']) ? $conn->real_escape_string(trim($_POST['subjectCategory'])) : '';
    $selectedTable = isset($_POST['selectedTable']) ? $conn->real_escape_string(trim($_POST['selectedTable'])) : '';

    // Debug to check POST data
    echo "subjectCategory: " . $subjectCategory . "<br>";
    echo "selectedTable: " . $selectedTable . "<br>";

    if (!empty($subjectCategory) && !empty($selectedTable)) {
        // Check if the Subject_Category2 column exists before trying to add it
        $checkColumnSql = "SHOW COLUMNS FROM `$selectedTable` LIKE 'Subject_Category2'";
        $columnResult = $conn->query($checkColumnSql);

        if ($columnResult->num_rows == 0) {
            // Add the column if it doesn't exist
            $alterTableSql = "ALTER TABLE `$selectedTable` ADD COLUMN `Subject_Category2` VARCHAR(255)";
            if ($conn->query($alterTableSql) === TRUE) {
                echo "Column 'Subject_Category2' added to table '$selectedTable'.<br>";
            } else {
                echo "Error adding 'Subject_Category2' column: " . $conn->error;
            }
        } else {
            echo "Column 'Subject_Category2' already exists.<br>";
        }

        // Initial merge subjectCategory and numbering
        $uniqueMergedName = $subjectCategory; // Start with subjectCategory name

        // Append numbers to ensure the table name is unique for subjectCategory only
        $counter = 1;
        while (true) {
            // Check if table exists
            $checkTableSql = "SHOW TABLES LIKE '$uniqueMergedName'";
            $tableResult = $conn->query($checkTableSql);

            if ($tableResult->num_rows == 0) {
                // If the table does not exist, break the loop
                break;
            } else {
                // If the table exists, append the counter to the subjectCategory name
                $uniqueMergedName = $subjectCategory . "_" . $counter;
                $counter++;
            }
        }

        // Debug uniqueMergedName
        echo "uniqueMergedName (new table name): " . $uniqueMergedName . "<br>";

        // Insert subjectCategory into Subject_Category and the unique merged name into Subject_Category2
        $insertSql = "INSERT INTO `$selectedTable` (Subject_Category, Subject_Category2) VALUES ('$subjectCategory', '$uniqueMergedName')";
        if ($conn->query($insertSql) === TRUE) {
            echo "Data inserted into '$selectedTable': Subject_Category = '$subjectCategory', Subject_Category2 = '$uniqueMergedName'.<br>";
        } else {
            echo "Error inserting into '$selectedTable': " . $conn->error;
        }

        // Create a new table with the uniqueMergedName (only affecting subjectCategory)
        $createTableSql = "CREATE TABLE IF NOT EXISTS `$uniqueMergedName` (
            id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            Chapter VARCHAR(255) NOT NULL,
            Book_ID INT(11) NOT NULL,
            Point INT(11) NOT NULL
        )";

        if ($conn->query($createTableSql) === TRUE) {
            echo "Table '$uniqueMergedName' created successfully.<br>";
        } else {
            echo "Error creating table '$uniqueMergedName': " . $conn->error;
        }

    } else {
        echo "Invalid table name or subject category.<br>";
    }
} else {
    echo "Invalid request method.<br>";
}

$conn->close();
?>
