view: ownership_eligbility_and_delivery_test{
  derived_table: {
    sql:
      WITH params AS (
        -- Templated filter for Country Code
        SELECT {% parameter country_code_filter %} AS country_code
      ),

      isrc_filter AS (
      -- Templated filter for ISRC list
      -- We use a dummy select to handle the condition logic
      SELECT isrc_val AS isrc
      FROM (
      SELECT 'dummy' as isrc_val
      )
      WHERE {% condition isrc_list_filter %} isrc_val {% endcondition %}
      -- Note: For large lists, users typically enter comma-separated values in the filter
      ),

      country_lookup AS (
      SELECT c.id AS country_id, c.country_code AS country_code
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.country c
      JOIN params p ON p.country_code = c.country_code
      ),

      track_artist_agg AS (
      SELECT
      track_id,
      LISTAGG(name, ', ') AS performer_names
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.track_artist
      WHERE type = 'performer'
      GROUP BY track_id
      ),

      release_territory AS (
      SELECT DISTINCT rtr.release_id
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.release_territory_restriction rtr
      JOIN country_lookup cl ON rtr.country_id = cl.country_id
      ),

      subaccount_territory AS (
      SELECT DISTINCT s.subaccount_royalty_collection_id
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount_royalty_collection_territories s
      JOIN country_lookup cl ON s.subaccount_royalty_collection_territory = cl.country_id
      ),

      base AS (
      SELECT
      v.vendor_id,
      IFF(v.company IS NULL OR v.company = '', v.name, v.company) AS label_name,
      r.label AS imprint,
      r.upc,
      r.release_name,
      ai.name AS artist_name,
      r.release_date,
      tr.isrc,
      tr.id AS tuid,
      tr.track_name,
      tr.version,
      ta.performer_names AS track_artists,
      to_char(r.ingestion_completed, 'YYYY-MM-DD') as ingestion_date,
      IFF(r.deletions = 'Y', 'Deleted', 'Not_Deleted') AS deletion_Status,
      to_char(fd.date_deleted, 'YYYY-MM-DD') as date_deleted,
      mr.is_owner AS flag_MasterRights,
      IFF(v.vendor_id IN (5403,7667,7844,8020,8119,8409,8446,8529,8673,8861,8932,9067,9159,9304,9349,10519,10693,11726,11818,12242,12370,12527,12596,14033,14061,14110,14237,14253,14265,14707,14835,14941,15078,15094,15273,15292,15384,15417,15426,15433,15447,15499,15620,15691,15744,15752,15766,15952,16052,16082,16099,16143,16153,16159,16160,16211,16312,16345,16351,16402,16405,16420,16422,16492,16494,16529,16530,16570,16600,16730,16791,16814,16841,16915,17020,17048,17067,17075,17124,17168,17176,17195,17237,17251,17299,17304,17516,17651,17685,17939,17960,18009,18093,18499,18730,19353,19649,20022,20908,21015,21462,21497,21538,21851,22041,22328,22547,22567,22697,22734,23082,23094,23104,23286,23492,23524,23834,20493,8869,4696), 'Y', 'N') AS Vendor_Blacklist,
      CASE
      WHEN rtr.release_id IS NOT NULL THEN 'N (RTR)'
      WHEN r.subaccount_id IS NULL THEN
      CASE WHEN REGEXP_INSTR(vc.royalty_collection_territory,'(^|,)' || TO_VARCHAR((SELECT MAX(cl.country_id) FROM country_lookup cl)) || '(,|$)' ) > 0 THEN 'Y' ELSE 'N (LTR)' END
      WHEN srct.subaccount_royalty_collection_id IS NOT NULL THEN 'Y'
      ELSE 'N (STR)'
      END AS eligible,
      CASE
      WHEN EXISTS (
      SELECT 1 FROM INTELLIGENCE.KNR.OWNERSHIP_DELIVERY_HISTORY_AGGREGATED d
      JOIN params p ON 1=1
      WHERE d.tuid = tr.id AND d.iso_2 = p.country_code
      ) THEN 'Delivered' ELSE 'Not Delivered'
      END AS delivery_status,
      CASE
      WHEN EXISTS (
      SELECT 1 FROM facts.prod.registry r2
      JOIN params p ON 1=1
      WHERE r2.tuid = tr.id AND r2.territory = p.country_code
      ) THEN 'Yes' ELSE 'No'
      END AS master_registry,
      (SELECT MAX(cl.country_id) FROM country_lookup cl) AS country_id_searched,
      (SELECT MAX(p.country_code) FROM params p) AS country_code_searched
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.track tr
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.track_master_rights mr ON tr.id = mr.track_id
      INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases r ON tr.release_id = r.release_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.full_deletions fd on fd.upc = r.upc
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.subaccount_royalty_collection src ON r.subaccount_id = src.subaccount_id AND src.active = 'Y'
      INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info ai ON r.artist_id = ai.artist_id
      INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor v ON ai.vendor_id = v.vendor_id
      INNER JOIN intelligence.prod.vw_active_vendor_contract vw ON v.vendor_id = vw.vendor_id
      INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor_contract vc ON vw.vendor_contract_id = vc.id
      LEFT JOIN track_artist_agg ta ON tr.id = ta.track_id
      LEFT JOIN release_territory rtr ON r.release_id = rtr.release_id
      LEFT JOIN subaccount_territory srct ON src.subaccount_royalty_collection_id = srct.subaccount_royalty_collection_id
      WHERE ((vc.royalty_collection_commission > 0) OR (vc.royalty_collection_commission = 0 AND IFNULL(vc.royalty_collection_territory,'') != '') OR ((vc.royalty_collection_commission = 0 OR vc.royalty_collection_commission IS NULL) AND v.owner IN ('Altafonte-US-DEF','Altafonte-ESP-DEF','Altafonte- NR','Altafonte-US','Altafonte-ESP')))
      AND (v.label_identifier IS NULL OR v.label_identifier != 'Test')
      AND r.product_type_id = 1
      AND r.not_for_distribution = 'N'
      AND r.release_status = 'in_content'
      AND tr.id IS NOT NULL
      AND tr.track_type = 'music'
      AND NOT (tr.isrc IS NULL OR tr.isrc = '')
      AND TRY_TO_NUMBER(LEFT(tr.p_line, 4)) BETWEEN 1900 AND 2100
      AND COALESCE(tr.length_minute, 0) + COALESCE(tr.length_seconds, 0) > 0
      AND NOT v.owner IN ('AWAL-UK', 'AWAL-US', 'KNR-NL', 'KNR-UK', 'KNR-UKFund', 'knr', 'awal')
      AND tr.isrc IN (SELECT isrc FROM isrc_filter)
      )

      SELECT * FROM base;;
  }

  # --- Filters (Used in the SQL above) ---

  filter: country_code_filter {
    type: string
    description: "Select the country code for the search"
  }

  filter: isrc_list_filter {
    type: string
    description: "Enter the list of ISRCs to filter by"
  }

  # --- Dimensions ---

  dimension: vendor_id { sql: ${TABLE}.vendor_id ;; type: number }
  dimension: label_name { sql: ${TABLE}.label_name ;; type: string }
  dimension: imprint { sql: ${TABLE}.imprint ;; type: string }
  dimension: upc { sql: ${TABLE}.upc ;; type: string }
  dimension: release_name { sql: ${TABLE}.release_name ;; type: string }
  dimension: artist_name { sql: ${TABLE}.artist_name ;; type: string }
  dimension: release_date { sql: ${TABLE}.release_date ;; type: date }
  dimension: isrc { sql: ${TABLE}.isrc ;; type: string }
  dimension: tuid { sql: ${TABLE}.tuid ;; type: number }
  dimension: track_name { sql: ${TABLE}.track_name ;; type: string }
  dimension: version { sql: ${TABLE}.version ;; type: string }
  dimension: track_artists { sql: ${TABLE}.track_artists ;; type: string }
  dimension: ingestion_date { sql: ${TABLE}.ingestion_date ;; type: date }
  dimension: deletion_status { sql: ${TABLE}.deletion_status ;; type: string }
  dimension: date_deleted { sql: ${TABLE}.date_deleted ;; type: date }
  dimension: flag_MasterRights { sql: ${TABLE}.flag_MasterRights ;; type: string }
  dimension: Vendor_Blacklist { sql: ${TABLE}.Vendor_Blacklist ;; type: string }
  dimension: eligible { sql: ${TABLE}.eligible ;; type: string }
  dimension: delivery_status { sql: ${TABLE}.delivery_status ;; type: string }
  dimension: master_registry { sql: ${TABLE}.master_registry ;; type: string }
  dimension: country_id_searched { sql: ${TABLE}.country_id_searched ;; type: number }
  dimension: country_code_searched { sql: ${TABLE}.country_code_searched ;; type: string }
}