-- ============================================================
-- SchoolMaster ERP — Seed Data
-- Run AFTER schema.sql
-- ============================================================
USE `olasteve_myschoo`;

-- ── School Settings ───────────────────────────────────────────
INSERT INTO `school_settings` (`school_name`,`address`,`phone`,`email`,`motto`,`state`,`lga`,`school_type`,`currency`,`sms_provider`,`sms_sender_id`)
VALUES ('Excellence Academy','1 School Road, Victoria Island','08012345678','info@excellenceacademy.edu.ng',
        'Knowledge, Character, Excellence','Lagos','Eti-Osa','mixed','NGN','termii','ExcSchool')
ON DUPLICATE KEY UPDATE `school_name` = VALUES(`school_name`);

-- ── Roles ─────────────────────────────────────────────────────
INSERT IGNORE INTO `roles` (`name`,`display_name`,`description`) VALUES
('proprietor',    'Proprietor',          'School owner with full access'),
('principal',     'Principal',           'School principal'),
('vice_principal','Vice Principal',       'Vice principal'),
('bursar',        'Bursar',              'Fee and financial management'),
('accountant',    'Accountant',          'Accounts officer'),
('class_teacher', 'Class Teacher',       'Homeroom / class teacher'),
('subject_teacher','Subject Teacher',    'Subject specialist teacher'),
('student',       'Student',             'Enrolled student'),
('parent',        'Parent / Guardian',   'Parent or guardian'),
('nurse',         'School Nurse',        'Medical officer'),
('librarian',     'Librarian',           'Library officer'),
('driver',        'Driver',              'School bus driver'),
('security',      'Security Officer',    'Security personnel'),
('cleaner',       'Cleaner',             'Cleaning staff'),
('hr_officer',    'HR Officer',          'Human resources officer');

-- ── Permissions ───────────────────────────────────────────────
INSERT IGNORE INTO `permissions` (`name`,`module`,`description`) VALUES
('dashboard.view',          'dashboard',     'View Dashboard'),
('students.view',           'students',      'View Students'),
('students.create',         'students',      'Enrol Students'),
('students.edit',           'students',      'Edit Student Records'),
('students.delete',         'students',      'Delete / Withdraw Students'),
('students.export',         'students',      'Export Student Data'),
('students.generate_id',    'students',      'Generate Student ID Cards'),
('attendance.take',         'attendance',    'Take Attendance'),
('attendance.view',         'attendance',    'View Attendance Reports'),
('attendance.edit',         'attendance',    'Edit Attendance Records'),
('fees.view',               'fees',          'View Fee Records'),
('fees.create',             'fees',          'Create Fee Schedules'),
('fees.collect',            'fees',          'Collect Payments'),
('fees.generate_receipt',   'fees',          'Generate Receipts'),
('fees.waive',              'fees',          'Waive Fees'),
('results.enter',           'examination',   'Enter Results'),
('results.view',            'examination',   'View Results'),
('results.publish',         'examination',   'Publish Results'),
('results.delete',          'examination',   'Delete Results'),
('results.view_own',        'examination',   'View Own Results (Student)'),
('results.view_children',   'examination',   'View Children Results (Parent)'),
('promotion.initiate',      'examination',   'Initiate Promotions'),
('promotion.approve',       'examination',   'Approve Promotions'),
('cbt.create',              'cbt',           'Create CBT Exams'),
('cbt.manage',              'cbt',           'Manage CBT Exams'),
('cbt.take',                'cbt',           'Take CBT Exams'),
('assignments.create',      'assignments',   'Create Assignments'),
('assignments.grade',       'assignments',   'Grade Assignments'),
('assignments.submit',      'assignments',   'Submit Assignments'),
('lms.upload',              'lms',           'Upload Learning Materials'),
('lms.view',                'lms',           'View Learning Materials'),
('communication.send_sms',  'communication', 'Send SMS'),
('communication.send_email','communication', 'Send Emails'),
('communication.message_teacher','communication','Message Teacher (Parent)'),
('library.manage',          'library',       'Manage Library'),
('clinic.manage',           'clinic',        'Manage Clinic Records'),
('transport.manage',        'transport',     'Manage Transport'),
('transport.view',          'transport',     'View Transport Info'),
('discipline.record',       'discipline',    'Record Discipline'),
('staff.manage',            'staff',         'Manage Staff'),
('accounts.view',           'accounts',      'View Accounts'),
('accounts.manage',         'accounts',      'Manage Accounts'),
('settings.manage',         'settings',      'Manage System Settings'),
('reports.view',            'reports',       'View Reports'),
('reports.export',          'reports',       'Export Reports'),
('audit_logs.view',         'system',        'View Audit Logs'),
('fees.view_children',      'fees',          'View Children Fees (Parent)'),
('attendance.view_children','attendance',    'View Children Attendance (Parent)');

-- ── Role → Permission Mapping ──────────────────────────────────
-- Proprietor: all permissions (handled in code as wildcard)
-- Principal
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p
WHERE r.name = 'principal'
  AND p.name IN (
    'dashboard.view','students.view','students.create','students.edit','students.delete',
    'students.export','students.generate_id','attendance.take','attendance.view',
    'attendance.edit','fees.view','fees.create','fees.generate_receipt','fees.waive',
    'results.enter','results.view','results.publish','results.delete',
    'promotion.initiate','promotion.approve','cbt.create','cbt.manage',
    'assignments.create','assignments.grade','lms.upload','lms.view',
    'communication.send_sms','communication.send_email','library.manage',
    'clinic.manage','transport.manage','discipline.record','staff.manage',
    'accounts.view','reports.view','reports.export'
  );

-- Vice Principal
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p
WHERE r.name = 'vice_principal'
  AND p.name IN (
    'dashboard.view','students.view','students.edit','attendance.view',
    'results.view','discipline.record','reports.view'
  );

-- Bursar
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p
WHERE r.name = 'bursar'
  AND p.name IN (
    'dashboard.view','fees.view','fees.create','fees.collect','fees.generate_receipt',
    'fees.waive','accounts.view','accounts.manage','reports.view','reports.export','students.view'
  );

-- Accountant
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p
WHERE r.name = 'accountant'
  AND p.name IN (
    'dashboard.view','fees.view','fees.collect','fees.generate_receipt','accounts.view','reports.view'
  );

-- Class Teacher
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p
WHERE r.name = 'class_teacher'
  AND p.name IN (
    'dashboard.view','students.view','attendance.take','attendance.view',
    'results.enter','results.view','cbt.create','cbt.manage',
    'assignments.create','assignments.grade','lms.upload','lms.view',
    'discipline.record','reports.view','communication.send_email'
  );

-- Subject Teacher
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p
WHERE r.name = 'subject_teacher'
  AND p.name IN (
    'dashboard.view','students.view','attendance.take','results.enter',
    'cbt.create','cbt.manage','assignments.create','assignments.grade',
    'lms.upload','lms.view'
  );

-- Student
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p
WHERE r.name = 'student'
  AND p.name IN ('dashboard.view','lms.view','cbt.take','assignments.submit','results.view_own');

-- Parent
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p
WHERE r.name = 'parent'
  AND p.name IN (
    'dashboard.view','results.view_children','fees.view_children',
    'attendance.view_children','communication.message_teacher'
  );

-- Nurse
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p
WHERE r.name = 'nurse' AND p.name IN ('dashboard.view','clinic.manage');

-- Librarian
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p
WHERE r.name = 'librarian' AND p.name IN ('dashboard.view','library.manage');

-- Driver
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p
WHERE r.name = 'driver' AND p.name IN ('dashboard.view','transport.view');

-- HR Officer
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p
WHERE r.name = 'hr_officer' AND p.name IN ('dashboard.view','staff.manage','reports.view');

-- Security, Cleaner
INSERT IGNORE INTO `role_permissions` (`role_id`,`permission_id`)
SELECT r.id, p.id FROM `roles` r, `permissions` p
WHERE r.name IN ('security','cleaner') AND p.name = 'dashboard.view';

-- ── Class Levels ──────────────────────────────────────────────
INSERT IGNORE INTO `class_levels` (`name`,`category`,`order_seq`) VALUES
('Creche',    'nursery', 1),
('Playgroup', 'nursery', 2),
('Nursery 1', 'nursery', 3),
('Nursery 2', 'nursery', 4),
('Primary 1', 'primary', 5),
('Primary 2', 'primary', 6),
('Primary 3', 'primary', 7),
('Primary 4', 'primary', 8),
('Primary 5', 'primary', 9),
('Primary 6', 'primary', 10),
('JSS1',      'jss',     11),
('JSS2',      'jss',     12),
('JSS3',      'jss',     13),
('SSS1',      'sss',     14),
('SSS2',      'sss',     15),
('SSS3',      'sss',     16);

-- ── Sample Academic Session ───────────────────────────────────
INSERT IGNORE INTO `academic_sessions` (`name`,`start_date`,`end_date`,`is_current`) VALUES
('2024/2025', '2024-09-09', '2025-07-25', 0),
('2025/2026', '2025-09-08', '2026-07-24', 1);

-- ── Terms for 2025/2026 ───────────────────────────────────────
INSERT IGNORE INTO `terms` (`session_id`,`term_number`,`name`,`opening_date`,`closing_date`,`exam_date`,`result_date`,`is_current`)
SELECT s.id, 1, 'First Term',  '2025-09-08','2025-12-12','2025-12-01','2025-12-12', 0
FROM `academic_sessions` s WHERE s.name='2025/2026';

INSERT IGNORE INTO `terms` (`session_id`,`term_number`,`name`,`opening_date`,`closing_date`,`exam_date`,`result_date`,`is_current`)
SELECT s.id, 2, 'Second Term', '2026-01-12','2026-04-03','2026-03-23','2026-04-03', 1
FROM `academic_sessions` s WHERE s.name='2025/2026';

INSERT IGNORE INTO `terms` (`session_id`,`term_number`,`name`,`opening_date`,`closing_date`,`exam_date`,`result_date`,`is_current`)
SELECT s.id, 3, 'Third Term',  '2026-04-27','2026-07-24','2026-07-13','2026-07-24', 0
FROM `academic_sessions` s WHERE s.name='2025/2026';

-- ── Fee Categories ────────────────────────────────────────────
INSERT IGNORE INTO `fee_categories` (`name`,`description`,`is_compulsory`) VALUES
('School Fees',      'Main tuition fees',             1),
('PTA Levy',         'Parent-Teacher Association levy',1),
('Examination Fees', 'End of term examination fees',  1),
('Hostel Fees',      'Boarding house fees',            0),
('Transport Fees',   'School bus / transport fees',    0),
('Development Levy', 'School development fund',        0),
('Uniform Levy',     'School uniform contribution',    0);

-- ── Grading Scale ─────────────────────────────────────────────
INSERT IGNORE INTO `grading_scales` (`name`,`is_default`) VALUES ('WAEC/NECO Scale', 1);

INSERT IGNORE INTO `grade_boundaries` (`grading_scale_id`,`grade`,`min_score`,`max_score`,`remark`)
SELECT gs.id, g.grade, g.min_s, g.max_s, g.remark
FROM `grading_scales` gs,
(SELECT 'A1' grade, 75 min_s, 100 max_s, 'Excellent'  remark UNION ALL
 SELECT 'B2',        70, 74.99,  'Very Good' UNION ALL
 SELECT 'B3',        65, 69.99,  'Good'      UNION ALL
 SELECT 'C4',        60, 64.99,  'Credit'    UNION ALL
 SELECT 'C5',        55, 59.99,  'Credit'    UNION ALL
 SELECT 'C6',        50, 54.99,  'Credit'    UNION ALL
 SELECT 'D7',        45, 49.99,  'Pass'      UNION ALL
 SELECT 'E8',        40, 44.99,  'Pass'      UNION ALL
 SELECT 'F9',         0, 39.99,  'Fail') g
WHERE gs.is_default = 1;

-- ── Subjects ──────────────────────────────────────────────────
INSERT IGNORE INTO `subjects` (`name`,`code`,`category`) VALUES
('English Language','ENG','core'),('Mathematics','MTH','core'),
('Basic Science','BSC','core'),('Social Studies','SST','core'),
('Civic Education','CIV','core'),('Christian Religious Studies','CRS','core'),
('Islamic Religious Studies','IRS','core'),('Agricultural Science','AGR','elective'),
('Computer Studies','ICT','core'),('Home Economics','HEC','elective'),
('Business Studies','BST','elective'),('Fine Art','ART','elective'),
('Physical Education','PHE','core'),('Music','MUS','elective'),
('French','FRN','elective'),('Yoruba','YOR','core'),
('Igbo','IGB','core'),('Hausa','HAU','core'),
('Physics','PHY','sss'),('Chemistry','CHM','sss'),
('Biology','BIO','sss'),('Further Mathematics','FMT','sss'),
('Economics','ECO','sss'),('Government','GOV','sss'),
('Literature in English','LIT','sss'),('Accounting','ACC','sss'),
('Commerce','COM','sss'),('Geography','GEO','sss'),
('History','HIS','elective'),('Nutritional Science','NUT','elective');

-- ── Default Admin (Proprietor) ────────────────────────────────
-- Password: Admin@1234 (change on first login)
INSERT IGNORE INTO `users`
  (`role_id`,`first_name`,`last_name`,`email`,`phone`,`password_hash`,`gender`,`status`)
SELECT r.id, 'System', 'Administrator', 'admin@school.edu.ng', '08000000000',
       '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'male', 'active'
FROM `roles` r WHERE r.name = 'proprietor'
LIMIT 1;