#!/bin/bash
# Copyright 2020 The Chromium OS Authors. All rights reserved.
# Use of this source code is governed by a BSD-style license that can be
# found in the LICENSE file.

# A helper tool to query for relative rootfs size differences detected during
# CQ runs.

CL="$1"

[[ -z "$CL" ]] && {
  echo "You must supply a CL."
  exit 1
}

prodcertstatus &>/dev/null || prodaccess

f1-sql \
  -csv_output=1 \
  << SQLtoHERE
-- Note that some data is missing from the chromeos_ci_eng.buildbucket_builds
-- table. https://crbug.com/1038752 is tracking that issue.
-- select * from chromeos_ci_eng.buildbucket_builds where id = 8892492747888114240;
WITH bb_ids AS (
  SELECT
    bb.id as ids
  FROM
    chromeos_ci_eng.buildbucket_builds AS bb
  CROSS JOIN
    UNNEST(input.gerrit_changes) AS changes
  WHERE
    changes.change=${CL}
)

, builds AS (
  SELECT
    bb.id AS bb_id,
    bb.builder,
    bb.input.gitiles_commit.id AS snapshot_hash,
    JSON_EXTRACT_SCALAR(bb.input.properties, '$.build_target.name') AS build_target,
    ARRAY_TO_STRING(ARRAY(SELECT
                            CONCAT('https://', c.host, '/c/', c.project, '/+/',
                                   CAST(c.change AS STRING), '/',
                                   CAST(c.patchset AS STRING))
                          FROM
                            UNNEST(bb.input.gerrit_changes) AS c), ' ') as patches

  FROM
    chromeos_ci_eng.buildbucket_builds AS bb

  WHERE
    bb.id IN (SELECT ids from bb_ids)
)

SELECT
  build_target,
  patches AS patch_urls,
  snapshot_hash,
  current_base_rootfs_size - baseline_base_rootfs_size AS rootfs_size_diff,
  current_build_id AS build_id,
  current_base_rootfs_size,
  baseline_build_id,
  baseline_base_rootfs_size
FROM (
  SELECT
    build_target,
    current_build_id,
    patches,
    snapshot_hash,
    e1.gauge AS current_base_rootfs_size,
    baseline_build_id,
    e2.gauge AS baseline_base_rootfs_size
  FROM (
    SELECT
      builds.build_target,
      builds.bb_id AS current_build_id,
      builds.patches AS patches,
      builds.snapshot_hash AS snapshot_hash,
      bb_parent.id AS baseline_build_id
    FROM
      builds
    JOIN
      chromeos_ci_eng.buildbucket_builds AS bb_parent
    ON
      bb_parent.input.gitiles_commit.id = builds.snapshot_hash AND
      bb_parent.builder.builder LIKE CONCAT(builds.build_target, '-snapshot') AND
      JSON_EXTRACT_SCALAR(bb_parent.input.properties, '$.build_target.name') = builds.build_target
  )
  JOIN
    chromeos_ci_eng.analysis_event_log.\`all\` AS ael1
  ON
    ael1.build_id = current_build_id
  CROSS JOIN
    UNNEST(ael1.create_image_result.events) AS e1
  JOIN
    chromeos_ci_eng.analysis_event_log.\`all\` AS ael2
  ON
    ael2.build_id = baseline_build_id
  CROSS JOIN
    UNNEST(ael2.create_image_result.events) AS e2
  WHERE
    e1.name LIKE '%.total_size.%' AND
    e2.name LIKE '%.total_size.%'
  )
WHERE
  build_target IS NOT NULL AND
  current_base_rootfs_size IS NOT NULL AND
  baseline_base_rootfs_size IS NOT NULL
ORDER BY
  rootfs_size_diff DESC;
SQLtoHERE