/**
 * Helper functions for PSQL/Pervasive database operations
 */

/**
 * Formats a date from PSQL YYMMDD format to ISO date string
 * @param psqlDate Date in YYMMDD format (e.g., "250105" for Jan 5, 2025)
 * @returns ISO date string (e.g., "2025-01-05") or null if invalid
 */
export function formatPsqlDate(psqlDate: string): string | null {
  if (!psqlDate || psqlDate.trim().length !== 6 || psqlDate === "000000") {  
    return null;  
  } 

  try {
    const year = parseInt(psqlDate.substring(0, 2), 10);
    const month = parseInt(psqlDate.substring(2, 4), 10);
    const day = parseInt(psqlDate.substring(4, 6), 10);

    // Convert 2-digit year to 4-digit (assuming 2000s)
    const fullYear = year + 2000;

    // Basic validation
    if (month < 1 || month > 12 || day < 1 || day > 31) {
      return null;
    }

    const date = new Date(fullYear, month - 1, day);
    // Validate that the constructed date matches the input values
    if (
      date.getFullYear() !== fullYear ||
      date.getMonth() !== (month - 1) ||
      date.getDate() !== day
    ) {
      return null;
    }
    return date.toISOString().split("T")[0];
  } catch (error) {
    return null;
  }
}

/**
 * Formats a time from PSQL HHMM format to HH:MM:SS
 * @param psqlTime Time in HHMM format (e.g., "1430" for 2:30 PM)
 * @returns Time string in HH:MM:SS format or null if invalid
 */
export function formatPsqlTime(psqlTime: string): string | null {
  if (!psqlTime) {
    return null;
  }
  const trimmed = psqlTime.trim();
  if (trimmed.length !== 4) {
    return null;
  }
  try {
    const hours = parseInt(trimmed.substring(0, 2), 10);
    const minutes = parseInt(trimmed.substring(2, 4), 10);
    if (
      isNaN(hours) ||
      isNaN(minutes) ||
      hours < 0 ||
      hours > 23 ||
      minutes < 0 ||
      minutes > 59
    ) {
      return null;
    }
    // Pad hours and minutes to 2 digits
    const hoursStr = hours.toString().padStart(2, "0");
    const minutesStr = minutes.toString().padStart(2, "0");
    return `${hoursStr}:${minutesStr}:00`;
  } catch (error) {
    return null;
  }
}

/**
 * Combines PSQL date and time into ISO datetime string
 * @param psqlDate Date in YYMMDD format
 * @param psqlTime Time in HHMM format
 * @returns ISO datetime string or null if invalid
 */
export function combinePsqlDateTime(
  psqlDate: string,
  psqlTime: string
): string | null {
  const date = formatPsqlDate(psqlDate);
  const time = formatPsqlTime(psqlTime);

  if (!date || !time) {
    return null;
  }

  return `${date}T${time}`;
}

/**
 * Helper to check if a PSQL date is "empty" (000000 or blank)
 */
export function isPsqlDateEmpty(psqlDate: string): boolean {
  return !psqlDate || psqlDate === "000000" || psqlDate.trim() === "";
}

/**
 * Clean and trim PSQL CHAR field (removes trailing spaces)
 * PSQL CHAR fields are fixed-width and padded with spaces
 */
export function cleanPsqlCharField(value: string | null | undefined): string {
  if (value === null || value === undefined) {
    return "";
  }
  return String(value).trim();
}