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 stockLedgerService = require('../services/stockLedgerService');

const generateTransferNumber = async (companyId) => {
  const today = new Date();
  const year = today.getFullYear();
  const prefix = `ST${year}`;
  
  const lastST = await db.InventoryStockTransfer.findOne({
    where: {
      company_id: companyId,
      transfer_number: { [Op.like]: `${prefix}%` }
    },
    order: [['transfer_number', 'DESC']]
  });

  let sequence = 1;
  if (lastST) {
    const lastSequence = parseInt(lastST.transfer_number.slice(-4)) || 0;
    sequence = lastSequence + 1;
  }

  return `${prefix}${sequence.toString().padStart(4, '0')}`;
};

exports.getAll = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, status, companyId, fiscalYearId } = 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 (fiscalYearId) {
    where.fiscal_year_id = fiscalYearId;
  }

  if (status) {
    where.status = status;
  }

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

  const records = await db.InventoryStockTransfer.findAndCountAll({
    where,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'fromPlant', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'toPlant', attributes: ['id', 'code', 'name'] }
    ],
    limit: limitNum,
    offset,
    order: [['transfer_date', 'DESC'], ['id', 'DESC']]
  });

  const response = getPagingData(records, page, limitNum);
  sendResponse(res, 200, true, 'Stock Transfers retrieved successfully', response);
});

exports.getById = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const st = await db.InventoryStockTransfer.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'fromPlant' },
      { model: db.Plant, as: 'toPlant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      {
        model: db.InventoryStockTransferItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' }
        ],
        order: [['line_number', 'ASC']]
      },
      { model: db.User, as: 'postedByUser', attributes: ['id', 'first_name', 'last_name'] },
      { model: db.User, as: 'createdByUser', attributes: ['id', 'first_name', 'last_name'] }
    ]
  });

  if (!st) {
    return sendError(res, 404, 'Stock Transfer not found');
  }

  sendResponse(res, 200, true, 'Stock Transfer retrieved successfully', st);
});

exports.create = asyncHandler(async (req, res) => {
  const transaction = await db.sequelize.transaction();
  
  try {
    const { items, ...stData } = req.body;

    // Basic validations
    if (!stData.company_id) return sendError(res, 400, 'Company is required');
    if (!stData.fiscal_year_id) return sendError(res, 400, 'Fiscal Year is required');
    if (!stData.transfer_date) return sendError(res, 400, 'Transfer Date is required');
    if (!stData.movement_type) stData.movement_type = '301';
    if (!stData.from_plant_id) return sendError(res, 400, 'From Plant is required');
    if (!stData.to_plant_id) return sendError(res, 400, 'To Plant is required');

    if (String(stData.movement_type) === '301') {
      if (String(stData.from_plant_id) === String(stData.to_plant_id)) {
        return sendError(res, 400, 'For movement type 301, To Plant must be different from From Plant');
      }
    }
    if (String(stData.movement_type) === '311') {
      if (String(stData.from_plant_id) !== String(stData.to_plant_id)) {
        return sendError(res, 400, 'For movement type 311, To Plant must be the same as From Plant');
      }
      if (!stData.from_storage_location) return sendError(res, 400, 'From Storage Location is required for movement type 311');
      if (!stData.to_storage_location) return sendError(res, 400, 'To Storage Location is required for movement type 311');
      if (String(stData.from_storage_location) === String(stData.to_storage_location)) {
        return sendError(res, 400, 'For movement type 311, To Storage Location must be different from From Storage Location');
      }
    }

    if (!Array.isArray(items) || items.length === 0) {
      return sendError(res, 400, 'At least one item is required');
    }
    
    if (!stData.transfer_number) {
      stData.transfer_number = await generateTransferNumber(stData.company_id || req.user.companyId);
    }

    stData.created_by = req.user.id;

    const st = await db.InventoryStockTransfer.create(stData, { transaction });

    if (items && items.length > 0) {
      const itemsData = items.map((item, index) => ({
        transfer_id: st.id,
        line_number: item.item_number || index + 1,
        material_id: item.material_id,
        description: item.description,
        quantity: item.quantity,
        uom_id: item.uom_id,
        unit_price: item.unit_price || 0,
        total_amount: (item.quantity || 0) * (item.unit_price || 0),
        batch_number: item.batch_number || null,
        remarks: item.remarks || null
      }));

      await db.InventoryStockTransferItem.bulkCreate(itemsData, { transaction });
    }

    await transaction.commit();

    const createdST = await db.InventoryStockTransfer.findByPk(st.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'fromPlant' },
        { model: db.Plant, as: 'toPlant' },
        {
          model: db.InventoryStockTransferItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 201, true, 'Stock Transfer created successfully', createdST);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

exports.update = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const transaction = await db.sequelize.transaction();
  
  try {
    const st = await db.InventoryStockTransfer.findByPk(id);
    
    if (!st) {
      await transaction.rollback();
      return sendError(res, 404, 'Stock Transfer not found');
    }

    if (st.status === 'POSTED') {
      await transaction.rollback();
      return sendError(res, 400, 'Cannot update posted Stock Transfer');
    }

    const { items, ...stData } = req.body;
    stData.updated_by = req.user.id;

    // Basic validations (same as create)
    if (!stData.company_id) return sendError(res, 400, 'Company is required');
    if (!stData.fiscal_year_id) return sendError(res, 400, 'Fiscal Year is required');
    if (!stData.transfer_date) return sendError(res, 400, 'Transfer Date is required');
    if (!stData.movement_type) stData.movement_type = '301';
    if (!stData.from_plant_id) return sendError(res, 400, 'From Plant is required');
    if (!stData.to_plant_id) return sendError(res, 400, 'To Plant is required');

    if (String(stData.movement_type) === '301') {
      if (String(stData.from_plant_id) === String(stData.to_plant_id)) {
        return sendError(res, 400, 'For movement type 301, To Plant must be different from From Plant');
      }
    }
    if (String(stData.movement_type) === '311') {
      if (String(stData.from_plant_id) !== String(stData.to_plant_id)) {
        return sendError(res, 400, 'For movement type 311, To Plant must be the same as From Plant');
      }
      if (!stData.from_storage_location) return sendError(res, 400, 'From Storage Location is required for movement type 311');
      if (!stData.to_storage_location) return sendError(res, 400, 'To Storage Location is required for movement type 311');
      if (String(stData.from_storage_location) === String(stData.to_storage_location)) {
        return sendError(res, 400, 'For movement type 311, To Storage Location must be different from From Storage Location');
      }
    }

    if (items && (!Array.isArray(items) || items.length === 0)) {
      return sendError(res, 400, 'At least one item is required');
    }

    await st.update(stData, { transaction });

    if (items) {
      await db.InventoryStockTransferItem.destroy({ where: { transfer_id: id }, transaction });

      if (items.length > 0) {
        const itemsData = items.map((item, index) => ({
          transfer_id: id,
          line_number: item.item_number || index + 1,
          material_id: item.material_id,
          description: item.description,
          quantity: item.quantity,
          uom_id: item.uom_id,
          unit_price: item.unit_price || 0,
          total_amount: (item.quantity || 0) * (item.unit_price || 0),
          batch_number: item.batch_number || null,
          remarks: item.remarks || null
        }));

        await db.InventoryStockTransferItem.bulkCreate(itemsData, { transaction });
      }
    }

    await transaction.commit();

    const updatedST = await db.InventoryStockTransfer.findByPk(id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'fromPlant' },
        { model: db.Plant, as: 'toPlant' },
        {
          model: db.InventoryStockTransferItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 200, true, 'Stock Transfer updated successfully', updatedST);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

exports.delete = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const st = await db.InventoryStockTransfer.findByPk(id);
  
  if (!st) {
    return sendError(res, 404, 'Stock Transfer not found');
  }

  if (st.status === 'POSTED') {
    return sendError(res, 400, 'Cannot delete posted Stock Transfer. Use status update to cancel.');
  }

  await db.InventoryStockTransferItem.destroy({ where: { transfer_id: id } });
  await st.update({ status: 'CANCELLED', updated_by: req.user.id });

  sendResponse(res, 200, true, 'Stock Transfer cancelled successfully');
});

exports.post = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const transaction = await db.sequelize.transaction();
  
  try {
    const st = await db.InventoryStockTransfer.findByPk(id, {
      include: [{ model: db.InventoryStockTransferItem, as: 'items' }]
    });
    
    if (!st) {
      await transaction.rollback();
      return sendError(res, 404, 'Stock Transfer not found');
    }

    if (st.status !== 'DRAFT') {
      await transaction.rollback();
      return sendError(res, 400, 'Only draft Stock Transfer can be posted');
    }

    if (!st.items || st.items.length === 0) {
      await transaction.rollback();
      return sendError(res, 400, 'Cannot post Stock Transfer without items');
    }

    // Movement-type validations before posting (defensive)
    if (String(st.movement_type) === '311') {
      if (String(st.from_plant_id) !== String(st.to_plant_id)) {
        await transaction.rollback();
        return sendError(res, 400, 'For movement type 311, From Plant and To Plant must be the same');
      }
      if (!st.from_storage_location || !st.to_storage_location) {
        await transaction.rollback();
        return sendError(res, 400, 'For movement type 311, From/To Storage Location are required');
      }
      if (String(st.from_storage_location) === String(st.to_storage_location)) {
        await transaction.rollback();
        return sendError(res, 400, 'For movement type 311, From/To Storage Location must be different');
      }
    }

    if (String(st.movement_type) === '301') {
      if (String(st.from_plant_id) === String(st.to_plant_id)) {
        await transaction.rollback();
        return sendError(res, 400, 'For movement type 301, From Plant and To Plant must be different');
      }
    }

    await st.update({
      status: 'POSTED',
      posted_at: new Date(),
      posted_by: req.user.id
    }, { transaction });

    // Update inventory stock ledger (transfer out from from_plant, transfer in to to_plant)
    // Note: storage locations are optional for 301; for 311 they are required.
    for (const item of st.items) {
      const qty = parseFloat(item.quantity || 0) || 0;
      if (qty <= 0) continue;

      const unitCost = parseFloat(item.unit_price || 0) || 0;
      const totalCost = qty * unitCost;

      await stockLedgerService.createLedgerEntry(
        {
          company_id: st.company_id,
          plant_id: st.from_plant_id,
          material_id: item.material_id,
          transaction_date: st.transfer_date,
          transaction_type: 'TRANSFER_OUT',
          reference_type: 'TRANSFER',
          reference_id: st.id,
          reference_number: st.transfer_number,
          batch_number: item.batch_number || null,
          storage_location: st.from_storage_location || null,
          quantity_in: 0,
          quantity_out: qty,
          unit_cost: unitCost,
          total_cost: totalCost,
          remarks: st.remarks || item.remarks || null,
          created_by: req.user.id,
        },
        transaction
      );

      await stockLedgerService.createLedgerEntry(
        {
          company_id: st.company_id,
          plant_id: st.to_plant_id,
          material_id: item.material_id,
          transaction_date: st.transfer_date,
          transaction_type: 'TRANSFER_IN',
          reference_type: 'TRANSFER',
          reference_id: st.id,
          reference_number: st.transfer_number,
          batch_number: item.batch_number || null,
          storage_location: st.to_storage_location || null,
          quantity_in: qty,
          quantity_out: 0,
          unit_cost: unitCost,
          total_cost: totalCost,
          remarks: st.remarks || item.remarks || null,
          created_by: req.user.id,
        },
        transaction
      );
    }

    await transaction.commit();

    const postedST = await db.InventoryStockTransfer.findByPk(id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'fromPlant' },
        { model: db.Plant, as: 'toPlant' },
        {
          model: db.InventoryStockTransferItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 200, true, 'Stock Transfer posted successfully', postedST);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

