-- 014 - separate the licence buyer from the establishment.
--
-- 012 put the payer's billing address on `establishments`, which was wrong:
-- the payer is a fact about one purchase, not about the school. A second
-- order paid by the mairie would have overwritten the first, losing the trail
-- exactly where the facture needs it.
--
--   orders         <- who paid, per purchase (billing_*, buyer_email/name)
--   establishments <- the school: name, kind, ville, pays, and its own
--                     contact_email / contact_phone, which nothing fills
--                     automatically and an admin enters when there is one.
--
-- The billing values already on establishments are copied onto their order
-- before the columns are dropped.
--
-- Re-runnable: every step is guarded on information_schema.

-- ── orders: the payer snapshot ──────────────────────────────────────────
-- buyer_email and buyer_name already carry the payer's email and name.

SET @has := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'billing_phone');
SET @sql := IF(@has = 0,
  'ALTER TABLE `orders`
     ADD COLUMN `billing_phone` varchar(32) DEFAULT NULL AFTER `buyer_name`,
     ADD COLUMN `billing_line1` varchar(255) DEFAULT NULL AFTER `billing_phone`,
     ADD COLUMN `billing_line2` varchar(255) DEFAULT NULL AFTER `billing_line1`,
     ADD COLUMN `billing_city` varchar(128) DEFAULT NULL AFTER `billing_line2`,
     ADD COLUMN `billing_postal_code` varchar(32) DEFAULT NULL AFTER `billing_city`,
     ADD COLUMN `billing_country` char(2) DEFAULT NULL AFTER `billing_postal_code`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── carry 012's values across before they are dropped ───────────────────
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 = 1,
  'UPDATE orders o
     JOIN establishments e ON e.establishment_id = o.establishment_id
      SET o.billing_phone       = COALESCE(o.billing_phone, e.phone),
          o.billing_line1       = COALESCE(o.billing_line1, e.address_line1),
          o.billing_line2       = COALESCE(o.billing_line2, e.address_line2),
          o.billing_city        = COALESCE(o.billing_city, e.city),
          o.billing_postal_code = COALESCE(o.billing_postal_code, e.postal_code),
          o.billing_country     = COALESCE(o.billing_country, e.country),
          o.buyer_name          = COALESCE(o.buyer_name, e.contact_name)
    WHERE o.kind = ''establishment''',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── establishments: keep the school, drop the payer ─────────────────────
-- `city` and `country` stay and now mean the school's own town and country,
-- which is what the buy page asks for. For the one row where they held a
-- billing address, the two are the same place.

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 = 1,
  'ALTER TABLE `establishments`
     DROP COLUMN `contact_name`,
     DROP COLUMN `phone`,
     DROP COLUMN `address_line1`,
     DROP COLUMN `address_line2`,
     DROP COLUMN `postal_code`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- The school's own number, entered by hand. Nothing fills it from a payment.
SET @has := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'establishments' AND COLUMN_NAME = 'contact_phone');
SET @sql := IF(@has = 0,
  'ALTER TABLE `establishments` ADD COLUMN `contact_phone` varchar(32) DEFAULT NULL AFTER `contact_email`',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Searching schools by town is the point of storing it.
SET @has := (SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'establishments' AND INDEX_NAME = 'idx_establishment_city');
SET @sql := IF(@has = 0,
  'ALTER TABLE `establishments` ADD KEY `idx_establishment_city` (`country`, `city`)',
  'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
