-- INNENT SCHOOL360 | Migration 005: Attendance + notification outbox (spec sec 30-31, 63)
CREATE TABLE attendance_records (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  attendance_date DATE NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  stream_id INT UNSIGNED NULL,
  term_id INT UNSIGNED NULL,
  status ENUM('present','absent','late','excused','sick','out') NOT NULL,
  remarks VARCHAR(200) NULL,
  source ENUM('manual','qr','rfid','biometric','mobile','api') NOT NULL DEFAULT 'manual',
  marked_by BIGINT UNSIGNED NULL,
  marked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_att (student_id, attendance_date),            -- one daily record per learner
  KEY idx_att_class_date (school_id, class_id, attendance_date),
  KEY idx_att_date_status (school_id, attendance_date, status),
  CONSTRAINT fk_att_student FOREIGN KEY (school_id, student_id) REFERENCES students(school_id, id),
  CONSTRAINT fk_att_class FOREIGN KEY (school_id, class_id) REFERENCES classes(school_id, id),
  CONSTRAINT fk_att_stream FOREIGN KEY (school_id, stream_id) REFERENCES streams(school_id, id),
  CONSTRAINT fk_att_term FOREIGN KEY (school_id, term_id) REFERENCES terms(school_id, id)
) ENGINE=InnoDB;

-- Central outbox: modules enqueue; a cron worker + SMS/email providers deliver (later phase).
CREATE TABLE notification_outbox (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  school_id INT UNSIGNED NOT NULL,
  event VARCHAR(40) NOT NULL,                  -- student_absent, fee_reminder ...
  channel ENUM('sms','email','portal','push') NOT NULL DEFAULT 'sms',
  parent_id BIGINT UNSIGNED NULL,
  student_id BIGINT UNSIGNED NULL,
  recipient VARCHAR(190) NOT NULL,
  message TEXT NOT NULL,
  dedupe_key VARCHAR(120) NOT NULL,            -- makes enqueue idempotent
  status ENUM('pending','sent','failed','cancelled') NOT NULL DEFAULT 'pending',
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  UNIQUE KEY uq_outbox_dedupe (school_id, dedupe_key),
  KEY idx_outbox_status (status, created_at),
  CONSTRAINT fk_outbox_school FOREIGN KEY (school_id) REFERENCES schools(id)
) ENGINE=InnoDB;

INSERT IGNORE INTO permissions (code, module, is_sensitive) VALUES ('attendance.mark_all','attendance',0);
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.code='attendance.mark_all'
WHERE r.code IN ('school_admin','principal');