-- Structured agendas, immutable summons/minutes publications, sign-off and actions.
CREATE TABLE meeting_agenda_templates (
 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,
 name VARCHAR(160) NOT NULL, order_key VARCHAR(40) NOT NULL DEFAULT 'craft', active TINYINT(1) NOT NULL DEFAULT 1, current_version INT UNSIGNED NOT NULL DEFAULT 1,
 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,
 scope_key VARCHAR(90) GENERATED ALWAYS AS (CONCAT(scope_type,':',province_id,':',IFNULL(unit_id,0))) STORED,
 UNIQUE KEY uq_agenda_template(scope_key,name), FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id),
 CONSTRAINT chk_agenda_template_scope CHECK((scope_type='province' AND unit_id IS NULL) OR (scope_type='unit' AND unit_id IS NOT NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE meeting_agenda_template_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, template_id BIGINT UNSIGNED NOT NULL, version_no INT UNSIGNED NOT NULL, item_no INT UNSIGNED NOT NULL,
 heading VARCHAR(220) NOT NULL, item_type ENUM('opening','minutes','matters','ceremony','motion','report','business','closing','other') NOT NULL DEFAULT 'business',
 responsible_role VARCHAR(120) NULL, resolution_placeholder TEXT NULL, sensitivity ENUM('general','committee','welfare','finance') NOT NULL DEFAULT 'general', created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_agenda_template_item(template_id,version_no,item_no), FOREIGN KEY(template_id) REFERENCES meeting_agenda_templates(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE meeting_agendas (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, meeting_id BIGINT UNSIGNED NOT NULL, scope_type ENUM('province','unit') NOT NULL, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NOT NULL,
 template_id BIGINT UNSIGNED NULL, title VARCHAR(220) NOT NULL, status ENUM('draft','review','approved','superseded','archived') NOT NULL DEFAULT 'draft', version_no INT UNSIGNED NOT NULL DEFAULT 1,
 approved_by BIGINT UNSIGNED NULL, approved_at DATETIME 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,
 UNIQUE KEY uq_meeting_agenda_scope(meeting_id,scope_type,province_id,unit_id), KEY idx_agenda_scope(scope_type,province_id,unit_id,status),
 FOREIGN KEY(meeting_id) REFERENCES meetings(id) ON DELETE CASCADE, FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE CASCADE, FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(template_id) REFERENCES meeting_agenda_templates(id) ON DELETE SET NULL, FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE meeting_agenda_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, agenda_id BIGINT UNSIGNED NOT NULL, logical_key CHAR(32) NOT NULL, version_no INT UNSIGNED NOT NULL, item_no INT UNSIGNED NOT NULL,
 heading VARCHAR(220) NOT NULL, body_text TEXT NULL, proposer_person_id BIGINT UNSIGNED NULL, responsible_role VARCHAR(120) NULL, resolution_text TEXT NULL,
 sensitivity ENUM('general','committee','welfare','finance') NOT NULL DEFAULT 'general', approved_for_general TINYINT(1) NOT NULL DEFAULT 0, item_status ENUM('draft','approved','withdrawn') NOT NULL DEFAULT 'draft',
 created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_agenda_item_version(agenda_id,logical_key,version_no), KEY idx_agenda_items(agenda_id,version_no,item_no),
 FOREIGN KEY(agenda_id) REFERENCES meeting_agendas(id) ON DELETE CASCADE, FOREIGN KEY(proposer_person_id) REFERENCES people(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE meeting_publication_snapshots (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, agenda_id BIGINT UNSIGNED NOT NULL, meeting_id BIGINT UNSIGNED NOT NULL,
 publication_type ENUM('summons','minutes','minutes_correction','minutes_addendum') NOT NULL, publication_no INT UNSIGNED NOT NULL, amendment_no INT UNSIGNED NOT NULL DEFAULT 0,
 parent_snapshot_id BIGINT UNSIGNED NULL, status ENUM('approved','issued','review','confirmed','locked','archived') NOT NULL, title VARCHAR(250) NOT NULL,
 event_fingerprint CHAR(64) NOT NULL, content_json LONGTEXT NOT NULL, document_id BIGINT UNSIGNED NULL, province_document_id BIGINT UNSIGNED NULL, document_version_id BIGINT UNSIGNED NULL, province_document_version_id BIGINT UNSIGNED NULL,
 approved_by BIGINT UNSIGNED NOT NULL, approved_at DATETIME NOT NULL, issued_at DATETIME NULL, confirmed_at DATETIME NULL, confirmed_meeting_id BIGINT UNSIGNED NULL, signed_off_by BIGINT UNSIGNED NULL, signed_off_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_publication_number(agenda_id,publication_type,publication_no,amendment_no), KEY idx_publication_meeting(meeting_id,publication_type,status),
 FOREIGN KEY(agenda_id) REFERENCES meeting_agendas(id) ON DELETE CASCADE, FOREIGN KEY(meeting_id) REFERENCES meetings(id) ON DELETE CASCADE, FOREIGN KEY(parent_snapshot_id) REFERENCES meeting_publication_snapshots(id) ON DELETE RESTRICT,
 FOREIGN KEY(document_id) REFERENCES tenant_documents(id) ON DELETE SET NULL, FOREIGN KEY(province_document_id) REFERENCES province_documents(id) ON DELETE SET NULL, FOREIGN KEY(document_version_id) REFERENCES tenant_document_versions(id) ON DELETE SET NULL, FOREIGN KEY(province_document_version_id) REFERENCES province_document_versions(id) ON DELETE SET NULL,
 FOREIGN KEY(approved_by) REFERENCES users(id), FOREIGN KEY(confirmed_meeting_id) REFERENCES meetings(id) ON DELETE SET NULL, FOREIGN KEY(signed_off_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT chk_publication_document CHECK((document_id IS NULL) OR (province_document_id IS NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE meeting_publication_recipients (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, snapshot_id BIGINT UNSIGNED NOT NULL, person_id BIGINT UNSIGNED NULL, recipient_email VARCHAR(254) NOT NULL, audience_source ENUM('unit_member','province_relationship','booking_attendee') NOT NULL,
 mail_queue_id BIGINT UNSIGNED NULL, queued_at DATETIME NULL, sent_at DATETIME NULL, delivery_status ENUM('pending','queued','sent','failed','suppressed') NOT NULL DEFAULT 'pending',
 UNIQUE KEY uq_publication_recipient(snapshot_id,recipient_email), FOREIGN KEY(snapshot_id) REFERENCES meeting_publication_snapshots(id) ON DELETE CASCADE, FOREIGN KEY(person_id) REFERENCES people(id) ON DELETE SET NULL, FOREIGN KEY(mail_queue_id) REFERENCES outbound_mail_queue(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE meeting_minutes_actions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, snapshot_id BIGINT UNSIGNED NOT NULL, agenda_item_key CHAR(32) NULL, title VARCHAR(250) NOT NULL, responsible_role VARCHAR(80) NOT NULL, due_at DATETIME NULL,
 status ENUM('open','completed','cancelled') NOT NULL DEFAULT 'open', action_task_id BIGINT UNSIGNED NULL, created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, completed_at DATETIME NULL,
 KEY idx_minutes_action(snapshot_id,status,due_at), FOREIGN KEY(snapshot_id) REFERENCES meeting_publication_snapshots(id) ON DELETE CASCADE, FOREIGN KEY(action_task_id) REFERENCES action_centre_tasks(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE action_centre_tasks MODIFY task_kind ENUM('role_message','invitation_request','booking_deadline','waiting_offer','draft_publication','unpaid_reminder','access_review','minutes_action') NOT NULL;
ALTER TABLE outbound_mail_queue ADD COLUMN province_id BIGINT UNSIGNED NULL AFTER unit_id, ADD KEY idx_mail_province(province_id,status), ADD CONSTRAINT fk_mail_province FOREIGN KEY(province_id) REFERENCES provinces(id) ON DELETE SET NULL;

CREATE TRIGGER meeting_publication_no_update_locked BEFORE UPDATE ON meeting_publication_snapshots FOR EACH ROW
 SET NEW.status=IF(OLD.status='locked','locked',NEW.status), NEW.content_json=IF(OLD.status='locked',OLD.content_json,NEW.content_json), NEW.event_fingerprint=IF(OLD.status='locked',OLD.event_fingerprint,NEW.event_fingerprint);
CREATE TRIGGER meeting_publication_no_delete BEFORE DELETE ON meeting_publication_snapshots FOR EACH ROW
 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Publication snapshots are append-only';

INSERT INTO migration_history(version,name,checksum,execution_ms) VALUES('202609280032','Structured agendas, summons and minutes',SHA2('202609280032_agendas_summons_minutes_v2',256),0)
ON DUPLICATE KEY UPDATE name=VALUES(name),checksum=VALUES(checksum);