const bcrypt = require('bcryptjs');

async function seedUsersAndStaff(db) {
  const hashDefault = bcrypt.hashSync('admin123', 4);
  const hashTeacher = bcrypt.hashSync('teacher123', 4);
  const hashStudent = bcrypt.hashSync('student123', 4);
  const hashParent  = bcrypt.hashSync('parent123', 4);
  const hashBursar  = bcrypt.hashSync('bursar123', 4);
  const hashHR      = bcrypt.hashSync('hr123', 4);
  const hashLib     = bcrypt.hashSync('librarian123', 4);
  const hashPrinc   = bcrypt.hashSync('principal123', 4);

  const users = [
    { uid: 'uid-admin',     email: 'admin@school.com',     first_name: 'System',    last_name: 'Admin',     role: 'school_admin', password: hashDefault },
    { uid: 'uid-principal', email: 'principal@school.com', first_name: 'Tendai',    last_name: 'Moyo',      role: 'principal',    password: hashPrinc },
    { uid: 'uid-teacher',   email: 'teacher@school.com',   first_name: 'Blessing',  last_name: 'Chiwenga',  role: 'teacher',      password: hashTeacher },
    { uid: 'uid-student',   email: 'student@school.com',   first_name: 'Tariro',    last_name: 'Mutasa',    role: 'student',      password: hashStudent },
    { uid: 'uid-parent',    email: 'parent@school.com',    first_name: 'Farai',     last_name: 'Mutasa',    role: 'parent',       password: hashParent },
    { uid: 'uid-bursar',    email: 'bursar@school.com',    first_name: 'Kudzai',    last_name: 'Ncube',     role: 'bursar',       password: hashBursar },
    { uid: 'uid-hr',        email: 'hr@school.com',        first_name: 'Nyasha',    last_name: 'Sithole',   role: 'hr',           password: hashHR },
    { uid: 'uid-librarian', email: 'librarian@school.com', first_name: 'Rumbidzai', last_name: 'Gumbo',     role: 'librarian',    password: hashLib },
  ];

  const stmtUser = db.prepare(
    `INSERT OR IGNORE INTO users (uid, email, password, first_name, last_name, role, is_active)
     VALUES (@uid, @email, @password, @first_name, @last_name, @role, 1)`
  );
  for (const u of users) stmtUser.run(u);

  // Retrieve integer user IDs
  const getUser = db.prepare(`SELECT id FROM users WHERE email = ?`);
  const parentId  = getUser.get('parent@school.com')?.id;
  const studentId = getUser.get('student@school.com')?.id;
  const teacherId = getUser.get('teacher@school.com')?.id;
  const bursarId  = getUser.get('bursar@school.com')?.id;
  const hrId      = getUser.get('hr@school.com')?.id;
  const libId     = getUser.get('librarian@school.com')?.id;

  // Parent-Student linkage
  if (parentId && studentId) {
    db.prepare(
      `INSERT OR IGNORE INTO parent_students (parent_id, student_id, relationship)
       VALUES (@parent_id, @student_id, @relationship)`
    ).run({ parent_id: parentId, student_id: studentId, relationship: 'Father' });
  }

  // Staff Roles
  const staffRoles = [
    { uid: 'uid-srole-teaching', name: 'Teaching Staff', description: 'Academic teaching staff', is_teaching_role: 1 },
    { uid: 'uid-srole-finance',  name: 'Finance & Accounts', description: 'Bursar & Accounting staff', is_teaching_role: 0 },
    { uid: 'uid-srole-hr',       name: 'Human Resources', description: 'HR & Personnel management', is_teaching_role: 0 },
    { uid: 'uid-srole-library',  name: 'Library Services', description: 'Librarian & Resource center staff', is_teaching_role: 0 },
  ];

  const stmtRole = db.prepare(
    `INSERT OR IGNORE INTO staff_roles (uid, name, description, is_teaching_role)
     VALUES (@uid, @name, @description, @is_teaching_role)`
  );
  for (const r of staffRoles) stmtRole.run(r);

  // Salary Grades
  const salaryGrades = [
    { uid: 'uid-sal-a', name: 'Senior Teacher Grade A', grade_level: 'SG-A', basic_salary: 850.00, housing_allowance: 150.00, transport_allowance: 80.00 },
    { uid: 'uid-sal-b', name: 'Bursar / Officer Grade B', grade_level: 'SG-B', basic_salary: 950.00, housing_allowance: 180.00, transport_allowance: 100.00 },
    { uid: 'uid-sal-c', name: 'HR Manager Grade C',     grade_level: 'SG-C', basic_salary: 1100.00, housing_allowance: 200.00, transport_allowance: 120.00 },
  ];

  const stmtSal = db.prepare(
    `INSERT OR IGNORE INTO salary_grades (uid, name, grade_level, basic_salary, housing_allowance, transport_allowance)
     VALUES (@uid, @name, @grade_level, @basic_salary, @housing_allowance, @transport_allowance)`
  );
  for (const s of salaryGrades) stmtSal.run(s);

  // Staff Records
  const roleTeachingId = db.prepare(`SELECT id FROM staff_roles WHERE uid = 'uid-srole-teaching'`).get()?.id;
  const roleFinanceId  = db.prepare(`SELECT id FROM staff_roles WHERE uid = 'uid-srole-finance'`).get()?.id;
  const roleHRId       = db.prepare(`SELECT id FROM staff_roles WHERE uid = 'uid-srole-hr'`).get()?.id;
  const roleLibId      = db.prepare(`SELECT id FROM staff_roles WHERE uid = 'uid-srole-library'`).get()?.id;

  const salGradeA = db.prepare(`SELECT id FROM salary_grades WHERE uid = 'uid-sal-a'`).get()?.id;
  const salGradeB = db.prepare(`SELECT id FROM salary_grades WHERE uid = 'uid-sal-b'`).get()?.id;
  const salGradeC = db.prepare(`SELECT id FROM salary_grades WHERE uid = 'uid-sal-c'`).get()?.id;

  const staff = [
    { uid: 'uid-staff-teacher', user_id: teacherId, staff_number: 'STF-001', role_id: roleTeachingId, salary_grade_id: salGradeA, employment_type: 'full-time', department: 'Academics', designation: 'Senior Science Teacher' },
    { uid: 'uid-staff-bursar',  user_id: bursarId,  staff_number: 'STF-002', role_id: roleFinanceId,  salary_grade_id: salGradeB, employment_type: 'full-time', department: 'Finance',   designation: 'School Bursar' },
    { uid: 'uid-staff-hr',      user_id: hrId,      staff_number: 'STF-003', role_id: roleHRId,       salary_grade_id: salGradeC, employment_type: 'full-time', department: 'HR',        designation: 'HR Manager' },
    { uid: 'uid-staff-lib',     user_id: libId,     staff_number: 'STF-004', role_id: roleLibId,      salary_grade_id: salGradeB, employment_type: 'full-time', department: 'Library',   designation: 'Head Librarian' },
  ];

  const stmtStaff = db.prepare(
    `INSERT OR IGNORE INTO staff_records (uid, user_id, staff_number, role_id, salary_grade_id, employment_type, department, designation, is_active)
     VALUES (@uid, @user_id, @staff_number, @role_id, @salary_grade_id, @employment_type, @department, @designation, 1)`
  );
  for (const st of staff) {
    if (st.user_id) stmtStaff.run(st);
  }

  console.log('[seed] users and staff seeded');
}

module.exports = { seedUsersAndStaff };
