CREATE TABLE IF NOT EXISTS notice_publications (
 notification_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
 audience VARCHAR(16) NOT NULL,
 priority VARCHAR(12) NOT NULL DEFAULT 'normal',
 software_name VARCHAR(160) NULL,
 software_version VARCHAR(80) NULL,
 FOREIGN KEY(notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
 CONSTRAINT chk_notice_audience CHECK(audience IN ('global','campuses')),
 CONSTRAINT chk_notice_priority CHECK(priority IN ('normal','important'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS notice_campuses (
 notification_id BIGINT UNSIGNED NOT NULL,
 campus_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(notification_id,campus_id),
 KEY idx_notice_campus(campus_id,notification_id),
 FOREIGN KEY(notification_id) REFERENCES notice_publications(notification_id) ON DELETE CASCADE,
 FOREIGN KEY(campus_id) REFERENCES campuses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS notification_reads (
 notification_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 read_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(notification_id,user_id),
 KEY idx_notice_reader(user_id,notification_id),
 FOREIGN KEY(notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Preserve known personal reads; never copy a broadcast's shared read flag to all students.
INSERT IGNORE INTO notification_reads(notification_id,user_id,read_at)
 SELECT id,recipient_user_id,read_at FROM notifications WHERE read_at IS NOT NULL AND recipient_user_id IS NOT NULL;
INSERT IGNORE INTO notification_reads(notification_id,user_id,read_at)
 SELECT n.id,s.user_id,n.read_at FROM notifications n JOIN students s ON s.id=n.student_id WHERE n.read_at IS NOT NULL;
