<?php

namespace KupShop\AdminBundle\Util\Filter;

use KupShop\AdminBundle\Query\Invert;
use KupShop\KupShopBundle\Util\DateUtil;
use Query\Operator;
use Query\QueryBuilder;

class OrdersFilterSpecs
{
    use \DatabaseCommunication;

    /**
     * @param array $filter
     * @param array $specs  default implicit specs
     *
     * @return callable
     */
    public function getSpecs($filter = [], $specs = [])
    {
        $filter = OrdersFilter::cleanFilter($filter);

        foreach ($filter as $key => $item) {
            $specName = 'spec_'.$key;
            $invert = isset($filter[$key.'_invert']);
            if (method_exists($this, $specName)) {
                $spec = $this->$specName($item);
                if ($key == 'reg_users') {
                    $specs[] = Invert::checkInvert($spec, $invert, 'o.id_user');
                } else {
                    $specs[] = Invert::checkInvert($spec, $invert);
                }
            }
        }

        if ($specs) {
            return Operator::andX($specs);
        }

        return null;
    }

    protected function spec_statuses(array $statuses)
    {
        return Operator::inIntArray($statuses, 'o.status');
    }

    protected function spec_pos(array $posIds)
    {
        return function (QueryBuilder $qb) use ($posIds) {
            $qb->leftJoin('o', 'pos', 'po', 'o.pos = po.id');

            return Operator::inIntArray($posIds, 'po.id');
        };
    }

    protected function spec_storno(array $storno)
    {
        return Operator::inIntArray($storno, 'o.status_storno');
    }

    protected function spec_state(array $states)
    {
        return Operator::orX(
            Operator::inStringArray($states, 'o.invoice_country'),
            Operator::inStringArray($states, 'o.delivery_country')
        );
    }

    protected function spec_currency(array $currency)
    {
        return Operator::inStringArray($currency, 'o.currency');
    }

    protected function spec_flags(array $flags)
    {
        return Operator::findInSet($flags, 'o.flags', 'OR');
    }

    protected function spec_reg_users(array $users)
    {
        return Operator::inIntArray($users, 'o.id_user');
    }

    protected function spec_users_groups(array $users_groups)
    {
        return function (QueryBuilder $qb) use ($users_groups) {
            return Operator::exists(sqlQueryBuilder()->select('ugr.id_user')
                ->from('users_groups_relations', 'ugr')
                ->where(Operator::inIntArray($users_groups, 'ugr.id_group'))
                ->andWhere('ugr.id_user = o.id_user'));
        };
    }

    protected function spec_first_bought_by_email()
    {
        return function (QueryBuilder $qb) {
            $first_bought_orders = sqlQueryBuilder()->select('MIN(ofb.id) as id')
                ->from('orders', 'ofb')
                ->andWhere(Operator::isNotNull('ofb.invoice_email'))
                ->groupBy('ofb.invoice_email');
            $qb->andWhere(Operator::inSubQuery('o.id', $first_bought_orders));
        };
    }

    protected function spec_first_bought()
    {
        return function (QueryBuilder $qb) {
            $first_bought_orders = sqlQueryBuilder()->select('MIN(ofb.id) as id')
                ->from('orders', 'ofb')
                ->andWhere(Operator::isNotNull('ofb.id_user'))
                ->groupBy('ofb.id_user');
            $qb->andWhere(Operator::inSubQuery('o.id', $first_bought_orders));
        };
    }

    protected function spec_users_registered(array $users_registered)
    {
        return function (QueryBuilder $qb) use ($users_registered) {
            $specs = [];
            foreach ($users_registered as $registered) {
                if ($registered) {
                    $specs[] = 'o.id_user IS NOT NULL';
                } else {
                    $specs[] = 'o.id_user IS NULL';
                }
            }

            return join(' OR ', $specs);
        };
    }

    protected function spec_has_ico()
    {
        return Operator::not(Operator::equals(['o.invoice_ico' => '']));
    }

    protected function spec_has_dic()
    {
        return Operator::not(Operator::equals(['o.invoice_dic' => '']));
    }

    protected function spec_ico_dic($ico_dic)
    {
        if (!empty($ico_dic)) {
            return Operator::equals(['o.invoice_ico' => $ico_dic, 'o.invoice_dic' => $ico_dic], 'OR');
        }
    }

    protected function spec_deliveries(array $deliveries)
    {
        return function (QueryBuilder $qb) use ($deliveries) {
            $qb->leftJoin('o', 'delivery_type', 'dt', 'dt.id = o.id_delivery');

            return Operator::inIntArray($deliveries, 'dt.id_delivery');
        };
    }

    protected function spec_payed(array $payed)
    {
        return Operator::inIntArray($payed, 'o.status_payed');
    }

    protected function spec_payments(array $payments)
    {
        return function (QueryBuilder $qb) use ($payments) {
            $qb->leftJoin('o', 'delivery_type', 'dt', 'dt.id = o.id_delivery');

            return Operator::inIntArray($payments, 'dt.id_payment');
        };
    }

    protected function spec_date_created(array $date_created)
    {
        return Operator::between(
            'o.date_created',
            $this->getDateRange($date_created)
        );
    }

    protected function spec_languages(array $languages)
    {
        return function (QueryBuilder $qb) use ($languages) {
            $qb->andWhere(Operator::inStringArray((array) $languages, 'o.id_language'));
        };
    }

    protected function spec_date_handled(array $date_handled)
    {
        return Operator::between(
            'o.date_handle',
            $this->getDateRange($date_handled)
        );
    }

    protected function spec_order_discount(array $id_order_discounts)
    {
        return function (QueryBuilder $qb) use ($id_order_discounts) {
            // filter using subquery to avoid duplicates caused by same discount
            // being applied to multiple items in the same order
            // https://klient.wpj.cz/service/task/11271/change/
            return Operator::exists(sqlQueryBuilder()->select('id_order')
                ->from('order_discounts_orders', 'odo')
                ->where(Operator::inIntArray($id_order_discounts, 'id_order_discount'))
                ->andWhere('odo.id_order = o.id'));
        };
    }

    protected function spec_sellers(array $sellers): callable
    {
        return function (QueryBuilder $qb) use ($sellers) {
            $qb->leftJoin('o', 'order_sellers', 'os', 'o.id = os.id_order');

            return Operator::inIntArray($sellers, 'os.id_seller');
        };
    }

    protected function spec_source(array $sources)
    {
        return Operator::inStringArray($sources, 'source');
    }

    protected function spec_property(array $properties)
    {
        $specs = [];
        foreach ($properties as $property) {
            $specName = 'spec_'.$property;
            if (method_exists($this, $specName)) {
                $specs[] = $this->$specName();
            }
        }

        return Operator::andX($specs);
    }

    protected function spec_dropship(array $dropshipIds): ?callable
    {
        if (!findModule(\Modules::DROPSHIP)) {
            return null;
        }

        return function (QueryBuilder $qb) use ($dropshipIds) {
            $qb->leftJoin('o', 'order_dropshipment', 'odps', 'odps.id_order = o.id');

            return Operator::inIntArray($dropshipIds, 'odps.id_dropshipment');
        };
    }

    protected function spec_salesman(array $salesman): ?callable
    {
        if (!findModule(\Modules::SALESMAN)) {
            return null;
        }

        return Operator::exists(
            sqlQueryBuilder()
                ->select('1')
                ->from('users', 'u')
                ->where('u.id = o.id_user')
                ->andWhere(Operator::inIntArray($salesman, 'u.id_salesman'))
        );
    }

    protected function spec_products(array $productIds): callable
    {
        $productFilterIds = [];
        $variationsFilterIds = [];

        foreach ($productIds as $productId) {
            $parts = explode('-', $productId);
            if (empty($parts[1])) {
                !empty($parts[0]) && ($productFilterIds[] = $parts[0]);
            } else {
                $variationsFilterIds[] = $parts[1];
            }
        }

        $existsOrderItems = fn ($field, $ids) => Operator::exists(
            sqlQueryBuilder()
                ->select('1')
                ->from('order_items', 'oi2')
                ->where('oi2.id_order = o.id')
                ->andWhere(Operator::inIntArray($ids, $field)));

        if (empty($variationsFilterIds)) {
            return $existsOrderItems('oi2.id_product', $productFilterIds);
        } else {
            return Operator::orX(
                $existsOrderItems('oi2.id_product', $productFilterIds),
                $existsOrderItems('oi2.id_variation', $variationsFilterIds)
            );
        }
    }

    private function getDateRange($date)
    {
        if ($date['relative'] ?? null) {
            $from = empty($date['offset_from']) ? '' : $this->prepareDate(DateUtil::getNowWithOffset($date['offset_from'])).' 00:00:00';
            $to = empty($date['offset_to']) ? '' : $this->prepareDate(DateUtil::getNowWithOffset($date['offset_to'])).' 23:59:59';
        } elseif ($date['from'] ?? null || $date_created['to'] ?? null) {
            $from = empty($date['from']) ? '' : $this->prepareDate($date['from']).' 00:00:00';
            $to = empty($date['to']) ? '' : $this->prepareDate($date['to']).' 23:59:59';
        }

        return new \Range(empty($from) ? '' : $from.' 00:00:00', empty($to) ? '' : $to.' 23:59:59');
    }

    protected function spec_batch($batch): ?callable
    {
        if (!findModule(\Modules::PRODUCTS_BATCHES) || !findModule(\Modules::WAREHOUSE) || !$batch) {
            return null;
        }

        return function (QueryBuilder $qb) use ($batch) {
            $qb->leftJoin('o', 'warehouse_orders', 'wo', 'wo.id_order=o.id')
                ->setParameter('batch', json_encode($batch));

            return "JSON_CONTAINS(
                    JSON_EXTRACT(wo.state, '$.checkouts[*].productBatch.code'),
                    :batch)";
        };
    }
}
