<?php
    /**
     * Product Class
     *
     * @package Wojo Framework
     * @author wojoscripts.com
     * @copyright 2023
     * @version 5.50: Product.php, v1.00 11/1/2023 8:15 PM Gewa Exp $
     *
     */
    if (!defined('_WOJO')) {
        die('Direct access to this location is not allowed.');
    }
    
    class Product
    {
        const mTable = 'products';
        const xTable = 'payments';
        const iTable = 'images';
        const fTable = 'files';
        const cxTable = 'cart';
        const cdTable = 'cdkeys';
        const ivTable = 'invoices';
        
        const FS = 104857600;
        const FE = 'png,jpg,jpeg,bmp,zip,pdf,doc,docx,txt,xls,xlsx,rar,mp4,mp3';
        
        const MAXAUDIO = 20971520;
        const MAXIMG = 5242880;
        
        const DATA = '/data/';
        
        /**
         * index
         *
         * @return void
         */
        public function index(): void
        {
            
            $tpl = App::View(BASEPATH . 'view/');
            $tpl->dir = 'admin/';
            $tpl->title = Language::$word->META_M_PRODUCTS;
            $tpl->caption = Language::$word->META_M_PRODUCTS;
            $tpl->subtitle = null;
            
            $find = isset($_POST['find']) ? Validator::sanitize($_POST['find'], 'default', 30) : null;
            
            if (isset($_GET['letter']) and $find) {
                $letter = Validator::sanitize($_GET['letter'], 'string', 2);
                $counter = Database::Go()->count(self::mTable, "WHERE `title` LIKE '%" . trim($find) . "%' AND `title` REGEXP '^" . $letter . "'")->run();
                $where = "WHERE p.title LIKE '%" . trim($find) . "%'  AND p.title REGEXP '^" . $letter . "'";
                
            } elseif (isset($_POST['find'])) {
                $counter = Database::Go()->count(self::mTable, "WHERE `title` LIKE '%" . trim($find) . "%'")->run();
                $where = "WHERE p.title LIKE '%" . trim($find) . "%'";
                
            } elseif (isset($_GET['letter'])) {
                $letter = Validator::sanitize($_GET['letter'], 'string', 2);
                $where = "WHERE p.title REGEXP '^" . $letter . "'";
                $counter = Database::Go()->count(self::mTable, "WHERE `title` REGEXP '^" . $letter . "'")->run();
            } else {
                $counter = Database::Go()->count(self::mTable)->run();
                $where = null;
            }
            
            if (isset($_GET['order']) and count(explode('|', $_GET['order'])) == 2) {
                list($sort, $order) = explode('|', $_GET['order']);
                $sort = Validator::sanitize($sort, 'string', 16);
                $order = Validator::sanitize($order, 'string', 4);
                if (in_array($sort, array(
                    'title',
                    'price',
                    'sales'
                ))) {
                    $ord = ($order == 'DESC') ? ' DESC' : ' ASC';
                    $sorting = $sort . $ord;
                } else {
                    $sorting = ' created DESC';
                }
            } else {
                $sorting = ' created DESC';
            }
            
            $pager = Paginator::instance();
            $pager->items_total = $counter;
            $pager->default_ipp = App::Core()->perpage;
            $pager->path = Url::url(Router::$path, '?');
            $pager->paginate();
            
            $sql = "
            SELECT p.id, p.price, p.created, p.title, p.likes, p.category_id, p.thumb, GROUP_CONCAT(m.title SEPARATOR ', ') as memberships,
              (SELECT COUNT(product_id) FROM `" . Comment::mTable . '` WHERE `' . Comment::mTable . '`.product_id = p.id) as comments,
              (SELECT COUNT(x.product_id) FROM `' . self::xTable . '` as x WHERE x.product_id = p.id) as sales, c.name
              FROM `' . self::mTable . '` as p
              LEFT JOIN `' . Content::cTable . '` as c ON c.id = p.category_id
              LEFT JOIN `' . Membership::mTable . "` as m ON FIND_IN_SET(m.id, p.membership_id)
              $where
              GROUP BY p.id
              ORDER BY $sorting " . $pager->limit;
            
            $tpl->data = Database::Go()->rawQuery($sql)->run();
            $tpl->pager = $pager;
            
            $tpl->template = 'admin/product';
        }
        
        /**
         * new
         *
         * @return void
         */
        public function new(): void
        {
            $tpl = App::View(BASEPATH . 'view/');
            $tpl->dir = 'admin/';
            $tpl->title = Language::$word->PRD_NEW;
            $tpl->caption = Language::$word->PRD_NEW;
            $tpl->crumbs = ['admin', 'products', Language::$word->META_NEW];
            
            $tpl->membership_list = Database::Go()->select(Membership::mTable, array('id', 'title'))->run();
            $tpl->files = Database::Go()->select(self::fTable, array('id', 'alias', 'extension', 'type', 'filesize'))->orderBy('alias', 'ASC')->run();
            $tpl->tree = App::Content()->categoryTree();
            $tpl->droplist = Content::getCatCheckList($tpl->tree, 0, 0, '&#166;&nbsp;&nbsp;&nbsp;&nbsp;');
            $tpl->custom_fields = Content::renderCustomFields(0, 'product');
            Session::set('digitoken', Utility::randNumbers(6));
            
            $tpl->template = 'admin/product';
        }
        
        /**
         * edit
         *
         * @param int $id
         * @return void
         */
        public function edit(int $id): void
        {
            $tpl = App::View(BASEPATH . 'view/');
            $tpl->dir = 'admin/';
            $tpl->title = Language::$word->PRD_EDIT;
            $tpl->caption = Language::$word->PRD_EDIT;
            $tpl->crumbs = ['admin', 'products', 'edit'];
            
            if (!$row = Database::Go()->select(self::mTable)->where('id', $id, '=')->first()->run()) {
                if (DEBUG) {
                    $tpl->error = 'Invalid ID ' . ($id) . ' detected [' . __CLASS__ . ', ln.:' . __line__ . ']';
                } else {
                    $tpl->error = Language::$word->META_ERROR;
                }
                $tpl->template = 'admin/error';
            } else {
                $tpl->data = $row;
                $tpl->membership_list = Database::Go()->select(Membership::mTable, array('id', 'title'))->run();
                $tpl->files = Database::Go()->select(self::fTable, array('id', 'alias', 'extension', 'type', 'filesize'))->orderBy('alias', 'ASC')->run();
                $tpl->tree = App::Content()->categoryTree();
                $tpl->droplist = Content::getCatCheckList($tpl->tree, 0, 0, '&#166;&nbsp;&nbsp;&nbsp;&nbsp;', $row->categories);
                $tpl->cdkeys = Database::Go()->select(self::cdTable, array('cdkey'))->where('product_id', $row->id, '=')->run();
                $tpl->images = Database::Go()->select(self::iTable, array('id', 'name'))->where('parent_id', $row->id, '=')->orderBy('sorting', 'ASC')->run();
                $tpl->custom_fields = Content::renderCustomFields($id, 'product');
                
                $tpl->template = 'admin/product';
            }
        }
        
        /**
         * history
         *
         * @param int $id
         * @return void
         */
        public function history(int $id): void
        {
            $tpl = App::View(BASEPATH . 'view/');
            $tpl->dir = 'admin/';
            $tpl->title = Language::$word->META_M_HISTORY;
            $tpl->caption = Language::$word->META_M_HISTORY;
            $tpl->crumbs = ['admin', 'products', 'history'];
            
            if (!$row = Database::Go()->select(self::mTable)->where('id', $id, '=')->first()->run()) {
                if (DEBUG) {
                    $tpl->error = 'Invalid ID ' . ($id) . ' detected [' . __CLASS__ . ', ln.:' . __line__ . ']';
                } else {
                    $tpl->error = Language::$word->META_ERROR;
                }
                $tpl->template = 'admin/error';
            } else {
                $pager = Paginator::instance();
                $pager->items_total = Database::Go()->count(self::xTable)->where('product_id', $id, '=')->where('status', 1, '=')->run();
                $pager->default_ipp = App::Core()->perpage;
                $pager->path = Url::url(Router::$path, '?');
                $pager->paginate();
                
                $sql = "
                SELECT p.amount, p.tax, p.coupon, p.total, p.currency, p.created, p.user_id, CONCAT(u.fname,' ',u.lname) as name
                  FROM `" . self::xTable . '` as p
                  LEFT JOIN ' . User::mTable . ' as u ON u.id = p.user_id
                  WHERE p.product_id = ?
                  AND p.status = ?
                  ORDER BY p.created DESC
                  ' . $pager->limit;
                
                $tpl->row = $row;
                $tpl->data = Database::Go()->rawQuery($sql, array($id, 1))->run();
                $tpl->pager = $pager;
                
                $tpl->subtitle = Language::$word->META_M_HISTORY . ' <small> ' . $tpl->row->title . '</small>';
                $tpl->template = 'admin/product';
            }
        }
        
        /**
         * processItem
         *
         * @return void
         */
        public function processItem(): void
        {
            $validate = Validator::run($_POST);
            $validate
                ->set('title', Language::$word->NAME)->required()->string()->min_len(3)->max_len(100)
                ->set('slug', Language::$word->PRD_SLUG)->string()->min_len(3)->max_len(100)
                ->set('price', Language::$word->PRD_PRICE)->required()->float()
                ->set('sprice', Language::$word->PRD_SPRICE)->required()->float()
                ->set('expiry', Language::$word->PRD_EXPIRY)->required()->numeric()
                ->set('type', Language::$word->PRD_TYPE)->required()->string()
                ->set('expiry_type', Language::$word->PRD_EXPIRY)->required()->string()
                ->set('active', Language::$word->PUBLISHED)->required()->numeric();
            
            $validate
                ->set('body', Language::$word->PRD_BODY)->text('advanced')
                ->set('pbody', Language::$word->PRD_PBODY)->text('advanced')
                ->set('keywords', Language::$word->METAKEYS)->string(true, true)
                ->set('description', Language::$word->METADESC)->string(true, true)
                ->set('youtube', Language::$word->PRD_YTUBE)->string()
                ->set('tags', Language::$word->PRD_TAGS)->string()
                ->set('affiliate', Language::$word->PRD_AFFURL)->url()
                ->set('cdkeys', Language::$word->PRD_CDKEYS)->string(true, true);
            
            switch ($_POST['type']) {
                case 'affiliate' :
                    $validate->set('affiliate', Language::$word->PRD_AFFURL)->required()->url();
                    break;
                
                case 'cdkey' :
                    if (strlen($_POST['cdkeys']) === 0) {
                        Message::$msgs['cdkeys'] = Language::$word->PRD_CDKEYS;
                    }
                    break;
                
                default :
                    if (!array_key_exists('files', $_POST)) {
                        Message::$msgs['files'] = Language::$word->PRD_FILE_ERROR;
                    }
                    break;
            }
            
            if (!array_key_exists('categories', $_POST)) {
                Message::$msgs['categories'] = Language::$word->PRD_CAT_ERROR;
            }
            
            if (array_key_exists('tags', $_POST)) {
                $validate->set('tags', Language::$word->PRD_TAGS)->required()->string();
            }
            (Filter::$id) ? $this->_updateItem($validate) : $this->_addItem($validate);
        }
        
        /**
         * _addItem
         *
         * @param Validator $validate
         * @return void
         */
        private function _addItem(Validator $validate): void
        {
            $thumb = File::upload('thumb', self::MAXIMG, 'png,jpg,jpeg');
            $audio = File::upload('audio', self::MAXAUDIO, 'mp3');
            
            $safe = $validate->safe();
            Content::verifyCustomFields('product');
            
            if (count(Message::$msgs) === 0) {
                $data = array(
                    'title' => $safe->title,
                    'slug' => empty($safe->slug) ? Url::doSeo($safe->title) : Url::doSeo($safe->slug),
                    'category_id' => intval($_POST['categories'][0]),
                    'categories' => Utility::implodeFields($_POST['categories']),
                    'files' => $safe->type == 'normal' ? Utility::implodeFields($_POST['files']) : 0,
                    'membership_id' => array_key_exists('memberships', $_POST) ? Utility::implodeFields($_POST['memberships']) : -1,
                    'price' => $safe->price,
                    'sprice' => $safe->sprice,
                    'is_sale' => ($safe->sprice > 0) ? 1 : 0,
                    'body' => $safe->body,
                    'pbody' => $safe->pbody,
                    'tags' => array_key_exists('tags', $_POST) ? strtolower($safe->tags) : null,
                    'youtube' => $safe->youtube,
                    'expiry' => $safe->expiry,
                    'expiry_type' => $safe->expiry_type,
                    'active' => $safe->active,
                    'type' => $safe->type,
                    'affiliate' => $safe->affiliate,
                    'token' => Utility::randomString(32),
                    'keywords' => $safe->keywords,
                    'description' => $safe->description,
                );
                
                $temp_id = Session::get('digitoken');
                File::makeDirectory(UPLOADS . '/data/' . $temp_id . '/thumbs');
                
                //process thumb
                if (array_key_exists('thumb', $_FILES)) {
                    $item_path = UPLOADS . self::DATA . $temp_id . '/';
                    $tresult = File::process($thumb, $item_path, false);
                    try {
                        $img = new Image($item_path . $tresult['fname']);
                        $img->bestFit(App::Core()->thumb_w, App::Core()->thumb_h)->save($item_path . 'thumbs/' . $tresult['fname']);
                    } catch (Exception $e) {
                        Debug::addMessage('errors', '<i>Error</i>', $e->getMessage(), 'session');
                    }
                    $data['thumb'] = $tresult['fname'];
                }
                
                //process audio
                if (array_key_exists('audio', $_FILES)) {
                    $item_path = UPLOADS . self::DATA . $temp_id . '/';
                    $aresult = File::process($audio, $item_path, false);
                    $data['audio'] = $aresult['fname'];
                }
                
                $last_id = Database::Go()->insert(self::mTable, $data)->run();
                
                //process cd keys
                if ($safe->type == 'cdkey') {
                    $keysArray = array();
                    $keys = preg_split('/\r\n|[\r\n]/', $safe->cdkeys);
                    foreach ($keys as $key) {
                        $keysArray[] = array(
                            'product_id' => $last_id,
                            'cdkey' => trim($key),
                        );
                    }
                    Database::Go()->batch(Product::cdTable, $keysArray)->run();
                }
                
                // Start Custom Fields
                $fl_array = Utility::array_key_exists_wildcard($_POST, 'custom_*', 'key-value');
                $dataArray = array();
                if ($fl_array) {
                    $fields = Database::Go()->select(Content::cfTable)->where('section', 'product', '=')->run();
                    foreach ($fields as $row) {
                        $dataArray[] = array(
                            'product_id' => $last_id,
                            'field_id' => $row->id,
                            'field_name' => $row->name,
                            'section' => 'product',
                        );
                    }
                    Database::Go()->batch(Content::cfdTable, $dataArray)->run();
                    foreach ($fl_array as $key => $val) {
                        if (!empty($val)) {
                            $cfdata['field_value'] = Validator::sanitize($val);
                            Database::Go()->update(Content::cfdTable, $cfdata)->where('product_id', $last_id, '=')->where('field_name', str_replace('custom_', '', $key), '=')->run();
                        }
                    }
                }
                //process related categories
                $cdataArray = array();
                foreach ($_POST['categories'] as $item) {
                    $cdataArray[] = array(
                        'product_id' => $last_id,
                        'category_id' => $item
                    );
                }
                Database::Go()->batch(Content::crTable, $cdataArray)->run();
                
                //process gallery
                if ($rows = Database::Go()->select(self::iTable, array('id', 'parent_id'))->where('parent_id', Session::get('digitoken'), '=')->run()) {
                    $query = 'UPDATE `' . self::iTable . '` SET `parent_id` = CASE ';
                    $idlist = '';
                    foreach ($rows as $item):
                        $query .= ' WHEN id = ' . $item->id . ' THEN ' . $last_id;
                        $idlist .= $item->id . ',';
                    endforeach;
                    $idlist = substr($idlist, 0, -1);
                    $query .= '
						  END
						  WHERE id IN (' . $idlist . ')';
                    Database::Go()->rawQuery($query)->run();
                    
                    $images = Database::Go()->select(self::iTable, array('name'))->where('parent_id', $last_id, '=')->run('json');
                    Database::Go()->update(self::mTable, array('images' => $images))->where('id', $last_id, '=')->run();
                }
                
                //rename temp folder
                File::copyDirectory(UPLOADS . '/data/' . $temp_id . '/', UPLOADS . '/data/' . $last_id . '/');
                File::deleteRecursive(UPLOADS . '/data/' . $temp_id, true);
                
                if ($last_id) {
                    $message = Message::formatSuccessMessage($data['title'], Language::$word->PRD_ADDED_OK);
                    $json['type'] = 'success';
                    $json['title'] = Language::$word->SUCCESS;
                    $json['message'] = $message;
                    $json['redirect'] = Url::url('/admin/products');
                    Logger::writeLog($message);
                } else {
                    $json['type'] = 'alert';
                    $json['title'] = Language::$word->ALERT;
                    $json['message'] = Language::$word->NOPROCCESS;
                }
                print json_encode($json);
                
            } else {
                Message::msgSingleStatus();
            }
        }
        
        /**
         * _updateItem
         *
         * @param Validator $validate
         * @return void
         */
        private function _updateItem(Validator $validate): void
        {
            $thumb = File::upload('thumb', self::MAXIMG, 'png,jpg,jpeg');
            $audio = File::upload('audio', self::MAXAUDIO, 'mp3');
            
            $safe = $validate->safe();
            Content::verifyCustomFields('product');
            
            if (count(Message::$msgs) === 0) {
                $data = array(
                    'title' => $safe->title,
                    'slug' => empty($safe->slug) ? Url::doSeo($safe->title) : Url::doSeo($safe->slug),
                    'category_id' => intval($_POST['categories'][0]),
                    'categories' => Utility::implodeFields($_POST['categories']),
                    'files' => $safe->type == 'normal' ? Utility::implodeFields($_POST['files']) : 0,
                    'membership_id' => array_key_exists('memberships', $_POST) ? Utility::implodeFields($_POST['memberships']) : -1,
                    'price' => $safe->price,
                    'sprice' => $safe->sprice,
                    'is_sale' => ($safe->sprice > 0) ? 1 : 0,
                    'body' => $safe->body,
                    'pbody' => $safe->pbody,
                    'tags' => array_key_exists('tags', $_POST) ? strtolower($safe->tags) : null,
                    'youtube' => $safe->youtube,
                    'expiry' => $safe->expiry,
                    'expiry_type' => $safe->expiry_type,
                    'active' => $safe->active,
                    'images' => Database::Go()->select(self::iTable, array('name'))->where('parent_id', Filter::$id, '=')->run('json'),
                    'type' => $safe->type,
                    'affiliate' => $safe->affiliate,
                    'keywords' => $safe->keywords,
                    'description' => $safe->description,
                );
                
                //process thumb
                $row = Database::Go()->select(self::mTable, array('thumb', 'audio'))->where('id', Filter::$id, '=')->first()->run();
                if (array_key_exists('thumb', $_FILES)) {
                    $item_path = UPLOADS . self::DATA . Filter::$id . '/';
                    $tresult = File::process($thumb, $item_path, false);
                    File::deleteFile($item_path . $row->thumb);
                    File::deleteFile($item_path . 'thumbs/' . $row->thumb);
                    try {
                        $img = new Image($item_path . $tresult['fname']);
                        $img->bestFit(App::Core()->thumb_w, App::Core()->thumb_h)->save($item_path . 'thumbs/' . $tresult['fname']);
                    } catch (Exception $e) {
                        Debug::addMessage('errors', '<i>Error</i>', $e->getMessage(), 'session');
                    }
                    $data['thumb'] = $tresult['fname'];
                }
                
                //process audio
                if (array_key_exists('audio', $_FILES)) {
                    $item_path = UPLOADS . self::DATA . Filter::$id . '/';
                    $fresult = File::process($audio, $item_path, false);
                    File::deleteFile($item_path . $row->audio);
                    $data['audio'] = $fresult['fname'];
                }
                
                Database::Go()->update(self::mTable, $data)->where('id', Filter::$id, '=')->run();
                
                //process cd keys
                if ($safe->type == 'cdkey') {
                    $keysArray = array();
                    Database::Go()->delete(self::cdTable)->where('product_id', Filter::$id, '=')->run();
                    $keys = preg_split('/\r\n|[\r\n]/', $safe->cdkeys);
                    foreach ($keys as $key) {
                        $keysArray[] = array(
                            'product_id' => Filter::$id,
                            'cdkey' => trim($key),
                        );
                    }
                    Database::Go()->batch(Product::cdTable, $keysArray)->run();
                }
                
                // Start Custom Fields
                $fl_array = Utility::array_key_exists_wildcard($_POST, 'custom_*', 'key-value');
                if ($fl_array) {
                    foreach ($fl_array as $key => $val) {
                        $cfdata['field_value'] = Validator::sanitize($val);
                        Database::Go()->update(Content::cfdTable, $cfdata)->where('product_id', Filter::$id, '=')->where('field_name', str_replace('custom_', '', $key), '=')->run();
                    }
                }
                
                //process related categories
                Database::Go()->delete(Content::crTable)->where('product_id', Filter::$id, '=')->run();
                $dataArray = array();
                foreach ($_POST['categories'] as $item) {
                    $dataArray[] = array(
                        'product_id' => Filter::$id,
                        'category_id' => $item
                    );
                }
                Database::Go()->batch(Content::crTable, $dataArray)->run();
                $message = Message::formatSuccessMessage($data['title'], Language::$word->PRD_UPDATE_OK);
                Message::msgReply(Database::Go()->affected(), 'success', $message);
                
                Logger::writeLog($message);
            } else {
                Message::msgSingleStatus();
            }
        }
        
        /**
         * frontIndex
         *
         * @return int|mixed
         */
        public function frontIndex(): mixed
        {
            
            $sql = "
            SELECT p.id, p.price, p.sprice, p.is_sale, MAX(p.created) as created, p.title, p.slug, p.likes, p.category_id, p.body, p.thumb, p.type, p.token, p.affiliate, p.membership_id, GROUP_CONCAT(m.title SEPARATOR ', ') as memberships, c.name, c.slug as cslug
            FROM `" . self::mTable . '` as p
            LEFT JOIN `' . Content::cTable . '` as c ON c.id = p.category_id
            LEFT JOIN `' . Membership::mTable . '` as m ON FIND_IN_SET(m.id, p.membership_id)
            WHERE p.active =?
            GROUP BY p.id
            ORDER BY created
            DESC LIMIT 0,' . App::Core()->featured;
            
            $row = Database::Go()->rawQuery($sql, array(1))->run();
            
            return ($row) ? : 0;
        }
        
        /**
         * render
         *
         * @param string $slug
         * @return void
         */
        public function render(string $slug): void
        {
            $core = App::Core();
            $tpl = App::View(BASEPATH . 'view/');
            $tpl->dir = 'front/themes/' . $core->theme . '/';
            $tpl->title = Url::formatMeta(Language::$word->META_M_CART);
            $tpl->crumbs = [array(0 => Language::$word->HOME, 1 => ''), Language::$word->META_WELCOME];
            $tpl->keywords = null;
            $tpl->description = null;
            
            $sql = "
            SELECT p.id, p.created, p.price, p.sprice, p.is_sale, p.title, p.thumb, p.token, p.likes, p.ratings, FORMAT((likes/ratings),0) as stars, p.body, p.pbody, p.type, p.token, p.images, p.audio, p.youtube, p.affiliate, p.membership_id, p.keywords, p.description, c.slug as cslug, c.name, GROUP_CONCAT(m.title SEPARATOR ', ') as memberships,
                   (SELECT COUNT(product_id)
                    FROM `" . Comment::mTable . '`
                    WHERE `' . Comment::mTable . '`.product_id = p.id
                    AND `' . Comment::mTable . '`.active = 1) as comments
              FROM `' . Product::mTable . '` as p
              LEFT JOIN `' . Content::cTable . '` as c ON c.id = p.category_id
              LEFT JOIN `' . Membership::mTable . '` as m ON FIND_IN_SET(m.id, p.membership_id)
              WHERE p.slug = ?
              AND p.active = ?
              GROUP BY p.id
            ';
            
            if (!$tpl->row = Database::Go()->rawQuery($sql, array($slug, 1))->first()->run()) {
                if (DEBUG) {
                    $tpl->error = 'Invalid product slug  ' . ($slug) . ' detected [' . __CLASS__ . ', ln.:' . __line__ . ']';
                    $tpl->template = 'front/themes/' . $core->theme . '/error';
                } else {
                    $tpl->error = Language::$word->META_ERROR;
                    $tpl->title = Language::$word->META_ERROR;
                    $tpl->template = 'front/themes/' . $core->theme . '/404';
                }
            } else {
                $sql2 = '
                SELECT p.id, p.price, p.sprice, p.is_sale, p.title, p.slug, p.category_id, p.thumb, p.type, p.token, p.affiliate, p.membership_id, c.name, c.slug as cslug
                  FROM `' . self::mTable . '` as p
                  LEFT JOIN `' . Content::cTable . '` as c ON c.id = p.category_id
                  WHERE p.id NOT IN(' . $tpl->row->id . ')
                  AND p.is_sale = ?
                  AND p.active = ?
                  ORDER BY RAND()
                  LIMIT 3';
                
                $tpl->special = Database::Go()->rawQuery($sql2, array(1, 1))->run();
                $tpl->custom_fields = Content::renderCustomFieldsFront($tpl->row->id, 'product');
                $tpl->images = Utility::jSonToArray($tpl->row->images);
                
                $tpl->title = Url::formatMeta($tpl->row->title);
                $tpl->keywords = $tpl->row->keywords;
                $tpl->description = $tpl->row->description;
                $tpl->crumbs = [array(0 => Language::$word->HOME, 1 => ''), $tpl->row->title];
                
                $tpl->meta = '
                  <meta property="og:type" content="article" />
                  <meta property="og:title" content="' . $tpl->row->title . '" />
                  <meta property="og:image" content="' . UPLOADURL . '/data/' . $tpl->row->id . '/thumbs/' . $tpl->row->thumb . '" />
                  <meta property="og:description" content="' . $tpl->row->title . '" />
                  <meta property="og:url" content="' . Url::url('/product', $slug) . '" />
			    ';
                
                $tpl->template = 'front/themes/' . $core->theme . '/product';
            }
        }
        
        /**
         * cart
         *
         * @return void
         */
        public function cart(): void
        {
            $core = App::Core();
            $tpl = App::View(BASEPATH . 'view/');
            $tpl->dir = 'front/themes/' . $core->theme . '/';
            $tpl->title = Url::formatMeta(Language::$word->META_M_CART);
            $tpl->crumbs = [array(0 => Language::$word->HOME, 1 => ''), Language::$word->META_M_CART];
            $tpl->keywords = null;
            $tpl->description = null;
            
            $tpl->data = self::getCartContent();
            $tpl->totals = self::getCartTotal();
            $tpl->tax = Content::calculateTax();
            $tpl->special = self::onSale();
            if (App::Auth()->is_User()) {
                $tpl->gateways = Database::Go()->select(Core::gTable)->where('active', 1, '=')->run();
            }
            
            $tpl->template = 'front/themes/' . $core->theme . '/cart';
        }
        
        /**
         * Compare
         *
         * @param string $ids
         * @return int|mixed
         */
        public function compare(string $ids): mixed
        {
            
            $sql = "
            SELECT p.id, p.price, p.sprice, p.is_sale, MAX(p.created) as p_created, p.title, p.slug, p.likes, p.category_id, p.body, p.thumb, p.type, p.token, p.affiliate, p.membership_id,
                   GROUP_CONCAT(m.title SEPARATOR ', ') as memberships, c.name, c.slug as cslug
              FROM `" . self::mTable . '` as p
              LEFT JOIN `' . Content::cTable . '` as c ON c.id = p.category_id
              LEFT JOIN `' . Membership::mTable . '` as m ON FIND_IN_SET(m.id, p.membership_id)
              WHERE p.id IN (' . $ids . ')
              AND p.active = ?
              GROUP BY p.id
              ORDER BY p_created
              DESC LIMIT 0, 6
            ';
            
            $row = Database::Go()->rawQuery($sql, array(1))->run();
            
            return ($row) ? : 0;
        }
        
        /**
         * search
         *
         * @param string $string
         * @return int|mixed
         */
        public function search(string $string): mixed
        {
            $sql = "
            SELECT p.id, p.price, p.sprice, p.is_sale, MAX(p.created) as p_created, p.title, p.slug, p.likes, p.category_id, p.body, p.thumb, p.type, p.token, p.affiliate, p.membership_id,
                   GROUP_CONCAT(m.title SEPARATOR ', ') AS memberships, c.name, c.slug as cslug
              FROM `" . self::mTable . '` AS p
              LEFT JOIN `' . Content::cTable . '` AS c ON c.id = p.category_id
              LEFT JOIN `' . Membership::mTable . "` AS m ON FIND_IN_SET(m.id, p.membership_id)
              WHERE p.active = ?
              AND MATCH (p.title, p.body) AGAINST ('" . $string . "' IN BOOLEAN MODE)
              GROUP BY p.id
              ORDER BY p_created DESC
              LIMIT 20
            ";
            
            $row = Database::Go()->rawQuery($sql, array(1))->run();
            
            return ($row) ? : 0;
        }
        
        /**
         * onSale
         *
         * @return int|mixed
         */
        public static function onSale(): mixed
        {
            
            $sql = '
            SELECT p.id, p.price, p.sprice, p.is_sale, p.title, p.slug, p.category_id, p.thumb, p.type, p.token, p.affiliate, p.membership_id, c.name, c.slug as cslug
              FROM `' . self::mTable . '` as p
              LEFT JOIN `' . Content::cTable . '` as c ON c.id = p.category_id
              WHERE p.is_sale = ?
              AND p.active = ?
              ORDER BY RAND()
              LIMIT 4';
            
            $row = Database::Go()->rawQuery($sql, array(1, 1))->run();
            
            return ($row) ? : 0;
            
        }
        
        /**
         * addToCart
         *
         * @param int $id
         * @return void
         */
        public static function addToCart(int $id): void
        {
            
            $sql = '
            SELECT id, title, slug, price, sprice, thumb, type, affiliate, is_sale, membership_id
              FROM `' . self::mTable . '`
              WHERE id = ?
              AND membership_id = ?
              AND active = ?
            ';
            
            if ($row = Database::Go()->rawQuery($sql, array($id, '-1', 1))->first()->run()) {
                if ($row->type == 'affiliate') {
                    Url::redirect($row->affiliate);
                    exit;
                } else {
                    if ($row->type == 'cdkey') {
                        $keys = Database::Go()->count(self::cdTable)->where('product_id', $row->id, '=')->run();
                        if (Validator::compareNumbers(intval($_POST['qty']), $keys, 'gt')) {
                            $json['status'] = 'error';
                            $json['type'] = 'error';
                            $json['title'] = Language::$word->ERROR;
                            $json['message'] = str_replace('[KEYS]', $keys, Language::$word->FRONT_CART_ERROR1);
                            print json_encode($json);
                            exit;
                        }
                    }
                    
                    $tax = Content::calculateTax();
                    $price = ($row->is_sale) ? $row->sprice : $row->price;
                    $data = array();
                    
                    for ($i = 0; $i < intval($_POST['qty']); $i++) {
                        $data[] = array(
                            'user_id' => App::Auth()->sesid,
                            'product_id' => $row->id,
                            'originalprice' => Validator::sanitize($price, 'float'),
                            'tax' => Validator::sanitize($tax, 'float'),
                            'totaltax' => Validator::sanitize($price * $tax, 'float'),
                            'total' => Validator::sanitize($price, 'float'),
                            'totalprice' => Validator::sanitize($tax * $price + $price, 'float'),
                        );
                    }
                    Database::Go()->batch(self::cxTable, $data)->run();
                    
                    $tpl = App::View(THEMEBASE . '/snippets/');
                    $tpl->template = 'cart';
                    $tpl->data = Product::getCartContent();
                    
                    $json['html'] = $tpl->render();
                    $json['status'] = 'success';
                    $json['title'] = Language::$word->SUCCESS;
                    $json['type'] = 'success';
                    $json['message'] = str_replace('[NAME]', $row->title, Language::$word->FRONT_CART_OK);
                    $json['counter'] = Product::cartCounter();
                    print json_encode($json);
                }
            } else {
                $json['status'] = 'error';
                $json['type'] = 'error';
                $json['title'] = Language::$word->ERROR;
                $json['message'] = Language::$word->FRONT_CART_ERROR;
                print json_encode($json);
            }
        }
        
        /**
         * getCartContent
         *
         * @param string|null $user_id
         * @return int|mixed
         */
        public static function getCartContent(string|null $user_id = ''): mixed
        {
            $sql = '
            SELECT c.total, p.id AS pid, p.title, p.slug, p.thumb, COUNT(*) AS items
              FROM `' . self::cxTable . '` AS c
              LEFT JOIN `' . self::mTable . '` AS p ON p.id = c.product_id
              WHERE c.user_id = ?
              GROUP BY c.product_id, c.total
              ORDER BY c.product_id DESC
            ';
            
            $row = Database::Go()->rawQuery($sql, array($user_id ? : App::Auth()->sesid))->run();
            
            return ($row) ? : 0;
        }
        
        /**
         * getCartContentIpn
         *
         * @param string|null $user_id
         * @return int|mixed
         */
        public static function getCartContentIpn(string|null $user_id = ''): mixed
        {
            $sql = '
            SELECT c.total, c.totalprice, c.coupon, c.totaltax, p.id, p.title
              FROM `' . self::cxTable . '` as c
              LEFT JOIN `' . self::mTable . '` as p ON p.id = c.product_id
              WHERE c.user_id = ?
              ORDER BY c.product_id DESC
            ';
            
            $row = Database::Go()->rawQuery($sql, array($user_id ? : App::Auth()->sesid))->run();
            
            return ($row) ? : 0;
        }
        
        /**
         * getCartTotal
         *
         * @param string|null $user_id
         * @return int|mixed
         */
        public static function getCartTotal(string|null $user_id = ''): mixed
        {
            $sql = '
            SELECT order_id, cart_id, SUM(coupon) as discount, SUM(totaltax) as tax, SUM(total) as subtotal, SUM(totalprice) as grand, COUNT(id) as items
              FROM `' . self::cxTable . '`
              WHERE user_id = ?
              GROUP BY user_id, order_id, cart_id
            ';
            
            $row = Database::Go()->rawQuery($sql, array($user_id ? : App::Auth()->sesid))->first()->run();
            
            return ($row) ? : 0;
        }
        
        /**
         * cartCounter
         *
         * @return int
         */
        public static function cartCounter(): int
        {
            $sql = '
            SELECT COUNT(id) as items
              FROM `' . self::cxTable . '`
              WHERE user_id = ?
              AND membership_id = ?
              GROUP BY user_id';
            
            $row = Database::Go()->rawQuery($sql, array(App::Auth()->sesid, 0))->first()->run();
            
            return ($row) ? $row->items : 0;
        }
        
        /**
         * fileAccess
         *
         * @param string $token
         * @return int|mixed
         */
        public static function fileAccess(string $token): mixed
        {
            $sql = "
            SELECT SUM(t.qty) as counter, t.created, t.status, t.ip, t.cdkey, t.id as tid, SUM(t.downloads) as file_downloads, FROM_UNIXTIME(t.file_date, '%M %e %Y, %H:%i') as registered, MAX(t.file_date) as file_date, p.id as pid, p.title, p.files, p.thumb, p.body, p.description, p.expiry, p.expiry_type, p.active as pactive, p.price, p.type, p.slug
              FROM `" . self::xTable . '` as t
              LEFT JOIN `' . self::mTable . '` as p ON t.product_id = p.id
              WHERE t.id = ?
              AND t.user_id = ?
              AND t.status = ?
              AND p.active = ?
              GROUP BY p.id, t.id
            ';
            
            $id = $token ? : 0;
            $row = Database::Go()->rawQuery($sql, array($id, App::Auth()->uid, 1, 1))->first()->run();
            
            return $row ? : 0;
        }
        
        /**
         * relatedFiles
         *
         * @param array $ids
         * @return int|mixed
         */
        public static function relatedFiles(array $ids): mixed
        {
            $row = Database::Go()->select(self::fTable)->where('id', $ids, 'IN')->run();
            
            return $row ? : 0;
            
        }
        
        /**
         * fileIcon
         *
         * @param string $type
         * @param string $size
         * @return string
         */
        public static function fileIcon(string $type, string $size = 'big'): string
        {
            return match ($type) {
                'image', 'jpg', 'png', 'jpeg', 'bmp', 'ai', 'psd' => "<i class=\"icon $size card image\"></i>",
                'video', 'mov', 'avi', 'flv', 'mp4', 'mpeg', 'wmv' => "<i class=\"icon $size play\"></i>",
                'audio', 'mp3', 'wav', 'aiff', 'ogg', 'wma', 'flac', 'm4a', 'm4b', 'm4p' => "<i class=\"icon $size music note list\"></i>",
                'document', 'text', 'txt', 'doc', 'docx', 'xls', 'xlsx', 'pdf' => "<i class=\"icon $size files\"></i>",
                'archive', 'application', 'zip', 'rar' => "<i class=\"icon $size archive\"></i>",
                default => "<i class=\"icon $size file\"></i>",
            };
        }
        
        /**
         * fileStyle
         *
         * @param string $type
         * @return string
         */
        public static function fileStyle(string $type): string
        {
            return match ($type) {
                'image', 'jpg', 'png', 'jpeg', 'bmp', 'ai', 'psd' => ' #e91e63',
                'video', 'mov', 'avi', 'flv', 'mp4', 'mpeg', 'wmv' => ' #3f51b5',
                'audio', 'mp3', 'wav', 'aiff', 'ogg', 'wma', 'flac', 'm4a', 'm4b', 'm4p' => ' #03a9f4',
                'document', 'text', 'txt', 'doc', 'docx', 'xls', 'xlsx', 'pdf' => ' #8bc34a',
                'archive', 'application', 'zip', 'rar' => ' #607d8b',
                default => ' #475467',
            };
        }
        
        /**
         * hasThumb
         *
         * @param string|null $thumb
         * @param int $id
         * @return string
         */
        public static function hasThumb(null|string $thumb, int $id): string
        {
            return ($thumb) ? UPLOADURL . '/data/' . $id . '/thumbs/' . $thumb : UPLOADURL . '/blank.jpg';
        }
        
        /**
         * hasImage
         *
         * @param string|null $image
         * @param int $id
         * @return string
         */
        public static function hasImage(null|string $image, int $id): string
        {
            return ($image) ? UPLOADURL . '/data/' . $id . '/thumbs/' . $image : UPLOADURL . '/blank.jpg';
        }
        
        /**
         * hasAudio
         *
         * @param string|null $audio
         * @param int $id
         * @return string
         */
        public static function hasAudio(null|string $audio, int $id): string
        {
            return UPLOADURL . '/data/' . $id . '/' . $audio;
        }
    }