drop table  raena_analytics.increase_cogs_alert;

create table raena_analytics.increase_cogs_alert
as
SELECT AA.sku,
       brand_name,
       new_cogs,
       new_cogs_updated_date,
       old_cogs,
       old_cogs_updated_date,
       po_number,
       cogs cogs_in_po,
       calculated_pkp_cogs po_cogs_with_pkp,
       quantity quantity_in_po,
       wholesale_gold_price,
       wholesale_silver_price,
       wholesale_bronze_price,
  cast(CEILING(new_cogs/cast((100-gold_gm_target) as bigint)) as bigint)*100 gold_msp,
  cast(CEILING(new_cogs/cast((100-silver_gm_target)as bigint)) as bigint)*100 silver_msp,
  cast(CEILING(new_cogs/cast((100-bronze_gm_target)as bigint)) as bigint)*100 bronze_msp
FROM
  (SELECT sku,
          cogs new_cogs,
          created_at  new_cogs_updated_date,
          old_cogs ,
          brand_name,
          cast(wholesale_gold_price as varchar)wholesale_gold_price ,
       cast(wholesale_silver_price as varchar) wholesale_silver_price,
       cast(wholesale_bronze_price as varchar)wholesale_bronze_price,
          old_created_date  old_cogs_updated_date,
          cogs-old_cogs diff
from (SELECT A.sku,
                        A.cogs,
                        A.old_cogs,
                        A.created_at,
                        A.old_created_date,
                        raena_catalog_management.brand.name brand_name,
                         raena_catalog_management.product.tier_price::json->'bf645e97-8a48-4977-8367-e987489760f9' wholesale_gold_price,
                        raena_catalog_management.product.tier_price::json->'8eb95d6e-915a-4a91-9c12-fa43db995e19' wholesale_silver_price,
                        raena_catalog_management.product.tier_price::json->'07030fbe-5801-4318-9e97-fe33fa169894' wholesale_bronze_price
                FROM (select sku , cogs , lead(cogs, 1)over ( partition by sku  order by created_at desc ) old_cogs ,
                     created_at ,
                  lead(created_at , 1 ) over (partition by sku  order by created_at desc ) old_created_date   
               from raena_catalog_management.cogs_audit 
               order by sku , created_at desc) A
                left join  raena_catalog_management.product on A.sku=raena_catalog_management.product.sku
                LEFT JOIN raena_catalog_management.brand  ON raena_catalog_management.product.brand_id=raena_catalog_management.brand.id 
        )A ) AA
LEFT JOIN
  (SELECT B.po_number,
          A.sku,
          A.received_quantity quantity ,
          C.cogs,
          C.calculated_pkp_cogs,
          A.created_at
   FROM raena_erp_management.inbound_grn_sku A
   LEFT JOIN raena_erp_management.inbound_order B ON A.po_id=B.id
   LEFT  JOIN raena_erp_management.inbound_order_sku C ON A.sku_id = C.id
  )BB ON AA.sku = BB.sku
AND AA.new_cogs_updated_date::date = BB.created_at::Date
LEFT JOIN
(SELECT sku ,
        sum(CASE WHEN tierName='GOLD' THEN gm_target END) gold_gm_target,
        sum(CASE WHEN tierName='SILVER' THEN gm_target END) silver_gm_target,
        sum(CASE WHEN tierName='BRONZE' THEN gm_target END)bronze_gm_target
 FROM
   (SELECT DISTINCT SKU,
                    gm_target,
                    t.name AS tierName
    FROM raena_gross_margin_management.gross_margin_config AA
    LEFT JOIN raena_user_management.tier t ON cast(AA.gm_target_tier AS TEXT) =cast(t.id AS TEXT)
    where gm_target <100) A
 GROUP BY 1) DD ON AA.sku = DD.sku
 where diff<>0
ORDER by gold_msp desc;