DROP TABLE IF EXISTS disc_comercial.HM_OBM_BASE_MATRIZ_RUC

CREATE TABLE disc_comercial.HM_OBM_BASE_MATRIZ_RUC
WITH ( format = 'Parquet', 
     parquet_compression = 'SNAPPY', 
     partitioned_by = ARRAY['p_periodo'],
     external_location= 's3://ibk-discovery-comercial-us-east-1-654654352211-data/discovery/comercial/vpc_new/202501_Aceptacion_BPE/Athena/HM_OBM_BASE_MATRIZ_RUC/'
   )
AS (
WITH TMP_FEEDBACK AS (
  SELECT 
        gestion periodo_campania, 
        num_ruc_autocompletado num_ruc,
        MAX(flg_gestionado_estricto) flg_gestionado,
        MAX(flg_ce) flg_ce,
        MAX(flg_nuevo_ap) flg_nuevo_ap_feeedback,
        MAX(flg_nuevo_pre) flg_nuevo_pre_feeedback
     FROM e_perm_aws.ds_csfedtlv_pso
     WHERE 1 = 1
     AND CAST(TRIM(gestion) AS INT) = 202503
     AND (flg_bpe = 1 OR nom_lista LIKE '%bpe%')
     AND (CAMPANIA IN ('NUEVOS AP', 'ECOSISTEMAS', 'NUEVOS PRE') 
                  OR lower(nom_lista) like '%dig%' 
                  OR lower(nom_lista) like '%dad%' 
                  OR lower(nom_lista) like '%nap%' --NAP
                  OR lower(nom_lista) like '%npre%' --npre
                  OR lower(nom_lista) like '%pricing%' ---pricing
                  OR lower(nom_lista) like '%pil%' ---pil
                  OR lower(nom_lista) like '%izi%' 
                  OR lower(nom_lista) like '%_im%'
                  OR lower(nom_lista) like '%_cd%'
        )
    GROUP BY gestion, 
        num_ruc_autocompletado
),
TMP_VPCONECT_CRUZE AS (
SELECT SUBSTRING(REPLACE(cast(date_add('month', -1, cast(date_parse(periodo_val,'%Y%m') as date)) as varchar), '-', ''), 1, 6) periodo_ejecucion, 
              periodo_val periodo_campania, 
              numruc_val num_ruc,
              MAX(CASE WHEN LOWER(campanha) like '%impulso%' AND LOWER(campanha) like '%comercial%'  THEN 1 ELSE 0 END) flg_impulso_comercial,
              MAX(CASE WHEN LOWER(campanha) like '%impulso%' AND LOWER(campanha) not like '%comercial%'  THEN 1 ELSE 0 END) flg_impulso_otro,
              MAX(CASE WHEN LOWER(campanha) like '%nuevo ap%' AND LOWER(campanha) like '%en%' AND LOWER(campanha) not like '%impulso%'  THEN 1 ELSE 0 END) flg_nuevo_ap_en,
              MAX(CASE WHEN LOWER(campanha) like '%nuevo ap%' AND LOWER(campanha) like '%linea%' THEN 1 ELSE 0 END) flg_nuevo_ap_linea,
              MAX(CASE WHEN LOWER(campanha) like '%nuevo ap%' AND LOWER(campanha) like '%cd%' AND LOWER(campanha) like '%agil%'  THEN 1 ELSE 0 END) flg_compra_deuda_agil,
              MAX(CASE WHEN LOWER(campanha) like '%nuevo ap%' AND LOWER(campanha) not like '%cd%' AND LOWER(campanha) like '%agil%'  THEN 1 ELSE 0 END) flg_nuevo_ap_agil,
              MAX(CASE WHEN LOWER(campanha) like '%nuevo ap%' AND LOWER(campanha) like '%cd%' AND LOWER(campanha) not like '%agil%'  THEN 1 ELSE 0 END) flg_compra_deuda,
              MAX(CASE WHEN LOWER(campanha) like '%nuevo pre%' THEN 1 ELSE 0 END) flg_nuevo_pre,
              
              MAX(CASE WHEN LOWER(campanha) LIKE '%impulso%' THEN 1 ELSE 0 END) flg_impulso_general,
              MAX(CASE WHEN LOWER(campanha) LIKE '%nuevo ap%' AND LOWER(campanha) NOT LIKE '%impulso%' THEN 1 ELSE 0 END) flg_nuevo_ap_general,
              MAX(CASE WHEN LOWER(campanha) LIKE '%nuevo pre%' THEN 1 ELSE 0 END) flg_nuevo_pre_general,
              
              max(CASE WHEN gestion_tipo IS NOT NULL AND gestion_tipo NOT LIKE '' THEN 1 ELSE 0 END) flg_gestionado,
              max(CASE WHEN gestion_tipo IS NOT NULL AND gestion_tipo NOT LIKE '' AND lower(gestion_tipo) NOT LIKE '%sin contacto%' THEN 1 ELSE 0 END) flg_ce,
              
              max(CASE WHEN (lower(gestion_tipo) LIKE '%acepta%' 
                  AND lower(gestion_tipo) NOT LIKE '%no%' AND lower(gestion_tipo) NOT LIKE '%RT%' )
                  OR  lower(gestion_tipo) LIKE '%calif%' OR  lower(gestion_tipo) LIKE '%desis%'
                  THEN 1 ELSE 0 END) flg_acepta_vpconnect,
              max(CASE WHEN (lower(gestion_tipo) LIKE '%acepta%' 
                  AND lower(gestion_tipo) NOT LIKE '%no%' AND lower(gestion_tipo) NOT LIKE '%RT%' )
                  THEN 1 ELSE 0 END) flg_acepta_vpconnect_ant,
              max(COALESCE(D.flg_gestionado, 0)) flg_gestionado_feedback,
              max(COALESCE(D.flg_ce, 0)) flg_ce_feedback,
              MAX(COALESCE(D.flg_nuevo_ap_feeedback, 0)) flg_nuevo_ap_feeedback,
              MAX(COALESCE(D.flg_nuevo_pre_feeedback, 0)) flg_nuevo_pre_feeedback
        FROM e_perm_aws.t_vpc_neg_empresas_vig C
        LEFT JOIN TMP_FEEDBACK D 
        ON C.periodo_val = D.periodo_campania
        AND C.numruc_val=D.num_ruc
        WHERE 1 = 1
        AND C.numruc_val IS NOT NULL
        AND C.numruc_val NOT LIKE ''
        AND C.numruc_val != '.'
        AND (lower(tipo_campanha_dsc) LIKE '%adq%'OR lower(tipo_campanha_dsc) LIKE '%impulso%')
        AND lower(campanha) NOT LIKE '%recurrente%'
        AND lower(campanha) NOT LIKE '%demo%'
        AND lower(subcampanha) NOT LIKE '%recurrente%'
        AND CAST(TRIM(periodo_val) AS INT) = 202503
        and coalesce(flg_activo, 0) = 1
        GROUP BY periodo_val, 
              numruc_val
), --SELECT * FROM TMP_VPCONECT_CRUZE LIMIT 100
tmp_contacto as (
    select codmes_ejecucion, codmes_campana, num_ruc_hash,
            max(cant_telefonos) num_telefono,
            max(max_prob_ruc) prob,
            max(flg_3m_ce) suma_3m_ce,
            max(universo_ruc) universo,
            max(grupo_contacto) grupo_contacto
    from e_perm_aws.scr_empbtctope
    where codmes_campana = '202503'
    group by codmes_ejecucion, codmes_campana, num_ruc_hash
)
SELECT a.periodo_ejecucion, 
      a.periodo_campania, 
      a.num_ruc,
      
      coalesce(a.flg_acepta_vpconnect_ant, 0 ) flg_acepta_vpconnect_ant,
      coalesce(a.flg_acepta_vpconnect, 0 ) flg_acepta_vpconnect,
      coalesce(b.flg_desembolso_nuevos, 0 ) flg_desembolso_nuevos,
      CASE WHEN a.flg_acepta_vpconnect_ant = 1 THEN 1 
           WHEN b.flg_desembolso_nuevos = 1 THEN 1 
           ELSE 0 END target_acepta_ant_desembolso,
      CASE WHEN a.flg_acepta_vpconnect = 1 THEN 1 
           WHEN b.flg_desembolso_nuevos = 1 THEN 1 
           ELSE 0 END target_acepta_desembolso,
           
      coalesce(a.flg_gestionado, 0 ) flg_gestionado_vpc,
      coalesce(a.flg_gestionado_feedback, 0 ) flg_gestionado_feedback,
      CASE WHEN flg_gestionado = 1 THEN 1 
           WHEN flg_gestionado_feedback = 1 THEN 1 
           ELSE 0 END flg_gestionado_final,
          
      coalesce(a.flg_ce, 0 ) flg_ce_vpc,
      coalesce(a.flg_ce_feedback, 0 ) flg_ce_feedback,
      CASE WHEN flg_ce = 1 THEN 1 
           WHEN flg_ce_feedback = 1 THEN 1 
           ELSE 0 END flg_ce_final,
           
      c.prob,
      c.grupo_contacto,
      
      a.flg_impulso_comercial,
      a.flg_impulso_otro,
      a.flg_nuevo_ap_en,
      a.flg_nuevo_ap_linea,
      a.flg_compra_deuda_agil,
      a.flg_nuevo_ap_agil,
      a.flg_compra_deuda,
      a.flg_nuevo_pre,
      a.flg_impulso_general,
      a.flg_nuevo_ap_general,
      a.flg_nuevo_pre_general,
      
      a.periodo_campania p_periodo
FROM TMP_VPCONECT_CRUZE a
left join disc_comercial.HM_OBM_COLNETA_ACEPTA_V2 B
on a.periodo_campania=b.periodo_desembolso
and a.num_ruc=b.num_ruc
left join tmp_contacto C
on a.periodo_campania=c.codmes_campana
and a.num_ruc=c.num_ruc_hash
)



SELECT periodo_campania, periodo_ejecucion, COUNT(1), COUNT(DISTINCT(num_ruc)), SUM(flg_acepta_vpconnect_ant), SUM(flg_acepta_vpconnect), 
       SUM(flg_ce_vpc), SUM(flg_ce_feedback), SUM(flg_ce_final)
FROM disc_comercial.HM_OBM_BASE_MATRIZ_RUC
GROUP BY periodo_campania, periodo_ejecucion
ORDER BY periodo_campania DESC, periodo_ejecucion DESC  -- 78791 -- 78791


SELECT * FROM disc_comercial.HM_OBM_BASE_MATRIZ_RUC LIMIT 100