<?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;
    /** @var string */
    private $jobs_table;

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

    /**
     * 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];
        }

        $total = $this->getCachedTotal($job_id);
        if ($total === 0) {
            $count_sql = "SELECT COUNT(1) FROM (" . $base_select_sql . ") as src";

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

        $last_id     = $this->getPoolCursor($job_id);
        $inserted    = 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 = $this->getPoolCursor($job_id);

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

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

    /**
     * Return the current keyset cursor: MAX(product_id) already in the pool for this job.
     *
     * Called at the START of buildPool() (B-02 fix) and after each successful chunk insert
     * to advance the cursor for the next iteration.  Returns 0 when the pool is empty.
     *
     * @param int $job_id
     * @return int
     */
    private function getPoolCursor($job_id) {

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

        return (int) $cursor;
    }

    /**
     * Read total_products from the job record (P-04 cache).
     *
     * Returns 0 when not yet stored, which signals buildPool() to run the COUNT query and
     * let AutomationJobManager persist the result.  On subsequent ticks the stored value
     * is returned directly, eliminating the expensive COUNT(*) subquery.
     *
     * @param int $job_id
     * @return int
     */
    private function getCachedTotal($job_id) {

        $total = $this->wpdb->get_var(
            $this->wpdb->prepare(
                "SELECT total_products FROM {$this->jobs_table} WHERE id = %d",
                $job_id
            )
        );

        return (int) $total;
    }

    /**
     * Build the SELECT SQL for product IDs based on filter
     * Returns a SQL string that selects a single column "ID" of product IDs.
     *
     * Premium filter augmentation (categories, tags, stock status) is applied in
     * buildPool() via the 'aipt_automation_pool_filter_query' filter hook (A-06).
     *
     * @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\\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;
    }
}
