-- Additive Order-to-Province mapping for hierarchical dashboard selection.
CREATE TABLE IF NOT EXISTS province_orders (
  province_id BIGINT UNSIGNED NOT NULL,
  order_key VARCHAR(60) NOT NULL,
  display_order INT UNSIGNED NOT NULL DEFAULT 100,
  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 (province_id,order_key),
  KEY idx_province_orders_lookup (order_key,active,display_order,province_id),
  CONSTRAINT fk_province_orders_province FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO province_orders(province_id,order_key,display_order)
SELECT id,order_key,100 FROM provinces WHERE order_key IS NOT NULL AND TRIM(order_key)<>'';

INSERT IGNORE INTO province_orders(province_id,order_key,display_order)
SELECT DISTINCT province_id,order_key,100 FROM units
WHERE province_id IS NOT NULL AND order_key IS NOT NULL AND TRIM(order_key)<>'' AND deleted_at IS NULL;