CREATE TABLE `ci_data.Releases_new` (
  Release STRING(128) NOT NULL,
  Major INT64 NOT NULL,
  Minor INT64 NOT NULL,
  Patch INT64,
  Product STRING,
  GADate DATE,
  DevelStartDate DATE,
  EOLDate DATE,
  ReleaseStatus STRING,
  PreviousRelease STRING(128),
  Capabilities ARRAY<STRING>
);

insert into `ci_data.Releases_new` (
  Release,
  Major,
  Minor,
  Patch,
  Product,
  GADate,
  DevelStartDate,
  EOLDate,
  ReleaseStatus,
  Capabilities
) select 
  Release,
  Major,
  Minor,
  Patch,
  Product,
  GADate,
  DevelStartDate,
  EOLDate,
  ReleaseStatus,
  ARRAY<STRING>[
    'componentReadiness',
    'sippyClassic',
    'metrics',
    -- no pullRequests
    'featureGates',
    'payloadTags'
  ]
from `ci_data.Releases`

UPDATE
  `ci_data.Releases_new` AS current_release
SET
  current_release.PreviousRelease = previous_release.Release
FROM
  `ci_data.Releases_new` AS previous_release
WHERE
  current_release.Major = previous_release.Major
  AND
  current_release.Minor = previous_release.Minor + 1;

UPDATE `ci_data.Releases_new`
SET Capabilities= ARRAY<STRING>['sippyClassic']
WHERE Release = '3.11';


ALTER TABLE `ci_data.Releases`
RENAME TO Releases_old;
ALTER TABLE `ci_data.Releases_new`
RENAME TO Releases;



update BackendDisruptionPercentilesDeltaCurrentVsPrevGAV2 query:
/* This view query compares the latest disruption report for current dev release with a 3 day lookback,
to previous GA release with a 30 day lookback. The view should dynamically adapt as soon as the Releases table
row gets updated with a GADate, this view will use the latest. 

This view contains data for every release above 4.14 compared to latest GA, including that release itself.
*/

WITH latest AS (
  SELECT
    ReportDate,
    TIMESTAMP(ReportDate) as time,
    BackendName,
    Platform,
    Release,
    UpgradeType,
    MasterNodesUpdated,
    Network,
    Topology,
    IPMode,
    P10,
    P15,
    P20,
    P25,
    P30,
    P35,
    P40,
    P45,
    P50,
    P55,
    P60,
    P65,
    P70,
    P75,
    P80,
    P85,
    P90,
    P95,
    P99,
    PercentageAboveZero,
    Architecture,
    Relevance,
    JobRuns,
    LookbackDays,
    FeatureSet
  FROM
    `openshift-ci-data-analysis.ci_data.BackendDisruptionPercentilesByDateWithRelevanceV2`
  WHERE
    /* omit releases prior to latest major disruption work, they won't compare cleanly */
    Release NOT IN ('4.10', '4.11', '4.12', '4.13') 
    AND Platform IN (
      'aws', 'azure', 'gcp', 'metal', 
      'vsphere'
    )
    AND Architecture IN ('amd64')
    AND Network IN ('sdn', 'ovn')
    AND Topology IN ('ha', 'external')
    AND UpgradeType IN ('minor', 'micro', 'none')
    AND ReportDate = (
      SELECT
        MAX(ReportDate)
      FROM
        `openshift-ci-data-analysis.ci_data.BackendDisruptionPercentilesByDateWithRelevanceV2`
    )
),
historical AS (
  SELECT
    BackendName,
    Platform,
    Release,
    UpgradeType,
    MasterNodesUpdated,
    Network,
    Topology,
    IPMode,
    P10,
    P15,
    P20,
    P25,
    P30,
    P35,
    P40,
    P45,
    P50,
    P55,
    P60,
    P65,
    P70,
    P75,
    P80,
    P85,
    P90,
    P95,
    P99,
    PercentageAboveZero,
    Architecture,
    Relevance,
    FeatureSet
  FROM
    `openshift-ci-data-analysis.ci_data.BackendDisruptionPercentilesByDateWithRelevanceV2`
  WHERE
    Release = (
      SELECT Release
      FROM `openshift-ci-data-analysis.ci_data.Releases`
      WHERE 'metrics' IN UNNEST(Capabilities)
      ORDER BY GADate DESC LIMIT 1)
    AND ReportDate = (SELECT GADate FROM `openshift-ci-data-analysis.ci_data.Releases` ORDER BY GADate DESC LIMIT 1)
    AND LookbackDays = 30
    AND Platform IN (
      'aws', 'azure', 'gcp', 'metal', 
      'vsphere'
    )
    AND Architecture IN ('amd64')
    AND Network IN ('sdn', 'ovn')
    AND Topology IN ('ha', 'external')
    AND UpgradeType IN ('minor', 'micro', 'none')
)
SELECT
  r.LookbackDays,
  r.ReportDate,
  r.Relevance,
  /* The new column names to be kept */
  ROUND(r.P10 - h.P10, 2) AS P10,
  ROUND(r.P15 - h.P15, 2) AS P15,
  ROUND(r.P20 - h.P20, 2) AS P20,
  ROUND(r.P25 - h.P25, 2) AS P25,
  ROUND(r.P30 - h.P30, 2) AS P30,
  ROUND(r.P35 - h.P35, 2) AS P35,
  ROUND(r.P40 - h.P40, 2) AS P40,
  ROUND(r.P45 - h.P45, 2) AS P45,
  ROUND(r.P50 - h.P50, 2) AS P50,
  ROUND(r.P55 - h.P55, 2) AS P55,
  ROUND(r.P60 - h.P60, 2) AS P60,
  ROUND(r.P65 - h.P65, 2) AS P65,
  ROUND(r.P70 - h.P70, 2) AS P70,
  ROUND(r.P75 - h.P75, 2) AS P75,
  ROUND(r.P80 - h.P80, 2) AS P80,
  ROUND(r.P85 - h.P85, 2) AS P85,
  ROUND(r.P90 - h.P90, 2) AS P90,
  ROUND(r.P95 - h.P95, 2) AS P95,
  ROUND(r.P99 - h.P99, 2) AS P99,
  ROUND(r.PercentageAboveZero - h.PercentageAboveZero) AS PercentageAboveZeroDelta,
  r.Release,
  h.Release AS CompareRelease,
  r.BackendName,
  r.Platform,
  r.UpgradeType,
  r.MasterNodesUpdated,
  r.Network,
  r.Topology,
  r.IPMode,
  r.Architecture,
  r.JobRuns,
  r.FeatureSet
FROM
  latest r
  JOIN historical h ON r.BackendName = h.BackendName
  AND r.Platform = h.Platform
  AND r.UpgradeType = h.UpgradeType
  AND r.MasterNodesUpdated = h.MasterNodesUpdated
  AND r.Network = h.Network
  AND r.Topology = h.Topology
  AND r.Architecture = h.Architecture
  AND r.IPMode = h.IPMode
;

insert into `ci_data.Releases` (
  Release,
  Major,
  Minor,  
  Product,
  DevelStartDate,
  GADate, 
  EOLDate,
  ReleaseStatus,
  PreviousRelease,
  Capabilities
) values (
  'Presubmits',
  4, 0,
  'OCP',
  '2019-01-01', null, null, null, null,  -- DevelStartDate required; no dates make sense
  ARRAY<STRING>[
    'sippyClassic',
    'pullRequests'
    -- no metrics
    -- no featureGates
    -- no payloadTags     
  ]
),(
  'aro-integration',
  4, 0,
  'HCM',
  '2020-02-02', null, null, null, null,  -- DevelStartDate required; no dates make sense
  ARRAY<STRING>[
    'sippyClassic'
    -- no metrics
    -- no pullRequests
    -- no featureGates
    -- no payloadTags     
  ]
);