-- Group-based, navigation-only links between independent units in linked Provinces.
CREATE TABLE IF NOT EXISTS unit_link_groups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(200) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_unit_link_group_creator 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 unit_link_group_members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  group_id BIGINT UNSIGNED NOT NULL,
  unit_id BIGINT UNSIGNED NOT NULL,
  source_unit_id BIGINT UNSIGNED NULL,
  status ENUM('pending','active','removed') NOT NULL,
  initiated_scope ENUM('platform','province','unit') NOT NULL,
  dual_confirmation_required TINYINT(1) NOT NULL DEFAULT 0,
  source_confirmed_by BIGINT UNSIGNED NULL,
  source_confirmed_at DATETIME NULL,
  target_confirmed_by BIGINT UNSIGNED NULL,
  target_confirmed_at DATETIME NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  removed_by BIGINT UNSIGNED NULL,
  removed_at DATETIME NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_unit_link_member (unit_id),
  KEY idx_unit_link_group_status (group_id,status,unit_id),
  KEY idx_unit_link_source (source_unit_id,status),
  CONSTRAINT fk_unit_link_member_group FOREIGN KEY (group_id) REFERENCES unit_link_groups(id) ON DELETE RESTRICT,
  CONSTRAINT fk_unit_link_member_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE RESTRICT,
  CONSTRAINT fk_unit_link_member_source FOREIGN KEY (source_unit_id) REFERENCES units(id) ON DELETE RESTRICT,
  CONSTRAINT fk_unit_link_member_source_confirmer FOREIGN KEY (source_confirmed_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_unit_link_member_target_confirmer FOREIGN KEY (target_confirmed_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_unit_link_member_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_unit_link_member_remover FOREIGN KEY (removed_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT chk_unit_link_member_source CHECK (source_unit_id IS NULL OR source_unit_id<>unit_id),
  CONSTRAINT chk_unit_link_member_confirmation CHECK (
    (status='pending' AND source_unit_id IS NOT NULL AND source_confirmed_at IS NOT NULL AND target_confirmed_at IS NULL)
    OR (status='active' AND source_confirmed_at IS NOT NULL AND target_confirmed_at IS NOT NULL)
    OR status='removed'
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unit_link_audit (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  group_id BIGINT UNSIGNED NULL,
  member_id BIGINT UNSIGNED NULL,
  source_unit_id BIGINT UNSIGNED NULL,
  target_unit_id BIGINT UNSIGNED NOT NULL,
  source_unit_label VARCHAR(260) NULL,
  target_unit_label VARCHAR(260) NOT NULL,
  action ENUM('created','requested','confirmed','removed') NOT NULL,
  initiated_scope ENUM('platform','province','unit') NOT NULL,
  dual_confirmation_required TINYINT(1) NOT NULL DEFAULT 0,
  actor_id BIGINT UNSIGNED NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_unit_link_audit_group (group_id,occurred_at,id),
  KEY idx_unit_link_audit_units (source_unit_id,target_unit_id,occurred_at),
  CONSTRAINT fk_unit_link_audit_actor FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;