view: review_session_history {
 derived_table: {
   sql:
 with full_names as (
    SELECT id,
        concat(f_name, ' ', l_name) as full_name
    FROM orchard_app_reporting_v2.art_relations_PROD_art_relations.orchadmin_users
),

      reviews_submitted as (
      SELECT rq.created_datetime,
      CASE WHEN DAYOFWEEKISO(rq.created_datetime) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', rq.created_datetime)))  -- Sat → Fri 12pm
      WHEN DAYOFWEEKISO(rq.created_datetime) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', rq.created_datetime)))  -- Sun → Fri 12pm
      ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', rq.created_datetime))   -- Weekday → 12pm same day
      END AS adj_created,
      rq.id as review_id,
      rq.status,
      rq.queue_name,
      rq.product_id,
      rq.locked_by_user_id,
      rq.locked_at_datetime,
      rq.locked_until_datetime,
      rq.moved_to_queue_by_user_id,
      rq.moved_to_target_id,
      rq.move_note,
      mt.display_name as escalation_reason,
      rq.moved_at_datetime,
      CASE WHEN DAYOFWEEKISO(rq.moved_at_datetime) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', rq.moved_at_datetime)))  -- Sat → Fri 12pm
      WHEN DAYOFWEEKISO(rq.moved_at_datetime) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', rq.moved_at_datetime)))  -- Sun → Fri 12pm
      ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', rq.moved_at_datetime))    -- Weekday → 12pm same day
      END AS adj_moved,
      rq.submission_type,
      rq.submission_count,
      rq.submitted_by_user_id,
      r.upc,
      r.release_name,
      r.special_instructions,
      v.vendor_id as label_id,
      coalesce(NULLIF(v.name,''),NULLIF(v.company,'')) as label_name,
      v.assigned_reviewer as assigned_reviewer_id,
      ar.full_name as assigned_reviewer,
      v.owner,
      v.assigned_to,
      iff(r.release_id is not NULL, r.release_name, 'DELETED PRODUCT') as product_name,
      a.name as product_primary_artist,
      g.genre,
      s.subaccount_name,
      s.subaccount_id,
      st.display_name as account_service_tier,
      lm.full_name as relationship_manager,
      slm.full_name as secondary_relationship_manager
      -- atg.brand_name,

      FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".REVIEW_QUEUE rq
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".RELEASES r
      ON rq.PRODUCT_ID = r.RELEASE_ID
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".PROJECT p
      ON r.PROJECT_ID = p.PROJECT_ID
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".VENDOR v
      ON p.VENDOR_ID = v.VENDOR_ID
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.genre g
      ON r.genre_id = g.genre_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_PROD_art_relations.artist_info a
      ON r.artist_id = a.artist_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_PROD_art_relations.subaccount s
      ON r.subaccount_id = s.subaccount_id
      LEFT JOIN "FACTS"."PROD".VENDOR_IN_SERVICE_TIER_SERVICE_TIER vst
      ON v.vendor_id = vst.vendor_id
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".move_target mt
      ON mt.ID = rq.MOVED_TO_TARGET_ID
      LEFT JOIN "FACTS"."PROD".SERVICE_TIER st
      ON st.uuid = vst.service_tier_uuid
      LEFT JOIN full_names lm
      ON v.assigned_to = lm.id
      LEFT JOIN full_names slm
      ON v.quarterback_label_manager = slm.id
      LEFT JOIN full_names ar
      ON v.assigned_reviewer = ar.id
      LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  atg
      ON v.vendor_id = atg.labelid
      WHERE rq._fivetran_deleted = false
      AND (v.label_identifier != 'Test' OR v.label_identifier IS NULL)
      AND v.assigned_reviewer IN (2903,3754,3753)
      -- 2903 CR, 3753 India, 3754 Vietnam
      AND v.vendor_id NOT IN (70948, 35360, 35976,16162, 16160)
      AND rq.queue_name NOT IN ('integrations')
      ),

      completed_reviews as (
      SELECT
      rs.*,
      ar.user_id as completed_by_id,
      CASE WHEN rs.status = 'complete' AND (rs.moved_at_datetime IS NULL AND ar.completed_datetime IS NULL) THEN 'unsubmitted' ELSE ar.completion_type END as completion_type,
      ar.completed_datetime,
      CASE WHEN DAYOFWEEKISO(ar.completed_datetime) = 6 THEN DATEADD(hour, 12, DATEADD(day, -1, DATE_TRUNC('DAY', ar.completed_datetime)))
                 WHEN DAYOFWEEKISO(ar.completed_datetime) = 7 THEN DATEADD(hour, 12, DATEADD(day, -2, DATE_TRUNC('DAY', ar.completed_datetime)))
                 ELSE DATEADD(hour, 12, DATE_TRUNC('DAY', ar.completed_datetime))
        END AS adj_completed,
      i.name as completed_by,
      dr.completion_reason,
      dr.completion_reason_agg,
      vh.validation_code,
      listagg(distinct vh.validation_code, ', ') as all_validation_codes,
      FROM reviews_submitted rs
      INNER JOIN (
          SELECT USER_ID,
          REVIEW_QUEUE_ID,
          'approval' AS COMPLETION_TYPE,
          CREATED_DATETIME AS completed_datetime,
          FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".APPROVAL
          WHERE _fivetran_deleted=false
          UNION ALL
          SELECT USER_ID,
          REVIEW_QUEUE_ID,
          'rejection' AS COMPLETION_TYPE,
          CREATED_DATETIME AS completed_datetime,
          FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".REJECTION
          WHERE _fivetran_deleted=false) ar
            ON ar.REVIEW_QUEUE_ID = rs.review_ID
      LEFT JOIN facts.PROD.identity i
            ON ar.user_id = i.id and i.id = rs.locked_by_user_id
      LEFT JOIN (
          SELECT
          his.review_queue_id,
          res.keyword AS completion_reason,
          LISTAGG(distinct res.keyword, ', ')
          WITHIN GROUP (ORDER BY res.keyword)
          OVER (PARTITION BY his.review_queue_id) AS completion_reason_agg
          FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".canned_response res
          LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".canned_response_category cat
          ON res.category_ID = cat.ID
          LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".canned_response_history his
          ON his.CANNED_RESPONSE_ID = res.id
      ) dr
        ON dr.review_queue_id = rs.review_id
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".review_validation_history vh
        ON vh.review_queue_id = rs.review_id
      WHERE i.name NOT IN ('Conrad Capalbo','Jay Fidlow','Gayae Manukyan','mbalan@theorchard.com','Malini Balan','piannone@theorchard.com','James Dooley','Troy Denkinger','Stefan Duberg','Gregory Tavarez','ingride ngaku','Arushi Jaiswal','Tristan Morris','Khannah Bint Saliym','Valentina Gilly','Joseph Oladimeji','Benjamin Kao')
      AND i.id NOT IN ('794821a1-048b-4f3d-93dd-213a46362e0a')
      group by all
      )

          select distinct cs.review_queue_id,
          -- cs.created_datetime,
          cs.session_open,
          cs.session_close,
          cs.session_duration_min,
          cs.session_progress,
          cs.actioned_by,
          -- listagg(cs.session_progress,', ') within group (order by cs.session_open) over (partition by cs.review_queue_id) as full_progress,
          -- min(case when session_progress LIKE '%Change Queue To Escalation%' OR session_progress LIKE '%Change Queue To Under_Investigation%' THEN session_close END) OVER (partition by cs.review_queue_id) as escalation_session,
          -- max(case when session_progress LIKE '%Complete Review%' THEN session_close END) OVER (partition by cs.review_queue_id) as completion_session,
          -- cs.completion_type,
          from (SELECT
          rmd.review_queue_id,
          rmd.user_id as actioned_by_id,
          coalesce(i.name,fn.full_name) as actioned_by,
          cr.created_datetime,
          CONVERT_TIMEZONE('UTC','America/New_York',DATE_TRUNC('second', TO_TIMESTAMP(rmd.SESSION_START_TIME / 1000))) AS session_open,
          CONVERT_TIMEZONE('UTC','America/New_York',DATE_TRUNC('second', TO_TIMESTAMP(rmd.SESSION_END_TIME / 1000))) AS session_close,
          round(rmd.total_duration_seconds/60,2) as session_duration_min,
          initcap(CASE WHEN label IS NULL THEN 'Closed Window' ELSE label END) AS session_progress,
          cr.completion_type,
          FROM SEGMENT_EVENTS.PROD_FRONTEND_CONTENT.REVIEW_MODE_DURATION rmd
          inner join completed_reviews cr
          on cr.review_id = rmd.review_queue_id
          left JOIN facts.PROD.identity i
          ON rmd.user_id = i.id
          left join full_names fn
          on rmd.user_id = to_varchar(fn.id)
          WHERE round(rmd.total_duration_seconds/60,2) > 0
          and review_queue_id IN (
          SELECT DISTINCT review_queue_id,
          FROM SEGMENT_EVENTS.PROD_FRONTEND_CONTENT.REVIEW_MODE_DURATION
          WHERE label LIKE '%Complete Review%')
          and completion_type not in ('unsubmitted')
          ORDER BY 1,2) cs
          -- group by 1,2,3,4,5,6,7
          order by 1,2
  ;;
 }

  dimension: review_queue_id{
    label: "Review ID"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.review_queue_id ;;
  }

  dimension: actioned_by{
    label: "Actioned By"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.actioned_by ;;
  }

  dimension_group: session_open {
    label: "Session Open"
    view_label: "Reviews"
    type: time
    timeframes: [
      day_of_week,
      date,
      week,
      month,
      quarter,
      year,
      time
    ]
    sql: ${TABLE}.session_open;;
  }

  dimension_group: session_close {
    label: "Session Close"
    view_label: "Reviews"
    type: time
    timeframes: [
      day_of_week,
      date,
      week,
      month,
      quarter,
      year,
      time
    ]
    sql: ${TABLE}.session_close;;
  }

  measure: session_duration_min{
    label: "Session Duration (min)"
    view_label: "Reviews"
    type: sum
    sql: ${TABLE}.session_duration_min;;
  }

  dimension: session_progress{
    label: "Session Progress"
    view_label: "Reviews"
    type: string
    sql: ${TABLE}.session_progress ;;
  }
  }