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 generateSQNumber = async (companyId) => {
  const today = new Date();
  const year = today.getFullYear();
  const prefix = `SQ${year}`;
  
  const lastSQ = await db.SalesQuotation.findOne({
    where: {
      company_id: companyId,
      sq_number: { [Op.like]: `${prefix}%` }
    },
    order: [['sq_number', 'DESC']]
  });

  let sequence = 1;
  if (lastSQ) {
    const lastSequence = parseInt(lastSQ.sq_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, plantId, fiscalYearId, customerId } = 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 (customerId) {
    where.customer_id = customerId;
  }

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

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

  const records = await db.SalesQuotation.findAndCountAll({
    where,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'] }
    ],
    limit: limitNum,
    offset,
    order: [['sq_date', 'DESC'], ['id', 'DESC']]
  });

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

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

  const sq = await db.SalesQuotation.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      { model: db.BusinessPartner, as: 'customer' },
      { model: db.BPAddress, as: 'customerAddress' },
      { model: db.Currency, as: 'currency' },
      {
        model: db.SalesQuotationItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' }
        ],
        order: [['item_number', 'ASC']]
      }
    ]
  });

  if (!sq) {
    return sendError(res, 'Sales Quotation not found', 404);
  }

  sendResponse(res, 200, true, 'Sales Quotation retrieved successfully', sq);
});

exports.create = asyncHandler(async (req, res) => {
  const data = req.body;
  const userId = req.user?.userId || null;
  const companyId = req.user?.companyId;

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

  if (!data.plant_id || !data.fiscal_year_id || !data.customer_id) {
    return sendError(res, 'Plant ID, Fiscal Year ID, and Customer ID are required', 400);
  }

  const transaction = await db.sequelize.transaction();

  try {
    if (!data.sq_number) {
      data.sq_number = await generateSQNumber(companyId);
    }

    data.company_id = companyId;
    data.created_by = userId;
    data.updated_by = userId;

    const { items, ...sqData } = data;
    
    let subTotal = 0;
    let discountAmount = 0;
    let taxAmount = 0;

    if (items && Array.isArray(items)) {
      items.forEach(item => {
        const quantity = parseFloat(item.quantity) || 0;
        const unitPrice = parseFloat(item.unit_price) || 0;
        const discountPercent = parseFloat(item.discount_percentage) || 0;
        const taxRate = parseFloat(item.tax_rate) || 0;

        const lineTotal = quantity * unitPrice;
        const lineDiscount = (lineTotal * discountPercent) / 100;
        const taxableAmount = lineTotal - lineDiscount;
        const lineTax = (taxableAmount * taxRate) / 100;

        subTotal += lineTotal;
        discountAmount += lineDiscount;
        taxAmount += lineTax;
      });
    }

    sqData.sub_total = subTotal;
    sqData.discount_amount = discountAmount;
    sqData.tax_amount = taxAmount;
    sqData.total_amount = subTotal - discountAmount + taxAmount;

    const sq = await db.SalesQuotation.create(sqData, { transaction });

    if (items && Array.isArray(items)) {
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        const quantity = parseFloat(item.quantity) || 0;
        const unitPrice = parseFloat(item.unit_price) || 0;
        const discountPercent = parseFloat(item.discount_percentage) || 0;
        const taxRate = parseFloat(item.tax_rate) || 0;

        const lineTotal = quantity * unitPrice;
        const lineDiscount = (lineTotal * discountPercent) / 100;
        const taxableAmount = lineTotal - lineDiscount;
        const lineTax = (taxableAmount * taxRate) / 100;

        await db.SalesQuotationItem.create({
          sq_id: sq.id,
          item_number: i + 1,
          material_id: item.material_id,
          description: item.description,
          quantity: quantity,
          uom_id: item.uom_id,
          unit_price: unitPrice,
          discount_percentage: discountPercent,
          discount_amount: lineDiscount,
          net_amount: taxableAmount,
          tax_rate: taxRate,
          tax_amount: lineTax,
          total_amount: taxableAmount + lineTax,
          hsn_code: item.hsn_code,
          delivery_date: item.delivery_date,
          remarks: item.remarks
        }, { transaction });
      }
    }

    await transaction.commit();

    const createdSQ = await db.SalesQuotation.findByPk(sq.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.FiscalYear, as: 'fiscalYear' },
        { model: db.BusinessPartner, as: 'customer' },
        {
          model: db.SalesQuotationItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 201, true, 'Sales Quotation created successfully', createdSQ);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

exports.update = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const data = req.body;
  const userId = req.user?.userId || null;

  const sq = await db.SalesQuotation.findByPk(id);

  if (!sq) {
    return sendError(res, 'Sales Quotation not found', 404);
  }

  if (sq.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Sales Quotations can be updated', 400);
  }

  const transaction = await db.sequelize.transaction();

  try {
    data.updated_by = userId;

    const { items, ...sqData } = data;
    
    let subTotal = 0;
    let discountAmount = 0;
    let taxAmount = 0;

    if (items && Array.isArray(items)) {
      items.forEach(item => {
        const quantity = parseFloat(item.quantity) || 0;
        const unitPrice = parseFloat(item.unit_price) || 0;
        const discountPercent = parseFloat(item.discount_percentage) || 0;
        const taxRate = parseFloat(item.tax_rate) || 0;

        const lineTotal = quantity * unitPrice;
        const lineDiscount = (lineTotal * discountPercent) / 100;
        const taxableAmount = lineTotal - lineDiscount;
        const lineTax = (taxableAmount * taxRate) / 100;

        subTotal += lineTotal;
        discountAmount += lineDiscount;
        taxAmount += lineTax;
      });
    }

    sqData.sub_total = subTotal;
    sqData.discount_amount = discountAmount;
    sqData.tax_amount = taxAmount;
    sqData.total_amount = subTotal - discountAmount + taxAmount;

    await sq.update(sqData, { transaction });

    await db.SalesQuotationItem.destroy({ where: { sq_id: sq.id }, transaction });

    if (items && Array.isArray(items)) {
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        const quantity = parseFloat(item.quantity) || 0;
        const unitPrice = parseFloat(item.unit_price) || 0;
        const discountPercent = parseFloat(item.discount_percentage) || 0;
        const taxRate = parseFloat(item.tax_rate) || 0;

        const lineTotal = quantity * unitPrice;
        const lineDiscount = (lineTotal * discountPercent) / 100;
        const taxableAmount = lineTotal - lineDiscount;
        const lineTax = (taxableAmount * taxRate) / 100;

        await db.SalesQuotationItem.create({
          sq_id: sq.id,
          item_number: i + 1,
          material_id: item.material_id,
          description: item.description,
          quantity: quantity,
          uom_id: item.uom_id,
          unit_price: unitPrice,
          discount_percentage: discountPercent,
          discount_amount: lineDiscount,
          net_amount: taxableAmount,
          tax_rate: taxRate,
          tax_amount: lineTax,
          total_amount: taxableAmount + lineTax,
          hsn_code: item.hsn_code,
          delivery_date: item.delivery_date,
          remarks: item.remarks
        }, { transaction });
      }
    }

    await transaction.commit();

    const updatedSQ = await db.SalesQuotation.findByPk(sq.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.FiscalYear, as: 'fiscalYear' },
        { model: db.BusinessPartner, as: 'customer' },
        {
          model: db.SalesQuotationItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    sendResponse(res, 200, true, 'Sales Quotation updated successfully', updatedSQ);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

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

  const sq = await db.SalesQuotation.findByPk(id);

  if (!sq) {
    return sendError(res, 'Sales Quotation not found', 404);
  }

  if (sq.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Sales Quotations can be deleted', 400);
  }

  await db.SalesQuotationItem.destroy({ where: { sq_id: sq.id } });
  await sq.destroy();

  sendResponse(res, 200, true, 'Sales Quotation deleted successfully', null);
});

exports.updateStatus = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const { status } = req.body;
  const userId = req.user?.userId || null;

  const sq = await db.SalesQuotation.findByPk(id);

  if (!sq) {
    return sendError(res, 'Sales Quotation not found', 404);
  }

  const validStatuses = ['DRAFT', 'SUBMITTED', 'APPROVED', 'REJECTED', 'CONVERTED', 'EXPIRED', 'CLOSED'];
  if (!validStatuses.includes(status)) {
    return sendError(res, 'Invalid status', 400);
  }

  const updateData = { status, updated_by: userId };

  if (status === 'SUBMITTED') {
    updateData.submitted_at = new Date();
    updateData.submitted_by = userId;
  } else if (status === 'APPROVED') {
    updateData.approved_at = new Date();
    updateData.approved_by = userId;
  } else if (status === 'CONVERTED') {
    updateData.converted_at = new Date();
    updateData.converted_by = userId;
  }

  await sq.update(updateData);

  const updatedSQ = await db.SalesQuotation.findByPk(sq.id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.BusinessPartner, as: 'customer' }
    ]
  });

  sendResponse(res, 200, true, 'Sales Quotation status updated successfully', updatedSQ);
});

