# Application configuration
env: "prod"
day : "20250822"
feishu:
  # feishu documentID is obj_token ,get from https://open.feishu.cn/document/server-docs/docs/wiki-v2/space-node/get_node
  appID: "cli_a8c5690eb1b6500c"
  documentUrl: "https://pingcap.feishu.cn/wiki/AF89wTvCUi3Hgjksr0vcUROdnoc"

  # Production document and production group
  documentID: "Row7d5SPwo6ifmx8tIPcyKDUnoL"
  receiveId: "oc_f7fe2ece646ca48f677514201fd991ce"
  # Card template configuration
  cardTemplate:
    templateID: "AAqIt09BLX3VJ"
    templateVersionName: "1.0.13"

# S3 configuration
s3:
  # Auth URL for S3
  auth_url: "https://www.ds.us-west-2.aws.observability.tidbcloud.com/client/auth"

  # Whether to enable SSL (default true)
  disable_ssl: false
  # AWS region
  region: "us-east-1"
  # S3 bucket name o11y-prod-shared-us-east-1
  bucket: "o11y-prod-shared"
  # Log file prefix path
  prefix: "diagnosis/data/1379661944646416027/slowlog-merged/1h/20250701"
  # Custom endpoint (optional, for S3 compatible storage services)
  endpoint: ""
  # Whether to force path style (optional)
  force_path_style: false
  

# Parsing configuration
parser:
  # Batch size
  batch_size: 1500
  # Number of concurrent processes
  concurrency: 5
  # Supported file formats
  supported_formats:
    - "json"

# Directory for input JSON files
inputDir: "./s3-file/slowquery-check"
TikvInputDir: "./s3-file/tikv-check"
# Directory for output result files
resultDir: "./result"
# Directory for log files (leave empty to use console output only)
logDir: "./logs"

# Database configuration
database:
  # Path to the SQLite database file
  dbPath: "../db-file/slowquery-check"
  tikvDbPath: "../db-file/tikv-check"
  # Name of the pattern column
  patternName: "pattern"
  # MySQL configuration for storing analysis results
  mysql:
    host: "gateway01.us-west-2.prod.aws.tidbcloud.com"
    port: 4000
    user: "2dShg4b2TMDP4Sk.root"
    database: "slowquery"
    charset: "utf8mb4"


# TiDB Clinic Dashboard configuration
clinic:
  # Base URL for TiDB Clinic
  baseUrl: "https://clinic.pingcap.com/portal/dashboard/cloud/ngm.html"
  clusterMetricsTemplateUrl: "https://www.ds.%s.%s.observability.tidbcloud.com"
  topSqlUrl: "https://clinic.pingcap.com/portal/dashboard/cloud/ngm.html?provider=%s&region=%s&orgId=%s&projectId=%s&clusterId=%s&clusterName=%s&deployType=dedicated#/topsql?from=%d&to=%d"
  o11yGlobalServerUrl: "http://www.gs.us-west-2.aws.observability.tidbcloud.com"

o11y:
  auth0TokenUrl: "https://tidb-soc2.us.auth0.com/oauth/token"
  auth0ClientId: "${CONFIG_O11Y_AUTH0CLIENTID}"
  auth0ClientSecret: "${CONFIG_O11Y_AUTH0CLIENTSECRET}"
  auth0Audience: "https://tidb-soc2.us.auth0.com/api/v2/"
  o11yMetadataBase: "http://www.gs.us-west-2.aws.observability.tidbcloud.com/api/v1/clusters"

queries:
  HugeResourceUsage:
    level: "Critical"
    order: 1
    patternName: "HugeResourceUsage"
    sql: |
      SELECT
        a.Digest,
        CAST(AVG(a.Mem_max) AS DECIMAL(10)) AS AVGMemMax,
        MAX(a.Mem_max) AS MAXMem,
        AVG(a.Query_time) AS AVGQueryTime,
        MAX(a.Query_time) AS MAXQueryTime,
        AVG(IFNULL(a.Request_unit_read, 0) + IFNULL(a.Request_unit_write, 0) )  AS AvgRU,
        IFNULL(SUM(a.Request_unit_read), 0) + IFNULL(SUM(a.Request_unit_write), 0) AS SumRU,
        COUNT(CASE WHEN a.Pattern_Name = 'HugeResourceUsage' THEN 1 END) AS TotalCount
      FROM `{{.Day}}` a
      WHERE EXISTS (
          SELECT 1
          FROM `{{.Day}}` b
          WHERE b.Pattern_Name = 'HugeResourceUsage'
          AND b.Digest = a.Digest
      )
      GROUP BY a.Digest
      HAVING TotalCount >= 10
      ORDER BY TotalCount DESC
      LIMIT 20;

  # SQLPlanChanged:
  #   level: "Critical"
  #   order: 2
  #   patternName: "SQLPlanChanged"
  #   sql: |
  #     SELECT
  #       a.Digest AS Digest,
  #       CAST(AVG(a.Mem_max) AS DECIMAL(10)) AS AVGMemMax,
  #       MAX(a.Mem_max) AS MAXMem,
  #       AVG(a.Query_time) AS AVGQueryTime,
  #       MAX(a.Query_time) AS MAXQueryTime,
  #       AVG(IFNULL(a.Request_unit_read, 0) + IFNULL(a.Request_unit_write, 0) )  AS AvgRU,
  #       IFNULL(SUM(a.Request_unit_read), 0) + IFNULL(SUM(a.Request_unit_write), 0) AS SumRU,
  #       count(*) AS TotalCount
  #     FROM
  #       (
  #       SELECT
  #       	Cluster_Id,
  #         Query,
  #         Digest,
  #         Binary_plan_parsed,
  #         DB,
  #         Formatted_Timestamp,
  #         Index_names,
  #         Mem_max,
  #         Query_time,
  #         Stats,
  #         Warnings,
  #         Request_unit_read,
  #         Request_unit_write
  #       FROM
  #         `{{.Day}}`
  #       WHERE
  #         Digest IN (
  #           select
  #             Digest
  #           from
  #             `{{.Day}}`
  #           Group by
  #               Cluster_Id, Digest
  #           HAVING COUNT(DISTINCT `Plan`) > 1
  #         )
  #      ) AS a
  #     GROUP BY
  #       Digest 
  #     HAVING TotalCount >= 10
  #     ORDER BY
  #       TotalCount DESC LIMIT 20
  #   countSql: |
  #     SELECT  
  #       SUM(CASE WHEN cnt >= 10 THEN 1 ELSE 0 END) AS count_ge_10,
  #       SUM(CASE WHEN cnt < 10 THEN 1 ELSE 0 END) AS count_lt_10,
  #       SUM(cnt) AS total_occurrences,
  #       COUNT(*) AS total_distinct_digests
  #     FROM (
  #       select Digest , COUNT(*) AS cnt
  #       FROM  `{{.Day}}`
  #       Group by Cluster_Id, Digest
  #       HAVING COUNT(DISTINCT `Plan`) > 1
  #     ) t

  Failed:
    level: "Critical"
    order: 3
    patternName: "Failed"
    sql: |
      SELECT
        a.Digest,
        CAST(AVG(a.Mem_max) AS DECIMAL(10)) AS AVGMemMax,
        MAX(a.Mem_max) AS MAXMem,
        AVG(a.Query_time) AS AVGQueryTime,
        MAX(a.Query_time) AS MAXQueryTime,
        AVG(IFNULL(a.Request_unit_read, 0) + IFNULL(a.Request_unit_write, 0) )  AS AvgRU,
        IFNULL(SUM(a.Request_unit_read), 0) + IFNULL(SUM(a.Request_unit_write), 0) AS SumRU,
        COUNT(CASE WHEN a.Pattern_Name = 'Failed' THEN 1 END) AS TotalCount
      FROM `{{.Day}}` a
      WHERE EXISTS (
          SELECT 1
          FROM `{{.Day}}` b
          WHERE b.Pattern_Name = 'Failed'
          AND b.Digest = a.Digest
      )
      GROUP BY a.Digest
      HAVING TotalCount >= 10
      ORDER BY TotalCount DESC
      LIMIT 20;
      
  TableFullScan:
    level: "Major"
    order: 4
    patternName: "TableFullScan"
  IndexFullScan:
    level: "Major"
    order: 5
    patternName: "IndexFullScan"
  DeepOffset:
    level: "Major"
    order: 6
    patternName: "DeepOffset"
  ExcessiveCopTask:
    level: "Major"
    order: 7
    patternName: "ExcessiveCopTask"
  StatsMissing:
    level: "Major"
    order: 8
    patternName: "StatsMissing"
  TiflashScanRegionImbalance:
    level: "Major"
    order: 9
    patternName: "StatsMissing"
  SendResponseSlow:
    level: "Major"
    order: 10
    patternName: "SendResponseSlow"

commonSql:
  countSql: |
    SELECT 
      SUM(CASE WHEN total_cnt >= 10 THEN 1 ELSE 0 END) AS count_ge_10,
      SUM(total_cnt) AS total_occurrences,
      COUNT(*) AS total_distinct_digests
    FROM (
        SELECT 
            Digest,
            COUNT(CASE WHEN Pattern_Name = '{{.PatternName}}' THEN 1 END) AS total_cnt
        FROM `{{.Day}}`
        WHERE Digest IN (
            SELECT DISTINCT Digest
            FROM `{{.Day}}`
            WHERE Pattern_Name = '{{.PatternName}}'
        )
        GROUP BY Digest
    )
    
queryProxy:
  host: "http://jumpserver-alb-ec2-1371391278.us-west-2.elb.amazonaws.com:81"
  path: "/slowquery/o11y-query"