MySQL 8.0

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

CREATE TABLE `products` (
  `prod_id` int(9) UNSIGNED NOT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `price` int(16) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `products` (`prod_id`, `product_name`, `price`) VALUES
(1, 'Product 1', 1000),
(2, 'Product 2', 2000);

CREATE TABLE `product_options` (
  `prod_op_id` int(9) UNSIGNED NOT NULL,
  `prod_op_name` varchar(255) NOT NULL,
  `prod_id` int(9) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `product_options` (`prod_op_id`, `prod_op_name`, `prod_id`) VALUES
(1, 'Color', 1),
(2, 'Size', 1),
(3, 'Grade', 1),
(4, 'Direction', 2),
(5, 'Smell', 2),
(6, 'Origin', 2);

CREATE TABLE `product_option_choices` (
  `choice_id` int(16) UNSIGNED NOT NULL,
  `prod_op_id` int(9) UNSIGNED NOT NULL,
  `prod_id` int(9) UNSIGNED NOT NULL,
  `choice_name` varchar(255) NOT NULL,
  `choice_price_adjust` int(10) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `product_option_choices` (`choice_id`, `prod_op_id`, `prod_id`, `choice_name`, `choice_price_adjust`) VALUES
(1, 1, 1, 'Purple', 100),
(2, 1, 1, 'Orange', -500),
(3, 2, 1, 'Small', -300),
(4, 2, 1, 'Large', 400),
(5, 3, 1, 'A', 900),
(6, 3, 1, 'B', 100),
(7, 4, 2, 'Up', 200),
(8, 4, 2, 'Down', -200),
(9, 5, 2, 'Vanilla', -150),
(10, 5, 2, 'Sandlewood', 0),
(11, 6, 2, 'Afica', 0),
(12, 6, 2, 'Norway', 700);

ALTER TABLE `products`
  ADD PRIMARY KEY (`prod_id`);

ALTER TABLE `product_options`
  ADD PRIMARY KEY (`prod_op_id`);

ALTER TABLE `product_option_choices`
  ADD PRIMARY KEY (`choice_id`);

Records: 2  Duplicates: 0  Warnings: 0

Records: 6  Duplicates: 0  Warnings: 0

Records: 12  Duplicates: 0  Warnings: 0

Records: 0  Duplicates: 0  Warnings: 0

Records: 0  Duplicates: 0  Warnings: 0

Records: 0  Duplicates: 0  Warnings: 0

/*  
Try to change the values of the variables @usr_prod_id And @usr_prod_op_id
The variables here play the role of user selections for fetching the data

VARIABLES SETTINGS:
 1. If both ids are 0 or Null - result is all products  with TOTAL values
 2. If product id = 0 or Null and option id > 0 result is selected option with option values 
 3, If product id > 0 And option id = 0 or Null result is selected product with all options and option values
 4. If product id > 0 And option id > 0 result is selected option with option values (same as 2.) 
 5. If any id is less then 0 - there will be no rows selected

*/
SET @usr_prod_id = 0, 
    @usr_prod_op_id = 0;
WITH
  usr_selection as
    ( Select  Case When Coalesce(@usr_prod_id, 0) = 0 And 
                        Coalesce(@usr_prod_op_id , 0) = 0 
                   Then 'PRODS'
                   When Coalesce(@usr_prod_id, 0) = 0 And 
                        Coalesce(@usr_prod_op_id , 0) > 0 
                   Then 'OPTS'
                   When Coalesce(@usr_prod_id, 0) > 0 And 
                        Coalesce(@usr_prod_op_id , 0) = 0 
                   Then 'OPTS'
                   When Coalesce(@usr_prod_id, 0) > 0 And
                        Coalesce(@usr_prod_op_id , 0) > 0
                   Then 'OPTS'
              Else 'ERROR'
              End as flag
   ), 
  grid AS
    ( Select     usr.flag, p.prod_id, p.product_name, p.price, 
                 po.prod_op_id, po.prod_op_name, 
                 poc.choice_name, poc.choice_price_adjust
      From       product_options po
      Inner Join usr_selection usr ON( usr.flag != 'ERROR' )
      Left Join  product_option_choices poc ON(poc.prod_id = po.prod_id And poc.prod_op_id = po.prod_op_id)
      Left Join  products p ON(p.prod_id = po.prod_id)  
   ),
  adjustments as
   ( Select    flag, prod_id, product_name, price, prod_op_id, prod_op_name, 
               Min(choice_price_adjust) as lowest_price_adjust, 
               Max(choice_price_adjust) as largest_price_adjust
     From      grid
     Group By  flag, prod_id, product_name, prod_op_id, prod_op_name, price
   )
Select Distinct    
          a.prod_id, a.product_name, a.price,  
          Case When flag = 'PRODS' 
               Then Sum(a.lowest_price_adjust) 
                        Over(Partition By prod_id)  
               When flag = 'OPTS'
               Then lowest_price_adjust
          End as lowest,
          Case When flag = 'PRODS' 
               Then Sum(a.largest_price_adjust) 
                        Over(Partition By prod_id)  
               When flag = 'OPTS'
               Then largest_price_adjust
          End as largest, 
          Case When flag = 'PRODS' Then Concat('Total - ', a.product_name)
               When flag = 'OPTS'  Then Concat('Option - ', a.prod_op_name)
          End as source_of_values
From      adjustments a
Where     a.prod_id = Case When Coalesce(@usr_prod_id, 0) > 0 
                           Then @usr_prod_id 
                      Else a.prod_id 
                      End 
      And 
          a.prod_op_id = Case When Coalesce(@usr_prod_op_id, 0) > 0 
                              Then @usr_prod_op_id 
                         Else a.prod_op_id
                         End
Order By  prod_id, product_name

prod_id	product_name	price	lowest	largest	source_of_values
1	Product 1	1000	-700	1400	Total - Product 1
2	Product 2	2000	-350	900	Total - Product 2