-- Per-unit delivery preferences and snapshot-derived postal dispatch queue.
CREATE TABLE unit_member_delivery_preferences (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL, person_id BIGINT UNSIGNED NOT NULL,
 essential_delivery ENUM('email','post','both','none') NOT NULL DEFAULT 'email', optional_delivery ENUM('email','post','both','none') NOT NULL DEFAULT 'none',
 postal_address_cipher MEDIUMTEXT NULL, postal_address_source ENUM('member_provided','officer_verified','imported','historic_record') NULL,
 postal_verified_at DATETIME NULL, postal_verified_by BIGINT UNSIGNED NULL, updated_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,
 UNIQUE KEY uq_unit_member_delivery(unit_id,person_id), KEY idx_delivery_person(person_id,unit_id),
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE, FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE CASCADE,
 FOREIGN KEY(postal_verified_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(updated_by) REFERENCES users(id),
 CONSTRAINT chk_postal_verification CHECK((postal_address_cipher IS NULL AND postal_address_source IS NULL AND postal_verified_at IS NULL) OR (postal_address_cipher IS NOT NULL AND postal_address_source IS NOT NULL AND postal_verified_at IS NOT NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE meeting_publication_recipients
 DROP INDEX uq_publication_recipient,
 MODIFY recipient_email VARCHAR(254) NULL,
 ADD COLUMN notice_class ENUM('essential','optional') NOT NULL DEFAULT 'essential' AFTER audience_source,
 ADD COLUMN delivery_method ENUM('email','post','both','none') NOT NULL DEFAULT 'none' AFTER notice_class,
 ADD COLUMN email_eligible TINYINT(1) NOT NULL DEFAULT 0 AFTER delivery_method,
 ADD COLUMN postal_eligible TINYINT(1) NOT NULL DEFAULT 0 AFTER email_eligible,
 ADD COLUMN postal_address_cipher MEDIUMTEXT NULL AFTER postal_eligible,
 ADD COLUMN postal_address_source VARCHAR(40) NULL AFTER postal_address_cipher,
 ADD COLUMN postal_verified_at DATETIME NULL AFTER postal_address_source,
 ADD COLUMN delivery_issue ENUM('missing_email','missing_address','preference_none') NULL AFTER postal_verified_at,
 ADD UNIQUE KEY uq_publication_person(snapshot_id,person_id);

CREATE TABLE postal_dispatches (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, recipient_id BIGINT UNSIGNED NOT NULL, snapshot_id BIGINT UNSIGNED NOT NULL,
 status ENUM('ready','exported','posted','returned','undeliverable','cancelled') NOT NULL DEFAULT 'ready',
 exported_at DATETIME NULL, posted_at DATETIME NULL, returned_at DATETIME NULL, status_note VARCHAR(500) NULL,
 updated_by BIGINT UNSIGNED NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_postal_dispatch_recipient(recipient_id), KEY idx_postal_queue(snapshot_id,status),
 FOREIGN KEY(recipient_id) REFERENCES meeting_publication_recipients(id) ON DELETE CASCADE, FOREIGN KEY(snapshot_id) REFERENCES meeting_publication_snapshots(id) ON DELETE CASCADE,
 FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE postal_address_exports (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL, snapshot_id BIGINT UNSIGNED NOT NULL,
 token_hash CHAR(64) NOT NULL, export_kind ENUM('labels','letters') NOT NULL, payload_cipher LONGTEXT NOT NULL,
 expires_at DATETIME NOT NULL, downloaded_at DATETIME NULL, revoked_at DATETIME NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_postal_export_token(token_hash), KEY idx_postal_export(unit_id,snapshot_id,expires_at),
 FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE, FOREIGN KEY(snapshot_id) REFERENCES meeting_publication_snapshots(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE postal_dispatch_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, dispatch_id BIGINT UNSIGNED NOT NULL, from_status VARCHAR(30) NULL, to_status VARCHAR(30) NOT NULL,
 note VARCHAR(500) NULL, actor_user_id BIGINT UNSIGNED NOT NULL, occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_postal_history(dispatch_id,occurred_at,id), FOREIGN KEY(dispatch_id) REFERENCES postal_dispatches(id) ON DELETE CASCADE, FOREIGN KEY(actor_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TRIGGER postal_dispatch_history_no_update BEFORE UPDATE ON postal_dispatch_history FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Postal history is append-only';
CREATE TRIGGER postal_dispatch_history_no_delete BEFORE DELETE ON postal_dispatch_history FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Postal history is append-only';

INSERT INTO migration_history(version,name,checksum,execution_ms) VALUES('202609280033','Per-unit postal delivery and print queue',SHA2('202609280033_postal_delivery_queue_v1',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);