-- MyArrival v0.8 migration for an existing v0.7/v0.7.1 database.
-- Run once in phpMyAdmin. If you import the new full schema.sql into a fresh database,
-- you do not need this migration.

ALTER TABLE conversations ADD COLUMN chat_token_hash CHAR(64) NULL AFTER channel;
CREATE UNIQUE INDEX uq_conversations_chat_token ON conversations(chat_token_hash);

CREATE TABLE IF NOT EXISTS rate_limits (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bucket VARCHAR(60) NOT NULL,
  key_hash CHAR(64) NOT NULL,
  window_start BIGINT NOT NULL,
  count INT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY uq_rate_bucket_key (bucket,key_hash),
  INDEX idx_rate_window (window_start)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS traveller_profiles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NULL,
  nationality VARCHAR(100) NULL,
  passport_number_enc TEXT NULL,
  date_of_birth_enc TEXT NULL,
  passport_country VARCHAR(100) NULL,
  gender VARCHAR(30) NULL,
  accommodation_enc TEXT NULL,
  purpose VARCHAR(80) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_profile_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_profile_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  INDEX idx_profile_user (user_id), INDEX idx_profile_order (order_id)
) ENGINE=InnoDB;