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

/**
 * Build PR-level where clause for report filters.
 */
function buildPrWhere(query, req) {
  const { search, status, companyId, plantId, dateFrom, dateTo } = query;
  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 (status && status !== 'ALL') where.status = status;
  if (dateFrom || dateTo) {
    where.pr_date = {};
    if (dateFrom) where.pr_date[Op.gte] = dateFrom;
    if (dateTo) where.pr_date[Op.lte] = dateTo;
  }
  if (search && String(search).trim()) {
    where.pr_number = { [Op.like]: `%${String(search).trim()}%` };
  }
  return where;
}

/**
 * Detailed Purchase Requisition Report – one row per PR line item with header info.
 * Query params: companyId, plantId, status, search, dateFrom, dateTo, page, limit, export
 */
exports.getDetailedPurchaseRequisitionReport = asyncHandler(async (req, res) => {
  try {
    const {
      page = 1,
      limit = 25,
      export: exportFlag,
      search,
      status,
      companyId,
      plantId,
      dateFrom,
      dateTo,
    } = req.query;

    const isExport = exportFlag === 'true' || exportFlag === true || exportFlag === 1 || exportFlag === '1';
    const effectiveLimit = isExport ? 50000 : parseInt(limit, 10) || 25;
    const effectivePage = isExport ? 1 : parseInt(page, 10) || 1;
    const { limit: limitNum, offset } = getPagination(effectivePage, effectiveLimit);

    const prWhere = buildPrWhere(req.query, req);

    const prInclude = [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'], required: false },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'], required: false },
      { model: db.User, as: 'requestedByUser', attributes: ['id', 'first_name', 'last_name', 'username'], required: false },
    ];

    const result = await db.PurchaseRequisitionItem.findAndCountAll({
      where: {},
      include: [
        {
          model: db.PurchaseRequisition,
          as: 'purchaseRequisition',
          where: prWhere,
          required: true,
          include: prInclude,
          attributes: ['id', 'pr_number', 'pr_date', 'required_date', 'status', 'total_amount', 'remarks'],
        },
        { model: db.Material, as: 'material', attributes: ['id', 'material_number', 'description'], required: false },
        { model: db.UOM, as: 'uom', attributes: ['id', 'uom_code', 'uom_name'], required: false },
      ],
      order: [
        ['purchaseRequisition', 'pr_date', 'DESC'],
        ['purchaseRequisition', 'pr_number', 'DESC'],
        ['line_number', 'ASC'],
      ],
      limit: limitNum,
      offset,
    });

    const items = (result.rows || []).map((row) => {
      const item = row?.toJSON ? row.toJSON() : row;
      const pr = item.purchaseRequisition || {};
      const companyName = pr.company?.name || pr.company?.code || '';
      const plantName = pr.plant?.name || pr.plant?.code || '';
      const requestedBy = pr.requestedByUser
        ? [pr.requestedByUser.first_name, pr.requestedByUser.last_name].filter(Boolean).join(' ') || pr.requestedByUser.username
        : '';
      return {
        id: `pr-${pr.id}-item-${item.id}`,
        pr_id: pr.id,
        pr_number: pr.pr_number || '',
        pr_date: pr.pr_date || '',
        required_date: pr.required_date || '',
        company: companyName,
        plant: plantName,
        requested_by: requestedBy,
        total_amount: pr.total_amount != null ? Number(pr.total_amount) : '',
        status: pr.status || '',
        remarks: pr.remarks || '',
        line_number: item.line_number != null ? item.line_number : '',
        material_code: item.material?.material_number || '',
        material_name: item.material?.description || '',
        item_description: item.description || '',
        quantity: item.quantity != null ? Number(item.quantity) : '',
        uom: item.uom?.uom_code || item.uom?.uom_name || '',
        unit_price: item.unit_price != null ? Number(item.unit_price) : '',
        estimated_amount: item.estimated_amount != null ? Number(item.estimated_amount) : '',
        item_required_date: item.required_date || '',
        gross_required_quantity: item.gross_required_quantity != null ? Number(item.gross_required_quantity) : '',
        available_stock_at_pr_creation: item.available_stock_at_pr_creation != null ? Number(item.available_stock_at_pr_creation) : '',
        item_remarks: item.remarks || '',
      };
    });

    const response = getPagingData(
      { count: result.count || 0, rows: items },
      effectivePage,
      limitNum
    );

    return sendResponse(res, 200, true, 'Detailed Purchase Requisition Report generated successfully', response);
  } catch (error) {
    console.error('Detailed Purchase Requisition Report error:', error);
    return sendError(res, error.message || 'Failed to generate Detailed Purchase Requisition Report', 500);
  }
});
