<?php
require_once "../models/Operations.php";

// Get all products
$sql = "SELECT pro_id,pro_pos_id FROM tb_products;";
$prodInfo = executePDOFetchAll($sql);

// Initialize variables for query construction
$cases = [];
$proIds = [];

foreach ($prodInfo as $product) {

    $proIds[] = $product['pro_id']; // Collect pro_ids for WHERE clause
    $proPosId = $product['pro_pos_id']; // Collect pro_ids for WHERE clause

    // Get all categories
    $sql = "SELECT cat_id,cat_pos_id FROM tb_categories WHERE cat_pos_id = $proPosId;";
    $catInfo = executePDOFetchOne($sql);
    $catid = $catInfo['cat_id'];
    $catPosid = $catInfo['cat_pos_id'];

    // Create a `CASE` clause for each product
    $cases[] = "WHEN pro_pos_id = $catPosid THEN '$catid'";
}

// Construct the full `UPDATE` query with `CASE` statements
$caseQuery = implode(" ", $cases);
$proIdsList = implode(",", $proIds);

$sqlCategoryId = "UPDATE tb_products
    SET pro_cat_id = CASE
        $caseQuery
    END
    WHERE pro_id IN ($proIdsList)
";

// Execute the single query
$answer = executeQueryPDOApi($sqlCategoryId, "UPDATED", "BATCH USERS ACCESS UPDATE");
var_dump($answer);
