CREATE TABLE IF NOT EXISTS mobile_refresh_tokens (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  company_id INT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  device_name VARCHAR(190) NULL,
  device_id VARCHAR(190) NULL,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_mobile_refresh_token_hash (token_hash),
  INDEX idx_mobile_refresh_user (company_id, user_id, revoked_at),
  INDEX idx_mobile_refresh_device (company_id, user_id, device_id),
  INDEX idx_mobile_refresh_expiry (expires_at),
  CONSTRAINT fk_mobile_refresh_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_mobile_refresh_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS push_tokens (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  company_id INT UNSIGNED NOT NULL,
  platform VARCHAR(30) NOT NULL,
  token VARCHAR(255) NOT NULL,
  device_id VARCHAR(190) NULL,
  device_name VARCHAR(190) NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  last_seen_at DATETIME NULL,
  INDEX idx_push_tokens_user (company_id, user_id, enabled),
  INDEX idx_push_tokens_device (company_id, user_id, device_id),
  INDEX idx_push_tokens_token (token(128)),
  CONSTRAINT fk_push_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_push_tokens_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
