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

const generateDeliveryNumber = async (companyId) => {
  const today = new Date();
  const year = today.getFullYear();
  const prefix = `DLV${year}`;
  
  const lastDelivery = await db.Delivery.findOne({
    where: {
      company_id: companyId,
      delivery_number: { [Op.like]: `${prefix}%` }
    },
    order: [['delivery_number', 'DESC']]
  });

  let sequence = 1;
  if (lastDelivery) {
    const lastSequence = parseInt(lastDelivery.delivery_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] = [
      { delivery_number: { [Op.like]: `%${search}%` } }
    ];
  }

  const records = await db.Delivery.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: [['delivery_date', 'DESC'], ['id', 'DESC']]
  });

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

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

  const delivery = await db.Delivery.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.SalesOrder, as: 'salesOrder' },
      {
        model: db.DeliveryItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.SalesOrderItem, as: 'salesOrderItem' },
          { model: db.UOM, as: 'uom' }
        ],
        order: [['item_number', 'ASC']]
      }
    ]
  });

  if (!delivery) {
    return sendError(res, 'Delivery not found', 404);
  }

  sendResponse(res, 200, true, 'Delivery retrieved successfully', delivery);
});

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);
  }

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

  // If Sales Order reference exists, validate delivery quantities
  if (data.so_id) {
    const salesOrder = await db.SalesOrder.findByPk(data.so_id, {
      include: [{ model: db.SalesOrderItem, as: 'items' }]
    });

    if (!salesOrder) {
      return sendError(res, 'Referenced Sales Order not found', 404);
    }

    // Validate delivery quantities don't exceed order quantities
    for (const deliveryItem of data.items) {
      if (deliveryItem.so_item_id) {
        const soItem = salesOrder.items.find(item => item.id === deliveryItem.so_item_id);
        if (soItem) {
          const deliveredQty = parseFloat(deliveryItem.quantity || 0);
          const orderedQty = parseFloat(soItem.quantity || 0);
          const alreadyDeliveredQty = parseFloat(soItem.delivered_quantity || 0);
          const remainingQty = orderedQty - alreadyDeliveredQty;

          if (deliveredQty > remainingQty) {
            return sendError(res, `Cannot deliver ${deliveredQty} for item. Remaining quantity is ${remainingQty}`, 400);
          }
        }
      }
    }
  }

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

  try {
    if (!data.delivery_number) {
      data.delivery_number = await generateDeliveryNumber(companyId);
    }

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

    const { items, ...deliveryData } = 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;
      });
    }

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

    const delivery = await db.Delivery.create(deliveryData, { 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.DeliveryItem.create({
          delivery_id: delivery.id,
          item_number: i + 1,
          material_id: item.material_id,
          so_item_id: item.so_item_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,
          batch_number: item.batch_number,
          storage_location: item.storage_location,
          remarks: item.remarks
        }, { transaction });
      }
    }

    await transaction.commit();

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

    sendResponse(res, 201, true, 'Delivery created successfully', createdDelivery);
  } 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 delivery = await db.Delivery.findByPk(id);

  if (!delivery) {
    return sendError(res, 'Delivery not found', 404);
  }

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

  // If Sales Order reference exists, validate delivery quantities
  if (data.so_id) {
    const salesOrder = await db.SalesOrder.findByPk(data.so_id, {
      include: [{ model: db.SalesOrderItem, as: 'items' }]
    });

    if (salesOrder && data.items && Array.isArray(data.items)) {
      // Get existing delivery items (excluding current delivery being updated)
      const existingDeliveryItems = await db.DeliveryItem.findAll({
        where: {
          delivery_id: { [Op.ne]: delivery.id },
          so_item_id: { [Op.ne]: null }
        },
        include: [
          {
            model: db.Delivery,
            as: 'delivery',
            where: {
              so_id: data.so_id,
              status: { [Op.in]: ['POSTED'] }
            },
            attributes: []
          }
        ]
      });

      // Calculate already delivered quantities
      const alreadyDelivered = {};
      existingDeliveryItems.forEach(item => {
        if (item.so_item_id) {
          alreadyDelivered[item.so_item_id] = (alreadyDelivered[item.so_item_id] || 0) + parseFloat(item.quantity || 0);
        }
      });

      // Validate delivery quantities
      for (const deliveryItem of data.items) {
        if (deliveryItem.so_item_id) {
          const soItem = salesOrder.items.find(item => item.id === deliveryItem.so_item_id);
          if (soItem) {
            const deliveredQty = parseFloat(deliveryItem.quantity || 0);
            const orderedQty = parseFloat(soItem.quantity || 0);
            const alreadyDeliveredQty = alreadyDelivered[deliveryItem.so_item_id] || 0;
            const remainingQty = orderedQty - alreadyDeliveredQty;

            if (deliveredQty > remainingQty) {
              return sendError(res, `Cannot deliver ${deliveredQty} for item. Remaining quantity is ${remainingQty}`, 400);
            }
          }
        }
      }
    }
  }

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

  try {
    data.updated_by = userId;

    const { items, ...deliveryData } = 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;
      });
    }

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

    await delivery.update(deliveryData, { transaction });

    await db.DeliveryItem.destroy({ where: { delivery_id: delivery.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.DeliveryItem.create({
          delivery_id: delivery.id,
          item_number: i + 1,
          material_id: item.material_id,
          so_item_id: item.so_item_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,
          batch_number: item.batch_number,
          storage_location: item.storage_location,
          remarks: item.remarks
        }, { transaction });
      }
    }

    await transaction.commit();

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

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

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

  const delivery = await db.Delivery.findByPk(id);

  if (!delivery) {
    return sendError(res, 'Delivery not found', 404);
  }

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

  await db.DeliveryItem.destroy({ where: { delivery_id: delivery.id } });
  await delivery.destroy();

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

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

  const delivery = await db.Delivery.findByPk(id);

  if (!delivery) {
    return sendError(res, 'Delivery not found', 404);
  }

  const validStatuses = ['DRAFT', 'SUBMITTED', 'POSTED', 'CANCELLED'];
  if (!validStatuses.includes(status)) {
    return sendError(res, 'Invalid status', 400);
  }

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

  try {
    const updateData = { status, updated_by: userId };

    if (status === 'POSTED') {
      // Use deliveryService to handle posting with stock reduction
      const deliveryService = require('../services/deliveryService');
      
      // Reload delivery with items for posting
      const deliveryWithItems = await db.Delivery.findByPk(delivery.id, {
        include: [
          {
            model: db.DeliveryItem,
            as: 'items',
            include: [
              { model: db.Material, as: 'material' },
              { model: db.UOM, as: 'uom' }
            ]
          }
        ],
        transaction
      });

      if (!deliveryWithItems) {
        await transaction.rollback();
        return sendError(res, 'Delivery not found', 404);
      }

      // Post delivery (reduces stock, updates Customer PO/Sales Order delivered quantities)
      // Service already updates delivery status to POSTED, so skip update here
      await deliveryService.postDelivery(deliveryWithItems, userId, transaction);
    } else {
      // For non-POSTED status changes, update normally
      await delivery.update(updateData, { transaction });
    }
    await transaction.commit();

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

    sendResponse(res, 200, true, 'Delivery status updated successfully', updatedDelivery);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

/**
 * Create Delivery from Customer PO
 * POST /delivery/create-from-customer-po/:customerPOId
 */
exports.createFromCustomerPO = asyncHandler(async (req, res) => {
  const { customerPOId } = req.params;
  const { delivery_number, delivery_date, items } = req.body;
  const userId = req.user?.userId || null;

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

  try {
    const deliveryService = require('../services/deliveryService');
    
    const delivery = await deliveryService.createDeliveryFromCustomerPO(customerPOId, {
      deliveryNumber: delivery_number,
      deliveryDate: delivery_date,
      items: items,
      userId
    });

    sendResponse(res, 201, true, 'Delivery created successfully from Customer PO', delivery);
  } catch (error) {
    console.error('Error creating Delivery from Customer PO:', error);
    sendError(res, error.message || 'Failed to create Delivery from Customer PO', 400);
  }
});

