# Cortex Search Services for FACTS database

resource "snowflake_cortex_search_service" "playlist_metadata_search_qa" {
  name     = "PLAYLIST_METADATA_SEARCH"
  database = var.database_name
  schema   = "QA"

  on = "PLAYLIST_NAME"

  # Note: FRONTLINE_PERCENT, LOCAL_PERCENT, and SMG_PERCENT, PLAYLIST_GENRES are intentionally
  # excluded from attributes because they are VARIANT (object) types and cannot
  # be included in Cortex Search service results. They remain in the source query
  # but are not returned in search results.
  attributes = [
    "STORE_PLAYLIST_ID",
    "STORE_ID",
    "PLAYLIST_ARTWORK_URL",
    "PLAYLIST_TRACK_COUNT",
    "PLAYLIST_FOLLOWER_COUNT",
    "PLAYLIST_TYPE",
    "PRIMARY_PLAYLIST_TYPE",
    "PLAYLIST_URI",
    "CURATOR_NAME",
    "CURATOR_ID",
    "CURATOR_COUNTRY",
    "LAST_UPDATED",
    "PLAYLIST_DESCRIPTION",
    "CREATED_AT",
    "OWNER"
  ]

  warehouse  = "QA_ETL_WAREHOUSE"
  target_lag = "1 hour"
  comment    = "Cortex Search service for playlist metadata search by name"

  query = <<-SQL
    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT,
      FRONTLINE_PERCENT,
      LOCAL_PERCENT,
      SMG_PERCENT,
      OWNER
    FROM FACTS.QA.PRIORITY_PLAYLIST_METADATA
  SQL
}

resource "snowflake_cortex_search_service" "playlist_metadata_search_prod" {
  name     = "PLAYLIST_METADATA_SEARCH"
  database = var.database_name
  schema   = "PROD"

  on = "PLAYLIST_NAME"

  # Note: FRONTLINE_PERCENT, LOCAL_PERCENT, and SMG_PERCENT, PLAYLIST_GENRES are intentionally
  # excluded from attributes because they are VARIANT (object) types and cannot
  # be included in Cortex Search service results. They remain in the source query
  # but are not returned in search results.
  attributes = [
    "STORE_PLAYLIST_ID",
    "STORE_ID",
    "PLAYLIST_ARTWORK_URL",
    "PLAYLIST_TRACK_COUNT",
    "PLAYLIST_FOLLOWER_COUNT",
    "PLAYLIST_TYPE",
    "PRIMARY_PLAYLIST_TYPE",
    "PLAYLIST_URI",
    "CURATOR_NAME",
    "CURATOR_ID",
    "CURATOR_COUNTRY",
    "LAST_UPDATED",
    "PLAYLIST_DESCRIPTION",
    "CREATED_AT",
    "OWNER"
  ]

  warehouse  = "PROD_ETL_WAREHOUSE"
  target_lag = "1 hour"
  comment    = "Cortex Search service for playlist metadata search by name"

  query = <<-SQL
    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT,
      FRONTLINE_PERCENT,
      LOCAL_PERCENT,
      SMG_PERCENT,
      OWNER
    FROM FACTS.PROD.PRIORITY_PLAYLIST_METADATA
  SQL
}

resource "snowflake_cortex_search_service" "playlist_metadata_search_v2_qa" {
  name     = "PLAYLIST_METADATA_SEARCH_V2"
  database = var.database_name
  schema   = "QA"

  on = "PLAYLIST_NAME"

  # Note: FRONTLINE_PERCENT, LOCAL_PERCENT, SMG_PERCENT, and PLAYLIST_GENRES are
  # intentionally excluded from attributes because they are VARIANT (object/array)
  # types and cannot be included in Cortex Search service results. They remain in
  # the source query but are not returned in search results.
  attributes = [
    "STORE_PLAYLIST_ID",
    "STORE_ID",
    "PLAYLIST_ARTWORK_URL",
    "PLAYLIST_TRACK_COUNT",
    "PLAYLIST_FOLLOWER_COUNT",
    "PLAYLIST_TYPE",
    "PRIMARY_PLAYLIST_TYPE",
    "PLAYLIST_URI",
    "CURATOR_NAME",
    "CURATOR_ID",
    "CURATOR_COUNTRY",
    "LAST_UPDATED",
    "PLAYLIST_DESCRIPTION",
    "CREATED_AT",
    "OWNER"
  ]

  warehouse  = "QA_ETL_WAREHOUSE"
  target_lag = "1 hour"
  comment    = "Version 2 of PLAYLIST_METADATA_SEARCH that includes both priority and hourly playlist metadata"

  query = <<-SQL
    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT,
      FRONTLINE_PERCENT,
      LOCAL_PERCENT,
      SMG_PERCENT,
      OWNER
    FROM FACTS.QA.PRIORITY_PLAYLIST_METADATA

    UNION ALL

    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT,
      FRONTLINE_PERCENT,
      LOCAL_PERCENT,
      SMG_PERCENT,
      OWNER
    FROM FACTS.QA.HOURLY_PLAYLIST_METADATA
  SQL
}

resource "snowflake_cortex_search_service" "playlist_metadata_search_v2_prod" {
  name     = "PLAYLIST_METADATA_SEARCH_V2"
  database = var.database_name
  schema   = "PROD"

  on = "PLAYLIST_NAME"

  # Note: FRONTLINE_PERCENT, LOCAL_PERCENT, SMG_PERCENT, and PLAYLIST_GENRES are
  # intentionally excluded from attributes because they are VARIANT (object/array)
  # types and cannot be included in Cortex Search service results. They remain in
  # the source query but are not returned in search results.
  attributes = [
    "STORE_PLAYLIST_ID",
    "STORE_ID",
    "PLAYLIST_ARTWORK_URL",
    "PLAYLIST_TRACK_COUNT",
    "PLAYLIST_FOLLOWER_COUNT",
    "PLAYLIST_TYPE",
    "PRIMARY_PLAYLIST_TYPE",
    "PLAYLIST_URI",
    "CURATOR_NAME",
    "CURATOR_ID",
    "CURATOR_COUNTRY",
    "LAST_UPDATED",
    "PLAYLIST_DESCRIPTION",
    "CREATED_AT",
    "OWNER"
  ]

  warehouse  = "PROD_ETL_WAREHOUSE"
  target_lag = "1 hour"
  comment    = "Version 2 of PLAYLIST_METADATA_SEARCH that includes both priority and hourly playlist metadata"

  query = <<-SQL
    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT,
      FRONTLINE_PERCENT,
      LOCAL_PERCENT,
      SMG_PERCENT,
      OWNER
    FROM FACTS.PROD.PRIORITY_PLAYLIST_METADATA

    UNION ALL

    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT,
      FRONTLINE_PERCENT,
      LOCAL_PERCENT,
      SMG_PERCENT,
      OWNER
    FROM FACTS.PROD.HOURLY_PLAYLIST_METADATA
  SQL
}

resource "snowflake_cortex_search_service" "playlist_metadata_search_v3_qa" {
  name     = "PLAYLIST_METADATA_SEARCH_V3"
  database = var.database_name
  schema   = "QA"

  on = "PLAYLIST_NAME"

  # Note: FRONTLINE_PERCENT, LOCAL_PERCENT, SMG_PERCENT, and PLAYLIST_GENRES are
  # intentionally excluded from attributes because they are VARIANT (object/array)
  # types and cannot be included in Cortex Search service results. They remain in
  # the source query but are not returned in search results.
  attributes = [
    "STORE_PLAYLIST_ID",
    "STORE_ID",
    "PLAYLIST_ARTWORK_URL",
    "PLAYLIST_TRACK_COUNT",
    "PLAYLIST_FOLLOWER_COUNT",
    "PLAYLIST_TYPE",
    "PRIMARY_PLAYLIST_TYPE",
    "PLAYLIST_URI",
    "CURATOR_NAME",
    "CURATOR_ID",
    "CURATOR_COUNTRY",
    "LAST_UPDATED",
    "PLAYLIST_DESCRIPTION",
    "CREATED_AT",
    "OWNER"
  ]

  warehouse  = "QA_ETL_WAREHOUSE"
  target_lag = "1 hour"
  comment    = "Version 3 of PLAYLIST_METADATA_SEARCH that includes priority and hourly playlist metadata"

  query = <<-SQL
    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      TO_VARIANT(PLAYLIST_GENRES) AS PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED::TIMESTAMP_NTZ AS LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT::TIMESTAMP_NTZ AS CREATED_AT,
      TO_VARIANT(FRONTLINE_PERCENT) AS FRONTLINE_PERCENT,
      TO_VARIANT(LOCAL_PERCENT) AS LOCAL_PERCENT,
      TO_VARIANT(SMG_PERCENT) AS SMG_PERCENT,
      OWNER
    FROM FACTS.QA.PRIORITY_PLAYLIST_METADATA

    UNION ALL

    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT,
      FRONTLINE_PERCENT,
      LOCAL_PERCENT,
      SMG_PERCENT,
      OWNER
    FROM FACTS.QA.PLAYLIST_HOURLY_METADATA_DBT
  SQL
}

resource "snowflake_cortex_search_service" "playlist_metadata_search_v3_prod" {
  name     = "PLAYLIST_METADATA_SEARCH_V3"
  database = var.database_name
  schema   = "PROD"

  on = "PLAYLIST_NAME"

  # Note: FRONTLINE_PERCENT, LOCAL_PERCENT, SMG_PERCENT, and PLAYLIST_GENRES are
  # intentionally excluded from attributes because they are VARIANT (object/array)
  # types and cannot be included in Cortex Search service results. They remain in
  # the source query but are not returned in search results.
  attributes = [
    "STORE_PLAYLIST_ID",
    "STORE_ID",
    "PLAYLIST_ARTWORK_URL",
    "PLAYLIST_TRACK_COUNT",
    "PLAYLIST_FOLLOWER_COUNT",
    "PLAYLIST_TYPE",
    "PRIMARY_PLAYLIST_TYPE",
    "PLAYLIST_URI",
    "CURATOR_NAME",
    "CURATOR_ID",
    "CURATOR_COUNTRY",
    "LAST_UPDATED",
    "PLAYLIST_DESCRIPTION",
    "CREATED_AT",
    "OWNER"
  ]

  warehouse  = "PROD_ETL_WAREHOUSE"
  target_lag = "1 hour"
  comment    = "Version 3 of PLAYLIST_METADATA_SEARCH that includes priority and hourly playlist metadata"

  query = <<-SQL
    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      TO_VARIANT(PLAYLIST_GENRES) AS PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED::TIMESTAMP_NTZ AS LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT::TIMESTAMP_NTZ AS CREATED_AT,
      TO_VARIANT(FRONTLINE_PERCENT) AS FRONTLINE_PERCENT,
      TO_VARIANT(LOCAL_PERCENT) AS LOCAL_PERCENT,
      TO_VARIANT(SMG_PERCENT) AS SMG_PERCENT,
      OWNER
    FROM FACTS.PROD.PRIORITY_PLAYLIST_METADATA

    UNION ALL

    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT,
      FRONTLINE_PERCENT,
      LOCAL_PERCENT,
      SMG_PERCENT,
      OWNER
    FROM FACTS.PROD.PLAYLIST_HOURLY_METADATA_DBT
  SQL
}

resource "snowflake_cortex_search_service" "playlist_metadata_search_v4_qa" {
  name     = "PLAYLIST_METADATA_SEARCH_V4"
  database = var.database_name
  schema   = "QA"

  on = "PLAYLIST_NAME"

  # Note: FRONTLINE_PERCENT, LOCAL_PERCENT, SMG_PERCENT, and PLAYLIST_GENRES are
  # intentionally excluded from attributes because they are VARIANT (object/array)
  # types and cannot be included in Cortex Search service results. They remain in
  # the source query but are not returned in search results.
  attributes = [
    "STORE_PLAYLIST_ID",
    "STORE_ID",
    "PLAYLIST_ARTWORK_URL",
    "PLAYLIST_TRACK_COUNT",
    "PLAYLIST_FOLLOWER_COUNT",
    "PLAYLIST_TYPE",
    "PRIMARY_PLAYLIST_TYPE",
    "PLAYLIST_URI",
    "CURATOR_NAME",
    "CURATOR_ID",
    "CURATOR_COUNTRY",
    "LAST_UPDATED",
    "PLAYLIST_DESCRIPTION",
    "CREATED_AT",
    "OWNER"
  ]

  warehouse = "QA_ETL_WAREHOUSE"
  # target_lag set to 1 day (rather than 1 hour) due to untested data volume
  # at non-priority playlist scale. Revisit after initial cost/performance monitoring.
  target_lag = "1 day"
  comment    = "Version 4 of PLAYLIST_METADATA_SEARCH that includes priority, hourly, and non-priority playlist metadata"

  query = <<-SQL
    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      TO_VARIANT(PLAYLIST_GENRES) AS PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED::TIMESTAMP_NTZ AS LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT::TIMESTAMP_NTZ AS CREATED_AT,
      TO_VARIANT(FRONTLINE_PERCENT) AS FRONTLINE_PERCENT,
      TO_VARIANT(LOCAL_PERCENT) AS LOCAL_PERCENT,
      TO_VARIANT(SMG_PERCENT) AS SMG_PERCENT,
      OWNER
    FROM FACTS.QA.PRIORITY_PLAYLIST_METADATA

    UNION ALL

    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT,
      FRONTLINE_PERCENT,
      LOCAL_PERCENT,
      SMG_PERCENT,
      OWNER
    FROM FACTS.QA.PLAYLIST_HOURLY_METADATA_DBT

    UNION ALL

    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT,
      FRONTLINE_PERCENT,
      LOCAL_PERCENT,
      SMG_PERCENT,
      OWNER
    FROM FACTS.QA.PLAYLIST_NON_PRIORITY_METADATA_DBT
  SQL
}

resource "snowflake_cortex_search_service" "nmf_chart_search_qa" {
  name     = "NMF_CHART_SEARCH"
  database = var.database_name
  schema   = "QA"

  on = "SEARCH_TEXT"

  attributes = [
    "ISRC",
    "CHART_DATE",
    "TRACK_NAME",
    "ARTIST_NAME"
  ]

  warehouse  = "QA_ETL_WAREHOUSE"
  target_lag = "5 minutes"
  comment    = "Cortex Search service for NMF chart search by track name, artist name, or ISRC"

  # Note: this service intentionally queries FACTS.PROD tables — QA uses prod NMF data.
  query = <<-SQL
    SELECT
      fc.isrc,
      fc.chart_date::VARCHAR AS chart_date,
      COALESCE(MIN(st.track_name), MIN(stm.track_name)) AS track_name,
      COALESCE(MIN(st.track_artists), MIN(stm.artist_name)) AS artist_name,
      COALESCE(MIN(st.track_name), MIN(stm.track_name), '') || ' ' || COALESCE(MIN(st.track_artists), MIN(stm.artist_name), '') AS search_text
    FROM FACTS.PROD.FACT_CHARTS fc
    JOIN FACTS.PROD.DIM_NEW_MUSIC_FRIDAY_CHART dnmfc
      ON fc.chartid = dnmfc.chartid
    LEFT JOIN FACTS.PROD.SPOTIFY_TRACKS st
      ON fc.isrc = st.isrc
    LEFT JOIN FACTS.PROD.SPOTIFY_TRACK_METADATA stm
      ON fc.isrc = stm.isrc
    WHERE fc.isrc IS NOT NULL
      AND fc.isrc != ''
    GROUP BY fc.isrc, fc.chart_date
  SQL
}

resource "snowflake_cortex_search_service" "nmf_chart_search_prod" {
  name     = "NMF_CHART_SEARCH"
  database = var.database_name
  schema   = "PROD"

  on = "SEARCH_TEXT"

  attributes = [
    "ISRC",
    "CHART_DATE",
    "TRACK_NAME",
    "ARTIST_NAME"
  ]

  warehouse  = "PROD_ETL_WAREHOUSE"
  target_lag = "5 minutes"
  comment    = "Cortex Search service for NMF chart search by track name, artist name, or ISRC"

  query = <<-SQL
    SELECT
      fc.isrc,
      fc.chart_date::VARCHAR AS chart_date,
      COALESCE(MIN(st.track_name), MIN(stm.track_name)) AS track_name,
      COALESCE(MIN(st.track_artists), MIN(stm.artist_name)) AS artist_name,
      COALESCE(MIN(st.track_name), MIN(stm.track_name), '') || ' ' || COALESCE(MIN(st.track_artists), MIN(stm.artist_name), '') AS search_text
    FROM FACTS.PROD.FACT_CHARTS fc
    JOIN FACTS.PROD.DIM_NEW_MUSIC_FRIDAY_CHART dnmfc
      ON fc.chartid = dnmfc.chartid
    LEFT JOIN FACTS.PROD.SPOTIFY_TRACKS st
      ON fc.isrc = st.isrc
    LEFT JOIN FACTS.PROD.SPOTIFY_TRACK_METADATA stm
      ON fc.isrc = stm.isrc
    WHERE fc.isrc IS NOT NULL
      AND fc.isrc != ''
    GROUP BY fc.isrc, fc.chart_date
  SQL
}

resource "snowflake_cortex_search_service" "playlist_metadata_search_v4_prod" {
  name     = "PLAYLIST_METADATA_SEARCH_V4"
  database = var.database_name
  schema   = "PROD"

  on = "PLAYLIST_NAME"

  # Note: FRONTLINE_PERCENT, LOCAL_PERCENT, SMG_PERCENT, and PLAYLIST_GENRES are
  # intentionally excluded from attributes because they are VARIANT (object/array)
  # types and cannot be included in Cortex Search service results. They remain in
  # the source query but are not returned in search results.
  attributes = [
    "STORE_PLAYLIST_ID",
    "STORE_ID",
    "PLAYLIST_ARTWORK_URL",
    "PLAYLIST_TRACK_COUNT",
    "PLAYLIST_FOLLOWER_COUNT",
    "PLAYLIST_TYPE",
    "PRIMARY_PLAYLIST_TYPE",
    "PLAYLIST_URI",
    "CURATOR_NAME",
    "CURATOR_ID",
    "CURATOR_COUNTRY",
    "LAST_UPDATED",
    "PLAYLIST_DESCRIPTION",
    "CREATED_AT",
    "OWNER"
  ]

  warehouse = "PROD_ETL_WAREHOUSE"
  # target_lag set to 1 day (rather than 1 hour) due to untested data volume
  # at non-priority playlist scale. Revisit after initial cost/performance monitoring.
  target_lag = "1 day"
  comment    = "Version 4 of PLAYLIST_METADATA_SEARCH that includes priority, hourly, and non-priority playlist metadata"

  query = <<-SQL
    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      TO_VARIANT(PLAYLIST_GENRES) AS PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED::TIMESTAMP_NTZ AS LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT::TIMESTAMP_NTZ AS CREATED_AT,
      TO_VARIANT(FRONTLINE_PERCENT) AS FRONTLINE_PERCENT,
      TO_VARIANT(LOCAL_PERCENT) AS LOCAL_PERCENT,
      TO_VARIANT(SMG_PERCENT) AS SMG_PERCENT,
      OWNER
    FROM FACTS.PROD.PRIORITY_PLAYLIST_METADATA

    UNION ALL

    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT,
      FRONTLINE_PERCENT,
      LOCAL_PERCENT,
      SMG_PERCENT,
      OWNER
    FROM FACTS.PROD.PLAYLIST_HOURLY_METADATA_DBT

    UNION ALL

    SELECT
      PLAYLIST_NAME,
      STORE_PLAYLIST_ID,
      STORE_ID,
      PLAYLIST_ARTWORK_URL,
      PLAYLIST_GENRES,
      PLAYLIST_TRACK_COUNT,
      PLAYLIST_FOLLOWER_COUNT,
      PLAYLIST_TYPE,
      PRIMARY_PLAYLIST_TYPE,
      PLAYLIST_URI,
      CURATOR_NAME,
      CURATOR_ID,
      CURATOR_COUNTRY,
      LAST_UPDATED,
      PLAYLIST_DESCRIPTION,
      CREATED_AT,
      FRONTLINE_PERCENT,
      LOCAL_PERCENT,
      SMG_PERCENT,
      OWNER
    FROM FACTS.PROD.PLAYLIST_NON_PRIORITY_METADATA_DBT
  SQL
}