<?php

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

class AI_eShop_Need_Suggestions {

    
    private static $instance = null;

    
    private $vitals_table;
    private $suggestions_table;
    private $catalogue_table;

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

    
    private function __construct() {
        global $wpdb;
        $this->vitals_table = $wpdb->prefix . 'aieo_core_product_vitals';
        $this->suggestions_table = $wpdb->prefix . 'aieo_product_need_suggestions';
        $this->catalogue_table = $wpdb->prefix . 'aieo_temp_product_catalogue';
    }

    
    public function get_total_products_with_needs($detailed = false) {
        global $wpdb;

        if (!$this->table_exists($this->vitals_table)) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Need Suggestions: Vitals table does not exist for count query'); }
            return $detailed ? array('parent_count' => 0, 'variation_count' => 0, 'total_impacted' => 0) : 0;
        }

        
        $parent_count = (int) $wpdb->get_var(
            "SELECT COUNT(*)
             FROM {$this->vitals_table}
             WHERE product_id = parent_id
               AND primary_need_id IS NOT NULL"
        );

        
        
        $total_impacted = (int) $wpdb->get_var(
            "SELECT COUNT(*)
             FROM {$this->vitals_table}
             WHERE parent_id IN (
                   SELECT product_id
                   FROM {$this->vitals_table}
                   WHERE product_id = parent_id
                     AND primary_need_id IS NOT NULL
               )"
        );

        
        $variation_count = $total_impacted - $parent_count;

        error_log(sprintf(
            'AIEO Need Suggestions: Found %d parent/simple products with needs, %d variations will inherit suggestions (total impacted: %d)',
            $parent_count,
            $variation_count,
            $total_impacted
        ));

        if ($detailed) {
            return array(
                'parent_count' => $parent_count,
                'variation_count' => $variation_count,
                'total_impacted' => $total_impacted,
            );
        }

        return $parent_count;
    }

    
    public function generate_suggestions_batch($offset = 0, $batch_size = 300) {
        global $wpdb;

        $results = array(
            'processed' => 0,
            'suggestions_created' => 0,
            'failed' => 0,
            'skipped' => 0,
            'complete' => false,
            'next_offset' => $offset,
        );

        
        if (!$this->table_exists($this->vitals_table)) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Need Suggestions: Vitals table does not exist'); }
            $results['complete'] = true;
            return $results;
        }

        
        if (!$this->table_exists($this->suggestions_table)) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Need Suggestions: Suggestions table does not exist'); }
            $results['complete'] = true;
            return $results;
        }

        
        if (!$this->table_exists($this->catalogue_table)) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Need Suggestions: Product catalogue table does not exist. Please run "Recreate full product catalogue" first.'); }
            $results['complete'] = true;
            return $results;
        }

        
        
        $products = $wpdb->get_results($wpdb->prepare(
            "SELECT parent_id AS product_id,
                    MAX(primary_need_id) AS primary_need_id,
                    MAX(secondary_need_id) AS secondary_need_id,
                    MAX(subcat_id) AS subcat_id,
                    MAX(head_id) AS head_id
             FROM {$this->vitals_table}
             WHERE product_id = parent_id
               AND primary_need_id IS NOT NULL
             GROUP BY parent_id
             ORDER BY parent_id
             LIMIT %d OFFSET %d",
            $batch_size,
            $offset
        ));

        if (empty($products)) {
            
            $results['complete'] = true;
            return $results;
        }

        foreach ($products as $product) {
            $result = $this->generate_suggestions_for_product(
                $product->product_id,
                $product->primary_need_id,
                $product->secondary_need_id,
                $product->subcat_id,
                $product->head_id
            );

            if ($result === false) {
                $results['failed']++;
            } elseif ($result === 0) {
                $results['skipped']++;
            } else {
                $results['suggestions_created'] += $result;
            }
            $results['processed']++;
        }

        
        $results['next_offset'] = $offset + count($products);

        
        if (count($products) < $batch_size) {
            $results['complete'] = true;
        }

        return $results;
    }

    
    public function generate_all_suggestions($batch_size = 100) {
        global $wpdb;

        $results = array(
            'processed' => 0,
            'suggestions_created' => 0,
            'failed' => 0,
            'skipped' => 0,
        );

        
        if (!$this->table_exists($this->vitals_table)) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Need Suggestions: Vitals table does not exist'); }
            return $results;
        }

        
        if (!$this->table_exists($this->suggestions_table)) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Need Suggestions: Suggestions table does not exist'); }
            return $results;
        }

        
        if (!$this->table_exists($this->catalogue_table)) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Need Suggestions: Product catalogue table does not exist. Please run "Recreate full product catalogue" first.'); }
            return $results;
        }

        
        $offset = 0;
        $total_processed = 0;

        do {
            
            
            $products = $wpdb->get_results($wpdb->prepare(
                "SELECT parent_id AS product_id,
                        MAX(primary_need_id) AS primary_need_id,
                        MAX(secondary_need_id) AS secondary_need_id,
                        MAX(subcat_id) AS subcat_id,
                        MAX(head_id) AS head_id
                 FROM {$this->vitals_table}
                 WHERE product_id = parent_id
                   AND primary_need_id IS NOT NULL
                 GROUP BY parent_id
                 ORDER BY parent_id
                 LIMIT %d OFFSET %d",
                $batch_size,
                $offset
            ));

            if (empty($products)) {
                break;
            }

            foreach ($products as $product) {
                $result = $this->generate_suggestions_for_product(
                    $product->product_id,
                    $product->primary_need_id,
                    $product->secondary_need_id,
                    $product->subcat_id,
                    $product->head_id
                );

                if ($result === false) {
                    $results['failed']++;
                } elseif ($result === 0) {
                    $results['skipped']++;
                } else {
                    $results['suggestions_created'] += $result;
                }
                $results['processed']++;
            }

            $offset += $batch_size;
            $total_processed += count($products);

        } while (count($products) === $batch_size);

        error_log(sprintf(
            'AIEO Need Suggestions: Processed %d products, created %d suggestions, skipped %d, failed %d',
            $results['processed'],
            $results['suggestions_created'],
            $results['skipped'],
            $results['failed']
        ));

        return $results;
    }

    
    public function generate_suggestions_for_product($product_id, $primary_need_id, $secondary_need_id = null, $subcat_id = null, $head_id = null) {
        global $wpdb;

        $product_id = absint($product_id);
        $primary_need_id = absint($primary_need_id);
        $subcat_id = $subcat_id ? absint($subcat_id) : null;
        $head_id = $head_id ? absint($head_id) : null;

        if (!$product_id || !$primary_need_id) {
            return false;
        }

        
        $excluded_ids = $this->get_excluded_product_ids($product_id);
        $excluded_ids[] = $product_id; 

        
        $needs = array($primary_need_id);
        if ($secondary_need_id) {
            $needs[] = absint($secondary_need_id);
        }
        $needs = array_unique($needs);

        
        $matching_products = $this->find_matching_products($product_id, $needs, $excluded_ids, 6, $subcat_id, $head_id);

        if (empty($matching_products)) {
            
            $wpdb->delete(
                $this->suggestions_table,
                array('product_id' => $product_id),
                array('%d')
            );
            return 0;
        }

        
        $match_score = isset($matching_products[0]['match_count']) ? $matching_products[0]['match_count'] : 1;

        
        $suggested_ids = array_column($matching_products, 'product_id');

        
        $json_data = wp_json_encode($suggested_ids);

        $existing = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM {$this->suggestions_table} WHERE product_id = %d",
            $product_id
        ));

        if ($existing) {
            $result = $wpdb->update(
                $this->suggestions_table,
                array(
                    'suggested_products' => $json_data,
                    'match_score' => $match_score,
                ),
                array('product_id' => $product_id),
                array('%s', '%d'),
                array('%d')
            );
        } else {
            $result = $wpdb->insert(
                $this->suggestions_table,
                array(
                    'product_id' => $product_id,
                    'suggested_products' => $json_data,
                    'match_score' => $match_score,
                ),
                array('%d', '%s', '%d')
            );
        }

        return $result !== false ? count($suggested_ids) : false;
    }

    
    private function find_matching_products($source_product_id, $needs, $excluded_ids, $limit = 6, $subcat_id = null, $head_id = null) {
        global $wpdb;

        if (empty($needs)) {
            return array();
        }

        
        $needs_placeholders = implode(',', array_fill(0, count($needs), '%d'));

        
        $exclusion_clause = '';
        if (!empty($excluded_ids)) {
            $exclusion_placeholders = implode(',', array_fill(0, count($excluded_ids), '%d'));
            $exclusion_clause = "AND pv.parent_id NOT IN ($exclusion_placeholders)";
        }

        
        $results = array();

        
        if ($subcat_id) {
            $results = $this->run_matching_query($needs, $needs_placeholders, $excluded_ids, $exclusion_clause, $limit, 'subcat_id', $subcat_id);
        }

        
        if (count($results) < $limit && $head_id) {
            
            $already_found = array_column($results, 'product_id');
            $remaining_limit = $limit - count($results);

            
            $expanded_excluded = array_merge($excluded_ids, $already_found);
            $expanded_exclusion_clause = '';
            if (!empty($expanded_excluded)) {
                $expanded_exclusion_placeholders = implode(',', array_fill(0, count($expanded_excluded), '%d'));
                $expanded_exclusion_clause = "AND pv.parent_id NOT IN ($expanded_exclusion_placeholders)";
            }

            $head_results = $this->run_matching_query($needs, $needs_placeholders, $expanded_excluded, $expanded_exclusion_clause, $remaining_limit, 'head_id', $head_id);
            $results = array_merge($results, $head_results);
        }

        
        if (count($results) < $limit) {
            $already_found = array_column($results, 'product_id');
            $remaining_limit = $limit - count($results);

            
            $final_excluded = array_merge($excluded_ids, $already_found);
            $final_exclusion_clause = '';
            if (!empty($final_excluded)) {
                $final_exclusion_placeholders = implode(',', array_fill(0, count($final_excluded), '%d'));
                $final_exclusion_clause = "AND pv.parent_id NOT IN ($final_exclusion_placeholders)";
            }

            $fallback_results = $this->run_matching_query_no_category($needs, $needs_placeholders, $final_excluded, $final_exclusion_clause, $remaining_limit);
            $results = array_merge($results, $fallback_results);
        }

        
        if (empty($results)) {
            error_log(sprintf(
                'AIEO Need Suggestions: No matches found for product %d with needs [%s], subcat_id=%s, head_id=%s, excluded [%s]',
                $source_product_id,
                implode(',', $needs),
                $subcat_id ?: 'null',
                $head_id ?: 'null',
                implode(',', $excluded_ids)
            ));
        }

        return $results;
    }

    
    private function run_matching_query($needs, $needs_placeholders, $excluded_ids, $exclusion_clause, $limit, $category_field, $category_value) {
        global $wpdb;

        
        $sql = "
            SELECT
                pv.parent_id AS product_id,
                MAX(pv.primary_need_id) AS primary_need_id,
                MAX(pv.secondary_need_id) AS secondary_need_id,
                MAX(
                    CASE WHEN pv.primary_need_id IN ($needs_placeholders) THEN 1 ELSE 0 END +
                    CASE WHEN pv.secondary_need_id IN ($needs_placeholders) THEN 1 ELSE 0 END
                ) AS match_count,
                MAX(COALESCE(cat.TotalItemSales, 0)) AS total_sales
            FROM {$this->vitals_table} pv
            LEFT JOIN (
                SELECT ParentProductId, MAX(TotalItemSales) AS TotalItemSales
                FROM {$this->catalogue_table}
                WHERE ParentProductId IS NOT NULL
                GROUP BY ParentProductId
            ) cat ON cat.ParentProductId = pv.parent_id
            WHERE pv.product_id = pv.parent_id
              AND pv.primary_need_id IS NOT NULL
              AND (
                  pv.primary_need_id IN ($needs_placeholders)
                  OR pv.secondary_need_id IN ($needs_placeholders)
              )
              AND pv.{$category_field} = %d
              $exclusion_clause
            GROUP BY pv.parent_id
            ORDER BY match_count DESC, total_sales DESC
            LIMIT %d
        ";

        
        $params = array_merge(
            $needs, 
            $needs, 
            $needs, 
            $needs  
        );

        
        $params[] = $category_value;

        if (!empty($excluded_ids)) {
            $params = array_merge($params, $excluded_ids);
        }

        $params[] = $limit;

        
        $prepared_sql = $wpdb->prepare($sql, $params);
        
        $results = $wpdb->get_results($prepared_sql, ARRAY_A);

        if ($wpdb->last_error) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Need Suggestions SQL Error: ' . $wpdb->last_error); }
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Need Suggestions SQL Query: ' . $prepared_sql); }
            return array();
        }

        return $results ?: array();
    }

    
    private function run_matching_query_no_category($needs, $needs_placeholders, $excluded_ids, $exclusion_clause, $limit) {
        global $wpdb;

        $sql = "
            SELECT
                pv.parent_id AS product_id,
                MAX(pv.primary_need_id) AS primary_need_id,
                MAX(pv.secondary_need_id) AS secondary_need_id,
                MAX(
                    CASE WHEN pv.primary_need_id IN ($needs_placeholders) THEN 1 ELSE 0 END +
                    CASE WHEN pv.secondary_need_id IN ($needs_placeholders) THEN 1 ELSE 0 END
                ) AS match_count,
                MAX(COALESCE(cat.TotalItemSales, 0)) AS total_sales
            FROM {$this->vitals_table} pv
            LEFT JOIN (
                SELECT ParentProductId, MAX(TotalItemSales) AS TotalItemSales
                FROM {$this->catalogue_table}
                WHERE ParentProductId IS NOT NULL
                GROUP BY ParentProductId
            ) cat ON cat.ParentProductId = pv.parent_id
            WHERE pv.product_id = pv.parent_id
              AND pv.primary_need_id IS NOT NULL
              AND (
                  pv.primary_need_id IN ($needs_placeholders)
                  OR pv.secondary_need_id IN ($needs_placeholders)
              )
              $exclusion_clause
            GROUP BY pv.parent_id
            ORDER BY match_count DESC, total_sales DESC
            LIMIT %d
        ";

        
        $params = array_merge(
            $needs, 
            $needs, 
            $needs, 
            $needs  
        );

        if (!empty($excluded_ids)) {
            $params = array_merge($params, $excluded_ids);
        }

        $params[] = $limit;

        
        $prepared_sql = $wpdb->prepare($sql, $params);
        
        $results = $wpdb->get_results($prepared_sql, ARRAY_A);

        if ($wpdb->last_error) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Need Suggestions SQL Error (no category): ' . $wpdb->last_error); }
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Need Suggestions SQL Query: ' . $prepared_sql); }
            return array();
        }

        return $results ?: array();
    }

    
    private function get_excluded_product_ids($product_id) {
        $excluded = array();

        
        $upsells = get_post_meta($product_id, '_upsell_ids', true);
        if (!empty($upsells) && is_array($upsells)) {
            $excluded = array_merge($excluded, array_map('absint', $upsells));
        }

        
        $crosssells = get_post_meta($product_id, '_crosssell_ids', true);
        if (!empty($crosssells) && is_array($crosssells)) {
            $excluded = array_merge($excluded, array_map('absint', $crosssells));
        }

        return array_unique($excluded);
    }

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

        $product_id = absint($product_id);
        if (!$product_id) {
            return false;
        }

        
        $parent_id = wp_get_post_parent_id($product_id);
        if ($parent_id) {
            $product_id = $parent_id;
        }

        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT suggested_products FROM {$this->suggestions_table} WHERE product_id = %d",
            $product_id
        ));

        if (!$row || empty($row->suggested_products)) {
            return false;
        }

        $suggested_ids = json_decode($row->suggested_products, true);

        if (!is_array($suggested_ids) || empty($suggested_ids)) {
            return false;
        }

        return $suggested_ids;
    }

    
    private function table_exists($table_name) {
        global $wpdb;
        return $wpdb->get_var("SHOW TABLES LIKE '$table_name'") === $table_name;
    }

    
    public function clear_all_suggestions() {
        global $wpdb;

        if (!$this->table_exists($this->suggestions_table)) {
            return 0;
        }

        $result = $wpdb->query("TRUNCATE TABLE {$this->suggestions_table}");
        return $result !== false ? $result : 0;
    }

    
    public function get_stats() {
        global $wpdb;

        if (!$this->table_exists($this->suggestions_table)) {
            return array(
                'total_products' => 0,
                'products_with_suggestions' => 0,
                'avg_suggestions_per_product' => 0,
            );
        }

        $total = $wpdb->get_var("SELECT COUNT(*) FROM {$this->suggestions_table}");

        $avg_suggestions = $wpdb->get_var("
            SELECT AVG(
                JSON_LENGTH(suggested_products)
            ) FROM {$this->suggestions_table}
            WHERE suggested_products IS NOT NULL
        ");

        return array(
            'total_products' => (int) $total,
            'products_with_suggestions' => (int) $total,
            'avg_suggestions_per_product' => round((float) $avg_suggestions, 1),
        );
    }
}