-- Restricted data-governance cases, reviewed disclosures, retention queues and suppression records.
CREATE TABLE data_governance_cases (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 request_type ENUM('access','correction','consent_withdrawal','restriction','erasure') NOT NULL,
 request_received_at DATETIME NOT NULL,
 requester_name VARCHAR(200) NOT NULL,
 requester_contact VARCHAR(254) NULL,
 identity_status ENUM('unverified','verified','failed') NOT NULL DEFAULT 'unverified',
 identity_verified_at DATETIME NULL,
 identity_verified_by BIGINT UNSIGNED NULL,
 identity_method VARCHAR(80) NULL,
 scope_summary VARCHAR(500) NOT NULL,
 responsible_officer_id BIGINT UNSIGNED NULL,
 deadline_at DATETIME NOT NULL,
 status ENUM('received','identity_check','review','restricted','decided','completed','rejected') NOT NULL DEFAULT 'received',
 decision_code ENUM('pending','approved','partially_approved','refused','retained_obligation','withdrawal_recorded') NOT NULL DEFAULT 'pending',
 decision_summary TEXT NULL,
 completed_at DATETIME NULL,
 completed_by BIGINT UNSIGNED NULL,
 created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_governance_case_queue(unit_id,status,deadline_at),
 KEY idx_governance_handler(responsible_officer_id,status),
 CONSTRAINT fk_governance_case_unit FOREIGN KEY(unit_id) REFERENCES units(id),
 CONSTRAINT fk_governance_case_verifier FOREIGN KEY(identity_verified_by) REFERENCES users(id),
 CONSTRAINT fk_governance_case_handler FOREIGN KEY(responsible_officer_id) REFERENCES users(id),
 CONSTRAINT fk_governance_case_completer FOREIGN KEY(completed_by) REFERENCES users(id),
 CONSTRAINT fk_governance_case_creator FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE data_governance_case_people (
 case_id BIGINT UNSIGNED NOT NULL,
 person_id BIGINT UNSIGNED NOT NULL,
 link_status ENUM('possible','verified','excluded') NOT NULL DEFAULT 'possible',
 reviewed_by BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 PRIMARY KEY(case_id,person_id),
 CONSTRAINT fk_governance_person_case FOREIGN KEY(case_id) REFERENCES data_governance_cases(id) ON DELETE CASCADE,
 CONSTRAINT fk_governance_person_person FOREIGN KEY(person_id) REFERENCES people(id),
 CONSTRAINT fk_governance_person_reviewer FOREIGN KEY(reviewed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE data_governance_case_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 case_id BIGINT UNSIGNED NOT NULL,
 actor_id BIGINT UNSIGNED NOT NULL,
 event_key VARCHAR(80) NOT NULL,
 restricted_note TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_governance_event(case_id,created_at),
 CONSTRAINT fk_governance_event_case FOREIGN KEY(case_id) REFERENCES data_governance_cases(id) ON DELETE CASCADE,
 CONSTRAINT fk_governance_event_actor FOREIGN KEY(actor_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE data_governance_source_reviews (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 case_id BIGINT UNSIGNED NOT NULL,
 data_class ENUM('person','membership','candidate','contact','booking','guest','message','document','welfare') NOT NULL,
 source_id BIGINT UNSIGNED NOT NULL,
 subject_person_id BIGINT UNSIGNED NULL,
 review_status ENUM('pending','include','exclude','redact') NOT NULL DEFAULT 'pending',
 third_party_status ENUM('not_applicable','pending','cleared','redacted','excluded') NOT NULL DEFAULT 'pending',
 restricted_note TEXT NULL,
 reviewed_by BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_governance_source(case_id,data_class,source_id),
 KEY idx_governance_source_review(case_id,review_status,third_party_status),
 CONSTRAINT fk_governance_source_case FOREIGN KEY(case_id) REFERENCES data_governance_cases(id) ON DELETE CASCADE,
 CONSTRAINT fk_governance_source_person FOREIGN KEY(subject_person_id) REFERENCES people(id),
 CONSTRAINT fk_governance_source_reviewer FOREIGN KEY(reviewed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE data_governance_exports (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 case_id BIGINT UNSIGNED NOT NULL,
 token_hash CHAR(64) NOT NULL,
 payload_cipher LONGTEXT NOT NULL,
 expires_at DATETIME NOT NULL,
 created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 downloaded_at DATETIME NULL,
 downloaded_by BIGINT UNSIGNED NULL,
 revoked_at DATETIME NULL,
 UNIQUE KEY uq_governance_export_token(token_hash),
 KEY idx_governance_export_case(case_id,expires_at),
 CONSTRAINT fk_governance_export_case FOREIGN KEY(case_id) REFERENCES data_governance_cases(id) ON DELETE CASCADE,
 CONSTRAINT fk_governance_export_creator FOREIGN KEY(created_by) REFERENCES users(id),
 CONSTRAINT fk_governance_export_downloader FOREIGN KEY(downloaded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tenant_retention_schedules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 data_class ENUM('memberships','candidates','guest_directory','bookings','guests','messages','documents','welfare','finance') NOT NULL,
 retention_months SMALLINT UNSIGNED NOT NULL,
 due_action ENUM('review','anonymise','delete') NOT NULL DEFAULT 'review',
 obligation_type ENUM('consent','operational','legal','accounting','safeguarding') NOT NULL DEFAULT 'operational',
 legal_basis VARCHAR(255) NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 updated_by BIGINT UNSIGNED NOT NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_retention_schedule(unit_id,data_class),
 CONSTRAINT fk_retention_schedule_unit FOREIGN KEY(unit_id) REFERENCES units(id),
 CONSTRAINT fk_retention_schedule_actor FOREIGN KEY(updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tenant_retention_queue (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 schedule_id BIGINT UNSIGNED NOT NULL,
 data_class VARCHAR(40) NOT NULL,
 source_id BIGINT UNSIGNED NOT NULL,
 due_at DATETIME NOT NULL,
 status ENUM('pending','retain','approved','completed','skipped','failed') NOT NULL DEFAULT 'pending',
 proposed_action ENUM('review','anonymise','delete') NOT NULL,
 decision_reason VARCHAR(500) NULL,
 reviewed_by BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 completed_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_retention_queue(schedule_id,data_class,source_id),
 KEY idx_retention_queue(unit_id,status,due_at),
 CONSTRAINT fk_retention_queue_unit FOREIGN KEY(unit_id) REFERENCES units(id),
 CONSTRAINT fk_retention_queue_schedule FOREIGN KEY(schedule_id) REFERENCES tenant_retention_schedules(id),
 CONSTRAINT fk_retention_queue_reviewer FOREIGN KEY(reviewed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE communication_suppressions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 contact_hash CHAR(64) NOT NULL,
 channel ENUM('email','phone','post','all') NOT NULL DEFAULT 'email',
 reason ENUM('objection','consent_withdrawal','erasure') NOT NULL,
 source_case_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_communication_suppression(unit_id,contact_hash,channel),
 CONSTRAINT fk_suppression_unit FOREIGN KEY(unit_id) REFERENCES units(id),
 CONSTRAINT fk_suppression_case FOREIGN KEY(source_case_id) REFERENCES data_governance_cases(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO migration_history(version,name,checksum,execution_ms) VALUES('202609270026','Restricted data governance cases exports retention and suppression',SHA2('202609270026_data_governance_v1',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);