<?php

namespace Velis\Lang;

use Exception;
use ReflectionException;
use Velis\Lang;
use Velis\Model\DataObject;
use Velis\Output;
use Velis\Xls;

/**
 * Lang value model
 *
 * @author Michał Ćwikliński <emce@velis.pl>
 * @author Olek Procki <olo@velis.pl>
 * @author Maciej Borowiec <maciej.borowiec@velistech.com>
 *
 * @property string $lang_group_id
 * @property string $lang_key_id
 * @property string $lang_id
 * @property string $value
 */
class Value extends DataObject
{
    /**
     * Default list order
     * @var string
     */
    protected static $_listDefaultOrder = 'lang_group_id, lang_key_id, lang_id';


    /**
     * Returns related sql table
     * @return string
     */
    protected function _getTableName()
    {
        return 'app.lang_value_tab';
    }


    /**
     * Returns datasource name
     *
     * @return string
     */
    protected function _getListDatasource()
    {
        return 'app.lang_value';
    }


    /**
     * Return array of primary keys
     * @return array
     */
    protected function _getPrimaryKeyField()
    {
        return ['lang_group_id','lang_key_id','lang_id'];
    }


    /**
     * String conversion
     * @return string
     */
    public function __toString()
    {
        return $this['value'] . '';
    }

    /**
     * Fetch translate langs to export
     * @param string|null $translateLang
     * @param array $params
     * @return string
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     * @throws ReflectionException
     * @throws \Velis\Exception
     */
    public static function exportToXls($translateLang = null, $params = [])
    {
        $xls = Xls::createSpreadsheet();
        $col = 'A';

        $cols = [
            'group',
            'key',
            'value_pl',
            'value_en',
        ];

        //Langs sheet
        $xls->getActiveSheet()->setTitle(Lang::get('LANG_TRANSLATIONS_TITLE'));
        $xls->setActiveSheetIndex(0);

        if (!$translateLang) {
            $cols[] = 'translation';
        } else {
            $additionalLangCol = 'value_' . $translateLang;
            $cols[] = $additionalLangCol;
        }

        foreach ($cols as $colLabel) {
            $xls->getActiveSheet()->setCellValue($col . '1', $colLabel);
            $xls->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
            $xls->getActiveSheet()->getStyle($col . '1')->applyFromArray(Output::getXlsHeadStyle());
            $col++;
        }

        $sheet = $xls->getActiveSheet();
        $xlsRow = 2;

        $data = Lang::getList(null, $params, null);

        foreach ($data as $groupId => $langGroup) {
            foreach ($langGroup as $keyId => $key) {
                $valuePl = $key['pl'];
                $valueEn = $key['en'];
                $valueAdditionalLang = $key[$translateLang] ?: null;

                $col = 'A';
                $sheet->setCellValue($col++ . $xlsRow, (string) $groupId);
                $sheet->setCellValue($col++ . $xlsRow, (string) $keyId);
                $sheet->setCellValue($col++ . $xlsRow, (string) $valuePl);
                $sheet->setCellValue($col++ . $xlsRow, (string) $valueEn);
                if (!$translateLang) {
                    $sheet->setCellValue($col++ . $xlsRow, '');
                } else {
                    $sheet->setCellValue($col++ . $xlsRow, (string) $valueAdditionalLang);
                }

                $xlsRow++;
            }
        }

        //Dictionaries sheet
        $xls->createSheet();
        $xls->setActiveSheetIndex(1);
        $xls->getActiveSheet()->setTitle(Lang::get('GENERAL_MENU_DICTIONARIES'));

        $translateColumn = 'column_translate_en';
        if ($translateLang) {
            $translateColumn = 'column_translate_' . $translateLang;
        }

        $cols2 = [
            'table_name',
            'column_name',
            'column_pl',
            'column_en',
            $translateColumn,
        ];

        $col = 'A';
        foreach ($cols2 as $colLabel) {
            $xls->getActiveSheet()->setCellValue($col . '1', $colLabel);
            $xls->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
            $xls->getActiveSheet()->getStyle($col . '1')->applyFromArray(Output::getXlsHeadStyle());
            $col++;
        }

        self::prepareDictionaryDataToExport($translateLang);

        $sheet = $xls->getActiveSheet();
        $row   = 2;

        $data = self::$_db->getAll('SELECT * FROM app.dictionary_export_tab');

        foreach ($data as $item) {
            $col = 'A';

            $sheet->setCellValue($col++ . $row, (string) $item['table_name']);
            $sheet->setCellValue($col++ . $row, (string) $item['column_name']);
            $sheet->setCellValue($col++ . $row, (string) $item['column_pl']);
            $sheet->setCellValue($col++ . $row, (string) $item['column_en']);
            $sheet->setCellValue($col++ . $row, (string) $item['column_translate']);

            $row++;
        }

        $xls->getActiveSheet()->freezePane('A2');

        // Save Excel file
        ob_start();
        $writer = Xls::createWriter($xls, Xls::FORMAT_XLS);
        $writer->save('php://output');

        return ob_get_clean();
    }


    public static function prepareDictionaryDataToExport($lang)
    {
        $params = [
            'language' => $lang ? : 'en'
        ];

        if (self::$_db->functionExists('app.export_dictionary')) {
            self::$_db->execFunction('app.export_dictionary', $params);
        }
    }


    /**
     * {@inheritDoc}
     */
    public static function getList($page = 1, $params = null, $order = null, $limit = self::ITEMS_PER_PAGE, $fields = null)
    {
        if ($params['update_date_from']) {
            self::$_listConditions[] = 'update_date >= :update_date_from';
            self::$_listParams['update_date_from'] = $params['update_date_from'];
        }

        if ($params['update_date_to']) {
            self::$_listConditions[] = 'update_date <= :update_date_to';
            self::$_listParams['update_date_to'] = $params['update_date_to'];
        }

        return parent::getList($page, $params, $order, $limit, $fields);
    }

    /**
     * Removes value
     */
    public function remove()
    {
        return parent::_remove();
    }

    /**
     * @return bool
     * @throws Exception
     */
    protected function isRequired(): bool
    {
        if (empty($this->lang_id)) {
            throw new Exception('Field lang_id is not set!');
        }

        return Lang::isRequired($this->lang_id);
    }

    /**
     * @return void
     * @throws Exception
     */
    public function change(): void
    {
        if (empty($this->value)) {
            if ($this->isRequired()) {
                throw new Exception(sprintf(Lang::get('GENERAL_LANG_REQUIRED'), mb_strtoupper($this->lang_id)));
            }

            $this->remove();
        } else {
            $this->add();
        }
    }

    public function getFullKey(): string
    {
        return sprintf('%s_%s', $this->lang_group_id, $this->lang_key_id);
    }
}
