indexer/services/db/migrations/014_locktime_and_redeem.down.sql

-- would drop any locktime values and reset to 0, a re-index would be required to backout.
ALTER TABLE `avm_outputs` DROP COLUMN locktime;
ALTER TABLE `avm_outputs` ADD COLUMN locktime int unsigned default 0 not null;
ALTER TABLE `avm_outputs` DROP COLUMN stake_locktime;
ALTER TABLE `avm_outputs` ADD COLUMN redeemed_at timestamp null;
ALTER TABLE `avm_outputs` ADD COLUMN redeeming_transaction_id varchar(50) default '' not null;
create index avm_outputs_chain_id_redeeming_transaction_id ON avm_outputs (chain_id, redeeming_transaction_id);

##
## Transactions
##

create table pvm_transactions
(
    id         varchar(50)     not null primary key,
    type       smallint        not null,
    block_id   varchar(50)     not null,
    nonce      bigint unsigned not null,
    signature  binary(65),
    created_at timestamp       not null default current_timestamp
);


##
## Subnets
##

create table pvm_subnets
(
    id         varchar(50)  not null primary key,
    network_id int unsigned not null,
    chain_id   varchar(50)  not null,
    threshold  int unsigned not null,
    created_at timestamp    not null default current_timestamp
);

create table pvm_subnet_control_keys
(
    subnet_id  varchar(50) not null,
    address    varchar(50) not null,
    public_key binary(33)  null

);
create unique index pvm_subnet_control_keys_subnet_id_address_idx ON pvm_subnet_control_keys (subnet_id, address);

##
## Validators
##

create table pvm_validators
(
    transaction_id varchar(50)     not null,

    # Validator
    node_id        varchar(50)     not null,
    weight         bigint unsigned not null,

    # Duration validator
    start_time     datetime        not null,
    end_time       datetime        not null,

    # Default subnet validator
    destination    varchar(50)     not null,
    shares         int unsigned    not null,

    # Subnet validator
    subnet_id      varchar(50)     not null
);
create index pvm_validators_node_id_idx ON pvm_validators (node_id);
create index pvm_validators_subnet_id_idx ON pvm_validators (subnet_id);
create unique index pvm_validators_tx_id_idx ON pvm_validators (transaction_id);

##
## Chains
##

create table pvm_chains
(
    id           varchar(50)      not null primary key,
    network_id   int unsigned     not null,
    subnet_id    varchar(50)      not null,

    name         varchar(255)     not null,
    vm_id        varchar(50)      not null,
    genesis_data varbinary(16384) not null
);

create table pvm_chains_control_signatures
(
    chain_id  varchar(50) not null,
    signature binary(65)  not null
);
create index pvm_chains_control_sigs_chain_id_idx ON pvm_chains_control_signatures (chain_id);


create table pvm_chains_fx_ids
(
    chain_id varchar(50) not null,
    fx_id    varchar(50) not null

);
create index pvm_chains_fix_ids_chain_id_idx ON pvm_chains_fx_ids (chain_id);
create unique index pvm_chains_fx_ids_chain_id_fix_id_idx ON pvm_chains_fx_ids (chain_id, fx_id);