<?php

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

class AI_eShop_Product_Needs {

    
    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 add_needs_fields_to_vitals_table() {
        global $wpdb;
        $table = $wpdb->prefix . 'aieo_core_product_vitals';

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

        
        $columns = $wpdb->get_results("SHOW COLUMNS FROM $table");
        $existing_columns = array();
        foreach ($columns as $col) {
            $existing_columns[] = $col->Field;
        }

        $fields_to_add = array();

        if (!in_array('primary_need_id', $existing_columns)) {
            $fields_to_add[] = "ADD COLUMN `primary_need_id` INT(11) NULL AFTER `variation_swatch`";
        }

        if (!in_array('secondary_need_id', $existing_columns)) {
            $fields_to_add[] = "ADD COLUMN `secondary_need_id` INT(11) NULL AFTER `primary_need_id`";
        }

        if (!in_array('other_needs_ids', $existing_columns)) {
            $fields_to_add[] = "ADD COLUMN `other_needs_ids` VARCHAR(1800) NULL AFTER `secondary_need_id`";
        }

        if (!in_array('primary_need', $existing_columns)) {
            $fields_to_add[] = "ADD COLUMN `primary_need` VARCHAR(255) NULL AFTER `other_needs_ids`";
        }

        if (!in_array('secondary_need', $existing_columns)) {
            $fields_to_add[] = "ADD COLUMN `secondary_need` VARCHAR(255) NULL AFTER `primary_need`";
        }

        if (!in_array('other_needs', $existing_columns)) {
            $fields_to_add[] = "ADD COLUMN `other_needs` TEXT NULL AFTER `secondary_need`";
        }

        
        if (!empty($fields_to_add)) {
            $alter_sql = "ALTER TABLE `$table` " . implode(', ', $fields_to_add);
            
            $result = $wpdb->query($alter_sql);

            if ($result === false) {
                if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log("AI eShop Optimizer: Failed to add needs fields to vitals table. Error: " . $wpdb->last_error); }
                return false;
            }

            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log("AI eShop Optimizer: Successfully added " . count($fields_to_add) . " needs fields to vitals table."); }
        }

        return true;
    }

    
    public function create_needs_stored_procedure() {
        global $wpdb;

        if (!function_exists('aieo_create_sp_AIEO_PopulateProductNeeds') && function_exists('aieo_load_sql_layer')) {
            aieo_load_sql_layer();
        }
        if (!function_exists('aieo_create_sp_AIEO_PopulateProductNeeds')) {
            return false;
        }

        $wpdb->query("DROP PROCEDURE IF EXISTS wp_AIEO_PopulateProductNeeds");
        $wpdb->last_error = '';
        aieo_create_sp_AIEO_PopulateProductNeeds('wp_');

        if ($wpdb->last_error !== '') {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log("AI eShop Optimizer: Failed to create needs stored procedure. Error: " . $wpdb->last_error); }
            return false;
        }

        return true;
    }

    
    public function populate_product_needs() {
        global $wpdb;

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

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

        
        $result = aieo_call_sproc(
            "CALL wp_AIEO_PopulateProductNeeds()",
            function () {
                
                
                if ( function_exists( 'aieo_load_sql_layer' ) ) { aieo_load_sql_layer(); }
                if ( function_exists( 'aieo_create_stored_procedures' ) ) { aieo_create_stored_procedures(); }
            }
        );

        if ($result === false) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log("AI eShop Optimizer: Failed to execute needs population procedure. Error: " . $wpdb->last_error); }
            return false;
        }

        
        $count = $wpdb->get_var("
            SELECT COUNT(DISTINCT product_id)
            FROM {$wpdb->prefix}aieo_core_product_vitals
            WHERE primary_need_id IS NOT NULL
        ");

        if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log("AI eShop Optimizer: Successfully populated needs for $count products."); }
        return true;
    }
}