view: potential_audio_infringement_audit_history {
  derived_table: {
    sql:
    WITH cr_completed_ignore_unsubmitted AS (
      SELECT
        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,
        st.DISPLAY_NAME AS ACCOUNT_SERVICE_TIER,
        rq.locked_at_datetime,
        rq.locked_until_datetime,
        rq.locked_by_user_id,
        r.sale_start_date,
        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
        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
        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.PRODUCT_ID as product_id,
  r.display_upc as display_upc,
  r.release_name as product_name,
  lp.name as product_primary_artists,
  ARRAY_TO_STRING(f.value:artists, ', ') as potentially_infringed_artists,
  rvh.Track_id,
  t.isrc as track_isrc,
  t.track_name as track_name,
  CAST(f.value:title AS STRING) as potentially_infringed_track_name,
  DATE(cr.REVIEW_COMPLETED_DATETIME) as review_completed_date,
  concat(o.f_name, ' ', o.l_name) as relationship_manager,
  concat(o2.f_name, ' ', o2.l_name) as secondary_relationship_manager,
  concat(o3.f_name, ' ', o3.l_name) as assigned_reviewer,
  cr.vendor_ID as account_id,
  IFF(v.company is NULL OR v.company = '', v.name, v.company) as account_name,
  CAST(f.value:label AS STRING) as potentially_infringed_label,
  cr.Owner as owner,
  cb.display_name as brand,
  st.display_name as account_service_tier,
  cr.id as review_queue_id,
  INITCAP(i.NAME) as actual_reviewer,
  cr.COMPLETION_TYPE as completion_type,
  app.note as approval_note,
  rej.note as rejection_note
FROM cr_completed_ignore_unsubmitted as cr
JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".review_validation_history rvh on cr.id = rvh.review_queue_id
LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".rejection rej on cr.id = rej.review_queue_id
LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".approval app on cr.id = app.review_queue_id
LEFT JOIN "ORCHARD_APP_REPORTING_V2"."ART_RELATIONS_PROD_ART_RELATIONS".RELEASES r ON CR.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.orchadmin_users o ON v.assigned_to = o.id
LEFT JOIN orchard_app_reporting_v2.art_relations_PROD_art_relations.orchadmin_users o2 ON v.quarterback_label_manager = o2.id
LEFT JOIN orchard_app_reporting_v2.art_relations_PROD_art_relations.orchadmin_users o3 ON v.assigned_reviewer = o3.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"."ART_RELATIONS_PROD_ART_RELATIONS".track t ON t.id=rvh.track_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 outer join FACTS.PROD.VENDOR_IN_SERVICE_TIER_SERVICE_TIER vst on vst.vendor_id = v.vendor_id
LEFT JOIN "FACTS"."PROD".SERVICE_TIER st ON st.uuid = vst.service_tier_uuid
LEFT JOIN "FACTS"."PROD".IDENTITY as i ON i.ID=cr.USER_ID
LEFT JOIN (
    SELECT lpi.product_id, listagg(lp1.name, ', ') as name
    FROM "FACTS"."PROD".LABEL_PARTICIPANT_PARTICIPATED_IN_ORCHARD_PRODUCT lpi
    LEFT JOIN "FACTS"."PROD".LABEL_PARTICIPANT lp1 ON lp1.id = lpi.label_participant_id
    WHERE lpi.participated_as = 'performer'
    GROUP BY lpi.product_id
) as lp ON cr.product_id = lp.product_id
LEFT JOIN "ORCHARD_APP_REPORTING_V2"."PROD_CONTENT_REVIEW_CONTENT_REVIEW".review_validation_history rvh2 on rvh.id = rvh2.id,
lateral flatten(input => rvh2.details, path => 'matches', outer => TRUE) f
WHERE lower(rvh.validation_code) = 'potential_audio_infringement';;
}

dimension: product_id {
  label: "Product ID"
  type: string
  sql: ${TABLE}.product_id ;;
}

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

dimension: release_name {
  label: "Product Name"
  type: string
  sql: ${TABLE}.product_name ;;
}

dimension: potentially_infringed_artists{
  label: "Potentially Infringed Artist(s)"
  type: string
  sql: ${TABLE}.potentially_infringed_artists ;;
}

dimension: product_primary_artists{
  label: "Product Primary Artist(s)"
  type: string
  sql: ${TABLE}.potentially_infringed_artists ;;
}

dimension: track_id{
  label: "Track ID"
  type: string
  sql: ${TABLE}.track_id ;;
}

dimension: isrc{
  label: "ISRC"
  type: string
  sql: ${TABLE}.track_isrc ;;
}

dimension: track_name{
  label: "Track Name"
  type: string
  sql: ${TABLE}.track_name ;;
}

dimension: potentially_infringed_track_name{
  label: "Potentially Infringed Track Name"
  type: string
  sql: ${TABLE}.potentially_infringed_track_name ;;
}

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

dimension: relationship_manager{
  label: "Relationship Manager"
  type: string
  sql: ${TABLE}.relationship_manager ;;
}

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

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

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

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

dimension: potentially_infringed_label{
  label: "Potentially Infringed Label"
  type: string
  sql: ${TABLE}.potentially_infringed_label ;;
}

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

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

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

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

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

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

dimension: approval_note{
  label: "Approval Note"
  type: string
  sql: ${TABLE}.approval_note ;;
}

dimension: rejection_note{
  label: "Rejection Note"
  type: string
  sql: ${TABLE}.rejection_note ;;
}
}