view: content_review {
  derived_table: {
    sql:
      SELECT
        rq.ID as Review_Queue_ID,
        rq.PRODUCT_ID as product_id,
        r.display_upc as display_upc,
        dt_release_schedule_dashboard.artist_name as product_artist_name,
        --LISTAGG(DISTINCT int_prod_vw_release_artists.release_artists, '|RECORD|') WITHIN GROUP (ORDER BY int_prod_vw_release_artists.release_artists) AS product_artist_list,
        r.project_id,
        p.project_name,
        r.not_for_distribution,
        iff(r.release_id is not NULL, r.release_name, 'DELETED PRODUCT') as Product_Name,
        r.format,
        r.sale_start_date,
        r.release_date,
        r.preorder_date,
        r.meta_language,
        r.special_instructions,
        r.deletions,
        r.release_status,
        INITCAP(rq.submission_type) AS SUBMISSION_TYPE,
        rq.CREATED_DATETIME AS REVIEW_SUBMITTED_ON,
        IFF(rq.STATUS = 'new','Ready For Review','Completed')as REVIEW_STATUS,
        IFF(rq.QUEUE_NAME = 'initial','For Review',IFF(rq.QUEUE_NAME = 'escalation','Escalation','Under Investigation')) as QUEUE_NAME,
        cr.REVIEW_COMPLETED_DATETIME AS REVIEW_COMPLETED_ON,
        INITCAP(cr.COMPLETION_TYPE) AS COMPLETION_TYPE,
        rej.note as REJECTION_NOTE,
        app.note as APPROVAL_NOTE,
        rq.move_note,
        concat(oa4.f_name, ' ', oa4.l_name) as Escalated_To,
        i.name as Actual_Reviewer,
        concat(oa3.f_name, ' ', oa3.l_name) as Assigned_Reviewer,
        concat(oa.f_name, ' ', oa.l_name) as Primary_Relationship_Manager,
        concat(oa2.f_name, ' ', oa2.l_name) as Secondary_Relationship_Manager,
        v.VENDOR_ID as ACCOUNT_ID,
        IFF(v.company is NULL OR v.company = '', v.name, v.company) as ACCOUNT_NAME,
        s.SUBACCOUNT_ID as subaccount_id,
        s.subaccount_name as SubAccount_Name,
        v.OWNER as owner,
        st.DISPLAY_NAME AS ACCOUNT_SERVICE_TIER,
        cb.display_name as brand,
        rq.locked_at_datetime as locked_at_datetime,
        rq.locked_until_datetime as locked_until_datetime,
        i2.name as Locked_by,
        dt_release_schedule_dashboard.mktg_blurb,
        dt_release_schedule_dashboard.global_mktg_highlights,
        dt_release_schedule_dashboard.local_mktg_highlights,
        pm.product_manager

        FROM ORCHARD_APP_REPORTING_V2.prod_CONTENT_REVIEW_CONTENT_REVIEW.REVIEW_QUEUE as rq
        LEFT JOIN (
                SELECT
                  USER_ID,
                  REVIEW_QUEUE_ID, 'approval' AS COMPLETION_TYPE,
                  CREATED_DATETIME AS REVIEW_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 REVIEW_COMPLETED_DATETIME
                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 ORCHARD_APP_REPORTING_V2.prod_CONTENT_REVIEW_CONTENT_REVIEW.rejection as rej ON rq.id = rej.review_queue_id AND rej._fivetran_deleted = false
        LEFT JOIN ORCHARD_APP_REPORTING_V2.prod_CONTENT_REVIEW_CONTENT_REVIEW.approval as app ON rq.id = app.review_queue_id AND app._fivetran_deleted = false
        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
        LEFT JOIN facts.prod.identity as i ON cr.user_id = i.id
        LEFT JOIN facts.prod.identity as i2 ON rq.locked_by_user_id = i2.id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users oa ON v.assigned_to = oa.id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users oa2 on oa2.id = v.quarterback_label_manager
        left JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users oa3 on oa3.id = v.assigned_reviewer
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users oa4 ON rq.moved_to_orchadmin_user_id = oa4.id
        left outer join FACTS.prod.COMPANY_BRAND_HAS_LABEL_VENDOR cbv on cbv.vendor_id = v.vendor_id
        left outer join FACTS.prod.COMPANY_BRAND cb on cbv.company_brand_uuid = cb.uuid
        LEFT JOIN INTELLIGENCE.DBT_PROD.RELEASE_SCHEDULE_DASHBOARD  AS dt_release_schedule_dashboard ON r.release_id = dt_release_schedule_dashboard.release_id
        LEFT JOIN intelligence.dbt_prod.artist_services_pm_union AS pm ON NULLIF(CAST(pm.upc AS VARCHAR), '') = NULLIF(CAST(r.display_upc AS VARCHAR), '')
        --LEFT JOIN INTELLIGENCE.DBT_PROD.RELEASE_ARTISTS  AS int_prod_vw_release_artists ON int_prod_vw_release_artists.release_id= dt_release_schedule_dashboard.release_id
        WHERE rq._fivetran_deleted=false
        AND v.vendor_id NOT IN (70948, 35360, 35976,16162, 16160, 25824)
        --AND [REVIEW_SUBMITTED_ON=daterange]
        HAVING rq.STATUS='new' OR cr.COMPLETION_TYPE IS NOT NULL
        ORDER BY REVIEW_SUBMITTED_ON, REVIEW_STATUS ASC
    ;;
  }

  dimension: review_queue_id {
    label: "Review Queue ID"
    view_label: "Review Info"
    type: number
    sql: ${TABLE}.Review_Queue_ID ;;
    value_format: "0"
  }

  dimension: product_id {
    label: "Product ID"
    view_label: "Product"
    type: number
    sql: ${TABLE}.product_id ;;
    value_format: "0"
  }

  dimension: product_link_completed {
    label : "Product Link - Completed"
    view_label: "Product"
    type: string
    sql: ${TABLE}.product_id ;;
    html:
    {% assign link = value %}
    <a href="https://oa.theorchard.com/cont_mgmt/view_release.php?release_id={{link}}"><u>Product Link</u></a>
    ;;
  }

  dimension: product_link_inprogress {
    label : "Product Link - In Progress"
    view_label: "Product"
    type: string
    sql: ${TABLE}.product_id ;;
    html:
    {% assign link = value %}
    <a href="https://oa.theorchard.com/cont_mgmt/edit_ex_release.php?release_id={{link}}"><u>Product Link</u></a>
    ;;
  }

  dimension: display_upc {
    label: "Display UPC"
    view_label: "Product"
    type: number
    sql: ${TABLE}.display_upc ;;
    value_format: "0"
  }

  dimension: product_artist_name {
    label: "Product Artist Name"
    view_label: "Product"
    type: string
    sql: ${TABLE}.product_artist_name ;;
  }

  dimension: project_id {
    label: "Project ID"
    view_label: "Product"
    type: number
    sql: ${TABLE}.project_id ;;
    value_format: "0"
  }

  dimension: project_name {
    label: "Project Name"
    view_label: "Product"
    type: string
    sql: ${TABLE}.project_name ;;
  }

  dimension: not_for_distribution {
    label: "Not For Distribution Status"
    view_label: "Product"
    type: string
    sql: ${TABLE}.not_for_distribution ;;
  }

  dimension: product_Name {
    label: "Product Name"
    view_label: "Product"
    type: string
    sql: ${TABLE}.Product_Name ;;
  }

  dimension: product_format {
    label: "Product Format"
    view_label: "Product"
    type: string
    sql: ${TABLE}.Format ;;
  }

  dimension_group: sale_start_date {
    label: "Sales Start Date"
    view_label: "Product"
    type: time
    sql: ${TABLE}.sale_start_date ;;
    timeframes: [
      year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num,
    ]
  }

  dimension_group: release_date {
    label: "Original Release Date"
    view_label: "Product"
    type: time
    sql: ${TABLE}.release_date ;;
    timeframes: [
      year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num,
    ]
  }

  dimension_group: preorder_date {
    label: "Pre-Order Date"
    view_label: "Product"
    type: time
    sql: ${TABLE}.preorder_date ;;
    timeframes: [
      year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num,
    ]
  }

  dimension: meta_language {
    label: "Metadata Language"
    view_label: "Product"
    type: string
    sql: ${TABLE}.meta_language ;;
  }

  dimension: special_instructions {
    label: "Special Instructions"
    view_label: "Product"
    type: string
    sql: ${TABLE}.special_instructions ;;
    html:
    {% assign blurb = value %}
    {% unless blurb.size == 0 %}
    <details>
    <summary>Expand</summary>
    {{blurb}}
    </details>
    {% endunless %};;
  }

  dimension: deletions {
    label: "Is The Product Deleted (Y/N)"
    view_label: "Product"
    type: string
    sql: ${TABLE}.deletions ;;
  }

  dimension: release_status {
    label: "Product Status"
    view_label: "Product"
    type: string
    sql: ${TABLE}.release_status ;;
  }

  dimension: submission_type {
    label: "Submission Type"
    view_label: "Review Info"
    type: string
    sql: ${TABLE}.submission_type ;;
  }

  dimension_group: review_submitted_on {
    view_label: "Review Info"
    label: "Review Submitted Date"
    type: time
    sql: ${TABLE}.REVIEW_SUBMITTED_ON ;;
    timeframes: [
      year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num,
    ]
  }

  dimension: review_status {
    label: "Review Status"
    view_label: "Review Info"
    type: string
    sql: ${TABLE}.REVIEW_STATUS ;;
  }

  dimension: queue_name {
    label: "Queue Name"
    view_label: "Review Info"
    type: string
    sql: ${TABLE}.QUEUE_NAME ;;
  }

  dimension_group: review_completed_on {
    view_label: "Review Info"
    label: "Review Completed Date"
    type: time
    sql: ${TABLE}.REVIEW_COMPLETED_ON ;;
    timeframes: [
      year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num,
    ]
  }

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

  dimension: rejection_note {
    label: "Rejection Note"
    view_label: "Review Info"
    type: string
    sql: ${TABLE}.REJECTION_NOTE;;
    html:
    {% assign blurb = value %}
    {% unless blurb.size == 0 %}
    <details>
    <summary>Expand</summary>
    {{blurb}}
    </details>
    {% endunless %};;
  }

  dimension: approval_note {
    label: "Approval Note"
    view_label: "Review Info"
    type: string
    sql: ${TABLE}.APPROVAL_NOTE ;;
    html:
    {% assign blurb = value %}
    {% unless blurb.size == 0 %}
    <details>
    <summary>Expand</summary>
    {{blurb}}
    </details>
    {% endunless %};;
  }

  dimension: move_note {
    label: "Move Note"
    view_label: "Review Info"
    type: string
    sql: ${TABLE}.move_note;;
    html:
    {% assign blurb = value %}
    {% unless blurb.size == 0 %}
    <details>
    <summary>Expand</summary>
    {{blurb}}
    </details>
    {% endunless %};;
  }

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

  dimension: actual_reviewer {
    label: "Actual Reviewer"
    view_label: "Review Info"
    type: string
    sql: ${TABLE}.Actual_Reviewer ;;
  }

  dimension: assigned_reviewer {
    label: "Assigned Reviewer"
    view_label: "Account"
    type: string
    sql: ${TABLE}.Assigned_Reviewer ;;
  }

  dimension: primary_relationship_manager {
    label: "Primary Relationship Manager"
    view_label: "Account"
    type: string
    sql: ${TABLE}.Primary_Relationship_Manager ;;
  }

  dimension: secondary_relationship_manager {
    label: "Secondary Relationship Manager"
    view_label: "Account"
    type: string
    sql: ${TABLE}.Secondary_Relationship_Manager ;;
  }

  dimension: account_id {
    label: "Account ID"
    view_label: "Account"
    type: number
    sql: ${TABLE}.ACCOUNT_ID ;;
    value_format: "0"
  }

  dimension: account_name {
    label: "Account Name"
    view_label: "Account"
    type: string
    sql: ${TABLE}.ACCOUNT_NAME ;;
  }

  dimension: sub_account_id {
    label: "Sub-Account ID"
    view_label: "Account"
    type: number
    sql: ${TABLE}.subaccount_id ;;
    value_format: "0"
  }

  dimension: sub_account_name {
    label: "Sub-Account Name"
    view_label: "Account"
    type: string
    sql: ${TABLE}.SubAccount_Name ;;
  }

  dimension: owner {
    label: "Owner"
    view_label: "Account"
    type: string
    sql: ${TABLE}.owner ;;
  }

  dimension: account_service_tier{
    label: "Account Service Tier"
    view_label: "Account"
    type: string
    sql: ${TABLE}.ACCOUNT_SERVICE_TIER ;;
  }

  dimension: brand {
    label: "Brand"
    view_label: "Account"
    type: string
    sql: ${TABLE}.brand ;;
  }

  dimension_group: locked_at_datetime {
    view_label: "Review Info"
    label: "Locked At Date"
    type: time
    sql: ${TABLE}.locked_at_datetime ;;
    timeframes: [
      year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num,
    ]
  }

  dimension_group: locked_until_datetime {
    view_label: "Review Info"
    label: "Locked Until Date"
    type: time
    sql: ${TABLE}.locked_until_datetime ;;
    timeframes: [
      year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num,
    ]
  }

  dimension: locked_by {
    label: "Locked By"
    view_label: "Review Info"
    type: string
    sql: ${TABLE}.Locked_by ;;
  }

  dimension: mktg_blurb {
    label: "Marketing Blurb"
    view_label: "Product"
    type: string
    sql: ${TABLE}.mktg_blurb;;
    html:
    {% assign blurb = value %}
    {% unless blurb.size == 0 %}
    <details>
    <summary>Expand</summary>
    {{blurb}}
    </details>
    {% endunless %};;
  }
  dimension: global_mktg_highlights {
    label: "Global Marketing Highlights"
    view_label: "Product"
    type: string
    sql: ${TABLE}.global_mktg_highlights;;
    html:
    {% assign blurb = value %}
    {% unless blurb.size == 0 %}
    <details>
    <summary>Expand</summary>
    {{blurb}}
    </details>
    {% endunless %};;
  }
  dimension: local_mktg_highlights {
    label: "Local Marketing Highlights"
    view_label: "Product"
    type: string
    sql: ${TABLE}.local_mktg_highlights;;
    html:
    {% assign blurb = value %}
    {% unless blurb.size == 0 %}
    <details>
    <summary>Expand</summary>
    {{blurb}}
    </details>
    {% endunless %};;
  }

  dimension: product_manager {
    label: "Product Manager"
    view_label: "Product"
    type: string
    sql: ${TABLE}.product_manager ;;
  }

  }