USE faunamix;

SET @has_quantity := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'reservation_requests'
    AND COLUMN_NAME = 'quantity'
);
SET @sql_quantity := IF(
  @has_quantity = 0,
  'ALTER TABLE reservation_requests ADD COLUMN quantity INT UNSIGNED NOT NULL DEFAULT 1 AFTER product_id',
  'SELECT 1'
);
PREPARE stmt FROM @sql_quantity; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_unit_price := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'reservation_requests'
    AND COLUMN_NAME = 'unit_price_try'
);
SET @sql_unit_price := IF(
  @has_unit_price = 0,
  'ALTER TABLE reservation_requests ADD COLUMN unit_price_try DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER requested_end',
  'SELECT 1'
);
PREPARE stmt FROM @sql_unit_price; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_total_price := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'reservation_requests'
    AND COLUMN_NAME = 'total_price_try'
);
SET @sql_total_price := IF(
  @has_total_price = 0,
  'ALTER TABLE reservation_requests ADD COLUMN total_price_try DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER unit_price_try',
  'SELECT 1'
);
PREPARE stmt FROM @sql_total_price; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_status := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'reservation_requests'
    AND COLUMN_NAME = 'status'
);
SET @sql_status_stage1 := IF(
  @has_status = 0,
  "ALTER TABLE reservation_requests ADD COLUMN status ENUM('new','processed','cancelled','pre_reservation','confirmed','payment_pending') NOT NULL DEFAULT 'pre_reservation' AFTER total_price_try",
  "ALTER TABLE reservation_requests MODIFY COLUMN status ENUM('new','processed','cancelled','pre_reservation','confirmed','payment_pending') NOT NULL DEFAULT 'pre_reservation'"
);
PREPARE stmt FROM @sql_status_stage1; EXECUTE stmt; DEALLOCATE PREPARE stmt;

UPDATE reservation_requests
SET status = CASE status
  WHEN 'new' THEN 'pre_reservation'
  WHEN 'processed' THEN 'confirmed'
  WHEN 'cancelled' THEN 'cancelled'
  WHEN 'pre_reservation' THEN 'pre_reservation'
  WHEN 'confirmed' THEN 'confirmed'
  WHEN 'payment_pending' THEN 'payment_pending'
  ELSE 'pre_reservation'
END;

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

UPDATE reservation_requests r
INNER JOIN products p ON p.id = r.product_id
SET
  r.quantity = CASE WHEN r.quantity < 1 THEN 1 ELSE r.quantity END,
  r.unit_price_try = CASE
    WHEN r.unit_price_try <= 0 THEN COALESCE(p.price_try, 0)
    ELSE r.unit_price_try
  END,
  r.total_price_try = CASE
    WHEN r.total_price_try <= 0 THEN COALESCE(p.price_try, 0) * (CASE WHEN r.quantity < 1 THEN 1 ELSE r.quantity END)
    ELSE r.total_price_try
  END
WHERE r.unit_price_try <= 0 OR r.total_price_try <= 0 OR r.quantity < 1;
