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

import 'dart:async';

import 'package:googleapis/bigquery/v2.dart';
import 'package:http/http.dart';

import 'access_client_provider.dart';

/// The sql query to query the build statistic from the
/// `flutter-dashboard.datasite.luci_prod_build_status`.
///
/// The schema of the `luci_prod_build_status` table:
/// time	            TIMESTAMP
/// date	            DATE
/// sha	              STRING
/// flaky_builds	    STRING
/// succeeded_builds	STRING
/// branch	          STRING
/// device_os       	STRING
/// pool	            STRING
/// repo	            STRING
/// builder_name	    STRING
/// success_count	    INTEGER
/// failure_count	    INTEGER
/// is_flaky	        INTEGER
///
/// This returns latest [LIMIT] number of build stats for each builder.
const String getBuilderStatisticQuery = r'''
select builder_name,
       sum(is_flaky) as flaky_number,
       count(*) as total_number,
       string_agg(case when is_flaky = 1 then flaky_builds end, ', ') as flaky_builds,
       string_agg(succeeded_builds, ', ') as succeeded_builds,
       array_agg(case when is_flaky = 1 then sha end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as recent_flaky_commit,
       array_agg(case when is_flaky = 1 then flaky_builds end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as flaky_build_of_recent_flaky_commit,
       sum(is_flaky)/count(*) as flaky_ratio
from (select *, row_number() over (partition by builder_name order by time desc) as rank from `flutter-dashboard.datasite.luci_prod_build_status`)
where date>=date_sub(current_date(), interval 30 day) and
      builder_name not like '%Drone' and
      repo='flutter' and
      branch='master' and
      pool = 'luci.flutter.prod' and
      builder_name not like '%Beta%' and
      builder_name not like '% beta %' and
      builder_name not like '%Stable%' and
      builder_name not like '% stable %' and
      builder_name not like '%Dev%' and
      builder_name not like '% dev %' and
      rank<=@LIMIT
group by builder_name;
''';

const String getStagingBuilderStatisticQuery = r'''
select builder_name,
       sum(is_flaky) as flaky_number,
       count(*) as total_number,
       string_agg(case when is_flaky = 1 then flaky_builds end, ', ') as flaky_builds,
       string_agg(succeeded_builds, ', ') as succeeded_builds,
       array_agg(case when is_flaky = 1 then sha end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as recent_flaky_commit,
       array_agg(case when is_flaky = 1 then flaky_builds end IGNORE NULLS ORDER BY date DESC)[ordinal(1)] as flaky_build_of_recent_flaky_commit,
       sum(is_flaky)/count(*) as flaky_ratio
from (select *, row_number() over (partition by builder_name order by time desc) as rank from `flutter-dashboard.datasite.luci_staging_build_status`)
where date>=date_sub(current_date(), interval 30 day) and
      builder_name not like '%Drone' and
      repo='flutter' and
      branch='master' and
      pool = 'luci.flutter.staging' and
      builder_name not like '%Beta%' and
      builder_name not like '% beta %' and
      rank<=@LIMIT
group by builder_name;
''';

// Returns builds in the past 30 days to exclude obsolete historical data.
const String getRecordsQuery = r'''
select sha, is_flaky, failure_count from `flutter-dashboard.datasite.luci_staging_build_status`
where builder_name=@BUILDER_NAME and date>=date_sub(current_date(), interval 30 day)
order by time desc
limit @LIMIT
''';

class BigqueryService {
  const BigqueryService(this.accessClientProvider);

  /// AccessClientProvider for OAuth 2.0 authenticated access client
  final AccessClientProvider accessClientProvider;

  /// Return a [TabledataResource] with an authenticated [client]
  Future<TabledataResource> defaultTabledata() async {
    final Client client = await accessClientProvider.createAccessClient(
      scopes: const <String>[BigqueryApi.bigqueryScope],
    );
    return BigqueryApi(client).tabledata;
  }

  /// Return a [JobsResource] with an authenticated [client]
  Future<JobsResource> defaultJobs() async {
    final Client client = await accessClientProvider.createAccessClient(
      scopes: const <String>[BigqueryApi.bigqueryScope],
    );
    return BigqueryApi(client).jobs;
  }

  /// Return the top [limit] number of current builder statistic.
  ///
  /// See getBuilderStatisticQuery to get the detail information about the table
  /// schema
  Future<List<BuilderStatistic>> listBuilderStatistic(
    String projectId, {
    int limit = 100,
    String bucket = 'prod',
  }) async {
    final JobsResource jobsResource = await defaultJobs();
    final QueryRequest query = QueryRequest.fromJson(<String, Object>{
      'query': bucket == 'staging' ? getStagingBuilderStatisticQuery : getBuilderStatisticQuery,
      'queryParameters': <Map<String, Object>>[
        <String, Object>{
          'name': 'LIMIT',
          'parameterType': <String, Object>{'type': 'INT64'},
          'parameterValue': <String, Object>{'value': '$limit'},
        },
      ],
      'useLegacySql': false,
    });
    final QueryResponse response = await jobsResource.query(query, projectId);
    if (!response.jobComplete!) {
      throw 'job does not complete';
    }
    final List<BuilderStatistic> result = <BuilderStatistic>[];
    for (final TableRow row in response.rows!) {
      final String builder = row.f![0].v as String;
      List<String>? flakyBuilds = (row.f![3].v as String?)?.split(', ');
      flakyBuilds?.sort();
      flakyBuilds = flakyBuilds?.reversed.toList();
      List<String>? succeededBuilds = (row.f![4].v as String?)?.split(', ');
      succeededBuilds?.sort();
      succeededBuilds = succeededBuilds?.reversed.toList();
      result.add(
        BuilderStatistic(
          name: builder,
          flakyRate: double.parse(row.f![7].v as String),
          flakyBuilds: flakyBuilds ?? const <String>[],
          succeededBuilds: succeededBuilds ?? const <String>[],
          recentCommit: row.f![5].v as String?,
          flakyBuildOfRecentCommit: row.f![6].v as String?,
          flakyNumber: int.parse(row.f![1].v as String),
          totalNumber: int.parse(row.f![2].v as String),
        ),
      );
    }
    return result;
  }

  /// Return the list of current builder statistic.
  ///
  /// See getBuilderStatisticQuery to get the detail information about the table
  /// schema
  Future<List<BuilderRecord>> listRecentBuildRecordsForBuilder(
    String projectId, {
    String? builder,
    int? limit,
  }) async {
    final JobsResource jobsResource = await defaultJobs();
    final QueryRequest query = QueryRequest.fromJson(<String, Object>{
      'query': getRecordsQuery,
      'parameterMode': 'NAMED',
      'queryParameters': <Map<String, Object>>[
        <String, Object>{
          'name': 'BUILDER_NAME',
          'parameterType': <String, Object>{'type': 'STRING'},
          'parameterValue': <String, Object?>{'value': builder},
        },
        <String, Object>{
          'name': 'LIMIT',
          'parameterType': <String, Object>{'type': 'INT64'},
          'parameterValue': <String, Object>{'value': '$limit'},
        },
      ],
      'useLegacySql': false
    });
    final QueryResponse response = await jobsResource.query(query, projectId);
    if (!response.jobComplete!) {
      throw 'job does not complete';
    }
    final List<BuilderRecord> result = <BuilderRecord>[];
    // When a test is newly marked as flaky, it is possible no execution exists.
    if (response.rows == null) {
      return result;
    }
    for (final TableRow row in response.rows!) {
      result.add(
        BuilderRecord(
          commit: row.f![0].v as String,
          isFlaky: row.f![1].v as String != '0',
          isFailed: row.f![2].v as String != '0',
        ),
      );
    }
    return result;
  }
}

class BuilderRecord {
  BuilderRecord({
    required this.commit,
    required this.isFlaky,
    required this.isFailed,
  });

  final String commit;
  final bool isFlaky;
  final bool isFailed;
}

class BuilderStatistic {
  BuilderStatistic({
    required this.name,
    required this.flakyRate,
    required this.flakyNumber,
    required this.totalNumber,
    this.flakyBuilds,
    this.succeededBuilds,
    this.recentCommit,
    this.flakyBuildOfRecentCommit,
  });

  final String name;
  final double flakyRate;
  final List<String>? flakyBuilds;
  final List<String>? succeededBuilds;
  final String? recentCommit;
  final String? flakyBuildOfRecentCommit;
  final int flakyNumber;
  final int totalNumber;
}