github.com/deso-protocol/core@v1.2.9/lib/postgres.go

package lib

import (
	"encoding/json"
	"fmt"
	"github.com/dgraph-io/badger/v3"
	"github.com/go-pg/pg/v10"
	"github.com/go-pg/pg/v10/orm"
	"github.com/golang/glog"
	"strings"
)

type Postgres struct {
	db *pg.DB
}

func NewPostgres(db *pg.DB) *Postgres {
	// Uncomment to print all queries.
	//db.AddQueryHook(pgdebug.DebugHook{
	//	Verbose: true,
	//})

	return &Postgres{
		db: db,
	}
}

// LogSelect is a helpful utility when developing or debugging queries. Simply call
// LogSelect(query) instead of query.Select() to get a log of the raw query.
func LogSelect(query *orm.Query) error {
	selectQuery := orm.NewSelectQuery(query)
	fmter := orm.NewFormatter().WithModel(selectQuery)
	queryStr, _ := selectQuery.AppendQuery(fmter, nil)
	glog.Info(string(queryStr))
	return query.Select()
}

const (
	MAIN_CHAIN = "main"
)

//
// Tables
//
// The current schema is the sum of all the migrations in the migrate folder. Eventually we should
// export the current schema as new instances of the chain shouldn't be running every single migration.
//
// For information about the `pg:"..."` annotations, see: https://pg.uptrace.dev/models/
//
// Common annotations include:
// - Custom primary key: `pg:",pk"`
// - Don't store 0 or false as NULL: `pg:",use_zero"`
//
// When we can, we use unique fields (or combinations of unique fields) as the primary keys on the models.
// This lets us use the WherePK() query while also minimizing columns and indicies on disk.
//
// Table names are defined so the relation is obvious even though go-pg can create them for us automatically.
//
// Column names are automatically created by go-pg. For example, a field named TipHash maps to tip_hash.
//

type PGChain struct {
	tableName struct{} `pg:"pg_chains"`

	Name    string     `pg:",pk"`
	TipHash *BlockHash `pg:",type:bytea"`
}

// PGBlock represents BlockNode and MsgDeSoHeader
type PGBlock struct {
	tableName struct{} `pg:"pg_blocks"`

	// BlockNode and MsgDeSoHeader
	Hash       *BlockHash `pg:",pk,type:bytea"`
	ParentHash *BlockHash `pg:",type:bytea"`
	Height     uint64     `pg:",use_zero"`

	// BlockNode
	DifficultyTarget *BlockHash  `pg:",type:bytea"`
	CumWork          *BlockHash  `pg:",type:bytea"`
	Status           BlockStatus `pg:",use_zero"` // TODO: Refactor

	// MsgDeSoHeader
	TxMerkleRoot *BlockHash `pg:",type:bytea"`
	Version      uint32     `pg:",use_zero"`
	Timestamp    uint64     `pg:",use_zero"`
	Nonce        uint64     `pg:",use_zero"`
	ExtraNonce   uint64     `pg:",use_zero"`

	// Notifications
	Notified bool `pg:",use_zero"`
}

// PGTransaction represents MsgDeSoTxn
type PGTransaction struct {
	tableName struct{} `pg:"pg_transactions"`

	Hash      *BlockHash `pg:",pk,type:bytea"`
	BlockHash *BlockHash `pg:",type:bytea"`
	Type      TxnType    `pg:",use_zero"`
	PublicKey []byte     `pg:",type:bytea"`
	ExtraData map[string][]byte
	R         *BlockHash `pg:",type:bytea"`
	S         *BlockHash `pg:",type:bytea"`

	// Relationships
	Outputs                     []*PGTransactionOutput         `pg:"rel:has-many,join_fk:output_hash"`
	MetadataBlockReward         *PGMetadataBlockReward         `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataBitcoinExchange     *PGMetadataBitcoinExchange     `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataPrivateMessage      *PGMetadataPrivateMessage      `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataSubmitPost          *PGMetadataSubmitPost          `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataUpdateExchangeRate  *PGMetadataUpdateExchangeRate  `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataUpdateProfile       *PGMetadataUpdateProfile       `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataFollow              *PGMetadataFollow              `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataLike                *PGMetadataLike                `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataCreatorCoin         *PGMetadataCreatorCoin         `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataCreatorCoinTransfer *PGMetadataCreatorCoinTransfer `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataSwapIdentity        *PGMetadataSwapIdentity        `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataCreateNFT           *PGMetadataCreateNFT           `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataUpdateNFT           *PGMetadataUpdateNFT           `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataAcceptNFTBid        *PGMetadataAcceptNFTBid        `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataNFTBid              *PGMetadataNFTBid              `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataNFTTransfer         *PGMetadataNFTTransfer         `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataAcceptNFTTransfer   *PGMetadataAcceptNFTTransfer   `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataBurnNFT             *PGMetadataBurnNFT             `pg:"rel:belongs-to,join_fk:transaction_hash"`
	MetadataDerivedKey          *PGMetadataDerivedKey          `pg:"rel:belongs-to,join_fk:transaction_hash"`
}

// PGTransactionOutput represents DeSoOutput, DeSoInput, and UtxoEntry
type PGTransactionOutput struct {
	tableName struct{} `pg:"pg_transaction_outputs"`

	OutputHash  *BlockHash `pg:",pk"`
	OutputIndex uint32     `pg:",pk,use_zero"`
	OutputType  UtxoType   `pg:",use_zero"`
	Height      uint32     `pg:",use_zero"`
	PublicKey   []byte
	AmountNanos uint64 `pg:",use_zero"`
	Spent       bool   `pg:",use_zero"`
	InputHash   *BlockHash
	InputIndex  uint32 `pg:",pk,use_zero"`
}

func (utxo *PGTransactionOutput) NewUtxoEntry() *UtxoEntry {
	return &UtxoEntry{
		PublicKey:   utxo.PublicKey,
		AmountNanos: utxo.AmountNanos,
		BlockHeight: utxo.Height,
		UtxoType:    utxo.OutputType,
		isSpent:     utxo.Spent,
		UtxoKey: &UtxoKey{
			TxID:  *utxo.OutputHash,
			Index: utxo.OutputIndex,
		},
	}
}

// PGMetadataBlockReward represents BlockRewardMetadataa
type PGMetadataBlockReward struct {
	tableName struct{} `pg:"pg_metadata_block_rewards"`

	TransactionHash *BlockHash `pg:",pk,type:bytea"`
	ExtraData       []byte     `pg:",type:bytea"`
}

// PGMetadataBitcoinExchange represents BitcoinExchangeMetadata
type PGMetadataBitcoinExchange struct {
	tableName struct{} `pg:"pg_metadata_bitcoin_exchanges"`

	TransactionHash   *BlockHash `pg:",pk,type:bytea"`
	BitcoinBlockHash  *BlockHash `pg:",type:bytea"`
	BitcoinMerkleRoot *BlockHash `pg:",type:bytea"`
	// Not storing BitcoinTransaction *wire.MsgTx
	// Not storing BitcoinMerkleProof []*merkletree.ProofPart
}

// PGMetadataPrivateMessage represents PrivateMessageMetadata
type PGMetadataPrivateMessage struct {
	tableName struct{} `pg:"pg_metadata_private_messages"`

	TransactionHash    *BlockHash `pg:",pk,type:bytea"`
	RecipientPublicKey []byte     `pg:",type:bytea"`
	EncryptedText      []byte     `pg:",type:bytea"`
	TimestampNanos     uint64
}

// PGMetadataSubmitPost represents SubmitPostMetadata
type PGMetadataSubmitPost struct {
	tableName struct{} `pg:"pg_metadata_submit_posts"`

	TransactionHash  *BlockHash `pg:",pk,type:bytea"`
	PostHashToModify *BlockHash `pg:",type:bytea"`
	ParentStakeID    *BlockHash `pg:",type:bytea"`
	Body             []byte     `pg:",type:bytea"`
	TimestampNanos   uint64
	IsHidden         bool `pg:",use_zero"`
}

// PGMetadataUpdateExchangeRate represents UpdateBitcoinUSDExchangeRateMetadataa
type PGMetadataUpdateExchangeRate struct {
	tableName struct{} `pg:"pg_metadata_update_exchange_rates"`

	TransactionHash    *BlockHash `pg:",pk,type:bytea"`
	USDCentsPerBitcoin uint64     `pg:",use_zero"`
}

// PGMetadataUpdateProfile represents UpdateProfileMetadata
type PGMetadataUpdateProfile struct {
	tableName struct{} `pg:"pg_metadata_update_profiles"`

	TransactionHash       *BlockHash `pg:",pk,type:bytea"`
	ProfilePublicKey      []byte     `pg:",type:bytea"`
	NewUsername           []byte     `pg:",type:bytea"`
	NewDescription        []byte     `pg:",type:bytea"`
	NewProfilePic         []byte     `pg:",type:bytea"`
	NewCreatorBasisPoints uint64     `pg:",use_zero"`
}

// PGMetadataFollow represents FollowMetadata
type PGMetadataFollow struct {
	tableName struct{} `pg:"pg_metadata_follows"`

	TransactionHash   *BlockHash `pg:",pk,type:bytea"`
	FollowedPublicKey []byte     `pg:",type:bytea"`
	IsUnfollow        bool       `pg:",use_zero"`
}

// PGMetadataLike represents LikeMetadata
type PGMetadataLike struct {
	tableName struct{} `pg:"pg_metadata_likes"`

	TransactionHash *BlockHash `pg:",pk,type:bytea"`
	LikedPostHash   *BlockHash `pg:",type:bytea"`
	IsUnlike        bool       `pg:",use_zero"`
}

// PGMetadataCreatorCoin represents CreatorCoinMetadataa
type PGMetadataCreatorCoin struct {
	tableName struct{} `pg:"pg_metadata_creator_coins"`

	TransactionHash             *BlockHash               `pg:",pk,type:bytea"`
	ProfilePublicKey            []byte                   `pg:",type:bytea"`
	OperationType               CreatorCoinOperationType `pg:",use_zero"`
	DeSoToSellNanos             uint64                   `pg:",use_zero"`
	CreatorCoinToSellNanos      uint64                   `pg:",use_zero"`
	DeSoToAddNanos              uint64                   `pg:",use_zero"`
	MinDeSoExpectedNanos        uint64                   `pg:",use_zero"`
	MinCreatorCoinExpectedNanos uint64                   `pg:",use_zero"`
}

// PGMetadataCreatorCoinTransfer represents CreatorCoinTransferMetadataa
type PGMetadataCreatorCoinTransfer struct {
	tableName struct{} `pg:"pg_metadata_creator_coin_transfers"`

	TransactionHash            *BlockHash `pg:",pk,type:bytea"`
	ProfilePublicKey           []byte     `pg:",type:bytea"`
	CreatorCoinToTransferNanos uint64     `pg:",use_zero"`
	ReceiverPublicKey          []byte     `pg:",type:bytea"`
}

// PGMetadataSwapIdentity represents SwapIdentityMetadataa
type PGMetadataSwapIdentity struct {
	tableName struct{} `pg:"pg_metadata_swap_identities"`

	TransactionHash *BlockHash `pg:",pk,type:bytea"`
	FromPublicKey   []byte     `pg:",type:bytea"`
	ToPublicKey     []byte     `pg:",type:bytea"`
}

// PGMetadataCreateNFT represents CreateNFTMetadata
type PGMetadataCreateNFT struct {
	tableName struct{} `pg:"pg_metadata_create_nfts"`

	TransactionHash           *BlockHash `pg:",pk,type:bytea"`
	NFTPostHash               *BlockHash `pg:",type:bytea"`
	NumCopies                 uint64     `pg:",use_zero"`
	HasUnlockable             bool       `pg:",use_zero"`
	IsForSale                 bool       `pg:",use_zero"`
	MinBidAmountNanos         uint64     `pg:",use_zero"`
	CreatorRoyaltyBasisPoints uint64     `pg:",use_zero"`
	CoinRoyaltyBasisPoints    uint64     `pg:",use_zero"`
}

// PGMetadataUpdateNFT represents UpdateNFTMetadata
type PGMetadataUpdateNFT struct {
	tableName struct{} `pg:"pg_metadata_update_nfts"`

	TransactionHash   *BlockHash `pg:",pk,type:bytea"`
	NFTPostHash       *BlockHash `pg:",type:bytea"`
	SerialNumber      uint64     `pg:",use_zero"`
	IsForSale         bool       `pg:",use_zero"`
	MinBidAmountNanos uint64     `pg:",use_zero"`
}

// PGMetadataAcceptNFTBid represents AcceptNFTBidMetadata
type PGMetadataAcceptNFTBid struct {
	tableName struct{} `pg:"pg_metadata_accept_nft_bids"`

	TransactionHash *BlockHash            `pg:",pk,type:bytea"`
	NFTPostHash     *BlockHash            `pg:",type:bytea"`
	SerialNumber    uint64                `pg:",use_zero"`
	BidderPKID      *PKID                 `pg:",type:bytea"`
	BidAmountNanos  uint64                `pg:",use_zero"`
	UnlockableText  []byte                `pg:",type:bytea"`
	BidderInputs    []*PGMetadataBidInput `pg:"rel:has-many,join_fk:transaction_hash"`
}

type PGMetadataBidInput struct {
	tableName struct{} `pg:"pg_metadata_bid_inputs"`

	TransactionHash *BlockHash `pg:",pk,type:bytea"`
	InputHash       *BlockHash `pg:",pk,type:bytea"`
	InputIndex      uint32     `pg:",pk,use_zero"`
}

// PGMetadataNFTBid represents NFTBidMetadata
type PGMetadataNFTBid struct {
	tableName struct{} `pg:"pg_metadata_nft_bids"`

	TransactionHash *BlockHash `pg:",pk,type:bytea"`
	NFTPostHash     *BlockHash `pg:",type:bytea"`
	SerialNumber    uint64     `pg:",use_zero"`
	BidAmountNanos  uint64     `pg:",use_zero"`
}

// PGMetadataNFTTransfer represents NFTTransferMetadata
type PGMetadataNFTTransfer struct {
	tableName struct{} `pg:"pg_metadata_nft_transfer"`

	TransactionHash   *BlockHash `pg:",pk,type:bytea"`
	NFTPostHash       *BlockHash `pg:",pk,type:bytea"`
	SerialNumber      uint64     `pg:",use_zero"`
	ReceiverPublicKey []byte     `pg:",pk,type:bytea"`
	UnlockableText    []byte     `pg:",type:bytea"`
}

// PGMetadataAcceptNFTTransfer represents AcceptNFTTransferMetadata
type PGMetadataAcceptNFTTransfer struct {
	tableName struct{} `pg:"pg_metadata_accept_nft_transfer"`

	TransactionHash *BlockHash `pg:",pk,type:bytea"`
	NFTPostHash     *BlockHash `pg:",pk,type:bytea"`
	SerialNumber    uint64     `pg:",use_zero"`
}

// PGMetadataBurnNFT represents BurnNFTMetadata
type PGMetadataBurnNFT struct {
	tableName struct{} `pg:"pg_metadata_burn_nft"`

	TransactionHash *BlockHash `pg:",pk,type:bytea"`
	NFTPostHash     *BlockHash `pg:",pk,type:bytea"`
	SerialNumber    uint64     `pg:",use_zero"`
}

// PGMetadataDerivedKey represents AuthorizeDerivedKeyMetadata
type PGMetadataDerivedKey struct {
	tableName struct{} `pg:"pg_metadata_derived_keys"`

	TransactionHash  *BlockHash                       `pg:",pk,type:bytea"`
	DerivedPublicKey PublicKey                        `pg:",type:bytea"`
	ExpirationBlock  uint64                           `pg:",use_zero"`
	OperationType    AuthorizeDerivedKeyOperationType `pg:",use_zero"`
	AccessSignature  []byte                           `pg:",type:bytea"`
}

type PGNotification struct {
	tableName struct{} `pg:"pg_notifications"`

	TransactionHash *BlockHash       `pg:",pk,type:bytea"`
	Mined           bool             `pg:",use_zero"`
	ToUser          []byte           `pg:",type:bytea"`
	FromUser        []byte           `pg:",type:bytea"`
	OtherUser       []byte           `pg:",type:bytea"`
	Type            NotificationType `pg:",use_zero"`
	Amount          uint64           `pg:",use_zero"`
	PostHash        *BlockHash       `pg:",type:bytea"`
	Timestamp       uint64           `pg:",use_zero"`
}

type NotificationType uint8

const (
	NotificationUnknown NotificationType = iota
	NotificationSendDESO
	NotificationLike
	NotificationFollow
	NotificationCoinPurchase
	NotificationCoinTransfer
	NotificationCoinDiamond
	NotificationPostMention
	NotificationPostReply
	NotificationPostRepost
	NotificationDESODiamond
)

type PGProfile struct {
	tableName struct{} `pg:"pg_profiles"`

	PKID                    *PKID      `pg:",pk,type:bytea"`
	PublicKey               *PublicKey `pg:",type:bytea"`
	Username                string
	Description             string
	ProfilePic              []byte
	CreatorBasisPoints      uint64
	DeSoLockedNanos         uint64
	NumberOfHolders         uint64
	CoinsInCirculationNanos uint64
	CoinWatermarkNanos      uint64
}

func (profile *PGProfile) Empty() bool {
	return profile.Username == ""
}

type PGPost struct {
	tableName struct{} `pg:"pg_posts"`

	PostHash                  *BlockHash `pg:",pk,type:bytea"`
	PosterPublicKey           []byte
	ParentPostHash            *BlockHash `pg:",type:bytea"`
	Body                      string
	RepostedPostHash          *BlockHash `pg:",type:bytea"`
	QuotedRepost              bool       `pg:",use_zero"`
	Timestamp                 uint64     `pg:",use_zero"`
	Hidden                    bool       `pg:",use_zero"`
	LikeCount                 uint64     `pg:",use_zero"`
	RepostCount               uint64     `pg:",use_zero"`
	QuoteRepostCount          uint64     `pg:",use_zero"`
	DiamondCount              uint64     `pg:",use_zero"`
	CommentCount              uint64     `pg:",use_zero"`
	Pinned                    bool       `pg:",use_zero"`
	NFT                       bool       `pg:",use_zero"`
	NumNFTCopies              uint64     `pg:",use_zero"`
	NumNFTCopiesForSale       uint64     `pg:",use_zero"`
	NumNFTCopiesBurned        uint64     `pg:",use_zero"`
	Unlockable                bool       `pg:",use_zero"`
	CreatorRoyaltyBasisPoints uint64     `pg:",use_zero"`
	CoinRoyaltyBasisPoints    uint64     `pg:",use_zero"`
	ExtraData                 map[string][]byte
}

func (post *PGPost) NewPostEntry() *PostEntry {
	postEntry := &PostEntry{
		PostHash:                       post.PostHash,
		PosterPublicKey:                post.PosterPublicKey,
		Body:                           []byte(post.Body),
		RepostedPostHash:               post.RepostedPostHash,
		IsQuotedRepost:                 post.QuotedRepost,
		TimestampNanos:                 post.Timestamp,
		IsHidden:                       post.Hidden,
		LikeCount:                      post.LikeCount,
		RepostCount:                    post.RepostCount,
		QuoteRepostCount:               post.QuoteRepostCount,
		DiamondCount:                   post.DiamondCount,
		CommentCount:                   post.CommentCount,
		IsPinned:                       post.Pinned,
		IsNFT:                          post.NFT,
		NumNFTCopies:                   post.NumNFTCopies,
		NumNFTCopiesForSale:            post.NumNFTCopiesForSale,
		NumNFTCopiesBurned:             post.NumNFTCopiesBurned,
		HasUnlockable:                  post.Unlockable,
		NFTRoyaltyToCoinBasisPoints:    post.CoinRoyaltyBasisPoints,
		NFTRoyaltyToCreatorBasisPoints: post.CreatorRoyaltyBasisPoints,
		PostExtraData:                  post.ExtraData,
	}

	if post.ParentPostHash != nil {
		postEntry.ParentStakeID = post.ParentPostHash.ToBytes()
	}

	return postEntry
}

// HasMedia is inefficient and needs to be moved to a column in the Posts table
func (post *PGPost) HasMedia() bool {
	bodyJSONObj := DeSoBodySchema{}
	err := json.Unmarshal([]byte(post.Body), &bodyJSONObj)
	// Return true if body json can be parsed and ImageUrls or VideoURLs is not nil/non-empty or EmbedVideoUrl is not nil/non-empty
	return (err == nil && len(bodyJSONObj.ImageURLs) > 0 || len(bodyJSONObj.VideoURLs) > 0) || len(post.ExtraData["EmbedVideoURL"]) > 0
}

type PGLike struct {
	tableName struct{} `pg:"pg_likes"`

	LikerPublicKey []byte     `pg:",pk,type:bytea"`
	LikedPostHash  *BlockHash `pg:",pk,type:bytea"`
}

func (like *PGLike) NewLikeEntry() *LikeEntry {
	return &LikeEntry{
		LikerPubKey:   like.LikerPublicKey,
		LikedPostHash: like.LikedPostHash,
	}
}

type PGFollow struct {
	tableName struct{} `pg:"pg_follows"`

	FollowerPKID *PKID `pg:",pk,type:bytea"`
	FollowedPKID *PKID `pg:",pk,type:bytea"`
}

func (follow *PGFollow) NewFollowEntry() *FollowEntry {
	return &FollowEntry{
		FollowerPKID: follow.FollowerPKID,
		FollowedPKID: follow.FollowedPKID,
	}
}

type PGDiamond struct {
	tableName struct{} `pg:"pg_diamonds"`

	SenderPKID      *PKID      `pg:",pk,type:bytea"`
	ReceiverPKID    *PKID      `pg:",pk,type:bytea"`
	DiamondPostHash *BlockHash `pg:",pk,type:bytea"`
	DiamondLevel    uint8
}

// TODO: This doesn't need to be a table. Just add sender to PGMetadataPrivateMessage?
// The only reason we might not want to do this is if we end up pruning Metadata tables.
type PGMessage struct {
	tableName struct{} `pg:"pg_messages"`

	MessageHash        *BlockHash `pg:",pk,type:bytea"`
	SenderPublicKey    []byte
	RecipientPublicKey []byte
	EncryptedText      []byte
	TimestampNanos     uint64
	// TODO: Version

	// Used to track deletions in the UtxoView
	isDeleted bool
}

type PGCreatorCoinBalance struct {
	tableName struct{} `pg:"pg_creator_coin_balances"`

	HolderPKID   *PKID `pg:",pk,type:bytea"`
	CreatorPKID  *PKID `pg:",pk,type:bytea"`
	BalanceNanos uint64
	HasPurchased bool
}

func (balance *PGCreatorCoinBalance) NewBalanceEntry() *BalanceEntry {
	return &BalanceEntry{
		HODLerPKID:   balance.HolderPKID,
		CreatorPKID:  balance.CreatorPKID,
		BalanceNanos: balance.BalanceNanos,
		HasPurchased: balance.HasPurchased,
	}
}

// PGBalance represents PublicKeyToDeSoBalanceNanos
type PGBalance struct {
	tableName struct{} `pg:"pg_balances"`

	PublicKey    *PublicKey `pg:",pk,type:bytea"`
	BalanceNanos uint64     `pg:",use_zero"`
}

// PGGlobalParams represents GlobalParamsEntry
type PGGlobalParams struct {
	tableName struct{} `pg:"pg_global_params"`

	ID uint64

	USDCentsPerBitcoin      uint64 `pg:",use_zero"`
	CreateProfileFeeNanos   uint64 `pg:",use_zero"`
	CreateNFTFeeNanos       uint64 `pg:",use_zero"`
	MaxCopiesPerNFT         uint64 `pg:",use_zero"`
	MinNetworkFeeNanosPerKB uint64 `pg:",use_zero"`
}

type PGRepost struct {
	tableName struct{} `pg:"pg_reposts"`

	ReposterPublickey *PublicKey `pg:",pk,type:bytea"`
	RepostedPostHash  *BlockHash `pg:",pk,type:bytea"`
	RepostPostHash    *BlockHash `pg:",type:bytea"`

	// Whether or not this entry is deleted in the view.
	isDeleted bool
}

// PGForbiddenKey represents ForbiddenPubKeyEntry
type PGForbiddenKey struct {
	tableName struct{} `pg:"pg_forbidden_keys"`

	PublicKey *PublicKey `pg:",pk,type:bytea"`
}

// PGNFT represents NFTEntry
type PGNFT struct {
	tableName struct{} `pg:"pg_nfts"`

	NFTPostHash  *BlockHash `pg:",pk,type:bytea"`
	SerialNumber uint64     `pg:",pk"`

	// This is needed to decrypt unlockable text.
	LastOwnerPKID              *PKID  `pg:",type:bytea"`
	OwnerPKID                  *PKID  `pg:",type:bytea"`
	ForSale                    bool   `pg:",use_zero"`
	MinBidAmountNanos          uint64 `pg:",use_zero"`
	UnlockableText             string
	LastAcceptedBidAmountNanos uint64 `pg:",use_zero"`
	IsPending                  bool   `pg:",use_zero"`
}

func (nft *PGNFT) NewNFTEntry() *NFTEntry {
	return &NFTEntry{
		LastOwnerPKID:              nft.LastOwnerPKID,
		OwnerPKID:                  nft.OwnerPKID,
		NFTPostHash:                nft.NFTPostHash,
		SerialNumber:               nft.SerialNumber,
		IsForSale:                  nft.ForSale,
		MinBidAmountNanos:          nft.MinBidAmountNanos,
		UnlockableText:             []byte(nft.UnlockableText),
		LastAcceptedBidAmountNanos: nft.LastAcceptedBidAmountNanos,
		IsPending:                  nft.IsPending,
	}
}

// PGNFTBid represents NFTBidEntry
type PGNFTBid struct {
	tableName struct{} `pg:"pg_nft_bids"`

	BidderPKID     *PKID      `pg:",pk,type:bytea"`
	NFTPostHash    *BlockHash `pg:",pk,type:bytea"`
	SerialNumber   uint64     `pg:",pk,use_zero"`
	BidAmountNanos uint64     `pg:",use_zero"`
	Accepted       bool       `pg:",use_zero"`
}

func (bid *PGNFTBid) NewNFTBidEntry() *NFTBidEntry {
	return &NFTBidEntry{
		BidderPKID:     bid.BidderPKID,
		NFTPostHash:    bid.NFTPostHash,
		SerialNumber:   bid.SerialNumber,
		BidAmountNanos: bid.BidAmountNanos,
	}
}

// PGDerivedKey represents DerivedKeyEntry
type PGDerivedKey struct {
	tableName struct{} `pg:"pg_derived_keys"`

	OwnerPublicKey   PublicKey                        `pg:",pk,type:bytea"`
	DerivedPublicKey PublicKey                        `pg:",pk,type:bytea"`
	ExpirationBlock  uint64                           `pg:",use_zero"`
	OperationType    AuthorizeDerivedKeyOperationType `pg:",use_zero"`
}

func (key *PGDerivedKey) NewDerivedKeyEntry() *DerivedKeyEntry {
	return &DerivedKeyEntry{
		OwnerPublicKey:   key.OwnerPublicKey,
		DerivedPublicKey: key.DerivedPublicKey,
		ExpirationBlock:  key.ExpirationBlock,
		OperationType:    key.OperationType,
	}
}

//
// Blockchain and Transactions
//

func (postgres *Postgres) UpsertBlock(blockNode *BlockNode) error {
	return postgres.db.RunInTransaction(postgres.db.Context(), func(tx *pg.Tx) error {
		return postgres.UpsertBlockTx(tx, blockNode)
	})
}

func (postgres *Postgres) UpsertBlockTx(tx *pg.Tx, blockNode *BlockNode) error {
	block := &PGBlock{
		Hash:   blockNode.Hash,
		Height: blockNode.Header.Height,

		DifficultyTarget: blockNode.DifficultyTarget,
		CumWork:          BigintToHash(blockNode.CumWork),
		Status:           blockNode.Status,

		TxMerkleRoot: blockNode.Header.TransactionMerkleRoot,
		Version:      blockNode.Header.Version,
		Timestamp:    blockNode.Header.TstampSecs,
		Nonce:        blockNode.Header.Nonce,
		ExtraNonce:   blockNode.Header.ExtraNonce,
	}

	// The genesis block has a nil parent
	if blockNode.Parent != nil {
		block.ParentHash = blockNode.Parent.Hash
	}

	_, err := tx.Model(block).WherePK().OnConflict("(hash) DO UPDATE").Insert()
	return err
}

// GetBlockIndex gets all the PGBlocks and creates a map of BlockHash to BlockNode as needed by blockchain.go
func (postgres *Postgres) GetBlockIndex() (map[BlockHash]*BlockNode, error) {
	var blocks []PGBlock
	err := postgres.db.Model(&blocks).Select()
	if err != nil {
		return nil, err
	}

	blockMap := make(map[BlockHash]*BlockNode)
	for _, block := range blocks {
		blockMap[*block.Hash] = &BlockNode{
			Hash:             block.Hash,
			Height:           uint32(block.Height),
			DifficultyTarget: block.DifficultyTarget,
			CumWork:          HashToBigint(block.CumWork),
			Header: &MsgDeSoHeader{
				Version:               block.Version,
				PrevBlockHash:         block.ParentHash,
				TransactionMerkleRoot: block.TxMerkleRoot,
				TstampSecs:            block.Timestamp,
				Height:                block.Height,
				Nonce:                 block.Nonce,
				ExtraNonce:            block.ExtraNonce,
			},
			Status: block.Status,
		}
	}

	// Setup parent pointers
	for _, blockNode := range blockMap {
		// Genesis block has nil parent
		parentHash := blockNode.Header.PrevBlockHash
		if parentHash != nil {
			blockNode.Parent = blockMap[*parentHash]
		}
	}

	return blockMap, nil
}

// GetChain returns the current chain by name. Postgres only supports MAIN_CHAIN for now but will eventually
// support multiple chains. A chain is defined by its Name and TipHash.
func (postgres *Postgres) GetChain(name string) *PGChain {
	chain := &PGChain{
		Name: name,
	}

	err := postgres.db.Model(chain).First()
	if err != nil {
		return nil
	}

	return chain
}

func (postgres *Postgres) UpsertChain(name string, tipHash *BlockHash) error {
	return postgres.db.RunInTransaction(postgres.db.Context(), func(tx *pg.Tx) error {
		return postgres.UpsertChainTx(tx, name, tipHash)
	})
}

func (postgres *Postgres) UpsertChainTx(tx *pg.Tx, name string, tipHash *BlockHash) error {
	bestChain := &PGChain{
		TipHash: tipHash,
		Name:    name,
	}

	_, err := tx.Model(bestChain).WherePK().OnConflict("(name) DO UPDATE").Insert()
	return err
}

// InsertTransactionsTx inserts all the transactions from a block in a bulk query
func (postgres *Postgres) InsertTransactionsTx(tx *pg.Tx, desoTxns []*MsgDeSoTxn, blockNode *BlockNode) error {
	var transactions []*PGTransaction
	var transactionOutputs []*PGTransactionOutput
	var transactionInputs []*PGTransactionOutput

	var metadataBlockRewards []*PGMetadataBlockReward
	var metadataBitcoinExchanges []*PGMetadataBitcoinExchange
	var metadataPrivateMessages []*PGMetadataPrivateMessage
	var metadataSubmitPosts []*PGMetadataSubmitPost
	var metadataUpdateProfiles []*PGMetadataUpdateProfile
	var metadataExchangeRates []*PGMetadataUpdateExchangeRate
	var metadataFollows []*PGMetadataFollow
	var metadataLikes []*PGMetadataLike
	var metadataCreatorCoins []*PGMetadataCreatorCoin
	var metadataSwapIdentities []*PGMetadataSwapIdentity
	var metadataCreatorCoinTransfers []*PGMetadataCreatorCoinTransfer
	var metadataCreateNFTs []*PGMetadataCreateNFT
	var metadataUpdateNFTs []*PGMetadataUpdateNFT
	var metadataAcceptNFTBids []*PGMetadataAcceptNFTBid
	var metadataBidInputs []*PGMetadataBidInput
	var metadataNFTBids []*PGMetadataNFTBid
	var metadataNFTTransfer []*PGMetadataNFTTransfer
	var metadataAcceptNFTTransfer []*PGMetadataAcceptNFTTransfer
	var metadataBurnNFT []*PGMetadataBurnNFT
	var metadataDerivedKey []*PGMetadataDerivedKey

	blockHash := blockNode.Hash

	// Iterate over all the transactions and build the arrays of data to insert
	for _, txn := range desoTxns {
		txnHash := txn.Hash()
		transaction := &PGTransaction{
			Hash:      txnHash,
			BlockHash: blockHash,
			Type:      txn.TxnMeta.GetTxnType(),
			PublicKey: txn.PublicKey,
			ExtraData: txn.ExtraData,
		}

		if txn.Signature != nil {
			transaction.R = BigintToHash(txn.Signature.R)
			transaction.S = BigintToHash(txn.Signature.S)
		}

		transactions = append(transactions, transaction)

		for ii, input := range txn.TxInputs {
			transactionInputs = append(transactionInputs, &PGTransactionOutput{
				OutputHash:  &input.TxID,
				OutputIndex: input.Index,
				Height:      blockNode.Height,
				InputHash:   txnHash,
				InputIndex:  uint32(ii),
				Spent:       true,
			})
		}

		for ii, output := range txn.TxOutputs {
			transactionOutputs = append(transactionOutputs, &PGTransactionOutput{
				OutputHash:  txnHash,
				OutputIndex: uint32(ii),
				OutputType:  0, // TODO
				PublicKey:   output.PublicKey,
				AmountNanos: output.AmountNanos,
			})
		}

		if txn.TxnMeta.GetTxnType() == TxnTypeBlockReward {
			txMeta := txn.TxnMeta.(*BlockRewardMetadataa)
			metadataBlockRewards = append(metadataBlockRewards, &PGMetadataBlockReward{
				TransactionHash: txnHash,
				ExtraData:       txMeta.ExtraData,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeBasicTransfer {
			// No extra metadata needed
		} else if txn.TxnMeta.GetTxnType() == TxnTypeBitcoinExchange {
			txMeta := txn.TxnMeta.(*BitcoinExchangeMetadata)
			metadataBitcoinExchanges = append(metadataBitcoinExchanges, &PGMetadataBitcoinExchange{
				TransactionHash:   txnHash,
				BitcoinBlockHash:  txMeta.BitcoinBlockHash,
				BitcoinMerkleRoot: txMeta.BitcoinMerkleRoot,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypePrivateMessage {
			txMeta := txn.TxnMeta.(*PrivateMessageMetadata)
			metadataPrivateMessages = append(metadataPrivateMessages, &PGMetadataPrivateMessage{
				TransactionHash:    txnHash,
				RecipientPublicKey: txMeta.RecipientPublicKey,
				EncryptedText:      txMeta.EncryptedText,
				TimestampNanos:     txMeta.TimestampNanos,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeSubmitPost {
			txMeta := txn.TxnMeta.(*SubmitPostMetadata)

			postHashToModify := &BlockHash{}
			parentStakeId := &BlockHash{}
			copy(postHashToModify[:], txMeta.PostHashToModify)
			copy(parentStakeId[:], txMeta.ParentStakeID)

			metadataSubmitPosts = append(metadataSubmitPosts, &PGMetadataSubmitPost{
				TransactionHash:  txnHash,
				PostHashToModify: postHashToModify,
				ParentStakeID:    parentStakeId,
				Body:             txMeta.Body,
				TimestampNanos:   txMeta.TimestampNanos,
				IsHidden:         txMeta.IsHidden,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeUpdateProfile {
			txMeta := txn.TxnMeta.(*UpdateProfileMetadata)
			metadataUpdateProfiles = append(metadataUpdateProfiles, &PGMetadataUpdateProfile{
				TransactionHash:       txnHash,
				ProfilePublicKey:      txMeta.ProfilePublicKey,
				NewUsername:           txMeta.NewUsername,
				NewProfilePic:         txMeta.NewProfilePic,
				NewCreatorBasisPoints: txMeta.NewCreatorBasisPoints,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeUpdateBitcoinUSDExchangeRate {
			txMeta := txn.TxnMeta.(*UpdateBitcoinUSDExchangeRateMetadataa)
			metadataExchangeRates = append(metadataExchangeRates, &PGMetadataUpdateExchangeRate{
				TransactionHash:    txnHash,
				USDCentsPerBitcoin: txMeta.USDCentsPerBitcoin,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeFollow {
			txMeta := txn.TxnMeta.(*FollowMetadata)
			metadataFollows = append(metadataFollows, &PGMetadataFollow{
				TransactionHash:   txnHash,
				FollowedPublicKey: txMeta.FollowedPublicKey,
				IsUnfollow:        txMeta.IsUnfollow,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeLike {
			txMeta := txn.TxnMeta.(*LikeMetadata)
			metadataLikes = append(metadataLikes, &PGMetadataLike{
				TransactionHash: txnHash,
				LikedPostHash:   txMeta.LikedPostHash,
				IsUnlike:        txMeta.IsUnlike,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeCreatorCoin {
			txMeta := txn.TxnMeta.(*CreatorCoinMetadataa)
			metadataCreatorCoins = append(metadataCreatorCoins, &PGMetadataCreatorCoin{
				TransactionHash:             txnHash,
				ProfilePublicKey:            txMeta.ProfilePublicKey,
				OperationType:               txMeta.OperationType,
				DeSoToSellNanos:             txMeta.DeSoToSellNanos,
				CreatorCoinToSellNanos:      txMeta.CreatorCoinToSellNanos,
				DeSoToAddNanos:              txMeta.DeSoToAddNanos,
				MinDeSoExpectedNanos:        txMeta.MinDeSoExpectedNanos,
				MinCreatorCoinExpectedNanos: txMeta.MinCreatorCoinExpectedNanos,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeSwapIdentity {
			txMeta := txn.TxnMeta.(*SwapIdentityMetadataa)
			metadataSwapIdentities = append(metadataSwapIdentities, &PGMetadataSwapIdentity{
				TransactionHash: txnHash,
				FromPublicKey:   txMeta.FromPublicKey,
				ToPublicKey:     txMeta.ToPublicKey,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeUpdateGlobalParams {
			// No extra metadata needed, it's all in ExtraData
		} else if txn.TxnMeta.GetTxnType() == TxnTypeCreatorCoinTransfer {
			txMeta := txn.TxnMeta.(*CreatorCoinTransferMetadataa)
			metadataCreatorCoinTransfers = append(metadataCreatorCoinTransfers, &PGMetadataCreatorCoinTransfer{
				TransactionHash:            txnHash,
				ProfilePublicKey:           txMeta.ProfilePublicKey,
				CreatorCoinToTransferNanos: txMeta.CreatorCoinToTransferNanos,
				ReceiverPublicKey:          txMeta.ReceiverPublicKey,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeCreateNFT {
			txMeta := txn.TxnMeta.(*CreateNFTMetadata)
			metadataCreateNFTs = append(metadataCreateNFTs, &PGMetadataCreateNFT{
				TransactionHash:           txnHash,
				NFTPostHash:               txMeta.NFTPostHash,
				NumCopies:                 txMeta.NumCopies,
				HasUnlockable:             txMeta.HasUnlockable,
				IsForSale:                 txMeta.IsForSale,
				MinBidAmountNanos:         txMeta.MinBidAmountNanos,
				CreatorRoyaltyBasisPoints: txMeta.NFTRoyaltyToCreatorBasisPoints,
				CoinRoyaltyBasisPoints:    txMeta.NFTRoyaltyToCoinBasisPoints,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeUpdateNFT {
			txMeta := txn.TxnMeta.(*UpdateNFTMetadata)
			metadataUpdateNFTs = append(metadataUpdateNFTs, &PGMetadataUpdateNFT{
				TransactionHash:   txnHash,
				NFTPostHash:       txMeta.NFTPostHash,
				SerialNumber:      txMeta.SerialNumber,
				IsForSale:         txMeta.IsForSale,
				MinBidAmountNanos: txMeta.MinBidAmountNanos,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeAcceptNFTBid {
			txMeta := txn.TxnMeta.(*AcceptNFTBidMetadata)
			metadataAcceptNFTBids = append(metadataAcceptNFTBids, &PGMetadataAcceptNFTBid{
				TransactionHash: txnHash,
				NFTPostHash:     txMeta.NFTPostHash,
				SerialNumber:    txMeta.SerialNumber,
				BidderPKID:      txMeta.BidderPKID,
				BidAmountNanos:  txMeta.BidAmountNanos,
				UnlockableText:  txMeta.UnlockableText,
			})

			for _, input := range txMeta.BidderInputs {
				metadataBidInputs = append(metadataBidInputs, &PGMetadataBidInput{
					TransactionHash: txnHash,
					InputHash:       input.TxID.NewBlockHash(),
					InputIndex:      input.Index,
				})
			}
		} else if txn.TxnMeta.GetTxnType() == TxnTypeNFTBid {
			txMeta := txn.TxnMeta.(*NFTBidMetadata)
			metadataNFTBids = append(metadataNFTBids, &PGMetadataNFTBid{
				TransactionHash: txnHash,
				NFTPostHash:     txMeta.NFTPostHash,
				SerialNumber:    txMeta.SerialNumber,
				BidAmountNanos:  txMeta.BidAmountNanos,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeNFTTransfer {
			txMeta := txn.TxnMeta.(*NFTTransferMetadata)
			metadataNFTTransfer = append(metadataNFTTransfer, &PGMetadataNFTTransfer{
				TransactionHash:   txnHash,
				NFTPostHash:       txMeta.NFTPostHash,
				SerialNumber:      txMeta.SerialNumber,
				ReceiverPublicKey: txMeta.ReceiverPublicKey,
				UnlockableText:    txMeta.UnlockableText,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeAcceptNFTTransfer {
			txMeta := txn.TxnMeta.(*AcceptNFTTransferMetadata)
			metadataAcceptNFTTransfer = append(metadataAcceptNFTTransfer, &PGMetadataAcceptNFTTransfer{
				TransactionHash: txnHash,
				NFTPostHash:     txMeta.NFTPostHash,
				SerialNumber:    txMeta.SerialNumber,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeBurnNFT {
			txMeta := txn.TxnMeta.(*BurnNFTMetadata)
			metadataBurnNFT = append(metadataBurnNFT, &PGMetadataBurnNFT{
				TransactionHash: txnHash,
				NFTPostHash:     txMeta.NFTPostHash,
				SerialNumber:    txMeta.SerialNumber,
			})
		} else if txn.TxnMeta.GetTxnType() == TxnTypeAuthorizeDerivedKey {
			txMeta := txn.TxnMeta.(*AuthorizeDerivedKeyMetadata)
			metadataDerivedKey = append(metadataDerivedKey, &PGMetadataDerivedKey{
				TransactionHash:  txnHash,
				DerivedPublicKey: *NewPublicKey(txMeta.DerivedPublicKey),
				ExpirationBlock:  txMeta.ExpirationBlock,
				OperationType:    txMeta.OperationType,
				AccessSignature:  txMeta.AccessSignature,
			})
		} else {
			return fmt.Errorf("InsertTransactionTx: Unimplemented txn type %v", txn.TxnMeta.GetTxnType().String())
		}
	}

	// Insert the block and all of its data in bulk

	if len(transactions) > 0 {
		if _, err := tx.Model(&transactions).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(transactionOutputs) > 0 {
		if _, err := tx.Model(&transactionOutputs).Returning("NULL").OnConflict("(output_hash, output_index) DO UPDATE").Insert(); err != nil {
			return err
		}
	}

	if len(transactionInputs) > 0 {
		if _, err := tx.Model(&transactionInputs).WherePK().Column("input_hash", "input_index", "spent").Update(); err != nil {
			return err
		}
	}

	if len(metadataBlockRewards) > 0 {
		if _, err := tx.Model(&metadataBlockRewards).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataBitcoinExchanges) > 0 {
		if _, err := tx.Model(&metadataBitcoinExchanges).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataPrivateMessages) > 0 {
		if _, err := tx.Model(&metadataPrivateMessages).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataSubmitPosts) > 0 {
		if _, err := tx.Model(&metadataSubmitPosts).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataUpdateProfiles) > 0 {
		if _, err := tx.Model(&metadataUpdateProfiles).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataExchangeRates) > 0 {
		if _, err := tx.Model(&metadataExchangeRates).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataFollows) > 0 {
		if _, err := tx.Model(&metadataFollows).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataLikes) > 0 {
		if _, err := tx.Model(&metadataLikes).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataCreatorCoins) > 0 {
		if _, err := tx.Model(&metadataCreatorCoins).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataSwapIdentities) > 0 {
		if _, err := tx.Model(&metadataSwapIdentities).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataCreatorCoinTransfers) > 0 {
		if _, err := tx.Model(&metadataCreatorCoinTransfers).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataCreateNFTs) > 0 {
		if _, err := tx.Model(&metadataCreateNFTs).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataUpdateNFTs) > 0 {
		if _, err := tx.Model(&metadataUpdateNFTs).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataAcceptNFTBids) > 0 {
		if _, err := tx.Model(&metadataAcceptNFTBids).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataBidInputs) > 0 {
		if _, err := tx.Model(&metadataBidInputs).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataNFTBids) > 0 {
		if _, err := tx.Model(&metadataNFTBids).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataNFTTransfer) > 0 {
		if _, err := tx.Model(&metadataNFTTransfer).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataAcceptNFTTransfer) > 0 {
		if _, err := tx.Model(&metadataAcceptNFTTransfer).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataBurnNFT) > 0 {
		if _, err := tx.Model(&metadataBurnNFT).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	if len(metadataDerivedKey) > 0 {
		if _, err := tx.Model(&metadataDerivedKey).Returning("NULL").Insert(); err != nil {
			return err
		}
	}

	return nil
}

func (postgres *Postgres) UpsertBlockAndTransactions(blockNode *BlockNode, desoBlock *MsgDeSoBlock) error {
	return postgres.db.RunInTransaction(postgres.db.Context(), func(tx *pg.Tx) error {
		err := postgres.UpsertBlockTx(tx, blockNode)
		if err != nil {
			return err
		}

		blockHash := blockNode.Hash
		err = postgres.UpsertChainTx(tx, MAIN_CHAIN, blockHash)
		if err != nil {
			return err
		}

		err = postgres.InsertTransactionsTx(tx, desoBlock.Txns, blockNode)
		if err != nil {
			return err
		}

		return nil
	})
}

//
// BlockView Flushing
//

func (postgres *Postgres) FlushView(view *UtxoView) error {
	return postgres.db.RunInTransaction(postgres.db.Context(), func(tx *pg.Tx) error {
		if err := postgres.flushUtxos(tx, view); err != nil {
			return err
		}
		if err := postgres.flushProfiles(tx, view); err != nil {
			return err
		}
		if err := postgres.flushPosts(tx, view); err != nil {
			return err
		}
		if err := postgres.flushLikes(tx, view); err != nil {
			return err
		}
		if err := postgres.flushFollows(tx, view); err != nil {
			return err
		}
		if err := postgres.flushDiamonds(tx, view); err != nil {
			return err
		}
		if err := postgres.flushMessages(tx, view); err != nil {
			return err
		}
		if err := postgres.flushCreatorCoinBalances(tx, view); err != nil {
			return err
		}
		if err := postgres.flushBalances(tx, view); err != nil {
			return err
		}
		if err := postgres.flushForbiddenKeys(tx, view); err != nil {
			return err
		}
		if err := postgres.flushNFTs(tx, view); err != nil {
			return err
		}
		if err := postgres.flushNFTBids(tx, view); err != nil {
			return err
		}
		if err := postgres.flushDerivedKeys(tx, view); err != nil {
			return err
		}

		return nil
	})
}

func (postgres *Postgres) flushUtxos(tx *pg.Tx, view *UtxoView) error {
	var outputs []*PGTransactionOutput
	for utxoKeyIter, utxoEntry := range view.UtxoKeyToUtxoEntry {
		// Making a copy of the iterator is required
		utxoKey := utxoKeyIter
		outputs = append(outputs, &PGTransactionOutput{
			OutputHash:  &utxoKey.TxID,
			OutputIndex: utxoKey.Index,
			OutputType:  utxoEntry.UtxoType,
			PublicKey:   utxoEntry.PublicKey,
			AmountNanos: utxoEntry.AmountNanos,
			Spent:       utxoEntry.isSpent,
		})
	}

	_, err := tx.Model(&outputs).WherePK().OnConflict("(output_hash, output_index) DO UPDATE").Insert()
	if err != nil {
		return err
	}

	return nil
}

func (postgres *Postgres) flushProfiles(tx *pg.Tx, view *UtxoView) error {
	var insertProfiles []*PGProfile
	var deleteProfiles []*PGProfile
	for _, pkidEntry := range view.PublicKeyToPKIDEntry {
		pkid := pkidEntry.PKID

		profile := &PGProfile{
			PKID:      pkid,
			PublicKey: NewPublicKey(pkidEntry.PublicKey),
		}

		profileEntry := view.ProfilePKIDToProfileEntry[*pkid]
		if profileEntry != nil {
			profile.Username = string(profileEntry.Username)
			profile.Description = string(profileEntry.Description)
			profile.ProfilePic = profileEntry.ProfilePic
			profile.CreatorBasisPoints = profileEntry.CreatorBasisPoints
			profile.DeSoLockedNanos = profileEntry.DeSoLockedNanos
			profile.NumberOfHolders = profileEntry.NumberOfHolders
			profile.CoinsInCirculationNanos = profileEntry.CoinsInCirculationNanos
			profile.CoinWatermarkNanos = profileEntry.CoinWatermarkNanos
		}

		if pkidEntry.isDeleted {
			deleteProfiles = append(deleteProfiles, profile)
		} else {
			insertProfiles = append(insertProfiles, profile)
		}
	}

	if len(insertProfiles) > 0 {
		_, err := tx.Model(&insertProfiles).WherePK().OnConflict("(pkid) DO UPDATE").Returning("NULL").Insert()
		if err != nil {
			return err
		}
	}

	if len(deleteProfiles) > 0 {
		_, err := tx.Model(&deleteProfiles).Returning("NULL").Delete()
		if err != nil {
			return err
		}
	}

	return nil
}

func (postgres *Postgres) flushPosts(tx *pg.Tx, view *UtxoView) error {
	var insertPosts []*PGPost
	var deletePosts []*PGPost
	for _, postEntry := range view.PostHashToPostEntry {
		if postEntry == nil {
			continue
		}

		post := &PGPost{
			PostHash:                  postEntry.PostHash,
			PosterPublicKey:           postEntry.PosterPublicKey,
			Body:                      string(postEntry.Body),
			RepostedPostHash:          postEntry.RepostedPostHash,
			QuotedRepost:              postEntry.IsQuotedRepost,
			Timestamp:                 postEntry.TimestampNanos,
			Hidden:                    postEntry.IsHidden,
			LikeCount:                 postEntry.LikeCount,
			RepostCount:               postEntry.RepostCount,
			QuoteRepostCount:          postEntry.QuoteRepostCount,
			DiamondCount:              postEntry.DiamondCount,
			CommentCount:              postEntry.CommentCount,
			Pinned:                    postEntry.IsPinned,
			NFT:                       postEntry.IsNFT,
			NumNFTCopies:              postEntry.NumNFTCopies,
			NumNFTCopiesForSale:       postEntry.NumNFTCopiesForSale,
			NumNFTCopiesBurned:        postEntry.NumNFTCopiesBurned,
			Unlockable:                postEntry.HasUnlockable,
			CreatorRoyaltyBasisPoints: postEntry.NFTRoyaltyToCreatorBasisPoints,
			CoinRoyaltyBasisPoints:    postEntry.NFTRoyaltyToCoinBasisPoints,
			ExtraData:                 postEntry.PostExtraData,
		}

		if len(postEntry.ParentStakeID) > 0 {
			post.ParentPostHash = NewBlockHash(postEntry.ParentStakeID)
		}

		if postEntry.isDeleted {
			deletePosts = append(deletePosts, post)
		} else {
			insertPosts = append(insertPosts, post)
		}
	}

	if len(insertPosts) > 0 {
		_, err := tx.Model(&insertPosts).WherePK().OnConflict("(post_hash) DO UPDATE").Returning("NULL").Insert()
		if err != nil {
			return err
		}
	}

	if len(deletePosts) > 0 {
		_, err := tx.Model(&deletePosts).Returning("NULL").Delete()
		if err != nil {
			return err
		}
	}

	return nil
}

func (postgres *Postgres) flushLikes(tx *pg.Tx, view *UtxoView) error {
	var insertLikes []*PGLike
	var deleteLikes []*PGLike
	for _, likeEntry := range view.LikeKeyToLikeEntry {
		if likeEntry == nil {
			continue
		}

		like := &PGLike{
			LikerPublicKey: likeEntry.LikerPubKey,
			LikedPostHash:  likeEntry.LikedPostHash,
		}

		if likeEntry.isDeleted {
			deleteLikes = append(deleteLikes, like)
		} else {
			insertLikes = append(insertLikes, like)
		}
	}

	if len(insertLikes) > 0 {
		_, err := tx.Model(&insertLikes).WherePK().OnConflict("DO NOTHING").Returning("NULL").Insert()
		if err != nil {
			return err
		}
	}

	if len(deleteLikes) > 0 {
		_, err := tx.Model(&deleteLikes).Returning("NULL").Delete()
		if err != nil {
			return err
		}
	}

	return nil
}

func (postgres *Postgres) flushFollows(tx *pg.Tx, view *UtxoView) error {
	var insertFollows []*PGFollow
	var deleteFollows []*PGFollow
	for _, followEntry := range view.FollowKeyToFollowEntry {
		if followEntry == nil {
			continue
		}

		follow := &PGFollow{
			FollowerPKID: followEntry.FollowerPKID,
			FollowedPKID: followEntry.FollowedPKID,
		}

		if followEntry.isDeleted {
			deleteFollows = append(deleteFollows, follow)
		} else {
			insertFollows = append(insertFollows, follow)
		}
	}

	if len(insertFollows) > 0 {
		_, err := tx.Model(&insertFollows).WherePK().OnConflict("DO NOTHING").Returning("NULL").Insert()
		if err != nil {
			return err
		}
	}

	if len(deleteFollows) > 0 {
		_, err := tx.Model(&deleteFollows).Returning("NULL").Delete()
		if err != nil {
			return err
		}
	}

	return nil
}

func (postgres *Postgres) flushDiamonds(tx *pg.Tx, view *UtxoView) error {
	var insertDiamonds []*PGDiamond
	var deleteDiamonds []*PGDiamond
	for _, diamondEntry := range view.DiamondKeyToDiamondEntry {
		diamond := &PGDiamond{
			SenderPKID:      diamondEntry.SenderPKID,
			ReceiverPKID:    diamondEntry.ReceiverPKID,
			DiamondPostHash: diamondEntry.DiamondPostHash,
			DiamondLevel:    uint8(diamondEntry.DiamondLevel),
		}

		if diamondEntry.isDeleted {
			deleteDiamonds = append(deleteDiamonds, diamond)
		} else {
			insertDiamonds = append(insertDiamonds, diamond)
		}
	}

	if len(insertDiamonds) > 0 {
		_, err := tx.Model(&insertDiamonds).WherePK().OnConflict("(sender_pkid, receiver_pkid, diamond_post_hash) DO UPDATE").Returning("NULL").Insert()
		if err != nil {
			return err
		}
	}

	if len(deleteDiamonds) > 0 {
		_, err := tx.Model(&deleteDiamonds).Returning("NULL").Delete()
		if err != nil {
			return err
		}
	}

	return nil
}

func (postgres *Postgres) flushMessages(tx *pg.Tx, view *UtxoView) error {
	var insertMessages []*PGMessage
	var deleteMessages []*PGMessage
	for _, message := range view.MessageMap {
		if message.isDeleted {
			deleteMessages = append(deleteMessages, message)
		} else {
			insertMessages = append(insertMessages, message)
		}
	}

	if len(insertMessages) > 0 {
		// TODO: There should never be a conflict here. Should we raise an error?
		_, err := tx.Model(&insertMessages).WherePK().OnConflict("(message_hash) DO NOTHING").Returning("NULL").Insert()
		if err != nil {
			return err
		}
	}

	if len(deleteMessages) > 0 {
		_, err := tx.Model(&deleteMessages).Returning("NULL").Delete()
		if err != nil {
			return err
		}
	}

	return nil
}

func (postgres *Postgres) flushCreatorCoinBalances(tx *pg.Tx, view *UtxoView) error {
	var insertBalances []*PGCreatorCoinBalance
	var deleteBalances []*PGCreatorCoinBalance
	for _, balanceEntry := range view.HODLerPKIDCreatorPKIDToBalanceEntry {
		if balanceEntry == nil {
			continue
		}

		balance := &PGCreatorCoinBalance{
			HolderPKID:   balanceEntry.HODLerPKID,
			CreatorPKID:  balanceEntry.CreatorPKID,
			BalanceNanos: balanceEntry.BalanceNanos,
			HasPurchased: balanceEntry.HasPurchased,
		}

		if balanceEntry.isDeleted {
			deleteBalances = append(deleteBalances, balance)
		} else {
			insertBalances = append(insertBalances, balance)
		}
	}

	if len(insertBalances) > 0 {
		_, err := tx.Model(&insertBalances).WherePK().OnConflict("(holder_pkid, creator_pkid) DO UPDATE").Returning("NULL").Insert()
		if err != nil {
			return err
		}
	}

	if len(deleteBalances) > 0 {
		_, err := tx.Model(&deleteBalances).Returning("NULL").Delete()
		if err != nil {
			return err
		}
	}

	return nil
}

func (postgres *Postgres) flushBalances(tx *pg.Tx, view *UtxoView) error {
	var balances []*PGBalance
	for pubKeyIter, balanceNanos := range view.PublicKeyToDeSoBalanceNanos {
		// Make a copy of the iterator since it might change from under us.
		pubKey := pubKeyIter[:]

		balance := &PGBalance{
			PublicKey:    NewPublicKey(pubKey),
			BalanceNanos: balanceNanos,
		}

		balances = append(balances, balance)
	}

	if len(balances) > 0 {
		_, err := tx.Model(&balances).WherePK().OnConflict("(public_key) DO UPDATE").Returning("NULL").Insert()
		if err != nil {
			return err
		}
	}

	return nil
}

func (postgres *Postgres) flushForbiddenKeys(tx *pg.Tx, view *UtxoView) error {
	var insertKeys []*PGForbiddenKey
	var deleteKeys []*PGForbiddenKey
	for _, keyEntry := range view.ForbiddenPubKeyToForbiddenPubKeyEntry {
		balance := &PGForbiddenKey{
			PublicKey: NewPublicKey(keyEntry.PubKey),
		}

		if keyEntry.isDeleted {
			deleteKeys = append(deleteKeys, balance)
		} else {
			insertKeys = append(insertKeys, balance)
		}
	}

	if len(insertKeys) > 0 {
		_, err := tx.Model(&insertKeys).WherePK().OnConflict("(public_key) DO UPDATE").Returning("NULL").Insert()
		if err != nil {
			return err
		}
	}

	if len(deleteKeys) > 0 {
		_, err := tx.Model(&deleteKeys).Returning("NULL").Delete()
		if err != nil {
			return err
		}
	}

	return nil
}

func (postgres *Postgres) flushNFTs(tx *pg.Tx, view *UtxoView) error {
	var insertNFTs []*PGNFT
	var deleteNFTs []*PGNFT
	for _, nftEntry := range view.NFTKeyToNFTEntry {
		nft := &PGNFT{
			NFTPostHash:                nftEntry.NFTPostHash,
			SerialNumber:               nftEntry.SerialNumber,
			LastOwnerPKID:              nftEntry.LastOwnerPKID,
			OwnerPKID:                  nftEntry.OwnerPKID,
			ForSale:                    nftEntry.IsForSale,
			MinBidAmountNanos:          nftEntry.MinBidAmountNanos,
			UnlockableText:             string(nftEntry.UnlockableText),
			LastAcceptedBidAmountNanos: nftEntry.LastAcceptedBidAmountNanos,
			IsPending:                  nftEntry.IsPending,
		}

		if nftEntry.isDeleted {
			deleteNFTs = append(deleteNFTs, nft)
		} else {
			insertNFTs = append(insertNFTs, nft)
		}
	}

	if len(insertNFTs) > 0 {
		_, err := tx.Model(&insertNFTs).WherePK().OnConflict("(nft_post_hash, serial_number) DO UPDATE").Returning("NULL").Insert()
		if err != nil {
			return err
		}
	}

	if len(deleteNFTs) > 0 {
		_, err := tx.Model(&deleteNFTs).Returning("NULL").Delete()
		if err != nil {
			return err
		}
	}

	return nil
}

func (postgres *Postgres) flushNFTBids(tx *pg.Tx, view *UtxoView) error {
	var insertBids []*PGNFTBid
	var deleteBids []*PGNFTBid
	for _, bidEntry := range view.NFTBidKeyToNFTBidEntry {
		nft := &PGNFTBid{
			BidderPKID:     bidEntry.BidderPKID,
			NFTPostHash:    bidEntry.NFTPostHash,
			SerialNumber:   bidEntry.SerialNumber,
			BidAmountNanos: bidEntry.BidAmountNanos,
			// TODO: Change how accepted bid logic works in consensus
			Accepted: false,
		}

		if bidEntry.isDeleted {
			deleteBids = append(deleteBids, nft)
		} else {
			insertBids = append(insertBids, nft)
		}
	}

	if len(insertBids) > 0 {
		_, err := tx.Model(&insertBids).WherePK().OnConflict("(nft_post_hash, bidder_pkid, serial_number) DO UPDATE").Returning("NULL").Insert()
		if err != nil {
			return err
		}
	}

	if len(deleteBids) > 0 {
		_, err := tx.Model(&deleteBids).Returning("NULL").Delete()
		if err != nil {
			return err
		}
	}

	return nil
}

func (postgres *Postgres) flushDerivedKeys(tx *pg.Tx, view *UtxoView) error {
	var insertKeys []*PGDerivedKey
	var deleteKeys []*PGDerivedKey
	for _, keyEntry := range view.DerivedKeyToDerivedEntry {
		key := &PGDerivedKey{
			OwnerPublicKey:   keyEntry.OwnerPublicKey,
			DerivedPublicKey: keyEntry.DerivedPublicKey,
			ExpirationBlock:  keyEntry.ExpirationBlock,
			OperationType:    keyEntry.OperationType,
		}

		if keyEntry.isDeleted {
			deleteKeys = append(deleteKeys, key)
		} else {
			insertKeys = append(insertKeys, key)
		}
	}

	if len(insertKeys) > 0 {
		_, err := tx.Model(&insertKeys).WherePK().OnConflict("(owner_public_key, derived_public_key) DO UPDATE").Returning("NULL").Insert()
		if err != nil {
			return err
		}
	}

	if len(deleteKeys) > 0 {
		_, err := tx.Model(&deleteKeys).Returning("NULL").Delete()
		if err != nil {
			return err
		}
	}

	return nil
}

//
// UTXOS
//

func (postgres *Postgres) GetUtxoEntryForUtxoKey(utxoKey *UtxoKey) *UtxoEntry {
	utxo := &PGTransactionOutput{
		OutputHash:  &utxoKey.TxID,
		OutputIndex: utxoKey.Index,
		Spent:       false,
	}

	err := postgres.db.Model(utxo).WherePK().Select()
	if err != nil {
		return nil
	}

	return utxo.NewUtxoEntry()
}

func (postgres *Postgres) GetUtxoEntriesForPublicKey(publicKey []byte) []*UtxoEntry {
	var transactionOutputs []*PGTransactionOutput
	err := postgres.db.Model(&transactionOutputs).Where("public_key = ?", publicKey).Select()
	if err != nil {
		return nil
	}

	var utxoEntries []*UtxoEntry
	for _, utxo := range transactionOutputs {
		utxoEntries = append(utxoEntries, utxo.NewUtxoEntry())
	}

	return utxoEntries
}

func (postgres *Postgres) GetOutputs(outputs []*PGTransactionOutput) []*PGTransactionOutput {
	err := postgres.db.Model(&outputs).WherePK().Select()
	if err != nil {
		return nil
	}
	return outputs
}

func (postgres *Postgres) GetBlockRewardsForPublicKey(publicKey *PublicKey, startHeight uint32, endHeight uint32) []*PGTransactionOutput {
	var transactionOutputs []*PGTransactionOutput
	err := postgres.db.Model(&transactionOutputs).Where("public_key = ?", publicKey).
		Where("height >= ?", startHeight).Where("height <= ?", endHeight).Select()
	if err != nil {
		return nil
	}
	return transactionOutputs
}

//
// Profiles
//

func (postgres *Postgres) GetProfileForUsername(nonLowercaseUsername string) *PGProfile {
	var profile PGProfile
	err := postgres.db.Model(&profile).Where("LOWER(username) = ?", strings.ToLower(nonLowercaseUsername)).First()
	if err != nil {
		return nil
	}
	return &profile
}

func (postgres *Postgres) GetProfileForPublicKey(publicKey []byte) *PGProfile {
	var profile PGProfile
	err := postgres.db.Model(&profile).Where("public_key = ?", publicKey).First()
	if err != nil {
		return nil
	}
	return &profile
}

func (postgres *Postgres) GetProfile(pkid PKID) *PGProfile {
	var profile PGProfile
	err := postgres.db.Model(&profile).Where("pkid = ?", pkid).First()
	if err != nil {
		return nil
	}
	return &profile
}

func (postgres *Postgres) GetProfilesForPublicKeys(publicKeys []*PublicKey) []*PGProfile {
	var profiles []*PGProfile
	err := postgres.db.Model(&profiles).WhereIn("public_key IN (?)", publicKeys).Select()
	if err != nil {
		return nil
	}
	return profiles
}

func (postgres *Postgres) GetProfilesByCoinValue(startLockedNanos uint64, limit int) []*PGProfile {
	var profiles []*PGProfile
	err := postgres.db.Model(&profiles).Where("deso_locked_nanos < ?", startLockedNanos).
		OrderExpr("deso_locked_nanos DESC").Limit(limit).Select()
	if err != nil {
		return nil
	}
	return profiles
}

func (postgres *Postgres) GetProfilesForUsernamePrefixByCoinValue(usernamePrefix string, limit int) []*PGProfile {
	var profiles []*PGProfile
	err := postgres.db.Model(&profiles).Where("username ILIKE ?", fmt.Sprintf("%s%%", usernamePrefix)).
		Where("deso_locked_nanos >= 0").OrderExpr("deso_locked_nanos DESC").Limit(limit).Select()
	if err != nil {
		return nil
	}
	return profiles
}

func (postgres *Postgres) GetProfilesForUsername(usernames []string) []*PGProfile {
	var profiles []*PGProfile
	err := postgres.db.Model(&profiles).Where("LOWER(username) IN (?)", usernames).Select()
	if err != nil {
		return nil
	}
	return profiles
}

//
// Posts
//

func (postgres *Postgres) GetPost(postHash *BlockHash) *PGPost {
	var post PGPost
	err := postgres.db.Model(&post).Where("post_hash = ?", postHash).First()
	if err != nil {
		return nil
	}
	return &post
}

func (postgres *Postgres) GetPosts(posts []*PGPost) []*PGPost {
	err := postgres.db.Model(&posts).WherePK().Select()
	if err != nil {
		return nil
	}
	return posts
}

func (postgres *Postgres) GetPostsForPublicKey(publicKey []byte, startTime uint64, limit uint64) []*PGPost {
	var posts []*PGPost
	err := postgres.db.Model(&posts).
		Where("poster_public_key = ?", publicKey).Where("timestamp < ?", startTime).
		Where("hidden IS NULL").Where("parent_post_hash IS NULL").
		OrderExpr("timestamp DESC").Limit(int(limit)).Select()
	if err != nil {
		return nil
	}
	return posts
}

//
// Comments
//

// TODO: Pagination
func (postgres *Postgres) GetComments(parentPostHash *BlockHash) []*PGPost {
	var posts []*PGPost
	err := postgres.db.Model(&posts).Where("parent_post_hash = ?", parentPostHash).Select()
	if err != nil {
		return nil
	}
	return posts
}

func (postgres *Postgres) GetMessage(messageHash *BlockHash) *PGMessage {
	var message PGMessage
	err := postgres.db.Model(&message).Where("message_hash = ?", messageHash).First()
	if err != nil {
		return nil
	}
	return &message
}

//
// LIKES
//

func (postgres *Postgres) GetLike(likerPublicKey []byte, likedPostHash *BlockHash) *PGLike {
	like := PGLike{
		LikerPublicKey: likerPublicKey,
		LikedPostHash:  likedPostHash,
	}
	err := postgres.db.Model(&like).WherePK().First()
	if err != nil {
		return nil
	}
	return &like
}

func (postgres *Postgres) GetLikes(likes []*PGLike) []*PGLike {
	err := postgres.db.Model(&likes).WherePK().Select()
	if err != nil {
		return nil
	}
	return likes
}

func (postgres *Postgres) GetLikesForPost(postHash *BlockHash) []*PGLike {
	var likes []*PGLike
	err := postgres.db.Model(&likes).Where("liked_post_hash = ?", postHash).Select()
	if err != nil {
		return nil
	}
	return likes
}

//
// Follows
//

func (postgres *Postgres) GetFollow(followerPkid *PKID, followedPkid *PKID) *PGFollow {
	follow := PGFollow{
		FollowerPKID: followerPkid,
		FollowedPKID: followedPkid,
	}
	err := postgres.db.Model(&follow).WherePK().First()
	if err != nil {
		return nil
	}
	return &follow
}

func (postgres *Postgres) GetFollows(follows []*PGFollow) []*PGFollow {
	err := postgres.db.Model(&follows).WherePK().Select()
	if err != nil {
		return nil
	}
	return follows
}

func (postgres *Postgres) GetFollowing(pkid *PKID) []*PGFollow {
	var follows []*PGFollow
	err := postgres.db.Model(&follows).Where("follower_pkid = ?", pkid).Select()
	if err != nil {
		return nil
	}
	return follows
}

func (postgres *Postgres) GetFollowers(pkid *PKID) []*PGFollow {
	var follows []*PGFollow
	err := postgres.db.Model(&follows).Where("followed_pkid = ?", pkid).Select()
	if err != nil {
		return nil
	}
	return follows
}

func (postgres *Postgres) GetDiamond(senderPkid *PKID, receiverPkid *PKID, postHash *BlockHash) *PGDiamond {
	diamond := PGDiamond{
		SenderPKID:      senderPkid,
		ReceiverPKID:    receiverPkid,
		DiamondPostHash: postHash,
	}
	err := postgres.db.Model(&diamond).WherePK().First()
	if err != nil {
		return nil
	}
	return &diamond
}

//
// Creator Coins
//

func (postgres *Postgres) GetCreatorCoinBalances(balances []*PGCreatorCoinBalance) []*PGCreatorCoinBalance {
	err := postgres.db.Model(&balances).WherePK().Select()
	if err != nil {
		return nil
	}
	return balances
}

func (postgres *Postgres) GetCreatorCoinBalance(holderPkid *PKID, creatorPkid *PKID) *PGCreatorCoinBalance {
	balance := PGCreatorCoinBalance{
		HolderPKID:  holderPkid,
		CreatorPKID: creatorPkid,
	}
	err := postgres.db.Model(&balance).WherePK().First()
	if err != nil {
		return nil
	}
	return &balance
}

func (postgres *Postgres) GetHoldings(pkid *PKID) []*PGCreatorCoinBalance {
	var holdings []*PGCreatorCoinBalance
	err := postgres.db.Model(&holdings).Where("holder_pkid = ?", pkid).Select()
	if err != nil {
		return nil
	}
	return holdings
}

func (postgres *Postgres) GetHolders(pkid *PKID) []*PGCreatorCoinBalance {
	var holdings []*PGCreatorCoinBalance
	err := postgres.db.Model(&holdings).Where("creator_pkid = ?", pkid).Select()
	if err != nil {
		return nil
	}
	return holdings
}

//
// NFTS
//

func (postgres *Postgres) GetNFT(nftPostHash *BlockHash, serialNumber uint64) *PGNFT {
	nft := PGNFT{
		NFTPostHash:  nftPostHash,
		SerialNumber: serialNumber,
	}
	err := postgres.db.Model(&nft).WherePK().First()
	if err != nil {
		return nil
	}
	return &nft
}

func (postgres *Postgres) GetNFTsForPostHash(nftPostHash *BlockHash) []*PGNFT {
	var nfts []*PGNFT
	err := postgres.db.Model(&nfts).Where("nft_post_hash = ?", nftPostHash).Select()
	if err != nil {
		return nil
	}
	return nfts
}

func (postgres *Postgres) GetNFTsForPKID(pkid *PKID) []*PGNFT {
	var nfts []*PGNFT
	err := postgres.db.Model(&nfts).Where("owner_pkid = ?", pkid).Select()
	if err != nil {
		return nil
	}
	return nfts
}

func (postgres *Postgres) GetNFTBidsForPKID(pkid *PKID) []*PGNFTBid {
	var nftBids []*PGNFTBid
	err := postgres.db.Model(&nftBids).Where("bidder_pkid = ?", pkid).Select()
	if err != nil {
		return nil
	}
	return nftBids
}

func (postgres *Postgres) GetNFTBidsForSerial(nftPostHash *BlockHash, serialNumber uint64) []*PGNFTBid {
	var nftBids []*PGNFTBid
	err := postgres.db.Model(&nftBids).Where("nft_post_hash = ?", nftPostHash).
		Where("serial_number = ?", serialNumber).Select()
	if err != nil {
		return nil
	}
	return nftBids
}

func (postgres *Postgres) GetNFTBid(nftPostHash *BlockHash, bidderPKID *PKID, serialNumber uint64) *PGNFTBid {
	bid := PGNFTBid{
		NFTPostHash:  nftPostHash,
		BidderPKID:   bidderPKID,
		SerialNumber: serialNumber,
	}
	err := postgres.db.Model(&bid).WherePK().First()
	if err != nil {
		return nil
	}
	return &bid
}

//
// Derived Keys
//

func (postgres *Postgres) GetDerivedKey(ownerPublicKey *PublicKey, derivedPublicKey *PublicKey) *PGDerivedKey {
	key := PGDerivedKey{
		OwnerPublicKey:   *ownerPublicKey,
		DerivedPublicKey: *derivedPublicKey,
	}
	err := postgres.db.Model(&key).WherePK().First()
	if err != nil {
		return nil
	}
	return &key
}

func (postgres *Postgres) GetAllDerivedKeysForOwner(ownerPublicKey *PublicKey) []*PGDerivedKey {
	var keys []*PGDerivedKey
	err := postgres.db.Model(&keys).Where("owner_public_key = ?", *ownerPublicKey).Select()
	if err != nil {
		return nil
	}
	return keys
}

//
// Balances
//

func (postgres *Postgres) GetBalance(publicKey *PublicKey) uint64 {
	balance := PGBalance{
		PublicKey: publicKey,
	}
	err := postgres.db.Model(&balance).WherePK().First()
	if err != nil {
		return 0
	}
	return balance.BalanceNanos
}

//
// PGChain Init
//

func (postgres *Postgres) InitGenesisBlock(params *DeSoParams, db *badger.DB) error {
	// Construct a node for the genesis block. Its height is zero and it has no parents. Its difficulty should be
	// set to the initial difficulty specified in the parameters and it should be assumed to be
	// valid and stored by the end of this function.
	genesisBlock := params.GenesisBlock
	diffTarget := MustDecodeHexBlockHash(params.MinDifficultyTargetHex)
	blockHash := MustDecodeHexBlockHash(params.GenesisBlockHashHex)
	genesisNode := NewBlockNode(
		nil,
		blockHash,
		0,
		diffTarget,
		BytesToBigint(ExpectedWorkForBlockHash(diffTarget)[:]),
		genesisBlock.Header,
		StatusHeaderValidated|StatusBlockProcessed|StatusBlockStored|StatusBlockValidated,
	)

	// Create the chain
	err := postgres.UpsertChain("main", blockHash)
	if err != nil {
		return fmt.Errorf("InitGenesisBlock: Error upserting chain: %v", err)
	}

	// Set the fields in the db to reflect the current state of our chain.
	//
	// Set the best hash to the genesis block in the db since its the only node
	// we're currently aware of. Set it for both the header chain and the block
	// chain.
	err = postgres.UpsertBlock(genesisNode)
	if err != nil {
		return fmt.Errorf("InitGenesisBlock: Error upserting block: %v", err)
	}

	for index, txOutput := range params.SeedBalances {
		_, err := postgres.db.Model(&PGTransactionOutput{
			OutputHash:  &BlockHash{},
			OutputIndex: uint32(index),
			OutputType:  UtxoTypeOutput,
			AmountNanos: txOutput.AmountNanos,
			PublicKey:   txOutput.PublicKey,
		}).Returning("NULL").Insert()
		if err != nil {
			return err
		}
	}

	return nil
}

//
// API
//

func (postgres *Postgres) GetNotifications(publicKey string) ([]*PGNotification, error) {
	keyBytes, _, _ := Base58CheckDecode(publicKey)

	var notifications []*PGNotification
	err := postgres.db.Model(&notifications).Where("to_user = ?", keyBytes).Order("timestamp desc").Limit(100).Select()
	if err != nil {
		return nil, err
	}

	return notifications, nil
}