view: dt_album_cover_art {
 derived_table: {
   sql:
  with cte as (
      SELECT
      au.PRODUCT_ID,
      af.ASSET_SUBTYPE,
      MAX(CASE WHEN af.ASSET_SUBTYPE = 'cover' THEN 'https://images.theorchard.io/v2/product/cover'||RIGHT(af.FILENAME, len(af.FILENAME) - 23) END) AS cover_url,
      MAX(CASE WHEN af.ASSET_SUBTYPE = 'xlarge_cover' THEN 'https://images.theorchard.io/v2/product/xlarge_cover'||RIGHT(af.FILENAME, len(af.FILENAME) - 30) END) AS xlarge_cover_url
    FROM
      "ORCHARD_APP_REPORTING_V2"."PROD_OWS_ASSETS_OWS_ASSETS"."ASSET_FINAL" af
    INNER JOIN
      "ORCHARD_APP_REPORTING_V2"."PROD_OWS_ASSETS_OWS_ASSETS"."ASSET_UPLOAD" au ON au.id = af.asset_upload_id
    WHERE
      au.api_version = 2 AND
      au.deleted = 0 AND
      (af.asset_subtype = 'cover' OR af.asset_subtype = 'xlarge_cover')
    GROUP BY
      au.PRODUCT_ID,af.ASSET_SUBTYPE
)
select distinct
product_id,
coalesce(max(xlarge_cover_url),max(cover_url)) as cover_url
from cte
group by product_id
  ;;
 }

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

  dimension: cover_url {
    label: "Cover URL"
    type: string
    hidden: yes
    sql: ${TABLE}.cover_url ;;
  }

  dimension: album_cover_art {
    label: "Album Cover Art"
    sql: ${TABLE}.cover_url ;;
    html: <img src="{{ value }}" style="max-width: 100%; max-height: 100%; object-fit: contain;" />;;
  }

}