-- based on https://collective.flashbots.net/t/do-you-want-to-run-a-builder/212/17
-- https://github.com/flashbots/builder/blob/main/flashbotsextra/database_types.go
-- https://github.com/flashbots/builder/blob/main/flashbotsextra/database_test.go

CREATE TABLE built_blocks (
    block_id              BIGINT PRIMARY KEY,
    block_number          BIGINT NOT NULL,
    profit                VARCHAR NOT NULL,
    slot                  BIGINT NOT NULL,
    hash                  VARCHAR NOT NULL,
    gas_limit             BIGINT NOT NULL,
    gas_used              BIGINT NOT NULL,
    base_fee              BIGINT NOT NULL,
    parent_hash           VARCHAR NOT NULL,
    proposer_pubkey       VARCHAR NOT NULL,
    proposer_fee_recipient VARCHAR NOT NULL,
    builder_pubkey        VARCHAR NOT NULL,
    timestamp             BIGINT NOT NULL,
    timestamp_datetime    TIMESTAMP NOT NULL,
    orders_closed_at      TIMESTAMP NOT NULL,
    sealed_at             TIMESTAMP NOT NULL
);

CREATE TABLE bundles (
    id                    BIGSERIAL PRIMARY KEY,
    bundle_hash           VARCHAR NOT NULL,

    param_signed_txs      VARCHAR NOT NULL,
    param_block_number    BIGINT NOT NULL,
    param_timestamp       BIGINT,
    received_timestamp    TIMESTAMP NOT NULL,
    param_reverting_tx_hashes VARCHAR,

    coinbase_diff         VARCHAR NOT NULL,
    total_gas_used        BIGINT NOT NULL,
    state_block_number    BIGINT NOT NULL,
    gas_fees              VARCHAR NOT NULL,
    eth_sent_to_coinbase  VARCHAR NOT NULL,
    is_high_prio          BOOLEAN NOT NULL DEFAULT false
);

CREATE TABLE built_blocks_bundles (
    block_id     BIGINT NOT NULL,
    bundle_id    BIGINT,
    block_number BIGINT NOT NULL,
    bundle_hash  VARCHAR NOT NULL,

    FOREIGN KEY (block_id) REFERENCES built_blocks(block_id),
    FOREIGN KEY (bundle_id) REFERENCES bundles(id)
);

CREATE TABLE built_blocks_all_bundles (
    block_id     BIGINT NOT NULL,
    bundle_id    BIGINT,
    block_number BIGINT NOT NULL,
    bundle_hash  VARCHAR NOT NULL,

    FOREIGN KEY (block_id) REFERENCES built_blocks(block_id),
    FOREIGN KEY (bundle_id) REFERENCES bundles(id)
);

CREATE TABLE latest_uuid_bundle (
    replacement_uuid UUID PRIMARY KEY,
    signing_address  VARCHAR NOT NULL,
    bundle_hash      VARCHAR NOT NULL,
    target_block_number BIGINT
);