migrations

var Migration001InitDatabase = &migrate.Migration{
	Id: "001-init-database",
	Up: []string{`
		CREATE TABLE IF NOT EXISTS ` + vars.TableValidatorRegistration + ` (
			id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			pubkey        varchar(98) NOT NULL,
			fee_recipient varchar(42) NOT NULL,
			timestamp     bigint NOT NULL,
			gas_limit     bigint NOT NULL,
			signature     text NOT NULL
		);

		CREATE UNIQUE INDEX IF NOT EXISTS ` + vars.TableValidatorRegistration + `_pubkey_timestamp_uidx ON ` + vars.TableValidatorRegistration + `(pubkey, timestamp DESC);


		CREATE TABLE IF NOT EXISTS ` + vars.TableExecutionPayload + ` (
			id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			slot            bigint NOT NULL,
			proposer_pubkey varchar(98) NOT NULL,
			block_hash      varchar(66) NOT NULL,

			version text NOT NULL,
			payload json NOT NULL
		);

		CREATE UNIQUE INDEX IF NOT EXISTS ` + vars.TableExecutionPayload + `_slot_pk_hash_idx ON ` + vars.TableExecutionPayload + `(slot, proposer_pubkey, block_hash);


		CREATE TABLE IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + ` (
			id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			execution_payload_id bigint,

			-- simulation & verification results
			sim_success boolean NOT NULL,
			sim_error   text    NOT NULL,

			-- bidtrace data
			signature            text NOT NULL,

			slot        bigint NOT NULL,
			parent_hash varchar(66) NOT NULL,
			block_hash  varchar(66) NOT NULL,

			builder_pubkey         varchar(98) NOT NULL,
			proposer_pubkey        varchar(98) NOT NULL,
			proposer_fee_recipient varchar(42) NOT NULL,

			gas_used   bigint NOT NULL,
			gas_limit  bigint NOT NULL,

			num_tx int NOT NULL,
			value  NUMERIC(48, 0),

			-- helpers
			epoch        bigint NOT NULL,
			block_number bigint NOT NULL,
			was_most_profitable boolean NOT NULL
		);

		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_slot_idx ON ` + vars.TableBuilderBlockSubmission + `("slot");
		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_blockhash_idx ON ` + vars.TableBuilderBlockSubmission + `("block_hash");
		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_blocknumber_idx ON ` + vars.TableBuilderBlockSubmission + `("block_number");
		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_builderpubkey_idx ON ` + vars.TableBuilderBlockSubmission + `("builder_pubkey");
		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_simsuccess_idx ON ` + vars.TableBuilderBlockSubmission + `("sim_success");
		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_mostprofit_idx ON ` + vars.TableBuilderBlockSubmission + `("was_most_profitable");
		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_executionpayloadid_idx ON ` + vars.TableBuilderBlockSubmission + `("execution_payload_id");


		CREATE TABLE IF NOT EXISTS ` + vars.TableDeliveredPayload + ` (
			id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			signed_blinded_beacon_block json,

			epoch bigint NOT NULL,
			slot  bigint NOT NULL,

			builder_pubkey         varchar(98) NOT NULL,
			proposer_pubkey        varchar(98) NOT NULL,
			proposer_fee_recipient varchar(42) NOT NULL,

			parent_hash  varchar(66) NOT NULL,
			block_hash   varchar(66) NOT NULL,
			block_number bigint NOT NULL,

			gas_used  bigint NOT NULL,
			gas_limit bigint NOT NULL,

			num_tx  int NOT NULL,
			value   NUMERIC(48, 0),

			UNIQUE (slot, proposer_pubkey, block_hash)
		);

		CREATE INDEX IF NOT EXISTS ` + vars.TableDeliveredPayload + `_slot_idx ON ` + vars.TableDeliveredPayload + `("slot");
		CREATE INDEX IF NOT EXISTS ` + vars.TableDeliveredPayload + `_blockhash_idx ON ` + vars.TableDeliveredPayload + `("block_hash");
		CREATE INDEX IF NOT EXISTS ` + vars.TableDeliveredPayload + `_blocknumber_idx ON ` + vars.TableDeliveredPayload + `("block_number");
		CREATE INDEX IF NOT EXISTS ` + vars.TableDeliveredPayload + `_proposerpubkey_idx ON ` + vars.TableDeliveredPayload + `("proposer_pubkey");
		CREATE INDEX IF NOT EXISTS ` + vars.TableDeliveredPayload + `_builderpubkey_idx ON ` + vars.TableDeliveredPayload + `("builder_pubkey");
		CREATE INDEX IF NOT EXISTS ` + vars.TableDeliveredPayload + `_value_idx ON ` + vars.TableDeliveredPayload + `("value");


		CREATE TABLE IF NOT EXISTS ` + vars.TableBlockBuilder + ` (
			id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			builder_pubkey  varchar(98) NOT NULL,
			description    	text NOT NULL,

			is_high_prio    boolean NOT NULL,
			is_blacklisted  boolean NOT NULL,

			last_submission_id   bigint references ` + vars.TableBuilderBlockSubmission + `(id) on delete set null,
			last_submission_slot bigint NOT NULL,

			num_submissions_total    bigint NOT NULL,
			num_submissions_simerror bigint NOT NULL,
			num_submissions_topbid   bigint NOT NULL,

			num_sent_getpayload bigint NOT NULL DEFAULT 0,

			UNIQUE (builder_pubkey)
		);
		`},
	Down: []string{`
		DROP TABLE IF EXISTS ` + vars.TableBuilderBlockSubmission + `;
		DROP TABLE IF EXISTS ` + vars.TableDeliveredPayload + `;
		DROP TABLE IF EXISTS ` + vars.TableBlockBuilder + `;
		DROP TABLE IF EXISTS ` + vars.TableExecutionPayload + `;
		DROP TABLE IF EXISTS ` + vars.TableValidatorRegistration + `;
		`},
	DisableTransactionUp:   false,
	DisableTransactionDown: false,
}

var Migration002RemoveIsBestAddReceivedAt = &migrate.Migration{
	Id: "002-remove-isbest-add-receivedat",
	Up: []string{`
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD received_at timestamp;

		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` DROP COLUMN was_most_profitable;
		DROP INDEX IF EXISTS ` + vars.TableBuilderBlockSubmission + `_mostprofit_idx;

		ALTER TABLE ` + vars.TableBlockBuilder + ` DROP COLUMN num_submissions_topbid;
	`, `
		CREATE INDEX CONCURRENTLY IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_received_idx ON ` + vars.TableBuilderBlockSubmission + `(received_at DESC);
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration003AddEligibleAtSignedAt = &migrate.Migration{
	Id: "003-add-eligibleat-add-signedat",
	Up: []string{`
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD eligible_at timestamp;
		ALTER TABLE ` + vars.TableDeliveredPayload + ` ADD signed_at timestamp;
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration004BlockedValidator = &migrate.Migration{
	Id: "004-blocked-validator",
	Up: []string{`
		CREATE TABLE IF NOT EXISTS ` + vars.TableBlockedValidator + ` (
			id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			pubkey        varchar(98) NOT NULL,
			is_blocked    boolean NOT NULL default false,
			notes         text NOT NULL default '',

			UNIQUE (pubkey)
		);
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration005RemoveBlockedValidator = &migrate.Migration{
	Id: "005-remove-blocked-validator",
	Up: []string{`
		DROP TABLE IF EXISTS ` + vars.TableBlockedValidator + `;
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration006CreateTooLateGetPayload = &migrate.Migration{
	Id: "006-create-too-late-get-payload",
	Up: []string{`
		CREATE TABLE IF NOT EXISTS ` + vars.TableTooLateGetPayload + ` (
			id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			slot            bigint NOT NULL,

			slot_start_timestamp   bigint NOT NULL,
			request_timestamp bigint NOT NULL,
			decode_timestamp  bigint NOT NULL,

			proposer_pubkey varchar(98) NOT NULL,
			block_hash      varchar(66) NOT NULL,
			ms_into_slot    bigint NOT NULL
		);

		CREATE UNIQUE INDEX IF NOT EXISTS ` + vars.TableTooLateGetPayload + `_slot_pk_hash_idx ON ` + vars.TableTooLateGetPayload + `(slot, proposer_pubkey, block_hash);
	`, `
		ALTER TABLE ` + vars.TableDeliveredPayload + ` ADD publish_ms bigint NOT NULL DEFAULT 0;
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration007BuilderSubmissionWasSimulated = &migrate.Migration{
	Id: "007-builder-submission-was-simulated",
	Up: []string{`
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD was_simulated boolean NOT NULL DEFAULT true;
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD sim_req_error text NOT NULL DEFAULT '';
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration008Optimistic = &migrate.Migration{
	Id: "008-optimistic",
	Up: []string{
		`
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD optimistic_submission bool NOT NULL default false;
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD decode_duration       bigint NOT NULL default 0;
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD prechecks_duration    bigint NOT NULL default 0;
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD simulation_duration   bigint NOT NULL default 0;
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD redis_update_duration bigint NOT NULL default 0;
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD total_duration        bigint NOT NULL default 0;
	`, `
		ALTER TABLE ` + vars.TableBlockBuilder + ` ADD is_optimistic  bool NOT NULL default false;
		ALTER TABLE ` + vars.TableBlockBuilder + ` ADD collateral     NUMERIC(48, 0) NOT NULL default 0;
		ALTER TABLE ` + vars.TableBlockBuilder + ` ADD builder_id     varchar(98) NOT NULL default '';
	`, `
		CREATE TABLE IF NOT EXISTS ` + vars.TableBuilderDemotions + `(
			id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,
			
			submit_block_request          json,
			signed_beacon_block  	      json,
			signed_validator_registration json,

			epoch bigint NOT NULL,
			slot  bigint NOT NULL,
			
			builder_pubkey  varchar(98) NOT NULL,
			proposer_pubkey varchar(98) NOT NULL,
			
			value NUMERIC(48, 0),
			
			fee_recipient varchar(42) NOT NULL,
			
			block_hash varchar(66) NOT NULL,
			
			sim_error text NOT NULL,
			
			UNIQUE (builder_pubkey, block_hash)
		);
	`,
	},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration009BlockBuilderRemoveReference = &migrate.Migration{
	Id: "009-block-builder-remove-reference",
	Up: []string{`
		ALTER TABLE ` + vars.TableBlockBuilder + ` DROP CONSTRAINT "` + vars.TableBlockBuilder + `_last_submission_id_fkey";
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration010PayloadAddBlobFields = &migrate.Migration{
	Id: "010-payload-add-blob-fields",
	Up: []string{`
		ALTER TABLE ` + vars.TableDeliveredPayload + ` ADD blob_gas_used bigint NOT NULL DEFAULT 0;
		ALTER TABLE ` + vars.TableDeliveredPayload + ` ADD excess_blob_gas bigint NOT NULL DEFAULT 0;
		ALTER TABLE ` + vars.TableDeliveredPayload + ` ADD num_blobs int NOT NULL DEFAULT 0;
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migrations = migrate.MemoryMigrationSource{
	Migrations: []*migrate.Migration{
		Migration001InitDatabase,
		Migration002RemoveIsBestAddReceivedAt,
		Migration003AddEligibleAtSignedAt,
		Migration004BlockedValidator,
		Migration005RemoveBlockedValidator,
		Migration006CreateTooLateGetPayload,
		Migration007BuilderSubmissionWasSimulated,
		Migration008Optimistic,
		Migration009BlockBuilderRemoveReference,
		Migration010PayloadAddBlobFields,
	},
}