view: dt_registry_territory_match {
 derived_table: {
   sql:
  WITH registry AS (
    SELECT
        isrc,
        LISTAGG(DISTINCT territory, ',') WITHIN GROUP (ORDER BY territory) AS territory_list
    FROM facts.prod.REGISTRY
    GROUP BY isrc
),

asset_report AS (
    SELECT
        CASE
            WHEN yt_asset_report.isrc IS NULL AND yt_asset_report.custom_id IS NOT NULL
                THEN SPLIT_PART(yt_asset_report.custom_id, '_', 2)
            ELSE yt_asset_report.isrc
        END AS isrc,
        yt_asset_report.asset_id,
        LISTAGG(DISTINCT yt_asset_report.ownership, ',')
            WITHIN GROUP (ORDER BY yt_asset_report.ownership) AS territory_list
    FROM facts.prod.staging_raw_youtube_asset_report yt_asset_report
    WHERE yt_asset_report.asset_type = 'SOUND_RECORDING'
      AND COALESCE(yt_asset_report.isrc, SPLIT_PART(yt_asset_report.custom_id, '_', 2)) IS NOT NULL
      AND SPLIT_PART(yt_asset_report.filename, '.', 2) IN ('ORCH', 'IODA', 'ENT')
    GROUP BY 1,2
),

joined AS (
    SELECT
        COALESCE(r.isrc, ar.isrc) AS isrc,
        ar.asset_id,
        r.territory_list AS registry_territories,
        ar.territory_list AS asset_report_territories
    FROM registry r
    FULL OUTER JOIN asset_report ar
        ON r.isrc = ar.isrc
),

status_calc AS (
    SELECT
        isrc,
        asset_id,
        registry_territories,
        asset_report_territories,
        ARRAY_SIZE(SPLIT(registry_territories, ',')) AS registry_count,
        ARRAY_SIZE(SPLIT(asset_report_territories, ',')) AS asset_count,
        CASE
            WHEN ARRAY_SIZE(SPLIT(registry_territories, ',')) > ARRAY_SIZE(SPLIT(asset_report_territories, ',')) THEN 'Underclaiming'
            WHEN ARRAY_SIZE(SPLIT(registry_territories, ',')) < ARRAY_SIZE(SPLIT(asset_report_territories, ',')) THEN 'Overclaiming'
            WHEN SPLIT(registry_territories, ',') <> SPLIT(asset_report_territories, ',') THEN 'Underclaiming'
        END AS status
    FROM joined
),

differences AS (
    SELECT
        isrc,
        asset_id,
        status,
        CASE
            WHEN status = 'Underclaiming' THEN ARRAY_EXCEPT(TO_ARRAY(SPLIT(registry_territories, ',')), TO_ARRAY(SPLIT(asset_report_territories, ',')))
            WHEN status = 'Overclaiming' THEN ARRAY_EXCEPT(TO_ARRAY(SPLIT(asset_report_territories, ',')), TO_ARRAY(SPLIT(registry_territories, ',')))
        END AS differing_array
    FROM status_calc
)

SELECT
    isrc,
    status,
    ARRAY_TO_STRING(differing_array, ',') AS differing_territories,
    ARRAY_SIZE(differing_array) AS missing_count,
    asset_id,
FROM differences
WHERE differing_array IS NOT NULL AND ARRAY_SIZE(differing_array) > 0

  ;;
 }

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

  dimension: status {
    label: "Status"
    type: string
    sql: ${TABLE}.status ;;
  }

  dimension: differing_territories {
    label: "Differing Territories"
    type: string
    sql: ${TABLE}.differing_territories ;;
  }

  dimension: asset_id {
    label: "Asset ID"
    type: string
    sql: ${TABLE}.asset_id ;;
  }

  measure: missing_count {
    label: "Missing Count"
    type: sum
    sql: ${TABLE}.missing_count ;;
  }

  }