const db = require('../models');
const { Op } = require('sequelize');
const stockLedgerService = require('./stockLedgerService');
const negativeStockPreventionService = require('./negativeStockPreventionService');
const glPostingService = require('./glPostingService');

/**
 * Post Delivery - Reduce stock and update Customer PO/Sales Order delivered quantities
 * 
 * @param {Object} delivery - Delivery instance with items
 * @param {Integer} userId - User ID posting the delivery
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Posted delivery with updated references
 */
exports.postDelivery = async (delivery, userId, transaction = null) => {
  const txn = transaction || await db.sequelize.transaction();
  const shouldCommit = !transaction;

  try {
    // Validate delivery has items
    if (!delivery.items || delivery.items.length === 0) {
      throw new Error('Cannot post delivery without items');
    }

    // Validate stock availability for all items before processing
    const itemsToValidate = delivery.items.map(item => ({
      material_id: item.material_id,
      source_storage_location: item.storage_location || delivery.storage_location || null,
      quantity: item.quantity,
      material: item.material
    }));

    const batchValidation = await negativeStockPreventionService.validateBatchStockIssues(
      itemsToValidate,
      {
        companyId: delivery.company_id,
        plantId: delivery.plant_id,
        operation: 'Delivery',
        transaction: txn
      }
    );

    if (!batchValidation.isValid) {
      const errorMessage = negativeStockPreventionService.formatValidationErrors(batchValidation.errors);
      throw new Error(errorMessage);
    }

    // Process each delivery item: reduce stock and update references
    for (const item of delivery.items) {
      const quantity = parseFloat(item.quantity || 0);
      const unitPrice = parseFloat(item.unit_price || 0) || 0;
      const totalAmount = quantity * unitPrice;
      const storageLocation = item.storage_location || delivery.storage_location || null;

      // Create stock ledger entry - ISSUE (outgoing stock for delivery)
      await stockLedgerService.createLedgerEntry({
        company_id: delivery.company_id,
        plant_id: delivery.plant_id,
        material_id: item.material_id,
        transaction_date: delivery.delivery_date,
        transaction_type: 'ISSUE',
        reference_type: 'DELIVERY',
        reference_id: delivery.id,
        reference_number: delivery.delivery_number,
        storage_location: storageLocation,
        quantity_in: 0,
        quantity_out: quantity,
        unit_cost: unitPrice,
        total_cost: totalAmount,
        batch_number: item.batch_number || null,
        remarks: `Delivery: ${delivery.delivery_number} - Delivered to customer`,
        created_by: userId
      }, txn);

      // Update Sales Order Item delivered quantity if linked
      if (item.so_item_id) {
        const soItem = await db.SalesOrderItem.findByPk(item.so_item_id, { transaction: txn });
        if (soItem) {
          const currentDelivered = parseFloat(soItem.delivered_quantity || 0);
          const newDeliveredQty = currentDelivered + quantity;
          const orderedQty = parseFloat(soItem.quantity || 0);
          
          if (newDeliveredQty > orderedQty) {
            throw new Error(`Cannot deliver ${quantity} for item. Total delivered would be ${newDeliveredQty}, but ordered quantity is ${orderedQty}`);
          }

          await soItem.update({
            delivered_quantity: newDeliveredQty,
            updated_at: new Date()
          }, { transaction: txn });
        }
      }

      // Update Customer PO Item delivered quantity if linked
      if (delivery.customer_po_id) {
        // Find Customer PO Item by material_id
        const customerPOItem = await db.CustomerPOItem.findOne({
          where: {
            po_id: delivery.customer_po_id,
            material_id: item.material_id
          },
          transaction: txn
        });

        if (customerPOItem) {
          const currentDelivered = parseFloat(customerPOItem.delivered_quantity || 0);
          const newDeliveredQty = currentDelivered + quantity;
          const orderedQty = parseFloat(customerPOItem.quantity || 0);
          
          if (newDeliveredQty > orderedQty) {
            throw new Error(`Cannot deliver ${quantity} for material. Total delivered would be ${newDeliveredQty}, but ordered quantity is ${orderedQty}`);
          }

          await customerPOItem.update({
            delivered_quantity: newDeliveredQty,
            updated_at: new Date()
          }, { transaction: txn });
        }
      }
    }

    // Update Delivery status
    await delivery.update({
      status: 'POSTED',
      posted_at: new Date(),
      posted_by: userId,
      updated_at: new Date()
    }, { transaction: txn });

    // Post to GL (accounting integration)
    try {
      await glPostingService.postDeliveryToGL(delivery, userId);
    } catch (glError) {
      console.warn('GL posting failed for delivery:', glError.message);
      // Don't fail the delivery posting if GL posting fails
      // Log the error but continue
    }

    // Update Sales Order status if all items are delivered
    if (delivery.so_id) {
      await updateSalesOrderStatus(delivery.so_id, txn);
    }

    // Update Customer PO status if all items are delivered
    if (delivery.customer_po_id) {
      await updateCustomerPOStatus(delivery.customer_po_id, txn);
    }

    if (shouldCommit) {
      await txn.commit();
    }

    return delivery;
  } catch (error) {
    if (shouldCommit) {
      await txn.rollback();
    }
    throw error;
  }
};

/**
 * Update Sales Order status based on delivered quantities
 */
async function updateSalesOrderStatus(salesOrderId, transaction) {
  const salesOrder = await db.SalesOrder.findByPk(salesOrderId, {
    include: [{ model: db.SalesOrderItem, as: 'items' }],
    transaction
  });

  if (!salesOrder || salesOrder.status === 'CLOSED' || salesOrder.status === 'CANCELLED') {
    return;
  }

  const allDelivered = salesOrder.items.every(item => {
    const ordered = parseFloat(item.quantity || 0);
    const delivered = parseFloat(item.delivered_quantity || 0);
    return delivered >= ordered;
  });

  const partiallyDelivered = salesOrder.items.some(item => {
    const ordered = parseFloat(item.quantity || 0);
    const delivered = parseFloat(item.delivered_quantity || 0);
    return delivered > 0 && delivered < ordered;
  });

  if (allDelivered && salesOrder.status !== 'DELIVERED') {
    await salesOrder.update({ status: 'DELIVERED' }, { transaction });
  } else if (partiallyDelivered && salesOrder.status !== 'PARTIALLY_DELIVERED') {
    await salesOrder.update({ status: 'PARTIALLY_DELIVERED' }, { transaction });
  }
}

/**
 * Update Customer PO status based on delivered quantities
 */
async function updateCustomerPOStatus(customerPOId, transaction) {
  const customerPO = await db.CustomerPO.findByPk(customerPOId, {
    include: [{ model: db.CustomerPOItem, as: 'items' }],
    transaction
  });

  if (!customerPO || customerPO.status === 'CLOSED' || customerPO.status === 'CANCELLED') {
    return;
  }

  const allDelivered = customerPO.items.every(item => {
    const ordered = parseFloat(item.quantity || 0);
    const delivered = parseFloat(item.delivered_quantity || 0);
    return delivered >= ordered;
  });

  const partiallyDelivered = customerPO.items.some(item => {
    const ordered = parseFloat(item.quantity || 0);
    const delivered = parseFloat(item.delivered_quantity || 0);
    return delivered > 0 && delivered < ordered;
  });

  if (allDelivered && customerPO.status !== 'DELIVERED') {
    await customerPO.update({ status: 'DELIVERED' }, { transaction });
  } else if (partiallyDelivered && customerPO.status !== 'PARTIALLY_DELIVERED') {
    await customerPO.update({ status: 'PARTIALLY_DELIVERED' }, { transaction });
  }
}

/**
 * Create Delivery from Customer PO
 * 
 * @param {Integer} customerPOId - Customer PO ID
 * @param {Object} options - Additional options
 * @param {String} options.deliveryNumber - Custom delivery number (optional)
 * @param {Date} options.deliveryDate - Delivery date (optional, defaults to today)
 * @param {Array} options.items - Items to deliver (optional, defaults to all items)
 * @param {Integer} options.userId - User ID creating the delivery
 * @param {Object} options.transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Created Delivery with items
 */
exports.createDeliveryFromCustomerPO = async (customerPOId, options = {}) => {
  const {
    deliveryNumber = null,
    deliveryDate = new Date().toISOString().split('T')[0],
    items = null, // If null, deliver all items
    userId = null,
    transaction = null
  } = options;

  const txn = transaction || await db.sequelize.transaction();
  const shouldCommit = !transaction;

  try {
    // Load Customer PO with all necessary data
    const customerPO = await db.CustomerPO.findByPk(customerPOId, {
      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.CustomerPOItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ],
      transaction: txn
    });

    if (!customerPO) {
      throw new Error(`Customer PO with ID ${customerPOId} not found`);
    }

    // Validate Customer PO has items
    if (!customerPO.items || customerPO.items.length === 0) {
      throw new Error(`Customer PO ${customerPO.po_number} has no items`);
    }

    // Generate delivery number if not provided
    let deliveryNum = deliveryNumber;
    if (!deliveryNum) {
      deliveryNum = await generateDeliveryNumber(customerPO.company_id, customerPO.fiscal_year_id, txn);
    }

    // Check if delivery number already exists
    const existingDelivery = await db.Delivery.findOne({
      where: {
        company_id: customerPO.company_id,
        plant_id: customerPO.plant_id,
        fiscal_year_id: customerPO.fiscal_year_id,
        delivery_number: deliveryNum
      },
      transaction: txn
    });

    if (existingDelivery) {
      throw new Error(`Delivery number ${deliveryNum} already exists`);
    }

    // Determine which items to deliver
    let itemsToDeliver = items;
    if (!itemsToDeliver) {
      // Deliver all items with pending quantities
      itemsToDeliver = customerPO.items.map(item => ({
        customer_po_item_id: item.id,
        material_id: item.material_id,
        quantity: Math.max(0, parseFloat(item.quantity || 0) - parseFloat(item.delivered_quantity || 0)),
        unit_price: item.unit_price,
        discount_percentage: item.discount_percentage || 0,
        tax_rate: item.tax_rate || 0,
        storage_location: item.storage_location || null
      })).filter(item => item.quantity > 0);
    }

    if (itemsToDeliver.length === 0) {
      throw new Error('No items to deliver. All items may already be delivered.');
    }

    // Calculate totals
    let subTotal = 0;
    let discountAmount = 0;
    let taxAmount = 0;

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

    // Create Delivery header
    const deliveryData = {
      company_id: customerPO.company_id,
      plant_id: customerPO.plant_id,
      fiscal_year_id: customerPO.fiscal_year_id,
      delivery_number: deliveryNum,
      delivery_date: deliveryDate,
      customer_id: customerPO.customer_id,
      customer_address_id: customerPO.customer_address_id,
      customer_po_id: customerPO.id, // Link to Customer PO
      so_id: customerPO.sales_order_id, // Link to Sales Order if exists
      storage_location: itemsToDeliver[0]?.storage_location || null,
      sub_total: subTotal,
      discount_amount: discountAmount,
      tax_amount: taxAmount,
      total_amount: subTotal - discountAmount + taxAmount,
      status: 'DRAFT', // Start as DRAFT, user can post later
      remarks: `Created from Customer PO: ${customerPO.po_number}`,
      created_by: userId,
      updated_by: userId
    };

    const delivery = await db.Delivery.create(deliveryData, { transaction: txn });

    // Create Delivery Items
    const deliveryItems = [];
    for (let i = 0; i < itemsToDeliver.length; i++) {
      const item = itemsToDeliver[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;

      // Find corresponding Sales Order Item if Customer PO is linked to Sales Order
      let soItemId = null;
      if (customerPO.sales_order_id) {
        const soItem = await db.SalesOrderItem.findOne({
          where: {
            so_id: customerPO.sales_order_id,
            material_id: item.material_id
          },
          transaction: txn
        });
        if (soItem) {
          soItemId = soItem.id;
        }
      }

      const deliveryItem = await db.DeliveryItem.create({
        delivery_id: delivery.id,
        item_number: i + 1,
        material_id: item.material_id,
        so_item_id: soItemId,
        description: item.material?.description || null,
        quantity: quantity,
        uom_id: item.material?.base_uom_id || null,
        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.material?.hsn_code || null,
        storage_location: item.storage_location || null,
        remarks: `From Customer PO Item: ${item.customer_po_item_id || 'N/A'}`
      }, { transaction: txn });

      deliveryItems.push(deliveryItem);
    }

    if (shouldCommit) {
      await txn.commit();
    }

    // Reload Delivery with all associations
    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.BPAddress, as: 'customerAddress' },
        { model: db.CustomerPO, as: 'customerPO' },
        { model: db.SalesOrder, as: 'salesOrder' },
        {
          model: db.DeliveryItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

    return createdDelivery;
  } catch (error) {
    if (shouldCommit) {
      await txn.rollback();
    }
    throw error;
  }
};

/**
 * Generate Delivery number
 * Format: DEL{YYYY}{####}
 */
async function generateDeliveryNumber(companyId, fiscalYearId, transaction) {
  const fiscalYear = await db.FiscalYear.findByPk(fiscalYearId, { transaction });
  if (!fiscalYear) {
    throw new Error('Fiscal Year not found');
  }

  const year = new Date(fiscalYear.start_date).getFullYear();
  const prefix = `DEL${year}`;

  const lastDelivery = await db.Delivery.findOne({
    where: {
      company_id: companyId,
      fiscal_year_id: fiscalYearId,
      delivery_number: { [Op.like]: `${prefix}%` }
    },
    order: [['delivery_number', 'DESC']],
    transaction
  });

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

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

