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

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

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

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

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

  const so = await db.SalesOrder.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.SalesQuotation, as: 'salesQuotation' },
      { 
        model: db.CustomerPO, 
        as: 'customerPO',
        required: false,
        include: [
          { model: db.Company, as: 'company' },
          { model: db.Plant, as: 'plant' },
          {
            model: db.CustomerPOItem,
            as: 'items',
            include: [
              { model: db.Material, as: 'material' },
              { model: db.UOM, as: 'uom' }
            ]
          }
        ]
      },
      { model: db.Currency, as: 'currency' },
      {
        model: db.SalesOrderItem,
        as: 'items',
        include: [
          { model: db.Material, as: 'material' },
          { model: db.UOM, as: 'uom' }
        ],
        order: [['item_number', 'ASC']]
      }
    ]
  });

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

  sendResponse(res, 200, true, 'Sales Order retrieved successfully', so);
});

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

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

  try {
    if (!data.so_number) {
      data.so_number = await generateSONumber(companyId);
    }

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

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

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

    const so = await db.SalesOrder.create(soData, { 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.SalesOrderItem.create({
          so_id: so.id,
          item_number: i + 1,
          material_id: item.material_id,
          description: item.description,
          quantity: quantity,
          delivered_quantity: 0,
          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,
          storage_location: item.storage_location,
          remarks: item.remarks
        }, { transaction });
      }
    }

    await transaction.commit();

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

    sendResponse(res, 201, true, 'Sales Order created successfully', createdSO);
  } 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 so = await db.SalesOrder.findByPk(id);

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

  // Price/GST locking: Prevent changes to price, tax, discount after approval
  const lockedFields = ['unit_price', 'tax_rate', 'discount_percentage'];
  if (so.status !== 'DRAFT' && so.status !== 'SUBMITTED') {
    // Check if any locked fields are being changed
    if (data.items && Array.isArray(data.items)) {
      const existingItems = await db.SalesOrderItem.findAll({
        where: { so_id: so.id },
        order: [['item_number', 'ASC']]
      });

      for (let i = 0; i < data.items.length && i < existingItems.length; i++) {
        const newItem = data.items[i];
        const existingItem = existingItems[i];
        
        if (existingItem.material_id === newItem.material_id) {
          if (Math.abs(parseFloat(existingItem.unit_price) - parseFloat(newItem.unit_price || 0)) > 0.01) {
            return sendError(res, `Cannot change unit price for item ${i + 1} after approval. Price is locked.`, 400);
          }
          if (Math.abs(parseFloat(existingItem.tax_rate) - parseFloat(newItem.tax_rate || 0)) > 0.01) {
            return sendError(res, `Cannot change tax rate for item ${i + 1} after approval. Tax rate is locked.`, 400);
          }
          if (Math.abs(parseFloat(existingItem.discount_percentage) - parseFloat(newItem.discount_percentage || 0)) > 0.01) {
            return sendError(res, `Cannot change discount for item ${i + 1} after approval. Discount is locked.`, 400);
          }
        }
      }
    }
    
    if (so.status === 'APPROVED' || so.status === 'RELEASED' || so.status === 'PARTIALLY_DELIVERED') {
      return sendError(res, `Cannot update Sales Order in ${so.status} status. Only DRAFT and SUBMITTED orders can be updated.`, 400);
    }
  }

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

  try {
    data.updated_by = userId;

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

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

    await so.update(soData, { transaction });

    await db.SalesOrderItem.destroy({ where: { so_id: so.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.SalesOrderItem.create({
          so_id: so.id,
          item_number: i + 1,
          material_id: item.material_id,
          description: item.description,
          quantity: quantity,
          delivered_quantity: item.delivered_quantity || 0,
          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,
          storage_location: item.storage_location,
          remarks: item.remarks
        }, { transaction });
      }
    }

    await transaction.commit();

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

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

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

  const so = await db.SalesOrder.findByPk(id);

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

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

  await db.SalesOrderItem.destroy({ where: { so_id: so.id } });
  await so.destroy();

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

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

  const so = await db.SalesOrder.findByPk(id);

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

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

  // Validate status transitions
  const statusFlow = {
    'DRAFT': ['SUBMITTED', 'REJECTED'],
    'SUBMITTED': ['APPROVED', 'REJECTED', 'DRAFT'],
    'APPROVED': ['RELEASED', 'REJECTED'],
    'RELEASED': ['PARTIALLY_DELIVERED', 'DELIVERED'],
    'PARTIALLY_DELIVERED': ['DELIVERED', 'CLOSED'],
    'DELIVERED': ['CLOSED'],
    'REJECTED': ['DRAFT'],
    'CLOSED': [] // Terminal state
  };

  if (!statusFlow[so.status] || !statusFlow[so.status].includes(status)) {
    return sendError(res, `Invalid status transition from ${so.status} to ${status}`, 400);
  }

  const updateData = { status, updated_by: userId };

  if (status === 'SUBMITTED') {
    updateData.submitted_at = new Date();
    updateData.submitted_by = userId;
  } else if (status === 'APPROVED') {
    // Perform ATP check before approval
    const items = await db.SalesOrderItem.findAll({
      where: { so_id: so.id },
      include: [{ model: db.Material, as: 'material' }]
    });

    const atpChecks = await atpService.checkATP(
      items.map(item => ({
        material_id: item.material_id,
        plant_id: so.plant_id,
        quantity: parseFloat(item.quantity),
        storage_location: item.storage_location
      }))
    );

    const unavailableItems = atpChecks.filter(check => !check.is_available);
    if (unavailableItems.length > 0) {
      const materials = unavailableItems.map(item => {
        const material = items.find(i => i.material_id === item.material_id)?.material;
        return `${material?.material_number || item.material_id} (Available: ${item.available_quantity}, Required: ${item.requested_quantity})`;
      }).join(', ');
      return sendError(res, `ATP check failed. Insufficient stock for: ${materials}`, 400);
    }

    updateData.approved_at = new Date();
    updateData.approved_by = userId;
  } else if (status === 'RELEASED') {
    // Perform ATP check before release (same as approval)
    const items = await db.SalesOrderItem.findAll({
      where: { so_id: so.id },
      include: [{ model: db.Material, as: 'material' }]
    });

    const atpChecks = await atpService.checkATP(
      items.map(item => ({
        material_id: item.material_id,
        plant_id: so.plant_id,
        quantity: parseFloat(item.quantity),
        storage_location: item.storage_location
      }))
    );

    const unavailableItems = atpChecks.filter(check => !check.is_available);
    if (unavailableItems.length > 0) {
      const materials = unavailableItems.map(item => {
        const material = items.find(i => i.material_id === item.material_id)?.material;
        return `${material?.material_number || item.material_id} (Available: ${item.available_quantity}, Required: ${item.requested_quantity})`;
      }).join(', ');
      return sendError(res, `ATP check failed. Insufficient stock for: ${materials}`, 400);
    }
  }

  await so.update(updateData);

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

  sendResponse(res, 200, true, 'Sales Order status updated successfully', updatedSO);
});

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

  const so = await db.SalesOrder.findByPk(id, {
    include: [{ model: db.SalesOrderItem, as: 'items' }]
  });

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

  if (so.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Sales Orders can be submitted', 400);
  }

  if (!so.items || so.items.length === 0) {
    return sendError(res, 'Cannot submit Sales Order without items', 400);
  }

  await so.update({
    status: 'SUBMITTED',
    submitted_at: new Date(),
    submitted_by: userId,
    updated_by: userId
  });

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

  sendResponse(res, 200, true, 'Sales Order submitted successfully', updatedSO);
});

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

  const so = await db.SalesOrder.findByPk(id, {
    include: [
      { model: db.SalesOrderItem, as: 'items', include: [{ model: db.Material, as: 'material' }] }
    ]
  });

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

  if (so.status !== 'SUBMITTED') {
    return sendError(res, 'Only SUBMITTED Sales Orders can be approved', 400);
  }

  // Perform ATP check
  const atpChecks = await atpService.checkATP(
    so.items.map(item => ({
      material_id: item.material_id,
      plant_id: so.plant_id,
      quantity: parseFloat(item.quantity),
      storage_location: item.storage_location
    }))
  );

  const unavailableItems = atpChecks.filter(check => !check.is_available);
  if (unavailableItems.length > 0) {
    const materials = unavailableItems.map(item => {
      const material = so.items.find(i => i.material_id === item.material_id)?.material;
      return `${material?.material_number || item.material_id} (Available: ${item.available_quantity}, Required: ${item.requested_quantity})`;
    }).join(', ');
    return sendError(res, `ATP check failed. Insufficient stock for: ${materials}`, 400);
  }

  await so.update({
    status: 'APPROVED',
    approved_at: new Date(),
    approved_by: userId,
    updated_by: userId
  });

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

  sendResponse(res, 200, true, 'Sales Order approved successfully', updatedSO);
});

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

  const so = await db.SalesOrder.findByPk(id, {
    include: [
      { model: db.SalesOrderItem, as: 'items', include: [{ model: db.Material, as: 'material' }] }
    ]
  });

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

  if (so.status !== 'APPROVED') {
    return sendError(res, 'Only APPROVED Sales Orders can be released', 400);
  }

  // Perform ATP check before release
  const atpChecks = await atpService.checkATP(
    so.items.map(item => ({
      material_id: item.material_id,
      plant_id: so.plant_id,
      quantity: parseFloat(item.quantity),
      storage_location: item.storage_location
    }))
  );

  const unavailableItems = atpChecks.filter(check => !check.is_available);
  if (unavailableItems.length > 0) {
    const materials = unavailableItems.map(item => {
      const material = so.items.find(i => i.material_id === item.material_id)?.material;
      return `${material?.material_number || item.material_id} (Available: ${item.available_quantity}, Required: ${item.requested_quantity})`;
    }).join(', ');
    return sendError(res, `ATP check failed. Insufficient stock for: ${materials}`, 400);
  }

  await so.update({
    status: 'RELEASED',
    updated_by: userId
  });

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

  sendResponse(res, 200, true, 'Sales Order released successfully', updatedSO);
});

/**
 * Create Customer PO from Sales Order
 * POST /salesorder/:id/create-customer-po
 */
exports.createCustomerPO = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const { po_number, po_date } = req.body;
  const userId = req.user?.userId || null;

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

  try {
    const salesOrderToCustomerPOService = require('../services/salesOrderToCustomerPOService');
    
    const customerPO = await salesOrderToCustomerPOService.createCustomerPOFromSalesOrder(id, {
      poNumber: po_number,
      poDate: po_date,
      userId
    });

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

/**
 * Link existing Customer PO to Sales Order
 * POST /salesorder/:id/link-customer-po
 * Body: { customer_po_id: 123 }
 */
exports.linkCustomerPO = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const { customer_po_id } = req.body;
  const userId = req.user?.userId || null;

  if (!customer_po_id) {
    return sendError(res, 'Customer PO ID is required', 400);
  }

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

  try {
    const salesOrderToCustomerPOService = require('../services/salesOrderToCustomerPOService');
    
    const result = await salesOrderToCustomerPOService.linkCustomerPOToSalesOrder(
      id,
      customer_po_id,
      userId
    );

    sendResponse(res, 200, true, 'Customer PO linked to Sales Order successfully', result);
  } catch (error) {
    console.error('Error linking Customer PO to Sales Order:', error);
    sendError(res, error.message || 'Failed to link Customer PO to Sales Order', 400);
  }
});

/**
 * Get Customer PO for a Sales Order
 * GET /salesorder/:id/customer-po
 */
exports.getCustomerPO = asyncHandler(async (req, res) => {
  const { id } = req.params;

  const salesOrder = await db.SalesOrder.findByPk(id, {
    include: [
      {
        model: db.CustomerPO,
        as: 'customerPO',
        include: [
          { model: db.Company, as: 'company' },
          { model: db.Plant, as: 'plant' },
          { model: db.BusinessPartner, as: 'customer' },
          {
            model: db.CustomerPOItem,
            as: 'items',
            include: [
              { model: db.Material, as: 'material' },
              { model: db.UOM, as: 'uom' }
            ]
          }
        ]
      }
    ]
  });

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

  if (!salesOrder.customerPO) {
    return sendError(res, 'No Customer PO linked to this Sales Order', 404);
  }

  sendResponse(res, 200, true, 'Customer PO retrieved successfully', salesOrder.customerPO);
});

