USE faunamix;

SET @has_option_expires_at := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'reservation_requests'
    AND COLUMN_NAME = 'option_expires_at'
);
SET @sql_option_expires_at := IF(
  @has_option_expires_at = 0,
  'ALTER TABLE reservation_requests ADD COLUMN option_expires_at DATETIME NULL DEFAULT NULL AFTER status',
  'SELECT 1'
);
PREPARE stmt FROM @sql_option_expires_at; EXECUTE stmt; DEALLOCATE PREPARE stmt;

ALTER TABLE reservation_requests
  MODIFY COLUMN status ENUM('pre_reservation','confirmed','payment_pending','payment_completed','cancelled')
  NOT NULL DEFAULT 'pre_reservation';
