const Database = require('better-sqlite3');
const { v4: uuidv4 } = require('uuid');
const path = require('path');

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
const db = new Database(dbPath);
db.pragma('foreign_keys = ON');

console.log('Populating leave portal and ensuring staff records...');

try {
  // 1. Ensure default leave types exist
  const leaveTypesCount = db.prepare('SELECT COUNT(*) as c FROM leave_types WHERE is_deleted = 0').get().c;
  if (leaveTypesCount === 0) {
    console.log('Inserting default leave types...');
    const insertLeaveType = db.prepare(`
      INSERT INTO leave_types (uid, name, code, description, max_days_per_year, is_paid, requires_approval, color)
      VALUES (?, ?, ?, ?, ?, 1, 1, ?)
    `);
    insertLeaveType.run(uuidv4(), 'Annual Leave', 'ANN', 'Paid vacation leave', 20, '#3b82f6');
    insertLeaveType.run(uuidv4(), 'Sick Leave', 'SIC', 'Medical leave for illness or appointments', 14, '#ef4444');
    insertLeaveType.run(uuidv4(), 'Maternity Leave', 'MAT', 'Maternity leave for child birth', 90, '#ec4899');
    insertLeaveType.run(uuidv4(), 'Casual/Emergency Leave', 'CAS', 'Brief urgent personal leave', 7, '#f59e0b');
    insertLeaveType.run(uuidv4(), 'Study Leave', 'STU', 'Educational study preparation', 15, '#10b981');
  }

  // Fetch all leave types
  const leaveTypes = db.prepare('SELECT * FROM leave_types WHERE is_deleted = 0').all();
  console.log(`✓ Leave Types available: ${leaveTypes.length}`);

  // 2. Ensure default salary grades and staff roles exist for linking
  let role = db.prepare('SELECT id FROM staff_roles WHERE is_deleted = 0 LIMIT 1').get();
  if (!role) {
    const roleId = db.prepare(`
      INSERT INTO staff_roles (uid, name, description, is_teaching_role, permissions)
      VALUES (?, 'Senior Teacher', 'Academic staff profile', 1, '[]')
    `).run(uuidv4()).lastInsertRowid;
    role = { id: roleId };
  }

  let grade = db.prepare('SELECT id FROM salary_grades WHERE is_deleted = 0 LIMIT 1').get();
  if (!grade) {
    const gradeId = db.prepare(`
      INSERT INTO salary_grades (uid, name, grade_level, basic_salary, housing_allowance, transport_allowance, medical_allowance, other_allowances, description, effective_date)
      VALUES (?, 'Grade A1', 'A1', 3500, 500, 300, 200, 100, 'Standard Academic Grade', '2026-01-01')
    `).run(uuidv4()).lastInsertRowid;
    grade = { id: gradeId };
  }

  // 3. Find teacher/staff users who lack staff_records and create them
  const staffUsers = db.prepare(`
    SELECT * FROM users 
    WHERE role IN ('teacher', 'hr', 'principal', 'librarian', 'bursar')
    AND is_deleted = 0
  `).all();

  console.log(`Analyzing ${staffUsers.length} staff accounts in users table...`);

  let recordsCreated = 0;
  const findRecord = db.prepare('SELECT id FROM staff_records WHERE user_id = ? AND is_deleted = 0');
  const insertRecord = db.prepare(`
    INSERT INTO staff_records (uid, user_id, staff_number, role_id, salary_grade_id, employment_type, appointment_date, bank_name, account_number, department, designation, is_active)
    VALUES (?, ?, ?, ?, ?, 'full-time', '2025-01-15', 'NMB Bank', '1004-9844-320', ?, ?, 1)
  `);

  staffUsers.forEach((u, i) => {
    const rec = findRecord.get(u.id);
    if (!rec) {
      const staffNum = `STF-${100 + i}`;
      const dept = u.role === 'teacher' ? 'Sciences' : 'Administration';
      const desig = u.role === 'teacher' ? 'Subject Instructor' : 'Administrative Lead';
      insertRecord.run(uuidv4(), u.id, staffNum, role.id, grade.id, dept, desig);
      recordsCreated++;
    }
  });

  console.log(`✓ Created ${recordsCreated} missing staff records.`);

  // 4. Populate leave_requests with realistic records
  // We want to clear out any old dummy leave requests to have a clean list, or just add to them
  db.prepare('DELETE FROM leave_requests').run();

  const insertLeave = db.prepare(`
    INSERT INTO leave_requests (uid, staff_id, leave_type_id, start_date, end_date, days_count, reason, status, approved_by, approved_at, rejection_reason)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);

  const currentStaff = db.prepare('SELECT user_id, first_name, last_name FROM staff_records sr JOIN users u ON sr.user_id = u.id').all();
  if (currentStaff.length === 0) {
    console.log('No staff records found to link leave requests to.');
    process.exit(1);
  }

  // Get an admin/principal ID for approvals
  const adminUser = db.prepare("SELECT id FROM users WHERE role IN ('school_admin', 'systems_admin', 'principal') LIMIT 1").get();
  const approverId = adminUser ? adminUser.id : currentStaff[0].user_id;

  const mockLeaves = [
    {
      staffIdx: 0,
      typeCode: 'ANN',
      start: '2026-07-01',
      end: '2026-07-15',
      days: 10,
      reason: 'Annual family summer travel and rest.',
      status: 'pending',
      approvedBy: null,
      approvedAt: null,
      rejReason: null
    },
    {
      staffIdx: Math.min(1, currentStaff.length - 1),
      typeCode: 'SIC',
      start: '2026-06-12',
      end: '2026-06-16',
      days: 4,
      reason: 'Dental surgery and subsequent mandatory bedrest.',
      status: 'approved',
      approvedBy: approverId,
      approvedAt: '2026-06-10 14:00:00',
      rejReason: null
    },
    {
      staffIdx: Math.min(2, currentStaff.length - 1),
      typeCode: 'CAS',
      start: '2026-06-25',
      end: '2026-06-27',
      days: 3,
      reason: 'Urgent family homestead repair in Mutare.',
      status: 'rejected',
      approvedBy: approverId,
      approvedAt: '2026-06-18 09:30:00',
      rejReason: 'Mid-term exams scheduled during this period; department is short-staffed.'
    },
    {
      staffIdx: Math.min(3, currentStaff.length - 1),
      typeCode: 'STU',
      start: '2026-08-10',
      end: '2026-08-20',
      days: 8,
      reason: 'Preparation and study block for postgraduate exams.',
      status: 'pending',
      approvedBy: null,
      approvedAt: null,
      rejReason: null
    },
    {
      staffIdx: Math.min(4, currentStaff.length - 1),
      typeCode: 'SIC',
      start: '2026-06-18',
      end: '2026-06-20',
      days: 2,
      reason: 'Severe acute seasonal influenza and medical recovery.',
      status: 'approved',
      approvedBy: approverId,
      approvedAt: '2026-06-18 07:15:00',
      rejReason: null
    },
    {
      staffIdx: Math.min(5, currentStaff.length - 1),
      typeCode: 'ANN',
      start: '2026-09-01',
      end: '2026-09-10',
      days: 7,
      reason: 'Personal leave for attending siblings wedding in Bulawayo.',
      status: 'pending',
      approvedBy: null,
      approvedAt: null,
      rejReason: null
    }
  ];

  mockLeaves.forEach(ml => {
    const staff = currentStaff[ml.staffIdx];
    const type = leaveTypes.find(t => t.code === ml.typeCode) || leaveTypes[0];
    
    insertLeave.run(
      uuidv4(),
      staff.user_id,
      type.id,
      ml.start,
      ml.end,
      ml.days,
      ml.reason,
      ml.status,
      ml.approvedBy,
      ml.approvedAt,
      ml.rejReason
    );
    console.log(`  Added ${ml.status} leave request for ${staff.first_name} ${staff.last_name} (${ml.days} days, ${ml.typeCode})`);
  });

  console.log('✓ Seeding complete! Leave requests are now populated.');

} catch (err) {
  console.error('Error during seeding leave requests:', err);
}
