const db = require('../models');
const { sendResponse, sendError } = require('../utils/response');
const asyncHandler = require('../middlewares/asyncHandler');
const { getPagination, getPagingData } = require('../utils/pagination');
const { Op, QueryTypes } = require('sequelize');
const { generateNextNumber } = require('../services/numberFormatService');

const toNum = (value) => {
  const n = parseFloat(value);
  return Number.isFinite(n) ? n : 0;
};

const parseIdArray = (value) => {
  if (value == null || value === '') return [];
  if (Array.isArray(value)) {
    return Array.from(new Set(value.map((v) => parseInt(v, 10)).filter((v) => Number.isFinite(v) && v > 0)));
  }
  if (typeof value === 'number') {
    const n = parseInt(value, 10);
    return Number.isFinite(n) && n > 0 ? [n] : [];
  }
  const str = String(value).trim();
  if (!str) return [];
  try {
    const parsed = JSON.parse(str);
    if (Array.isArray(parsed)) return parseIdArray(parsed);
  } catch (e) {
    // Fallback parsing
  }
  if (str.includes(',')) return parseIdArray(str.split(','));
  const n = parseInt(str, 10);
  return Number.isFinite(n) && n > 0 ? [n] : [];
};

const toJson = (row) => (typeof row?.toJSON === 'function' ? row.toJSON() : row);

const generatePackingNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'PRODUCTION_PACKING', fiscalYearId, transaction);
  } catch (e) {
    const year = new Date().getFullYear();
    const prefix = `PACK${year}`;
    const last = await db.ProductionPacking.findOne({
      where: {
        company_id: companyId,
        packing_number: { [Op.like]: `${prefix}%` }
      },
      attributes: ['packing_number'],
      order: [['packing_number', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    let seq = 1;
    if (last?.packing_number) {
      const parsed = parseInt(String(last.packing_number).slice(-4), 10) || 0;
      seq = parsed + 1;
    }
    return `${prefix}${String(seq).padStart(4, '0')}`;
  }
};

const getMainRouteCard = async (routeCardId, transaction = null) => {
  let current = await db.RouteCard.findByPk(routeCardId, {
    include: [{ model: db.RouteCard, as: 'previousRouteCard', attributes: ['id', 'previous_route_card_id'] }],
    ...(transaction ? { transaction } : {})
  });
  if (!current) return null;
  while (current.previous_route_card_id) {
    const parent = await db.RouteCard.findByPk(current.previous_route_card_id, {
      include: [{ model: db.RouteCard, as: 'previousRouteCard', attributes: ['id', 'previous_route_card_id'] }],
      ...(transaction ? { transaction } : {})
    });
    if (!parent) break;
    current = parent;
  }
  return current;
};

const getRelatedRouteCardIds = async (mainRouteCardId, transaction = null) => {
  const visited = new Set([parseInt(mainRouteCardId, 10)]);
  let frontier = [parseInt(mainRouteCardId, 10)];

  while (frontier.length > 0) {
    const children = await db.RouteCard.findAll({
      where: { previous_route_card_id: { [Op.in]: frontier } },
      attributes: ['id'],
      ...(transaction ? { transaction } : {})
    });
    const next = [];
    children.forEach((c) => {
      const id = parseInt(c.id, 10);
      if (!visited.has(id)) {
        visited.add(id);
        next.push(id);
      }
    });
    frontier = next;
  }
  return Array.from(visited);
};

const getAcceptedQtyByRouteCards = async (routeCardIds, transaction = null) => {
  if (!Array.isArray(routeCardIds) || routeCardIds.length === 0) return { totalAcceptedQty: 0, totalQty: 0, perRouteCard: [] };
  const ops = await db.RouteCardOperation.findAll({
    where: { route_card_id: { [Op.in]: routeCardIds } },
    attributes: ['route_card_id', 'operation_sequence_no', 'completed_qty'],
    order: [['route_card_id', 'ASC'], ['operation_sequence_no', 'DESC']],
    ...(transaction ? { transaction } : {})
  });

  const perRouteCardMap = new Map();
  ops.forEach((op) => {
    if (!perRouteCardMap.has(op.route_card_id)) {
      perRouteCardMap.set(op.route_card_id, toNum(op.completed_qty));
    }
  });

  const routeCards = await db.RouteCard.findAll({
    where: { id: { [Op.in]: routeCardIds } },
    attributes: ['id', 'route_card_number', 'production_qty'],
    ...(transaction ? { transaction } : {})
  });

  const perRouteCard = routeCards.map((rc) => ({
    id: rc.id,
    route_card_number: rc.route_card_number,
    accepted_qty: perRouteCardMap.get(rc.id) || 0,
    total_qty: toNum(rc.production_qty)
  }));
  // Rework route cards are for reprocessing same lot; avoid double counting.
  const totalAcceptedQty = perRouteCard.reduce((max, x) => Math.max(max, toNum(x.accepted_qty)), 0);
  const totalQty = perRouteCard.reduce((max, x) => Math.max(max, toNum(x.total_qty)), 0);
  return { totalAcceptedQty, totalQty, perRouteCard };
};

const hydratePackerNames = async (items) => {
  const rows = Array.isArray(items) ? items : [];
  const allPackerIds = Array.from(new Set(rows.flatMap((r) => parseIdArray(r?.packer_ids))));
  if (allPackerIds.length === 0) {
    return rows.map((r) => ({ ...r, packer_ids: [], packer_names: [], packer_display: '' }));
  }

  const operators = await db.ProductionOperator.findAll({
    where: { id: { [Op.in]: allPackerIds } },
    attributes: ['id', 'operator_name']
  });
  const map = new Map(operators.map((o) => [o.id, o.operator_name || `Packer ${o.id}`]));

  return rows.map((r) => {
    const ids = parseIdArray(r?.packer_ids);
    const names = ids.map((id) => map.get(id)).filter(Boolean);
    return {
      ...r,
      packer_ids: ids,
      packer_names: names,
      packer_display: names.join(', ')
    };
  });
};

const validateRouteCardForPacking = (routeCard) => {
  const rcStatus = String(routeCard?.status || '').toUpperCase();
  const finalQcStatus = String(routeCard?.finalInspection?.status || '').toUpperCase();
  if (!(rcStatus === 'CLOSED' || rcStatus === 'COMPLETED')) {
    throw new Error('Packing is allowed only when Route Card is completed/closed');
  }
  if (finalQcStatus !== 'APPROVED') {
    throw new Error('Packing is allowed only when Final QC status is APPROVED');
  }
};

const dropUniqueRouteCardIndexIfExists = async (transaction = null) => {
  const uniqueIndexes = await db.sequelize.query(
    "SHOW INDEX FROM production_packing WHERE Column_name = 'route_card_id' AND Non_unique = 0",
    {
      type: QueryTypes.SELECT,
      ...(transaction ? { transaction } : {})
    }
  );
  for (const idx of uniqueIndexes || []) {
    const keyName = idx?.Key_name;
    if (!keyName || keyName === 'PRIMARY') continue;
    await db.sequelize.query(`ALTER TABLE production_packing DROP INDEX \`${keyName}\``, {
      ...(transaction ? { transaction } : {})
    });
  }
};

const packingIncludes = [
  { model: db.RouteCard, as: 'routeCard', attributes: ['id', 'route_card_number', 'production_qty'] },
  { model: db.ProductionPacking, as: 'referencePacking', attributes: ['id', 'packing_number'], required: false },
  { model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number'], required: false },
  { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'production_order_number'], required: false },
  { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'], required: false },
  { model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description'], required: false }
];

const attachFGStats = async (items) => {
  const rows = Array.isArray(items) ? items : [];
  if (!db.ProductionFGInward || rows.length === 0) return rows;
  const packingIds = rows.map((r) => parseInt(r?.id, 10)).filter((v) => Number.isFinite(v) && v > 0);
  if (!packingIds.length) return rows;

  const inwardRows = await db.ProductionFGInward.findAll({
    where: { packing_id: { [Op.in]: packingIds } },
    attributes: ['packing_id', 'inward_entry_qty']
  });

  const inwardByPacking = new Map();
  inwardRows.forEach((row) => {
    const packingId = parseInt(row?.packing_id, 10);
    if (!Number.isFinite(packingId)) return;
    const prev = inwardByPacking.get(packingId) || 0;
    inwardByPacking.set(packingId, prev + toNum(row?.inward_entry_qty));
  });

  return rows.map((row) => {
    const moved = toNum(inwardByPacking.get(parseInt(row?.id, 10)));
    const packed = toNum(row?.packed_qty);
    return {
      ...row,
      moved_to_fg_qty: moved,
      available_for_fg_qty: Math.max(packed - moved, 0)
    };
  });
};

exports.getPrefillByRouteCard = asyncHandler(async (req, res) => {
  const { routeCardId } = req.params;
  const reqCompanyId = req.user?.companyId || null;

  const root = await getMainRouteCard(routeCardId);
  if (!root) return sendError(res, 'Route Card not found', 404);
  if (reqCompanyId && String(root.company_id) !== String(reqCompanyId)) {
    return sendError(res, 'Access denied for this Route Card', 403);
  }

  const routeCard = await db.RouteCard.findByPk(root.id, {
    include: [
      { model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number', 'customer_id'] },
      {
        model: db.ProductionOrder,
        as: 'productionOrder',
        attributes: ['id', 'production_order_number', 'customer_po_id'],
        include: [
          {
            model: db.CustomerPO,
            as: 'customerPO',
            attributes: ['id', 'customer_id'],
            include: [{ model: db.BusinessPartner, as: 'customer', attributes: ['id', 'bp_number', 'name'] }]
          }
        ]
      },
      { model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description'] },
      { model: db.ProductionFinalInspection, as: 'finalInspection', attributes: ['id', 'status'], required: false }
    ]
  });
  if (!routeCard) return sendError(res, 'Route Card not found', 404);

  validateRouteCardForPacking(routeCard);
  const relatedRouteCardIds = await getRelatedRouteCardIds(routeCard.id);
  const accepted = await getAcceptedQtyByRouteCards(relatedRouteCardIds);

  const customerFromPO = routeCard?.productionOrder?.customerPO?.customer;
  const customerFromWO = routeCard?.workOrder?.customer_id
    ? await db.BusinessPartner.findByPk(routeCard.workOrder.customer_id, { attributes: ['id', 'bp_number', 'name'] })
    : null;
  const customer = customerFromPO || customerFromWO || null;

  sendResponse(res, 200, true, 'Packing prefill retrieved successfully', {
    route_card_id: routeCard.id,
    route_card_number: routeCard.route_card_number,
    work_order_id: routeCard.workOrder?.id || null,
    work_order_number: routeCard.workOrder?.work_order_number || '',
    production_order_id: routeCard.productionOrder?.id || null,
    production_order_number: routeCard.productionOrder?.production_order_number || '',
    customer_id: customer?.id || null,
    customer_name: customer ? `${customer.bp_number || ''}${customer.name ? ` - ${customer.name}` : ''}`.trim() : '',
    finished_material_id: routeCard.finishedMaterial?.id || null,
    item_code: routeCard.finishedMaterial?.material_number || '',
    item_description: routeCard.finishedMaterial?.description || '',
    total_qty: accepted.totalQty || toNum(routeCard.production_qty),
    accepted_qty: accepted.totalAcceptedQty,
    related_route_cards: accepted.perRouteCard
  });
});

exports.getPackersByRouteCard = asyncHandler(async (req, res) => {
  const { routeCardId } = req.query;
  const reqCompanyId = req.user?.companyId || null;
  if (!routeCardId) return sendError(res, 'routeCardId is required', 400);

  const root = await getMainRouteCard(routeCardId);
  if (!root) return sendError(res, 'Route Card not found', 404);
  if (reqCompanyId && String(root.company_id) !== String(reqCompanyId)) {
    return sendError(res, 'Access denied for this Route Card', 403);
  }

  const relatedIds = await getRelatedRouteCardIds(root.id);
  const ops = await db.RouteCardOperation.findAll({
    where: { route_card_id: { [Op.in]: relatedIds } },
    attributes: ['operation_code']
  });
  const opCodeSet = new Set(ops.map((o) => String(o.operation_code || '').trim()).filter(Boolean));

  const operators = await db.ProductionOperator.findAll({
    where: {
      company_id: root.company_id,
      status: 'ACTIVE'
    },
    include: [{ model: db.OperationName, as: 'operationName', attributes: ['id', 'operation_code'], required: false }],
    attributes: ['id', 'operator_name', 'shift', 'work_center_id'],
    order: [['operator_name', 'ASC']]
  });

  const rows = operators
    .filter((o) => {
      if (opCodeSet.size === 0) return true;
      const opCode = String(o?.operationName?.operation_code || '').trim();
      return opCodeSet.has(opCode);
    })
    .map((o) => ({
      id: o.id,
      operator_name: o.operator_name,
      shift: o.shift,
      work_center_id: o.work_center_id
    }));

  sendResponse(res, 200, true, 'Packers retrieved successfully', rows);
});

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] = [{ packing_number: { [Op.like]: `%${search}%` } }];
  }

  const records = await db.ProductionPacking.findAndCountAll({
    where,
    include: packingIncludes,
    limit: limitNum,
    offset,
    order: [['id', 'DESC']]
  });

  const response = getPagingData(records, page, limitNum);
  let items = (response.items || []).map(toJson);
  for (let i = 0; i < items.length; i += 1) {
    const row = items[i];
    if (row?.reference_packing_id) continue;
    const relatedIds = parseIdArray(row?.related_route_card_ids);
    if (!relatedIds.length) continue;
    const acceptedStats = await getAcceptedQtyByRouteCards(relatedIds);
    const acceptedQty = toNum(acceptedStats.totalAcceptedQty);
    const packedQty = toNum(row?.packed_qty);
    items[i] = {
      ...row,
      accepted_qty: acceptedQty,
      balance_qty: Math.max(acceptedQty - packedQty, 0)
    };
  }
  items = await attachFGStats(items);
  response.items = await hydratePackerNames(items);
  sendResponse(res, 200, true, 'Production Packing records retrieved successfully', response);
});

exports.getById = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const record = await db.ProductionPacking.findByPk(id, {
    include: packingIncludes
  });
  if (!record) return sendError(res, 'Production Packing record not found', 404);
  let item = toJson(record);
  if (!item?.reference_packing_id) {
    const relatedIds = parseIdArray(item?.related_route_card_ids);
    if (relatedIds.length > 0) {
      const acceptedStats = await getAcceptedQtyByRouteCards(relatedIds);
      const acceptedQty = toNum(acceptedStats.totalAcceptedQty);
      const packedQty = toNum(item?.packed_qty);
      item = {
        ...item,
        accepted_qty: acceptedQty,
        balance_qty: Math.max(acceptedQty - packedQty, 0)
      };
    }
  }
  item = (await attachFGStats([item]))[0];
  item = (await hydratePackerNames([item]))[0];
  sendResponse(res, 200, true, 'Production Packing record retrieved successfully', item);
});

exports.create = asyncHandler(async (req, res) => {
  const data = req.body || {};
  const userId = req.user?.userId || null;
  const reqCompanyId = req.user?.companyId || null;
  if (!data.route_card_id) return sendError(res, 'route_card_id is required', 400);
  const sourcePackingId = data.source_packing_id ? parseInt(data.source_packing_id, 10) : null;

  const transaction = await db.sequelize.transaction();
  try {
    const root = await getMainRouteCard(data.route_card_id, transaction);
    if (!root) throw new Error('Route Card not found');
    if (reqCompanyId && String(root.company_id) !== String(reqCompanyId)) {
      throw new Error('Access denied for this Route Card');
    }

    const routeCard = await db.RouteCard.findByPk(root.id, {
      include: [
        { model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number', 'customer_id'] },
        {
          model: db.ProductionOrder,
          as: 'productionOrder',
          attributes: ['id', 'production_order_number', 'customer_po_id'],
          include: [{ model: db.CustomerPO, as: 'customerPO', attributes: ['id', 'customer_id'] }]
        },
        { model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description'] },
        { model: db.ProductionFinalInspection, as: 'finalInspection', attributes: ['id', 'status'], required: false }
      ],
      transaction
    });
    if (!routeCard) throw new Error('Route Card not found');
    validateRouteCardForPacking(routeCard);

    const relatedRouteCardIds = await getRelatedRouteCardIds(routeCard.id, transaction);
    let acceptedQty = 0;
    let packedQty = 0;
    let balanceQty = 0;
    const customerId = routeCard?.productionOrder?.customerPO?.customer_id || routeCard?.workOrder?.customer_id || null;
    let referencePackingId = null;

    if (Number.isFinite(sourcePackingId) && sourcePackingId > 0) {
      const source = await db.ProductionPacking.findByPk(sourcePackingId, { transaction });
      if (!source) throw new Error('Source packing not found');
      if (String(source.route_card_id) !== String(routeCard.id)) {
        throw new Error('Source packing does not belong to selected Route Card');
      }
      if (String(source.status) !== 'PARTIAL_COMPLETED') {
        throw new Error('Reference packing can be generated only from PARTIAL_COMPLETED packing');
      }
      const sourceBalance = toNum(source.balance_qty);
      if (sourceBalance <= 0) throw new Error('No balance qty available for reference packing');
      acceptedQty = sourceBalance;
      packedQty = 0;
      balanceQty = sourceBalance;
      referencePackingId = source.id;
    } else {
      const existingActive = await db.ProductionPacking.findOne({
        where: {
          route_card_id: routeCard.id,
          status: { [Op.in]: ['PENDING', 'IN_PROGRESS'] }
        },
        order: [['id', 'DESC']],
        transaction
      });
      if (existingActive) {
        await transaction.rollback();
        const existingRecord = await db.ProductionPacking.findByPk(existingActive.id, { include: packingIncludes });
        return sendResponse(
          res,
          200,
          true,
          'Packing already exists for this Route Card',
          (await hydratePackerNames([toJson(existingRecord)]))[0]
        );
      }

      const accepted = await getAcceptedQtyByRouteCards(relatedRouteCardIds, transaction);
      acceptedQty = toNum(accepted.totalAcceptedQty);
      packedQty = 0;
      balanceQty = Math.max(acceptedQty - packedQty, 0);
    }

    // Backward-compatibility for environments where old unique(route_card_id)
    // index still exists and migration has not yet been applied.
    if (Number.isFinite(sourcePackingId) && sourcePackingId > 0) {
      await dropUniqueRouteCardIndexIfExists(transaction);
    }

    const created = await db.ProductionPacking.create({
      company_id: routeCard.company_id,
      plant_id: routeCard.plant_id,
      fiscal_year_id: routeCard.fiscal_year_id,
      packing_number: await generatePackingNumber(routeCard.company_id, routeCard.fiscal_year_id, transaction),
      route_card_id: routeCard.id,
      reference_packing_id: referencePackingId,
      work_order_id: routeCard.work_order_id || null,
      production_order_id: routeCard.production_order_id || null,
      customer_id: customerId,
      finished_material_id: routeCard.finished_material_id || null,
      accepted_qty: acceptedQty,
      packed_qty: packedQty,
      balance_qty: balanceQty,
      related_route_card_ids: JSON.stringify(relatedRouteCardIds),
      status: 'PENDING',
      created_by: userId,
      updated_by: userId
    }, { transaction });

    await transaction.commit();
    const record = await db.ProductionPacking.findByPk(created.id, { include: packingIncludes });
    sendResponse(res, 201, true, 'Production Packing created successfully', (await hydratePackerNames([toJson(record)]))[0]);
  } catch (error) {
    await transaction.rollback();
    const detail = error?.errors?.[0]?.message || error?.parent?.sqlMessage || error?.message;
    sendError(res, detail || 'Failed to create Production Packing', 400);
  }
});

const applyPackingProgress = async ({ record, payload, userId, forceComplete = false }) => {
  const updates = {};
  const acceptedQty = toNum(record.accepted_qty);
  const packedQty = payload.packed_qty !== undefined ? toNum(payload.packed_qty) : toNum(record.packed_qty);
  if (packedQty > acceptedQty) throw new Error(`Packed Qty cannot be greater than Accepted Qty (${acceptedQty})`);

  const balanceQty = Math.max(acceptedQty - packedQty, 0);
  updates.packed_qty = packedQty;
  updates.balance_qty = balanceQty;

  if (payload.actual_start !== undefined) updates.actual_start = payload.actual_start || null;
  if (payload.actual_end !== undefined) updates.actual_end = payload.actual_end || null;
  if (payload.remarks !== undefined) updates.remarks = payload.remarks || null;

  if (payload.packer_ids !== undefined) {
    const ids = parseIdArray(payload.packer_ids);
    updates.packer_ids = ids.length > 0 ? JSON.stringify(ids) : null;
  }

  if (forceComplete) {
    if (!updates.actual_end && !record.actual_end) updates.actual_end = new Date();
    updates.status = balanceQty <= 0 ? 'COMPLETED' : 'PARTIAL_COMPLETED';
  } else if (record.status === 'PENDING') {
    updates.status = 'IN_PROGRESS';
  } else if (record.status === 'IN_PROGRESS') {
    updates.status = (packedQty >= acceptedQty && acceptedQty > 0) ? 'COMPLETED' : 'IN_PROGRESS';
  }

  updates.updated_by = userId;
  await record.update(updates);
  return record;
};

exports.start = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const payload = req.body || {};
  const record = await db.ProductionPacking.findByPk(id);
  if (!record) return sendError(res, 'Production Packing record not found', 404);
  if (record.status === 'COMPLETED') return sendError(res, 'Completed packing cannot be started', 400);
  if (record.status === 'PARTIAL_COMPLETED') return sendError(res, 'This packing is partial completed. Create reference packing for balance qty', 400);
  if (!payload.actual_start) return sendError(res, 'actual_start is required', 400);

  await applyPackingProgress({ record, payload, userId, forceComplete: false });
  sendResponse(res, 200, true, 'Production Packing started successfully', record);
});

exports.updateProgress = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const payload = req.body || {};
  const record = await db.ProductionPacking.findByPk(id);
  if (!record) return sendError(res, 'Production Packing record not found', 404);
  if (record.status === 'COMPLETED') return sendError(res, 'Completed packing cannot be updated', 400);
  if (record.status === 'PARTIAL_COMPLETED') return sendError(res, 'This packing is partial completed. Create reference packing for balance qty', 400);

  await applyPackingProgress({ record, payload, userId, forceComplete: false });
  sendResponse(res, 200, true, 'Production Packing updated successfully', record);
});

exports.complete = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const payload = req.body || {};
  const record = await db.ProductionPacking.findByPk(id);
  if (!record) return sendError(res, 'Production Packing record not found', 404);
  if (record.status === 'COMPLETED') return sendError(res, 'Packing already completed', 400);
  if (record.status === 'PARTIAL_COMPLETED') return sendError(res, 'This packing is already partial completed. Create reference packing for balance qty', 400);

  await applyPackingProgress({ record, payload, userId, forceComplete: true });
  const refreshed = await db.ProductionPacking.findByPk(id);
  sendResponse(
    res,
    200,
    true,
    refreshed.status === 'COMPLETED'
      ? 'Production Packing completed successfully'
      : 'Production Packing partial completed: generate reference packing for balance qty',
    refreshed
  );
});

