ALTER TABLE workflow_steps
  ADD COLUMN retired_at DATETIME NULL AFTER updated_at,
  ADD COLUMN active_scope_key VARCHAR(40)
    GENERATED ALWAYS AS (IF(retired_at IS NULL, 'ACTIVE', id)) STORED AFTER retired_at,
  DROP INDEX uq_workflow_steps_code,
  DROP INDEX uq_workflow_steps_order,
  ADD UNIQUE KEY uq_workflow_steps_code (workflow_template_id, step_code, active_scope_key),
  ADD UNIQUE KEY uq_workflow_steps_order (workflow_template_id, step_order, active_scope_key),
  ADD KEY idx_workflow_steps_retired (workflow_template_id, retired_at);

ALTER TABLE workflow_required_documents
  ADD COLUMN retired_at DATETIME NULL AFTER created_at,
  ADD COLUMN active_scope_key VARCHAR(40)
    GENERATED ALWAYS AS (IF(retired_at IS NULL, 'ACTIVE', id)) STORED AFTER retired_at,
  DROP INDEX uq_workflow_required_documents_code,
  ADD UNIQUE KEY uq_workflow_required_documents_code (workflow_template_id, requirement_code, active_scope_key),
  ADD KEY idx_workflow_required_documents_retired (workflow_template_id, retired_at);

ALTER TABLE workflow_required_forms
  ADD COLUMN retired_at DATETIME NULL AFTER created_at,
  ADD COLUMN active_scope_key VARCHAR(40)
    GENERATED ALWAYS AS (IF(retired_at IS NULL, 'ACTIVE', id)) STORED AFTER retired_at,
  DROP INDEX uq_workflow_required_forms_code,
  ADD UNIQUE KEY uq_workflow_required_forms_code (workflow_template_id, requirement_code, active_scope_key),
  ADD KEY idx_workflow_required_forms_retired (workflow_template_id, retired_at);

ALTER TABLE packet_template_items
  ADD COLUMN retired_at DATETIME NULL AFTER created_at,
  ADD COLUMN active_scope_key VARCHAR(40)
    GENERATED ALWAYS AS (IF(retired_at IS NULL, 'ACTIVE', id)) STORED AFTER retired_at,
  DROP INDEX uq_packet_template_items_order,
  ADD UNIQUE KEY uq_packet_template_items_order (packet_template_id, sort_order, active_scope_key),
  ADD KEY idx_packet_template_items_retired (packet_template_id, retired_at);
