<?php

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

class AI_eShop_Chat_Product_Vitals_Helper {

    
    private static $instance = null;

    
    public static function get_instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    
    private function __construct() {
        
    }

    
    public function is_vitals_table_available() {
        global $wpdb;
        $table = $wpdb->prefix . 'aieo_core_product_vitals';

        
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$table'");
        if (!$table_exists) {
            return false;
        }

        
        $count = $wpdb->get_var("SELECT COUNT(*) FROM $table");
        return $count > 0;
    }

    
    public function get_product_ids($args = array()) {
        global $wpdb;

        if (!$this->is_vitals_table_available()) {
            return false;
        }

        $defaults = array(
            'prioritize_sales' => false,
            'limit' => null,
            'offset' => 0,
            'include_variations' => true,
            'variations_only' => false,
        );

        $args = wp_parse_args($args, $defaults);
        $table = $wpdb->prefix . 'aieo_core_product_vitals';

        
        $where_clauses = array();

        if ($args['variations_only']) {
            
            $where_clauses[] = "v.parent_id <> v.product_id";
        } elseif (!$args['include_variations']) {
            
            $where_clauses[] = "v.parent_id = v.product_id";
        }

        
        $where_clauses[] = "p.post_status = 'publish'";
        $where_clauses[] = "p.post_type = 'product'";

        $where_sql = 'WHERE ' . implode(' AND ', $where_clauses);

        
        $order_by_sql = '';
        if ($args['prioritize_sales']) {
            
            
            $order_by_sql = "ORDER BY v.product_latest_sale_date DESC, v.product_first_sale_date DESC";
        } else {
            
            $order_by_sql = "ORDER BY v.product_id ASC";
        }

        
        $limit_sql = '';
        if ($args['limit'] !== null) {
            $limit = absint($args['limit']);
            $offset = absint($args['offset']);
            $limit_sql = "LIMIT $offset, $limit";
        }

        
        
        
        $sql = "SELECT DISTINCT v.product_id
                FROM $table v
                INNER JOIN {$wpdb->posts} p ON v.product_id = p.ID
                $where_sql $order_by_sql $limit_sql";
        
        $product_ids = $wpdb->get_col($sql);

        
        $product_ids = array_unique(array_map('intval', $product_ids));

        return array_values($product_ids);
    }

    
    public function get_sellable_unit_ids($args = array()) {
        global $wpdb;

        if (!$this->is_vitals_table_available()) {
            return false;
        }

        $defaults = array(
            'prioritize_sales' => false,
            'in_stock_only'    => true,
            'limit'            => null,
            'offset'           => 0,
        );
        $args = wp_parse_args($args, $defaults);
        $table = $wpdb->prefix . 'aieo_core_product_vitals';

        
        
        
        
        $where = array(
            "p.post_status = 'publish'",
            "p.post_type IN ('product', 'product_variation')",
            
            
            
            "(v.product_id <> v.parent_id OR vp.parent_id IS NULL)",
        );
        if ($args['in_stock_only']) {
            $where[] = 'v.stock > 0';
        }
        $where_sql = 'WHERE ' . implode(' AND ', $where);

        $order_by_sql = $args['prioritize_sales']
            ? 'ORDER BY v.product_latest_sale_date DESC, v.product_first_sale_date DESC'
            : 'ORDER BY v.product_id ASC';

        $limit_sql = '';
        if ($args['limit'] !== null) {
            $limit  = absint($args['limit']);
            $offset = absint($args['offset']);
            $limit_sql = "LIMIT $offset, $limit";
        }

        
        $product_ids = $wpdb->get_col(
            "SELECT DISTINCT v.product_id
             FROM {$table} v
             INNER JOIN {$wpdb->posts} p ON v.product_id = p.ID
             LEFT JOIN (
                 SELECT DISTINCT parent_id
                 FROM {$table}
                 WHERE product_id <> parent_id
             ) vp ON vp.parent_id = v.product_id
             $where_sql $order_by_sql $limit_sql"
        );

        return array_values(array_unique(array_map('intval', $product_ids)));
    }

    
    public function count_sellable_units($args = array()) {
        global $wpdb;

        if (!$this->is_vitals_table_available()) {
            return false;
        }

        $defaults = array('in_stock_only' => true);
        $args = wp_parse_args($args, $defaults);
        $table = $wpdb->prefix . 'aieo_core_product_vitals';

        
        
        
        $cache_key = 'aieo_chat_count_sellable_units_' . ($args['in_stock_only'] ? '1' : '0');
        $cached = get_transient($cache_key);
        if ($cached !== false) {
            return (int) $cached;
        }

        $where = array(
            "p.post_status = 'publish'",
            "p.post_type IN ('product', 'product_variation')",
            "(v.product_id <> v.parent_id OR vp.parent_id IS NULL)",
        );
        if ($args['in_stock_only']) {
            $where[] = 'v.stock > 0';
        }
        $where_sql = 'WHERE ' . implode(' AND ', $where);

        
        $count = (int) $wpdb->get_var(
            "SELECT COUNT(DISTINCT v.product_id)
             FROM {$table} v
             INNER JOIN {$wpdb->posts} p ON v.product_id = p.ID
             LEFT JOIN (
                 SELECT DISTINCT parent_id
                 FROM {$table}
                 WHERE product_id <> parent_id
             ) vp ON vp.parent_id = v.product_id
             $where_sql"
        );

        set_transient($cache_key, $count, 5 * MINUTE_IN_SECONDS);
        return $count;
    }

    
    public function get_product_count($args = array()) {
        global $wpdb;

        if (!$this->is_vitals_table_available()) {
            return false;
        }

        $defaults = array(
            'include_variations' => true,
            'variations_only' => false,
        );

        $args = wp_parse_args($args, $defaults);
        $table = $wpdb->prefix . 'aieo_core_product_vitals';

        
        $where_clauses = array();

        if ($args['variations_only']) {
            $where_clauses[] = "v.parent_id <> v.product_id";
        } elseif (!$args['include_variations']) {
            $where_clauses[] = "v.parent_id = v.product_id";
        }

        
        $where_clauses[] = "p.post_status = 'publish'";
        $where_clauses[] = "p.post_type = 'product'";

        $where_sql = 'WHERE ' . implode(' AND ', $where_clauses);

        
        $count = $wpdb->get_var("SELECT COUNT(*)
                                 FROM $table v
                                 INNER JOIN {$wpdb->posts} p ON v.product_id = p.ID
                                 $where_sql");

        return (int) $count;
    }

    
    public function get_product_vitals($product_id) {
        global $wpdb;

        if (!$this->is_vitals_table_available()) {
            return null;
        }

        $table = $wpdb->prefix . 'aieo_core_product_vitals';

        $product_data = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM $table WHERE product_id = %d",
            $product_id
        ));

        return $product_data;
    }

    
    public function is_variation($product_id) {
        $vitals = $this->get_product_vitals($product_id);

        if (!$vitals) {
            return null;
        }

        
        return $vitals->parent_id != $vitals->product_id;
    }

    
    public function get_variation_attributes($product_id) {
        $vitals = $this->get_product_vitals($product_id);

        if (!$vitals || !$this->is_variation($product_id)) {
            return array();
        }

        $attributes = array();

        
        if (!empty($vitals->color)) {
            $attributes['Color'] = $vitals->color;
        }

        if (!empty($vitals->size)) {
            $attributes['Size'] = $vitals->size;
        }

        
        if (!empty($vitals->variation_attribute_description)) {
            
            
            
            if (empty($attributes)) {
                $attributes['Variation'] = $vitals->variation_attribute_description;
            }
        }

        return $attributes;
    }

    
    public function get_sales_data($product_id) {
        $vitals = $this->get_product_vitals($product_id);

        if (!$vitals) {
            return null;
        }

        return array(
            'first_sale_date' => $vitals->product_first_sale_date ?? null,
            'latest_sale_date' => $vitals->product_latest_sale_date ?? null,
            'has_sales' => !empty($vitals->product_latest_sale_date),
        );
    }

    
    public function get_products_for_training($args = array()) {
        
        $product_ids = $this->get_product_ids($args);

        if ($product_ids !== false) {
            return $product_ids;
        }

        
        return $this->get_products_from_woocommerce($args);
    }

    
    private function get_products_from_woocommerce($args = array()) {
        $defaults = array(
            'prioritize_sales' => false,
            'limit' => -1,
            'offset' => 0,
            'include_variations' => true,
            'variations_only' => false,
        );

        $args = wp_parse_args($args, $defaults);

        $product_ids = array();

        if (!$args['variations_only']) {
            
            $query_args = array(
                'post_type' => 'product',
                'posts_per_page' => $args['limit'],
                'offset' => $args['offset'],
                'fields' => 'ids',
                'post_status' => 'publish',
            );

            if ($args['prioritize_sales']) {
                
                $query_args['meta_key'] = 'total_sales';
                $query_args['orderby'] = 'meta_value_num';
                $query_args['order'] = 'DESC';
            }

            $product_ids = get_posts($query_args);
        }

        if ($args['include_variations'] || $args['variations_only']) {
            
            $variation_query_args = array(
                'post_type' => 'product_variation',
                'posts_per_page' => $args['limit'],
                'offset' => $args['offset'],
                'fields' => 'ids',
                'post_status' => 'publish',
            );

            $variation_ids = get_posts($variation_query_args);

            if ($args['variations_only']) {
                $product_ids = $variation_ids;
            } else {
                $product_ids = array_merge($product_ids, $variation_ids);
            }
        }

        return $product_ids;
    }
}