<?php

namespace App\Library;

use App\License\Pdf\View as LicensePdfView;
use App\License\Pdf\Entity as PdfEntity;
use App\Taxation\Pdf\View as TaxationPdfView;
use App\License\Entity as LicenseEntity;
use Exception;
use Qs\Db\Model as DbModel;
use Qs_ConfigTrait;
use Qs_Db;
use Qs_FileFs;
use Zend_Db_Expr;

class Model extends DbModel
{
    use DbModel\WritableTrait;
    use Qs_ConfigTrait;

    const CATEGORY_RECEIPTS_ID              = 5;
    const CATEGORY_LICENSING_ID             = 1;
    const CATEGORY_RFP_ID                   = 2;
    const CATEGORY_COMMUNICATIONS_ID        = 3;
    const CATEGORY_ID_MARKETPLACE           = 4;
    const CATEGORY_TAXATION_ID              = 6; //not implemented

    protected $tableAlias = 'Library';
    protected $tableShortAlias = 'lib';

    protected function initData(array &$data)
    {
        $this->getTable()->emptyToNull($data);
        return $this;
    }

    protected function saveDependencies(array $data)
    {
        foreach (['naicsCodes', 'vendors'] as $name) {
            $method = 'save' . ucfirst($name);
            if (array_key_exists($name, $data) && method_exists($this, $method)) {
                $this->$method($data[$name], $data['id']);
            }
        }
        return $this;
    }

    /** @noinspection PhpUnusedPrivateMethodInspection */
    /**
     * @param array $codes
     * @param $libraryId
     * @return $this
     */
    private function saveNaicsCodes(array $codes, $libraryId)
    {
        $this->getTable('LibraryNaics')->saveRelationIds($codes, 'naicsCode', compact('libraryId'));
        return $this;
    }

    /** @noinspection PhpUnusedPrivateMethodInspection */
    /**
     * @param array $vendors
     * @param $libraryId
     * @return $this
     */

    private function saveVendors(array $vendors, $libraryId)
    {
        $this->getTable('LibraryVendor')->saveRelationIds($vendors, 'vendorId', compact('libraryId'));
        return $this;
    }

    public function getLibraryNaicsCodes($libraryId)
    {
        $select = $this->db->select();
        $select->from(Qs_Db::getPair('LibraryNaics', 'ln'), ['naicsCode']);
        $select->where('`ln`.`libraryId` = ?', $libraryId, Qs_Db::INT_TYPE);
        return $this->db->fetchCol($select);
    }

    public function getLibraryVendors($libraryId)
    {
        $select = $this->db->select();
        $select->from(Qs_Db::getPair('LibraryVendor', 'lv'), ['vendorId']);
        $select->where('`lv`.`libraryId` = ?', $libraryId, Qs_Db::INT_TYPE);
        return $this->db->fetchCol($select);
    }

    public function getNotificationFiles($type, $id, array $tribeIds = [])
    {
        switch ($type) {
            case Obj::RECORD_TYPE_LIBRARY:
                $select = $this->db->select();
                $select->from(Qs_Db::getPair('Library', 'lib'), ['file', 'title', 'emailNote']);
                $select->join(Qs_Db::getPair('DLibraryCategory', 'lc'), '`lc`.`id` = `lib`.`categoryId`', [
                    'categoryId'    => 'id',
                    'categoryTitle' => 'title',
                    'categoryAlias' => 'alias',
                ]);
                $select->columns(['recordType' => new Zend_Db_Expr($this->db->quote($type))]);
                $select->where('`lib`.`id` = ?', $id, Qs_Db::INT_TYPE);
                break;
            case Obj::RECORD_TYPE_TAXATION_INVOICE:
                $select = $this->db->select();
                $select->from(Qs_Db::getPair('Taxation', 't'), [
                    'title' => $this->getTaxationLibraryTitleExpr(Obj::RECORD_TYPE_TAXATION_INVOICE),
                    'file'  => $this->getTaxationInvoiceExpr(),
                    'vendorId',
                ]);
                $select->join(Qs_Db::getPair('DLibraryCategory', 'lc'), '`lc`.`id` = ' . self::CATEGORY_TAXATION_ID, [
                    'categoryId'    => 'id',
                    'categoryTitle' => 'title',
                    'categoryAlias' => 'alias',
                ]);
                $select->columns(['recordType' => new Zend_Db_Expr($this->db->quote($type))]);
                $select->where('`t`.`id` = ?', $id, Qs_Db::INT_TYPE);
                break;
            case Obj::RECORD_TYPE_LICENSE:
                $select = $this->db->select();
                $union = [];
                foreach ($this->getConfigArray('libraryLicenseTitle') as $fileType => $title) {
                    switch ($fileType) {
                        case PdfEntity::TYPE_RECEIPT:
                            $subSelect = $this->db->select()
                                ->from(
                                    Qs_Db::getPair('License', 'l'),
                                    [
                                        'title' => $this->getLicenceLibraryTitleExpr($fileType),
                                        'file'  => $this->getLicenseLibraryFileExpr($fileType),
                                        'vendorId',
                                    ]
                                )
                                ->join(
                                    Qs_Db::getPair('DLibraryCategory', 'lc'),
                                    '`lc`.`id` = ' . self::CATEGORY_RECEIPTS_ID,
                                    [
                                        'categoryId'    => 'id',
                                        'categoryTitle' => 'title',
                                        'categoryAlias' => 'alias',
                                    ]
                                )
                                ->where('`l`.`id` = ?', $id, Qs_Db::INT_TYPE)
                                ->columns(['recordType' => new Zend_Db_Expr($this->db->quote($type))]);
                            break;
                        case PdfEntity::TYPE_LICENSE:
                            $subSelect = $this->db->select()
                                ->from(
                                    Qs_Db::getPair('License', 'l'),
                                    [
                                        'title' => $this->getLicenceLibraryTitleExpr($fileType),
                                        'file'  => $this->getLicenseLibraryFileExpr($fileType),
                                        'vendorId',
                                    ]
                                )
                                ->join(
                                    Qs_Db::getPair('LicenseTribe', 'lt'),
                                    '`l`.`id` = `lt`.`licenseId` AND `lt`.`status` = "' . LicenseEntity::TRIBE_STATUS_APPROVED . '"',
                                    []
                                )
                                ->join(
                                    Qs_Db::getPair('Tribe', 't'),
                                    '`lt`.`tribeId` = `t`.`id`',
                                    []
                                )
                                ->join(
                                    Qs_Db::getPair('DLibraryCategory', 'lc'),
                                    '`lc`.`id` = ' . self::CATEGORY_LICENSING_ID,
                                    [
                                        'categoryId'    => 'id',
                                        'categoryTitle' => 'title',
                                        'categoryAlias' => 'alias',
                                    ]
                                )
                                ->where('`l`.`id` = ?', $id, Qs_Db::INT_TYPE)
                                ->columns(['recordType' => new Zend_Db_Expr($this->db->quote($type))]);
                            if ($tribeIds) {
                                $subSelect->where('`t`.`id` IN (?)', $tribeIds, Qs_Db::INT_TYPE);
                            }
                            break;
                        case PdfEntity::TYPE_PREMIUM_RECEIPT:
                            break;
                        default:
                            throw new Exception('Unknown library file type');
                    }
                    $union[] = $subSelect;
                }
                $select->union($union);
                break;

            case Obj::RECORD_TYPE_PREMIUM:
                $select = $this->db->select();
                $select->from(
                        Qs_Db::getPair('License', 'l'),
                        [
                            'title' => $this->getLicenceLibraryTitleExpr(PdfEntity::TYPE_PREMIUM_RECEIPT),
                            'file'  => $this->getLicenseLibraryFileExpr(PdfEntity::TYPE_PREMIUM_RECEIPT),
                            'vendorId',
                        ]
                    )
                    ->join(
                        Qs_Db::getPair('LicenseTribe', 'lt'),
                        '`l`.`id` = `lt`.`licenseId` AND `lt`.`status` = "' . LicenseEntity::TRIBE_STATUS_APPROVED . '"'
                            . ' AND `lt`.`surchargePaid` IS NOT NULL',
                        []
                    )
                    ->join(
                        Qs_Db::getPair('Tribe', 't'),
                        '`lt`.`tribeId` = `t`.`id`',
                        []
                    )
                    ->join(
                        Qs_Db::getPair('DLibraryCategory', 'lc'),
                        '`lc`.`id` = ' . self::CATEGORY_LICENSING_ID,
                        [
                            'categoryId'    => 'id',
                            'categoryTitle' => 'title',
                            'categoryAlias' => 'alias',
                        ]
                    )
                    ->where('`l`.`id` = ?', $id, Qs_Db::INT_TYPE)
                    ->columns(['recordType' => new Zend_Db_Expr($this->db->quote($type))]);
                if ($tribeIds) {
                    $select->where('`t`.`id` IN (?)', $tribeIds, Qs_Db::INT_TYPE);
                }
                break;
            default:
                throw new Exception('Invalid library record type: ' . $type);
        }
        return $this->db->fetchAll($select);
    }

    public function getTaxationLibraryTitleExpr($fileType, $alias = 't')
    {
        $titles = $this->getConfigArray('libraryTaxationTitle');
        return new Zend_Db_Expr(
            'REPLACE(' . $this->db->quote($titles[$fileType]) . ", '{id}', `{$alias}`.`id`)"
        );
    }

    public function getLicenceLibraryTitleExpr($fileType, $licenseAlias = 'l', $tribeAlias = 't')
    {
        $titles = $this->getConfigArray('libraryLicenseTitle');
        $title = $this->db->quote($titles[$fileType]);

        switch ($fileType) {
            case PdfEntity::TYPE_RECEIPT:
                $expr = "REPLACE({$title}, '{id}', `{$licenseAlias}`.`id`)"; // license id
                break;
            case PdfEntity::TYPE_LICENSE:
                $expr = "REPLACE({$title}, '{id}', `{$licenseAlias}`.`id`)"; // license id
                $expr = "REPLACE({$expr}, '{tribeTitle}', `{$tribeAlias}`.`title`)"; // tribe title
                break;
            case PdfEntity::TYPE_PREMIUM_RECEIPT:
                $expr = "REPLACE({$title}, '{id}', `{$licenseAlias}`.`id`)"; // license id
                $expr = "REPLACE({$expr}, '{tribeTitle}', `{$tribeAlias}`.`title`)"; // tribe title
                break;

            default:
                throw new Exception('Unknown library file type');
        }

        return new Zend_Db_Expr($expr);
    }

    public function getLicenseLibraryFileExpr($type, $licenseAlias = 'l', $tribeAlias = 't')
    {
        $fullAlias = LicensePdfView::getFileTypePage($type, 'fullAlias');

        switch ($type) {
            case PdfEntity::TYPE_RECEIPT:
                $expr = "CONCAT('{$fullAlias}', '/', '{$type}', '-', `{$licenseAlias}`.`id`, '.pdf')";
                break;
            case PdfEntity::TYPE_LICENSE:
                $expr = "CONCAT('{$fullAlias}', '/', '{$type}', '-', `{$licenseAlias}`.`id`, '_', `{$tribeAlias}`.`id`, '.pdf')";
                break;
            case PdfEntity::TYPE_PREMIUM_RECEIPT:
                $expr = "CONCAT('{$fullAlias}', '/', '{$type}', '-', `{$licenseAlias}`.`id`, '_', `{$tribeAlias}`.`id`, '.pdf')";
                break;
            default:
                throw new Exception('Unknown library file type');
        }

        return new Zend_Db_Expr($expr);
    }

    public function getTaxationReceiptExpr($alias = 't')
    {
        $type = TaxationPdfView::TYPE_RECEIPT;
        return new Zend_Db_Expr(
            'CONCAT("' . TaxationPdfView::getFileTypePage($type, 'fullAlias')
            . '", "/", "' . $type . '", "-", `' .  $alias. '`.`id`, ".pdf")');
    }

    public function getTaxationInvoiceExpr($alias = 't')
    {
        return new Zend_Db_Expr("CONCAT('" . Qs_FileFs::WEB_PATH . "', '/', `{$alias}`.`invoiceFile`)");
    }

    public static function getNaicsCodes($naicsCodes)
    {
        $select = Qs_Db::getSelect();
        $select->from(Qs_Db::getPair('Naics'));
        $select->where('`id` IN (?)', $naicsCodes);
        return Qs_Db::getInstance()->fetchAll($select);
    }
}