alrahma_sunday_school/tables_update.txt

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;