DROP TABLE IF EXISTS d_mdl_vpc_disc.JRR_AUTCOMPLETADO_BASE_PROVEEDORES;
CREATE TABLE d_mdl_vpc_disc.JRR_AUTCOMPLETADO_BASE_PROVEEDORES
WITH ( format = 'Parquet', 
   parquet_compression = 'SNAPPY', 
   partitioned_by = ARRAY['p_periodo'],
   external_location= 's3://sagemaker-us-east-1-058528764918/vpc/Factura_electronica/athena_2/JRR_AUTCOMPLETADO_BASE_PROVEEDORES/'
  )
AS (
    SELECT BB.periodo, BB.periodo_2,
           BB.fuente, BB.tipo_origen, BB.tipo,
           CASE WHEN COALESCE(BB.cu_proveedor, '') = '' THEN EE.cuc_num ELSE BB.cu_proveedor END cu_proveedor,
           BB.num_doc_proveedor, 
           CASE WHEN COALESCE(BB.num_doc_proveedor_hash, '') = '' THEN CC.key_value ELSE BB.num_doc_proveedor_hash END num_doc_proveedor_hash,
           CASE WHEN COALESCE(BB.cu_hub, '') = '' THEN FF.cuc_num ELSE BB.cu_hub END cu_hub,
           BB.num_doc_hub,
           CASE WHEN COALESCE(BB.num_doc_hub_hash, '') = '' THEN DD.key_value ELSE BB.num_doc_hub_hash END num_doc_hub_hash,
            
           CASE WHEN num_doc_proveedor_init != num_doc_proveedor THEN 1 ELSE 0 END flg_rescate_ruc10,
           CASE WHEN COALESCE(BB.num_doc_proveedor_hash, '') = '' AND COALESCE(CC.key_value, '') != '' THEN 1 ELSE 0 END flg_rescate_ruc_prov_dim,
           CASE WHEN COALESCE(BB.num_doc_hub_hash, '') = '' AND COALESCE(DD.key_value, '') != '' THEN 1 ELSE 0 END flg_rescate_ruc_hub_dim,
           CASE WHEN COALESCE(BB.cu_proveedor, '') = '' AND COALESCE(EE.cuc_num, '') != '' THEN 1 ELSE 0 END flg_rescate_cuc_prov_dim,
           CASE WHEN COALESCE(BB.cu_hub, '') = '' AND COALESCE(FF.cuc_num, '') != '' THEN 1 ELSE 0 END flg_rescate_cuc_hub_dim,
           
           BB.periodo p_periodo
    FROM d_mdl_vpc_disc.HM_JRR_BASE_PROVEEDORES BB
    LEFT JOIN e_perm_aws.t_dim_cliente CC
    ON BB.cu_proveedor = CC.cuc_num 
    LEFT JOIN e_perm_aws.t_dim_cliente DD
    ON BB.cu_hub = DD.cuc_num
    LEFT JOIN e_perm_aws.t_dim_cliente EE
    ON BB.num_doc_proveedor_hash = EE.key_value 
    LEFT JOIN e_perm_aws.t_dim_cliente FF
    ON BB.num_doc_hub_hash = FF.key_value 
)