<?php

namespace Salesloo\Admin;

use \Salesloo\Admin_Ajax;

/**
 * Admin Affiliates classes
 */
class Affiliate_Leaderboard
{
    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/affiliate-leaderboard.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
        ];
    }

    public function ajax_get_affiliates_rank()
    {
        global $wpdb;

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

        $daterange = isset($_GET['date']) ? sanitize_text_field($_GET['date']) : '';
        $products  = isset($_GET['product_id']) ? $_GET['product_id'] : 'all';
        $user_id   = isset($_GET['user_id']) ? $_GET['user_id'] : 'all';
        $per_page  = 20;
        $paged     = isset($_GET['paged']) ? intval($_GET['paged']) : 1;
        $offset    = ($paged - 1) * $per_page;


        $commission = $wpdb->prefix . 'salesloo_commission';
        $user = $wpdb->users;

        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'";
        }

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

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

        $query =
            "SELECT SQL_CALC_FOUND_ROWS
                {$user}.ID as user_id,
                {$user}.user_login as username,
                COUNT(
                    CASE
                    WHEN status IN ('pending', 'unpaid', 'paid', 'cancelled', 'refunded')
                    AND {$commission}.product_id IN ($products)
                    AND DATE({$commission}.created_at) BETWEEN '$date->start' AND '$date->end'
                    THEN 1 ELSE NULL END
                ) AS leads,
                COUNT(
                    CASE
                    WHEN status IN ('unpaid', 'paid')
                    AND {$commission}.product_id IN ($products)
                    AND DATE({$commission}.created_at) BETWEEN '$date->start' AND '$date->end'
                    THEN 1 ELSE NULL END
                ) AS sales,
                ROW_NUMBER() OVER(ORDER BY COUNT(
                    CASE
                    WHEN status IN ('unpaid', 'paid')
                    AND {$commission}.product_id IN ($products)
                    AND DATE({$commission}.created_at) BETWEEN '$date->start' AND '$date->end'
                    THEN 1 ELSE NULL END
                ) DESC, COUNT(
                    CASE
                    WHEN status IN ('pending', 'unpaid', 'paid', 'cancelled', 'refunded')
                    AND {$commission}.product_id IN ($products)
                    AND DATE({$commission}.created_at) BETWEEN '$date->start' AND '$date->end'
                    THEN 1 ELSE NULL END
                ) DESC) AS ranking,
                JSON_OBJECT(
                    'total',
                        SUM(
                            CASE
                            WHEN status IN ('paid', 'unpaid')
                            AND {$commission}.product_id IN ($products)
                            AND DATE({$commission}.created_at) BETWEEN '$date->start' AND '$date->end'
                            THEN amount ELSE 0 END
                        ),
                    'pending',
                        SUM(
                            CASE
                            WHEN status = 'pending'
                            AND {$commission}.product_id IN ($products)
                            AND DATE({$commission}.created_at) BETWEEN '$date->start' AND '$date->end'
                            THEN amount ELSE 0 END
                        ),
                    'paid',
                        SUM(
                            CASE
                            WHEN status = 'paid'
                            AND {$commission}.product_id IN ($products)
                            AND DATE({$commission}.created_at) BETWEEN '$date->start' AND '$date->end'
                            THEN amount ELSE 0 END
                        ),
                    'unpaid',
                        SUM(
                            CASE
                            WHEN status = 'unpaid'
                            AND {$commission}.product_id IN ($products)
                            AND DATE({$commission}.created_at) BETWEEN '$date->start' AND '$date->end'
                            THEN amount ELSE 0 END
                        ),
                    'cancelled',
                        SUM(
                            CASE
                            WHEN status = 'cancelled'
                            AND {$commission}.product_id IN ($products)
                            AND DATE({$commission}.created_at) BETWEEN '$date->start' AND '$date->end'
                            THEN amount ELSE 0 END
                        )
                ) AS commission

            FROM $user

            LEFT JOIN $commission
                ON {$user}.ID = {$commission}.user_id
            
            WHERE {$user}.ID IN($user_ids)

            GROUP BY {$user}.ID

            ORDER BY sales DESC, leads DESC

            LIMIT %d OFFSET %d";

        $results = $wpdb->get_results($wpdb->prepare($query, $per_page, $offset));
        $found = $wpdb->get_var('SELECT FOUND_ROWS()');

        $items = [];
        foreach ($results as $item) {
            $item->name = salesloo_user_get_name($item->user_id);

            $avatar = get_user_meta($item->user_id, 'avatar', true);
            if (empty($avatar)) {
                $avatar = get_avatar_url($item->user_id);
            }

            if (empty($avatar)) {
                $avatar = 'https://ui-avatars.com/api/?name=' . urlencode($item->name);
            }

            $item->avatar = $avatar;

            $rank_attribute = '';
            $rank_class = 'text-xl';
            $bg_class = 'bg-gradient-to-r from-gray-600 to-gray-300';
            if ($item->ranking == 1) {
                $rank_attribute = 'st';
                $rank_class = 'text-4xl';
                $bg_class = 'bg-gradient-to-r from-orange-400 to-orange-300';
            }
            if ($item->ranking == 2) {
                $rank_attribute = 'nd';
                $rank_class = 'text-3xl';
                $bg_class = 'bg-gradient-to-r from-green-600 to-green-300';
            }
            if ($item->ranking == 3) {
                $rank_attribute = 'rd';
                $rank_class = 'text-2xl';
                $bg_class = 'bg-gradient-to-r from-blue-600 to-blue-300';
            }

            $item->rank = (object)[
                'number' => $item->ranking,
                'att' => $rank_attribute,
                'class' => $rank_class
            ];

            $item->bg = (object)[
                'class' => $bg_class
            ];

            $commission = (object) json_decode($item->commission);

            $item->commission = (object) [
                'unpaid' => salesloo_convert_money($commission->unpaid),
                'paid' => salesloo_convert_money($commission->paid),
                'total' => salesloo_convert_money($commission->total)
            ];


            $items[] = $item;
        }

        $data = [
            'items'    => $items,
            'total'    => $found,
            'paged'    => $paged,
            'pages'    => ceil($found / $per_page),
            'per_page' => $per_page,
        ];

        wp_send_json($data);
    }


    public function install_hooks()
    {
        add_action('wp_ajax_salesloo_get_affiliates_rank', [$this, 'ajax_get_affiliates_rank']);
        // add_action('wp_ajax_salesloo_get_income', [$this, 'ajax_get_income']);
    }
}
