<?php

class Dashboard
{
    private $db_con;

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

    public function dashboard_generate($selectorID, $selectorValue): array
    {

        // ini_set('display_errors', 1);
        // ini_set('display_startup_errors', 1);
        // error_reporting(E_ALL);
        $profitableSelector = '';
        $unprofitableSelector = '';
        $output_message = new OutputMessage();
        

        $tmp_data = [
            "not_paid" => number_format($this->not_paid_total()),
            "paid_ytd" => "$" . $this->paid_ytd(),
            // "paid_count" => number_format($this->order_paid_count()),
            "not_found_count" => number_format($this->order_not_found_count()),
            // "paid" => "$" . $this->paid_last_fourteen_days(),
            "total_completed_orders" => number_format($this->get_total_completed_orders()),
        ];

        return $output_message->successOutput($tmp_data, 'Dashboard Loaded');
    }

    private function not_paid_total(){
        $not_paid_sql = "   SELECT  COUNT(a.id) as total
                            FROM    " . tbl_work_order . " a
                            WHERE   (a.check_date is null or a.check_date = '')
                                    AND YEAR( a.completion_date) = YEAR(NOW())";
        $not_paid_query = mysqli_query($this->db_con, $not_paid_sql);
        $not_paid_arr = mysqli_fetch_assoc($not_paid_query);

        return $not_paid_arr["total"];
    }

    private function order_paid_count(){
        $order_sql = "   SELECT  COUNT(a.id) as total
                            FROM    " . tbl_work_order . " a
                            WHERE   a.check_date is not null AND a.check_date != ''
                                    AND YEAR( a.completion_date) = YEAR(NOW())";
        $order_query = mysqli_query($this->db_con, $order_sql);
        $result = mysqli_fetch_assoc($order_query);

        return $result["total"];
    }

    private function order_not_found_count(){
        $order_sql = "   SELECT  COUNT(DISTINCT a.work_order_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   b.id IS NULL
                                    AND a.status = 'true'
                                    AND YEAR( a.completion_date) = YEAR(NOW())";
        $order_query = mysqli_query($this->db_con, $order_sql);
        $result = mysqli_fetch_assoc($order_query);

        return $result["total"];
    }

    private function paid_last_fourteen_days(){
        $total_paid_sql = " SELECT SUM(a.amount) AS total_amount 
                            FROM " . tbl_work_order . " a
                            WHERE (DATE(a.check_date) >= NOW() - INTERVAL 14 DAY)
                                   AND a.check_date != '' and a.check_date is not null
                                    AND a.status = 'true'";
        $total_paid_query = mysqli_query($this->db_con, $total_paid_sql);
        $total_paid_arr = mysqli_fetch_assoc($total_paid_query);

        return number_format($total_paid_arr['total_amount']);
    }

    private function paid_ytd(){
        $total_paid_sql = " SELECT SUM(a.amount) AS total_amount 
                            FROM " . tbl_work_order . " a
                            WHERE   YEAR(a.check_date) = YEAR(NOW())
                                   AND a.check_date != '' and a.check_date is not null
                                    AND a.status = 'true'";
        $total_paid_query = mysqli_query($this->db_con, $total_paid_sql);
        $total_paid_arr = mysqli_fetch_assoc($total_paid_query);

        return number_format($total_paid_arr['total_amount']);
    }

    private function get_total_completed_orders(){
        $work_order_sql = "SELECT count(a.id) as total
                           FROM    ".tbl_work_order." a
                           WHERE   YEAR(completion_date) = '".date('Y')."'
                                   AND a.status = 'true'
                                   AND a.check_date != '' and a.check_date is not null";
       $work_order_query = mysqli_query($this->db_con, $work_order_sql);
       $work_order_err = mysqli_fetch_assoc($work_order_query);
       return $work_order_err['total'];
    }

    public function yearly_profit(): array
    {
        $month_profit = [];
        $month_profit_sql = "   SELECT SUM(a.amount) AS profit, DATE_FORMAT(a.check_date, '%m/%d/%Y') as check_date
                                FROM cltpr_work_order_tbl a
                                WHERE   a.status = 'true'
                                        AND a.check_date IS NOT NULL
                                        AND a.amount > 0
                                GROUP BY a.check_date
                                ORDER BY a.check_date DESC
                                LIMIT 5;";

        $month_profit_query = mysqli_query($this->db_con, $month_profit_sql);

        while ($month_profit_arr = mysqli_fetch_assoc($month_profit_query)) {
            $month_profit[$month_profit_arr["check_date"]] = $month_profit_arr["profit"];
        }

        ksort($month_profit);
        
        $tmp_data = [
            "year_chart" => $month_profit
        ];
        return $tmp_data;
    }

    public function get_inspectors_profits(){
        $profits = $this->generate_reports_alias();

        $result = [
             "profits" => $profits
        ];
        return $result; 
    }

    public function get_top_5_profitable_work_code(){


        $top_5_profitable_alias = $this->generate_work_code_report("profitable");

        $result = [
             "top_5_profitable_work_code" => $top_5_profitable_alias
        ];

        return $result;
    }

    public function get_top_5_nonprofit_work_code()
    {
        $top_5_unprofitable_alias = $this->generate_work_code_report("unprofitable");

        $result = [
             "top_5_nonprofitable_work_code" => $top_5_unprofitable_alias
        ];
        return $result;
    }
    
    
    public function generate_reports_alias(): array
    {
        $inspectors = [];
        $inspector_sql = "  SELECT *
                            FROM " . tbl_usr . "
                            WHERE usr_type='inspector' AND status='true'
                            ORDER BY uid DESC";
        $inspector_query = mysqli_query($this->db_con, $inspector_sql) or (die($this->db_con));

        while ($inspector = mysqli_fetch_assoc($inspector_query)) {
            
            $usr_id = $inspector["uid"];
            $alias = implode("','", explode(",", strtolower($inspector["alias"])));

            $total_final_profit = 0;
            $total_profit = 0;
            $total_debit = 0;
            $total_credit = 0;

            //Orders
            $order_sql = "SELECT SUM(b.amount) as profit
                                FROM " . tbl_work_order . " a
                                JOIN " . tbl_price_list . " b ON a.work_code= b.work_code AND a.zip_code=b.zip_code
                                WHERE   a.alias IN ('$alias')
                                        AND (DATE(a.check_date) >= NOW() - INTERVAL 14 DAY)
                                        AND a.status='true' 
                                        AND b.status='true'
                                        AND a.check_date != '' AND a.check_date is not null
                                        AND a.amount > 0";

            $order_query = mysqli_query($this->db_con, $order_sql) or (die($this->db_con));
            $total_profit = mysqli_fetch_assoc($order_query)['profit'];
            
            
            //Calculator Debit/Credit
            $dc_sql = " SELECT * 
                            FROM " . tbl_debit_credit . "
                            WHERE   usr_id='$usr_id'
                                    AND status='true'
                                    AND (DATE(dc_date) >= NOW() - INTERVAL 14 DAY) 
                            ORDER BY dc_date ASC";
            $dc_query = mysqli_query($this->db_con, $dc_sql) or (die($this->db_con));

            while ($dc_arr = mysqli_fetch_assoc($dc_query)) {
                if ($dc_arr["dc_type"] == "credit") {
                    $total_credit += $dc_arr["dc_amount"];
                } else {
                    $total_debit += $dc_arr["dc_amount"];
                }
            }


            $total_final_profit = ($total_profit + $total_credit) - $total_debit;

            $inspector_data = [
                "name" => strtoupper($inspector["first_name"] . " " . $inspector["last_name"]),
                "total_profit" => $total_final_profit,
            ];

            $inspectors[] = $inspector_data;
        }


        $profits = [];

        usort($inspectors, function ($a, $b) {
            return $b['total_profit'] <=> $a['total_profit'];
        });


        $profits["profitable"] = array_slice(array_map(function ($alias, $data) {
            return [
                'total_profit' => $this->formatDollarAmount($data['total_profit']),
                'name' => $data['name']
            ];
        }, array_keys($inspectors), $inspectors), 0, 5);


        usort($inspectors, function ($a, $b) {
            return $a['total_profit'] <=> $b['total_profit'];
        });

        $profits["unprofitable"] = array_slice(array_map(function ($alias, $data) {
            return [
                'total_profit' => $this->formatDollarAmount($data['total_profit']),
                'name' => $data['name']
            ];
        }, array_keys($inspectors), $inspectors), 0, 5);


        return $profits;
    }


    public function generate_work_code_report($profitType): array
    {
        
        // ini_set('display_errors', 1);
        // ini_set('display_startup_errors', 1);
        // error_reporting(E_ALL);
        if($profitType === "profitable") {
            $order_sql = "SELECT SUM(a.amount - b.amount) as profit, a.work_code
                        FROM " . tbl_work_order . " a
                        JOIN " . tbl_price_list . " b ON a.work_code= b.work_code AND a.zip_code=b.zip_code
                        WHERE   a.status='true' && b.status = 'true'
                                AND a.check_date != '' AND a.check_date is not null
                                AND a.amount > 0
                                
                                AND YEAR( a.check_date) = YEAR(NOW())
                        Group by a.work_code
                        ORDER BY profit DESC
                        LIMIT 5;";
        } else {
            $order_sql = "SELECT SUM(a.amount - b.amount) as profit, a.work_code
                        FROM " . tbl_work_order . " a
                        JOIN " . tbl_price_list . " b ON a.work_code= b.work_code AND a.zip_code=b.zip_code
                        WHERE   a.status='true' && b.status = 'true'
                                AND a.check_date != '' AND a.check_date is not null
                                AND a.amount > 0
                                AND YEAR( a.check_date) = YEAR(NOW())
                        Group by a.work_code
                        ORDER BY profit ASC
                        LIMIT 5;";
        }

        $order_query = mysqli_query($this->db_con, $order_sql) or (die($this->db_con));

        $work_code_list = [];
        while ($work_code = mysqli_fetch_assoc($order_query)) {
            $work_code_list[] = [
                "work_code" => $work_code["work_code"],
                "profit" => $this->formatDollarAmount($work_code["profit"]),
            ];
        }

        return $work_code_list;
    }


    public function get_top_5_inspectors_completed_orders()
    {
        $work_order_sql = " SELECT   UPPER(CONCAT(b.first_name,' ', b.last_name)) as name, COUNT(a.id) as total_orders
                            FROM " . tbl_work_order . " a
                            JOIN " . tbl_usr . " b ON b.alias like CONCAT('%', a.alias, '%')
                            WHERE    b.usr_type = 'inspector'
                                    AND b.alias != ''
                                    AND b.status = 'true'
                                    AND a.status = 'true'
                                    AND YEAR(a.completion_date) = '" . date('Y') . "'
                            GROUP BY b.id
                            ORDER BY total_orders DESC
                            LIMIT 5;";

        $work_order_query = mysqli_query($this->db_con, $work_order_sql);
        $inspectors = [];
        while ($inspector = mysqli_fetch_assoc($work_order_query)) {
            $inspectors[] = [
                "name" => $inspector["name"],
                "total_orders" => number_format($inspector["total_orders"]),
            ];
        }

    
        return [
            "get_top_5_inspectors_completed_orders" => $inspectors
        ];
    }
    
    public function get_top_5_states(){

        $order_sql = "  SELECT  SUM(a.amount - b.amount) as profit, UPPER(a.state) as state
                        FROM " . tbl_work_order . " a
                        JOIN " . tbl_price_list . " b ON a.work_code= b.work_code AND a.zip_code=b.zip_code
                        WHERE   YEAR(a.check_date) = '" . date('Y') . "'
                                AND a.status='true'
                                AND a.check_date != '' AND a.check_date is not null
                                AND a.amount > 0
                        GROUP BY a.state
                        ORDER BY profit DESC
                        LIMIT 5;";

        $work_order_query = mysqli_query($this->db_con, $order_sql);
        $states = [];
        while ($state = mysqli_fetch_assoc($work_order_query)) {
            $states[] = [
                "state" => $state["state"],
                "profit" => $this->formatDollarAmount($state["profit"]),
            ];
        }

        $result = [
            "get_top_5_states" => $states
        ];
        return $result;
    }

    function formatDollarAmount($amount) {
        $absAmount = number_format(abs($amount), 2);
        return ($amount < 0 ? '-' : '') . '$' . $absAmount;
    }
}