/*
SUMMARY: A table of products as merchandised, with prices and costs
DATABASE: bi-test, referencing magento
AUTHOR: Aria Stewart
NOTES ON WHAT THIS DOES, BUSINESS-WISE: Future-proof source of catalog pricing data (with cost)
TECHNICAL NOTES:
    - maker_id can be null. There are (at last count) 127 products without
      makers. Some are virtual like gift cards and perks. Some are not. Mostly
      discontinued, but validation and clean-up here would be great.

    Expected runtime 5 seconds.
*/

DELIMITER ;;

SET @@sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';;

REPLACE INTO view_sql_modes (table_name, sql_mode) VALUES ('v_product_price_cost', @@sql_mode);;

CREATE TABLE IF NOT EXISTS mv_product_price_cost (
    product_id int(10) unsigned NOT NULL,
    homepage_launch_date DATE,
    price decimal(12,4),
    min_price decimal(12,4),
    max_price decimal(12,4),
    cost decimal(12,4),
    min_cost decimal(12,4),
    max_cost decimal(12,4),
    special_price   decimal(12,4),
    special_price_start date,
    special_price_end   date,
    min_product_margin decimal(12,4) GENERATED ALWAYS AS (CASE min_price > 0
        WHEN true THEN (min_price - min_cost) / min_price * 100
        ELSE NULL
    END),
    max_product_margin decimal(12,4) GENERATED ALWAYS AS (CASE max_price > 0
        WHEN true THEN (max_price - max_cost) / max_price * 100
        ELSE NULL
    END),
    product_margin decimal(12,4) GENERATED ALWAYS AS (CASE price > 0
        WHEN true THEN (price - cost) / price * 100
        ELSE NULL
    END),
    refreshed_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (product_id)
) WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME (
    PARTITION p_hist HISTORY,
    PARTITION p_cur CURRENT
);;

CREATE OR REPLACE VIEW temp_v_product_price_cost AS
    WITH
        special_prices AS (
            SELECT
                product_id,
                (SELECT value
                    FROM mv_product_attribute_decimal
                        AS attr
                    WHERE attribute_code = 'special_price'
                        AND store_id = 0
                        AND attr.product_id = mv_product.product_id) AS special_price,
                (SELECT value
                    FROM mv_product_attribute_datetime
                        AS attr
                    WHERE attribute_code = 'special_from_date'
                        AND store_id = 0
                        AND attr.product_id = mv_product.product_id) AS special_price_start,
                (SELECT value
                    FROM mv_product_attribute_datetime
                        AS attr
                    WHERE attribute_code = 'special_to_date'
                        AND store_id = 0
                        AND attr.product_id = mv_product.product_id) AS special_price_end
                FROM mv_product
        ),
        price AS (
            SELECT
                    product_id,
                    value
                FROM mv_product_attribute_decimal
                WHERE attribute_code = 'price' AND store_id = 0
        ),
        cost AS (
            SELECT
                    product_id,
                    value
                FROM mv_product_attribute_decimal
                WHERE attribute_code = 'cost' AND store_id = 0
        ),
        bundle_parts_price_cost AS (
        SELECT
                parent_product_id AS product_id,
                MIN(CASE selection_price_type
                    WHEN 0 THEN selection_price_value
                    WHEN 1 THEN bundle_price.value * (selection_price_value / 100)
                END) * IF (selection_can_change_qty, 1, selection_qty) AS min_price_adj,
                MAX(CASE selection_price_type
                    WHEN 0 THEN selection_price_value
                    WHEN 1 THEN bundle_price.value * (selection_price_value / 100)
                END) * IF (selection_can_change_qty, 1, selection_qty) AS max_price_adj,
                MIN(cost.value * IF (selection_can_change_qty, 1, selection_qty)) AS min_cost,
                MAX(cost.value * IF (selection_can_change_qty, 1, selection_qty)) AS max_cost
            FROM mv_product
            JOIN magento.catalog_product_bundle_selection USING (product_id)
            LEFT JOIN cost USING (product_id)
            LEFT JOIN price AS bundle_price ON bundle_price.product_id = parent_product_id
            GROUP BY parent_product_id, option_id
        ),
        bundle_price_cost AS (
            SELECT
                    product_id AS product_id,
                    price.value + SUM(min_price_adj) AS min_price,
                    price.value + SUM(max_price_adj) AS max_price,
                    SUM(min_cost) AS min_cost,
                    SUM(max_cost) AS max_cost
                FROM bundle_parts_price_cost
                JOIN price USING (product_id)
                GROUP BY product_id
        ),
        config_parts_price_cost AS (
            SELECT
                    RANK() OVER (PARTITION BY l.parent_id, position ORDER BY min_price_adj ASC) AS rank_low,
                    RANK() OVER (PARTITION BY l.parent_id, position ORDER BY max_price_adj DESC) AS rank_high,
                    l.parent_id AS product_id,
                    COALESCE(MIN(CASE is_percent
                        WHEN false THEN pricing_value
                        WHEN true THEN config_price.value * (pricing_value / 100)
                    END), 0) AS min_price_adj,
                    COALESCE(MAX(CASE is_percent
                        WHEN false THEN pricing_value
                        WHEN true THEN config_price.value * (pricing_value / 100)
                    END), 0) AS max_price_adj,
                    MIN(cost.value) AS min_cost,
                    MAX(cost.value) AS max_cost
                FROM magento.catalog_product_super_link AS l 
                JOIN magento.catalog_product_super_attribute AS a ON l.parent_id = a.product_id
                LEFT JOIN magento.catalog_product_super_attribute_pricing ap USING (product_super_attribute_id)
                JOIN mv_product_attribute AS v ON (l.product_id = v.product_id AND a.attribute_id = v.attribute_id)
                LEFT JOIN cost ON l.product_id = cost.product_id
                LEFT JOIN price AS config_price ON l.product_id = config_price.product_id
                GROUP BY l.parent_id, position
        ),
        config_price_cost AS (
            SELECT
                    product_id AS product_id,
                    price.value + SUM(min_price_adj) AS min_price,
                    price.value + SUM(max_price_adj) AS max_price,
                    SUM(min_cost) AS min_cost,
                    SUM(max_cost) AS max_cost
                FROM config_parts_price_cost
                JOIN price USING (product_id)
                GROUP BY product_id
        )

        SELECT * from config_parts_price_cost
        /*
    SELECT
            product_id,
            DATE(news_from_date.value) AS homepage_launch_date,
            CASE type_id
                WHEN 'simple' THEN price.value
                WHEN 'configurable' THEN
                    CASE config_price_cost.min_price = config_price_cost.max_price
                        WHEN true THEN config_price_cost.min_price
                        ELSE null
                    END
                WHEN 'bundle' THEN
                    CASE bundle_price_cost.min_price = bundle_price_cost.max_price
                        WHEN true THEN bundle_price_cost.min_price
                        ELSE null
                    END
            END                                                         AS price,
            CASE type_id
                WHEN 'simple' THEN price.value
                WHEN 'configurable' THEN config_price_cost.min_price
                WHEN 'bundle' THEN bundle_price_cost.min_price
            END                                                         AS min_price,
            CASE type_id
                WHEN 'simple' THEN price.value
                WHEN 'configurable' THEN config_price_cost.max_price
                WHEN 'bundle' THEN bundle_price_cost.max_price
            END                                                         AS max_price,
            CASE type_id
                WHEN 'simple' THEN cost.value
                WHEN 'configurable' THEN
                    CASE config_price_cost.min_cost = config_price_cost.max_cost
                        WHEN true THEN config_price_cost.min_cost
                        ELSE null
                    END
                WHEN 'bundle' THEN
                    CASE bundle_price_cost.min_cost = bundle_price_cost.max_cost
                        WHEN true THEN bundle_price_cost.min_cost
                        ELSE null
                    END
            END                                                         AS cost,
            CASE type_id
                WHEN 'simple' THEN cost.value
                WHEN 'configurable' THEN config_price_cost.min_cost
                WHEN 'bundle' THEN bundle_price_cost.min_cost
            END                                                         AS min_cost,
            CASE type_id
                WHEN 'simple' THEN cost.value
                WHEN 'configurable' THEN config_price_cost.max_cost
                WHEN 'bundle' THEN bundle_price_cost.max_cost
            END                                                         AS max_cost,
            IF(
                NOW() BETWEEN special_price_start AND special_price_end,
                special_price,
                NULL
            )                                                           AS special_price,
            IF(
                NOW() BETWEEN special_price_start AND special_price_end,
                special_price_start,
                NULL
            )                                                           AS special_price_start,
            IF(
                NOW() BETWEEN special_price_start AND special_price_end,
                special_price_end,
                NULL
            )                                                           AS special_price_end

        FROM mv_product
        LEFT JOIN bundle_price_cost USING (product_id)
        LEFT JOIN config_price_cost USING (product_id)
        LEFT JOIN price USING (product_id)
        LEFT JOIN cost USING (product_id)
        LEFT JOIN special_prices USING (product_id)
        LEFT JOIN (
            SELECT product_id, value
                FROM `bi-test`.mv_product_attribute_datetime
                WHERE attribute_code = 'news_from_date' AND store_id = 0
        ) news_from_date USING (product_id)
*/
    ;;

-- CALL materialize_view('v_product_price_cost');;

DELIMITER ;
