<?php
// Database connection details
$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(json_encode(array('status' => 'error', 'message' => 'Connection failed: ' . $conn->connect_error)));
}

// Get `department` and `chapter` from POST data
$department = isset($_POST['department']) ? trim($_POST['department']) : '';
$chapter = isset($_POST['chapter']) ? trim($_POST['chapter']) : '';

// Validate inputs
if (empty($department)) {
    echo json_encode(array('status' => 'error', 'message' => 'Department not provided.'));
    exit;
}

if (empty($chapter)) {
    echo json_encode(array('status' => 'error', 'message' => 'Chapter not provided.'));
    exit;
}

// Step 1: Check if the `department` table exists
$checkDepartmentTableQuery = "SHOW TABLES LIKE '$department'";
$departmentTableResult = $conn->query($checkDepartmentTableQuery);

if ($departmentTableResult && $departmentTableResult->num_rows > 0) {
    // Step 2: Delete the specific `chapter` from `Subject_Category2` column in `department` table
    $deleteChapterQuery = "DELETE FROM `$department` WHERE `Subject_Category2` = '$chapter'";
    if ($conn->query($deleteChapterQuery) === TRUE) {
        echo json_encode(array('status' => 'success', 'message' => "Chapter '$chapter' deleted from `$department` table successfully."));

        // Step 3: Search for the chapter table corresponding to the `chapter` value
        $checkChapterTableQuery = "SHOW TABLES LIKE '$chapter'";
        $chapterTableResult = $conn->query($checkChapterTableQuery);

        if ($chapterTableResult && $chapterTableResult->num_rows > 0) {
            // Step 4: Fetch data from the `Chapter` column in the `chapter` table
            $fetchChapterDataQuery = "SELECT `Chapter` FROM `$chapter`";
            $chapterDataResult = $conn->query($fetchChapterDataQuery);

            if ($chapterDataResult && $chapterDataResult->num_rows > 0) {
                while ($row = $chapterDataResult->fetch_assoc()) {
                    $chapterTable = $row['Chapter'];

                    // Step 5: Check if the `Chapter` table exists and delete it
                    $checkChapterExistsQuery = "SHOW TABLES LIKE '$chapterTable'";
                    $chapterExistsResult = $conn->query($checkChapterExistsQuery);

                    if ($chapterExistsResult && $chapterExistsResult->num_rows > 0) {
                        // Delete the `Chapter` table
                        $dropChapterTableQuery = "DROP TABLE `$chapterTable`";
                        if ($conn->query($dropChapterTableQuery) === TRUE) {
                            echo json_encode(array('status' => 'success', 'message' => "Chapter table '$chapterTable' deleted successfully."));
                        } else {
                            echo json_encode(array('status' => 'error', 'message' => "Error deleting chapter table '$chapterTable': " . $conn->error));
                        }
                    } else {
                        echo json_encode(array('status' => 'error', 'message' => "Chapter table '$chapterTable' does not exist."));
                    }
                }
            } else {
                echo json_encode(array('status' => 'error', 'message' => "No data found in Chapter column of the `$chapter` table."));
            }

            // After deleting Chapter tables, delete the `chapter` table itself
            $dropChapterQuery = "DROP TABLE `$chapter`";
            if ($conn->query($dropChapterQuery) === TRUE) {
                echo json_encode(array('status' => 'success', 'message' => "Chapter table '$chapter' deleted successfully."));
            } else {
                echo json_encode(array('status' => 'error', 'message' => "Error deleting chapter table '$chapter': " . $conn->error));
            }
        } else {
            echo json_encode(array('status' => 'error', 'message' => "Chapter table '$chapter' does not exist."));
        }
    } else {
        echo json_encode(array('status' => 'error', 'message' => "Failed to delete chapter from `$department` table: " . $conn->error));
    }
} else {
    echo json_encode(array('status' => 'error', 'message' => "Department table '$department' does not exist."));
}

// Close the connection
$conn->close();
?>
