<?php

namespace App\Company\Admin;

use App\Company\AbstractObj;
use App\Company\Entity;
use App\Company\Model;
use App\User\Entity as UserEntity;
use App\User\Model as UserModel;
use App\Service\Google\Geocoding;
use Qs_Array;
use Qs_Db;
use Qs_Db_Filter;
use Qs_Db_Table;
use Qs_ImageFs;
use Qs_String;
use Zend_Db_Expr;
use Zend_Db_Select;
use Exception;

class Obj extends AbstractObj
{
    protected $_filterFields = ['name', 'city'];
    protected $_districtsAutocompleteLimit = 20;

    protected function _getFromColumns()
    {
        $columns = parent::_getFromColumns();
        $columns['activeIndividualsCount'] = $this->getActiveIndividualsCountExpr();
        $columns['counties'] = Model::getCountiesColumn();
        return $columns;
    }

    protected function _addDependenciesFromDb(array &$data)
    {
        $data['counties'] = $this->getCounties();
        return parent::_addDependenciesFromDb($data);
    }

    protected function _getFromDbSelect($primaryKey)
    {
        $select = parent::_getFromDbSelect($primaryKey);
        $select->joinLeft($this->_getPair('CompanyType', 't'), '`Company`.`type` = `t`.`id`', ['typeTitle' => 'title']);
        $select->joinLeft($this->_getPair('CompanyStatus', 's'), '`Company`.`status` = `s`.`id`', [
            'statusTitle' => 'title'
        ]);
        $select->joinLeft($this->_getPair('DState', 'state'), '`state`.`id` = `Company`.`state`', [
            'stateTitle' => 'title'
        ]);
        $select->joinLeft($this->_getPair('DState', 'mailingState'), '`mailingState`.`id` = `Company`.`mailingState`', [
            'mailingStateTitle' => 'title'
        ]);
        $select->joinLeft($this->_getPair('User', 'scu'), '`scu`.`id` = `Company`.`surveyContactId`', [
            'surveyContactName' => UserModel::getFullNameWithNicknameExpr('scu')
        ]);
        $select->joinLeft($this->_getPair('StatutoryAuthority', 'sa'), '`sa`.`id` = `Company`.`statutoryAuthorityId`', [
            'statutoryAuthorityTitle' => 'title'
        ]);

        return $select;
    }

    public function getCounties($columns = ['id'])
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('County', 'c'), $columns);
        $select->join(
            $this->_getPair('Company2County', 'c2c'),
            '`c2c`.`countyId` = `c`.`id` '
          . 'AND `c2c`.`companyId` = ' . $this->_db->quote($this->_primaryKey, Qs_Db::INT_TYPE),
            []
        );
        return count($columns) == 1 ? $this->_db->fetchCol($select) : $this->_db->fetchAll($select);
    }

    protected function getActiveIndividualsCountExpr()
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('User'), 'COUNT(*)')
            ->where('`User`.`companyId` = `' . $this->_getTableAlias() . '`.`id`')
            ->where('`User`.`status` = ?', UserEntity::STATUS_ACTIVE);
        return new Zend_Db_Expr('(' . $select . ')');
    }

    public function getAutocompleteDistrictOptions(array $request)
    {
        $options = [];
        $terms = preg_split("/[, \r\n]+/", $request['term']);
        $type = $request['districtType'];
        $districts = Model::getDistricts($type);

        if ($districts) {
            $lastTerm = array_pop($terms);
            foreach ($terms as $value => $term) {
                if (!in_array($term, $districts)) {
                    unset($terms[$value]);
                }
            }

            $_options = Model::getDistricts($type, $lastTerm, $terms, $this->_districtsAutocompleteLimit);
            foreach ($_options as $_option) {
                $options[] = ['value' => $_option, 'title' => $_option];
            }
        }

        return $options;
    }

    public function getAutocompleteUserOptions(array $request)
    {
        $term = Qs_Array::get($request, 'term');
        $companyId = Qs_Array::get($request, 'companyId');
        $status = (array) Qs_Array::get($request, 'status');

        $select = $this->_db->select();
        $select->from($this->_getPair('User'), ['value' => 'id', 'title' => UserModel::getFullNameWithNicknameExpr()]);
        Qs_Db_Filter::where($select, ['IF (`nickname` IS NULL, `firstName`, `nickname`)', 'lastName'], $term);
        if ($companyId) {
            $select->where('`User`.`companyId` = ?', $companyId, Qs_Db::INT_TYPE);
        }
        if ($status) {
            $select->where('`User`.`status` IN (?)', $status);
        }

        $data = $this->_db->fetchAll($select);

        return $data;
    }

    protected function _insertDependency()
    {
        $this->_saveDependency();
        return parent::_insertDependency();
    }

    protected function _updateDependency()
    {
        $this->_saveDependency();
        return parent::_updateDependency();
    }

    protected function _saveDependency()
    {
        $this->_saveUtilityCategories();
        $this->_saveAssociateCategories();
        $this->_saveSafetyGroups();
        $this->_saveDistricts();
        $this->_savePermissions();
        $this->_savePositions();
        $this->_saveGeoCoordinates();
        $this->_saveCounties();

        return $this;
    }

    protected function _saveUtilityCategories()
    {
        $table = new Qs_Db_Table('CompanyUtilityCategory');
        $table->delete($this->_db->quoteInto('`companyId` = ?', $this->getPrimaryKey(), Qs_Db::INT_TYPE));

        if ($this->_data['utilityCategoryId']) {
            foreach ($this->_data['utilityCategoryId'] as $categoryId) {
                $data = [
                    'companyId'         => $this->getPrimaryKey(),
                    'utilityCategoryId' => $categoryId,
                ];
                $table->insert($data);
            }
        }

        return $this;
    }

    protected function _saveAssociateCategories()
    {
        return $this->saveAssociateCategories(Qs_Array::get($this->_data, 'associateCategory'), $this->getPrimaryKey());
    }

    public function saveAssociateCategories(array $categories, $companyId)
    {
        $table = new Qs_Db_Table('CompanyAssociateCategory');
        $table->delete($this->_db->quoteInto('`companyId` = ?', $companyId, Qs_Db::INT_TYPE));

        foreach ($categories as $categoryId) {
            $data = [
                'companyId'  => $companyId,
                'categoryId' => $categoryId,
            ];
            $table->insert($data);
        }

        return $this;
    }

    public function getAssociateCategories($companyId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('AssociateCategory', 'ac'), ['id', 'title']);
        $select->join(
            $this->_getPair('CompanyAssociateCategory', 'cac'),
            '`cac`.`categoryId` = `ac`.`id` AND `cac`.`companyId` = ' . $this->_db->quote($companyId, Qs_Db::INT_TYPE),
            []
        );
        return $this->_db->fetchPairs($select);
    }

    public function getSafetyGroups($companyId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('SafetyGroup', 'sg'), ['id', 'title']);
        $select->join(
            $this->_getPair('CompanySafetyGroup', 'csg'),
            '`csg`.`safetyGroupId` = `sg`.`id` AND `csg`.`companyId` = ' . $this->_db->quote($companyId, Qs_Db::INT_TYPE),
            []
        );
        return $this->_db->fetchPairs($select);
    }

    public function getUtilityCategories($companyId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('UtilityCategory', 'uc'), ['id', 'title']);
        $select->join(
            $this->_getPair('CompanyUtilityCategory', 'cuc'),
            '`cuc`.`utilityCategoryId` = `uc`.`id` AND `cuc`.`companyId` = ' . $this->_db->quote($companyId, Qs_Db::INT_TYPE),
            []
        );
        return $this->_db->fetchPairs($select);
    }

    public function getPersonnelPositions($companyId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('CompanyUtilityStaff', 'cus'), ['id']);
        $select->join(
            $this->_getPair('PersonnelPosition', 'pp'),
            '`pp`.`id` = `cus`.`positionId`',
            ['positionTitle' => 'title']
        );
        $select->join(
            $this->_getPair('User', 'u'),
            '`u`.`id` = `cus`.`userId`',
            ['userName' => UserModel::getFullNameWithNicknameExpr('u')]
        );
        $select->where('`cus`.`companyId` = ?', $companyId, Qs_Db::INT_TYPE);
        $select->order('cus.sorter');
        return $this->_db->fetchAll($select);
    }

    public function getManagementPrivileges($companyId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('User', 'u'), ['id', 'userName' => UserModel::getFullNameWithNicknameExpr('u')]);
        $select->join(
            $this->_getPair('CompanyPermission', 'cp'),
            '`cp`.`userId` = `u`.`id` AND `cp`.`companyId` = ' . $this->_db->quote($companyId, Qs_Db::INT_TYPE),
            []
        );
        $select->order('userName');
        return $this->_db->fetchAll($select);
    }

    protected function _saveSafetyGroups()
    {
        $table = new Qs_Db_Table('CompanySafetyGroup');
        $table->delete($this->_db->quoteInto('`companyId` = ?', $this->getPrimaryKey(), Qs_Db::INT_TYPE));

        if ($this->_data['safetyGroupId']) {
            foreach ($this->_data['safetyGroupId'] as $groupId) {
                $data = [
                    'companyId'     => $this->getPrimaryKey(),
                    'safetyGroupId' => $groupId,
                ];
                $table->insert($data);
            }
        }

        return $this;
    }

    protected function _saveDistricts()
    {
        $table = new Qs_Db_Table('CompanyDistrict');
        $table->delete($this->_db->quoteInto('`companyId` = ?', $this->getPrimaryKey(), Qs_Db::INT_TYPE));

        if ($this->_data['district']) {
            foreach ($this->_data['district'] as $districtType => $districts) {
                if ($districts) {
                    foreach ($districts as $district) {
                        $data = [
                            'companyId'    => $this->getPrimaryKey(),
                            'districtType' => $districtType,
                            'district'     => $district,
                        ];
                        $table->insert($data);
                    }
                }
            }
        }

        return $this;
    }

    protected function _saveCounties()
    {
        Model::saveCounties(Qs_Array::get($this->_data, 'counties', []), $this->_primaryKey);
        return $this;
    }

    protected function _savePermissions()
    {
        $table = new Qs_Db_Table('CompanyPermission');
        $table->delete($this->_db->quoteInto('`companyId` = ?', $this->getPrimaryKey(), Qs_Db::INT_TYPE));

        if (!empty($this->_data['permission'])) {
            foreach ($this->_data['permission'] as $userId) {
                $data = [
                    'companyId' => $this->getPrimaryKey(),
                    'userId'    => $userId,
                ];
                $table->insert($data);
            }
        }

        return $this;
    }

    protected function _savePositions()
    {
        $table = new Qs_Db_Table('CompanyUtilityStaff');
        $table->delete($this->_db->quoteInto('`companyId` = ?', $this->getPrimaryKey(), Qs_Db::INT_TYPE));
        if (!empty($this->_data['position'])) {
            foreach ($this->_data['position'] as $data) {
                $data['companyId'] = $this->getPrimaryKey();
                $table->insert($data);
            }
        }

        return $this;
    }

    protected function _saveGeoCoordinates()
    {
        if (Entity::TYPE_REGULAR != $this->getData('type')) {
            return $this;
        }

        $newAddress = Model::createAddress4Geocoding($this->_data);
        if ($this->_previousData && !empty($this->_previousData['latitude'])
            && !empty($this->_previousData['longitude'])
        ) {
            $oldAddress = Model::createAddress4Geocoding($this->_previousData);
            if ($newAddress == $oldAddress) {
                return $this;
            }
        }

        $geocoding = new Geocoding($newAddress);
        if ($geocoding->send()) {
            $response  = $geocoding->getResponse();
            $data = $response->getLocation();
            $this->_getTable()->updateByKey($data, $this->getPrimaryKey());
        } else {
            throw new Exception($geocoding->getError());
        }

        return $this;
    }

    protected function _filter(Zend_Db_Select $select)
    {
        parent::_filter($select);

        foreach (['mnHouse', 'usCongressional'] as $type) {
            $districts = Qs_Array::get($this->_filter, $type);
            $districts = trim($districts);
            if ($districts) {
                $districts = preg_split('/[,\s]+/', $districts);
                $districts = array_filter($districts);
                if ($districts) {
                    $method = 'get' . ucfirst($type) . 'DistrictFilter';
                    $select->where('EXISTS ' . Model::$method($districts));
                }
            }
        }
        $this->_filterByPopulation($select);

        foreach (['utilityCategory', 'associateCategory', 'safetyGroup', 'counties'] as $filed) {
            $values = Qs_Array::get($this->_filter, $filed);
            if ($values) {
                $method = 'get' . ucfirst($filed) . 'Filter';
                $select->where('EXISTS ' . Model::$method($values));
            }
        }

        return $this;
    }

    protected function _filterByPopulation(Zend_Db_Select $select)
    {
        if (!($populationRange = $this->getFilter('populationRange'))) {
            return $this;
        }
        $options = explode('-', $populationRange);
        $min = array_shift($options) or $min = 0;
        $max = array_shift($options) or $max = 0;
        if ($min) {
            $select->where('`Company`.`population` >= ?', $min, Qs_Db::INT_TYPE);
        }
        if ($max) {
            $select->where('`Company`.`population` <= ?', $max, Qs_Db::INT_TYPE);
        }
        return $this;
    }

    public function getExportColumns()
    {
        $columns = $this->getConfigArray('exportFields');

        foreach ($columns as $key => $title) {
            if ('' == $title) {
                if (strtolower($key) === $key) {
                    $title = ucfirst($key);
                } else {
                    $title = ucfirst(Qs_String::decamelize($key));
                }
                $columns[$key] = $title;
            }
        }

        return $columns;
    }

    public function getList4ExportStatement()
    {
        $select = $this->_db->select();

        $columns = [
            '*',
            'counties'                => Model::getCountiesColumn(),
            'districtMnHouse'         => Model::getMnHouseDistrictColumn(),
            'districtUsCongressional' => Model::getUsCongressDistrictColumn(),
            'utilityCategory'         => Model::getUtilityCategoryColumn(),
            'associateCategory'       => Model::getAssociateCategoryColumn(),
            'safetyGroups'            => Model::getSafetyGroupColumn(),
            'positions'               => Model::getUtilityStaffColumn(),
            'permission'              => Model::getPermissionColumn(),
            'surveyContact'           => UserModel::getFullNameWithNicknameExpr()
        ];
        $select->from($this->_getPair(), $columns);

        $select->joinLeft($this->_getPair('CompanyType', 't'), '`Company`.`type` = `t`.`id`', ['type' => 'title']);
        $select->joinLeft($this->_getPair('CompanyStatus', 's'), '`Company`.`status` = `s`.`id`', ['status' => 'title']);
        $select->joinLeft($this->_getPair('User'), '`Company`.`surveyContactId` = `User`.`id`', []);
        $this->_joinStatutoryAuthority($select);

        $this->_filter($select);

        $this->_applySelectOptions($select, $this->_selectOptions);

        return $select->query();
    }

    public function prepareExportRow(array &$data)
    {
        $data['description'] = Qs_String::stripTags($data['description']);
        $data['taxExempt'] = ('y' == $data['taxExempt']) ? 'Yes': 'No';
        $data['competitors'] = preg_replace('/[\r\n]+/', ', ', $data['competitors']);

        $physicalAddress = array_values(Model::createAddress($data));
        $mailingAddress = array_values(Model::createAddress($data, 'mailing'));
        $data['asPhysicalAddress'] = ($physicalAddress == $mailingAddress) ? 'Yes' : 'No';

        if ($data['logo']) {
            $data['logo'] = BASE_URL . '/' . Qs_ImageFs::get($data['logo']);
        }

        return $this;
    }
}