#standardSQL
  -- Copyright 2021 The Nomulus Authors. All Rights Reserved.
  --
  -- Licensed under the Apache License, Version 2.0 (the "License");
  -- you may not use this file except in compliance with the License.
  -- You may obtain a copy of the License at
  --
  --     http://www.apache.org/licenses/LICENSE-2.0
  --
  -- Unless required by applicable law or agreed to in writing, software
  -- distributed under the License is distributed on an "AS IS" BASIS,
  -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  -- See the License for the specific language governing permissions and
  -- limitations under the License.

  -- Determine the number of attempted adds each registrar made.

  -- Since the specification requests all 'attempted' adds, we regex the
  -- monthly GKE logs, searching for all create commands and associating
  -- them with their corresponding registrars.

  -- Example log generated by FlowReporter in GKE logs:
  --google.registry.flows.FlowReporter
  -- recordToLogs: FLOW-LOG-SIGNATURE-METADATA:
  --{"serverTrid":"oNwL2J2eRya7bh7c9oHIzg==-2360a","clientId":"ipmirror"
  -- ,"commandType":"hello", "resourceType":"","flowClassName":"HelloFlow"
  -- ,"targetId":"","targetIds":[],"tld":"",
  -- "tlds":[],"icannActivityReportField":""}

-- This outer select just converts the registrar's ID to their name.
SELECT
  tld,
  registrar_table.registrar_name AS registrar_name,
  'ATTEMPTED_ADDS' AS metricName,
  count AS metricValue
FROM (
  SELECT
    JSON_EXTRACT_SCALAR(json, '$.tld') AS tld,
    JSON_EXTRACT_SCALAR(json, '$.clientId') AS clientId,
    COUNT(json) AS count
  FROM (
    -- Extract JSON metadata package from monthly logs
    SELECT
      REGEXP_EXTRACT(jsonPayload.message, r'FLOW-LOG-SIGNATURE-METADATA: (.*)\n?$') AS json
    FROM
      `domain-registry-alpha.gke_logs.stderr_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20170901'
      AND '20170930'
      AND STARTS_WITH(jsonPayload.message, "FLOW-LOG-SIGNATURE-METADATA")
      -- Look for domain creates
      AND REGEXP_CONTAINS(jsonPayload.message, r'"commandType":"create","resourceType":"domain"')
      -- Filter prober data
      AND NOT REGEXP_CONTAINS(jsonPayload.message, r'"prober-[a-z]{2}-((any)|(canary))"'))
  GROUP BY
    tld,
    clientId ) AS logs_table
JOIN
  EXTERNAL_QUERY("projects/domain-registry-alpha/locations/us/connections/domain-registry-alpha-sql",
    '''SELECT registrar_id, registrar_name FROM "Registrar";''') AS registrar_table
ON
  logs_table.clientId = registrar_table.registrar_id
ORDER BY
  tld,
  registrar_name
