-- headers table can be queried by hash or height
create table if not exists headers (
       id int primary key,
       hash text,       
       flags int,
       height int,
       mtp int,
       version int,
       time int,
       bits int,
       nonce int,
       merkle_root text,
       parent_id int
);

create index if not exists headers_hash_ix
       on headers (hash);
create index if not exists headers_height_ix
       on headers (height);
create index if not exists headers_parent_id_ix
       on headers (parent_id);


-- transactions table can be queried by hash

-- we don't need a output and input fks in transaction table. Instead,
-- for reverse navigation from output to transaction we use the
-- transaction_id in the output to directly look up transaction with
-- the primary key using the join table.
-- This avoids the circular reference problem in the in-memory db schema.
create table if not exists transactions (
       id int primary key,
       hash text,
       coinbase bool,
       size int,
       weight int,
       locktime int,
       version int,
       input_count int,
       output_count int
);

create index if not exists transactions_hash_ix
       on transactions (hash);

-- join table to store relationship between headers and transactions
create table if not exists headers_transactions (
       header_id int,
       transaction_id int
);

create unique index if not exists headers_transactions_headers_ix
       on headers_transactions (header_id);
create unique index if not exists headers_transactions_transactions_ix
       on headers_transactions (transaction_id);

create table if not exists inputs (
       id int primary key,
       input_index int,
       sequence int,
       length int,
       script TEXT,
       witness bool
);

create table if not exists transactions_inputs (
       transaction_id int,
       input_id int,
       input_index int -- this input_index helps query for a point (transaction_id, index)
);

create index if not exists transactions_inputs_transaction_id_ix
       on transactions_inputs (transaction_id);
create index if not exists transactions_inputs_input_id_ix
       on transactions_inputs (input_id);
create index if not exists transactions_inputs_input_index_ix
       on transactions_inputs (input_index);

create table if not exists outputs (
       id int primary key,
       output_index int,
       value int,
       length int,
       script TEXT
);

create table if not exists transactions_outputs (
       transaction_id int,
       output_id int,
       output_index int -- this input_index helps query for a point (transaction_id, index)
);

create index if not exists transactions_outputs_transaction_id_ix
       on transactions_outputs (transaction_id);
create index if not exists transactions_outputs_output_id_ix
       on transactions_outputs (output_id);
create index if not exists transactions_inputs_output_index_ix
       on transactions_outputs (output_index);


create table if not exists prevouts(
       input_id int,
       output_id int
);

create index if not exists prevouts_input_ix on prevouts(input_id);
create index if not exists prevouts_output_ix on prevouts(output_id);