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

    if (!empty($input_Book_ID) && !empty($input_Chapter) && !empty($input_Point)) {

        // 1. Check if `input_Book_ID` exists in `Subject_Cagory_Book_ID`
        $checkBookSIdSql = "SELECT book_S_id FROM `Subject_Cagory_Book_ID` WHERE book_S_id = '$input_Book_ID'";
        $bookSIdResult = $conn->query($checkBookSIdSql);

        if ($bookSIdResult->num_rows > 0) {
            echo "Book ID '$input_Book_ID' already exists in 'Subject_Cagory_Book_ID'. No further actions needed.";
        } else {
            // If `Book_ID` does not exist in `Subject_Cagory_Book_ID`, insert it
            $insertBookSIdSql = "INSERT INTO `Subject_Cagory_Book_ID` (book_S_id) VALUES ('$input_Book_ID')";
            if ($conn->query($insertBookSIdSql) === TRUE) {
                echo "Book ID '$input_Book_ID' inserted into 'Subject_Cagory_Book_ID'. ";
            } else {
                echo "Error inserting Book ID: " . $conn->error;
                exit();
            }

            // 2. Insert `input_Chapter`, `input_Book_ID`, and `input_Point` into `selectedTable`
            if (!empty($selectedTable)) {
                $insertIntoSelectedTableSql = "INSERT INTO `$selectedTable` (Chapter, Book_ID, Point) 
                                               VALUES ('$input_Chapter', '$input_Book_ID', '$input_Point')";
                if ($conn->query($insertIntoSelectedTableSql) === TRUE) {
                    echo "Data inserted into '$selectedTable' successfully.";
                } else {
                    echo "Error inserting data into '$selectedTable': " . $conn->error;
                    exit();
                }
            }

            // 3. Create a new table based on `input_Chapter`
            $newTableName = $input_Chapter;
            $suffix = 0;

            // Check if the table already exists, and append suffix if necessary
            do {
                $checkTableExistsSql = "SHOW TABLES LIKE '$newTableName'";
                $result = $conn->query($checkTableExistsSql);

                if ($result->num_rows > 0) {
                    $suffix++;
                    $newTableName = $input_Chapter . '_' . $suffix;
                } else {
                    break;
                }
            } while (true);

            // Create the new table with `Class_link` and `Type`
            $createTableSql = "CREATE TABLE `$newTableName` (
                id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                Class_link VARCHAR(255) NOT NULL,
                Type VARCHAR(100) NOT NULL
            )";

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

    } else {
        echo "Invalid input data.";
    }
} else {
    echo "Invalid request method.";
}

$conn->close();
?>
