view: chartmetric_spotify_playlist_stats {
  sql_table_name: CHARTMETRIC.RAW_DATA.L_SPOTIFY_PLAYLIST_STAT ;;

  # =========================================================================
  # 1. FOREIGN KEYS FOR JOINING (No Primary Key as this is a log table)
  # =========================================================================
  dimension: spotify_track_id_fk {
    type: number
    sql: ${TABLE}.SPOTIFY ;;
    description: "Foreign key linking directly to spotify_metadata.track_id."
  }

  dimension: spotify_playlist_id_fk {
    type: number
    sql: ${TABLE}.SPOTIFY_PLAYLIST ;;
    description: "Foreign key linking directly to spotify_metadata.playlist_pk_id."
  }

  # =========================================================================
  # 2. RAW HISTORICAL STATS FIELDS
  # =========================================================================
  dimension: position {
    type: number
    sql: ${TABLE}.POSITION ;;
    description: "The historical position/rank of the track on the playlist at this specific snapshot timestamp."
  }

  # =========================================================================
  # 3. DATE / TIMESTAMPS
  # =========================================================================
  dimension_group: snapshot {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      quarter,
      year,
      day_of_week,
      day_of_month
    ]
    datatype: timestamp
    sql: ${TABLE}.TIMESTP ;;
    description: "The exact date and time snapshot when this tracking state was logged."
  }

  # =========================================================================
  # 4. MEASURES (Aggregations for Chart Analytics)
  # =========================================================================
  measure: record_count {
    type: count
    description: "Total number of historical tracking data points."
  }

  measure: average_position {
    type: average
    sql: ${position} ;;
    value_format_name: decimal_1
    description: "The mathematical average position of the track across the selected timeframe."
  }

  measure: peak_position {
    type: min
    sql: ${position} ;;
    value_format_name: decimal_0
    description: "The best performance achieved on the playlist (closest numerical value to rank 1)."
  }

  measure: lowest_position {
    type: max
    sql: ${position} ;;
    value_format_name: decimal_0
    description: "The worst/lowest performance recorded on the playlist."
  }
}