SET NAMES utf8mb4;
START TRANSACTION;

ALTER TABLE people ADD birth_date DATE NULL AFTER phone, ADD KEY idx_people_birth_date(birth_date);

CREATE TABLE report_catalogue (
 report_key VARCHAR(60) PRIMARY KEY, title VARCHAR(160) NOT NULL, scope_levels SET('unit','province') NOT NULL,
 required_capability VARCHAR(80) NOT NULL, audience VARCHAR(160) NOT NULL, definition_text VARCHAR(1000) NOT NULL,
 source_text VARCHAR(1000) NOT NULL, small_group_threshold TINYINT UNSIGNED NOT NULL DEFAULT 5, active TINYINT(1) NOT NULL DEFAULT 1,
 CHECK(small_group_threshold BETWEEN 3 AND 20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE report_schedules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, scope_key VARCHAR(80) NOT NULL, scope_type ENUM('unit','province') NOT NULL,
 province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL, report_key VARCHAR(60) NOT NULL,
 filters_json VARCHAR(2000) NOT NULL, output_format ENUM('html','csv','pdf') NOT NULL DEFAULT 'html',
 recipient_type ENUM('user','role') NOT NULL, recipient_user_id BIGINT UNSIGNED NULL, recipient_role VARCHAR(60) NULL,
 cadence ENUM('daily','weekly','monthly') NOT NULL, next_run_at DATETIME NOT NULL, expires_hours SMALLINT UNSIGNED NOT NULL DEFAULT 168,
 active TINYINT(1) 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,
 KEY idx_report_schedule_due(active,next_run_at,id), KEY idx_report_schedule_scope(scope_key,report_key),
 FOREIGN KEY(report_key) REFERENCES report_catalogue(report_key), FOREIGN KEY(province_id) REFERENCES provinces(id),
 FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(recipient_user_id) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id),
 CHECK((scope_type='unit' AND unit_id IS NOT NULL) OR (scope_type='province' AND unit_id IS NULL)),
 CHECK((recipient_type='user' AND recipient_user_id IS NOT NULL AND recipient_role IS NULL) OR (recipient_type='role' AND recipient_user_id IS NULL AND recipient_role IS NOT NULL)),
 CHECK(expires_hours BETWEEN 1 AND 720)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE report_runs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, schedule_id BIGINT UNSIGNED NULL, scope_key VARCHAR(80) NOT NULL,
 scope_type ENUM('unit','province') NOT NULL, province_id BIGINT UNSIGNED NOT NULL, unit_id BIGINT UNSIGNED NULL,
 report_key VARCHAR(60) NOT NULL, filters_json VARCHAR(2000) NOT NULL, definition_snapshot VARCHAR(1000) NOT NULL,
 source_snapshot VARCHAR(1000) NOT NULL, as_of DATETIME NOT NULL, output_format ENUM('html','csv','pdf') NOT NULL,
 payload_cipher LONGTEXT NOT NULL, row_count INT UNSIGNED NOT NULL DEFAULT 0, status ENUM('ready','expired','revoked') NOT NULL DEFAULT 'ready',
 idempotency_key CHAR(64) NOT NULL, expires_at DATETIME NOT NULL, generated_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_report_run_idempotency(idempotency_key), KEY idx_report_run_scope(scope_key,created_at), KEY idx_report_run_expiry(status,expires_at),
 FOREIGN KEY(schedule_id) REFERENCES report_schedules(id) ON DELETE SET NULL, FOREIGN KEY(report_key) REFERENCES report_catalogue(report_key),
 FOREIGN KEY(province_id) REFERENCES provinces(id), FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(generated_by) REFERENCES users(id),
 CHECK((scope_type='unit' AND unit_id IS NOT NULL) OR (scope_type='province' AND unit_id IS NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE report_run_recipients (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, report_run_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL,
 required_capability VARCHAR(80) NOT NULL, notified_at DATETIME NULL, opened_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_report_recipient(report_run_id,user_id), KEY idx_report_inbox(user_id,created_at),
 FOREIGN KEY(report_run_id) REFERENCES report_runs(id) ON DELETE CASCADE, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE report_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, report_run_id BIGINT UNSIGNED NOT NULL, actor_id BIGINT UNSIGNED NOT NULL,
 event_key VARCHAR(60) NOT NULL, occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_report_event(report_run_id,occurred_at), FOREIGN KEY(report_run_id) REFERENCES report_runs(id) ON DELETE CASCADE,
 FOREIGN KEY(actor_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO report_catalogue(report_key,title,scope_levels,required_capability,audience,definition_text,source_text,small_group_threshold) VALUES
('age_bands','Age bands','unit,province','reports.membership','Secretary and authorised Province membership officers','Current unit memberships grouped by age at the as-of date; missing birth dates are Unknown.','people.birth_date and unit_members; membership state is recorded explicitly.',5),
('membership_trends','Membership trends and movements','unit,province','reports.membership','Secretary and authorised Province membership officers','Starts and ends in the selected period, plus current membership and verified-person totals.','unit_members dates and verified people identities.',5),
('member_status','Active and inactive membership','unit,province','reports.membership','Secretary and authorised Province membership officers','Counts the explicitly recorded active, inactive, resigned and deceased states. Attendance never changes these states.','unit_members.state.',5),
('membership_type','Membership type','unit,province','reports.membership','Secretary and authorised Province membership officers','Current unit memberships grouped by recorded membership type.','unit_members.membership_type.',5),
('admission_route','Admitted and joining members','unit,province','reports.ceremonies','Secretary and authorised Province ceremonial officers','Unit memberships grouped by the explicitly recorded admitted or joining route.','unit_members.admission_route.',5),
('ceremony_progression','Order ceremony progression','unit,province','reports.ceremonies','Secretary and authorised Province ceremonial officers','Recorded ceremony milestones grouped by Order and configured terminology.','member_milestones joined to unit_members.',5),
('office_succession','Office and succession','unit,province','reports.ceremonies','Secretary and authorised Province ceremonial officers','Recorded office terms overlapping the selected period; no future succession is inferred.','member_offices joined to unit_members.',5),
('birthdays_anniversaries','Birthdays and Masonic anniversaries','unit,province','reports.membership','Secretary and authorised Province membership officers','Known birthdays and recorded membership start anniversaries in the selected month. Province output is aggregate only.','people.birth_date and unit_members.start_date.',5),
('attendance','Attendance, apologies, visitors and no-shows','unit,province','reports.attendance','Secretary and authorised Province operational officers','Booking and kiosk outcomes by event. A no-show is a non-apology active booking without check-in after the event; it does not explain membership status.','meetings, bookings and guests kiosk fields.',5),
('event_capacity','Event and dining capacity','unit,province','reports.dining','Dining Steward and authorised Province operational officers','Active booking and dining-place counts compared with recorded event capacity.','meetings, bookings and guests.',5),
('communications_delivery','Communications delivery','unit,province','reports.communications','Secretary and authorised Province communications officers','Queued, sent and failed delivery counts in the selected period; message content and recipients are excluded.','communication_log status and timestamps.',5);

INSERT INTO migration_history(version,name,checksum,execution_ms) VALUES('202609280042','Scoped report catalogue inbox schedules and privacy-safe aggregates',SHA2('202609280042_report_catalogue_v1',256),0);
COMMIT;