const db = require('../models');
const { Op } = require('sequelize');
const backflushingService = require('./backflushingService');

/**
 * Create routing execution records for a Production Order
 * Called when Production Order is released and has a routing
 * 
 * @param {Object} productionOrder - Production Order object
 * @param {Integer} userId - User ID creating the execution
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of created execution records
 */
exports.createRoutingExecutionForProductionOrder = async (productionOrder, userId, transaction = null) => {
  if (!productionOrder.routing_id) {
    return [];
  }

  const options = transaction ? { transaction } : {};

  // Get routing with operations
  const routing = await db.Routing.findByPk(productionOrder.routing_id, {
    include: [
      {
        model: db.RoutingOperation,
        as: 'operations',
        where: { status: 'ACTIVE' },
        required: false,
        include: [
          {
            model: db.WorkCenter,
            as: 'workCenter'
          }
        ],
        order: [['operation_number', 'ASC']]
      }
    ],
    ...options
  });

  if (!routing || !routing.operations || routing.operations.length === 0) {
    return [];
  }

  const executions = [];
  const orderQuantity = parseFloat(productionOrder.order_quantity || 0);
  const baseQuantity = parseFloat(routing.base_quantity || 1);
  const startDate = productionOrder.start_date ? new Date(productionOrder.start_date) : new Date();
  let currentDate = new Date(startDate);

  // Calculate production dates for each operation
  for (const operation of routing.operations) {
    const setupTime = parseInt(operation.setup_time || 0);
    const machineTime = parseFloat(operation.machine_time || 0);
    const laborTime = parseFloat(operation.labor_time || 0);
    const queueTime = parseInt(operation.queue_time || 0);
    const moveTime = parseInt(operation.move_time || 0);

    // Calculate planned times based on order quantity
    const quantityRatio = orderQuantity / baseQuantity;
    const plannedMachineTime = machineTime * quantityRatio;
    const plannedLaborTime = laborTime * quantityRatio;

    // Calculate operation duration (in minutes)
    // Use the maximum of machine time and labor time, plus setup time
    const operationDuration = Math.max(plannedMachineTime, plannedLaborTime) * 60 + setupTime;

    // Planned start date (after queue time from previous operation)
    const plannedStartDate = new Date(currentDate);
    plannedStartDate.setMinutes(plannedStartDate.getMinutes() + queueTime);

    // Planned end date
    const plannedEndDate = new Date(plannedStartDate);
    plannedEndDate.setMinutes(plannedEndDate.getMinutes() + operationDuration);

    // Create execution record
    const execution = await db.ProductionOrderRoutingExecution.create({
      production_order_id: productionOrder.id,
      routing_operation_id: operation.id,
      operation_number: operation.operation_number,
      work_center_id: operation.work_center_id,
      planned_start_date: plannedStartDate,
      planned_end_date: plannedEndDate,
      planned_setup_time: setupTime,
      planned_machine_time: plannedMachineTime,
      planned_labor_time: plannedLaborTime,
      confirmed_quantity: 0,
      status: 'PENDING',
      created_by: userId,
      updated_by: userId
    }, options);

    executions.push(execution);

    // Move to next operation (add move time)
    currentDate = new Date(plannedEndDate);
    currentDate.setMinutes(currentDate.getMinutes() + moveTime);
  }

  return executions;
};

/**
 * Start a routing operation
 * 
 * @param {Integer} executionId - Execution record ID
 * @param {Integer} userId - User ID starting the operation
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Updated execution record
 */
exports.startOperation = async (executionId, userId, transaction = null) => {
  const options = transaction ? { transaction } : {};

  const execution = await db.ProductionOrderRoutingExecution.findByPk(executionId, {
    include: [
      {
        model: db.ProductionOrder,
        as: 'productionOrder'
      }
    ],
    ...options
  });

  if (!execution) {
    throw new Error(`Routing execution ${executionId} not found`);
  }

  if (execution.status !== 'PENDING') {
    throw new Error(`Operation can only be started from PENDING status. Current status: ${execution.status}`);
  }

  // Enforce operation sequence: do not allow starting an operation
  // if any previous operation (lower operation_number) is not completed/skipped/cancelled.
  const blockingPrevOperation = await db.ProductionOrderRoutingExecution.findOne({
    where: {
      production_order_id: execution.production_order_id,
      operation_number: { [Op.lt]: execution.operation_number },
      status: { [Op.notIn]: ['COMPLETED', 'SKIPPED', 'CANCELLED'] }
    },
    order: [['operation_number', 'DESC']],
    ...options
  });

  if (blockingPrevOperation) {
    throw new Error(
      `Cannot start operation ${execution.operation_number} before completing operation ${blockingPrevOperation.operation_number}.`
    );
  }

  const FLOW_EPS = 0.0005;
  const flowLineType = (material) => {
    if (!material) return null;
    const t = String(material.material_type || '').toUpperCase();
    if (t === 'RAW_MATERIAL') return 'RAW';
    if (t === 'CONSUMABLE' || t === 'PACKAGING') return 'FASTENER';
    return null;
  };

  const poId = execution.production_order_id;
  const components = await db.ProductionOrderComponent.findAll({
    where: { production_order_id: poId },
    include: [{ model: db.Material, as: 'material', attributes: ['material_type'] }],
    ...options
  });
  const receipts = await db.ProductionOrderMaterialReceiptLine.findAll({
    where: { production_order_id: poId },
    ...options
  });
  const receiptMap = new Map(
    receipts.map((r) => [`${String(r.line_type || '').toUpperCase()}|${String(r.line_key || '').trim()}`, r])
  );

  for (const c of components) {
    const lt = flowLineType(c.material);
    if (!lt) continue;
    const req = parseFloat(c.required_quantity || 0) || 0;
    if (req <= FLOW_EPS) continue;
    const issued = parseFloat(c.issued_quantity || 0) || 0;
    if (issued <= FLOW_EPS) {
      throw new Error(
        'Cannot start production operation: issue raw materials / fasteners from stores first (not issued for at least one required line).'
      );
    }
    let rec =
      receiptMap.get(`${lt}|${String(c.id)}`) ||
      (lt === 'FASTENER' && c.material_id ? receiptMap.get(`FASTENER|${String(c.material_id)}`) : null);
    const received = rec ? parseFloat(rec.received_qty || 0) || 0 : 0;
    if (received + FLOW_EPS < issued) {
      throw new Error(
        'Cannot start production operation: materials are issued but not fully received in production. Record material receive until received qty covers issued qty for all lines.'
      );
    }
  }

  await execution.update({
    status: 'IN_PROGRESS',
    actual_start_date: new Date(),
    started_by: userId,
    updated_by: userId
  }, options);

  return execution;
};

/**
 * Complete a routing operation
 * 
 * @param {Integer} executionId - Execution record ID
 * @param {Object} data - Completion data (actual times, confirmed quantity, remarks)
 * @param {Integer} userId - User ID completing the operation
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Updated execution record
 */
exports.completeOperation = async (executionId, data, userId, transaction = null) => {
  const options = transaction ? { transaction } : {};

  const execution = await db.ProductionOrderRoutingExecution.findByPk(executionId, {
    include: [
      {
        model: db.ProductionOrder,
        as: 'productionOrder'
      }
    ],
    ...options
  });

  if (!execution) {
    throw new Error(`Routing execution ${executionId} not found`);
  }

  if (execution.status !== 'IN_PROGRESS') {
    throw new Error(`Operation can only be completed from IN_PROGRESS status. Current status: ${execution.status}`);
  }

  await execution.update({
    status: 'COMPLETED',
    actual_end_date: new Date(),
    actual_setup_time: data.actual_setup_time !== undefined ? parseInt(data.actual_setup_time) : execution.actual_setup_time,
    actual_machine_time: data.actual_machine_time !== undefined ? parseFloat(data.actual_machine_time) : execution.actual_machine_time,
    actual_labor_time: data.actual_labor_time !== undefined ? parseFloat(data.actual_labor_time) : execution.actual_labor_time,
    confirmed_quantity: data.confirmed_quantity !== undefined ? parseFloat(data.confirmed_quantity) : execution.confirmed_quantity,
    remarks: data.remarks || execution.remarks,
    completed_by: userId,
    updated_by: userId
  }, options);

  // Optional: Trigger backflush on operation completion (instead of Production Confirmation posting).
  // Enabled by setting BACKFLUSH_TRIGGER=OPERATION_COMPLETE.
  if (process.env.BACKFLUSH_TRIGGER === 'OPERATION_COMPLETE') {
    const qty = data.confirmed_quantity !== undefined ? parseFloat(data.confirmed_quantity) : null;
    if (qty != null && !isNaN(qty) && qty > 0 && execution.productionOrder) {
      try {
        await backflushingService.backflushComponents(execution.productionOrder, qty, userId, transaction);
      } catch (backflushError) {
        // Do not fail operation completion if backflush fails; log and continue.
        console.error('Error backflushing components on operation complete:', backflushError);
      }
    }
  }

  return execution;
};

/**
 * Get routing executions for a Production Order
 * 
 * @param {Integer} productionOrderId - Production Order ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of execution records
 */
exports.getRoutingExecutionsForProductionOrder = async (productionOrderId, transaction = null) => {
  const options = transaction ? { transaction } : {};

  return await db.ProductionOrderRoutingExecution.findAll({
    where: {
      production_order_id: productionOrderId
    },
    include: [
      {
        model: db.RoutingOperation,
        as: 'routingOperation',
        include: [
          {
            model: db.WorkCenter,
            as: 'workCenter'
          }
        ]
      },
      {
        model: db.WorkCenter,
        as: 'workCenter'
      },
      {
        model: db.User,
        as: 'startedByUser',
        attributes: ['id', 'username', 'email', 'first_name', 'last_name']
      },
      {
        model: db.User,
        as: 'completedByUser',
        attributes: ['id', 'username', 'email', 'first_name', 'last_name']
      }
    ],
    order: [['operation_number', 'ASC']],
    ...options
  });
};

/**
 * Get routing executions for shop-floor execution screen (across orders)
 *
 * Filters:
 * - companyId / plantId (via ProductionOrder)
 * - workCenterId (execution.work_center_id)
 * - date (YYYY-MM-DD): planned_start_date within that day (optional)
 * - statuses: array of execution statuses (default handled by controller)
 */
exports.getShopFloorOperations = async (
  { companyId, plantId, workCenterId, date, statuses, limit = 200, offset = 0 },
  transaction = null
) => {
  const options = transaction ? { transaction } : {};

  const execWhere = {};
  if (workCenterId) {
    execWhere.work_center_id = workCenterId;
  }
  if (Array.isArray(statuses) && statuses.length > 0) {
    execWhere.status = { [Op.in]: statuses };
  }

  // Date filter applies to planned_start_date (if present)
  if (date) {
    const start = new Date(`${date}T00:00:00.000Z`);
    const end = new Date(`${date}T23:59:59.999Z`);
    execWhere.planned_start_date = { [Op.between]: [start, end] };
  }

  const productionOrderWhere = {};
  if (companyId) productionOrderWhere.company_id = companyId;
  if (plantId) productionOrderWhere.plant_id = plantId;

  return await db.ProductionOrderRoutingExecution.findAll({
    where: execWhere,
    include: [
      {
        model: db.ProductionOrder,
        as: 'productionOrder',
        required: true,
        where: productionOrderWhere,
        attributes: [
          'id',
          'production_order_number',
          'status',
          'order_quantity',
          'confirmed_quantity',
          'start_date',
          'finish_date'
        ],
        include: [
          {
            model: db.Material,
            as: 'finishedMaterial',
            attributes: ['id', 'material_number', 'description']
          }
        ]
      },
      {
        model: db.RoutingOperation,
        as: 'routingOperation',
        include: [
          { model: db.WorkCenter, as: 'workCenter', attributes: ['id', 'work_center_code', 'description'] }
        ]
      },
      { model: db.WorkCenter, as: 'workCenter', attributes: ['id', 'work_center_code', 'description'] }
    ],
    order: [['planned_start_date', 'ASC'], ['operation_number', 'ASC'], ['id', 'ASC']],
    limit,
    offset,
    ...options
  });
};

/**
 * Calculate work center utilization for a date range
 * 
 * @param {Integer} workCenterId - Work Center ID
 * @param {Date} startDate - Start date
 * @param {Date} endDate - End date
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Utilization data
 */
exports.getWorkCenterUtilization = async (workCenterId, startDate, endDate, transaction = null) => {
  const options = transaction ? { transaction } : {};

  const workCenter = await db.WorkCenter.findByPk(workCenterId, options);
  if (!workCenter) {
    throw new Error(`Work Center ${workCenterId} not found`);
  }

  // Real utilization based on Route Card operations (WO + Route Card flow)
  const rcOperations = await db.RouteCardOperation.findAll({
    where: {
      work_center_id: workCenterId,
      status: { [Op.in]: ['PENDING', 'IN_PROGRESS', 'COMPLETED'] }
    },
    include: [
      {
        model: db.RouteCard,
        as: 'routeCard',
        attributes: ['id', 'route_card_number', 'start_date', 'work_order_id', 'production_order_id'],
        include: [
          { model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number'] },
          { model: db.ProductionOrder, as: 'productionOrder', attributes: ['id', 'production_order_number', 'order_quantity'] }
        ]
      }
    ],
    order: [['operation_sequence_no', 'ASC']],
    ...options
  });

  const rangeStart = new Date(startDate);
  const rangeEnd = new Date(endDate);
  // cover full day for end date
  rangeEnd.setHours(23, 59, 59, 999);
  const now = new Date();

  const overlapsRange = (s, e) => {
    if (!s && !e) return false;
    const start = s ? new Date(s) : null;
    const end = e ? new Date(e) : null;
    if (start && end) return start <= rangeEnd && end >= rangeStart;
    if (start && !end) return start <= rangeEnd;
    return false;
  };

  const inRangeByRouteCardDate = (rcStartDate) => {
    if (!rcStartDate) return false;
    const d = new Date(rcStartDate);
    return d >= rangeStart && d <= rangeEnd;
  };

  const executions = rcOperations
    .filter((op) => overlapsRange(op.actual_start, op.actual_end) || inRangeByRouteCardDate(op.routeCard?.start_date))
    .map((op) => {
      const json = typeof op.toJSON === 'function' ? op.toJSON() : op;
      return {
        id: json.id,
        operation_number: json.operation_sequence_no,
        status: json.status,
        planned_qty: parseFloat(json.planned_qty || 0),
        input_qty: parseFloat(json.input_qty || 0),
        completed_qty: parseFloat(json.completed_qty || 0),
        rework_qty: parseFloat(json.rework_qty || 0),
        actual_start: json.actual_start,
        actual_end: json.actual_end,
        routeCard: json.routeCard || null,
        workOrder: json.routeCard?.workOrder || null,
        productionOrder: json.routeCard?.productionOrder || null
      };
    });

  // Calculate total utilized hours from actual timings
  let totalPlannedHours = 0; // keep key for UI compatibility; represents allocated/used hours
  let totalActualHours = 0;
  let totalCapacityHours = 0;

  const capacityPerDay = parseFloat(workCenter.capacity || 0);
  const daysDiff = Math.max(1, Math.ceil((rangeEnd - rangeStart) / (1000 * 60 * 60 * 24)));
  totalCapacityHours = capacityPerDay * daysDiff;

  for (const execution of executions) {
    const start = execution.actual_start ? new Date(execution.actual_start) : null;
    const end = execution.actual_end ? new Date(execution.actual_end) : null;

    if (start && end && end > start) {
      const hours = (end - start) / (1000 * 60 * 60);
      totalPlannedHours += hours;
      totalActualHours += hours;
    } else if (start && execution.status === 'IN_PROGRESS') {
      const effectiveEnd = now < rangeEnd ? now : rangeEnd;
      if (effectiveEnd > start) {
        const hours = (effectiveEnd - start) / (1000 * 60 * 60);
        totalPlannedHours += hours;
        totalActualHours += hours;
      }
    }
  }

  const utilizationPercentage = totalCapacityHours > 0 
    ? (totalPlannedHours / totalCapacityHours) * 100 
    : 0;

  return {
    workCenter,
    startDate,
    endDate,
    totalExecutions: executions.length,
    totalPlannedHours: parseFloat(totalPlannedHours.toFixed(2)),
    totalActualHours: parseFloat(totalActualHours.toFixed(2)),
    totalCapacityHours: parseFloat(totalCapacityHours.toFixed(2)),
    utilizationPercentage: parseFloat(utilizationPercentage.toFixed(2)),
    executions
  };
};

/**
 * Update Production Order dates based on routing execution
 * 
 * @param {Integer} productionOrderId - Production Order ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Updated Production Order
 */
exports.updateProductionOrderDatesFromRouting = async (productionOrderId, transaction = null) => {
  const options = transaction ? { transaction } : {};

  const executions = await exports.getRoutingExecutionsForProductionOrder(productionOrderId, transaction);

  if (executions.length === 0) {
    return null;
  }

  const productionOrder = await db.ProductionOrder.findByPk(productionOrderId, options);
  if (!productionOrder) {
    throw new Error(`Production Order ${productionOrderId} not found`);
  }

  // Get first operation start date and last operation end date
  const firstExecution = executions[0];
  const lastExecution = executions[executions.length - 1];

  const newStartDate = firstExecution.planned_start_date || productionOrder.start_date;
  const newFinishDate = lastExecution.planned_end_date || productionOrder.finish_date;

  await productionOrder.update({
    start_date: newStartDate,
    finish_date: newFinishDate
  }, options);

  return productionOrder;
};

