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

exports.getAll = asyncHandler(async (req, res) => {
  const {
    page = 1,
    limit = 25,
    companyId,
    plantId,
    itemId,
    warehouseId,
    fiscalYearId,
    referenceType,
    referenceId,
    fromDate,
    toDate
  } = req.query;

  const { limit: limitNum, offset } = getPagination(page, limit);
  await fgStockLedgerService.assignMissingLedgerNumbers({
    companyId: companyId || req.user?.companyId
  });
  await fgStockLedgerService.recalculateBalances({
    companyId: companyId || req.user?.companyId,
    plantId: plantId || null,
    fiscalYearId: fiscalYearId || null,
    itemId: itemId || null,
    warehouseId: warehouseId || null
  });
  const records = await fgStockLedgerService.list({
    companyId: companyId || req.user?.companyId,
    plantId,
    fiscalYearId,
    itemId,
    warehouseId,
    referenceType,
    referenceId,
    fromDate,
    toDate,
    limit: limitNum,
    offset
  });

  let finalRecords = records;
  const allowAutoBackfill = String(req.query.autoBackfill || 'true').toLowerCase() !== 'false';
  if (
    allowAutoBackfill &&
    records.count === 0 &&
    (companyId || req.user?.companyId) &&
    !itemId &&
    !warehouseId &&
    !referenceType &&
    !referenceId
  ) {
    await fgStockLedgerService.backfillFromFGInward({
      companyId: companyId || req.user?.companyId,
      plantId: plantId || null,
      fiscalYearId: fiscalYearId || null,
      createdBy: req.user?.userId || null
    });
    finalRecords = await fgStockLedgerService.list({
      companyId: companyId || req.user?.companyId,
      plantId,
      fiscalYearId,
      itemId,
      warehouseId,
      referenceType,
      referenceId,
      fromDate,
      toDate,
      limit: limitNum,
      offset
    });
  }

  const response = getPagingData(finalRecords, page, limitNum);
  const rows = response.items || [];
  const fgInwardIds = rows.filter((r) => r.reference_type === 'FG_INWARD' && r.reference_id).map((r) => r.reference_id);
  const dispatchIds = rows.filter((r) => r.reference_type === 'DISPATCH' && r.reference_id).map((r) => r.reference_id);

  let inwardMap = new Map();
  let dispatchMap = new Map();

  if (fgInwardIds.length) {
    const inwardRows = await db.ProductionFGInward.findAll({
      where: { id: { [Op.in]: fgInwardIds } },
      attributes: ['id', 'fg_inward_number']
    });
    inwardMap = new Map(inwardRows.map((r) => [r.id, r.fg_inward_number]));
  }

  if (dispatchIds.length) {
    const dRows = await db.FGDispatch.findAll({
      where: { id: { [Op.in]: dispatchIds } },
      attributes: ['id', 'dispatch_no']
    });
    dispatchMap = new Map(dRows.map((r) => [r.id, r.dispatch_no]));
  }

  response.items = rows.map((r) => {
    let reference_number = '';
    if (r.reference_type === 'FG_INWARD') reference_number = inwardMap.get(r.reference_id) || '';
    else if (r.reference_type === 'DISPATCH') reference_number = dispatchMap.get(r.reference_id) || '';
    return { ...r.toJSON(), reference_number };
  });

  sendResponse(res, 200, true, 'FG stock ledger retrieved successfully', response);
});

exports.backfill = asyncHandler(async (req, res) => {
  const transaction = await db.sequelize.transaction();
  try {
    const result = await fgStockLedgerService.backfillFromFGInward({
      companyId: req.body?.companyId || req.user?.companyId,
      plantId: req.body?.plantId || null,
      fiscalYearId: req.body?.fiscalYearId || null,
      createdBy: req.user?.userId || null,
      transaction
    });
    await transaction.commit();
    sendResponse(res, 200, true, 'FG stock ledger backfill completed successfully', result);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

exports.backfillStoresLedger = asyncHandler(async (req, res) => {
  const transaction = await db.sequelize.transaction();
  try {
    const companyId = req.body?.companyId || req.user?.companyId;
    const plantId = req.body?.plantId || null;
    const fiscalYearId = req.body?.fiscalYearId || null;
    const createdBy = req.user?.userId || req.user?.id || null;

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

    const inwardWhere = {
      company_id: companyId,
      finished_material_id: { [Op.ne]: null },
      storage_location_id: { [Op.ne]: null }
    };
    if (plantId) inwardWhere.plant_id = plantId;
    if (fiscalYearId) inwardWhere.fiscal_year_id = fiscalYearId;

    const inwardRows = await db.ProductionFGInward.findAll({
      where: inwardWhere,
      attributes: [
        'id',
        'company_id',
        'plant_id',
        'finished_material_id',
        'storage_location_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
    });

    let inwardInserted = 0;
    let inwardSkipped = 0;
    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) {
        inwardSkipped += 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: parseFloat(row.inward_entry_qty || 0),
        quantity_out: 0,
        remarks: row.fg_inward_number ? `FG Inward: ${row.fg_inward_number}` : 'FG Inward',
        created_by: createdBy || row.created_by || null
      }, transaction);
      inwardInserted += 1;
    }

    const dispatchWhere = {
      company_id: companyId,
      item_id: { [Op.ne]: null },
      warehouse_id: { [Op.ne]: null },
      status: 'COMPLETED'
    };
    if (plantId) dispatchWhere.plant_id = plantId;
    if (fiscalYearId) dispatchWhere.fiscal_year_id = fiscalYearId;

    const dispatchRows = await db.FGDispatch.findAll({
      where: dispatchWhere,
      attributes: [
        'id',
        'company_id',
        'plant_id',
        'item_id',
        'warehouse_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
    });

    let dispatchInserted = 0;
    let dispatchSkipped = 0;
    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) {
        dispatchSkipped += 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: parseFloat(row.dispatch_qty || 0),
        remarks: row.dispatch_no ? `FG Dispatch: ${row.dispatch_no}` : 'FG Dispatch',
        created_by: createdBy || row.created_by || null
      }, transaction);
      dispatchInserted += 1;
    }

    // Ensure historical backfill updates running balances in proper date order.
    const recalc = await stockLedgerService.recalculateBalances({
      companyId,
      plantId: plantId || null,
      transaction
    });

    await transaction.commit();
    sendResponse(res, 200, true, 'Stores stock ledger backfill from FG inward/dispatch completed successfully', {
      inward: { total: inwardRows.length, inserted: inwardInserted, skipped: inwardSkipped },
      dispatch: { total: dispatchRows.length, inserted: dispatchInserted, skipped: dispatchSkipped },
      recalculate: recalc
    });
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});
