DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_ADEX_PRICING

CREATE TABLE d_mdl_vpc_disc.HM_ADEX_PRICING
WITH ( format = 'Parquet', 
         parquet_compression = 'SNAPPY', 
         partitioned_by = ARRAY['p_periodo'],
         external_location= 's3://sagemaker-us-east-1-058528764918/vpc/pricingbpe/athenav2/HM_ADEX_PRICING/'
       )
AS (
SELECT CAST(periodo_val as varchar) periodo_adex, ruc_cd num_ruc, 
       substr(replace(substr(cast(date_add('month', +2, date_parse(CAST(periodo_val as varchar) ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6) periodo_ejecucion,
       substr(replace(substr(cast(date_add('month', +3, date_parse(CAST(periodo_val as varchar) ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6) periodo_campania,
       SUM(valor_mto) monto_adex,
       SUM(CASE WHEN tipo_dsc = 'EXP' THEN valor_mto else 0 END) monto_exportacion,
       SUM(CASE WHEN tipo_dsc = 'IMP' THEN valor_mto else 0 END) monto_importtacion, 
       SUM(CASE WHEN tipo_dsc = 'EXP' AND continente_dsc IN ('AFRICA', 'ASIA', 'OCEANIA', 'EUROPA') THEN valor_mto else 0 END) monto_exporttacion_otro_continente,
       SUM(CASE WHEN tipo_dsc = 'IMP' AND continente_dsc IN ('AFRICA', 'ASIA', 'OCEANIA', 'EUROPA') THEN valor_mto else 0 END) monto_importtacion_otro_continente,  
       SUM(CASE WHEN tipo_dsc = 'EXP' AND continente_dsc LIKE '%AMERICA%' THEN valor_mto else 0 END) monto_exporttacion_america,
       SUM(CASE WHEN tipo_dsc = 'IMP' AND continente_dsc LIKE '%AMERICA%' THEN valor_mto else 0 END) monto_importtacion_america,
       periodo_val p_periodo
FROM e_perm_aws.mst_vpc_adex
WHERE ruc_cd IN (select num_ruc from d_mdl_vpc_disc.STAGE_HM_BASE_CAMPANIA_PRICING)
GROUP BY periodo_val, ruc_cd
)