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

CREATE OR REPLACE VIEW v_maker AS
    WITH including_tests AS (
        SELECT DISTINCT 
            -- Core attributes (id, sku, name, brand_name, launch date (coalesce(min(news from date), launch_date), live on site date , etc); used by most things; ==grommet prefix==; drop-ship; enabled; discontinued;
            cpe.entity_id                                           AS maker_id,
            core_store.store_id                                     AS store_id,
            cpe.sku                                                 AS maker_sku,
            TRIM(SUBSTRING(cpe.sku, 1, LOCATE('-', cpe.sku) - 1))   AS sku_prefix,
            COALESCE(
                (SELECT varchar_value
                    FROM mv_catalog_product_entity_attribute
                        AS attr
                    WHERE attribute_code = 'brand_name'
                        AND store_id = 0
                        AND attr.entity_id = cpe.entity_id),
                (SELECT varchar_value
                    FROM mv_catalog_product_entity_attribute
                        AS attr
                    WHERE attribute_code = 'name'
                        AND store_id = 0
                        AND attr.entity_id = cpe.entity_id)
            )                                                       AS name,
            (SELECT varchar_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'short_description'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id)             AS description,
            (SELECT int_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'visibility'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id) != 1        AS is_visible,
            (SELECT datetime_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'launch_date'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id)             AS live_on_site_date, -- FIXME
            (SELECT datetime_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'news_from_date'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id)             AS launch_date, -- FIXME join simples and min() ?
            COALESCE(( SELECT int_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'is_discontinued'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id), 0)         AS is_discontinued,
            COALESCE((SELECT int_value AS value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'status'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id
            ), 0) = 1                                               AS is_enabled -- FIXME: For marketing feed: is_available

            FROM magento.catalog_product_entity
                AS cpe
            JOIN magento.catalog_product_website
                AS cpw
                ON cpw.product_id = cpe.entity_id
            JOIN magento.core_store
                USING (website_id)
            JOIN (SELECT DISTINCT
                    maker_id, maker_sku, entity_id
                FROM mv_product_heirarchy_groupings)
                AS heir
                USING (entity_id)
            WHERE cpe.type_id = 'grouped'
                AND sku NOT LIKE '%TEST%'
                AND store_id != 2
        ),
        ranked AS (
            SELECT maker_id, RANK() OVER (PARTITION BY maker_id ORDER BY is_visible DESC, maker_id ASC) AS rank FROM including_tests
        )
        SELECT * FROM ranked WHERE rank = 1;

CREATE OR REPLACE VIEW v_product AS
        SELECT DISTINCT
            -- Core attributes (id, sku, name, brand_name, launch date (coalesce(min(news from date), launch_date), live on site date , etc); used by most things; ==grommet prefix==; drop-ship; enabled; discontinued;
            cpe.entity_id                                           AS product_id,
            core_store.store_id                                     AS store_id,
            TRIM(cpe.sku)                                           AS sku,
            TRIM(SUBSTRING(cpe.sku, 1, LOCATE('-', cpe.sku) - 1))   AS sku_prefix,
            (SELECT varchar_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'name'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id)             AS name,
            (SELECT varchar_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'brand_name'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id)             AS brand_name,
            (SELECT varchar_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'short_description'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id)             AS description,
            heir.maker_id                                           AS maker_id,
            heir.maker_sku                                          AS maker_sku,
            cpe.type_id                                             AS type_id,
            (SELECT int_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'visibility'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id) != 1        AS visible,
            (SELECT datetime_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'launch_date'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id)             AS live_on_site_date, -- FIXME
            (SELECT datetime_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'news_from_date'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id)             AS launch_date, -- FIXME
            COALESCE(( SELECT int_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'is_discontinued'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id), 0)         AS is_discontinued,
            COALESCE(( SELECT int_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'is_sample'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id), 0)         AS is_sample,
            LPAD((SELECT text_value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'upc'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id), 14, '0')   AS gtin, -- UPC-12 are GTIN-12s; Adding zeroes makes them GTIN-14s.
            COALESCE((SELECT int_value AS value
                FROM mv_catalog_product_entity_attribute
                    AS attr
                WHERE attribute_code = 'status'
                    AND store_id = 0
                    AND attr.entity_id = cpe.entity_id
            ), 0) = 1                                               AS is_enabled -- FIXME: For marketing feed: is_available

            FROM magento.catalog_product_entity
                AS cpe
            JOIN magento.catalog_product_website
                AS cpw
                ON cpw.product_id = cpe.entity_id
            JOIN magento.core_store
                USING (website_id)
            JOIN (SELECT DISTINCT
                    maker_id, maker_sku, entity_id
                FROM mv_product_heirarchy_groupings)
                AS heir
                USING (entity_id)
            WHERE cpe.type_id != 'grouped'
                AND sku != 'groupedproductdummy'
                AND maker_sku NOT LIKE '%TEST%'
                AND store_id != 2
            ;

            SELECT * FROM v_maker;
