<?php
// ini_set('display_errors', 1);
// ini_set('display_startup_errors', 1);
// error_reporting(E_ALL);

class NotFoundOrder
{
    private $db_con;

    public function __construct()
    {
        global $con;
        $this->db_con = $con;
    }


public function not_found_order_list($input_data): array
{
    
                // ini_set('display_errors', 1);
                // ini_set('display_startup_errors', 1);
                // error_reporting(E_ALL);
    $result = [];
    $item_details = new ItemDetails();
    $output_message = new OutputMessage();
    $not_found_order_list_data = [];

    $start_date = $input_data->start_date;
    $end_date = $input_data->end_date;
    $order_type = $input_data->order_type;
    $search_type = $input_data->search_type;
    $page = $input_data->page;
    $sort_by = $input_data->sort_by;
    $sort_as = $input_data->sort_as;
    $limit = 100;
    $offset = ($page - 1) * $limit;

    // WHERE conditions
    $where = ["a.status = 'true'"];

    if (!empty($start_date) && !empty($end_date)) {
        $where[] = "(DATE(a.completion_date) BETWEEN '$start_date' AND '$end_date')";
    } elseif (!empty($start_date)) {
        $where[] = "(DATE(a.completion_date) >= '$start_date')";
    } elseif (!empty($end_date)) {
        $where[] = "(DATE(a.completion_date) <= '$end_date')";
    }

    if ($order_type == 1) {
        $where[] = "(a.is_paid = 1)";
    } elseif ($order_type == 2) {
        $where[] = "(a.is_paid = 0)";
    }

    if (!empty($search_type)) {
        $search_type = mysqli_real_escape_string($this->db_con, $search_type);
        $where[] = "(" .
            "a.work_order_id LIKE '%$search_type%' OR " .
            "a.work_code LIKE '%$search_type%' OR " .
            "a.alias LIKE '%$search_type%' OR " .
            "a.amount LIKE '%$search_type%' OR " .
            "a.address LIKE '%$search_type%'" .
        ")";
    }

    $order_by_sql = "ORDER BY a.completion_date DESC";
    $allowed_sort_fields = ['work_order_id', 'address', 'state', 'zip_code', 'alias', 'work_code', 'amount', 'completion_date'];
    $allowed_sort_directions = ['ASC', 'DESC'];

    if (in_array($sort_by, $allowed_sort_fields) && in_array($sort_as, $allowed_sort_directions)) {
        $order_by_sql = "ORDER BY a.$sort_by $sort_as";
    }


    $where_sql = "";
    if (!empty($where)) {
        $where_sql = "WHERE " . implode(" AND ", $where);
    }

    // Final SQL using LEFT JOIN instead of NOT IN (col1, col2)
    $not_found_sql = "
        SELECT a.*, a.uid, a.completion_date, a.work_order_id, a.work_code, a.amount, a.zip_code 
        FROM " . tbl_work_order . " a
        LEFT JOIN " . tbl_price_list . " b 
            ON a.work_code = b.work_code AND a.zip_code = b.zip_code
        $where_sql AND b.work_code IS NULL
        GROUP BY a.id
        $order_by_sql
        LIMIT $offset, $limit
    ";

    // For pagination: use same base logic for COUNT
    $count_sql = "
        SELECT COUNT(DISTINCT a.id) as total
        FROM " . tbl_work_order . " a
        LEFT JOIN " . tbl_price_list . " b 
            ON a.work_code = b.work_code AND a.zip_code = b.zip_code
        $where_sql AND b.work_code IS NULL
    ";

    // Get total rows
    $count_query = mysqli_query($this->db_con, $count_sql);
    $count_result = mysqli_fetch_assoc($count_query);
    $total_rows = $count_result['total'] ?? 0;

    $result['pagination']['total_records'] = $total_rows;

    // Run actual query
    $not_found_query = mysqli_query($this->db_con, $not_found_sql);
    while ($not_found_arr = mysqli_fetch_assoc($not_found_query)) {
        // $state = $item_details->get_state_from_zip($not_found_arr['zip_code']);
        $tmp_data = [
            "id" => $not_found_arr["uid"],
            "work_order_id" => $not_found_arr["work_order_id"],
            "work_code" => strtoupper($not_found_arr["work_code"]),
            "alias" => strtoupper($not_found_arr["alias"]),
            "address" => ucwords($not_found_arr["address"]),
            "full_address" => ucwords($not_found_arr['address']) . ', '. ucwords($not_found_arr['city']). ', '. ucwords($not_found_arr['state']).', '.ucwords($not_found_arr['zip_code']),
            "amount" => number_format((float)filter_var($not_found_arr["amount"], FILTER_VALIDATE_FLOAT), 2, '.', ','),
            "zip_code" => $not_found_arr["zip_code"],
            "completion_date" => date('m/d/Y', strtotime($not_found_arr["completion_date"])),
            "state" =>  $not_found_arr["state"],
        ];
        $not_found_order_list_data[] = $tmp_data;
    }

    $result['list'] = $not_found_order_list_data;

    if (count($result['list']) > 0) {
        $result['pagination']['page_size'] = $limit;
        $result['pagination']['current_page'] = $page;
        $result['pagination']['total_pages'] = ceil($total_rows / $limit);
        return $output_message->successOutput($result, 'All not found order(s) Loaded');
    } else {
        return $output_message->errorOutput('No not found order available');
    }
}

    


    public function not_found_order_details($input_data)
    {

        $output_message = new OutputMessage();

        $not_found_details_sql = /** @lang text */
            "SELECT uid, amount FROM " . tbl_work_order . " where uid='" . $input_data->usr_not_found_id . "' AND STATUS='true'";

        $not_found_details_query = mysqli_query($this->db_con, $not_found_details_sql) or die(mysqli_error($this->db_con));

        $not_found_details_arr = mysqli_fetch_assoc($not_found_details_query);

        $not_found_order_details_data = [
            "id" => $not_found_details_arr["uid"],
            "amount" => number_format((float)filter_var($not_found_details_arr["amount"], FILTER_VALIDATE_FLOAT), 2, '.', ',')
        ];

        if (count($not_found_order_details_data) > 0) {
            $api_output = $output_message->successOutput($not_found_order_details_data, 'All not found order(s) Loaded');
        } else {
            $api_output = $output_message->errorOutput('No not found order available');
        }

        return $api_output;
    }

    public function not_found_order_manage_bulk($input_data)
    {
        $output_message = new OutputMessage();
        $tmp_count = 0;

        $tmp_work_order_id_arr = explode(",", $input_data->usr_not_found_id);
        $tmp_work_order_amount_arr = explode(",", $input_data->usr_not_found_amount);
        $tmp_data = new stdClass();

        for ($i = 0; $i < sizeof($tmp_work_order_id_arr); $i++) {
            $tmp_data->usr_not_found_id = $tmp_work_order_id_arr[$i];
            $tmp_data->usr_not_found_amount = $tmp_work_order_amount_arr[$i];
            $tmp_update_status = $this->not_found_order_manage_single($tmp_data);
            if ($tmp_update_status) {
                $tmp_count++;
            }

        }

        if ($tmp_count == sizeof($tmp_work_order_id_arr)) {
            $api_output = $output_message->successOutput("", 'Not Found Order Updated And Added to price database');
        } else {
            $api_output = $output_message->errorOutput('not found order update Error');
        }

        return $api_output;
    }

    private function not_found_order_manage_single($input_data)
    {
        $update_not_found_sql = "UPDATE " . tbl_work_order . " SET amount='" . $input_data->usr_not_found_amount . "' ";
        $update_not_found_sql .= "WHERE uid='" . $input_data->usr_not_found_id . "'";

        $update_not_found_query = mysqli_query($this->db_con, $update_not_found_sql);

        if ($update_not_found_query) {
            $not_found_details_sql = /** @lang text */
                "SELECT * FROM " . tbl_work_order . " where uid='" . $input_data->usr_not_found_id . "' AND STATUS='true'";

            $not_found_details_query = mysqli_query($this->db_con, $not_found_details_sql) or die (mysqli_error($this->db_con));

            $not_found_details_arr = mysqli_fetch_assoc($not_found_details_query);

            if (count($not_found_details_arr) > 0) {

                $get_price_list_sql = "SELECT * FROM " . tbl_price_list . " WHERE work_code='" . $not_found_details_arr["work_code"] . "' AND zip_code='" . $not_found_details_arr["zip_code"] . "' AND status='true' LIMIT 1";

                $get_price_query = mysqli_query($this->db_con, $get_price_list_sql) or die (mysqli_error($this->db_con));

                if (mysqli_num_rows($get_price_query) > 0) {
                    $add_price_list_sql = /** @lang text */
                        "UPDATE " . tbl_price_list . " SET amount='" . $not_found_details_arr["amount"] . "' WHERE ";
                    $add_price_list_sql .= /** @lang text */
                        "work_code='" . $not_found_details_arr["work_code"] . "' AND zip_code='" . $not_found_details_arr["zip_code"] . "'";
                } else {
                    $add_price_list_sql = /** @lang text */
                        "INSERT INTO " . tbl_price_list . " (uid, work_code, amount, zip_code) VALUES ";
                    $add_price_list_sql .= /** @lang text */
                        "('" . uniqid() . "', '" . $not_found_details_arr["work_code"] . "', '";
                    $add_price_list_sql .= /** @lang text */
                        $not_found_details_arr["amount"] . "', '" . $not_found_details_arr["zip_code"] . "')";
                }
                return mysqli_query($this->db_con, $add_price_list_sql);
            } else {
                return false;
            }
        } else {
            return false;
        }
    }
    /*
        private function ftpUpload($file, $file_name, $upload_type): bool
        {
            global $img_ftp_server, $img_ftp_user_name, $img_ftp_user_pass, $audio_ftp_server, $audio_ftp_user_name, $audio_ftp_user_pass;

            $ftp_server = "";
            $ftp_user = "";
            $ftp_pass = "";


            if ($upload_type == "image") {
                $ftp_server = $img_ftp_server;
                $ftp_user = $img_ftp_user_name;
                $ftp_pass = $img_ftp_user_pass;
            } else if ($upload_type == "audio") {
                $ftp_server = $audio_ftp_server;
                $ftp_user = $audio_ftp_user_name;
                $ftp_pass = $audio_ftp_user_pass;
            }


            $ftp_con = ftp_connect($ftp_server);

            if (!$ftp_con) {
                $tmp_status = false;
            } else {
                $login = ftp_login($ftp_con, $ftp_user, $ftp_pass);
                if (!$login) {
                    $tmp_status = false;
                } else {
                    if (ftp_put($ftp_con, $file_name, $file, FTP_BINARY)) {
                        $tmp_status = true;
                    } else {
                        $tmp_status = false;
                    }
                }

                ftp_close($ftp_con);
            }
            return $tmp_status;
        }
        */
}






