-- Apply once after migration 014. Existing Provinces remain usable with an
-- unspecified Order until an administrator explicitly chooses one.
-- Before import, check for duplicate names using:
-- SELECT LOWER(TRIM(name)) AS name_key, COUNT(*) AS copies FROM provinces
-- GROUP BY LOWER(TRIM(name)) HAVING COUNT(*) > 1;
-- Rename duplicates first; a UNIQUE index cannot be added while they remain.
ALTER TABLE provinces
  ADD COLUMN order_key VARCHAR(40) NULL AFTER slug,
  ADD COLUMN custom_order_name VARCHAR(150) NULL AFTER order_key,
  ADD COLUMN normalized_name VARCHAR(200) GENERATED ALWAYS AS (LOWER(TRIM(name))) STORED,
  ADD UNIQUE KEY uq_provinces_normalized_name(normalized_name);