{-# LANGUAGE DeriveAnyClass        #-}
{-# LANGUAGE DeriveGeneric         #-}
# LANGUAGE DuplicateRecordFields #
# LANGUAGE OverloadedStrings      #

module Sql.Read.Builds where

import           Control.Monad.IO.Class           (liftIO)
import           Control.Monad.Trans.Reader       (ask)
import           Data.Aeson
import           Data.Either.Utils                (maybeToEither)
import           Data.Set                         (Set)
import qualified Data.Set                         as Set
import           Data.Text                        (Text)
import qualified Data.Text                        as T
import           Data.Time                        (UTCTime)
import           Database.PostgreSQL.Simple
import           Database.PostgreSQL.Simple.Types (fromPGArray)
import           GHC.Generics
import           GHC.Int                          (Int64)
import qualified Safe

import qualified Builds
import qualified DbHelpers
import qualified DebugUtils                       as D
import qualified JsonUtils
import qualified Sql.QueryUtils                   as Q
import           Sql.Read.Types                   (DbIO, runQuery)
import qualified Sql.Read.Types                   as SqlReadTypes
import qualified UnmatchedBuilds
import qualified WebApi


-- | Get the most recent queued_at time of a build that was
-- fetched via the provider-specific API.
getMostRecentProviderApiFetchedBuild ::
     Int64 -- ^ provider ID
  -> Text -- ^ branch name
  -> DbIO (Maybe UTCTime)
getMostRecentProviderApiFetchedBuild provider_id branch_name = do
  conn <- ask
  liftIO $ do
    xs <- query conn sql (provider_id, branch_name)
    return $ Safe.headMay $ map (\(Only x) -> x) xs
  where
    sql = Q.join [
        "SELECT latest_queued_at"
      , "FROM ci_provider_scan_ranges"
      , "WHERE"
      , Q.conjunction [
          "provider = ?"
        , "branch_filter = ?"
        ]
      , "ORDER BY latest_queued_at DESC"
      , "LIMIT 1"
      ]


-- | Only searches for CircleCI builds
getUnvisitedBuildIds ::
     Connection
  -> Either (Set Builds.UniversalBuildId) Int
  -> IO [DbHelpers.WithId Builds.UniversalBuild]
getUnvisitedBuildIds
    conn
    whitelisted_builds_or_fetch_count = do

  rows <- case whitelisted_builds_or_fetch_count of
    Left whitelist_set -> query conn sql
        (SqlReadTypes.circleCIProviderIndex, In $ map (\(Builds.UniversalBuildId x) -> x) $ Set.toList whitelist_set)
      where
        sql = Q.join [
            sql_prefix
          , "WHERE"
          , Q.conjunction where_conditions
          ]

        where_conditions = [
            "provider = ?"
          , "universal_build_id IN ?"
          ]

    Right limit -> query conn sql
        (SqlReadTypes.circleCIProviderIndex, limit)
      where
        sql = Q.join [
            sql_prefix
          , "WHERE"
          , Q.conjunction where_conditions
          , "ORDER BY build_num DESC"
          , "LIMIT ?"
          ]

        where_conditions = [
            "provider = ?"
          ]

  return $ map f rows

  where
    f (universal_build_id, provider_buildnum, provider_id, build_namespace, succeeded, sha1) = DbHelpers.WithId universal_build_id $ Builds.UniversalBuild
      (Builds.NewBuildNumber provider_buildnum)
      provider_id
      build_namespace
      succeeded
      (Builds.RawCommit sha1)

    sql_prefix = Q.join [
        "SELECT"
      , Q.list [
          "universal_build_id"
        , "build_num"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "commit_sha1"
        ]
      , "FROM unvisited_builds"
      ]


-- | Only searches for CircleCI builds
getUnvisitedBuildsForSha1 ::
     Builds.RawCommit
  -> DbIO [DbHelpers.WithId Builds.UniversalBuild]
getUnvisitedBuildsForSha1 (Builds.RawCommit sha1) = do
  conn <- ask
  liftIO $ do
    rows <- query conn sql (SqlReadTypes.circleCIProviderIndex, sha1)
    return $ map f rows
  where
    f (universal_build_id, provider_buildnum, provider_id, build_namespace, succeeded, sha1) = DbHelpers.WithId universal_build_id $ Builds.UniversalBuild
      (Builds.NewBuildNumber provider_buildnum)
      provider_id
      build_namespace
      succeeded
      (Builds.RawCommit sha1)


    sql = Q.join [
        "SELECT"
      , Q.list [
          "universal_build_id"
        , "build_num"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "commit_sha1"
        ]
      , "FROM unvisited_builds"
      , "WHERE"
      , Q.conjunction [
          "provider = ?"
        , "commit_sha1 = ?"
        , "NOT succeeded"
        ]
      , "ORDER BY universal_build_id DESC"
      ]


-- | TODO Use this
lookupUniversalBuildFromProviderBuild ::
     Connection
  -> Builds.BuildNumber
  -> IO (Maybe (DbHelpers.WithId Builds.UniversalBuild))
lookupUniversalBuildFromProviderBuild conn (Builds.NewBuildNumber build_num) = do
  rows <- query conn sql $ Only build_num
  return $ Safe.headMay rows
  where
    sql = Q.join [
        "SELECT"
      , Q.list [
          "global_build_num"
        , "build_number"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "vcs_revision"
        , "queued_at"
        , "job_name"
        , "branch"
        , "started_at"
        , "finished_at"
        ]
      , "FROM global_builds"
      , "WHERE build_number = ?"
      , "ORDER BY"
      , Q.list [
          "provider DESC"
        , "global_build_num DESC"
        ]
      , "LIMIT 1"
      ]


lookupUniversalBuild ::
     Builds.UniversalBuildId
  -> DbIO (Either Text (DbHelpers.WithId Builds.UniversalBuild))
lookupUniversalBuild (Builds.UniversalBuildId universal_build_num) = do
  conn <- ask
  xs <- liftIO $ query conn sql $ Only universal_build_num
  return $ maybeToEither "build not found" $ Safe.headMay xs
  where
    sql = Q.join [
        "SELECT"
      , Q.list [
          "global_build_num"
        , "build_number"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "vcs_revision"
        ]
      , "FROM global_builds"
      , "WHERE global_build_num = ?"
      ]


getGlobalBuild ::
     Builds.UniversalBuildId
  -> DbIO (Either Text Builds.StorableBuild)
getGlobalBuild (Builds.UniversalBuildId global_build_num) = do
  conn <- ask
  liftIO $ do
    xs <- query conn sql $ Only global_build_num
    return $ maybeToEither err_msg $ Safe.headMay xs
  where
    err_msg = T.pack $ unwords [
        "Universal build number not located:"
      , show global_build_num
      ]

    sql = Q.join [
        "SELECT"
      , Q.list [
          "global_build_num"
        , "build_number"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "vcs_revision"
        , "queued_at"
        , "job_name"
        , "branch"
        , "started_at"
        , "finished_at"
        ]
      , "FROM global_builds"
      , "WHERE global_build_num = ?"
      ]


getFlakyMasterBuildsToRetry ::
  DbIO [(Builds.UniversalBuildId, Builds.BuildNumber)]
getFlakyMasterBuildsToRetry = do
  conn <- ask
  liftIO $ query_ conn sql
  where
    sql = Q.join [
        "SELECT"
      , Q.list [
          "global_build"
        , "build_num"
        ]
      , "FROM master_flaky_builds_to_retry"
      ]


transformPatternRows row =
  (tup1, fromPGArray pattern_ids_array)
  where

    (   pattern_ids_array
      , step_id
      , step_name
      , universal_build_id
      , build_num
      , provider_id
      , build_namespace
      , succeeded
      , vcs_revision
      ) = row

    ubuild = DbHelpers.WithId universal_build_id $ Builds.UniversalBuild
      (Builds.NewBuildNumber build_num)
      provider_id
      build_namespace
      succeeded
      (Builds.RawCommit vcs_revision)

    tup1 = (Builds.NewBuildStepId step_id, step_name, ubuild)



getRevisitableWhitelistedBuilds ::
     [Builds.UniversalBuildId]
  -> DbIO [((Builds.BuildStepId, Text, DbHelpers.WithId Builds.UniversalBuild), [Int64])]
getRevisitableWhitelistedBuilds universal_build_ids = do
  conn <- ask
  liftIO $ do
    (timing, xs) <- D.timeThisFloat $ query conn sql $ Only $ In parms
    D.debugList [
        "getRevisitableWhitelistedBuilds took"
      , show timing
      , "seconds"
      ]

    return $ map transformPatternRows xs
  where
    parms = map (\(Builds.UniversalBuildId x) -> x) universal_build_ids
    sql = Q.join [
        "SELECT"
      , Q.list [
          "unscanned_patterns_array"
        , "step_id"
        , "step_name"
        , "universal_build"
        , "build_num"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "vcs_revision"
        ]
      , "FROM unscanned_patterns"
      , "WHERE universal_build IN ?"
      ]


getRevisitableBuilds ::
  DbIO [((Builds.BuildStepId, Text, DbHelpers.WithId Builds.UniversalBuild), [Int64])]
getRevisitableBuilds = do
  conn <- ask
  liftIO $ map transformPatternRows <$> query_ conn sql
  where
    sql = Q.join [
        "SELECT"
      , Q.list [
          "unscanned_patterns_array"
        , "step_id"
        , "step_name"
        , "universal_build"
        , "build_num"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "vcs_revision"
        ]
      , "FROM unscanned_patterns"
      ]


listBuilds :: Query -> DbIO [WebApi.BuildBranchRecord]
listBuilds sql = do
  conn <- ask
  liftIO $ query_ conn sql


apiUnmatchedBuilds :: DbIO [WebApi.BuildBranchDateRecord]
apiUnmatchedBuilds = runQuery $ Q.join [
    "SELECT"
  , Q.list [
      "global_build"
    , "step_name"
    , "job_name"
    , "vcs_revision"
    , "queued_at"
    ]
  , "FROM unattributed_failed_builds"
  , "ORDER BY queued_at DESC"
  , "LIMIT 1000"
  ]


apiIdiopathicBuilds :: DbIO [WebApi.BuildBranchRecord]
apiIdiopathicBuilds = listBuilds $ Q.join [
    "SELECT"
  , Q.list [
      "branch"
    , "global_build_num"
    ]
  , "FROM idiopathic_build_failures"
  , "ORDER BY global_build_num DESC"
  ]


apiUnmatchedCommitBuilds ::
     Builds.RawCommit
  -> DbIO (Either Text [UnmatchedBuilds.UnmatchedBuild])
apiUnmatchedCommitBuilds (Builds.RawCommit sha1) = do
  conn <- ask
  liftIO $ do
    xs <- query conn sql $ Only sha1
    return $ Right xs
  where
    sql = Q.join [
        "SELECT"
      , Q.list [
          "unattributed_failed_builds.build_number"
        , "unattributed_failed_builds.step_name"
        , "unattributed_failed_builds.queued_at"
        , "unattributed_failed_builds.job_name"
        , "unattributed_failed_builds.branch"
        , "unattributed_failed_builds.global_build"
        , "ci_providers.icon_url"
        , "ci_providers.label"
        ]
      , "FROM unattributed_failed_builds"
      , "JOIN ci_providers"
      , "ON unattributed_failed_builds.provider = ci_providers.id"
      , "WHERE"
      , Q.conjunction [
          "unattributed_failed_builds.vcs_revision = ?"
        , "NOT is_empirically_determined_flaky"
        ]
      ]


apiIdiopathicCommitBuilds ::
     Builds.RawCommit
  -> DbIO (Either Text [UnmatchedBuilds.UnmatchedBuild])
apiIdiopathicCommitBuilds (Builds.RawCommit sha1) = do
  conn <- ask
  liftIO $ Right . map f <$> query conn sql (Only sha1)
  where
    f (build, step_name, queued_at, job_name, branch, universal_build_id, provider_icon_url, provider_label) =
      UnmatchedBuilds.UnmatchedBuild
        (Builds.NewBuildNumber build)
        step_name
        queued_at
        job_name
        branch
        (Builds.UniversalBuildId universal_build_id)
        provider_icon_url
        provider_label

    sql = Q.join [
        "SELECT"
      , Q.list [
          "build_num"
        , "step_name"
        , "queued_at"
        , "job_name"
        , "idiopathic_build_failures.branch"
        , "builds_join_steps.universal_build"
        , "ci_providers.icon_url"
        , "ci_providers.label"
        ]
      , "FROM idiopathic_build_failures"
      , "JOIN builds_join_steps"
      , "ON idiopathic_build_failures.global_build_num = builds_join_steps.universal_build"
      , "JOIN ci_providers"
      , "ON builds_join_steps.provider = ci_providers.id"
      , "WHERE vcs_revision = ?"
      ]


apiTimeoutCommitBuilds ::
     Builds.RawCommit
  -> DbIO (Either Text [UnmatchedBuilds.UnmatchedBuild])
apiTimeoutCommitBuilds (Builds.RawCommit sha1) = do
  conn <- ask
  liftIO $ fmap Right $ query conn sql $ Only sha1
  where
    sql = Q.join [
        "SELECT"
      , Q.list [
          "build_num"
        , "step_name"
        , "queued_at"
        , "job_name"
        , "branch"
        , "universal_build"
        , "ci_providers.icon_url"
        , "ci_providers.label"
        ]
      , "FROM builds_join_steps"
      , "JOIN ci_providers"
      , "ON builds_join_steps.provider = ci_providers.id"
      , "WHERE"
      , Q.conjunction [
          "vcs_revision = ?"
        , "is_timeout"
        ]
      ]


data MissingJobStats = MissingJobStats {
    _job_name          :: Text
  , _count             :: Int
  , _latest_absence_at :: UTCTime
  } deriving (Generic, FromRow)

instance ToJSON MissingJobStats where
  toJSON = genericToJSON JsonUtils.dropUnderscore


apiMissingRequiredBuilds :: DbIO [MissingJobStats]
apiMissingRequiredBuilds = runQuery sql
  where
  sql = Q.join [
      "SELECT"
    , Q.list [
        "job_name"
      , "COUNT(*) AS count"
      , "MAX(master_commits_basic_metadata.committer_date) AS latest_absence_at"
--      , "MAX(master_required_unbuilt_jobs_mview.id) AS latest_id"
--      , "array_agg(master_required_unbuilt_jobs_mview.id) AS commit_ids"
      ]
    , "FROM master_required_unbuilt_jobs_mview"
    , "JOIN master_commits_basic_metadata ON"
    , "master_commits_basic_metadata.id = master_required_unbuilt_jobs_mview.id"
    , "WHERE tstzrange(now() - interval '7 days', now()) @> master_commits_basic_metadata.committer_date"
    , "GROUP BY master_required_unbuilt_jobs_mview.job_name"
    , "ORDER BY count DESC"
    ]


getProviderSurrogateIdFromUniversalBuild ::
     Builds.UniversalBuildId
  -> DbIO (Either Text Builds.ProviderSurrogateId)
getProviderSurrogateIdFromUniversalBuild (Builds.UniversalBuildId ubuild_id) = do
  conn <- ask
  liftIO $ do
    xs <- query conn sql $ Only ubuild_id
    return $ maybeToEither err_msg $ Safe.headMay $
      map (\(Only x) -> Builds.ProviderSurrogateId x) xs
  where
    err_msg = T.unwords [
        "build"
      , T.pack $ show ubuild_id
      , "not in database"
      ]

    sql = Q.join [
        "SELECT"
      , "provider_build_surrogate"
      , "FROM universal_builds"
      , "WHERE id = ?"
      ]


getStepIdFromUniversalBuild ::
     Builds.UniversalBuildId
  -> DbIO (Either Text Builds.BuildStepId)
getStepIdFromUniversalBuild (Builds.UniversalBuildId ubuild_id) = do
  conn <- ask
  liftIO $ do
    xs <- query conn sql $ Only ubuild_id
    return $ maybeToEither err_msg $ Safe.headMay $
      map (\(Only x) -> Builds.NewBuildStepId x) xs
  where
    err_msg = T.unwords [
        "build"
      , T.pack $ show ubuild_id
      , "not in database"
      ]

    sql = Q.join [
        "SELECT id"
      , "FROM build_steps"
      , "WHERE universal_build = ?"
      ]

# LANGUAGE DeriveAnyClass        #
# LANGUAGE DeriveGeneric         #
 | Get the most recent queued_at time of a build that was
 fetched via the provider-specific API.
 ^ provider ID
 ^ branch name
 | Only searches for CircleCI builds
 | Only searches for CircleCI builds
 | TODO Use this
      , "MAX(master_required_unbuilt_jobs_mview.id) AS latest_id"
      , "array_agg(master_required_unbuilt_jobs_mview.id) AS commit_ids"
# LANGUAGE DuplicateRecordFields #
# LANGUAGE OverloadedStrings      #

module Sql.Read.Builds where

import           Control.Monad.IO.Class           (liftIO)
import           Control.Monad.Trans.Reader       (ask)
import           Data.Aeson
import           Data.Either.Utils                (maybeToEither)
import           Data.Set                         (Set)
import qualified Data.Set                         as Set
import           Data.Text                        (Text)
import qualified Data.Text                        as T
import           Data.Time                        (UTCTime)
import           Database.PostgreSQL.Simple
import           Database.PostgreSQL.Simple.Types (fromPGArray)
import           GHC.Generics
import           GHC.Int                          (Int64)
import qualified Safe

import qualified Builds
import qualified DbHelpers
import qualified DebugUtils                       as D
import qualified JsonUtils
import qualified Sql.QueryUtils                   as Q
import           Sql.Read.Types                   (DbIO, runQuery)
import qualified Sql.Read.Types                   as SqlReadTypes
import qualified UnmatchedBuilds
import qualified WebApi


getMostRecentProviderApiFetchedBuild ::
  -> DbIO (Maybe UTCTime)
getMostRecentProviderApiFetchedBuild provider_id branch_name = do
  conn <- ask
  liftIO $ do
    xs <- query conn sql (provider_id, branch_name)
    return $ Safe.headMay $ map (\(Only x) -> x) xs
  where
    sql = Q.join [
        "SELECT latest_queued_at"
      , "FROM ci_provider_scan_ranges"
      , "WHERE"
      , Q.conjunction [
          "provider = ?"
        , "branch_filter = ?"
        ]
      , "ORDER BY latest_queued_at DESC"
      , "LIMIT 1"
      ]


getUnvisitedBuildIds ::
     Connection
  -> Either (Set Builds.UniversalBuildId) Int
  -> IO [DbHelpers.WithId Builds.UniversalBuild]
getUnvisitedBuildIds
    conn
    whitelisted_builds_or_fetch_count = do

  rows <- case whitelisted_builds_or_fetch_count of
    Left whitelist_set -> query conn sql
        (SqlReadTypes.circleCIProviderIndex, In $ map (\(Builds.UniversalBuildId x) -> x) $ Set.toList whitelist_set)
      where
        sql = Q.join [
            sql_prefix
          , "WHERE"
          , Q.conjunction where_conditions
          ]

        where_conditions = [
            "provider = ?"
          , "universal_build_id IN ?"
          ]

    Right limit -> query conn sql
        (SqlReadTypes.circleCIProviderIndex, limit)
      where
        sql = Q.join [
            sql_prefix
          , "WHERE"
          , Q.conjunction where_conditions
          , "ORDER BY build_num DESC"
          , "LIMIT ?"
          ]

        where_conditions = [
            "provider = ?"
          ]

  return $ map f rows

  where
    f (universal_build_id, provider_buildnum, provider_id, build_namespace, succeeded, sha1) = DbHelpers.WithId universal_build_id $ Builds.UniversalBuild
      (Builds.NewBuildNumber provider_buildnum)
      provider_id
      build_namespace
      succeeded
      (Builds.RawCommit sha1)

    sql_prefix = Q.join [
        "SELECT"
      , Q.list [
          "universal_build_id"
        , "build_num"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "commit_sha1"
        ]
      , "FROM unvisited_builds"
      ]


getUnvisitedBuildsForSha1 ::
     Builds.RawCommit
  -> DbIO [DbHelpers.WithId Builds.UniversalBuild]
getUnvisitedBuildsForSha1 (Builds.RawCommit sha1) = do
  conn <- ask
  liftIO $ do
    rows <- query conn sql (SqlReadTypes.circleCIProviderIndex, sha1)
    return $ map f rows
  where
    f (universal_build_id, provider_buildnum, provider_id, build_namespace, succeeded, sha1) = DbHelpers.WithId universal_build_id $ Builds.UniversalBuild
      (Builds.NewBuildNumber provider_buildnum)
      provider_id
      build_namespace
      succeeded
      (Builds.RawCommit sha1)


    sql = Q.join [
        "SELECT"
      , Q.list [
          "universal_build_id"
        , "build_num"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "commit_sha1"
        ]
      , "FROM unvisited_builds"
      , "WHERE"
      , Q.conjunction [
          "provider = ?"
        , "commit_sha1 = ?"
        , "NOT succeeded"
        ]
      , "ORDER BY universal_build_id DESC"
      ]


lookupUniversalBuildFromProviderBuild ::
     Connection
  -> Builds.BuildNumber
  -> IO (Maybe (DbHelpers.WithId Builds.UniversalBuild))
lookupUniversalBuildFromProviderBuild conn (Builds.NewBuildNumber build_num) = do
  rows <- query conn sql $ Only build_num
  return $ Safe.headMay rows
  where
    sql = Q.join [
        "SELECT"
      , Q.list [
          "global_build_num"
        , "build_number"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "vcs_revision"
        , "queued_at"
        , "job_name"
        , "branch"
        , "started_at"
        , "finished_at"
        ]
      , "FROM global_builds"
      , "WHERE build_number = ?"
      , "ORDER BY"
      , Q.list [
          "provider DESC"
        , "global_build_num DESC"
        ]
      , "LIMIT 1"
      ]


lookupUniversalBuild ::
     Builds.UniversalBuildId
  -> DbIO (Either Text (DbHelpers.WithId Builds.UniversalBuild))
lookupUniversalBuild (Builds.UniversalBuildId universal_build_num) = do
  conn <- ask
  xs <- liftIO $ query conn sql $ Only universal_build_num
  return $ maybeToEither "build not found" $ Safe.headMay xs
  where
    sql = Q.join [
        "SELECT"
      , Q.list [
          "global_build_num"
        , "build_number"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "vcs_revision"
        ]
      , "FROM global_builds"
      , "WHERE global_build_num = ?"
      ]


getGlobalBuild ::
     Builds.UniversalBuildId
  -> DbIO (Either Text Builds.StorableBuild)
getGlobalBuild (Builds.UniversalBuildId global_build_num) = do
  conn <- ask
  liftIO $ do
    xs <- query conn sql $ Only global_build_num
    return $ maybeToEither err_msg $ Safe.headMay xs
  where
    err_msg = T.pack $ unwords [
        "Universal build number not located:"
      , show global_build_num
      ]

    sql = Q.join [
        "SELECT"
      , Q.list [
          "global_build_num"
        , "build_number"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "vcs_revision"
        , "queued_at"
        , "job_name"
        , "branch"
        , "started_at"
        , "finished_at"
        ]
      , "FROM global_builds"
      , "WHERE global_build_num = ?"
      ]


getFlakyMasterBuildsToRetry ::
  DbIO [(Builds.UniversalBuildId, Builds.BuildNumber)]
getFlakyMasterBuildsToRetry = do
  conn <- ask
  liftIO $ query_ conn sql
  where
    sql = Q.join [
        "SELECT"
      , Q.list [
          "global_build"
        , "build_num"
        ]
      , "FROM master_flaky_builds_to_retry"
      ]


transformPatternRows row =
  (tup1, fromPGArray pattern_ids_array)
  where

    (   pattern_ids_array
      , step_id
      , step_name
      , universal_build_id
      , build_num
      , provider_id
      , build_namespace
      , succeeded
      , vcs_revision
      ) = row

    ubuild = DbHelpers.WithId universal_build_id $ Builds.UniversalBuild
      (Builds.NewBuildNumber build_num)
      provider_id
      build_namespace
      succeeded
      (Builds.RawCommit vcs_revision)

    tup1 = (Builds.NewBuildStepId step_id, step_name, ubuild)



getRevisitableWhitelistedBuilds ::
     [Builds.UniversalBuildId]
  -> DbIO [((Builds.BuildStepId, Text, DbHelpers.WithId Builds.UniversalBuild), [Int64])]
getRevisitableWhitelistedBuilds universal_build_ids = do
  conn <- ask
  liftIO $ do
    (timing, xs) <- D.timeThisFloat $ query conn sql $ Only $ In parms
    D.debugList [
        "getRevisitableWhitelistedBuilds took"
      , show timing
      , "seconds"
      ]

    return $ map transformPatternRows xs
  where
    parms = map (\(Builds.UniversalBuildId x) -> x) universal_build_ids
    sql = Q.join [
        "SELECT"
      , Q.list [
          "unscanned_patterns_array"
        , "step_id"
        , "step_name"
        , "universal_build"
        , "build_num"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "vcs_revision"
        ]
      , "FROM unscanned_patterns"
      , "WHERE universal_build IN ?"
      ]


getRevisitableBuilds ::
  DbIO [((Builds.BuildStepId, Text, DbHelpers.WithId Builds.UniversalBuild), [Int64])]
getRevisitableBuilds = do
  conn <- ask
  liftIO $ map transformPatternRows <$> query_ conn sql
  where
    sql = Q.join [
        "SELECT"
      , Q.list [
          "unscanned_patterns_array"
        , "step_id"
        , "step_name"
        , "universal_build"
        , "build_num"
        , "provider"
        , "build_namespace"
        , "succeeded"
        , "vcs_revision"
        ]
      , "FROM unscanned_patterns"
      ]


listBuilds :: Query -> DbIO [WebApi.BuildBranchRecord]
listBuilds sql = do
  conn <- ask
  liftIO $ query_ conn sql


apiUnmatchedBuilds :: DbIO [WebApi.BuildBranchDateRecord]
apiUnmatchedBuilds = runQuery $ Q.join [
    "SELECT"
  , Q.list [
      "global_build"
    , "step_name"
    , "job_name"
    , "vcs_revision"
    , "queued_at"
    ]
  , "FROM unattributed_failed_builds"
  , "ORDER BY queued_at DESC"
  , "LIMIT 1000"
  ]


apiIdiopathicBuilds :: DbIO [WebApi.BuildBranchRecord]
apiIdiopathicBuilds = listBuilds $ Q.join [
    "SELECT"
  , Q.list [
      "branch"
    , "global_build_num"
    ]
  , "FROM idiopathic_build_failures"
  , "ORDER BY global_build_num DESC"
  ]


apiUnmatchedCommitBuilds ::
     Builds.RawCommit
  -> DbIO (Either Text [UnmatchedBuilds.UnmatchedBuild])
apiUnmatchedCommitBuilds (Builds.RawCommit sha1) = do
  conn <- ask
  liftIO $ do
    xs <- query conn sql $ Only sha1
    return $ Right xs
  where
    sql = Q.join [
        "SELECT"
      , Q.list [
          "unattributed_failed_builds.build_number"
        , "unattributed_failed_builds.step_name"
        , "unattributed_failed_builds.queued_at"
        , "unattributed_failed_builds.job_name"
        , "unattributed_failed_builds.branch"
        , "unattributed_failed_builds.global_build"
        , "ci_providers.icon_url"
        , "ci_providers.label"
        ]
      , "FROM unattributed_failed_builds"
      , "JOIN ci_providers"
      , "ON unattributed_failed_builds.provider = ci_providers.id"
      , "WHERE"
      , Q.conjunction [
          "unattributed_failed_builds.vcs_revision = ?"
        , "NOT is_empirically_determined_flaky"
        ]
      ]


apiIdiopathicCommitBuilds ::
     Builds.RawCommit
  -> DbIO (Either Text [UnmatchedBuilds.UnmatchedBuild])
apiIdiopathicCommitBuilds (Builds.RawCommit sha1) = do
  conn <- ask
  liftIO $ Right . map f <$> query conn sql (Only sha1)
  where
    f (build, step_name, queued_at, job_name, branch, universal_build_id, provider_icon_url, provider_label) =
      UnmatchedBuilds.UnmatchedBuild
        (Builds.NewBuildNumber build)
        step_name
        queued_at
        job_name
        branch
        (Builds.UniversalBuildId universal_build_id)
        provider_icon_url
        provider_label

    sql = Q.join [
        "SELECT"
      , Q.list [
          "build_num"
        , "step_name"
        , "queued_at"
        , "job_name"
        , "idiopathic_build_failures.branch"
        , "builds_join_steps.universal_build"
        , "ci_providers.icon_url"
        , "ci_providers.label"
        ]
      , "FROM idiopathic_build_failures"
      , "JOIN builds_join_steps"
      , "ON idiopathic_build_failures.global_build_num = builds_join_steps.universal_build"
      , "JOIN ci_providers"
      , "ON builds_join_steps.provider = ci_providers.id"
      , "WHERE vcs_revision = ?"
      ]


apiTimeoutCommitBuilds ::
     Builds.RawCommit
  -> DbIO (Either Text [UnmatchedBuilds.UnmatchedBuild])
apiTimeoutCommitBuilds (Builds.RawCommit sha1) = do
  conn <- ask
  liftIO $ fmap Right $ query conn sql $ Only sha1
  where
    sql = Q.join [
        "SELECT"
      , Q.list [
          "build_num"
        , "step_name"
        , "queued_at"
        , "job_name"
        , "branch"
        , "universal_build"
        , "ci_providers.icon_url"
        , "ci_providers.label"
        ]
      , "FROM builds_join_steps"
      , "JOIN ci_providers"
      , "ON builds_join_steps.provider = ci_providers.id"
      , "WHERE"
      , Q.conjunction [
          "vcs_revision = ?"
        , "is_timeout"
        ]
      ]


data MissingJobStats = MissingJobStats {
    _job_name          :: Text
  , _count             :: Int
  , _latest_absence_at :: UTCTime
  } deriving (Generic, FromRow)

instance ToJSON MissingJobStats where
  toJSON = genericToJSON JsonUtils.dropUnderscore


apiMissingRequiredBuilds :: DbIO [MissingJobStats]
apiMissingRequiredBuilds = runQuery sql
  where
  sql = Q.join [
      "SELECT"
    , Q.list [
        "job_name"
      , "COUNT(*) AS count"
      , "MAX(master_commits_basic_metadata.committer_date) AS latest_absence_at"
      ]
    , "FROM master_required_unbuilt_jobs_mview"
    , "JOIN master_commits_basic_metadata ON"
    , "master_commits_basic_metadata.id = master_required_unbuilt_jobs_mview.id"
    , "WHERE tstzrange(now() - interval '7 days', now()) @> master_commits_basic_metadata.committer_date"
    , "GROUP BY master_required_unbuilt_jobs_mview.job_name"
    , "ORDER BY count DESC"
    ]


getProviderSurrogateIdFromUniversalBuild ::
     Builds.UniversalBuildId
  -> DbIO (Either Text Builds.ProviderSurrogateId)
getProviderSurrogateIdFromUniversalBuild (Builds.UniversalBuildId ubuild_id) = do
  conn <- ask
  liftIO $ do
    xs <- query conn sql $ Only ubuild_id
    return $ maybeToEither err_msg $ Safe.headMay $
      map (\(Only x) -> Builds.ProviderSurrogateId x) xs
  where
    err_msg = T.unwords [
        "build"
      , T.pack $ show ubuild_id
      , "not in database"
      ]

    sql = Q.join [
        "SELECT"
      , "provider_build_surrogate"
      , "FROM universal_builds"
      , "WHERE id = ?"
      ]


getStepIdFromUniversalBuild ::
     Builds.UniversalBuildId
  -> DbIO (Either Text Builds.BuildStepId)
getStepIdFromUniversalBuild (Builds.UniversalBuildId ubuild_id) = do
  conn <- ask
  liftIO $ do
    xs <- query conn sql $ Only ubuild_id
    return $ maybeToEither err_msg $ Safe.headMay $
      map (\(Only x) -> Builds.NewBuildStepId x) xs
  where
    err_msg = T.unwords [
        "build"
      , T.pack $ show ubuild_id
      , "not in database"
      ]

    sql = Q.join [
        "SELECT id"
      , "FROM build_steps"
      , "WHERE universal_build = ?"
      ]
