-- SELECT
-- prd.id,
-- prd.brand,
-- prd.cat_no,
-- pkg.package,
-- pkg.origin_cost,
-- pkg.cost,
-- pkg.price,
-- pkg.delivery,
-- pkg.created_at,
-- pkg.modified_at,
-- prd.shipping_group
-- SELECT round(79 * 9.4936)

UPDATE "product_package" pkg
SET cost = case
when shipping_group ilike any(array['A1A', 'G1A', 'L']) then round((origin_cost + 2.5) * 7.12 * 1.13*1.045)
when shipping_group ilike 'B1A' then round((origin_cost + 2.5) * 7.12 * 1.13*1.045) + 85
when shipping_group ilike 'C1A' then round((origin_cost + 2.5) * 7.12 * 1.13*1.045) + 50
else null end,
price = case
when shipping_group ilike any(array['A1A', 'G1A', 'L']) then round(origin_cost * 16)
when shipping_group ilike 'B1A' then round((origin_cost + 5) * 16)
when shipping_group ilike 'C1A' then round((origin_cost + 5) * 16)
else null end,
price_expiry_date = now() + interval '37days'
FROM public.rawdata prd
WHERE pkg.brand = prd.brand
AND pkg.cat_no = prd.cat_no
AND pkg.brand = 'ep'
;
--
-- SELECT
-- *
-- FROM product_package
UPDATE product_package
set cost = (origin_cost) * 8.4 * 1.13 * 1.045,
price = origin_cost * 16,
price_expiry_date = now() + interval '37days'
where brand='bp';


-- SELECT
-- *
-- FROM product_package
UPDATE product_package
set cost = origin_cost * 8.3,
price = origin_cost * 16,
price_expiry_date = now() + interval '187days'
where brand='usp';


-- SELECT
-- *
-- FROM product_package
UPDATE product_package pkg
set cost = case
when (
    prd.info2 ilike '-86'
    or (attrs ->> 'shipping_condition') ilike 'dry ice'
    or (attrs ->> 'iata_regulations') ilike 'dangerous'
    or (attrs ->> 'controlled_prd') is not null
    or (attrs ->> 'un_code') is not null
) then round((origin_cost * 6.1)::numeric, 2)
else round((origin_cost * 0.7 * 6.8 * 1.13 * 1.045)::numeric, 2)
end,
price = case
when (
    prd.info2 ilike '-86'
    or (attrs ->> 'shipping_condition') ilike 'dry ice'
    or (attrs ->> 'iata_regulations') ilike 'dangerous'
    or (attrs ->> 'controlled_prd') is not null
    or (attrs ->> 'un_code') is not null
) then round((origin_cost * 16.8)::numeric, 2)
else round((origin_cost * 16)::numeric, 2)
end,
delivery = case
when delivery not ilike 'In Stock' then delivery
when (
    info2 ilike '-86'
    or (attrs ->> 'shipping_condition') ilike 'dry ice'
    or (attrs ->> 'iata_regulations') ilike 'dangerous'
    or (attrs ->> 'controlled_prd') is not null
    or (attrs ->> 'un_code') is not null
) then '70'
else '42'
end,
price_expiry_date = now() + interval '37days'
FROM rawdata prd
where pkg.brand = prd.brand
AND pkg.cat_no = prd.cat_no
AND pkg.brand='trc'

;




-- SELECT
-- *
-- FROM product_package
UPDATE product_package
set cost = origin_cost * 9,
price = origin_cost * 16,
delivery = 56,
price_expiry_date = now() + interval '37days'
where brand='nist';