// The MySQL server stores/returns naive DATETIME strings (no timezone
// attached) representing wall-clock time in this zone. Hardcoded here and
// used via native Intl APIs rather than relying on the Node process's own
// TZ environment variable — this makes date handling correct regardless of
// how the process is deployed/restarted, sidestepping an entire class of
// "did the env var actually reload" deployment issues.
const DB_TIMEZONE = 'Europe/Berlin';

const partsFormatter = new Intl.DateTimeFormat('en-US', {
  timeZone: DB_TIMEZONE,
  year: 'numeric',
  month: '2-digit',
  day: '2-digit',
  hour: '2-digit',
  minute: '2-digit',
  second: '2-digit',
  hour12: false,
});

function getParts(date: Date): Record<string, string> {
  const parts: Record<string, string> = {};
  for (const p of partsFormatter.formatToParts(date)) {
    if (p.type !== 'literal') parts[p.type] = p.value;
  }
  if (parts.hour === '24') parts.hour = '00'; // some locales report midnight as 24
  return parts;
}

/**
 * Parses a MySQL DATETIME string (e.g. "2026-08-21 15:31:15"), which
 * represents a wall-clock moment in DB_TIMEZONE, into the correct absolute
 * Date/instant — regardless of the Node process's own configured timezone.
 */
export function parseDbDateTime(dbString: string): Date {
  const naiveUTC = new Date(`${dbString.replace(' ', 'T')}Z`);
  const parts = getParts(naiveUTC);
  const asIfLocal = Date.UTC(
    Number(parts.year),
    Number(parts.month) - 1,
    Number(parts.day),
    Number(parts.hour),
    Number(parts.minute),
    Number(parts.second),
  );
  const offsetMs = naiveUTC.getTime() - asIfLocal;
  return new Date(naiveUTC.getTime() + offsetMs);
}

/**
 * Converts an absolute Date/instant into a MySQL DATETIME string
 * ("YYYY-MM-DD HH:MM:SS") representing that instant's wall-clock time in
 * DB_TIMEZONE — for building query parameters that compare correctly
 * against stored DATETIME columns.
 */
export function formatDbDateTime(date: Date): string {
  const parts = getParts(date);
  return `${parts.year}-${parts.month}-${parts.day} ${parts.hour}:${parts.minute}:${parts.second}`;
}