-- Guided, editable Order shortlists and per-Province overrides.
SET @drop_old_province_name_index = IF(
  (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='provinces' AND index_name='uq_provinces_normalized_name')>0,
  'ALTER TABLE provinces DROP INDEX uq_provinces_normalized_name',
  'SELECT 1'
);
PREPARE province_link_stmt FROM @drop_old_province_name_index;
EXECUTE province_link_stmt;
DEALLOCATE PREPARE province_link_stmt;

SET @add_province_order_identity = IF(
  (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='provinces' AND column_name='normalized_order_identity')=0,
  "ALTER TABLE provinces ADD COLUMN normalized_order_identity VARCHAR(260) GENERATED ALWAYS AS (CASE WHEN order_key='other' THEN CONCAT('other:',LOWER(TRIM(COALESCE(custom_order_name,'')))) ELSE COALESCE(NULLIF(order_key,''),'(unset)') END) STORED",
  'SELECT 1'
);
PREPARE province_link_stmt FROM @add_province_order_identity;
EXECUTE province_link_stmt;
DEALLOCATE PREPARE province_link_stmt;

SET @add_province_order_name_index = IF(
  (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name='provinces' AND index_name='uq_provinces_order_name')=0,
  'ALTER TABLE provinces ADD UNIQUE INDEX uq_provinces_order_name(normalized_name,normalized_order_identity)',
  'SELECT 1'
);
PREPARE province_link_stmt FROM @add_province_order_name_index;
EXECUTE province_link_stmt;
DEALLOCATE PREPARE province_link_stmt;

CREATE TABLE IF NOT EXISTS order_link_pairing_defaults (
  source_order_key VARCHAR(100) NOT NULL,
  target_order_key VARCHAR(100) NOT NULL,
  display_order TINYINT UNSIGNED NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_by BIGINT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (source_order_key,target_order_key),
  KEY idx_order_link_shortlist (source_order_key,active,display_order),
  CONSTRAINT fk_order_link_default_created FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_order_link_default_updated FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT chk_order_link_default_distinct CHECK (source_order_key <> target_order_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS province_link_pairing_settings (
  province_id BIGINT UNSIGNED NOT NULL,
  use_platform_defaults TINYINT(1) NOT NULL DEFAULT 1,
  updated_by BIGINT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (province_id),
  CONSTRAINT fk_province_link_setting_province FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE CASCADE,
  CONSTRAINT fk_province_link_setting_actor FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS province_link_pairing_overrides (
  province_id BIGINT UNSIGNED NOT NULL,
  target_order_key VARCHAR(100) NOT NULL,
  display_order TINYINT UNSIGNED NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (province_id,target_order_key),
  KEY idx_province_link_override (province_id,display_order),
  CONSTRAINT fk_province_link_override_province FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE CASCADE,
  CONSTRAINT fk_province_link_override_actor FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS province_link_configuration_audit (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  scope_type ENUM('platform','province') NOT NULL,
  province_id BIGINT UNSIGNED NULL,
  source_order_key VARCHAR(100) NULL,
  target_order_key VARCHAR(100) NULL,
  action VARCHAR(50) NOT NULL,
  actor_id BIGINT UNSIGNED NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_province_link_config_audit (occurred_at,id),
  CONSTRAINT fk_province_link_config_province FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE SET NULL,
  CONSTRAINT fk_province_link_config_actor FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO order_link_pairing_defaults(source_order_key,target_order_key,display_order) VALUES
('craft','royal_arch',1),('royal_arch','craft',1),
('mark','royal_ark_mariners',1),('royal_ark_mariners','mark',1),
('knights_templar','knights_malta',1),('knights_malta','knights_templar',1),
('secret_monitor','scarlet_cord',1),('scarlet_cord','secret_monitor',1);

UPDATE province_links
SET relationship_label='Associated Province or District'
WHERE relationship_label IS NULL OR TRIM(relationship_label)='';

ALTER TABLE province_links
  MODIFY relationship_label VARCHAR(100) NOT NULL;