view: queue_move_history {
  derived_table: {
    sql:
    with cr_completed_reviews_with_vendor_data AS (
    SELECT *
      FROM(
      SELECT
        r.UPC,
        rq.ID,
        rq.PRODUCT_ID,
        rq.CREATED_DATETIME AS REVIEW_CREATED_DATETIME,
        rq.STATUS,
        rq.QUEUE_NAME,
        cr.COMPLETION_TYPE,
        cr.REVIEW_COMPLETED_DATETIME,
        cr.USER_ID,
        v.VENDOR_ID,
        s.SUBACCOUNT_ID,
        v.OWNER,
        v.name as label_name,
        st.DISPLAY_NAME AS ACCOUNT_SERVICE_TIER,
        rq.locked_at_datetime,
        rq.locked_until_datetime,
        rq.locked_by_user_id,
        cr.approval_note,
        rq.submission_type,
        rq.submission_count,
        rq.SUBMITTED_BY_USER_ID
      FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".REVIEW_QUEUE as rq
      INNER JOIN (
        SELECT USER_ID, REVIEW_QUEUE_ID, 'approval' AS COMPLETION_TYPE, CREATED_DATETIME AS REVIEW_COMPLETED_DATETIME, NOTE as APPROVAL_NOTE
        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, NOTE as REJECTION_NOTE
        FROM "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".REJECTION
        WHERE _fivetran_deleted=false
      ) as cr ON rq.ID=cr.REVIEW_QUEUE_ID
      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 p.PROJECT_ID = r.PROJECT_ID
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".VENDOR v ON v.VENDOR_ID=p.VENDOR_ID
      LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".subaccount s ON s.SUBACCOUNT_ID=r.SUBACCOUNT_ID
      LEFT JOIN "FACTS"."PROD".VENDOR_IN_SERVICE_TIER_SERVICE_TIER vst ON v.vendor_id = vst.vendor_id
      LEFT JOIN "FACTS"."PROD".SERVICE_TIER st ON st.uuid = vst.service_tier_uuid
      WHERE rq._fivetran_deleted=false
      AND v.label_identifier != 'Test' OR v.label_identifier IS NULL
      HAVING rq.STATUS='new' OR cr.COMPLETION_TYPE IS NOT NULL
      )
    )

  SELECT
  cr.UPC as upc,
  cr.vendor_id as label_id,
  cr.label_name as label_name,
  cr.REVIEW_CREATED_DATETIME as SUBMISSION_DATE,
  cr.REVIEW_COMPLETED_DATETIME as REVIEW_COMPLETED_DATE,
  cr.COMPLETION_TYPE as completion_type,
  qm.QUEUE_NAME as CURRENT_QUEUE,
  qm.PREVIOUS_QUEUE_NAME as PREVIOUS_QUEUE,
  qm.MOVED_AT_DATETIME as DATE_MOVED,
  qm.MOVED_TO_TARGET_NAME as ESCALATED_TO,
  qm.MOVE_NOTE as move_note
FROM cr_completed_reviews_with_vendor_data as cr
LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".QUEUE_MOVE_HISTORY qm
  ON cr.ID = qm.review_queue_id
WHERE CURRENT_QUEUE != 'on_hold';;
}

  dimension: upc{
    label: "UPC"
    type: string
    sql: ${TABLE}.upc ;;
  }

  dimension: label_id{
    label: "Label ID"
    type: string
    sql: ${TABLE}.label_id ;;
  }

  dimension: label_name{
    label: "Label Name"
    type: string
    sql: ${TABLE}.label_name ;;
  }

  dimension_group: submission_date{
    label: "Submission"
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year,
      time
    ]
    sql: ${TABLE}.submission_date;;
  }

  dimension_group: review_completed_date{
    label: "Review Completed"
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year,
      time
    ]
    sql: ${TABLE}.review_completed_date;;
  }

  dimension: completion_type{
    label: "Completion Type"
    type: string
    sql: ${TABLE}.completion_type ;;
  }

  dimension: current_queue{
    label: "Current Queue"
    type: string
    sql: ${TABLE}.current_queue ;;
  }

  dimension: previous_queue{
    label: "Previous Queue"
    type: string
    sql: ${TABLE}.previous_queue ;;
  }

  dimension_group: date_moved{
    label: "Moved"
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year,
      time
    ]
    sql: ${TABLE}.date_moved;;
  }

  dimension: escalated_to{
    label: "Escalated To"
    type: string
    sql: ${TABLE}.escalated_to ;;
  }

  dimension: move_note{
    label: "Move Note"
    type: string
    sql: ${TABLE}.move_note ;;
    html:
    {% assign blurb = value %}
    {% unless blurb.size == 0 %}
    <details style="margin: 0; padding: 0;">
    <div style="font-family: inherit; white-space: pre-line; margin: 0; padding: 0;">
    {{ blurb | safe }}
    </div>
    </details>
    {% endunless %};;
  }
}