import { DataPoint } from "../DataPoint";

export class MleByKeyCountryPlatform extends DataPoint {

  static source: string = 'mobile_latency_event'
  measurement: string = 'mle_by_key_country_platform'

  // TODO: Figure this crap out, still better in 2 places than 20
  static groups: string[] = [
    'platform',
    'country'
  ]
  groups: string[] = [
    'platform',
    'country'
  ]

  manual_groups: string[] = [
    'key'
  ]

  // TODO: Figure this crap out, still better in 2 places than 20
  static percentiles: number[] = [ 99, 95, 90, 75, 50 ]
  percentiles: number[]        = [ 99, 95, 90, 75, 50 ]

  public static query(day: string): string {
    return `
    with selection as 
    (
    select
        date_trunc('day', time) as day,
        load_time as duration,
        latency_event as key,
        ${this.groups.join(', ')}
    from tahoe.${this.source}
    where date = '${day}'
    and latency_event in ('app_launch', 'video_start', 'player_loaded', 'chat_connected', 'chat_message_sent', 'viewer_list', 'clip_create', 'search_results', 'whisper_list', 'whisper_history', 'whisper_sent', 'search_results')
    and load_time < 300000
    and load_time > 0
    and load_time is not null
    and platform in ('ios', 'android')
    
    union all
    
      select
        date_trunc('day', time) as day,
        load_time as duration,
        screen_name as key,
        ${this.groups.join(', ')}
    from tahoe.${this.source}
    where date = '${day}'
    and latency_event = 'page_loaded'
    and screen_name in ('profile_other', 'live', 'login', 'signup', 'sign_up')
    and load_time < 300000
    and load_time > 0
    and load_time is not null
    and platform in ('ios', 'android')
    
    union all
    
      select
        date_trunc('day', time) as day,
        load_time as duration,
        sub_screen as key,
        ${this.groups.join(', ')}
    from tahoe.${this.source}
    where date = '${day}'
    and latency_event = 'page_loaded'
    and screen_name = 'browse' 
    and sub_screen in ('browse_games', 'browse_popular')
    and load_time < 300000
    and load_time > 0
    and load_time is not null
    and platform in ('ios', 'android')
    
    )
  , agged as 
  (
      select
        key,
        day,
        ${this.groups.join(', ')},
        ${this.percentiles.map(p => {
              return `percentile_cont(0.${p}) within group (order by duration) over (partition by day, key, ${this.groups.join(', ')}) as duration_tp${p}`}).join(",\n")
            }
      from selection 
  )
  
  select 
      day,
      count(0) as count,
      key,
      ${this.groups.join(', ')},
      ${this.percentiles.map(p => { return `duration_tp${p}`}).join(', ')}
  from agged 
  group by
    day,
    key,
    ${this.groups.join(', ')},
    ${this.percentiles.map(p => { return `duration_tp${p}`}).join(', ')}
  order by
    day,
    key,
    ${this.groups.join(', ')}
    `
  }

}