WITH ppac_users AS (
        SELECT
          user_id,
          app_user_id,
          first_name,
          last_name,
          batch_code,
          email,
          phone_number::TEXT,
          signup_advisor_id,
          transfer_confirmed_at::TIMESTAMP,
          onboard_datetime::TIMESTAMP,
          user_created_at::TIMESTAMP,
          call_center_partner_id,
          ARRAY_REMOVE(cohorts, 'new_claims') as cohorts,
          zendesk_id,
          current_step
        FROM dfu_users as users
        LEFT JOIN (
          SELECT id as zendesk_id, external_id
          FROM zendesk.users
        ) as zendesk_users
        ON  users.app_user_id = zendesk_users.external_id
      ),

      df_admins AS (
        SELECT df_admins.*,
         ids.source_id AS app_admin_id,
         pod_ids.source_id AS onboarding_pod_id
        FROM df_admins
        LEFT OUTER JOIN (SELECT identifiable_id, source_id FROM identifiers WHERE source_project = 'pillpack' AND source_class = 'Admin')
            AS ids
          ON df_admins.id = ids.identifiable_id
        LEFT OUTER JOIN (SELECT identifiable_id, source_id FROM identifiers WHERE source_project = 'pillpack' AND source_class = 'OnboardingPod')   AS pod_ids
          ON df_admins.pod_id = pod_ids.identifiable_id
      ),

      ppac_f9_groups AS (--Selects the most recent team for every advisor email out of Five9 Agent States
          SELECT DISTINCT ON (agent_email)
            agent_email,
            agent_group,
            ROW_NUMBER() OVER(ORDER BY agent_email DESC) AS primary_key
          FROM five9_agent_state_logs AS logs
          WHERE agent_group IS NOT NULL AND agent_email IS NOT NULL
          ORDER BY agent_email, timestamp DESC

      ),

      ppac_firsttouch_calls AS (--This selects the 45 day first-touch timestamp for each phone number and their transfer timestamp
        SELECT DISTINCT ON (users.app_user_id)
            users.app_user_id,
            call_logs.id as call_log_id,
            call_logs.number1,
            users.transfer_confirmed_at as transfer_confirmed_date,
            call_timestamp as min_timestamp,
            row_number() OVER(ORDER BY call_timestamp ASC) as primary_key,
            (users.transfer_confirmed_at - call_timestamp)::interval as attribution_window,
            (
              SELECT count(*)
              FROM (
                SELECT *
                FROM generate_series(call_timestamp::timestamp, users.transfer_confirmed_at::timestamp, '1 day') i
              ) as grandtable
              WHERE date_part('dow', i) NOT IN (0)
            ) as count_workdays
        FROM (

              --This selects phone numbers and call timestamps for all calls
              SELECT *, CAST((call_logs.date::TEXT||' ' ||call_logs.time::TEXT) AS TIMESTAMP WITHOUT TIME ZONE) as call_timestamp
              FROM public.five9_call_logs as call_logs
              --FROM ppac_replica_public.f9_call_logs as call_logs
              WHERE call_logs.call_type NOT LIKE 'Manual' --we don't want to attribute anything back to a "Manual" outbound call type

        ) as call_logs
        INNER JOIN (

                -- This selects all users and their transfer timestamps and phone numbers
                SELECT
                  users.phone_number::BIGINT,
                  users.app_user_id,
                  users.transfer_confirmed_at::TIMESTAMP
                FROM public.dfu_users AS users
                WHERE transfer_confirmed_at IS NOT NULL
                GROUP BY 1,2,3

        ) as users ON (users.phone_number = call_logs.number1)
        WHERE (
          ( extract(epoch from (transfer_confirmed_at - call_timestamp)::interval) >= 0) AND --we don't want to include calls occuring after the transfer timestamp
          ( extract(epoch from (transfer_confirmed_at - call_timestamp)::interval) <= (45*(24*60*60)))) --we don't want to include calls further than 45 days after the transfer timestamp
        ORDER BY users.app_user_id, call_timestamp ASC

    )
SELECT
	DATE((ppac_customer_analysis.transfer_confirmed_at )::timestamptz AT TIME ZONE 'America/New_York') AS "transfer_confirmed_at",
	df_admins_advisor.email  AS "agent_email",
	five9_call_logs.campaign  AS "campaign",
	five9_call_logs.call_type  AS "call_type",
	COUNT(DISTINCT (CASE WHEN 'yes' = 'yes'
        THEN five9_call_logs.number1
      ELSE
        0
      END)) AS "count_closes"
FROM ppac_users AS ppac_customer_analysis
LEFT JOIN df_admins AS df_admins_advisor ON ppac_customer_analysis.signup_advisor_id = df_admins_advisor.app_admin_id
LEFT JOIN ppac_f9_groups ON df_admins_advisor.email = LOWER(ppac_f9_groups.agent_email)
LEFT JOIN ppac_firsttouch_calls ON ppac_customer_analysis.app_user_id = ppac_firsttouch_calls.app_user_id
LEFT JOIN public.five9_call_logs  AS five9_call_logs ON ppac_firsttouch_calls.call_log_id::BIGINT = five9_call_logs.id::BIGINT

WHERE ((((ppac_customer_analysis.transfer_confirmed_at ) >= ((SELECT ((DATE_TRUNC('day', CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York') + (-44 || ' day')::INTERVAL) AT TIME ZONE 'America/New_York'))) AND (ppac_customer_analysis.transfer_confirmed_at ) < ((SELECT (((DATE_TRUNC('day', CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York') + (-44 || ' day')::INTERVAL) + (45 || ' day')::INTERVAL) AT TIME ZONE 'America/New_York')))))) AND ((ppac_f9_groups.agent_group  IN ('PP-Morpheus', 'PP-Kronos', 'PP-Apollo', 'PP-Athena', 'PP-Nike', 'PP-Zeus', 'PP-Hercules'))) AND (((five9_call_logs.campaign IS NOT NULL) AND five9_call_logs.campaign <> 'Manual Outbound'))
GROUP BY 1,2,3,4
ORDER BY 1 DESC