<?php
// Check if the 'TYPE' parameter is set in the POST request
if(isset($_POST['TYPE'])){
    // Process based on the value of 'TYPE'
    switch($_POST['TYPE']){
        case 'user':
            // Include the file containing database connection details
            include('dbopen.php');
            
            // Set a default value for the user ID
            $user = '99912312~~~';
            
            // Validate and sanitize the user ID received via GET request
            if(isset($_GET['user']) && strlen($_GET['user']) > 10){
                $user = $_GET['user'];
            }
            
            // Prepare and execute the SQL query
            $query = "SELECT * FROM members WHERE memberid=?";
            $stmt = mysqli_prepare($conn, $query);
            
            // Bind the parameter and execute the statement
            mysqli_stmt_bind_param($stmt, "s", $user);
            mysqli_stmt_execute($stmt);
            
            // Get the result set
            $result = mysqli_stmt_get_result($stmt);
            $data = array();
            
            // Check if there are any rows returned
            if (mysqli_num_rows($result) > 0) {
                // Loop through the result set and add each row to the data array
                while ($row = mysqli_fetch_assoc($result)) {
                    $data[] = $row;
                }
            }
            
            // Encode the data as JSON
            $json_data = json_encode($data);
            
            // Close the statement and database connection
            mysqli_stmt_close($stmt);
            mysqli_close($conn);
            
            // Output the JSON data
            header('Content-Type: application/json');
            echo $json_data;
            break;
        case 'establishments':
            // Include the file containing database connection details
            include('dbopen.php');
 
            
            // Prepare and execute the SQL query
            $query = "SELECT JSON_OBJECT( 'establishments', JSON_ARRAYAGG( JSON_OBJECT( 'category', e.category, 'establishment_type', JSON_OBJECT('id', et.esTypeid, 'name', et.establishment_type) ) ) ) AS establishment_json FROM (SELECT DISTINCT category FROM establishments) e JOIN establishments et ON e.category = et.category;";
            $stmt = mysqli_prepare($conn, $query);
            
            // Bind the parameter and execute the statement
            //mysqli_stmt_bind_param($stmt, "s", $user);
            mysqli_stmt_execute($stmt);
            
            // Get the result set
            $result = mysqli_stmt_get_result($stmt);
            $data = array();
            
            // Check if there are any rows returned
            if (mysqli_num_rows($result) > 0) {
                // Loop through the result set and add each row to the data array
                while ($row = mysqli_fetch_assoc($result)) {
                    $data[] = $row;
                }
            }
            
            // Encode the data as JSON
            $json_data = json_encode($data);
            
            // Close the statement and database connection
            mysqli_stmt_close($stmt);
            mysqli_close($conn);
            
            // Output the JSON data
            header('Content-Type: application/json');
            echo $json_data;
            break;
         
              
        case 'discounts':
            // Include the file containing database connection details
            include('dbopen.php');
 
            $search = "";
$location = "";
$category = "";

if(isset($_GET['search'])){
    $search = $_GET['search'];
}
if(isset($_GET['location'])){
    $location = $_GET['location'];
}
if(isset($_GET['category'])){
    $category = $_GET['category'];
}

// Prepare and execute the SQL query
$query = "SELECT 
    CONCAT(
        CASE WHEN stores.country IS NOT NULL THEN CONCAT(stores.country, ' - ') ELSE '' END,
        CASE WHEN stores.province IS NOT NULL THEN CONCAT(stores.province, ' - ') ELSE '' END,
        CASE WHEN stores.city IS NOT NULL THEN stores.city ELSE '' END
    ) AS location,
    CONCAT(
        '<b>',stores.name,'</b>', 
        CASE WHEN stores.branch_name IS NOT NULL THEN CONCAT('-', stores.branch_name) ELSE '' END,
        ' [',
        establishments.establishment_type,
        '] ',
        CASE 
            WHEN EXISTS (
                SELECT 1
                FROM discount
                WHERE stores.storeid = discount.storeid
            ) 
            THEN (
                SELECT 
                    GROUP_CONCAT(CONCAT('<br/><span class=\"badge bg-primary\">', discount.description,'</span>') SEPARATOR '')             
                FROM discount
                WHERE stores.storeid = discount.storeid
            ) 
            ELSE '' 
        END
    ) AS store_name_with_branch
FROM  
    stores
LEFT OUTER JOIN 
    establishments ON stores.estypeid = establishments.esTypeid
WHERE 
    (
        CONCAT(
            CASE WHEN stores.country IS NOT NULL THEN CONCAT(stores.country, ' - ') ELSE '' END,
            CASE WHEN stores.province IS NOT NULL THEN CONCAT(stores.province, ' - ') ELSE '' END,
            CASE WHEN stores.city IS NOT NULL THEN stores.city ELSE '' END
        ) LIKE CONCAT('%', ?, '%')
        OR
        CONCAT(
            '<b>',stores.name,'</b>', 
            CASE WHEN stores.branch_name IS NOT NULL THEN CONCAT('-', stores.branch_name) ELSE '' END,
            ' [',
            establishments.establishment_type,
            '] ',
            CASE 
                WHEN EXISTS (
                    SELECT 1
                    FROM discount
                    WHERE stores.storeid = discount.storeid
                ) 
                THEN (
                    SELECT 
                        GROUP_CONCAT(CONCAT('<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span class=\"badge bg-primary\">', discount.description,'</span>') SEPARATOR '')             
                    FROM discount
                    WHERE stores.storeid = discount.storeid
                ) 
                ELSE '' 
            END
        ) LIKE CONCAT('%', ?, '%')
        
    )
    AND
    CONCAT(
        CASE WHEN stores.country IS NOT NULL THEN CONCAT(stores.country, ' - ') ELSE '' END,
        CASE WHEN stores.province IS NOT NULL THEN CONCAT(stores.province, ' - ') ELSE '' END,
        CASE WHEN stores.city IS NOT NULL THEN stores.city ELSE '' END
    ) LIKE CONCAT('%', ?, '%')
    AND
    establishments.establishment_type LIKE CONCAT('%', ?, '%')";

$stmt = mysqli_prepare($conn, $query);

// Bind the parameters and execute the statement
mysqli_stmt_bind_param($stmt, "ssss", $search, $search, $location, $category);
mysqli_stmt_execute($stmt);

// Get the result set
$result = mysqli_stmt_get_result($stmt);
$data = array();

// Check if there are any rows returned
if (mysqli_num_rows($result) > 0) {
    // Loop through the result set and add each row to the data array
    while ($row = mysqli_fetch_assoc($result)) {
        $data[] = $row;
    }
}

// Encode the data as JSON
$json_data = json_encode($data);

// Close the statement and database connection
mysqli_stmt_close($stmt);
mysqli_close($conn);

            
            // Output the JSON data
            header('Content-Type: application/json');
            echo $json_data;
            break;
        
        case 'register':

            if (
                isset($_POST['firstName']) && !empty($_POST['firstName']) &&
                isset($_POST['middleName']) && !empty($_POST['middleName']) &&
                isset($_POST['lastName']) && !empty($_POST['lastName']) &&
                isset($_POST['mobileNumber']) && !empty($_POST['mobileNumber']) &&
                isset($_POST['email']) && !empty($_POST['email']) &&
                isset($_POST['country']) && !empty($_POST['country']) &&
                isset($_POST['cityMunicipality']) && !empty($_POST['cityMunicipality']) &&
                isset($_POST['region']) && !empty($_POST['region']) &&
                isset($_POST['club']) && !empty($_POST['club'])
            ) {
                // All required fields are provided
                $firstName = $_POST['firstName'];
                $middleName = $_POST['middleName'];
                $lastName = $_POST['lastName'];
                $mobileNumber = $_POST['mobileNumber'];
                $email = $_POST['email'];
                $country = $_POST['country'];
                $cityMunicipality = $_POST['cityMunicipality'];
                $region = $_POST['region'];
                $club = $_POST['club'];
            
                // Process the form data further as needed
                // For now, let's just display the submitted data
                
            } else {
                // Display an error message if any required field is missing
                header("Location: {$_SERVER['HTTP_REFERER']}");
                die("");
            }
            
            // Include the file containing database connection details
            include('dbopen.php');
            
            $id = uniqid();
            // Prepare and execute the SQL query
            $query = "INSERT INTO members (memberId,FirstName, Mname, Lname, MobileNumber, Email, Country, CityMunicipality, Region, Club) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?, ?)";
            $stmt = mysqli_prepare($conn, $query);
            
            if (!$stmt) {
                die("Error: " . mysqli_error($conn));
            }
            
            // Bind the parameter and execute the statement
            mysqli_stmt_bind_param($stmt, "ssssssssss",$id, $firstName, $middleName, $lastName, $mobileNumber, $email, $country, $cityMunicipality, $region, $club);
            if (!mysqli_stmt_execute($stmt)) {
                die("Error: " . mysqli_error($conn));
            }
            
            // Get the last inserted ID
            //$id = mysqli_insert_id($conn);
            
            // Close the statement and database connection
            mysqli_stmt_close($stmt);
            mysqli_close($conn);
            
            // Output the QR code
            echo "<h2>Form Submitted Successfully!</h2>";
            echo "<p>First Name: $firstName</p>";
            echo "<p>Middle Name: $middleName</p>";
            echo "<p>Last Name: $lastName</p>";
            echo "<p>Mobile Number: $mobileNumber</p>";
            echo "<p>Email: $email</p>";
            echo "<p>Country: $country</p>";
            echo "<p>City/Municipality: $cityMunicipality</p>";
            echo "<p>Region: $region</p>";
            echo "<p>Club: $club</p>";
            echo '<img src="https://chart.googleapis.com/chart?chs=320x320&cht=qr&chl=' . $id . '.dokimark.com/eagles">';
            break;            
        default:
            // Handle invalid 'TYPE' parameter with a 500 Internal Server Error
            http_response_code(500);
            echo json_encode(array("error" => "An unspecified internal server error occurred."));
            break;
    }
} else {
    // Handle missing 'TYPE' parameter with a 404 Not Found error
    http_response_code(404);
    echo json_encode(array("error" => "The page you are looking for could not be found."));
}
?>
