const fs = require('fs');
const { promisify } = require('util');
const parseString = promisify(require('xml2js').parseString);
const { Pool } = require('pg');
const XLSX = require('xlsx');
const { performance } = require('perf_hooks');

function evaluateCondition(conditions, value) {
  for (const condition of conditions) {
    if (condition._ && condition._ === value) {
      return condition.__text;
    }
  }
  return value;
}

function evaluateFormula(formula, value) {
  if (typeof formula === 'string') {
    if (formula.startsWith('{#if')) {
      const conditions = formula.Condition;
      return evaluateCondition(conditions, value);
    } else {
      return eval(formula.replace(/value/g, `'${value}'`));
    }
  } else {
    return value;
  }
}

async function insertDataFromExcelToPostgreSQL() {
  try {
    console.log('Inserting data into PostgreSQL...');

    const excelFilePath = process.argv[2];
    const xmlFilePath = process.argv[3];

    if (!excelFilePath || !xmlFilePath) {
      console.error('Please provide both Excel file path and XML file path as command-line arguments.');
      return;
    }

    // Step 1: Parse the XML file
    const xmlString = fs.readFileSync(xmlFilePath, 'utf8');
    const xmlData = await parseString(xmlString);

    const rootElement = xmlData.Mapping.RootElement[0];
    const mappingFields = xmlData.Mapping.MappingFields[0].Field;

    // Step 2: Read the Excel file
    const workbook = XLSX.readFile(excelFilePath);
    const worksheet = workbook.Sheets[workbook.SheetNames[0]];
    const excelData = XLSX.utils.sheet_to_json(worksheet);

    // Step 3: Connect to the PostgreSQL database
    const pool = new Pool({
      user: 'root',
      host: 'localhost',
      database: 'myform',
      password: 'password',
      port: '5432',
    });

    const client = await pool.connect();

    // Step 4: Insert the data into the table
    const columnNames = [];
    const valuePlaceholders = [];
    const values = [];
    let skippedRecords = 0;
    let importedRecords = excelData.length;

    for (const field of mappingFields) {
      const excelColumnName = field.ExcelColumnName[0];
      const tableFieldName = field.TableFieldName[0];
      const formula = field.Formula[0];
      const validation = field.Validation[0];

      columnNames.push(tableFieldName);
      valuePlaceholders.push(`$${columnNames.length}`);

      // Extract the corresponding Excel column values for each table field
      let fieldValues = excelData.map(row => row[excelColumnName]);

      if (formula) {
        fieldValues = fieldValues.map(value => evaluateFormula(formula, value));
      }

      const processedValues = fieldValues.map(value => `${value}`);
      values.push(processedValues);

      // Check if the field values match the validation rule
      if (validation) {
        for (let i = 0; i < fieldValues.length; i++) {
          const value = fieldValues[i];
          const validationResult = eval(validation.replace(/value/g, `'${value}'`));

          if (!validationResult) {
            skippedRecords++;
            console.log(`Skipped record at index ${i}: ${value}`);
            fieldValues[i] = null;
            importedRecords--; // Decrement importedRecords when a record is skipped
          }
        }
      }
    }

    const insertQuery = `INSERT INTO ${rootElement} (${columnNames.join(',')}) VALUES (${valuePlaceholders.join(',')})`;

    // Calculate the maximum number of rows for any field
    const maxRows = values.reduce((max, fieldValues) => Math.max(max, fieldValues.length), 0);
    const startTime = performance.now();

    for (let i = 0; i < maxRows; i++) {
      const rowValues = values.map(fieldValues => fieldValues[i]);

      // Skip the record if any value is null
      if (rowValues.some(value => value === null)) {
        skippedRecords++;
        importedRecords--; // Decrement importedRecords when a record is skipped
        continue;
      }

      // Insert the row into the database
      try {
        await client.query(insertQuery, rowValues);

        const progress = ((i + 1) / maxRows) * 100;
        console.log(`Progress: ${progress.toFixed(2)}%`);
      } catch (error) {
        console.log(`Error inserting record at index ${i}: ${rowValues}`);
        console.error(error);
        importedRecords--; // Decrement importedRecords when a record fails to be inserted
      }
    }

    const endTime = performance.now();
    const executionTime = (endTime - startTime) / 1000;

    // Close the database connection
    client.release();
    pool.end();

    console.log(`Data processed successfully.`);
    console.log(`Total records: ${excelData.length}`);
    console.log(`Skipped records: ${skippedRecords}`);
    console.log(`Imported records: ${importedRecords}`);
    console.log(`Execution time: ${executionTime.toFixed(2)} seconds`);
  } catch (error) {
    console.error('Error:', error);
  }
}

// Check if command-line arguments are provided correctly
if (process.argv.length < 4) {
  console.error('Please provide both Excel file path and XML file path as command-line arguments.');
} else {
  // Call the function to start the process
  insertDataFromExcelToPostgreSQL();
}