DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_GESTION_VPCONNECT_CAMPANIA_ADQ
   CREATE TABLE d_mdl_vpc_disc.HM_GESTION_VPCONNECT_CAMPANIA_ADQ
   WITH ( format = 'Parquet', 
       parquet_compression = 'SNAPPY', 
       partitioned_by = ARRAY['p_periodo'],
       external_location= 's3://sagemaker-us-east-1-058528764918/vpc/contactabilidad/athena_2/HM_GESTION_VPCONNECT_CAMPANIA_ADQ/'
      )
    AS (
        SELECT Z.* 
        FROM(
           SELECT periodo_campania, 
                   CASE WHEN campania LIKE '%Nuevo Ap%' THEN 'NUEVOS AP'
                        WHEN campania LIKE '%Nuevo Pre%' THEN 'NUEVOS PRE'
                        WHEN campania LIKE '%Eco%' THEN 'ECOSISTEMAS'
                        ELSE 'X' END campania,
                    canal,
                    num_ruc,
                    gestion_tipo,
                    gestion_subtipo,
                    gestion_comentario,
                    fecha_gestion_dt_2,
                    flg_gestion_vpconnect,
                    flg_ce_vpconnect,
                    flg_acepta_vpconnect,
                    flg_no_acepta_vpconnect,
                    flg_lo_pensara_vpconnect,
                    flg_no_califica_vpconnect,
                    flg_desiste_vpconnect,
                    ROW_NUMBER() OVER(PARTITION BY periodo_campania, 
                                         CASE WHEN campania LIKE '%Nuevo Ap%' THEN 'NUEVOS AP'
                                              WHEN campania LIKE '%Nuevo Pre%' THEN 'NUEVOS PRE'
                                              WHEN campania LIKE '%Eco%' THEN 'ECOSISTEMAS'
                                              ELSE 'X' END, num_ruc, fecha_gestion_dt_2 
                                      ORDER BY CASE WHEN gestion_tipo = 'Acepta Campaña' then 1
                                               WHEN gestion_tipo = 'Lo Pensará' then 2
                                               WHEN gestion_tipo = 'Enviando a GTP' then 1
                                               WHEN gestion_tipo = 'No Acepta Campaña' then 4
                                               WHEN gestion_tipo = 'No Califica' then 5
                                               WHEN gestion_tipo = 'Sin Contacto' then 6
                                               WHEN gestion_tipo = 'Desistió' then 7
                                               WHEN gestion_tipo like '%Acepta Visita%' then 8
                                               ELSE 9 END ASC, fecha_gestion_dt DESC
                   ) ORDEN_DIARIO,
                   CASE WHEN CAST(CONCAT(CAST(EXTRACT(YEAR FROM fecha_gestion_dt_2) AS VARCHAR), LPAD(CAST(EXTRACT(MONTH FROM fecha_gestion_dt_2) AS VARCHAR), 2, '0')) AS INT) < CAST(periodo_campania AS INT) then 1 else 0 end flg_tuberia,
                  CASE WHEN CAST(CONCAT(CAST(EXTRACT(YEAR FROM fecha_gestion_dt_2) AS VARCHAR), LPAD(CAST(EXTRACT(MONTH FROM fecha_gestion_dt_2) AS VARCHAR), 2, '0')) AS INT) < CAST(periodo_campania AS INT) then BB.min_fecha_gestion_dt_2 else fecha_gestion_dt_2 end fecha_gestion_ajustado,
                    periodo_campania p_periodo
            FROM d_mdl_vpc_disc.HM_GESTION_VPCONNECT AA
            LEFT JOIN (
                 SELECT periodo_campania periodo_campania_2, min(fecha_gestion_dt_2) min_fecha_gestion_dt_2
                 FROM d_mdl_vpc_disc.HM_GESTION_VPCONNECT
                 WHERE CAST(CONCAT(CAST(EXTRACT(YEAR FROM fecha_gestion_dt_2) AS VARCHAR), LPAD(CAST(EXTRACT(MONTH FROM fecha_gestion_dt_2) AS VARCHAR), 2, '0')) AS INT) = CAST(periodo_campania AS INT)
                 GROUP BY periodo_campania
            ) BB
            ON AA.periodo_campania = bb.periodo_campania_2
            WHERE 1 = 1
            AND tipo_campania LIKE 'Adq%'
            AND FLG_ACTIVO = 1
            AND gestion_tipo IS NOT NULL
            AND gestion_tipo NOT LIKE ''
        ) Z
        WHERE Z.ORDEN_DIARIO = 1
    )