-- Release 078: provider-staged people import provenance and review controls.
ALTER TABLE people_import_batches
  ADD COLUMN mapping_version VARCHAR(80) NULL AFTER mapping_sha256,
  ADD COLUMN provider_received_at DATETIME NULL AFTER mapping_version,
  ADD COLUMN source_cursor VARCHAR(191) NULL AFTER provider_received_at,
  ADD COLUMN approved_by BIGINT UNSIGNED NULL AFTER confirmed_at,
  ADD COLUMN approved_at DATETIME NULL AFTER approved_by,
  ADD KEY idx_people_import_provider(source_system,provider_received_at),
  ADD CONSTRAINT fk_people_import_approved_by FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL;

ALTER TABLE people_import_rows
  ADD COLUMN source_external_id VARCHAR(191) NULL AFTER source_unit_slug,
  ADD COLUMN source_timestamp DATETIME NULL AFTER source_external_id,
  ADD COLUMN mapping_version VARCHAR(80) NULL AFTER source_timestamp,
  ADD COLUMN validation_result ENUM('valid','invalid','review_required') NOT NULL DEFAULT 'valid' AFTER validation_errors,
  ADD COLUMN duplicate_confidence SMALLINT UNSIGNED NULL AFTER validation_result,
  ADD COLUMN proposed_action ENUM('create','link_existing','create_affiliation','reject','review') NOT NULL DEFAULT 'review' AFTER duplicate_confidence,
  ADD COLUMN reviewer_decision ENUM('pending','use_existing','create_separate','reject') NOT NULL DEFAULT 'pending' AFTER proposed_action,
  ADD COLUMN reviewed_by BIGINT UNSIGNED NULL AFTER reviewer_decision,
  ADD COLUMN reviewed_at DATETIME NULL AFTER reviewed_by,
  ADD KEY idx_people_import_row_external(batch_id,source_external_id),
  ADD KEY idx_people_import_row_review(batch_id,reviewer_decision,validation_result),
  ADD CONSTRAINT fk_people_import_row_reviewer FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS people_import_field_provenance (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  row_id BIGINT UNSIGNED NOT NULL,
  field_name VARCHAR(80) NOT NULL,
  ownership ENUM('source_controlled','locally_controlled','reviewed','never_imported') NOT NULL,
  source_value_sha256 CHAR(64) NULL,
  applied_value_sha256 CHAR(64) NULL,
  action ENUM('proposed','accepted','preserved','rejected') NOT NULL DEFAULT 'proposed',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_people_import_field_provenance(row_id,field_name),
  KEY idx_people_import_provenance_owner(row_id,ownership,action),
  CONSTRAINT fk_people_import_field_row FOREIGN KEY(row_id) REFERENCES people_import_rows(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO migration_history(version,name,checksum,execution_ms)
VALUES('202610020078','Provider people import provenance and review controls',SHA2('202610020078_provider_people_import_v1',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);