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

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

const generateSalesReturnNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'SALES_RETURN', fiscalYearId, transaction);
  } catch (err) {
    const year = new Date().getFullYear();
    const prefix = `SRTN${year}`;
    const last = await db.SalesReturn.findOne({
      where: { company_id: companyId, return_number: { [Op.like]: `${prefix}%` } },
      order: [['return_number', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    const seq = last ? ((parseInt(String(last.return_number).slice(-4), 10) || 0) + 1) : 1;
    return `${prefix}${String(seq).padStart(4, '0')}`;
  }
};

const getReturnedMap = async (invoiceId, transaction = null, excludeSalesReturnId = null) => {
  const where = {
    invoice_id: invoiceId,
    status: { [Op.in]: ['DRAFT', 'APPROVED'] }
  };
  if (excludeSalesReturnId) where.id = { [Op.ne]: excludeSalesReturnId };

  const salesReturns = await db.SalesReturn.findAll({
    where,
    attributes: ['id'],
    raw: true,
    transaction: transaction || undefined
  });
  const ids = salesReturns.map((r) => r.id);
  if (ids.length === 0) return new Map();

  const rows = await db.SalesReturnItem.findAll({
    where: { sales_return_id: { [Op.in]: ids } },
    attributes: ['invoice_item_id', [fn('SUM', col('return_quantity')), 'returned_qty']],
    group: ['invoice_item_id'],
    raw: true,
    transaction: transaction || undefined
  });

  const map = new Map();
  rows.forEach((r) => map.set(Number(r.invoice_item_id), toNum(r.returned_qty)));
  return map;
};

const buildPrefill = async ({
  invoiceId,
  companyId,
  plantId,
  fiscalYearId,
  excludeSalesReturnId = null,
  transaction = null
}) => {
  const invoice = await db.SalesInvoice.findOne({
    where: {
      id: invoiceId,
      company_id: companyId,
      plant_id: plantId,
      fiscal_year_id: fiscalYearId,
      status: { [Op.ne]: 'CANCELLED' }
    },
    include: [
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'name', 'legal_name'] },
      { model: db.SalesOrder, as: 'salesOrder', attributes: ['id', 'so_number'], required: false },
      {
        model: db.SalesInvoiceItem,
        as: 'items',
        attributes: ['id', 'item_number', 'material_id', 'uom_id', 'quantity', 'description', 'unit_price', 'tax_rate'],
        include: [
          { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
          { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'] }
        ]
      }
    ],
    order: [[{ model: db.SalesInvoiceItem, as: 'items' }, 'item_number', 'ASC']],
    transaction: transaction || undefined
  });

  if (!invoice) {
    throw new Error('Sales invoice not found for selected company/plant/fiscal year');
  }

  const returnedMap = await getReturnedMap(invoice.id, transaction, excludeSalesReturnId);

  const dispatchRows = await db.FGDispatch.findAll({
    where: {
      invoice_id: invoice.id,
      status: { [Op.ne]: 'CANCELLED' }
    },
    attributes: ['id', 'item_id', 'warehouse_id', 'dispatch_date'],
    include: [
      { model: db.StorageLocation, as: 'warehouse', attributes: ['id', 'storage_location_code', 'description'], required: false }
    ],
    order: [['dispatch_date', 'DESC'], ['id', 'DESC']],
    transaction: transaction || undefined
  });

  const warehouseByItem = new Map();
  let fallbackWarehouse = null;
  dispatchRows.forEach((row) => {
    const matId = parseInt(row.item_id, 10);
    const whId = parseInt(row.warehouse_id, 10);
    if (Number.isFinite(matId) && matId > 0 && Number.isFinite(whId) && whId > 0 && !warehouseByItem.has(matId)) {
      warehouseByItem.set(matId, {
        warehouse_id: whId,
        warehouse_name: `${row.warehouse?.storage_location_code || ''}${row.warehouse?.description ? ` - ${row.warehouse.description}` : ''}`.trim()
      });
    }
    if (!fallbackWarehouse && Number.isFinite(whId) && whId > 0) {
      fallbackWarehouse = {
        warehouse_id: whId,
        warehouse_name: `${row.warehouse?.storage_location_code || ''}${row.warehouse?.description ? ` - ${row.warehouse.description}` : ''}`.trim()
      };
    }
  });

  const items = (invoice.items || []).map((it) => {
    const soldQty = toNum(it.quantity);
    const alreadyReturned = returnedMap.get(Number(it.id)) || 0;
    const balanceQty = Math.max(0, soldQty - alreadyReturned);
    const wh = warehouseByItem.get(Number(it.material_id)) || fallbackWarehouse || {};
    return {
      invoice_item_id: it.id,
      line_number: it.item_number,
      material_id: it.material_id,
      uom_id: it.uom_id,
      item_code: it.material?.material_number || '',
      item_name: it.material?.description || it.description || '',
      sold_quantity: soldQty,
      already_returned_quantity: alreadyReturned,
      balance_return_quantity: balanceQty,
      unit_price: toNum(it.unit_price),
      tax_rate: toNum(it.tax_rate),
      warehouse_id: wh.warehouse_id || null,
      warehouse_name: wh.warehouse_name || ''
    };
  });

  const fallbackWarehouseId = items.find((it) => Number(it.warehouse_id) > 0)?.warehouse_id || null;
  const fallbackWarehouseName = items.find((it) => Number(it.warehouse_id) > 0)?.warehouse_name || '';

  return {
    invoice_id: invoice.id,
    invoice_number: invoice.invoice_number,
    invoice_date: invoice.invoice_date,
    customer_id: invoice.customer_id,
    customer_name: invoice.customer?.name || invoice.customer?.legal_name || '',
    so_id: invoice.so_id || null,
    so_number: invoice.salesOrder?.so_number || '',
    warehouse_id: fallbackWarehouseId,
    warehouse_name: fallbackWarehouseName,
    items
  };
};

exports.getInvoiceOptions = asyncHandler(async (req, res) => {
  const { search = '' } = req.query;
  const companyId = parseInt(req.query.companyId || req.user?.companyId, 10);
  const plantId = parseInt(req.query.plantId, 10);
  const fiscalYearId = parseInt(req.query.fiscalYearId, 10);

  const buildWhere = ({ usePlant = true, useFiscal = true } = {}) => {
    const where = { status: { [Op.ne]: 'CANCELLED' } };
    if (companyId > 0) where.company_id = companyId;
    if (usePlant && plantId > 0) where.plant_id = plantId;
    if (useFiscal && fiscalYearId > 0) where.fiscal_year_id = fiscalYearId;
    if (search) {
      where[Op.or] = [{ invoice_number: { [Op.like]: `%${search}%` } }];
    }
    return where;
  };

  const fetchRows = (where) => db.SalesInvoice.findAll({
    where,
    attributes: ['id', 'invoice_number', 'invoice_date', 'customer_id', 'so_id'],
    include: [
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'name', 'legal_name'] },
      { model: db.SalesOrder, as: 'salesOrder', attributes: ['id', 'so_number'], required: false },
      { model: db.SalesInvoiceItem, as: 'items', attributes: ['id', 'quantity'], required: false },
      {
        model: db.DeliveryChallan,
        as: 'deliveryChallans',
        attributes: ['id', 'challan_no', 'status', 'delivery_status'],
        required: true,
        where: {
          [Op.or]: [
            { delivery_status: 'DELIVERED' },
            { status: { [Op.in]: ['DELIVERED', 'CLOSED'] } }
          ]
        }
      }
    ],
    order: [['invoice_date', 'DESC'], ['id', 'DESC']],
    distinct: true,
    limit: 200
  });

  // Primary: company + plant + fiscal year
  let rows = await fetchRows(buildWhere({ usePlant: true, useFiscal: true }));
  // Fallback: company + plant (ignore fiscal) - helps when context FY changed
  if (!rows.length && fiscalYearId > 0) {
    rows = await fetchRows(buildWhere({ usePlant: true, useFiscal: false }));
  }
  // Fallback: company only - helps when invoice created in another plant
  if (!rows.length && plantId > 0) {
    rows = await fetchRows(buildWhere({ usePlant: false, useFiscal: false }));
  }

  sendResponse(res, 200, true, 'Sales invoice options fetched successfully', rows);
});

exports.getInvoicePrefill = asyncHandler(async (req, res) => {
  const invoiceId = parseInt(req.params.invoiceId, 10);
  const companyId = parseInt(req.query.companyId || req.user?.companyId, 10);
  const plantId = parseInt(req.query.plantId, 10);
  const fiscalYearId = parseInt(req.query.fiscalYearId, 10);
  const excludeSalesReturnId = parseInt(req.query.excludeSalesReturnId, 10);

  if (!(invoiceId > 0) || !(companyId > 0) || !(plantId > 0) || !(fiscalYearId > 0)) {
    return sendError(res, 'invoiceId, companyId, plantId and fiscalYearId are required', 400);
  }

  const data = await buildPrefill({
    invoiceId,
    companyId,
    plantId,
    fiscalYearId,
    excludeSalesReturnId: Number.isFinite(excludeSalesReturnId) && excludeSalesReturnId > 0
      ? excludeSalesReturnId
      : null
  });
  sendResponse(res, 200, true, 'Sales return prefill loaded successfully', data);
});

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

  const where = {};
  where.company_id = companyId || req.user?.companyId;
  if (plantId) where.plant_id = plantId;
  if (fiscalYearId) where.fiscal_year_id = fiscalYearId;
  if (status) where.status = status;
  if (search) {
    where[Op.or] = [
      { return_number: { [Op.like]: `%${search}%` } },
      { '$salesInvoice.invoice_number$': { [Op.like]: `%${search}%` } }
    ];
  }

  const result = await db.SalesReturn.findAndCountAll({
    where,
    include: [
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'name', 'legal_name'], required: false },
      { model: db.SalesInvoice, as: 'salesInvoice', attributes: ['id', 'invoice_number', 'invoice_date'], required: false },
      { model: db.SalesOrder, as: 'salesOrder', attributes: ['id', 'so_number'], required: false },
      { model: db.StorageLocation, as: 'warehouse', attributes: ['id', 'storage_location_code', 'description'], required: false },
      { model: db.SalesReturnItem, as: 'items', attributes: ['id', 'return_quantity'], required: false }
    ],
    order: [['return_date', 'DESC'], ['id', 'DESC']],
    distinct: true,
    limit: pageSize,
    offset
  });

  sendResponse(res, 200, true, 'Sales returns fetched successfully', getPagingData(result, page, pageSize));
});

exports.getById = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const row = await db.SalesReturn.findByPk(id, {
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.FiscalYear, as: 'fiscalYear', attributes: ['id', 'fiscal_year'] },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'name', 'legal_name'] },
      { model: db.SalesInvoice, as: 'salesInvoice', attributes: ['id', 'invoice_number', 'invoice_date'] },
      { model: db.SalesOrder, as: 'salesOrder', attributes: ['id', 'so_number'] },
      { model: db.StorageLocation, as: 'warehouse', attributes: ['id', 'storage_location_code', 'description'] },
      {
        model: db.SalesReturnItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] },
          { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'] },
          { model: db.SalesInvoiceItem, as: 'invoiceItem', attributes: ['id', 'item_number', 'quantity'] },
          { model: db.StorageLocation, as: 'warehouse', attributes: ['id', 'storage_location_code', 'description'] }
        ]
      }
    ],
    order: [[{ model: db.SalesReturnItem, as: 'items' }, 'line_number', 'ASC']]
  });
  if (!row) return sendError(res, 'Sales return not found', 404);
  sendResponse(res, 200, true, 'Sales return fetched successfully', row);
});

exports.create = asyncHandler(async (req, res) => {
  const userId = req.user?.userId || req.user?.id || null;
  const data = req.body || {};
  const items = Array.isArray(data.items) ? data.items : [];

  const companyId = parseInt(data.company_id || req.user?.companyId, 10);
  const plantId = parseInt(data.plant_id, 10);
  const fiscalYearId = parseInt(data.fiscal_year_id, 10);
  const invoiceId = parseInt(data.invoice_id, 10);
  const returnDate = data.return_date;

  if (!(companyId > 0) || !(plantId > 0) || !(fiscalYearId > 0) || !(invoiceId > 0) || !returnDate) {
    return sendError(res, 'company_id, plant_id, fiscal_year_id, invoice_id and return_date are required', 400);
  }
  if (!items.length) return sendError(res, 'At least one return item is required', 400);

  const transaction = await db.sequelize.transaction();
  try {
    const prefill = await buildPrefill({ invoiceId, companyId, plantId, fiscalYearId, transaction });
    const prefillMap = new Map(prefill.items.map((it) => [Number(it.invoice_item_id), it]));
    const normalizedItems = [];

    for (let i = 0; i < items.length; i += 1) {
      const line = items[i] || {};
      const invoiceItemId = parseInt(line.invoice_item_id, 10);
      const returnQty = toNum(line.return_quantity);
      if (!(invoiceItemId > 0) || returnQty <= 0) continue;

      const pre = prefillMap.get(invoiceItemId);
      if (!pre) throw new Error(`Invalid invoice item selected at row ${i + 1}`);
      if (returnQty > toNum(pre.balance_return_quantity)) {
        throw new Error(`Return quantity cannot exceed balance for item ${pre.item_code || pre.item_name}. Allowed: ${toNum(pre.balance_return_quantity).toFixed(3)}`);
      }

      normalizedItems.push({
        line_number: normalizedItems.length + 1,
        invoice_item_id: invoiceItemId,
        material_id: pre.material_id,
        uom_id: pre.uom_id || null,
        sold_quantity: toNum(pre.sold_quantity),
        already_returned_quantity: toNum(pre.already_returned_quantity),
        return_quantity: returnQty,
        return_reason: line.return_reason || null,
        warehouse_id: line.warehouse_id || pre.warehouse_id || data.warehouse_id || null,
        remarks: line.remarks || null
      });
    }

    if (!normalizedItems.length) throw new Error('Please enter at least one valid return quantity');

    const returnNumber = await generateSalesReturnNumber(companyId, fiscalYearId, transaction);
    const created = await db.SalesReturn.create({
      company_id: companyId,
      plant_id: plantId,
      fiscal_year_id: fiscalYearId,
      return_number: returnNumber,
      return_date: returnDate,
      customer_id: prefill.customer_id,
      invoice_id: invoiceId,
      so_id: prefill.so_id || null,
      warehouse_id: data.warehouse_id || prefill.warehouse_id || null,
      return_reason: data.return_reason || null,
      remarks: data.remarks || null,
      status: 'DRAFT',
      created_by: userId,
      updated_by: userId
    }, { transaction });

    await db.SalesReturnItem.bulkCreate(
      normalizedItems.map((it) => ({ ...it, sales_return_id: created.id })),
      { transaction }
    );

    await transaction.commit();
    const fresh = await db.SalesReturn.findByPk(created.id, { include: [{ model: db.SalesReturnItem, as: 'items' }] });
    sendResponse(res, 201, true, 'Sales return created successfully', fresh);
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message || 'Failed to create sales return', 400);
  }
});

exports.update = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const userId = req.user?.userId || req.user?.id || null;
  const data = req.body || {};
  const items = Array.isArray(data.items) ? data.items : [];
  const existing = await db.SalesReturn.findByPk(id);

  if (!existing) return sendError(res, 'Sales return not found', 404);
  if (existing.status !== 'DRAFT') return sendError(res, 'Only DRAFT sales return can be updated', 400);
  if (!items.length) return sendError(res, 'At least one return item is required', 400);

  const companyId = parseInt(data.company_id || existing.company_id, 10);
  const plantId = parseInt(data.plant_id || existing.plant_id, 10);
  const fiscalYearId = parseInt(data.fiscal_year_id || existing.fiscal_year_id, 10);
  const invoiceId = parseInt(data.invoice_id || existing.invoice_id, 10);

  const transaction = await db.sequelize.transaction();
  try {
    const prefill = await buildPrefill({
      invoiceId,
      companyId,
      plantId,
      fiscalYearId,
      excludeSalesReturnId: id,
      transaction
    });
    const prefillMap = new Map(prefill.items.map((it) => [Number(it.invoice_item_id), it]));
    const normalizedItems = [];

    for (let i = 0; i < items.length; i += 1) {
      const line = items[i] || {};
      const invoiceItemId = parseInt(line.invoice_item_id, 10);
      const returnQty = toNum(line.return_quantity);
      if (!(invoiceItemId > 0) || returnQty <= 0) continue;

      const pre = prefillMap.get(invoiceItemId);
      if (!pre) throw new Error(`Invalid invoice item selected at row ${i + 1}`);
      if (returnQty > toNum(pre.balance_return_quantity)) {
        throw new Error(`Return quantity cannot exceed balance for item ${pre.item_code || pre.item_name}. Allowed: ${toNum(pre.balance_return_quantity).toFixed(3)}`);
      }

      normalizedItems.push({
        line_number: normalizedItems.length + 1,
        invoice_item_id: invoiceItemId,
        material_id: pre.material_id,
        uom_id: pre.uom_id || null,
        sold_quantity: toNum(pre.sold_quantity),
        already_returned_quantity: toNum(pre.already_returned_quantity),
        return_quantity: returnQty,
        return_reason: line.return_reason || null,
        warehouse_id: line.warehouse_id || pre.warehouse_id || data.warehouse_id || null,
        remarks: line.remarks || null
      });
    }

    if (!normalizedItems.length) throw new Error('Please enter at least one valid return quantity');

    await existing.update({
      return_date: data.return_date || existing.return_date,
      customer_id: prefill.customer_id,
      invoice_id: invoiceId,
      so_id: prefill.so_id || null,
      warehouse_id: data.warehouse_id || prefill.warehouse_id || null,
      return_reason: data.return_reason || null,
      remarks: data.remarks || null,
      updated_by: userId
    }, { transaction });

    await db.SalesReturnItem.destroy({ where: { sales_return_id: id }, transaction });
    await db.SalesReturnItem.bulkCreate(
      normalizedItems.map((it) => ({ ...it, sales_return_id: id })),
      { transaction }
    );

    await transaction.commit();
    const fresh = await db.SalesReturn.findByPk(id, { include: [{ model: db.SalesReturnItem, as: 'items' }] });
    sendResponse(res, 200, true, 'Sales return updated successfully', fresh);
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message || 'Failed to update sales return', 400);
  }
});

exports.approve = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const userId = req.user?.userId || req.user?.id || null;

  const salesReturn = await db.SalesReturn.findByPk(id, {
    include: [
      { model: db.SalesReturnItem, as: 'items' },
      { model: db.SalesInvoice, as: 'salesInvoice', attributes: ['id', 'invoice_number'] }
    ]
  });
  if (!salesReturn) return sendError(res, 'Sales return not found', 404);
  if (salesReturn.status !== 'DRAFT') return sendError(res, 'Only DRAFT sales return can be approved', 400);

  const transaction = await db.sequelize.transaction();
  try {
    const prefill = await buildPrefill({
      invoiceId: salesReturn.invoice_id,
      companyId: salesReturn.company_id,
      plantId: salesReturn.plant_id,
      fiscalYearId: salesReturn.fiscal_year_id,
      excludeSalesReturnId: id,
      transaction
    });
    const prefillMap = new Map(prefill.items.map((it) => [Number(it.invoice_item_id), it]));

    for (const row of (salesReturn.items || [])) {
      const pre = prefillMap.get(Number(row.invoice_item_id));
      const qty = toNum(row.return_quantity);
      if (!pre || qty <= 0) continue;
      if (qty > toNum(pre.balance_return_quantity)) {
        throw new Error(`Return quantity exceeds current balance for item ${pre.item_code || pre.item_name}. Allowed now: ${toNum(pre.balance_return_quantity).toFixed(3)}`);
      }

      const warehouseId = row.warehouse_id || salesReturn.warehouse_id || pre.warehouse_id || null;
      if (!(parseInt(warehouseId, 10) > 0)) {
        throw new Error(`Warehouse is required for item ${pre.item_code || pre.item_name}`);
      }

      await fgStockLedgerService.createEntry({
        company_id: salesReturn.company_id,
        plant_id: salesReturn.plant_id,
        fiscal_year_id: salesReturn.fiscal_year_id,
        item_id: row.material_id,
        warehouse_id: warehouseId,
        reference_type: 'ADJUSTMENT',
        reference_id: salesReturn.id,
        movement_date: salesReturn.return_date,
        inward_qty: qty,
        outward_qty: 0,
        created_by: userId
      }, transaction);
    }

    await salesReturn.update({
      status: 'APPROVED',
      approved_at: new Date(),
      approved_by: userId,
      updated_by: userId
    }, { transaction });

    await transaction.commit();
    const updated = await db.SalesReturn.findByPk(id, { include: [{ model: db.SalesReturnItem, as: 'items' }] });
    sendResponse(res, 200, true, 'Sales return approved successfully', updated);
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message || 'Failed to approve sales return', 400);
  }
});

exports.delete = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const transaction = await db.sequelize.transaction();
  try {
    const row = await db.SalesReturn.findByPk(id, { transaction });
    if (!row) {
      await transaction.rollback();
      return sendError(res, 'Sales return not found', 404);
    }
    if (row.status !== 'DRAFT') {
      await transaction.rollback();
      return sendError(res, 'Only DRAFT sales return can be deleted', 400);
    }
    await db.SalesReturnItem.destroy({ where: { sales_return_id: id }, transaction });
    await row.destroy({ transaction });
    await transaction.commit();
    sendResponse(res, 200, true, 'Sales return deleted successfully');
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message || 'Failed to delete sales return', 400);
  }
});

