const XLSX = require("xlsx");
const db = require("../config/db");
const leadsController = require("./leadsController");

exports.uploadExcel = async (req, res) => {
  const excelFile = req.file;
  const userId = req.body.userId;
  const tableName = `user_${userId}`;

  if (!excelFile) {
    return res.status(400).json({ error: "No file uploaded." });
  }

  const excelFilePath = excelFile.path;

  try {
    // Read the Excel file
    const workbook = XLSX.readFile(excelFilePath);
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];
    const worksheetData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });

    if (worksheetData.length === 0) {
      return res
        .status(400)
        .json({ error: "No data found in the Excel sheet." });
    }

    // Function to check if a cell contains a header label
    const isHeaderCell = (cell) =>
      typeof cell === "string" &&
      (cell.includes("תעודת זהות") ||
        cell.includes("ענף ראשי") ||
        cell.includes("ענף (משני)") ||
        cell.includes("סוג מוצר") ||
        cell.includes("חברה") ||
        cell.includes("תקופת ביטוח") ||
        cell.includes('פרמיה בש"ח') ||
        cell.includes("סוג פרמיה") ||
        cell.includes("מספר פוליסה") ||
        cell.includes("סיווג תכנית"));

    // Find the header row
    const headerRow = worksheetData.find((row) => row.some(isHeaderCell));

    if (!headerRow) {
      console.log("Headers not found.");
      return res
        .status(400)
        .json({ error: "Headers not found in the Excel sheet." });
    }

    // Find the index of the headerRow
    let headerRowIndex = -1;
    for (let i = 0; i < worksheetData.length; i++) {
      if (worksheetData[i] === headerRow) {
        headerRowIndex = i;
        break;
      }
    }

    if (headerRowIndex === -1) {
      console.log("Header Row Index not found.");
      return res.status(400).json({ error: "Header Row Index not found." });
    }

    const dataToInsert = [];

    const headerMappings = {
      "תעודת זהות": "leadimid",
      "ענף ראשי": "first_branch",
      "ענף (משני)": "second_branch",
      "סוג מוצר": "product_type",
      חברה: "insurance_company",
      "תקופת ביטוח": "insurance_period",
      'פרמיה בש"ח': "premium",
      "סוג פרמיה": "premium_type",
      "מספר פוליסה": "policy_number",
      "סיווג תכנית": "policy_classification", // Update this mapping to match the exact header text
    };

    // Find the index of each header column
    const headerIndexes = headerRow.map((header) =>
      worksheetData[headerRowIndex].indexOf(header)
    );

    function convertDateFormat(dateStr) {
      const parts = dateStr.split("/");
      if (parts.length === 3) {
        return `${parts[2]}-${parts[1].padStart(2, "0")}-${parts[0].padStart(
          2,
          "0"
        )}`;
      }
      return dateStr; // Return original string if it doesn't match expected format
    }

    // After finding headerRowIndex and before looping through each row to process data...

    for (let i = headerRowIndex + 1; i < worksheetData.length; i++) {
      const row = worksheetData[i];

      // Initialize rowData with placeholders for required fields
      let rowData = {
        leadimid: null,
        first_branch: null,
        second_branch: null,
        product_type: null,
        insurance_company: null,
        insurance_period: null,
        premium: null,
        premium_type: null,
        policy_number: null,
        policy_classification: null,
        start_date: null,
        end_date: null,
      };

      let skipRow = false;

      row.forEach((cell, cellIndex) => {
        const header = headerRow[cellIndex];
        const mapping = headerMappings[header];

        if (mapping) {
          // If a required field is empty, mark the row to be skipped.
          if (cell === null || cell === "") {
            console.log(
              `Skipping row ${i} due to missing data in column "${header}"`
            );
            skipRow = true;
            return;
          }
          rowData[mapping] = cell;
        }
      });

      if (skipRow) {
        continue; // Skip to the next iteration of the outer loop
      }

      // Process 'insurance_period' if it exists and is not empty
      if (rowData.insurance_period && rowData.insurance_period.includes("-")) {
        const periods = rowData.insurance_period
          .split(" - ")
          .map((date) => date.trim());
        if (periods.length === 2) {
          rowData.start_date = convertDateFormat(periods[0]);
          rowData.end_date = convertDateFormat(periods[1]);
        }
      }
      delete rowData.insurance_period; // Since insurance_period is split into two fields

      // Ensure no critical fields are missing before adding row data for insertion
      if (!rowData.leadimid /* || other critical fields check */) {
        console.log(`Skipping row ${i} due to missing critical data`);
        continue;
      }

      // Prepare the data for insertion, excluding 'insurance_period'
      dataToInsert.push([
        rowData.leadimid,
        rowData.policy_classification,
        rowData.second_branch,
        rowData.first_branch,
        rowData.insurance_company,
        rowData.premium_type,
        rowData.policy_number,
        parseFloat(rowData.premium), // Ensure numeric values are correctly parsed
        rowData.product_type,
        rowData.end_date,
        rowData.start_date,
      ]);
    }

    // Prepare the SQL query
    const sql = `
INSERT INTO ${tableName}
(leadimid, policy_classification, second_branch, first_branch, insurance_company, premium_type, policy_number, premium, product_type, end_date, start_date)
VALUES ?`;

    db.query(sql, [dataToInsert], (err, results) => {
      if (err) {
        console.error("Error inserting data:", err);
        return res
          .status(500)
          .json({ error: "Error inserting data into the database." });
      }

      console.log("Data inserted successfully.");

      // Invalidate cache after successful data insertion
      leadsController.invalidateCache();

      res
        .status(200)
        .json({ message: "File uploaded and data inserted successfully." });
    });
  } catch (err) {
    console.error("Error reading Excel file:", err);
    res.status(500).json({ error: "Error reading Excel file." });
  }
};
