drop table if exists raena_analytics.monthly_sold_product;

create table raena_analytics.monthly_sold_product
as
select brand_name , brand_page_url , 
        sum(product_sold) product_sold
from
(
    select *
    from raena_spider_management.rce_brand
) rceb
left join
(
    select *
    from raena_spider_management.rce_product
) rcep on rcep.rce_brand_id = rceb.id
where product_sold is not null and brand_name is not null and brand_name != ''
group by brand_name, brand_page_url
order by sum(product_sold) desc;


drop table if exists raena_analytics.avg_sold_product;

create table raena_analytics.avg_sold_product
as
select distinct brand_name , brand_page_url , 
        avg(ratings) avg_ratings
from
(
    select *
    from raena_spider_management.rce_brand
) rceb
left join
(
    select *
    from raena_spider_management.rce_product
) rcep on rcep.rce_brand_id = rceb.id
where ratings is not null and brand_name is not null and brand_name != ''
group by brand_name, brand_page_url
order by avg(ratings) desc;

drop table if exists raena_analytics.total_sold_product;

create table raena_analytics.total_sold_product
as
select brand_name , brand_page_url ,
	rceb.rce_source_id,
        sum(product_sold_total) product_sold_total,
        sum(product_sold) product_sold,
        avg(ratings) avg_ratings,
        count(distinct rcers.id) number_of_reseller_store_selling,
        CASE
            WHEN LOWER(brand_name) = LOWER(b.name) then true
            ELSE false
        END ss_present_in_Raena,
        b.name raena_brand_name,
        number_of_products_in_raena
from
(
    select *
    from raena_spider_management.rce_brand
) rceb
left join
(
    select *
    from raena_spider_management.rce_product
) rcep on rcep.rce_brand_id = rceb.id and rceb.rce_source_id= rcep.rce_source_id
left join
(
    select A.name , count(distinct B.sku) number_of_products_in_raena
    from raena_catalog_management.brand A
    left join raena_catalog_management.product B on   A.id = B.brand_id
    group by 1
) b on LOWER(b.name) = LOWER(rceb.brand_name)
left join
(
    select *
    from raena_spider_management.rce_reseller_store
) rcers on rcers.id = rcep.rce_store_id
where product_sold_total is not null and brand_name is not null and brand_name != ''
group by brand_name, brand_page_url,rceb.rce_source_id,CASE
            WHEN LOWER(brand_name) = LOWER(b.name) then true
            ELSE false
        end,b.name,number_of_products_in_raena
order by sum(product_sold_total) desc;

drop table if exists raena_analytics.inventory_snapshot_dashboard_table ;
    
create table raena_analytics.inventory_snapshot_dashboard_table 
as
SELECT snp_date, sku_code,case WHEN warehouse_code='WH-CGK45' THEN 'Cikarang'
                    WHEN warehouse_code='WH-SRG18' THEN 'Semarang'
                    WHEN warehouse_code='WH-SUB51' THEN 'Surabaya'
                    WHEN warehouse_code='WH-UPG04' THEN 'Makassar'
                    WHEN warehouse_code='WH-MES07' THEN 'Medan'
                    WHEN warehouse_code='WH-AAP02' THEN 'Samarinda' end warehouse , min(inventory) inventory_remaining
FROM raena_analytics.inventory_snapshot
GROUP BY 1,2,3
ORDER BY 1,2,3;