const ExcelJS = require("exceljs");
const db = require("../models");
const { Op } = require("sequelize");

const Submission = db.Submission;
const SubmissionProduct = db.SubmissionProduct;
const Establishment = db.Establishment;
const Product = db.Product;
const UnitMaster = db.UnitMaster;

const QUARTERS = ["Q1", "Q2", "Q3", "Q4"];
const BASE_YEAR = Number(process.env.BASE_YEAR) || 2022;

const parseYears = (yearsParam) => {
  if (!yearsParam) return null;
  const years = String(yearsParam)
    .split(",")
    .map((y) => parseInt(y.trim(), 10))
    .filter((y) => !Number.isNaN(y));
  return years.length ? years : null;
};

const parseStatuses = (statusParam) => {
  if (!statusParam) return ["Approved", "Submitted", "Resubmitted"];
  return String(statusParam)
    .split(",")
    .map((s) => s.trim())
    .filter(Boolean);
};

const parseNumber = (value) => {
  if (value === null || value === undefined || value === "") return null;
  const num = Number(value);
  return Number.isNaN(num) ? null : num;
};

const sanitizeUom = (uom) => {
  if (uom === null || uom === undefined) return "";
  const trimmed = String(uom).trim();
  if (!trimmed || trimmed.toLowerCase() === "not defined") return "";
  return trimmed;
};

const joinUnique = (set) =>
  [...set].map(sanitizeUom).filter(Boolean).sort().join(", ");

const hasPeriodData = (cell) => {
  if (!cell) return false;
  return cell.quantity !== null || cell.value !== null;
};

const addPeriodValues = (target, source) => {
  if (!source) return;
  if (source.quantity !== null) {
    target.quantity = (target.quantity ?? 0) + source.quantity;
  }
  if (source.value !== null) {
    target.value = (target.value ?? 0) + source.value;
  }
};

const aggregateRowsByHsCode = (rawRows) => {
  const hsMap = new Map();

  for (const row of rawRows) {
    const hsKey = String(row.hs_code);
    if (!hsMap.has(hsKey)) {
      hsMap.set(hsKey, {
        hs_code: row.hs_code,
        establishments: new Set(),
        units: new Set(),
        periods: {},
      });
    }

    const entry = hsMap.get(hsKey);
    const rowHasData = Object.values(row.periods).some(hasPeriodData);
    if (row.establishment && rowHasData) entry.establishments.add(row.establishment);
    if (row.unit && rowHasData) entry.units.add(row.unit);

    for (const [periodKey, cell] of Object.entries(row.periods)) {
      if (!hasPeriodData(cell)) continue;
      if (!entry.periods[periodKey]) {
        entry.periods[periodKey] = { quantity: null, value: null };
      }
      addPeriodValues(entry.periods[periodKey], cell);
    }
  }

  return [...hsMap.values()]
    .map((entry) => ({
      establishment: joinUnique(entry.establishments),
      hs_code: entry.hs_code,
      unit: joinUnique(entry.units),
      periods: entry.periods,
    }))
    .sort((a, b) => Number(a.hs_code) - Number(b.hs_code));
};

const yearHasData = (year, rows) => {
  const suffix = `-${year}`;
  return rows.some((row) =>
    Object.entries(row.periods).some(
      ([key, cell]) => key.endsWith(suffix) && hasPeriodData(cell)
    )
  );
};

const resolveExportYears = (discoveredYears, rows, requestedYears) => {
  const yearsWithData = new Set();

  for (const year of discoveredYears) {
    if (yearHasData(year, rows)) yearsWithData.add(year);
  }

  if (requestedYears?.length) {
    for (const year of requestedYears) {
      if (yearHasData(year, rows)) yearsWithData.add(year);
    }
  }

  yearsWithData.add(BASE_YEAR);

  const otherYears = [...yearsWithData]
    .filter((y) => y !== BASE_YEAR)
    .sort((a, b) => a - b);

  return [BASE_YEAR, ...otherYears].filter(
    (year, index, arr) => arr.indexOf(year) === index
  );
};

const buildAnnexIIPeriods = (years, rows) => {
  const periods = [];

  for (const year of years) {
    if (year === BASE_YEAR) {
      for (const quarter of QUARTERS) {
        periods.push(`${quarter}-${year}`);
      }
      continue;
    }

    for (const quarter of QUARTERS) {
      const periodKey = `${quarter}-${year}`;
      const hasData = rows.some((row) => hasPeriodData(row.periods[periodKey]));
      if (hasData) periods.push(periodKey);
    }
  }

  return periods;
};

const buildAnnexIIYearPeriods = (years, rows) => {
  const result = [];
  for (const year of years) {
    if (year === BASE_YEAR) {
      result.push({ year, quarters: [...QUARTERS] });
      continue;
    }
    const quarters = QUARTERS.filter((q) =>
      rows.some((row) => hasPeriodData(row.periods[`${q}-${year}`]))
    );
    if (quarters.length) result.push({ year, quarters });
  }
  return result;
};

const styleHeaderRow = (row) => {
  row.eachCell((cell) => {
    cell.font = { bold: true };
    cell.alignment = { horizontal: "center", vertical: "middle", wrapText: true };
    cell.border = {
      top: { style: "thin" },
      left: { style: "thin" },
      bottom: { style: "thin" },
      right: { style: "thin" },
    };
  });
};

const setExcelResponseHeaders = (res, filename) => {
  res.setHeader("X-Frame-Options", "DENY");
  res.setHeader("X-Content-Type-Options", "nosniff");
  res.setHeader(
    "Content-Type",
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
  );
  res.setHeader("Content-Disposition", `attachment; filename=${filename}`);
};

const fetchExportRows = async ({ years, statuses, emirate_id, establishment_id }) => {
  const submissionWhere = {
    status: { [Op.in]: statuses },
  };

  if (years?.length) {
    submissionWhere.year = { [Op.in]: years };
  }

  const establishmentWhere = {};
  if (emirate_id) {
    establishmentWhere.establishment_emirate_id = emirate_id;
  }
  if (establishment_id) {
    establishmentWhere.id = establishment_id;
  }

  const submissions = await Submission.findAll({
    where: submissionWhere,
    include: [
      {
        model: Establishment,
        as: "establishment",
        attributes: ["id", "establishment_code", "factory_name"],
        where: Object.keys(establishmentWhere).length ? establishmentWhere : undefined,
      },
      {
        model: SubmissionProduct,
        as: "products",
        where: { is_active: true },
        required: true,
        include: [
          {
            model: Product,
            as: "product",
            attributes: ["id", "hs_code", "product_name", "unit_id"],
            include: [
              {
                model: UnitMaster,
                as: "unit",
                attributes: ["uom"],
              },
            ],
          },
        ],
      },
    ],
    order: [
      ["year", "ASC"],
      ["quarter", "ASC"],
      ["id", "DESC"],
    ],
  });

  const rawRows = [];
  const discoveredYears = new Set();

  for (const submission of submissions) {
    const establishmentCode =
      submission.establishment?.establishment_code ||
      submission.establishment?.factory_name ||
      `EST-${submission.establishment_id}`;

    const periodKey = `${submission.quarter}-${submission.year}`;
    discoveredYears.add(submission.year);

    for (const sp of submission.products) {
      const hsCode = sp.product?.hs_code;
      if (!hsCode) continue;

      const unit = sanitizeUom(sp.product?.unit?.uom);
      rawRows.push({
        establishment: establishmentCode,
        hs_code: hsCode,
        unit,
        periods: {
          [periodKey]: {
            quantity: parseNumber(sp.current_quantity),
            value: parseNumber(sp.current_cost),
          },
        },
      });
    }
  }

  const rows = aggregateRowsByHsCode(rawRows);
  const resolvedYears = resolveExportYears(
    [...discoveredYears],
    rows,
    years
  );

  return { rows, years: resolvedYears };
};

const buildAnnexIIWorkbook = (rows, years) => {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet("Sheet1");
  const periods = buildAnnexIIPeriods(years, rows);

  const headerRow1 = ["Establishment", "Product \n(HS 8Digit)", "Unit"];
  const headerRow2 = ["", "", ""];

  for (const period of periods) {
    headerRow1.push(period);
    headerRow1.push("");
    headerRow2.push("Quantity", "Value");
  }

  worksheet.addRow(headerRow1);
  worksheet.addRow(headerRow2);

  worksheet.mergeCells(1, 1, 2, 1);
  worksheet.mergeCells(1, 2, 2, 2);
  worksheet.mergeCells(1, 3, 2, 3);

  let colIndex = 4;
  for (const period of periods) {
    worksheet.mergeCells(1, colIndex, 1, colIndex + 1);
    worksheet.getCell(1, colIndex).value = period;
    colIndex += 2;
  }

  styleHeaderRow(worksheet.getRow(1));
  styleHeaderRow(worksheet.getRow(2));

  worksheet.getColumn(1).width = 24;
  worksheet.getColumn(2).width = 18;
  worksheet.getColumn(3).width = 20;

  for (const row of rows) {
    const values = [row.establishment, row.hs_code, row.unit];
    for (const period of periods) {
      const cell = row.periods[period] || {};
      values.push(cell.quantity ?? null, cell.value ?? null);
    }
    const excelRow = worksheet.addRow(values);
    for (let c = 4; c < values.length; c += 2) {
      const qtyCell = excelRow.getCell(c);
      const valCell = excelRow.getCell(c + 1);
      if (qtyCell.value !== null) qtyCell.numFmt = "#,##0.##";
      if (valCell.value !== null) valCell.numFmt = "#,##0.##";
    }
  }

  return workbook;
};

const buildAnnexIIIColumns = (years, rows) => {
  const columns = [];
  for (const year of years) {
    const quarters =
      year === BASE_YEAR
        ? [...QUARTERS]
        : QUARTERS.filter((q) =>
            rows.some((row) => hasPeriodData(row.periods[`${q}-${year}`]))
          );
    for (const quarter of quarters) {
      columns.push({
        year,
        quarter,
        periodKey: `${quarter}-${year}`,
      });
    }
  }
  return columns;
};

const buildAnnexIIIWorkbook = (rows, years, metric) => {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet("Sheet1");
  const isQuantity = metric === "quantity";
  const columns = buildAnnexIIIColumns(years, rows);

  const yearRow = ["Establishment ", "Product(HS-8D)", "Unit"];
  const quarterRow = ["", "", ""];

  for (const col of columns) {
    yearRow.push("");
    quarterRow.push(col.quarter);
  }

  worksheet.addRow(yearRow);
  worksheet.addRow(quarterRow);

  worksheet.mergeCells(1, 1, 2, 1);
  worksheet.mergeCells(1, 2, 2, 2);
  worksheet.mergeCells(1, 3, 2, 3);

  let colIndex = 4;
  let i = 0;
  while (i < columns.length) {
    const year = columns[i].year;
    const yearLabel =
      year === BASE_YEAR ? `BASE YEAR ${year}` : `AND ${year}`;
    let j = i;
    while (j < columns.length && columns[j].year === year) j++;
    const span = j - i;

    worksheet.getCell(1, colIndex).value = yearLabel;
    if (span > 1) {
      worksheet.mergeCells(1, colIndex, 1, colIndex + span - 1);
    }

    colIndex += span;
    i = j;
  }

  styleHeaderRow(worksheet.getRow(1));
  styleHeaderRow(worksheet.getRow(2));

  worksheet.getColumn(1).width = 24;
  worksheet.getColumn(2).width = 18;
  worksheet.getColumn(3).width = 20;
  for (let c = 4; c < 4 + columns.length; c++) {
    worksheet.getColumn(c).width = 12;
  }

  for (const row of rows) {
    const values = [
      row.establishment,
      row.hs_code,
      isQuantity ? row.unit : "AED",
    ];

    for (const col of columns) {
      const cell = row.periods[col.periodKey] || {};
      values.push(isQuantity ? (cell.quantity ?? null) : (cell.value ?? null));
    }

    const excelRow = worksheet.addRow(values);
    for (let c = 4; c < 4 + columns.length; c++) {
      const dataCell = excelRow.getCell(c);
      if (dataCell.value !== null) dataCell.numFmt = "#,##0.##";
    }
  }

  return workbook;
};

const exportAnnex = async (res, { type, years, statuses, emirate_id, establishment_id }) => {
  const parsedYears = parseYears(years);
  const parsedStatuses = parseStatuses(statuses);

  const { rows, years: resolvedYears } = await fetchExportRows({
    years: parsedYears,
    statuses: parsedStatuses,
    emirate_id,
    establishment_id,
  });

  if (!rows.length) {
    const err = new Error("No submission data found for the given filters");
    err.statusCode = 404;
    throw err;
  }

  let workbook;
  let filename;

  if (type === "annex_ii") {
    workbook = buildAnnexIIWorkbook(rows, resolvedYears);
    filename = `Annex_II_Establishment_Quarterly_${Date.now()}.xlsx`;
  } else if (type === "annex_iii_quantity") {
    workbook = buildAnnexIIIWorkbook(rows, resolvedYears, "quantity");
    filename = `Annex_III_Item_Level_Quantity_${Date.now()}.xlsx`;
  } else if (type === "annex_iii_values") {
    workbook = buildAnnexIIIWorkbook(rows, resolvedYears, "value");
    filename = `Annex_III_Item_Level_Values_${Date.now()}.xlsx`;
  } else {
    const err = new Error("Invalid export type");
    err.statusCode = 400;
    throw err;
  }

  setExcelResponseHeaders(res, filename);
  await workbook.xlsx.write(res);
  res.end();
};

module.exports = {
  exportAnnex,
  parseYears,
  parseStatuses,
  fetchExportRows,
  aggregateRowsByHsCode,
  resolveExportYears,
  buildAnnexIIWorkbook,
  buildAnnexIIIWorkbook,
  BASE_YEAR,
};