/*
SUMMARY: A flat products table with attributes of interest
DATABASE: bi-test, referencing magento
AUTHOR: Aria Stewart
NOTES ON WHAT THIS DOES, BUSINESS-WISE:
    Summary of all makers 
TECHNICAL NOTES:
    This all comes from store 1. We don't have maker SKUs in wholesale as of
    2018-08-31.

    Expected runtime is 5 seconds.
*/

DELIMITER ;;

SET @@sql_mode = 'STRICT_TRANS_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_thirdparty_reflektion_categories', @@sql_mode);;

CREATE OR REPLACE VIEW v_thirdparty_reflektion_categories AS
  WITH RECURSIVE 
    url AS (
      SELECT * FROM (
        SELECT entity_id AS category_id, value, DENSE_RANK() OVER (PARTITION BY entity_id ORDER BY store_id = 1 DESC) AS rank
          FROM magento.catalog_category_entity_url_key 
      ) x WHERE rank = 1
    ),
    cats AS (
      SELECT 
          category_id, 
          store_id,
          category_type,
          category_name,
          TRIM(category_name) AS category_breadcrumbs,
          url.value AS url_key,
          CONCAT('/', url.value) AS url_path
        FROM mv_category
        JOIN url USING (category_id)
        WHERE category_depth <=> 1 
      UNION ALL
      SELECT
          c.category_id,
          c.store_id,
          c.category_type,
          c.category_name,
          CONCAT(cats.category_breadcrumbs, '>', TRIM(c.category_name)),
          url.value AS url_key,
          CONCAT(cats.url_path, '/', url.value)
        FROM mv_category c 
        JOIN url USING (category_id)
        JOIN cats ON cats.category_id = c.parent_category_id
    )
  SELECT 
      category_breadcrumbs,
      url_path AS category_landingpage_url,
      category_name AS name,
      url_key,
      url_path,
      category_name AS title,
      COALESCE(meta_title, category_name) AS meta_title,
      description AS description,
      COALESCE(meta_description, description) AS meta_description,
      meta_keywords AS meta_keywords,
      thumbnail_url AS thumbnail_url,
      category_id AS ccid
    FROM cats
    LEFT JOIN (
      SELECT category_id, meta_title FROM (
        SELECT entity_id AS category_id, value AS meta_title, DENSE_RANK() OVER (PARTITION BY entity_id ORDER BY store_id = 1 DESC) AS rank
          FROM magento.catalog_category_entity_varchar
          JOIN magento.eav_attribute using (attribute_id)
          WHERE attribute_code = 'meta_title'
      ) x WHERE rank = 1
    ) AS meta_title USING (category_id)
    LEFT JOIN (
      SELECT category_id, description FROM (
        SELECT entity_id AS category_id, value AS description, DENSE_RANK() OVER (PARTITION BY entity_id ORDER BY store_id = 1 DESC) AS rank
          FROM magento.catalog_category_entity_text
          JOIN magento.eav_attribute using (attribute_id)
          WHERE attribute_code = 'description'
      ) x WHERE rank = 1
    ) AS description USING (category_id)
    LEFT JOIN (
      SELECT category_id, meta_description FROM (
        SELECT entity_id AS category_id, value AS meta_description, DENSE_RANK() OVER (PARTITION BY entity_id ORDER BY store_id = 1 DESC) AS rank
          FROM magento.catalog_category_entity_text
          JOIN magento.eav_attribute using (attribute_id)
          WHERE attribute_code = 'meta_description'
      ) x WHERE rank = 1
    ) AS meta_description USING (category_id)
    LEFT JOIN (
      SELECT category_id, meta_keywords FROM (
        SELECT entity_id AS category_id, value AS meta_keywords, DENSE_RANK() OVER (PARTITION BY entity_id ORDER BY store_id = 1 DESC) AS rank
          FROM magento.catalog_category_entity_text
          JOIN magento.eav_attribute using (attribute_id)
          WHERE attribute_code = 'meta_keywords'
      ) x WHERE rank = 1
    ) AS meta_keywords USING (category_id)
    LEFT JOIN (
      SELECT category_id, thumbnail_url FROM (
        SELECT entity_id AS category_id, value AS thumbnail_url, DENSE_RANK() OVER (PARTITION BY entity_id ORDER BY store_id = 1 DESC) AS rank
          FROM magento.catalog_category_entity_varchar
          JOIN magento.eav_attribute using (attribute_id)
          WHERE attribute_code = 'image'
      ) x WHERE rank = 1
    ) AS image USING (category_id)

    WHERE store_id = 1

;;

DELIMITER ;
