<?php

declare(strict_types=1);

namespace KupShop\PreordersBundle\Admin\lists;

use KupShop\AdminBundle\AdminList\BaseList;
use Query\Operator;
use Query\QueryBuilder;

class PreordersItemsList extends BaseList
{
    protected $tableName = 'preorders_items';
    protected ?string $tableAlias = 'pi';

    protected $tableDef = [
        'id' => 'id',
        'fields' => [
            'Název' => ['field' => 'title', 'size' => 3, 'type' => 'products', 'spec' => 'p.title, p.id AS id'],
            'Varianta' => ['field' => 'variation_title', 'spec' => 'COALESCE(pv.title, "Bez varianty") AS variation_title'],
            'Kód' => ['field' => 'code', 'spec' => 'COALESCE(pv.code, p.code) AS code'],
            'EAN' => ['field' => 'ean', 'spec' => 'COALESCE(pv.ean, p.ean) AS ean'],
            'Číslo produktu' => ['field' => 'p.id'],

            'Celkem (ks)' => ['field' => 'pieces', 'spec' => 'SUM(pi.pieces) AS pieces'],
            'Zbývá odeslat (ks)' => ['field' => 'pieces_remaining', 'spec' => 'SUM(pi.pieces - pi.pieces_sent) AS pieces_remaining'],
            'Odesláno (ks)' => ['field' => 'pieces_sent', 'spec' => 'SUM(pi.pieces_sent) AS pieces_sent'],

            'Průměrná cena za ks' => ['field' => 'average_price', 'spec' => 'AVG(pi.piece_price * currencies.rate) AS average_price', 'render' => 'renderPrice'],
            'Celková cena' => ['field' => 'total_price', 'spec' => 'SUM(pi.piece_price * pi.pieces * currencies.rate) AS total_price', 'render' => 'renderPrice'],

            'Produktová cena (bez DPH)' => ['field' => 'product_price', 'spec' => 'COALESCE(pv.price, p.price) AS product_price', 'render' => 'renderPrice'],
            'Rozdíl předobjednávkové a produktové ceny' => ['field' => 'price_diff', 'spec' => '(AVG(pi.piece_price * currencies.rate) - COALESCE(pv.price, p.price)) AS price_diff', 'render' => 'renderPrice'],
            'Na skladě' => ['field' => 'in_store', 'spec' => 'COALESCE(pv.in_store, p.in_store) AS in_store'],
        ],
    ];

    public function getQuery(): QueryBuilder
    {
        $qb = sqlQueryBuilder()
            ->from($this->tableName, $this->tableAlias)
            ->leftJoin('pi', 'products', 'p', 'pi.id_product = p.id')
            ->leftJoin('pi', 'products_variations', 'pv', 'pi.id_variation = pv.id')
            ->leftJoin('pi', 'currencies', 'currencies', 'pi.currency = currencies.id')
            ->leftJoin('pi', 'preorders_dates', 'pd', 'pi.id_preorder_date = pd.id')
            ->addGroupBy('pi.id_product')
            ->addGroupBy('pi.id_variation');

        $qb->andWhere($this->getFilterSpec());

        return $qb;
    }

    private function getFilterSpec(): \Closure
    {
        return function (QueryBuilder $qb) {
            $equals = [];

            if ($preorder = getVal('id_preorder')) {
                $equals[] = Operator::inIntArray($preorder, 'pd.id_preorder');
            }

            if ($date = getVal('id_preorder_date')) {
                $equals[] = Operator::inIntArray($date, 'pi.id_preorder_date');
            }

            if ($user = getVal('id_user')) {
                $equals[] = Operator::inIntArray($user, 'pi.id_user');
            }

            if (getVal('only_remaining', default: 'N') === 'Y') {
                $qb->andWhere('(pi.pieces - pi.pieces_sent) <> 0');
            }

            $qb->andWhere(Operator::andX($equals));
        };
    }
}

return PreordersItemsList::class;
