view: orch_app_ows_pod_episode {
  sql_table_name: orchard_app_reporting_v2.prod_ows_podcast_ows_podcast.episode ;;

  dimension: episode_id {
    type: number
    sql: ${TABLE}.id ;;
    primary_key: yes
  }

  dimension: external_id {
    type: number
    sql: ${TABLE}.external_id;;
    value_format: "0"
  }

  dimension: content {
    type: string
    sql: ${TABLE}.content ;;
    label: "Rating"
  }

  dimension: created_by {
    type: string
    sql: ${TABLE}.created_by ;;
  }

  dimension: apple_episode_id {
    description: "Apple Podcasts identifier that will be maintained by the producers in the podcasting platform"
    type: string
    sql: ${TABLE}.apple_id ;;
  }


  dimension_group: created {
    type: time
    sql: ${TABLE}.created_date ;;
    timeframes: [date, week, month, year, hour, minute, time]
  }

  dimension: days_since_release {
    hidden: yes
    view_label: "Episode"
    description: "Calculates the difference in days between an episode's Published Date and when activity occured (Report Date)"
    type: number
    sql: IFF(SIGN(DATEDIFF('day', ${orch_app_ows_pod_episode.published_date}, ${facts_prod_staging_raw_megaphone.report_date})) = -1,
      1, (DATEDIFF('day', ${orch_app_ows_pod_episode.published_date}, ${facts_prod_staging_raw_megaphone.report_date}) + 1)) ;;
  }


  dimension: description {
    type: string
    sql: ${TABLE}.description ;;
  }

  dimension: draft {
    type:yesno
    sql: ${TABLE}.draft ;;
  }


  dimension: episode_number {
    type: number
    sql: ${TABLE}.episode_number ;;
  }

  dimension: episode_type {
    type: string
    sql: ${TABLE}.episode_type ;;
  }

  dimension: is_deleted {
    type: yesno
    sql: ${TABLE}.is_deleted ;;
  }

  dimension: megaphone_id {
    type: string
    sql: ${TABLE}.megaphone_id ;;
  }

  dimension: uuid {
    type: string
    sql: ${TABLE}.uuid ;;
  }


  dimension: planned_mid_roll_count {
    type: number
    sql: ${TABLE}.planned_mid_roll_count ;;
  }

  dimension: planned_post_roll_count {
    type: number
    sql: ${TABLE}.planned_post_roll_count ;;
  }

  dimension: planned_pre_roll_count {
    type: number
    sql: ${TABLE}.planned_pre_roll_count ;;
  }

  dimension: podcast_id {
    type: number
    sql: ${TABLE}.podcast_id ;;
  }

  dimension_group: published {
    type: time
    sql: ${TABLE}.published_date ;;
    timeframes: [date, week, month, year, time]
  }

  dimension: season_number {
    type: number
    sql: ${TABLE}.season_number;;
  }

  dimension: title {
    type: string
    sql: ${TABLE}.title ;;
  }
}