<?php

class NotFoundReport
{
    private $db_con;

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

    public function not_found_report_download($input_data)
    {

        if($input_data->usr_download_type==="all"){
            return $this->not_found_report_download_all($input_data);
        }else{
            return $this->not_found_report_download_single($input_data);
        }

    }

    public function assign_alias($input_data)
    {
        ini_set('display_errors', 1);
        ini_set('display_startup_errors', 1);
        error_reporting(E_ALL);

        $output_message = new OutputMessage();

        // Sanitize input
        $usr_id = mysqli_real_escape_string($this->db_con, $input_data->usr_id ?? '');
        $new_alias = trim(mysqli_real_escape_string($this->db_con, $input_data->usr_alias ?? ''));

        if (empty($usr_id) || $new_alias === '') {
            return $output_message->errorOutput("User ID or alias missing.");
        }

        // Ensure string comparison by quoting id
        $sql_get_alias = "SELECT alias FROM " . tbl_usr . " WHERE uid = '$usr_id'";
        $result = mysqli_query($this->db_con, $sql_get_alias);
        $_result = mysqli_fetch_assoc($result);
        $current_aliases = $_result ? $_result['alias'] : '';

        $alias_array = array_map('trim', explode(',', $current_aliases));

        if (in_array($new_alias, $alias_array)) {
            return $output_message->errorOutput("Alias already assigned to this user.");
        }

        $updated_aliases = $current_aliases ? $current_aliases . ',' . $new_alias : $new_alias;

        // Update the alias safely
        $sql_update = "UPDATE " . tbl_usr . " SET alias = '$updated_aliases' WHERE uid = '$usr_id'";
        $update_result = mysqli_query($this->db_con, $sql_update);

        if ($update_result) {
            return $output_message->successOutput(null, "Alias assigned successfully.");
        } else {
            return $output_message->errorOutput("Failed to assign alias.");
        }
    }



    private function not_found_report_download_all($input_data)
    {
        $output_message = new OutputMessage();
        $tmp_file_name = "report-not-found-order-all" . $input_data->usr_report_start_date . "-to-" . $input_data->usr_report_end_date;
        $tmp_title = " not found orders all" . date('m/d/Y', strtotime($input_data->usr_report_start_date)) . " - " . date('m/d/Y', strtotime($input_data->usr_report_end_date))." - ".$input_data->usr_report_alias;


        $report_file = $this->generate_not_found_report_pdf($tmp_title, $tmp_file_name, $this->not_found_report_list($input_data)["data"], false);

        if (!$report_file) {
            $api_output = $output_message->errorOutput("Report send ERROR");
        } else {
            $api_output = $output_message->successOutput(report_via_url . $report_file, "Report download successfully");
        }
        return $api_output;
    }

    private function not_found_report_download_single($input_data)
    {
        $output_message = new OutputMessage();

        $not_found_report_data=[];
        $tmp_tot_amount=0;

        $get_order_sql = "SELECT " . tbl_work_order . ".work_order_id as report_work_order_id, " . tbl_work_order . ".alias as report_alias, " . tbl_work_order . ".completion_date as report_completion_date, " . tbl_work_order . ".work_code as report_work_code, " . tbl_work_order . ".address as report_address, " . tbl_work_order . ".amount as report_amount, " . tbl_price_list . ".amount as report_price_amount FROM " . tbl_work_order . " LEFT JOIN " . tbl_price_list . " ON " . tbl_work_order . ".work_code=" . tbl_price_list . ".work_code AND " . tbl_work_order . ".zip_code=" . tbl_price_list . ".zip_code WHERE " . tbl_work_order . ".alias='".$input_data->usr_report_alias."' AND DATE(completion_date) between '" . $input_data->usr_report_start_date . "' AND '" . $input_data->usr_report_end_date . "' ORDER BY completion_date DESC";
        $get_order_query = mysqli_query($this->db_con, $get_order_sql) or (die($this->db_con));


        while ($get_order_arr = mysqli_fetch_assoc($get_order_query)) {

            $tmp_data = [
                "alias" => strtoupper($get_order_arr["report_alias"]),
                "complete_date" => date('m/d/Y', strtotime($get_order_arr["report_completion_date"])),
                "work_order_id" => strtoupper($get_order_arr["report_work_order_id"]),
                "work_code" => strtoupper($get_order_arr["report_work_code"]),
                "address" => strtoupper($get_order_arr["report_address"])
            ];

            if ($get_order_arr["report_price_amount"] == null) {
                $tmp_data["amount"] = "Not Found";
                if ($get_order_arr["report_amount"] == 0) {
                    $tmp_data["amount"] = "Not Paid";
                }
            } else {
                $tmp_data["amount"] = $get_order_arr["report_price_amount"];
                $tmp_tot_amount += $get_order_arr["report_price_amount"];
            }
            //$tmp_tot_amount += $get_order_arr["report_amount"];
            // $tmp_data["amount"]="$".number_format($tmp_tot_amount, 2, '.', ',');

            $not_found_report_data["order_details"][] = $tmp_data;
        }
        $not_found_report_data["amount"] = $tmp_tot_amount;
        $not_found_report_data["tot_order"] = mysqli_num_rows($get_order_query);

        if ($input_data->usr_download_type == "single") {
            $tmp_single_report=true;
        }else{
            $tmp_single_report=false;
        }




        $tmp_file_name = $input_data->usr_report_alias."_" . date('m-d-Y', strtotime($input_data->usr_report_start_date)) . "_" . date('m-d-Y', strtotime($input_data->usr_report_end_date));
        $tmp_title = " not found orders " . date('m/d/Y', strtotime($input_data->usr_report_start_date)) . " - " . date('m/d/Y', strtotime($input_data->usr_report_end_date))." - ".$input_data->usr_report_alias;

        $report_file = $this->generate_not_found_report_pdf($tmp_title, $tmp_file_name, $not_found_report_data, $tmp_single_report);

        if (!$report_file) {
            $api_output = $output_message->errorOutput("Report send ERROR");
        } else {
            $api_output = $output_message->successOutput(report_via_url . $report_file, "Report download successfully");
        }
        return $api_output;
    }


    public function not_found_report_list($input_data)
    {

        $output_message = new OutputMessage();

        $report_details_data = $this->not_found_report_generate($input_data);

        if (!$report_details_data) {
            $api_output = $output_message->errorOutput('No report found');
        } else {
            $api_output = $output_message->successOutput($report_details_data, "report generated successfully");
        }
        return $api_output;
    }

    // private function not_found_report_generate($input_data)
    // {
    //     $report_data = [];


    //     $tmp_alias_data = "";

    //     $get_inspector_sql = "SELECT alias FROM " . tbl_usr . " WHERE usr_type='inspector' AND status='true' ORDER BY uid DESC";

    //     $get_inspector_query = mysqli_query($this->db_con, $get_inspector_sql) or (die($this->db_con));

    //     while ($get_inspector_arr = mysqli_fetch_assoc($get_inspector_query)) {
    //         $tmp_alias_data .= implode("','", explode(",", strtolower($get_inspector_arr["alias"]))) . "','";
    //     }
    //     $tmp_alias_data = substr($tmp_alias_data, 0, -3);


    //     $get_order_sql = "SELECT " . tbl_work_order . ".alias as report_alias, SUM(" . tbl_work_order . ".amount) as report_amount, SUM(" . tbl_price_list . ".amount) as report_price_amount FROM " . tbl_work_order . " LEFT JOIN " . tbl_price_list . " ON " . tbl_work_order . ".work_code=" . tbl_price_list . ".work_code AND " . tbl_work_order . ".zip_code=" . tbl_price_list . ".zip_code WHERE " . tbl_work_order . ".alias NOT IN ('" . $tmp_alias_data . "') AND DATE(completion_date) between '" . $input_data->usr_report_start_date . "' AND '" . $input_data->usr_report_end_date . "' GROUP BY " . tbl_work_order . ".alias";
    //     $get_order_query = mysqli_query($this->db_con, $get_order_sql) or (die($this->db_con));

    //     while ($get_order_arr = mysqli_fetch_assoc($get_order_query)) {
    //         $tmp_tot_amount = 0;
    //         $tmp_data = [
    //             "alias" => strtoupper($get_order_arr["report_alias"]),
    //         ];

    //         if ($get_order_arr["report_price_amount"] != null) {
    //             $tmp_tot_amount += $get_order_arr["report_price_amount"];
    //         }
    //         //$tmp_tot_amount += $get_order_arr["report_amount"];
    //         $tmp_data["amount"] = "$".number_format($tmp_tot_amount, 2, '.', ',');
    //         $tmp_data["dis_amount"] = $tmp_tot_amount;

    //         $report_data[] = $tmp_data;
    //     }


    //     return $report_data;
    // }

    private function not_found_report_generate($input_data)
    {
        $report_data = [];

        $tmp_alias_data = "";

        $get_inspector_sql = "SELECT alias FROM " . tbl_usr . " WHERE usr_type='inspector' AND status='true' ORDER BY uid DESC";
        $get_inspector_query = mysqli_query($this->db_con, $get_inspector_sql) or (die($this->db_con));
        while ($get_inspector_arr = mysqli_fetch_assoc($get_inspector_query)) {
            $tmp_alias_data .= implode("','", explode(",", strtolower($get_inspector_arr["alias"]))) . "','";
        }
        $tmp_alias_data = substr($tmp_alias_data, 0, -3); // Remove trailing "','"

        // Pagination inputs
        $page = $input_data->page ?? 1;
        $limit = 100;
        $offset = ($page - 1) * $limit;

        // Total count for pagination
        $count_sql = "
            SELECT COUNT(DISTINCT " . tbl_work_order . ".alias) as total
            FROM " . tbl_work_order . "
            LEFT JOIN " . tbl_price_list . "
            ON " . tbl_work_order . ".work_code = " . tbl_price_list . ".work_code
            AND " . tbl_work_order . ".zip_code = " . tbl_price_list . ".zip_code
            WHERE " . tbl_work_order . ".alias NOT IN ('" . $tmp_alias_data . "')
            AND DATE(completion_date) BETWEEN '" . $input_data->usr_report_start_date . "' AND '" . $input_data->usr_report_end_date . "'
        ";

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

        // Query with LIMIT & OFFSET
        $get_order_sql = "
            SELECT " . tbl_work_order . ".alias as report_alias,
                SUM(" . tbl_work_order . ".amount) as report_amount,
                SUM(" . tbl_price_list . ".amount) as report_price_amount
            FROM " . tbl_work_order . "
            LEFT JOIN " . tbl_price_list . "
            ON " . tbl_work_order . ".work_code = " . tbl_price_list . ".work_code
            AND " . tbl_work_order . ".zip_code = " . tbl_price_list . ".zip_code
            WHERE " . tbl_work_order . ".alias NOT IN ('" . $tmp_alias_data . "')
            AND DATE(completion_date) BETWEEN '" . $input_data->usr_report_start_date . "' AND '" . $input_data->usr_report_end_date . "'
            GROUP BY " . tbl_work_order . ".alias
            LIMIT $offset, $limit
        ";

        $get_order_query = mysqli_query($this->db_con, $get_order_sql) or (die($this->db_con));

        while ($get_order_arr = mysqli_fetch_assoc($get_order_query)) {
            $tmp_tot_amount = 0;
            $tmp_data = [
                "alias" => strtoupper($get_order_arr["report_alias"]),
            ];

            if ($get_order_arr["report_price_amount"] != null) {
                $tmp_tot_amount += $get_order_arr["report_price_amount"];
            }

            $tmp_data["amount"] = "$" . number_format($tmp_tot_amount, 2, '.', ',');
            $tmp_data["dis_amount"] = $tmp_tot_amount;

            $report_data[] = $tmp_data;
        }

        return $report_data;

        // Return with pagination info
        // return [
        //     "list" => $report_data,
        //     "pagination" => [
        //         "total_records" => $total_rows,
        //         "page_size" => $limit,
        //         "current_page" => $page,
        //         "total_pages" => ceil($total_rows / $limit)
        //     ]
        // ];
    }


    private function generate_not_found_report_pdf($file_title, $file_name, $file_data, $single_report)
    {


        $make_pdf = new MakePdf();
        $report_inspector_lst_data = "";
        $report_order_lst_data = "";
        $report_debit_credit_lst_data = "";
        $tmp_tot_amount = 0;

        if ($single_report) {
            $report_template = file_get_contents("lib/template/report/template_report_single.html");
        } else {
            $report_template = file_get_contents("lib/template/report/template_report_all.html");
        }

        if ($single_report) {
            foreach ($file_data["order_details"] as $single_order_data) {
                if($single_order_data["amount"] == "Not Found" || $single_order_data["amount"] == "Not Paid"){
                    $tmp_amount=strtoupper($single_order_data["amount"]);
                }else{
                    $tmp_amount="$".number_format((float)filter_var($single_order_data["amount"], FILTER_VALIDATE_FLOAT), 2, '.', ',');
                }

                $report_order_lst_data .= /** @lang text */
                    '<tr>
                       <td>' . $single_order_data["alias"] . '</td>
                       <td>' . $single_order_data["complete_date"] . '</td>
                       <td>' . $single_order_data["work_order_id"] . '</td>
                       <td>' . $single_order_data["work_code"] . '</td>
                       <td>' . strtoupper($single_order_data["address"]) . '</td>                       
                       <td>' . $tmp_amount . '</td>
                   </tr>';
            }

            $report_template = str_replace("{{order_lst}}", $report_order_lst_data, $report_template);
            $report_template = str_replace("{{sub_total_price}}", number_format((float)filter_var((($file_data["amount"] - $file_data["tot_credit"]) + $file_data["tot_debit"]), FILTER_VALIDATE_FLOAT), 2, '.', ','), $report_template);
            $report_template = str_replace("{{debit_credit_lst}}", "", $report_template);
            $report_template = str_replace("{{total_order}}", $file_data["tot_order"], $report_template);
            $tmp_tot_amount = $file_data["amount"];

        } else {
            foreach ($file_data as $single_inspector) {
                $report_inspector_lst_data .= /** @lang text */
                    '<tr>
                       <td>' . $single_inspector["alias"] . '</td>
                       <td>' . $single_inspector["amount"] . '</td>
                   </tr>';
                $tmp_tot_amount += $single_inspector["dis_amount"];
            }
            $report_template = str_replace("{{inspector_lst}}", $report_inspector_lst_data, $report_template);
            $report_template = str_replace("{{total_inspector}}", count($file_data), $report_template);
        }



        $report_template = str_replace("{{total_price}}", number_format((float)filter_var($tmp_tot_amount, FILTER_VALIDATE_FLOAT), 2, '.', ','), $report_template);
        $report_template = str_replace("{{order_name}}", ucwords($file_title), $report_template);


        return $make_pdf->create_pdf($file_name, $report_template);
    }
}