<?php

class App_ViewControllerLog_Admin_Obj extends Qs_Db_Obj
{
    const ROLE_ADMIN = 'admin';
    const ROLE_USER = 'user';

    protected $_tableAlias = 'ViewControllerLog';
    protected $_tableShortAlias = 'vcl';

    public function setSelectOptions(array $options = array())
    {
        $order = (array) Qs_Array::get($options, 'order');
        $this->_fixSorter($order);
        if (null !== $this->getFilter('roleId')) {
            array_unshift($order, 's.changed DESC', 's.id');
        }
        $options['order'] = $order;
        return parent::setSelectOptions($options);
    }

    protected function _fixSorter(array &$order)
    {
        foreach ($order as $index => $option) {
            if (false !== strpos($option, 'logAdded')) {
                Qs_Array::insertAfter($order, $index, 'vcl.id' . (strpos($option, 'DESC') ? ' DESC' : ''));
                break;
            }
        }
        return $this;
    }

    protected function _getFromColumns()
    {
        $columns = parent::_getFromColumns();
        $columns['logAdded'] = 'added';
        return $columns;
    }

    public function getListSelect()
    {
        if (null === $this->_select) {
            $this->_select = parent::getListSelect();
            $this->_joinSession($this->_select);
            $this->_joinRole($this->_select);
            $this->_joinApplication($this->_select);
        }
        return $this->_select;
    }

    protected function _joinSession(Zend_Db_Select $select)
    {
        $select->join(
            $this->_getSubPair('Session', 's'),
            '`s`.`id` = ' . $this->_quoteField('sessionId'),
            array(
                'sessionId'      => 'id',
                'sessionClosed'  => 'closed',
                'sessionChanged' => 'changed',
                'sessionTitle'   => $this->_getSessionNameExpr('s')
            )
        );
    }

    protected function _joinRole(Zend_Db_Select $select)
    {
        $tableAlias = '';
        switch ($this->getFilter('role')) {
            case self::ROLE_ADMIN:
                $tableAlias = 'Admin';
                $roleName = 'CONCAT(`r`.`firstName`, " ", `r`.`lastName`)';
                break;
            case self::ROLE_USER:
                $tableAlias = 'User';
                $roleName = 'r.name';
                break;
            default:
                $this->_throw();
                break;
        }

        $select->join(
            $this->_getPair($tableAlias, 'r'),
            '`r`.`id` = ' . $this->_quoteField('roleId'),
            array('roleName' => $roleName)
        );

        return $this;
    }

    protected function _joinApplication(Zend_Db_Select $select)
    {
        $controller = $this->_quoteField('controller');
        $role = $this->_db->quote($this->getFilter('role'));
        $select->joinLeft(
            $this->_getPair('Application'),
            '`Application`.`class` = ' . $controller . ' AND FIND_IN_SET(' . $role . ', `Application`.`role`)',
            array('applicationTitle' => 'title')
        );
        return $this;
    }

    protected function _getRoleNameColumn($alias = null)
    {
        $column = '';
        $prefix = '';
        if (null !== $alias) {
            $prefix = $alias . '.';
        }

        switch ($this->getFilter('role')) {
            case self::ROLE_ADMIN:
                $parts = array(
                    $this->_db->quoteIdentifier($prefix . 'firstName'),
                    $this->_db->quote(' '),
                    $this->_db->quoteIdentifier($prefix . 'lastName'),
                );
                $column = 'CONCAT(' . implode(', ', $parts) . ')';
                break;
            case self::ROLE_USER:
                $column = $this->_db->quoteIdentifier($prefix . 'name');
                break;
            default:
                $this->_throw();
                break;
        }

        return $column;
    }

    public function getRoleOptions()
    {
        $options = array();
        switch ($this->getFilter('role')) {
            case self::ROLE_ADMIN:
                $options = $this->_get4Select('Admin', array('id', new Zend_Db_Expr($this->_getRoleNameColumn())));
                break;
            case self::ROLE_USER:
                $options = $this->_get4Select('User', array('id', new Zend_Db_Expr($this->_getRoleNameColumn())));
                break;
            default:
                $this->_throw();
                break;
        }
        return $options;
    }

    public function getControllerOptions()
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('Application'), array('class', 'title'));
        $select->order('Application.title');
        $select->where('FIND_IN_SET(?, `Application`.`role`)', $this->getFilter('role'));
        return $this->_db->fetchPairs($select);
    }

    public function getSessionOptions($filter = null)
    {
        $select = $this->_db->select();
        $select->from($this->_getSubPair('Session', 's'), array('id', 'title' => $this->_getSessionNameExpr()));
        if (null === $filter) {
            $filter = array(
                'role'       => $this->getFilter('role'),
                'roleId'     => $this->getFilter('roleId'),
                'controller' => $this->getFilter('controller'),
            );
            $filter = array_filter($filter);
        }
        Qs_Db::filter($select, $filter, 's');
        if (($controller = $this->getFilter('controller'))) {
            $select->where('EXISTS(' . $this->_getControllerSubSelect($controller) . ')');
        }
        $select->order('s.added DESC');
        return $this->_db->fetchPairs($select);
    }

    protected function _getControllerSubSelect($controller)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair(null, 'l'), new Zend_Db_Expr('1'));
        $select->where('`l`.`sessionId` = `s`.`id`');
        $select->where('`l`.`controller` = ?', $controller);
        return $select;
    }

    protected function _getSessionNameExpr($alias = null)
    {
        $added = Qs_Db::quoteField('added', $alias);
        $changed = Qs_Db::quoteField('changed', $alias);
        $timeFormat = '%h:%i %p';
        $dateTimeFormat = '%m/%d/%Y ' . $timeFormat;

        return "CONCAT(DATE_FORMAT({$added}, '{$dateTimeFormat}'), ' - ', "
            . "DATE_FORMAT({$changed}, '{$dateTimeFormat}'))";
    }

    public function getMinDate()
    {
        $select = $this->_db->select();
        $select->from($this->_getPair(), 'MIN(`added`)');
        return $this->_db->fetchOne($select);
    }

    public function getDataSize()
    {
        $config = Qs_Db::getConfig();
        $sql = 'SHOW TABLE STATUS '
             . 'FROM `' . $config->database->params->dbname . '` '
             . "LIKE '{$config->database->tablePrefix}ViewControllerLog%'";
        $list = Qs_Db::getInstance()->fetchAll($sql);
        return Qs_Array::sum($list, 'Data_length') + Qs_Array::sum($list, 'Index_length');
    }

    protected function _throw()
    {
        throw new Qs_Exception('Undefined role: ' . $this->getFilter('role'));
    }
}