<?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 the `years` and `Department` from POST data
$years = isset($_POST['years']) ? trim($_POST['years']) : '';
$department = isset($_POST['Department']) ? trim($_POST['Department']) : '';

// Check if `years` and `Department` are provided
if (empty($years)) {
    echo json_encode(array('status' => 'error', 'message' => 'Years not provided.'));
    exit;
}

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

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

if ($yearsTableResult && $yearsTableResult->num_rows > 0) {
    // Step 2: Delete the selected department row from the Department2 column in the years table
    $deleteDepartmentQuery = "DELETE FROM `$years` WHERE `Department2` = '$department' LIMIT 1";
    if ($conn->query($deleteDepartmentQuery) === TRUE) {
        echo "Department '$department' deleted from `$years` successfully.<br>";

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

        if ($departmentTableResult && $departmentTableResult->num_rows > 0) {
            // Step 4: Fetch all `Subject_Category2` data from the department table
            $fetchSubjectCategoryQuery = "SELECT Subject_Category2 FROM `$department`";
            $subjectCategoryResult = $conn->query($fetchSubjectCategoryQuery);

            if ($subjectCategoryResult && $subjectCategoryResult->num_rows > 0) {
                while ($subjectRow = $subjectCategoryResult->fetch_assoc()) {
                    $subjectCategoryTable = $subjectRow['Subject_Category2'];

                    // Check if the Subject_Category2 table exists
                    $checkSubjectCategoryTableQuery = "SHOW TABLES LIKE '$subjectCategoryTable'";
                    $subjectCategoryTableResult = $conn->query($checkSubjectCategoryTableQuery);

                    if ($subjectCategoryTableResult && $subjectCategoryTableResult->num_rows > 0) {
                        // Step 5: Fetch data from the `Chapter` column in the `Subject_Category2` table
                        $fetchChapterQuery = "SELECT Chapter FROM `$subjectCategoryTable`";
                        $chapterResult = $conn->query($fetchChapterQuery);

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

                                // Step 6: Check if the Chapter table exists and delete it
                                $checkChapterTableQuery = "SHOW TABLES LIKE '$chapterTable'";
                                $chapterTableResult = $conn->query($checkChapterTableQuery);

                                if ($chapterTableResult && $chapterTableResult->num_rows > 0) {
                                    // Delete the chapter table
                                    $dropChapterTableQuery = "DROP TABLE `$chapterTable`";
                                    if ($conn->query($dropChapterTableQuery) === TRUE) {
                                        echo "Chapter table '$chapterTable' deleted successfully.<br>";
                                    } else {
                                        echo "Error deleting Chapter table '$chapterTable': " . $conn->error . "<br>";
                                    }
                                } else {
                                    echo "Chapter table '$chapterTable' does not exist.<br>";
                                }
                            }
                        } else {
                            echo "No data found in Chapter column in table '$subjectCategoryTable'.<br>";
                        }

                        // After deleting Chapter tables, delete the Subject_Category2 table itself
                        $dropSubjectCategoryTableQuery = "DROP TABLE `$subjectCategoryTable`";
                        if ($conn->query($dropSubjectCategoryTableQuery) === TRUE) {
                            echo "Subject_Category2 table '$subjectCategoryTable' deleted successfully.<br>";
                        } else {
                            echo "Error deleting Subject_Category2 table '$subjectCategoryTable': " . $conn->error . "<br>";
                        }
                    } else {
                        echo "Subject_Category2 table '$subjectCategoryTable' does not exist.<br>";
                    }
                }
            } else {
                echo "No data found in Subject_Category2 column in department table '$department'.<br>";
            }

            // After processing `Subject_Category2` and `Chapter`, delete the department table itself
            $dropDepartmentTableQuery = "DROP TABLE `$department`";
            if ($conn->query($dropDepartmentTableQuery) === TRUE) {
                echo "Department table '$department' deleted successfully.<br>";
            } else {
                echo "Error deleting department table '$department': " . $conn->error . "<br>";
            }
        } else {
            echo "Department table '$department' does not exist.<br>";
        }
    } else {
        echo json_encode(array('status' => 'error', 'message' => 'Failed to delete department from the years table: ' . $conn->error));
    }
} else {
    echo json_encode(array('status' => 'error', 'message' => 'Years table does not exist.'));
}

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