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

/**
 * Create Customer PO from Sales Order
 * 
 * @param {Integer} salesOrderId - Sales Order ID
 * @param {Object} options - Additional options
 * @param {String} options.poNumber - Custom PO number (optional, will be generated if not provided)
 * @param {Date} options.poDate - PO date (optional, defaults to today)
 * @param {Integer} options.userId - User ID creating the Customer PO
 * @param {Object} options.transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Created Customer PO with items
 */
exports.createCustomerPOFromSalesOrder = async (salesOrderId, options = {}) => {
  const {
    poNumber = null,
    poDate = new Date().toISOString().split('T')[0],
    userId = null,
    transaction = null
  } = options;

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

  try {
    // Load Sales Order with all necessary data
    const salesOrder = await db.SalesOrder.findByPk(salesOrderId, {
      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.SalesOrderItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ],
      transaction: txn
    });

    if (!salesOrder) {
      throw new Error(`Sales Order with ID ${salesOrderId} not found`);
    }

    // Check if Customer PO already exists for this Sales Order
    if (salesOrder.customer_po_id) {
      const existingCPO = await db.CustomerPO.findByPk(salesOrder.customer_po_id, {
        include: [
          { model: db.Company, as: 'company' },
          { model: db.Plant, as: 'plant' },
          {
            model: db.CustomerPOItem,
            as: 'items'
          }
        ],
        transaction: txn
      });
      if (existingCPO) {
        throw new Error(`Customer PO ${existingCPO.po_number} already exists for Sales Order ${salesOrder.so_number}`);
      }
    }

    // Validate Sales Order has items
    if (!salesOrder.items || salesOrder.items.length === 0) {
      throw new Error(`Sales Order ${salesOrder.so_number} has no items`);
    }

    // Generate PO number if not provided
    let customerPONumber = poNumber;
    if (!customerPONumber) {
      customerPONumber = await generateCustomerPONumber(salesOrder.company_id, salesOrder.fiscal_year_id, txn);
    }

    // Check if PO number already exists
    const existingPO = await db.CustomerPO.findOne({
      where: {
        company_id: salesOrder.company_id,
        plant_id: salesOrder.plant_id,
        fiscal_year_id: salesOrder.fiscal_year_id,
        po_number: customerPONumber
      },
      transaction: txn
    });

    if (existingPO) {
      throw new Error(`Customer PO number ${customerPONumber} already exists`);
    }

    // Create Customer PO header
    const customerPOData = {
      company_id: salesOrder.company_id,
      plant_id: salesOrder.plant_id,
      fiscal_year_id: salesOrder.fiscal_year_id,
      po_number: customerPONumber,
      po_date: poDate,
      customer_id: salesOrder.customer_id,
      customer_address_id: salesOrder.customer_address_id,
      sales_order_id: salesOrder.id, // Link to Sales Order
      currency_id: salesOrder.currency_id,
      payment_terms: salesOrder.payment_terms,
      delivery_terms: salesOrder.delivery_terms,
      delivery_date: salesOrder.delivery_date,
      sub_total: salesOrder.sub_total,
      discount_amount: salesOrder.discount_amount,
      tax_amount: salesOrder.tax_amount,
      total_amount: salesOrder.total_amount,
      status: 'DRAFT', // Start as DRAFT, user can approve later
      remarks: `Created from Sales Order: ${salesOrder.so_number}`,
      created_by: userId,
      updated_by: userId
    };

    const customerPO = await db.CustomerPO.create(customerPOData, { transaction: txn });

    // Create Customer PO Items from Sales Order Items
    const customerPOItems = [];
    for (let i = 0; i < salesOrder.items.length; i++) {
      const soItem = salesOrder.items[i];
      
      const customerPOItem = await db.CustomerPOItem.create({
        po_id: customerPO.id,
        item_number: i + 1,
        material_id: soItem.material_id,
        description: soItem.description,
        quantity: soItem.quantity,
        delivered_quantity: 0, // Start with 0 delivered
        uom_id: soItem.uom_id,
        unit_price: soItem.unit_price,
        discount_percentage: soItem.discount_percentage || 0,
        discount_amount: soItem.discount_amount || 0,
        net_amount: soItem.net_amount || (soItem.quantity * soItem.unit_price),
        tax_rate: soItem.tax_rate || 0,
        tax_amount: soItem.tax_amount || 0,
        total_amount: soItem.total_amount,
        hsn_code: soItem.hsn_code,
        delivery_date: soItem.delivery_date,
        storage_location: soItem.storage_location,
        remarks: soItem.remarks || `From Sales Order Item: ${soItem.item_number}`
      }, { transaction: txn });

      customerPOItems.push(customerPOItem);
    }

    // Link Sales Order to Customer PO (bidirectional link)
    await salesOrder.update({
      customer_po_id: customerPO.id
    }, { transaction: txn });

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

    // Reload Customer PO with all associations
    const createdCPO = await db.CustomerPO.findByPk(customerPO.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.Currency, as: 'currency' },
        {
          model: db.CustomerPOItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        }
      ]
    });

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

/**
 * Link existing Customer PO to Sales Order
 * 
 * @param {Integer} salesOrderId - Sales Order ID
 * @param {Integer} customerPOId - Customer PO ID
 * @param {Integer} userId - User ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Updated Sales Order and Customer PO
 */
exports.linkCustomerPOToSalesOrder = async (salesOrderId, customerPOId, userId, transaction = null) => {
  const txn = transaction || await db.sequelize.transaction();
  const shouldCommit = !transaction;

  try {
    const salesOrder = await db.SalesOrder.findByPk(salesOrderId, { transaction: txn });
    if (!salesOrder) {
      throw new Error(`Sales Order with ID ${salesOrderId} not found`);
    }

    const customerPO = await db.CustomerPO.findByPk(customerPOId, { transaction: txn });
    if (!customerPO) {
      throw new Error(`Customer PO with ID ${customerPOId} not found`);
    }

    // Validate they belong to same company and customer
    if (salesOrder.company_id !== customerPO.company_id) {
      throw new Error('Sales Order and Customer PO must belong to the same company');
    }

    if (salesOrder.customer_id !== customerPO.customer_id) {
      throw new Error('Sales Order and Customer PO must belong to the same customer');
    }

    // Check if Sales Order already linked to another Customer PO
    if (salesOrder.customer_po_id && salesOrder.customer_po_id !== customerPOId) {
      throw new Error(`Sales Order ${salesOrder.so_number} is already linked to Customer PO ID ${salesOrder.customer_po_id}`);
    }

    // Check if Customer PO already linked to another Sales Order
    if (customerPO.sales_order_id && customerPO.sales_order_id !== salesOrderId) {
      throw new Error(`Customer PO ${customerPO.po_number} is already linked to Sales Order ID ${customerPO.sales_order_id}`);
    }

    // Create bidirectional link
    await salesOrder.update({
      customer_po_id: customerPOId,
      updated_by: userId
    }, { transaction: txn });

    await customerPO.update({
      sales_order_id: salesOrderId,
      updated_by: userId
    }, { transaction: txn });

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

    // Reload both records
    const updatedSO = await db.SalesOrder.findByPk(salesOrderId, {
      include: [
        { model: db.CustomerPO, as: 'customerPO' }
      ]
    });

    const updatedCPO = await db.CustomerPO.findByPk(customerPOId, {
      include: [
        { model: db.SalesOrder, as: 'salesOrder' }
      ]
    });

    return {
      salesOrder: updatedSO,
      customerPO: updatedCPO
    };
  } catch (error) {
    if (shouldCommit) {
      await txn.rollback();
    }
    throw error;
  }
};

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

  // Use fiscal year start year for numbering
  const year = new Date(fiscalYear.start_date).getFullYear();
  const prefix = `CPO${year}`;

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

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

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

