# frozen_string_literal: true

# each query should be a hash with the following keys:
# :name (required) - query name/identifier
# :title (optional) - title displayed on the page
# :description (optional) - description displayed bellow the title
# :query (required) - SQL query or a Proc
# :hidden (optional) - whether the output should be displayed on the page

SQL_QUERIES = [
  {
    name: 'latest_commit_stats_grouped_by_file',
    title: 'Specs grouped by filename ordered by total time (first 1000)',
    query: <<~SQL
            WITH last_commit AS
              (SELECT commit_hash
               FROM spec_profiling_results
               WHERE branch='ee:master'
               ORDER BY date DESC LIMIT 1)
            SELECT DISTINCT r.file,
                   sum(r.time) AS total_time,
                   sum(r.query_count) AS total_queries,
                   sum(r.query_time) AS total_query_time,
                   sum(r.request_count) AS total_requests
            FROM spec_profiling_results r,
                 last_commit lc
            WHERE r.commit_hash = lc.commit_hash
            AND branch='ee:master'
            GROUP BY r.file, r.branch, r.commit_hash
            ORDER BY total_time DESC
            LIMIT 1000;
    SQL
  }, {
    name: 'slowest_tests',
    title: 'Individual tests by time (first 1000)',
    query: <<~SQL
            WITH last_commit AS
              (SELECT commit_hash
               FROM spec_profiling_results
               WHERE branch='ee:master'
               ORDER BY date DESC LIMIT 1)
            SELECT DISTINCT concat(r.file, ':', r.line_number) AS LOCATION,
                   r.time,
                   r.query_count,
                   r.query_time,
                   r.request_count,
                   r.feature_category
            FROM spec_profiling_results r,
                 last_commit lc
            WHERE r.commit_hash = lc.commit_hash
            AND branch='ee:master'
            ORDER BY r.time DESC
            LIMIT 1000;
    SQL
  }, {
    name: 'query_count',
    title: 'Individual tests by query count (first 1000)',
    query: <<~SQL
            WITH last_commit AS
              (SELECT commit_hash
               FROM spec_profiling_results
               WHERE branch='ee:master'
               ORDER BY date DESC LIMIT 1)
            SELECT DISTINCT concat(r.file, ':', r.line_number) AS LOCATION,
                   r.time,
                   r.query_count,
                   r.query_time,
                   r.request_count,
                   r.feature_category
            FROM spec_profiling_results r,
                 last_commit lc
            WHERE r.commit_hash = lc.commit_hash
            AND branch='ee:master'
            ORDER BY r.query_count DESC
            LIMIT 1000;
    SQL
  }, {
    name: 'query_time',
    title: 'Individual tests by query time (first 1000)',
    query: <<~SQL
            WITH last_commit AS
              (SELECT commit_hash
               FROM spec_profiling_results
               WHERE branch='ee:master'
               ORDER BY date DESC LIMIT 1)
            SELECT DISTINCT concat(r.file, ':', r.line_number) AS LOCATION,
                   r.time,
                   r.query_count,
                   r.query_time,
                   r.request_count,
                   r.feature_category
            FROM spec_profiling_results r,
                 last_commit lc
            WHERE r.commit_hash = lc.commit_hash
            AND branch='ee:master'
            ORDER BY r.query_time DESC
            LIMIT 1000;
    SQL
  }, {
    name: 'overall_time',
    title: 'Overall time to run all tests',
    query: <<~SQL
            SELECT commit_hash,
                   min(created_at) as commit_time,
                   sum(time) AS total_time,
                   count(id) AS number_of_tests,
                   sum(time)/count(id) AS time_per_single_test,
                   sum(query_count) AS total_queries,
                   sum(query_time) AS total_query_time,
                   sum(request_count) AS total_requests,
                   date(min(created_at)) as commit_date
            FROM spec_profiling_results
            WHERE branch='ee:master'
              AND date > now() - interval '1 year'
            GROUP BY commit_hash
            ORDER BY commit_time DESC
            LIMIT 1000;
    SQL
  }, {
    # this query is not displayed but is used only as a base for
    # other queries/tables
    name: 'avg_time_per_file_per_day',
    hidden: true,
    query: <<~SQL
            SELECT
              file,
              date(created_at) AS day,
              sum(time) AS sum_time,
              avg(time) AS avg_time
            FROM spec_profiling_results
            WHERE date > now() - interval '7 days'
            GROUP BY file,day;
    SQL
  }, {
    name: 'avg_time_per_file_increase',
    title: 'Biggest slow downs per file (last 7 days, top 1000 by impact)',
    description: 'Shows biggest test slowdowns in the last 7 days sorted by default by pct_change_impact. pct_change_impact = percent change * runtime_weight. runtime_weight - overall time in secs how long the test ran that day (bigger = worse). pct_change_by_day shows percent change by sequence of last 7 days.',
    query: proc { |query| query.avg_change_increase }
  }, {
    name: 'avg_time_per_file_decrease',
    title: 'Biggest speed ups per file (last 7 days, top 1000 by impact)',
    description: 'Shows biggest test speed ups in the last 7 days sorted by default by pct_change_impact. pct_change_impact = percent change * runtime_weight. runtime_weight - overall time in secs how long the test ran that day (bigger = worse). pct_change_by_day shows percent change by sequence of last 7 days.',
    query: proc { |query| query.avg_change_decrease }
  }, {
    name: 'percentage_of_tests_with_feature_category',
    title: 'Percentage of RSpec tests having feature_category set',
    description: 'Percentage of RSpec tests having feature_category set',
    query: <<~SQL
            SELECT round(
              100.0 *
              COUNT(DISTINCT (CASE WHEN feature_category IS NOT NULL THEN concat(file, ':', line_number) END)) /
              COALESCE(NULLIF(COUNT(DISTINCT concat(file, ':', line_number)), 0), 1),
              2)
            AS percentage_of_tests_with_feature_category
            FROM spec_profiling_results
            WHERE date > now() - interval '7 days';
    SQL
  }, {
    name: 'slowest_features_tests',
    title: 'Individual feature tests by time (first 1000)',
    query: <<~SQL
            WITH last_commit AS
              (SELECT commit_hash
               FROM spec_profiling_results
               WHERE branch='ee:master'
               ORDER BY date DESC LIMIT 1)
            SELECT DISTINCT concat(r.file, ':', r.line_number) AS LOCATION,
                   r.time,
                   r.query_count,
                   r.query_time,
                   r.request_count,
                   r.feature_category
            FROM spec_profiling_results r,
                 last_commit lc
            WHERE r.commit_hash = lc.commit_hash AND r.file LIKE '%features%'
            AND branch='ee:master'
            ORDER BY r.time DESC
            LIMIT 1000;
    SQL
  }, {
    name: 'slowest_controllers_and_requests_tests',
    title: 'Individual controllers and requests tests by time (first 1000)',
    query: <<~SQL
            WITH last_commit AS
              (SELECT commit_hash
               FROM spec_profiling_results
               WHERE branch='ee:master'
               ORDER BY date DESC LIMIT 1)
            SELECT DISTINCT concat(r.file, ':', r.line_number) AS LOCATION,
                   r.time,
                   r.query_count,
                   r.query_time,
                   r.request_count,
                   r.feature_category
            FROM spec_profiling_results r,
                 last_commit lc
            WHERE r.commit_hash = lc.commit_hash AND (r.file LIKE '%controllers%' OR r.file LIKE '%requests%')
            AND branch='ee:master'
            ORDER BY r.time DESC
            LIMIT 1000;
    SQL
  }, {
    name: 'slowest_lib_tests',
    title: 'Individual lib tests by time (first 1000)',
    query: <<~SQL
            WITH last_commit AS
              (SELECT commit_hash
               FROM spec_profiling_results
               WHERE branch='ee:master'
               ORDER BY date DESC LIMIT 1)
            SELECT DISTINCT concat(r.file, ':', r.line_number) AS LOCATION,
                   r.time,
                   r.query_count,
                   r.query_time,
                   r.request_count,
                   r.feature_category
            FROM spec_profiling_results r,
                 last_commit lc
            WHERE r.commit_hash = lc.commit_hash AND (r.file LIKE '%lib%')
            AND branch='ee:master'
            ORDER BY r.time DESC
            LIMIT 1000;
    SQL
  }, {
    name: 'slowest_non_features_controllers_requests_tests',
    title: 'Individual non features, controllers and requests tests by time (first 1000)',
    query: <<~SQL
            WITH last_commit AS
              (SELECT commit_hash
               FROM spec_profiling_results
               WHERE branch='ee:master'
               ORDER BY date DESC LIMIT 1)
            SELECT DISTINCT concat(r.file, ':', r.line_number) AS LOCATION,
                   r.time,
                   r.query_count,
                   r.query_time,
                   r.request_count,
                   r.feature_category
            FROM spec_profiling_results r,
                 last_commit lc
            WHERE r.commit_hash = lc.commit_hash AND NOT (r.file LIKE '%controllers%' OR r.file LIKE '%requests%' OR r.file LIKE '%features%' OR r.file LIKE '%lib%')
            AND branch='ee:master'
            ORDER BY r.time DESC
            LIMIT 1000;
    SQL
  }
].freeze