{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE EmptyDataDecls #-}
{-# LANGUAGE FlexibleContexts #-}
{-# LANGUAGE GADTs #-}
{-# LANGUAGE GeneralizedNewtypeDeriving #-}
{-# LANGUAGE MultiParamTypeClasses #-}
{-# LANGUAGE QuasiQuotes #-}
{-# LANGUAGE TemplateHaskell #-}
{-# LANGUAGE TypeFamilies #-}
{-# LANGUAGE DerivingStrategies #-}
{-# LANGUAGE DeriveAnyClass #-}
{-# LANGUAGE StandaloneDeriving #-}
{-# LANGUAGE UndecidableInstances #-}
{-# LANGUAGE DataKinds #-}
{-# LANGUAGE FlexibleInstances #-}
{-# LANGUAGE TypeOperators #-}
{-# LANGUAGE TypeApplications #-}

module Zenith.DB where

import Codec.Borsh
import Control.Exception (SomeException(..), throw, throwIO, try)
import Control.Monad (forM_, unless, when)
import Control.Monad.IO.Class (MonadIO, liftIO)
import Control.Monad.Logger
  ( LoggingT
  , NoLoggingT
  , logDebugN
  , logErrorN
  , logInfoN
  , runNoLoggingT
  , runStderrLoggingT
  )
import qualified Data.ByteString as BS
import Data.HexString
import Data.Int
import Data.List (group, sort)
import Data.Maybe (catMaybes, fromJust, isJust)
import Data.Pool (Pool)
import qualified Data.Text as T
import qualified Data.Text.Encoding as TE
import Data.Time.Clock (UTCTime, getCurrentTime)
import qualified Data.UUID as U
import Data.Word
import Database.Esqueleto.Experimental
import qualified Database.Persist.Sqlite as PS
import Database.Persist.TH
import Haskoin.Transaction.Common
  ( OutPoint(..)
  , TxIn(..)
  , TxOut(..)
  , txHashToHex
  )
import System.Directory (doesFileExist, getHomeDirectory, removeFile)
import System.FilePath ((</>))
import ZcashHaskell.Keys (deriveUfvk, deriveUivk)
import ZcashHaskell.Orchard
  ( compareAddress
  , getSaplingFromUA
  , isValidUnifiedAddress
  )
import ZcashHaskell.Transparent (encodeTransparentReceiver)
import ZcashHaskell.Types
  ( DecodedNote(..)
  , ExchangeAddress(..)
  , OrchardAction(..)
  , OrchardBundle(..)
  , OrchardReceiver(..)
  , OrchardWitness(..)
  , Phrase(..)
  , SaplingAddress(..)
  , SaplingBundle(..)
  , SaplingReceiver(..)
  , SaplingWitness(..)
  , Scope(..)
  , ShieldedOutput(..)
  , ShieldedSpend(..)
  , ToBytes(..)
  , Transaction(..)
  , TransparentAddress(..)
  , TransparentBundle(..)
  , TransparentReceiver(..)
  , TxError(..)
  , UnifiedAddress(..)
  , ValidAddress(..)
  , ValidVk(..)
  , ZcashNet(..)
  )
import Zenith.Tree (OrchardNode(..), SaplingNode(..), Tree(..), truncateTree)
import Zenith.Types
  ( AccountBalance(..)
  , AccountType(..)
  , HexStringDB(..)
  , OrchardSpendingKeyDB(..)
  , PhraseDB(..)
  , PrivacyPolicy(..)
  , RseedDB(..)
  , SaplingSpendingKeyDB(..)
  , ScopeDB(..)
  , TransparentSpendingKeyDB(..)
  , UnifiedAddressDB(..)
  , UnifiedFvkDB(..)
  , UnifiedIvkDB(..)
  , ZcashAccountAPI(..)
  , ZcashAddressAPI(..)
  , ZcashNetDB(..)
  , ZcashNoteAPI(..)
  , ZcashPool(..)
  , ZcashWalletAPI(..)
  , ZenithStatus(..)
  , ZenithUuid(..)
  )

share
  [mkPersist sqlSettings, mkMigrate "schemaMigration"]
  [persistLowerCase|
  ZenithSchema
    version Int
    action T.Text
    UniqueAction version action
    deriving Show Eq
  |]

share
  [mkPersist sqlSettings, mkMigrate "migrateAll"]
  [persistLowerCase|
  ZcashWallet
    name T.Text
    network ZcashNetDB
    seedPhrase PhraseDB
    birthdayHeight Int
    lastSync Int default=0
    local Bool default=TRUE
    UniqueWallet name network
    deriving Show Eq
  ZcashAccount
    index Int
    walletId ZcashWalletId
    name T.Text
    orchSpendKey OrchardSpendingKeyDB Maybe default=NULL
    sapSpendKey SaplingSpendingKeyDB Maybe default=NULL
    tPrivateKey TransparentSpendingKeyDB Maybe default=NULL
    fvk UnifiedFvkDB Maybe default=NULL
    ivk UnifiedIvkDB Maybe default=NULL
    type AccountType default='Local'
    UniqueAccount index walletId
    UniqueAccName walletId name
    deriving Show Eq
  WalletAddress
    index Int
    accId ZcashAccountId
    name T.Text
    uAddress UnifiedAddressDB
    scope ScopeDB
    UniqueAddress index scope accId
    UniqueAddName accId name
    deriving Show Eq
  WalletTransaction
    txId HexStringDB
    accId ZcashAccountId
    block Int
    conf Int
    time Int
    UniqueWTx txId accId
    deriving Show Eq
  UserTx
    hex HexStringDB
    address WalletAddressId OnDeleteCascade OnUpdateCascade
    time Int
    amount Int
    memo T.Text
    UniqueUTx hex address
    deriving Show Eq
  WalletTrNote
    tx WalletTransactionId OnDeleteCascade OnUpdateCascade
    accId ZcashAccountId OnDeleteCascade OnUpdateCascade
    address WalletAddressId OnDeleteCascade OnUpdateCascade
    value Int64
    spent Bool
    script BS.ByteString
    change Bool
    position Int
    UniqueTNote tx accId script
    deriving Show Eq
  WalletTrSpend
    tx WalletTransactionId OnDeleteCascade OnUpdateCascade
    note WalletTrNoteId OnDeleteCascade OnUpdateCascade
    accId ZcashAccountId OnDeleteCascade OnUpdateCascade
    value Int64
    UniqueTrSpend tx accId
    deriving Show Eq
  WalletSapNote
    tx WalletTransactionId OnDeleteCascade OnUpdateCascade
    accId ZcashAccountId OnDeleteCascade OnUpdateCascade
    value Int64
    recipient BS.ByteString
    memo T.Text
    spent Bool
    nullifier HexStringDB
    position Word64
    witness HexStringDB
    change Bool
    witPos ShieldOutputId OnDeleteIgnore OnUpdateIgnore
    rseed RseedDB
    UniqueSapNote tx nullifier
    deriving Show Eq
  WalletSapSpend
    tx WalletTransactionId OnDeleteCascade OnUpdateCascade
    note WalletSapNoteId OnDeleteCascade OnUpdateCascade
    accId ZcashAccountId OnDeleteCascade OnUpdateCascade
    value Int64
    UniqueSapSepnd tx accId
    deriving Show Eq
  WalletOrchNote
    tx WalletTransactionId OnDeleteCascade OnUpdateCascade
    accId ZcashAccountId OnDeleteCascade OnUpdateCascade
    value Int64
    recipient BS.ByteString
    memo T.Text
    spent Bool
    nullifier HexStringDB
    position Int64
    witness HexStringDB
    change Bool
    witPos OrchActionId OnDeleteIgnore OnUpdateIgnore
    rho BS.ByteString
    rseed RseedDB
    UniqueOrchNote tx nullifier
    deriving Show Eq
  WalletOrchSpend
    tx WalletTransactionId OnDeleteCascade OnUpdateCascade
    note WalletOrchNoteId OnDeleteCascade OnUpdateCascade
    accId ZcashAccountId OnDeleteCascade OnUpdateCascade
    value Int64
    UniqueOrchSpend tx accId
    deriving Show Eq
  ZcashBlock
    height Int
    hash HexStringDB
    conf Int
    time Int
    network ZcashNetDB
    UniqueBlock height network
    deriving Show Eq
  ZcashTransaction
    blockId ZcashBlockId OnDeleteCascade OnUpdateCascade
    txId HexStringDB
    UniqueTx blockId txId
    deriving Show Eq
  TransparentNote
    tx ZcashTransactionId OnDeleteCascade OnUpdateCascade
    value Int64
    script BS.ByteString
    position Int
    UniqueTNPos tx position
    deriving Show Eq
  TransparentSpend
    tx ZcashTransactionId OnDeleteCascade OnUpdateCascade
    outPointHash HexStringDB
    outPointIndex Word64
    script BS.ByteString
    seq Word64
    position Int
    UniqueTSPos tx position
    deriving Show Eq
  OrchAction
    tx ZcashTransactionId OnDeleteCascade OnUpdateCascade
    nf HexStringDB
    rk HexStringDB
    cmx HexStringDB
    ephKey HexStringDB
    encCipher HexStringDB
    outCipher HexStringDB
    cv HexStringDB
    auth HexStringDB
    position Int
    UniqueOAPos tx position
    deriving Show Eq
  ShieldOutput
    tx ZcashTransactionId OnDeleteCascade OnUpdateCascade
    cv HexStringDB
    cmu HexStringDB
    ephKey HexStringDB
    encCipher HexStringDB
    outCipher HexStringDB
    proof HexStringDB
    position Int
    UniqueSOPos tx position
    deriving Show Eq
  ShieldSpend
    tx ZcashTransactionId OnDeleteCascade OnUpdateCascade
    cv HexStringDB
    anchor HexStringDB
    nullifier HexStringDB
    rk HexStringDB
    proof HexStringDB
    authSig HexStringDB
    position Int
    UniqueSSPos tx position
    deriving Show Eq
  QrCode
    address WalletAddressId OnDeleteCascade OnUpdateCascade
    version ZcashPool
    bytes BS.ByteString
    height Int
    width Int
    name T.Text
    UniqueQr address version
    deriving Show Eq
  AddressBook
    network ZcashNetDB
    abdescrip T.Text
    abaddress T.Text
    UniqueABA abaddress
    deriving Show Eq
  Operation json
    uuid ZenithUuid
    start UTCTime
    end UTCTime Maybe
    status ZenithStatus
    result T.Text Maybe
    UniqueOp uuid
    deriving Show Eq
  ChainSync
    name T.Text
    start UTCTime
    end UTCTime Maybe
    status ZenithStatus
    UniqueSync name
    deriving Show Eq
  TreeStore
    pool ZcashPool
    bytes BS.ByteString
    lastSync Int
    UniquePool pool
    deriving Show Eq
    |]

-- ** Type conversions
-- | @ZcashWallet@
toZcashWalletAPI :: Entity ZcashWallet -> ZcashWalletAPI
toZcashWalletAPI w =
  ZcashWalletAPI
    (fromIntegral $ fromSqlKey $ entityKey w)
    (zcashWalletName $ entityVal w)
    (getNet $ zcashWalletNetwork $ entityVal w)
    (zcashWalletBirthdayHeight $ entityVal w)
    (zcashWalletLastSync $ entityVal w)
    (zcashWalletLocal $ entityVal w)

-- | @ZcashAccount@
toZcashAccountAPI :: Entity ZcashAccount -> ZcashAccountAPI
toZcashAccountAPI a =
  ZcashAccountAPI
    (fromIntegral $ fromSqlKey $ entityKey a)
    (fromIntegral $ fromSqlKey $ zcashAccountWalletId $ entityVal a)
    (zcashAccountName $ entityVal a)
    (zcashAccountType $ entityVal a)

-- | @WalletAddress@
toZcashAddressAPI :: Entity WalletAddress -> ZcashAddressAPI
toZcashAddressAPI a =
  ZcashAddressAPI
    (fromIntegral $ fromSqlKey $ entityKey a)
    (fromIntegral $ fromSqlKey $ walletAddressAccId $ entityVal a)
    (walletAddressName $ entityVal a)
    (getUA $ walletAddressUAddress $ entityVal a)
    (getSaplingFromUA $
     TE.encodeUtf8 $ getUA $ walletAddressUAddress $ entityVal a)
    (case t_rec =<<
          (isValidUnifiedAddress . TE.encodeUtf8 . getUA . walletAddressUAddress)
            (entityVal a) of
       Nothing -> Nothing
       Just tRec ->
         Just $
         encodeTransparentReceiver
           (maybe
              TestNet
              ua_net
              ((isValidUnifiedAddress .
                TE.encodeUtf8 . getUA . walletAddressUAddress) $
               entityVal a))
           tRec)

-- | @WalletTrNote@
trToZcashNoteAPI :: ConnectionPool -> Entity WalletTrNote -> IO ZcashNoteAPI
trToZcashNoteAPI pool n = do
  t <- getWalletTransaction pool $ walletTrNoteTx $ entityVal n
  case t of
    Nothing -> throwIO $ userError "Unable to find transaction"
    Just t' -> do
      return $
        ZcashNoteAPI
          (getHex $ walletTransactionTxId $ entityVal t') -- tx ID
          Zenith.Types.TransparentPool -- pool
          (fromIntegral (walletTrNoteValue (entityVal n)) / 100000000.0) -- zec
          (walletTrNoteValue $ entityVal n) -- zats
          "" -- memo
          (walletTransactionConf (entityVal t') >= 10) -- confirmed
          (walletTransactionBlock $ entityVal t') -- blockheight
          (walletTransactionTime $ entityVal t') -- blocktime
          (walletTrNotePosition $ entityVal n) -- outindex
          (walletTrNoteChange $ entityVal n) -- change

-- | @WalletSapNote@
sapToZcashNoteAPI :: ConnectionPool -> Entity WalletSapNote -> IO ZcashNoteAPI
sapToZcashNoteAPI pool n = do
  t <- getWalletTransaction pool $ walletSapNoteTx $ entityVal n
  oi <- getSaplingOutIndex pool $ walletSapNoteWitPos $ entityVal n
  case t of
    Nothing -> throwIO $ userError "Unable to find transaction"
    Just t' -> do
      return $
        ZcashNoteAPI
          (getHex $ walletTransactionTxId $ entityVal t') -- tx ID
          Zenith.Types.SaplingPool -- pool
          (fromIntegral (walletSapNoteValue (entityVal n)) / 100000000.0) -- zec
          (walletSapNoteValue $ entityVal n) -- zats
          (walletSapNoteMemo $ entityVal n) -- memo
          (walletTransactionConf (entityVal t') >= 10) -- confirmed
          (walletTransactionBlock $ entityVal t') -- blockheight
          (walletTransactionTime $ entityVal t') -- blocktime
          oi -- outindex
          (walletSapNoteChange $ entityVal n) -- change

-- | @WalletOrchNote@
orchToZcashNoteAPI :: ConnectionPool -> Entity WalletOrchNote -> IO ZcashNoteAPI
orchToZcashNoteAPI pool n = do
  t <- getWalletTransaction pool $ walletOrchNoteTx $ entityVal n
  oi <- getOrchardOutIndex pool $ walletOrchNoteWitPos $ entityVal n
  case t of
    Nothing -> throwIO $ userError "Unable to find transaction"
    Just t' -> do
      return $
        ZcashNoteAPI
          (getHex $ walletTransactionTxId $ entityVal t') -- tx ID
          OrchardPool
          (fromIntegral (walletOrchNoteValue (entityVal n)) / 100000000.0) -- zec
          (walletOrchNoteValue $ entityVal n) -- zats
          (walletOrchNoteMemo $ entityVal n) -- memo
          (walletTransactionConf (entityVal t') >= 10) -- confirmed
          (walletTransactionBlock $ entityVal t') -- blockheight
          (walletTransactionTime $ entityVal t') -- blocktime
          oi -- outindex
          (walletOrchNoteChange $ entityVal n) -- change

-- * Database functions
-- | Initializes the database
initDb ::
     T.Text -- ^ The database path to check
  -> NoLoggingT IO (Either String Bool)
initDb dbName = do
  x <-
    liftIO
      (try $ PS.runSqlite dbName $ runMigrationUnsafeQuiet schemaMigration :: IO
         (Either SomeException [T.Text]))
  case x of
    Left _ -> do
      logErrorN "Failed to initiate schema table"
      return $ Left "Failed to initiate schema table"
    Right _ -> do
      pool <- liftIO $ runNoLoggingT $ initPool dbName
      j <-
        liftIO
          (try $ PS.runSqlite dbName $ runMigrationQuiet migrateAll :: IO
             (Either SomeException [T.Text]))
      case j of
        Left e1 -> do
          logDebugN "Automatic migration failed, starting manual"
          versions <- liftIO $ getVersions pool
          migrateTables pool versions
          PS.runSqlite dbName $ printMigration migrateAll
          m <-
            liftIO
              (try $ PS.runSqlite dbName $ runMigration migrateAll :: IO
                 (Either SomeException ()))
          case m of
            Left e2 -> do
              logErrorN $ "Failed to migrate data tables " <> T.pack (show e2)
              return $ Left $ "Failed to migrate data tables" ++ show e2
            Right _ -> do
              logInfoN "Migration of tables successful"
              return $ Right False
        Right _ -> do
          _ <-
            runNoLoggingT $
            PS.retryOnBusy $
            flip PS.runSqlPool pool $ do
              upsert (ZenithSchema 1 "Viewing Keys") []
          wallets <-
            runNoLoggingT $
            PS.retryOnBusy $
            flip PS.runSqlPool pool $ do select . from $ table @ZcashWallet
          accounts <-
            runNoLoggingT $
            PS.retryOnBusy $
            flip PS.runSqlPool pool $ do select . from $ table @ZcashAccount
          addresses <-
            runNoLoggingT $
            PS.retryOnBusy $
            flip PS.runSqlPool pool $ do select . from $ table @WalletAddress
          abook <-
            runNoLoggingT $
            PS.retryOnBusy $
            flip PS.runSqlPool pool $ do select . from $ table @AddressBook
          hDir <- liftIO getHomeDirectory
          let backupDb = hDir </> "Zenith/.backup.db"
          checkDbFile <- liftIO $ doesFileExist backupDb
          when checkDbFile $ liftIO $ removeFile backupDb
          _ <- PS.runSqlite (T.pack backupDb) $ runMigrationQuiet migrateAll
          backupPool <- liftIO $ runNoLoggingT $ initPool $ T.pack backupDb
          _ <-
            runNoLoggingT $
            PS.retryOnBusy $
            flip PS.runSqlPool backupPool $ insertMany_ $ entityVal <$> wallets
          _ <-
            runNoLoggingT $
            PS.retryOnBusy $
            flip PS.runSqlPool backupPool $ insertMany_ $ entityVal <$> accounts
          _ <-
            runNoLoggingT $
            PS.retryOnBusy $
            flip PS.runSqlPool backupPool $ insertMany_ $ entityVal <$> abook
          _ <-
            runNoLoggingT $
            PS.retryOnBusy $
            flip PS.runSqlPool backupPool $
            insertMany_ $ entityVal <$> addresses
          return $ Right False

migrateTables :: ConnectionPool -> [Int] -> NoLoggingT IO ()
migrateTables pool versions = do
  unless (1 `elem` versions) $ do
    logDebugN "Making version 1 changes"
    _ <-
      runNoLoggingT $
      PS.retryOnBusy $
      flip PS.runSqlPool pool $ do
        rawExecute
          "ALTER TABLE \"zcash_wallet\" ADD COLUMN \"local\" BOOLEAN NOT NULL DEFAULT TRUE;"
          []
        rawExecute
          "ALTER TABLE \"zcash_account\" RENAME COLUMN \"orch_spend_key\" TO \"orch_spend_key_old\";"
          []
        rawExecute
          "ALTER TABLE \"zcash_account\" RENAME COLUMN \"sap_spend_key\" TO \"sap_spend_key_old\";"
          []
        rawExecute
          "ALTER TABLE \"zcash_account\" RENAME COLUMN \"t_private_key\" TO \"t_private_key_old\";"
          []
        rawExecute
          "ALTER TABLE \"zcash_account\" ADD COLUMN \"orch_spend_key\" VARCHAR NULL DEFAULT NULL;"
          []
        rawExecute
          "ALTER TABLE \"zcash_account\" ADD COLUMN \"sap_spend_key\" VARCHAR NULL DEFAULT NULL;"
          []
        rawExecute
          "ALTER TABLE \"zcash_account\" ADD COLUMN \"t_private_key\" VARCHAR NULL DEFAULT NULL;"
          []
        rawExecute
          "UPDATE \"zcash_account\" SET \"orch_spend_key\" = \"orch_spend_key_old\", \"sap_spend_key\" = \"sap_spend_key_old\", \"t_private_key\" = \"t_private_key_old\" WHERE 1=1;"
          []
        rawExecute
          "ALTER TABLE \"zcash_account\" DROP COLUMN \"orch_spend_key_old\";"
          []
        rawExecute
          "ALTER TABLE \"zcash_account\" DROP COLUMN \"sap_spend_key_old\";"
          []
        rawExecute
          "ALTER TABLE \"zcash_account\" DROP COLUMN \"t_private_key_old\";"
          []
        rawExecute
          "ALTER TABLE \"zcash_account\" ADD COLUMN \"fvk\" VARCHAR NULL DEFAULT NULL;"
          []
        rawExecute
          "ALTER TABLE \"zcash_account\" ADD COLUMN \"ivk\" VARCHAR NULL DEFAULT NULL;"
          []
        rawExecute
          "ALTER TABLE \"zcash_account\" ADD COLUMN \"type\" VARCHAR NOT NULL DEFAULT 'Local';"
          []
    _ <-
      runNoLoggingT $
      PS.retryOnBusy $
      flip PS.runSqlPool pool $ do upsert (ZenithSchema 1 "Viewing Keys") []
    logDebugN "Version 1 changes complete"

initPool :: T.Text -> NoLoggingT IO ConnectionPool
initPool dbPath = do
  let dbInfo = PS.mkSqliteConnectionInfo dbPath
  PS.createSqlitePoolFromInfo dbInfo 5

getVersions :: ConnectionPool -> IO [Int]
getVersions pool = do
  versions <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        v <- from $ table @ZenithSchema
        orderBy [asc $ v ^. ZenithSchemaVersion]
        pure (v ^. ZenithSchemaVersion)
  return $ map (\(Value x) -> x) versions

-- | Upgrade the database
upgradeDb ::
     T.Text -- ^ database path
  -> IO ()
upgradeDb dbName = do
  PS.runSqlite dbName $ do runMigrationUnsafe migrateAll

-- | Get existing wallets from database
getWallets :: ConnectionPool -> ZcashNet -> IO [Entity ZcashWallet]
getWallets pool n =
  runNoLoggingT $
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    select $ do
      wallets <- from $ table @ZcashWallet
      where_ (wallets ^. ZcashWalletNetwork ==. val (ZcashNetDB n))
      pure wallets

walletExists :: ConnectionPool -> Int -> IO (Maybe (Entity ZcashWallet))
walletExists pool n =
  runNoLoggingT $
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    selectOne $ do
      wallets <- from $ table @ZcashWallet
      where_ (wallets ^. ZcashWalletId ==. val (toSqlKey $ fromIntegral n))
      pure wallets

getVkWallet :: ConnectionPool -> IO (Maybe (Entity ZcashWallet))
getVkWallet pool = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        wal <- from $ table @ZcashWallet
        where_ (wal ^. ZcashWalletName ==. val "Viewing Keys")
        where_ (wal ^. ZcashWalletLocal ==. val False)
        pure wal

saveVkWallet ::
     ConnectionPool -> ZcashNet -> Int -> IO (Maybe (Entity ZcashWallet))
saveVkWallet pool znet bh = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $
    insertUniqueEntity $
    ZcashWallet
      "Viewing Keys"
      (ZcashNetDB znet)
      (PhraseDB $ Phrase "")
      bh
      0
      False

getNetwork :: ConnectionPool -> WalletAddressId -> IO ZcashNet
getNetwork pool a = do
  n <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        (wallet :& acc :& addr) <-
          from $ table @ZcashWallet `innerJoin` table @ZcashAccount `on`
          (\(wallet :& acc) ->
             wallet ^. ZcashWalletId ==. acc ^. ZcashAccountWalletId) `innerJoin`
          table @WalletAddress `on`
          (\(_ :& acc :& addr) ->
             acc ^. ZcashAccountId ==. addr ^. WalletAddressAccId)
        where_ (addr ^. WalletAddressId ==. val a)
        pure $ wallet ^. ZcashWalletNetwork
  case n of
    Nothing -> throwIO $ userError "Failed to find wallet"
    Just (Value n') -> return $ getNet n'

-- | Save a new wallet to the database
saveWallet ::
     ConnectionPool -- ^ The database path to use
  -> ZcashWallet -- ^ The wallet to add to the database
  -> IO (Maybe (Entity ZcashWallet))
saveWallet pool w =
  runNoLoggingT $
  PS.retryOnBusy $ flip PS.runSqlPool pool $ insertUniqueEntity w

-- | Update the last sync block for the wallet
updateWalletSync :: ConnectionPool -> Int -> ZcashWalletId -> IO ()
updateWalletSync pool b i = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      update $ \w -> do
        set w [ZcashWalletLastSync =. val b]
        where_ $ w ^. ZcashWalletId ==. val i

-- | Returns a list of accounts associated with the given wallet
getAccounts ::
     ConnectionPool -- ^ The database path
  -> ZcashWalletId -- ^ The wallet ID to check
  -> NoLoggingT IO [Entity ZcashAccount]
getAccounts pool w =
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    select $ do
      accs <- from $ table @ZcashAccount
      where_ (accs ^. ZcashAccountWalletId ==. val w)
      pure accs

-- | Returns a list of accounts with no viewing keys. For database migration purposes
getAccountsNoVKs ::
     ConnectionPool -- ^ The database path
  -> NoLoggingT IO [(Value ZcashNetDB, Entity ZcashAccount)]
getAccountsNoVKs pool =
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    select $ do
      (wallet :& acc) <-
        from $ table @ZcashWallet `innerJoin` table @ZcashAccount `on`
        (\(wallet :& acc) ->
           wallet ^. ZcashWalletId ==. acc ^. ZcashAccountWalletId)
      where_
        (acc ^. ZcashAccountType ==. val Local &&.
         isNothing (acc ^. ZcashAccountFvk))
      pure (wallet ^. ZcashWalletNetwork, acc)

getAccountById ::
     ConnectionPool -> ZcashAccountId -> IO (Maybe (Entity ZcashAccount))
getAccountById pool za = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        accs <- from $ table @ZcashAccount
        where_ (accs ^. ZcashAccountId ==. val za)
        pure accs

-- | Returns the largest account index for the given wallet
getMaxAccount ::
     ConnectionPool -- ^ The database path
  -> ZcashWalletId -- ^ The wallet ID to check
  -> IO Int
getMaxAccount pool w = do
  a <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        accs <- from $ table @ZcashAccount
        where_ (accs ^. ZcashAccountWalletId ==. val w)
        orderBy [desc $ accs ^. ZcashAccountIndex]
        pure accs
  case a of
    Nothing -> return $ -1
    Just x -> return $ zcashAccountIndex $ entityVal x

-- | Save a new account to the database
saveAccount ::
     ConnectionPool -- ^ The database path
  -> ZcashAccount -- ^ The account to add to the database
  -> IO (Maybe (Entity ZcashAccount))
saveAccount pool a =
  runNoLoggingT $
  PS.retryOnBusy $ flip PS.runSqlPool pool $ insertUniqueEntity a

-- | Returns the largest block in storage
getMaxBlock ::
     Pool SqlBackend -- ^ The database pool
  -> ZcashNetDB
  -> IO Int
getMaxBlock pool net = do
  b <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        bls <- from $ table @ZcashBlock
        where_ (bls ^. ZcashBlockNetwork ==. val net)
        orderBy [desc $ bls ^. ZcashBlockHeight]
        pure bls
  case b of
    Nothing -> return $ -1
    Just x -> return $ zcashBlockHeight $ entityVal x

-- | Returns a list of addresses associated with the given account
getAddresses ::
     ConnectionPool -- ^ The database path
  -> ZcashAccountId -- ^ The account ID to check
  -> NoLoggingT IO [Entity WalletAddress]
getAddresses pool a =
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    select $ do
      addrs <- from $ table @WalletAddress
      where_ (addrs ^. WalletAddressAccId ==. val a)
      where_ (addrs ^. WalletAddressScope ==. val (ScopeDB External))
      orderBy [asc $ addrs ^. WalletAddressId]
      pure addrs

getAddressById ::
     ConnectionPool -> WalletAddressId -> IO (Maybe (Entity WalletAddress))
getAddressById pool a = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        addr <- from $ table @WalletAddress
        where_ (addr ^. WalletAddressId ==. val a)
        pure addr

-- | Returns a list of change addresses associated with the given account
getInternalAddresses ::
     ConnectionPool -- ^ The database path
  -> ZcashAccountId -- ^ The account ID to check
  -> IO [Entity WalletAddress]
getInternalAddresses pool a =
  runNoLoggingT $
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    select $ do
      addrs <- from $ table @WalletAddress
      where_ (addrs ^. WalletAddressAccId ==. val a)
      where_ (addrs ^. WalletAddressScope ==. val (ScopeDB Internal))
      pure addrs

-- | Returns a list of addressess associated with the given wallet
getWalletAddresses ::
     ConnectionPool -- ^ The database path
  -> ZcashWalletId -- ^ the wallet to search
  -> NoLoggingT IO [Entity WalletAddress]
getWalletAddresses pool w = do
  accs <- getAccounts pool w
  addrs <- mapM (getAddresses pool . entityKey) accs
  return $ concat addrs

getExternalAddresses :: ConnectionPool -> IO [Entity WalletAddress]
getExternalAddresses pool = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        addrs <- from $ table @WalletAddress
        where_ $ addrs ^. WalletAddressScope ==. val (ScopeDB External)
        return addrs

-- | Returns the largest address index for the given account
getMaxAddress ::
     ConnectionPool -- ^ The database path
  -> ZcashAccountId -- ^ The account ID to check
  -> Scope -- ^ The scope of the address
  -> IO Int
getMaxAddress pool aw s = do
  a <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        addrs <- from $ table @WalletAddress
        where_ $ addrs ^. WalletAddressAccId ==. val aw
        where_ $ addrs ^. WalletAddressScope ==. val (ScopeDB s)
        orderBy [desc $ addrs ^. WalletAddressIndex]
        pure addrs
  case a of
    Nothing -> return $ -1
    Just x -> return $ walletAddressIndex $ entityVal x

-- | Save a new address to the database
saveAddress ::
     ConnectionPool -- ^ the database path
  -> WalletAddress -- ^ The wallet to add to the database
  -> IO (Maybe (Entity WalletAddress))
saveAddress pool w =
  runNoLoggingT $
  PS.retryOnBusy $ flip PS.runSqlPool pool $ insertUniqueEntity w

-- * Block
-- | Save a block to the database
saveBlock :: ConnectionPool -> ZcashBlock -> IO (Key ZcashBlock)
saveBlock pool b =
  runNoLoggingT $ PS.retryOnBusy $ flip PS.runSqlPool pool $ do insert b

-- | Read a block by height
getBlock ::
     ConnectionPool -> Int -> ZcashNetDB -> IO (Maybe (Entity ZcashBlock))
getBlock pool b znet =
  runNoLoggingT $
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    selectOne $ do
      bl <- from $ table @ZcashBlock
      where_ $
        bl ^. ZcashBlockHeight ==. val b &&. bl ^. ZcashBlockNetwork ==.
        val znet
      pure bl

getBlockHash :: ConnectionPool -> Int -> ZcashNetDB -> IO (Maybe HexString)
getBlockHash pool b znet = do
  r <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        bl <- from $ table @ZcashBlock
        where_ $
          bl ^. ZcashBlockHeight ==. val b &&. bl ^. ZcashBlockNetwork ==.
          val znet
        pure $ bl ^. ZcashBlockHash
  case r of
    Nothing -> return Nothing
    Just (Value h) -> return $ Just $ getHex h

-- | Save a transaction to the data model
saveTransaction ::
     ConnectionPool -- ^ the database path
  -> ZcashBlockId -- ^ The block the transaction is in
  -> Transaction -- ^ The transaction to save
  -> NoLoggingT IO (Key ZcashTransaction)
saveTransaction pool bi wt =
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    let ix = [0 ..]
    w <- insert $ ZcashTransaction bi (HexStringDB $ tx_id wt)
    when (isJust $ tx_transpBundle wt) $ do
      _ <-
        insertMany_ $
        zipWith (curry (storeTxOut w)) ix $
        (tb_vout . fromJust . tx_transpBundle) wt
      _ <-
        insertMany_ $
        zipWith (curry (storeTxIn w)) ix $
        (tb_vin . fromJust . tx_transpBundle) wt
      return ()
    when (isJust $ tx_saplingBundle wt) $ do
      _ <-
        insertMany_ $
        zipWith (curry (storeSapSpend w)) ix $
        (sbSpends . fromJust . tx_saplingBundle) wt
      _ <-
        insertMany_ $
        zipWith (curry (storeSapOutput w)) ix $
        (sbOutputs . fromJust . tx_saplingBundle) wt
      return ()
    when (isJust $ tx_orchardBundle wt) $
      insertMany_ $
      zipWith (curry (storeOrchAction w)) ix $
      (obActions . fromJust . tx_orchardBundle) wt
    return w
  where
    storeTxOut :: ZcashTransactionId -> (Int, TxOut) -> TransparentNote
    storeTxOut wid (i, TxOut v s) = TransparentNote wid (fromIntegral v) s i
    storeTxIn :: ZcashTransactionId -> (Int, TxIn) -> TransparentSpend
    storeTxIn wid (i, TxIn (OutPoint h k) s sq) =
      TransparentSpend
        wid
        (HexStringDB . fromText $ txHashToHex h)
        (fromIntegral k)
        s
        (fromIntegral sq)
        i
    storeSapSpend :: ZcashTransactionId -> (Int, ShieldedSpend) -> ShieldSpend
    storeSapSpend wid (i, sp) =
      ShieldSpend
        wid
        (HexStringDB $ sp_cv sp)
        (HexStringDB $ sp_anchor sp)
        (HexStringDB $ sp_nullifier sp)
        (HexStringDB $ sp_rk sp)
        (HexStringDB $ sp_proof sp)
        (HexStringDB $ sp_auth sp)
        i
    storeSapOutput ::
         ZcashTransactionId -> (Int, ShieldedOutput) -> ShieldOutput
    storeSapOutput wid (i, so) =
      ShieldOutput
        wid
        (HexStringDB $ s_cv so)
        (HexStringDB $ s_cmu so)
        (HexStringDB $ s_ephKey so)
        (HexStringDB $ s_encCipherText so)
        (HexStringDB $ s_outCipherText so)
        (HexStringDB $ s_proof so)
        i
    storeOrchAction :: ZcashTransactionId -> (Int, OrchardAction) -> OrchAction
    storeOrchAction wid (i, oa) =
      OrchAction
        wid
        (HexStringDB $ nf oa)
        (HexStringDB $ rk oa)
        (HexStringDB $ cmx oa)
        (HexStringDB $ eph_key oa)
        (HexStringDB $ enc_ciphertext oa)
        (HexStringDB $ out_ciphertext oa)
        (HexStringDB $ cv oa)
        (HexStringDB $ auth oa)
        i

-- | Get the transactions from a particular block forward
getZcashTransactions ::
     ConnectionPool -- ^ The database path
  -> Int -- ^ Block
  -> ZcashNet -- ^ Network
  -> IO [Entity ZcashTransaction]
getZcashTransactions pool b net =
  runNoLoggingT $
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    select $ do
      (blks :& txs) <-
        from $ table @ZcashBlock `innerJoin` table @ZcashTransaction `on`
        (\(blks :& txs) ->
           blks ^. ZcashBlockId ==. txs ^. ZcashTransactionBlockId)
      where_ (blks ^. ZcashBlockHeight >. val b)
      where_ (blks ^. ZcashBlockNetwork ==. val (ZcashNetDB net))
      orderBy [asc $ blks ^. ZcashBlockHeight]
      return txs

-- ** QR codes
-- | Functions to manage the QR codes stored in the database
saveQrCode ::
     ConnectionPool -- ^ the connection pool
  -> QrCode
  -> NoLoggingT IO (Maybe (Entity QrCode))
saveQrCode pool qr =
  PS.retryOnBusy $ flip PS.runSqlPool pool $ insertUniqueEntity qr

getQrCodes ::
     ConnectionPool -- ^ the connection pool
  -> WalletAddressId
  -> IO [Entity QrCode]
getQrCodes pool wId =
  runNoLoggingT $
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    select $ do
      qrs <- from $ table @QrCode
      where_ $ qrs ^. QrCodeAddress ==. val wId
      return qrs

getQrCode :: ConnectionPool -> ZcashPool -> WalletAddressId -> IO (Maybe QrCode)
getQrCode pool zp wId = do
  r <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        qrs <- from $ table @QrCode
        where_ $ qrs ^. QrCodeAddress ==. val wId
        where_ $ qrs ^. QrCodeVersion ==. val zp
        return qrs
  return $ entityVal <$> r

upgradeQrTable :: ConnectionPool -> IO ()
upgradeQrTable pool = do
  r <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $
    selectOne $ do
      qrs <- from $ table @QrCode
      where_ $ qrs ^. QrCodeVersion ==. val OrchardPool
      return countRows
  unless (maybe 0 (\(Value x) -> x) r > (0 :: Int)) $ do
    _ <-
      runNoLoggingT $
      PS.retryOnBusy $
      flip PS.runSqlPool pool $ do
        rawExecute
          "update qr_code set version = ? where version = ?"
          [PersistText "OrchardPool", PersistText "Orchard"]
        rawExecute
          "update qr_code set version = ? where version = ?"
          [PersistText "SaplingPool", PersistText "Sapling"]
        rawExecute
          "update qr_code set version = ? where version = ?"
          [PersistText "TransparentPool", PersistText "Transparent"]
    return ()

upgradeAccountTable :: ConnectionPool -> NoLoggingT IO ()
upgradeAccountTable pool = do
  accs <- liftIO $ runNoLoggingT $ getAccountsNoVKs pool
  logDebugN $ T.pack $ show $ length accs
  forM_ accs $ \(Value znet, a) -> do
    FullVk b <-
      liftIO $
      deriveUfvk
        (getNet znet)
        (getOrchSK <$> zcashAccountOrchSpendKey (entityVal a))
        (getSapSK <$> zcashAccountSapSpendKey (entityVal a))
        (getTranSK <$> zcashAccountTPrivateKey (entityVal a))
    IncomingVk c <-
      liftIO $
      deriveUivk
        (getNet znet)
        (getOrchSK <$> zcashAccountOrchSpendKey (entityVal a))
        (getSapSK <$> zcashAccountSapSpendKey (entityVal a))
        (getTranSK <$> zcashAccountTPrivateKey (entityVal a))
    runNoLoggingT $
      PS.retryOnBusy $
      flip PS.runSqlPool pool $ do
        update $ \w -> do
          set
            w
            [ ZcashAccountFvk =. just (val $ UnifiedFvkDB b)
            , ZcashAccountIvk =. just (val $ UnifiedIvkDB c)
            ]
          where_ $ w ^. ZcashAccountId ==. val (entityKey a)

-- * Wallet
-- | Get the block of the last transaction known to the wallet
getMaxWalletBlock ::
     ConnectionPool -- ^ The database path
  -> IO Int
getMaxWalletBlock pool = do
  b <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        txs <- from $ table @WalletTransaction
        where_ $ txs ^. WalletTransactionBlock >. val 0
        orderBy [desc $ txs ^. WalletTransactionBlock]
        return txs
  case b of
    Nothing -> return $ -1
    Just x -> return $ walletTransactionBlock $ entityVal x

getMinBirthdayHeight :: ConnectionPool -> ZcashNetDB -> IO Int
getMinBirthdayHeight pool znet = do
  b <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        w <- from $ table @ZcashWallet
        where_
          (w ^. ZcashWalletBirthdayHeight >. val 0 &&. w ^. ZcashWalletNetwork ==.
           val znet)
        orderBy [asc $ w ^. ZcashWalletBirthdayHeight]
        pure w
  case b of
    Nothing -> return 0
    Just x -> return $ zcashWalletBirthdayHeight $ entityVal x

getLastSyncBlock :: ConnectionPool -> ZcashWalletId -> IO Int
getLastSyncBlock pool zw = do
  b <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        w <- from $ table @ZcashWallet
        where_ (w ^. ZcashWalletId ==. val zw)
        pure w
  case b of
    Nothing -> throwIO $ userError "Failed to retrieve wallet"
    Just x -> return $ zcashWalletLastSync $ entityVal x

-- | Save a @WalletTransaction@
saveWalletTransaction ::
     ConnectionPool
  -> ZcashAccountId
  -> Entity ZcashTransaction
  -> IO WalletTransactionId
saveWalletTransaction pool za zt = do
  let zT' = entityVal zt
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      b <-
        selectOne $ do
          blks <- from $ table @ZcashBlock
          where_ (blks ^. ZcashBlockId ==. val (zcashTransactionBlockId zT'))
          pure blks
      case b of
        Nothing ->
          throw $ userError "invalid block for saving wallet transaction"
        Just blk -> do
          t <-
            upsert
              (WalletTransaction
                 (zcashTransactionTxId zT')
                 za
                 (zcashBlockHeight $ entityVal blk)
                 (zcashBlockConf $ entityVal blk)
                 (zcashBlockTime $ entityVal blk))
              []
          return $ entityKey t

-- | Save a @WalletSapNote@
saveWalletSapNote ::
     ConnectionPool -- ^ The database path
  -> WalletTransactionId -- ^ The index for the transaction that contains the note
  -> Int32 -- ^ note position
  -> Bool -- ^ change flag
  -> ZcashAccountId
  -> ShieldOutputId
  -> DecodedNote -- The decoded Sapling note
  -> IO ()
saveWalletSapNote pool wId pos ch za zt dn = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      _ <-
        upsert
          (WalletSapNote
             wId
             za
             (fromIntegral $ a_value dn)
             (a_recipient dn)
             (T.filter (/= '\NUL') $ TE.decodeUtf8Lenient $ a_memo dn)
             False
             (HexStringDB $ a_nullifier dn)
             (fromIntegral pos)
             (HexStringDB $ hexString "00")
             ch
             zt
             (RseedDB $ a_rseed dn))
          []
      return ()

-- | Save a @WalletOrchNote@
saveWalletOrchNote ::
     ConnectionPool
  -> WalletTransactionId
  -> Int32
  -> Bool
  -> ZcashAccountId
  -> OrchActionId
  -> DecodedNote
  -> IO ()
saveWalletOrchNote pool wId pos ch za zt dn = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      _ <-
        upsert
          (WalletOrchNote
             wId
             za
             (fromIntegral $ a_value dn)
             (a_recipient dn)
             (T.filter (/= '\NUL') $ TE.decodeUtf8Lenient $ a_memo dn)
             False
             (HexStringDB $ a_nullifier dn)
             (fromIntegral pos)
             (HexStringDB $ hexString "00")
             ch
             zt
             (a_rho dn)
             (RseedDB $ a_rseed dn))
          []
      return ()

-- | Find the Transparent Notes that match the given transparent receiver
findTransparentNotes ::
     ConnectionPool -- ^ The database path
  -> Int -- ^ Starting block
  -> ZcashNetDB -- ^ Network to use
  -> Entity WalletAddress
  -> IO ()
findTransparentNotes pool b net t = do
  let tReceiver = t_rec =<< readUnifiedAddressDB (entityVal t)
  case tReceiver of
    Just tR -> do
      let s =
            BS.concat
              [ BS.pack [0x76, 0xA9, 0x14]
              , (toBytes . tr_bytes) tR
              , BS.pack [0x88, 0xAC]
              ]
      tN <-
        runNoLoggingT $
        PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          select $ do
            (blks :& txs :& tNotes) <-
              from $ table @ZcashBlock `innerJoin` table @ZcashTransaction `on`
              (\(blks :& txs) ->
                 blks ^. ZcashBlockId ==. txs ^. ZcashTransactionBlockId) `innerJoin`
              table @TransparentNote `on`
              (\(_ :& txs :& tNotes) ->
                 txs ^. ZcashTransactionId ==. tNotes ^. TransparentNoteTx)
            where_ (blks ^. ZcashBlockHeight >. val b)
            where_ (blks ^. ZcashBlockNetwork ==. val net)
            where_ (tNotes ^. TransparentNoteScript ==. val s)
            pure (blks, txs, tNotes)
      mapM_
        (saveWalletTrNote
           pool
           (getScope $ walletAddressScope $ entityVal t)
           (walletAddressAccId $ entityVal t)
           (entityKey t))
        tN
    Nothing -> return ()

-- | Add the transparent notes to the wallet
saveWalletTrNote ::
     ConnectionPool -- ^ the database path
  -> Scope
  -> ZcashAccountId
  -> WalletAddressId
  -> (Entity ZcashBlock, Entity ZcashTransaction, Entity TransparentNote)
  -> IO ()
saveWalletTrNote pool ch za wa (blk, zt, tn) = do
  let zT' = entityVal zt
  let b = entityVal blk
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      t <-
        upsert
          (WalletTransaction
             (zcashTransactionTxId zT')
             za
             (zcashBlockHeight b)
             (zcashBlockConf b)
             (zcashBlockTime b))
          []
      insert_ $
        WalletTrNote
          (entityKey t)
          za
          wa
          (transparentNoteValue $ entityVal tn)
          False
          (transparentNoteScript $ entityVal tn)
          (ch == Internal)
          (fromIntegral $ transparentNotePosition $ entityVal tn)

-- | Save a Sapling note to the wallet database
saveSapNote :: ConnectionPool -> WalletSapNote -> IO ()
saveSapNote pool wsn =
  runNoLoggingT $ PS.retryOnBusy $ flip PS.runSqlPool pool $ insert_ wsn

-- | Get the shielded outputs from the given blockheight
getShieldedOutputs ::
     ConnectionPool -- ^ database path
  -> Int -- ^ block
  -> ZcashNetDB -- ^ network to use
  -> IO [(Entity ZcashTransaction, Entity ShieldOutput)]
getShieldedOutputs pool b net =
  runNoLoggingT $
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    select $ do
      (blks :& txs :& sOutputs) <-
        from $ table @ZcashBlock `innerJoin` table @ZcashTransaction `on`
        (\(blks :& txs) ->
           blks ^. ZcashBlockId ==. txs ^. ZcashTransactionBlockId) `innerJoin`
        table @ShieldOutput `on`
        (\(_ :& txs :& sOutputs) ->
           txs ^. ZcashTransactionId ==. sOutputs ^. ShieldOutputTx)
      where_ (blks ^. ZcashBlockHeight >=. val b)
      where_ (blks ^. ZcashBlockNetwork ==. val net)
      orderBy
        [ asc $ txs ^. ZcashTransactionId
        , asc $ sOutputs ^. ShieldOutputPosition
        ]
      pure (txs, sOutputs)

-- | Get the Orchard actions from the given blockheight forward
getOrchardActions ::
     ConnectionPool -- ^ database path
  -> Int -- ^ block
  -> ZcashNetDB -- ^ network to use
  -> IO [(Entity ZcashTransaction, Entity OrchAction)]
getOrchardActions pool b net =
  runNoLoggingT $
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    select $ do
      (blks :& txs :& oActions) <-
        from $ table @ZcashBlock `innerJoin` table @ZcashTransaction `on`
        (\(blks :& txs) ->
           blks ^. ZcashBlockId ==. txs ^. ZcashTransactionBlockId) `innerJoin`
        table @OrchAction `on`
        (\(_ :& txs :& oActions) ->
           txs ^. ZcashTransactionId ==. oActions ^. OrchActionTx)
      where_ (blks ^. ZcashBlockHeight >=. val b)
      where_ (blks ^. ZcashBlockNetwork ==. val net)
      orderBy
        [asc $ txs ^. ZcashTransactionId, asc $ oActions ^. OrchActionPosition]
      pure (txs, oActions)

findNotesByAddress ::
     ConnectionPool -> ValidAddress -> Entity WalletAddress -> IO [ZcashNoteAPI]
findNotesByAddress pool va addr = do
  let ua =
        isValidUnifiedAddress
          ((TE.encodeUtf8 . getUA . walletAddressUAddress . entityVal) addr)
  case ua of
    Just ua' -> do
      if compareAddress va ua'
        then do
          case va of
            Unified _ -> getWalletNotes pool addr
            ZcashHaskell.Types.Sapling s -> do
              n <- getSapNotes pool $ sa_receiver s
              mapM (sapToZcashNoteAPI pool) n
            ZcashHaskell.Types.Transparent t -> do
              n <- getTrNotes pool $ ta_receiver t
              mapM (trToZcashNoteAPI pool) n
            Exchange e -> do
              n <- getTrNotes pool $ ex_address e
              mapM (trToZcashNoteAPI pool) n
        else return []
    Nothing -> return []

getTrNotes :: ConnectionPool -> TransparentReceiver -> IO [Entity WalletTrNote]
getTrNotes pool tr = do
  let s =
        BS.concat
          [ BS.pack [0x76, 0xA9, 0x14]
          , (toBytes . tr_bytes) tr
          , BS.pack [0x88, 0xAC]
          ]
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        tnotes <- from $ table @WalletTrNote
        where_ (tnotes ^. WalletTrNoteScript ==. val s)
        pure tnotes

getTrFilteredNotes ::
     ConnectionPool
  -> [HexStringDB]
  -> TransparentReceiver
  -> IO [Entity WalletTrNote]
getTrFilteredNotes pool txs tr = do
  let s =
        BS.concat
          [ BS.pack [0x76, 0xA9, 0x14]
          , (toBytes . tr_bytes) tr
          , BS.pack [0x88, 0xAC]
          ]
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        (wt :& tnotes) <-
          from $ table @WalletTransaction `innerJoin` table @WalletTrNote `on`
          (\(wt :& tnotes) ->
             wt ^. WalletTransactionId ==. tnotes ^. WalletTrNoteTx)
        where_ (tnotes ^. WalletTrNoteScript ==. val s)
        where_ (wt ^. WalletTransactionTxId `in_` valList txs)
        pure tnotes

traceTrDag :: ConnectionPool -> Entity WalletTrNote -> IO [Entity WalletTrNote]
traceTrDag pool note = do
  trSpend <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        trSpends <- from $ table @WalletTrSpend
        where_ (trSpends ^. WalletTrSpendNote ==. val (entityKey note))
        pure trSpends
  case trSpend of
    Nothing -> return []
    Just tnote -> do
      nxtChg <-
        runNoLoggingT $
        PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          selectOne $ do
            nts <- from $ table @WalletTrNote
            where_
              (nts ^. WalletTrNoteTx ==. val (walletTrSpendTx $ entityVal tnote) &&.
               nts ^.
               WalletTrNoteChange ==.
               val True)
            pure nts
      case nxtChg of
        Nothing -> return []
        Just nxt -> do
          nxtSearch <- traceTrDag pool nxt
          return $ nxt : nxtSearch

getSapNotes :: ConnectionPool -> SaplingReceiver -> IO [Entity WalletSapNote]
getSapNotes pool sr = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        snotes <- from $ table @WalletSapNote
        where_ (snotes ^. WalletSapNoteRecipient ==. val (getBytes sr))
        pure snotes

getSapFilteredNotes ::
     ConnectionPool
  -> [HexStringDB]
  -> SaplingReceiver
  -> IO [Entity WalletSapNote]
getSapFilteredNotes pool txs sr = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        (wt :& snotes) <-
          from $ table @WalletTransaction `innerJoin` table @WalletSapNote `on`
          (\(wt :& snotes) ->
             wt ^. WalletTransactionId ==. snotes ^. WalletSapNoteTx)
        where_ (snotes ^. WalletSapNoteRecipient ==. val (getBytes sr))
        where_ (wt ^. WalletTransactionTxId `in_` valList txs)
        pure snotes

traceSapDag ::
     ConnectionPool -> Entity WalletSapNote -> IO [Entity WalletSapNote]
traceSapDag pool note = do
  sapSpend <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        sapSpends <- from $ table @WalletSapSpend
        where_ (sapSpends ^. WalletSapSpendNote ==. val (entityKey note))
        pure sapSpends
  case sapSpend of
    Nothing -> return []
    Just snote -> do
      nxtChg <-
        runNoLoggingT $
        PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          selectOne $ do
            nts <- from $ table @WalletSapNote
            where_
              (nts ^. WalletSapNoteTx ==.
               val (walletSapSpendTx $ entityVal snote) &&.
               nts ^.
               WalletSapNoteChange ==.
               val True)
            pure nts
      case nxtChg of
        Nothing -> return []
        Just nxt -> do
          nxtSearch <- traceSapDag pool nxt
          return $ nxt : nxtSearch

getOrchNotes :: ConnectionPool -> OrchardReceiver -> IO [Entity WalletOrchNote]
getOrchNotes pool o = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        onotes <- from $ table @WalletOrchNote
        where_ (onotes ^. WalletOrchNoteRecipient ==. val (getBytes o))
        pure onotes

getOrchFilteredNotes ::
     ConnectionPool
  -> [HexStringDB]
  -> OrchardReceiver
  -> IO [Entity WalletOrchNote]
getOrchFilteredNotes pool txs o = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        (wt :& onotes) <-
          from $ table @WalletTransaction `innerJoin` table @WalletOrchNote `on`
          (\(wt :& onotes) ->
             wt ^. WalletTransactionId ==. onotes ^. WalletOrchNoteTx)
        where_ (onotes ^. WalletOrchNoteRecipient ==. val (getBytes o))
        where_ (wt ^. WalletTransactionTxId `in_` valList txs)
        pure onotes

traceOrchDag ::
     ConnectionPool -> Entity WalletOrchNote -> IO [Entity WalletOrchNote]
traceOrchDag pool note = do
  orchSpend <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        orchSpends <- from $ table @WalletOrchSpend
        where_ (orchSpends ^. WalletOrchSpendNote ==. val (entityKey note))
        pure orchSpends
  case orchSpend of
    Nothing -> return []
    Just onote -> do
      nxtChg <-
        runNoLoggingT $
        PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          selectOne $ do
            nts <- from $ table @WalletOrchNote
            where_
              (nts ^. WalletOrchNoteTx ==.
               val (walletOrchSpendTx $ entityVal onote) &&.
               nts ^.
               WalletOrchNoteChange ==.
               val True)
            pure nts
      case nxtChg of
        Nothing -> return []
        Just nxt -> do
          nxtSearch <- traceOrchDag pool nxt
          return $ nxt : nxtSearch

getWalletNotes ::
     ConnectionPool -- ^ database path
  -> Entity WalletAddress
  -> IO [ZcashNoteAPI]
getWalletNotes pool w = do
  let w' = entityVal w
  let tReceiver = t_rec =<< readUnifiedAddressDB w'
  let sReceiver = s_rec =<< readUnifiedAddressDB w'
  let oReceiver = o_rec =<< readUnifiedAddressDB w'
  trNotes <-
    case tReceiver of
      Nothing -> return []
      Just tR -> getTrNotes pool tR
  sapNotes <-
    case sReceiver of
      Nothing -> return []
      Just sR -> getSapNotes pool sR
  orchNotes <-
    case oReceiver of
      Nothing -> return []
      Just oR -> getOrchNotes pool oR
  trNotes' <- mapM (trToZcashNoteAPI pool) trNotes
  sapNotes' <- mapM (sapToZcashNoteAPI pool) sapNotes
  orchNotes' <- mapM (orchToZcashNoteAPI pool) orchNotes
  return $ trNotes' <> sapNotes' <> orchNotes'

-- | Get the transactions belonging to the given address
getWalletTransactions ::
     ConnectionPool -- ^ database path
  -> Entity WalletAddress
  -> NoLoggingT IO ()
getWalletTransactions pool w = do
  let w' = entityVal w
  chgAddr <-
    liftIO $ getInternalAddresses pool $ walletAddressAccId $ entityVal w
  let ctReceiver = t_rec =<< readUnifiedAddressDB (entityVal $ head chgAddr)
  let csReceiver = s_rec =<< readUnifiedAddressDB (entityVal $ head chgAddr)
  let coReceiver = o_rec =<< readUnifiedAddressDB (entityVal $ head chgAddr)
  let tReceiver = t_rec =<< readUnifiedAddressDB w'
  let sReceiver = s_rec =<< readUnifiedAddressDB w'
  let oReceiver = o_rec =<< readUnifiedAddressDB w'
  trNotes <-
    case tReceiver of
      Nothing -> return []
      Just tR -> liftIO $ getTrNotes pool tR
  sapNotes <-
    case sReceiver of
      Nothing -> return []
      Just sR -> liftIO $ getSapNotes pool sR
  orchNotes <-
    case oReceiver of
      Nothing -> return []
      Just oR -> liftIO $ getOrchNotes pool oR
  clearUserTx (entityKey w)
  mapM_ addTr trNotes
  mapM_ addSap sapNotes
  mapM_ addOrch orchNotes
  trSpends <-
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        trSpends <- from $ table @WalletTrSpend
        where_
          (trSpends ^. WalletTrSpendNote `in_` valList (map entityKey trNotes))
        pure trSpends
  sapSpends <- mapM (getSapSpends . entityKey) sapNotes
  orchSpends <- mapM (getOrchSpends . entityKey) orchNotes
  mapM_ subTSpend trSpends
  mapM_ subSSpend $ catMaybes sapSpends
  mapM_ subOSpend $ catMaybes orchSpends
  foundTxs <- getTxs $ entityKey w
  trChgNotes <-
    case ctReceiver of
      Nothing -> return []
      Just tR -> liftIO $ getTrFilteredNotes pool foundTxs tR
  trChgNotes' <- liftIO $ mapM (traceTrDag pool) trChgNotes
  trChgSpends <-
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        trS <- from $ table @WalletTrSpend
        where_
          (trS ^. WalletTrSpendNote `in_`
           valList (map entityKey (trChgNotes <> concat trChgNotes')))
        pure trS
  sapChgNotes <-
    case csReceiver of
      Nothing -> return []
      Just sR -> liftIO $ getSapFilteredNotes pool foundTxs sR
  sapChgNotes' <- liftIO $ mapM (traceSapDag pool) sapChgNotes
  sapChgSpends <-
    mapM (getSapSpends . entityKey) (sapChgNotes <> concat sapChgNotes')
  orchChgNotes <-
    case coReceiver of
      Nothing -> return []
      Just oR -> liftIO $ getOrchFilteredNotes pool foundTxs oR
  orchChgNotes' <- liftIO $ mapM (traceOrchDag pool) orchChgNotes
  orchChgSpends <-
    mapM (getOrchSpends . entityKey) (orchChgNotes <> concat orchChgNotes')
  mapM_ addTr (trChgNotes <> concat trChgNotes')
  mapM_ addSap (sapChgNotes <> concat sapChgNotes')
  mapM_ addOrch (orchChgNotes <> concat orchChgNotes')
  mapM_ subTSpend trChgSpends
  mapM_ subSSpend $ catMaybes sapChgSpends
  mapM_ subOSpend $ catMaybes orchChgSpends
  where
    clearUserTx :: WalletAddressId -> NoLoggingT IO ()
    clearUserTx waId = do
      PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          delete $ do
            u <- from $ table @UserTx
            where_ (u ^. UserTxAddress ==. val waId)
            return ()
    getTxs :: WalletAddressId -> NoLoggingT IO [HexStringDB]
    getTxs waId = do
      res <-
        PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          select $ do
            t <- from $ table @UserTx
            where_ (t ^. UserTxAddress ==. val waId)
            return (t ^. UserTxHex)
      return $ map (\(Value x) -> x) res
    getSapSpends ::
         WalletSapNoteId -> NoLoggingT IO (Maybe (Entity WalletSapSpend))
    getSapSpends n = do
      PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          selectOne $ do
            sapSpends <- from $ table @WalletSapSpend
            where_ (sapSpends ^. WalletSapSpendNote ==. val n)
            pure sapSpends
    getOrchSpends ::
         WalletOrchNoteId -> NoLoggingT IO (Maybe (Entity WalletOrchSpend))
    getOrchSpends n = do
      PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          selectOne $ do
            orchSpends <- from $ table @WalletOrchSpend
            where_ (orchSpends ^. WalletOrchSpendNote ==. val n)
            pure orchSpends
    addTr :: Entity WalletTrNote -> NoLoggingT IO ()
    addTr n =
      upsertUserTx
        (walletTrNoteTx $ entityVal n)
        (entityKey w)
        (fromIntegral $ walletTrNoteValue $ entityVal n)
        ""
    addSap :: Entity WalletSapNote -> NoLoggingT IO ()
    addSap n =
      upsertUserTx
        (walletSapNoteTx $ entityVal n)
        (entityKey w)
        (fromIntegral $ walletSapNoteValue $ entityVal n)
        (walletSapNoteMemo $ entityVal n)
    addOrch :: Entity WalletOrchNote -> NoLoggingT IO ()
    addOrch n =
      upsertUserTx
        (walletOrchNoteTx $ entityVal n)
        (entityKey w)
        (fromIntegral $ walletOrchNoteValue $ entityVal n)
        (walletOrchNoteMemo $ entityVal n)
    subTSpend :: Entity WalletTrSpend -> NoLoggingT IO ()
    subTSpend n =
      upsertUserTx
        (walletTrSpendTx $ entityVal n)
        (entityKey w)
        (-(fromIntegral $ walletTrSpendValue $ entityVal n))
        ""
    subSSpend :: Entity WalletSapSpend -> NoLoggingT IO ()
    subSSpend n =
      upsertUserTx
        (walletSapSpendTx $ entityVal n)
        (entityKey w)
        (-(fromIntegral $ walletSapSpendValue $ entityVal n))
        ""
    subOSpend :: Entity WalletOrchSpend -> NoLoggingT IO ()
    subOSpend n =
      upsertUserTx
        (walletOrchSpendTx $ entityVal n)
        (entityKey w)
        (-(fromIntegral $ walletOrchSpendValue $ entityVal n))
        ""
    upsertUserTx ::
         WalletTransactionId
      -> WalletAddressId
      -> Int
      -> T.Text
      -> NoLoggingT IO ()
    upsertUserTx tId wId amt memo = do
      tr <-
        PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          select $ do
            tx <- from $ table @WalletTransaction
            where_ (tx ^. WalletTransactionId ==. val tId)
            pure tx
      existingUtx <-
        PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          selectOne $ do
            ut <- from $ table @UserTx
            where_
              (ut ^. UserTxHex ==.
               val (walletTransactionTxId $ entityVal $ head tr))
            where_ (ut ^. UserTxAddress ==. val wId)
            pure ut
      case existingUtx of
        Nothing -> do
          _ <-
            PS.retryOnBusy $
            flip PS.runSqlPool pool $ do
              upsert
                (UserTx
                   (walletTransactionTxId $ entityVal $ head tr)
                   wId
                   (walletTransactionTime $ entityVal $ head tr)
                   amt
                   memo)
                []
          return ()
        Just uTx -> do
          _ <-
            PS.retryOnBusy $
            flip PS.runSqlPool pool $ do
              update $ \t -> do
                set
                  t
                  [ UserTxAmount +=. val amt
                  , UserTxMemo =.
                    val (memo <> " " <> userTxMemo (entityVal uTx))
                  ]
                where_ (t ^. UserTxId ==. val (entityKey uTx))
          return ()

getWalletTransaction ::
     ConnectionPool
  -> WalletTransactionId
  -> IO (Maybe (Entity WalletTransaction))
getWalletTransaction pool i =
  runNoLoggingT $
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    selectOne $ do
      trs <- from $ table @WalletTransaction
      where_ (trs ^. WalletTransactionId ==. val i)
      pure trs

getUserTx :: ConnectionPool -> WalletAddressId -> IO [Entity UserTx]
getUserTx pool aId = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        uTxs <- from $ table @UserTx
        where_ (uTxs ^. UserTxAddress ==. val aId)
        orderBy [asc $ uTxs ^. UserTxTime]
        return uTxs

-- | Get wallet transparent notes by account
getWalletTrNotes :: ConnectionPool -> ZcashAccountId -> IO [Entity WalletTrNote]
getWalletTrNotes pool za = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        n <- from $ table @WalletTrNote
        where_ (n ^. WalletTrNoteAccId ==. val za)
        pure n

-- | find Transparent spends
findTransparentSpends :: ConnectionPool -> ZcashAccountId -> IO ()
findTransparentSpends pool za = do
  notes <- getWalletTrNotes pool za
  mapM_ findOneTrSpend notes
  where
    findOneTrSpend :: Entity WalletTrNote -> IO ()
    findOneTrSpend n = do
      mReverseTxId <-
        runNoLoggingT $
        PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          selectOne $ do
            wtx <- from $ table @WalletTransaction
            where_
              (wtx ^. WalletTransactionId ==. val (walletTrNoteTx $ entityVal n))
            pure $ wtx ^. WalletTransactionTxId
      case mReverseTxId of
        Nothing -> throwIO $ userError "failed to get tx ID"
        Just (Value reverseTxId) -> do
          let flipTxId =
                HexStringDB $
                HexString $ BS.reverse $ toBytes $ getHex reverseTxId
          s <-
            runNoLoggingT $
            PS.retryOnBusy $
            flip PS.runSqlPool pool $ do
              select $ do
                (tx :& trSpends) <-
                  from $
                  table @ZcashTransaction `innerJoin` table @TransparentSpend `on`
                  (\(tx :& trSpends) ->
                     tx ^. ZcashTransactionId ==. trSpends ^. TransparentSpendTx)
                where_
                  (trSpends ^. TransparentSpendOutPointHash ==. val flipTxId)
                where_
                  (trSpends ^. TransparentSpendOutPointIndex ==.
                   val (fromIntegral $ walletTrNotePosition $ entityVal n))
                pure (tx, trSpends)
          if null s
            then return ()
            else do
              runNoLoggingT $
                PS.retryOnBusy $
                flip PS.runSqlPool pool $ do
                  _ <-
                    update $ \w -> do
                      set w [WalletTrNoteSpent =. val True]
                      where_ $ w ^. WalletTrNoteId ==. val (entityKey n)
                  t' <- upsertWalTx (entityVal $ fst $ head s) za
                  _ <-
                    upsert
                      (WalletTrSpend
                         (entityKey t')
                         (entityKey n)
                         za
                         (walletTrNoteValue $ entityVal n))
                      []
                  return ()

getWalletSapNotes ::
     ConnectionPool -> ZcashAccountId -> IO [Entity WalletSapNote]
getWalletSapNotes pool za = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        n <- from $ table @WalletSapNote
        where_ (n ^. WalletSapNoteAccId ==. val za)
        pure n

-- | Sapling DAG-aware spend tracking
findSapSpends ::
     ConnectionPool -> ZcashAccountId -> [Entity WalletSapNote] -> IO ()
findSapSpends _ _ [] = return ()
findSapSpends pool za (n:notes) = do
  s <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        (tx :& sapSpends) <-
          from $ table @ZcashTransaction `innerJoin` table @ShieldSpend `on`
          (\(tx :& sapSpends) ->
             tx ^. ZcashTransactionId ==. sapSpends ^. ShieldSpendTx)
        where_
          (sapSpends ^. ShieldSpendNullifier ==.
           val (walletSapNoteNullifier (entityVal n)))
        pure (tx, sapSpends)
  if null s
    then findSapSpends pool za notes
    else do
      runNoLoggingT $
        PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          _ <-
            update $ \w -> do
              set w [WalletSapNoteSpent =. val True]
              where_ $ w ^. WalletSapNoteId ==. val (entityKey n)
          t' <- upsertWalTx (entityVal $ fst $ head s) za
          _ <-
            upsert
              (WalletSapSpend
                 (entityKey t')
                 (entityKey n)
                 za
                 (walletSapNoteValue $ entityVal n))
              []
          return ()
      findSapSpends pool za notes

getWalletOrchNotes ::
     ConnectionPool -> ZcashAccountId -> IO [Entity WalletOrchNote]
getWalletOrchNotes pool za = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        n <- from $ table @WalletOrchNote
        where_ (n ^. WalletOrchNoteAccId ==. val za)
        pure n

getUnspentSapNotes :: ConnectionPool -> IO [Entity WalletSapNote]
getUnspentSapNotes pool = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        n <- from $ table @WalletSapNote
        where_ (n ^. WalletSapNoteSpent ==. val False)
        pure n

getSaplingCmus ::
     ConnectionPool
  -> ShieldOutputId
  -> ShieldOutputId
  -> IO [Value HexStringDB]
getSaplingCmus pool zt m = do
  PS.runSqlPool
    (select $ do
       n <- from $ table @ShieldOutput
       where_ (n ^. ShieldOutputId >. val zt &&. n ^. ShieldOutputId <=. val m)
       orderBy [asc $ n ^. ShieldOutputId]
       pure $ n ^. ShieldOutputCmu)
    pool

getMaxSaplingNote :: Pool SqlBackend -> IO ShieldOutputId
getMaxSaplingNote pool = do
  flip PS.runSqlPool pool $ do
    maxBlock <-
      selectOne $ do
        blks <- from $ table @ZcashBlock
        where_ $ blks ^. ZcashBlockHeight >. val 0
        orderBy [desc $ blks ^. ZcashBlockHeight]
        pure $ blks ^. ZcashBlockHeight
    case maxBlock of
      Nothing -> return $ toSqlKey 0
      Just (Value mb) -> do
        x <-
          selectOne $ do
            (blks :& txs :& n) <-
              from $ table @ZcashBlock `innerJoin` table @ZcashTransaction `on`
              (\(blks :& txs) ->
                 blks ^. ZcashBlockId ==. txs ^. ZcashTransactionBlockId) `innerJoin`
              table @ShieldOutput `on`
              (\(_ :& txs :& n) ->
                 txs ^. ZcashTransactionId ==. n ^. ShieldOutputTx)
            where_ (blks ^. ZcashBlockHeight <=. val (mb - 5))
            orderBy [desc $ n ^. ShieldOutputId]
            pure (n ^. ShieldOutputId)
        case x of
          Nothing -> return $ toSqlKey 0
          Just (Value y) -> return y

updateSapNoteRecord ::
     Pool SqlBackend
  -> WalletSapNoteId
  -> SaplingWitness
  -> ShieldOutputId
  -> IO ()
updateSapNoteRecord pool n w o = do
  flip PS.runSqlPool pool $ do
    update $ \x -> do
      set
        x
        [ WalletSapNoteWitness =. val (HexStringDB $ sapWit w)
        , WalletSapNoteWitPos =. val o
        ]
      where_ (x ^. WalletSapNoteId ==. val n)

getUnspentOrchNotes :: ConnectionPool -> IO [Entity WalletOrchNote]
getUnspentOrchNotes pool = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        n <- from $ table @WalletOrchNote
        where_ (n ^. WalletOrchNoteSpent ==. val False)
        pure n

getOrchardCmxs ::
     ConnectionPool -> OrchActionId -> OrchActionId -> IO [Value HexStringDB]
getOrchardCmxs pool zt m = do
  PS.runSqlPool
    (select $ do
       n <- from $ table @OrchAction
       where_ (n ^. OrchActionId >. val zt &&. n ^. OrchActionId <=. val m)
       orderBy [asc $ n ^. OrchActionId]
       pure $ n ^. OrchActionCmx)
    pool

getMaxOrchardNote :: Pool SqlBackend -> IO OrchActionId
getMaxOrchardNote pool = do
  flip PS.runSqlPool pool $ do
    maxBlock <-
      selectOne $ do
        blks <- from $ table @ZcashBlock
        where_ $ blks ^. ZcashBlockHeight >. val 0
        orderBy [desc $ blks ^. ZcashBlockHeight]
        pure $ blks ^. ZcashBlockHeight
    case maxBlock of
      Nothing -> return $ toSqlKey 0
      Just (Value mb) -> do
        x <-
          selectOne $ do
            (blks :& txs :& n) <-
              from $ table @ZcashBlock `innerJoin` table @ZcashTransaction `on`
              (\(blks :& txs) ->
                 blks ^. ZcashBlockId ==. txs ^. ZcashTransactionBlockId) `innerJoin`
              table @OrchAction `on`
              (\(_ :& txs :& n) ->
                 txs ^. ZcashTransactionId ==. n ^. OrchActionTx)
            where_ (blks ^. ZcashBlockHeight <=. val (mb - 5))
            orderBy [desc $ n ^. OrchActionId]
            pure (n ^. OrchActionId)
        case x of
          Nothing -> return $ toSqlKey 0
          Just (Value y) -> return y

updateOrchNoteRecord ::
     Pool SqlBackend
  -> WalletOrchNoteId
  -> OrchardWitness
  -> OrchActionId
  -> IO ()
updateOrchNoteRecord pool n w o = do
  flip PS.runSqlPool pool $ do
    update $ \x -> do
      set
        x
        [ WalletOrchNoteWitness =. val (HexStringDB $ orchWit w)
        , WalletOrchNoteWitPos =. val o
        ]
      where_ (x ^. WalletOrchNoteId ==. val n)

findOrchSpends ::
     ConnectionPool -> ZcashAccountId -> [Entity WalletOrchNote] -> IO ()
findOrchSpends _ _ [] = return ()
findOrchSpends pool za (n:notes) = do
  s <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        (tx :& orchSpends) <-
          from $ table @ZcashTransaction `innerJoin` table @OrchAction `on`
          (\(tx :& orchSpends) ->
             tx ^. ZcashTransactionId ==. orchSpends ^. OrchActionTx)
        where_
          (orchSpends ^. OrchActionNf ==.
           val (walletOrchNoteNullifier (entityVal n)))
        pure (tx, orchSpends)
  if null s
    then findOrchSpends pool za notes
    else do
      runNoLoggingT $
        PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          _ <-
            update $ \w -> do
              set w [WalletOrchNoteSpent =. val True]
              where_ $ w ^. WalletOrchNoteId ==. val (entityKey n)
          t' <- upsertWalTx (entityVal $ fst $ head s) za
          _ <-
            upsert
              (WalletOrchSpend
                 (entityKey t')
                 (entityKey n)
                 za
                 (walletOrchNoteValue $ entityVal n))
              []
          return ()
      findOrchSpends pool za notes

upsertWalTx ::
     MonadIO m
  => ZcashTransaction
  -> ZcashAccountId
  -> SqlPersistT m (Entity WalletTransaction)
upsertWalTx zt za = do
  blk <-
    selectOne $ do
      blks <- from $ table @ZcashBlock
      where_ (blks ^. ZcashBlockId ==. val (zcashTransactionBlockId zt))
      pure blks
  case blk of
    Nothing -> throw $ userError "Invalid block for transaction"
    Just b ->
      upsert
        (WalletTransaction
           (zcashTransactionTxId zt)
           za
           (zcashBlockHeight $ entityVal b)
           (zcashBlockConf $ entityVal b)
           (zcashBlockTime $ entityVal b))
        []

getSaplingOutIndex :: ConnectionPool -> ShieldOutputId -> IO Int
getSaplingOutIndex pool i = do
  o <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        sout <- from $ table @ShieldOutput
        where_ (sout ^. ShieldOutputId ==. val i)
        pure $ sout ^. ShieldOutputPosition
  case o of
    Nothing -> throwIO $ userError "couldn't find shielded output"
    Just (Value o') -> return o'

getOrchardOutIndex :: ConnectionPool -> OrchActionId -> IO Int
getOrchardOutIndex pool i = do
  o <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        sout <- from $ table @OrchAction
        where_ (sout ^. OrchActionId ==. val i)
        pure $ sout ^. OrchActionPosition
  case o of
    Nothing -> throwIO $ userError "couldn't find orchard action"
    Just (Value o') -> return o'

getBalance :: ConnectionPool -> ZcashAccountId -> IO Integer
getBalance pool za = do
  trNotes <- getWalletUnspentTrNotes pool za
  let tAmts = map (walletTrNoteValue . entityVal) trNotes
  let tBal = sum tAmts
  sapNotes <- getWalletUnspentSapNotes pool za
  let sAmts = map (walletSapNoteValue . entityVal) sapNotes
  let sBal = sum sAmts
  orchNotes <- getWalletUnspentOrchNotes pool za
  let oAmts = map (walletOrchNoteValue . entityVal) orchNotes
  let oBal = sum oAmts
  return . fromIntegral $ tBal + sBal + oBal

getTransparentBalance :: ConnectionPool -> ZcashAccountId -> IO Integer
getTransparentBalance pool za = do
  trNotes <- getWalletUnspentTrNotes pool za
  let tAmts = map (walletTrNoteValue . entityVal) trNotes
  return . fromIntegral $ sum tAmts

getShieldedBalance :: ConnectionPool -> ZcashAccountId -> IO Integer
getShieldedBalance pool za = do
  sapNotes <- getWalletUnspentSapNotes pool za
  let sAmts = map (walletSapNoteValue . entityVal) sapNotes
  let sBal = sum sAmts
  orchNotes <- getWalletUnspentOrchNotes pool za
  let oAmts = map (walletOrchNoteValue . entityVal) orchNotes
  let oBal = sum oAmts
  return . fromIntegral $ sBal + oBal

getUnconfirmedBalance :: ConnectionPool -> ZcashAccountId -> IO Integer
getUnconfirmedBalance pool za = do
  trNotes <- getWalletUnspentUnconfirmedTrNotes pool za
  let tAmts = map (walletTrNoteValue . entityVal) trNotes
  let tBal = sum tAmts
  sapNotes <- getWalletUnspentUnconfirmedSapNotes pool za
  let sAmts = map (walletSapNoteValue . entityVal) sapNotes
  let sBal = sum sAmts
  orchNotes <- getWalletUnspentUnconfirmedOrchNotes pool za
  let oAmts = map (walletOrchNoteValue . entityVal) orchNotes
  let oBal = sum oAmts
  return . fromIntegral $ tBal + sBal + oBal

getPoolBalance :: ConnectionPool -> ZcashAccountId -> IO AccountBalance
getPoolBalance pool za = do
  trNotes <- getWalletUnspentTrNotes pool za
  let tAmts = map (walletTrNoteValue . entityVal) trNotes
  let tBal = sum tAmts
  sapNotes <- getWalletUnspentSapNotes pool za
  let sAmts = map (walletSapNoteValue . entityVal) sapNotes
  let sBal = sum sAmts
  orchNotes <- getWalletUnspentOrchNotes pool za
  let oAmts = map (walletOrchNoteValue . entityVal) orchNotes
  let oBal = sum oAmts
  return $ AccountBalance tBal sBal oBal

getUnconfPoolBalance :: ConnectionPool -> ZcashAccountId -> IO AccountBalance
getUnconfPoolBalance pool za = do
  trNotes <- getWalletUnspentUnconfirmedTrNotes pool za
  let tAmts = map (walletTrNoteValue . entityVal) trNotes
  let tBal = sum tAmts
  sapNotes <- getWalletUnspentUnconfirmedSapNotes pool za
  let sAmts = map (walletSapNoteValue . entityVal) sapNotes
  let sBal = sum sAmts
  orchNotes <- getWalletUnspentUnconfirmedOrchNotes pool za
  let oAmts = map (walletOrchNoteValue . entityVal) orchNotes
  let oBal = sum oAmts
  return $ AccountBalance tBal sBal oBal

rewindWalletTransactions :: ConnectionPool -> Int -> IO ()
rewindWalletTransactions pool b = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      delete $ do
        _ <- from $ table @UserTx
        return ()
      oldTxs <-
        select $ do
          txs <- from $ table @WalletTransaction
          where_ $ txs ^. WalletTransactionBlock >. val b
          pure txs
      let oldKeys = map entityKey oldTxs
      delete $ do
        x <- from $ table @WalletOrchSpend
        where_ $ x ^. WalletOrchSpendTx `in_` valList oldKeys
        return ()
      delete $ do
        x <- from $ table @WalletOrchNote
        where_ $ x ^. WalletOrchNoteTx `in_` valList oldKeys
        return ()
      delete $ do
        x <- from $ table @WalletSapSpend
        where_ $ x ^. WalletSapSpendTx `in_` valList oldKeys
        return ()
      delete $ do
        x <- from $ table @WalletSapNote
        where_ $ x ^. WalletSapNoteTx `in_` valList oldKeys
        return ()
      delete $ do
        x <- from $ table @WalletTrSpend
        where_ $ x ^. WalletTrSpendTx `in_` valList oldKeys
        return ()
      delete $ do
        x <- from $ table @WalletTrNote
        where_ $ x ^. WalletTrNoteTx `in_` valList oldKeys
        return ()
      delete $ do
        txs <- from $ table @WalletTransaction
        where_ $ txs ^. WalletTransactionBlock >. val b
        return ()
      update $ \w -> do
        set w [ZcashWalletLastSync =. val b]

clearWalletTransactions :: ConnectionPool -> IO ()
clearWalletTransactions pool = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      delete $ do
        _ <- from $ table @UserTx
        return ()
      delete $ do
        _ <- from $ table @WalletOrchSpend
        return ()
      delete $ do
        _ <- from $ table @WalletOrchNote
        return ()
      delete $ do
        _ <- from $ table @WalletSapSpend
        return ()
      delete $ do
        _ <- from $ table @WalletSapNote
        return ()
      delete $ do
        _ <- from $ table @WalletTrNote
        return ()
      delete $ do
        _ <- from $ table @WalletTrSpend
        return ()
      delete $ do
        _ <- from $ table @WalletTransaction
        return ()
      update $ \w -> do
        set w [ZcashWalletLastSync =. val 0]

clearWalletData :: ConnectionPool -> IO ()
clearWalletData pool = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      delete $ do
        _ <- from $ table @TreeStore
        return ()
      delete $ do
        _ <- from $ table @TransparentNote
        return ()
      delete $ do
        _ <- from $ table @TransparentSpend
        return ()
      delete $ do
        _ <- from $ table @OrchAction
        return ()
      delete $ do
        _ <- from $ table @ShieldOutput
        return ()
      delete $ do
        _ <- from $ table @ShieldSpend
        return ()
      delete $ do
        _ <- from $ table @ZcashTransaction
        return ()
      delete $ do
        _ <- from $ table @ZcashBlock
        return ()

getWalletUnspentTrNotes ::
     ConnectionPool -> ZcashAccountId -> IO [Entity WalletTrNote]
getWalletUnspentTrNotes pool za = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        (txs :& tNotes) <-
          from $ table @WalletTransaction `innerJoin` table @WalletTrNote `on`
          (\(txs :& tNotes) ->
             txs ^. WalletTransactionId ==. tNotes ^. WalletTrNoteTx)
        where_ (tNotes ^. WalletTrNoteAccId ==. val za)
        where_ (tNotes ^. WalletTrNoteSpent ==. val False)
        where_
          ((tNotes ^. WalletTrNoteChange ==. val True &&. txs ^.
            WalletTransactionConf >=.
            val 3) ||.
           (tNotes ^. WalletTrNoteChange ==. val False &&. txs ^.
            WalletTransactionConf >=.
            val 10))
        pure tNotes

getWalletUnspentUnconfirmedTrNotes ::
     ConnectionPool -> ZcashAccountId -> IO [Entity WalletTrNote]
getWalletUnspentUnconfirmedTrNotes pool za = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        (txs :& tNotes) <-
          from $ table @WalletTransaction `innerJoin` table @WalletTrNote `on`
          (\(txs :& tNotes) ->
             txs ^. WalletTransactionId ==. tNotes ^. WalletTrNoteTx)
        where_ (tNotes ^. WalletTrNoteAccId ==. val za)
        where_ (tNotes ^. WalletTrNoteSpent ==. val False)
        where_
          ((tNotes ^. WalletTrNoteChange ==. val True &&. txs ^.
            WalletTransactionConf <.
            val 3) ||.
           (tNotes ^. WalletTrNoteChange ==. val False &&. txs ^.
            WalletTransactionConf <.
            val 10))
        pure tNotes

getWalletUnspentSapNotes ::
     ConnectionPool -> ZcashAccountId -> IO [Entity WalletSapNote]
getWalletUnspentSapNotes pool za = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        (txs :& sNotes) <-
          from $ table @WalletTransaction `innerJoin` table @WalletSapNote `on`
          (\(txs :& sNotes) ->
             txs ^. WalletTransactionId ==. sNotes ^. WalletSapNoteTx)
        where_ (sNotes ^. WalletSapNoteAccId ==. val za)
        where_ (sNotes ^. WalletSapNoteSpent ==. val False)
        where_
          ((sNotes ^. WalletSapNoteChange ==. val True &&. txs ^.
            WalletTransactionConf >=.
            val 3) ||.
           (sNotes ^. WalletSapNoteChange ==. val False &&. txs ^.
            WalletTransactionConf >=.
            val 10))
        pure sNotes

getWalletUnspentUnconfirmedSapNotes ::
     ConnectionPool -> ZcashAccountId -> IO [Entity WalletSapNote]
getWalletUnspentUnconfirmedSapNotes pool za = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        (txs :& sNotes) <-
          from $ table @WalletTransaction `innerJoin` table @WalletSapNote `on`
          (\(txs :& sNotes) ->
             txs ^. WalletTransactionId ==. sNotes ^. WalletSapNoteTx)
        where_ (sNotes ^. WalletSapNoteAccId ==. val za)
        where_ (sNotes ^. WalletSapNoteSpent ==. val False)
        where_
          ((sNotes ^. WalletSapNoteChange ==. val True &&. txs ^.
            WalletTransactionConf <.
            val 3) ||.
           (sNotes ^. WalletSapNoteChange ==. val False &&. txs ^.
            WalletTransactionConf <.
            val 10))
        pure sNotes

getWalletUnspentOrchNotes ::
     ConnectionPool -> ZcashAccountId -> IO [Entity WalletOrchNote]
getWalletUnspentOrchNotes pool za = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        (txs :& oNotes) <-
          from $ table @WalletTransaction `innerJoin` table @WalletOrchNote `on`
          (\(txs :& oNotes) ->
             txs ^. WalletTransactionId ==. oNotes ^. WalletOrchNoteTx)
        where_ (oNotes ^. WalletOrchNoteAccId ==. val za)
        where_ (oNotes ^. WalletOrchNoteSpent ==. val False)
        where_
          ((oNotes ^. WalletOrchNoteChange ==. val True &&. txs ^.
            WalletTransactionConf >=.
            val 3) ||.
           (oNotes ^. WalletOrchNoteChange ==. val False &&. txs ^.
            WalletTransactionConf >=.
            val 10))
        pure oNotes

getWalletUnspentUnconfirmedOrchNotes ::
     ConnectionPool -> ZcashAccountId -> IO [Entity WalletOrchNote]
getWalletUnspentUnconfirmedOrchNotes pool za = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        (txs :& oNotes) <-
          from $ table @WalletTransaction `innerJoin` table @WalletOrchNote `on`
          (\(txs :& oNotes) ->
             txs ^. WalletTransactionId ==. oNotes ^. WalletOrchNoteTx)
        where_ (oNotes ^. WalletOrchNoteAccId ==. val za)
        where_ (oNotes ^. WalletOrchNoteSpent ==. val False)
        where_
          ((oNotes ^. WalletOrchNoteChange ==. val True &&. txs ^.
            WalletTransactionConf <.
            val 3) ||.
           (oNotes ^. WalletOrchNoteChange ==. val False &&. txs ^.
            WalletTransactionConf <.
            val 10))
        pure oNotes

selectUnspentNotes ::
     ConnectionPool
  -> ZcashAccountId
  -> Integer
  -> IO ([Entity WalletTrNote], [Entity WalletSapNote], [Entity WalletOrchNote])
selectUnspentNotes pool za amt = do
  trNotes <- getWalletUnspentTrNotes pool za
  let (a1, tList) = checkTransparent (fromIntegral amt) trNotes
  if a1 > 0
    then do
      sapNotes <- getWalletUnspentSapNotes pool za
      let (a2, sList) = checkSapling a1 sapNotes
      if a2 > 0
        then do
          orchNotes <- getWalletUnspentOrchNotes pool za
          let (a3, oList) = checkOrchard a2 orchNotes
          if a3 > 0
            then throwIO $ userError "Not enough funds"
            else return (tList, sList, oList)
        else return (tList, sList, [])
    else return (tList, [], [])
  where
    checkTransparent ::
         Int64 -> [Entity WalletTrNote] -> (Int64, [Entity WalletTrNote])
    checkTransparent x [] = (x, [])
    checkTransparent x (n:ns) =
      if walletTrNoteValue (entityVal n) < x
        then ( fst (checkTransparent (x - walletTrNoteValue (entityVal n)) ns)
             , n :
               snd (checkTransparent (x - walletTrNoteValue (entityVal n)) ns))
        else (0, [n])
    checkSapling ::
         Int64 -> [Entity WalletSapNote] -> (Int64, [Entity WalletSapNote])
    checkSapling x [] = (x, [])
    checkSapling x (n:ns) =
      if walletSapNoteValue (entityVal n) < x
        then ( fst (checkSapling (x - walletSapNoteValue (entityVal n)) ns)
             , n : snd (checkSapling (x - walletSapNoteValue (entityVal n)) ns))
        else (0, [n])
    checkOrchard ::
         Int64 -> [Entity WalletOrchNote] -> (Int64, [Entity WalletOrchNote])
    checkOrchard x [] = (x, [])
    checkOrchard x (n:ns) =
      if walletOrchNoteValue (entityVal n) < x
        then ( fst (checkOrchard (x - walletOrchNoteValue (entityVal n)) ns)
             , n : snd (checkOrchard (x - walletOrchNoteValue (entityVal n)) ns))
        else (0, [n])

selectUnspentNotesV2 ::
     ConnectionPool
  -> ZcashAccountId
  -> Int64
  -> [Int]
  -> PrivacyPolicy
  -> IO
       (Either
          TxError
          ( [Entity WalletTrNote]
          , [Entity WalletSapNote]
          , [Entity WalletOrchNote]))
selectUnspentNotesV2 pool za amt recv policy = do
  case policy of
    Full ->
      if elem 1 recv || elem 2 recv || elem 5 recv || elem 6 recv
        then return $
             Left $ PrivacyPolicyError "Receiver not capable of Full privacy"
        else if elem 4 recv && elem 3 recv
               then return $
                    Left $
                    PrivacyPolicyError
                      "Combination of receivers not allowed for Full privacy"
               else if 4 `elem` recv
                      then do
                        orchNotes <- getWalletUnspentOrchNotes pool za
                        let (a1, oList) =
                              checkOrchard (fromIntegral amt) orchNotes
                        if a1 > 0
                          then return $
                               Left $
                               PrivacyPolicyError
                                 "Not enough notes for Full privacy"
                          else return $ Right ([], [], oList)
                      else do
                        sapNotes <- getWalletUnspentSapNotes pool za
                        let (a2, sList) =
                              checkSapling (fromIntegral amt) sapNotes
                        if a2 > 0
                          then return $
                               Left $
                               PrivacyPolicyError
                                 "Not enough notes for Full privacy"
                          else return $ Right ([], sList, [])
    Medium ->
      if elem 1 recv || elem 2 recv || elem 5 recv || elem 6 recv
        then return $
             Left $ PrivacyPolicyError "Receiver not capable of Medium privacy"
        else do
          orchNotes <- getWalletUnspentOrchNotes pool za
          let (a1, oList) = checkOrchard (fromIntegral amt) orchNotes
          if a1 > 0
            then do
              sapNotes <- getWalletUnspentSapNotes pool za
              let (a2, sList) = checkSapling a1 sapNotes
              if a2 > 0
                then return $
                     Left $
                     PrivacyPolicyError "Not enough notes for Medium privacy"
                else return $ Right ([], sList, oList)
            else return $ Right ([], [], oList)
    Low ->
      if 0 `elem` recv
        then return $ Left ZHError
        else do
          if elem 5 recv || elem 6 recv
            then return $
                 Left $
                 PrivacyPolicyError
                   "Exchange addresses not supported with Low privacy"
            else do
              orchNotes <- getWalletUnspentOrchNotes pool za
              let (a1, oList) = checkOrchard (fromIntegral amt) orchNotes
              if a1 > 0
                then do
                  sapNotes <- getWalletUnspentSapNotes pool za
                  let (a2, sList) = checkSapling a1 sapNotes
                  if a2 > 0
                    then do
                      trNotes <- getWalletUnspentTrNotes pool za
                      let (a3, tList) = checkTransparent a2 trNotes
                      if a3 > 0
                        then return $ Left InsufficientFunds
                        else return $ Right (tList, sList, oList)
                    else return $ Right ([], sList, oList)
                else return $ Right ([], [], oList)
    None -> do
      if elem 3 recv || elem 4 recv
        then return $
             Left $
             PrivacyPolicyError
               "Shielded recipients not compatible with privacy policy."
        else do
          trNotes <- getWalletUnspentTrNotes pool za
          let (a3, tList) = checkTransparent (fromIntegral amt) trNotes
          if a3 > 0
            then return $
                 Left $ PrivacyPolicyError "Insufficient transparent funds"
            else return $ Right (tList, [], [])
  where
    checkTransparent ::
         Int64 -> [Entity WalletTrNote] -> (Int64, [Entity WalletTrNote])
    checkTransparent x [] = (x, [])
    checkTransparent x (n:ns) =
      if walletTrNoteValue (entityVal n) < x
        then ( fst (checkTransparent (x - walletTrNoteValue (entityVal n)) ns)
             , n :
               snd (checkTransparent (x - walletTrNoteValue (entityVal n)) ns))
        else (0, [n])
    checkSapling ::
         Int64 -> [Entity WalletSapNote] -> (Int64, [Entity WalletSapNote])
    checkSapling x [] = (x, [])
    checkSapling x (n:ns) =
      if walletSapNoteValue (entityVal n) < x
        then ( fst (checkSapling (x - walletSapNoteValue (entityVal n)) ns)
             , n : snd (checkSapling (x - walletSapNoteValue (entityVal n)) ns))
        else (0, [n])
    checkOrchard ::
         Int64 -> [Entity WalletOrchNote] -> (Int64, [Entity WalletOrchNote])
    checkOrchard x [] = (x, [])
    checkOrchard x (n:ns) =
      if walletOrchNoteValue (entityVal n) < x
        then ( fst (checkOrchard (x - walletOrchNoteValue (entityVal n)) ns)
             , n : snd (checkOrchard (x - walletOrchNoteValue (entityVal n)) ns))
        else (0, [n])

getWalletTxId ::
     ConnectionPool -> WalletTransactionId -> IO (Maybe (Value HexStringDB))
getWalletTxId pool wId = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        wtx <- from $ table @WalletTransaction
        where_ (wtx ^. WalletTransactionId ==. val wId)
        pure $ wtx ^. WalletTransactionTxId

getUnconfirmedBlocks :: ConnectionPool -> IO [Int]
getUnconfirmedBlocks pool = do
  r <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        wtx <- from $ table @WalletTransaction
        where_ (wtx ^. WalletTransactionConf <=. val 10)
        pure $ wtx ^. WalletTransactionBlock
  return $ map (\(Value i) -> i) r

saveConfs :: ConnectionPool -> Int -> Int -> IO ()
saveConfs pool b c = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      update $ \t -> do
        set t [WalletTransactionConf =. val c]
        where_ $ t ^. WalletTransactionBlock ==. val b
      update $ \bl -> do
        set bl [ZcashBlockConf =. val c]
        where_ $ bl ^. ZcashBlockHeight ==. val b

getReceivers :: ConnectionPool -> [Entity WalletTrNote] -> IO [WalletAddressId]
getReceivers pool ns = do
  r <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $
        distinct $ do
          t <- from $ table @WalletTrNote
          where_ (t ^. WalletTrNoteId `in_` valList (map entityKey ns))
          return (t ^. WalletTrNoteAddress)
  return $ map (\(Value x) -> x) r

-- | Helper function to extract a Unified Address from the database
readUnifiedAddressDB :: WalletAddress -> Maybe UnifiedAddress
readUnifiedAddressDB =
  isValidUnifiedAddress . TE.encodeUtf8 . getUA . walletAddressUAddress

-- | Get list of external zcash addresses from database
getAdrBook :: ConnectionPool -> ZcashNet -> IO [Entity AddressBook]
getAdrBook pool n =
  runNoLoggingT $
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    select $ do
      adrbook <- from $ table @AddressBook
      where_ (adrbook ^. AddressBookNetwork ==. val (ZcashNetDB n))
      pure adrbook

-- | Save a new address into AddressBook 
saveAdrsInAdrBook ::
     ConnectionPool -- ^ The database path to use
  -> AddressBook -- ^ The address to add to the database
  -> IO (Maybe (Entity AddressBook))
saveAdrsInAdrBook pool a =
  runNoLoggingT $
  PS.retryOnBusy $ flip PS.runSqlPool pool $ insertUniqueEntity a

-- | Update an existing address into AddressBook 
updateAdrsInAdrBook :: ConnectionPool -> T.Text -> T.Text -> T.Text -> IO ()
updateAdrsInAdrBook pool d a ia = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      update $ \ab -> do
        set ab [AddressBookAbdescrip =. val d, AddressBookAbaddress =. val a]
        where_ $ ab ^. AddressBookAbaddress ==. val ia

-- | Get one AddrssBook record using the Address as a key
-- getABookRec :: ConnectionPool -> T.Tex t -> IO (Maybe (Entity AddressBook))
-- getABookRec pool a = do
--  runNoLoggingT $
--    PS.retryOnBusy $
--    flip PS.runSqlPool pool $
--      select $ do
--        adrbook <- from $ table @AddressBook
--        where_ ((adrbook ^. AddressBookAbaddress) ==. val a)
--        return adrbook
-- | delete an existing address from AddressBook 
deleteAdrsFromAB :: ConnectionPool -> T.Text -> IO ()
deleteAdrsFromAB pool ia = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      delete $ do
        ab <- from $ table @AddressBook
        where_ (ab ^. AddressBookAbaddress ==. val ia)

rmdups :: Ord a => [a] -> [a]
rmdups = map head . group . sort

-- * Zenith Operations
-- | Get an operation by UUID
getOperation :: ConnectionPool -> U.UUID -> IO (Maybe (Entity Operation))
getOperation pool uid = do
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        ops <- from $ table @Operation
        where_ (ops ^. OperationUuid ==. val (ZenithUuid uid))
        pure ops

-- | Save an operation
saveOperation :: ConnectionPool -> Operation -> IO (Maybe (Key Operation))
saveOperation pool op = do
  runNoLoggingT $ PS.retryOnBusy $ flip PS.runSqlPool pool $ insertUnique op

-- | Finalize an operation with either a successful result or an error
finalizeOperation ::
     ConnectionPool -> Key Operation -> ZenithStatus -> T.Text -> IO ()
finalizeOperation pool op status result = do
  tstamp <- getCurrentTime
  runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $
    update $ \ops -> do
      set
        ops
        [ OperationEnd =. val (Just tstamp)
        , OperationStatus =. val status
        , OperationResult =. val (Just result)
        ]
      where_ (ops ^. OperationId ==. val op)

-- * Chain sync
-- | Check if the wallet is currently running a sync
isSyncing :: ConnectionPool -> IO Bool
isSyncing pool = do
  s <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $
    selectOne $ do
      r <- from $ table @ChainSync
      where_ $ r ^. ChainSyncStatus ==. val Processing
      pure r
  case s of
    Nothing -> return False
    Just _ -> return True

-- | Record the start of a sync
startSync :: ConnectionPool -> IO ()
startSync pool = do
  start <- getCurrentTime
  _ <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $
    upsert (ChainSync "Internal" start Nothing Processing) []
  return ()

-- | Complete a sync
completeSync :: ConnectionPool -> ZenithStatus -> IO ()
completeSync pool st = do
  end <- getCurrentTime
  _ <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $
    update $ \s -> do
      set s [ChainSyncEnd =. val (Just end), ChainSyncStatus =. val st]
      where_ (s ^. ChainSyncName ==. val "Internal")
  return ()

-- | Rewind the data store to a given block height
rewindWalletData :: ConnectionPool -> Int -> ZcashNetDB -> NoLoggingT IO ()
rewindWalletData pool b net = do
  logDebugN "Starting transaction rewind"
  liftIO $ rewindWalletTransactions pool b
  logDebugN "Completed transaction rewind"
  logDebugN "Starting data store rewind"
  _ <-
    runStderrLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      oldBlocks <-
        select $ do
          blk <- from $ table @ZcashBlock
          where_
            (blk ^. ZcashBlockHeight >. val b &&. blk ^. ZcashBlockNetwork ==.
             val net)
          pure blk
      let oldBlkKeys = map entityKey oldBlocks
      oldTxs <-
        select $ do
          txs <- from $ table @ZcashTransaction
          where_ $ txs ^. ZcashTransactionBlockId `in_` valList oldBlkKeys
          pure txs
      let oldTxKeys = map entityKey oldTxs
      delete $ do
        x <- from $ table @TransparentNote
        where_ $ x ^. TransparentNoteTx `in_` valList oldTxKeys
  logDebugN "Completed TransparentNote delete"
  _ <-
    runStderrLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      oldBlocks <-
        select $ do
          blk <- from $ table @ZcashBlock
          where_
            (blk ^. ZcashBlockHeight >. val b &&. blk ^. ZcashBlockNetwork ==.
             val net)
          pure blk
      let oldBlkKeys = map entityKey oldBlocks
      oldTxs <-
        select $ do
          txs <- from $ table @ZcashTransaction
          where_ $ txs ^. ZcashTransactionBlockId `in_` valList oldBlkKeys
          pure txs
      let oldTxKeys = map entityKey oldTxs
      delete $ do
        x <- from $ table @TransparentSpend
        where_ $ x ^. TransparentSpendTx `in_` valList oldTxKeys
  logDebugN "Completed TransparentSpend delete"
  _ <-
    runStderrLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      oldBlocks <-
        select $ do
          blk <- from $ table @ZcashBlock
          where_
            (blk ^. ZcashBlockHeight >. val b &&. blk ^. ZcashBlockNetwork ==.
             val net)
          pure blk
      let oldBlkKeys = map entityKey oldBlocks
      oldTxs <-
        select $ do
          txs <- from $ table @ZcashTransaction
          where_ $ txs ^. ZcashTransactionBlockId `in_` valList oldBlkKeys
          pure txs
      let oldTxKeys = map entityKey oldTxs
      delete $ do
        x <- from $ table @ShieldOutput
        where_ $ x ^. ShieldOutputTx `in_` valList oldTxKeys
  logDebugN "Completed ShieldOutput delete"
  _ <-
    runStderrLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      oldBlocks <-
        select $ do
          blk <- from $ table @ZcashBlock
          where_
            (blk ^. ZcashBlockHeight >. val b &&. blk ^. ZcashBlockNetwork ==.
             val net)
          pure blk
      let oldBlkKeys = map entityKey oldBlocks
      oldTxs <-
        select $ do
          txs <- from $ table @ZcashTransaction
          where_ $ txs ^. ZcashTransactionBlockId `in_` valList oldBlkKeys
          pure txs
      let oldTxKeys = map entityKey oldTxs
      delete $ do
        x <- from $ table @ShieldSpend
        where_ $ x ^. ShieldSpendTx `in_` valList oldTxKeys
  logDebugN "Completed ShieldSpend delete"
  _ <-
    runStderrLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      oldBlocks <-
        select $ do
          blk <- from $ table @ZcashBlock
          where_
            (blk ^. ZcashBlockHeight >. val b &&. blk ^. ZcashBlockNetwork ==.
             val net)
          pure blk
      let oldBlkKeys = map entityKey oldBlocks
      oldTxs <-
        select $ do
          txs <- from $ table @ZcashTransaction
          where_ $ txs ^. ZcashTransactionBlockId `in_` valList oldBlkKeys
          pure txs
      let oldTxKeys = map entityKey oldTxs
      delete $ do
        x <- from $ table @OrchAction
        where_ $ x ^. OrchActionTx `in_` valList oldTxKeys
  logDebugN "Completed OrchAction delete"
  _ <-
    runStderrLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      oldBlocks <-
        select $ do
          blk <- from $ table @ZcashBlock
          where_
            (blk ^. ZcashBlockHeight >. val b &&. blk ^. ZcashBlockNetwork ==.
             val net)
          pure blk
      let oldBlkKeys = map entityKey oldBlocks
      oldTxs <-
        select $ do
          txs <- from $ table @ZcashTransaction
          where_ $ txs ^. ZcashTransactionBlockId `in_` valList oldBlkKeys
          pure txs
      let oldTxKeys = map entityKey oldTxs
      delete $ do
        x <- from $ table @ZcashTransaction
        where_ $ x ^. ZcashTransactionId `in_` valList oldTxKeys
  logDebugN "Completed ZcashTransaction delete"
  _ <-
    runStderrLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      delete $ do
        blk <- from $ table @ZcashBlock
        where_ $
          (blk ^. ZcashBlockHeight >. val b) &&.
          (blk ^. ZcashBlockNetwork ==. val net)
  logDebugN "Completed data store rewind"
  {-
   -_ <- liftIO $ clearTrees pool
   -logDebugN "Cleared commitment trees"
   -}
  saplingOutputIx <- liftIO $ getSaplingOutputAtBlock pool net b
  orchardActionIx <- liftIO $ getOrchardActionAtBlock pool net b
  case saplingOutputIx of
    Nothing -> logErrorN "Couldn't get Sapling output index for tree rewind"
    Just soIx -> do
      saplingTree <- liftIO $ getSaplingTree pool
      truncSapTree <- truncateTree (maybe InvalidTree fst saplingTree) soIx
      _ <- liftIO $ upsertSaplingTree pool b truncSapTree
      logDebugN $ "Truncated Sapling tree at index " <> T.pack (show soIx)
      case orchardActionIx of
        Nothing -> logErrorN "Couldn't get Orchard action index for tree rewind"
        Just oaIx -> do
          orchardTree <- liftIO $ getOrchardTree pool
          truncOrchTree <- truncateTree (maybe InvalidTree fst orchardTree) oaIx
          _ <- liftIO $ upsertOrchardTree pool b truncOrchTree
          logDebugN $ "Truncated Orchard tree at index " <> T.pack (show oaIx)

clearTrees :: ConnectionPool -> IO ()
clearTrees pool =
  runNoLoggingT $
  PS.retryOnBusy $
  flip PS.runSqlPool pool $ do
    delete $ do
      tr <- from $ table @TreeStore
      return ()

getSaplingOutputAtBlock ::
     ConnectionPool -> ZcashNetDB -> Int -> IO (Maybe Int64)
getSaplingOutputAtBlock pool znet b = do
  r <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        (blks :& txs :& sOutputs) <-
          from $ table @ZcashBlock `innerJoin` table @ZcashTransaction `on`
          (\(blks :& txs) ->
             blks ^. ZcashBlockId ==. txs ^. ZcashTransactionBlockId) `innerJoin`
          table @ShieldOutput `on`
          (\(_ :& txs :& sOutputs) ->
             txs ^. ZcashTransactionId ==. sOutputs ^. ShieldOutputTx)
        where_ (blks ^. ZcashBlockHeight <=. val b)
        where_ (blks ^. ZcashBlockNetwork ==. val znet)
        orderBy [desc $ sOutputs ^. ShieldOutputId]
        return sOutputs
  case r of
    Nothing -> return Nothing
    Just so -> return $ Just $ fromSqlKey $ entityKey so

getOrchardActionAtBlock ::
     ConnectionPool -> ZcashNetDB -> Int -> IO (Maybe Int64)
getOrchardActionAtBlock pool znet b = do
  r <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        (blks :& txs :& oActions) <-
          from $ table @ZcashBlock `innerJoin` table @ZcashTransaction `on`
          (\(blks :& txs) ->
             blks ^. ZcashBlockId ==. txs ^. ZcashTransactionBlockId) `innerJoin`
          table @OrchAction `on`
          (\(_ :& txs :& oActions) ->
             txs ^. ZcashTransactionId ==. oActions ^. OrchActionTx)
        where_ (blks ^. ZcashBlockHeight <=. val b)
        where_ (blks ^. ZcashBlockNetwork ==. val znet)
        orderBy [desc $ oActions ^. OrchActionId]
        return oActions
  case r of
    Nothing -> return Nothing
    Just so -> return $ Just $ fromSqlKey $ entityKey so

-- * Tree storage
-- | Read the Orchard commitment tree
getOrchardTree :: ConnectionPool -> IO (Maybe (Tree OrchardNode, Int))
getOrchardTree pool = do
  treeRecord <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        tr <- from $ table @TreeStore
        where_ (tr ^. TreeStorePool ==. val OrchardPool)
        pure tr
  case treeRecord of
    Nothing -> return Nothing
    Just tR ->
      case deserialiseBorsh $ BS.fromStrict $ treeStoreBytes $ entityVal tR of
        Left _ -> return Nothing
        Right t -> return $ Just (t, treeStoreLastSync $ entityVal tR)

-- | Save the Orchard commitment tree
upsertOrchardTree :: ConnectionPool -> Int -> Tree OrchardNode -> IO ()
upsertOrchardTree pool ls tree = do
  let treeBytes = BS.toStrict $ serialiseBorsh tree
  chk <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        tr <- from $ table @TreeStore
        where_ (tr ^. TreeStorePool ==. val OrchardPool)
        pure tr
  if not (null chk)
    then do
      _ <-
        runNoLoggingT $
        PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          update $ \p -> do
            set p [TreeStoreBytes =. val treeBytes, TreeStoreLastSync =. val ls]
            where_ $ p ^. TreeStorePool ==. val OrchardPool
      return ()
    else do
      _ <-
        runNoLoggingT $
        PS.retryOnBusy $
        flip PS.runSqlPool pool $
        insertUnique_ $ TreeStore OrchardPool treeBytes ls
      return ()

-- | Read the Sapling commitment tree
getSaplingTree :: ConnectionPool -> IO (Maybe (Tree SaplingNode, Int))
getSaplingTree pool = do
  treeRecord <-
    runStderrLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      selectOne $ do
        tr <- from $ table @TreeStore
        where_ (tr ^. TreeStorePool ==. val SaplingPool)
        pure tr
  case treeRecord of
    Nothing -> return Nothing
    Just tR ->
      case deserialiseBorsh $ BS.fromStrict $ treeStoreBytes $ entityVal tR of
        Left _ -> return Nothing
        Right t -> return $ Just (t, treeStoreLastSync $ entityVal tR)

-- | Save the Sapling commitment tree
upsertSaplingTree :: ConnectionPool -> Int -> Tree SaplingNode -> IO ()
upsertSaplingTree pool ls tree = do
  let treeBytes = BS.toStrict $ serialiseBorsh tree
  chk <-
    runNoLoggingT $
    PS.retryOnBusy $
    flip PS.runSqlPool pool $ do
      select $ do
        tr <- from $ table @TreeStore
        where_ (tr ^. TreeStorePool ==. val SaplingPool)
        pure tr
  if not (null chk)
    then do
      _ <-
        runNoLoggingT $
        PS.retryOnBusy $
        flip PS.runSqlPool pool $ do
          update $ \p -> do
            set p [TreeStoreBytes =. val treeBytes, TreeStoreLastSync =. val ls]
            where_ $ p ^. TreeStorePool ==. val SaplingPool
      return ()
    else do
      _ <-
        runNoLoggingT $
        PS.retryOnBusy $
        flip PS.runSqlPool pool $
        insertUnique_ $ TreeStore SaplingPool treeBytes ls
      return ()
