--
-- Copyright 2023 The Android Open Source Project
--
-- 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
--
--     https://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.

SELECT IMPORT('common.timestamps');

-- Converts a battery_stats counter value to human readable string.
--
-- @arg track STRING  The counter track name (e.g. 'battery_stats.audio').
-- @arg value LONG    The counter value.
-- @ret STRING        The human-readable name for the counter value.
SELECT CREATE_FUNCTION(
  'BATTERY_STATS_COUNTER_TO_STRING(track STRING, value FLOAT)',
  'STRING',
  '
  SELECT
    CASE
      WHEN ($track = "battery_stats.wifi_scan" OR
            $track = "battery_stats.wifi_radio" OR
            $track = "battery_stats.mobile_radio" OR
            $track = "battery_stats.audio" OR
            $track = "battery_stats.video" OR
            $track = "battery_stats.camera" OR
            $track = "battery_stats.power_save" OR
            $track = "battery_stats.phone_in_call")
        THEN
          CASE $value
            WHEN 0 THEN "inactive"
            WHEN 1 THEN "active"
            ELSE "unknown"
          END
      WHEN $track = "battery_stats.wifi"
        THEN
          CASE $value
            WHEN 0 THEN "off"
            WHEN 1 THEN "on"
            ELSE "unknown"
          END
      WHEN $track = "battery_stats.phone_state"
        THEN
          CASE $value
            WHEN 0 THEN "in"
            WHEN 1 THEN "out"
            WHEN 2 THEN "emergency"
            WHEN 3 THEN "off"
            ELSE "unknown"
          END
      WHEN ($track = "battery_stats.phone_signal_strength" OR
            $track = "battery_stats.wifi_signal_strength")
        THEN
          CASE $value
            WHEN 0 THEN "none"
            WHEN 1 THEN "poor"
            WHEN 2 THEN "moderate"
            WHEN 3 THEN "good"
            WHEN 4 THEN "great"
            ELSE "unknown"
          END
      WHEN $track = "battery_stats.wifi_suppl"
        THEN
          CASE $value
            WHEN 0 THEN "invalid"
            WHEN 1 THEN "disconn"
            WHEN 2 THEN "disabled"
            WHEN 3 THEN "inactive"
            WHEN 4 THEN "scanning"
            WHEN 5 THEN "authenticating"
            WHEN 6 THEN "associating"
            WHEN 7 THEN "associated"
            WHEN 8 THEN "4-way-handshake"
            WHEN 9 THEN "group-handshake"
            WHEN 10 THEN "completed"
            WHEN 11 THEN "dormant"
            WHEN 12 THEN "uninit"
            ELSE "unknown"
          END
      WHEN $track = "battery_stats.data_conn"
        THEN
          CASE $value
            WHEN 0 THEN "oos"
            WHEN 1 THEN "gprs"
            WHEN 2 THEN "edge"
            WHEN 3 THEN "umts"
            WHEN 4 THEN "cdma"
            WHEN 5 THEN "evdo_0"
            WHEN 6 THEN "evdo_A"
            WHEN 7 THEN "1xrtt"
            WHEN 8 THEN "hsdpa"
            WHEN 9 THEN "hsupa"
            WHEN 10 THEN "hspa"
            WHEN 11 THEN "iden"
            WHEN 12 THEN "evdo_b"
            WHEN 13 THEN "lte"
            WHEN 14 THEN "ehrpd"
            WHEN 15 THEN "hspap"
            WHEN 16 THEN "gsm"
            WHEN 17 THEN "td_scdma"
            WHEN 18 THEN "iwlan"
            WHEN 19 THEN "lte_ca"
            WHEN 20 THEN "nr"
            WHEN 21 THEN "emngcy"
            WHEN 22 THEN "other"
            ELSE "unknown"
          END
      ELSE CAST($value AS text)
    END
  '
);


-- View of human readable battery stats counter-based states. These are recorded
-- by BatteryStats as a bitmap where each 'category' has a unique value at any
-- given time.
--
-- @column ts                  Timestamp in nanoseconds.
-- @column dur                 The duration the state was active.
-- @column track_name          The name of the counter track.
-- @column value               The counter value as a number.
-- @column value_name          The counter value as a human-readable string.
CREATE VIEW android_battery_stats_state AS
SELECT
  ts,
  name AS track_name,
  CAST(value AS INT64) AS value,
  BATTERY_STATS_COUNTER_TO_STRING(name, value) AS value_name,
  IFNULL(LEAD(ts) OVER (PARTITION BY track_id ORDER BY ts) - ts, -1) AS dur
FROM counter
JOIN counter_track
  ON counter.track_id = counter_track.id
WHERE counter_track.name GLOB 'battery_stats.*';


-- View of slices derived from battery_stats events. Battery stats records all
-- events as instants, however some may indicate whether something started or
-- stopped with a '+' or '-' prefix. Events such as jobs, top apps, foreground
-- apps or long wakes include these details and allow drawing slices between
-- instant events found in a trace.
--
-- For example, we may see an event like the following on 'battery_stats.top':
--
--     -top=10215:"com.google.android.apps.nexuslauncher"
--
-- This view will find the associated start ('+top') with the matching suffix
-- (everything after the '=') to construct a slice. It computes the timestamp
-- and duration from the events and extract the details as follows:
--
--     track_name='battery_stats.top'
--     str_value='com.google.android.apps.nexuslauncher'
--     int_value=10215
--
-- @column track_name          The battery stats track name.
-- @column ts                  Timestamp in nanoseconds.
-- @column dur                 The duration of the event.
-- @column str_value           The string part of the event identifier.
-- @column int_value           The integer part of the event identifier.
CREATE VIEW android_battery_stats_event_slices AS
WITH
  event_markers AS (
    SELECT
      ts,
      track.name AS track_name,
      str_split(slice.name, '=', 1) AS key,
      substr(slice.name, 1, 1) = '+' AS start
    FROM slice
    JOIN track
      ON slice.track_id = track.id
    WHERE
      track_name GLOB 'battery_stats.*'
      AND substr(slice.name, 1, 1) IN ('+', '-')
  ),
  with_neighbors AS (
    SELECT
      *,
      LAG(ts) OVER (PARTITION BY track_name, key ORDER BY ts) AS last_ts,
      LEAD(ts) OVER (PARTITION BY track_name, key ORDER BY ts) AS next_ts
    FROM event_markers
  ),
  -- Note: query performance depends on the ability to push down filters on
  -- the track_name. It would be more clear below to have two queries and union
  -- them, but doing so prevents push down through the above window functions.
  event_spans AS (
    SELECT
      track_name, key,
      IIF(start, ts, TRACE_START()) AS ts,
      IIF(start, next_ts, ts) AS end_ts
    FROM with_neighbors
    -- For the majority of events, we take the `start` event and compute the dur
    -- based on next_ts. In the off chance we get an end event with no prior
    -- start (matched by the second half of this where), we can create an event
    -- starting from the beginning of the trace ending at the current event.
    WHERE (start OR last_ts IS NULL)
  )
SELECT
  ts,
  IFNULL(end_ts-ts, -1) AS dur,
  track_name,
  str_split(key, '"', 1) AS str_value,
  CAST(str_split(key, ':', 0) AS INT64) AS int_value
FROM event_spans;