<?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
    $selectedTable = isset($_POST['selectedTable']) ? $conn->real_escape_string(trim($_POST['selectedTable'])) : '';
    $year = isset($_POST['year']) ? $conn->real_escape_string(trim($_POST['year'])) : '';

    if (!empty($selectedTable) && !empty($year)) {
        // Create a dynamic table name by combining selectedTable and year
        $dynamicTableName = $selectedTable . "_" . $year;

        // Check if the dynamic table already exists
        $checkDynamicTableSql = "SHOW TABLES LIKE '$dynamicTableName'";
        $dynamicTableResult = $conn->query($checkDynamicTableSql);

        if ($dynamicTableResult->num_rows == 0) {
            // Create a new table named after the selectedTable_year with 'Department' and 'Department2' columns
            $createYearTableSql = "CREATE TABLE IF NOT EXISTS `$dynamicTableName` (
                id INT(11) AUTO_INCREMENT PRIMARY KEY,
                Department VARCHAR(255),
                Department2 VARCHAR(255)
            )";
            if ($conn->query($createYearTableSql) === TRUE) {
                echo "Table '$dynamicTableName' created successfully. ";
            } else {
                echo "Error creating table '$dynamicTableName': " . $conn->error;
            }
        } else {
            echo "Table '$dynamicTableName' already exists. ";
        }

        // Check if the year and dynamicTableName already exist in the selected table
        $checkYearSql = "SELECT * FROM `$selectedTable` WHERE `Year` = '$year' AND `Table_Name` = '$dynamicTableName'";
        $yearResult = $conn->query($checkYearSql);

        if ($yearResult->num_rows == 0) {
            // Insert the year and dynamicTableName into the selected table
            $insertYearSql = "INSERT INTO `$selectedTable` (`Year`, `Table_Name`) VALUES ('$year', '$dynamicTableName')";
            if ($conn->query($insertYearSql) === TRUE) {
                echo "Year and table name inserted successfully into '$selectedTable'. ";
            } else {
                echo "Error inserting year and table name: " . $conn->error;
            }
        } else {
            echo "Year and table name already exist in '$selectedTable'. ";
        }

        // Check if the dynamicTableName already exists in the Class_Year table
        $checkClassYearSql = "SELECT * FROM `Class_Year` WHERE `class_and_year` = '$dynamicTableName'";
        $classYearResult = $conn->query($checkClassYearSql);

        if ($classYearResult->num_rows == 0) {
            // Create or update the Class_Year table
            $createClassYearTableSql = "CREATE TABLE IF NOT EXISTS `Class_Year` (
                id INT(11) AUTO_INCREMENT PRIMARY KEY,
                class_and_year VARCHAR(255)
            )";
            if ($conn->query($createClassYearTableSql) === TRUE) {
                // Insert the dynamicTableName into the Class_Year table
                $insertClassYearSql = "INSERT INTO `Class_Year` (`class_and_year`) VALUES ('$dynamicTableName')";
                if ($conn->query($insertClassYearSql) === TRUE) {
                    echo "Table '$dynamicTableName' inserted into 'Class_Year' table.";
                } else {
                    echo "Error inserting into 'Class_Year' table: " . $conn->error;
                }
            } else {
                echo "Error creating 'Class_Year' table: " . $conn->error;
            }
        } else {
            echo "Table '$dynamicTableName' already exists in 'Class_Year' table.";
        }
    } else {
        echo "Invalid table or year.";
    }
} else {
    echo "Invalid request method.";
}

$conn->close();
?>
