const db = require('../models');
const { Op } = require('sequelize');
const numberFormatService = require('./numberFormatService');

/**
 * Create Sales Invoice from Delivery
 * 
 * @param {Integer} deliveryId - Delivery ID
 * @param {Object} options - Additional options
 * @param {String} options.invoiceNumber - Custom invoice number (optional)
 * @param {Date} options.invoiceDate - Invoice date (optional, defaults to today)
 * @param {Integer} options.userId - User ID creating the invoice
 * @param {Object} options.transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Created Sales Invoice with items
 */
exports.createSalesInvoiceFromDelivery = async (deliveryId, options = {}) => {
  const {
    invoiceNumber = null,
    invoiceDate = new Date().toISOString().split('T')[0],
    userId = null,
    transaction = null
  } = options;

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

  try {
    // Load Delivery with all necessary data
    const delivery = await db.Delivery.findByPk(deliveryId, {
      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' }
          ]
        }
      ],
      transaction: txn
    });

    if (!delivery) {
      throw new Error(`Delivery with ID ${deliveryId} not found`);
    }

    // Check if Delivery is posted
    if (delivery.status !== 'POSTED') {
      throw new Error(`Cannot create invoice from delivery. Delivery ${delivery.delivery_number} is not posted`);
    }

    // Check if invoice already exists for this delivery
    const existingInvoice = await db.SalesInvoice.findOne({
      where: { delivery_id: deliveryId },
      transaction: txn
    });

    if (existingInvoice) {
      throw new Error(`Sales Invoice ${existingInvoice.invoice_number} already exists for Delivery ${delivery.delivery_number}`);
    }

    // Validate Delivery has items
    if (!delivery.items || delivery.items.length === 0) {
      throw new Error(`Delivery ${delivery.delivery_number} has no items`);
    }

    // Generate invoice number if not provided
    let invoiceNum = invoiceNumber;
    if (!invoiceNum) {
      invoiceNum = await generateInvoiceNumber(delivery.company_id, delivery.fiscal_year_id, txn);
    }

    // Check if invoice number already exists
    const existingInvoiceByNumber = await db.SalesInvoice.findOne({
      where: {
        company_id: delivery.company_id,
        plant_id: delivery.plant_id,
        fiscal_year_id: delivery.fiscal_year_id,
        invoice_number: invoiceNum
      },
      transaction: txn
    });

    if (existingInvoiceByNumber) {
      throw new Error(`Sales Invoice number ${invoiceNum} already exists`);
    }

    // Get customer GSTIN if available
    let customerGSTINId = null;
    if (delivery.customer) {
      const customerGSTIN = await db.BPGSTIN.findOne({
        where: {
          bp_id: delivery.customer_id,
          is_default: true,
          status: 'ACTIVE'
        },
        transaction: txn
      });
      if (customerGSTIN) {
        customerGSTINId = customerGSTIN.id;
      }
    }

    // Create Sales Invoice header
    const invoiceData = {
      company_id: delivery.company_id,
      plant_id: delivery.plant_id,
      fiscal_year_id: delivery.fiscal_year_id,
      invoice_number: invoiceNum,
      invoice_date: invoiceDate,
      customer_id: delivery.customer_id,
      customer_address_id: delivery.customer_address_id,
      customer_gstin_id: customerGSTINId,
      delivery_id: delivery.id, // Link to Delivery
      so_id: delivery.so_id, // Link to Sales Order if exists
      customer_po_id: delivery.customer_po_id, // Link to Customer PO if exists
      currency_id: delivery.customerPO?.currency_id || delivery.salesOrder?.currency_id || null,
      place_of_supply: delivery.customerAddress?.state?.gst_state_code || null,
      payment_terms: delivery.customerPO?.payment_terms || delivery.salesOrder?.payment_terms || null,
      sub_total: delivery.sub_total,
      discount_amount: delivery.discount_amount,
      tax_amount: delivery.tax_amount,
      total_amount: delivery.total_amount,
      status: 'DRAFT', // Start as DRAFT, user can post later
      remarks: `Created from Delivery: ${delivery.delivery_number}`,
      created_by: userId,
      updated_by: userId
    };

    const salesInvoice = await db.SalesInvoice.create(invoiceData, { transaction: txn });

    // Create Sales Invoice Items from Delivery Items
    const invoiceItems = [];
    for (let i = 0; i < delivery.items.length; i++) {
      const deliveryItem = delivery.items[i];
      
      const invoiceItem = await db.SalesInvoiceItem.create({
        invoice_id: salesInvoice.id,
        item_number: i + 1,
        material_id: deliveryItem.material_id,
        description: deliveryItem.description,
        quantity: deliveryItem.quantity,
        uom_id: deliveryItem.uom_id,
        unit_price: deliveryItem.unit_price,
        discount_percentage: deliveryItem.discount_percentage || 0,
        discount_amount: deliveryItem.discount_amount || 0,
        net_amount: deliveryItem.net_amount || (deliveryItem.quantity * deliveryItem.unit_price),
        tax_rate: deliveryItem.tax_rate || 0,
        tax_amount: deliveryItem.tax_amount || 0,
        total_amount: deliveryItem.total_amount,
        hsn_code: deliveryItem.hsn_code,
        remarks: deliveryItem.remarks || `From Delivery Item: ${deliveryItem.item_number}`
      }, { transaction: txn });

      invoiceItems.push(invoiceItem);
    }

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

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

    // Reload Sales Invoice with all associations
    const createdInvoice = await db.SalesInvoice.findByPk(salesInvoice.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.BPGSTIN, as: 'customerGSTIN' },
        { model: db.Delivery, as: 'delivery' },
        { model: db.SalesOrder, as: 'salesOrder' },
        { model: db.CustomerPO, as: 'customerPO' },
        { model: db.Currency, as: 'currency' },
        {
          model: db.SalesInvoiceItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

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

/**
 * Update Customer PO status based on invoice completion
 * If all items are delivered and invoiced, status can be CLOSED
 */
async function updateCustomerPOInvoiceStatus(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;
  }

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

  // Check if all posted deliveries are invoiced
  const postedDeliveries = await db.Delivery.findAll({
    where: {
      customer_po_id: customerPOId,
      status: 'POSTED'
    },
    include: [
      {
        model: db.SalesInvoice,
        as: 'invoices',
        where: { status: 'POSTED' },
        required: false
      }
    ],
    transaction
  });

  const allDeliveriesInvoiced = postedDeliveries.length === 0 || 
    postedDeliveries.every(delivery => {
      return delivery.invoices && delivery.invoices.length > 0;
    });

  // If all items are delivered and all deliveries are invoiced, can close
  // Note: Status change to CLOSED might require additional business logic
  // For now, we just ensure status reflects delivery completion
  if (allDelivered && customerPO.status !== 'DELIVERED') {
    await customerPO.update({ status: 'DELIVERED' }, { transaction });
  }
}

/**
 * Generate Sales Invoice number (uses Number Format Master with company code and FY reset)
 */
async function generateInvoiceNumber(companyId, fiscalYearId, transaction) {
  try {
    return await numberFormatService.generateNextNumber(companyId, 'SALES_INVOICE', fiscalYearId, transaction);
  } catch (err) {
    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 = `INV${year}`;
    const lastInvoice = await db.SalesInvoice.findOne({
      where: { company_id: companyId, fiscal_year_id: fiscalYearId, invoice_number: { [Op.like]: `${prefix}%` } },
      order: [['invoice_number', 'DESC']],
      transaction
    });
    let sequence = 1;
    if (lastInvoice) {
      const lastSequence = parseInt(lastInvoice.invoice_number.slice(-4)) || 0;
      sequence = lastSequence + 1;
    }
    return `${prefix}${sequence.toString().padStart(4, '0')}`;
  }
}

