SET NAMES utf8mb4;

CREATE TABLE sms_message (
  message_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id VARCHAR(40) NOT NULL,
  client_id BIGINT UNSIGNED NOT NULL,
  api_key_id BIGINT UNSIGNED NULL,
  idempotency_key VARCHAR(190) NOT NULL,
  destination_e164 VARCHAR(32) NOT NULL,
  body_ciphertext LONGBLOB NULL,
  body_iv VARBINARY(32) NULL,
  body_tag VARBINARY(32) NULL,
  body_hash CHAR(64) NOT NULL,
  reference VARCHAR(190) NULL,
  status ENUM('queued','routing','assigned','accepted','sending','sent','delivery_pending','partially_delivered','delivered','failed','unknown','cancelled','expired') NOT NULL DEFAULT 'queued',
  priority ENUM('high','normal','low') NOT NULL DEFAULT 'normal',
  segments_estimated INT UNSIGNED NULL,
  segments_actual INT UNSIGNED NULL,
  selected_sim_id BIGINT UNSIGNED NULL,
  selected_gateway_id BIGINT UNSIGNED NULL,
  current_attempt_id BIGINT UNSIGNED NULL,
  queued_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  assigned_at DATETIME(3) NULL,
  accepted_at DATETIME(3) NULL,
  sent_at DATETIME(3) NULL,
  delivered_at DATETIME(3) NULL,
  failed_at DATETIME(3) NULL,
  expires_at DATETIME(3) NULL,
  last_error_code VARCHAR(100) NULL,
  last_error_message VARCHAR(500) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (message_id),
  UNIQUE KEY uq_message_public_id (public_id),
  UNIQUE KEY uq_message_client_idempotency (client_id, idempotency_key),
  KEY ix_message_client_status_created (client_id, status, created_at),
  KEY ix_message_status_queue (status, priority, queued_at),
  KEY ix_message_reference (client_id, reference),
  KEY ix_message_destination (client_id, destination_e164),
  KEY ix_message_current_attempt (current_attempt_id),
  CONSTRAINT fk_message_client FOREIGN KEY (client_id) REFERENCES sms_client(client_id) ON DELETE RESTRICT,
  CONSTRAINT fk_message_api_key FOREIGN KEY (api_key_id) REFERENCES sms_api_key(api_key_id) ON DELETE SET NULL,
  CONSTRAINT fk_message_sim FOREIGN KEY (selected_sim_id) REFERENCES sms_sim(sim_id) ON DELETE RESTRICT,
  CONSTRAINT fk_message_gateway FOREIGN KEY (selected_gateway_id) REFERENCES sms_gateway(gateway_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_message_attempt (
  attempt_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  attempt_public_id VARCHAR(40) NOT NULL,
  message_id BIGINT UNSIGNED NOT NULL,
  sim_id BIGINT UNSIGNED NOT NULL,
  gateway_id BIGINT UNSIGNED NOT NULL,
  gateway_slot_id BIGINT UNSIGNED NOT NULL,
  command_id VARCHAR(40) NOT NULL,
  attempt_number INT UNSIGNED NOT NULL,
  status ENUM('assigned','accepted','sending','sent','failed','unknown','reconciling') NOT NULL DEFAULT 'assigned',
  dispatch_at DATETIME(3) NULL,
  accepted_at DATETIME(3) NULL,
  send_started_at DATETIME(3) NULL,
  sent_at DATETIME(3) NULL,
  failed_at DATETIME(3) NULL,
  unknown_at DATETIME(3) NULL,
  failure_class ENUM('DEFINITE_NOT_SENT','RETRYABLE_NOT_SENT','UNCERTAIN','SENT') NULL,
  error_code VARCHAR(100) NULL,
  error_message VARCHAR(500) NULL,
  segments_count INT UNSIGNED NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (attempt_id),
  UNIQUE KEY uq_attempt_public_id (attempt_public_id),
  UNIQUE KEY uq_attempt_command_id (command_id),
  UNIQUE KEY uq_attempt_message_number (message_id, attempt_number),
  KEY ix_attempt_status_created (status, created_at),
  CONSTRAINT fk_attempt_message FOREIGN KEY (message_id) REFERENCES sms_message(message_id) ON DELETE CASCADE,
  CONSTRAINT fk_attempt_sim FOREIGN KEY (sim_id) REFERENCES sms_sim(sim_id) ON DELETE RESTRICT,
  CONSTRAINT fk_attempt_gateway FOREIGN KEY (gateway_id) REFERENCES sms_gateway(gateway_id) ON DELETE RESTRICT,
  CONSTRAINT fk_attempt_slot FOREIGN KEY (gateway_slot_id) REFERENCES sms_gateway_slot(gateway_slot_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_message_part (
  part_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  attempt_id BIGINT UNSIGNED NOT NULL,
  part_index INT UNSIGNED NOT NULL,
  part_count INT UNSIGNED NOT NULL,
  status ENUM('pending','sent','failed','delivery_pending','delivered','delivery_unknown') NOT NULL DEFAULT 'pending',
  android_result_code VARCHAR(100) NULL,
  android_error_code VARCHAR(100) NULL,
  sent_at DATETIME(3) NULL,
  delivered_at DATETIME(3) NULL,
  delivery_pdu_hash CHAR(64) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (part_id),
  UNIQUE KEY uq_message_part_attempt_index (attempt_id, part_index),
  KEY ix_message_part_status (status),
  CONSTRAINT fk_message_part_attempt FOREIGN KEY (attempt_id) REFERENCES sms_message_attempt(attempt_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;