CREATE DATABASE IF NOT EXISTS gsm_modem
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE gsm_modem;

CREATE TABLE IF NOT EXISTS devices (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  sim VARCHAR(32) NOT NULL DEFAULT '',
  phone VARCHAR(20) NOT NULL DEFAULT '',
  imei VARCHAR(20) NOT NULL DEFAULT '',
  status VARCHAR(64) NOT NULL DEFAULT 'NEW SIM',
  first_seen DATETIME NOT NULL,
  last_seen DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_sim_phone (sim, phone),
  KEY idx_imei (imei)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sim_imei_history (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  sim VARCHAR(32) NOT NULL,
  imei VARCHAR(20) NOT NULL,
  phone VARCHAR(20) NOT NULL DEFAULT '',
  first_seen DATETIME NOT NULL,
  last_seen DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sim_imei (sim, imei)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;