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

const round3 = (value) => Number((parseFloat(value || 0) || 0).toFixed(3));

const MAX_LEDGER_ROWS = 5000;

const materialTypeFilterWhere = (materialTypeParam) => {
  const v = String(materialTypeParam || '').toUpperCase();
  if (v === 'RAW' || v === 'RAW_MATERIAL') {
    return { material_type: 'RAW_MATERIAL' };
  }
  if (v === 'FASTENER' || v === 'CONSUMABLE') {
    return { [Op.or]: [{ material_type: 'CONSUMABLE' }, { material_type: 'PACKAGING' }] };
  }
  return null;
};

const transactionTypeLabel = (movementType, direction) => {
  switch (String(movementType || '')) {
    case 'RECEIPT_PO_RECEIVE':
      return 'Receive';
    case 'RECEIPT_MATERIAL_ISSUE':
      return 'Receive';
    case 'RECEIPT_PO_ADJUST_DOWN':
      return direction === 'OUT' ? 'Receive adjustment (out)' : 'Adjustment';
    case 'OPERATION_CONSUME':
      return 'Consumption';
    case 'OPERATION_SCRAP':
      return 'Scrap';
    case 'REVERSAL':
      return direction === 'IN' ? 'Reverse' : 'Reverse (out)';
    default:
      return movementType || '—';
  }
};

const materialCategoryLabel = (materialType) => {
  const t = String(materialType || '').toUpperCase();
  if (t === 'RAW_MATERIAL') return 'Raw material';
  if (t === 'CONSUMABLE' || t === 'PACKAGING') return 'Fastener';
  return t || '—';
};

/** Item codes like COMP001-FT-000040 are fasteners even if master type is RAW_MATERIAL */
const isFastenerItemCode = (materialNumber) => {
  const s = String(materialNumber || '').toUpperCase();
  return /(?:^|[-_/])FT(?:[-_/]|$)/.test(s);
};

const isGrnStyleDisplayName = (displayName) => {
  const name = String(displayName || '');
  return /\d+\.?\d*\s*mm/i.test(name) && (name.includes('×') || name.includes('x'));
};

const materialCategoryDisplay = (materialType, materialNumber, materialDisplayName) => {
  const t = String(materialType || '').toUpperCase();
  if (t === 'CONSUMABLE' || t === 'PACKAGING') return 'Fastener';
  if (
    t === 'RAW_MATERIAL' &&
    !isGrnStyleDisplayName(materialDisplayName) &&
    isFastenerItemCode(materialNumber)
  ) {
    return 'Fastener';
  }
  return materialCategoryLabel(materialType);
};

const ledgerGroupKey = (row) => {
  if (row.material_issue_id) return `mi:${row.material_issue_id}`;
  if (row.material_receipt_line_id) return `rl:${row.material_receipt_line_id}`;
  if (row.route_card_operation_id) return `rcop:${row.route_card_operation_id}`;
  return `L:${row.id}`;
};

const formatSl = (loc) => {
  if (!loc) return '';
  const c = loc.storage_location_code || '';
  const d = loc.description || '';
  if (c && d) return `${c} — ${d}`;
  return c || d || '';
};

const trimQtyStr = (n) => {
  if (n == null || n === '') return null;
  const v = parseFloat(n);
  if (!Number.isFinite(v)) return null;
  const s = v.toFixed(4).replace(/\.?0+$/, '');
  return s || '0';
};

/** Match Material Issue / GRN nesting line: "1.6 mm × GI × 1200 × 2500" */
const formatGrnStyleMaterialName = (material, lineDescription) => {
  const raw = String(lineDescription || '').trim();
  if (
    raw &&
    (raw.includes('×') || raw.includes('x') || /\d+\.?\d*\s*mm/i.test(raw))
  ) {
    return raw;
  }
  const mat = material || {};
  const t = trimQtyStr(mat.thickness);
  const L = trimQtyStr(mat.length);
  const W = trimQtyStr(mat.width);
  const desc = String(mat.description || mat.material_number || '').trim();
  const parts = [];
  if (t != null) parts.push(`${t} mm`);
  if (desc) parts.push(desc);
  if (L != null && W != null) parts.push(`${L} × ${W}`);
  else if (L != null) parts.push(L);
  else if (W != null) parts.push(W);
  if (parts.length >= 2) return parts.join(' × ');
  return desc || raw || mat.material_number || '—';
};

/**
 * GET /production/wip-storage/ledger
 * Transaction ledger for Production Storage (WIP). Balance snapshot is per plant + material (shared pool).
 */
exports.getLedger = asyncHandler(async (req, res) => {
  const companyId = req.user?.companyId ?? req.user?.company_id;
  const plantId = req.query.plant_id != null && req.query.plant_id !== '' ? parseInt(req.query.plant_id, 10) : null;
  const productionOrderId =
    req.query.production_order_id != null && req.query.production_order_id !== ''
      ? parseInt(req.query.production_order_id, 10)
      : null;
  const materialId =
    req.query.material_id != null && req.query.material_id !== '' ? parseInt(req.query.material_id, 10) : null;
  const dateFrom = req.query.date_from ? String(req.query.date_from).slice(0, 10) : null;
  const dateTo = req.query.date_to ? String(req.query.date_to).slice(0, 10) : null;
  const materialType = req.query.material_type;
  const page = Math.max(parseInt(req.query.page, 10) || 1, 1);
  const limit = Math.min(Math.max(parseInt(req.query.limit, 10) || 50, 1), 200);

  if (companyId == null && !req.user?.isSuperAdmin && !req.user?.is_super_admin) {
    return sendError(res, 'Company context required', 400);
  }

  const where = {};
  if (companyId != null && !req.user?.isSuperAdmin && !req.user?.is_super_admin) {
    where.company_id = companyId;
  }
  if (Number.isFinite(plantId) && plantId > 0) {
    where.plant_id = plantId;
  }
  if (Number.isFinite(productionOrderId) && productionOrderId > 0) {
    where.production_order_id = productionOrderId;
  }
  if (Number.isFinite(materialId) && materialId > 0) {
    where.material_id = materialId;
  }
  if (dateFrom || dateTo) {
    where.created_at = {};
    if (dateFrom) where.created_at[Op.gte] = new Date(`${dateFrom}T00:00:00.000Z`);
    if (dateTo) where.created_at[Op.lte] = new Date(`${dateTo}T23:59:59.999Z`);
  }

  const matWhere = materialTypeFilterWhere(materialType);

  const rows = await db.ProductionWipLedger.findAll({
    where,
    include: [
      {
        model: db.ProductionOrder,
        as: 'productionOrder',
        attributes: ['id', 'production_order_number'],
        required: true,
      },
      {
        model: db.Material,
        as: 'material',
        attributes: ['id', 'material_number', 'description', 'material_type', 'thickness', 'length', 'width'],
        required: Boolean(matWhere),
        where: matWhere || undefined,
      },
      {
        model: db.UOM,
        as: 'uom',
        attributes: ['id', 'uom_code', 'uom_name'],
        required: false,
      },
      {
        model: db.RouteCardOperation,
        as: 'routeCardOperation',
        attributes: ['id', 'operation_sequence_no', 'operation_code', 'operation_name'],
        required: false,
        include: [
          {
            model: db.RouteCard,
            as: 'routeCard',
            attributes: ['id', 'route_card_number'],
            required: false,
          },
        ],
      },
      {
        model: db.MaterialIssue,
        as: 'materialIssue',
        attributes: ['id', 'issue_number'],
        required: false,
      },
      {
        model: db.ProductionOrderMaterialReceiptLine,
        as: 'materialReceiptLine',
        required: false,
        include: [
          {
            model: db.StorageLocation,
            as: 'fromStorageLocation',
            attributes: ['id', 'storage_location_code', 'description'],
            required: false,
          },
          {
            model: db.StorageLocation,
            as: 'toStorageLocation',
            attributes: ['id', 'storage_location_code', 'description'],
            required: false,
          },
        ],
      },
    ],
    order: [
      ['created_at', 'ASC'],
      ['id', 'ASC'],
    ],
    limit: MAX_LEDGER_ROWS + 1,
  });

  const truncated = rows.length > MAX_LEDGER_ROWS;
  const slice = truncated ? rows.slice(0, MAX_LEDGER_ROWS) : rows;

  const issueIds = [...new Set(slice.map((r) => r.material_issue_id).filter((id) => id > 0))];
  const issueItemMap = new Map();
  if (issueIds.length > 0) {
    const issues = await db.MaterialIssue.findAll({
      where: { id: { [Op.in]: issueIds } },
      include: [
        {
          model: db.MaterialIssueItem,
          as: 'items',
          attributes: ['material_id', 'source_storage_location', 'storage_location', 'description'],
        },
      ],
    });
    issues.forEach((iss) => {
      const items = iss.items || [];
      items.forEach((it) => {
        issueItemMap.set(`${iss.id}|${it.material_id}`, it);
      });
    });
  }

  let totalIn = 0;
  let totalOut = 0;
  const running = new Map();

  const enriched = slice.map((r) => {
    const j = typeof r.toJSON === 'function' ? r.toJSON() : r;
    const qty = round3(j.qty);
    const isIn = String(j.direction || '').toUpperCase() === 'IN';
    if (isIn) totalIn += qty;
    else totalOut += qty;

    const key = `${j.production_order_id}|${j.material_id}`;
    const prev = running.get(key) || 0;
    const delta = isIn ? qty : -qty;
    const runBal = round3(prev + delta);
    running.set(key, runBal);

    const issueItem =
      j.material_issue_id && j.material_id
        ? issueItemMap.get(`${j.material_issue_id}|${j.material_id}`)
        : null;
    const fromWh = j.materialReceiptLine
      ? formatSl(j.materialReceiptLine.fromStorageLocation)
      : issueItem?.source_storage_location || '';
    const toWh = j.materialReceiptLine
      ? formatSl(j.materialReceiptLine.toStorageLocation)
      : issueItem?.storage_location || '';

    let referenceNumber = '';
    if (j.materialIssue?.issue_number) referenceNumber = j.materialIssue.issue_number;
    else if (j.materialReceiptLine?.id) referenceNumber = `PO-recv line ${j.materialReceiptLine.id}`;
    else if (j.routeCardOperation?.routeCard?.route_card_number) {
      referenceNumber = `${j.routeCardOperation.routeCard.route_card_number}`;
    } else referenceNumber = `L${j.id}`;

    const opLabel = j.routeCardOperation
      ? `${j.routeCardOperation.routeCard?.route_card_number || 'RC'} · Seq ${j.routeCardOperation.operation_sequence_no} ${j.routeCardOperation.operation_code || ''}`.trim()
      : '';

    const materialName = formatGrnStyleMaterialName(j.material, issueItem?.description);

    return {
      id: j.id,
      created_at: j.created_at,
      production_order_id: j.production_order_id,
      production_order_number: j.productionOrder?.production_order_number || '',
      material_id: j.material_id,
      material_number: j.material?.material_number || '',
      material_name: materialName,
      material_type: j.material?.material_type || '',
      material_category: materialCategoryDisplay(
        j.material?.material_type,
        j.material?.material_number,
        materialName
      ),
      transaction_type: transactionTypeLabel(j.movement_type, j.direction),
      movement_type: j.movement_type,
      direction: j.direction,
      reference_number: referenceNumber,
      qty_in: isIn ? qty : 0,
      qty_out: isIn ? 0 : qty,
      uom_code: j.uom?.uom_code || '',
      running_balance: runBal,
      from_warehouse: fromWh || '—',
      to_warehouse: toWh || '—',
      operation: opLabel || '—',
      remarks: j.remarks || '',
      material_issue_id: j.material_issue_id || null,
      material_receipt_line_id: j.production_order_material_receipt_line_id || null,
      route_card_operation_id: j.route_card_operation_id || null,
    };
  });

  const groupOrder = [];
  const groupMap = new Map();
  for (const row of enriched) {
    const gk = ledgerGroupKey(row);
    if (!groupMap.has(gk)) {
      groupMap.set(gk, []);
      groupOrder.push(gk);
    }
    groupMap.get(gk).push(row);
  }

  const groups = groupOrder.map((gk) => {
    const lines = groupMap.get(gk) || [];
    const first = lines[0] || {};
    let tin = 0;
    let tout = 0;
    for (const ln of lines) {
      tin += round3(ln.qty_in || 0);
      tout += round3(ln.qty_out || 0);
    }
    return {
      group_id: gk,
      production_order_id: first.production_order_id,
      production_order_number: first.production_order_number || '',
      created_at: first.created_at,
      reference_number: first.reference_number || '',
      transaction_type: first.transaction_type || '',
      from_warehouse: first.from_warehouse || '—',
      to_warehouse: first.to_warehouse || '—',
      line_count: lines.length,
      total_qty_in: round3(tin),
      total_qty_out: round3(tout),
      lines,
    };
  });

  const totalGroups = groups.length;
  const start = (page - 1) * limit;
  const pageGroups = groups.slice(start, start + limit);

  const balWhere = {};
  if (companyId != null && !req.user?.isSuperAdmin && !req.user?.is_super_admin) {
    balWhere.company_id = companyId;
  }
  if (Number.isFinite(plantId) && plantId > 0) balWhere.plant_id = plantId;
  if (Number.isFinite(materialId) && materialId > 0) balWhere.material_id = materialId;

  const balanceRows = await db.ProductionWipBalance.findAll({
    where: balWhere,
    attributes: ['material_id', 'available_qty'],
  });
  let currentWipQty = 0;
  if (balanceRows.length === 0) {
    currentWipQty = 0;
  } else if (!matWhere) {
    currentWipQty = round3(balanceRows.reduce((s, b) => s + round3(b.available_qty), 0));
  } else {
    const mids = [...new Set(balanceRows.map((b) => b.material_id).filter((id) => id > 0))];
    const mats = await db.Material.findAll({
      where: { id: { [Op.in]: mids } },
      attributes: ['id', 'material_type'],
    });
    const typeById = new Map(mats.map((m) => [m.id, String(m.material_type || '').toUpperCase()]));
    for (const b of balanceRows) {
      const t = typeById.get(b.material_id) || '';
      let include = false;
      if (matWhere.material_type) include = t === String(matWhere.material_type).toUpperCase();
      else if (matWhere[Op.or]) include = t === 'CONSUMABLE' || t === 'PACKAGING';
      if (include) currentWipQty += round3(b.available_qty);
    }
    currentWipQty = round3(currentWipQty);
  }

  sendResponse(res, 200, true, 'Production Storage ledger loaded', {
    groups: pageGroups,
    pagination: {
      page,
      limit,
      total: totalGroups,
      totalPages: Math.ceil(totalGroups / limit) || 0,
    },
    summary: {
      total_qty_in: round3(totalIn),
      total_qty_out: round3(totalOut),
      net_movement: round3(totalIn - totalOut),
      current_wip_balance_sum: currentWipQty,
    },
    truncated,
    max_rows: MAX_LEDGER_ROWS,
  });
});
