<?php

class DbGlobalization extends TModule {

    const CACHE_PREFIX = 'DbGlobalization';

    private $_connection;

    public function setConnection($dbModule) {
        $dbModule = $this->Application->getModule($dbModule);
        if (!($dbModule instanceof TDataSourceConfig)) {
            throw new TConfigurationException(
                Prado::localize(
                    'Invalid DB module ID for DbGlobalization'
                )
            );
        }
        $this->_connection = $dbModule->DbConnection;
    }

    public function getConnection() {
        if (!($this->_connection instanceof TDbConnection)) {
            throw new TConfigurationException(
                Prado::localize(
                    'Connection ID for DBGlobalization not set'
                )
            );
        }
        return $this->_connection;
    }

    private $_tableName = 'translations';
    private $_languageColumn = 'language';
    private $_categoryColumn = 'category';
    private $_stringColumn = 'string';
    private $_translationColumn = 'translation';

    public function getTableName() {
        return $this->_tableName;
    }

    public function getLanguageColumn() {
        return $this->_languageColumn;
    }

    public function getCategoryColumn() {
        return $this->_categoryColumn;
    }

    public function getStringColumn() {
        return $this->_stringColumn;
    }

    public function getTranslationColumn() {
        return $this->_translationColumn;
    }

    public function setTableName(string $value) {
        $this->_tableName = $value;
    }

    public function setLanguageColumn(string $value) {
        $this->_languageColumn = $value;
    }

    public function setCategoryColumn(string $value) {
        $this->_categoryColumn = $value;
    }

    public function setStringColumn(string $value) {
        $this->_stringColumn = $value;
    }

    public function setTranslationColumn(string $value) {
        $this->_translationColumn = $value;
    }

    private $_cache;

    public function setCache($value) {
        if (TPropertyValue::ensureBoolean($value)) {
            $this->_cache = $this->Application->getCache();
        }
    }

    public function getCache() {
        return $this->_cache;
    }

    protected $_cacheContent;

    protected function _loadCache(string $lang) {
        if ($this->Cache) {
            if (!$this->_cacheContent) {
                $this->_cacheContent = $this->Cache->get(self::CACHE_PREFIX . '.' . $lang);
            }
        }
        return $this->_cacheContent;
    }

    protected function _getTranslationFromCache(string $string, string $category, string $language=NULL) {
        if (!$language) {
            $language = $this->Application->getGlobalization()->getCulture();
        }
        $cache = $this->_loadCache($language);
        if (!$cache) {
            return NULL;
        }
        if (isset($cache[$category]) && isset($cache[$category][$string])) {
            return $cache[$category][$string];
        }
        return NULL;
    }

    public function rebuildCache() {
        if ($this->Cache && $this->_rebuildCache) {
            $dbTranslations = $this->_getTranslationsFromDB();
            $cache = [];
            foreach ($dbTranslations as $translation) {
                $language = $translation[$this->LanguageColumn];
                $category = $translation[$this->CategoryColumn];
                $string = $translation[$this->StringColumn];
                if (!isset($cache[$language])) {
                    $cache[$language] = [];
                }
                if (!isset($cache[$language][$category])) {
                    $cache[$language][$category] = [];
                }
                $cache[$language][$category][$string] = $translation[$this->TranslationColumn] ?: '';
            }
            foreach ($cache as $lang => $langCache) {
                $this->Cache->set(self::CACHE_PREFIX . '.' . $lang, $langCache);
            }
        }
    }

    protected function _initTranslationDB() {
        $this->Connection->setActive(TRUE);
        try {
            $tableInfo = $this->Connection->DbMetaData->getTableInfo(
                $this->TableName
            );
        } catch (TDbException $e) {
            $sql = 'CREATE TABLE %s ('
                . '%s VARCHAR(255),'
                . '%s VARCHAR(255),'
                . '%s VARCHAR(255),'
                . '%s TEXT,'
                . 'PRIMARY KEY (%s, %s, %s),'
                . 'INDEX (%s),'
                . 'INDEX (%s),'
                . 'INDEX (%s)'
                . ')';
            $tableName = $this->Connection->quoteTableName($this->TableName);
            $languageColumn = $this->Connection->quoteColumnName($this->LanguageColumn);
            $categoryColumn = $this->Connection->quoteColumnName($this->CategoryColumn);
            $stringColumn = $this->Connection->quoteColumnName($this->StringColumn);
            $translationColumn = $this->Connection->quoteColumnName($this->TranslationColumn);
            $this->Connection->createCommand(
                sprintf(
                    $sql,
                    $tableName,
                    $languageColumn, $categoryColumn, $stringColumn, $translationColumn,
                    $languageColumn, $categoryColumn, $stringColumn,
                    $languageColumn,
                    $categoryColumn,
                    $stringColumn
                )
            )->execute();
        }
    }

    protected function _getTranslationsFromDB(string $string=NULL, string $category=NULL, string $language=NULL) {
        $this->_initTranslationDB();
        $criteria = [];
        if ($string) {
            $criteria[$this->StringColumn] = $string;
        }
        if ($category) {
            $criteria[$this->CategoryColumn] = $category;
        }
        if ($language) {
            $criteria[$this->LanguageColumn] = $language;
        }
        $sql = 'SELECT * FROM ' . $this->Connection->quoteTableName($this->TableName);
        $params = [];
        if ($criteria) {
            $sql .= ' WHERE ';
            $sqlCriteria = [];
            foreach ($criteria as $column => $value) {
                $sqlCriteria[] = sprintf(
                    '%s = ?',
                    $this->Connection->quoteColumnName($column)
                );
                $params[] = $value;
            }
            $sql .= implode(' AND ', $sqlCriteria);
        }
        $command = $this->Connection->createCommand($sql);
        foreach ($params as $index => $param) {
            $command->bindValue($index + 1, $param);
        }
        return $command->query()->readAll();
    }

    protected function _insertTranslation(string $string, string $category, string $language) {
        $sql = sprintf(
            'INSERT INTO %s(%s, %s, %s) VALUES(?, ?, ?)',
            $this->Connection->quoteTableName($this->TableName),
            $this->Connection->quoteColumnName($this->LanguageColumn),
            $this->Connection->quoteColumnName($this->CategoryColumn),
            $this->Connection->quoteColumnName($this->StringColumn)
        );
        $command = $this->Connection->createCommand($sql);
        $command->bindValue(1, $language);
        $command->bindValue(2, $category);
        $command->bindValue(3, $string);
        $command->execute();
    }

    protected function _getTranslationFromDB(string $string=NULL, string $category=NULL, string $language=NULL) {
        if (!$language) {
            $language = $this->Application->getGlobalization()->getCulture();
        }
        $dbResult = $this->_getTranslationsFromDB($string, $category, $language);
        if ($dbResult) {
            return $dbResult[0][$this->TranslationColumn] ?: '';
        } else {
            $this->_insertTranslation($string, $category, $language);
            return NULL;
        }
        return NULL;
    }

    private $_rebuildCache = FALSE;

    public function getTranslation(string $string, string $category, string $language=NULL) {
        $cacheString = $this->_getTranslationFromCache($string, $category, $language);
        if ($cacheString !== NULL) {
            return $cacheString ?: $string;
        } else {
            $dbString = $this->_getTranslationFromDB($string, $category, $language);
            $this->_rebuildCache = TRUE;
            return $dbString ?: $string;
        }
        return $string;
    }

    public function init($config) {
        parent::init($config);
        $this->Application->attachEventHandler('OnEndRequest', [$this, 'rebuildCache']);
    }
}

?>