<?php session_start();
include("db/db.php");

if (isset($_SESSION['loggedIn']) && $_SESSION['loggedIn'] == true) {
    //die('yes');

    // TO TEST INPUT VALUES
    function test_input($data)
    {
        $data = trim($data);
        $data = stripslashes($data);
        $data = htmlspecialchars($data);
        return $data;
    }

    setUserSignOut();

    //THIS IS TO CREATE THE ADD EXCURSION FORM
    //hasitha-2023-08-22 ehidata base eken ganna details comment kala
    if ((isset($_POST['ref_no']) || isset($_POST['ehi_no'])) && (!isset($_POST['ticket_no']) && !isset($_POST['excursion_date']))) {

        //$ref_no = '%' . $get_ref_no . '%';
        $ehi_details = array();

        // if (($dbonline) && (isset($_POST['ref_no']) && !isset($_POST['ehi_no']))) {
        //     $ref_no = test_input($_POST['ref_no']);
        //     if ($ref_no != '') {
        //         $sql = "select *
        //           FROM
        //             ehi
        //           WHERE
        //             arno=$ref_no";

        //         $result = mysqli_query($dbonline, $sql);

        //         if ($result->num_rows > 0) {
        //             while ($row = $result->fetch_assoc()) {
        //                 $ehi_details[] = array(
        //                     "ehi_no" => $row["EHIno"],
        //                     "ref_no" => $row["arno"],
        //                     "name" => $row["name"],
        //                     "agent_name" => $row["agt"],
        //                     "adult" => $row["pax_a"],
        //                     "child" => $row["pax_c"],
        //                     "infant" => $row["pax_i"],
        //                     // "hotel" => $row["tour_beach"],

        //                 );
        //             }
        //         } else {
        //             $ehi_details[] = array(
        //                 "ehi_no" => '',
        //                 "ref_no" => '',
        //                 "name" => '',
        //                 "agent_name" => '',
        //                 "adult" => '',
        //                 "child" => '',
        //                 "infant" => '',
        //                 //"room_no" => '',

        //             );
        //         }
        //     } else {
        //         $ehi_details[] = array(
        //             "ehi_no" => '',
        //             "ref_no" => '',
        //             "name" => '',
        //             "agent_name" => '',
        //             "adult" => '',
        //             "child" => '',
        //             "infant" => '',
        //             // "room_no" => '',
        //         );
        //     }
        // } else if (($dbonline) && (isset($_POST['ehi_no']) && !isset($_POST['ref_no']))) {
        //     $ehi_no = test_input($_POST['ehi_no']);
        //     if ($ehi_no != '') {
        //         $sql = "select *
        //           FROM
        //             ehi
        //           WHERE
        //             EHIno=$ehi_no";

        //         $result = mysqli_query($dbonline, $sql);
        //         if ($result->num_rows > 0) {
        //             while ($row = $result->fetch_assoc()) {
        //                 $ehi_details[] = array(
        //                     "ehi_no" => $row["EHIno"],
        //                     "ref_no" => $row["arno"],
        //                     "name" => $row["name"],
        //                     "agent_name" => $row["agt"],
        //                     "adult" => $row["pax_a"],
        //                     "child" => $row["pax_c"],
        //                     "infant" => $row["pax_i"],
        //                     //  "hotel" => $row["tour_beach"],

        //                 );
        //             }
        //         } else {
        //             $ehi_details[] = array(
        //                 "ehi_no" => '',
        //                 "ref_no" => '',
        //                 "name" => '',
        //                 "agent_name" => '',
        //                 "adult" => '',
        //                 "child" => '',
        //                 "infant" => '',
        //                 // "room_no" => '',
        //             );
        //         }
        //     } else {
        //         $ehi_details[] = array(
        //             "ehi_no" => '',
        //             "ref_no" => '',
        //             "name" => '',
        //             "agent_name" => '',
        //             "adult" => '',
        //             "child" => '',
        //             "infant" => '',
        //             // "room_no" => '',

        //         );
        //     }
        // } else {
        //     echo "EHI Database Connection Failed";
        // }
        echo json_encode($ehi_details);
    }


    //THIS IS TO ADD EXCURSIONS TO EX_DB
    if (isset($_POST['ref_no']) && isset($_POST['ehi_no']) && isset($_POST['amount']) && ($_POST['add_excursion'] === 'add')) {

        $ticket_no = $room_no = $excursion_date = $amount = "";

        if ($_SERVER["REQUEST_METHOD"] == "POST") {

            $ref_no = test_input($_POST['ref_no']);
            $ehi_no = test_input($_POST['ehi_no']);
            $get_name = test_input($_POST['name']);
            $client_name = strpos($get_name, "/") ? substr($get_name, 0, strpos($get_name, "/")) : $get_name;
            $adult = test_input($_POST['adult']);

            $company = test_input($_POST['company']);


            $child = test_input($_POST['child']);
            $infant = test_input($_POST['infant']);
            $excursion = test_input($_POST['excursion']);
            $hotel = test_input($_POST['hotel']);
            $remark = test_input($_POST['remark']);
            $transfer_type = test_input($_POST['transfer_type']);
            $pickup_time = test_input($_POST['pickup_time']);
            $promoted_guide = $_SESSION['user_id'];
            // $agent_name = $_SESSION['user_name'];

            $agent_name =  test_input($_POST['agent_name']);
            $insert_date = date("Y-m-d");
            $driver_id = 1;
            // $put = '-';
            $tour_guide_id = 1;
            $created_at = date("Y-m-d H:i:s");
            $ticket_no = test_input($_POST["ticket_no"]);
            $room_no = test_input($_POST["room_no"]);
            $get_excursion_date = test_input($_POST["excursion_date"]);
            $excursion_date = date("Y-m-d", strtotime($get_excursion_date));
            $amount = test_input($_POST["amount"]);
        }

        if ($transfer_type === '0') {
            $transfer_type = '2';
        }

        //        if ($promoted_guide === '0' || $promoted_guide === 'null') {
        //            session_unset();
        //            header("location: sign-in.php");
        //        }


        if ($_SESSION['user_id'] === '0' || $_SESSION['user_id'] === 'null') {
            session_unset();
            header("location: sign-in.php");
        }

        //die($ref_no.'****'.$ehi_no.'****'.$ticket_no.'****'.$client_name.'****'.$adult.'****'.$child.'****'.$hotel.'****'.$excursion.'****'.$excursion_date.'****'.$amount.'****'.$promoted_guide.'****'.$driver_id.'****'.$put.'****'.$tour_guide_id.'****'.$created_at.'****'.$insert_date.'****'.$agent_name.'****'.$room_no);
        //die("'$insert_date', '$ref_no', '$ehi_no', '$agent_name' '$client_name', '$adult', '$child', '$ticket_no', '$room_no', '$driver_id', '$put', '$tour_guide_id', '$excursion_date' , '$hotel', '$excursion', '$amount', '$promoted_guide', '1', '$created_at', '$remark'");

        $get_excursion_id = "SELECT excursion_no FROM excursion_no";

        $ex_result = mysqli_query($db, $get_excursion_id);
        if ($ex_result->num_rows > 0) {
            while ($ex_row = $ex_result->fetch_assoc()) {
                $excursion_no = $ex_row["excursion_no"];
                $nxt_excursion_id = $excursion_no + 1;
            }
        }
        $nxt_excursion_id = $excursion_no + 1;

        $insert_excursion_details = "INSERT INTO excursions
                                          (insert_date, agent_ref, ehi_no, agent, client_name, adult, company, child, infant , ticket_no, room_no, put, excursion_date, hotel_id, excursion_id, rate, promote_guide_id, val, created_at, remark, excursion_no, transfer_type)
                                    VALUES
                                          ('$insert_date', '$ref_no', '$ehi_no', '$agent_name', '$client_name', '$adult', '$company', '$child', '$infant' , '$ticket_no', '$room_no', '$pickup_time', '$excursion_date', '$hotel', '$excursion', '$amount', '$promoted_guide', '1', '$created_at', '$remark', '$nxt_excursion_id', '$transfer_type')";

        $insert_excursion_no = "UPDATE excursion_no
                                    SET
                                    excursion_no = '$nxt_excursion_id'";

        if (($db->query($insert_excursion_details)) && ($db->query($insert_excursion_no) === TRUE)) {
            //echo "Record updated successfully";
            echo json_encode('1');
        } else {
            echo "Error updating record: " . $db->error;
            echo json_encode('0');
        }
    }


    //THIS IS TO UPDATE EXCURSIONS TO EX_DB
    if (isset($_POST['ref_no']) && isset($_POST['ehi_no']) && isset($_POST['amount']) && ($_POST['add_excursion'] === 'update')) {

        $ticket_no = $room_no = $excursion_date = $amount = "";

        if ($_SERVER["REQUEST_METHOD"] == "POST") {

            $ref_no = test_input($_POST['ref_no']);
            $ehi_no = test_input($_POST['ehi_no']);
            $get_name = test_input($_POST['name']);
            $client_name = strpos($get_name, "/") ? substr($get_name, 0, strpos($get_name, "/")) : $get_name;

            $company_id = test_input($_POST['company']);
            if ($company_id == 1) {
                $company = 'CMD';
            }

            $adult = test_input($_POST['adult']);
            $child = test_input($_POST['child']);
            $infant = test_input($_POST['infant']);
            $excursion = test_input($_POST['excursion']);
            $transfer_type = test_input($_POST['transfer_type']);
            $pickup_time = test_input($_POST['pickup_time']);
            $hotel = test_input($_POST['hotel']);
            $remark = test_input($_POST['remark']);
            $updated_at = date("Y-m-d H:i:s");
            $ticket_no = test_input($_POST["ticket_no"]);
            $room_no = test_input($_POST["room_no"]);
            $get_excursion_date = test_input($_POST["excursion_date"]);
            $excursion_date = date("Y-m-d", strtotime($get_excursion_date));
            $amount = test_input($_POST["amount"]);
            $excursion_no = test_input($_POST["excursion_no"]);

            //echo $excursion_date.'/'.$excursion_no.'/'.$_POST['add_excursion'];
        }

        if ($_SESSION['user_id'] === '0' || $_SESSION['user_id'] === 'null') {
            session_unset();
            header("location: sign-in.php");
        }

        $insert_excursion_details = "UPDATE
                                    excursions
                                      SET
                                           adult = '$adult',
                                           child = '$child',
                                           infant = '$infant',
                                           company = '$company',
                                           ticket_no = '$ticket_no',
                                           room_no = '$room_no',
                                           excursion_date = '$excursion_date',
                                           hotel_id = '$hotel',
                                           excursion_id = '$excursion',
                                           put = '$pickup_time',
                                           rate = '$amount',
                                           remark = '$remark',
                                           transfer_type = '$transfer_type',
                                           updated_at = '$updated_at'
                                      WHERE
                                           excursion_no = '$excursion_no'";


        if ($db->query($insert_excursion_details)) {
            //echo "Record updated successfully";
            echo json_encode('1');
        } else {
            echo "Error updating record: " . $db->error;
            echo json_encode('0');
        }
    }


    //THIS IS TO SEARCH EXCURSIONS
    if (isset($_POST['ref_no_se']) || isset($_POST['ticket_no_se']) || isset($_POST['ehi_no_se']) || isset($_POST['excursion_se']) || isset($_POST['excursion_date_se'])) {



        if ($_POST['ref_no_se'] != '') {
            $ref_no_se = test_input($_POST['ref_no_se']);
        } else {
            $ref_no_se = "%";
        }

        if ($_POST['ticket_no_se'] != '') {
            $ticket_no_se = test_input($_POST['ticket_no_se']);
        } else {
            $ticket_no_se = "%";
        }

        if ($_POST['ehi_no_se'] != '') {
            $ehi_no_se = test_input($_POST['ehi_no_se']);
        } else {
            $ehi_no_se = "%";
        }

        if ($_POST['user_se'] != '0') {
            if ($_POST['user_se'] === 'undefined') {
                $user_se = "%";
            } else {
                $user_se = test_input($_POST['user_se']);
            }
        } else {
            $user_se = "%";
        }

        if ($_POST['excursion_date_se'] != '') {
            $get_excursion_date_se = test_input($_POST['excursion_date_se']);
            $excursion_date_se = date("Y-m-d", strtotime($get_excursion_date_se));
        } else {
            $excursion_date_se = "%";
        }

        if ($_POST['excursion_se'] != '0') {
            $excursion_se = test_input($_POST['excursion_se']);
            // $excursion_name_se = test_input($_POST['excursion_name_se']);
        } else {
            $excursion_se = "%";
        }

        if ($_POST['excursion_name_se'] != 'Select Excursion') {
            $excursion_name_se = test_input($_POST['excursion_name_se']);
            // $excursion_name_se = test_input($_POST['excursion_name_se']);
        } else {
            $excursion_name_se = "%";
        }

        //    $user_id = $_SESSION['user_id'];
        //    $sql_3 = "select role_id
        //                         FROM
        //                           users
        //                         WHERE
        //                           id=$user_id";
        //    $result_3 = mysqli_query($db, $sql_3);
        //    while ($row_3 = mysqli_fetch_array($result_3)) {
        //        $user_role = $row_3['role_id'];
        //    }

        //die($ref_no_se.'/'.$ticket_no_se.'/'.$ehi_no_se.'/'.$excursion_date_se.'/'.$excursion_se.'/'.$user_se);

        $ehi_search_details = array();

        if ($db) {

            // die($excursion_name_se);
            $sql_4 = "SELECT
            excursion_list.excursion_name, 
            excursions.*
        FROM
            excursion_list
            INNER JOIN
            excursions
            ON 
                excursion_list.id = excursions.excursion_id

                    
                  WHERE
                    (agent_ref LIKE '$ref_no_se'
                    AND ticket_no LIKE '$ticket_no_se'
                    AND ehi_no LIKE '$ehi_no_se'
                  AND excursion_name Like '$excursion_name_se'
                    AND promote_guide_id LIKE '$user_se'
                    AND excursion_date LIKE '$excursion_date_se')";

            $result_4 = mysqli_query($db, $sql_4);

            if ($result_4->num_rows > 0) {
                //die($result_4->num_rows . '/' . 'has');
                while ($row_4 = $result_4->fetch_assoc()) {
                    //echo($row_4["excursion_id"]);
                    $excursion_id = $row_4["excursion_id"];
                    $hotel_id = $row_4["hotel_id"];
                    /// $driver_id = $row_4["driver_id"];
                    /// $tour_guide_id = $row_4["tour_guide_id"];
                    $promote_guide_id = $row_4["promote_guide_id"];

                    // GET EXCURSION NAME
                    $excursion_name = "SELECT excursion_name FROM excursion_list WHERE id='$excursion_id'";
                    $ex_name = mysqli_query($db, $excursion_name);
                    if ($ex_name->num_rows > 0) {
                        while ($ex_row1 = $ex_name->fetch_assoc()) {
                            $excursion_name = $ex_row1["excursion_name"];
                        }
                    }

                    // GET HOTEL NAME
                    $hotel_name = "SELECT hotel_name FROM hotels WHERE id='$hotel_id'";
                    $ex_hot_name = mysqli_query($db, $hotel_name);
                    if ($ex_hot_name->num_rows > 0) {
                        while ($ex_row2 = $ex_hot_name->fetch_assoc()) {
                            $hotel_name = $ex_row2["hotel_name"];
                        }
                    }

                    // GET DRIVER NAME
                    $driver_name = "SELECT driver_name FROM drivers WHERE id='$driver_id'";
                    $ex_drv_name = mysqli_query($db, $driver_name);
                    if ($ex_drv_name->num_rows > 0) {
                        while ($ex_row3 = $ex_drv_name->fetch_assoc()) {
                            $driver_name = $ex_row3["driver_name"];
                        }
                    }

                    /// GET TOUR GUIDE NAME
                    // $tour_guide = "SELECT first_name, last_name FROM users WHERE id='$tour_guide_id'";
                    // $ex_tourGuide_name = mysqli_query($db, $tour_guide);
                    // if ($ex_tourGuide_name->num_rows > 0) {
                    //     while ($ex_row4 = $ex_tourGuide_name->fetch_assoc()) {
                    //         $tour_guide1 = $ex_row4["first_name"];
                    //         $tour_guide2 = $ex_row4["last_name"];
                    //         $tour_guide_name = $tour_guide1 . ' ' . $tour_guide2;
                    //     }
                    // }

                    // GET TOUR GUIDE NAME
                    $promoted_guide = "SELECT first_name, last_name FROM users WHERE id='$promote_guide_id'";
                    $ex_promoteGuide_name = mysqli_query($db, $promoted_guide);
                    if ($ex_promoteGuide_name->num_rows > 0) {
                        while ($ex_row5 = $ex_promoteGuide_name->fetch_assoc()) {
                            $promoted_guide1 = $ex_row5["first_name"];
                            $promoted_guide2 = $ex_row5["last_name"];
                            $promoted_guide_name = $promoted_guide1 . ' ' . $promoted_guide2;
                        }
                    }

                    $ehi_search_details[] = array(
                        "ex_id" => $row_4["id"],
                        "ehi_no" => $row_4["ehi_no"],
                        "excursion_no" => $row_4["excursion_no"],
                        "agent_ref" => $row_4["agent_ref"],
                        "name" => $row_4["client_name"],
                        "adult" => $row_4["adult"],
                        "child" => $row_4["child"],
                        "infant" => $row_4["infant"],
                        "insert_date" => $row_4["insert_date"],
                        "agent" => $row_4["agent"],
                        "company" => $row_4["company"],
                        "ticket_no" => $row_4["ticket_no"],
                        "room_no" => $row_4["room_no"],
                        // "driver_id" => $row_4["driver_id"],
                        // "driver" => $driver_name,
                        "pickup_time" => $row_4["put"],
                        //"tour_guide_id" => $row_4["tour_guide_id"],
                        //"tour_guide_name" => $tour_guide_name,
                        "excursion_date" => $row_4["excursion_date"],
                        "hotel_id" => $row_4["hotel_id"],
                        "excursion_id" => $row_4["excursion_id"],
                        "excursion_name" => $excursion_name,
                        "hotel_name" => $hotel_name,
                        "rate" => $row_4["rate"],
                        "promote_guide_id" => $row_4["promote_guide_id"],
                        "promote_guide_name" => $promoted_guide_name,
                        "created_at" => $row_4["created_at"],
                        "remark" => $row_4["remark"],
                    );
                    //die($ehi_search_details);
                }
            } else {
                echo json_encode('3');
                exit();
            }
        } else {
            echo "EHI Database Connection Failed";
        }

        echo json_encode($ehi_search_details);
    }
} else { //die('no');
    session_unset();
    header("Location: sign-in.php");
}

//THIS IS TO AUTO SIGN OUT USER
if (isset($_POST['autoSign_out_fn']) && ($_POST['autoSign_out_fn']) == '92') {
    session_unset();
    //header("Location: sign-in.php");
    echo json_encode('92');
}


//THIS IS TO GET THE EXCURSION RATE ACCORDING TO THE HOTEL
if (isset($_POST['excursion_hotel_rate']) && ($_POST['excursion_hotel_rate'] == '1')) {

    $adult = test_input($_POST['adult']);
    $child = test_input($_POST['child']);
    $infant = test_input($_POST['infant']);
    $excursion = test_input($_POST['excursion']);
    $transfer_type = test_input($_POST['transfer_type']);
    $hotel = test_input($_POST['hotel']);

    // GET HOTEL CITY
    $hotel_city = "SELECT city_id FROM hotels WHERE id='$hotel'";
    $hot_city_name = mysqli_query($db, $hotel_city);
    if ($hot_city_name->num_rows > 0) {
        while ($hot_city_name_row2 = $hot_city_name->fetch_assoc()) {
            $hotel_city_id = $hot_city_name_row2["city_id"];
        }
    }

    // die($hotel.'/'.$hotel_city_id . '/' . $excursion . "/" . $transfer_type);

    // $total_amount=0;
    if ($db) {

        if (($excursion != 0) && ($hotel != 0)) {
            // die($hotel_city_id . '/' . $excursion . "/" . $transfer_type);

            // $excursion = 1;
            // $hotel_city_id = 2;
            // $transfer_type = 1;

            $sql_81 = "SELECT * FROM excursion_list WHERE id='$excursion'";
            $ex_name = mysqli_query($db, $sql_81);
            if ($ex_name->num_rows > 0) {
                while ($ex_name_row2 = $ex_name->fetch_assoc()) {
                    $ex_name_id = $ex_name_row2["id"];
                }
            }



            // $sql_8 = "SELECT * FROM excursion_names WHERE excursion_name='$ex_name_last' AND city_id='$hotel_city_id' AND transfer_id='$transfer_type'";



            $sql_8 = "SELECT hotels.hotel_name AS hotel_name, 
        excursion_list.excursion_name AS excursion_name, 
        excursion_price.id AS id, 
        excursion_price.excursion_id AS excursion_id, 
        excursion_price.transfer_id AS transfer_id, 
        excursion_price.city_id AS city_id, 
        excursion_price.amount AS amount, 
        excursion_price.val AS val, 
        excursion_price.updated_at AS updated_at, 
        excursion_price.created_at AS created_at, 
        hotels.id AS hotel_id
    FROM
        (
            (
                excursion_list
                join
                excursion_price
                ON 
                    (
                        (
                            excursion_list.id = excursion_price.excursion_id
                        )
                    )
            )
            join
            hotels
            ON 
                (
                    (
                        hotels.city_id = excursion_price.city_id
                    )
                )
        ) WHERE excursion_price.city_id='$hotel_city_id' AND excursion_price.transfer_id='$transfer_type' AND hotels.id ='$hotel' and excursion_id='$ex_name_id'";




            $result_8 = mysqli_query($db, $sql_8);
            // print_r($result_8);
            $total_amount = 0;

            if ($result_8->num_rows > 0) {

                // die($result_8->num_rows . '/' . 'has');
                while ($row_8 = $result_8->fetch_assoc()) {
                    $amount = $row_8["amount"];
                }


                $ad = $amount * $adult;
                $ch = ($amount / 2 * $child);

                $total_amount = $ad + $ch;
                //die($total_amount);

                echo json_encode($total_amount);
            } else {
                // echo json_encode('3');
                echo json_encode($total_amount);
                //  exit();
            }
        }
    } else {
        echo "EHI Database Connection Failed";
    }
}
// GET HOTEL CITY


if (isset($_POST['hotelid'])) {
    if ($db) {

        $hotelid = test_input($_POST['hotelid']);
        $transfer_type = test_input($_POST['transfer_type']);


        $hotel_city = "SELECT * FROM hotels WHERE id='$hotelid'";
        $hot_city_name = mysqli_query($db, $hotel_city);
        if ($hot_city_name->num_rows > 0) {
            while ($hot_city_name_row2 = $hot_city_name->fetch_assoc()) {
                $hotel_city_id = $hot_city_name_row2["city_id"];
            }
        }



        $excursion_list = "SELECT * FROM excursion_list";



        $excursion_list_name = mysqli_query($db, $excursion_list);

        //  die($excursion_list_name);
        if ($excursion_list_name->num_rows > 0) {
            $excursions_list_array = [];
            while ($excursion_list_name_row3 = $excursion_list_name->fetch_assoc()) {
                $excursions_list_array[$excursion_list_name_row3['id']] = $excursion_list_name_row3['excursion_name'];
            }
        }

        echo json_encode($excursions_list_array);
    }
}
