-- Staged, tenant-scoped people import. Applying this file is a deployment step;
-- the application never runs it automatically.
ALTER TABLE member_milestones ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE member_ranks ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE member_offices ADD COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TABLE people_import_batches (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  scope_type ENUM('unit','province') NOT NULL,
  province_id BIGINT UNSIGNED NOT NULL,
  unit_id BIGINT UNSIGNED NULL,
  source_name VARCHAR(255) NOT NULL,
  source_sha256 CHAR(64) NOT NULL,
  source_size INT UNSIGNED NOT NULL,
  source_system VARCHAR(100) NOT NULL,
  mapping_json LONGTEXT NOT NULL,
  mapping_sha256 CHAR(64) NOT NULL,
  idempotency_key CHAR(64) NOT NULL,
  status ENUM('preview','committing','committed','committed_with_errors','failed','undone','undo_partial') NOT NULL DEFAULT 'preview',
  dry_run TINYINT(1) NOT NULL DEFAULT 1,
  accepted_count INT UNSIGNED NOT NULL DEFAULT 0,
  rejected_count INT UNSIGNED NOT NULL DEFAULT 0,
  unresolved_count INT UNSIGNED NOT NULL DEFAULT 0,
  applied_count INT UNSIGNED NOT NULL DEFAULT 0,
  failed_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  confirmed_at DATETIME NULL,
  committed_at DATETIME NULL,
  undo_until DATETIME NULL,
  undone_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_people_import_idempotency (idempotency_key),
  KEY idx_people_import_scope (scope_type,province_id,unit_id,status),
  CONSTRAINT fk_people_import_province FOREIGN KEY (province_id) REFERENCES provinces(id),
  CONSTRAINT fk_people_import_unit FOREIGN KEY (unit_id) REFERENCES units(id),
  CONSTRAINT fk_people_import_actor FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE people_import_rows (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  batch_id BIGINT UNSIGNED NOT NULL,
  row_number INT UNSIGNED NOT NULL,
  source_unit_slug VARCHAR(191) NULL,
  unit_id BIGINT UNSIGNED NULL,
  record_type VARCHAR(32) NOT NULL,
  row_fingerprint CHAR(64) NOT NULL,
  raw_json LONGTEXT NOT NULL,
  normalized_json LONGTEXT NOT NULL,
  status ENUM('accepted','rejected','unresolved','applied','failed','undone') NOT NULL,
  validation_errors LONGTEXT NULL,
  resolved_person_id BIGINT UNSIGNED NULL,
  resolution ENUM('new','existing') NULL,
  applied_at DATETIME NULL,
  failure_code VARCHAR(80) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_people_import_row (batch_id,row_number),
  KEY idx_people_import_row_status (batch_id,status,row_number),
  KEY idx_people_import_row_unit (unit_id),
  CONSTRAINT fk_people_import_row_batch FOREIGN KEY (batch_id) REFERENCES people_import_batches(id) ON DELETE CASCADE,
  CONSTRAINT fk_people_import_row_unit FOREIGN KEY (unit_id) REFERENCES units(id),
  CONSTRAINT fk_people_import_row_person FOREIGN KEY (resolved_person_id) REFERENCES people(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE people_import_duplicate_suggestions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  row_id BIGINT UNSIGNED NOT NULL,
  person_id BIGINT UNSIGNED NOT NULL,
  score SMALLINT UNSIGNED NOT NULL,
  signals_json LONGTEXT NOT NULL,
  decision ENUM('pending','use_existing','create_separate','dismissed') NOT NULL DEFAULT 'pending',
  reviewed_by BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_people_import_suggestion (row_id,person_id),
  KEY idx_people_import_suggestion_person (person_id),
  CONSTRAINT fk_people_import_suggestion_row FOREIGN KEY (row_id) REFERENCES people_import_rows(id) ON DELETE CASCADE,
  CONSTRAINT fk_people_import_suggestion_person FOREIGN KEY (person_id) REFERENCES people(id),
  CONSTRAINT fk_people_import_suggestion_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE person_import_keys (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  scope_type ENUM('unit','province') NOT NULL,
  scope_id BIGINT UNSIGNED NOT NULL,
  source_system VARCHAR(100) NOT NULL,
  external_person_id VARCHAR(191) NOT NULL,
  person_id BIGINT UNSIGNED NOT NULL,
  source_row_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_person_import_key (scope_type,scope_id,source_system,external_person_id),
  KEY idx_person_import_key_person (person_id),
  CONSTRAINT fk_person_import_key_person FOREIGN KEY (person_id) REFERENCES people(id),
  CONSTRAINT fk_person_import_key_row FOREIGN KEY (source_row_id) REFERENCES people_import_rows(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE person_attendance_history (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  unit_id BIGINT UNSIGNED NOT NULL,
  person_id BIGINT UNSIGNED NOT NULL,
  event_date DATE NOT NULL,
  event_title VARCHAR(255) NOT NULL,
  attendance_status ENUM('attended','apology','absent','unknown') NOT NULL DEFAULT 'unknown',
  source_row_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_person_attendance_import (source_row_id),
  KEY idx_person_attendance_unit_date (unit_id,event_date),
  KEY idx_person_attendance_person (person_id),
  CONSTRAINT fk_person_attendance_unit FOREIGN KEY (unit_id) REFERENCES units(id),
  CONSTRAINT fk_person_attendance_person FOREIGN KEY (person_id) REFERENCES people(id),
  CONSTRAINT fk_person_attendance_row FOREIGN KEY (source_row_id) REFERENCES people_import_rows(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE people_import_actions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  row_id BIGINT UNSIGNED NOT NULL,
  entity_table VARCHAR(64) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,
  expected_updated_at DATETIME NULL,
  undone_at DATETIME NULL,
  undo_note VARCHAR(191) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_people_import_action_row (row_id,id),
  CONSTRAINT fk_people_import_action_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('202609270025','Staged people CSV import and reconciliation',SHA2('202609270025_people_staged_import_v1',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);