-- Roles
INSERT IGNORE INTO roles (name, role_type, is_system_role) VALUES
('super_admin','staff',1),
('head_teacher','staff',0),
('class_teacher','staff',0),
('subject_teacher','staff',0),
('accountant','staff',0),
('admin_staff','staff',0),
('student','student',1),
('guardian','guardian',1);

-- Permissions: module.action naming, kept granular on purpose so scope
-- checks in the base repository stay simple (permission = capability,
-- not "which records" — that's the ownership/scope layer).
INSERT IGNORE INTO permissions (code, module, description) VALUES
-- students
('students.view','students','View student records'),
('students.create','students','Register new students'),
('students.update','students','Edit student records'),
('students.withdraw','students','Withdraw/deactivate a student'),
-- staff
('staff.view','staff','View staff records'),
('staff.create','staff','Onboard new staff'),
('staff.update','staff','Edit staff records'),
-- results
('results.enter','results','Enter subject scores'),
('results.view_own_class','results','View results for own assigned classes'),
('results.view_all','results','View results across all classes'),
('results.approve','results','Approve submitted results'),
('results.publish','results','Publish approved results to students/guardians'),
-- fees
('fees.view','fees','View fee structures and balances'),
('fees.record_payment','fees','Record a fee payment'),
('fees.manage_structure','fees','Create/edit fee structures'),
-- exams (CBT)
('exams.create','exams','Create CBT exams and questions'),
('exams.publish','exams','Schedule/publish an exam'),
('exams.view_results','exams','View CBT exam results'),
('exams.take','exams','Attempt an assigned exam'),
-- attendance
('attendance.record','attendance','Mark daily attendance'),
('attendance.view','attendance','View attendance records'),
-- settings & admin
('settings.manage','settings','Manage school-wide settings'),
('roles.manage','roles','Manage roles and permissions'),
('activity_logs.view','activity_logs','View system activity logs');

-- Role-permission mapping
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON TRUE
WHERE
  (r.name = 'super_admin') -- gets everything
  OR (r.name = 'head_teacher' AND p.code IN (
      'students.view','students.create','students.update','students.withdraw',
      'staff.view','results.view_all','results.approve','results.publish',
      'fees.view','attendance.view','activity_logs.view'))
  OR (r.name = 'class_teacher' AND p.code IN (
      'students.view','results.enter','results.view_own_class',
      'attendance.record','attendance.view'))
  OR (r.name = 'subject_teacher' AND p.code IN (
      'students.view','results.enter','results.view_own_class'))
  OR (r.name = 'accountant' AND p.code IN (
      'fees.view','fees.record_payment','fees.manage_structure','students.view'))
  OR (r.name = 'admin_staff' AND p.code IN (
      'students.view','students.create','students.update','staff.view'))
  OR (r.name = 'student' AND p.code IN (
      'exams.take'))
  OR (r.name = 'guardian' AND p.code IN (
      'fees.view'));