START TRANSACTION;
DROP TEMPORARY TABLE IF EXISTS _rel;
CREATE TEMPORARY TABLE _rel(product_pk BIGINT UNSIGNED, location_pk BIGINT UNSIGNED, qty INT, price DECIMAL(10,2)) ENGINE=InnoDB;
INSERT INTO _rel (product_pk,location_pk,qty,price) VALUES (367,65,1,15.82),(2635,4,1,56.00),(4024,30,1,32.92),(4024,57,1,32.92),(6397,176,1,119.67),(6658,39,1,22.57),(8165,107,2,20.45),(9031,35,1,75.85),(9941,152,1,183.00),(9994,19,1,54.29),(10960,20,1,79.98),(11374,85,1,73.21),(11399,39,1,105.59),(11493,116,1,81.07),(12318,122,1,114.63),(12574,160,1,39.90),(13352,39,1,29.19),(13522,31,1,75.40),(13588,24,1,131.50),(13853,66,1,56.00),(13855,66,1,29.27),(13858,66,1,29.74),(14299,66,1,259.08),(14692,50,1,70.49),(14764,39,1,43.46),(14799,167,1,82.88),(14800,71,1,35.89),(14837,66,1,31.86),(22193,66,1,78.10),(15143,66,1,49.79),(15223,66,1,94.83),(15353,39,1,77.83);
UPDATE stocks s JOIN _rel r ON s.product_id=r.product_pk AND s.location_id=r.location_pk
   SET s.quantity=r.qty, s.average_price=r.price, s.warehouse_id=54,
       s.last_counted_at='2026-08-31', s.updated_at=NOW();
SELECT ROW_COUNT() INTO @updated;
INSERT INTO stocks (product_id,warehouse_id,location_id,sub_location,quantity,reserved_quantity,
  average_price,is_selling,`condition`,last_counted_at,created_at,updated_at)
SELECT r.product_pk,54,r.location_pk,NULL,r.qty,0,r.price,1,'new','2026-08-31',NOW(),NOW()
FROM _rel r LEFT JOIN stocks s ON s.product_id=r.product_pk AND s.location_id=r.location_pk
WHERE s.id IS NULL;
INSERT INTO stock_history
 (product_id,warehouse_id,location_id,action_type,quantity_before,quantity_change,quantity_after,
  unit_price,reference_type,reference_id,user_id,notes,created_at)
SELECT r.product_pk,54,r.location_pk,'stock_take',0,r.qty,r.qty,r.price,'stock_take','SEPT-RESCAN-2026-08-31-REL',NULL,
 'September rescan - position released: only disqualifier was a manual adjustment before 2026-08-29 (per Mohit)',NOW()
FROM _rel r;
SELECT @updated AS rows_updated, (SELECT SUM(qty) FROM _rel) AS units_released, 32 AS expect_pos, 33 AS expect_units;