-- ------------------------------------------------------------
-- Design Core v3 patch — role separated workflow
-- MySQL 5.7 compatible. Does not drop existing design/product/stone/order data.
-- ------------------------------------------------------------

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='design_requests' AND COLUMN_NAME='revision_requested_at')=0,
  'ALTER TABLE design_requests ADD COLUMN revision_requested_at DATETIME NULL AFTER admin_decision_at',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='design_requests' AND COLUMN_NAME='production_revision_at')=0,
  'ALTER TABLE design_requests ADD COLUMN production_revision_at DATETIME NULL AFTER revision_requested_at',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='design_requests' AND COLUMN_NAME='product_linked_at')=0,
  'ALTER TABLE design_requests ADD COLUMN product_linked_at DATETIME NULL AFTER production_revision_at',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='design_groups' AND COLUMN_NAME='revision_note')=0,
  'ALTER TABLE design_groups ADD COLUMN revision_note TEXT NULL AFTER admin_note',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='design_groups' AND COLUMN_NAME='production_revision_note')=0,
  'ALTER TABLE design_groups ADD COLUMN production_revision_note TEXT NULL AFTER revision_note',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
