<?php

namespace Salesloo\Admin;

use \Salesloo\Admin_Ajax;

/**
 * Admin Dashboard classes
 */
class Dashboard
{
    private $admin_ajax;

    private $table;

    private $args;

    /**
     * __construct
     *
     * @return void
     */
    public function __construct()
    {

        $this->admin_ajax = new Admin_Ajax;
    }

    /**
     * return dashboard template
     *
     * @return void
     */
    public static function template()
    {
        salesloo_hidding_wp_admin_notices();

        $x_data = [
            'filter' => [
                'product_id' => 'all',
                'daterange' => date('Y-m-d', strtotime('-30 day')) . ' - ' . date('Y-m-d')
            ],
            'loader' => '...',
        ];

        include salesloo_get_template('admin/dashboard.php');
        return;
    }

    /**
     * split_daterange
     *
     * @param  string $daterange
     * @param  bool $time
     * @return object
     */
    private function split_daterange($daterange, $time = false)
    {
        global $wpdb;

        if ($daterange) {
            $date = explode(' - ', $daterange);
            $date_start = date('Y-m-d', strtotime($date[0]));
            $date_end = isset($date[1]) ? date('Y-m-d', strtotime($date[1])) : date('Y-m-d', strtotime($date[0]));
        } else {
            $first_data = $wpdb->get_row("SELECT ID,created_at FROM {$wpdb->prefix}salesloo_invoice ORDER BY created_at ASC");
            $date_start = $first_data->created_at ? date('Y-m-d', strtotime($first_data->created_at)) : '2000-01-01';
            $date_end = date('Y-m-d');
        }

        return (object)[
            'start' => $time ? date('Y-m-d 00:00:00', strtotime($date_start)) : $date_start,
            'end' => $time ? date('Y-m-d 23:59:59', strtotime($date_end)) : $date_end
        ];
    }

    /**
     * ajax_get_stat
     *
     * @return void
     */
    public function ajax_get_stat()
    {
        global $wpdb;

        $this->admin_ajax->verify_nonce($_GET['nonce'], 'salesloo_get_stat');

        $daterange     = isset($_GET['date']) ? sanitize_text_field($_GET['date']) : '';
        $products = isset($_GET['product_id']) ? $_GET['product_id'] : 'all';

        $invoice_status = array_keys(salesloo_get_invoice_statuses());
        $invoice_status = implode('\',\'', $invoice_status);

        $invoice = $wpdb->prefix . 'salesloo_invoice';
        $invoice_order = $wpdb->prefix . 'salesloo_invoice_order';
        $order = $wpdb->prefix . 'salesloo_order';

        if ($products == 'all' || empty($products)) {
            $products = "SELECT ID FROM {$wpdb->prefix}salesloo_product";
        } else {
            $products = is_array($products) ? implode('\',\'', array_map('sanitize_text_field', $products)) : sanitize_text_field($products);
            $products = "'$products'";
        }

        $date = $this->split_daterange($daterange);

        $query =
            "SELECT
                COUNT(
                    DISTINCT CASE 
                    WHEN {$invoice}.status IN ('$invoice_status')
                    AND {$order}.product_id IN ($products)
                    AND DATE({$invoice}.created_at) BETWEEN '$date->start' AND '$date->end'
                    THEN {$invoice}.ID ELSE NULL END
                ) AS leads,
                COUNT(
                    DISTINCT CASE 
                    WHEN {$invoice}.status IN ('completed')
                    AND {$order}.product_id IN ($products)
                    AND DATE({$invoice}.created_at) BETWEEN '$date->start' AND '$date->end'
                    THEN {$invoice}.ID ELSE NULL END
                ) AS sales

            FROM $invoice 

            LEFT JOIN $invoice_order
                ON {$invoice}.ID = {$invoice_order}.invoice_id

            LEFT JOIN $order
                ON {$order}.ID = {$invoice_order}.order_id
                
            WHERE DATE({$invoice}.created_at) BETWEEN '$date->start' AND '$date->end'";

        $result = $wpdb->get_row($query);

        $conversion = 0;
        if (intval($result->leads) > 0) {
            $conversion = intval($result->sales) / intval($result->leads) * 100;
        }
        if (intval($result->sales) == 0) {
            $conversion = 0;
        }

        $result->conversion = intval($result->sales) == 0 ? '0%' : round($conversion, 2) . '%';
        $result->query = $query;

        wp_send_json($result);
    }

    public function monthly_query()
    {
        global $wpdb;

        [$daterange, $products, $invoice_status] = $this->args;
        [$invoice, $invoice_order, $order] = $this->table;

        $date = $this->split_daterange($daterange);

        $periode = new \DatePeriod(
            new \DateTime($date->start),
            new \DateInterval('P1M'),
            new \DateTime($date->end)
        );

        $query =
            "SELECT
                COUNT(
                    DISTINCT CASE 
                    WHEN {$invoice}.status IN ('$invoice_status')
                    AND {$order}.product_id IN ($products)
                    AND DATE({$invoice}.created_at) BETWEEN '$date->start' AND '$date->end'
                    THEN {$invoice}.ID ELSE NULL END
                ) AS leads,
                COUNT(
                    DISTINCT CASE 
                    WHEN {$invoice}.status IN ('completed')
                    AND {$order}.product_id IN ($products)
                    AND DATE({$invoice}.created_at) BETWEEN '$date->start' AND '$date->end'
                    THEN {$invoice}.ID ELSE NULL END
                ) AS sales,
                DATE_FORMAT({$invoice}.created_at, '%Y-%m') as date

            FROM $invoice 

            LEFT JOIN $invoice_order
                ON {$invoice}.ID = {$invoice_order}.invoice_id

            LEFT JOIN $order
                ON {$order}.ID = {$invoice_order}.order_id

            WHERE DATE({$invoice}.created_at) BETWEEN '$date->start' AND '$date->end'

            GROUP BY DATE_FORMAT({$invoice}.created_at, '%Y-%m')";

        $results = $wpdb->get_results($query);

        $data = [];
        foreach ($results as $result) {
            $data[$result->date] = $result;
        }

        $categories = [];
        $leads = [];
        $sales = [];
        $conversions = [];
        foreach ($periode as $key => $value) {
            $categories[] = $value->format('Y/m');

            $date_key = $value->format('Y-m');
            $lead = 0;
            $sale = 0;
            $convert = 0;

            if (isset($data[$date_key])) {
                $lead = $data[$date_key]->leads ? intval($data[$date_key]->leads) : $lead;
                $sale = $data[$date_key]->sales ? intval($data[$date_key]->sales) : $sale;
            }

            $convert = intval($sale) / intval($lead) * 100;

            $leads[] = $lead;
            $sales[] = $sale;
            $conversions[] = intval($sale) == 0 ? 0 : round($convert, 2);
        }

        return (object)[
            'leads' => $leads,
            'sales' => $sales,
            'conversions' => $conversions,
            'categories' => $categories
        ];
    }

    private function daily_query()
    {
        global $wpdb;

        [$daterange, $products, $invoice_status] = $this->args;
        [$invoice, $invoice_order, $order] = $this->table;

        $date = $this->split_daterange($daterange);

        $periode = new \DatePeriod(
            new \DateTime($date->start),
            new \DateInterval('P1D'),
            new \DateTime($date->end)
        );

        $query =
            "SELECT
                COUNT(
                    DISTINCT CASE 
                    WHEN {$invoice}.status IN ('$invoice_status')
                    AND {$order}.product_id IN ($products)
                    AND DATE({$invoice}.created_at) BETWEEN '$date->start' AND '$date->end'
                    THEN {$invoice}.ID ELSE NULL END
                ) AS leads,
                COUNT(
                    DISTINCT CASE 
                    WHEN {$invoice}.status IN ('completed')
                    AND {$order}.product_id IN ($products)
                    AND DATE({$invoice}.created_at) BETWEEN '$date->start' AND '$date->end'
                    THEN {$invoice}.ID ELSE NULL END
                ) AS sales,
                DATE({$invoice}.created_at) as date

            FROM $invoice 

            LEFT JOIN $invoice_order
                ON {$invoice}.ID = {$invoice_order}.invoice_id

            LEFT JOIN $order
                ON {$order}.ID = {$invoice_order}.order_id

            WHERE DATE({$invoice}.created_at) BETWEEN '$date->start' AND '$date->end'

            GROUP BY DATE({$invoice}.created_at)";

        $results = $wpdb->get_results($query);

        $data = [];
        foreach ($results as $result) {
            $data[$result->date] = $result;
        }

        $categories = [];
        $leads = [];
        $sales = [];
        $conversions = [];
        foreach ($periode as $key => $value) {
            $categories[] = $value->format('d/m/y');

            $date_key = $value->format('Y-m-d');
            $lead = 0;
            $sale = 0;
            $convert = 0;

            if (isset($data[$date_key])) {
                $lead = $data[$date_key]->leads ? intval($data[$date_key]->leads) : $lead;
                $sale = $data[$date_key]->sales ? intval($data[$date_key]->sales) : $sale;
            }

            $convert = 0;

            if (intval($lead) > 0) {
                $convert = intval($sale) / intval($lead) * 100;
            }

            if (intval($sale) == 0) {
                $convert = 0;
            }

            $leads[] = $lead;
            $sales[] = $sale;
            $conversions[] = intval($sale) == 0 ? 0 : round($convert, 2);
        }

        return (object)[
            'leads' => $leads,
            'sales' => $sales,
            'conversions' => $conversions,
            'categories' => $categories
        ];
    }

    /**
     * hourly_query
     *
     * @return void
     */
    private function hourly_query()
    {
        global $wpdb;

        [$daterange, $products, $invoice_status] = $this->args;
        [$invoice, $invoice_order, $order] = $this->table;

        $date = $this->split_daterange($daterange, true);

        $periode = [];
        $startdate = $date->start;
        $enddate = date('Y-m-d 23:00:00', strtotime($date->end));
        $periode[] = $startdate;
        while ($startdate < $enddate) {
            $startdate = strtotime($startdate) + 3600;
            $startdate = date('Y-m-d H:i:s', $startdate);
            $periode[] = $startdate;
        }

        $query =
            "SELECT
                COUNT(
                    DISTINCT CASE 
                    WHEN {$invoice}.status IN ('{$invoice_status}')
                    AND {$order}.product_id IN ($products)
                    AND {$invoice}.created_at BETWEEN '$date->start' AND '$date->end'
                    THEN {$invoice}.ID ELSE NULL END
                ) AS leads,
                COUNT(
                    DISTINCT CASE 
                    WHEN {$invoice}.status IN ('completed')
                    AND {$order}.product_id IN ($products)
                    AND {$invoice}.created_at BETWEEN '$date->start' AND '$date->end'
                    THEN {$invoice}.ID ELSE NULL END
                ) AS sales,
                DATE_FORMAT({$invoice}.created_at, '%Y-%m-%d %H:00:00') as date

            FROM $invoice 

            LEFT JOIN $invoice_order
                ON {$invoice}.ID = {$invoice_order}.invoice_id

            LEFT JOIN $order
                ON {$order}.ID = {$invoice_order}.order_id

            WHERE {$invoice}.created_at BETWEEN '$date->start' AND '$date->end'

            GROUP BY DATE_FORMAT({$invoice}.created_at, '%Y-%m-%d %H:00:00')";

        $results = $wpdb->get_results($query);

        $data = [];
        foreach ($results as $result) {
            $data[$result->date] = $result;
        }

        $categories = [];
        $leads = [];
        $sales = [];
        $conversions = [];
        foreach ($periode as $key => $value) {
            $categories[] = date('y/m/d H:i', strtotime($value));

            $date_key = $value;
            $lead = 0;
            $sale = 0;
            $convert = 0;

            if (isset($data[$date_key])) {
                $lead = $data[$date_key]->leads ? intval($data[$date_key]->leads) : $lead;
                $sale = $data[$date_key]->sales ? intval($data[$date_key]->sales) : $sale;
            }

            $convert = intval($sale) / intval($lead) * 100;

            $leads[] = $lead;
            $sales[] = $sale;
            $conversions[] = intval($sale) == 0 ? 0 : round($convert, 2);
        }

        return (object)[
            'leads' => $leads,
            'sales' => $sales,
            'conversions' => $conversions,
            'categories' => $categories
        ];
    }

    public function ajax_get_stat_chart()
    {
        global $wpdb;

        $this->admin_ajax->verify_nonce($_GET['nonce'], 'salesloo_get_stat_chart');

        $daterange     = isset($_GET['date']) ? sanitize_text_field($_GET['date']) : '';
        $products = isset($_GET['product_id']) ? $_GET['product_id'] : 'all';

        $invoice_status = array_keys(salesloo_get_invoice_statuses());
        $invoice_status = implode('\',\'', $invoice_status);

        if ($products == 'all' || empty($products)) {
            $products = "SELECT ID FROM {$wpdb->prefix}salesloo_product";
        } else {
            $products = is_array($products) ? implode('\',\'', array_map('sanitize_text_field', $products)) : sanitize_text_field($products);
            $products = "'$products'";
        }

        $this->table = [
            $wpdb->prefix . 'salesloo_invoice',
            $wpdb->prefix . 'salesloo_invoice_order',
            $wpdb->prefix . 'salesloo_order'
        ];

        $this->args = [
            $daterange,
            $products,
            $invoice_status
        ];

        $date = $this->split_daterange($daterange);

        $date1 = date_create($date->start);
        $date2 = date_create($date->end);
        $diff  = date_diff($date1, $date2);
        $month = intval($diff->format('%m'));

        if ($month > 3) {
            $results = $this->monthly_query();
        } else {
            $day = intval($diff->format('%a'));
            if ($day > 3) {
                $results = $this->daily_query();
            } else {
                $results = $this->hourly_query();
            }
        }

        $number = wp_parse_args($results->leads, $results->sales);
        $higher_number = max($number);

        $res = [
            'series' => [
                [
                    'name' => 'Leads',
                    'type' => 'area',
                    'data' => $results->leads
                ],
                [
                    'name' => 'Sales',
                    'type' => 'area',
                    'data' => $results->sales
                ],
                [
                    'name' => 'Conversion',
                    'type' => 'column',
                    'data' => $results->conversions
                ]
            ],
            'xaxis' => [
                'categories' => $results->categories,
                'title' => [
                    'text' => ''
                ]
            ],
            'maxnumber' => $higher_number
        ];

        wp_send_json($res);
    }

    public function ajax_get_income()
    {
        global $wpdb;

        $this->admin_ajax->verify_nonce($_GET['nonce'], 'salesloo_get_income');

        $daterange     = isset($_GET['date']) ? sanitize_text_field($_GET['date']) : '';
        $products = isset($_GET['product_id']) ? $_GET['product_id'] : 'all';

        $invoice = $wpdb->prefix . 'salesloo_invoice';
        $invoice_order = $wpdb->prefix . 'salesloo_invoice_order';
        $order = $wpdb->prefix . 'salesloo_order';
        $commission = $wpdb->prefix . 'salesloo_commission';

        if ($products == 'all' || empty($products)) {
            $products = "SELECT ID FROM {$wpdb->prefix}salesloo_product";
        } else {
            $products = is_array($products) ? implode('\',\'', array_map('sanitize_text_field', $products)) : sanitize_text_field($products);
            $products = "'$products'";
        }

        $date = $this->split_daterange($daterange);

        $query =
            "SELECT
                SUM(
                    CASE 
                    WHEN {$invoice}.status IN ('completed')
                    AND {$order}.product_id IN ($products)
                    AND DATE({$invoice}.created_at) BETWEEN '$date->start' AND '$date->end'
                    THEN {$invoice}.total ELSE 0 END
                ) AS revenue

            FROM $invoice

            LEFT JOIN $invoice_order
                ON {$invoice}.ID = {$invoice_order}.invoice_id

            LEFT JOIN $order
                ON {$order}.ID = {$invoice_order}.order_id
            
            WHERE DATE({$invoice}.created_at) BETWEEN '$date->start' AND '$date->end'";

        $result_income = $wpdb->get_row($query);

        $query =
            "SELECT
                SUM(
                    CASE 
                    WHEN {$commission}.status IN ('paid', 'unpaid')
                    AND {$commission}.product_id IN ($products)
                    AND DATE({$commission}.created_at) BETWEEN '$date->start' AND '$date->end'
                    THEN {$commission}.amount ELSE 0 END
                ) AS total

            FROM $commission
            
            WHERE DATE({$commission}.created_at) BETWEEN '$date->start' AND '$date->end'";

        $result_commission = $wpdb->get_row($query);

        $revenue = $result_income->revenue;
        $commision = $result_commission->total;
        $profit = intval($result_income->revenue) - intval($result_commission->total);

        $result = [
            'revenue' => salesloo_convert_money($revenue),
            'commission' => salesloo_convert_money($commision),
            'profit' => salesloo_convert_money($profit)
        ];

        wp_send_json($result);
    }


    public function install_hooks()
    {
        add_action('wp_ajax_salesloo_get_stat', [$this, 'ajax_get_stat']);
        add_action('wp_ajax_salesloo_get_stat_chart', [$this, 'ajax_get_stat_chart']);
        add_action('wp_ajax_salesloo_get_income', [$this, 'ajax_get_income']);
    }
}
