<?php
//session_start(); 
//Start the session at the beginning of the file

// Database connection
include_once 'connect.php';

// Function to fetch options for dropdowns
function fetch_options($query, $column_name, $default_text, $selected_value = '') {
    global $conn;
    $result = $conn->query($query);
    $options = "<option value=''>$default_text</option>";
    while ($row = $result->fetch_assoc()) {
        $selected = ($row['id'] == $selected_value) ? 'selected' : '';
        $options .= "<option value='" . $row['id'] . "' $selected>" . $row[$column_name] . "</option>";
    }
    return $options;
}

// Fetch all districts
$district_selected = isset($_GET['district']) ? $_GET['district'] : '';
$districts_options = fetch_options("SELECT district_id AS id, district_name FROM districts WHERE status = 1 ORDER BY district_name ASC", 'district_name', "All Districts", $district_selected);

// Fetch other options for dropdowns
$tehsil_selected = isset($_GET['tehsil']) ? $_GET['tehsil'] : '';
$constituency_selected = isset($_GET['constituency']) ? $_GET['constituency'] : '';
$uc_selected = isset($_GET['uc']) ? $_GET['uc'] : '';
$school_level_selected = isset($_GET['school_level']) ? $_GET['school_level'] : '';
$school_category_selected = isset($_GET['school_category']) ? $_GET['school_category'] : '';

$tehsils_options = $district_selected ? fetch_options("SELECT tehsil_id AS id, tehsil_name FROM tehsils WHERE district_id = '$district_selected' ORDER BY tehsil_name ASC", 'tehsil_name', "All Tehsils", $tehsil_selected) : "<option value=''>All Tehsils</option>";

$constituencies_options = $tehsil_selected ? fetch_options("SELECT cons_id AS id, cons_name FROM constituency WHERE tehsil_id = '$tehsil_selected' ORDER BY cons_name ASC", 'cons_name', "All Constituencies", $constituency_selected) : "<option value=''>All Constituencies</option>";

$ucs_options = $constituency_selected ? fetch_options("SELECT uc_id AS id, uc_name FROM union_councils WHERE cons_id = '$constituency_selected' ORDER BY uc_name ASC", 'uc_name', "All Union Councils", $uc_selected) : "<option value=''>All Union Councils</option>";

$school_levels_options = $uc_selected ? fetch_options("SELECT level_id AS id, level_name FROM school_level ORDER BY level_name ASC", 'level_name', "All School Levels", $school_level_selected) : "<option value=''>All School Levels</option>";

// Initialize school categories options
$school_categories_options = "<option value=''>All School Categories</option>";

if ($school_level_selected) {
    // Fetch school categories based on selected school level (no level_id in school_cate)
    $categories_query = "SELECT cate_id AS id, school_type FROM school_cate ORDER BY school_type ASC";
    $school_categories_options = fetch_options($categories_query, 'school_type', 'All School Categories', $school_category_selected);
}

// Check if search button was clicked
$search_performed = isset($_GET['search']);
$search_emis_code = isset($_GET['emis_code']) ? $_GET['emis_code'] : '';

if ($search_performed) {
    $_SESSION['search_performed'] = true;
} else {
    $_SESSION['search_performed'] = false;
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Dropdown Lists</title>
    <style>
        .form-row {
            display: flex;
            flex-wrap: wrap;
            gap: 10px;
            align-items: center;
            justify-content: center;
        }
        select, .form-control, .btn1 {
            width: 150px;
            padding: 5px;
            box-sizing: border-box;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        th, td {
            border: 1px solid black;
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2;
        }
    </style>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
</head>
<body>
<div>
    <form id="dropdown-form" class="form-row" method="GET" action="">
        <select id="district" name="district"><?php echo $districts_options; ?></select>
        <select id="tehsil" name="tehsil"><?php echo $tehsils_options; ?></select>
        <select id="constituency" name="constituency"><?php echo $constituencies_options; ?></select>
        <select id="uc" name="uc"><?php echo $ucs_options; ?></select>
        <select id="school_level" name="school_level"><?php echo $school_levels_options; ?></select>
        <select id="school_category" name="school_category"><?php echo $school_categories_options; ?></select>
        <strong>OR</strong>
        <input type="text" class="form-control" id="emis_code" name="emis_code" placeholder="Enter EMIS Code" value="<?php echo htmlspecialchars($search_emis_code); ?>">
        <button type="submit" name="search" class="btn1 btn-success">Search</button>
    </form>
</div>

<?php
if ($search_performed) {
    // Get the search criteria from the form
    $district = $district_selected;
    $tehsil = $tehsil_selected;
    $constituency = $constituency_selected;
    $uc = $uc_selected;
    $school_level = $school_level_selected;
    $school_category = $school_category_selected;
    $emis_code = $search_emis_code;

    // Construct the SQL query based on the criteria
    $query = "
        SELECT s.school_name, s.emis_code, d.district_name, t.tehsil_name, c.cons_name, u.uc_name, sl.level_name, sc.school_type, s.contact_no, s.status
        FROM schools s
        LEFT JOIN districts d ON s.district_id = d.district_id
        LEFT JOIN tehsils t ON s.tehsil_id = t.tehsil_id
        LEFT JOIN constituency c ON s.cons_id = c.cons_id
        LEFT JOIN union_councils u ON s.uc_id = u.uc_id
        LEFT JOIN school_level sl ON s.level_id = sl.level_id
        LEFT JOIN school_cate sc ON s.cate_id = sc.cate_id
        WHERE 1 = 1";

    if ($emis_code != '') {
        // If EMIS code is provided, only search by EMIS code
        $query .= " AND s.emis_code = '$emis_code'";
    } else {
        // Apply other filters if EMIS code is not provided
        if ($district != '') {
            $query .= " AND s.district_id = '$district'";
        }
        if ($tehsil != '') {
            $query .= " AND s.tehsil_id = '$tehsil'";
        }
        if ($constituency != '') {
            $query .= " AND s.cons_id = '$constituency'";
        }
        if ($uc != '') {
            $query .= " AND s.uc_id = '$uc'";
        }
        if ($school_level != '') {
            $query .= " AND s.level_id = '$school_level'";
        }
        if ($school_category != '') {
            $query .= " AND s.cate_id = '$school_category'";
        }
    }

    // Execute the query
    $result = $conn->query($query);

    if ($result->num_rows > 0) {
        echo "<table>
            <tr>
                <th>S.No</th>
                <th>School Name</th>
                <th>EMIS Code</th>
                <th>District</th>
                <th>Tehsil</th>
                <th>Constituency</th>
                <th>Union Council</th>
                <th>Level</th>
                <th>Category</th>
                <th>Contact</th>
                <th>Status</th>
            </tr>";

        $serial_no = 1; // Initialize the serial number counter
        while ($row = $result->fetch_assoc()) {
            echo "<tr>
                <td>{$serial_no}</td>
                <td>" . htmlspecialchars($row['school_name']) . "</td>
                <td>" . htmlspecialchars($row['emis_code']) . "</td>
                <td>" . htmlspecialchars($row['district_name']) . "</td>
                <td>" . htmlspecialchars($row['tehsil_name']) . "</td>
                <td>" . htmlspecialchars($row['cons_name']) . "</td>
                <td>" . htmlspecialchars($row['uc_name']) . "</td>
                <td>" . htmlspecialchars($row['level_name']) . "</td>
                <td>" . htmlspecialchars($row['school_type']) . "</td>
                <td>" . htmlspecialchars($row['contact_no']) . "</td>
                <td>" . htmlspecialchars($row['status']) . "</td>
            </tr>";
            $serial_no++; // Increment the serial number counter
        }
        echo "</table>";
    } else {
        echo "<p style='text-align: center;'>No Results Found</p>";
    }
}
?>
<script>
$(document).ready(function() {
    $('#district').change(function() {
        var district_id = $(this).val();
        if (district_id != '') {
            $.ajax({
                type: 'POST',
                url: 'fetch_tehsils.php',
                data: { district_id: district_id },
                success: function(response) {
                    $('#tehsil').html(response);
                    $('#constituency').html('<option value="">All Constituencies</option>');
                    $('#uc').html('<option value="">All Union Councils</option>');
                    $('#school_level').html('<option value="">All School Levels</option>');
                    $('#school_category').html('<option value="">All School Categories</option>');
                }
            });
        } else {
            $('#tehsil').html('<option value="">All Tehsils</option>');
            $('#constituency').html('<option value="">All Constituencies</option>');
            $('#uc').html('<option value="">All Union Councils</option>');
            $('#school_level').html('<option value="">All School Levels</option>');
            $('#school_category').html('<option value="">All School Categories</option>');
        }
    });

    $('#tehsil').change(function() {
        var tehsil_id = $(this).val();
        if (tehsil_id != '') {
            $.ajax({
                type: 'POST',
                url: 'fetch_constituencies.php',
                data: { tehsil_id: tehsil_id },
                success: function(response) {
                    $('#constituency').html(response);
                    $('#uc').html('<option value="">All Union Councils</option>');
                    $('#school_level').html('<option value="">All School Levels</option>');
                    $('#school_category').html('<option value="">All School Categories</option>');
                }
            });
        } else {
            $('#constituency').html('<option value="">All Constituencies</option>');
            $('#uc').html('<option value="">All Union Councils</option>');
            $('#school_level').html('<option value="">All School Levels</option>');
            $('#school_category').html('<option value="">All School Categories</option>');
        }
    });

    $('#constituency').change(function() {
        var cons_id = $(this).val();
        if (cons_id != '') {
            $.ajax({
                type: 'POST',
                url: 'fetch_ucs.php',
                data: { cons_id: cons_id },
                success: function(response) {
                    $('#uc').html(response);
                    $('#school_level').html('<option value="">All School Levels</option>');
                    $('#school_category').html('<option value="">All School Categories</option>');
                }
            });
        } else {
            $('#uc').html('<option value="">All Union Councils</option>');
            $('#school_level').html('<option value="">All School Levels</option>');
            $('#school_category').html('<option value="">All School Categories</option>');
        }
    });

    $('#uc').change(function() {
        var uc_id = $(this).val();
        if (uc_id != '') {
            $.ajax({
                type: 'POST',
                url: 'fetch_school_levels.php',
                data: { uc_id: uc_id },
                success: function(response) {
                    $('#school_level').html(response);
                    $('#school_category').html('<option value="">All School Categories</option>');
                }
            });
        } else {
            $('#school_level').html('<option value="">All School Levels</option>');
            $('#school_category').html('<option value="">All School Categories</option>');
        }
    });

    $('#school_level').change(function() {
        var level_id = $(this).val();
        if (level_id != '') {
            $.ajax({
                type: 'POST',
                url: 'fetch_school_categories.php',
                data: { level_id: level_id },
                success: function(response) {
                    $('#school_category').html(response);
                }
            });
        } else {
            $('#school_category').html('<option value="">All School Categories</option>');
        }
    });

    // Reset dropdowns and clear EMIS code if EMIS code is provided
    <?php if ($search_emis_code): ?>
        $('#district').val('');
        $('#tehsil').html('<option value="">All Tehsils</option>');
        $('#constituency').html('<option value="">All Constituencies</option>');
        $('#uc').html('<option value="">All Union Councils</option>');
        $('#school_level').html('<option value="">All School Levels</option>');
        $('#school_category').html('<option value="">All School Categories</option>');
        $('#emis_code').val('');
    <?php endif; ?>
});
</script>
</body>
</html>
