<?php

use KupShop\AdminBundle\AdminList\BaseList;
use KupShop\AdminBundle\AdminList\FiltersStorage;
use KupShop\AdminBundle\Util\Stats\StoreDataLoader;
use KupShop\ElninoBundle\Query\QueryBuilder;
use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
use KupShop\KupShopBundle\Util\Excel\ExcelGenerator;
use KupShop\KupShopBundle\Util\Excel\ExcelHeaderBuilder;
use KupShop\KupShopBundle\Util\HtmlBuilder\HTML;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use Query\Operator;

class StockInList extends BaseList
{
    use FiltersStorage;

    protected $orderParam = [
        'sort' => 'ID',
        'direction' => 'DESC',
    ];

    protected $template = 'list/stockIn.tpl';

    protected $tableDef = [
        'id' => 's.id',
        'class' => 'getRowClass',
        'fields' => [
            'ID' => ['field' => 's.number', 'render' => 'renderNumber'],
            'supplier' => ['translate' => true, 'field' => 'ss.name', 'size' => 2],
            'ico' => ['translate' => true, 'field' => 'ss.ico', 'visible' => 'N'],
            'dic' => ['translate' => true, 'field' => 'ss.dic', 'visible' => 'N'],
            'priceWithoutVat' => ['translate' => true, 'field' => 'total_price', 'render' => 'renderPrice'],
            'dateIssued' => ['translate' => true, 'field' => 'datec', 'render' => 'renderDate'],
            'dateExpiration' => ['translate' => true, 'field' => 'datee', 'render' => 'renderDate'],
            'dateStockIn' => ['translate' => true, 'field' => 'datesin', 'render' => 'renderDate', 'spec' => 's.date_stock_in AS datesin'],
            'code' => ['translate' => true, 'field' => 's.code'],
            'payment' => ['translate' => true, 'field' => 's.payment_method'],
            'closed' => ['translate' => true, 'field' => 'closed', 'size' => 0.7, 'render' => 'renderBoolean'],
            'listPaid' => ['translate' => true, 'field' => 'paid', 'size' => 0.7, 'render' => 'renderBoolean'],
            'note' => ['translate' => true, 'field' => 's.note'],
            'currencyRate' => ['translate' => true, 'field' => 's.multiplier', 'spec' => 's.multiplier', 'render' => 'renderFloat', 'visible' => 'N'],
            'foreignPrice' => ['translate' => true, 'field' => 'foreignPrice', 'visible' => 'N', 'spec' => ''],
        ],
    ];

    public function &getTableDef()
    {
        if (!$this->tableDefLoaded) {
            $tableDef = parent::getTableDef();
            if (getVal('IDp')) {
                $pos = array_search('priceWithoutVat', array_keys($tableDef['fields']));
                $pos = intval($pos) ?: 1;
                $price = ['translate' => true, 'field' => 'si.price', 'render' => 'renderPrice', 'title' => 'Cena prod.', 'visible' => 'Y', 'position' => $pos];
                $priceVAT = array_merge($price, ['translate' => true, 'render' => 'renderPriceVAT', 'title' => 'Cena prod. DPH']);
                $qty = ['translate' => true, 'field' => 'si.quantity', 'title' => 'Množství', 'visible' => 'Y', 'position' => $pos];

                $tableDef['fields'] = array_merge(
                    array_slice($tableDef['fields'], 0, $pos),
                    ['pieces' => $qty, 'listItemPrice' => $price, 'listItemPriceWithVat' => $priceVAT],
                    array_slice($tableDef['fields'], $pos + 1)
                );

                if (!empty($tableDef['fields']['foreignPrice'])) {
                    $tableDef['fields']['foreignPrice']['spec'] = self::getForeignPriceSpec('si.price');
                }
            } else {
                if (!empty($tableDef['fields']['foreignPrice'])) {
                    $tableDef['fields']['foreignPrice']['spec'] = self::getForeignPriceSpec();
                }
            }

            $this->tableDef = $tableDef;
        }

        return $this->tableDef;
    }

    public function customizeTableDef($tableDef)
    {
        if (getVal('index') == 'future') {
            $column = [
                'dateStockIn' => $tableDef['fields']['dateIssued'],
            ];
            unset($tableDef['fields']['dateIssued']);
            unset($tableDef['fields']['dateStockIn']);
            $tableDef['fields'] = array_merge(
                array_slice($tableDef['fields'], 0, 3),
                $column,
                array_slice($tableDef['fields'], 3),
            );
        }

        if ((findModule(Modules::WAREHOUSE) || findModule(Modules::CHECK_APP)) && getVal('index') == 'future') {
            $tableDef['fields']['stockInCheck'] = ['translate' => true, 'field' => 's.id', 'size' => 1, 'class' => 'right', 'render' => 'renderAutomaticStockIn'];
            $tableDef['fields']['massStockIn'] = ['translate' => true, 'field' => 's.id', 'size' => 1, 'class' => 'right', 'render' => 'renderAutomaticMassStockIn'];
            $this->pageDivide = 200;
        }

        return $tableDef;
    }

    public function getRowClass($values)
    {
        $flags = explodeFlags($values['flags']);

        if (isset($flags['O'])) {
            return 'row-blue';
        }

        return $this->getListRowValue($values, 'should_pay') ? 'row-red' : '';
    }

    public function renderICDIC($values)
    {
        $ICDIC = [$values['ico'], $values['dic']];

        return implode(' / ', array_filter($ICDIC));
    }

    public function renderAutomaticStockIn($values)
    {
        $output = HTML::create('a');

        $output
            ->attr('href', "javascript:nw('stockInCheck', '{$values['id']}')")
            ->tag('span')
            ->class('badge badge-primary')
                    ->tag('span')
                    ->class('glyphicon glyphicon-barcode')
                        ->text(' Kontrola')
                    ->end()
            ->end();

        return $output;
    }

    public function renderAutomaticMassStockIn($values)
    {
        $output = HTML::create('input');

        $output
            ->attr('style', 'margin-top: 0px;')
            ->attr('type', 'checkbox')
            ->attr('name', 'stockIns')
            ->attr('value', $values['id']);

        return $output;
    }

    public function renderPrice($values, $column)
    {
        return printPrice($this->getListRowValue($values, $column['field']), ['ceil' => false]);
    }

    public function renderNumber($values, $column)
    {
        $index = $this->getListRowValue($values, 'id_index');

        switch ($index) {
            case 'future':
                return 'Budoucí faktura';
            case 'preorder':
                return 'Předobjednávková faktura';
            case 'closure':
                return 'Uzávěrka '.$this->getListRowValue($values, 'number');
            default:
                return $this->getListRowValue($values, $column['field']);
        }
    }

    private static function getForeignPriceSpec(string $field = 's.total_price'): string
    {
        // language=MariaDB
        return "
            IF(COALESCE(s.multiplier, 1) = 1,
                '',
                CONCAT_WS(' ',
                    FORMAT({$field} / s.multiplier, 2, 'cs_CZ'), 
                    COALESCE(JSON_VALUE(s.data, '$.currency_symbol'), '€')
                )
            ) AS foreignPrice
        ";
    }

    public function getSQLOrdering(&$var, &$orderParam)
    {
        parent::getSQLOrdering($var, $orderParam);

        if (getVal('index') == 'future' && !getVal('order')) {
            $var['orderField'] = 'date_issued';
            $var['orderDir'] = 'ASC';
        }
    }

    public function getQuery()
    {
        /** @var QueryBuilder $qb */
        $qb = sqlQueryBuilder()
                    ->select('s.id', 's.number', 's.id_index', 's.flags', 'ss.id as supplier_id, ss.name, ss.ico, ss.dic',
                        's.total_price as total_price', 's.date_issued as datec', 's.date_expiration AS datee',
                        's.code', 's.payment_method', 'IF(s.closed,\'Y\',\'N\') as closed', 'IF(s.paid,\'Y\',\'N\') as paid',
                        's.note', 's.date_expiration - INTERVAL 5 day < NOW() AND s.paid = \'N\' as should_pay')
                    ->from('stock_in', 's')
                    ->leftJoin('s', 'suppliers', 'ss', 's.id_supplier=ss.id');

        extract($_REQUEST, EXTR_SKIP | EXTR_REFS);

        // ###########
        if (!empty($IDs)) {
            /* (s.number=":IDs" OR s.code LIKE '%:IDs%') */
            $qb->andWhere(Operator::orX(Operator::equals(['s.number' => $IDs]), Operator::like(['s.code' => '%'.$IDs.'%'])));
        }
        // ###########
        if (!empty($IDp)) {
            $qb->leftJoin('s', 'stock_in_items', 'si', 'si.id_stock_in=s.id')
                ->leftJoin('si', 'products', 'p', 'si.id_product=p.id');

            if (!empty($IDv)) {
                $qb->andWhere(Operator::equals(['si.id_variation' => $IDv]));
            }

            $qb->addSelect('si.price, p.vat, SUM(si.quantity) AS quantity')
                ->andWhere(Operator::equals(['si.id_product' => $IDp]))
                ->groupBy('s.id');
        }
        // ###########
        if (isset($supplier) && $supplier != '') {
            $qb->andWhere('( ss.name LIKE :supplier OR ss.ico LIKE :supplier)')
            ->setParameter('supplier', '%'.$supplier.'%');

            unset($fields, $name);
        }
        // ###########
        if (!empty($priceFrom) && intval($priceFrom) > 0) {
            $priceFrom = str_replace(',', '.', $priceFrom);
            $qb->andWhere('s.total_price >= :price_from')->setParameter('price_from', $priceFrom);
        }
        // ###########
        if (!empty($priceTo) && intval($priceTo) > 0) {
            $priceTo = str_replace(',', '.', $priceTo);
            $qb->andWhere('s.total_price <= :price_to')->setParameter('price_to', $priceTo);
        }
        // ###########
        if (!empty($dateFrom) || !empty($dateTo)) {
            $type = !empty($dateType) ? $dateType : 'date_expiration';

            if (!empty($dateFrom)) {
                $qb->andWhere('s.'.$type.'>=:date_from')->setParameter('date_from', $this->prepareDate($dateFrom).' 00:00:00');
            }
            if (!empty($dateTo)) {
                $qb->andWhere('s.'.$type.'<=:date_to')->setParameter('date_to', $this->prepareDate($dateTo).' 23:59:59');
            }
        }
        // ###########
        if (!empty($payment_method)) {
            $qb->andWhere('s.payment_method = :payment_method')->setParameter('payment_method', $payment_method);
        }

        $index = getVal('index', null, 'invoice');

        $qb->andWhere(Operator::equals(['s.id_index' => $index]));

        if ($index == 'closure') {
            /**
             * @var $storeDataLoader StoreDataLoader
             */
            $storeDataLoader = ServiceContainer::getService(StoreDataLoader::class);

            $var['closure_now'] = $storeDataLoader->getCurrentStoreValue();

            $sql = sqlQuery("SELECT total_price AS without_vat FROM stock_in WHERE id_index='closure' ORDER BY id DESC LIMIT 1");
            $var['closure_last'] = sqlFetch($sql);
        }

        if ($index == 'closure') {
            return [
                'qb' => $qb,
                'closure_now' => $var['closure_now'],
                'closure_last' => $var['closure_last'],
            ];
        } else {
            return $qb;
        }
    }

    public function recalcTotalPrice($id)
    {
        sqlQuery('UPDATE stock_in s
         SET total_price=(
            SELECT SUM(si.quantity*si.price)+s.transport_price-s.discount
            FROM stock_in_items si
            WHERE si.id_stock_in=s.id
         )
         WHERE s.id=:id', ['id' => $id]);
    }

    public function handleFinalizeYearStock()
    {
        $queryQuantity = 'SELECT p.id AS id_product, pv.id as id_variation, COALESCE(pv.in_store, p.in_store) AS quantity, p.title, v.vat
        FROM products p
        LEFT JOIN products_variations pv ON p.id=pv.id_product
        LEFT JOIN vats AS v ON p.vat=v.id';

        return $this->handleFinalizeYear($queryQuantity);
    }

    public function handleFinalizeYearInventory()
    {
        $queryQuantity = 'SELECT ii.id_product, ii.id_variation, SUM(ii.quantity) quantity, p.title, v.vat
        FROM inventory_items ii
        LEFT JOIN products p ON ii.id_product=p.id
        LEFT JOIN vats AS v ON p.vat=v.id
        GROUP BY ii.id_product, ii.id_variation';

        return $this->handleFinalizeYear($queryQuantity);
    }

    public function handleFinalizeYear($queryQuantity)
    {
        $year = date('Y') - 1;
        $yearNext = $year + 1;

        sqlStartTransaction();
        $qb = sqlQueryBuilder()->select('*')
            ->from('stock_in', 'si')
            ->where(Operator::equals(['id_index' => 'closure']))
            ->andWhere(Operator::equals(['number' => $year]))
            ->execute()->fetchOne();
        if ($qb) {
            return $this->returnError("Uzávěrku za rok {$year} nelze vytvořit, již byla vytvořena.", false);
        }

        $supplierId = returnSQLResult('SELECT MIN(id) FROM suppliers');

        sqlQuery('INSERT INTO stock_in
            (`number`, `code`, `closed`, `date_created`, `id_supplier`, `total_price`, `transport_price`, `date_expiration`, `date_issued`, `payment_method`, `paid`, `note`, `discount`, `finished`, `id_index`, `date_stock_in`)
            VALUES (
            :year, :code, 0, NOW( ), :supplierId, 0, 0, :date, :date, \'dobirka\', 0, :text, 0, 1, \'closure\', :date)',
            ['code' => "-{$year}", 'year' => $year, 'supplierId' => $supplierId, 'date' => "{$yearNext}-01-01", 'text' => "Uzávěrka roku {$year}"]);

        $newId = sqlInsertId();

        if (findModule(Modules::WAREHOUSE)) {
            $date = "{$year}-12-31";
            /** @var $storeDataLoader StoreDataLoader */
            $storeDataLoader = ServiceContainer::getService(StoreDataLoader::class);
            $queryQuantity = $storeDataLoader->getCurrentStoreQb($date)
                ->select("NULL, p.title, {$newId}, p.id as id_product, pv.id as id_variation, 
                          ss.in_store as quantity, r.average_price as price, v.vat");
            sqlQuery("INSERT INTO stock_in_items
                (`id`, `name`, `id_stock_in`, `id_product`, `id_variation`, `quantity`, `price`, `vat`)
                {$queryQuantity->getSQL()}", $queryQuantity->getParameters());
        } else {
            sqlQuery("INSERT INTO stock_in_items
            (`id`, `name`, `id_stock_in`, `id_product`, `id_variation`, `quantity`, `price`, `vat`)
            SELECT NULL, i.title, {$newId}, i.id_product, i.id_variation, quantity, average_price, i.vat
            FROM
            (
              {$queryQuantity}
            ) i
            LEFT JOIN
            (
              SELECT sii.id_product, sii.id_variation, SUM(sii.quantity*sii.price)/SUM(sii.quantity) average_price
              FROM stock_in_items sii
              LEFT JOIN stock_in si ON si.id=sii.id_stock_in
              WHERE COALESCE(si.date_stock_in, si.date_issued) >= '{$year}-01-01' AND COALESCE(si.date_stock_in, si.date_issued) < '{$yearNext}-01-01' 
                AND (si.id_index != 'future') AND (sii.quantity > 0)
              GROUP BY sii.id_product, sii.id_variation
            ) s ON i.id_product = s.id_product AND (i.id_variation IS NULL OR i.id_variation = s.id_variation)
            WHERE quantity > 0");
        }

        $this->recalcTotalPrice($newId);

        sqlQuery("UPDATE stock_in SET closed = 1 WHERE date_stock_in < '{$yearNext}-01-01'");

        sqlFinishTransaction();

        return $this->returnOK("Byla vytvořena uzávěrka za rok {$year}", false, ['index' => 'closure']);
    }

    public function handleExportCurrentStore()
    {
        $date = getVal('date');
        $filename = 'uzaverka-'.date('Y-m-d', strtotime($date ?? 'today')).'.xlsx';

        ini_set('memory_limit', '512M');
        ini_set('max_execution_time', '180');

        /** @var ExcelGenerator $excelGenerator */
        $excelGenerator = ServiceContainer::getService(ExcelGenerator::class);

        $header = (new ExcelHeaderBuilder())
           ->int('ID produktu', 'id_product')
           ->int('ID varianty', 'id_variation')
           ->string('EAN', 'ean')
           ->string('Kod', 'code')
           ->string('Produkt', 'product_title')
           ->string('Varianta', 'variation_title')
           ->price('Vážená nákupní cena bez DPH', 'average_price')
           ->float('Počet kusů', 'in_store')
           ->price('Hodnota zboží na skladě', 'warehouse_value')
           ->int('DPH', 'vat')
           ->price('Nákupní cena bez DPH', 'price_buy');

        $data = function () use ($date) {
            /**
             * @var $storeDataLoader StoreDataLoader
             */
            $storeDataLoader = ServiceContainer::getService(StoreDataLoader::class);
            $sql = $storeDataLoader->getCurrentStoreQb($date)
                ->select(
                    'COALESCE(pv.ean, p.ean) as ean, p.id as id_product, pv.id as id_variation, 
                p.title as product_title, pv.title as variation_title, v.vat, COALESCE(pv.code, p.code) as code,
                r.average_price, COALESCE(pv.price_buy, p.price_buy) as price_buy'
                );

            $in_store_source = findModule(Modules::WAREHOUSE) ? 'ss' : 's';
            $sql->addSelect("{$in_store_source}.in_store, ({$in_store_source}.in_store * r.average_price) as warehouse_value");

            $sum = 0;
            foreach ($sql->execute() as $row) {
                $sum += $row['warehouse_value'];
                yield $row;
            }

            yield [
                'ean' => 'Celkem',
                'id_product' => null,
                'id_variation' => null,
                'product_title' => null,
                'variation_title' => null,
                'vat' => null,
                'code' => null,
                'average_price' => null,
                'price_buy' => null,
                'in_store' => null,
                'warehouse_value' => $sum,
            ];
        };

        $excelGenerator->generateExcel($header->getHeader(), $header->formatData($data()), $filename);

        writeDownActivity('Export hodnoty skladu');
        exit;
    }

    public function handleStockInExport()
    {
        $header = [
            ['name' => 'Číslo faktury'],
            ['name' => 'Datum', 'type' => 'datetime', 'format' => 'd.m.yyyy'],
            ['name' => 'Produkt'],
            ['name' => 'Varianta'],
            ['name' => 'Kód'],
            ['name' => 'Kód u dodavatele'],
            ['name' => 'EAN', 'type' => DataType::TYPE_STRING],
            ['name' => 'Množství', 'type' => DataType::TYPE_NUMERIC],
            ['name' => 'Dodavatel'],
            ['name' => 'Nák. cena v měně/ks', 'type' => DataType::TYPE_NUMERIC],
            ['name' => 'Měna'],
            ['name' => 'Nák. cena/ks v CZK', 'type' => DataType::TYPE_NUMERIC],
            ['name' => 'Celk. cena v měně', 'type' => DataType::TYPE_NUMERIC],
            ['name' => 'Měna'],
            ['name' => 'Kurz', 'type' => DataType::TYPE_NUMERIC],
            ['name' => 'Aktuálně skladem', 'type' => DataType::TYPE_NUMERIC],
            ['name' => 'Prodejní cena', 'type' => DataType::TYPE_NUMERIC, 'format' => '0.00'],
            ['name' => 'Aktuální nák. cena', 'type' => DataType::TYPE_NUMERIC],
        ];
        if (findModule(Modules::STORES)) {
            $header[] = ['name' => 'Sklad'];
        }

        /** @var ExcelGenerator $excelGenerator */
        $excelGenerator = ServiceContainer::getService(ExcelGenerator::class);
        $now = (new DateTime())->format('d-m-Y_H-i-s');
        $excelGenerator->generateExcel($header, $this->getExportData(), "export-naskladneni-{$now}.xlsx");
        exit;
    }

    protected function getExportData()
    {
        $qb = $this->getQuery()
            ->select("s.code as invoice_number, s.date_issued, p.title product_title, pv.title variation_title, COALESCE(pv.code, p.code) as code, ps.code as code_supplier, COALESCE(pv.ean, p.ean), si.quantity as quantity, ss.name as supplier_name,
             si.price/s.multiplier as price, JSON_UNQUOTE(json_extract(s.data, '$.currency_symbol')) as symbol, si.price as price_czk, (si.price/s.multiplier)*si.quantity as price_sum, JSON_UNQUOTE(json_extract(s.data, '$.currency_symbol')) as price_sum_symbol, s.multiplier as rate,
             coalesce(pv.in_store, p.in_store) as in_store, coalesce(pv.price, p.price)*(1+(v.vat/100)) as unit_price
            ")
            ->innerJoin('s', 'stock_in_items', 'si', 's.id=si.id_stock_in')
            ->innerJoin('si', 'products', 'p', 'si.id_product=p.id')
            ->leftJoin('si', 'products_variations', 'pv', 'si.id_variation=pv.id')
            ->leftJoin('si', 'products_of_suppliers', 'ps', 'si.id_product=ps.id_product 
            and si.id_variation=ps.id_variation and s.id_supplier=ps.id_supplier')
            ->innerJoin('p', 'vats', 'v', 'p.vat=v.id')
        ;

        if (findModule(Modules::PRODUCTS, Modules::SUB_PRICE_BUY)) {
            $qb->addSelect('coalesce(pv.price_buy, p.price_buy) as price_buy');
        }

        if (findModule(Modules::STORES)) {
            $qb->addSelect('st.name as store')->leftJoin('s', 'stores', 'st', "st.id=JSON_UNQUOTE(JSON_EXTRACT(s.data, '$.id_store'))");
        }

        foreach ($qb->execute() as $values) {
            yield array_values($values);
        }
    }
}