package query

// SQL formated with: https://ol-tools.com/sql-prettify
const (
	createMetricTableSQL = `CREATE TABLE IF NOT EXISTS %s (
	name varchar,
	type int64,
	labels JSON,
	timestamp int64,
	value int64
)`

	createUsageTableSQL = `DROP TABLE IF EXISTS %s; CREATE TABLE %s (
	keyspace_id varchar,
	cluster_id varchar,
	metric_type varchar,
	digest varchar,
	value int64
)`

	queryUsageSQL = `SELECT
  keyspace_id,
  cluster_id,
  sum(value) as value
FROM
  %s
WHERE
  metric_type = '%s'
  AND digest = '%s'
GROUP BY
  keyspace_id,
  cluster_id`

	loadDataRuSQL = `SELECT
  data.labels ->> '$.keyspace_id' AS keyspace_id,
  sum(value) AS value
FROM
  (
    SELECT
      min(timestamp) AS timestamp,
      max(value) AS value,
      labels
    FROM
      %s
    WHERE
      type = 0
      AND name = 'tikv_worker_load_data_wru_cost_counter'
    GROUP BY
      labels
  ) AS data
  JOIN load_data_task ON data.timestamp = load_data_task.timestamp
  AND (data.labels ->> '$.task_id') = load_data_task.task_id
GROUP BY
  (data.labels ->> '$.keyspace_id')`

	publicEgressSQL = `SELECT
  cluster_id,
  sum(
    if(incr < 0, 0, incr)
  ) AS value
FROM
  (
    SELECT
      labels ->> '$.cluster_id' as cluster_id,
      value - lag(value) OVER (
        PARTITION BY labels
        ORDER BY
          timestamp
      ) AS incr
    FROM
      %s
    WHERE
      (labels ->> '$.direction') = 'out'
      AND (labels ->> '$.cluster_id') != ''
      AND (labels ->> '$.mode') = 'public'
      AND type = 0
      AND name = 'gateway_connection_flow_bytes_total'
  )
GROUP BY
  cluster_id`

	privatelinkEgressSQL = `SELECT
  cluster_id,
  sum(
    if(incr < 0, 0, incr)
  ) AS value
FROM
  (
    SELECT
      labels ->> '$.cluster_id' as cluster_id,
      value - lag(value) OVER (
        PARTITION BY labels
        ORDER BY
          timestamp
      ) AS incr
    FROM
      %s
    WHERE
      (labels ->> '$.direction') = 'out'
      AND (labels ->> '$.cluster_id') != ''
      AND (labels ->> '$.mode') = 'privatelink'
      AND type = 0
      AND name = 'gateway_connection_flow_bytes_total'
  )
GROUP BY
  cluster_id`

	tidbRuSQL = `SELECT
  keyspace_id,
  sum(
    if(incr < 0, 0, incr)
  ) AS value
FROM
  (
    SELECT
      labels ->> '$.name' as keyspace_id,
      value - lag(value) OVER (
        PARTITION BY (name, labels)
        ORDER BY
          timestamp
      ) AS incr
    FROM
      %s
    WHERE
      ((labels ->> '$.type') = 'tp' OR (labels ->> '$.type') IS NULL)
      AND type = 0
      AND name IN (
        'resource_manager_resource_unit_read_request_unit_sum',
        'resource_manager_resource_unit_write_request_unit_sum',
        'resource_manager_resource_unit_sql_layer_request_unit',
        'resource_manager_resource_unit_sql_layer_request_unit_sum'
      )
      AND fmod(TRY_CAST((labels ->> '$.name') AS INTEGER), %d) = %d
  )
GROUP BY
  keyspace_id`

	tiflashRuSQL = `SELECT
  keyspace_id,
  sum(
    if(incr < 0, 0, incr)
  ) AS value
FROM
  (
    SELECT
      labels ->> '$.name' as keyspace_id,
      value - lag(value) OVER (
        PARTITION BY (name, labels)
        ORDER BY
          timestamp
      ) AS incr
    FROM
      %s
    WHERE
      (labels ->> '$.type') = 'ap'
      AND type = 0
      AND name IN (
        'resource_manager_resource_unit_read_request_unit_sum',
        'resource_manager_resource_unit_write_request_unit_sum'
      )
  )
GROUP BY
  keyspace_id`

	tidbRUV2SQL = `SELECT
  keyspace_id,
  sum(
    if(incr < 0, 0, incr)
  ) AS value
FROM
  (
    SELECT
      labels ->> '$.name' as keyspace_id,
      value - lag(value) OVER (
        PARTITION BY (name, labels)
        ORDER BY
          timestamp
      ) AS incr
    FROM
      %s
    WHERE
      ((labels ->> '$.type') = 'tp' OR (labels ->> '$.type') IS NULL)
      AND type = 0
      AND name = 'resource_manager_resource_unit_request_unit_v2_sum'
      AND fmod(TRY_CAST((labels ->> '$.name') AS INTEGER), %d) = %d
  )
GROUP BY
  keyspace_id`

	tiflashRUV2SQL = `SELECT
  keyspace_id,
  sum(
    if(incr < 0, 0, incr)
  ) AS value
FROM
  (
    SELECT
      labels ->> '$.name' as keyspace_id,
      value - lag(value) OVER (
        PARTITION BY (name, labels)
        ORDER BY
          timestamp
      ) AS incr
    FROM
      %s
    WHERE
      (labels ->> '$.type') = 'ap'
      AND type = 0
      AND name = 'resource_manager_resource_unit_request_unit_v2_sum'
  )
GROUP BY
  keyspace_id`

	tikvStorageMediumSQL = `SELECT
  keyspace_id,
  sum(value) * 5 AS value
FROM
  (
    SELECT
      avg(value) AS value,
      segment,
      labels ->> '$.keyspace_id' AS keyspace_id,
      labels ->> '$.region_id' AS region_id
    FROM
      (
        SELECT
          quantile_disc(value, 0.5) AS value,
          labels,
          FLOOR(timestamp / 5 / 60) AS segment
        FROM
          %s
        WHERE
          name = 'tikv_store_size_bytes'
          AND type = 1
          AND (labels ->> '$.type') = 'used'
          AND ((labels ->> '$.storage_class') = 'standard' OR (labels ->> '$.storage_class') IS NULL)
          AND fmod(TRY_CAST((labels ->> '$.keyspace_id') AS INTEGER), %d) = %d
        GROUP BY
          segment,
          labels
      )
    GROUP BY
      segment,
      labels ->> '$.keyspace_id',
      labels ->> '$.region_id'
  )
GROUP BY
  keyspace_id
HAVING
  sum(value) > 40.0 * 1024 * 1024 * 60 / 5
  AND sum(value) < 1024.0 * 1024 * 1024 * 60 / 5`

	tiflashStorageMediumSQL = `SELECT
  keyspace_id,
  sum(value) * 5 AS value
FROM
  (
    SELECT
      avg(value) AS value,
      segment,
      labels ->> '$.keyspace_id' AS keyspace_id,
      labels ->> '$.region_id' AS region_id
    FROM
      (
        SELECT
          quantile_disc(value, 0.5) AS value,
          labels,
          FLOOR(timestamp / 5 / 60) AS segment
        FROM
          %s
        WHERE
          name = 'tikv_store_size_bytes'
          AND type = 1
          AND (labels ->> '$.type') = 'tiflash_used'
        GROUP BY
          segment,
          labels
      )
    GROUP BY
      segment,
      labels ->> '$.keyspace_id',
      labels ->> '$.region_id'
  )
GROUP BY
  keyspace_id
HAVING
  sum(value) > 40.0 * 1024 * 1024 * 60 / 5
  AND sum(value) < 1024.0 * 1024 * 1024 * 60 / 5`

	tikvStorageLargeSQL = `SELECT
  keyspace_id,
  sum(value) * 5 AS value
FROM
  (
    SELECT
      avg(value) AS value,
      segment,
      labels ->> '$.keyspace_id' AS keyspace_id,
      labels ->> '$.region_id' AS region_id
    FROM
      (
        SELECT
          quantile_disc(value, 0.5) AS value,
          labels,
          FLOOR(timestamp / 5 / 60) AS segment
        FROM
          %s
        WHERE
          name = 'tikv_store_size_bytes'
          AND type = 1
          AND (labels ->> '$.type') = 'used'
          AND ((labels ->> '$.storage_class') = 'standard' OR (labels ->> '$.storage_class') IS NULL)
          AND fmod(TRY_CAST((labels ->> '$.keyspace_id') AS INTEGER), %d) = %d
        GROUP BY
          segment,
          labels
      )
    GROUP BY
      segment,
      labels ->> '$.keyspace_id',
      labels ->> '$.region_id'
  )
GROUP BY
  keyspace_id
HAVING
  sum(value) >= 1024.0 * 1024 * 1024 * 60 / 5`

	tikvStorageIAMediumSQL = `SELECT
  keyspace_id,
  sum(value) * 5 AS value
FROM
  (
    SELECT
      avg(value) AS value,
      segment,
      labels ->> '$.keyspace_id' AS keyspace_id,
      labels ->> '$.region_id' AS region_id
    FROM
      (
        SELECT
          quantile_disc(value, 0.5) AS value,
          labels,
          FLOOR(timestamp / 5 / 60) AS segment
        FROM
          %s
        WHERE
          name = 'tikv_store_size_bytes'
          AND type = 1
          AND (labels ->> '$.type') = 'used'
          AND (labels ->> '$.storage_class') = 'ia'
          AND fmod(TRY_CAST((labels ->> '$.keyspace_id') AS INTEGER), %d) = %d
        GROUP BY
          segment,
          labels
      )
    GROUP BY
      segment,
      labels ->> '$.keyspace_id',
      labels ->> '$.region_id'
  )
GROUP BY
  keyspace_id
HAVING
  sum(value) > 40.0 * 1024 * 1024 * 60 / 5
  AND sum(value) < 1024.0 * 1024 * 1024 * 60 / 5`

	tikvStorageIALargeSQL = `SELECT
  keyspace_id,
  sum(value) * 5 AS value
FROM
  (
    SELECT
      avg(value) AS value,
      segment,
      labels ->> '$.keyspace_id' AS keyspace_id,
      labels ->> '$.region_id' AS region_id
    FROM
      (
        SELECT
          quantile_disc(value, 0.5) AS value,
          labels,
          FLOOR(timestamp / 5 / 60) AS segment
        FROM
          %s
        WHERE
          name = 'tikv_store_size_bytes'
          AND type = 1
          AND (labels ->> '$.type') = 'used'
          AND (labels ->> '$.storage_class') = 'ia'
          AND fmod(TRY_CAST((labels ->> '$.keyspace_id') AS INTEGER), %d) = %d
        GROUP BY
          segment,
          labels
      )
    GROUP BY
      segment,
      labels ->> '$.keyspace_id',
      labels ->> '$.region_id'
  )
GROUP BY
  keyspace_id
HAVING
  sum(value) >= 1024.0 * 1024 * 1024 * 60 / 5`

	tiflashStorageLargeSQL = `SELECT
  keyspace_id,
  sum(value) * 5 AS value
FROM
  (
    SELECT
      avg(value) AS value,
      segment,
      labels ->> '$.keyspace_id' AS keyspace_id,
      labels ->> '$.region_id' AS region_id
    FROM
      (
        SELECT
          quantile_disc(value, 0.5) AS value,
          labels,
          FLOOR(timestamp / 5 / 60) AS segment
        FROM
          %s
        WHERE
          name = 'tikv_store_size_bytes'
          AND type = 1
          AND (labels ->> '$.type') = 'tiflash_used'
        GROUP BY
          segment,
          labels
      )
    GROUP BY
      segment,
      labels ->> '$.keyspace_id',
      labels ->> '$.region_id'
  )
GROUP BY
  keyspace_id
HAVING
  sum(value) >= 1024.0 * 1024 * 1024 * 60 / 5`

	rcuSQL = `SELECT
  keyspace_id,
  sum(value) * 5 AS value
FROM
  (
    SELECT
      avg(value) AS value,
      floor(timestamp / 60 / 5) AS segment,
      (labels ->> '$.keyspace_id') AS keyspace_id
    FROM
      %s
    WHERE
      name = 'serverless_metering_rcu_provisioned_capacity'
    GROUP BY
      segment,
      keyspace_id
  )
GROUP BY
  keyspace_id`

	execCountSQL = `SELECT
  db,
  cluster_id,
  sum(if(incr < 0, 0, incr)) AS value
FROM
  (
    SELECT
      labels ->> '$.db' AS db,
      labels ->> '$.cluster_id' AS cluster_id,
      value - lag(value) OVER (
        PARTITION BY
          (labels)
        ORDER BY
          timestamp
      ) AS incr
    FROM
      %s
    WHERE
      name = 'tidb_executor_statement_total'
  )
WHERE
  db IS NOT NULL
  AND db != ''
GROUP BY
  db,
  cluster_id`
)

// manage load data tasks, ensure it wouldn't be calcuated multiple times.
const (
	createLoadDataTaskSQL = `CREATE TABLE IF NOT EXISTS load_data_task (
  task_id varchar,
  timestamp int64,
  PRIMARY KEY (task_id)
)`

	importLoadDataTaskSQL = `INSERT INTO
  load_data_task (
    SELECT
      task_id,
      timestamp
    FROM
      '%s'
    WHERE
      timestamp > epoch (current_localtimestamp () - INTERVAL 2 DAY)
  ) ON CONFLICT DO NOTHING`

	writeLoadDataTaskSQL = `INSERT INTO
  load_data_task (
    SELECT
      labels ->> '$.task_id',
      min(timestamp)
    FROM
      %s
    WHERE
      type = 0
      AND name = 'tikv_worker_load_data_wru_cost_counter'
    GROUP BY
      labels ->> '$.task_id'
  ) ON CONFLICT DO NOTHING`

	exportLoadDataTaskSQL = `COPY load_data_task TO '%s' (FORMAT PARQUET)`
)