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 { generateNextNumber } = require('../services/numberFormatService');
const fgStockLedgerService = require('../services/fgStockLedgerService');
const stockLedgerService = require('../services/stockLedgerService');

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

const generateFGInwardNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'PRODUCTION_FG_INWARD', fiscalYearId, transaction);
  } catch (e) {
    const year = new Date().getFullYear();
    const prefix = `FGI${year}`;
    const last = await db.ProductionFGInward.findOne({
      where: {
        company_id: companyId,
        fg_inward_number: { [Op.like]: `${prefix}%` }
      },
      attributes: ['fg_inward_number'],
      order: [['fg_inward_number', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    let seq = 1;
    if (last?.fg_inward_number) {
      const parsed = parseInt(String(last.fg_inward_number).slice(-4), 10) || 0;
      seq = parsed + 1;
    }
    return `${prefix}${String(seq).padStart(4, '0')}`;
  }
};

const inwardIncludes = [
  { model: db.ProductionPacking, as: 'packing', attributes: ['id', 'packing_number', 'packed_qty'] },
  { model: db.ProductionFGInward, as: 'referenceFGInward', attributes: ['id', 'fg_inward_number'], required: false },
  { model: db.RouteCard, as: 'routeCard', attributes: ['id', 'route_card_number'] },
  { model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number'] },
  { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'production_order_number'] },
  { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'], required: false },
  { model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description'], required: false },
  { model: db.StorageLocation, as: 'storageLocation', attributes: ['id', 'storage_location_code', 'description', 'storage_location_type'] }
];

exports.getAll = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, companyId, plantId, fiscalYearId, fromDate, toDate } = req.query;
  const { limit: limitNum, offset } = getPagination(page, limit);

  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 (fiscalYearId) where.fiscal_year_id = fiscalYearId;

  if (fromDate || toDate) {
    where.inward_entry_date = {};
    if (fromDate) where.inward_entry_date[Op.gte] = new Date(fromDate);
    if (toDate) {
      const dt = new Date(toDate);
      dt.setHours(23, 59, 59, 999);
      where.inward_entry_date[Op.lte] = dt;
    }
  }

  if (search) {
    where[Op.or] = [{ fg_inward_number: { [Op.like]: `%${search}%` } }];
  }

  const records = await db.ProductionFGInward.findAndCountAll({
    where,
    include: inwardIncludes,
    limit: limitNum,
    offset,
    order: [['id', 'DESC']]
  });

  const response = getPagingData(records, page, limitNum);
  sendResponse(res, 200, true, 'FG inward entries retrieved successfully', response);
});

exports.create = asyncHandler(async (req, res) => {
  const data = req.body || {};
  const userId = req.user?.userId || null;
  const reqCompanyId = req.user?.companyId || null;
  const packingId = parseInt(data.packing_id, 10);
  const storageLocationId = parseInt(data.storage_location_id, 10);
  const inwardQty = toNum(data.inward_entry_qty);
  if (!Number.isFinite(packingId) || packingId <= 0) return sendError(res, 'packing_id is required', 400);
  if (!Number.isFinite(storageLocationId) || storageLocationId <= 0) return sendError(res, 'storage_location_id is required', 400);
  if (inwardQty <= 0) return sendError(res, 'inward_entry_qty should be greater than 0', 400);

  const transaction = await db.sequelize.transaction();
  try {
    const packing = await db.ProductionPacking.findByPk(packingId, { transaction });
    if (!packing) throw new Error('Packing record not found');
    if (reqCompanyId && String(packing.company_id) !== String(reqCompanyId)) {
      throw new Error('Access denied for this packing record');
    }

    const packedQty = toNum(packing.packed_qty);
    if (packedQty <= 0) throw new Error('Packed qty is zero. FG inward cannot be created.');
    if (!packing.finished_material_id) throw new Error('Finished material is required for FG inward stock ledger');

    const storageLocation = await db.StorageLocation.findByPk(storageLocationId, { transaction });
    if (!storageLocation) throw new Error('Storage location not found');
    if (String(storageLocation.company_id) !== String(packing.company_id) || String(storageLocation.plant_id) !== String(packing.plant_id)) {
      throw new Error('Storage location does not belong to the same company/plant');
    }
    if (String(storageLocation.status || '').toUpperCase() !== 'ACTIVE') {
      throw new Error('Storage location must be ACTIVE');
    }
    if (String(storageLocation.storage_location_type || '').toUpperCase() !== 'WAREHOUSE') {
      throw new Error('Storage location must be of WAREHOUSE type');
    }

    const movedQtyRaw = await db.ProductionFGInward.sum('inward_entry_qty', {
      where: { packing_id: packing.id },
      transaction
    });
    const movedQtyBefore = toNum(movedQtyRaw);
    const availableQty = Math.max(packedQty - movedQtyBefore, 0);
    if (availableQty <= 0) throw new Error('No packed quantity available for FG inward');
    if (inwardQty > availableQty) {
      throw new Error(`Inward qty cannot be greater than available qty (${availableQty})`);
    }

    const statusScopeWhere = {
      company_id: packing.company_id,
      plant_id: packing.plant_id,
      fiscal_year_id: packing.fiscal_year_id,
      work_order_id: packing.work_order_id || null,
      route_card_id: packing.route_card_id || null,
      finished_material_id: packing.finished_material_id || null
    };

    const movedQtyScopeRaw = await db.ProductionFGInward.sum('inward_entry_qty', {
      where: statusScopeWhere,
      transaction
    });
    const movedQtyScopeBefore = toNum(movedQtyScopeRaw);

    const latestInward = await db.ProductionFGInward.findOne({
      where: statusScopeWhere,
      attributes: ['id', 'fg_inward_number'],
      order: [['id', 'DESC']],
      transaction
    });

    const movedQtyAfter = movedQtyScopeBefore + inwardQty;
    const targetInwardQty = Math.max(toNum(packing.accepted_qty), packedQty);
    const balanceInwardQty = Math.max(targetInwardQty - movedQtyAfter, 0);
    const inwardStatus = balanceInwardQty <= 0 ? 'FULLY_RECEIVED' : 'PARTIAL_RECEIVED';

    const routeCard = await db.RouteCard.findByPk(packing.route_card_id, {
      attributes: ['id', 'production_qty'],
      transaction
    });

    const created = await db.ProductionFGInward.create({
      company_id: packing.company_id,
      plant_id: packing.plant_id,
      fiscal_year_id: packing.fiscal_year_id,
      fg_inward_number: await generateFGInwardNumber(packing.company_id, packing.fiscal_year_id, transaction),
      packing_id: packing.id,
      reference_fg_inward_id: latestInward?.id || null,
      route_card_id: packing.route_card_id || null,
      work_order_id: packing.work_order_id || null,
      production_order_id: packing.production_order_id || null,
      customer_id: packing.customer_id || null,
      finished_material_id: packing.finished_material_id || null,
      storage_location_id: storageLocationId,
      production_qty: toNum(routeCard?.production_qty),
      accepted_qty: toNum(packing.accepted_qty),
      packed_qty: packedQty,
      inward_entry_qty: inwardQty,
      balance_inward_qty: balanceInwardQty,
      inward_entry_date: data.inward_entry_date ? new Date(data.inward_entry_date) : new Date(),
      status: inwardStatus,
      created_by: userId,
      updated_by: userId
    }, { transaction });

    const scopeRows = await db.ProductionFGInward.findAll({
      where: statusScopeWhere,
      attributes: ['id', 'inward_entry_qty', 'accepted_qty', 'packed_qty', 'status', 'balance_inward_qty'],
      order: [['id', 'ASC']],
      transaction
    });

    const scopeTargetQty = scopeRows.reduce((maxQty, row) => {
      const rowTarget = Math.max(toNum(row.accepted_qty), toNum(row.packed_qty));
      return Math.max(maxQty, rowTarget);
    }, 0);

    let runningInwardQty = 0;
    for (const row of scopeRows) {
      runningInwardQty += toNum(row.inward_entry_qty);
      const rowBalanceQty = Math.max(scopeTargetQty - runningInwardQty, 0);
      const rowStatus = rowBalanceQty <= 0 ? 'FULLY_RECEIVED' : 'PARTIAL_RECEIVED';

      if (toNum(row.balance_inward_qty) !== rowBalanceQty || String(row.status) !== rowStatus) {
        await row.update({
          balance_inward_qty: rowBalanceQty,
          status: rowStatus,
          updated_by: userId
        }, { transaction });
      }
    }

    await fgStockLedgerService.createEntry({
      company_id: packing.company_id,
      plant_id: packing.plant_id,
      fiscal_year_id: packing.fiscal_year_id,
      item_id: packing.finished_material_id,
      warehouse_id: storageLocationId,
      reference_type: 'FG_INWARD',
      reference_id: created.id,
      movement_date: data.inward_entry_date ? new Date(data.inward_entry_date) : new Date(),
      inward_qty: inwardQty,
      outward_qty: 0,
      created_by: userId
    }, transaction);

    // Mirror FG inward into Stores stock ledger so Stock Register/Store flows stay in sync.
    await stockLedgerService.createLedgerEntry({
      company_id: packing.company_id,
      plant_id: packing.plant_id,
      material_id: packing.finished_material_id,
      transaction_date: data.inward_entry_date ? new Date(data.inward_entry_date) : new Date(),
      transaction_type: 'RECEIPT',
      reference_type: 'PRODUCTION_RECEIPT',
      reference_id: created.id,
      reference_number: created.fg_inward_number,
      storage_location: storageLocation.storage_location_code || null,
      quantity_in: inwardQty,
      quantity_out: 0,
      remarks: `FG Inward: ${created.fg_inward_number}`,
      created_by: userId
    }, transaction);

    await transaction.commit();
    const record = await db.ProductionFGInward.findByPk(created.id, { include: inwardIncludes });
    sendResponse(res, 201, true, 'FG inward created successfully', record);
  } catch (error) {
    await transaction.rollback();
    const detail = error?.errors?.[0]?.message || error?.parent?.sqlMessage || error?.message;
    sendError(res, detail || 'Failed to create FG inward', 400);
  }
});
