-- 012 - billing contact details for an establishment.
--
-- Stripe Checkout collects these on the public buy page (required billing
-- address + phone); fulfilment copies them here so a school's details are
-- searchable in our own admin, not only in the Stripe dashboard.
--
-- `contact_name` is one column because Stripe's name field is one field.
--
-- Re-runnable: every ALTER is guarded on information_schema.

-- contact_name
SET @has := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'establishments' AND COLUMN_NAME = 'contact_name');
SET @sql := IF(@has = 0,
  'ALTER TABLE `establishments` ADD COLUMN `contact_name` varchar(128) DEFAULT NULL AFTER `contact_email`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- phone
SET @has := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'establishments' AND COLUMN_NAME = 'phone');
SET @sql := IF(@has = 0,
  'ALTER TABLE `establishments` ADD COLUMN `phone` varchar(32) DEFAULT NULL AFTER `contact_name`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- address_line1
SET @has := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'establishments' AND COLUMN_NAME = 'address_line1');
SET @sql := IF(@has = 0,
  'ALTER TABLE `establishments` ADD COLUMN `address_line1` varchar(255) DEFAULT NULL AFTER `phone`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- address_line2
SET @has := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'establishments' AND COLUMN_NAME = 'address_line2');
SET @sql := IF(@has = 0,
  'ALTER TABLE `establishments` ADD COLUMN `address_line2` varchar(255) DEFAULT NULL AFTER `address_line1`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- city
SET @has := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'establishments' AND COLUMN_NAME = 'city');
SET @sql := IF(@has = 0,
  'ALTER TABLE `establishments` ADD COLUMN `city` varchar(128) DEFAULT NULL AFTER `address_line2`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- postal_code
SET @has := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'establishments' AND COLUMN_NAME = 'postal_code');
SET @sql := IF(@has = 0,
  'ALTER TABLE `establishments` ADD COLUMN `postal_code` varchar(32) DEFAULT NULL AFTER `city`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- country (ISO 3166-1 alpha-2, as Stripe returns it)
SET @has := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'establishments' AND COLUMN_NAME = 'country');
SET @sql := IF(@has = 0,
  'ALTER TABLE `establishments` ADD COLUMN `country` char(2) DEFAULT NULL AFTER `postal_code`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
