<?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 inputs
    $subject = isset($_POST['subject']) ? $conn->real_escape_string(trim($_POST['subject'])) : '';
    $selectedTable5 = isset($_POST['selectedTable5']) ? $conn->real_escape_string(trim($_POST['selectedTable5'])) : '';
    $selectedTable6 = isset($_POST['selectedTable6']) ? $conn->real_escape_string(trim($_POST['selectedTable6'])) : '';

    // Merge the selectedTable6 and subject (e.g., BBA_Accounting)
    $mergedText = $selectedTable6 . "_" . $subject;

    if (!empty($subject) && !empty($selectedTable5) && !empty($mergedText)) {
        // Check if the subject already exists in the selected table
        $checkSubjectSql = "SELECT * FROM `$selectedTable5` WHERE `Department` = '$subject'";
        $subjectResult = $conn->query($checkSubjectSql);

        if ($subjectResult->num_rows == 0) {
            // Check if the merged table already exists, and if so, append a number to create a unique name
            $tableExists = true;
            $counter = 1;
            $uniqueMergedText = $mergedText;

            // Loop to check if table exists, increment number if needed
            while ($tableExists) {
                $checkMergedTableSql = "SHOW TABLES LIKE '$uniqueMergedText'";
                $mergedTableResult = $conn->query($checkMergedTableSql);

                if ($mergedTableResult->num_rows == 0) {
                    $tableExists = false;  // Table doesn't exist, unique name found
                } else {
                    $uniqueMergedText = $mergedText . "_" . $counter;  // Append number to make it unique
                    $counter++;
                }
            }

            // Now insert the subject and the final table name (uniqueMergedText) into `$selectedTable5`
            $insertSubjectSql = "INSERT INTO `$selectedTable5` (Department, Department2) VALUES ('$subject', '$uniqueMergedText')";

            if ($conn->query($insertSubjectSql) === TRUE) {
                // Create the new table with the unique merged name and include both Subject_Category and Subject_Category2
                $createTableSql = "CREATE TABLE IF NOT EXISTS `$uniqueMergedText` (
                    id INT(11) AUTO_INCREMENT PRIMARY KEY,
                    Subject_Category VARCHAR(255),
                    Subject_Category2 VARCHAR(255)
                )";

                if ($conn->query($createTableSql) === TRUE) {
                    echo "Table '$uniqueMergedText' created successfully with 'Subject_Category' and 'Subject_Category2' columns. Subject inserted into '$selectedTable5' successfully.";
                } else {
                    echo "Error creating table '$uniqueMergedText': " . $conn->error;
                }
            } else {
                echo "Error inserting subject: " . $conn->error;
            }
        } else {
            echo "Subject already exists in '$selectedTable5'.";
        }
    } else {
        echo "Invalid input.";
    }
} else {
    echo "Invalid request method.";
}

$conn->close();
?>
