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

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

const TIMEZONE = 'America/New_York';

// Punch types from the database
const PUNCH_TYPES = {
  OUT: 0,
  IN: 1,
  BREAK_START: 2,
  BREAK_END: 3,
};

// Standard hours for overtime calculation (40 hours per week)
const STANDARD_WEEKLY_HOURS = 40;

// Reference pay period start date (January 25, 2026 is a Sunday, first day of a pay period)
const PAY_PERIOD_REFERENCE = DateTime.fromObject(
  { year: 2026, month: 1, day: 25 },
  { zone: TIMEZONE }
);

/**
 * Calculate the pay period that contains a given date
 * Pay periods are 14 days starting on Sunday
 * @param {DateTime} date - The date to find the pay period for
 * @returns {{ start: DateTime, end: DateTime, week1: { start: DateTime, end: DateTime }, week2: { start: DateTime, end: DateTime } }}
 */
function getPayPeriodForDate(date) {
  const dt = DateTime.isDateTime(date) ? date : DateTime.fromJSDate(date, { zone: TIMEZONE });

  // Calculate days since reference
  const daysSinceReference = Math.floor(dt.diff(PAY_PERIOD_REFERENCE, 'days').days);

  // Find which pay period this falls into (can be negative for dates before reference)
  const payPeriodIndex = Math.floor(daysSinceReference / 14);

  // Calculate the start of this pay period
  const start = PAY_PERIOD_REFERENCE.plus({ days: payPeriodIndex * 14 }).startOf('day');
  const end = start.plus({ days: 13 }).endOf('day');

  // Week 1: Sunday through Saturday
  const week1Start = start;
  const week1End = start.plus({ days: 6 }).endOf('day');

  // Week 2: Sunday through Saturday
  const week2Start = start.plus({ days: 7 }).startOf('day');
  const week2End = end;

  return {
    start,
    end,
    week1: { start: week1Start, end: week1End },
    week2: { start: week2Start, end: week2End },
  };
}

/**
 * Get the current pay period
 */
function getCurrentPayPeriod() {
  return getPayPeriodForDate(DateTime.now().setZone(TIMEZONE));
}

/**
 * Navigate to previous or next pay period
 * @param {DateTime} currentStart - Current pay period start
 * @param {number} offset - Number of pay periods to move (negative for previous)
 */
function navigatePayPeriod(currentStart, offset) {
  const newStart = currentStart.plus({ days: offset * 14 });
  return getPayPeriodForDate(newStart);
}

/**
 * Calculate working hours from timeclock entries, broken down by week
 * @param {Array} punches - Timeclock punch entries
 * @param {Object} payPeriod - Pay period with week boundaries
 */
function calculateHoursByWeek(punches, payPeriod) {
  // Group by employee
  const byEmployee = new Map();

  punches.forEach(punch => {
    if (!byEmployee.has(punch.EmployeeID)) {
      byEmployee.set(punch.EmployeeID, {
        employeeId: punch.EmployeeID,
        firstname: punch.firstname || '',
        lastname: punch.lastname || '',
        punches: [],
      });
    }
    byEmployee.get(punch.EmployeeID).punches.push(punch);
  });

  const employeeResults = [];
  let totalHours = 0;
  let totalBreakHours = 0;
  let totalOvertimeHours = 0;
  let totalRegularHours = 0;
  let week1TotalHours = 0;
  let week1TotalOvertime = 0;
  let week2TotalHours = 0;
  let week2TotalOvertime = 0;

  byEmployee.forEach((employeeData) => {
    // Sort punches by timestamp
    employeeData.punches.sort((a, b) => new Date(a.TimeStamp) - new Date(b.TimeStamp));

    // Calculate hours for each week
    const week1Punches = employeeData.punches.filter(p => {
      const dt = DateTime.fromJSDate(new Date(p.TimeStamp), { zone: TIMEZONE });
      return dt >= payPeriod.week1.start && dt <= payPeriod.week1.end;
    });

    const week2Punches = employeeData.punches.filter(p => {
      const dt = DateTime.fromJSDate(new Date(p.TimeStamp), { zone: TIMEZONE });
      return dt >= payPeriod.week2.start && dt <= payPeriod.week2.end;
    });

    const week1Hours = calculateHoursFromPunches(week1Punches);
    const week2Hours = calculateHoursFromPunches(week2Punches);

    // Calculate overtime per week (anything over 40 hours)
    const week1Overtime = Math.max(0, week1Hours.hours - STANDARD_WEEKLY_HOURS);
    const week2Overtime = Math.max(0, week2Hours.hours - STANDARD_WEEKLY_HOURS);
    const week1Regular = week1Hours.hours - week1Overtime;
    const week2Regular = week2Hours.hours - week2Overtime;

    const employeeTotal = week1Hours.hours + week2Hours.hours;
    const employeeBreaks = week1Hours.breakHours + week2Hours.breakHours;
    const employeeOvertime = week1Overtime + week2Overtime;
    const employeeRegular = employeeTotal - employeeOvertime;

    totalHours += employeeTotal;
    totalBreakHours += employeeBreaks;
    totalOvertimeHours += employeeOvertime;
    totalRegularHours += employeeRegular;
    week1TotalHours += week1Hours.hours;
    week1TotalOvertime += week1Overtime;
    week2TotalHours += week2Hours.hours;
    week2TotalOvertime += week2Overtime;

    employeeResults.push({
      employeeId: employeeData.employeeId,
      name: `${employeeData.firstname} ${employeeData.lastname}`.trim() || `Employee ${employeeData.employeeId}`,
      week1Hours: week1Hours.hours,
      week1BreakHours: week1Hours.breakHours,
      week1Overtime,
      week1Regular,
      week2Hours: week2Hours.hours,
      week2BreakHours: week2Hours.breakHours,
      week2Overtime,
      week2Regular,
      totalHours: employeeTotal,
      totalBreakHours: employeeBreaks,
      overtimeHours: employeeOvertime,
      regularHours: employeeRegular,
    });
  });

  // Sort by total hours descending
  employeeResults.sort((a, b) => b.totalHours - a.totalHours);

  return {
    byEmployee: employeeResults,
    totals: {
      hours: totalHours,
      breakHours: totalBreakHours,
      overtimeHours: totalOvertimeHours,
      regularHours: totalRegularHours,
      activeEmployees: employeeResults.filter(e => e.totalHours > 0).length,
    },
    byWeek: [
      {
        week: 1,
        start: payPeriod.week1.start.toISODate(),
        end: payPeriod.week1.end.toISODate(),
        hours: week1TotalHours,
        overtime: week1TotalOvertime,
        regular: week1TotalHours - week1TotalOvertime,
      },
      {
        week: 2,
        start: payPeriod.week2.start.toISODate(),
        end: payPeriod.week2.end.toISODate(),
        hours: week2TotalHours,
        overtime: week2TotalOvertime,
        regular: week2TotalHours - week2TotalOvertime,
      },
    ],
  };
}

/**
 * Calculate hours from a set of punches
 */
function calculateHoursFromPunches(punches) {
  let hours = 0;
  let breakHours = 0;
  let currentIn = null;
  let breakStart = null;

  punches.forEach(punch => {
    const punchTime = new Date(punch.TimeStamp);

    switch (punch.PunchType) {
      case PUNCH_TYPES.IN:
        currentIn = punchTime;
        break;
      case PUNCH_TYPES.OUT:
        if (currentIn) {
          hours += (punchTime - currentIn) / (1000 * 60 * 60);
          currentIn = null;
        }
        break;
      case PUNCH_TYPES.BREAK_START:
        breakStart = punchTime;
        break;
      case PUNCH_TYPES.BREAK_END:
        if (breakStart) {
          breakHours += (punchTime - breakStart) / (1000 * 60 * 60);
          breakStart = null;
        }
        break;
    }
  });

  return { hours, breakHours };
}

/**
 * Calculate FTE for a pay period (based on 80 hours = 1 FTE for 2-week period)
 */
function calculateFTE(totalHours) {
  const fullTimePeriodHours = STANDARD_WEEKLY_HOURS * 2; // 80 hours for 2 weeks
  return totalHours / fullTimePeriodHours;
}

// Main payroll metrics endpoint
router.get('/', async (req, res) => {
  const startTime = Date.now();
  console.log(`[PAYROLL-METRICS] Starting request`);

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

  try {
    const mainOperation = async () => {
      const { payPeriodStart, navigate } = req.query;

      let payPeriod;

      if (payPeriodStart) {
        // Parse the provided start date
        const startDate = DateTime.fromISO(payPeriodStart, { zone: TIMEZONE });
        if (!startDate.isValid) {
          return res.status(400).json({ error: 'Invalid payPeriodStart date format' });
        }
        payPeriod = getPayPeriodForDate(startDate);
      } else {
        // Default to current pay period
        payPeriod = getCurrentPayPeriod();
      }

      // Handle navigation if requested
      if (navigate) {
        const offset = parseInt(navigate, 10);
        if (!isNaN(offset)) {
          payPeriod = navigatePayPeriod(payPeriod.start, offset);
        }
      }

      console.log(`[PAYROLL-METRICS] Getting DB connection...`);
      const { connection, release } = await getDbConnection();
      console.log(`[PAYROLL-METRICS] DB connection obtained in ${Date.now() - startTime}ms`);

      // Build query for the pay period
      const periodStart = payPeriod.start.toJSDate();
      const periodEnd = payPeriod.end.toJSDate();

      const timeclockQuery = `
        SELECT
          tc.EmployeeID,
          tc.TimeStamp,
          tc.PunchType,
          e.firstname,
          e.lastname
        FROM timeclock tc
        LEFT JOIN employees e ON tc.EmployeeID = e.employeeid
        WHERE tc.TimeStamp >= ? AND tc.TimeStamp <= ?
          AND e.hidden = 0
          AND e.disabled = 0
        ORDER BY tc.EmployeeID, tc.TimeStamp
      `;

      const [timeclockRows] = await connection.execute(timeclockQuery, [periodStart, periodEnd]);

      // Calculate hours with week breakdown
      const hoursData = calculateHoursByWeek(timeclockRows, payPeriod);

      // Calculate FTE
      const fte = calculateFTE(hoursData.totals.hours);
      const activeEmployees = hoursData.totals.activeEmployees;
      const avgHoursPerEmployee = activeEmployees > 0 ? hoursData.totals.hours / activeEmployees : 0;

      // Get previous pay period data for comparison
      const prevPayPeriod = navigatePayPeriod(payPeriod.start, -1);
      const [prevTimeclockRows] = await connection.execute(timeclockQuery, [
        prevPayPeriod.start.toJSDate(),
        prevPayPeriod.end.toJSDate(),
      ]);

      const prevHoursData = calculateHoursByWeek(prevTimeclockRows, prevPayPeriod);
      const prevFte = calculateFTE(prevHoursData.totals.hours);

      // Calculate comparisons
      const comparison = {
        hours: calculateComparison(hoursData.totals.hours, prevHoursData.totals.hours),
        overtimeHours: calculateComparison(hoursData.totals.overtimeHours, prevHoursData.totals.overtimeHours),
        fte: calculateComparison(fte, prevFte),
        activeEmployees: calculateComparison(hoursData.totals.activeEmployees, prevHoursData.totals.activeEmployees),
      };

      const response = {
        payPeriod: {
          start: payPeriod.start.toISODate(),
          end: payPeriod.end.toISODate(),
          label: formatPayPeriodLabel(payPeriod),
          week1: {
            start: payPeriod.week1.start.toISODate(),
            end: payPeriod.week1.end.toISODate(),
            label: formatWeekLabel(payPeriod.week1),
          },
          week2: {
            start: payPeriod.week2.start.toISODate(),
            end: payPeriod.week2.end.toISODate(),
            label: formatWeekLabel(payPeriod.week2),
          },
          isCurrent: isCurrentPayPeriod(payPeriod),
        },
        totals: {
          hours: hoursData.totals.hours,
          breakHours: hoursData.totals.breakHours,
          overtimeHours: hoursData.totals.overtimeHours,
          regularHours: hoursData.totals.regularHours,
          activeEmployees,
          fte,
          avgHoursPerEmployee,
        },
        previousTotals: {
          hours: prevHoursData.totals.hours,
          overtimeHours: prevHoursData.totals.overtimeHours,
          activeEmployees: prevHoursData.totals.activeEmployees,
          fte: prevFte,
        },
        comparison,
        byEmployee: hoursData.byEmployee,
        byWeek: hoursData.byWeek,
      };

      return { response, release };
    };

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

    const { response, release } = result;

    if (release) release();

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

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

// Get pay period info endpoint (for navigation without full data)
router.get('/period-info', async (req, res) => {
  try {
    const { payPeriodStart, navigate } = req.query;

    let payPeriod;

    if (payPeriodStart) {
      const startDate = DateTime.fromISO(payPeriodStart, { zone: TIMEZONE });
      if (!startDate.isValid) {
        return res.status(400).json({ error: 'Invalid payPeriodStart date format' });
      }
      payPeriod = getPayPeriodForDate(startDate);
    } else {
      payPeriod = getCurrentPayPeriod();
    }

    if (navigate) {
      const offset = parseInt(navigate, 10);
      if (!isNaN(offset)) {
        payPeriod = navigatePayPeriod(payPeriod.start, offset);
      }
    }

    res.json({
      payPeriod: {
        start: payPeriod.start.toISODate(),
        end: payPeriod.end.toISODate(),
        label: formatPayPeriodLabel(payPeriod),
        week1: {
          start: payPeriod.week1.start.toISODate(),
          end: payPeriod.week1.end.toISODate(),
          label: formatWeekLabel(payPeriod.week1),
        },
        week2: {
          start: payPeriod.week2.start.toISODate(),
          end: payPeriod.week2.end.toISODate(),
          label: formatWeekLabel(payPeriod.week2),
        },
        isCurrent: isCurrentPayPeriod(payPeriod),
      },
    });
  } catch (error) {
    console.error('Error in /payroll-metrics/period-info:', error);
    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 formatPayPeriodLabel(payPeriod) {
  const startStr = payPeriod.start.toFormat('MMM d');
  const endStr = payPeriod.end.toFormat('MMM d, yyyy');
  return `${startStr} – ${endStr}`;
}

function formatWeekLabel(week) {
  const startStr = week.start.toFormat('MMM d');
  const endStr = week.end.toFormat('MMM d');
  return `${startStr} – ${endStr}`;
}

function isCurrentPayPeriod(payPeriod) {
  const now = DateTime.now().setZone(TIMEZONE);
  return now >= payPeriod.start && now <= payPeriod.end;
}

module.exports = router;