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

/**
 * Calculate work center capacity 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<Number>} Available capacity in hours
 */
const getWorkCenterCapacity = 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`);
  }

  // Get capacity per day (in hours)
  const capacityPerDay = parseFloat(workCenter.capacity || 0);
  
  // Calculate number of days
  const start = new Date(startDate);
  const end = new Date(endDate);
  const daysDiff = Math.ceil((end - start) / (1000 * 60 * 60 * 24));
  
  // Total capacity = capacity per day × number of days
  const totalCapacity = capacityPerDay * daysDiff;

  // Get already scheduled hours in this period
  const scheduledHours = await getScheduledHours(workCenterId, startDate, endDate, transaction);

  // Available capacity = total capacity - scheduled hours
  return Math.max(0, totalCapacity - scheduledHours);
};

/**
 * Get scheduled hours for a work center in 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<Number>} Scheduled hours
 */
const getScheduledHours = async (workCenterId, startDate, endDate, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const schedules = await db.ProductionSchedule.findAll({
    where: {
      work_center_id: workCenterId,
      schedule_status: {
        [Op.in]: ['SCHEDULED', 'IN_PROGRESS']
      },
      [Op.or]: [
        {
          scheduled_start_date: {
            [Op.between]: [startDate, endDate]
          }
        },
        {
          scheduled_end_date: {
            [Op.between]: [startDate, endDate]
          }
        },
        {
          [Op.and]: [
            { scheduled_start_date: { [Op.lte]: startDate } },
            { scheduled_end_date: { [Op.gte]: endDate } }
          ]
        }
      ]
    },
    attributes: [
      [db.sequelize.fn('SUM', db.sequelize.col('planned_hours')), 'total_hours']
    ],
    raw: true,
    ...options
  });

  return parseFloat(schedules[0]?.total_hours || 0);
};

/**
 * Get work center loading (utilization percentage)
 * 
 * @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>} Loading data with utilization percentage
 */
const getWorkCenterLoading = async (workCenterId, startDate, endDate, transaction = null) => {
  const workCenter = await db.WorkCenter.findByPk(workCenterId, transaction ? { transaction } : {});
  if (!workCenter) {
    throw new Error(`Work Center ${workCenterId} not found`);
  }

  const capacityPerDay = parseFloat(workCenter.capacity || 0);
  const start = new Date(startDate);
  const end = new Date(endDate);
  const daysDiff = Math.max(1, Math.ceil((end - start) / (1000 * 60 * 60 * 24)));
  const totalCapacity = capacityPerDay * daysDiff;

  // Real-time loading from Route Card operations
  const ops = await db.RouteCardOperation.findAll({
    where: {
      work_center_id: workCenterId,
      status: { [Op.in]: ['PENDING', 'IN_PROGRESS', 'COMPLETED'] }
    },
    attributes: ['id', 'status', 'actual_start', 'actual_end'],
    ...(transaction ? { transaction } : {})
  });
  const now = new Date();
  let scheduledHours = 0;
  ops.forEach((op) => {
    const s = op.actual_start ? new Date(op.actual_start) : null;
    const e = op.actual_end ? new Date(op.actual_end) : null;
    if (s && e && e > s) {
      scheduledHours += (e - s) / (1000 * 60 * 60);
    } else if (s && op.status === 'IN_PROGRESS') {
      const effectiveEnd = now > s ? now : s;
      scheduledHours += (effectiveEnd - s) / (1000 * 60 * 60);
    }
  });
  const utilization = totalCapacity > 0 ? (scheduledHours / totalCapacity) * 100 : 0;

  return {
    workCenterId,
    workCenterCode: workCenter.work_center_code,
    workCenterDescription: workCenter.description,
    totalCapacity,
    scheduledHours: parseFloat(scheduledHours.toFixed(2)),
    availableCapacity: Math.max(0, totalCapacity - scheduledHours),
    utilization: Math.min(100, utilization)
  };
};

/**
 * Forward schedule a Production Order
 * Schedules from start date forward
 * 
 * @param {Object} productionOrder - Production Order object
 * @param {Date} startDate - Start date for scheduling
 * @param {Integer} userId - User ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of created schedule records
 */
exports.forwardScheduleProductionOrder = async (productionOrder, startDate, userId, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  if (!productionOrder.routing_id) {
    throw new Error('Production Order must have a routing to schedule');
  }

  // 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) {
    throw new Error('Routing has no active operations');
  }

  // Delete existing schedules for this Production Order
  await db.ProductionSchedule.destroy({
    where: {
      production_order_id: productionOrder.id,
      schedule_status: { [Op.in]: ['SCHEDULED', 'ON_HOLD'] }
    },
    ...options
  });

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

  // Schedule 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;
    const plannedHours = (Math.max(plannedMachineTime, plannedLaborTime) + (setupTime / 60));

    // Add queue time
    const scheduledStartDate = new Date(currentDate);
    scheduledStartDate.setMinutes(scheduledStartDate.getMinutes() + queueTime);

    // Calculate end date (start + planned hours)
    const scheduledEndDate = new Date(scheduledStartDate);
    scheduledEndDate.setHours(scheduledEndDate.getHours() + plannedHours);

    // Check work center capacity
    const availableCapacity = await getWorkCenterCapacity(
      operation.work_center_id,
      scheduledStartDate,
      scheduledEndDate,
      transaction
    );

    if (availableCapacity < plannedHours) {
      // If not enough capacity, shift to next available slot
      // For simplicity, we'll add a buffer and reschedule
      const bufferHours = plannedHours - availableCapacity;
      scheduledStartDate.setHours(scheduledStartDate.getHours() + bufferHours);
      scheduledEndDate.setHours(scheduledEndDate.getHours() + bufferHours);
    }

    // Create schedule record
    const schedule = await db.ProductionSchedule.create({
      company_id: productionOrder.company_id,
      plant_id: productionOrder.plant_id,
      production_order_id: productionOrder.id,
      routing_operation_id: operation.id,
      work_center_id: operation.work_center_id,
      operation_number: operation.operation_number,
      scheduled_start_date: scheduledStartDate,
      scheduled_end_date: scheduledEndDate,
      planned_hours: plannedHours,
      scheduled_quantity: orderQuantity,
      priority: productionOrder.priority || 0,
      schedule_status: 'SCHEDULED',
      schedule_type: 'FORWARD',
      created_by: userId,
      updated_by: userId
    }, options);

    schedules.push(schedule);

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

  return schedules;
};

/**
 * Backward schedule a Production Order
 * Schedules from finish date backward
 * 
 * @param {Object} productionOrder - Production Order object
 * @param {Date} finishDate - Finish date for scheduling
 * @param {Integer} userId - User ID
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of created schedule records
 */
exports.backwardScheduleProductionOrder = async (productionOrder, finishDate, userId, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  if (!productionOrder.routing_id) {
    throw new Error('Production Order must have a routing to schedule');
  }

  // Get routing with operations (reverse order for backward scheduling)
  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', 'DESC']] // Reverse order
      }
    ],
    ...options
  });

  if (!routing || !routing.operations || routing.operations.length === 0) {
    throw new Error('Routing has no active operations');
  }

  // Delete existing schedules
  await db.ProductionSchedule.destroy({
    where: {
      production_order_id: productionOrder.id,
      schedule_status: { [Op.in]: ['SCHEDULED', 'ON_HOLD'] }
    },
    ...options
  });

  const schedules = [];
  const orderQuantity = parseFloat(productionOrder.order_quantity || 0);
  const baseQuantity = parseFloat(routing.base_quantity || 1);
  let currentDate = new Date(finishDate);

  // Schedule each operation backward
  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 moveTime = parseInt(operation.move_time || 0);

    // Calculate planned times
    const quantityRatio = orderQuantity / baseQuantity;
    const plannedMachineTime = machineTime * quantityRatio;
    const plannedLaborTime = laborTime * quantityRatio;
    const plannedHours = (Math.max(plannedMachineTime, plannedLaborTime) + (setupTime / 60));

    // End date is current date
    const scheduledEndDate = new Date(currentDate);

    // Start date = end date - planned hours
    const scheduledStartDate = new Date(scheduledEndDate);
    scheduledStartDate.setHours(scheduledStartDate.getHours() - plannedHours);

    // Check work center capacity and adjust if needed
    const availableCapacity = await getWorkCenterCapacity(
      operation.work_center_id,
      scheduledStartDate,
      scheduledEndDate,
      transaction
    );

    if (availableCapacity < plannedHours) {
      // Shift backward to find available slot
      const bufferHours = plannedHours - availableCapacity;
      scheduledStartDate.setHours(scheduledStartDate.getHours() - bufferHours);
      scheduledEndDate.setHours(scheduledEndDate.getHours() - bufferHours);
    }

    // Create schedule record
    const schedule = await db.ProductionSchedule.create({
      company_id: productionOrder.company_id,
      plant_id: productionOrder.plant_id,
      production_order_id: productionOrder.id,
      routing_operation_id: operation.id,
      work_center_id: operation.work_center_id,
      operation_number: operation.operation_number,
      scheduled_start_date: scheduledStartDate,
      scheduled_end_date: scheduledEndDate,
      planned_hours: plannedHours,
      scheduled_quantity: orderQuantity,
      priority: productionOrder.priority || 0,
      schedule_status: 'SCHEDULED',
      schedule_type: 'BACKWARD',
      created_by: userId,
      updated_by: userId
    }, options);

    schedules.push(schedule);

    // Move backward to previous operation (subtract move time)
    currentDate = new Date(scheduledStartDate);
    currentDate.setMinutes(currentDate.getMinutes() - moveTime);
  }

  // Reverse schedules array to maintain operation order
  return schedules.reverse();
};

/**
 * Get production schedule for a date range
 * 
 * @param {Integer} companyId - Company ID
 * @param {Integer} plantId - Plant ID (optional)
 * @param {Date} startDate - Start date
 * @param {Date} endDate - End date
 * @param {Integer} workCenterId - Work Center ID (optional)
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of schedule records
 */
exports.getProductionSchedule = async (companyId, plantId = null, startDate, endDate, workCenterId = null, transaction = null) => {
  const options = transaction ? { transaction } : {};

  const rangeStart = new Date(startDate);
  const rangeEnd = new Date(endDate);
  rangeEnd.setHours(23, 59, 59, 999);
  const now = new Date();

  const opWhere = {
    status: { [Op.in]: ['PENDING', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED'] }
  };
  if (workCenterId) opWhere.work_center_id = workCenterId;

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

  const ops = await db.RouteCardOperation.findAll({
    where: opWhere,
    include: [
      { model: db.WorkCenter, as: 'workCenter' },
      {
        model: db.RouteCard,
        as: 'routeCard',
        where: routeCardWhere,
        include: [
          {
            model: db.ProductionOrder,
            as: 'productionOrder',
            include: [{ model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description'] }]
          },
          { model: db.WorkOrder, as: 'workOrder', attributes: ['id', 'work_order_number', 'priority'] },
          { model: db.Material, as: 'finishedMaterial', attributes: ['id', 'material_number', 'description'] },
          { model: db.Plant, as: 'plant' }
        ]
      }
    ],
    order: [['operation_sequence_no', 'ASC']],
    ...options
  });

  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;
  };

  return ops
    .map((op) => {
      const json = typeof op.toJSON === 'function' ? op.toJSON() : op;
      const rc = json.routeCard || {};
      const plannedStart = json.actual_start || (rc.start_date ? new Date(rc.start_date) : null);
      const plannedEnd = json.actual_end || null;
      const startForRange = plannedStart;
      const endForRange = plannedEnd || (json.status === 'IN_PROGRESS' ? now : plannedStart);
      if (!overlapsRange(startForRange, endForRange)) return null;

      const durationHours =
        startForRange && endForRange && new Date(endForRange) > new Date(startForRange)
          ? (new Date(endForRange) - new Date(startForRange)) / (1000 * 60 * 60)
          : 0;

      const mappedStatus =
        json.status === 'PENDING'
          ? 'SCHEDULED'
          : json.status === 'IN_PROGRESS'
            ? 'IN_PROGRESS'
            : json.status === 'COMPLETED'
              ? 'COMPLETED'
              : json.status === 'CANCELLED'
                ? 'CANCELLED'
                : 'SCHEDULED';

      return {
        id: `rcop_${json.id}`,
        production_order_id: rc.production_order_id || null,
        operation_number: json.operation_sequence_no,
        work_center_id: json.work_center_id,
        scheduled_start_date: plannedStart,
        scheduled_end_date: plannedEnd,
        planned_hours: parseFloat(durationHours.toFixed(2)),
        scheduled_quantity: parseFloat(json.planned_qty || 0),
        schedule_status: mappedStatus,
        priority: rc?.workOrder?.priority || rc?.productionOrder?.priority || null,
        productionOrder: rc.productionOrder || null,
        workCenter: json.workCenter || null,
        plant: rc.plant || null,
        routingOperation: {
          operation_description: json.operation_name || json.operation_code || null
        },
        routeCard: rc,
        workOrder: rc.workOrder || null
      };
    })
    .filter(Boolean)
    .sort((a, b) => {
      const aTime = a.scheduled_start_date ? new Date(a.scheduled_start_date).getTime() : 0;
      const bTime = b.scheduled_start_date ? new Date(b.scheduled_start_date).getTime() : 0;
      if (aTime !== bTime) return aTime - bTime;
      return (a.operation_number || 0) - (b.operation_number || 0);
    });
};

/**
 * Get work center loading for multiple work centers
 * 
 * @param {Integer} companyId - Company ID
 * @param {Integer} plantId - Plant ID (optional)
 * @param {Date} startDate - Start date
 * @param {Date} endDate - End date
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Array>} Array of work center loading data
 */
exports.getWorkCenterLoadings = async (companyId, plantId = null, startDate, endDate, transaction = null) => {
  const options = transaction ? { transaction } : {};
  
  const where = {
    company_id: companyId,
    status: 'ACTIVE'
  };

  if (plantId) {
    where.plant_id = plantId;
  }

  const workCenters = await db.WorkCenter.findAll({
    where,
    ...options
  });

  const loadings = [];
  for (const workCenter of workCenters) {
    const loading = await getWorkCenterLoading(workCenter.id, startDate, endDate, transaction);
    loadings.push(loading);
  }

  return loadings.sort((a, b) => b.utilization - a.utilization);
};

/**
 * Optimize production schedule
 * Reschedules Production Orders to balance work center loading
 * If productionOrderIds is empty, optimizes all active Production Orders
 * 
 * @param {Array} productionOrderIds - Array of Production Order IDs (optional, if empty optimizes all)
 * @param {Date} startDate - Start date for optimization
 * @param {Date} endDate - End date for optimization
 * @param {Integer} userId - User ID
 * @param {Integer} companyId - Company ID (optional)
 * @param {Integer} plantId - Plant ID (optional)
 * @param {Object} transaction - Sequelize transaction (optional)
 * @returns {Promise<Object>} Optimization results
 */
exports.optimizeSchedule = async (productionOrderIds, startDate, endDate, userId, companyId = null, plantId = null, transaction = null) => {
  const options = transaction ? { transaction } : {};
  const useTransaction = !transaction;
  
  let txn = transaction;
  if (useTransaction) {
    txn = await db.sequelize.transaction();
  }

  try {
    const where = {
      status: { [Op.in]: ['DRAFT', 'RELEASED', 'IN_PROGRESS'] }
    };

    if (productionOrderIds && productionOrderIds.length > 0) {
      where.id = { [Op.in]: productionOrderIds };
    }

    if (companyId) {
      where.company_id = companyId;
    }

    if (plantId) {
      where.plant_id = plantId;
    }

    const productionOrders = await db.ProductionOrder.findAll({
      where,
      include: [
        { model: db.Routing, as: 'routing' }
      ],
      ...(txn ? { transaction: txn } : {})
    });

    if (productionOrders.length === 0) {
      return {
        optimized: 0,
        conflicts: [],
        schedules: [],
        message: 'No Production Orders found to optimize'
      };
    }

    const optimizedSchedules = [];
    const conflicts = [];

    // Sort Production Orders by priority and due date
    productionOrders.sort((a, b) => {
      const priorityDiff = (b.priority || 0) - (a.priority || 0);
      if (priorityDiff !== 0) return priorityDiff;
      const aDate = a.finish_date || a.start_date || new Date();
      const bDate = b.finish_date || b.start_date || new Date();
      return aDate - bDate;
    });

    // Schedule each Production Order
    for (const po of productionOrders) {
      try {
        if (!po.routing_id) {
          conflicts.push({
            productionOrderId: po.id,
            productionOrderNumber: po.production_order_number,
            error: 'Production Order has no routing'
          });
          continue;
        }

        if (po.finish_date) {
          // Backward schedule from finish date
          const schedules = await exports.backwardScheduleProductionOrder(po, po.finish_date, userId, txn);
          optimizedSchedules.push(...schedules);
        } else if (po.start_date) {
          // Forward schedule from start date
          const schedules = await exports.forwardScheduleProductionOrder(po, po.start_date, userId, txn);
          optimizedSchedules.push(...schedules);
        } else {
          // Forward schedule from today
          const schedules = await exports.forwardScheduleProductionOrder(po, new Date(), userId, txn);
          optimizedSchedules.push(...schedules);
        }
      } catch (error) {
        conflicts.push({
          productionOrderId: po.id,
          productionOrderNumber: po.production_order_number,
          error: error.message
        });
      }
    }

    if (useTransaction) {
      await txn.commit();
    }

    return {
      optimized: optimizedSchedules.length,
      conflicts,
      schedules: optimizedSchedules,
      message: `Optimized ${optimizedSchedules.length} schedules. ${conflicts.length} conflicts.`
    };
  } catch (error) {
    if (useTransaction) {
      await txn.rollback();
    }
    throw error;
  }
};

// Export helper functions
exports.getWorkCenterCapacity = getWorkCenterCapacity;
exports.getWorkCenterLoading = getWorkCenterLoading;
exports.getScheduledHours = getScheduledHours;

