<?php

namespace App\Library;

use Qs_Db;
use Qs_Db_Obj;
use Qs_FileFs;
use Zend_Db_Select;
use Zend_Db_Expr;
use Qs_Db_Filter;
use App\License\Pdf\Entity as PdfEntity;
use App\License\Entity as LicenseEntity;
use App\VendorBlacklist\Admin\Obj as BlacklistObj;
use App\Acl\Permission as AclPermission;

/**
 * @method array getDLibraryCategory4Select($columns = '*', $where = null, $order = null, $limit = null)
 */
class AbstractObj extends Qs_Db_Obj
{
    const SEND_TO_SPECIFIC_VENDORS     = 1;
    const SEND_TO_VENDORS_WITH_NAICS   = 2;
    const SEND_TO_ALL_APPROVED_VENDORS = 3;
    const SEND_TO_ALL_VENDORS          = 4;

    const RECORD_TYPE_LIBRARY          = 'library';
    const RECORD_TYPE_TAXATION_INVOICE = 'taxationInvoice';
    const RECORD_TYPE_TAXATION_RECEIPT = 'taxationReceipt';
    const RECORD_TYPE_LICENSE          = 'license';
    const RECORD_TYPE_PREMIUM          = 'premium';

    //ma_DLibraryCategory
    const CATEGORY_ID_RECEIPTS       = 5;
    const CATEGORY_ID_LICENSING      = 1;
    const CATEGORY_ID_RFP            = 2;
    const CATEGORY_ID_COMMUNICATIONS = 3;
    const CATEGORY_ID_MARKETPLACE    = 4;
    const CATEGORY_ID_TAXATION       = 6; //not implemented


    protected $_tableAlias = 'Library';
    protected $_tableShortAlias = 'l';
    protected $_fileFields = ['file'];
    protected $model;

    public function getListSelect()
    {
        if (null == $this->_select) {
            $this->_select = parent::getListSelect();
            $this->_select->reset(Zend_Db_Select::FROM);
            $this->_select->reset(Zend_Db_Select::COLUMNS);
            $this->_select->reset(Zend_Db_Select::WHERE);

            if (!empty($this->_filter['categoryId'])) {
                $categoryId = $this->_filter['categoryId'];
                if ($categoryId == self::CATEGORY_ID_LICENSING) {
                    $this->_select->union([
                        $this->_getLibrarySelect(),
                        $this->_getLicensesSelect(PdfEntity::TYPE_LICENSE),
                    ]);
                } elseif ($categoryId == self::CATEGORY_ID_RECEIPTS) {
                    $this->_select->union([
                        $this->_getLibrarySelect(),
                        $this->_getLicensesSelect(PdfEntity::TYPE_RECEIPT),
                        $this->_getLicensesSelect(PdfEntity::TYPE_PREMIUM_RECEIPT),
                    ]);
                } elseif ($categoryId == self::CATEGORY_ID_TAXATION) {
                    /*
                    $this->_select->union([
                        $this->_getLibrarySelect(),
                        $this->_getTaxationInvoiceSelect(),
                        $this->_getTaxationReceiptSelect(),
                    ]);
                    */
                } else {
                    $this->_select = $this->_getLibrarySelect();
                    $this->_applySelectOptions($this->_select, $this->_selectOptions);
                }
            } else {
                $this->_select->union([
                    $this->_getLibrarySelect(),
                    $this->_getLicensesSelect(PdfEntity::TYPE_LICENSE),
                    $this->_getLicensesSelect(PdfEntity::TYPE_RECEIPT),
                    $this->_getLicensesSelect(PdfEntity::TYPE_PREMIUM_RECEIPT),
                    // $this->_getTaxationInvoiceSelect(),
                    // $this->_getTaxationReceiptSelect(),
                ]);
            }
        }
        return $this->_select;
    }

    protected function _getLibrarySelect()
    {
        $select = $this->_db->select();
        $columns = [
            'id',
            'title',
            'sendTo',
            'file'          => "CONCAT('" . Qs_FileFs::WEB_PATH . "', '/', `file`)",
            'added',
            'recordType'    => new Zend_Db_Expr('"' . self::RECORD_TYPE_LIBRARY . '"'),
            'category'      => new Zend_Db_Expr('`lc`.`title`'),
            'isBlacklisted' => new Zend_Db_Expr('NULL'),
            'vendor'        => new Zend_Db_Expr('NULL'),

        ];
        $select->from($this->_getPair(), $columns);
        $this->_joinCategory($select);

        $this->_filterLibraryItemsByVendor($select);

        if (!empty($this->_filter['categoryId'])) {
            $select->where('`l`.`categoryId` = ?', $this->_filter['categoryId'], Qs_Db::INT_TYPE);
        }
        $this->_unionFilter($select, $this->_tableAlias, $this->_tableShortAlias, ['title']);
        return $select;
    }


    protected function _getLicensesSelect($fileType)
    {
        $select = $this->_db->select();


        $recordType = ($fileType == PdfEntity::TYPE_PREMIUM_RECEIPT)
            ? self::RECORD_TYPE_PREMIUM
            : self::RECORD_TYPE_LICENSE;

        $categoryTitle = (in_array($fileType, [PdfEntity::TYPE_RECEIPT, PdfEntity::TYPE_PREMIUM_RECEIPT]))
            ? 'Registration / Licensing Receipts'
            : 'Licenses';

        $columns = [
            'id',
            'title'         => $this->getModel()->getLicenceLibraryTitleExpr($fileType, 'lc'),
            'sendTo'        => new Zend_Db_Expr('NULL'),
            'file'          => $this->getModel()->getLicenseLibraryFileExpr($fileType, 'lc'),
            'added',
            'recordType'    => new Zend_Db_Expr('"' . $recordType . '"'),
            'category'      => new Zend_Db_Expr('"' . $categoryTitle . '"'),
            'isBlacklisted' => (new BlacklistObj())->getIsVendorBlacklistedExpr('v', AclPermission::getAdminTribeId()),
        ];
        $select->from($this->_getPair('License', 'lc'), $columns);

        $this->_joinVendor($select, 'lc');
        $this->_filterByVendor($select, 'lc');

        $this->_joinLicenseTribe($select, 'lc', $fileType);
        $this->_filterLicenseByTribe($select, 'lct');

        $this->_unionFilter($select, 'License', 'lc', [$columns['title']]);
        return $select;
    }
    /*
        protected function _getTaxationInvoiceSelect()
        {
            $select = $this->_db->select();
            $columns = [
                'id',
                'title'      => $this->getModel()->getTaxationLibraryTitleExpr(self::RECORD_TYPE_TAXATION_INVOICE),
                'file'       => $this->getModel()->getTaxationInvoiceExpr(),
                'added',
                'recordType' => new Zend_Db_Expr($this->_db->quote(self::RECORD_TYPE_TAXATION_INVOICE)),
                'category'   => new Zend_Db_Expr('"Taxation"'),
            ];
            $select->from($this->_getPair('Taxation', 't'), $columns);
            $this->_joinVendor($select, 't');

            $this->_filterByVendor($select, 't');
            $this->_unionFilter($select, 'Taxation', 't', [$columns['title']]);
            return $select;
        }
    */
    /*
        protected function _getTaxationReceiptSelect()
        {
            $select = $this->_db->select();
            $columns = [
                'id',
                'title'      => $this->getModel()->getTaxationLibraryTitleExpr(self::RECORD_TYPE_TAXATION_RECEIPT),
                'file'       => $this->getModel()->getTaxationReceiptExpr(),
                'added'      => 'transactionTime',
                'recordType' => new Zend_Db_Expr($this->_db->quote(self::RECORD_TYPE_TAXATION_RECEIPT)),
                'category'   => new Zend_Db_Expr('"Taxation"'),
            ];
            $select->from($this->_getPair('Taxation', 't'), $columns);
            $this->_joinVendor($select, 't');

            $this->_filterByVendor($select, 't');
            $this->_unionFilter($select, 'Taxation', 't', [$columns['title']]);
            return $select;
        }
    */
    protected function _unionFilter(Zend_Db_Select $select, $tableAlias, $tableShortAlias, array $filterFields)
    {
        if (!$this->hasFilter()) {
            return $this;
        }
        if (!empty($filterFields) && array_key_exists('query', $this->_filter)) {
            Qs_Db_Filter::where($select, [$tableShortAlias ?: $tableAlias => $filterFields], $this->_filter['query']);
        }
        if (($date = $this->getFilter('fromDate'))) {
            $select->where('DATE(`' . ($tableShortAlias ?: $tableAlias) . '`.`added`) >= ?', $date);
        }
        if (($date = $this->getFilter('toDate'))) {
            $select->where('DATE(`' . ($tableShortAlias ?: $tableAlias) . '`.`added`) <= ?', $date);
        }
        return $this;
    }

    protected function _joinCategory(Zend_Db_Select $select)
    {
        $select->joinLeft(
            $this->_getPair('DLibraryCategory', 'lc'),
            '`lc`.`id` = `l`.`categoryId`',
            []
        );
        return $this;
    }

    protected function _joinLicenseTribe(Zend_Db_Select $select, $tableAlias = 'lc', $fileType)
    {
        $select->joinLeft(
            $this->_getPair('LicenseTribe', 'lct'),
            '`lct`.`licenseId` = `' . $tableAlias . '`.`id`',
            []
        );
        $select->joinLeft(
            $this->_getPair('Tribe', 't'),
            '`lct`.`tribeId` = `t`.`id`',
            []
        );
        if ($fileType == PdfEntity::TYPE_RECEIPT) {
            $select->where("`lc`.`status` = ? ", LicenseEntity::STATUS_ACTIVE);
        } else {
            $select->where("`lct`.`status` = ? ", LicenseEntity::TRIBE_STATUS_APPROVED);
        }

        if ($fileType == PdfEntity::TYPE_PREMIUM_RECEIPT) {
            $select->where("`lct`.`surchargePaid` IS NOT NULL");
        }

        return $this;
    }

    protected function _filterLicenseByTribe(Zend_Db_Select $select, $tableAlias = 'lct')
    {
        if (!empty($this->_filter['tribeId'])) {
            $select->where('`' . $tableAlias . '`.`tribeId` = ?', $this->_filter['tribeId'], Qs_Db::INT_TYPE);
        }
        return $this;
    }

    protected function _joinVendor(Zend_Db_Select $select, $tableAlias = 'l')
    {
        $select->joinLeft(
            $this->_getPair('Vendor', 'v'),
            '`v`.`id` = `' . $tableAlias . '`.`vendorId`',
            ['vendor' => 'dbaName']
        );
        return $this;
    }

    protected function _filterByVendor(Zend_Db_Select $select, $tableAlias = 'l')
    {
        if (!empty($this->_filter['vendorId'])) {
            $select->where('`' . $tableAlias . '`.`vendorId` = ?', $this->_filter['vendorId'], Qs_Db::INT_TYPE);
        }
        return $this;
    }

    protected function _filterLibraryItemsByVendor(Zend_Db_Select $select, $tableAlias = 'l')
    {
        if (!empty($this->_filter['vendorId'])) {

            $select->where(
                '('
                . '`' . $tableAlias . '`.`sendTo` = ' . self::SEND_TO_SPECIFIC_VENDORS
                . ' AND '
                . 'EXISTS (' . $this->_getLibraryVendorSubSelect($tableAlias, $this->_filter['vendorId']) . ')'
            );
            $select->orWhere(
                '`' . $tableAlias . '`.`sendTo` = ' . self::SEND_TO_VENDORS_WITH_NAICS
                . ' AND '
                . 'EXISTS (' . $this->_getLibraryNaicsSubSelect($tableAlias, $this->_filter['vendorId']) . ')'
            );
            $select->orWhere(
                '`' . $tableAlias . '`.`sendTo` = ' . self::SEND_TO_ALL_APPROVED_VENDORS
                . ' AND '
                . 'EXISTS (' . $this->_getLibraryApprovedVendorsSubSelect($this->_filter['vendorId']) . ')'
            );
            $select->orWhere('`' . $tableAlias . '`.`sendTo` = ' . self::SEND_TO_ALL_VENDORS
                . ')'
            );
        }
        return $this;
    }

    protected function _getLibraryVendorSubSelect($tableAlias = 'l', $vendorId)
    {
        $subSelect = $this->_db->select();
        $subSelect->from($this->_getPair('LibraryVendor', 'lv'), [new Zend_Db_Expr('1')]);
        $subSelect->where("`lv`.`libraryId` = `{$tableAlias}`.`id`");
        $subSelect->where("`lv`.`vendorId` = ?", $vendorId, Qs_Db::INT_TYPE);
        return $subSelect;
    }

    protected function _getLibraryNaicsSubSelect($tableAlias = 'l', $vendorId)
    {
        $subSelect = $this->_db->select();
        $subSelect->from($this->_getPair('LibraryNaics', 'ln'), [new Zend_Db_Expr('1')]);
        $subSelect->joinLeft(
            $this->_getPair('LicenseNaics', 'licn'),
            '`licn`.`naicsCode` = `ln`.`naicsCode`',
            []
        );
        $subSelect->joinLeft(
            $this->_getPair('License', 'lic'),
            '`lic`.`id` = `licn`.`licenseId`',
            []
        );
        $subSelect->where("`ln`.`libraryId` = `{$tableAlias}`.`id`");
        $subSelect->where("`lic`.`vendorId` = ?", $vendorId, Qs_Db::INT_TYPE);
        return $subSelect;
    }

    protected function _getLibraryApprovedVendorsSubSelect($vendorId)
    {
        $subSelect = $this->_db->select();
        $subSelect->from($this->_getPair('License', 'lic'), [new Zend_Db_Expr('1')]);
        $subSelect->joinLeft(
            $this->_getPair('LicenseTribe', 'lict'),
            '`lic`.`id` = `lict`.`licenseId`',
            []
        );
        $subSelect->where("`lict`.`status` = ?", LicenseEntity::TRIBE_STATUS_APPROVED);
        $subSelect->where("`lic`.`vendorId` = ?", $vendorId, Qs_Db::INT_TYPE);
        return $subSelect;
    }

    public function getModel()
    {
        if (null === $this->model) {
            $this->model = new Model(['db' => $this->_db]);
        }
        return $this->model;
    }

    public function setModel($model)
    {
        $this->model = $model;
        return $this;
    }

    protected function _addDependenciesFromDb(array &$data)
    {
        $data['naicsCodes'] = $this->getModel()->getLibraryNaicsCodes($data['id']);
        $data['vendors'] = $this->getModel()->getLibraryVendors($data['id']);
        return $this;
    }

}