MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE packages (
	package_id INT(11) NOT NULL,
	package_item_id INT(11) NOT NULL,
	variant_id INT(11) NOT NULL,
	quantity INT(11) NOT NULL,
	retail_price DECIMAL(12,2) NOT NULL,
	sell_price DECIMAL(12,2) NOT NULL,
	stock_available INT(11) NOT NULL,
	INDEX package_id (package_id) USING BTREE,
	INDEX package_item_id (package_item_id) USING BTREE,
	INDEX variant_id (variant_id) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

INSERT INTO packages (package_id, package_item_id, quantity, variant_id, retail_price, sell_price, stock_available) VALUES 
(1000001, 28, 1, 3399, 489.00, 342.30, 26),
(1000001, 28, 1, 3400, 489.00, 342.30, 23),
(1000001, 28, 1, 3409, 489.00, 342.30, 81),
(1000001, 29, 1, 819, 69.95, 55.95, 830),
(1000001, 29, 1, 821, 44.95, 35.95, 741),
(1000001, 30, 1, 807, 39.95, 31.95, 162),
(1000002, 31, 1, 6369, 519.00, 363.30, 93),
(1000002, 31, 1, 6371, 519.00, 363.30, 60),
(1000002, 31, 1, 6372, 519.00, 363.30, 40),
(1000002, 31, 1, 19289, 519.00, 363.30, 24),
(1000002, 31, 1, 19290, 519.00, 363.30, 20),
(1000002, 31, 1, 21774, 519.00, 363.30, 14),
(1000002, 31, 1, 23045, 519.00, 363.30, 0),
(1000002, 37, 1, 821, 44.95, 35.95, 741),
(1000002, 38, 1, 807, 39.95, 31.95, 162),
(1000003, 34, 1, 3399, 489.00, 342.30, 26),
(1000003, 34, 1, 3400, 489.00, 342.30, 23),
(1000003, 34, 1, 3409, 489.00, 342.30, 81),
(1000003, 35, 1, 819, 69.95, 55.95, 830),
(1000003, 36, 1, 810, 59.95, 47.95, 420),
(1000004, 39, 1, 6369, 519.00, 363.30, 93),
(1000004, 39, 1, 6371, 519.00, 363.30, 60),
(1000004, 39, 1, 6372, 519.00, 363.30, 40),
(1000004, 39, 1, 19289, 519.00, 363.30, 24),
(1000004, 39, 1, 19290, 519.00, 363.30, 20),
(1000004, 39, 1, 21774, 519.00, 363.30, 14),
(1000004, 39, 1, 23045, 519.00, 363.30, 0),
(1000004, 40, 1, 819, 69.95, 55.95, 830),
(1000004, 41, 1, 810, 59.95, 47.95, 420),
(1000008, 51, 1, 20107, 799.00, 639.20, 5),
(1000008, 51, 1, 22461, 799.00, 639.20, 3),
(1000008, 52, 2, 821, 44.95, 35.95, 741),
(1000008, 53, 2, 18396, 49.95, 39.95, 3),
(1000013, 66, 1, 3365, 679.00, 475.30, 12),
(1000013, 66, 1, 3367, 679.00, 475.30, 9),
(1000013, 66, 1, 12243, 679.00, 475.30, 1),
(1000013, 66, 1, 19991, 679.00, 475.30, 1),
(1000013, 67, 2, 807, 39.95, 31.95, 162),
(1000013, 68, 2, 821, 44.95, 35.95, 741),
(1000014, 69, 1, 3365, 679.00, 475.30, 12),
(1000014, 69, 1, 3367, 679.00, 475.30, 9),
(1000014, 69, 1, 12243, 679.00, 475.30, 1),
(1000014, 69, 1, 19991, 679.00, 475.30, 1),
(1000014, 70, 2, 819, 69.95, 55.95, 830),
(1000014, 71, 2, 810, 59.95, 47.95, 420);

SET max_recursive_iterations=20;

WITH RECURSIVE cte_packages AS (
SELECT 
  package_id,
  DENSE_RANK() OVER (ORDER BY package_id) AS p_group,
  package_item_id,
  DENSE_RANK() OVER (PARTITION BY package_id ORDER BY package_item_id) AS pi_group,
  ROW_NUMBER() OVER (PARTITION BY package_item_id ORDER BY variant_id) AS pi_group_row,
  COUNT(*) OVER (PARTITION BY package_item_id) AS pi_group_row_count,
  quantity,
  variant_id,
  retail_price,
  sell_price,
  stock_available
FROM packages
),
cte_combinations AS
(
  SELECT 
  		0 AS combination_id,
  		p_group,
  		p_group AS next_p_group,
  		pi_group,
  		pi_group_row,
  		CASE 
		  WHEN pi_group_row < pi_group_row_count
		  THEN pi_group_row + 1
		  ELSE 1
		END
		AS next_pi_group_row,
  		pi_group_row_count,
		package_id,
		package_item_id,
		quantity,
		variant_id,
		retail_price,
		sell_price,
		stock_available 	
	FROM 
		cte_packages
	WHERE 
		p_group = 1 AND pi_group = 1 AND pi_group_row = 1
	UNION
	SELECT
		cc.combination_id + 1,
		cpi.p_group,
		CASE 
		  WHEN cpi.pi_group_row = cpi.pi_group_row_count
		  THEN cpi.p_group + 1
		  ELSE cpi.p_group
		END
  		AS next_p_group,
  		cpi.pi_group,
  		cpi.pi_group_row,
  		CASE 
		  WHEN cpi.pi_group_row < cpi.pi_group_row_count
		  THEN cpi.pi_group_row + 1
		  ELSE 1
		END
		AS next_pi_group_row,
  		cpi.pi_group_row_count,
		cpi.package_id,
		cpi.package_item_id,
		cpi.quantity,
		cpi.variant_id,
		cpi.retail_price,
		cpi.sell_price,
		cpi.stock_available
	FROM
		cte_combinations cc
	INNER JOIN
		cte_packages cpi
		ON cpi.p_group = cc.next_p_group AND cpi.pi_group = 1 AND cpi.pi_group_row = cc.next_pi_group_row
	LEFT JOIN
		cte_packages opi
		ON opi.p_group = cpi.p_group AND opi.pi_group <> 1
)
SELECT * FROM cte_combinations

Records: 45  Duplicates: 0  Warnings: 0

combination_id	p_group	next_p_group	pi_group	pi_group_row	next_pi_group_row	pi_group_row_count	package_id	package_item_id	quantity	variant_id	retail_price	sell_price	stock_available
0	1	1	1	1	2	3	1000001	28	1	3399	489.00	342.30	26
1	1	1	1	2	3	3	1000001	28	1	3400	489.00	342.30	23
2	1	2	1	3	1	3	1000001	28	1	3409	489.00	342.30	81
3	2	2	1	1	2	7	1000002	31	1	6369	519.00	363.30	93
4	2	2	1	2	3	7	1000002	31	1	6371	519.00	363.30	60
5	2	2	1	3	4	7	1000002	31	1	6372	519.00	363.30	40
6	2	2	1	4	5	7	1000002	31	1	19289	519.00	363.30	24
7	2	2	1	5	6	7	1000002	31	1	19290	519.00	363.30	20
8	2	2	1	6	7	7	1000002	31	1	21774	519.00	363.30	14
9	2	3	1	7	1	7	1000002	31	1	23045	519.00	363.30	0
10	3	3	1	1	2	3	1000003	34	1	3399	489.00	342.30	26
11	3	3	1	2	3	3	1000003	34	1	3400	489.00	342.30	23
12	3	4	1	3	1	3	1000003	34	1	3409	489.00	342.30	81
13	4	4	1	1	2	7	1000004	39	1	6369	519.00	363.30	93
14	4	4	1	2	3	7	1000004	39	1	6371	519.00	363.30	60
15	4	4	1	3	4	7	1000004	39	1	6372	519.00	363.30	40
16	4	4	1	4	5	7	1000004	39	1	19289	519.00	363.30	24
17	4	4	1	5	6	7	1000004	39	1	19290	519.00	363.30	20
18	4	4	1	6	7	7	1000004	39	1	21774	519.00	363.30	14
19	4	5	1	7	1	7	1000004	39	1	23045	519.00	363.30	0
20	5	5	1	1	2	2	1000008	51	1	20107	799.00	639.20	5