<?php
header('Access-Control-Allow-Origin: *');
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: POST");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");

include "config.php";
include "functions.php";

use \Firebase\JWT\JWT;
$postjson = json_decode(file_get_contents('php://input'), true);

error_reporting(E_ALL);
ini_set('display_errors', 1);

// Load environment variables
require __DIR__ . '/vendor/autoload.php';
use Dotenv\Dotenv;

$dotenv = Dotenv::createImmutable(__DIR__);
$dotenv->load();

// Retrieve the secret key from the environment variables
$secret_key = $_ENV['SECRET_KEY'];

try {
    date_default_timezone_set('Asia/Amman');
    $pdo = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME, DB_USER, DB_PASSWORD);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $pdo->exec('SET time_zone = "+03:00"');
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
    die("Error: " . $e->getMessage());
}

function radians($degrees) {
    return $degrees * (pi() / 180.0);
}




// if ($postjson["method"] == "editUserProfile") {
//     try{
//         $requiredFields = ['userId', 'userFirstName', 'userPassword', 'userMobile', 'userEmail'];
//         foreach ($requiredFields as $field) {
//             if (empty($postjson[$field])) {
//                 http_response_code(400); // Bad Request
//                 echo json_encode(array('success' => false, 'message' => $field . ' is required.'));
//                 exit;
//             }
//         }
//         $hashedPassword = password_hash($postjson['userPassword'], PASSWORD_DEFAULT);

//         $stmtProfile = $pdo->prepare("UPDATE users SET userFirstName = '$postjson[userFirstName]', userPassword = '$hashedPassword', userMobile = '$postjson[userMobile]', userEmail = '$postjson[userEmail]' WHERE userId = $postjson[userId]");


//     } catch (PDOException $e) {
//         http_response_code(500); // Internal Server Error
//         echo json_encode(array('success' => false, 'message' => 'Database error.'. $e->getMessage()));
//     }

// }

if ($postjson["method"] == "orderPriceForOpenCounterDraft") { //calculate the total price for the order of sub specs that have distance
// Validate input fields
$requiredFields = ['subSpecId', 'reqDistance','subSpecPrice'];
foreach ($requiredFields as $field) {
    if (empty($postjson[$field])) {
        http_response_code(400); // Bad Request
        echo json_encode(array('success' => false, 'message' => $field . ' is required.'));
        exit;
    }
}
    try{
        //Get the min distance AND open Counter for the provided sub spec
        $stmtMinDistanceAndOpenCounter = $pdo->prepare("SELECT distanceMin, openCounter FROM subSpecialties WHERE subSpecId = :subSpecId");
        $stmtMinDistanceAndOpenCounter->bindParam(':subSpecId', $postjson['subSpecId']);
        $stmtMinDistanceAndOpenCounter->execute();
        $minDistanceAndOpenCounter = $stmtMinDistanceAndOpenCounter->fetch(PDO::FETCH_ASSOC);

        

        //calculate the total price of the order based on the open counter, min distance, distance from app, sub Spec, sub spec price
        //final price = ((distance from app - min distance) * sub spec price) + open counter
        // ex = ( ( 10.45-15 ) * 1.15 ) + 15 
        // echo "final price".$finalReqPrice;
        
        if($postjson['reqDistance']<=$minDistanceAndOpenCounter['distanceMin']){
            $finalReqPrice=$minDistanceAndOpenCounter['openCounter'];
        }else{
            $finalReqPrice = (($postjson['reqDistance'] - $minDistanceAndOpenCounter['distanceMin']) * $postjson['subSpecPrice']) + $minDistanceAndOpenCounter['openCounter'];
        }

        if ($minDistanceAndOpenCounter) {
            http_response_code(200);
            echo json_encode(array('success' => true, 'finalReqPrice' => $finalReqPrice));
        } else {
            http_response_code(400);
            echo json_encode(array('success' => false, 'message' => "Error in fetching data"));
        }


    } catch (PDOException $e) {
        http_response_code(500); // Internal Server Error
        echo json_encode(array('success' => false, 'message' => 'Database error.' . $e->getMessage()));
    }

}


//testing max and min rang for cat 2,3
if ($postjson["method"] == "orderPriceForOpenCounter") { //calculate the total price for the order of sub specs that have distance
    // Validate input fields
$requiredFields = ['subSpecId', 'reqDistance'];
foreach ($requiredFields as $field) {
    if (empty($postjson[$field])) {
        http_response_code(400); // Bad Request
        echo json_encode(array('success' => false, 'message' => $field . ' is required.'));
        exit;
    }
}
   
        try{
                        $sql = "SELECT minDistance, maxDistance, price AS pricePerKm 
                        FROM specUnitsPrice 
                        WHERE subSpecId = :subSpecId";

                // Execute the SQL query using PDO or any other MySQL database library
                // Replace `YourDBConnection` with your database connection object or instance
                $stmt = $pdo->prepare($sql);
                $stmt->bindParam(':subSpecId', $postjson["subSpecId"]);
                $stmt->execute();

                // Fetch the data from the database
                $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

                // Construct the $priceRanges array based on the fetched data
                $priceRanges = [];
                foreach ($rows as $row) {
                    $priceRanges[] = [
                        'min' => $row['minDistance'],
                        'max' => $row['maxDistance'],
                        'pricePerKm' => $row['pricePerKm']
                    ];
                }

                $distance = $postjson["reqDistance"];

                 //Get the min distance AND open Counter for the provided sub spec
        $stmtMinDistanceAndOpenCounter = $pdo->prepare("SELECT distanceMin, openCounter FROM subSpecialties WHERE subSpecId = :subSpecId");
        $stmtMinDistanceAndOpenCounter->bindParam(':subSpecId', $postjson['subSpecId']);
        $stmtMinDistanceAndOpenCounter->execute();
        $minDistanceAndOpenCounter = $stmtMinDistanceAndOpenCounter->fetch(PDO::FETCH_ASSOC);

         //service fee
         $stmtServiceFee=$pdo->prepare("SELECT serviceFee from subSpecialties WHERE subSpecId = ?");
         $stmtServiceFee->execute([$postjson['subSpecId']]);
         $serviceFee=$stmtServiceFee->fetchColumn();

        //calculate the total price of the order based on the open counter, min distance, distance from app, sub Spec, sub spec price
        //final price = ((distance from app - min distance) * sub spec price) + open counter
        // ex = ( ( 10.45-15 ) * 1.15 ) + 15 
        // echo "final price".$finalReqPrice;

        $finalPrice=0;
        if($postjson['reqDistance']<=$minDistanceAndOpenCounter['distanceMin']){
            $finalPrice=$minDistanceAndOpenCounter['openCounter'] + $serviceFee;
        }else{
            // $finalReqPrice = (($postjson['reqDistance'] - $minDistanceAndOpenCounter['distanceMin']) * $postjson['subSpecPrice']) + $minDistanceAndOpenCounter['openCounter'];
             // Calculate the price for each range and sum them up
             foreach ($priceRanges as $range) {
                $rangeDistance = min($range['max'], $distance) - max($range['min'], 0); // Calculate the distance within the range
                if ($rangeDistance > 0) {
                    $finalPrice += $rangeDistance * $range['pricePerKm']; // Add the price for this range
                }
            }
            $finalPrice = $finalPrice + $minDistanceAndOpenCounter['openCounter'] + $serviceFee;
        }

            

                // Initialize the final price
                // $finalPrice = 0;

             

                // Output the final price
                // echo "Final Price: $" . number_format($finalPrice, 2) + 20 ;
                // echo "///".$distance."///";
                // echo "///".$priceRanges[0]['max']."///";
                // echo "///".$priceRanges[0]['min']."///";


                if ($finalPrice !=0) {
                    http_response_code(200);
                    echo json_encode(array('success' => true, 'finalReqPrice' => $finalPrice));
                } else {
                    http_response_code(400);
                    echo json_encode(array('success' => false, 'message' => "Error in fetching data"));
                }


            
            // totalDistance = 352 (from app)
            // open counter 15 
            // rang 1 15 - 100 ->      1 k = 1.00
            // rang 2 100 - 200 ->     1 k = 0.80
            // rang 3 200 - 300 ->     1 k = 0.75
            // rang 4 300 - 1000 ->    1 k = 0.60

            // if totalDistance < 100 ==> (totalDistance - open counter) * 1.00 
            //150 if totalDistance > 100 && <=200 ==> ((totalDistance - (totalDistance - 100) - open counter) * rang 1 price) + (totalDistance - 100) * rang 2 price

            //(((150 - (150 - 100 ) - 15) * 1 ) + (150 - 100) * 0.80) +20

            //250 if totalDistance > 200 && <=300 ==> ((totalDistance - (totalDistance - 200) - open counter) * rang 1 price) + (totalDistance - 100) * rang 2 price

          

      
    
    
        } catch (PDOException $e) {
            http_response_code(500); // Internal Server Error
            echo json_encode(array('success' => false, 'message' => 'Database error.' . $e->getMessage()));
        }
    
    }

if ($postjson["method"] == "getContactInfo") {
    try {
        $stmt = $pdo->prepare("SELECT * FROM contactInfo");
        $stmt->execute();
        $contactInfo = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $formattedContactInfo = array_map(function ($contact) {
            return array(
                "facebookUrl" => $contact["facebookUrl"],
                "facebookLogo" => $contact["facebookLogo"]
            );
        }, $contactInfo);

        foreach ($contactInfo as $contact) {
            $formattedContactInfo[] = array(
                "instagramUrl" => $contact["instagramUrl"],
                "instagramLogo" => $contact["instagramLogo"]
            );

            $formattedContactInfo[] = array(
                "snapchatUrl" => $contact["snapchatUrl"],
                "snapchatLogo" => $contact["snapchatLogo"]
            );

            $formattedContactInfo[] = array(
                "youtubeUrl" => $contact["youtubeUrl"],
                "youtubeLogo" => $contact["youtubeLogo"]
            );
            $formattedContactInfo[] = array(
                "linkedinUrl" => $contact["linkedinUrl"],
                "linkedinLogo" => $contact["linkedinLogo"]
            );
            $formattedContactInfo[] = array(
                "whatsappUrl" => $contact["whatsappUrl"],
                "whatsappLogo" => $contact["whatsappLogo"]
            );

            
        }

        if ($formattedContactInfo) {
            http_response_code(200);
            echo json_encode(array('success' => true, 'contactInfo' => $formattedContactInfo));
        } else {
            http_response_code(400);
            echo json_encode(array('success' => false, 'message' => "Error in fetching data"));
        }
    } catch (PDOException $e) {
        http_response_code(500); // Internal Server Error
        echo json_encode(array('success' => false, 'message' => 'Database error.' . $e->getMessage()));
    }
}



// if ($postjson["method"] == "getContactInfo") {

//     try{
//         $stmt=$pdo->prepare("SELECT * FROM contactInfo");
//         $stmt->execute();
//         $contactInfo = $stmt->fetchAll(PDO::FETCH_ASSOC);
//         if($contactInfo){
//         http_response_code(200);
//             echo json_encode(array('success' => true, 'contactInfo' => $contactInfo));
//         }else{
//         http_response_code(400);
//             echo json_encode(array('success' => false, 'message' => "error in fetching data"));
//         }
//     } catch (PDOException $e) {
//     http_response_code(500); // Internal Server Error
//     echo json_encode(array('success' => false, 'message' => 'Database error.'. $e->getMessage()));
// }
// }

if ($postjson["method"] == "getAllRequestsByUserId") {

    try{
        $stmtCheckUser=$pdo->prepare("SELECT userRole from users WHERE userId =?");
        $stmtCheckUser->execute([$postjson["userId"]]);
        $userRole=$stmtCheckUser->fetchColumn();
        if($userRole=="customer"){
            $stmt=$pdo->prepare("SELECT r.*, u.* , s.subSpecNameAr
            FROM requests r
            INNER JOIN users u ON u.userId = r.reqVenderId
            INNER JOIN subSpecialties s ON s.subSpecId = r.reqSubSpecId
            WHERE r.reqUserId = ? ORDER BY r.reqId DESC;
            
        ");
        $stmt->execute([$postjson['userId']]);
        $requests = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $count=$stmt->rowCount();
        if($count>0){
            echo json_encode(array('success' => true, 'requests' => $requests ));
        }else{
            echo json_encode(array('success' => false, 'message' => 'no requests for this user'));

        }
        }else{
            echo json_encode(array('success' => false, 'message' => 'invalid user'));
        }

        

    } catch (PDOException $e) {
        http_response_code(500); // Internal Server Error
        echo json_encode(array('success' => false, 'message' => 'Database error.'. $e->getMessage()));
    }
}

if ($postjson["method"] == "addNewRequest") {
    // Validate input fields
    $requiredFields = ['reqUserId', 'reqVenderId', 'reqSubSpecId', 'reqFrom', 'reqTo', 'reqUnit', 'reqPrice', 'reqTotalPrice'];
    foreach ($requiredFields as $field) {
        if (empty($postjson[$field])) {
            http_response_code(400); // Bad Request
            echo json_encode(array('success' => false, 'message' => $field . ' is required.'));
            exit;
        }
    }

    try {
        // Check if the requested user, vendor, and specialty exist
        $stmtCheckUser = $pdo->prepare("SELECT COUNT(*) as count FROM users WHERE userId = ?");
        $stmtCheckUser->execute([$postjson['reqUserId']]);
        $userExists = $stmtCheckUser->fetch(PDO::FETCH_ASSOC)['count'];

        $stmtCheckVendor = $pdo->prepare("SELECT COUNT(*) as count FROM users WHERE userId = ? AND userRole = 'vendor'");
        $stmtCheckVendor->execute([$postjson['reqVenderId']]);
        $vendorExists = $stmtCheckVendor->fetch(PDO::FETCH_ASSOC)['count'];

        $stmtCheckSpec = $pdo->prepare("SELECT COUNT(*) as count FROM subSpecialties WHERE subSpecId = ?");
        $stmtCheckSpec->execute([$postjson['reqSubSpecId']]);
        $specExists = $stmtCheckSpec->fetch(PDO::FETCH_ASSOC)['count'];

        if ($userExists && $vendorExists && $specExists) {
            // Insert the new request into the requests table
            $stmt = $pdo->prepare("
                INSERT INTO requests (reqUserId, reqVenderId, reqSubSpecId, reqFrom,reqFromLat,reqFromLong, reqTo,reqToLat,reqToLong, reqUnit, reqPrice,reqQty,reqTotalPrice)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ");
            $stmt->execute([
                $postjson['reqUserId'],
                $postjson['reqVenderId'],
                $postjson['reqSubSpecId'],
                $postjson['reqFrom'],
                $postjson['reqFromLat'],
                $postjson['reqFromLong'],
                $postjson['reqTo'],
                $postjson['reqToLat'],
                $postjson['reqToLong'],
                $postjson['reqUnit'],
                $postjson['reqPrice'],
                $postjson['reqQty'],
                $postjson['reqTotalPrice']
            ]);
            // $lastRequestId = $stmt->lastInsertId();
            $lastRequestId = $pdo->lastInsertId();

                        $getCreatedAt = $pdo->prepare("
                SELECT reqDate FROM requests WHERE reqId = ?
            ");
            $getCreatedAt->execute([$lastRequestId]);

            // Fetch the result
            $result = $getCreatedAt->fetch(PDO::FETCH_ASSOC);

            // Get the reqCreatedAt value
            $reqCreatedAt = $result['reqDate'];
            

            $venderFcm=$pdo->prepare("SELECT userFcm from users WHERE userId=?");
            $venderFcm->execute([$postjson['reqVenderId']]);
            $fcm=$venderFcm->fetchColumn();
            // sendGCM("طلب جديد","لديك طلب جديد ",$fcm,"none","none",true);
            sendKeepNotification("طلب جديد","لديك طلب جديد ",$fcm,"none","none",true,true,$lastRequestId);
            
            // $fcm="ecOMQbIWQDSjYxQFhm7qJv:APA91bE96Wiu0EsPXS7tfy3Vmz9yu3Chv3DW1IWs0No3OPmWnzOQJl0RKP4-CcskfBgBJagCQnOYUra5_-5WdPrD2l9WJxwz-KB6spU5y2wRYuanO0Vv9QQQY2jKc3LlCfmloF07GnPk";
            // insertNotification("طلب جديد","لديك طلب جديد ",$postjson['reqVenderId'],$fcm,"none","none");

            // $stmt = $pdo->prepare("INSERT INTO `notifications`(`notTitle`, `notificationContent`, `notToUserId`) VALUES (?, ?, ?)");
            // $stmt->execute(array("طلب جديد","لديك طلب جديد ",$postjson['reqVenderId']));


            // Return success response
            http_response_code(200);
            echo json_encode(['success' => true, 'message' => 'Request added successfully.','reqCreatedAt'=>$reqCreatedAt,"seconds"=>60, 'requestId' => $lastRequestId]);
        } else {
            // Return failure response if user, vendor, or specialty doesn't exist
            http_response_code(401);
            echo json_encode(['success' => false, 'message' => 'Invalid user, vendor, or specialty.']);
        }
    } catch (PDOException $e) {
        // Return failure response for any database error
        http_response_code(500); // Internal Server Error
        echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
    }
}



if ($postjson["method"] == "getAllVendorsBySpecIdAndUserId") {
    try {
        $stmtCoordinates = $pdo->prepare("SELECT userLatitude, userLongitude FROM users WHERE userId = :providedUserID");
        $stmtCoordinates->bindParam(':providedUserID', $postjson['userId']);
        $stmtCoordinates->execute();
        $userCoordinates = $stmtCoordinates->fetch(PDO::FETCH_ASSOC);

        if ($userCoordinates) {
            $providedUserLatitude = $userCoordinates['userLatitude'];
            $providedUserLongitude = $userCoordinates['userLongitude'];
            $providedUserLat= $userCoordinates['userLatitude'];
            // echo $providedUserLatitude."/".$providedUserLongitude;

            // $stmtVendors = $pdo->prepare("
            //     SELECT 
            //         u.userId, 
            //         u.userRole,
            //         u.userMobile,
            //         u.userFirstName,
            //         u.userStatus,
            //         u.userCredit,
            //         CAST(u.userLatitude AS DOUBLE) AS userLatitude,
            //         CAST(u.userLongitude AS DOUBLE) AS userLongitude,
            //         u.userAddress,
            //         6371 * ACOS(
            //             COS(RADIANS(:providedUserLatitude)) * COS(RADIANS(u.userLatitude)) * COS(RADIANS(u.userLongitude) - RADIANS(:providedUserLongitude)) + 
            //             SIN(RADIANS(:providedUserLat)) * SIN(RADIANS(u.userLatitude))
            //         ) AS distance
            //     FROM 
            //         users u
            //     JOIN 
            //         vendorSpecialties vs ON u.userId = vs.vsUserId
            //     WHERE 
            //         u.userRole = 'vendor' 
            //         AND vs.subSpecId = :providedSpecId 
            //         AND u.userId != :providedUserID
            //         AND u.userStatus = 'active'
            //         HAVING
            //         distance <= 50
            // ");

                                if($postjson['subSpecId']==1 || $postjson['subSpecId']==2){

                                //if the spec is gaz or water, no need to check the credit
                                $stmtVendors = $pdo->prepare("
                                SELECT 
                                u.userId, 
                                u.userRole,
                                u.userMobile,
                                u.userFirstName,
                                u.userStatus,
                                u.userCredit,
                                CAST(u.userLatitude AS DOUBLE) AS userLatitude,
                                CAST(u.userLongitude AS DOUBLE) AS userLongitude,
                                u.userAddress,
                                6371 * ACOS(
                                    COS(RADIANS(:providedUserLatitude)) * COS(RADIANS(u.userLatitude)) * COS(RADIANS(u.userLongitude) - RADIANS(:providedUserLongitude)) + 
                                    SIN(RADIANS(:providedUserLat)) * SIN(RADIANS(u.userLatitude))
                                ) AS distance
                            FROM 
                                users u
                            JOIN 
                                vendorSpecialties vs ON u.userId = vs.vsUserId
                            LEFT JOIN
                                requests r ON u.userId = r.reqVenderId
                            WHERE 
                                u.userRole = 'vendor' 
                                AND vs.subSpecId = :providedSpecId 
                                AND u.userId != :providedUserID
                                AND u.userStatus = 'active'
                                AND (r.reqStatus IS NULL OR r.reqStatus NOT IN ('new', 'pending'))
                                AND u.userId NOT IN (SELECT reqVenderId FROM requests WHERE reqStatus IN ('new','pending'))
                                AND u.userId NOT IN (
                                    SELECT reqVenderId
                                    FROM requests
                                    WHERE reqStatus = 'rejected'
                                    AND reqUserId = :userID
                                    AND TIMESTAMPDIFF(SECOND, reqDate, NOW()) < 60
                                )
                                GROUP BY u.userId
                            HAVING
                                distance <= 3
                                ORDER BY distance asc       
                    ");
                }else{

                    $stmtVendors = $pdo->prepare("
                                SELECT 
                                u.userId, 
                                u.userRole,
                                u.userMobile,
                                u.userFirstName,
                                u.userStatus,
                                u.userCredit,
                                CAST(u.userLatitude AS DOUBLE) AS userLatitude,
                                CAST(u.userLongitude AS DOUBLE) AS userLongitude,
                                u.userAddress,
                                6371 * ACOS(
                                    COS(RADIANS(:providedUserLatitude)) * COS(RADIANS(u.userLatitude)) * COS(RADIANS(u.userLongitude) - RADIANS(:providedUserLongitude)) + 
                                    SIN(RADIANS(:providedUserLat)) * SIN(RADIANS(u.userLatitude))
                                ) AS distance
                            FROM 
                                users u
                            JOIN 
                                vendorSpecialties vs ON u.userId = vs.vsUserId
                            LEFT JOIN
                                requests r ON u.userId = r.reqVenderId
                            WHERE 
                                u.userRole = 'vendor' 
                                AND vs.subSpecId = :providedSpecId 
                                AND u.userId != :providedUserID
                                AND u.userStatus = 'active'
                                AND u.userCredit > 5
                                AND (r.reqStatus IS NULL OR r.reqStatus NOT IN ('new', 'pending'))
                                AND u.userId NOT IN (SELECT reqVenderId FROM requests WHERE reqStatus IN ('new','pending'))
                                AND u.userId NOT IN (
                                    SELECT reqVenderId
                                    FROM requests
                                    WHERE reqStatus = 'rejected'
                                    AND reqUserId = :userID
                                    AND TIMESTAMPDIFF(SECOND, reqDate, NOW()) < 60
                                )
                                GROUP BY u.userId
                            HAVING
                                distance <= 10
                                ORDER BY distance asc       
                    ");

                }
                    // AND TIMESTAMPDIFF(HOUR, reqDate, NOW()) < 24

            $stmtVendors->bindParam(':providedSpecId', $postjson['subSpecId']);
            $stmtVendors->bindParam(':providedUserID', $postjson['userId']);
            $stmtVendors->bindParam(':providedUserLatitude', $providedUserLatitude);
            $stmtVendors->bindParam(':providedUserLongitude', $providedUserLongitude);
            $stmtVendors->bindParam(':providedUserLat', $providedUserLat);
            $stmtVendors->bindParam(':userID', $postjson['userId']);

            $stmtVendors->execute();
            
            $allVendors = $stmtVendors->fetchAll(PDO::FETCH_ASSOC);

            if (!empty($allVendors)) {
                echo json_encode(array('success' => true, 'allVendors' => $allVendors));
            } else {
                echo json_encode(array('success' => false, 'message' => 'No Vendors found.'));
            }
        } else {
            echo json_encode(array('success' => false, 'message' => 'User coordinates not found.'));
        }
    } catch (PDOException $e) {
        http_response_code(500);
        echo json_encode(array('success' => false, 'message' => 'Database error.'.$e));
    }
}



if ($postjson["method"] == "getAllSpecialtiesWithCategories") {
    try {
        // Retrieve all specialties with their associated category information
        $stmt = $pdo->prepare("
            SELECT s.specId, s.specNameAr, s.catId, s.specPhoto, c.catQty, c.catName as categoryName
            FROM specialties s
            JOIN categories c ON s.catId = c.catId
            ORDER BY s.catId, s.specOrderBy
        ");
        $stmt->execute();
        $specialtiesWithCategories = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (!empty($specialtiesWithCategories)) {
            $result = [];

            foreach ($specialtiesWithCategories as $specialty) {
                $categoryName = $specialty['categoryName'];
                unset($specialty['categoryName']);

                // Check if the category exists, if not, create it
                if (!isset($result[$categoryName])) {
                    $result[$categoryName] = [
                        'specialities' => []
                    ];
                }

                // Check if there are sub-specialties for the current specialty
                $subSpecialties = getSubSpecialties($pdo, $specialty['specId']);

                // Build the sub-specialties array
                $subSpecialtiesArray = [];
                foreach ($subSpecialties as $subSpecialty) {
                    $subSpecialtiesArray[] = [
                        'subSpecialtyId' => $subSpecialty['subSpecId'],
                        'subSpecialtyName' => $subSpecialty['subSpecNameAr'],
                        'serviceFee' => $subSpecialty['serviceFee'],
                        'subSpecialityUnitPrice' => getSpecialityItem($pdo, $subSpecialty['subSpecId'])
                    ];
                }

                // Build the specialty item
                $specialtyItem = [
                    'specialtyId' => $specialty['specId'],
                    'catId' => $specialty['catId'],
                    'specialtyName' => $specialty['specNameAr'],
                    'specialtyPhoto' => $specialty['specPhoto'],
                    'subSpecialties' => $subSpecialtiesArray
                ];

                // Add the specialty to the result
                $result[$categoryName]['specialities'][] = $specialtyItem;
            }

            // Data organized by category, return it as a JSON response
            echo json_encode(array('success' => true, 'specialtiesByCategory' => $result));
        } else {
            // No data found
            http_response_code(404);
            echo json_encode(array('success' => false, 'message' => 'No specialties found with categories.'));
        }
    } catch (PDOException $e) {
        http_response_code(500); // Internal Server Error
        echo json_encode(array('success' => false, 'message' => 'Database error.'. $e->getMessage()));
    }
}

function getSubSpecialties($pdo, $specId) {
    $stmt = $pdo->prepare("
        SELECT sub.subSpecId, sub.subSpecNameAr, sub.serviceFee
        FROM subSpecialties sub
        WHERE sub.specId = :specId
    ");
    $stmt->bindParam(':specId', $specId, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getSpecialityItem($pdo, $subSpecId) {
    $stmt = $pdo->prepare("
        SELECT sp.unit as specialityUnit, sp.price as specialityPrice
        FROM specUnitsPrice sp
        WHERE sp.subSpecId = :subSpecId
    ");
    $stmt->bindParam(':subSpecId', $subSpecId, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}



// if ($postjson["method"] == "getAllSpecialtiesWithCategories") {
//     try {
//         // Retrieve all specialties with their associated category information
//         $stmt = $pdo->prepare("
//             SELECT s.specId, s.specName, s.catId, s.specPhoto,c.catQty, c.catName as categoryName
//             FROM specialties s
//             JOIN categories c ON s.catId = c.catId
//             ORDER BY s.catId
//         ");
//         $stmt->execute();
//         $specialtiesWithCategories = $stmt->fetchAll(PDO::FETCH_ASSOC);

//         $stmt1 = $pdo->prepare("
//             SELECT s.specId, s.specName, s.catId, s.specPhoto, c.catName as categoryName, 
//             sp.unit as specialityUnit, sp.price as specialityPrice
//             FROM specialties s
//             JOIN categories c ON s.catId = c.catId
//             LEFT JOIN specUnitsPrice sp ON s.specId = sp.specId
//             ORDER BY s.catId
//         ");
//         $stmt1->execute();
//         $specialtiesWithCategories1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);

//         if (!empty($specialtiesWithCategories)) {
//             $result = [];

//             foreach ($specialtiesWithCategories as $specialty) {
//                 $categoryName = $specialty['categoryName'];
//                 unset($specialty['categoryName']);

//                 // Check if the 'specialityItem' key exists, if not, create it
//                 if (!isset($result[$categoryName])) {
//                     $result[$categoryName] = [];
//                 }

//                 // Check if there are specialties with additional information
//                 if (!empty($specialtiesWithCategories1)) {
//                     // Find matching specialties with additional information
//                     foreach ($specialtiesWithCategories1 as $specialtyWithItem) {
//                         if ($specialtyWithItem['specId'] == $specialty['specId']) {
//                             $specialtyItem = [
//                                 'specialityId' => $specialtyWithItem['specId'],
//                                 'specialityUnit' => $specialtyWithItem['specialityUnit'],
//                                 'specialityPrice' => $specialtyWithItem['specialityPrice']
//                             ];

//                             // Add the 'specialityItem' to the current specialty
//                             $specialty['specialityItem'][] = $specialtyItem;
//                         }
//                     }
//                 } else {
//                     // Create a default 'specialityItem' with null values
//                     $specialtyItem = [
//                         'specialityId' => null,
//                         'specialityUnit' => null,
//                         'specialityPrice' => null
//                     ];

//                     // Add the 'specialityItem' to the current specialty
//                     $specialty['specialityItem'][] = $specialtyItem;
//                 }

//                 $result[$categoryName][] = $specialty;
//             }

//             // Data organized by category, return it as a JSON response
//             echo json_encode(array('success' => true, 'specialtiesByCategory' => $result));
//         } else {
//             // No data found
//             echo json_encode(array('success' => false, 'message' => 'No specialties found with categories.'));
//         }
//     } catch (PDOException $e) {
//         http_response_code(500); // Internal Server Error
//         echo json_encode(array('success' => false, 'message' => 'Database error.'));
//     }
// }




if ($postjson["method"] == "getAllCustomers") {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE userRole = ? ");
    $stmt->execute(['customer']);
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
    // echo "SSSS";
    if (count($users) > 0) {
    
        $response = array(
            'success' => true,
            'message' => 'all customers',
            'customers' => $users
        );
    }else{
        http_response_code(400);
        $response = array(
            'success' => false,
            'message' => 'No available customers',
        );
    }
    echo json_encode($response);

}

if ($postjson["method"] == "getAllCategories") {
    $stmt = $pdo->prepare("SELECT * FROM categories ");
    $stmt->execute();
    $cats = $stmt->fetchAll(PDO::FETCH_ASSOC);
    // echo "SSSS";
    if (count($cats) > 0) {
     
        $response = array(
            'success' => true,
            'message' => 'all categories',
            'categories' => $cats
        );
    }else{
        http_response_code(400);
        $response = array(
            'success' => false,
            'message' => 'No available categories',
        );
    }
    echo json_encode($response);

}



if ($postjson["method"] == "getAllSpecialtiesByCatId") {
    // Validate input fields
    if (empty($postjson['catId'])) {
        http_response_code(400); // Bad Request
        echo json_encode(array('success' => false, 'message' => 'catId is required.'));
        exit;
    }

    try {
        // Retrieve category name for the given category ID
        $stmtCategory = $pdo->prepare("SELECT catName FROM categories WHERE catId = ?");
        $stmtCategory->execute([$postjson['catId']]);
        $categoryName = $stmtCategory->fetchColumn();

        // Retrieve all specialties for the given category ID
        $stmtSpecialties = $pdo->prepare("SELECT * FROM specialties WHERE catId = ?");
        $stmtSpecialties->execute([$postjson['catId']]);
        $specialties = $stmtSpecialties->fetchAll(PDO::FETCH_ASSOC);

        if (!empty($specialties)) {
            // Data found, return it as a JSON response
            echo json_encode(array('success' => true, 'categoryName' => $categoryName, 'specialties' => $specialties));
        } else {
            // No data found for the given category ID
            echo json_encode(array('success' => false, 'message' => 'No specialties found for the specified category.'));
        }
    } catch (PDOException $e) {
        http_response_code(500); // Internal Server Error
        echo json_encode(array('success' => false, 'message' => 'Database error.'));
    }
}



if ($postjson["method"] == "cancelRequest") {
    // Validate input fields
    if (empty($postjson['reqId'])) {
        http_response_code(400); // Bad Request
        echo json_encode(array('success' => false, 'message' => 'reqId is required.'));
        exit;
    }

    try {
        // Check if the request exists
        $stmtCheckRequest = $pdo->prepare("SELECT COUNT(*) as count FROM requests WHERE reqId = ?");
        $stmtCheckRequest->execute([$postjson['reqId']]);
        $requestExists = $stmtCheckRequest->fetch(PDO::FETCH_ASSOC)['count'];

        if ($requestExists) {
            // Update the request status to "accepted"
            $stmtUpdateRequest = $pdo->prepare("UPDATE requests SET reqStatus = 'canceled' WHERE reqId = ?");
            $stmtUpdateRequest->execute([$postjson['reqId']]);

            // Return success response
            echo json_encode(['success' => true, 'message' => 'Request canceled successfully.']);
        } else {
            // Return failure response if the request doesn't exist
            http_response_code(404); // Not Found
            echo json_encode(['success' => false, 'message' => 'Request not found.']);
        }
    } catch (PDOException $e) {
        // Return failure response for any database error
        http_response_code(500); // Internal Server Error
        echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
    }
}

if ($postjson["method"] == "requestRate") {
    try {
        // Check if the request exists
        $stmtCheckRequest = $pdo->prepare("SELECT COUNT(*) as count FROM requests WHERE reqId = ?");
        $stmtCheckRequest->execute([$postjson['reqId']]);
        $requestExists = $stmtCheckRequest->fetch(PDO::FETCH_ASSOC)['count'];

        if ($requestExists) {
            // Update the request status to "accepted"
            $stmtUpdateRequest = $pdo->prepare("UPDATE requests SET reqRate = ? WHERE reqId = ?");
            $stmtUpdateRequest->execute([$postjson['reqRate'],$postjson['reqId']]);

            // Return success response
            echo json_encode(['success' => true, 'message' => 'Request rated successfully.']);
        } else {
            // Return failure response if the request doesn't exist
            http_response_code(404); // Not Found
            echo json_encode(['success' => false, 'message' => 'Request not found.']);
        }
    } catch (PDOException $e) {
        // Return failure response for any database error
        http_response_code(500); // Internal Server Error
        echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
    }
}

if ($postjson["method"] == "requestReportIssue") {
            try{   
                
                $stmt=$pdo->prepare("INSERT INTO issues (issueCustomerId,issueVenderId,issueFromUserId,issueDetails, issueReqId) 
                VALUES (?, ?, ?, ?, ?)
                ");
               if($stmt->execute([$postjson['reqUserId'],$postjson['reqVenderId'],$postjson['reqUserId'],$postjson['reqIssueDetails'],$postjson['reqId']])){

                $stmtUpdateRequest = $pdo->prepare("UPDATE requests SET reqRate = 0 WHERE reqId = ?");
                $stmtUpdateRequest->execute([$postjson['reqId']]);

                     echo json_encode(['success' => true, 'message' => 'Issue inserted Successfully.']);
               }else{
                     echo json_encode(['success' => false, 'message' => 'issue not insereted.']);
               }


            }catch (PDOException $e) {
                    // Return failure response for any database error
                    http_response_code(500); // Internal Server Error
                    echo json_encode(['success' => false, 'message' => 'Database error: ' . $e->getMessage()]);
                }
}




function generateVerificationLink($token)
{
    return 'http://domain.com/verify?token=' . $token;
}

function generateToken($length = 32)
{
    $tokenBytes = random_bytes($length);
    return bin2hex($tokenBytes);
}

?>
