const express = require('express');
const { DateTime } = require('luxon');

const router = express.Router();
const { getDbConnection, getPoolStatus } = require('../db/connection');
const {
  getTimeRangeConditions,
} = require('../utils/timeUtils');

const TIMEZONE = 'America/New_York';

// Main operations metrics endpoint - focused on picking and shipping
router.get('/', async (req, res) => {
  const startTime = Date.now();
  console.log(`[OPERATIONS-METRICS] Starting request for timeRange: ${req.query.timeRange}`);

  const timeoutPromise = new Promise((_, reject) => {
    setTimeout(() => reject(new Error('Request timeout after 30 seconds')), 30000);
  });

  try {
    const mainOperation = async () => {
      const { timeRange, startDate, endDate } = req.query;
      console.log(`[OPERATIONS-METRICS] Getting DB connection...`);
      const { connection, release } = await getDbConnection();
      console.log(`[OPERATIONS-METRICS] DB connection obtained in ${Date.now() - startTime}ms`);

      const { whereClause, params, dateRange } = getTimeRangeConditions(timeRange, startDate, endDate);

      // Query for picking tickets - using subquery to avoid duplication from bucket join
      // Ship-together orders: only count main orders (is_sub = 0 or NULL), not sub-orders
      const pickingWhere = whereClause.replace(/date_placed/g, 'pt.createddate');

      // First get picking ticket stats without the bucket join (to avoid duplication)
      const pickingStatsQuery = `
        SELECT
          pt.createdby as employeeId,
          e.firstname,
          e.lastname,
          COUNT(DISTINCT pt.pickingid) as ticketCount,
          SUM(pt.totalpieces_picked) as piecesPicked,
          SUM(TIMESTAMPDIFF(SECOND, pt.createddate, pt.closeddate)) as pickingTimeSeconds,
          AVG(NULLIF(pt.picking_speed, 0)) as avgPickingSpeed
        FROM picking_ticket pt
        LEFT JOIN employees e ON pt.createdby = e.employeeid
        WHERE ${pickingWhere}
          AND pt.closeddate IS NOT NULL
        GROUP BY pt.createdby, e.firstname, e.lastname
      `;

      // Separate query for order counts (needs bucket join for ship-together handling)
      const orderCountQuery = `
        SELECT
          pt.createdby as employeeId,
          COUNT(DISTINCT CASE WHEN ptb.is_sub = 0 OR ptb.is_sub IS NULL THEN ptb.orderid END) as ordersPicked
        FROM picking_ticket pt
        LEFT JOIN picking_ticket_buckets ptb ON pt.pickingid = ptb.pickingid
        WHERE ${pickingWhere}
          AND pt.closeddate IS NOT NULL
        GROUP BY pt.createdby
      `;

      const [[pickingStatsRows], [orderCountRows]] = await Promise.all([
        connection.execute(pickingStatsQuery, params),
        connection.execute(orderCountQuery, params)
      ]);

      // Merge the results
      const orderCountMap = new Map();
      orderCountRows.forEach(row => {
        orderCountMap.set(row.employeeId, parseInt(row.ordersPicked || 0));
      });

      // Aggregate picking totals
      let totalOrdersPicked = 0;
      let totalPiecesPicked = 0;
      let totalTickets = 0;
      let totalPickingTimeSeconds = 0;
      let pickingSpeedSum = 0;
      let pickingSpeedCount = 0;

      const pickingByEmployee = pickingStatsRows.map(row => {
        const ordersPicked = orderCountMap.get(row.employeeId) || 0;
        totalOrdersPicked += ordersPicked;
        totalPiecesPicked += parseInt(row.piecesPicked || 0);
        totalTickets += parseInt(row.ticketCount || 0);
        totalPickingTimeSeconds += parseInt(row.pickingTimeSeconds || 0);
        if (row.avgPickingSpeed && row.avgPickingSpeed > 0) {
          pickingSpeedSum += parseFloat(row.avgPickingSpeed);
          pickingSpeedCount++;
        }

        const empPickingHours = parseInt(row.pickingTimeSeconds || 0) / 3600;

        return {
          employeeId: row.employeeId,
          name: `${row.firstname || ''} ${row.lastname || ''}`.trim() || `Employee ${row.employeeId}`,
          ticketCount: parseInt(row.ticketCount || 0),
          ordersPicked,
          piecesPicked: parseInt(row.piecesPicked || 0),
          pickingHours: empPickingHours,
          avgPickingSpeed: row.avgPickingSpeed ? parseFloat(row.avgPickingSpeed) : null,
        };
      });

      const totalPickingHours = totalPickingTimeSeconds / 3600;
      const avgPickingSpeed = pickingSpeedCount > 0 ? pickingSpeedSum / pickingSpeedCount : 0;

      // Query for shipped orders - totals
      // Ship-together orders: only count main orders (order_type != 8 for sub-orders)
      const shippingWhere = whereClause.replace(/date_placed/g, 'o.date_shipped');

      const shippingQuery = `
        SELECT
          COUNT(DISTINCT CASE WHEN o.order_type != 8 OR o.order_type IS NULL THEN o.order_id END) as ordersShipped,
          COALESCE(SUM(o.stats_prod_pieces), 0) as piecesShipped
        FROM _order o
        WHERE ${shippingWhere}
          AND o.order_status IN (100, 92)
      `;

      const [shippingRows] = await connection.execute(shippingQuery, params);
      const shipping = shippingRows[0] || { ordersShipped: 0, piecesShipped: 0 };

      // Query for shipped orders by employee
      const shippingByEmployeeQuery = `
        SELECT
          e.employeeid,
          e.firstname,
          e.lastname,
          COUNT(DISTINCT CASE WHEN o.order_type != 8 OR o.order_type IS NULL THEN o.order_id END) as ordersShipped,
          COALESCE(SUM(o.stats_prod_pieces), 0) as piecesShipped
        FROM _order o
        JOIN employees e ON o.stats_cid_shipped = e.cid
        WHERE ${shippingWhere}
          AND o.order_status IN (100, 92)
          AND e.hidden = 0
          AND e.disabled = 0
        GROUP BY e.employeeid, e.firstname, e.lastname
        ORDER BY ordersShipped DESC
      `;

      const [shippingByEmployeeRows] = await connection.execute(shippingByEmployeeQuery, params);
      const shippingByEmployee = shippingByEmployeeRows.map(row => ({
        employeeId: row.employeeid,
        name: `${row.firstname || ''} ${row.lastname || ''}`.trim() || `Employee ${row.employeeid}`,
        ordersShipped: parseInt(row.ordersShipped || 0),
        piecesShipped: parseInt(row.piecesShipped || 0),
      }));

      // Calculate period dates
      let periodStart, periodEnd;
      if (dateRange?.start) {
        periodStart = new Date(dateRange.start);
      } else if (params[0]) {
        periodStart = new Date(params[0]);
      } else {
        periodStart = new Date();
        periodStart.setDate(periodStart.getDate() - 30);
      }

      if (dateRange?.end) {
        periodEnd = new Date(dateRange.end);
      } else if (params[1]) {
        periodEnd = new Date(params[1]);
      } else {
        periodEnd = new Date();
      }

      // Calculate productivity (orders/pieces per picking hour)
      const ordersPerHour = totalPickingHours > 0 ? totalOrdersPicked / totalPickingHours : 0;
      const piecesPerHour = totalPickingHours > 0 ? totalPiecesPicked / totalPickingHours : 0;

      // Get daily trend data for picking
      // Use DATE_FORMAT to get date string in Eastern timezone
      // Business day starts at 1 AM, so subtract 1 hour before taking the date
      const pickingTrendWhere = whereClause.replace(/date_placed/g, 'pt.createddate');
      const pickingTrendQuery = `
        SELECT
          DATE_FORMAT(DATE_SUB(pt.createddate, INTERVAL 1 HOUR), '%Y-%m-%d') as date,
          COUNT(DISTINCT CASE WHEN ptb.is_sub = 0 OR ptb.is_sub IS NULL THEN ptb.orderid END) as ordersPicked,
          COALESCE(SUM(pt.totalpieces_picked), 0) as piecesPicked
        FROM picking_ticket pt
        LEFT JOIN picking_ticket_buckets ptb ON pt.pickingid = ptb.pickingid
        WHERE ${pickingTrendWhere}
          AND pt.closeddate IS NOT NULL
        GROUP BY DATE_FORMAT(DATE_SUB(pt.createddate, INTERVAL 1 HOUR), '%Y-%m-%d')
        ORDER BY date
      `;

      // Get shipping trend data
      const shippingTrendWhere = whereClause.replace(/date_placed/g, 'o.date_shipped');
      const shippingTrendQuery = `
        SELECT
          DATE_FORMAT(DATE_SUB(o.date_shipped, INTERVAL 1 HOUR), '%Y-%m-%d') as date,
          COUNT(DISTINCT CASE WHEN o.order_type != 8 OR o.order_type IS NULL THEN o.order_id END) as ordersShipped,
          COALESCE(SUM(o.stats_prod_pieces), 0) as piecesShipped
        FROM _order o
        WHERE ${shippingTrendWhere}
          AND o.order_status IN (100, 92)
        GROUP BY DATE_FORMAT(DATE_SUB(o.date_shipped, INTERVAL 1 HOUR), '%Y-%m-%d')
        ORDER BY date
      `;

      const [[pickingTrendRows], [shippingTrendRows]] = await Promise.all([
        connection.execute(pickingTrendQuery, params),
        connection.execute(shippingTrendQuery, params),
      ]);

      // Create maps for trend data
      const pickingByDate = new Map();
      pickingTrendRows.forEach(row => {
        const date = String(row.date);
        pickingByDate.set(date, {
          ordersPicked: parseInt(row.ordersPicked || 0),
          piecesPicked: parseInt(row.piecesPicked || 0),
        });
      });

      const shippingByDate = new Map();
      shippingTrendRows.forEach(row => {
        const date = String(row.date);
        shippingByDate.set(date, {
          ordersShipped: parseInt(row.ordersShipped || 0),
          piecesShipped: parseInt(row.piecesShipped || 0),
        });
      });

      // Generate all dates in the period range for complete trend data
      const allDatesInRange = [];
      const startDt = DateTime.fromJSDate(periodStart).setZone(TIMEZONE).startOf('day');
      const endDt = DateTime.fromJSDate(periodEnd).setZone(TIMEZONE).startOf('day');

      let currentDt = startDt;
      while (currentDt <= endDt) {
        allDatesInRange.push(currentDt.toFormat('yyyy-MM-dd'));
        currentDt = currentDt.plus({ days: 1 });
      }

      // Build trend data for all dates in range
      const trend = allDatesInRange.map(date => {
        const picking = pickingByDate.get(date) || { ordersPicked: 0, piecesPicked: 0 };
        const shippingData = shippingByDate.get(date) || { ordersShipped: 0, piecesShipped: 0 };

        // Parse date string in Eastern timezone to get proper ISO timestamp
        const dateDt = DateTime.fromFormat(date, 'yyyy-MM-dd', { zone: TIMEZONE });

        return {
          date,
          timestamp: dateDt.toISO(),
          ordersPicked: picking.ordersPicked,
          piecesPicked: picking.piecesPicked,
          ordersShipped: shippingData.ordersShipped,
          piecesShipped: shippingData.piecesShipped,
        };
      });

      // Get previous period data for comparison
      const previousRange = getPreviousPeriodRange(timeRange, startDate, endDate);
      let comparison = null;
      let previousTotals = null;

      if (previousRange) {
        // Previous picking data
        const prevPickingWhere = previousRange.whereClause.replace(/date_placed/g, 'pt.createddate');

        const [[prevPickingStatsRows], [prevOrderCountRows]] = await Promise.all([
          connection.execute(
            `SELECT
               SUM(pt.totalpieces_picked) as piecesPicked,
               SUM(TIMESTAMPDIFF(SECOND, pt.createddate, pt.closeddate)) as pickingTimeSeconds
             FROM picking_ticket pt
             WHERE ${prevPickingWhere}
               AND pt.closeddate IS NOT NULL`,
            previousRange.params
          ),
          connection.execute(
            `SELECT
               COUNT(DISTINCT CASE WHEN ptb.is_sub = 0 OR ptb.is_sub IS NULL THEN ptb.orderid END) as ordersPicked
             FROM picking_ticket pt
             LEFT JOIN picking_ticket_buckets ptb ON pt.pickingid = ptb.pickingid
             WHERE ${prevPickingWhere}
               AND pt.closeddate IS NOT NULL`,
            previousRange.params
          )
        ]);

        const prevPickingStats = prevPickingStatsRows[0] || { piecesPicked: 0, pickingTimeSeconds: 0 };
        const prevOrderCount = prevOrderCountRows[0] || { ordersPicked: 0 };
        const prevPicking = {
          ordersPicked: parseInt(prevOrderCount.ordersPicked || 0),
          piecesPicked: parseInt(prevPickingStats.piecesPicked || 0),
          pickingTimeSeconds: parseInt(prevPickingStats.pickingTimeSeconds || 0)
        };
        const prevPickingHours = prevPicking.pickingTimeSeconds / 3600;

        // Previous shipping data
        const prevShippingWhere = previousRange.whereClause.replace(/date_placed/g, 'o.date_shipped');
        const [prevShippingRows] = await connection.execute(
          `SELECT
             COUNT(DISTINCT CASE WHEN o.order_type != 8 OR o.order_type IS NULL THEN o.order_id END) as ordersShipped,
             COALESCE(SUM(o.stats_prod_pieces), 0) as piecesShipped
           FROM _order o
           WHERE ${prevShippingWhere}
             AND o.order_status IN (100, 92)`,
          previousRange.params
        );
        const prevShipping = prevShippingRows[0] || { ordersShipped: 0, piecesShipped: 0 };

        // Calculate previous productivity
        const prevOrdersPerHour = prevPickingHours > 0 ? parseInt(prevPicking.ordersPicked || 0) / prevPickingHours : 0;
        const prevPiecesPerHour = prevPickingHours > 0 ? parseInt(prevPicking.piecesPicked || 0) / prevPickingHours : 0;

        previousTotals = {
          ordersPicked: parseInt(prevPicking.ordersPicked || 0),
          piecesPicked: parseInt(prevPicking.piecesPicked || 0),
          pickingHours: prevPickingHours,
          ordersShipped: parseInt(prevShipping.ordersShipped || 0),
          piecesShipped: parseInt(prevShipping.piecesShipped || 0),
          ordersPerHour: prevOrdersPerHour,
          piecesPerHour: prevPiecesPerHour,
        };

        comparison = {
          ordersPicked: calculateComparison(totalOrdersPicked, parseInt(prevPicking.ordersPicked || 0)),
          piecesPicked: calculateComparison(totalPiecesPicked, parseInt(prevPicking.piecesPicked || 0)),
          ordersShipped: calculateComparison(parseInt(shipping.ordersShipped || 0), parseInt(prevShipping.ordersShipped || 0)),
          piecesShipped: calculateComparison(parseInt(shipping.piecesShipped || 0), parseInt(prevShipping.piecesShipped || 0)),
          ordersPerHour: calculateComparison(ordersPerHour, prevOrdersPerHour),
          piecesPerHour: calculateComparison(piecesPerHour, prevPiecesPerHour),
        };
      }

      const response = {
        dateRange,
        totals: {
          // Picking metrics
          ordersPicked: totalOrdersPicked,
          piecesPicked: totalPiecesPicked,
          ticketCount: totalTickets,
          pickingHours: totalPickingHours,

          // Shipping metrics
          ordersShipped: parseInt(shipping.ordersShipped || 0),
          piecesShipped: parseInt(shipping.piecesShipped || 0),

          // Productivity metrics
          ordersPerHour,
          piecesPerHour,
          avgPickingSpeed,
        },
        previousTotals,
        comparison,
        byEmployee: {
          picking: pickingByEmployee,
          shipping: shippingByEmployee,
        },
        trend,
      };

      return { response, release };
    };

    let result;
    try {
      result = await Promise.race([mainOperation(), timeoutPromise]);
    } catch (error) {
      if (error.message.includes('timeout')) {
        console.log(`[OPERATIONS-METRICS] Request timed out in ${Date.now() - startTime}ms`);
        throw error;
      }
      throw error;
    }

    const { response, release } = result;

    if (release) release();

    console.log(`[OPERATIONS-METRICS] Request completed in ${Date.now() - startTime}ms`);
    res.json(response);

  } catch (error) {
    console.error('Error in /operations-metrics:', error);
    console.log(`[OPERATIONS-METRICS] Request failed in ${Date.now() - startTime}ms`);
    res.status(500).json({ error: error.message });
  }
});

// Health check
router.get('/health', async (req, res) => {
  try {
    const { connection, release } = await getDbConnection();
    await connection.execute('SELECT 1 as test');
    release();

    res.json({
      status: 'healthy',
      timestamp: new Date().toISOString(),
      pool: getPoolStatus(),
    });
  } catch (error) {
    res.status(500).json({
      status: 'unhealthy',
      timestamp: new Date().toISOString(),
      error: error.message,
    });
  }
});

// Helper functions
function calculateComparison(currentValue, previousValue) {
  if (typeof previousValue !== 'number') {
    return { absolute: null, percentage: null };
  }

  const absolute = typeof currentValue === 'number' ? currentValue - previousValue : null;
  const percentage =
    absolute !== null && previousValue !== 0
      ? (absolute / Math.abs(previousValue)) * 100
      : null;

  return { absolute, percentage };
}

function getPreviousPeriodRange(timeRange, startDate, endDate) {
  if (timeRange && timeRange !== 'custom') {
    const prevTimeRange = getPreviousTimeRange(timeRange);
    if (!prevTimeRange || prevTimeRange === timeRange) {
      return null;
    }
    return getTimeRangeConditions(prevTimeRange);
  }

  const hasCustomDates = (timeRange === 'custom' || !timeRange) && startDate && endDate;
  if (!hasCustomDates) {
    return null;
  }

  const start = new Date(startDate);
  const end = new Date(endDate);

  if (Number.isNaN(start.getTime()) || Number.isNaN(end.getTime())) {
    return null;
  }

  const duration = end.getTime() - start.getTime();
  if (!Number.isFinite(duration) || duration <= 0) {
    return null;
  }

  const prevEnd = new Date(start.getTime() - 1);
  const prevStart = new Date(prevEnd.getTime() - duration);

  return getTimeRangeConditions('custom', prevStart.toISOString(), prevEnd.toISOString());
}

function getPreviousTimeRange(timeRange) {
  const map = {
    today: 'yesterday',
    thisWeek: 'lastWeek',
    thisMonth: 'lastMonth',
    last7days: 'previous7days',
    last30days: 'previous30days',
    last90days: 'previous90days',
    yesterday: 'twoDaysAgo'
  };
  return map[timeRange] || timeRange;
}

module.exports = router;