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

// Check if course (table name) and years are provided
if (empty($course)) {
    echo json_encode(array('status' => 'error', 'message' => 'Course not provided.'));
    exit;
}

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

// Check if the course table exists in the database
$checkTableQuery = "SHOW TABLES LIKE '$course'";
$result = $conn->query($checkTableQuery);

if ($result && $result->num_rows > 0) {
    // Table exists, now check if the year exists in the Table_Name column
    $checkYearQuery = "SELECT * FROM `$course` WHERE `Table_Name` = '$years'";
    $yearResult = $conn->query($checkYearQuery);

    if ($yearResult && $yearResult->num_rows > 0) {
        // Year exists in the selected table

        // Step 1: Check if the table named after the year exists
        $checkYearTableQuery = "SHOW TABLES LIKE '$years'";
        $yearTableResult = $conn->query($checkYearTableQuery);

        if ($yearTableResult && $yearTableResult->num_rows > 0) {
            // Year table exists, now fetch all data from Department2 column in the year table
            $fetchDepartment2Query = "SELECT Department2 FROM `$years`";
            $department2Result = $conn->query($fetchDepartment2Query);

            if ($department2Result && $department2Result->num_rows > 0) {
                // Step 2: Loop through Department2 values and check if tables exist
                while ($row = $department2Result->fetch_assoc()) {
                    $departmentTable = $row['Department2'];

                    // Check if the table exists in the database
                    $checkDepartmentTableQuery = "SHOW TABLES LIKE '$departmentTable'";
                    $departmentTableResult = $conn->query($checkDepartmentTableQuery);

                    if ($departmentTableResult && $departmentTableResult->num_rows > 0) {
                        // Department2 table exists, now fetch Subject_Category2 data
                        $fetchSubjectCategoryQuery = "SELECT Subject_Category2 FROM `$departmentTable`";
                        $subjectCategoryResult = $conn->query($fetchSubjectCategoryQuery);

                        if ($subjectCategoryResult && $subjectCategoryResult->num_rows > 0) {
                            // Step 3: Loop through Subject_Category2 values and check if corresponding tables exist
                            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) {
                                    // Subject_Category2 table exists, now fetch Chapter data
                                    $fetchChapterQuery = "SELECT Chapter FROM `$subjectCategoryTable`";
                                    $chapterResult = $conn->query($fetchChapterQuery);

                                    if ($chapterResult && $chapterResult->num_rows > 0) {
                                        // Step 4: Loop through Chapter values and check if corresponding tables exist
                                        while ($chapterRow = $chapterResult->fetch_assoc()) {
                                            $chapterTable = $chapterRow['Chapter'];

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

                                            if ($chapterTableResult && $chapterTableResult->num_rows > 0) {
                                                // Chapter table exists, delete it
                                                $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 json_encode(array('status' => 'error', 'message' => 'No data found in Subject_Category2 column in table ' . $departmentTable));
                        }

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

            // Step 5: After deleting Department2 and Subject_Category2 tables, delete the year table
            $deleteYearTableQuery = "DROP TABLE `$years`";
            if ($conn->query($deleteYearTableQuery) === TRUE) {
                echo "Year table '$years' deleted successfully.<br>";

                // Step 6: Now delete the row from the course table where the year was found
                $deleteYearFromCourseQuery = "DELETE FROM `$course` WHERE `Table_Name` = '$years'";
                if ($conn->query($deleteYearFromCourseQuery) === TRUE) {
                    echo json_encode(array('status' => 'success', 'message' => 'Year table and associated data deleted, and year removed from the course table.'));
                } else {
                    echo json_encode(array('status' => 'error', 'message' => 'Failed to delete year from the course table: ' . $conn->error));
                }
            } else {
                echo json_encode(array('status' => 'error', 'message' => 'Failed to delete the year table: ' . $conn->error));
            }
        } else {
            echo json_encode(array('status' => 'error', 'message' => 'Year table does not exist on the server.'));
        }
    } else {
        // Year not found in the course table
        echo json_encode(array('status' => 'error', 'message' => 'Year not found in the selected course.'));
    }
} else {
    // Course table not found
    echo json_encode(array('status' => 'error', 'message' => 'Course table not found.'));
}

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