const db = require('../models');
const { Op } = require('sequelize');
const { generateNextNumber } = require('./numberFormatService');
const productionQCService = require('./productionQCService');

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 to CSV/single scalar parsing
  }
  if (str.includes(',')) return parseIdArray(str.split(','));
  const n = parseInt(str, 10);
  return Number.isFinite(n) && n > 0 ? [n] : [];
};

const generateRouteCardNumber = async (companyId, fiscalYearId = null, transaction = null) => {
  try {
    return await generateNextNumber(companyId, 'ROUTE_CARD', fiscalYearId, transaction);
  } catch (err) {
    console.warn('Route card number from format master failed, using fallback:', err.message);
    const today = new Date();
    const year = today.getFullYear();
    const prefix = `RC${year}`;
    const last = await db.RouteCard.findOne({
      where: { company_id: companyId, route_card_number: { [Op.like]: `${prefix}%` } },
      order: [['route_card_number', 'DESC']],
      ...(transaction ? { transaction } : {})
    });
    let sequence = 1;
    if (last) {
      const lastSequence = parseInt(String(last.route_card_number).slice(-4)) || 0;
      sequence = lastSequence + 1;
    }
    return `${prefix}${sequence.toString().padStart(4, '0')}`;
  }
};

exports.generateFromWorkOrder = async ({ workOrderId, userId }) => {
  const transaction = await db.sequelize.transaction();
  try {
    const workOrder = await db.WorkOrder.findByPk(workOrderId, {
      include: [
        { model: db.WorkOrderOperationPlan, as: 'operations' },
        { model: db.ProductionOrder, as: 'productionOrder', include: [{ model: db.BOMHeader, as: 'bom' }] },
        { model: db.Material, as: 'finishedMaterial' }
      ],
      transaction
    });

    if (!workOrder) {
      await transaction.rollback();
      throw new Error('Work Order not found');
    }

    // Allow only when Work Order is Released (Approved/Released)
    if (workOrder.status !== 'RELEASED') {
      await transaction.rollback();
      throw new Error('Route Card can be generated only when Work Order status is RELEASED');
    }

    const existing = await db.RouteCard.findOne({ where: { work_order_id: workOrder.id }, transaction });
    if (existing) {
      await transaction.rollback();
      throw new Error(`Route Card already exists for this Work Order (${existing.route_card_number})`);
    }

    const rcNumber = await generateRouteCardNumber(workOrder.company_id, workOrder.fiscal_year_id, transaction);
    const todayStr = new Date().toISOString().slice(0, 10);
    const productionQty = parseFloat(workOrder.work_order_quantity || 0);

    const routeCard = await db.RouteCard.create(
      {
        company_id: workOrder.company_id,
        plant_id: workOrder.plant_id,
        fiscal_year_id: workOrder.fiscal_year_id,
        route_card_number: rcNumber,
        work_order_id: workOrder.id,
        production_order_id: workOrder.production_order_id || null,
        finished_material_id: workOrder.finished_material_id || null,
        bom_id: workOrder.productionOrder?.bom_id || workOrder.productionOrder?.bom?.id || null,
        production_qty: productionQty,
        start_date: todayStr,
        status: 'OPEN',
        created_by: userId,
        updated_by: userId
      },
      { transaction }
    );

    // Resolve operation descriptions from OperationName master
    const opCodes = Array.from(
      new Set((workOrder.operations || []).map((o) => String(o.operation_name || '').trim()).filter(Boolean))
    );
    const opNameRows = opCodes.length
      ? await db.OperationName.findAll({
          where: { company_id: workOrder.company_id, operation_code: { [Op.in]: opCodes } },
          attributes: ['operation_code', 'description', 'department_id'],
          transaction
        })
      : [];
    const opNameMap = new Map(opNameRows.map((r) => [String(r.operation_code), r.description || '']));
    const opDepartmentMap = new Map(opNameRows.map((r) => [String(r.operation_code), r.department_id || null]));

    const ops = (workOrder.operations || [])
      .slice()
      .sort((a, b) => (a.operation_sequence_no || 0) - (b.operation_sequence_no || 0))
      .map((op, idx) => ({
        route_card_id: routeCard.id,
        operation_sequence_no: parseInt(op.operation_sequence_no, 10),
        operation_code: op.operation_name,
        operation_name: opNameMap.get(String(op.operation_name)) || null,
        department_id:
          op.department_id != null && op.department_id !== ''
            ? parseInt(op.department_id, 10)
            : (opDepartmentMap.get(String(op.operation_name)) || null),
        work_center_id: op.work_center_id,
        planned_qty: op.planned_qty != null ? parseFloat(op.planned_qty) : productionQty,
        input_qty: idx === 0 ? productionQty : 0,
        completed_qty: 0,
        rejected_qty: 0,
        fr_rejected_qty: 0,
        mr_rejected_qty: 0,
        rejection_fr: false,
        rejection_mr: false,
        rework_qty: 0,
        status: 'PENDING',
        created_by: userId,
        updated_by: userId
      }));

    if (ops.length === 0) {
      await transaction.rollback();
      throw new Error('Work Order has no planned operations. Please add operations before generating Route Card.');
    }

    await db.RouteCardOperation.bulkCreate(ops, { transaction });

    await transaction.commit();
    return routeCard;
  } catch (e) {
    await transaction.rollback();
    throw e;
  }
};

exports.createReworkRouteCard = async ({
  sourceRouteCardId,
  sourceRouteCardOperationId = null,
  includeFollowingOperations = false,
  reworkSourceLabel = 'Rework',
  sourceProductionQCId = null,
  reworkQty,
  userId,
  transaction
}) => {
  const qty = parseFloat(reworkQty || 0);
  if (!(qty > 0)) {
    throw new Error('Rework quantity must be greater than 0 to create rework route card');
  }

  const sourceRouteCard = await db.RouteCard.findByPk(sourceRouteCardId, {
    include: [{ model: db.RouteCardOperation, as: 'operations' }],
    transaction
  });
  if (!sourceRouteCard) {
    throw new Error('Source Route Card not found for rework generation');
  }

  const rcNumber = await generateRouteCardNumber(sourceRouteCard.company_id, sourceRouteCard.fiscal_year_id, transaction);
  const todayStr = new Date().toISOString().slice(0, 10);

  const reworkRouteCard = await db.RouteCard.create(
    {
      company_id: sourceRouteCard.company_id,
      plant_id: sourceRouteCard.plant_id,
      fiscal_year_id: sourceRouteCard.fiscal_year_id,
      route_card_number: rcNumber,
      work_order_id: sourceRouteCard.work_order_id,
      previous_route_card_id: sourceRouteCard.id,
      source_production_qc_id: sourceProductionQCId,
      production_order_id: sourceRouteCard.production_order_id || null,
      finished_material_id: sourceRouteCard.finished_material_id || null,
      bom_id: sourceRouteCard.bom_id || null,
      production_qty: qty,
      start_date: todayStr,
      status: 'OPEN',
      created_by: userId,
      updated_by: userId
    },
    { transaction }
  );

  const sortedOps = (sourceRouteCard.operations || [])
    .slice()
    .sort((a, b) => (a.operation_sequence_no || 0) - (b.operation_sequence_no || 0));

  let opsToCreate = sortedOps;
  if (sourceRouteCardOperationId) {
    const selectedOp = sortedOps.find((op) => String(op.id) === String(sourceRouteCardOperationId));
    if (!selectedOp) {
      throw new Error('Selected rework operation was not found in source Route Card');
    }
    if (includeFollowingOperations) {
      const selectedSeq = parseInt(selectedOp.operation_sequence_no, 10) || 0;
      opsToCreate = sortedOps.filter((op) => (parseInt(op.operation_sequence_no, 10) || 0) >= selectedSeq);
    } else {
      opsToCreate = [selectedOp];
    }
  }

  const reworkQtyText = Number.isFinite(qty) ? qty.toFixed(3) : String(qty);
  const remarkText = `${reworkSourceLabel} from previous Route Card ${sourceRouteCard.route_card_number} | Rework Qty: ${reworkQtyText}`;
  const opsPayload = opsToCreate.map((op, idx) => ({
    route_card_id: reworkRouteCard.id,
    operation_sequence_no: op.operation_sequence_no,
    operation_code: op.operation_code,
    operation_name: op.operation_name || null,
    operator_id: null,
    department_id: op.department_id || null,
    work_center_id: op.work_center_id,
    planned_qty: qty,
    input_qty: idx === 0 ? qty : 0,
    completed_qty: 0,
    checked_qty: 0,
    rejected_qty: 0,
    fr_rejected_qty: 0,
    mr_rejected_qty: 0,
    rejection_fr: false,
    rejection_mr: false,
    rework_qty: 0,
    actual_start: null,
    actual_end: null,
    status: 'PENDING',
    remarks: remarkText,
    created_by: userId,
    updated_by: userId
  }));

  await db.RouteCardOperation.bulkCreate(opsPayload, { transaction });
  return reworkRouteCard;
};

exports.ensureSingleReworkOperation = async ({
  reworkRouteCardId,
  sourceRouteCardOperationId,
  includeFollowingOperations = false,
  reworkSourceLabel = 'Rework',
  reworkQty,
  userId,
  transaction
}) => {
  const qty = parseFloat(reworkQty || 0);
  if (!(qty > 0)) {
    throw new Error('Rework quantity must be greater than 0');
  }

  const reworkRc = await db.RouteCard.findByPk(reworkRouteCardId, { transaction });
  if (!reworkRc) {
    throw new Error('Rework Route Card not found');
  }

  const sourceOp = await db.RouteCardOperation.findByPk(sourceRouteCardOperationId, { transaction });
  if (!sourceOp) {
    throw new Error('Source rework operation not found');
  }

  if (String(sourceOp.route_card_id) !== String(reworkRc.previous_route_card_id)) {
    throw new Error('Source operation does not belong to previous Route Card');
  }
  const previousRc = await db.RouteCard.findByPk(reworkRc.previous_route_card_id, {
    attributes: ['route_card_number'],
    transaction
  });
  const previousRcNo = previousRc?.route_card_number || reworkRc.previous_route_card_id || '';
  const reworkQtyText = Number.isFinite(qty) ? qty.toFixed(3) : String(qty);
  const remarkText = `${reworkSourceLabel} from previous Route Card ${previousRcNo} | Rework Qty: ${reworkQtyText}`;

  await reworkRc.update({ production_qty: qty, updated_by: userId || reworkRc.updated_by }, { transaction });

  await db.RouteCardOperation.destroy({
    where: { route_card_id: reworkRc.id },
    transaction
  });

  let sourceOps = [sourceOp];
  if (includeFollowingOperations) {
    const allSourceOps = await db.RouteCardOperation.findAll({
      where: { route_card_id: reworkRc.previous_route_card_id },
      order: [['operation_sequence_no', 'ASC'], ['id', 'ASC']],
      transaction
    });
    const selectedSeq = parseInt(sourceOp.operation_sequence_no, 10) || 0;
    sourceOps = allSourceOps.filter((op) => (parseInt(op.operation_sequence_no, 10) || 0) >= selectedSeq);
  }

  const payload = sourceOps.map((op, idx) => ({
    route_card_id: reworkRc.id,
    operation_sequence_no: op.operation_sequence_no,
    operation_code: op.operation_code,
    operation_name: op.operation_name || null,
    operator_id: null,
    department_id: op.department_id || null,
    work_center_id: op.work_center_id,
    planned_qty: qty,
    input_qty: idx === 0 ? qty : 0,
    completed_qty: 0,
    checked_qty: 0,
    rejected_qty: 0,
    fr_rejected_qty: 0,
    mr_rejected_qty: 0,
    rejection_fr: false,
    rejection_mr: false,
    rework_qty: 0,
    actual_start: null,
    actual_end: null,
    status: 'PENDING',
    remarks: remarkText,
    created_by: userId,
    updated_by: userId
  }));
  if (payload.length > 0) {
    await db.RouteCardOperation.bulkCreate(payload, { transaction });
  }

  return reworkRc;
};

exports.startOperation = async ({ routeCardOperationId, userId }) => {
  const transaction = await db.sequelize.transaction();
  try {
    const op = await db.RouteCardOperation.findByPk(routeCardOperationId, { transaction });
    if (!op) {
      await transaction.rollback();
      throw new Error('Route Card operation not found');
    }
    if (op.status !== 'PENDING') {
      await transaction.rollback();
      throw new Error(`Operation can only be started from PENDING. Current: ${op.status}`);
    }

    // Enforce sequence within this route card.
    // Previous operations are considered ready only when:
    // - SKIPPED/CANCELLED, or
    // - COMPLETED and QC is APPROVED.
    const prevOps = await db.RouteCardOperation.findAll({
      where: {
        route_card_id: op.route_card_id,
        operation_sequence_no: { [Op.lt]: op.operation_sequence_no }
      },
      include: [
        {
          model: db.ProductionQC,
          as: 'productionQC',
          attributes: ['id', 'status'],
          required: false
        }
      ],
      order: [['operation_sequence_no', 'ASC']],
      transaction
    });

    const blockingPrev = prevOps.find((prev) => {
      const st = String(prev.status || '');
      if (st === 'SKIPPED' || st === 'CANCELLED') return false;
      if (st === 'COMPLETED') {
        return String(prev.productionQC?.status || '') !== 'APPROVED';
      }
      return true;
    });
    if (blockingPrev) {
      await transaction.rollback();
      if (String(blockingPrev.status) === 'COMPLETED') {
        throw new Error(`QC approval pending for operation ${blockingPrev.operation_sequence_no}. Approve QC before starting next operation.`);
      }
      throw new Error(`Complete operation ${blockingPrev.operation_sequence_no} first.`);
    }

    await op.update(
      {
        status: 'IN_PROGRESS',
        actual_start: new Date(),
        updated_by: userId
      },
      { transaction }
    );

    await transaction.commit();
    return op;
  } catch (e) {
    await transaction.rollback();
    throw e;
  }
};

exports.completeOperation = async ({ routeCardOperationId, data, userId }) => {
  const transaction = await db.sequelize.transaction();
  try {
    const op = await db.RouteCardOperation.findByPk(routeCardOperationId, { transaction });
    if (!op) {
      await transaction.rollback();
      throw new Error('Route Card operation not found');
    }
    if (op.status !== 'IN_PROGRESS') {
      await transaction.rollback();
      throw new Error(`Operation can only be completed from IN_PROGRESS. Current: ${op.status}`);
    }

    const totalQty = parseFloat(op.planned_qty || 0);
    const hasAcceptedQty = data.completed_qty !== undefined && data.completed_qty !== null && String(data.completed_qty).trim() !== '';
    if (!hasAcceptedQty) {
      await transaction.rollback();
      throw new Error('Accepted Qty is required');
    }
    const completedQty = parseFloat(data.completed_qty);
    const frRejectedQty = data.fr_rejected_qty != null ? parseFloat(data.fr_rejected_qty) : 0;
    const mrRejectedQty = data.mr_rejected_qty != null ? parseFloat(data.mr_rejected_qty) : 0;
    const rejectedQty = frRejectedQty + mrRejectedQty;
    const rejectionFr = frRejectedQty > 0;
    const rejectionMr = mrRejectedQty > 0;
    const operatorIds = parseIdArray(
      data.operator_ids !== undefined
        ? data.operator_ids
        : (data.operator_id !== undefined ? data.operator_id : (op.operator_ids || op.operator_id))
    );
    const operatorIdRaw = operatorIds[0] || null;
    if (!Number.isFinite(completedQty)) {
      await transaction.rollback();
      throw new Error('Accepted Qty must be a valid number');
    }
    if (completedQty < 0 || frRejectedQty < 0 || mrRejectedQty < 0) {
      await transaction.rollback();
      throw new Error('Accepted Qty, FR Rejection Qty and MR Rejection Qty cannot be negative');
    }
    if (!operatorIdRaw) {
      await transaction.rollback();
      throw new Error('Operator selection is required before completing operation');
    }
    const operatorRows = await db.ProductionOperator.findAll({
      where: { id: { [Op.in]: operatorIds }, status: 'ACTIVE' },
      attributes: ['id'],
      transaction
    });
    if (operatorRows.length !== operatorIds.length) {
      await transaction.rollback();
      throw new Error('One or more selected operators are invalid or inactive');
    }
    if (completedQty > totalQty) {
      await transaction.rollback();
      throw new Error(`Accepted Qty cannot be greater than Total Qty (${totalQty})`);
    }
    if (rejectedQty > totalQty) {
      await transaction.rollback();
      throw new Error(`Rejected Qty cannot be greater than Total Qty (${totalQty})`);
    }
    if (completedQty + rejectedQty > totalQty) {
      await transaction.rollback();
      throw new Error(`Accepted Qty + Rejected Qty cannot be greater than Total Qty (${totalQty})`);
    }

    const remarksText = data.remarks != null ? String(data.remarks).trim() : '';
    if (rejectedQty > 0 && !remarksText) {
      await transaction.rollback();
      throw new Error('Remarks is required when Rejection Qty is greater than 0');
    }

    await op.update(
      {
        status: 'COMPLETED',
        actual_end: data.actual_end ? new Date(data.actual_end) : new Date(),
        actual_start: data.actual_start ? new Date(data.actual_start) : op.actual_start,
        completed_qty: completedQty,
        checked_qty: 0,
        rejected_qty: rejectedQty,
        fr_rejected_qty: frRejectedQty,
        mr_rejected_qty: mrRejectedQty,
        rejection_fr: rejectionFr,
        rejection_mr: rejectionMr,
        rework_qty: 0,
        operator_id: operatorIdRaw,
        operator_ids: operatorIds.length > 0 ? JSON.stringify(operatorIds) : null,
        remarks: data.remarks !== undefined ? remarksText : op.remarks,
        updated_by: userId
      },
      { transaction }
    );

    // Move completed qty to next operation as input_qty
    const nextOp = await db.RouteCardOperation.findOne({
      where: {
        route_card_id: op.route_card_id,
        operation_sequence_no: { [Op.gt]: op.operation_sequence_no }
      },
      order: [['operation_sequence_no', 'ASC']],
      transaction
    });
    if (nextOp) {
      const nextInput = parseFloat(nextOp.input_qty || 0) + completedQty;
      await nextOp.update({ input_qty: nextInput, updated_by: userId }, { transaction });
    } else {
      // If last operation completed, close the route card
      const rc = await db.RouteCard.findByPk(op.route_card_id, { transaction });
      if (rc && rc.status === 'OPEN') {
        await rc.update({ status: 'CLOSED', updated_by: userId }, { transaction });
      }
    }

    // Auto-create Production QC (same as former "Move to QC" action)
    await productionQCService.createFromRouteCardOperationIfNeeded({
      routeCardOperationId: op.id,
      userId,
      transaction
    });

    await transaction.commit();
    return op;
  } catch (e) {
    await transaction.rollback();
    throw e;
  }
};

exports.updateActuals = async ({ routeCardOperationId, data, userId }) => {
  const transaction = await db.sequelize.transaction();
  try {
    const op = await db.RouteCardOperation.findByPk(routeCardOperationId, { transaction });
    if (!op) {
      await transaction.rollback();
      throw new Error('Route Card operation not found');
    }

    const patch = { updated_by: userId };
    if (data.actual_start !== undefined) {
      patch.actual_start = data.actual_start ? new Date(data.actual_start) : null;
    }
    if (data.actual_end !== undefined) {
      patch.actual_end = data.actual_end ? new Date(data.actual_end) : null;
    }
    if (data.operator_ids !== undefined || data.operator_id !== undefined) {
      const operatorIds = parseIdArray(data.operator_ids !== undefined ? data.operator_ids : data.operator_id);
      if (operatorIds.length === 0) {
        patch.operator_id = null;
        patch.operator_ids = null;
      } else {
        const operatorRows = await db.ProductionOperator.findAll({
          where: { id: { [Op.in]: operatorIds }, status: 'ACTIVE' },
          attributes: ['id'],
          transaction
        });
        if (operatorRows.length !== operatorIds.length) {
          await transaction.rollback();
          throw new Error('One or more selected operators are invalid or inactive');
        }
        patch.operator_id = operatorIds[0];
        patch.operator_ids = JSON.stringify(operatorIds);
      }
    }

    await op.update(patch, { transaction });
    await transaction.commit();
    return op;
  } catch (e) {
    await transaction.rollback();
    throw e;
  }
};

exports.cancelOperation = async ({ routeCardOperationId, userId, remarks }) => {
  const productionWipService = require('./productionWipService');
  const transaction = await db.sequelize.transaction();
  try {
    const op = await db.RouteCardOperation.findByPk(routeCardOperationId, { transaction });
    if (!op) {
      await transaction.rollback();
      throw new Error('Route Card operation not found');
    }
    if (op.status !== 'PENDING' && op.status !== 'IN_PROGRESS') {
      await transaction.rollback();
      throw new Error(`Only PENDING or IN_PROGRESS operations can be cancelled (current: ${op.status})`);
    }

    await productionWipService.reverseAllConsumptionsForOperation(transaction, {
      routeCardOperationId,
      userId,
    });

    const remarkText = remarks != null ? String(remarks).trim() : '';
    const merged = [remarkText, op.remarks].filter(Boolean).join(' | ');

    await op.update(
      {
        status: 'CANCELLED',
        remarks: merged || op.remarks,
        updated_by: userId,
      },
      { transaction }
    );

    await transaction.commit();
    return op;
  } catch (e) {
    await transaction.rollback();
    throw e;
  }
};
