-- Flexible navigation-only relationships between independent Provinces or Districts.
CREATE TABLE IF NOT EXISTS province_links (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  province_id_a BIGINT UNSIGNED NOT NULL,
  province_id_b BIGINT UNSIGNED NOT NULL,
  relationship_label VARCHAR(100) NULL,
  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 (id),
  UNIQUE KEY uq_province_link_pair (province_id_a,province_id_b),
  KEY idx_province_link_b (province_id_b,province_id_a),
  CONSTRAINT fk_province_link_a FOREIGN KEY (province_id_a) REFERENCES provinces(id) ON DELETE CASCADE,
  CONSTRAINT fk_province_link_b FOREIGN KEY (province_id_b) REFERENCES provinces(id) ON DELETE CASCADE,
  CONSTRAINT fk_province_link_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_province_link_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT chk_province_link_order CHECK (province_id_a < province_id_b)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS province_link_audit (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  link_id BIGINT UNSIGNED NULL,
  province_id_a BIGINT UNSIGNED NULL,
  province_id_b BIGINT UNSIGNED NULL,
  province_a_name VARCHAR(200) NOT NULL,
  province_b_name VARCHAR(200) NOT NULL,
  order_a_label VARCHAR(200) NOT NULL,
  order_b_label VARCHAR(200) NOT NULL,
  relationship_label VARCHAR(100) NULL,
  action ENUM('created','updated','removed') NOT NULL,
  actor_id BIGINT UNSIGNED NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_province_link_audit_time (occurred_at,id),
  KEY idx_province_link_audit_provinces (province_id_a,province_id_b,occurred_at),
  CONSTRAINT fk_province_link_audit_link FOREIGN KEY (link_id) REFERENCES province_links(id) ON DELETE SET NULL,
  CONSTRAINT fk_province_link_audit_actor FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;