SET NAMES utf8mb4;

CREATE TABLE sms_admin_user (
  admin_user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL,
  name VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role_code ENUM('superadmin','operator','billing','viewer') NOT NULL DEFAULT 'viewer',
  active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME(3) 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 (admin_user_id),
  UNIQUE KEY uq_admin_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_admin_session (
  admin_session_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  admin_user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  csrf_hash CHAR(64) NOT NULL,
  expires_at DATETIME(3) NOT NULL,
  revoked_at DATETIME(3) NULL,
  ip VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (admin_session_id),
  UNIQUE KEY uq_admin_session_token_hash (token_hash),
  KEY ix_admin_session_user_expiry (admin_user_id, expires_at),
  CONSTRAINT fk_admin_session_user FOREIGN KEY (admin_user_id) REFERENCES sms_admin_user(admin_user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_client (
  client_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_code VARCHAR(64) NOT NULL,
  name VARCHAR(190) NOT NULL,
  status ENUM('active','suspended','disabled') NOT NULL DEFAULT 'active',
  default_country_code VARCHAR(8) NOT NULL DEFAULT '+40',
  timezone VARCHAR(64) NOT NULL DEFAULT 'Europe/Bucharest',
  monthly_segment_limit BIGINT UNSIGNED NULL,
  daily_segment_limit BIGINT UNSIGNED NULL,
  max_segments_per_message INT UNSIGNED NOT NULL DEFAULT 10,
  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 (client_id),
  UNIQUE KEY uq_client_code (client_code),
  KEY ix_client_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_api_key (
  api_key_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_id BIGINT UNSIGNED NULL,
  name VARCHAR(190) NOT NULL,
  key_prefix VARCHAR(32) NOT NULL,
  key_hash CHAR(64) NOT NULL,
  scopes TEXT NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  last_used_at DATETIME(3) NULL,
  expires_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  revoked_at DATETIME(3) NULL,
  PRIMARY KEY (api_key_id),
  UNIQUE KEY uq_api_key_hash (key_hash),
  KEY ix_api_key_client_active (client_id, active),
  CONSTRAINT fk_api_key_client FOREIGN KEY (client_id) REFERENCES sms_client(client_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_provider (
  provider_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(64) NOT NULL,
  name VARCHAR(190) NOT NULL,
  country_code CHAR(2) NOT NULL DEFAULT 'RO',
  active TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT 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 (provider_id),
  UNIQUE KEY uq_provider_code (code),
  KEY ix_provider_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_provider_account (
  provider_account_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  contract_reference VARCHAR(190) NULL,
  account_owner_type ENUM('regantis','client','other') NOT NULL DEFAULT 'regantis',
  account_owner_client_id BIGINT UNSIGNED NULL,
  billing_name VARCHAR(190) NULL,
  billing_currency CHAR(3) NOT NULL DEFAULT 'RON',
  billing_cycle_day TINYINT UNSIGNED NOT NULL DEFAULT 1,
  monthly_fixed_cost DECIMAL(15,4) NULL,
  included_segments BIGINT UNSIGNED NULL,
  overage_cost_per_segment DECIMAL(15,6) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT 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 (provider_account_id),
  KEY ix_provider_account_provider_active (provider_id, active),
  KEY ix_provider_account_owner (account_owner_client_id),
  CONSTRAINT fk_provider_account_provider FOREIGN KEY (provider_id) REFERENCES sms_provider(provider_id) ON DELETE RESTRICT,
  CONSTRAINT fk_provider_account_client FOREIGN KEY (account_owner_client_id) REFERENCES sms_client(client_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_sim (
  sim_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider_account_id BIGINT UNSIGNED NOT NULL,
  label VARCHAR(190) NOT NULL,
  msisdn VARCHAR(32) NOT NULL,
  country_code CHAR(2) NOT NULL DEFAULT 'RO',
  iccid_masked VARCHAR(32) NULL,
  status ENUM('active','paused','quota_exhausted','blocked','inactive','retired') NOT NULL DEFAULT 'inactive',
  activation_date DATE NULL,
  deactivation_date DATE NULL,
  billing_fixed_cost DECIMAL(15,4) NULL,
  included_segments BIGINT UNSIGNED NULL,
  overage_cost_per_segment DECIMAL(15,6) NULL,
  daily_limit BIGINT UNSIGNED NULL,
  monthly_limit BIGINT UNSIGNED NULL,
  minute_rate_limit INT UNSIGNED NULL,
  hour_rate_limit INT UNSIGNED NULL,
  notes TEXT 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 (sim_id),
  UNIQUE KEY uq_sim_msisdn (msisdn),
  KEY ix_sim_provider_account_status (provider_account_id, status),
  CONSTRAINT fk_sim_provider_account FOREIGN KEY (provider_account_id) REFERENCES sms_provider_account(provider_account_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_gateway (
  gateway_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  gateway_uuid CHAR(36) NOT NULL,
  gateway_type ENUM('ANDROID','LINUX_MODEM','MODEM_BANK') NOT NULL DEFAULT 'ANDROID',
  name VARCHAR(190) NOT NULL,
  status ENUM('provisioning','online','offline','degraded','disabled','revoked') NOT NULL DEFAULT 'provisioning',
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  manufacturer VARCHAR(120) NULL,
  model VARCHAR(120) NULL,
  android_version VARCHAR(64) NULL,
  sdk_version INT UNSIGNED NULL,
  app_version VARCHAR(64) NULL,
  device_fingerprint_hash CHAR(64) NULL,
  credential_hash CHAR(64) NULL,
  provisioned_at DATETIME(3) NULL,
  last_connected_at DATETIME(3) NULL,
  last_seen_at DATETIME(3) NULL,
  last_ip VARCHAR(45) NULL,
  battery_percent TINYINT UNSIGNED NULL,
  is_charging TINYINT(1) NULL,
  network_type VARCHAR(32) NULL,
  connection_version VARCHAR(32) 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 (gateway_id),
  UNIQUE KEY uq_gateway_uuid (gateway_uuid),
  UNIQUE KEY uq_gateway_credential_hash (credential_hash),
  KEY ix_gateway_status_enabled (status, enabled),
  KEY ix_gateway_last_seen (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_gateway_slot (
  gateway_slot_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  gateway_id BIGINT UNSIGNED NOT NULL,
  slot_index TINYINT UNSIGNED NOT NULL,
  sim_id BIGINT UNSIGNED NULL,
  runtime_subscription_id INT NULL,
  carrier_display_name VARCHAR(190) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  last_verified_at DATETIME(3) NULL,
  last_changed_at DATETIME(3) 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 (gateway_slot_id),
  UNIQUE KEY uq_gateway_slot (gateway_id, slot_index),
  UNIQUE KEY uq_gateway_slot_sim (sim_id),
  KEY ix_gateway_slot_active (gateway_id, active),
  CONSTRAINT fk_gateway_slot_gateway FOREIGN KEY (gateway_id) REFERENCES sms_gateway(gateway_id) ON DELETE CASCADE,
  CONSTRAINT fk_gateway_slot_sim FOREIGN KEY (sim_id) REFERENCES sms_sim(sim_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_client_route (
  route_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_id BIGINT UNSIGNED NOT NULL,
  sim_id BIGINT UNSIGNED NOT NULL,
  priority INT UNSIGNED NOT NULL DEFAULT 10,
  weight INT UNSIGNED NOT NULL DEFAULT 1,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  allow_fallback TINYINT(1) NOT NULL DEFAULT 1,
  country_prefix VARCHAR(16) NULL,
  minute_limit_override INT UNSIGNED NULL,
  hour_limit_override INT UNSIGNED NULL,
  daily_limit_override BIGINT UNSIGNED NULL,
  monthly_limit_override BIGINT 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 (route_id),
  UNIQUE KEY uq_client_route_sim (client_id, sim_id),
  KEY ix_client_route_select (client_id, enabled, priority),
  CONSTRAINT fk_client_route_client FOREIGN KEY (client_id) REFERENCES sms_client(client_id) ON DELETE CASCADE,
  CONSTRAINT fk_client_route_sim FOREIGN KEY (sim_id) REFERENCES sms_sim(sim_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sms_gateway_provision_token (
  token_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  gateway_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  expires_at DATETIME(3) NOT NULL,
  claimed_at DATETIME(3) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (token_id),
  UNIQUE KEY uq_gateway_provision_token_hash (token_hash),
  KEY ix_gateway_provision_gateway (gateway_id, expires_at),
  CONSTRAINT fk_gateway_provision_gateway FOREIGN KEY (gateway_id) REFERENCES sms_gateway(gateway_id) ON DELETE CASCADE,
  CONSTRAINT fk_gateway_provision_admin FOREIGN KEY (created_by) REFERENCES sms_admin_user(admin_user_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;