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

export class BceByClientKeyDestinationIsapplaunchCountry_MobileWeb extends DataPoint {

  static source: string = 'benchmark_custom_event'
  measurement: string = 'bce_by_client_key_destination_isapplaunch_country'

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

  // 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', ce.time) as day,
          ce.duration,
          ce.country,
          ct.client_app,
          ct.destination,
          ce.key,
          ct.is_app_launch
        FROM "tahoe"."${this.source}" AS ce
        JOIN "tahoe".benchmark_complete_transition AS ct ON ct.benchmark_session_id = ce.benchmark_session_id
        WHERE ce.date = '${day}'
        AND ct.date = '${day}'
        AND ce.duration < 300000
        AND ce.duration >= 0
        AND ce.duration is not null
        and ce.key in ('player-loaded', 'player-played','first-cue', 'chat-connected')
        --and ct.lost_visibility is false
        and left(ct.url, 29) != 'https://clips.twitch.tv/embed'
        and ce.duration < 300000 
        and left(ct.url, 38) != 'https://go.twitch.tv/passport-callback'
        and ct.url != 'https://go.twitch.tv/monstercat'
        and ct.destination in ('channel', 'directory_main_game', 'directory_game', 'channel_profile', 'vod')
        and ct.platform = 'mobile_web'
        AND ce.country IS NOT null
      ),
      agged as (
        SELECT
          day,
          ${this.groups.join(', ')},
          ${this.percentiles.map(p => {
            return `percentile_cont(0.${p}) within group (order by duration) over (partition by day, ${this.groups.join(', ')}) as duration_tp${p}`}).join(",\n")
          }
        FROM selection
      )

      select
        day,
        count(0) as count,
        ${this.groups.join(', ')},
        ${this.percentiles.map(p => { return `duration_tp${p}`}).join(', ')}
      from agged
      group by
        day,
        ${this.groups.join(', ')},
        ${this.percentiles.map(p => { return `duration_tp${p}`}).join(', ')}
      order by
        day,
        ${this.groups.join(', ')}
    `
  }

}