ALTER TABLE `student_class` ADD UNIQUE KEY `uq_student_semester_year` (`student_id`, `semester`, `school_year`); ALTER TABLE `enrollments` ADD INDEX `idx_enrollment_lookup` (`student_id`, `semester`, `school_year`); -- 1) Add generated normalized columns (stored so they can be indexed) ALTER TABLE students ADD COLUMN firstname_norm VARCHAR(100) GENERATED ALWAYS AS (LOWER(TRIM(firstname))) STORED, ADD COLUMN lastname_norm VARCHAR(100) GENERATED ALWAYS AS (LOWER(TRIM(lastname))) STORED; -- 2) Prevent duplicates for the same student, same DOB & school year -- 2) (Optional but recommended) If you have a multi-school setup, include school identifier -- Otherwise, skip school_id in the index. CREATE UNIQUE INDEX ux_students_unique_per_year_global ON students (school_year, dob, firstname_norm, lastname_norm); -- Or, if you have multiple schools: -- CREATE UNIQUE INDEX ux_students_unique_per_year_global -- ON students (school_id, school_year, dob, firstname_norm, lastname_norm); CREATE TABLE IF NOT EXISTS email_templates ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, template_key VARCHAR(64) NOT NULL UNIQUE, -- 'attendance_notice', 'grade_update', 'behavior_note' name VARCHAR(100) NOT NULL, subject VARCHAR(255) NOT NULL, body MEDIUMTEXT NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS families ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, family_code VARCHAR(32) UNIQUE, household_name VARCHAR(120) NULL, address_line1 VARCHAR(150) NULL, address_line2 VARCHAR(150) NULL, city VARCHAR(80) NULL, state VARCHAR(40) NULL, postal_code VARCHAR(20) NULL, country VARCHAR(2) NULL, primary_phone VARCHAR(40) NULL, preferred_lang VARCHAR(10) DEFAULT 'en', preferred_contact_method ENUM('email','sms','phone') DEFAULT 'email', is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS family_students ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, family_id INT UNSIGNED NOT NULL, student_id INT UNSIGNED NOT NULL, is_primary_home TINYINT(1) DEFAULT 1, notes VARCHAR(255) NULL, UNIQUE KEY uq_family_student (family_id, student_id), KEY idx_fs_student (student_id), CONSTRAINT fk_fs_family FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE CASCADE, CONSTRAINT fk_fs_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS family_comm_prefs ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, family_id INT UNSIGNED NOT NULL, category ENUM('attendance','grade','behavior','general') NOT NULL, via_email TINYINT(1) DEFAULT 1, via_sms TINYINT(1) DEFAULT 0, cc_all_guardians TINYINT(1) DEFAULT 1, UNIQUE KEY uq_family_category (family_id, category), CONSTRAINT fk_fcp_family FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS communication_logs ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_id INT UNSIGNED NOT NULL, family_id INT UNSIGNED NULL, student_name VARCHAR(150) NOT NULL, template_key VARCHAR(64) NOT NULL, subject VARCHAR(255) NOT NULL, body MEDIUMTEXT NOT NULL, recipients TEXT NOT NULL, cc TEXT NULL, bcc TEXT NULL, attachments TEXT NULL, status ENUM('sent','failed') NOT NULL, error_message TEXT NULL, sent_by INT UNSIGNED NULL, metadata JSON NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_comm_student (student_id), KEY idx_comm_family (family_id), KEY idx_comm_template (template_key), CONSTRAINT fk_comm_family FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT IGNORE INTO email_templates (template_key, name, subject, body) VALUES ('attendance_notice', 'Attendance Notice', 'Attendance Update for {{student_fullname}}', 'Dear {{parent_salutation}},\n\nThis is an update regarding {{student_fullname}} ({{student_grade}}). {{attendance_message}}\n\nDate: {{date}}\nClass/Section: {{class_section}}\n\nIf you have any questions, please reply to this email.\n\nRegards,\n{{school_name}}'), ('grade_update', 'Grade Update', 'Grade Update for {{student_fullname}}', 'Dear {{parent_salutation}},\n\nWe would like to share a grade update for {{student_fullname}} ({{student_grade}}).\n\nAssessment: {{assessment_name}}\nScore: {{score_obtained}} / {{score_total}}\nComments: {{teacher_comments}}\n\nPlease reach out if you need more details.\n\nRegards,\n{{teacher_name}} — {{school_name}}'), ('behavior_note', 'Behavior Note', 'Behavior Note for {{student_fullname}}', 'Dear {{parent_salutation}},\n\nWe want to inform you about {{student_fullname}}\'s behavior on {{date}}.\n\nSummary: {{behavior_summary}}\nActions Taken: {{actions_taken}}\nNext Steps: {{next_steps}}\n\nThank you for your partnership.\n\nRegards,\n{{teacher_name}} — {{school_name}}'); CREATE TABLE `family_guardians` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `family_id` INT UNSIGNED NOT NULL, `user_id` INT UNSIGNED NOT NULL, `relation` VARCHAR(32) DEFAULT NULL, `is_primary` TINYINT(1) DEFAULT 0, `receive_emails` TINYINT(1) DEFAULT 1, `receive_sms` TINYINT(1) DEFAULT 0, `custody_notes` VARCHAR(255) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uq_family_user` (`family_id`,`user_id`), KEY `idx_fg_family` (`family_id`), KEY `idx_fg_user` (`user_id`), CONSTRAINT `fk_fg_family` FOREIGN KEY (`family_id`) REFERENCES `school`.`families` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_family_guardians_user_id` FOREIGN KEY (`user_id`) REFERENCES `school`.`users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE roles ADD COLUMN slug VARCHAR(64) DEFAULT NULL AFTER name, ADD COLUMN dashboard_route VARCHAR(255) NOT NULL DEFAULT '/landing_page/guest_dashboard' AFTER description, ADD COLUMN priority INT(10) UNSIGNED NOT NULL DEFAULT 100 AFTER dashboard_route, ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1 AFTER priority;