<?php

namespace BRSystems\Core\Sys\sysTranslationsPub\inc;

use BRSystems\Core\Library\cDb\cDb;

/**
 * sysTranslationsPub Module
 *
 * @category sysTranslationsPub Module
 * @author Mareks Keišs <mareks.keiss@brsystems.lv>
 * @since Version 2.0
 */
class sysTranslationsPubModule
{
    public $db;

    /**
     * Class constructor
     */
    public function __construct() {

        $this->db = cDb::getInstance();

    }

    /**
     * Function to get module records
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category sysTranslationsPub Module
     * @param string $where - sql query
     * @param string $sort - sql query
     * @param int $offset - db table offset
     * @param int $limitPerPage - limit per page
     * @return array of data
     * @version 1.1
     */
    public function getModuleRecords($where, $sort, $offset = 0, $limitPerPage = 20){

            $sSql = "
                SELECT SQL_CALC_FOUND_ROWS
                    DISTINCT msg.id,
                    msg.msg_message,
                    map.links, map.rec_count
                FROM
                    site_pub_msg msg
                    LEFT JOIN site_pub_msg_translations msgt ON
                        msgt.msg_id = msg.id
                    LEFT JOIN (
                        SELECT 
                            ll.msg_id, GROUP_CONCAT(sm.p_title SEPARATOR '|') as links, COUNT(ll.msg_id) as rec_count
                        FROM
                            temp_pub_msg_links ll
                            LEFT JOIN site_map sm ON
                                    sm.id = ll.map_id
                            LEFT JOIN site_pub_msg mm ON
                                mm.id = ll.msg_id
                        WHERE 
                            mm.id is not null
                        GROUP BY 
                            ll.msg_id
                    ) map ON
                        map.msg_id = msg.id
                WHERE
                    1 {$where}
                ORDER BY
                    {$sort}
                LIMIT
                    {$offset}, {$limitPerPage}
                ";

            $this->db->query($sSql);
            return $this->db->getArray();
    }

    /**
     * Function to get module records
     *
     * @author Aivars Žukovskis <mareks.keiss@brsystems.lv>
     * @category sysTranslationsPub Module
     * @return array of data
     * @version 1.1
     */
    public function getRecordData(){

            $sSql = "
                    SELECT SQL_CALC_FOUND_ROWS
                        DISTINCT msg.id,
                        msg.msg_message,
                        ll.lang_code,
                        msgt.msg_translation
                    FROM
                        site_pub_msg msg
                        LEFT JOIN site_pub_msg_translations msgt ON
                            msgt.msg_id = msg.id
                        LEFT JOIN site_languages ll ON
				            msgt.msg_lang_id = ll.id
                ";

            $this->db->query($sSql);
            return $this->db->getFieldBasedArrayFields('id','lang_code',array('msg_message', 'msg_translation'));
    }

    /**
     * Function to get data by query and limit
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category APP pubLogistics
     * @param string $sQuery - route id
     * @param int $offset - route data
     * @param int $chunkSize - route data
     * @return array - record data
     * @version 1.1
     */
    public function getDataByQueryAndOffset($sQuery, $offset = 0, $chunkSize = 0){

        if(!!$sQuery){

            $this->db->query("
                {$sQuery}

                LIMIT
                    {$offset}, {$chunkSize}
            ");
            return $this->db->getArray();
        }
        return false;
    }

    /**
     * Function to get data by query and limit
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category APP pubLogistics
     * @return array - record data
     * @version 1.1
     */
    public function getBaseData(){


        $sSql = "
                SELECT SQL_CALC_FOUND_ROWS
                DISTINCT msg.id,
                   msg.msg_message as message
                FROM
                    site_pub_msg msg
        ";
        $this->db->query($sSql);
        return $this->db->getFieldBasedArray('id');

    }

    /**
     * Function to get data by query and limit
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category APP pubLogistics
     * @return array - record data
     * @version 1.1
     */
    public function getRecordTranslations(){

        $sSql = "
                SELECT
                    msgt.msg_id,
                    msgt.msg_lang_id,
                    msgt.msg_translation
                FROM
                    site_pub_msg_translations msgt
                ";

        $this->db->query($sSql);
        return $this->db->getArray();

    }

    /**
     * Function to get logistic object data and add to array cols
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category APP pubLogistics
     * @param string $sQuery - query to exec
     * @param int $offset - row offset
     * @param int $chunkSize - chunk size
     * @param array $data - data array
     * @param array $r2i - logistic id to col id
     * @param string|int $zero - zero value
     * @return array - record data
     * @version 1.1
     */
    public function addLogisticsObjectsToDataArray($sQuery, $offset = 0, $chunkSize = 0, &$data, $r2i, $zero = 0){
        if(!!$sQuery && !!$data){

            $this->db->query("
                {$sQuery}

                LIMIT
                    {$offset}, {$chunkSize}
            ");

            $pData = $this->db->getFieldBasedArrayFields('id', 'r', 'p');
            if(!!$pData && is_array($pData)){
                foreach($data as &$v){
                    foreach($r2i as $cId=>$rId) {  // col id & Row id
                        $v["p{$cId}"] = (isset($pData[$v['id']][$rId]) && $pData[$v['id']][$rId]? $pData[$v['id']][$rId] : $zero);
                    }

                }
                unset($v);
            }

            return $this->db->getArray();
        }
        return false;
    }

    /**
     * Function to add or save record data
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category sysTranslationsPub Module
     * @param int $id - record id for update
     * @param array $data - record data
     * @return boot record new/old id
     * @version 1.1
     */
    public function saveRecordDataById($id, $data){

        if($id){
            if(!$this->db->_queryUpdate('site_pub_msg', $data, array('id' => $id))){
                return false;
            }
        }else{
            if(!($id = $this->db->_queryInsert('site_pub_msg', $data))){
                return false;
            }
        }
        return $id;

    }

    /**
     * Function to get record data by id
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category sysTranslationsPub Module
     * @param int $id - record id
     * @return array - record data
     * @version 1.1
     */
    public function getRecordDataById($id){

        return $this->db->_querySelectRow('site_pub_msg', false, array('id' => $id));

    }

    /**
     * Function to get system languages array
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category sysTranslationsPub Module
     * @return array - record data
     * @version 1.1
     */
    public function getSysLanguages(){

        return $this->db->_querySelect('site_languages', false, array('type' => 0), array('site_languages.order' => 'ASC'));

    }

    /**
     * Function to get record translation data
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category sysTranslationsPub Module
     * @param int $msgId - message record id
     * @return array - record data
     * @version 1.1
     */
    public function getRecordTranslationsById($msgId){

        $sSql = "
                SELECT
                    msgt.msg_lang_id,
                    msgt.msg_translation
                FROM
                    site_pub_msg_translations msgt
                WHERE
                    msgt.msg_id = " . $this->db->prepData($msgId) . "
                ";

        $this->db->query($sSql);
        return $this->db->getFieldBasedArray('msg_lang_id');

    }

    /**
     * Function to delete all table records form db
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category sysTranslationsPub Module
     * @return bool true / false
     * @version 1.1
     */
    public function deleteAllRecords(){

        $this->db->execute("DELETE FROM `temp_pub_msg_links`");
        $this->db->execute("DELETE FROM `site_pub_msg_translations`");
        return $this->db->execute("DELETE FROM `site_pub_msg`");

    }

    /**
     * Function to delete all table records form db that are unused or untranslated
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category sysTranslationsPub Module
     * @return bool true / false
     * @version 1.1
     */
    public function cleanupAllRecords(){

        $this->db->executeMulti("
            SELECT 
                @lang_count:=COUNT(id)
            FROM
                `site_languages` ll
            WHERE 
                ll.type = '0';
           
            CREATE TEMPORARY TABLE tmp_id_to_delete_from_msg AS
            SELECT 
                msg.id, count_check.cc
            FROM
                `site_pub_msg` msg 
                LEFT JOIN `site_pub_msg_translations` msgt 
                        ON msg.id = msgt.msg_id 
                LEFT JOIN (
                        SELECT 
                            msg.id,COUNT(msg.id) cc
                        FROM
                            `site_pub_msg` msg 
                            LEFT JOIN `site_pub_msg_translations` msgt 
                                        ON msg.id = msgt.msg_id 
                        GROUP BY msg.id 
                ) count_check ON
                    count_check.id = msg.id
            WHERE 
                msg.msg_message = msgt.msg_translation 
                or msgt.msg_translation is null 
                or TRIM(msgt.msg_translation) = '' 
            GROUP BY msg.id 
            HAVING COUNT(msg.id) = count_check.cc;
        ", true);

        $this->db->query("SELECT COUNT(id) as total FROM tmp_id_to_delete_from_msg;");
        $rows = $this->db->getOne();

        $this->db->executeMulti("
            DELETE b_msg
            FROM `site_pub_msg` b_msg
            WHERE b_msg.id IN (SELECT id FROM tmp_id_to_delete_from_msg);
            
            DELETE t_msg
            FROM `site_pub_msg_translations` t_msg
            WHERE t_msg.msg_id IN (SELECT id FROM tmp_id_to_delete_from_msg);
            
            
            CREATE TEMPORARY TABLE tmp_links_to_delete_from_msg AS
            SELECT 
                *, COUNT(map_id) as cc
            FROM
                temp_pub_msg_links ll
            GROUP BY 
                ll.map_id, msg_id
            HAVING 
                cc > 1;
                
            DELETE pl
            FROM temp_pub_msg_links pl
            WHERE pl.msg_id IN (SELECT msg_id FROM tmp_links_to_delete_from_msg);
                
            INSERT INTO temp_pub_msg_links
            SELECT msg_id, map_id FROM tmp_links_to_delete_from_msg;
                
        ");

        return (is_numeric($rows) ? $rows : false);

    }

    /**
     * Function to delete table records form db
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category sysTranslationsPub Module
     * @param array $ids - record ids to delete
     * @return bool true / false
     * @version 1.1
     */
    public function deleteRecords($ids){

        $ids = implode(',', $ids);
        $this->db->execute("DELETE FROM `temp_pub_msg_links` WHERE `msg_id` IN ({$ids})");
        $this->db->execute("DELETE FROM `site_pub_msg_translations` WHERE `msg_id` IN ({$ids})");
        return $this->db->execute("DELETE FROM `site_pub_msg` WHERE `id` IN ({$ids})");

    }

    /**
     * Function to update message translations
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category sysTranslationsPub Module
     * @param int $msgId - record id
     * @param array $translations - record translations
     * @return bool true / false
     * @version 1.1
     */
    public function updateTranslations($msgId, $translations){

        $this->db->_queryDelete('site_pub_msg_translations', array('msg_id' => $msgId));
        return $this->db->_queryInsertArray('site_pub_msg_translations', $translations, false, true);

    }


    /**
     * Function to change record status in db table
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category sysTranslationsPub Module
     * @param array $ids - record ids to affect
     * @param string $field - field to update for status ENUM
     * @return bool true / false
     * @version 1.1
     */
    public function changeRecordsStatus($ids, $field){

        $time = time();
        $ids = implode(',', $ids);
        $status = $this->db->query("UPDATE site_menus msg SET {$field} = '1', menu_modified = '{$time}' WHERE {$field} = '0' AND id IN ({$ids})");
        if(!$status)
            return $status;
        $status = $this->db->query("UPDATE site_menus msg SET {$field} = '0', menu_modified = '{$time}' WHERE {$field} = '1' AND id IN ({$ids}) AND menu_modified <> '{$time}'");
        return $status;

    }


    /**
     * Function to reserve next x classificator table ids
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category CMS cmsClassificators
     * @param array $resRowsCount - tables rows to reserve
     * @return bool record new/old id
     * @version 1.1
     */
    public function rsvTableRows($resRowsCount){

        if($resRowsCount && $resRowsCount > 0){
            $this->db->query("SELECT COALESCE(MAX(id),0)+{$resRowsCount} FROM site_pub_msg");
            if($maxId = $this->db->getOne()){
                $this->db->query("INSERT INTO site_pub_msg (id, msg_message) VALUES ({$maxId}, -1)");
                return $maxId;
            }
        }
        return false;
    }

    public function getMaxId(){
        $this->db->query("SELECT MAX(id) FROM site_pub_msg");
        return $this->db->getOne();
    }
    /**
     * Function to add or save classificator row record data
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category CMS cmsClassificators
     * @param array/string $msg
     * @return array
     * @version 1.1
     */
    public function getMsgIdsByMsg($msg){

        $sSql = "
                SELECT
                    id,
                    msg_message
                FROM
                    site_pub_msg
                WHERE
                    msg_message IN ('" . implode("','", $this->db->prepData($msg, true)) . "')
                ";

        $this->db->query($sSql);
        return $this->db->getArray();
    }

    public function truncateTable($table){
        $this->db->execute("TRUNCATE TABLE {$table}");
    }

    /**
     * Function to add or save classificator row record data
     *
     * @author Mareks Keišs <mareks.keiss@brsystems.lv>
     * @category CMS cmsClassificators
     * @param string $table - table name
     * @param array $data - import data
     * @param array|bool $where - condition for removal
     * @return bool record new/old id
     * @version 1.1
     */
    public function saveTableImportData($table, $data, $where = false){

        if(!!$where && is_array($where))
            $this->db->_queryDelete($table, $where);
        $this->db->_queryInsertArray($table, $data, false, true);

    }
}