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

export class CpsiByCountry extends DataPoint {

  static source: string = 'curse_perf_startup_item'
  measurement: string = 'cpsi_by_country'

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

  // 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,
          relative_start_time,
          client_country as country
        FROM "tahoe"."${this.source}"
        WHERE date = '${day}'
        AND relative_start_time < 1800000
        AND relative_start_time >= 0
        AND relative_start_time is not null
        AND is_key_metric = true
        AND category = 1
        AND subcategory = 3000
        AND is_os_startup = false
        AND is_uac_restart = false
        AND is_restart = false
        AND was_updated = false
        AND update_errored = false
        AND is_new_install = false
        AND client_country IS NOT null
      ),
      agged as (
        SELECT
          day,
          ${this.groups.join(', ')},
          ${this.percentiles.map(p => {
            return `percentile_cont(0.${p}) within group (order by relative_start_time) 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(', ')}
    `
  }

}