view: dt_charts_monitoring_latest_update
{
  derived_table: {
    sql:
WITH latest
     AS (SELECT platform,
                frequency,
                MAX(latest_chart_date) AS latest_chart_date
         FROM   facts.prod.dim_chart
         GROUP  BY 1, 2
         ORDER  BY 1, 2)
SELECT latest.platform,
       latest.frequency,
       latest.latest_chart_date,
       CASE
         WHEN latest.frequency = 'weekly' THEN DATEADD(week, -1, CURRENT_DATE())
         WHEN latest.frequency = 'daily' AND latest.platform = 'spotify'
              THEN DATEADD(day, -2, CURRENT_DATE())
         WHEN latest.frequency = 'daily' AND latest.platform != 'spotify'
              THEN DATEADD(day, -1, CURRENT_DATE())
         ELSE DATEADD(day, -100, CURRENT_DATE())
       END AS expected_latest_chart_date,
       latest.latest_chart_date - expected_latest_chart_date AS outage,
       COUNT(dc.*) AS updated_charts
-- TODO: add expected_updated_charts
FROM   latest
       INNER JOIN facts.prod.dim_chart dc
               ON latest.platform = dc.platform
                  AND latest.frequency = dc.frequency
                  AND latest.latest_chart_date = dc.latest_chart_date
GROUP  BY 1, 2, 3
ORDER  BY 1, 2, 3;;
    }

  dimension: platform {
    type: string
    sql: ${TABLE}.PLATFORM;;
  }

  dimension: frequency {
    type: string
    sql: ${TABLE}.FREQUENCY;;
  }

  dimension: latest_chart_date {
    type: date
    sql: ${TABLE}.LATEST_CHART_DATE;;
  }

  dimension: expected_latest_chart_date {
    type: date
    sql: ${TABLE}.EXPECTED_LATEST_CHART_DATE;;
  }

  dimension: outage {
    type: number
    sql: ${TABLE}.OUTAGE;;
    html: {% if value < 0 %}
          <p style="color: black; background-color: #FFAA00; font-size:100%;">{{ rendered_value }} days overdue</p>
          {% else %}
          <p style="color: black; background-color: #58FA58; font-size:100%;">{{ rendered_value }}</p>
          {% endif %}
        ;;
  }

  dimension: updated_charts {
    type: number
    sql:  ${TABLE}.UPDATED_CHARTS;;
  }
}