-- Additive Unit-to-Order identities. A Unit remains one record while it can
-- participate in multiple authorised Orders within its own Province.
CREATE TABLE IF NOT EXISTS unit_orders (
  unit_id BIGINT UNSIGNED NOT NULL,
  order_key VARCHAR(80) NOT NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (unit_id,order_key),
  KEY idx_unit_orders_active_order (order_key,active,unit_id),
  CONSTRAINT fk_unit_orders_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Preserve every installed Unit's existing primary Order before enabling
-- additional Orders. Safe to run repeatedly.
INSERT IGNORE INTO unit_orders(unit_id,order_key,is_primary,active)
SELECT id,order_key,1,1
FROM units
WHERE order_key IS NOT NULL AND TRIM(order_key)<>'';

INSERT IGNORE INTO migration_history(version,name,checksum,execution_ms)
VALUES('202610010065','Unit multi-Order identities',SHA2('202610010065_unit_orders_v1',256),0);