<?php

namespace App\User\Import;

class Obj extends \Qs_Db_Obj
{
    const ERROR_CODE_REQUIRED = 1;
    const ERROR_CODE_INVALID_EMAIL = 2;
    const ERROR_CODE_NOT_UNIQUE_EMAIL = 3;
    const ERROR_CODE_NOT_UNIQUE_MEMBER_ID = 4;
    const ERROR_CODE_INVALID_STATE = 5;

    protected $_tableAlias = 'User';
    protected $_importTableAlias = 'UserImport';
    protected $_importTableName;
    protected $_importErrors = array();
    protected $_importedRowsCount = 0;
    protected $_insertedRowsCount = 0;
    protected $_updatedRowsCount = 0;
    protected $_importedErrorRowsCount = 0;
    protected $_insertedUsersNames = array();

    protected $_requiredFields = array('ID', 'FIRST_NAME', 'LAST_NAME', 'EMAIL');
    protected $_fieldMap = array(
            'ID'             => 'memberId',
            'FIRST_NAME'     => 'firstName',
            'LAST_NAME'      => 'lastName',
            'MIDDLE_NAME'    => 'middleInitial',
            'SUFFIX'         => 'suffix',
            'EMAIL'          => 'email',
            'TITLE'          => 'title',
            'COMPANY'        => 'company',
            'ADDRESS_1'      => 'address',
            'CITY'           => 'city',
            'STATE_PROVINCE' => 'state',
            'ZIP'            => 'zip',
            'WORK_PHONE'     => 'phone',
            'JOIN_DATE'      => 'joinDate',
        );

    protected $_columns;
    /**
     * @var array [fieldName => null]
     */
    protected $_userFields;

    protected function _init()
    {
        $this->_importTableName = \Qs_Db::getConfig()->toArray()['database']['tablePrefix'] .  $this->_importTableAlias;
        parent::_init();
    }

    public function import()
    {
        if (!$this->_data['csvFile']) {
            return false;
        }

        $filePath = View::getFullImportFilePath($this->_data['csvFile']);

        $handle = fopen($filePath, 'r');
        if (!$handle) {
            return false;
        }

        $this->_columns = fgetcsv($handle);
        if (empty($this->_columns) || count($this->_columns) != count(array_filter($this->_columns))) {
            $this->_addError('Wrong file format');
            return false;
        }

        $this->_createTemporaryImportTable();
        $this->_importFromFile($filePath);
        return true;
    }

    public function dropTemporaryImportTable()
    {
        //drop temporary table
        $this->_db->query('DROP TABLE IF EXISTS ' . $this->_getTableName($this->_importTableAlias));
        return $this;

    }

    protected function _insertErrorCodeToDb($row, $errorCode)
    {
        switch ($errorCode) {
            case self::ERROR_CODE_REQUIRED:
                $message = 'Not all required fields are completed';
                break;
            case self::ERROR_CODE_INVALID_EMAIL:
                $message = 'Invalid email address';
                break;
            case self::ERROR_CODE_NOT_UNIQUE_EMAIL:
                $message = 'The member with such email already exists';
                break;
            case self::ERROR_CODE_NOT_UNIQUE_MEMBER_ID:
                $message = 'Member\'s ID is not unique';
                break;
            case self::ERROR_CODE_INVALID_STATE:
                $message = 'State value is incorrect';
                break;
            default:
                $message = '';
                break;
        }

        $sql = "
                UPDATE {$this->_importTableName}
                SET
                    `errorCode` = " . $errorCode . ",
                    `errorMessage` = " . $this->_db->quote($message) . ",
                    `processed` = 'y'
                WHERE `importId` = " . $row['importId'];

        $this->_db->query($sql);
        return $this;
    }

    protected function _stateExists($state)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('DState'), 'id')
            ->where('`id` = ?', $state)
            ->limit(1);
        return (bool) $this->_db->fetchOne($select);
    }

    protected function _markUserAsProcessed($user)
    {
        $this->_getTable($this->_importTableAlias)->update(array('processed' => 'y'),
                                                           array('importId = ' . $this->_db->quote($user['importId'])));
        return $this;
    }

    protected function _importCsvDataToTmpTable($filePath)
    {
        $sql = "
            LOAD DATA INFILE '{$filePath}'
            INTO TABLE `{$this->_importTableName}`
            FIELDS TERMINATED BY ','
            OPTIONALLY  ENCLOSED BY '\"'
            LINES TERMINATED BY '\n'
            IGNORE 1 LINES
        ";

        $this->_db->query($sql);

        //STEP 2
        $sql = "
            ALTER TABLE `{$this->_importTableName}`
            ADD `errorCode` CHAR(2) NULL DEFAULT NULL,
            ADD `errorMessage` VARCHAR(255) NULL DEFAULT NULL,
            ADD `processed` ENUM('n', 'y') NOT NULL DEFAULT 'n',
            ADD `importId` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
            ";

        $this->_db->query($sql);
    }

    public function getImportStatistic()
    {
        return array(
            'importedRowsCount'      => $this->_importedRowsCount,
            'importedErrorRowsCount' => $this->_importedErrorRowsCount,
            'insertedRowsCount'      => $this->_insertedRowsCount,
            'updatedRowsCount'       => $this->_updatedRowsCount,
            'insertedUsersNames'     => $this->_insertedUsersNames,
        );
    }

    protected function _importFromFile($filePath)
    {
        $this->_importCsvDataToTmpTable($filePath);

        $sql = "SELECT * FROM `{$this->_importTableName}`";
        $res = $this->_db->query($sql);

        $emailValidator = new \Zend_Validate_EmailAddress();
        $uniqueEmailValidator = new \Qs_Validate_Unique($this->_getTable('User'), 'email');

        while($user = $res->fetch()) {
            //required
            $this->_importedErrorRowsCount++;
            foreach ($this->_requiredFields as $field) {
                if (empty($user[$field])) {
                    $this->_insertErrorCodeToDb($user, self::ERROR_CODE_REQUIRED);
                    continue 2;
                }

            }

            $user['ID'] = trim($user['ID']);
            $user['EMAIL'] = trim($user['EMAIL']);

            //validate: email
            if (!$emailValidator->isValid($user['EMAIL'])) {
                $this->_insertErrorCodeToDb($user, self::ERROR_CODE_INVALID_EMAIL);
                continue;
            }

            $uniqueEmailValidator->setWhere('memberId IS NULL OR memberId != ' . $this->_db->quote($user['ID']));
            //unique: email
            if (!$uniqueEmailValidator->isValid($user['EMAIL'])) {
                $this->_insertErrorCodeToDb($user, self::ERROR_CODE_NOT_UNIQUE_EMAIL);
                continue;
            }

            //available: state
            if (!empty($user['STATE_PROVINCE']) && !$this->_stateExists($user['STATE_PROVINCE'])) {
                $this->_insertErrorCodeToDb($user, self::ERROR_CODE_INVALID_STATE);
                continue;
            }

            //insert new user if ok
            $this->_addUserData($user);
            $this->_markUserAsProcessed($user);
            $this->_importedErrorRowsCount--;
            $this->_importedRowsCount++;
        }

        $this->_setErrorsFromImport();

        return $this;
    }

    protected function _setErrorsFromImport()
    {
        $sql = "
            SELECT `ID`, `LAST_NAME`, `FIRST_NAME`, `errorCode`, `errorMessage` FROM `{$this->_importTableName}`
            WHERE `processed` = 'y' AND `errorCode` IS NOT NULL
            ORDER BY `LAST_NAME`, `FIRST_NAME`
        ";
        $res = $this->_db->query($sql);
        if (!$res) {
            return $this;
        }

        while ($user = $res->fetch()) {
            $name = $user['LAST_NAME'];
            if ($name && $user['FIRST_NAME']) {
                $name .= ', ';
            }
            $name .= $user['FIRST_NAME'];
            $message = $name . ' (ID: ' . $user['ID'] . '): '
                     . 'code ' . $user['errorCode'] . ' - ' . $user['errorMessage'];
            $this->_importErrors[] = $message;
        }
        return $this;
    }

    public function getImportErrors()
    {
        return $this->_importErrors;
    }

    protected function _isUserExists($memberId)
    {
        $select = $this->_db->select();
        $select->from($this->_getPair('User'), 'id')
            ->where('`memberId` = ?', $memberId)
            ->limit(1);
        return (bool)$this->_db->fetchOne($select);
    }

    protected function _getUsedFields()
    {
        if (null === $this->_userFields) {
            $fields = array_intersect_key($this->_fieldMap, array_fill_keys($this->_columns, null));
            $this->_userFields = array_fill_keys(array_values($fields), null);
        }
        return $this->_userFields;
    }

    protected function _addUserData($user)
    {
        $nullExpr = new \Zend_Db_Expr('NULL');
        $data = array(
            'memberId'      => $user['ID'],
            'firstName'     => $user['FIRST_NAME'],
            'lastName'      => $user['LAST_NAME'],
            'email'         => $user['EMAIL'],
            'middleInitial' => empty($user['MIDDLE_NAME']) ? $nullExpr : $user['MIDDLE_NAME'],
            'suffix'        => empty($user['SUFFIX']) ? $nullExpr : $user['SUFFIX'],
            'title'         => empty($user['TITLE']) ? $nullExpr : $user['TITLE'],
            'company'       => empty($user['COMPANY']) ? $nullExpr : $user['COMPANY'],
            'address'       => empty($user['ADDRESS_1']) ? $nullExpr : $user['ADDRESS_1'],
            'city'          => empty($user['CITY']) ? $nullExpr : $user['CITY'],
            'state'         => empty($user['STATE_PROVINCE']) ? $nullExpr : $user['STATE_PROVINCE'],
            'zip'           => empty($user['ZIP']) ? $nullExpr : $user['ZIP'],
            'phone'         => empty($user['WORK_PHONE']) ? $nullExpr : $user['WORK_PHONE'],
            'joinDate'      => empty($user['JOIN_DATE']) ? $nullExpr : date('Y-m-d', strtotime($user['JOIN_DATE'])),
        );

        $userId = $this->_isUserExists($data['memberId']);

        if ($userId) {
            $data = array_intersect_key($data, $this->_getUsedFields());
            $this->_getTable('User')->update($data, 'memberId = ' . $this->_db->quote($data['memberId']));
            $this->_updatedRowsCount++;
        } else {
            $this->insert($data);
            $this->_insertedRowsCount++;
            $this->_insertedUsersNames[] = $data['lastName'] . ' ' . $data['firstName'];
        }

        return $this;
    }

    protected function _createTemporaryImportTable()
    {
        $columns = array();
        foreach ($this->_columns as $column) {
            $columns[] = '`' . $column . '` VARCHAR(255) DEFAULT "" NOT NULL ';
        }
        $columnsSql = implode(', ', $columns);

        $sql = "
            DROP TABLE IF EXISTS `{$this->_importTableName}`;
            CREATE TABLE `{$this->_importTableName}` (
              {$columnsSql}
            ) engine MyIsam ;
        ";

        $this->_db->query($sql);
    }
}