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

exports.getAll = asyncHandler(async (req, res) => {
  const { page = 1, limit = 25, search, companyId, plantId, materialId, isActive } = 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 (materialId) {
    where.material_id = materialId;
  }

  if (isActive !== undefined) {
    where.is_active = isActive === 'true';
  }

  if (search) {
    where[Op.or] = [
      { '$material.material_number$': { [Op.like]: `%${search}%` } },
      { '$material.description$': { [Op.like]: `%${search}%` } }
    ];
  }

  const records = await db.MaterialReorderLevel.findAndCountAll({
    where,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description', 'material_type'] }
    ],
    limit: limitNum,
    offset,
    order: [['material_id', 'ASC'], ['storage_location', 'ASC']]
  });

  // Add current stock balance to each record
  const itemsWithStock = await Promise.all(
    records.rows.map(async (item) => {
      const balance = await stockLedgerService.getCurrentBalance(
        item.company_id,
        item.plant_id,
        item.material_id,
        item.storage_location
      );
      return {
        ...item.toJSON(),
        current_stock: balance,
        is_below_reorder_level: balance <= item.reorder_level
      };
    })
  );

  const response = {
    ...getPagingData(records, page, limitNum),
    items: itemsWithStock
  };

  sendResponse(res, 200, true, 'Reorder levels retrieved successfully', response);
});

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

  const reorderLevel = await db.MaterialReorderLevel.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.Material, as: 'material' },
      { model: db.User, as: 'createdByUser', attributes: ['id', 'username', 'email', 'first_name', 'last_name'] },
      { model: db.User, as: 'updatedByUser', attributes: ['id', 'username', 'email', 'first_name', 'last_name'] }
    ]
  });

  if (!reorderLevel) {
    return sendError(res, 404, 'Reorder level not found');
  }

  // Add current stock balance
  const balance = await stockLedgerService.getCurrentBalance(
    reorderLevel.company_id,
    reorderLevel.plant_id,
    reorderLevel.material_id,
    reorderLevel.storage_location
  );

  const response = {
    ...reorderLevel.toJSON(),
    current_stock: balance,
    is_below_reorder_level: balance <= reorderLevel.reorder_level
  };

  sendResponse(res, 200, true, 'Reorder level retrieved successfully', response);
});

exports.create = asyncHandler(async (req, res) => {
  const data = req.body;

  if (!data.company_id || !data.plant_id || !data.material_id) {
    return sendError(res, 400, 'Company ID, Plant ID, and Material ID are required');
  }

  // Check if reorder level already exists for this material and storage location
  const existing = await db.MaterialReorderLevel.findOne({
    where: {
      company_id: data.company_id,
      plant_id: data.plant_id,
      material_id: data.material_id,
      storage_location: data.storage_location || null
    }
  });

  if (existing) {
    return sendError(res, 400, 'Reorder level already exists for this material and storage location');
  }

  const reorderLevel = await db.MaterialReorderLevel.create({
    ...data,
    created_by: req.user.id,
    updated_by: req.user.id
  });

  const created = await db.MaterialReorderLevel.findByPk(reorderLevel.id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.Material, as: 'material' }
    ]
  });

  sendResponse(res, 201, true, 'Reorder level created successfully', created);
});

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

  const reorderLevel = await db.MaterialReorderLevel.findByPk(id);

  if (!reorderLevel) {
    return sendError(res, 404, 'Reorder level not found');
  }

  await reorderLevel.update({
    ...data,
    updated_by: req.user.id
  });

  const updated = await db.MaterialReorderLevel.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.Material, as: 'material' }
    ]
  });

  sendResponse(res, 200, true, 'Reorder level updated successfully', updated);
});

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

  const reorderLevel = await db.MaterialReorderLevel.findByPk(id);

  if (!reorderLevel) {
    return sendError(res, 404, 'Reorder level not found');
  }

  await reorderLevel.destroy();

  sendResponse(res, 200, true, 'Reorder level deleted successfully');
});

exports.getLowStockAlerts = asyncHandler(async (req, res) => {
  const { companyId, plantId } = req.query;
  const finalCompanyId = companyId || req.user?.companyId;

  if (!finalCompanyId || !plantId) {
    return sendError(res, 400, 'Company ID and Plant ID are required');
  }

  const where = {
    company_id: finalCompanyId,
    plant_id: plantId,
    is_active: true,
    status: 'ACTIVE'
  };

  const reorderLevels = await db.MaterialReorderLevel.findAll({
    where,
    include: [
      { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'] }
    ]
  });

  const alerts = [];

  for (const reorderLevel of reorderLevels) {
    const balance = await stockLedgerService.getCurrentBalance(
      reorderLevel.company_id,
      reorderLevel.plant_id,
      reorderLevel.material_id,
      reorderLevel.storage_location
    );

    if (balance <= reorderLevel.reorder_level) {
      alerts.push({
        ...reorderLevel.toJSON(),
        current_stock: balance,
        shortfall: reorderLevel.reorder_level - balance,
        reorder_quantity: reorderLevel.reorder_quantity || (reorderLevel.maximum_stock ? reorderLevel.maximum_stock - balance : null)
      });
    }
  }

  sendResponse(res, 200, true, 'Low stock alerts retrieved successfully', { alerts, count: alerts.length });
});

