drop table if exists sims;

drop table if exists devices;

create table devices (
    id                                  bigint primary key auto_increment,
    uuid                                varchar(36) not null,
    owner                               varchar(50) not null,
    cordova                             varchar(50),
    model                               varchar(512),
    platform                            varchar(50),
    version                             varchar(50),
    manufacturer                        varchar(50),
    is_virtual                          boolean,
    serial                              varchar(100),
    carrier_name                        varchar(64),
    country_code                        varchar(8),
    mcc                                 varchar(8),
    mnc                                 varchar(8),
    call_state                          smallint,
    data_activity                       smallint,
    network_type                        smallint,
    phone_type                          smallint,
    sim_state                           smallint,
    is_network_roaming                  boolean,
    phone_count                         tinyint,
    active_subscription_info_count      smallint,
    active_subscription_info_count_max  smallint,
    phone_number                        varchar(32),
    device_id                           varchar(32),
    device_software_version             varchar(32),
    sim_serial_number                   varchar(32),
    subscriber_id                       varchar(32),
    last_seen                           bigint,
    online                              boolean,
    constraint fk_devices_users foreign key(owner) references users(username)
);

drop index if exists ix_devices_uuid on devices;

create unique index ix_devices_uuid on devices (uuid);

drop index if exists ix_devices_last_seen on devices;

create unique index ix_devices_last_seen on devices (last_seen);

create table sims (
     id                     bigint primary key auto_increment,
     physical_device_id     bigint not null,
     carrier_name           varchar(64),
     display_name           varchar(256),
     country_code           varchar(8),
     mcc                    varchar(8),
     mnc                    varchar(8),
     is_network_roaming     boolean,
     is_data_roaming        boolean,
     sim_slot_index         tinyint,
     phone_number           varchar(16),
     device_id              varchar(32),
     sim_serial_number      varchar(32),
     subscription_id        varchar(32),
     last_seen              bigint,
     usable                 boolean,
     constraint fk_sims_devices foreign key(physical_device_id) references devices(id)
);

drop index if exists ix_sims_sim_serial_number on sims;

create unique index ix_sims_sim_serial_number on sims (sim_serial_number);
