<?php

namespace BRSystems\Core\App\index\inc;

use BRSystems\Core\Cms\cmsProducts\inc\cmsProductsModule;
use BRSystems\Core\Cms\cmsTextManager\inc\cmsTextManagerConst;
use BRSystems\Core\Library\cDb\cDb;

class indexModule
{
    public        $db;
    public string $dbBaseTitle;

    public function __construct()
    {
        $this->db          = cDb::getInstance();
        $this->dbBaseTitle = '`' . DB_NAME . '`';
    }

    /**
     * Function to get article data by type
     *
     * @param int $typeId - article page type id
     * @param int $langId - language id
     * @param boolean $bImg - indicates if we need image too or not
     * @param boolean $bRand - if we need random rows with limit
     * @param int $iLimit - how many random elements we need
     * @return array of data
     * @author Aivars Žukovskis <info@brsystems.lv>
     * @category APP Index
     * @version 1.1
     */
    public function getArticleData($typeId, $langId, $bImg = false, $bRand = false, $iLimit = null)
    {
        $sAdditionSelect   = $bImg ? ", maf.file_link as i" : "";
        $sAdditionLeftJoin = $bImg ?
            "LEFT JOIN {$this->dbBaseTitle}.mod_articles_files maf ON
                ma.id = maf.ref_id
                AND maf.file_disabled = '0'
                AND maf.file_type = 2
                AND maf.file_order = 0
            " : "";

        $sOrderBy = $bRand ? "RAND()" : "ma.a_npk ASC";
        $sLimit   = $iLimit !== null ? "LIMIT {$iLimit}" : "";

        $sSql = "
                SELECT
                    ma.id,
                    mar.a_title as t,
                    mar.a_lhtml as d,
                    mar.a_shtml as s,
                    mar.a_link as l,
                    mar.a_v as v
                    {$sAdditionSelect}
                FROM
                    {$this->dbBaseTitle}.mod_articles ma
                    LEFT JOIN {$this->dbBaseTitle}.mod_articles_records mar ON
                        ma.id = mar.a_id
                        AND mar.a_lang_id = {$langId}
                    {$sAdditionLeftJoin}
                WHERE
                    ma.a_disabled = '0'
                    AND ma.a_type = {$typeId}
                    AND mar.a_title IS NOT NULL
                    AND mar.a_lhtml IS NOT NULL
                ORDER BY
                    {$sOrderBy} {$sLimit}
        ";

        $this->db->query($sSql);
        return $this->db->getFieldBasedArrayFieldsAdv(
            'id',
            $bImg ? ['t', 'd', 'i', 'v', 'dt', 's', 'l'] : ['t', 'd', 'dt'],
        );
    }

    /**
     * Function to get file path
     *
     * @param int $id - file id
     * @return null|array of data
     * @author Aivars Žukovskis <info@brsystems.lv>
     * @category APP Index
     * @version 1
     */
    public function getFilePath($id)
    {
        return $this->db->_querySelectRow(
            $this->dbBaseTitle . '.mod_articles_files',
            ['file_name', 'file_link'],
            ['id' => $id],
        );
    }

    /*  Save Contact us form  */
    public function saveComment($vData)
    {
        return $this->db->_queryInsert($this->dbBaseTitle . '.mod_messages', $vData);
    }

    /*  Get Emails  */
    public function chkUserSubscription($email)
    {
        return $this->db->_querySelectOne(
            $this->dbBaseTitle . '.mod_users_subscription_mails',
            ['email_sub'],
            ['email' => $email],
        );
    }

    public function chkEmailExists($email)
    {
        return $this->db->_querySelectOne(
            $this->dbBaseTitle . '.mod_users_subscription_mails',
            ['id'],
            ['email' => $email],
        );
    }

    /*  Save Emails  */
    public function saveEmail($data)
    {
        return $this->db->_queryInsert($this->dbBaseTitle . '.mod_users_subscription_mails', $data);
    }

    /*  Disable Emails  */
    public function disableEmail($email)
    {
        return $this->db->_queryUpdate(
            $this->dbBaseTitle . '.mod_users_subscription_mails',
            ['email_sub' => 0, 'email_modified' => time()],
            ['email' => $email],
        );
    }

    /*  Enable Emails  */
    public function enableEmail($email)
    {
        return $this->db->_queryUpdate(
            $this->dbBaseTitle . '.mod_users_subscription_mails',
            ['email_sub' => 1, 'email_modified' => time()],
            ['email' => $email],
        );
    }


    /**
     * Function to check user by login
     *
     * @param array $vWhere - what to check
     * @return bool|int user id
     * @author Aivars Žukovskis <info@brsystems.lv>
     * @category APP Index
     * @version 1.1
     */
    public function chkUserByLoginData($vWhere)
    {
        return $this->db->_querySelectOne($this->dbBaseTitle . '.mod_users', ['id'], $vWhere);
    }

    /**
     * Function to insert data
     *
     * @param array $vInsert
     * @return bool|int user id
     * @author Aivars Žukovskis <info@brsystems.lv>
     * @category APP Index
     * @version 1.1
     */
    public function insertAccountToCreateData($vInsert)
    {
        return $this->db->_queryInsert($this->dbBaseTitle . '.mod_account_to_create', $vInsert);
    }

    /**
     * Function to activate user after registration link
     *
     * @param int $id
     * @return bool|int user id
     * @author Aivars Žukovskis <info@brsystems.lv>
     * @category APP Index
     * @version 1.1
     */
    public function activateUser($id)
    {
        if ($id) {
            return $this->db->_queryUpdate($this->dbBaseTitle . '.mod_users', ['user_activated' => 1], ['id' => $id]);
        }
        return false;
    }

    /**
     * Function to activate user after registration link
     *
     * @param array $vWhere
     * @return bool|int user id
     * @author Aivars Žukovskis <info@brsystems.lv>
     * @category APP Index
     * @version 1.1
     */
    public function chkAccountActivationListForPendingAccount($vWhere)
    {
        $sSql = "
            SELECT
              user_email,
              user_login
            FROM
              {$this->dbBaseTitle}.mod_account_to_create
            WHERE
              status = '0'
              AND (user_login = '{$vWhere['user_login']}' OR user_email = '{$vWhere['user_email']}')
        ";

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

    public function getAllCategoryButtons($lang, $showDefLang = false, $bImg = null)
    {
        $showDefLang = ($showDefLang ? "0" : "1");

        $sAdditionSelect   = $bImg ? ", maf.file_link as i" : "";
        $sAdditionLeftJoin = $bImg ?
            "LEFT JOIN mod_articles_files maf ON
                ma.id = maf.ref_id
                AND maf.file_disabled = '0'
                AND maf.file_type = 2
                AND maf.file_order = 0
            " : "";


        $sSql        = "
                SELECT
                    ma.id,
                    mac.a_title as t,
                    cat.a_title as ct,
                    mac.a_url_alias_id as alias_id,
                    CONCAT(url.url_fname, CASE WHEN ll.`default` = '1' AND {$showDefLang} THEN '' ELSE ll.lang_code END) as l
                    {$sAdditionSelect}
                FROM
                    mod_articles ma
                    LEFT JOIN mod_articles_records mac ON
                        ma.id = mac.a_id
                    LEFT JOIN mod_articles_records cat ON
                        cat.a_id = ma.a_cat
                        AND cat.a_lang_id = {$lang}
                    {$sAdditionLeftJoin}
                    LEFT JOIN site_map_urls url ON
                        mac.a_url_alias_id = url.id
                    LEFT JOIN site_languages ll ON
                        ll.id = mac.a_lang_id
                WHERE
                    mac.a_lang_id = {$lang}
                    AND ma.a_disabled = '0'
                    AND ma.a_type = " . cmsTextManagerConst::BASE_SUB_CATEGORIES . "
                    AND ma.id <> 2
                ORDER BY
                    ma.a_npk ASC, mac.a_title ASC
        ";

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

    public function getCatButtons($lang, $showDefLang = false)
    {
        $catLinks    = cmsProductsModule::TABLE_CATEGORY_LINKS;
        $showDefLang = ($showDefLang ? "0" : "1");
        $sSql        = "
                SELECT
                    DISTINCT url.id as url_id,
                    ma.id,
                    mac.a_title as t,
                    cat.a_title as ct,
                    mac.a_url_alias_id as alias_id,
                    CONCAT(url.url_fname, CASE WHEN ll.`default` = '1' AND {$showDefLang} THEN '' ELSE ll.lang_code END) as l,
                    COALESCE(pc.prod_count, 0) as prod_count,
                    maf.file_link as i
                FROM
                    mod_articles ma
                    LEFT JOIN mod_articles_files maf ON
                        ma.id = maf.ref_id
                        AND maf.file_disabled = '0'
                        AND maf.file_type = 2
                        AND maf.file_order = 0
                    LEFT JOIN (
                        SELECT
                            cc.cat_id,
                            COUNT(DISTINCT pp.id) as prod_count
                        FROM
                            mod_products pp
                            LEFT JOIN $catLinks cc ON
                                pp.id = cc.prod_id
                        WHERE
                            pp.prod_deleted = '0'
                            AND pp.prod_disabled = '0'
                        GROUP BY
                            cc.cat_id
                    ) pc ON
                        pc.cat_id = ma.id
                    LEFT JOIN mod_articles_records mac ON
                        ma.id = mac.a_id
                    LEFT JOIN mod_articles_records cat ON
                        cat.a_id = ma.a_cat
                        AND cat.a_lang_id = {$lang}
                    LEFT JOIN mod_articles ca ON
                        cat.a_id = ca.id
                    LEFT JOIN site_map_urls url ON
                        mac.a_url_alias_id = url.id
                    LEFT JOIN site_languages ll ON
                        ll.id = mac.a_lang_id
                WHERE
                    mac.a_lang_id = {$lang}
                    AND ma.a_disabled = '0'
                    AND ma.a_type = " . cmsTextManagerConst::BASE_SUB_CATEGORIES . "
                    AND ma.id <> 2
                ORDER BY
                    ca.a_npk ASC, ma.a_npk ASC, mac.a_title ASC
        ";

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

    public function getFeaturedProducts($lang, $limit = 16): array
    {
        $limit = (int)$limit;
        if ($limit < 1) {
            return [];
        }

        $sSql = "
            SELECT
                pp.id,
                price_data.prod_price,
                pp.prod_discount,
                pp.prod_discount_value,
                CASE WHEN pp.prod_discount = '1'
                    THEN ROUND(price_data.prod_price - (pp.prod_discount_value / 100 * price_data.prod_price), 2)
                    ELSE ROUND(price_data.prod_price, 2)
                END as prod_discount_price,
                pp.prod_created,
                pp.prod_available,
                ii.prod_key,
                ii.prod_name,
                ii.prod_description,
                ii.prod_sdescription,
                COALESCE(img.img_path, '') as img_path,
                CONCAT(su.url_fname, CASE WHEN sl.`default` = '1' THEN '' ELSE sl.lang_code END) as prod_url
            FROM
                mod_products pp
                INNER JOIN (
                    SELECT
                        cc.prod_id,
                        MIN(cc.prod_price) as prod_price
                    FROM
                        mod_products_to_count_and_classificators cc
                    WHERE
                        cc.prod_disabled = '0'
                    GROUP BY
                        cc.prod_id
                ) price_data ON
                    price_data.prod_id = pp.id
                LEFT JOIN mod_products_info ii ON
                    pp.id = ii.prod_id
                    AND ii.lang_id = {$lang}
                LEFT JOIN mod_products_images img ON
                    pp.id = img.prod_id
                    AND img.img_blocked = '0'
                    AND img.img_order = 1
                LEFT JOIN site_map_urls su ON
                    su.url_item_id = pp.id
                    AND su.url_type = 0
                    AND su.url_lang_id = {$lang}
                LEFT JOIN site_languages sl ON
                    sl.id = su.url_lang_id
            WHERE
                pp.prod_deleted = '0'
                AND pp.prod_disabled = '0'
                AND pp.prod_featured = '1'
            ORDER BY
                pp.id DESC
            LIMIT {$limit}
        ";

        $this->db->query($sSql);
        return $this->db->getFieldBasedArrayFieldsAdv(
            'id',
            ['id', 'prod_price', 'prod_discount', 'prod_discount_value', 'prod_discount_price', 'prod_created', 'prod_available', 'prod_key', 'prod_name', 'prod_description', 'prod_sdescription', 'img_path', 'prod_url'],
        ) ?: [];
    }

    public function getCategoriesUrls($ids, $lang): array
    {
        if (empty($ids) || !is_array($ids)) {
            return [];
        }

        $sSql = "
            SELECT
                url_item_id,
                url_fname
            FROM
                site_map_urls sm
            WHERE
                url_lang_id = {$lang}
                AND url_type = ". cmsTextManagerConst::MAP_URL_TYPE ."
                AND url_item_id IN (" . implode(',', $ids) . ")
        ";

        $this->db->query($sSql);
        return $this->db->getKeyValBasedArray('url_item_id', 'url_fname') ?: [];
    }

    public function countProductsInCategories($ids): array
    {
        if (empty($ids) || !is_array($ids)) {
            return [];
        }

        $sSql = "
            SELECT
                parent.id as cat_id,
                COUNT(DISTINCT cc.prod_id) as c
            FROM
                mod_articles parent
                INNER JOIN mod_articles sub ON
                    sub.a_cat = parent.id
                INNER JOIN " . cmsProductsModule::TABLE_CATEGORY_LINKS . " cc ON
                    cc.cat_id = sub.id
                INNER JOIN " . cmsProductsModule::TABLE_PRODUCTS . " pp ON
                    pp.id = cc.prod_id
            WHERE
                parent.id IN (" . implode(',', $ids) . ")
                AND pp.prod_deleted = '0'
                AND pp.prod_disabled = '0'
            GROUP BY
                parent.id
        ";

        $this->db->query($sSql);
        return $this->db->getKeyValBasedArray('cat_id', 'c') ?: [];
    }
}