<?php

namespace App\User\Admin;

use App\User\Model;
use Qs_Db;
use Qs_Array;
use Qs_ImageFs;
use Qs_String;
use Zend_Db_Expr;
use App\User\AbstractObj;
use App\User\Module;
use App\User\Entity;

class Obj extends AbstractObj
{
    protected $_hasFilter = true;

    protected $_reorderTitleColumn = 'reorderLeadershipTitle';
    protected $_reorderIsGroupedItems = true;
    protected $_reorderGroupKeyColumn = 'leadershipGroupId';
    protected $_reorderGroupTitleColumn = 'leadershipGroupName';
    protected $_reorderGroupSorterColumn = 'LeadershipGroup.sorter';

    protected $_nullableFields = ['joinDate', 'leadershipGroupId'];

    public function getFilterFields()
    {
        if (!$this->_filterFields) {
            $filterFields = $this->getConfig('filterFields');
            $filterFields = ($filterFields) ? $filterFields->toArray() : [];
            $mode = $this->getConfig('mode');
            $this->_filterFields = Qs_Array::get($filterFields, (string) $mode, []);
        }
        return $this->_filterFields;
    }

    protected function _getReorderSelect()
    {
        $select = parent::_getReorderSelect();
        $select->join(
            $this->_getPair('LeadershipGroup'),
            '`LeadershipGroup`.`id` = `User`.`leadershipGroupId`',
            [
                'category'            => 'IFNULL(`LeadershipGroup`.`name`, "  --- ")',
                'categoryEnabled'     => 'IFNULL(`LeadershipGroup`.`enabled`, "y")',
                'leadershipGroupName' => 'name',
            ]
        );
        return $select;
    }

    protected function _filter(\Zend_Db_Select $select)
    {
        $select->where('`' . $this->_tableAlias . '`.`bought` = "y"');

        if ($committeeId = Qs_Array::get($this->_filter, 'committeeId')) {
            $select->where('EXISTS (' . $this->_getCommitteesFilterSelect($committeeId) . ')');
        }

        if ($listId = Qs_Array::get($this->_filter, 'listId')) {
            $select->where('EXISTS (' . $this->_getListsFilterSelect($listId) . ')');
        }

        return parent::_filter($select);
    }

    protected function _getCommitteesFilterSelect($committeeId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair($this->_tableAlias . 'Committee'), [new Zend_Db_Expr('1')]);
        $select->where('`' . $this->_tableAlias . 'Committee`.`userId` = `' . $this->_tableAlias . '`.`id`');
        $select->where('`' . $this->_tableAlias . 'Committee`.`committeeId` = ?', $committeeId, Qs_Db::INT_TYPE);
        $select->limit(1);
        return $select;
    }

    protected function _getListsFilterSelect($listId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair($this->_tableAlias . 'List'), [new Zend_Db_Expr('1')]);
        $select->where('`' . $this->_tableAlias . 'List`.`userId` = `' . $this->_tableAlias . '`.`id`');
        $select->where('`' . $this->_tableAlias . 'List`.`listId` = ?', $listId, Qs_Db::INT_TYPE);
        $select->limit(1);
        return $select;
    }

    protected function _getFromColumns()
    {
        $columns = parent::_getFromColumns();
        $columns['reorderLeadershipTitle'] = Model::getFullNameWithPositionDbExpr();
        return $columns;
    }

    /**
     * @return \Zend_Db_Select
     */
    protected function _getCompanySelect()
    {
        $select = $this->_db->select();
        $select->from(Qs_Db::getTableName('Company'), ['id', 'title' => Model::getCompanyTitleDbExpr()]);
        return $select;
    }

    /**
     * @param string $filter
     * @return array
     */
    public function getCompany4Select($filter)
    {
        $select = $this->_getCompanySelect();

        $filter = trim($filter);
        if ($filter) {
            $filter = '%' . str_replace(['%', '_'], ['\\%', '\\_'], $filter) . '%';
            $filterColumns = ['name', 'address', 'address2', 'city', 'state', 'zip', 'phone', 'fax'];
            foreach ($filterColumns as $column) {
                $select->orWhere('`' . $column . '` LIKE(?)', $filter);
            }
        }

        $select->order('name');
        $select->limit('30');

        return $this->_db->fetchPairs($select);
    }

    public function getCompanyTitleById($id)
    {
        $select = $this->_getCompanySelect();
        $select->where('`id` = ?', $id, Qs_Db::INT_TYPE);
        $row = $this->_db->fetchRow($select);
        return Qs_Array::get($row, 'title');
    }

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

    protected function _deleteDependency()
    {
        parent::_deleteDependency();
        $this->_deleteCommittees();
        $this->_deleteLists();
        return $this;
    }

    protected function _insertAddresses()
    {
        if ($this->isAssociationMode()) {
            $this->_insertAddress(Entity::ADDRESS_HOME);
        } else {
            parent::_insertAddresses();
        }
        return $this;
    }

    protected function _deleteAddresses()
    {
        if ($this->isAssociationMode()) {
            $this->_deleteAddress(Entity::ADDRESS_HOME);
        } else {
            parent::_deleteAddresses();
        }
        return $this;
    }

    protected function _insertCommittees()
    {
        $table = $this->_getTable($this->_tableAlias . 'Committee');
        $committees = Qs_Array::get($this->_data, 'committees');
        if ($committees) {
            foreach ($committees as $row) {
                if ('' != $row['committeeId'] && '' != $row['positionId']) {
                    $row['userId'] = $this->getPrimaryKey();
                    $table->insert($row);
                }
            }
        }
        return $this;
    }

    protected function _deleteCommittees()
    {
        $this->_deleteIds($this->_tableAlias . 'Committee', 'userId');
        return $this;
    }

    protected function _insertLists()
    {
        $table = $this->_getTable($this->_tableAlias . 'List');
        $lists = Qs_Array::get($this->_data, 'lists');
        if ($lists) {
            foreach ($lists as $listId) {
                $row['userId'] = $this->getPrimaryKey();
                $row['listId'] = $listId;
                $table->insert($row);
            }
        }
        return $this;
    }

    protected function _deleteLists()
    {
        $this->_deleteIds($this->_tableAlias . 'List', 'userId');
        return $this;
    }

    protected function _getSimpleExportColumns()
    {
        return $this->getConfig('export')->fields->{Module::MODE_SIMPLE}->toArray();
    }

    protected function _getAssociationExportColumns()
    {
        $columns = $this->getConfig('export')->fields->{Module::MODE_ASSOCIATION}->toArray();
        $dynamicColumns = [];

        // add committees
        $committees = $this->_getTable('Committee')->get4Select();
        foreach ($committees as $key => $title) {
            $dynamicColumns[Entity::EXPORT_COMMITTEE_PREFIX . $key] = 'Committee: ' . $title;
        }

        // add lists
        $lists = $this->_getTable('List')->get4Select();
        foreach ($lists as $key => $title) {
            $dynamicColumns[Entity::EXPORT_LIST_PREFIX . $key] = 'List: ' . $title;
        }

        $splitColumn = $this->getConfig('export')->dynamicFieldsAfter;
        if (false !== ($splitPos = Qs_Array::keyPos($splitColumn, $columns))) {
            $splitPos++;
            $after = array_slice($columns, 0, $splitPos, true);
            $before = array_slice($columns, $splitPos, null, true);
            $columns = $after + $dynamicColumns + $before;
        } else {
            $columns += $dynamicColumns;
        }

        return $columns;
    }

    public function getExportColumns()
    {
        if ($this->isAssociationMode()) {
            $columns = $this->_getAssociationExportColumns();
        } else {
            $columns = $this->_getSimpleExportColumns();
        }

        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();
        $select->from($this->_getPair());

        if ($this->isAssociationMode()) {
            $select->joinLeft(
                $this->_getPair('MembershipType', 'mt'),
                '`mt`.`id` = `User`.`membershipTypeId`',
                ['membershipType' => 'title']
            );

            $companyColumns = ['name', 'phone', 'fax', 'address', 'address2', 'city', 'state', 'zip'];
            $selectColumns = [];
            foreach ($companyColumns as $column) {
                $key = 'company' . ucfirst($column);
                $expr = 'IF (`User`.`companyId` IS NULL, `rc`.`' . $column . '`, `Company`.`' . $column . '`)';
                $selectColumns[$key] = new Zend_Db_Expr($expr);
            }
            $select->joinLeft($this->_getPair('Company'), '`Company`.`id` = `User`.`companyId`', []);
            $select->joinLeft(
                $this->_getPair($this->_tableAlias . 'RegistrationCompany', 'rc'),
                '`rc`.`userId` = `User`.`id`',
                $selectColumns
            );

            $select->joinLeft(
                $this->_getPair('LeadershipGroup', 'lg'),
                '`lg`.`id` = `User`.`leadershipGroupId`',
                ['leadershipGroup' => 'name']
            );
        }

        $select->joinLeft(
            $this->_getPair($this->_tableAlias . 'Status', 's'),
            '`s`.`id` = `User`.`status`',
            ['status' => 'title']
        );

        $select->where('`User`.`bought` = "y"');
        $this->_filter($select);

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

        return $select->query();
    }

    public function prepareExportRow(array &$data)
    {
        $this->_prepareBio($data['bio']);

        // prepare addresses
        $this->_addAddressesFromDb($data);
        if (array_key_exists(Entity::ADDRESS_SHIPPING, $data)
            && 'y' == Qs_Array::get($data[Entity::ADDRESS_SHIPPING], 'asBilling')
        ) {
            $data[Entity::ADDRESS_SHIPPING] = array_merge(
                $data[Entity::ADDRESS_SHIPPING],
                $data[Entity::ADDRESS_BILLING]
            );
        } else {
            $data[Entity::ADDRESS_SHIPPING]['asBilling'] = 'n';
        }
        foreach ([Entity::ADDRESS_BILLING, Entity::ADDRESS_SHIPPING, Entity::ADDRESS_HOME] as $type) {
            if (array_key_exists($type, $data)) {
                foreach ($data[$type] as $key => $val) {
                    $index = $type . ucfirst($key);
                    $data[$index] = $val;
                }
            }
        }

        // prepare lists
        $this->_addCommitteesFromDb($data);
        if (array_key_exists('committees', $data)) {
            $positions = $this->_getTable('CommitteePosition')->get4Select();
            foreach ($data['committees'] as $row) {
                $key = Entity::EXPORT_COMMITTEE_PREFIX . $row['committeeId'];
                $data[$key] = Qs_Array::get($positions, $row['positionId'], 'Unknown');
            }
        }

        // prepare committees
        $this->_addListsFromDb($data);
        if (array_key_exists('lists', $data)) {
            foreach ($data['lists'] as $listId) {
                $key = Entity::EXPORT_LIST_PREFIX . $listId;
                $data[$key] = 'y';
            }
        }

        // prepare photo link
        if (!empty($data['photo'])) {
            $data['photo'] = BASE_URL . '/' . Qs_ImageFs::get($data['photo']);
        }

        return $this;
    }

    protected function _prepareBio(&$bio)
    {
        $bio = strip_tags($bio);
        $bio = preg_replace("/[\n\r]+/", "\n", $bio);
        $bio = preg_replace("/\s+/", " ", $bio);

        return $this;
    }
}