<?php

namespace App\Company;

use Qs_Db_Table;
use Qs_Array;
use Qs_Db;
use Zend_Db_Expr;
use App\User\Model as UserModel;
use App\Company\Admin\View as AdminView;

class Model
{
    public static $emptyOption = ['' => 'Select One'];

    protected static $_companyTypes;
    protected static $_companyStatuses;
    protected static $_counties;

    public static function prepareFormData(array &$data)
    {
        if (array_key_exists('asPhysicalAddress', $data)) {
            if ('y' == $data['asPhysicalAddress']) {
                $data['mailingAddress']  = $data['address'];
                $data['mailingAddress2'] = $data['address2'];
                $data['mailingCity']     = $data['city'];
                $data['mailingState']    = $data['state'];
                $data['mailingZip']      = $data['zip'];
            } else {
                unset($data['asPhysicalAddress']);
            }
        }

        if (!empty($data['district'])) {
            foreach ($data['district'] as $key => $value) {
                $value = preg_split('/[, ]/', $value);
                $districts = self::getDistricts($key);
                array_walk($value, function (&$val) use ($districts) {
                    $val = trim($val);
                    if (!in_array($val, $districts)) {
                        $val = '';
                    }
                });
                $data['district'][$key] = array_filter($value);
            }
        }

        return;
    }

    public static function getCompanyTypes()
    {
        if (null === self::$_companyTypes) {
            self::$_companyTypes = (new Qs_Db_Table('CompanyType'))->get4Select();
        }
        return self::$_companyTypes;
    }

    public static function getCompanyStatuses()
    {
        if (null === self::$_companyStatuses) {
            self::$_companyStatuses = (new Qs_Db_Table('CompanyStatus'))->get4Select();
        }
        return self::$_companyStatuses;
    }

    public static function getCountiesBy(array $filter, $columns = ['id', 'title'])
    {
        $db = Qs_Db::getInstance();
        $select = $db->select();
        $select->from(Qs_Db::getPair('County'), $columns);
        Qs_Db::filter($select, $filter, 'County');
        return $db->fetchPairs($select);
    }

    public static function getCounties()
    {
        if (null === self::$_counties) {
            self::$_counties = (new Qs_Db_Table('County'))->get4Select();
        }
        return self::$_counties;
    }

    public static function getDistricts($type, $query = null, $exclude = [], $limit = null)
    {
        $where = ['`type` = ?' => $type];

        if ($query) {
            $query = (string) $query;
            $query = str_replace(['_', '%'], ['\\_', '\\%'], $query);
            $query = '%' . $query . '%';
            $where['`district` LIKE ?'] = $query;
        }

        if ($exclude) {
            $where['`district` NOT IN (?)'] = $exclude;
        }

        $table = new Qs_Db_Table('LegislativeDistrict');
        return $table->get4Select(['key' => 'district', 'district'], $where, 'district', $limit);
    }

    public static function createAddress(array $data, $fieldPrefix = '')
    {
        $addressFields = ['address', 'address2', 'city', 'state', 'zip'];
        $address = [];
        foreach ($addressFields as $field) {
            if ($fieldPrefix) {
                $key = $fieldPrefix . ucfirst($field);
            } else {
                $key = $field;
            }
            $address[$key] = Qs_Array::get($data, $key);
        }
        return $address;
    }

    public static function createAddress4Geocoding(array $data, $fieldPrefix = '')
    {
        $keys = [
            0 => 'address',
            1 => 'address2',
            2 => 'city',
            3 => 'state',
            4 => 'zip'
        ];

        if ('' != $fieldPrefix) {
            foreach ($keys as &$key) {
                $key = $fieldPrefix . ucfirst($key);
            }
            unset($key);
        }

        $address = $data[$keys[0]];
        if ($data[$keys[1]]) {
            $address .= ', ' . $data[$keys[1]];
        }
        $address .= ', ' . $data[$keys[2]];
        $address .= ', ' . $data[$keys[3]] . ' ' . $data[$keys[4]];

        return $address;
    }

    public static function getUserNameById($userId)
    {
        $db = Qs_Db::getInstance();

        $select = $db->select();
        $select->from(Qs_Db::getPair('User'), [UserModel::getFullNameWithNicknameExpr()]);
        $select->where('`User`.`id` = ?', (string) $userId, Qs_Db::INT_TYPE);

        return $db->fetchOne($select);
    }

    public static function getDistrictDataUrl()
    {
        $url = AdminView::getPage('url');
        $params['action'] = 'autocompleteDistrict';
        return ($url) ? ($url . '?' . http_build_query($params)) : null;
    }

    public static function getUserDataUrl(array $params = [])
    {
        if (empty($params['action'])) {
            $params['action'] = 'autocompleteUser';
        }
        $url = AdminView::getPage('url');
        return ($url) ? ($url . '?' . http_build_query($params)) : null;
    }

    public static function getMnHouseDistrictColumn($companyAlias = 'Company')
    {
        $cols = [new Zend_Db_Expr(
            'GROUP_CONCAT(DISTINCT `hd`.`district` ORDER BY `hd`.`district` SEPARATOR ", " )'
        )];

        $select = Qs_Db::getSelect();

        $select->from(Qs_Db::getPair('CompanyDistrict', 'hd'), $cols);
        $select->where('`hd`.`districtType` = ?', Entity::DISTRICT_TYPE_MN_HOUSE);
        $select->where('`hd`.`companyId` = `' . $companyAlias . '`.`id`');
        $select->group('hd.companyId');

        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getUsCongressDistrictColumn($companyAlias = 'Company')
    {
        $cols = [new Zend_Db_Expr(
            'GROUP_CONCAT(DISTINCT `cd`.`district` ORDER BY `cd`.`district` SEPARATOR ", " )'
        )];

        $select = Qs_Db::getSelect();

        $select->from(Qs_Db::getPair('CompanyDistrict', 'cd'), $cols);
        $select->where('`cd`.`districtType` = ?', Entity::DISTRICT_TYPE_US_CONGRESSIONAL);
        $select->where('`cd`.`companyId` = `' . $companyAlias . '`.`id`');
        $select->group('cd.companyId');

        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getMnHouseDistrictFilter(array $districts)
    {
        $select = Qs_Db::getSelect();

        $select->from(Qs_Db::getPair('CompanyDistrict', 'hdf'), [new Zend_Db_Expr('1')]);
        $select->where('`hdf`.`districtType` = ?', Entity::DISTRICT_TYPE_MN_HOUSE);
        $select->where('`hdf`.`companyId` = `Company`.`id`');
        $select->where('`hdf`.`district` IN (?)', $districts);

        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getUsCongressionalDistrictFilter(array $districts)
    {
        $select = Qs_Db::getSelect();

        $select->from(Qs_Db::getPair('CompanyDistrict', 'cdf'), [new Zend_Db_Expr('1')]);
        $select->where('`cdf`.`districtType` = ?', Entity::DISTRICT_TYPE_US_CONGRESSIONAL);
        $select->where('`cdf`.`companyId` = `Company`.`id`');
        $select->where('`cdf`.`district` IN (?)', $districts);

        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getUtilityCategoryFilter(array $categories)
    {
        $select = Qs_Db::getSelect();

        $select->from(Qs_Db::getPair('CompanyUtilityCategory', 'ucf'), [new Zend_Db_Expr('1')]);
        $select->where('`ucf`.`companyId` = `Company`.`id`');
        $select->where('`ucf`.`utilityCategoryId` IN (?)', $categories);

        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getAssociateCategoryFilter(array $categories)
    {
        $select = Qs_Db::getSelect();

        $select->from(Qs_Db::getPair('CompanyAssociateCategory', 'acf'), [new Zend_Db_Expr('1')]);
        $select->where('`acf`.`companyId` = `Company`.`id`');
        $select->where('`acf`.`categoryId` IN (?)', $categories);

        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getSafetyGroupFilter(array $groups)
    {
        $select = Qs_Db::getSelect();

        $select->from(Qs_Db::getPair('CompanySafetyGroup', 'sgf'), [new Zend_Db_Expr('1')]);
        $select->where('`sgf`.`companyId` = `Company`.`id`');
        $select->where('`sgf`.`safetyGroupId` IN (?)', $groups);

        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getCountiesFilter(array $countyIds)
    {
        $select = Qs_Db::getSelect();

        $select->from(Qs_Db::getPair('Company2County', 'c2c'), [new Zend_Db_Expr('1')]);
        $select->where('`c2c`.`companyId` = `Company`.`id`');
        $select->where('`c2c`.`countyId` IN (?)', $countyIds, Qs_Db::INT_TYPE);

        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getUtilityCategoryColumn()
    {
        $select = Qs_Db::getSelect();

        $cols = [new Zend_Db_Expr('GROUP_CONCAT(`uc`.`title` ORDER BY `uc`.`sorter` SEPARATOR ", " )')];
        $select->from(Qs_Db::getPair('CompanyUtilityCategory', 'cuc'), $cols);
        $select->join(Qs_Db::getPair('UtilityCategory', 'uc'), '`uc`.`id` = `cuc`.`utilityCategoryId`', []);
        $select->where('`cuc`.`companyId` = `Company`.`id`');
        $select->group('cuc.companyId');

        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getAssociateCategoryColumn()
    {
        $select = Qs_Db::getSelect();

        $cols = [new Zend_Db_Expr('GROUP_CONCAT(`ac`.`title` ORDER BY `ac`.`title` SEPARATOR ", " )')];
        $select->from(Qs_Db::getPair('CompanyAssociateCategory', 'cac'), $cols);
        $select->join(Qs_Db::getPair('AssociateCategory', 'ac'), '`ac`.`id` = `cac`.`categoryId`', []);
        $select->where('`cac`.`companyId` = `Company`.`id`');
        $select->group('cac.companyId');

        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getSafetyGroupColumn()
    {
        $select = Qs_Db::getSelect();

        $cols = [new Zend_Db_Expr('GROUP_CONCAT(`sg`.`title` ORDER BY `sg`.`title` SEPARATOR ", " )')];
        $select->from(Qs_Db::getPair('CompanySafetyGroup', 'csg'), $cols);
        $select->join(Qs_Db::getPair('SafetyGroup', 'sg'), '`sg`.`id` = `csg`.`safetyGroupId`', []);
        $select->where('`csg`.`companyId` = `Company`.`id`');
        $select->group('csg.companyId');

        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getUtilityStaffColumn()
    {
        $select = Qs_Db::getSelect();

        $cols = [new Zend_Db_Expr(
            'GROUP_CONCAT('
                . 'CONCAT_WS(": ", `pp`.`title`, ' . UserModel::getFullNameWithNicknameExpr() . ') '
                . 'ORDER BY `cus`.`sorter` '
                . 'SEPARATOR ", " '
            . ')'
        )];
        $select->from(Qs_Db::getPair('CompanyUtilityStaff', 'cus'), $cols);
        $select->join(Qs_Db::getPair('PersonnelPosition', 'pp'), '`pp`.`id` = `cus`.`positionId`', []);
        $select->join(Qs_Db::getPair('User'), '`User`.`id` = `cus`.`userId`', []);
        $select->where('`cus`.`companyId` = `Company`.`id`');
        $select->group('cus.companyId');

        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getPermissionColumn()
    {
        $select = Qs_Db::getSelect();

        $cols = [new Zend_Db_Expr(
            'GROUP_CONCAT('
                . UserModel::getFullNameWithNicknameExpr() . ' '
                . 'ORDER BY `User`.`lastName` '
                . 'SEPARATOR ", "'
            . ')'
        )];
        $select->from(Qs_Db::getPair('CompanyPermission', 'cp'), $cols);
        $select->join(Qs_Db::getPair('User'), '`User`.`id` = `cp`.`userId`', []);
        $select->where('`cp`.`companyId` = `Company`.`id`');
        $select->group('cp.companyId');

        return new Zend_Db_Expr('(' . $select . ')');
    }


    public static function getCountiesColumn($alias = 'Company')
    {
        $select = Qs_Db::getSelect();
        $select->from(Qs_Db::getPair('Company2County', 'c2c'), []);
        $select->join(Qs_Db::getPair('County', 'County'), 'County.id = c2c.countyId', ['GROUP_CONCAT(title SEPARATOR ", ")']);
        $select->where("c2c.companyId = {$alias}.id");
        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getAssociateCategory4Filter()
    {
        $select = Qs_Db::getSelect();

        $select->from(Qs_Db::getPair('AssociateCategory', 'ac'), ['id', 'title']);
        $select->join(Qs_Db::getPair('CompanyAssociateCategory', 'cac'), '`ac`.`id` = `cac`.`categoryId`', []);
        $select->join(Qs_Db::getPair('Company', 'c'), '`c`.`id` = `cac`.`companyId`', []);
        $select->where('`c`.`status` = ?', Entity::STATUS_ACTIVE);
        $select->where('`c`.`type` = ?', Entity::TYPE_ASSOCIATE);
        $select->order('ac.title');
        return Qs_Db::getInstance()->fetchPairs($select);
    }

    public static function saveCounties(array $list, $companyId)
    {
        $key = compact('companyId');
        $table = (new Qs_Db_Table('Company2County'));
        $table->deleteBy($key);
        $table->saveRelationIds($list, 'countyId', $key);
    }
}