<?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"] == "GET") {
    // Fetch data from Whice_Class table
    $sql = "SELECT Class FROM Whice_Class";
    $result = $conn->query($sql);

    $data = array();
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }
    }

    echo json_encode($data);

} elseif ($_SERVER["REQUEST_METHOD"] == "POST") {
    $className = $_POST['className'];

    // Fetch table names from the Table_Name column in the class table
    $sql = "SELECT Table_Name FROM `$className`";
    $result = $conn->query($sql);

    $tablesToDelete = array();
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $tablesToDelete[] = trim($row['Table_Name']); // Trim whitespace
        }
    }

    // Process each table from Table_Name column
    foreach ($tablesToDelete as $table) {
        // Fetch Department2 data from the current table
        $sql = "SELECT Department2 FROM `$table`";
        $result = $conn->query($sql);

        $department2Tables = array();
        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $department2Tables[] = trim($row['Department2']); // Collect and trim Department2 values
            }
        }

        // Output the Department2 data (for debugging)
        echo "Department2 data from table '$table':\n";
        print_r($department2Tables);

        // For each Department2 value, fetch Subject_Category2 and check if the table exists before deleting
        foreach ($department2Tables as $departmentTable) {
            // Before deleting the Department2 table, fetch Subject_Category2 data
            $sql = "SELECT Subject_Category2 FROM `$departmentTable`";
            $result = $conn->query($sql);

            $subjectCategory2Tables = array();
            if ($result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {
                    $subjectCategory2Tables[] = trim($row['Subject_Category2']); // Collect and trim Subject_Category2 values
                }
            }

            // Output the Subject_Category2 data (for debugging)
            echo "Subject_Category2 data from table '$departmentTable':\n";
            print_r($subjectCategory2Tables);

            // Before deleting $subjectCategory2Tables, process the Chapter column
            foreach ($subjectCategory2Tables as $subjectTable) {
                // Fetch Chapter column data from each table in $subjectCategory2Tables
                $sql = "SELECT Chapter FROM `$subjectTable`";
                $result = $conn->query($sql);

                $chapterTables = array();
                if ($result->num_rows > 0) {
                    while ($row = $result->fetch_assoc()) {
                        $chapterTables[] = trim($row['Chapter']); // Collect and trim Chapter values
                    }
                }

                // Check if tables corresponding to Chapter values exist and delete them
                foreach ($chapterTables as $chapterTable) {
                    // Sanitize and check if the table exists before trying to delete it
                    $cleanedChapterTableName = $conn->real_escape_string($chapterTable);
                    $checkChapterTableSql = "SHOW TABLES LIKE '$cleanedChapterTableName'";
                    $checkChapterResult = $conn->query($checkChapterTableSql);

                    if ($checkChapterResult->num_rows > 0) { // If table exists
                        // Delete the table
                        $sql = "DROP TABLE IF EXISTS `$cleanedChapterTableName`";
                        if ($conn->query($sql) === TRUE) {
                            echo "Table '$cleanedChapterTableName' (Chapter) deleted successfully.\n";
                        } else {
                            echo "Error deleting table '$cleanedChapterTableName': " . $conn->error . "\n";
                        }
                    } else {
                        echo "Table '$cleanedChapterTableName' (Chapter) does not exist.\n";
                    }
                }

                // Now delete the $subjectTable itself
                $cleanedTableName = $conn->real_escape_string($subjectTable);
                $checkTableSql = "SHOW TABLES LIKE '$cleanedTableName'";
                $checkResult = $conn->query($checkTableSql);

                if ($checkResult->num_rows > 0) { // If table exists
                    $sql = "DROP TABLE IF EXISTS `$cleanedTableName`";
                    if ($conn->query($sql) === TRUE) {
                        echo "Table '$cleanedTableName' deleted successfully.\n";
                    } else {
                        echo "Error deleting table '$cleanedTableName': " . $conn->error . "\n";
                    }
                } else {
                    echo "Table '$cleanedTableName' does not exist.\n";
                }
            }

            // Drop the Department2 table itself
            $sql = "DROP TABLE IF EXISTS `$departmentTable`";
            if ($conn->query($sql) === TRUE) {
                echo "Table '$departmentTable' deleted successfully.\n";
            } else {
                echo "Error deleting table '$departmentTable': " . $conn->error . "\n";
            }
        }

        // Drop the current table itself
        $sql = "DROP TABLE IF EXISTS `$table`";
        if ($conn->query($sql) === TRUE) {
            echo "Table '$table' deleted successfully.\n";
        } else {
            echo "Error deleting table '$table': " . $conn->error . "\n";
        }
    }

    // Delete the Class from Whice_Class table
    $sql = "DELETE FROM Whice_Class WHERE Class = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("s", $className);

    if ($stmt->execute()) {
        echo "Record deleted successfully from Whice_Class table.\n";
    } else {
        echo "Error deleting record from Whice_Class table: " . $conn->error . "\n";
    }

    $stmt->close();

    // Drop the table named after the class itself
    $sql = "DROP TABLE IF EXISTS `$className`";
    if ($conn->query($sql) === TRUE) {
        echo "Table '$className' deleted successfully.\n";
    } else {
        echo "Error deleting table '$className': " . $conn->error . "\n";
    }
}

$conn->close();
?>
