// Script to convert Navy_Data.txt into a format suitable for database population
const fs = require('fs');
const path = require('path');

// Read the Navy_Data.txt file
const navyDataPath = path.join(__dirname, '..', 'Navy_Data.txt');
const navyDataContent = fs.readFileSync(navyDataPath, 'utf8');

// Extract the mockUsers array from the file
const mockUsersMatch = navyDataContent.match(/export const mockUsers: User\[\] = \[([\s\S]*?)\];/);
if (!mockUsersMatch) {
  console.error('Could not find mockUsers array in Navy_Data.txt');
  process.exit(1);
}

// Parse the users data (this is a simplified parser)
const usersText = mockUsersMatch[1];
const users = [];

// Split by user objects (look for { id: pattern)
const userBlocks = usersText.split(/(?=\s*{\s*id:\s*\d+)/).filter(block => block.trim());

userBlocks.forEach(block => {
  try {
    // Extract key fields using regex
    const idMatch = block.match(/id:\s*(\d+)/);
    const nameMatch = block.match(/name:\s*"([^"]+)"/);
    const rankMatch = block.match(/rank:\s*"([^"]+)"/);
    const serviceNoMatch = block.match(/service_no:\s*"([^"]+)"/);
    const unitMatch = block.match(/unit:\s*"([^"]+)"/);
    const branchMatch = block.match(/branch:\s*"([^"]+)"/);
    const subBranchMatch = block.match(/subBranch:\s*"([^"]+)"/);
    const departmentMatch = block.match(/department:\s*"([^"]+)"/);
    const roleMatch = block.match(/role:\s*USER_ROLES\.(\w+)/);
    const designationMatch = block.match(/designation:\s*"([^"]+)"/);
    const phoneMatch = block.match(/phone:\s*"([^"]+)"/);
    const mobileMatch = block.match(/mobile:\s*"([^"]+)"/);
    const altMobileMatch = block.match(/alternative_mobile:\s*"([^"]+)"/);
    const emailMatch = block.match(/email:\s*"([^"]+)"/);
    const statusMatch = block.match(/status:\s*"([^"]+)"/);

    if (idMatch && nameMatch && unitMatch) {
      const user = {
        id: parseInt(idMatch[1]),
        name: nameMatch[1],
        rank: rankMatch ? rankMatch[1] : '',
        service_no: serviceNoMatch ? serviceNoMatch[1] : '',
        unit: unitMatch[1],
        branch: branchMatch ? branchMatch[1] : '',
        subBranch: subBranchMatch ? subBranchMatch[1] : '',
        department: departmentMatch ? departmentMatch[1] : '',
        role: roleMatch ? roleMatch[1] : 'User',
        designation: designationMatch ? designationMatch[1] : '',
        phone: phoneMatch ? phoneMatch[1] : '',
        mobile: mobileMatch ? mobileMatch[1] : '',
        alternative_mobile: altMobileMatch ? altMobileMatch[1] : '',
        email: emailMatch ? emailMatch[1] : '',
        status: statusMatch ? statusMatch[1] : 'offline'
      };
      users.push(user);
    }
  } catch (error) {
    console.warn('Error parsing user block:', error.message);
  }
});

console.log(`Parsed ${users.length} users from Navy_Data.txt`);

// Extract unique units
const units = Array.from(new Set(users.map(user => user.unit))).map((unitName, index) => ({
  id: index + 1,
  name: unitName,
  code: unitName.replace(/\s+/g, '_').toUpperCase(),
  description: `${unitName} unit`,
  parent_id: null,
  is_active: 1
}));

console.log('Unique units found:');
units.forEach(unit => console.log(`- ${unit.name} (ID: ${unit.id})`));

// Create SQL insert statements for units
const unitsSQL = units.map(unit => 
  `INSERT INTO units (id, name, code, description, parent_id, is_active, created_at, updated_at) VALUES (${unit.id}, '${unit.name}', '${unit.code}', '${unit.description}', NULL, 1, NOW(), NOW());`
).join('\n');

// Create SQL insert statements for users (simplified)
const usersSQL = users.map(user => {
  const roleId = user.role === 'SUPER_ADMIN' ? 1 : user.role === 'ADMIN' ? 2 : 3;
  const unitId = units.find(u => u.name === user.unit)?.id || 1;
  
  return `INSERT INTO users (id, name, rank, service_no, unit_id, unit_name, branch, sub_branch, department, role_id, role, designation, phone, mobile, alternative_mobile, email, status, is_active, created_at, updated_at) VALUES (${user.id}, '${user.name}', '${user.rank}', '${user.service_no}', ${unitId}, '${user.unit}', '${user.branch}', '${user.subBranch}', '${user.department}', ${roleId}, '${user.role}', '${user.designation}', '${user.phone}', '${user.mobile}', '${user.alternative_mobile}', '${user.email}', '${user.status}', 1, NOW(), NOW());`;
}).join('\n');

// Write to files
fs.writeFileSync(path.join(__dirname, '..', 'database_population_units.sql'), unitsSQL);
fs.writeFileSync(path.join(__dirname, '..', 'database_population_users.sql'), usersSQL);

console.log('\nGenerated SQL files:');
console.log('- database_population_units.sql');
console.log('- database_population_users.sql');
console.log('\nYou can use these SQL files to populate your database with the comprehensive Navy data.');
