-- 011 - the public establishment store.
--
-- An establishment purchase sells two quantities in one Checkout session, and
-- `uniq_session` means one session is one row. `quantity` holds their sum; the
-- split and the amount actually charged live in the new columns, since
-- unit_amount_cents cannot express two unit prices.
--
-- Re-runnable: every ALTER is guarded on information_schema.

-- kind += 'establishment'
SET @has_kind := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'orders'
    AND COLUMN_NAME = 'kind'
    AND COLUMN_TYPE LIKE '%establishment%'
);
SET @sql := IF(@has_kind = 0,
  "ALTER TABLE `orders` MODIFY `kind` enum('class','player','renewal','individual','establishment') NOT NULL",
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- classes_quantity
SET @has_col := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'classes_quantity'
);
SET @sql := IF(@has_col = 0,
  'ALTER TABLE `orders` ADD COLUMN `classes_quantity` smallint UNSIGNED DEFAULT NULL AFTER `quantity`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- players_quantity
SET @has_col := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'players_quantity'
);
SET @sql := IF(@has_col = 0,
  'ALTER TABLE `orders` ADD COLUMN `players_quantity` smallint UNSIGNED DEFAULT NULL AFTER `classes_quantity`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- total_amount_cents — what Stripe actually charged, in one number.
SET @has_col := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'total_amount_cents'
);
SET @sql := IF(@has_col = 0,
  'ALTER TABLE `orders` ADD COLUMN `total_amount_cents` int UNSIGNED DEFAULT NULL AFTER `unit_amount_cents`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- school_name — typed on the buy page, before any establishment row exists.
-- Fulfilment names the establishment from it; kept here so an order that
-- failed to fulfil still says which school paid.
SET @has_col := (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'school_name'
);
SET @sql := IF(@has_col = 0,
  'ALTER TABLE `orders` ADD COLUMN `school_name` varchar(255) DEFAULT NULL AFTER `buyer_name`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
