<?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;

        
        $needs_attribute = get_option('aieo_core_attribute_need', 'pa_need');

        
        $wpdb->query("DROP PROCEDURE IF EXISTS wp_AIEO_PopulateProductNeeds");

        
        $sql = "
CREATE PROCEDURE wp_AIEO_PopulateProductNeeds()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    -- Step 1: Build parent needs with correct ordering
    DROP TEMPORARY TABLE IF EXISTS tmp_parent_needs;
    CREATE TEMPORARY TABLE tmp_parent_needs (
        parent_id      INT NOT NULL PRIMARY KEY,
        ids_csv        VARCHAR(2000) NULL,
        primary_id     INT NULL,
        secondary_id   INT NULL,
        rest_ids       VARCHAR(1800) NULL,
        primary_name   VARCHAR(255) NULL,
        secondary_name VARCHAR(255) NULL,
        other_names    TEXT NULL
    ) ENGINE=InnoDB;

    -- Get parent products with their needs ordered by term name
    INSERT INTO tmp_parent_needs (parent_id, ids_csv, primary_id, secondary_id, rest_ids)
    SELECT
        pv.product_id AS parent_id,

        -- Get all need IDs ordered by term name
        GROUP_CONCAT(
            tt.term_id
            ORDER BY t.name ASC
            SEPARATOR ','
        ) AS ids_csv,

        -- Extract first ID as primary
        CAST(SUBSTRING_INDEX(
            GROUP_CONCAT(
                tt.term_id
                ORDER BY t.name ASC
                SEPARATOR ','
            ),
            ',', 1
        ) AS UNSIGNED) AS primary_id,

        -- Extract second ID as secondary
        CASE
            WHEN (LENGTH(GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','))
                  - LENGTH(REPLACE(GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','), ',', ''))) >= 1
            THEN CAST(SUBSTRING_INDEX(
                    SUBSTRING_INDEX(
                        GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','),
                        ',', 2
                    ),
                    ',', -1
                ) AS UNSIGNED)
            ELSE NULL
        END AS secondary_id,

        -- Extract remaining IDs (3rd onwards)
        CASE
            WHEN (LENGTH(GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','))
                  - LENGTH(REPLACE(GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','), ',', ''))) >= 2
            THEN SUBSTRING(
                    GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','),
                    LOCATE(',', GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ','),
                           LOCATE(',', GROUP_CONCAT(tt.term_id ORDER BY t.name ASC SEPARATOR ',')) + 1) + 1
                )
            ELSE ''
        END AS rest_ids

    FROM {$wpdb->prefix}aieo_core_product_vitals pv
    JOIN {$wpdb->prefix}posts p
        ON p.ID = pv.product_id
        AND pv.product_id = pv.parent_id  -- parents only
        AND pv.status = 1
    JOIN {$wpdb->prefix}term_relationships tr
        ON tr.object_id = p.ID
    JOIN {$wpdb->prefix}term_taxonomy tt
        ON tt.term_taxonomy_id = tr.term_taxonomy_id
        AND tt.taxonomy = '{$needs_attribute}'
    JOIN {$wpdb->prefix}terms t
        ON t.term_id = tt.term_id
    GROUP BY pv.product_id
    HAVING ids_csv IS NOT NULL;

    -- Step 2: Resolve primary/secondary names
    UPDATE tmp_parent_needs n
    LEFT JOIN {$wpdb->prefix}terms t1 ON t1.term_id = n.primary_id
    LEFT JOIN {$wpdb->prefix}terms t2 ON t2.term_id = n.secondary_id
    SET n.primary_name   = COALESCE(t1.name, ''),
        n.secondary_name = COALESCE(t2.name, '');

    -- Step 3: Build other_names from rest_ids list
    DROP TEMPORARY TABLE IF EXISTS tmp_parent_other;
    CREATE TEMPORARY TABLE tmp_parent_other (
        parent_id   INT NOT NULL PRIMARY KEY,
        other_names TEXT NULL
    ) ENGINE=InnoDB;

    INSERT INTO tmp_parent_other (parent_id, other_names)
    SELECT
        n.parent_id,
        GROUP_CONCAT(t.name ORDER BY FIND_IN_SET(t.term_id, n.rest_ids) SEPARATOR ', ')
    FROM tmp_parent_needs n
    JOIN {$wpdb->prefix}term_taxonomy tt
        ON tt.taxonomy = '{$needs_attribute}'
        AND n.rest_ids <> ''
        AND FIND_IN_SET(tt.term_id, n.rest_ids) > 0
    JOIN {$wpdb->prefix}terms t
        ON t.term_id = tt.term_id
    GROUP BY n.parent_id;

    UPDATE tmp_parent_needs n
    LEFT JOIN tmp_parent_other o ON o.parent_id = n.parent_id
    SET n.other_names = COALESCE(o.other_names, '');

    -- Step 4: Apply to PARENTS in vitals table
    UPDATE {$wpdb->prefix}aieo_core_product_vitals pv
    JOIN tmp_parent_needs n ON n.parent_id = pv.product_id
    SET
        pv.primary_need_id   = NULLIF(n.primary_id, 0),
        pv.secondary_need_id = NULLIF(n.secondary_id, 0),
        pv.other_needs_ids   = COALESCE(n.rest_ids, ''),
        pv.primary_need      = COALESCE(n.primary_name, ''),
        pv.secondary_need    = COALESCE(n.secondary_name, ''),
        pv.other_needs       = COALESCE(n.other_names, '')
    WHERE pv.parent_id = pv.product_id
        AND pv.status = 1;

    -- Step 5: Propagate to VARIATIONS (children) from their parents
    UPDATE {$wpdb->prefix}aieo_core_product_vitals pvChild
    JOIN tmp_parent_needs n ON n.parent_id = pvChild.parent_id
    SET
        pvChild.primary_need_id   = NULLIF(n.primary_id, 0),
        pvChild.secondary_need_id = NULLIF(n.secondary_id, 0),
        pvChild.other_needs_ids   = COALESCE(n.rest_ids, ''),
        pvChild.primary_need      = COALESCE(n.primary_name, ''),
        pvChild.secondary_need    = COALESCE(n.secondary_name, ''),
        pvChild.other_needs       = COALESCE(n.other_names, '')
    WHERE pvChild.product_id <> pvChild.parent_id
        AND pvChild.status = 1;

    -- Cleanup
    DROP TEMPORARY TABLE IF EXISTS tmp_parent_other;
    DROP TEMPORARY TABLE IF EXISTS tmp_parent_needs;

    COMMIT;
END";

        
        $result = $wpdb->query($sql);

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

        if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log("AI eShop Optimizer: Successfully created wp_AIEO_PopulateProductNeeds stored procedure."); }
        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 = $wpdb->query("CALL wp_AIEO_PopulateProductNeeds()");

        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;
    }
}
