async function seedFacilitiesAndServices(db) {
  const studentId = db.prepare(`SELECT id FROM users WHERE email = 'student@school.com'`).get()?.id;
  const teacherId = db.prepare(`SELECT id FROM users WHERE email = 'teacher@school.com'`).get()?.id;
  const adminId   = db.prepare(`SELECT id FROM users WHERE email = 'admin@school.com'`).get()?.id;
  const parentId  = db.prepare(`SELECT id FROM users WHERE email = 'parent@school.com'`).get()?.id;
  const libId     = db.prepare(`SELECT id FROM users WHERE email = 'librarian@school.com'`).get()?.id;
  const hrId      = db.prepare(`SELECT id FROM users WHERE email = 'hr@school.com'`).get()?.id;

  // Hostels & Rooms
  db.prepare(
    `INSERT OR IGNORE INTO hostels (uid, name, code, type, address, phone, total_rooms, capacity, is_active)
     VALUES (@uid, @name, @code, @type, @address, @phone, @total_rooms, @capacity, 1)`
  ).run({
    uid: 'uid-hostel-kili',
    name: 'Kilimanjaro Boarding House',
    code: 'HOSTEL-KILI',
    type: 'boys',
    address: 'School Upper Grounds',
    phone: '+263 77 123 4567',
    total_rooms: 20,
    capacity: 40
  });

  const hostelId = db.prepare(`SELECT id FROM hostels WHERE uid = 'uid-hostel-kili'`).get()?.id;

  db.prepare(
    `INSERT OR IGNORE INTO room_types (uid, name, code, description, bed_count, price_per_semester)
     VALUES (@uid, @name, @code, @description, @bed_count, @price_per_semester)`
  ).run({
    uid: 'uid-rtype-dbl',
    name: 'Double Occupancy Standard',
    code: 'ROOM-DBL',
    description: 'Spacious double bed student room',
    bed_count: 2,
    price_per_semester: 250.00
  });

  const rtypeId = db.prepare(`SELECT id FROM room_types WHERE uid = 'uid-rtype-dbl'`).get()?.id;

  if (hostelId && rtypeId) {
    db.prepare(
      `INSERT OR IGNORE INTO rooms (uid, hostel_id, room_type_id, room_number, floor, bed_count, status)
       VALUES (@uid, @hostel_id, @room_type_id, @room_number, @floor, @bed_count, @status)`
    ).run({
      uid: 'uid-room-101',
      hostel_id: hostelId,
      room_type_id: rtypeId,
      room_number: 'Room 101',
      floor: 'Ground Floor',
      bed_count: 2,
      status: 'occupied'
    });

    const roomId = db.prepare(`SELECT id FROM rooms WHERE uid = 'uid-room-101'`).get()?.id;

    if (studentId && roomId) {
      db.prepare(
        `INSERT OR IGNORE INTO room_assignments (uid, student_id, room_id, bed_number, start_date, end_date, status)
         VALUES (@uid, @student_id, @room_id, @bed_number, @start_date, @end_date, @status)`
      ).run({
        uid: 'uid-rassign-1',
        student_id: studentId,
        room_id: roomId,
        bed_number: 1,
        start_date: '2026-01-10',
        end_date: '2026-12-15',
        status: 'active'
      });
    }
  }

  // Vehicles, Routes & Transport Allocations
  db.prepare(
    `INSERT OR IGNORE INTO vehicles (uid, registration_number, model, make, year, capacity, vehicle_type, fuel_type, status)
     VALUES (@uid, @registration_number, @model, @make, @year, @capacity, @vehicle_type, @fuel_type, @status)`
  ).run({
    uid: 'uid-veh-1',
    registration_number: 'ABF-4910',
    model: 'Coaster Bus 65-Seater',
    make: 'Toyota',
    year: 2022,
    capacity: 65,
    vehicle_type: 'bus',
    fuel_type: 'diesel',
    status: 'active'
  });

  db.prepare(
    `INSERT OR IGNORE INTO routes (uid, name, code, description, start_point, end_point, distance_km, estimated_time_minutes, status)
     VALUES (@uid, @name, @code, @description, @start_point, @end_point, @distance_km, @estimated_time_minutes, @status)`
  ).run({
    uid: 'uid-route-north',
    name: 'Harare North Suburban Route',
    code: 'RT-NORTH',
    description: 'Borrowdale - Sam Levy - Mt Pleasant - School',
    start_point: 'Borrowdale',
    end_point: 'School Campus',
    distance_km: 18.5,
    estimated_time_minutes: 45,
    status: 'active'
  });

  const routeId = db.prepare(`SELECT id FROM routes WHERE uid = 'uid-route-north'`).get()?.id;

  if (routeId) {
    db.prepare(
      `INSERT OR IGNORE INTO pickup_points (uid, route_id, name, address, pickup_time, dropoff_time, sequence_order)
       VALUES (@uid, @route_id, @name, @address, @pickup_time, @dropoff_time, @sequence_order)`
    ).run({
      uid: 'uid-pp-1',
      route_id: routeId,
      name: 'Sam Levy Village Stop',
      address: 'Borrowdale Road',
      pickup_time: '06:45',
      dropoff_time: '16:15',
      sequence_order: 1
    });

    const ppId = db.prepare(`SELECT id FROM pickup_points WHERE uid = 'uid-pp-1'`).get()?.id;

    if (studentId && ppId) {
      db.prepare(
        `INSERT OR IGNORE INTO transport_allocations (uid, student_id, route_id, pickup_point_id, fee_amount, is_active)
         VALUES (@uid, @student_id, @route_id, @pickup_point_id, @fee_amount, 1)`
      ).run({
        uid: 'uid-talloc-1',
        student_id: studentId,
        route_id: routeId,
        pickup_point_id: ppId,
        fee_amount: 80.00
      });
    }
  }

  // Inventory & Store Management
  db.prepare(
    `INSERT OR IGNORE INTO item_categories (uid, name, code, description)
     VALUES (@uid, @name, @code, @description)`
  ).run({
    uid: 'uid-cat-stat',
    name: 'Stationery & Writing',
    code: 'CAT-STAT',
    description: 'Pens, markers, exercise books and paper'
  });

  db.prepare(
    `INSERT OR IGNORE INTO item_categories (uid, name, code, description)
     VALUES (@uid, @name, @code, @description)`
  ).run({
    uid: 'uid-cat-lab',
    name: 'Laboratory Equipment',
    code: 'CAT-LAB',
    description: 'Science glassware, meters and chemicals'
  });

  const catStatId = db.prepare(`SELECT id FROM item_categories WHERE uid = 'uid-cat-stat'`).get()?.id;

  if (catStatId) {
    db.prepare(
      `INSERT OR IGNORE INTO items (uid, name, code, barcode, description, category_id, unit, purchase_price, selling_price, reorder_level, is_active)
       VALUES (@uid, @name, @code, @barcode, @description, @category_id, @unit, @purchase_price, @selling_price, @reorder_level, 1)`
    ).run({
      uid: 'uid-item-marker',
      name: 'Whiteboard Markers (Pack of 10)',
      code: 'ITM-MARKER-10',
      barcode: '6001234567890',
      description: 'Black dry-erase whiteboard markers',
      category_id: catStatId,
      unit: 'pack',
      purchase_price: 5.50,
      selling_price: 7.00,
      reorder_level: 10
    });

    db.prepare(
      `INSERT OR IGNORE INTO items (uid, name, code, barcode, description, category_id, unit, purchase_price, selling_price, reorder_level, is_active)
       VALUES (@uid, @name, @code, @barcode, @description, @category_id, @unit, @purchase_price, @selling_price, @reorder_level, 1)`
    ).run({
      uid: 'uid-item-book',
      name: 'A4 Exercise Book 72-Page',
      code: 'ITM-BK-A4-72',
      barcode: '6001234567891',
      description: '72-page ruled exercise book',
      category_id: catStatId,
      unit: 'piece',
      purchase_price: 0.80,
      selling_price: 1.20,
      reorder_level: 50
    });
  }

  // Library Books & Borrowing
  db.prepare(
    `INSERT OR IGNORE INTO library_books (uid, isbn, title, author, category, publisher, year_published, total_copies, available_copies, shelf_location, status)
     VALUES (@uid, @isbn, @title, @author, @category, @publisher, @year_published, @total_copies, @available_copies, @shelf_location, @status)`
  ).run({
    uid: 'uid-bk-puremath',
    isbn: '978-0199148011',
    title: 'Pure Mathematics 1 for O-Level',
    author: 'L. Bostock & S. Chandler',
    category: 'Mathematics',
    publisher: 'Oxford University Press',
    year_published: 2018,
    total_copies: 15,
    available_copies: 12,
    shelf_location: 'Shelf M-04',
    status: 'available'
  });

  db.prepare(
    `INSERT OR IGNORE INTO library_books (uid, isbn, title, author, category, publisher, year_published, total_copies, available_copies, shelf_location, status)
     VALUES (@uid, @isbn, @title, @author, @category, @publisher, @year_published, @total_copies, @available_copies, @shelf_location, @status)`
  ).run({
    uid: 'uid-bk-thingsfallapart',
    isbn: '978-0435905254',
    title: 'Things Fall Apart',
    author: 'Chinua Achebe',
    category: 'Literature',
    publisher: 'Heinemann',
    year_published: 2010,
    total_copies: 20,
    available_copies: 18,
    shelf_location: 'Shelf L-02',
    status: 'available'
  });

  const bookId = db.prepare(`SELECT id FROM library_books WHERE uid = 'uid-bk-puremath'`).get()?.id;

  if (bookId && studentId) {
    db.prepare(
      `INSERT OR IGNORE INTO library_issues (uid, book_id, user_id, issue_date, due_date, status, issued_by)
       VALUES (@uid, @book_id, @user_id, @issue_date, @due_date, @status, @issued_by)`
    ).run({
      uid: 'uid-libissue-1',
      book_id: bookId,
      user_id: studentId,
      issue_date: '2026-07-10',
      due_date: '2026-07-24',
      status: 'issued',
      issued_by: libId || adminId || 1
    });
  }

  // Notices
  const notices = [
    { uid: 'uid-notice-1', title: 'Term 1 Inter-House Athletics Gala', content: 'The annual inter-house athletics competition will take place on Friday 1st August 2026. All students to wear house sports attire.', category: 'Sports', priority: 'Important', audience: 'All', created_by: adminId || 1 },
    { uid: 'uid-notice-2', title: 'Parent-Teacher Consultation Day', content: 'Parent-teacher conferences for Form 4 and Lower 6 will be held on Saturday 8th August 2026 from 09:00 AM to 14:00 PM in the Main Hall.', category: 'Academic', priority: 'Normal', audience: 'Parents', created_by: adminId || 1 },
  ];

  const stmtNotice = db.prepare(
    `INSERT OR IGNORE INTO notices (uid, title, content, category, priority, audience, is_pinned, created_by)
     VALUES (@uid, @title, @content, @category, @priority, @audience, 1, @created_by)`
  );
  for (const n of notices) stmtNotice.run(n);

  // Messages (internal messaging)
  if (adminId && studentId) {
    db.prepare(
      `INSERT OR IGNORE INTO messages (uid, sender_id, recipient_id, subject, body, is_read, priority)
       VALUES (@uid, @sender_id, @recipient_id, @subject, @body, 1, @priority)`
    ).run({
      uid: 'uid-msg-1',
      sender_id: adminId,
      recipient_id: studentId,
      subject: 'Welcome to Term 1 2026 Academic Year',
      body: 'Dear Student, welcome back to school! Please check your portal timetable and report any course discrepancies to the Bursar or Class Teacher.',
      priority: 'normal'
    });
  }

  if (teacherId && parentId) {
    db.prepare(
      `INSERT OR IGNORE INTO messages (uid, sender_id, recipient_id, subject, body, is_read, priority)
       VALUES (@uid, @sender_id, @recipient_id, @subject, @body, 0, @priority)`
    ).run({
      uid: 'uid-msg-2',
      sender_id: teacherId,
      recipient_id: parentId,
      subject: 'Mathematics Progress Update - Tariro Mutasa',
      body: 'Dear Mr. Mutasa, Tariro scored 88% on the recent Mid-Term Mathematics assessment. Excellent performance!',
      priority: 'normal'
    });
  }

  // Leave Types & Requests
  db.prepare(
    `INSERT OR IGNORE INTO leave_types (uid, name, code, description, max_days_per_year, is_paid, color)
     VALUES (@uid, @name, @code, @description, @max_days_per_year, @is_paid, @color)`
  ).run({
    uid: 'uid-ltype-annual',
    name: 'Annual Leave',
    code: 'LEAVE-ANNUAL',
    description: 'Standard annual leave allocation',
    max_days_per_year: 24,
    is_paid: 1,
    color: '#3B82F6'
  });

  db.prepare(
    `INSERT OR IGNORE INTO leave_types (uid, name, code, description, max_days_per_year, is_paid, color)
     VALUES (@uid, @name, @code, @description, @max_days_per_year, @is_paid, @color)`
  ).run({
    uid: 'uid-ltype-sick',
    name: 'Sick Leave',
    code: 'LEAVE-SICK',
    description: 'Medical & sick leave',
    max_days_per_year: 12,
    is_paid: 1,
    color: '#EF4444'
  });

  const ltypeSickId = db.prepare(`SELECT id FROM leave_types WHERE uid = 'uid-ltype-sick'`).get()?.id;

  if (ltypeSickId && teacherId) {
    db.prepare(
      `INSERT OR IGNORE INTO leave_requests (uid, staff_id, leave_type_id, start_date, end_date, days_count, reason, status, approved_by)
       VALUES (@uid, @staff_id, @leave_type_id, @start_date, @end_date, @days_count, @reason, @status, @approved_by)`
    ).run({
      uid: 'uid-lreq-1',
      staff_id: teacherId,
      leave_type_id: ltypeSickId,
      start_date: '2026-06-10',
      end_date: '2026-06-12',
      days_count: 2,
      reason: 'Flu and fever medical rest',
      status: 'approved',
      approved_by: hrId || adminId || 1
    });
  }

  console.log('[seed] facilities, transport, library, notices & services seeded');
}

module.exports = { seedFacilitiesAndServices };
