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

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

const buildNestingDescriptionMap = async ({ companyId, plantId, materialIds = [] }) => {
  const map = new Map();
  if (!companyId || !plantId || !Array.isArray(materialIds) || materialIds.length === 0) {
    return map;
  }

  const poItems = await db.PurchaseOrderItem.findAll({
    where: {
      material_id: { [Op.in]: materialIds }
    },
    attributes: ['id', 'material_id', 'description'],
    include: [
      {
        model: db.PurchaseOrder,
        as: 'purchaseOrder',
        attributes: ['id', 'remarks'],
        required: true,
        where: {
          company_id: companyId,
          plant_id: plantId,
          status: { [Op.ne]: 'CANCELLED' }
        },
        include: [
          {
            model: db.PurchaseRequisition,
            as: 'purchaseRequisition',
            attributes: ['id', 'remarks'],
            required: false
          }
        ]
      }
    ],
    order: [['material_id', 'ASC'], ['id', 'DESC']]
  });

  poItems.forEach((row) => {
    const matId = row?.material_id;
    if (!matId || map.has(matId)) return;
    const desc = String(row?.description || '').trim();
    if (!desc) return;
    const poRemarks = String(row?.purchaseOrder?.remarks || '').toUpperCase();
    const prRemarks = String(row?.purchaseOrder?.purchaseRequisition?.remarks || '').toUpperCase();
    const isFromNesting = poRemarks.includes('FROM_NESTING') || prRemarks.includes('FROM_NESTING');
    if (isFromNesting) {
      map.set(matId, desc);
    }
  });

  return map;
};

async function backfillFGMovementsToStoresLedger({ companyId, plantId, materialIds = [], userId = null }) {
  if (!companyId || !plantId || !Array.isArray(materialIds) || materialIds.length === 0) {
    return { inserted: 0, skipped: 0 };
  }

  const transaction = await db.sequelize.transaction();
  try {
    let inserted = 0;
    let skipped = 0;

    const inwardRows = await db.ProductionFGInward.findAll({
      where: {
        company_id: companyId,
        plant_id: plantId,
        finished_material_id: { [Op.in]: materialIds },
        storage_location_id: { [Op.ne]: null }
      },
      attributes: [
        'id',
        'company_id',
        'plant_id',
        'finished_material_id',
        'inward_entry_qty',
        'inward_entry_date',
        'fg_inward_number',
        'created_by'
      ],
      include: [
        { model: db.StorageLocation, as: 'storageLocation', attributes: ['storage_location_code'], required: false }
      ],
      order: [['inward_entry_date', 'ASC'], ['id', 'ASC']],
      transaction
    });

    for (const row of inwardRows) {
      const exists = await db.InventoryStockLedger.findOne({
        where: {
          company_id: row.company_id,
          plant_id: row.plant_id,
          material_id: row.finished_material_id,
          transaction_type: 'RECEIPT',
          reference_type: 'PRODUCTION_RECEIPT',
          reference_id: row.id
        },
        attributes: ['id'],
        transaction
      });

      if (exists) {
        skipped += 1;
        continue;
      }

      await stockLedgerService.createLedgerEntry({
        company_id: row.company_id,
        plant_id: row.plant_id,
        material_id: row.finished_material_id,
        transaction_date: row.inward_entry_date || new Date(),
        transaction_type: 'RECEIPT',
        reference_type: 'PRODUCTION_RECEIPT',
        reference_id: row.id,
        reference_number: row.fg_inward_number || null,
        storage_location: row.storageLocation?.storage_location_code || null,
        quantity_in: toNum(row.inward_entry_qty),
        quantity_out: 0,
        remarks: row.fg_inward_number ? `FG Inward: ${row.fg_inward_number}` : 'FG Inward',
        created_by: userId || row.created_by || null
      }, transaction);
      inserted += 1;
    }

    const dispatchRows = await db.FGDispatch.findAll({
      where: {
        company_id: companyId,
        plant_id: plantId,
        item_id: { [Op.in]: materialIds },
        warehouse_id: { [Op.ne]: null },
        status: 'COMPLETED'
      },
      attributes: [
        'id',
        'company_id',
        'plant_id',
        'item_id',
        'dispatch_qty',
        'dispatch_date',
        'dispatch_no',
        'created_by'
      ],
      include: [
        { model: db.StorageLocation, as: 'warehouse', attributes: ['storage_location_code'], required: false }
      ],
      order: [['dispatch_date', 'ASC'], ['id', 'ASC']],
      transaction
    });

    for (const row of dispatchRows) {
      const exists = await db.InventoryStockLedger.findOne({
        where: {
          company_id: row.company_id,
          plant_id: row.plant_id,
          material_id: row.item_id,
          transaction_type: 'ISSUE',
          reference_type: 'OTHER',
          reference_id: row.id
        },
        attributes: ['id'],
        transaction
      });

      if (exists) {
        skipped += 1;
        continue;
      }

      await stockLedgerService.createLedgerEntry({
        company_id: row.company_id,
        plant_id: row.plant_id,
        material_id: row.item_id,
        transaction_date: row.dispatch_date || new Date(),
        transaction_type: 'ISSUE',
        reference_type: 'OTHER',
        reference_id: row.id,
        reference_number: row.dispatch_no || null,
        storage_location: row.warehouse?.storage_location_code || null,
        quantity_in: 0,
        quantity_out: toNum(row.dispatch_qty),
        remarks: row.dispatch_no ? `FG Dispatch: ${row.dispatch_no}` : 'FG Dispatch',
        created_by: userId || row.created_by || null
      }, transaction);
      inserted += 1;
    }

    await stockLedgerService.recalculateBalances({ companyId, plantId, transaction });
    await transaction.commit();
    return { inserted, skipped };
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}

/**
 * Create missing OPENING_BALANCE ledger rows from Material Master (material_plant)
 * so Stores Stock Register shows opening qty at the assigned storage location.
 * Idempotent: skips when an OPENING_BALANCE row already exists for material+plant.
 *
 * @param {number} companyId
 * @param {number} plantId
 * @param {number[]|null|undefined} materialIds - If a non-empty array, only those materials; otherwise all rows for the plant with opening > 0
 * @param {number|null} userId
 */
async function ensureOpeningBalanceLedgerFromMaterialMaster({
  companyId,
  plantId,
  materialIds = undefined,
  userId = null,
}) {
  if (!companyId || !plantId) {
    return { inserted: 0, skipped: 0 };
  }

  const mpWhere = {
    plant_id: plantId,
    opening_balance: { [Op.gt]: 0 },
  };
  if (Array.isArray(materialIds) && materialIds.length > 0) {
    mpWhere.material_id = { [Op.in]: materialIds };
  }

  const transaction = await db.sequelize.transaction();
  try {
    let inserted = 0;
    let skipped = 0;

    const mps = await db.MaterialPlant.findAll({
      where: mpWhere,
      include: [
        {
          model: db.Material,
          as: 'material',
          attributes: ['id', 'company_id', 'base_uom_id'],
          where: { company_id: companyId },
          required: true,
        },
      ],
      transaction,
    });

    if (mps.length === 0) {
      await transaction.commit();
      return { inserted: 0, skipped: 0 };
    }

    const existingRows = await db.InventoryStockLedger.findAll({
      where: {
        company_id: companyId,
        plant_id: plantId,
        transaction_type: 'OPENING_BALANCE',
        reference_type: 'OPENING_BALANCE',
      },
      attributes: ['material_id'],
      transaction,
    });
    const existingMaterialIds = new Set(existingRows.map((r) => r.material_id));

    for (const mp of mps) {
      if (existingMaterialIds.has(mp.material_id)) {
        skipped += 1;
        continue;
      }

      const openingBalance = parseFloat(mp.opening_balance || 0);
      if (openingBalance <= 0) continue;

      const unitCost = parseFloat(mp.opening_balance_unit_cost || 0);
      const openingBalanceDate =
        mp.opening_balance_date || new Date().toISOString().split('T')[0];

      await stockLedgerService.createLedgerEntry(
        {
          company_id: companyId,
          plant_id: plantId,
          material_id: mp.material_id,
          transaction_date: openingBalanceDate,
          transaction_type: 'OPENING_BALANCE',
          reference_type: 'OPENING_BALANCE',
          reference_id: mp.id,
          reference_number: `OB-${mp.id}`,
          storage_location: mp.storage_location || null,
          quantity_in: openingBalance,
          quantity_out: 0,
          unit_cost: unitCost,
          total_cost: openingBalance * unitCost,
          valuation_method: 'FIFO',
          remarks: 'Opening balance from Material Master (synced to stock register)',
          created_by: userId || null,
        },
        transaction
      );
      existingMaterialIds.add(mp.material_id);
      inserted += 1;
    }

    if (inserted > 0) {
      await stockLedgerService.recalculateBalances({ companyId, plantId, transaction });
    }

    await transaction.commit();
    return { inserted, skipped };
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}

/**
 * Get Stock Register - Materials grouped by material type with transaction history
 * 
 * Query Parameters:
 * - companyId: Company ID
 * - plantId: Plant ID
 * - materialType: Filter by material type (RAW_MATERIAL, SEMI_FINISHED, FINISHED_GOOD, etc.)
 * - materialId: Filter by specific material ID
 * - startDate: Start date for transaction history (YYYY-MM-DD)
 * - endDate: End date for transaction history (YYYY-MM-DD)
 * - storageLocation: Filter by storage location
 * - page: Page number (default: 1)
 * - limit: Items per page (default: 25)
 * - search: Search by material number or description
 */
exports.getStockRegister = asyncHandler(async (req, res) => {
  const {
    companyId: companyIdParam,
    plantId: plantIdParam,
    materialType,
    materialId,
    startDate,
    endDate,
    storageLocation,
    page = 1,
    limit = 25,
    search
  } = req.query;

  const companyId = companyIdParam || req.user?.companyId;
  const plantId = plantIdParam;

  if (!companyId) {
    return sendError(res, 'Company ID is required', 400);
  }

  if (!plantId) {
    return sendError(res, 'Plant ID is required', 400);
  }

  // Sync opening stock from Material Master for this plant for ALL materials (not only the current page).
  // Otherwise materials on other pages never get OPENING_BALANCE ledger rows and appear with no stock.
  await ensureOpeningBalanceLedgerFromMaterialMaster({
    companyId,
    plantId,
    userId: req.user?.userId || req.user?.id || null,
  });

  const { limit: limitNum, offset } = getPagination(page, limit);

  // Build material filter
  const materialWhere = {
    company_id: companyId,
    status: 'ACTIVE'
  };

  if (materialType) {
    materialWhere.material_type = materialType;
  }

  if (materialId) {
    materialWhere.id = materialId;
  }

  if (search) {
    materialWhere[Op.or] = [
      { material_number: { [Op.like]: `%${search}%` } },
      { description: { [Op.like]: `%${search}%` } }
    ];
  }

  // Get materials with their material type
  const materials = await db.Material.findAll({
    where: materialWhere,
    include: [
      {
        model: db.MaterialType,
        as: 'materialType',
        attributes: ['id', 'type_code', 'type_name']
      },
      {
        model: db.MaterialThickness,
        as: 'materialThickness',
        attributes: ['id', 'thickness_code', 'thickness_value', 'thickness_unit']
      },
      {
        model: db.MaterialGrade,
        as: 'materialGrade',
        attributes: ['id', 'grade_code', 'grade_name']
      },
      {
        model: db.UOM,
        as: 'weightUOM',
        attributes: ['id', 'uom_code', 'uom_name']
      },
      {
        model: db.UOM,
        as: 'baseUOM',
        attributes: ['id', 'uom_code', 'uom_name']
      }
    ],
    order: [['material_type', 'ASC'], ['material_number', 'ASC']],
    limit: limitNum,
    offset
  });

  // Get total count for pagination
  const totalMaterials = await db.Material.count({ where: materialWhere });

  // Build stock ledger filter
  const ledgerWhere = {
    company_id: companyId,
    plant_id: plantId
  };

  // Add material filter if materials exist
  if (materials.length > 0) {
    ledgerWhere.material_id = materialId ? materialId : { [Op.in]: materials.map(m => m.id) };
  }

  // Add date filters
  if (startDate) {
    ledgerWhere.transaction_date = { [Op.gte]: startDate };
  }

  if (endDate) {
    if (ledgerWhere.transaction_date) {
      ledgerWhere.transaction_date[Op.lte] = endDate;
    } else {
      ledgerWhere.transaction_date = { [Op.lte]: endDate };
    }
  }

  // Add storage location filter
  if (storageLocation) {
    ledgerWhere.storage_location = storageLocation;
  }

  // Get all stock ledger entries for these materials
  let stockLedgerEntries = await db.InventoryStockLedger.findAll({
    where: ledgerWhere,
    include: [
      {
        model: db.Material,
        as: 'material',
        attributes: ['id', 'material_number', 'description', 'material_type']
      },
      {
        model: db.User,
        as: 'createdByUser',
        attributes: ['id', 'username', 'first_name', 'last_name']
      }
    ],
    order: [['material_id', 'ASC'], ['transaction_date', 'ASC'], ['id', 'ASC']]
  });

  // Auto-heal historical FG movements into Stores ledger if missing.
  if (
    stockLedgerEntries.length === 0 &&
    materials.length > 0 &&
    !startDate &&
    !endDate
  ) {
    await backfillFGMovementsToStoresLedger({
      companyId,
      plantId,
      materialIds: materials.map((m) => m.id),
      userId: req.user?.userId || req.user?.id || null
    });

    stockLedgerEntries = await db.InventoryStockLedger.findAll({
      where: ledgerWhere,
      include: [
        {
          model: db.Material,
          as: 'material',
          attributes: ['id', 'material_number', 'description', 'material_type']
        },
        {
          model: db.User,
          as: 'createdByUser',
          attributes: ['id', 'username', 'first_name', 'last_name']
        }
      ],
      order: [['material_id', 'ASC'], ['transaction_date', 'ASC'], ['id', 'ASC']]
    });
  }

  // Group ledger entries by material
  const materialLedgerMap = new Map();
  stockLedgerEntries.forEach(entry => {
    const matId = entry.material_id;
    if (!materialLedgerMap.has(matId)) {
      materialLedgerMap.set(matId, []);
    }
    materialLedgerMap.get(matId).push(entry);
  });

  // Build response with materials and their transaction history
  const nestingDescriptionMap = await buildNestingDescriptionMap({
    companyId,
    plantId,
    materialIds: materials.map((m) => m.id)
  });

  const stockRegisterItems = materials.map(material => {
    const transactions = materialLedgerMap.get(material.id) || [];
    
    // Calculate opening balance (balance before startDate if provided)
    let openingBalance = 0;
    if (startDate) {
      const openingEntries = transactions.filter(t => 
        new Date(t.transaction_date) < new Date(startDate)
      );
      if (openingEntries.length > 0) {
        openingBalance = parseFloat(
          openingEntries[openingEntries.length - 1].balance_quantity || 0
        );
      } else {
        // Get the last balance before startDate from database
        // This is a simplified approach - in production, you might want to query separately
        openingBalance = 0;
      }
    } else {
      // If no startDate, opening balance is 0 (or first transaction's opening balance)
      if (transactions.length > 0) {
        // Find the earliest transaction and work backwards if needed
        openingBalance = 0; // Simplified - would need to query for actual opening balance
      }
    }

    // Calculate totals for the period
    const periodTransactions = startDate || endDate
      ? transactions.filter(t => {
          const tDate = new Date(t.transaction_date);
          const start = startDate ? new Date(startDate) : null;
          const end = endDate ? new Date(endDate) : null;
          return (!start || tDate >= start) && (!end || tDate <= end);
        })
      : transactions;

    const totalReceipts = periodTransactions
      .filter(
        (t) =>
          t.transaction_type === 'RECEIPT' ||
          t.transaction_type === 'TRANSFER_IN' ||
          t.transaction_type === 'RETURN' ||
          t.transaction_type === 'OPENING_BALANCE'
      )
      .reduce((sum, t) => sum + parseFloat(t.quantity_in || 0), 0);

    const totalIssues = periodTransactions
      .filter(t => t.transaction_type === 'ISSUE' || t.transaction_type === 'TRANSFER_OUT' || t.transaction_type === 'SCRAP')
      .reduce((sum, t) => sum + parseFloat(t.quantity_out || 0), 0);

    const totalAdjustments = periodTransactions
      .filter(t => t.transaction_type === 'ADJUSTMENT')
      .reduce((sum, t) => {
        const adjQty = parseFloat(t.quantity_in || 0) - parseFloat(t.quantity_out || 0);
        return sum + adjQty;
      }, 0);

    // Current balance (from last transaction or opening balance + receipts - issues + adjustments)
    let currentBalance = openingBalance + totalReceipts - totalIssues + totalAdjustments;
    if (transactions.length > 0 && !startDate && !endDate) {
      // Use the last transaction's balance if no date filter
      const lastTransaction = transactions[transactions.length - 1];
      currentBalance = parseFloat(lastTransaction.balance_quantity || 0);
    }

    // Format transactions for response
    const formattedTransactions = periodTransactions.map(t => ({
      id: t.id,
      transaction_date: t.transaction_date,
      transaction_type: t.transaction_type,
      reference_type: t.reference_type,
      reference_id: t.reference_id,
      reference_number: t.reference_number,
      quantity_in: parseFloat(t.quantity_in || 0),
      quantity_out: parseFloat(t.quantity_out || 0),
      balance_quantity: parseFloat(t.balance_quantity || 0),
      unit_cost: parseFloat(t.unit_cost || 0),
      total_cost: parseFloat(t.total_cost || 0),
      batch_number: t.batch_number,
      lot_number: t.lot_number,
      storage_location: t.storage_location,
      remarks: t.remarks,
      created_by: t.created_by,
      created_by_user: t.createdByUser ? {
        id: t.createdByUser.id,
        username: t.createdByUser.username,
        full_name: [t.createdByUser.first_name, t.createdByUser.last_name].filter(Boolean).join(' ') || t.createdByUser.username
      } : null
    }));

    return {
      material_id: material.id,
      material_number: material.material_number,
      description: nestingDescriptionMap.get(material.id) || material.description,
      material_type: material.material_type,
      material_type_name: material.materialType?.type_name || material.material_type,
      material_group: material.material_group,
      base_uom: material.baseUOM ? {
        id: material.baseUOM.id,
        uom_code: material.baseUOM.uom_code,
        uom_name: material.baseUOM.uom_name
      } : null,
      weight: material.net_weight || material.gross_weight,
      weight_uom: material.weightUOM ? {
        id: material.weightUOM.id,
        uom_code: material.weightUOM.uom_code
      } : null,
      thickness: material.materialThickness ? {
        value: material.materialThickness.thickness_value,
        unit: material.materialThickness.thickness_unit
      } : null,
      grade: material.materialGrade ? {
        code: material.materialGrade.grade_code,
        name: material.materialGrade.grade_name
      } : null,
      opening_balance: openingBalance,
      total_receipts: totalReceipts,
      total_issues: totalIssues,
      total_adjustments: totalAdjustments,
      current_balance: currentBalance,
      transaction_count: formattedTransactions.length,
      transactions: formattedTransactions
    };
  });

  // Group by material type for response
  const groupedByType = {};
  stockRegisterItems.forEach(item => {
    const type = item.material_type || 'OTHER';
    if (!groupedByType[type]) {
      groupedByType[type] = {
        material_type: type,
        material_type_name: item.material_type_name,
        items: []
      };
    }
    groupedByType[type].items.push(item);
  });

  const pagingData = getPagingData({ count: totalMaterials, rows: stockRegisterItems }, page, limitNum);
  
  const response = {
    items: stockRegisterItems, // Keep stockRegisterItems (processed items with transaction history)
    grouped_by_type: groupedByType,
    summary: {
      total_materials: totalMaterials,
      materials_in_period: stockRegisterItems.length,
      total_opening_balance: stockRegisterItems.reduce((sum, item) => sum + item.opening_balance, 0),
      total_receipts: stockRegisterItems.reduce((sum, item) => sum + item.total_receipts, 0),
      total_issues: stockRegisterItems.reduce((sum, item) => sum + item.total_issues, 0),
      total_adjustments: stockRegisterItems.reduce((sum, item) => sum + item.total_adjustments, 0),
      total_current_balance: stockRegisterItems.reduce((sum, item) => sum + item.current_balance, 0)
    },
    totalItems: pagingData.totalItems,
    totalPages: pagingData.totalPages,
    currentPage: pagingData.currentPage,
    itemsPerPage: pagingData.itemsPerPage
  };

  sendResponse(res, 200, true, 'Stock register retrieved successfully', response);
});

/**
 * Get Stock Register for a specific material with detailed transaction history
 */
exports.getMaterialStockRegister = asyncHandler(async (req, res) => {
  const { materialId } = req.params;
  const {
    companyId: companyIdParam,
    plantId: plantIdParam,
    startDate,
    endDate,
    storageLocation
  } = req.query;

  const companyId = companyIdParam || req.user?.companyId;
  const plantId = plantIdParam;

  if (!companyId || !plantId || !materialId) {
    return sendError(res, 'Company ID, Plant ID, and Material ID are required', 400);
  }

  // Get material details
  const material = await db.Material.findByPk(materialId, {
    include: [
      {
        model: db.MaterialType,
        as: 'materialType',
        attributes: ['id', 'type_code', 'type_name']
      },
      {
        model: db.MaterialThickness,
        as: 'materialThickness',
        attributes: ['id', 'thickness_code', 'thickness_value', 'thickness_unit']
      },
      {
        model: db.MaterialGrade,
        as: 'materialGrade',
        attributes: ['id', 'grade_code', 'grade_name']
      },
      {
        model: db.UOM,
        as: 'weightUOM',
        attributes: ['id', 'uom_code', 'uom_name']
      },
      {
        model: db.UOM,
        as: 'baseUOM',
        attributes: ['id', 'uom_code', 'uom_name']
      }
    ]
  });

  if (!material) {
    return sendError(res, 'Material not found', 404);
  }

  // Build stock ledger filter
  const ledgerWhere = {
    company_id: companyId,
    plant_id: plantId,
    material_id: materialId
  };

  if (startDate) {
    ledgerWhere.transaction_date = { [Op.gte]: startDate };
  }

  if (endDate) {
    if (ledgerWhere.transaction_date) {
      ledgerWhere.transaction_date[Op.lte] = endDate;
    } else {
      ledgerWhere.transaction_date = { [Op.lte]: endDate };
    }
  }

  if (storageLocation) {
    ledgerWhere.storage_location = storageLocation;
  }

  await ensureOpeningBalanceLedgerFromMaterialMaster({
    companyId,
    plantId,
    materialIds: [Number(materialId)],
    userId: req.user?.userId || req.user?.id || null,
  });

  // Get all stock ledger entries
  const transactions = await db.InventoryStockLedger.findAll({
    where: ledgerWhere,
    include: [
      {
        model: db.User,
        as: 'createdByUser',
        attributes: ['id', 'username', 'first_name', 'last_name']
      }
    ],
    order: [['transaction_date', 'ASC'], ['id', 'ASC']]
  });

  // Calculate opening balance
  let openingBalance = 0;
  if (startDate) {
    const openingEntry = await db.InventoryStockLedger.findOne({
      where: {
        company_id: companyId,
        plant_id: plantId,
        material_id: materialId,
        transaction_date: { [Op.lt]: startDate }
      },
      order: [['transaction_date', 'DESC'], ['id', 'DESC']]
    });
    if (openingEntry) {
      openingBalance = parseFloat(openingEntry.balance_quantity || 0);
    }
  }

  // Calculate totals
  const totalReceipts = transactions
    .filter(
      (t) =>
        t.transaction_type === 'RECEIPT' ||
        t.transaction_type === 'TRANSFER_IN' ||
        t.transaction_type === 'RETURN' ||
        t.transaction_type === 'OPENING_BALANCE'
    )
    .reduce((sum, t) => sum + parseFloat(t.quantity_in || 0), 0);

  const totalIssues = transactions
    .filter(t => t.transaction_type === 'ISSUE' || t.transaction_type === 'TRANSFER_OUT' || t.transaction_type === 'SCRAP')
    .reduce((sum, t) => sum + parseFloat(t.quantity_out || 0), 0);

  const totalAdjustments = transactions
    .filter(t => t.transaction_type === 'ADJUSTMENT')
    .reduce((sum, t) => {
      const adjQty = parseFloat(t.quantity_in || 0) - parseFloat(t.quantity_out || 0);
      return sum + adjQty;
    }, 0);

  const currentBalance = openingBalance + totalReceipts - totalIssues + totalAdjustments;

  // Format transactions
  const formattedTransactions = transactions.map(t => ({
    id: t.id,
    transaction_date: t.transaction_date,
    transaction_type: t.transaction_type,
    reference_type: t.reference_type,
    reference_id: t.reference_id,
    reference_number: t.reference_number,
    quantity_in: parseFloat(t.quantity_in || 0),
    quantity_out: parseFloat(t.quantity_out || 0),
    balance_quantity: parseFloat(t.balance_quantity || 0),
    unit_cost: parseFloat(t.unit_cost || 0),
    total_cost: parseFloat(t.total_cost || 0),
    batch_number: t.batch_number,
    lot_number: t.lot_number,
    storage_location: t.storage_location,
    remarks: t.remarks,
    created_by: t.created_by,
    created_by_user: t.createdByUser ? {
      id: t.createdByUser.id,
      username: t.createdByUser.username,
      full_name: t.createdByUser.full_name
    } : null
  }));

  const response = {
    material: {
      id: material.id,
      material_number: material.material_number,
      description: (
        await buildNestingDescriptionMap({
          companyId,
          plantId,
          materialIds: [material.id]
        })
      ).get(material.id) || material.description,
      material_type: material.material_type,
      material_type_name: material.materialType?.type_name || material.material_type,
      base_uom: material.baseUOM ? {
        id: material.baseUOM.id,
        uom_code: material.baseUOM.uom_code,
        uom_name: material.baseUOM.uom_name
      } : null,
      weight: material.net_weight || material.gross_weight,
      weight_uom: material.weightUOM ? {
        id: material.weightUOM.id,
        uom_code: material.weightUOM.uom_code
      } : null,
      thickness: material.materialThickness ? {
        value: material.materialThickness.thickness_value,
        unit: material.materialThickness.thickness_unit
      } : null,
      grade: material.materialGrade ? {
        code: material.materialGrade.grade_code,
        name: material.materialGrade.grade_name
      } : null
    },
    opening_balance: openingBalance,
    total_receipts: totalReceipts,
    total_issues: totalIssues,
    total_adjustments: totalAdjustments,
    current_balance: currentBalance,
    transaction_count: formattedTransactions.length,
    transactions: formattedTransactions
  };

  sendResponse(res, 200, true, 'Material stock register retrieved successfully', response);
});

