<?php
/**
 * @copyright    OCTemplates
 * @support      https://octemplates.net/
 * @license      LICENSE.txt
 */

class ModelOctemplatesHelper extends Controller {

    public function getOctCartProducts() {
        $cart_query = $this->db->query("SELECT product_id FROM " . DB_PREFIX . "cart WHERE api_id = '" . (isset($this->session->data['api_id']) ? (int)$this->session->data['api_id'] : 0) . "' AND customer_id = '" . (int)$this->customer->getId() . "' AND session_id = '" . $this->db->escape($this->session->getId()) . "'");
    
        if ($cart_query->num_rows > 0) {
            $product_ids = array_column($cart_query->rows, 'product_id');
            $unique_product_ids = array_unique($product_ids);
            return $unique_product_ids;
        } else {
            return [];
        }
    }

    public function getModuleIdByCode($code) {
        if (!$code || strlen($code) < 3) {
            return null;
        }
        $query = $this->db->query("SELECT module_id FROM `" . DB_PREFIX . "module` WHERE `code` = '" . $this->db->escape($code) . "'");
        return $query->num_rows ? $query->row['module_id'] : null;
    }

	public function addOctReviewData($review_id, $data) {
        if (!$review_id || !$data) {
            return;
        }
		$this->db->query("INSERT INTO " . DB_PREFIX . "oct_product_reviews SET review_id = '" . (int)$review_id . "', positive_text = '" . $this->db->escape($data['positive_text']) . "', negative_text = '" . $this->db->escape($data['negative_text']) . "'");
	}

    public function getOctReviewData($review_id) {

        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "oct_product_reviews WHERE review_id = '" . (int)$review_id . "'");
        $reputation_query = $this->db->query("SELECT SUM(plus_reputation) AS positive_votes FROM " . DB_PREFIX . "oct_product_reviews_reputation WHERE review_id = '" . (int)$review_id . "'");
        $positive_votes = isset($reputation_query->row['positive_votes']) ? (int)$reputation_query->row['positive_votes'] : 0;
    
        if ($query->num_rows) {
            return array(
                'positive_text' => $query->row['positive_text'],
                'negative_text' => $query->row['negative_text'],
                'admin_answer'  => $query->row['admin_answer'],
                'positive_votes'=> $positive_votes
            );
        } else {
            return array(
                'positive_text' => '',
                'negative_text' => '',
                'admin_answer'  => '',
                'positive_votes'=> $positive_votes
            );
        }
    } 

    public function addOctProductReputation($data = array()) {
        $sql = "INSERT INTO " . DB_PREFIX . "oct_product_reviews_reputation SET review_id = '" . (int)$data['review_id'] . "', ip = '" . $this->db->escape($data['ip']) . "'";

        $sql .= ", plus_reputation = (plus_reputation + 1)";

        $this->db->query($sql);
    }

    public function checkOctUserIp($ip, $review_id) {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "oct_product_reviews_reputation WHERE ip = '" . $this->db->escape($ip) . "' AND review_id = '" . (int)$review_id . "'");

        return $query->rows;
    }

    public function getManualRecommendations($products) {

        if(empty($products)) return array();

        $recommended_products = array();

        foreach ($products as $product) {
            $manual_query = $this->db->query("
                SELECT pr.related_id 
                FROM " . DB_PREFIX . "product_related pr 
                JOIN " . DB_PREFIX . "product p ON (pr.related_id = p.product_id) 
                JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) 
                WHERE pr.product_id = '" . (int)$product['product_id'] . "' 
                AND p.status = '1' 
                AND p.date_available <= NOW() 
                AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' 
                AND p.quantity > 0 
                LIMIT 5
            ");

            foreach ($manual_query->rows as $result) {
                if (!isset($recommended_products[$result['related_id']])) {
                    $recommended_products[$result['related_id']] = $result['related_id'];
                }
            }
        }

        return $recommended_products;
    }

    public function getAutomaticRecommendations($products) {
        if (empty($products)) return array();

        $cache_table = DB_PREFIX . 'oct_bought_together_cache';
        if (!$this->tableExists($cache_table)) {
            return $this->getAutomaticRecommendationsDirect($products);
        }

        $first_product_id = isset($products[0]['product_id']) ? (int)$products[0]['product_id'] : 0;

        $query = $this->db->query("
            SELECT with_product_id 
            FROM {$cache_table}
            WHERE product_id = '" . $first_product_id . "'
            ORDER BY total_count DESC
            LIMIT 12
        ");

        if ($query->num_rows) {
            $recommended = [];
            foreach ($query->rows as $row) {
                $wid = (int)$row['with_product_id'];
                $recommended[$wid] = $wid;
            }
            return $recommended;
        } 
    }

    public function octMassGenerateBoughtTogether() {
        $query = $this->db->query("
            SELECT DISTINCT op.product_id
            FROM " . DB_PREFIX . "order_product op
            JOIN " . DB_PREFIX . "product p ON op.product_id = p.product_id
            JOIN " . DB_PREFIX . "product_to_store p2s ON p.product_id = p2s.product_id
            WHERE p.status = '1'
            AND p.date_available <= NOW()
            AND p.quantity > 0
            AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'
        ");
        $all_products = $query->rows;
        $cache_table = DB_PREFIX . 'oct_bought_together_cache';
        $this->db->query("TRUNCATE TABLE {$cache_table}");
        $values = [];

        foreach ($all_products as $p) {
            $pid = (int)$p['product_id'];
            $recommendations = $this->getAutomaticRecommendationsDirect(
                [['product_id' => $pid]],
                true
            );
            foreach ($recommendations as $with_id => $cnt) {
                if ($with_id == $pid) continue;
                $values[] = "(" . $pid . "," . (int)$with_id . "," . (int)$cnt . ")";
            }
        }
        
        if (!empty($values)) {
            $sql = "INSERT INTO {$cache_table} (product_id, with_product_id, total_count)
                    VALUES " . implode(',', $values);
            $this->db->query($sql);
        }
    }
    
    private function tableExists($table_name) {
        $check = $this->db->query("SHOW TABLES LIKE '" . $this->db->escape($table_name) . "'");
        return (bool)$check->num_rows;
    }

    private function getAutomaticRecommendationsDirect($products, $return_count = false) {
        if (empty($products)) return array();
        $recommended_products = [];
        foreach ($products as $product) {
            $auto_query = $this->db->query("
                SELECT op2.product_id, COUNT(*) as count_total
                FROM " . DB_PREFIX . "order_product op1
                JOIN " . DB_PREFIX . "order_product op2 ON op1.order_id = op2.order_id
                JOIN " . DB_PREFIX . "product p ON (op2.product_id = p.product_id)
                JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)
                WHERE op1.product_id = '" . (int)$product['product_id'] . "'
                  AND op2.product_id != '" . (int)$product['product_id'] . "'
                  AND p.status = '1'
                  AND p.date_available <= NOW()
                  AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'
                  AND p.quantity > 0
                GROUP BY op2.product_id
                ORDER BY count_total DESC
                LIMIT 12
            ");
            foreach ($auto_query->rows as $row) {
                $wid = (int)$row['product_id'];
                $cnt = (int)$row['count_total'];
                if ($return_count) {
                    $recommended_products[$wid] = ($recommended_products[$wid] ?? 0) + $cnt;
                } else {
                    $recommended_products[$wid] = $wid;
                }
            }
        }
        if ($return_count) {
            arsort($recommended_products);
        }
        return $recommended_products;
    }

    public function getOrderTtnNumbers($order_id) {
        $columns = array(
            'npttn'  => 'novaposhta_cn_number',
            'upttn'  => 'ukrposhta_cn_number',
            'rozttn' => 'rozetka_delivery_cn_number'
        );

        $result = array(
            'npttn'  => '',
            'upttn'  => '',
            'rozttn' => ''
        );

        $existing_query = $this->db->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '" . DB_PREFIX . "order' AND COLUMN_NAME IN ('" . implode("', '", array_values($columns)) . "')");

        if (!$existing_query->num_rows) {
            return $result;
        }

        $existing_columns = array_column($existing_query->rows, 'COLUMN_NAME');

        $select_columns = array();
        foreach ($columns as $column) {
            if (in_array($column, $existing_columns)) {
                $select_columns[] = "`" . $column . "`";
            }
        }

        if (empty($select_columns)) {
            return $result;
        }

        $order_query = $this->db->query("SELECT " . implode(', ', $select_columns) . " FROM `" . DB_PREFIX . "order` WHERE order_id = '" . (int)$order_id . "'");

        if (!$order_query->num_rows) {
            return $result;
        }

        foreach ($columns as $key => $column) {
            if (isset($order_query->row[$column]) && $order_query->row[$column] !== null) {
                $result[$key] = strip_tags(trim($order_query->row[$column]));
            }
        }

        return $result;
    }

    public function getLastOrderHistoryComment($order_id) {
        $query = $this->db->query("SELECT comment FROM " . DB_PREFIX . "order_history WHERE order_id = '" . (int)$order_id . "' AND comment != '' ORDER BY date_added DESC LIMIT 1");

        if ($query->num_rows) {
            return strip_tags(trim($query->row['comment']));
        }

        return '';
    }
}