drop table if exists messages;

drop table if exists dispatches;

create table dispatches (
                            id          bigint primary key auto_increment,
                            uuid        varchar(36) not null,
                            from_iccid  varchar(22),
                            submitter   varchar(50) not null,
                            submission_timestamp bigint,
                            modification_timestamp bigint,
                            send_to_international boolean,
                            send_to_national_landlines boolean,
                            send_to_national_mobiles boolean,
                            resend_successfully_delivered boolean,
                            resend_successfully_sent boolean,
                            resend_unsuccessfully_sent boolean,
                            resend_unsent boolean,
                            ask_for_delivery_report boolean,
                            status tinyint,
                            message_count int
);

drop index if exists ix_dispatches_uuid on dispatches;

create index ix_dispatches_uuid on dispatches (uuid);

drop index if exists ix_dispatches_from_iccid on dispatches;

create index ix_dispatches_from_iccid on dispatches (from_iccid);

drop index if exists ix_dispatches_submitter on dispatches;

create index ix_dispatches_submitter on dispatches (submitter);

drop index if exists ix_dispatches_submission_timestamp on dispatches;

create index ix_dispatches_submission_timestamp on dispatches (submission_timestamp);

drop index if exists ix_dispatches_status on dispatches;

create index ix_dispatches_status on dispatches (status);

create table messages (
                          id bigint primary key auto_increment,
                          hash_high bigint not null,
                          hash_low bigint not null,
                          from_iccid varchar(22),
                          to_number varchar(16),
                          content varchar(1000),
                          creation_timestamp bigint,
                          allocation_timestamp bigint,
                          sent_timestamp bigint,
                          delivered_timestamp bigint,
                          dispatch_request_id bigint,
                          sending_status smallint,
                          delivery_status smallint,
                          constraint fk_messages_dispatches foreign key(dispatch_request_id) references dispatches(id)
);

drop index if exists ix_messages_hash on dispatches;

create index ix_messages_hash on messages (hash_high, hash_low);

drop index if exists ix_messages_from_iccid on dispatches;

create index ix_messages_from_iccid on messages (from_iccid);

drop index if exists ix_messages_to_number on dispatches;

create index ix_messages_to_number on messages (to_number);

drop index if exists ix_messages_creation_timestamp on dispatches;

create index ix_messages_creation_timestamp on messages (creation_timestamp);

drop index if exists ix_messages_allocation_timestamp on dispatches;

create index ix_messages_allocation_timestamp on messages (allocation_timestamp);

drop index if exists ix_messages_sent_timestamp on dispatches;

create index ix_messages_sent_timestamp on messages (sent_timestamp);

drop index if exists ix_messages_delivered_timestamp on dispatches;

create index ix_messages_delivered_timestamp on messages (delivered_timestamp);

drop index if exists ix_messages_sending_status on dispatches;

create index ix_messages_sending_status on messages (sending_status);

drop index if exists ix_messages_delivery_status on dispatches;

create index ix_messages_delivery_status on messages (delivery_status);
