const db = require('../models');
const { sendResponse, sendError } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const { getPagination, getPagingData } = require('../utils/pagination');
const { Op } = require('sequelize');

const toNumber = (value) => {
  const num = parseFloat(value);
  return Number.isFinite(num) ? num : 0;
};

const toFixed3 = (value) => Number(toNumber(value).toFixed(3));

/** Same logic as production nesting UI header “released” qty. */
const computePoReleasedQuantity = (po) => {
  const batches = Array.isArray(po?.batches) ? po.batches : [];
  const releasedFromBatches = toFixed3(
    (batches || [])
      .filter((b) => ['RELEASED', 'IN_PROGRESS'].includes(String(b?.status || '').toUpperCase()))
      .reduce((sum, b) => sum + toNumber(b?.quantity || 0), 0)
  );
  if (toNumber(releasedFromBatches) > 0) return releasedFromBatches;
  if (
    ['RELEASED', 'IN_PROGRESS', 'PARTIALLY_CONFIRMED', 'CONFIRMED', 'CLOSED'].includes(
      String(po?.status || '').toUpperCase()
    )
  ) {
    return toFixed3(po?.order_quantity || 0);
  }
  return toFixed3(0);
};

const scrapWeightFromConfirmation = (c) => {
  const gross = c?.nesting_gross_weight;
  const net = c?.nesting_net_weight;
  if (gross == null || net == null) return null;
  const w = toNumber(gross) - toNumber(net);
  if (!Number.isFinite(w)) return null;
  return toFixed3(w);
};

/** Align with production nesting UI: grade string from material master on a nesting line. */
const gradeShortFromMaterial = (m) => {
  const sheetCode = String(m?.materialSheetType?.sheet_type_code || '').trim();
  const gradeCode = String(m?.materialGrade?.grade_code || '').trim();
  let gradeShort = '-';
  if (sheetCode && gradeCode) gradeShort = `${sheetCode} ${gradeCode}`;
  else if (gradeCode) gradeShort = gradeCode;
  else gradeShort = String(m?.materialGrade?.grade_name || '').trim() || '-';
  if (!gradeShort || gradeShort === '-') {
    gradeShort = String(m?.description || '').trim() || '-';
  }
  return gradeShort;
};

const gradeShortFromNestingRow = (row) => gradeShortFromMaterial(row?.material);

const nestingRowMatchesCatalogGrade = (catalogGradeShort, row) => {
  const gTarget = String(catalogGradeShort || '').replace(/\s+/g, ' ').trim().toUpperCase();
  const gRow = String(gradeShortFromNestingRow(row) || '').replace(/\s+/g, ' ').trim().toUpperCase();
  if (!gTarget) return true;
  if (gRow === gTarget) return true;
  if (gRow && (gTarget === gRow || gTarget.includes(gRow) || gRow.includes(gTarget))) return true;
  const tokens = gTarget.split(/[\s/]+/).filter((t) => t.length >= 2);
  for (const tok of tokens) {
    if (tok.length >= 2 && gRow.includes(tok)) return true;
  }
  return false;
};

/** Nesting line belongs to the same sheet group as a confirmation row (T + grade + L×W). */
const nestingLineMatchesConfirmationGroup = (line, conf) => {
  const t = toFixed3(toNumber(line?.thickness ?? line?.material?.thickness));
  const tC = toFixed3(conf.thickness);
  if (Math.abs(t - tC) > 0.001) return false;
  if (!nestingRowMatchesCatalogGrade(String(conf.grade_short || '').trim(), line)) return false;
  const l = toFixed3(toNumber(line?.material?.length));
  const w = toFixed3(toNumber(line?.material?.width));
  const lC = toFixed3(conf.max_length);
  const wC = toFixed3(conf.max_width);
  if (Math.abs(l - lC) > 0.02 || Math.abs(w - wC) > 0.02) return false;
  return true;
};

const storeLineMatchesConfirmationGroup = (sl, conf) => {
  if (Math.abs(toFixed3(sl.thickness) - toFixed3(conf.thickness)) > 0.001) return false;
  const gL = String(sl.grade_short || '').replace(/\s+/g, ' ').trim().toUpperCase();
  const gC = String(conf.grade_short || '').replace(/\s+/g, ' ').trim().toUpperCase();
  if (gC && !gL) return false;
  if (gC && gL && !(gL === gC || gL.includes(gC) || gC.includes(gL))) return false;
  if (Math.abs(toFixed3(sl.max_length) - toFixed3(conf.max_length)) > 0.02) return false;
  if (Math.abs(toFixed3(sl.max_width) - toFixed3(conf.max_width)) > 0.02) return false;
  return true;
};

/**
 * Scrap Report Register — one row per saved nesting confirmation group (thickness / grade / sheet size).
 *
 * Query params: companyId, plantId, status (production order status), search (production / nesting no.),
 * dateFrom, dateTo (on confirmation updated_at), productionOrderId, page, limit, export
 */
exports.getScrapReportRegisterReport = asyncHandler(async (req, res) => {
  try {
    const {
      page = 1,
      limit = 25,
      export: exportFlag,
      search,
      status,
      companyId,
      plantId,
      dateFrom,
      dateTo,
      productionOrderId,
    } = req.query;

    const isExport = exportFlag === 'true' || exportFlag === true || exportFlag === 1 || exportFlag === '1';
    const effectiveLimit = isExport ? 50000 : parseInt(limit, 10) || 25;
    const effectivePage = isExport ? 1 : parseInt(page, 10) || 1;
    const { limit: limitNum, offset } = getPagination(effectivePage, effectiveLimit);

    const where = {};

    if (companyId) {
      where.company_id = companyId;
    } else if (req.user && !req.user.isSuperAdmin) {
      where.company_id = req.user.companyId;
    }

    if (plantId) where.plant_id = plantId;
    if (productionOrderId) where.production_order_id = productionOrderId;

    if (dateFrom || dateTo) {
      where.updated_at = {};
      if (dateFrom) where.updated_at[Op.gte] = `${String(dateFrom).trim()} 00:00:00`;
      if (dateTo) where.updated_at[Op.lte] = `${String(dateTo).trim()} 23:59:59.999`;
    }

    const searchTrim = search && String(search).trim() ? String(search).trim() : '';
    if (searchTrim) {
      const like = { [Op.like]: `%${searchTrim}%` };
      where[Op.or] = [
        { production_order_number: like },
        { customer_po_number: like },
        { finished_material_number: like },
        { '$productionOrder.production_order_number$': like },
        { '$productionOrder.nesting_number$': like },
      ];
    }

    const poIncludeWhere = {};
    if (status && status !== 'ALL') {
      poIncludeWhere.status = status;
    }

    const productionOrderInclude = {
      model: db.ProductionOrder,
      as: 'productionOrder',
      attributes: [
        'id',
        'production_order_number',
        'nesting_number',
        'order_quantity',
        'status',
        'plant_id',
        'company_id',
      ],
      required: true,
      include: [
        {
          model: db.ProductionOrderBatch,
          as: 'batches',
          attributes: ['id', 'status', 'quantity'],
          required: false,
        },
        { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'], required: false },
      ],
    };

    if (Object.keys(poIncludeWhere).length > 0) {
      productionOrderInclude.where = poIncludeWhere;
    }

    const include = [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'], required: false },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'], required: false },
      productionOrderInclude,
    ];

    const records = await db.ProductionNestingConfirmation.findAndCountAll({
      where,
      include,
      order: [['updated_at', 'DESC'], ['production_order_id', 'DESC'], ['id', 'DESC']],
      limit: limitNum,
      offset,
      distinct: true,
      subQuery: false,
    });

    const rows = records.rows || [];
    const poIds = [...new Set(rows.map((r) => r.production_order_id).filter(Boolean))];

    let nestingLinesByPo = new Map();
    let storeLinesFlat = [];

    if (poIds.length > 0) {
      const nestingWhere = { production_order_id: { [Op.in]: poIds } };
      if (where.company_id) nestingWhere.company_id = where.company_id;

      const nestingLines = await db.ProductionNesting.findAll({
        where: nestingWhere,
        include: [
          {
            model: db.Material,
            as: 'material',
            required: true,
            include: [
              { model: db.MaterialSheetType, as: 'materialSheetType', required: false },
              { model: db.MaterialGrade, as: 'materialGrade', required: false },
              { model: db.MaterialType, as: 'materialType', required: false },
            ],
          },
        ],
      });

      nestingLinesByPo = new Map();
      (nestingLines || []).forEach((line) => {
        const pid = line.production_order_id;
        if (!nestingLinesByPo.has(pid)) nestingLinesByPo.set(pid, []);
        nestingLinesByPo.get(pid).push(line);
      });

      const nsrWhere = { production_order_id: { [Op.in]: poIds } };
      if (where.company_id) nsrWhere.company_id = where.company_id;

      const nestingStoreRequests = await db.NestingStoresRequest.findAll({
        where: nsrWhere,
        include: [{ model: db.NestingStoresRequestLine, as: 'lines', required: false }],
      });

      storeLinesFlat = [];
      (nestingStoreRequests || []).forEach((req) => {
        const poId = req.production_order_id;
        const lines = req.lines || [];
        lines.forEach((ln) => {
          const j = ln?.toJSON ? ln.toJSON() : ln;
          storeLinesFlat.push({ ...j, production_order_id: poId });
        });
      });
    }

    const items = rows.map((row) => {
      const c = row?.toJSON ? row.toJSON() : row;
      const po = c.productionOrder || {};
      const releasedQty = computePoReleasedQuantity(po);

      const linesForPo = nestingLinesByPo.get(c.production_order_id) || [];
      const groupNestingLines = linesForPo.filter((ln) => nestingLineMatchesConfirmationGroup(ln, c));

      let nestingConfirmedQty = toFixed3(c.confirmed_qty ?? 0);
      let scrapQty = toFixed3(c.scrap_qty ?? 0);
      let scrapWeight = scrapWeightFromConfirmation(c);

      if (groupNestingLines.length > 0) {
        nestingConfirmedQty = toFixed3(
          groupNestingLines.reduce((s, ln) => s + toNumber(ln.nesting_confirmed_quantity), 0)
        );
        scrapQty = toFixed3(groupNestingLines.reduce((s, ln) => s + toNumber(ln.scrap_quantity), 0));
      }

      const storeLinesForGroup = storeLinesFlat.filter(
        (sl) =>
          sl.production_order_id === c.production_order_id && storeLineMatchesConfirmationGroup(sl, c)
      );
      if (storeLinesForGroup.length > 0) {
        const wSum = storeLinesForGroup.reduce((s, ln) => s + toNumber(ln.scrap_weight), 0);
        const hasAnyWeight = storeLinesForGroup.some((ln) => ln.scrap_weight != null);
        if (hasAnyWeight) {
          scrapWeight = toFixed3(wSum);
        }
      }

      return {
        id: c.id,
        production_order_id: c.production_order_id,
        production_order_number: po.production_order_number || c.production_order_number || '',
        nesting_number: po.nesting_number || null,
        order_quantity: toFixed3(po.order_quantity ?? 0),
        order_uom_code: po.uom?.uom_code || null,
        material_released_qty: releasedQty,
        nesting_confirmed_qty: nestingConfirmedQty,
        scrap_weight: scrapWeight,
        nesting_weight_uom_code: c.nesting_weight_uom_code || null,
        scrap_qty: scrapQty,
        thickness: c.thickness != null ? toFixed3(c.thickness) : null,
        grade_short: c.grade_short || '',
        max_length: c.max_length != null ? toFixed3(c.max_length) : null,
        max_width: c.max_width != null ? toFixed3(c.max_width) : null,
        finished_material_number: c.finished_material_number || null,
        finished_material_description: c.finished_material_description || null,
        scrap_percentage: c.scrap_percentage != null ? toFixed3(c.scrap_percentage) : null,
        production_order_status: po.status || null,
        company: c.company || null,
        plant: c.plant || null,
        updated_at: c.updated_at || null,
      };
    });

    const response = getPagingData({ count: records.count || 0, rows: items }, effectivePage, limitNum);

    return sendResponse(res, 200, true, 'Scrap Report Register generated successfully', response);
  } catch (error) {
    console.error('Scrap Report Register Report error:', error);
    return sendError(res, error.message || 'Failed to generate Scrap Report Register', 500);
  }
});
