<?php

declare(strict_types=1);

namespace ACA\WC\Search\User\ShopOrder;

use ACP\Query\Bindings;
use ACP\Search\Comparison;
use ACP\Search\Operators;
use ACP\Search\Value;

class OrderCount extends Comparison
{

    protected array $status;

    public function __construct(array $stati = [])
    {
        $operators = new Operators([
            Operators::EQ,
            Operators::GT,
            Operators::LT,
            Operators::IS_EMPTY,
            Operators::BETWEEN,
        ]);

        $this->status = $stati;

        parent::__construct($operators, Value::INT);
    }

    protected function create_query_bindings(string $operator, Value $value): Bindings
    {
        global $wpdb;

        $bindings = new Bindings();

        if (Operators::IS_EMPTY === $operator || (Operators::EQ === $operator && 0 === (int)$value->get_value())) {
            return $bindings->where(
                $wpdb->users . ".ID NOT IN( SELECT meta_value FROM wp_postmeta WHERE meta_key = '_customer_user' )"
            );
        }

        switch ($operator) {
            case Operators::LT;
                $having = sprintf('HAVING orders < %d', (int)$value->get_value());
                break;
            case Operators::GT;
                $having = sprintf('HAVING orders > %d', (int)$value->get_value());
                break;
            case Operators::BETWEEN:
                $values = $value->get_value();
                $having = sprintf('HAVING orders >= %d AND orders <= %s', (int)$values[0], (string)$values[1]);

                break;
            default:
                $having = sprintf('HAVING orders = %d', (int)$value->get_value());
        }

        $where = $this->status
            ? sprintf("AND p.post_status IN ( '%s' )", implode("','", array_map('esc_sql', $this->status)))
            : '';

        $sql = "SELECT pm.meta_value as user_id, count(*) as orders
				FROM $wpdb->posts as p
				INNER JOIN $wpdb->postmeta as pm 
					ON p.ID = pm.post_id
				WHERE p.post_type = 'shop_order'
					AND pm.meta_key = '_customer_user'
					AND p.post_status <> 'trash'
					AND p.post_status <> 'auto-draft'
					$where
				GROUP BY pm.meta_value
				$having
				";

        $user_ids = $wpdb->get_col($sql);

        if (empty($user_ids)) {
            $user_ids = [0];
        }

        $user_ids = array_filter($user_ids, 'is_numeric');
        $where = $wpdb->users . '.ID IN( ' . implode(',', $user_ids) . ')';

        return $bindings->where($where);
    }

}