-- Unit and Province lifecycle controls. Existing records remain active and no historical data is deleted.
ALTER TABLE units
  ADD COLUMN lifecycle_state ENUM('active','transition','suspended','closed','archived','restored') NOT NULL DEFAULT 'active' AFTER active,
  ADD COLUMN lifecycle_state_at DATETIME NULL AFTER lifecycle_state,
  ADD COLUMN lifecycle_reason VARCHAR(500) NULL AFTER lifecycle_state_at,
  ADD KEY idx_units_lifecycle(lifecycle_state,province_id,active);
ALTER TABLE provinces
  ADD COLUMN lifecycle_state ENUM('active','transition','suspended','closed','archived','restored') NOT NULL DEFAULT 'active' AFTER active,
  ADD COLUMN lifecycle_state_at DATETIME NULL AFTER lifecycle_state,
  ADD COLUMN lifecycle_reason VARCHAR(500) NULL AFTER lifecycle_state_at,
  ADD KEY idx_provinces_lifecycle(lifecycle_state,active);
UPDATE units SET lifecycle_state=CASE WHEN deleted_at IS NOT NULL THEN 'archived' WHEN active=0 THEN 'suspended' ELSE 'active' END,lifecycle_state_at=COALESCE(lifecycle_state_at,NOW()) WHERE lifecycle_state='active' AND (active=0 OR deleted_at IS NOT NULL);
UPDATE provinces SET lifecycle_state=CASE WHEN active=0 THEN 'suspended' ELSE 'active' END,lifecycle_state_at=COALESCE(lifecycle_state_at,NOW()) WHERE lifecycle_state='active' AND active=0;
CREATE TABLE IF NOT EXISTS lifecycle_transitions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  scope_type ENUM('province','unit') NOT NULL,
  province_id BIGINT UNSIGNED NOT NULL,
  unit_id BIGINT UNSIGNED NULL,
  from_state VARCHAR(20) NOT NULL,
  to_state VARCHAR(20) NOT NULL,
  reason VARCHAR(500) NOT NULL,
  approval_note VARCHAR(500) NULL,
  approved_by BIGINT UNSIGNED NULL,
  initiated_by BIGINT UNSIGNED NOT NULL,
  restored_from_transition_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_lifecycle_scope(scope_type,province_id,unit_id,created_at),
  KEY idx_lifecycle_state(to_state,created_at),
  CONSTRAINT fk_lifecycle_transition_province FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE RESTRICT,
  CONSTRAINT fk_lifecycle_transition_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON DELETE RESTRICT,
  CONSTRAINT fk_lifecycle_transition_actor FOREIGN KEY (initiated_by) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT fk_lifecycle_transition_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS lifecycle_handover_cases (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  transition_id BIGINT UNSIGNED NOT NULL,
  scope_type ENUM('province','unit') NOT NULL,
  province_id BIGINT UNSIGNED NOT NULL,
  unit_id BIGINT UNSIGNED NULL,
  status ENUM('open','ready','completed','cancelled') NOT NULL DEFAULT 'open',
  outgoing_user_id BIGINT UNSIGNED NULL,
  incoming_user_id BIGINT UNSIGNED NULL,
  review_due_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_lifecycle_handover_transition(transition_id),
  KEY idx_lifecycle_handover_scope(scope_type,province_id,unit_id,status),
  CONSTRAINT fk_lifecycle_handover_transition FOREIGN KEY (transition_id) REFERENCES lifecycle_transitions(id) ON DELETE RESTRICT,
  CONSTRAINT fk_lifecycle_handover_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS lifecycle_handover_tasks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  handover_id BIGINT UNSIGNED NOT NULL,
  task_key VARCHAR(60) NOT NULL,
  status ENUM('open','completed','waived') NOT NULL DEFAULT 'open',
  note VARCHAR(1000) NULL,
  completed_by BIGINT UNSIGNED NULL,
  completed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_lifecycle_handover_task(handover_id,task_key),
  CONSTRAINT fk_lifecycle_task_handover FOREIGN KEY (handover_id) REFERENCES lifecycle_handover_cases(id) ON DELETE RESTRICT,
  CONSTRAINT fk_lifecycle_task_actor FOREIGN KEY (completed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS lifecycle_delegated_cover (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  handover_id BIGINT UNSIGNED NOT NULL,
  grant_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  permission_set_id BIGINT UNSIGNED NOT NULL,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  revoked_by BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_lifecycle_cover_active(user_id,expires_at,revoked_at),
  CONSTRAINT fk_lifecycle_cover_handover FOREIGN KEY (handover_id) REFERENCES lifecycle_handover_cases(id) ON DELETE RESTRICT,
  CONSTRAINT fk_lifecycle_cover_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
  CONSTRAINT fk_lifecycle_cover_set FOREIGN KEY (permission_set_id) REFERENCES permission_sets(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;