inventory/inventory-server/dashboard/acot-server/utils/timeUtils.js

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

const TIMEZONE = 'America/New_York';
const DB_TIMEZONE = 'UTC-05:00';
const BUSINESS_DAY_START_HOUR = 1; // 1 AM Eastern
const WEEK_START_DAY = 7; // Sunday (Luxon uses 1 = Monday, 7 = Sunday)
const DB_DATETIME_FORMAT = 'yyyy-LL-dd HH:mm:ss';

const isDateTime = (value) => DateTime.isDateTime(value);

const ensureDateTime = (value, { zone = TIMEZONE } = {}) => {
  if (!value) return null;

  if (isDateTime(value)) {
    return value.setZone(zone);
  }

  if (value instanceof Date) {
    return DateTime.fromJSDate(value, { zone });
  }

  if (typeof value === 'number') {
    return DateTime.fromMillis(value, { zone });
  }

  if (typeof value === 'string') {
    let dt = DateTime.fromISO(value, { zone, setZone: true });
    if (!dt.isValid) {
      dt = DateTime.fromSQL(value, { zone });
    }
    return dt.isValid ? dt : null;
  }

  return null;
};

const getNow = () => DateTime.now().setZone(TIMEZONE);

const getDayStart = (input = getNow()) => {
  const dt = ensureDateTime(input);
  if (!dt || !dt.isValid) {
    const fallback = getNow();
    return fallback.set({
      hour: BUSINESS_DAY_START_HOUR,
      minute: 0,
      second: 0,
      millisecond: 0
    });
  }

  const sameDayStart = dt.set({
    hour: BUSINESS_DAY_START_HOUR,
    minute: 0,
    second: 0,
    millisecond: 0
  });

  return dt.hour < BUSINESS_DAY_START_HOUR
    ? sameDayStart.minus({ days: 1 })
    : sameDayStart;
};

const getDayEnd = (input = getNow()) => {
  return getDayStart(input).plus({ days: 1 }).minus({ milliseconds: 1 });
};

const getWeekStart = (input = getNow()) => {
  const dt = ensureDateTime(input);
  if (!dt || !dt.isValid) {
    return getDayStart();
  }

  const startOfWeek = dt.set({ weekday: WEEK_START_DAY }).startOf('day');
  const normalized = startOfWeek > dt ? startOfWeek.minus({ weeks: 1 }) : startOfWeek;
  return normalized.set({
    hour: BUSINESS_DAY_START_HOUR,
    minute: 0,
    second: 0,
    millisecond: 0
  });
};

const getRangeForTimeRange = (timeRange = 'today', now = getNow()) => {
  const current = ensureDateTime(now);
  if (!current || !current.isValid) {
    throw new Error('Invalid reference time for range calculation');
  }

  switch (timeRange) {
    case 'today': {
      return {
        start: getDayStart(current),
        end: getDayEnd(current)
      };
    }
    case 'yesterday': {
      const target = current.minus({ days: 1 });
      return {
        start: getDayStart(target),
        end: getDayEnd(target)
      };
    }
    case 'twoDaysAgo': {
      const target = current.minus({ days: 2 });
      return {
        start: getDayStart(target),
        end: getDayEnd(target)
      };
    }
    case 'thisWeek': {
      return {
        start: getWeekStart(current),
        end: getDayEnd(current)
      };
    }
    case 'lastWeek': {
      const lastWeek = current.minus({ weeks: 1 });
      const weekStart = getWeekStart(lastWeek);
      const weekEnd = weekStart.plus({ days: 6 });
      return {
        start: weekStart,
        end: getDayEnd(weekEnd)
      };
    }
    case 'thisMonth': {
      const dayStart = getDayStart(current);
      const monthStart = dayStart.startOf('month').set({ hour: BUSINESS_DAY_START_HOUR });
      return {
        start: monthStart,
        end: getDayEnd(current)
      };
    }
    case 'lastMonth': {
      const lastMonth = current.minus({ months: 1 });
      const monthStart = lastMonth
        .startOf('month')
        .set({ hour: BUSINESS_DAY_START_HOUR, minute: 0, second: 0, millisecond: 0 });
      const monthEnd = monthStart.plus({ months: 1 }).minus({ days: 1 });
      return {
        start: monthStart,
        end: getDayEnd(monthEnd)
      };
    }
    case 'last7days': {
      const dayStart = getDayStart(current);
      return {
        start: dayStart.minus({ days: 6 }),
        end: getDayEnd(current)
      };
    }
    case 'last30days': {
      const dayStart = getDayStart(current);
      return {
        start: dayStart.minus({ days: 29 }),
        end: getDayEnd(current)
      };
    }
    case 'last90days': {
      const dayStart = getDayStart(current);
      return {
        start: dayStart.minus({ days: 89 }),
        end: getDayEnd(current)
      };
    }
    case 'previous7days': {
      const currentPeriodStart = getDayStart(current).minus({ days: 6 });
      const previousEndDay = currentPeriodStart.minus({ days: 1 });
      const previousStartDay = previousEndDay.minus({ days: 6 });
      return {
        start: getDayStart(previousStartDay),
        end: getDayEnd(previousEndDay)
      };
    }
    case 'previous30days': {
      const currentPeriodStart = getDayStart(current).minus({ days: 29 });
      const previousEndDay = currentPeriodStart.minus({ days: 1 });
      const previousStartDay = previousEndDay.minus({ days: 29 });
      return {
        start: getDayStart(previousStartDay),
        end: getDayEnd(previousEndDay)
      };
    }
    case 'previous90days': {
      const currentPeriodStart = getDayStart(current).minus({ days: 89 });
      const previousEndDay = currentPeriodStart.minus({ days: 1 });
      const previousStartDay = previousEndDay.minus({ days: 89 });
      return {
        start: getDayStart(previousStartDay),
        end: getDayEnd(previousEndDay)
      };
    }
    default:
      throw new Error(`Unknown time range: ${timeRange}`);
  }
};

const toDatabaseSqlString = (dt) => {
  const normalized = ensureDateTime(dt);
  if (!normalized || !normalized.isValid) {
    throw new Error('Invalid datetime provided for SQL conversion');
  }
  const dbTime = normalized.setZone(DB_TIMEZONE, { keepLocalTime: true });
  return dbTime.toFormat(DB_DATETIME_FORMAT);
};

const formatBusinessDate = (input) => {
  const dt = ensureDateTime(input);
  if (!dt || !dt.isValid) return '';
  return dt.setZone(TIMEZONE).toFormat('LLL d, yyyy');
};

const getTimeRangeLabel = (timeRange) => {
  const labels = {
    today: 'Today',
    yesterday: 'Yesterday',
    twoDaysAgo: 'Two Days Ago',
    thisWeek: 'This Week',
    lastWeek: 'Last Week',
    thisMonth: 'This Month',
    lastMonth: 'Last Month',
    last7days: 'Last 7 Days',
    last30days: 'Last 30 Days',
    last90days: 'Last 90 Days',
    previous7days: 'Previous 7 Days',
    previous30days: 'Previous 30 Days',
    previous90days: 'Previous 90 Days'
  };

  return labels[timeRange] || timeRange;
};

const getTimeRangeConditions = (timeRange, startDate, endDate) => {
  if (timeRange === 'custom' && startDate && endDate) {
    const start = ensureDateTime(startDate);
    const end = ensureDateTime(endDate);

    if (!start || !start.isValid || !end || !end.isValid) {
      throw new Error('Invalid custom date range provided');
    }

    return {
      whereClause: 'date_placed >= ? AND date_placed <= ?',
      params: [toDatabaseSqlString(start), toDatabaseSqlString(end)],
      dateRange: {
        start: start.toUTC().toISO(),
        end: end.toUTC().toISO(),
        label: `${formatBusinessDate(start)} - ${formatBusinessDate(end)}`
      }
    };
  }

  const normalizedRange = timeRange || 'today';
  const range = getRangeForTimeRange(normalizedRange);

  return {
    whereClause: 'date_placed >= ? AND date_placed <= ?',
    params: [toDatabaseSqlString(range.start), toDatabaseSqlString(range.end)],
    dateRange: {
      start: range.start.toUTC().toISO(),
      end: range.end.toUTC().toISO(),
      label: getTimeRangeLabel(normalizedRange)
    }
  };
};

const getBusinessDayBounds = (timeRange) => {
  const range = getRangeForTimeRange(timeRange);
  return {
    start: range.start.toJSDate(),
    end: range.end.toJSDate()
  };
};

const parseBusinessDate = (mysqlDatetime) => {
  if (!mysqlDatetime || mysqlDatetime === '0000-00-00 00:00:00') {
    return null;
  }

  const dt = DateTime.fromSQL(mysqlDatetime, { zone: DB_TIMEZONE });
  if (!dt.isValid) {
    console.error('[timeUtils] Failed to parse MySQL datetime:', mysqlDatetime, dt.invalidExplanation);
    return null;
  }

  return dt.toUTC().toJSDate();
};

const formatMySQLDate = (input) => {
  if (!input) return null;

  const dt = ensureDateTime(input, { zone: 'utc' });
  if (!dt || !dt.isValid) return null;

  return dt.setZone(DB_TIMEZONE).toFormat(DB_DATETIME_FORMAT);
};

module.exports = {
  getBusinessDayBounds,
  getTimeRangeConditions,
  formatBusinessDate,
  getTimeRangeLabel,
  parseBusinessDate,
  formatMySQLDate,
  // Expose helpers for tests or advanced consumers
  _internal: {
    getDayStart,
    getDayEnd,
    getWeekStart,
    getRangeForTimeRange,
    BUSINESS_DAY_START_HOUR
  }
};