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

const generatePaymentNo = async (companyId, fiscalYearId, transaction) => {
  try {
    return await generateNextNumber(companyId, 'VENDOR_PAYMENT', fiscalYearId, transaction);
  } catch (e) {
    const year = new Date().getFullYear();
    const prefix = `VPAY${year}`;
    const last = await db.VendorPayment.findOne({
      where: {
        company_id: companyId,
        payment_no: { [Op.like]: `${prefix}%` }
      },
      attributes: ['payment_no'],
      order: [['payment_no', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    const next = (parseInt(String(last?.payment_no || '').slice(-4), 10) || 0) + 1;
    return `${prefix}${String(next).padStart(4, '0')}`;
  }
};

const normalizePaymentItems = (items = []) => items.map((it, idx) => ({
  line_no: idx + 1,
  payment_mode: String(it.payment_mode || '').toUpperCase(),
  amount: toNum(it.amount),
  bank_name: it.bank_name || null,
  ifsc_code: it.ifsc_code || null,
  reference_no: it.reference_no || null,
  remarks: it.remarks || null
}));

const validatePaymentItems = (items) => {
  if (!Array.isArray(items) || items.length === 0) throw new Error('At least one payment item is required');
  items.forEach((it) => {
    if (!['CASH', 'BANK', 'UPI', 'CHEQUE', 'NEFT', 'ONLINE_TRANSFER'].includes(it.payment_mode)) {
      throw new Error('Invalid payment mode in payment items');
    }
    if (toNum(it.amount) <= 0) throw new Error('Payment item amount should be greater than 0');
    if ((it.payment_mode === 'BANK' || it.payment_mode === 'NEFT' || it.payment_mode === 'CHEQUE' || it.payment_mode === 'ONLINE_TRANSFER') && (!it.bank_name || !it.ifsc_code)) {
      throw new Error('bank_name and ifsc_code are required for BANK/NEFT/CHEQUE/ONLINE_TRANSFER payment items');
    }
    if (it.payment_mode !== 'CASH' && !it.reference_no) {
      throw new Error('reference_no is required for non-cash payment items');
    }
  });
};

const getPostedAppliedVendorPaymentAmount = async (invoiceId, excludePaymentId = null, transaction = null) => {
  const where = { purchase_invoice_id: invoiceId, status: 'POSTED' };
  if (excludePaymentId) where.id = { [Op.ne]: excludePaymentId };
  // Avoid SUM(literal) here; some Sequelize/MySQL versions throw
  // "smth.col.startsWith is not a function" with literal expressions.
  const postedToInvoice = await db.VendorPayment.sum('paid_to_invoice_amount', { where, transaction });
  const postedAdjustedAdvance = await db.VendorPayment.sum('adjusted_advance_amount', { where, transaction });
  return toNum(postedToInvoice) + toNum(postedAdjustedAdvance);
};

const getVendorInvoiceWithOutstanding = async (invoiceId, excludePaymentId = null, transaction = null) => {
  const invoice = await db.VendorInvoice.findByPk(invoiceId, {
    attributes: ['id', 'vendor_id', 'invoice_number', 'net_payable', 'total_amount', 'status'],
    include: [{ model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'bp_number', 'name'], required: false }],
    transaction
  });
  if (!invoice) throw new Error('Purchase invoice not found');
  const postedApplied = await getPostedAppliedVendorPaymentAmount(invoiceId, excludePaymentId, transaction);
  const invoiceAmount = toNum(invoice.net_payable || invoice.total_amount);
  const outstanding = Math.max(invoiceAmount - postedApplied, 0);
  return { invoice, postedApplied, outstanding };
};

const updateVendorInvoicePaymentStatus = async (invoiceId, transaction) => {
  if (!invoiceId) return;
  const { invoice, outstanding } = await getVendorInvoiceWithOutstanding(invoiceId, null, transaction);
  const nextStatus = outstanding <= 0 ? 'PAID' : 'POSTED';
  if (['POSTED', 'PAID'].includes(String(invoice.status || '').toUpperCase()) && invoice.status !== nextStatus) {
    await invoice.update({ status: nextStatus }, { transaction });
  }
};

const computeVendorPaymentFigures = async ({
  vendor,
  invoiceId,
  paidAmount,
  adjustAdvance,
  excludePaymentId = null,
  transaction = null
}) => {
  const vendorAdvance = toNum(vendor?.vendor_advance_balance);
  let invoice = null;
  let invoiceOutstanding = 0;

  if (invoiceId) {
    const data = await getVendorInvoiceWithOutstanding(invoiceId, excludePaymentId, transaction);
    invoice = data.invoice;
    invoiceOutstanding = toNum(data.outstanding);
    if (invoice.vendor_id !== vendor.id) {
      throw new Error('Selected purchase invoice does not belong to selected vendor');
    }
    if (!['APPROVED', 'POSTED', 'PAID'].includes(String(invoice.status || '').toUpperCase())) {
      throw new Error('Purchase invoice must be approved/posted before payment');
    }
  }

  const adjustedAdvanceAmount = invoice && adjustAdvance
    ? Math.min(vendorAdvance, invoiceOutstanding)
    : 0;
  const outstandingAfterAdvance = Math.max(invoiceOutstanding - adjustedAdvanceAmount, 0);
  const paidToInvoiceAmount = invoice ? Math.min(paidAmount, outstandingAfterAdvance) : 0;
  const advanceAmount = invoice ? Math.max(paidAmount - paidToInvoiceAmount, 0) : paidAmount;
  const balanceAmount = invoice ? Math.max(outstandingAfterAdvance - paidToInvoiceAmount, 0) : 0;

  return {
    invoice,
    adjustedAdvanceAmount,
    paidToInvoiceAmount,
    advanceAmount,
    balanceAmount
  };
};

exports.getInvoiceOptions = asyncHandler(async (req, res) => {
  const companyId = req.query.companyId || req.user?.companyId;
  const plantId = parseInt(req.query.plantId, 10);
  const fiscalYearId = parseInt(req.query.fiscalYearId, 10);
  const vendorId = parseInt(req.query.vendorId, 10);
  if (!companyId || !Number.isFinite(plantId) || !Number.isFinite(fiscalYearId)) {
    return sendError(res, 'companyId, plantId and fiscalYearId are required', 400);
  }

  const where = {
    company_id: companyId,
    plant_id: plantId,
    fiscal_year_id: fiscalYearId,
    // Keep invoice list broad so vendor payment form can always show invoice numbers.
    status: { [Op.in]: ['DRAFT', 'SUBMITTED', 'APPROVED', 'POSTED', 'PAID'] }
  };
  if (Number.isFinite(vendorId) && vendorId > 0) where.vendor_id = vendorId;

  const invoices = await db.VendorInvoice.findAll({
    where,
    attributes: ['id', 'invoice_number', 'vendor_invoice_number', 'invoice_date', 'vendor_id', 'net_payable', 'total_amount'],
    include: [{ model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'bp_number', 'name'], required: false }],
    order: [['invoice_date', 'DESC'], ['id', 'DESC']]
  });

  const payload = [];
  for (const inv of invoices) {
    const postedApplied = await getPostedAppliedVendorPaymentAmount(inv.id);
    const invoiceAmount = toNum(inv.net_payable || inv.total_amount);
    const outstanding = Math.max(invoiceAmount - postedApplied, 0);
    if (outstanding <= 0) continue;
    payload.push({
      id: inv.id,
      invoice_number: inv.invoice_number,
      vendor_invoice_number: inv.vendor_invoice_number,
      invoice_date: inv.invoice_date,
      vendor_id: inv.vendor_id,
      vendor_name: inv.vendor?.name || '',
      vendor_code: inv.vendor?.bp_number || '',
      invoice_total: invoiceAmount,
      paid_amount: postedApplied,
      balance_amount: outstanding
    });
  }

  sendResponse(res, 200, true, 'Unpaid purchase invoice options retrieved successfully', payload);
});

exports.getAll = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, status, companyId, plantId, fiscalYearId, dateFrom, dateTo } = req.query;
  const vendorId = parseInt(req.query.vendorId, 10);
  const invoiceId = parseInt(req.query.invoiceId, 10);
  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 (status) where.status = status;
  if (Number.isFinite(vendorId) && vendorId > 0) where.vendor_id = vendorId;
  if (Number.isFinite(invoiceId) && invoiceId > 0) where.purchase_invoice_id = invoiceId;
  if (search) where[Op.or] = [{ payment_no: { [Op.like]: `%${search}%` } }, { reference_no: { [Op.like]: `%${search}%` } }];
  if (dateFrom && dateTo) {
    where.payment_date = { [Op.between]: [dateFrom, dateTo] };
  } else if (dateFrom) {
    where.payment_date = { [Op.gte]: dateFrom };
  } else if (dateTo) {
    where.payment_date = { [Op.lte]: dateTo };
  }

  const rows = await db.VendorPayment.findAndCountAll({
    where,
    include: [
      { model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'bp_number', 'name'], required: false },
      { model: db.VendorInvoice, as: 'purchaseInvoice', attributes: ['id', 'invoice_number', 'vendor_invoice_number'], required: false }
    ],
    limit: limitNum,
    offset,
    order: [['id', 'DESC']]
  });
  const response = getPagingData(rows, page, limitNum);
  sendResponse(res, 200, true, 'Vendor payments retrieved successfully', response);
});

exports.getById = asyncHandler(async (req, res) => {
  const row = await db.VendorPayment.findByPk(req.params.id, {
    include: [
      { model: db.BusinessPartner, as: 'vendor', attributes: ['id', 'bp_number', 'name'] },
      { model: db.VendorInvoice, as: 'purchaseInvoice', attributes: ['id', 'invoice_number', 'vendor_invoice_number', 'invoice_date', 'net_payable', 'total_amount'] },
      { model: db.VendorPaymentItem, as: 'items', required: false }
    ]
  });
  if (!row) return sendError(res, 'Vendor payment not found', 404);
  sendResponse(res, 200, true, 'Vendor payment retrieved successfully', row);
});

exports.create = asyncHandler(async (req, res) => {
  const data = req.body || {};
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;
  const vendorId = parseInt(data.vendor_id, 10);
  const invoiceId = parseInt(data.purchase_invoice_id, 10);
  const paymentItems = normalizePaymentItems(data.payment_items || []);
  const adjustAdvance = !!data.adjust_advance;

  if (!companyId) return sendError(res, 'Company ID is required', 400);
  if (!data.plant_id || !data.fiscal_year_id) return sendError(res, 'plant_id and fiscal_year_id are required', 400);
  if (!Number.isFinite(vendorId) || vendorId <= 0) return sendError(res, 'vendor_id is required', 400);

  const t = await db.sequelize.transaction();
  try {
    const vendor = await db.BusinessPartner.findByPk(vendorId, { transaction: t });
    if (!vendor) throw new Error('Vendor not found');
    if (!['VENDOR', 'BOTH'].includes(String(vendor.bp_type || '').toUpperCase())) {
      throw new Error('Selected business partner is not a vendor');
    }

    validatePaymentItems(paymentItems);
    const paidAmount = paymentItems.reduce((sum, it) => sum + toNum(it.amount), 0);
    const calc = await computeVendorPaymentFigures({
      vendor,
      invoiceId: Number.isFinite(invoiceId) && invoiceId > 0 ? invoiceId : null,
      paidAmount,
      adjustAdvance,
      transaction: t
    });

    const status = String(data.status || 'DRAFT').toUpperCase();
    if (!['DRAFT', 'POSTED'].includes(status)) throw new Error('Invalid status');

    const payment = await db.VendorPayment.create({
      company_id: companyId,
      plant_id: data.plant_id,
      fiscal_year_id: data.fiscal_year_id,
      payment_no: data.payment_no || await generatePaymentNo(companyId, data.fiscal_year_id, t),
      payment_date: data.payment_date || new Date(),
      vendor_id: vendor.id,
      purchase_invoice_id: calc.invoice?.id || null,
      invoice_total: toNum(calc.invoice?.net_payable || calc.invoice?.total_amount || 0),
      paid_amount: paidAmount,
      paid_to_invoice_amount: calc.paidToInvoiceAmount,
      advance_amount: calc.advanceAmount,
      adjusted_advance_amount: calc.adjustedAdvanceAmount,
      balance_amount: calc.balanceAmount,
      payment_mode: paymentItems[0]?.payment_mode || 'BANK',
      bank_account: data.bank_account || null,
      bank_name: paymentItems[0]?.bank_name || null,
      ifsc_code: paymentItems[0]?.ifsc_code || null,
      reference_no: paymentItems[0]?.reference_no || null,
      remarks: data.remarks || null,
      status,
      posted_at: status === 'POSTED' ? new Date() : null,
      posted_by: status === 'POSTED' ? userId : null,
      created_by: userId,
      updated_by: userId
    }, { transaction: t });

    await Promise.all(paymentItems.map((it) => db.VendorPaymentItem.create({
      vendor_payment_id: payment.id,
      line_no: it.line_no,
      payment_mode: it.payment_mode,
      amount: toNum(it.amount),
      bank_name: it.bank_name,
      ifsc_code: it.ifsc_code,
      reference_no: it.reference_no,
      remarks: it.remarks
    }, { transaction: t })));

    if (status === 'POSTED') {
      const nextAdvance = Math.max(
        toNum(vendor.vendor_advance_balance) - calc.adjustedAdvanceAmount + calc.advanceAmount,
        0
      );
      await vendor.update({ vendor_advance_balance: nextAdvance }, { transaction: t });
      await updateVendorInvoicePaymentStatus(calc.invoice?.id || null, t);
      await glPostingService.postVendorPaymentToGL(payment, userId);
    }

    await t.commit();
    const created = await db.VendorPayment.findByPk(payment.id);
    sendResponse(res, 201, true, 'Vendor payment created successfully', created);
  } catch (e) {
    await t.rollback();
    return sendError(res, e.message || 'Failed to create vendor payment', 400);
  }
});

exports.update = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const data = req.body || {};
  const userId = req.user?.userId || null;
  const row = await db.VendorPayment.findByPk(id);
  if (!row) return sendError(res, 'Vendor payment not found', 404);
  if (row.status !== 'DRAFT') return sendError(res, 'Only DRAFT vendor payment can be updated', 400);

  const vendorId = parseInt(data.vendor_id ?? row.vendor_id, 10);
  const invoiceId = data.purchase_invoice_id === null || data.purchase_invoice_id === ''
    ? null
    : parseInt(data.purchase_invoice_id ?? row.purchase_invoice_id, 10);
  const paymentItems = Array.isArray(data.payment_items) ? normalizePaymentItems(data.payment_items) : null;
  const paidAmount = paymentItems
    ? paymentItems.reduce((sum, it) => sum + toNum(it.amount), 0)
    : toNum(data.paid_amount ?? row.paid_amount);
  const adjustAdvance = data.adjust_advance === undefined ? !!toNum(row.adjusted_advance_amount) : !!data.adjust_advance;

  const t = await db.sequelize.transaction();
  try {
    const vendor = await db.BusinessPartner.findByPk(vendorId, { transaction: t });
    if (!vendor) throw new Error('Vendor not found');
    if (paymentItems) validatePaymentItems(paymentItems);
    const calc = await computeVendorPaymentFigures({
      vendor,
      invoiceId: Number.isFinite(invoiceId) && invoiceId > 0 ? invoiceId : null,
      paidAmount,
      adjustAdvance,
      excludePaymentId: row.id,
      transaction: t
    });

    await row.update({
      payment_date: data.payment_date || row.payment_date,
      vendor_id: vendor.id,
      purchase_invoice_id: calc.invoice?.id || null,
      invoice_total: toNum(calc.invoice?.net_payable || calc.invoice?.total_amount || 0),
      paid_amount: paidAmount,
      paid_to_invoice_amount: calc.paidToInvoiceAmount,
      advance_amount: calc.advanceAmount,
      adjusted_advance_amount: calc.adjustedAdvanceAmount,
      balance_amount: calc.balanceAmount,
      payment_mode: data.payment_mode ? String(data.payment_mode).toUpperCase() : row.payment_mode,
      bank_account: data.bank_account ?? row.bank_account,
      bank_name: data.bank_name ?? row.bank_name,
      ifsc_code: data.ifsc_code ?? row.ifsc_code,
      reference_no: data.reference_no ?? row.reference_no,
      remarks: data.remarks ?? row.remarks,
      updated_by: userId
    }, { transaction: t });

    if (paymentItems) {
      await db.VendorPaymentItem.destroy({ where: { vendor_payment_id: row.id }, transaction: t });
      await Promise.all(paymentItems.map((it) => db.VendorPaymentItem.create({
        vendor_payment_id: row.id,
        line_no: it.line_no,
        payment_mode: it.payment_mode,
        amount: toNum(it.amount),
        bank_name: it.bank_name,
        ifsc_code: it.ifsc_code,
        reference_no: it.reference_no,
        remarks: it.remarks
      }, { transaction: t })));
    }

    await t.commit();
    const updated = await db.VendorPayment.findByPk(row.id);
    sendResponse(res, 200, true, 'Vendor payment updated successfully', updated);
  } catch (e) {
    await t.rollback();
    return sendError(res, e.message || 'Failed to update vendor payment', 400);
  }
});

exports.updateStatus = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const userId = req.user?.userId || null;
  const status = String(req.body?.status || '').toUpperCase();
  if (status !== 'POSTED') return sendError(res, 'Only POSTED status is allowed', 400);

  const row = await db.VendorPayment.findByPk(id);
  if (!row) return sendError(res, 'Vendor payment not found', 404);
  if (row.status !== 'DRAFT') return sendError(res, 'Only DRAFT vendor payment can be posted', 400);

  const t = await db.sequelize.transaction();
  try {
    const vendor = await db.BusinessPartner.findByPk(row.vendor_id, { transaction: t });
    if (!vendor) throw new Error('Vendor not found');

    await row.update({
      status: 'POSTED',
      posted_at: new Date(),
      posted_by: userId,
      updated_by: userId
    }, { transaction: t });

    const nextAdvance = Math.max(
      toNum(vendor.vendor_advance_balance) - toNum(row.adjusted_advance_amount) + toNum(row.advance_amount),
      0
    );
    await vendor.update({ vendor_advance_balance: nextAdvance }, { transaction: t });
    await updateVendorInvoicePaymentStatus(row.purchase_invoice_id, t);
    await glPostingService.postVendorPaymentToGL(row, userId);

    await t.commit();
    const updated = await db.VendorPayment.findByPk(row.id);
    sendResponse(res, 200, true, 'Vendor payment posted successfully', updated);
  } catch (e) {
    await t.rollback();
    return sendError(res, e.message || 'Failed to post vendor payment', 400);
  }
});

exports.delete = asyncHandler(async (req, res) => {
  const id = parseInt(req.params.id, 10);
  const row = await db.VendorPayment.findByPk(id);
  if (!row) return sendError(res, 'Vendor payment not found', 404);
  if (row.status !== 'DRAFT') return sendError(res, 'Only DRAFT vendor payment can be deleted', 400);

  const t = await db.sequelize.transaction();
  try {
    await db.VendorPaymentItem.destroy({ where: { vendor_payment_id: row.id }, transaction: t });
    await row.destroy({ transaction: t });
    await t.commit();
    sendResponse(res, 200, true, 'Vendor payment deleted successfully');
  } catch (e) {
    await t.rollback();
    return sendError(res, e.message || 'Failed to delete vendor payment', 400);
  }
});

