<?php

namespace AIPT\Api\AutomationJobs;

/**
 * PoolBuilder
 *
 * Builds job product pool using paginated INSERT...SELECT to avoid PHP memory pressure
 * Works for both small and large catalogs; chunk size is configurable.
 *
 * @since 2.3.0
 */
class PoolBuilder {
    /** @var \wpdb */
    private $wpdb;
    /** @var string */
    private $job_products_table;

    public function __construct() {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->job_products_table = $wpdb->prefix . 'aipt_job_products';
    }

    /**
     * Build pool for a job using INSERT...SELECT with keyset pagination.
     *
     * - Uses keyset pagination (WHERE ID > last_id) for O(1) performance vs OFFSET's O(n)
     * - Ensures no duplicates via UNIQUE(job_id, product_id) using INSERT IGNORE
     *
     * @param int   $job_id Job ID
     * @param array $filter Product filter configuration (same shape as AutomationJobManager::getFilteredProductIds filters)
     * @param int   $chunk_size Number of products per INSERT chunk
     * @param int   $max_chunks Optional safety cap on chunks per call (prevents long requests)
     * @return array{inserted:int,total:int}
     */
    public function buildPool($job_id, array $filter, $chunk_size = 2000, $max_chunks = 20) {
        $job_id = (int) $job_id;
        $chunk_size = max(100, (int) $chunk_size);
        $max_chunks = max(1, (int) $max_chunks);

        $base_select_sql = $this->buildFilteredProductsSelect($filter);
        if (!$base_select_sql) {
            return ['inserted' => 0, 'total' => 0];
        }

        $count_sql = "SELECT COUNT(1) FROM (" . $base_select_sql . ") as src";

        $total = (int) $this->wpdb->get_var($count_sql);

        $inserted = 0;
        $last_id = 0; 
        $chunks_done = 0;

        while ($chunks_done < $max_chunks) {

            $sql = $this->wpdb->prepare(
                "INSERT IGNORE INTO {$this->job_products_table} (job_id, product_id, status, created_at)
                 SELECT %d, src.ID, 'pending', %s
                 FROM (" . $base_select_sql . ") as src
                 WHERE src.ID > %d
                 ORDER BY src.ID ASC
                 LIMIT %d",
                $job_id,
                current_time('mysql'),
                $last_id,
                $chunk_size
            );

            $res = $this->wpdb->query($sql);
            if ($res === false) {
                break;
            }

            $chunk_inserted = (int) $res;
            $inserted += $chunk_inserted;
            $chunks_done++;

            if ($chunk_inserted === 0) {
                break;
            }

            $last_id = (int) $this->wpdb->get_var(
                $this->wpdb->prepare(
                    "SELECT MAX(product_id) FROM {$this->job_products_table} WHERE job_id = %d",
                    $job_id
                )
            );

            if ($last_id === 0) {
                break;
            }
        }

        return ['inserted' => $inserted, 'total' => $total];
    }

    /**
     * Build the SELECT SQL for product IDs based on filter
     * Returns a SQL string that selects a single column "ID" of product IDs.
     *
     * @param array $filter
     * @return string|null
     */
    private function buildFilteredProductsSelect(array $filter) {
        global $wpdb;
        $conditions = [];
        $joins = [];

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

        if (class_exists('\\AIPT\\Premium\\PremiumFeatureManager', false) &&
            class_exists('\\AIPT\\Premium\\AutomationJobs\\Services\\ProductFilterAugmenter')) {
            \AIPT\Premium\AutomationJobs\Services\ProductFilterAugmenter::addPoolBuilderFilters($joins, $conditions, $filter);
        }

        if (!empty($filter['priceRange']['min']) || !empty($filter['priceRange']['max'])) {
            $joins[] = "INNER JOIN {$wpdb->postmeta} pm_price ON pm_price.post_id = p.ID";
            $conditions[] = "pm_price.meta_key = '_price'";
            if (!empty($filter['priceRange']['min'])) {
                $conditions[] = $this->wpdb->prepare("CAST(pm_price.meta_value AS DECIMAL(10,2)) >= %f", (float) $filter['priceRange']['min']);
            }
            if (!empty($filter['priceRange']['max'])) {
                $conditions[] = $this->wpdb->prepare("CAST(pm_price.meta_value AS DECIMAL(10,2)) <= %f", (float) $filter['priceRange']['max']);
            }
        }

        if (!empty($filter['includeIds'])) {
            $include_ids = array_filter(array_map('intval', explode(',', (string) $filter['includeIds'])));
            if (!empty($include_ids)) {
                $conditions[] = "p.ID IN (" . implode(',', $include_ids) . ")";
            }
        }

        if (!empty($filter['excludeIds'])) {
            $exclude_ids = array_filter(array_map('intval', explode(',', (string) $filter['excludeIds'])));
            if (!empty($exclude_ids)) {
                $conditions[] = "p.ID NOT IN (" . implode(',', $exclude_ids) . ")";
            }
        }

        $sql = "SELECT DISTINCT p.ID FROM {$wpdb->posts} p";
        if (!empty($joins)) {
            $sql .= ' ' . implode(' ', $joins);
        }
        if (!empty($conditions)) {
            $sql .= ' WHERE ' . implode(' AND ', $conditions);
        }

        return $sql;
    }
}

