const ExcelJS = require('exceljs');
const path = require('path');
const fs = require('fs');

const testDataDir = path.join(__dirname, '..', 'test-data');

// Ensure test-data directory exists
if (!fs.existsSync(testDataDir)) {
  fs.mkdirSync(testDataDir, { recursive: true });
}

async function createWorkOrderFile(filename, workOrders) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Sheet1');

  worksheet.getCell('A1').value = 'Work Order Number';
  workOrders.forEach((wo, index) => {
    worksheet.getCell(`A${index + 2}`).value = wo;
  });

  const filepath = path.join(testDataDir, filename);
  await workbook.xlsx.writeFile(filepath);
  console.log(`Created: ${filepath}`);
  return filepath;
}

async function createComponentLotFile(filename, lots) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Sheet1');

  worksheet.getCell('A1').value = 'Lot Number';
  worksheet.getCell('B1').value = 'Item Number';
  lots.forEach((lot, index) => {
    worksheet.getCell(`A${index + 2}`).value = lot.lotNumber;
    worksheet.getCell(`B${index + 2}`).value = lot.itemNumber;
  });

  const filepath = path.join(testDataDir, filename);
  await workbook.xlsx.writeFile(filepath);
  console.log(`Created: ${filepath}`);
  return filepath;
}

async function createItemNumberFile(filename, items) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Sheet1');

  worksheet.getCell('A1').value = 'Item Number';
  items.forEach((item, index) => {
    worksheet.getCell(`A${index + 2}`).value = item;
  });

  const filepath = path.join(testDataDir, filename);
  await workbook.xlsx.writeFile(filepath);
  console.log(`Created: ${filepath}`);
  return filepath;
}

async function createPartOperationFile(filename, operations) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Sheet1');

  worksheet.getCell('A1').value = 'Item Number';
  worksheet.getCell('B1').value = 'Operation Number';
  worksheet.getCell('C1').value = 'MIS Number';
  worksheet.getCell('D1').value = 'MIS Revision';

  operations.forEach((op, index) => {
    worksheet.getCell(`A${index + 2}`).value = op.itemNumber;
    worksheet.getCell(`B${index + 2}`).value = op.operationNumber;
    worksheet.getCell(`C${index + 2}`).value = op.misNumber;
    worksheet.getCell(`D${index + 2}`).value = op.misRevision;
  });

  const filepath = path.join(testDataDir, filename);
  await workbook.xlsx.writeFile(filepath);
  console.log(`Created: ${filepath}`);
  return filepath;
}

async function createEmptyExcelFile(filename) {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Sheet1');
  // Just headers, no data
  worksheet.getCell('A1').value = 'Work Order Number';

  const filepath = path.join(testDataDir, filename);
  await workbook.xlsx.writeFile(filepath);
  console.log(`Created: ${filepath}`);
  return filepath;
}

async function createInvalidTextFile(filename) {
  const filepath = path.join(testDataDir, filename);
  fs.writeFileSync(filepath, 'This is not a valid Excel file\nJust plain text');
  console.log(`Created: ${filepath}`);
  return filepath;
}

async function main() {
  console.log('Creating test Excel files...\n');

  // Work Order test files
  await createWorkOrderFile('single_workorder.xlsx', [99059700]);
  await createWorkOrderFile('multiple_workorders.xlsx', [99059700, 99059701, 99059702]);
  // Maximum work orders from test data
  await createWorkOrderFile('max_workorders.xlsx', [
    99059700, 99002260, 99002259, 99002258, 99002257,
    99002256, 99002255, 99002254, 99002252, 99002251,
    99002250, 99002249, 99002248, 99002247, 99002246
  ]);

  // Component Lot test files
  await createComponentLotFile('single_lot.xlsx', [
    { lotNumber: 'LOT001', itemNumber: '00598004702' }
  ]);
  await createComponentLotFile('multiple_lots.xlsx', [
    { lotNumber: 'LOT001', itemNumber: '00598004702' },
    { lotNumber: 'LOT002', itemNumber: '00598004703' }
  ]);

  // Item Number test files
  await createItemNumberFile('single_item.xlsx', ['00598004702']);
  await createItemNumberFile('multiple_items.xlsx', ['00598004702', '00598004703', '00598004704']);

  // Part Operation test files
  await createPartOperationFile('single_operation.xlsx', [
    { itemNumber: '00598004702', operationNumber: '100', misNumber: 'MIS001', misRevision: 'A' }
  ]);
  await createPartOperationFile('multiple_operations.xlsx', [
    { itemNumber: '00598004702', operationNumber: '100', misNumber: 'MIS001', misRevision: 'A' },
    { itemNumber: '00598004702', operationNumber: '200', misNumber: 'MIS002', misRevision: 'B' }
  ]);

  // Invalid test files for negative testing
  console.log('\nCreating invalid test files for negative testing...');
  await createEmptyExcelFile('empty_file.xlsx');
  await createInvalidTextFile('invalid_format.txt');

  // Work orders with invalid data
  await createWorkOrderFile('invalid_workorders.xlsx', ['ABC123XYZ', 'INVALID', '']);
  await createWorkOrderFile('special_chars_workorders.xlsx', ['99059700!@#', '99002260$%^', '99002259&*']);

  console.log('\nAll test files created successfully!');
}

main().catch(console.error);