view: dt_store_availability_analytics_sme_updated {
  derived_table: {
    sql:
    with STORE_SUMMARY_T1 as (select AVAIL.FEEDID,
                                     case
                                         when MISSING_DATE is not null and
                                              DOWNLOAD_ACTIVITY_DATE < dateadd('DAY', -THRESHOLD, current_date())
                                             then DOWNLOAD_ACTIVITY_DATE
                                         end as MISSING_DATE,
                                     case
                                         when INCOMPLETE_DATE is not null and
                                              DOWNLOAD_ACTIVITY_DATE < dateadd('DAY', -THRESHOLD, current_date())
                                             then DOWNLOAD_ACTIVITY_DATE
                                         end as INCOMPLETE_DATE,
                                     case
                                         when DUPLICATED_DATE is not null and
                                              DOWNLOAD_ACTIVITY_DATE < dateadd('DAY', -THRESHOLD, current_date())
                                             then DOWNLOAD_ACTIVITY_DATE
                                         end as DUPLICATED_DATE,
                                     STORE_HIGH_WATERMARK
                              from FACTS.PROD.DATA_AVAILABILITY_BY_STORE_DAILY AVAIL
                                       inner join INTELLIGENCE.PROD.STORE_SLAS SLAS on AVAIL.FEEDID = SLAS.FEEDID
                                       inner join FACTS.PROD.DIM_LICENSOR LICENSOR on LICENSOR.FEEDID = SLAS.FEEDID
                              where coalesce(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) is not null
                                and LICENSOR.DISTRIBUTOR = 'sme'
                              group by 1, 2, 3, 4, 5),
         FILTERED_SUMMARY_T1 as (select *
                                 from STORE_SUMMARY_T1
                                 where MISSING_DATE > '2021-01-17'
                                    or MISSING_DATE is null),
         STORES_AGGREGATED_T1 as (select FEEDID,
                                         listagg(right(MISSING_DATE, 5), ', ')
                                                 within group (order by MISSING_DATE)    as MISSING_DATES,
                                         listagg(right(INCOMPLETE_DATE, 5), ', ')
                                                 within group (order by INCOMPLETE_DATE) as INCOMPLETE_DATES,
                                         listagg(right(DUPLICATED_DATE, 5), ', ')
                                                 within group (order by DUPLICATED_DATE) as DUPLICATED_DATES,
                                         STORE_HIGH_WATERMARK
                                  from FILTERED_SUMMARY_T1
                                  group by 1, 5),
         DT_STORE_AVAILABILITY_ANALYTICS_T1 as (select SA.FEEDID,
                                                       DF.FEEDNAME,
                                                       '\n' ||
                                                       case
                                                           when MISSING_DATES != ''
                                                               then '-Missing Dates: ' || MISSING_DATES || '\n'
                                                           else '' end ||
                                                       case
                                                           when INCOMPLETE_DATES != ''
                                                               then '-Incomplete Dates: ' || INCOMPLETE_DATES || '\n'
                                                           else '' end ||
                                                       case
                                                           when DUPLICATED_DATES != ''
                                                               then '-Duplicated Dates: ' || DUPLICATED_DATES || '\n'
                                                           else '' end || '\n'
                                                                            as ERROR_DATES,
                                                       STORE_HIGH_WATERMARK as MAX_COMPLETE_DATE
                                                from STORES_AGGREGATED_T1 SA
                                                         left join FACTS.PROD.DIM_FEED DF on SA.FEEDID = DF.FEEDID),
         TABLE1 as (select DSAA.FEEDID                      as FEEDID,
                           DSAA.FEEDNAME                    as FEEDNAME,
                           case
                               when length(DSAA.ERROR_DATES) < 3 then '0'
                               else '1'
                               end                          as OVERALL_STATUS,
                           current_date - MAX_COMPLETE_DATE as LATE_DELIVERY_DAYE,
                           case
                               when DSAA.FEEDID in (1, 4, 35, 36, 37) and LATE_DELIVERY_DAYE <= 2 then 'green'
                               when DSAA.FEEDID in (1, 4, 35, 36, 37) and LATE_DELIVERY_DAYE <= 3 then 'red'
                               when DSAA.FEEDID in (1, 4, 35, 36, 37) and LATE_DELIVERY_DAYE > 3 then 'red-notify'
                               when DSAA.FEEDID in (3, 9, 11, 13, 16, 38, 42, 43, 44, 49, 50, 51) and
                                    LATE_DELIVERY_DAYE <= 2
                                   then 'green'
                               when DSAA.FEEDID in (3, 9, 11, 13, 16, 38, 42, 43, 44, 49, 50, 51) and LATE_DELIVERY_DAYE = 3
                                   then 'yellow'
                               when DSAA.FEEDID in (3, 9, 11, 13, 16, 38, 42, 43, 44, 49, 50, 51) and LATE_DELIVERY_DAYE > 3
                                   then 'red'
                               end                          as OVERALLSTATUS,
                           DSAA.ERROR_DATES                 as ERRORDATES,
                           (to_char(
                                   to_date(DSAA.MAX_COMPLETE_DATE),
                                   'YYYY-MM-DD'))           as MAXOMPLETEDDATE,
                           decode(DSAA.FEEDID, 1, '00', 4, '01', 13, '02', 16, '05', 9, '06', 3,
                                  '07', 11, '10',
                                  '12')                     as PRIORITYRANKINGSORT,
                           decode(DSAA.FEEDID, 1, '1', 4, '2', 13, '3', 16, '6', 9, '7', 3, '8',
                                  14, '9', 11, '12', '')
                                                            as PRIORITYRANKING
                    from DT_STORE_AVAILABILITY_ANALYTICS_T1 DSAA
                    where (DSAA.FEEDID != 38)
                    group by (to_date(DSAA.MAX_COMPLETE_DATE)), 1, 2, 3, 4, 5, 6, 8, 9
                        fetch next 500 rows only),
         STORE_SUMMARY_T2
             as (select AVAIL.FEEDID,
                        case
                            when MISSING_DATE is not null and
                                 DOWNLOAD_ACTIVITY_DATE < dateadd('DAY', -THRESHOLD, current_date())
                                then DOWNLOAD_ACTIVITY_DATE
                            end as MISSING_DATE,
                        case
                            when INCOMPLETE_DATE is not null and
                                 DOWNLOAD_ACTIVITY_DATE < dateadd('DAY', -THRESHOLD, current_date())
                                then DOWNLOAD_ACTIVITY_DATE
                            end as INCOMPLETE_DATE,
                        case
                            when DUPLICATED_DATE is not null and
                                 DOWNLOAD_ACTIVITY_DATE < dateadd('DAY', -THRESHOLD, current_date())
                                then DOWNLOAD_ACTIVITY_DATE
                            end as DUPLICATED_DATE,
                        STORE_HIGH_WATERMARK
                 from (with STORE_DATA_TOTAL
                                as (select FEEDID,
                                           DOWNLOAD_ACTIVITY_DATE,
                                           sum(VIEWS) as UNITS
                                    from FACTS.PROD.FACT_YOUTUBE_VIDEO_ANALYTICS
                                    where DOWNLOAD_ACTIVITY_DATE between dateadd('WEEK', -12, current_date()) and current_date()
                                      and (LICENSORID in
                                           (select LICENSORID
                                            from FACTS.PROD.DIM_LICENSOR
                                            where DISTRIBUTOR = 'sme'))
                                    group by 1, 2
                                    union all
                                    select FEEDID,
                                           DOWNLOAD_ACTIVITY_DATE,
                                           sum(VIEWS) as UNITS
                                    from FACTS.PROD.FACT_YOUTUBE_ASSET_ANALYTICS
                                    where DOWNLOAD_ACTIVITY_DATE between dateadd('WEEK', -12, current_date()) and current_date()
                                      and (LICENSORID in
                                           (select LICENSORID
                                            from FACTS.PROD.DIM_LICENSOR
                                            where DISTRIBUTOR = 'sme'))
                                    group by 1, 2),

      ALL_AVAILABLE
      as (select S.FEEDID,
      DD.DISPLAYDATE as DOWNLOAD_ACTIVITY_DATE
      from FACTS.PROD.DIM_DAY DD
      cross join (select distinct FEEDID from STORE_DATA_TOTAL) S
      where DD.DISPLAYDATE > dateadd('WEEK', -12, current_date())
      and DD.DISPLAYDATE <= current_date()),

      STORE_DATA_SUMMARY
      as (select AA.FEEDID,
      AA.DOWNLOAD_ACTIVITY_DATE,
      SDS.UNITS
      from ALL_AVAILABLE AA
      left join STORE_DATA_TOTAL SDS
      on AA.FEEDID = SDS.FEEDID and
      AA.DOWNLOAD_ACTIVITY_DATE = SDS.DOWNLOAD_ACTIVITY_DATE),

      STORE_THRESHOLDS
      as (select FEEDID,
      percentile_cont(.25) within group (order by UNITS) as Q1,
      percentile_cont(.75) within group (order by UNITS) as Q3,
      Q3 - Q1                                            as IQR,
      Q1 - 4 * IQR                                       as MIN_THRESHOLD,
      Q3 + 8 * IQR                                       as MAX_THRESHOLD
      from STORE_DATA_SUMMARY
      where DOWNLOAD_ACTIVITY_DATE between dateadd('WEEK', -12, current_date())
      and dateadd('WEEK', -4, current_date())
      group by FEEDID),

      EXPANDED_STORE_DATA_SUMMARY
      as (select SDS.FEEDID,
      SDS.DOWNLOAD_ACTIVITY_DATE,
      iff(SDS.UNITS is null, 1, null)            as MISSING_DATE,
      iff(SDS.UNITS < ST.MIN_THRESHOLD, 1, null) as INCOMPLETE_DATE,
      iff(SDS.UNITS > ST.MAX_THRESHOLD, 1, null) as DUPLICATED_DATE
      from STORE_DATA_SUMMARY SDS
      left join STORE_THRESHOLDS ST
      on SDS.FEEDID = ST.FEEDID),

      MAX_AVAILABLE_DATES
      as (select FEEDID,
      max(DOWNLOAD_ACTIVITY_DATE) as STORE_HIGH_WATERMARK
      from EXPANDED_STORE_DATA_SUMMARY
      where coalesce(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) is null
      group by 1)

      select case
      when DF.FEEDID = 13
      then 9999
      when DF.FEEDID = 18
      then 49602
      when DF.FEEDID = 14
      then 45302
      else DF.STOREID
      end as STOREID,
      ESDS.FEEDID,
      ESDS.DOWNLOAD_ACTIVITY_DATE,
      ESDS.MISSING_DATE,
      ESDS.INCOMPLETE_DATE,
      ESDS.DUPLICATED_DATE,
      MAD.STORE_HIGH_WATERMARK
      from EXPANDED_STORE_DATA_SUMMARY ESDS
      left join MAX_AVAILABLE_DATES MAD
      on ESDS.FEEDID = MAD.FEEDID
      inner join FACTS.PROD.DIM_FEED DF
      on DF.FEEDID = ESDS.FEEDID) AVAIL
      inner join INTELLIGENCE.PROD.STORE_SLAS SLAS on AVAIL.FEEDID = SLAS.FEEDID
      inner join FACTS.PROD.DIM_LICENSOR LICENSOR on LICENSOR.FEEDID = SLAS.FEEDID
      where coalesce(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) is not null
      and LICENSOR.DISTRIBUTOR = 'sme'
      group by 1, 2, 3, 4, 5),
      FILTERED_SUMMARY_T2
      as (select *
      from STORE_SUMMARY_T2
      where MISSING_DATE > '2021-01-17'
      or MISSING_DATE is null),
      STORES_AGGREGATED_T2 as (select FEEDID,
      listagg(
      right(MISSING_DATE, 5),
      ', ')
      within group (order by MISSING_DATE)    as MISSING_DATES,
      listagg(
      right(INCOMPLETE_DATE, 5),
      ', ')
      within group (order by INCOMPLETE_DATE) as INCOMPLETE_DATES,
      listagg(
      right(DUPLICATED_DATE, 5),
      ', ')
      within group (order by DUPLICATED_DATE) as DUPLICATED_DATES,
      STORE_HIGH_WATERMARK
      from FILTERED_SUMMARY_T2
      group by 1, 5),
      DT_STORE_AVAILABILITY_ANALYTICS_T2 as (select SA.FEEDID,
      DF.FEEDNAME,
      '\n' ||
      case
      when MISSING_DATES != ''
      then '-Missing Dates: ' || MISSING_DATES || '\n'
      else '' end ||
      case
      when INCOMPLETE_DATES != ''
      then '-Incomplete Dates: ' || INCOMPLETE_DATES || '\n'
      else '' end ||
      case
      when DUPLICATED_DATES != ''
      then '-Duplicated Dates: ' || DUPLICATED_DATES || '\n'
      else '' end ||
      '\n'
      as ERROR_DATES,
      STORE_HIGH_WATERMARK as MAX_COMPLETE_DATE
      from STORES_AGGREGATED_T2 SA
      left join FACTS.PROD.DIM_FEED DF on SA.FEEDID = DF.FEEDID),
      DT_STORE_AVAILABILITY_ANALYTICS_SME_T2 as (select DSAAT.FEEDID                     as FEEDID,
      DSAAT.FEEDNAME                   as FEEDNAME,
      case
      when length(DSAAT.ERROR_DATES) < 3
      then '0'
      else '1'
      end                          as OVERALL_STATUS,
      current_date - MAX_COMPLETE_DATE as LATE_DELIVERY_DAYE,
      case
      when DSAAT.FEEDID in (1, 4, 35, 36, 37) and LATE_DELIVERY_DAYE <= 2
      then 'green'
      when DSAAT.FEEDID in (1, 4, 35, 36, 37) and LATE_DELIVERY_DAYE <= 3
      then 'red'
      when DSAAT.FEEDID in (1, 4, 35, 36, 37) and LATE_DELIVERY_DAYE > 3
      then 'red-notify'
      when DSAAT.FEEDID in
      (3, 9, 11, 13, 16, 38, 42, 43, 44, 49, 50, 51) and
      LATE_DELIVERY_DAYE <= 2
      then 'green'
      when DSAAT.FEEDID in
      (3, 9, 11, 13, 16, 38, 42, 43, 44, 49, 50, 51) and
      LATE_DELIVERY_DAYE = 3
      then 'yellow'
      when DSAAT.FEEDID in
      (3, 9, 11, 13, 16, 38, 42, 43, 44, 49, 50, 51) and
      LATE_DELIVERY_DAYE > 3
      then 'red'
      end                          as OVERALLSTATUS,
      DSAAT.ERROR_DATES                as ERRORDATES,
      (to_char(
      to_date(DSAAT.MAX_COMPLETE_DATE),
      'YYYY-MM-DD'))           as MAXOMPLETEDDATE,
      '12'                             as PRIORITYRANKINGSORT,
      ''                               as PRIORITYRANKING
      from DT_STORE_AVAILABILITY_ANALYTICS_T2 DSAAT
      fetch next 500 rows only),
      TABLE2 as (select FEEDID                                            as FEEDID,
      FEEDNAME                                          as FEEDNAME,
      OVERALL_STATUS                                    as OVERALL_STATUS,
      LATE_DELIVERY_DAYE                                as LATE_DELIVERY_DAYE,
      OVERALLSTATUS                                     as OVERALLSTATUS,
      ERRORDATES                                        as ERRORDATES,
      (to_char(to_date(MAXOMPLETEDDATE), 'YYYY-MM-DD')) as MAXCOMPLETEDATE,
      PRIORITYRANKINGSORT                               as PRIORITYRANKINGSORT,
      PRIORITYRANKING                                   as PRIORITYRANKING
      from DT_STORE_AVAILABILITY_ANALYTICS_SME_T2
      fetch next 500 rows only)
      select *
      from TABLE1
      union all
      select *
      from TABLE2;;
  }


  dimension: feedid {
    type: number
    label: "Feed ID"
    sql: ${TABLE}.FEEDID ;;
  }

  dimension: feedname {
    type: string
    label: "Feed Name"
    sql: ${TABLE}.FEEDNAME ;;
  }

  dimension: error_dates {
    type: string
    label: "Error Dates"
    sql: ${TABLE}.ERRORDATES ;;
    html:{{ value }};;
  }

  dimension: max_complete_date {
    type: date
    label: "Most Recent Day of Complete Data"
    sql: ${TABLE}.MAXOMPLETEDDATE ;;
  }

  dimension: overall_status {
    label: "Status"

    sql: ${TABLE}.OVERALLSTATUS ;;

    type: string
    html: {% if value == 'green' %}
        <p style="color: #72c275; background-color: #72c275; font-size:100%;">""</p>
        {% elsif value == 'yellow' %}
        <p style="color: #f4ea56; background-color: #f4ea56; font-size:100%;">""</p>
        {% elsif value == 'red' %}
        <p style="color: #ea9999; background-color: #ea9999; font-size:100%;">""</p>
        {% elsif value == 'red-notify' %}
        <p style="color: #ea9999; background-color: #ea9999; font-size:100%;">"Notify users"</p>
        {% endif %}
        ;;

    }

    dimension: priority_ranking  {
      label: "Priority Ranking"
      type: string
      sql: ${TABLE}.PRIORITYRANKING ;;
    }

    dimension: priority_ranking_sort  {
      label: "Priority Ranking Sort"
      type: string
      sql: ${TABLE}.PRIORITYRANKINGSORT ;;
    }
  }