/**
 * Convert any date-like value to MariaDB DATETIME (`YYYY-MM-DD HH:MM:SS` UTC).
 * Never returns an ISO string with `T` / `Z` / milliseconds.
 */
export function toMysqlDateTime(value: string | Date | null | undefined): string | null {
  if (value == null || value === "") return null;

  if (value instanceof Date) {
    if (Number.isNaN(value.getTime())) return null;
    return formatUtcMysql(value);
  }

  const raw = String(value).trim();
  if (!raw) return null;

  // Already MySQL / MariaDB DATETIME
  if (/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/.test(raw)) return raw;

  // ISO-8601 variants: 2026-07-14T11:24:07.403Z, +00:00, space-separated with millis
  const normalized = raw
    .replace(/T/i, " ")
    .replace(/Z$/i, "")
    .replace(/([+-]\d{2}:?\d{2})$/, "")
    .replace(/\.\d+/, "")
    .trim();

  if (/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/.test(normalized)) {
    return normalized;
  }

  const d = new Date(raw);
  if (Number.isNaN(d.getTime())) return null;
  return formatUtcMysql(d);
}

function formatUtcMysql(d: Date): string {
  const yyyy = d.getUTCFullYear();
  const mm = String(d.getUTCMonth() + 1).padStart(2, "0");
  const dd = String(d.getUTCDate()).padStart(2, "0");
  const hh = String(d.getUTCHours()).padStart(2, "0");
  const mi = String(d.getUTCMinutes()).padStart(2, "0");
  const ss = String(d.getUTCSeconds()).padStart(2, "0");
  return `${yyyy}-${mm}-${dd} ${hh}:${mi}:${ss}`;
}

/** Convert MariaDB DATETIME / Date into an ISO string for the app API. */
export function fromMysqlDateTime(
  value: string | Date | null | undefined,
  fallback?: string | null,
): string | null {
  if (value == null || value === "") return fallback ?? null;
  const d = value instanceof Date ? value : new Date(value);
  if (Number.isNaN(d.getTime())) return fallback ?? null;
  return d.toISOString();
}