-- Optional manual migration for production databases.
-- Development can use: npm run db:sync
CREATE TABLE IF NOT EXISTS impersonation_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  session_code VARCHAR(80) NOT NULL UNIQUE,
  impersonation_type ENUM('SUPER_ADMIN_TO_TENANT','TENANT_TO_CLIENT') NOT NULL,
  tenant_id BIGINT UNSIGNED NULL,
  source_user_id BIGINT UNSIGNED NOT NULL,
  source_user_type VARCHAR(30) NOT NULL,
  target_user_id BIGINT UNSIGNED NOT NULL,
  target_user_type VARCHAR(30) NOT NULL,
  token_jti VARCHAR(100) NOT NULL,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ended_at DATETIME NULL,
  status ENUM('ACTIVE','ENDED','EXPIRED','REVOKED') NOT NULL DEFAULT 'ACTIVE',
  reason VARCHAR(255) NULL,
  ip_address VARCHAR(80) NULL,
  user_agent VARCHAR(500) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  is_delete TINYINT(1) NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  deleted_by BIGINT UNSIGNED NULL,
  deleted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_imp_session_code (session_code),
  INDEX idx_imp_tenant_status (tenant_id, status, is_delete),
  INDEX idx_imp_source_target (source_user_id, target_user_id, status, is_delete),
  INDEX idx_imp_token_status (token_jti, status, is_delete)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
