{% autoescape off %}
with min_date as (
  select sess.company, min(sess."startTime"::timestamp::date) min_date
  from contact_session sess
  where 1 = 1
{% if activities %}
  and sess."startedWithActivity_id" in ({{ activities }})
{% endif %}
  group by sess.company
)
,weeks as (
SELECT company,
       ROW_NUMBER() OVER(PARTITION BY company ORDER BY d) week_number,
       d as week_start,
       d+6 AS week_end
FROM  (
  SELECT company, generate_series(d1
                        ,d2
                        ,interval '1 week')::date AS d
  FROM  ( SELECT company
               , date_trunc('week', min_date.min_date) d1
               , date_trunc('week', CURRENT_DATE) d2
          from min_date) sub1
  ) sub2
)
, session_info as (
  SELECT sess.id
  , COALESCE(part.id, 0) part_id
  , sess."isMulti"
  , sess.company
  , sess.spot_id
  , sess."startTime"::timestamp::DATE DATE
  , sess."hourCount"
  , sess.consultant_id
  , sess.coordinator_id
  , weeks.week_number
  , CASE WHEN sess."contactAttempts" IS NULL THEN '' ELSE to_char(sess."contactAttempts", '99999') END attempts
  , consultant."lastName" || ' ' ||  consultant."firstName" consultant
  , consultant.agency_id
  , spot.raw->>'City' city
  , spot.raw->>'Region' region
  , spot.raw->>'Business Unit' unit
  , spot.raw->>'outlet legal name' legal_name
  , spot.raw->>'outlet name' outlet_name
  , spot.raw->>'RKA' rka
  , spot.raw->>'Trade Channel Name' channel
  , spot.raw->>'Territory Name' territory
  , spot.customer_number
  , spot.address
  , agency.title agency
  , coordinator."lastName" || ' ' ||  coordinator."firstName" coordinator
  , CASE
       WHEN sess."stopTime" IS null or EXTRACT(YEAR from sess."stopTime") < 2000 THEN 0
       ELSE LEAST(EXTRACT(EPOCH FROM sess."stopTime"-sess."startTime")/3600, coalesce(DATE_PART('hour', "plannedEndTime" - "plannedStartTime" ), sess."hourCount"))
    END total_session_length
, CASE WHEN part.id IS NULL THEN
    CASE
      WHEN sess."stopTime" IS null or EXTRACT(YEAR from sess."stopTime") < 2000  THEN 0
      ELSE LEAST(EXTRACT(EPOCH FROM sess."stopTime"-sess."startTime")/3600, coalesce(DATE_PART('hour', "plannedEndTime" - "plannedStartTime" ), sess."hourCount"))
    END
    ELSE
        case when part."stopTimeStr" is null or part."stopTimeStr" = '' then 0
        ELSE
            (EXTRACT(EPOCH FROM to_timestamp(part."stopTimeStr", 'MM/DD/YYYY HH24:MI:SS'))-
            EXTRACT(EPOCH FROM to_timestamp(part."startTimeStr", 'MM/DD/YYYY HH24:MI:SS')))/3600
        END
    END session_length
  , coalesce(DATE_PART('hour', "plannedEndTime" - "plannedStartTime" ), sess."hourCount") hour_count
  , CASE WHEN part.id IS NULL
       THEN to_timestamp(sess."startTimeStr", 'MM/DD/YYYY HH24:MI:SS')::TIMESTAMP WITHOUT TIME zone
       ELSE to_timestamp(part."startTimeStr", 'MM/DD/YYYY HH24:MI:SS')::TIMESTAMP WITHOUT TIME zone
    END "startTime"
  , CASE WHEN part.id IS NULL
     THEN to_timestamp(sess."stopTimeStr", 'MM/DD/YYYY HH24:MI:SS')::TIMESTAMP WITHOUT TIME zone
     ELSE
        case
            when part."stopTimeStr" is null or part."stopTimeStr" = ''
                then to_timestamp(sess."stopTimeStr", 'MM/DD/YYYY HH24:MI:SS')::TIMESTAMP WITHOUT TIME zone
            ELSE
                to_timestamp(part."stopTimeStr", 'MM/DD/YYYY HH24:MI:SS')::TIMESTAMP WITHOUT TIME zone
        END
    END "stopTime"
FROM contact_session sess
inner join weeks
    on sess."startTime"::timestamp::date >= weeks.week_start
   and sess."startTime"::timestamp::date <= weeks.week_end
left join contact_sessionpart part
ON part.session_id = sess.id
left join points_spot spot
  on spot.id = coalesce(part.spot_id, sess.spot_id)
left join users_user coordinator
   on coordinator.id = sess.coordinator_id
inner join users_user consultant
   on consultant.id = sess.consultant_id
{% if agencies %}
  and consultant.agency_id in ({{ agencies }})
{% endif %}
{% if agency %}
  and consultant.agency_id = {{ agency}}
{% endif %}
left join company_agency agency
   on agency.id = sess.agency_id
where 1 = 1
{% if units %}
  and spot."businessUnit_id" in ({{ units }})
{% endif %}
{% if activities %}
  and sess."startedWithActivity_id" in ({{ activities }})
{% endif %}
  and EXTRACT(YEAR FROM to_timestamp(sess."startTimeStr", 'MM/DD/YYYY HH24:MI:SS')::timestamp without time zone) > 1900
{% if coordinator %}
  and sess.coordinator_id = {{ coordinator }}
{% endif %}
{% if date_start %}
  and sess."plannedStartTime" >= to_date('{{ date_start }}', 'YYYY-MM-DD')
{% endif %}
{% if date_end %}
  and sess."plannedStartTime" <= to_date('{{ date_end}}', 'YYYY-MM-DD')+ INTERVAL '1 day'
{% endif %}
)
select si.id as session
     , max(si.unit) unit
     , max(si.week_number) week_number
     , max(si.region) region
     , max(si.agency) agency
     , max(si.city) city
     , max(si.coordinator) coordinator
     , max(si.consultant) consultant
     , '' s3
     , max(si.date) session_date
     , max(si.hour_count) hour_count
     , max(si.total_session_length)/24 total_sessionlength
     , max(si.session_length)/24 session_length
     , max(si."startTime") start_time
     , max(si."stopTime") stop_time
     , max(si.customer_number) customer_number
     , max(si.legal_name)
     , max(si.outlet_name) outlet_name
     , max(si.address) address
     , max(si.rka) rka
     , max(si.channel) channel
     , max(si.territory) territory
     , max(si.attempts) attempts
     , count(cnt."contactEventId") contacts_count
from session_info si

left join contact_contacteventcontact cnt
   on si.id=cnt.session_id
group by si.id
order by 10, 8, 1 -- session_date, consultant, session_id
{% endautoescape %}