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 getLatestCompletedQtyFromOperations = (operations = []) => {
  let acceptedQty = 0;
  let latestSeqNo = -1;
  let latestOpId = -1;
  (Array.isArray(operations) ? operations : []).forEach((op) => {
    const completedQty = parseFloat(op.completed_qty || 0) || 0;
    if (completedQty <= 0) return;
    const seqNo = parseInt(op.operation_sequence_no, 10) || 0;
    const opId = parseInt(op.id, 10) || 0;
    if (seqNo > latestSeqNo || (seqNo === latestSeqNo && opId > latestOpId)) {
      latestSeqNo = seqNo;
      latestOpId = opId;
      acceptedQty = completedQty;
    }
  });
  return acceptedQty;
};

const getLatestCompletedQtyByRouteCardIds = async (routeCardIds = []) => {
  const ids = Array.from(new Set((routeCardIds || []).map((x) => parseInt(x, 10)).filter((x) => Number.isFinite(x) && x > 0)));
  const result = new Map();
  if (ids.length === 0) return result;

  const rows = await db.RouteCardOperation.findAll({
    attributes: ['id', 'route_card_id', 'operation_sequence_no', 'completed_qty'],
    where: { route_card_id: { [Op.in]: ids } },
    order: [['route_card_id', 'ASC'], ['operation_sequence_no', 'ASC'], ['id', 'ASC']]
  });

  const latestByRouteCardId = new Map();
  rows.forEach((row) => {
    const rcId = parseInt(row.route_card_id, 10);
    const completedQty = parseFloat(row.completed_qty || 0) || 0;
    if (!(completedQty > 0)) return;
    const seqNo = parseInt(row.operation_sequence_no, 10) || 0;
    const opId = parseInt(row.id, 10) || 0;
    const prev = latestByRouteCardId.get(rcId);
    if (!prev || seqNo > prev.seqNo || (seqNo === prev.seqNo && opId > prev.opId)) {
      latestByRouteCardId.set(rcId, { seqNo, opId, qty: completedQty });
    }
  });

  latestByRouteCardId.forEach((val, rcId) => {
    result.set(rcId, val.qty);
  });
  return result;
};

const generateFinalInspectionNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'PRODUCTION_FINAL_INSPECTION', fiscalYearId, transaction);
  } catch (err) {
    const year = new Date().getFullYear();
    const prefix = `FQI${year}`;
    const last = await db.ProductionFinalInspection.findOne({
      where: {
        company_id: companyId,
        final_inspection_number: { [Op.like]: `${prefix}%` }
      },
      order: [['final_inspection_number', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    let sequence = 1;
    if (last?.final_inspection_number) {
      const lastSequence = parseInt(String(last.final_inspection_number).slice(-4), 10) || 0;
      sequence = lastSequence + 1;
    }
    return `${prefix}${String(sequence).padStart(4, '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
  }
  if (str.includes(',')) return parseIdArray(str.split(','));
  const n = parseInt(str, 10);
  return Number.isFinite(n) && n > 0 ? [n] : [];
};

const checkRouteCardReadyForFinalQC = async (routeCardId, transaction) => {
  const routeCard = await db.RouteCard.findByPk(routeCardId, {
    include: [
      {
        model: db.RouteCardOperation,
        as: 'operations',
        attributes: ['id', 'operation_sequence_no', 'status', 'completed_qty', 'rework_qty'],
        include: [{ model: db.ProductionQC, as: 'productionQC', attributes: ['id', 'status', 'rework_qty'], required: false }]
      },
      { model: db.RouteCard, as: 'reworkRouteCards', attributes: ['id', 'status'], required: false }
    ],
    transaction
  });
  if (!routeCard) throw new Error('Route Card not found');
  // Allow Final QC for main Route Card and Final QC rework Route Cards.
  // Block only operation-wise QC rework cards (source_production_qc_id is present).
  if (routeCard.previous_route_card_id && routeCard.source_production_qc_id) {
    throw new Error('Final QC is not allowed for operation-wise QC rework Route Card');
  }

  const ops = Array.isArray(routeCard.operations) ? routeCard.operations : [];
  if (ops.length === 0) return { routeCard, ready: false, reason: 'No operations available for final QC' };

  const pendingOrProgress = ops.filter((o) => ['PENDING', 'IN_PROGRESS'].includes(String(o.status || ''))).length;
  const qcPending = ops.filter((o) => String(o.status || '') === 'COMPLETED' && String(o.productionQC?.status || '') !== 'APPROVED').length;
  const childReworks = Array.isArray(routeCard.reworkRouteCards) ? routeCard.reworkRouteCards : [];
  const hasOpenChildRework = childReworks.some((x) => String(x.status || '') === 'OPEN');

  // Final QC is allowed when operations are done, operation-wise QC is approved, and no OPEN rework route card remains.
  // Closed rework history should not block Final QC movement.
  const ready = pendingOrProgress === 0 && qcPending === 0 && !hasOpenChildRework;
  return {
    routeCard,
    ready,
    reason: ready ? null : 'Route Card is not ready for Final QC (pending operation/QC or rework exists)'
  };
};

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] = [
      { final_inspection_number: { [Op.like]: `%${search}%` } },
      { '$workOrder.work_order_number$': { [Op.like]: `%${search}%` } },
      { '$routeCard.route_card_number$': { [Op.like]: `%${search}%` } },
      { '$productionOrder.production_order_number$': { [Op.like]: `%${search}%` } }
    ];
  }

  const records = await db.ProductionFinalInspection.findAndCountAll({
    where,
    subQuery: false,
    include: [
      { model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number'], required: false },
      {
        model: db.RouteCard,
        as: 'routeCard',
        attributes: ['id', 'route_card_number', 'status', 'previous_route_card_id', 'source_production_qc_id'],
        required: true,
        include: [
          { model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description'], required: false },
          {
            model: db.RouteCard,
            as: 'previousRouteCard',
            attributes: ['id', 'route_card_number'],
            required: false,
            include: [
              { model: db.ProductionFinalInspection, as: 'finalInspection', attributes: ['id', 'final_inspection_number', 'status'], required: false }
            ]
          }
        ]
      },
      { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'production_order_number'], required: false },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'name', 'legal_name'], required: false },
      { model: db.User, as: 'qcByUser', attributes: ['id', 'first_name', 'last_name', 'username'], required: false }
    ],
    limit: limitNum,
    offset,
    order: [['id', 'DESC']]
  });

  const response = getPagingData(records, page, limitNum);
  const routeCardIds = (response.items || []).map((x) => parseInt(x?.route_card_id, 10)).filter((x) => Number.isFinite(x) && x > 0);
  const acceptedQtyByRouteCardId = await getLatestCompletedQtyByRouteCardIds(routeCardIds);
  response.items = (response.items || []).map((row) => {
    const json = typeof row?.toJSON === 'function' ? row.toJSON() : row;
    const rcId = parseInt(json.route_card_id, 10);
    const calcAccepted = acceptedQtyByRouteCardId.get(rcId);
    const refFinalInspectionNo = json?.routeCard?.previousRouteCard?.finalInspection?.final_inspection_number || null;
    return {
      ...json,
      accepted_qty: calcAccepted != null ? parseFloat((parseFloat(calcAccepted || 0)).toFixed(3)) : json.accepted_qty,
      reference_final_inspection_number: refFinalInspectionNo
    };
  });
  sendResponse(res, 200, true, 'Final Inspection records retrieved successfully', response);
});

exports.getById = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const record = await db.ProductionFinalInspection.findByPk(id, {
    include: [
      { model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number'], required: false },
      {
        model: db.RouteCard, as: 'routeCard', attributes: ['id', 'route_card_number', 'status', 'previous_route_card_id', 'source_production_qc_id'], required: true,
        include: [
          {
            model: db.RouteCardOperation,
            as: 'operations',
            attributes: ['id', 'operation_sequence_no', 'operation_code', 'operation_name', 'completed_qty', 'checked_qty', 'rejected_qty', 'rework_qty', 'status'],
            include: [
              { model: db.Department, as: 'department', attributes: ['id', 'department_code', 'department_name'], required: false },
              { model: db.WorkCenter, as: 'workCenter', attributes: ['id', 'work_center_code', 'description'], required: false },
              { model: db.ProductionQC, as: 'productionQC', attributes: ['id', 'status', 'accepted_qty', 'checked_qty', 'rework_qty', 'rework_remarks', 'qc_at'], required: false }
            ]
          },
          {
            model: db.RouteCard,
            as: 'previousRouteCard',
            attributes: ['id', 'route_card_number'],
            required: false,
            include: [
              { model: db.ProductionFinalInspection, as: 'finalInspection', attributes: ['id', 'final_inspection_number', 'status'], required: false }
            ]
          }
        ]
      },
      { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'production_order_number'], required: false },
      { model: db.BusinessPartner, as: 'customer', attributes: ['id', 'name', 'legal_name'], required: false },
      { model: db.User, as: 'qcByUser', attributes: ['id', 'first_name', 'last_name', 'username'], required: false }
    ],
    order: [[{ model: db.RouteCard, as: 'routeCard' }, { model: db.RouteCardOperation, as: 'operations' }, 'operation_sequence_no', 'ASC']]
  });
  if (!record) return sendError(res, 'Final Inspection record not found', 404);
  const json = typeof record?.toJSON === 'function' ? record.toJSON() : record;
  json.reference_final_inspection_number = json?.routeCard?.previousRouteCard?.finalInspection?.final_inspection_number || null;
  sendResponse(res, 200, true, 'Final Inspection record retrieved successfully', json);
});

exports.createFromRouteCard = asyncHandler(async (req, res) => {
  const { routeCardId } = req.params;
  const userId = req.user?.userId || null;
  const transaction = await db.sequelize.transaction();
  try {
    const existing = await db.ProductionFinalInspection.findOne({
      where: { route_card_id: routeCardId },
      transaction
    });
    if (existing) {
      if (!existing.final_inspection_number) {
        const number = await generateFinalInspectionNumber(existing.company_id, existing.fiscal_year_id, transaction);
        await existing.update({
          final_inspection_number: number,
          updated_by: userId
        }, { transaction });
        await transaction.commit();
        const latest = await db.ProductionFinalInspection.findByPk(existing.id);
        return sendResponse(res, 200, true, 'Final Inspection already exists for this Route Card', latest);
      }
      await transaction.rollback();
      return sendResponse(res, 200, true, 'Final Inspection already exists for this Route Card', existing);
    }

    const { routeCard, ready, reason } = await checkRouteCardReadyForFinalQC(routeCardId, transaction);
    if (!ready) {
      await transaction.rollback();
      return sendError(res, reason, 400);
    }

    const operations = Array.isArray(routeCard.operations) ? routeCard.operations : [];
    const acceptedQty = getLatestCompletedQtyFromOperations(operations);

    const workOrder = await db.WorkOrder.findByPk(routeCard.work_order_id, {
      attributes: ['id', 'customer_id'],
      transaction
    });

    const created = await db.ProductionFinalInspection.create({
      company_id: routeCard.company_id,
      plant_id: routeCard.plant_id,
      fiscal_year_id: routeCard.fiscal_year_id,
      route_card_id: routeCard.id,
      final_inspection_number: await generateFinalInspectionNumber(routeCard.company_id, routeCard.fiscal_year_id, transaction),
      work_order_id: routeCard.work_order_id || null,
      production_order_id: routeCard.production_order_id || null,
      customer_id: workOrder?.customer_id || null,
      order_qty: parseFloat(routeCard.production_qty || 0),
      accepted_qty: acceptedQty,
      checked_qty: 0,
      rework_qty: 0,
      remarks: null,
      status: 'PENDING',
      created_by: userId,
      updated_by: userId
    }, { transaction });

    await transaction.commit();
    sendResponse(res, 201, true, 'Moved to Final Inspection successfully', created);
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message || 'Failed to move Route Card to Final Inspection', 500);
  }
});

exports.start = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const record = await db.ProductionFinalInspection.findByPk(id);
  if (!record) return sendError(res, 'Final Inspection record not found', 404);
  if (record.status !== 'PENDING') return sendError(res, 'Only PENDING records can be started', 400);
  await record.update({ status: 'IN_PROGRESS', started_at: new Date(), updated_by: userId });
  sendResponse(res, 200, true, 'Final Inspection started successfully', record);
});

exports.complete = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const record = await db.ProductionFinalInspection.findByPk(id);
  if (!record) return sendError(res, 'Final Inspection record not found', 404);
  if (record.status !== 'IN_PROGRESS') return sendError(res, 'Only IN_PROGRESS records can be completed', 400);

  const routeCardOps = await db.RouteCardOperation.findAll({
    where: { route_card_id: record.route_card_id },
    attributes: ['id', 'operation_sequence_no', 'completed_qty']
  });
  const acceptedQty = getLatestCompletedQtyFromOperations(routeCardOps);
  const hasCheckedQty = req.body.checked_qty !== undefined && req.body.checked_qty !== null && String(req.body.checked_qty).trim() !== '';
  if (!hasCheckedQty) return sendError(res, 'Checked Qty is required', 400);
  const checkedQty = parseFloat(req.body.checked_qty) || 0;
  const reworkQty = req.body.rework_qty !== undefined ? parseFloat(req.body.rework_qty) || 0 : 0;
  const reworkFromOperationId = req.body.rework_from_operation_id != null ? parseInt(req.body.rework_from_operation_id, 10) : null;
  const remarks = String(req.body.remarks || '').trim();
  const qcByIds = parseIdArray(req.body.qc_by_ids !== undefined ? req.body.qc_by_ids : req.body.qc_by);
  const qcBy = qcByIds[0] || null;
  const startedAt = req.body.started_at ? new Date(req.body.started_at) : (record.started_at ? new Date(record.started_at) : new Date());
  const completedAt = req.body.completed_at
    ? new Date(req.body.completed_at)
    : (req.body.actual_end ? new Date(req.body.actual_end) : new Date());

  if (!qcBy) return sendError(res, 'QC Name is required', 400);
  if (acceptedQty < 0 || checkedQty < 0 || reworkQty < 0) return sendError(res, 'Accepted Qty, Checked Qty and Rework Qty cannot be negative', 400);
  if (checkedQty > acceptedQty) return sendError(res, 'Checked Qty cannot be greater than Accepted Qty', 400);
  if (Number.isNaN(startedAt.getTime())) return sendError(res, 'Invalid Actual Start date/time', 400);
  if (Number.isNaN(completedAt.getTime())) return sendError(res, 'Invalid Date & Time', 400);
  if (completedAt < startedAt) return sendError(res, 'Actual End date/time cannot be earlier than Actual Start date/time', 400);
  if (reworkQty > 0) {
    if (!(Number.isFinite(reworkFromOperationId) && reworkFromOperationId > 0)) {
      return sendError(res, 'Select Rework From Operation when Rework Qty is greater than 0', 400);
    }
    const selectedOperation = await db.RouteCardOperation.findOne({
      where: { id: reworkFromOperationId, route_card_id: record.route_card_id },
      attributes: ['id', 'status']
    });
    if (!selectedOperation) {
      return sendError(res, 'Selected Rework From Operation is invalid', 400);
    }
    if (String(selectedOperation.status || '') !== 'COMPLETED') {
      return sendError(res, 'Rework From Operation must be a COMPLETED operation', 400);
    }
  }

  await record.update({
    accepted_qty: acceptedQty,
    checked_qty: checkedQty,
    rework_qty: reworkQty,
    rework_from_operation_id: reworkQty > 0 ? reworkFromOperationId : null,
    remarks: remarks || null,
    qc_by: qcBy,
    qc_by_ids: JSON.stringify(qcByIds),
    started_at: startedAt,
    completed_at: completedAt,
    status: 'COMPLETED',
    updated_by: userId
  });

  const latest = await db.ProductionFinalInspection.findByPk(id);
  sendResponse(res, 200, true, 'Final Inspection completed successfully', {
    ...(latest?.toJSON ? latest.toJSON() : record.toJSON()),
    rework_route_card: null,
    rework_in_existing_route_card: reworkQty > 0
  });
});

exports.approve = asyncHandler(async (req, res) => {
  const { id } = req.params;
  const userId = req.user?.userId || null;
  const transaction = await db.sequelize.transaction();
  try {
    const record = await db.ProductionFinalInspection.findByPk(id, { transaction });
    if (!record) {
      await transaction.rollback();
      return sendError(res, 'Final Inspection record not found', 404);
    }
    if (!['COMPLETED', 'APPROVED'].includes(record.status)) {
      await transaction.rollback();
      return sendError(res, 'Only COMPLETED or APPROVED records can be processed for approval', 400);
    }

    if (record.status !== 'APPROVED') {
      await record.update({ status: 'APPROVED', updated_by: userId }, { transaction });
    }

    const reworkQty = parseFloat(record.rework_qty || 0);
    if (reworkQty > 0) {
      if (!(parseInt(record.rework_from_operation_id, 10) > 0)) {
        await transaction.rollback();
        return sendError(res, 'Rework From Operation is required before approval', 400);
      }
    }

    await transaction.commit();
    const latest = await db.ProductionFinalInspection.findByPk(id);
    sendResponse(res, 200, true, 'Final Inspection approved successfully', {
      ...(latest?.toJSON ? latest.toJSON() : record.toJSON()),
      rework_route_card: null,
      rework_in_existing_route_card: reworkQty > 0
    });
  } catch (error) {
    await transaction.rollback();
    sendError(res, error.message || 'Failed to approve Final Inspection', 500);
  }
});
