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 { generateNextNumber } = require('../services/numberFormatService');
const {
  fetchMaterialsForLineDescription,
} = require('../utils/rawMaterialLineDescription');
const { computeLineWeightKg } = require('../utils/lineWeightKg');

async function materialNumberByMaterialIds(materialIds, transaction) {
  const ids = [
    ...new Set(
      materialIds
        .map((id) => parseInt(id, 10))
        .filter((id) => Number.isFinite(id) && id > 0)
    )
  ];
  const map = new Map();
  if (ids.length === 0) return map;
  const mats = await db.Material.findAll({
    where: { id: { [Op.in]: ids } },
    attributes: ['id', 'material_number'],
    ...(transaction ? { transaction } : {})
  });
  mats.forEach((m) => {
    const mn = m.material_number != null ? String(m.material_number).trim() : '';
    if (mn) map.set(m.id, mn.slice(0, 100));
  });
  return map;
}

const enrichPRWithPOConsumption = async (prs) => {
  const prList = Array.isArray(prs) ? prs : [];
  if (prList.length === 0) return [];

  const prIds = prList.map((pr) => Number(pr.id)).filter((id) => Number.isFinite(id) && id > 0);
  if (prIds.length === 0) return prList;

  const prItems = await db.PurchaseRequisitionItem.findAll({
    where: { pr_id: { [Op.in]: prIds } },
    attributes: ['id', 'pr_id', 'line_number', 'material_id', 'uom_id', 'quantity'],
    order: [['pr_id', 'ASC'], ['line_number', 'ASC'], ['id', 'ASC']]
  });

  const poHeaders = await db.PurchaseOrder.findAll({
    where: { pr_id: { [Op.in]: prIds } },
    attributes: ['id', 'pr_id']
  });
  const poIds = poHeaders.map((po) => po.id);
  const poById = new Map(poHeaders.map((po) => [Number(po.id), Number(po.pr_id)]));

  let poItems = [];
  if (poIds.length > 0) {
    poItems = await db.PurchaseOrderItem.findAll({
      where: { po_id: { [Op.in]: poIds } },
      attributes: ['po_id', 'material_id', 'uom_id', 'quantity']
    });
  }

  const consumedByKey = new Map();
  poItems.forEach((item) => {
    const prId = poById.get(Number(item.po_id));
    if (!prId) return;
    const key = `${prId}::${item.material_id || ''}::${item.uom_id || ''}`;
    const qty = parseFloat(item.quantity || 0) || 0;
    consumedByKey.set(key, (consumedByKey.get(key) || 0) + qty);
  });

  const itemConsumptionById = new Map();
  const prSummary = new Map();

  prItems.forEach((item) => {
    const prId = Number(item.pr_id);
    const key = `${prId}::${item.material_id || ''}::${item.uom_id || ''}`;
    const remainingPool = parseFloat(consumedByKey.get(key) || 0) || 0;
    const reqQty = parseFloat(item.quantity || 0) || 0;
    const consumedOnLine = Math.min(reqQty, Math.max(remainingPool, 0));
    const pendingQty = Math.max(0, reqQty - consumedOnLine);
    consumedByKey.set(key, Math.max(0, remainingPool - consumedOnLine));

    itemConsumptionById.set(Number(item.id), {
      consumed_quantity: consumedOnLine,
      pending_quantity: pendingQty,
      is_fully_sent: pendingQty <= 0
    });

    const summary = prSummary.get(prId) || { total_items: 0, sent_items: 0, pending_items: 0 };
    summary.total_items += 1;
    if (pendingQty <= 0) summary.sent_items += 1;
    else summary.pending_items += 1;
    prSummary.set(prId, summary);
  });

  return prList.map((pr) => {
    const plain = pr.toJSON ? pr.toJSON() : { ...pr };
    const summary = prSummary.get(Number(plain.id)) || { total_items: 0, sent_items: 0, pending_items: 0 };
    plain.total_items = summary.total_items;
    plain.sent_items = summary.sent_items;
    plain.pending_items = summary.pending_items;
    plain.is_fully_sent = summary.total_items > 0 && summary.pending_items === 0;

    if (Array.isArray(plain.items)) {
      plain.items = plain.items
        .map((it) => {
          const cons = itemConsumptionById.get(Number(it.id)) || {
            consumed_quantity: 0,
            pending_quantity: parseFloat(it.quantity || 0) || 0,
            is_fully_sent: false
          };
          return { ...it, ...cons };
        })
        .sort((a, b) => {
          const aPending = parseFloat(a.pending_quantity || 0);
          const bPending = parseFloat(b.pending_quantity || 0);
          if ((aPending > 0) !== (bPending > 0)) return aPending > 0 ? -1 : 1;
          return Number(a.line_number || 0) - Number(b.line_number || 0);
        });
    }

    return plain;
  });
};

const parseProductionOrderNumberFromRemarks = (remarks) => {
  if (!remarks || typeof remarks !== 'string') return '';
  const m = remarks.match(/Production\s+Order:\s*([^\s|,\n]+)/i);
  return m ? String(m[1]).trim() : '';
};

/** Adds production_order_number (comma-separated if multiple MRs) from linked MaterialRequest → ProductionOrder, else PR remarks. */
const enrichPRWithProductionOrderInfo = async (prRows) => {
  const list = Array.isArray(prRows) ? prRows : [];
  if (list.length === 0) return list;

  const prIds = list.map((p) => Number(p.id)).filter((id) => Number.isFinite(id) && id > 0);
  if (prIds.length === 0) return list;

  const materialRequests = await db.MaterialRequest.findAll({
    where: { purchase_requisition_id: { [Op.in]: prIds } },
    attributes: ['id', 'purchase_requisition_id', 'production_order_id'],
    include: [
      {
        model: db.ProductionOrder,
        as: 'productionOrder',
        attributes: ['id', 'production_order_number'],
        required: false
      }
    ]
  });

  const numbersByPrId = new Map();
  materialRequests.forEach((mr) => {
    const prId = Number(mr.purchase_requisition_id);
    const poNum = String(mr.productionOrder?.production_order_number || '').trim();
    if (!prId || !poNum) return;
    if (!numbersByPrId.has(prId)) numbersByPrId.set(prId, new Set());
    numbersByPrId.get(prId).add(poNum);
  });

  return list.map((pr) => {
    const plain = pr && typeof pr.toJSON === 'function' ? pr.toJSON() : { ...pr };
    const id = Number(plain.id);
    const fromMr = numbersByPrId.get(id);
    const fromMrStr = fromMr && fromMr.size > 0 ? Array.from(fromMr).sort().join(', ') : '';
    const fromRemarks = parseProductionOrderNumberFromRemarks(plain.remarks || '');
    plain.production_order_number = fromMrStr || fromRemarks || null;
    return plain;
  });
};

/** Adds customer_display from linked MaterialRequest.customer or from ProductionOrder.customerPO.customer. */
const enrichPRWithCustomerInfo = async (prRows) => {
  const list = Array.isArray(prRows) ? prRows : [];
  if (list.length === 0) return list;

  const prIds = list.map((p) => Number(p.id)).filter((id) => Number.isFinite(id) && id > 0);
  if (prIds.length === 0) return list;

  const materialRequests = await db.MaterialRequest.findAll({
    where: { purchase_requisition_id: { [Op.in]: prIds } },
    attributes: ['id', 'purchase_requisition_id', 'customer_id', 'production_order_id'],
    include: [
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'], required: false },
      {
        model: db.ProductionOrder,
        as: 'productionOrder',
        attributes: ['id', 'customer_po_id'],
        required: false,
        include: [
          {
            model: db.CustomerPO,
            as: 'customerPO',
            attributes: ['id', 'customer_id'],
            required: false,
            include: [
              { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'], required: false }
            ]
          }
        ]
      }
    ]
  });

  const customerByPrId = new Map();
  const setIfEmpty = (prId, bp) => {
    if (!prId || !bp) return;
    if (customerByPrId.has(prId)) return;
    const name = String(bp.name || '').trim();
    const code = String(bp.bp_number || '').trim();
    const display = [name, code].filter(Boolean).join(' — ');
    if (display) customerByPrId.set(prId, display);
  };

  materialRequests.forEach((mr) => {
    const prId = Number(mr.purchase_requisition_id);
    setIfEmpty(prId, mr.customer);
    setIfEmpty(prId, mr.productionOrder?.customerPO?.customer);
  });

  return list.map((pr) => {
    const plain = pr && typeof pr.toJSON === 'function' ? pr.toJSON() : { ...pr };
    const id = Number(plain.id);
    plain.customer_display = customerByPrId.get(id) || null;
    return plain;
  });
};

const generatePRNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'PURCHASE_REQUISITION', fiscalYearId, transaction);
  } catch (err) {
    console.warn('PR number from format master failed, using fallback:', err.message);
    const today = new Date();
    const year = today.getFullYear();
    const prefix = `PR${year}`;
    const lastPR = await db.PurchaseRequisition.findOne({
      where: { company_id: companyId, pr_number: { [Op.like]: `${prefix}%` } },
      order: [['pr_number', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    let sequence = 1;
    if (lastPR) {
      const lastSequence = parseInt(lastPR.pr_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 } = 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 (status) {
    where.status = status;
  }

  if (search) {
    where[Op.or] = [
      { pr_number: { [Op.like]: `%${search}%` } }
    ];
  }

  const includePOConsumption = String(req.query.includePOConsumption || '').toLowerCase() === 'true';

  const records = await db.PurchaseRequisition.findAndCountAll({
    where,
    include: [
      { model: db.Company, as: 'company', attributes: ['id', 'code', 'name'] },
      { model: db.Plant, as: 'plant', attributes: ['id', 'code', 'name'] },
      { model: db.User, as: 'requestedByUser', attributes: ['id', 'first_name', 'last_name'] },
      ...(includePOConsumption
        ? [{
            model: db.PurchaseRequisitionItem,
            as: 'items',
            attributes: ['id', 'line_number', 'material_id', 'uom_id', 'quantity'],
            required: false
          }]
        : [])
    ],
    limit: limitNum,
    offset,
    order: [['pr_date', 'DESC'], ['id', 'DESC']]
  });

  const response = getPagingData(records, page, limitNum);
  if (includePOConsumption && Array.isArray(response.items) && response.items.length > 0) {
    response.items = await enrichPRWithPOConsumption(response.items);
  }
  if (Array.isArray(response.items) && response.items.length > 0) {
    response.items = await enrichPRWithProductionOrderInfo(response.items);
    response.items = await enrichPRWithCustomerInfo(response.items);
  }
  sendResponse(res, 200, true, 'Purchase Requisitions retrieved successfully', response);
});

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

  const pr = await db.PurchaseRequisition.findByPk(id, {
    include: [
      { model: db.Company, as: 'company' },
      { model: db.Plant, as: 'plant' },
      { model: db.FiscalYear, as: 'fiscalYear' },
      { model: db.Department, as: 'department' },
      { model: db.CostCenter, as: 'costCenter' },
      {
        model: db.PurchaseRequisitionItem,
        as: 'items',
        include: [
          { 
            model: db.Material, 
            as: 'material',
            include: [
              { model: db.MaterialType, as: 'materialType', attributes: ['id', 'type_code', 'type_name'] },
              { model: db.MaterialThickness, as: 'materialThickness', attributes: ['id', 'thickness_code', 'thickness_value', 'thickness_unit'] },
              { model: db.MaterialGrade, as: 'materialGrade', attributes: ['id', 'grade_code', 'grade_name'] },
              { model: db.UOM, as: 'weightUOM', attributes: ['id', 'uom_code', 'uom_name'] }
            ]
          },
          { model: db.UOM, as: 'uom' }
        ],
        order: [['line_number', 'ASC']]
      },
      { model: db.User, as: 'requestedByUser', attributes: ['id', 'first_name', 'last_name'] },
      { model: db.User, as: 'submittedByUser', attributes: ['id', 'first_name', 'last_name'] },
      { model: db.User, as: 'approvedByUser', attributes: ['id', 'first_name', 'last_name'] },
      { model: db.User, as: 'rejectedByUser', attributes: ['id', 'first_name', 'last_name'] },
      { model: db.User, as: 'createdByUser', attributes: ['id', 'first_name', 'last_name'] }
    ]
  });

  if (!pr) {
    return sendError(res, 'Purchase Requisition not found', 404);
  }

  const [enrichedPR] = await enrichPRWithPOConsumption([pr]);
  const [withPo] = await enrichPRWithProductionOrderInfo([enrichedPR || pr]);
  const [finalPr] = await enrichPRWithCustomerInfo([withPo || enrichedPR || pr]);
  sendResponse(res, 200, true, 'Purchase Requisition retrieved successfully', finalPr || withPo || enrichedPR || pr);
});

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) {
    return sendError(res, 'Plant ID and Fiscal Year ID are required', 400);
  }

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

  try {
    // Get user's department_id for the Purchase Requisition (if not provided in request body)
    if (!data.department_id && userId) {
      const user = await db.User.findByPk(userId, {
        attributes: ['id', 'department_id'],
        transaction
      });
      if (user?.department_id) {
        data.department_id = user.department_id;
      }
    }

    if (!data.pr_number) {
      data.pr_number = await generatePRNumber(companyId, data.fiscal_year_id, transaction);
    }

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

    const { items, ...prData } = data;
    
    let totalAmount = 0;
    if (items && Array.isArray(items)) {
      items.forEach(item => {
        const amount = parseFloat(item.estimated_amount) || (parseFloat(item.quantity) || 0) * (parseFloat(item.unit_price) || 0);
        totalAmount += amount;
      });
    }
    prData.total_amount = totalAmount;

    const pr = await db.PurchaseRequisition.create(prData, { transaction });

    if (items && Array.isArray(items)) {
      const mnById = await materialNumberByMaterialIds(
        items.map((it) => it.material_id),
        transaction
      );
      const matMap = await fetchMaterialsForLineDescription(
        db,
        items.map((it) => it.material_id),
        { transaction }
      );
      const prItems = items.map((item, index) => {
        const mid = parseInt(item.material_id, 10);
        const explicitMn =
          item.material_number != null && String(item.material_number).trim()
            ? String(item.material_number).trim().slice(0, 100)
            : null;
        return {
          pr_id: pr.id,
          line_number: index + 1,
          material_id: item.material_id,
          material_number: explicitMn || mnById.get(mid) || null,
          description: item.description,
          quantity: item.quantity,
          uom_id: item.uom_id,
          unit_price: item.unit_price || 0,
          estimated_amount: item.estimated_amount || (item.quantity * (item.unit_price || 0)),
          required_date: item.required_date,
          remarks: item.remarks,
          line_weight_kg: computeLineWeightKg({
            quantity: item.quantity,
            line_weight_kg: item.line_weight_kg,
            material: matMap.get(mid),
          }),
        };
      });

      await db.PurchaseRequisitionItem.bulkCreate(prItems, { transaction });
    }

    await transaction.commit();

    const createdPR = await db.PurchaseRequisition.findByPk(pr.id, {
      include: [
        { model: db.Company, as: 'company' },
        { model: db.Plant, as: 'plant' },
        { model: db.FiscalYear, as: 'fiscalYear' },
        { model: db.Department, as: 'department' },
        { model: db.CostCenter, as: 'costCenter' },
        {
          model: db.PurchaseRequisitionItem,
          as: 'items',
          include: [
            { model: db.Material, as: 'material' },
            { model: db.UOM, as: 'uom' }
          ]
        },
        { model: db.User, as: 'createdByUser', attributes: ['id', 'first_name', 'last_name', 'department_id'] },
        { model: db.User, as: 'requestedByUser', attributes: ['id', 'first_name', 'last_name', 'department_id'] }
      ]
    });

    sendResponse(res, 201, true, 'Purchase Requisition created successfully', createdPR);
  } 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 pr = await db.PurchaseRequisition.findByPk(id);
  if (!pr) {
    return sendError(res, 'Purchase Requisition not found', 404);
  }

  if (pr.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Purchase Requisitions can be updated', 400);
  }

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

  try {
    const { items, ...prData } = data;
    prData.updated_by = userId;

    let totalAmount = 0;
    if (items && Array.isArray(items)) {
      items.forEach(item => {
        const amount = parseFloat(item.estimated_amount) || (parseFloat(item.quantity) || 0) * (parseFloat(item.unit_price) || 0);
        totalAmount += amount;
      });
    }
    prData.total_amount = totalAmount;

    await pr.update(prData, { transaction });

    if (items && Array.isArray(items)) {
      await db.PurchaseRequisitionItem.destroy({ where: { pr_id: pr.id }, transaction });

      const mnById = await materialNumberByMaterialIds(
        items.map((it) => it.material_id),
        transaction
      );
      const matMap = await fetchMaterialsForLineDescription(
        db,
        items.map((it) => it.material_id),
        { transaction }
      );
      const prItems = items.map((item, index) => {
        const mid = parseInt(item.material_id, 10);
        const explicitMn =
          item.material_number != null && String(item.material_number).trim()
            ? String(item.material_number).trim().slice(0, 100)
            : null;
        return {
          pr_id: pr.id,
          line_number: index + 1,
          material_id: item.material_id,
          material_number: explicitMn || mnById.get(mid) || null,
          description: item.description,
          quantity: item.quantity,
          uom_id: item.uom_id,
          unit_price: item.unit_price || 0,
          estimated_amount: item.estimated_amount || (item.quantity * (item.unit_price || 0)),
          required_date: item.required_date,
          remarks: item.remarks,
          line_weight_kg: computeLineWeightKg({
            quantity: item.quantity,
            line_weight_kg: item.line_weight_kg,
            material: matMap.get(mid),
          }),
        };
      });

      await db.PurchaseRequisitionItem.bulkCreate(prItems, { transaction });
    }

    await transaction.commit();

    const updatedPR = await db.PurchaseRequisition.findByPk(pr.id, {
      include: [
        { model: db.PurchaseRequisitionItem, as: 'items', include: [{ model: db.Material, as: 'material' }, { model: db.UOM, as: 'uom' }] }
      ]
    });

    sendResponse(res, 200, true, 'Purchase Requisition updated successfully', updatedPR);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

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

  const pr = await db.PurchaseRequisition.findByPk(id);
  if (!pr) {
    return sendError(res, 'Purchase Requisition not found', 404);
  }

  if (pr.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Purchase Requisitions can be submitted', 400);
  }

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

  sendResponse(res, 200, true, 'Purchase Requisition submitted successfully', pr);
});

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

  const pr = await db.PurchaseRequisition.findByPk(id);
  if (!pr) {
    return sendError(res, 'Purchase Requisition not found', 404);
  }

  if (pr.status !== 'SUBMITTED') {
    return sendError(res, 'Only SUBMITTED Purchase Requisitions can be approved', 400);
  }

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

  sendResponse(res, 200, true, 'Purchase Requisition approved successfully', pr);
});

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

  const pr = await db.PurchaseRequisition.findByPk(id);
  if (!pr) {
    return sendError(res, 'Purchase Requisition not found', 404);
  }

  if (pr.status !== 'SUBMITTED') {
    return sendError(res, 'Only SUBMITTED Purchase Requisitions can be rejected', 400);
  }

  await pr.update({
    status: 'REJECTED',
    rejected_at: new Date(),
    rejected_by: userId,
    rejection_reason: rejection_reason,
    updated_by: userId
  });

  sendResponse(res, 200, true, 'Purchase Requisition rejected successfully', pr);
});

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

  const pr = await db.PurchaseRequisition.findByPk(id);
  if (!pr) {
    return sendError(res, 'Purchase Requisition not found', 404);
  }

  if (pr.status !== 'DRAFT') {
    return sendError(res, 'Only DRAFT Purchase Requisitions can be deleted', 400);
  }

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

  try {
    await db.PurchaseRequisitionItem.destroy({ where: { pr_id: id }, transaction });
    await pr.destroy({ transaction });
    await transaction.commit();

    sendResponse(res, 200, true, 'Purchase Requisition deleted successfully', null);
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
});

