-- Public order tracking token + payment fields for customer PWA.

ALTER TABLE `orders`
  ADD COLUMN `tracking_token` varchar(64) NULL AFTER `order_number`,
  ADD COLUMN `payment_method` enum('pay_at_counter') NOT NULL DEFAULT 'pay_at_counter' AFTER `status`,
  ADD COLUMN `payment_status` enum('unpaid','paid','refunded') NOT NULL DEFAULT 'unpaid' AFTER `payment_method`;

UPDATE `orders`
SET `tracking_token` = CONCAT(
  REPLACE(UUID(), '-', ''),
  SUBSTRING(SHA2(CONCAT(id, order_number, RAND()), 256), 1, 16)
)
WHERE `tracking_token` IS NULL OR `tracking_token` = '';

ALTER TABLE `orders`
  MODIFY COLUMN `tracking_token` varchar(64) NOT NULL,
  ADD UNIQUE KEY `UQ_orders_tracking_token` (`tracking_token`);