<?php
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Dynamic SQL fragments in this class are generated from fixed table names and intval ID lists with %d placeholders.

namespace Anbarak;

if (! defined('ABSPATH')) exit;


/**
 * Product helper class
 * @author rezaramezanpour.ir@gmail.com  
 */
class ProductsInfo
{

    private static $instance;
    private $__variableProductsIds;
    private $__notManagedVariableProductsIds;

    private $__inStockProducts;
    private $__outOfStockProducts;
    private $__managedStockCount;
    private $__lowStockProducts;

    /**
     * We use single query for holding all
     * products data preventing duplicate queries
     * for improving performance 
     */
    private $__products;

    public static function getInstance()
    {
        if (!isset(self::$instance)) {
            self::$instance = new ProductsInfo;
        }
        return self::$instance;
    }

    public function __construct()
    {
        global $wpdb;
        $this->getVariableProductsIds();
        $this->getNotManagedVariableProductsIds();

        $excluded_ids = $this->getNotManagedVariableProductsIds();
        $excluded_sql = '';
        $prepare_args = [];
        if (!empty($excluded_ids)) {
            $placeholders = array_fill(0, count($excluded_ids), '%d');
            $excluded_sql = 'AND p.ID NOT IN(' . implode(',', $placeholders) . ')';
            $prepare_args = array_map('intval', $excluded_ids);
        }

        $sql_base = "SELECT 
                p.post_parent, 
                p.ID, 
                p.post_title,  
                p.post_name, 
                metaSku.meta_value AS sku,
                metaStock.meta_value AS stock_quantity,
                metaStockStatus.meta_value AS stock_status
            FROM 
                {$wpdb->prefix}posts p 
                LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_sku') AS metaSku ON p.ID = metaSku.post_id 
                LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_stock') AS metaStock ON p.ID = metaStock.post_id
                LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_stock_status') AS metaStockStatus ON p.ID = metaStockStatus.post_id
            WHERE 
                p.post_type IN ('product_variation', 'product') 
                AND p.post_status = 'publish'";

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $excluded_sql is generated from intval IDs and %d placeholders.
        $this->__products = !empty($prepare_args)
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $excluded_sql is generated from intval IDs and %d placeholders.
            ? $wpdb->get_results($wpdb->prepare($sql_base . $excluded_sql . " GROUP BY p.ID, sku ORDER BY p.post_parent DESC", $prepare_args))
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            : $wpdb->get_results($sql_base . " GROUP BY p.ID, sku ORDER BY p.post_parent DESC");
    }

    public function getVariableProductsIds(): void
    {
        global $wpdb;
        $variableProducts = $wpdb->get_results("
        SELECT posts.ID FROM {$wpdb->prefix}posts AS posts 
            INNER JOIN {$wpdb->prefix}term_relationships AS term_relationships ON posts.ID = term_relationships.object_id 
            INNER JOIN {$wpdb->prefix}term_taxonomy AS term_taxonomy ON term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id 
            INNER JOIN {$wpdb->prefix}terms AS terms ON term_taxonomy.term_id = terms.term_id 
        WHERE 
            term_taxonomy.taxonomy = 'product_type' 
            AND terms.slug = 'variable' 
            AND posts.post_type = 'product'
        ");

        if (!empty($variableProducts)) {
            $variableProducts = array_column($variableProducts, 'ID');
        }
        $this->__variableProductsIds = $variableProducts;
    }

    public function getNotManagedVariableProductsIds(): void
    {
        global $wpdb;

        $query = "
            SELECT p.ID 
            FROM {$wpdb->posts} p
            LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id 
                AND pm.meta_key = '_manage_stock'
            WHERE p.post_type = 'product_variation'
                AND (pm.meta_value IS NULL OR pm.meta_value = 'no')
        ";

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $query contains no user input, uses $wpdb->posts and $wpdb->postmeta
        $this->__notManagedVariableProductsIds = $wpdb->get_col($query);
    }

    public function getList(): array
    {
        return $this->__products;
    }

    /**
     * Returns IDs of variables products
     * 
     * @return array
     */
    public function getVariablesIds(): array
    {
        return $this->__variableProductsIds;
    }


    /**
     * Returns IDs of variables products where stock management is not enabled 
     * 
     * @return array
     */
    public function getNonManagedVariablesIds(): array
    {
        return $this->__notManagedVariableProductsIds;
    }

    /**
     * Returns variables products list in a comma separated format
     */
    public function getVariablesIdsSeq(): string
    {
        if (empty($this->__variableProductsIds)) return '';
        return implode(',', array_map('intval', $this->__variableProductsIds));
    }

    /**
     * Returns variables products list in a comma separated format
     */
    public function getNotManagedVariablesIdsSeq(): string
    {
        if (empty($this->__notManagedVariableProductsIds)) return '';
        return implode(',', array_map('intval', $this->__notManagedVariableProductsIds));
    }

    private function buildExcludedIdsClause(array $ids): array
    {
        if (empty($ids)) {
            return ['', []];
        }
        $int_ids = array_map('intval', $ids);
        $placeholders = array_fill(0, count($int_ids), '%d');
        return ['AND p.ID NOT IN(' . implode(',', $placeholders) . ')', $int_ids];
    }

    /**
     * Get total products
     * managed stock and non-managed (including variations)
     */
    public function getTotal(): int
    {
        global $wpdb;
        list($excluded_sql, $prepare_args) = $this->buildExcludedIdsClause($this->__variableProductsIds);

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $excluded_sql is generated from intval IDs and %d placeholders.
        return !empty($prepare_args)
            ? (int) $wpdb->get_var($wpdb->prepare("SELECT count(ID) products
            FROM {$wpdb->prefix}posts p 
            WHERE 
            p.post_type IN ('product_variation', 'product') 
            AND p.post_status = 'publish'" . $excluded_sql, $prepare_args))
            : (int) $wpdb->get_var("SELECT count(ID) products
            FROM {$wpdb->prefix}posts p 
            WHERE 
            p.post_type IN ('product_variation', 'product') 
            AND p.post_status = 'publish'");
    }

    public function getManagedCount(): int
    {
        if ($this->__managedStockCount) {
            return $this->__managedStockCount;
        }

        global $wpdb;
        list($excluded_sql, $prepare_args) = $this->buildExcludedIdsClause($this->__variableProductsIds);

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $excluded_sql is generated from intval IDs and %d placeholders.
        $this->__managedStockCount = !empty($prepare_args)
            ? $wpdb->get_var($wpdb->prepare("SELECT count(ID)
            FROM 
                {$wpdb->prefix}posts p 
                LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_manage_stock') AS mt1 ON p.ID = mt1.post_id 
            WHERE 
                p.post_type IN ('product_variation', 'product')
                AND mt1.meta_value = 'yes'
                AND p.post_status = 'publish'" . $excluded_sql . "
            LIMIT 0,1", $prepare_args))
            : $wpdb->get_var("SELECT count(ID)
            FROM 
                {$wpdb->prefix}posts p 
                LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_manage_stock') AS mt1 ON p.ID = mt1.post_id 
            WHERE 
                p.post_type IN ('product_variation', 'product')
                AND mt1.meta_value = 'yes'
                AND p.post_status = 'publish'
            LIMIT 0,1");

        return $this->__managedStockCount;
    }

    public function getInStock(): array
    {
        if ($this->__inStockProducts) {
            return $this->__inStockProducts;
        }

        global $wpdb;
        list($excluded_sql, $prepare_args) = $this->buildExcludedIdsClause($this->__variableProductsIds);

        $sql_base = "SELECT DISTINCT
                p.ID,
                p.post_title,
                p.post_author,
                p.post_date,
                p.post_date_gmt,
                p.post_status,
                p.post_name,
                p.post_modified,
                p.post_modified_gmt,
                p.post_parent,
                p.guid,
                p.post_type,
                mt2.meta_value as stock,
                mt3.meta_value as price,
                mt5.meta_value as vendor,
            (
                    SELECT SUM((e.price / e.qty) * (e.qty - IFNULL(e.consumed_qty, 0)))
                    FROM {$wpdb->prefix}anbarak_expenses e
                    WHERE e.product_id = p.ID
                    AND e.deleted_at IS NULL
                    AND (e.qty - IFNULL(e.consumed_qty, 0)) > 0
                ) as total_buy_cost
            FROM 
                {$wpdb->prefix}posts p 
                LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_manage_stock') AS mt1 ON p.ID = mt1.post_id 
                LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_stock') AS mt2 ON p.ID = mt2.post_id 
                LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_price') AS mt3 ON p.ID = mt3.post_id 
                LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = 'vendor') AS mt5 ON p.ID = mt5.post_id  
            WHERE 
                p.post_type IN ('product_variation', 'product')
                AND mt1.meta_value = 'yes'
                AND mt2.meta_value > 0
                AND p.post_status = 'publish'";

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $excluded_sql is generated from intval IDs and %d placeholders.
        $this->__inStockProducts = !empty($prepare_args)
            ? $wpdb->get_results($wpdb->prepare($sql_base . $excluded_sql . " ORDER BY p.post_date_gmt DESC", $prepare_args))
            : $wpdb->get_results($sql_base . " ORDER BY p.post_date_gmt DESC");

        return $this->__inStockProducts;
    }

    public function getOutOfStock(): array
    {
        if ($this->__outOfStockProducts) {
            return $this->__outOfStockProducts;
        }

        global $wpdb;
        list($excluded_sql, $prepare_args) = $this->buildExcludedIdsClause($this->__variableProductsIds);

        $sql_base = "SELECT
                p.ID,
                p.post_title,
                p.post_author,
                p.post_date,
                p.post_date_gmt,
                p.post_content,
                p.post_excerpt,
                p.post_status,
                p.post_name,
                p.post_modified,
                p.post_modified_gmt,
                p.post_parent,
                p.guid,
                p.post_type
            FROM 
                {$wpdb->prefix}posts p 
                LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_manage_stock') AS mt1 ON p.ID = mt1.post_id 
                LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_stock') AS mt2 ON p.ID = mt2.post_id 
            WHERE 
                p.post_type IN ('product_variation', 'product')
                AND mt1.meta_value = 'yes'
                AND mt2.meta_value < 1
                AND p.post_status = 'publish'";

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $excluded_sql is generated from intval IDs and %d placeholders.
        $this->__outOfStockProducts = !empty($prepare_args)
            ? $wpdb->get_results($wpdb->prepare($sql_base . $excluded_sql, $prepare_args))
            : $wpdb->get_results($sql_base);

        return $this->__outOfStockProducts;
    }

    public function getLowStock(): array
    {

        if ($this->__lowStockProducts) {
            return $this->__lowStockProducts;
        }
        global $wpdb;

        $low_stock_threshold_fallback =  ANBARAK_PRODUCTS_LOW_STOCK_THRESHOLD;
        list($excluded_sql, $prepare_args) = $this->buildExcludedIdsClause($this->__variableProductsIds);

        $sql_base = "SELECT
                p.ID,
                p.post_title,
                p.post_author,
                p.post_date,
                p.post_date_gmt,
                p.post_status,
                p.post_name,
                p.post_modified,
                p.post_modified_gmt,
                p.post_parent,
                p.guid,
                p.post_type,
                mt2.meta_value as stock,
                mt3.meta_value as low_stock
            FROM 
                {$wpdb->prefix}posts p 
                LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_manage_stock') AS mt1 ON p.ID = mt1.post_id 
                LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_stock') AS mt2 ON p.ID = mt2.post_id
            LEFT JOIN (SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = '_low_stock_amount') AS mt3 ON p.ID = mt3.post_id 
            WHERE 
                p.post_type IN ('product_variation', 'product')
                AND mt1.meta_value = 'yes'
                AND mt2.meta_value > 0
                AND mt2.meta_value > 0
                AND (
                    (mt3.meta_value IS NOT NULL AND mt2.meta_value <= mt3.meta_value)
                    OR 
                    (mt3.meta_value IS NULL AND mt2.meta_value <= %d)
                )
                AND p.post_status = 'publish'";

        $all_args = array_merge([$low_stock_threshold_fallback], $prepare_args);
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $excluded_sql is generated from intval IDs and %d placeholders.
        $this->__lowStockProducts = !empty($prepare_args)
            ? $wpdb->get_results($wpdb->prepare($sql_base . $excluded_sql, $all_args))
            : $wpdb->get_results($wpdb->prepare($sql_base, $low_stock_threshold_fallback));

        return $this->__lowStockProducts;
    }
}
