-- FT PO Lines - Allocated
-- TODOS: 
    -- improve allocated po number splitting 

-- NOTES: 
    -- money: cost and price values are in lowest unit for the currency (e.g. cents for USD)
    -- update date: a revision to update date does not necessarily mean a relevant attribute changed; but instead something on the data source changed

WITH po_ev AS(
    SELECT purchase_order_id, MIN(created_at) created_at
    FROM hive.fashionthing_production.purchase_order_events
    WHERE description = 'purchase_order_published'
    GROUP BY purchase_order_id, description
)
SELECT
  po_line.*
FROM (
    SELECT
    ---- po line attributes
  -- sku
        tm_as.sku_id AS sku_id,
        tm_as.units_allocated AS quantity,
        tm_as.units_allocated * ft_mb.unit_cost_value AS total_cost, 
        ft_os.vendor_part_number AS vendor_part_number, 
        ft_os.universal_product_code AS universal_product_code, 
        -- ft_os.european_article_number AS european_article_number, 
        'Ea' AS uom,
        
        -- buy 
        CASE WHEN tm_mb.cancelled_at IS NULL THEN false ELSE true END AS cancel_flag,
        tm_mb.brand_sku AS brand_sku,
        ft_mb.commodity_code AS commodity_code,
        ft_mb.unit_cost_value AS cost,
        ft_mb.initial_retail_price_value AS initial_retail_price_value,
        
        -- purchase order
        -- CASE WHEN ft_po.created_at < DATE '2021-08-04' THEN
        --     CONCAT(SUBSTR(ft_po.oracle_purchase_order_number, 1, 8), '-', tm_h.code, '-', SUBSTR(ft_po.business_line, 1, 1))
        -- ELSE 
            CONCAT(SUBSTR(ft_po.oracle_purchase_order_number, 1, 8), '-', tm_h.code) /* END */ AS po_number, 

        -- item type 
        tm_ci.name AS class,
        tm_it_department.name AS department,
        tm_it_division.name AS division,
        tm_it_mi.name AS merch_intent,
        tm_it_bl.name AS gender_intent, 
        
        -- style 
        ft_ft_sv.price_override_value AS price_value,
        ft_ft_s.retail_price_value AS retail_price_value, 
        ft_ft_sv.size_spectrum AS size_spectrum,
        ft_ft_sv.primary_client_focus AS primary_client_focus, 
        JSON_EXTRACT_SCALAR(ft_ft_sv.characteristics, '$["seasonal_intent"]') AS seasonal_intent,
        ft_ft_sv.business_group AS business_group,
        
        tm_po.cancel_on AS expiration_date,
        
    ---- create and update dates

        -- sku
        tm_as.created_at as tm_allocated_sku_create_date,
        tm_as.updated_at as tm_allocated_sku_update_date,
        tm_os.created_at as tm_ordered_sku_create_date,
        tm_os.updated_at as tm_ordered_sku_update_date,
        ft_os.created_at as ft_ordered_sku_create_date,
        ft_os.updated_at as ft_ordered_sku_update_date,
        
        -- merch buy
        tm_mb.created_at as tm_merch_buy_create_date,
        tm_mb.updated_at as tm_merch_buy_update_date,
        ft_mb.created_at as ft_merch_buy_create_date,
        ft_mb.updated_at as ft_merch_buy_update_date,
        
        -- purchase order
        tm_po.created_at as tm_purchase_order_create_date,
        tm_po.updated_at as tm_purchase_order_update_date,
        ft_po.created_at as ft_purchase_order_create_date,
        ft_po.updated_at as ft_purchase_order_update_date,
    
        -- style
        ft_s.created_at as ft_style_create_date,
        ft_s.updated_at as ft_style_update_date,
        ft_ft_s.created_at as ft_ft_style_create_date,
        ft_ft_s.updated_at as ft_ft_style_update_date,
        
        -- style variant
        ft_sv.created_at as ft_style_variant_create_date,
        ft_sv.updated_at as ft_style_variant_update_date,

        ft_sk.brand_color color,
        tm_cs.name size
    
---- data sources

    -- subject: allocated po line    
    FROM hive.transmetropolitan_production.allocated_skus tm_as
    
    -- hizzy attributes
    INNER JOIN hive.transmetropolitan_production.buy_hizzy_allocations tm_bha ON tm_bha.id = tm_as.buy_hizzy_allocation_id
    INNER JOIN hive.transmetropolitan_production.hizzies tm_h ON tm_h.id = tm_bha.hizzy_id
    
    -- purchase attributes
    INNER JOIN hive.transmetropolitan_production.ordered_skus tm_os ON tm_os.id = tm_as.ordered_sku_id 
    INNER JOIN hive.fashionthing_production.ft_ordered_skus ft_os ON ft_os.ordered_sku_id = tm_os.id 
    INNER JOIN hive.transmetropolitan_production.merch_buys tm_mb ON tm_mb.id = tm_os.merch_buy_id
    INNER JOIN hive.fashionthing_production.ft_merch_buys ft_mb ON ft_mb.merch_buy_id = tm_mb.id
    INNER JOIN hive.transmetropolitan_production.purchase_orders tm_po ON tm_po.id = tm_mb.purchase_order_id
    INNER JOIN hive.fashionthing_production.ft_purchase_orders ft_po ON ft_po.purchase_order_id = tm_po.id
    
    -- styles attributes
    INNER JOIN hive.fashionthing_production.styles ft_s ON ft_s.id = tm_mb.style_id
    INNER JOIN hive.fashionthing_production.ft_styles ft_ft_s ON ft_ft_s.style_id = ft_s.id
    INNER JOIN hive.fashionthing_production.style_variants ft_sv ON ft_sv.id = tm_mb.style_variant_id
    INNER JOIN hive.fashionthing_production.ft_style_variants ft_ft_sv ON ft_ft_sv.style_variant_id = ft_sv.id
    INNER JOIN hive.fashionthing_production.skus ft_sk ON tm_os.sku_id = ft_sk.id
    INNER JOIN hive.transmetropolitan_production.core_size tm_cs ON tm_cs.id = ft_sk.size_id
    
    -- item type attributes 
    INNER JOIN hive.transmetropolitan_production.core_itemtype tm_ci ON tm_ci.id = ft_s.item_type_id
    INNER JOIN hive.transmetropolitan_production.core_itemtype tm_it_department ON tm_it_department.id = tm_ci.parent_id
    INNER JOIN hive.transmetropolitan_production.core_itemtype tm_it_division ON tm_it_division.id = tm_it_department.parent_id
    INNER JOIN hive.transmetropolitan_production.core_itemtype tm_it_bl ON tm_it_bl.id = tm_it_division.parent_id
    INNER JOIN hive.transmetropolitan_production.core_itemtype tm_it_mi ON tm_it_mi.id = tm_it_bl.parent_id
    
    --brand
    INNER JOIN hive.transmetropolitan_production.core_brand tm_cb ON tm_cb.id = tm_mb.brand_id
    
    -- publish events 
    INNER JOIN po_ev ON po_ev.purchase_order_id = ft_po.purchase_order_id

    
---- filtering 
    WHERE
        (tm_po.updated_at >= DATE '2021-08-04' OR po_ev.created_at >= DATE '2021-08-04')
        AND LOWER(tm_cb.name) NOT LIKE '%nike%'
        AND (
            tm_as.units_allocated > 0
            OR
            tm_os.created_at <> tm_os.updated_at
        )
        AND tm_mb.allocation_approved = true
        AND ft_po.oracle_purchase_order_number <> ''
) as po_line