Function bodies 451 total

export async function updateCredentialCounter(
  credentialId: string,
  newCounter: number
): Promise<void> {
  await updateOne(
    "webauthn_credentials",
    { credential_id: credentialId },
    {
      counter: newCounter,
      last_used_on: new Date().toISOString(),
    }
  );
}

export async function saveChallenge(
  challenge: string,
  purpose: "registration" | "authentication",
  memberId?: number
): Promise<void> {
  const expiresOn = new Date(Date.now() + 5 * 60 * 1000); // 5 minutes

  await insertOne("webauthn_challenges", {
    challenge,
    member_id: memberId || null,
    purpose,
    expires_on: expiresOn.toISOString(),
  });
}

export async function getChallenge(challenge: string): Promise<WebAuthnChallenge | null> {
  const rows = await query<WebAuthnChallenge>(
    'SELECT * FROM webauthn_challenges WHERE challenge = ? AND expires_on > datetime("now")',
    [challenge]
  );

  const challengeData = rows[0] || null;

  // Delete challenge after retrieval (single-use)
  if (challengeData) {
    await deleteOne("webauthn_challenges", { challenge });
  }

  return challengeData;
}

export async function deleteExpiredChallenges(): Promise<number> {
  const conn = writeConn;
  const stmt = await conn.prepare(
    'DELETE FROM webauthn_challenges WHERE expires_on <= datetime("now")'
  );

  try {
    const result = await stmt.run();
    return result.changes || 0;
  } finally {
    await stmt.finalize();
  }
}

export function formatCollectionEntry(entry: {
  quantity: number;
  acquired_date: string;
  removed_date?: string | null;
}): string {
  const parts = [`Qty: ${entry.quantity}`];

  if (entry.acquired_date) {
    parts.push(`Acquired: ${new Date(entry.acquired_date).toLocaleDateString()}`);
  }

  if (entry.removed_date) {
    parts.push(`Removed: ${new Date(entry.removed_date).toLocaleDateString()}`);
  }

  return parts.join(" • ");
}

export function canEditCollection(
  entry: { member_id: number },
  viewer: { id: number; is_admin?: boolean } | null
): boolean {
  if (!viewer) return false;
  return entry.member_id === viewer.id || Boolean(viewer.is_admin);
}

export function getVisibilityFilter(
  memberId: number,
  viewerId: number | null
): "all" | "public" {
  return memberId === viewerId ? "all" : "public";
}

export function formBoolean() {
  return z.preprocess((val) => {
    // Handle undefined (field not in form)
    if (val === undefined) return false;
    // If array (hidden + checkbox both sent), take last value
    if (Array.isArray(val)) {
      return val[val.length - 1] === "1";
    }
    // If string
    return val === "1";
  }, z.boolean());
}

export function getBapFormTitle(selectedType: string) {
  switch (selectedType) {
    default:
    case "Fish":
    case "Invert":
      return "Breeder Awards Submission";
    case "Plant":
      return "Horticultural Awards Submission";
    case "Coral":
      return "Coral Awards Submission";
  }
}

export function validateFormResult<T>(
  parsed: z.SafeParseReturnType<unknown, T>,
  errors: Map<string, string>,
  onError?: () => void
): parsed is z.SafeParseSuccess<T> {
  if (parsed.success) {
    return true;
  }
  parsed.error.issues.forEach((issue) => {
    errors.set(String(issue.path[0]), issue.message);
  });
  onError?.();
  return false;
}

export function extractValid<T extends z.ZodRawShape>(
  schema: z.ZodObject<T>,
  data: unknown
): Partial<z.infer<typeof schema>> {
  const result: Partial<z.infer<typeof schema>> = {};

  for (const key in schema.shape) {
    const subSchema = schema.shape[key];
    const value =
      data && typeof data === "object" && key in data
        ? (data as Record<string, unknown>)[key]
        : undefined;
    const parsed = subSchema.safeParse(value);
    if (parsed.success) {
      // eslint-disable-next-line @typescript-eslint/no-unsafe-assignment
      result[key] = parsed.data;
    }
  }
  return result;
}

export function validateQueryWithFallback<T extends z.ZodRawShape>(
  schema: z.ZodObject<T>,
  query: Record<string, unknown>,
  logContext?: string
): {
  success: boolean;
  data: z.infer<typeof schema>;
  errors: string[];
  isPartial: boolean;
} {

  constructor(
    message: string,
    public serviceName: string,
    public statusCode?: number,
    public response?: unknown
  ) {
    super(message);
    this.name = "IntegrationAPIError";
  }

  protected logInit(): void {
    if (!this.config.enabled) {
      logger.info(`${this.serviceName} integration is disabled`);
    } else {
      logger.info(`${this.serviceName} integration client initialized`);
    }
  }

  protected async enforceRateLimit(): Promise<void> {
    const now = Date.now();
    const timeSinceLastRequest = now - this.lastRequestTime;

    if (timeSinceLastRequest < this.config.rateLimitMs) {
      const waitTime = this.config.rateLimitMs - timeSinceLastRequest;
      logger.info(`${this.serviceName} rate limiting: waiting ${waitTime}ms`);
      await delay(waitTime);
    }

    this.lastRequestTime = Date.now();
  }

  protected async get<T>(
    endpoint: string,
    queryParams?: Record<string, string | number | boolean>,
    retryCount = 0
  ): Promise<T | null> {
    await this.enforceRateLimit();

    // Build URL with query parameters
    let url = `${this.config.baseUrl}${endpoint}`;
    if (queryParams) {
      const params = new URLSearchParams();
      for (const [key, value] of Object.entries(queryParams)) {
        params.append(key, String(value));
      }
      url += `?${params.toString()}`;
    }

    logger.info(`${this.serviceName} API request: ${endpoint}`);

    try {
      const controller = new AbortController();
      const timeout = setTimeout(() => controller.abort(), this.config.timeoutMs);

      const response = await fetch(url, {
        signal: controller.signal,
        headers: {
          Accept: "application/json",
          ...this.config.headers,
        },
      });

      clearTimeout(timeout);

      // Handle different status codes
      if (response.status =

  constructor(clientConfig?: Partial<IntegrationClientConfig>) {
    const defaultConfig = config.fishbase;

    if (!defaultConfig) {
      throw new Error("FishBase configuration not found in config.json");
    }

    // Map config field names to match IntegrationClientConfig
    const integrationConfig: IntegrationClientConfig = {
      baseUrl: defaultConfig.baseUrl,
      rateLimitMs: defaultConfig.rateLimitMs,
      maxRetries: defaultConfig.maxRetries,
      timeoutMs: defaultConfig.timeoutMs,
      enabled: defaultConfig.enableSync, // Map enableSync to enabled
    };

    super({
      ...integrationConfig,
      ...clientConfig,
    });

    this.logInit();
  }

  async getSpecies(genus: string, species: string): Promise<FishBaseSpecies | null> {
    if (!this.isEnabled()) {
      logger.warn("FishBase integration is disabled");
      return null;
    }

    try {
      const response = await this.get<FishBaseSpeciesResponse>("/species", {
        genus,
        species,
      });

      if (!response || response.error) {
        logger.warn(`FishBase API error: ${response?.error || "Unknown error"}`);
        return null;
      }

      if (response.count === 0 || response.data.length === 0) {
        logger.info(`Species not found in FishBase: ${genus} ${species}`);
        return null;
      }

      // Return the first match (should only be one for exact genus/species match)
      return response.data[0];
    } catch (error) {
      logger.error(`Failed to get FishBase species data for ${genus} ${species}`, error);
      return null;
    }
  }

  async getExternalData(genus: string, species: string): Promise<FishBaseResult | null> {
    const speciesData = await this.getSpecies(genus, species);

    if (!speciesData) {
      return null;
    }

    // Construct FishBase URL from SpecCode
    const fishbaseUrl = `https://www.fishbase.se/summary/${speciesData.SpecCode}`;

    // Collect image URLs from various fields
    const imageUrls: string[] = [];

    // Helper function to add image URLs
    const addImageUrl = (filename: string | null) => {
      if (filename) {
        // FishBase images are hosted at fishbase.se
        const imageUrl = `https://www.fishbase.se/images/species/${filename}`;
        if (!imageUrls.includes(imageUrl)) {
          imageUrls.push(imageUrl);
        }
      }
    };

    // Add images in order of preference
    addImageUrl(speciesData.PicPreferredName); // Preferred general image
    addImageUrl(speciesData.PicPreferredNameM); // Preferred male image
    addImageUrl(speciesData.PicPreferredN

  async testConnection(): Promise<boolean> {
    try {
      // Try a simple species query as a connectivity test
      // Using a common, well-known species
      const result = await this.getSpecies("Poecilia", "reticulata");
      return result !== null;
    } catch (error) {
      logger.error("FishBase API connection test failed", error);
      return false;
    }
  }

export function getFishBaseClient(): FishBaseClient {
  if (!clientInstance) {
    if (!config.fishbase?.enableSync) {
      throw new Error("FishBase integration is disabled in configuration");
    }
    clientInstance = new FishBaseClient();
  }
  return clientInstance;
}

  constructor(clientConfig?: Partial<IntegrationClientConfig>) {
    const defaultConfig = config.gbif;

    if (!defaultConfig) {
      throw new Error("GBIF configuration not found in config.json");
    }

    // Map config field names to match IntegrationClientConfig
    const integrationConfig: IntegrationClientConfig = {
      baseUrl: defaultConfig.baseUrl,
      rateLimitMs: defaultConfig.rateLimitMs,
      maxRetries: defaultConfig.maxRetries,
      timeoutMs: defaultConfig.timeoutMs,
      enabled: defaultConfig.enableSync,
    };

    super({
      ...integrationConfig,
      ...clientConfig,
    });

    this.logInit();
  }

  async matchSpecies(genus: string, species: string): Promise<GBIFSpeciesMatch | null> {
    if (!this.isEnabled()) {
      logger.warn("GBIF integration is disabled");
      return null;
    }

    try {
      const scientificName = `${genus} ${species}`;
      const response = await this.get<GBIFSpeciesMatch>("/species/match", {
        name: scientificName,
        verbose: false,
      });

      if (!response) {
        logger.info(`Species not found in GBIF: ${scientificName}`);
        return null;
      }

      // Check if we got a good match
      if (response.matchType === "NONE" || response.confidence < 80) {
        logger.info(
          `Low confidence GBIF match for ${scientificName}: ${response.confidence}% (${response.matchType})`
        );
        return null;
      }

      return response;
    } catch (error) {
      logger.error(`Failed to match species in GBIF for ${genus} ${species}`, error);
      return null;
    }
  }

  async getSpeciesDetail(usageKey: number): Promise<GBIFSpeciesDetail | null> {
    if (!this.isEnabled()) {
      return null;
    }

    try {
      const response = await this.get<GBIFSpeciesDetail>(`/species/${usageKey}`);
      return response;
    } catch (error) {
      logger.error(`Failed to get GBIF species detail for key ${usageKey}`, error);
      return null;
    }
  }

  async getSpeciesMedia(usageKey: number, limit = 20): Promise<GBIFMedia[]> {
    if (!this.isEnabled()) {
      return [];
    }

    try {
      const response = await this.get<GBIFMediaResponse>(`/species/${usageKey}/media`, {
        limit,
      });

      if (!response || !response.results) {
        return [];
      }

      // Filter to only images (StillImage)
      const images = response.results.filter(
        (media) => media.type === "StillImage" && media.identifier
      );

      return images;
    } catch (error) {
      logger.error(`Failed to get GBIF media for key ${usageKey}`, error);
      return [];
    }
  }

  async getExternalData(genus: string, species: string): Promise<GBIFResult | null> {
    const match = await this.matchSpecies(genus, species);

    if (!match) {
      return null;
    }

    // Construct GBIF species page URL
    const gbifUrl = `https://www.gbif.org/species/${match.usageKey}`;

    // Construct occurrence map URL
    // This is a static map image showing where the species has been observed
    const occurrenceMapUrl = `https://api.gbif.org/v2/map/occurrence/density/0/0/[email protected]?taxonKey=${match.usageKey}&bin=hex&hexPerTile=30&style=purpleYellow.point`;

    // Get images
    const media = await this.getSpeciesMedia(match.usageKey, 10);
    const imageUrls = media.map((m) => m.identifier).filter((url) => url && url.length > 0);

    return {
      usageKey: match.usageKey,
      gbifUrl,
      occurrenceMapUrl,
      imageUrls,
      scientificName: match.canonicalName || match.scientificName,
      confidence: match.confidence,
    };
  }

  async testConnection(): Promise<boolean> {
    try {
      // Try to match a common, well-known species
      const result = await this.matchSpecies("Poecilia", "reticulata");
      if (!result) {
        logger.warn("GBIF test query returned no results");
        return false;
      }

      logger.info("GBIF API connection test successful");
      return true;
    } catch (error) {
      logger.error("GBIF API connection test failed", error);
      return false;
    }
  }

export function getGBIFClient(): GBIFClient {
  if (!clientInstance) {
    if (!config.gbif?.enableSync) {
      throw new Error("GBIF integration is disabled in configuration");
    }
    clientInstance = new GBIFClient();
  }
  return clientInstance;
}

  constructor(
    message: string,
    public statusCode?: number,
    public response?: unknown
  ) {
    super(message);
    this.name = "IUCNAPIError";
  }

  constructor(clientConfig?: Partial<IUCNClientConfig>) {
    // Load configuration from config.json and merge with overrides
    const defaultConfig = config.iucn;

    if (!defaultConfig || !defaultConfig.apiToken) {
      throw new Error("IUCN configuration not found in config.json");
    }

    this.config = {
      ...defaultConfig,
      ...clientConfig,
    };

    if (this.config.apiToken === "YOUR_IUCN_API_TOKEN_HERE") {
      throw new Error("IUCN API token not configured. Please add your token to config.json");
    }

    logger.info("IUCN API client initialized");
  }

  private async enforceRateLimit(): Promise<void> {
    const now = Date.now();
    const timeSinceLastRequest = now - this.lastRequestTime;

    if (timeSinceLastRequest < this.config.rateLimitMs) {
      const waitTime = this.config.rateLimitMs - timeSinceLastRequest;
      logger.info(`Rate limiting: waiting ${waitTime}ms`);
      await delay(waitTime);
    }

    this.lastRequestTime = Date.now();
  }

  private async request<T>(
    endpoint: string,
    retryCount = 0
  ): Promise<T | null> {
    await this.enforceRateLimit();

    const url = `${this.config.baseUrl}${endpoint}`;

    logger.info(`IUCN API request: ${endpoint}`);

    try {
      const controller = new AbortController();
      const timeout = setTimeout(() => controller.abort(), this.config.timeoutMs);

      const response = await fetch(url, {
        signal: controller.signal,
        headers: {
          Accept: "application/json",
          Authorization: `Bearer ${this.config.apiToken}`,
        },
      });

      clearTimeout(timeout);

      // Handle different status codes
      if (response.status === 404) {
        logger.info(`IUCN API: Species not found (404)`);
        return null;
      }

      if (response.status === 429) {
        // Rate limited
        if (retryCount < this.config.maxRetries) {
          const backoffMs = this.config.rateLimitMs * Math.pow(2, retryCount);
          logger.warn(`

  async getSpeciesByName(scientificName: string): Promise<IUCNSpeciesResult | null> {
    const parts = scientificName.trim().split(/\s+/);
    if (parts.length < 2) {
      logger.warn(`Invalid scientific name format: ${scientificName}`);
      return null;
    }

    const genus = parts[0];
    const species = parts[1];

    return this.getSpecies(genus, species);
  }

  async getSpecies(genus: string, species: string): Promise<IUCNSpeciesResult | null> {
    const encodedGenus = encodeURIComponent(genus);
    const encodedSpecies = encodeURIComponent(species);
    const response = await this.request<IUCNV4TaxonResponse>(
      `/taxa/scientific_name?genus_name=${encodedGenus}&species_name=${encodedSpecies}`
    );

    if (!response || !response.assessments || response.assessments.length === 0) {
      return null;
    }

    // Get the latest assessment
    const latestAssessment = response.assessments.find((a) => a.latest);
    if (!latestAssessment) {
      return null;
    }

    // Convert v4 response to v3-compatible format for backward compatibility
    return {
      taxonid: response.taxon.sis_id,
      scientific_name: response.taxon.scientific_name,
      kingdom: response.taxon.kingdom_name,
      phylum: response.taxon.phylum_name,
      class: response.taxon.class_name,
      order: response.taxon.order_name,
      family: response.tax

  async checkIfSynonym(
    genus: string,
    species: string
  ): Promise<{
    isSynonym: boolean;
    acceptedName: { genus: string; species: string; taxonId: number; url: string } | null;
  }> {

  async testConnection(): Promise<boolean> {
    try {
      // Try a simple taxa query as a connectivity test
      // Using a common, well-known species that should always be in the database
      await this.getSpecies("Homo", "sapiens");
      return true;
    } catch (error) {
      logger.error("IUCN API connection test failed", error);
      return false;
    }
  }

export function getIUCNClient(): IUCNClient {
  if (!clientInstance) {
    if (!config.iucn?.enableSync) {
      throw new Error("IUCN integration is disabled in configuration");
    }
    clientInstance = new IUCNClient();
  }
  return clientInstance;
}

  constructor(clientConfig?: Partial<IntegrationClientConfig>) {
    const defaultConfig = config.wikipedia;

    if (!defaultConfig) {
      throw new Error("Wikipedia configuration not found in config.json");
    }

    // Map config field names to match IntegrationClientConfig
    const integrationConfig: IntegrationClientConfig = {
      baseUrl: defaultConfig.baseUrl,
      rateLimitMs: defaultConfig.rateLimitMs,
      maxRetries: defaultConfig.maxRetries,
      timeoutMs: defaultConfig.timeoutMs,
      enabled: defaultConfig.enableSync,
    };

    super({
      ...integrationConfig,
      ...clientConfig,
    });

    this.wikidataUrl = defaultConfig.wikidataUrl ?? "https://www.wikidata.org/w/api.php";
    this.logInit();
  }

  async queryWikidata(genus: string, species: string): Promise<WikidataSpeciesResult[]> {
    if (!this.isEnabled()) {
      logger.warn("Wikipedia/Wikidata integration is disabled");
      return [];
    }

    const scientificName = `${genus} ${species}`;

    // SPARQL query to find species by scientific name
    const sparqlQuery = `
SELECT DISTINCT ?item ?itemLabel ?article ?image WHERE {
  # Find items with this exact taxon name
  ?item wdt:P225 "${scientificName}" .

  # Optional: Get English Wikipedia article
  OPTIONAL {
    ?article schema:about ?item ;
             schema:isPartOf <https://en.wikipedia.org/> .
  }

  # Optional: Get image
  OPTIONAL {
    ?item wdt:P18 ?image .
  }

  SERVICE wikibase:label { bd:serviceParam wikibase:language "en". }
}
LIMIT 5
    `.trim();

    try {
      // Wikidata SPARQL endpoint
      const sparqlEndpoint = "https://query.wikidata.org/sparql";

      await this.enforceRateLimit();

      logger.info(`Wikidata SPARQL query for: ${scient

  async getPageSummary(title: string, lang = "en"): Promise<WikipediaPageSummary | null> {
    if (!this.isEnabled()) {
      return null;
    }

    try {
      // Wikipedia REST API endpoint for page summary
      const endpoint = `/page/summary/${encodeURIComponent(title)}`;

      // Override baseUrl for this request to use the correct language
      const originalBaseUrl = this.config.baseUrl;
      this.config.baseUrl = `https://${lang}.wikipedia.org/api/rest_v1`;

      const summary = await this.get<WikipediaPageSummary>(endpoint);

      // Restore original baseUrl
      this.config.baseUrl = originalBaseUrl;

      return summary;
    } catch (error) {
      logger.error(`Failed to get Wikipedia page summary for ${title}`, error);
      return null;
    }
  }

  async getExternalData(genus: string, species: string): Promise<WikipediaResult | null> {
    const wikidataResults = await this.queryWikidata(genus, species);

    if (wikidataResults.length === 0) {
      return null;
    }

    // Use the first result (most relevant)
    const primaryResult = wikidataResults[0];

    // Extract Wikidata ID from URL
    const wikidataUrlMatch = primaryResult.item.value.match(/Q\d+$/);
    if (!wikidataUrlMatch) {
      logger.warn("Could not extract Wikidata ID from URL");
      return null;
    }

    const wikidataId = wikidataUrlMatch[0];
    const wikidataUrl = primaryResult.item.value;

    // Collect Wikipedia article URLs (try multiple languages)
    const wikipediaUrls: Record<string, string> = {};

    // If we have an English article from SPARQL, add it
    if (primaryResult.article) {
      wikipediaUrls.en = primaryResult.article.value;
    }

    // Try to get page summary for more details and images
    const scientificName = `${genus}

  async testConnection(): Promise<boolean> {
    try {
      // Test Wikidata SPARQL
      const wikidataResults = await this.queryWikidata("Poecilia", "reticulata");
      if (wikidataResults.length === 0) {
        logger.warn("Wikidata test query returned no results");
        return false;
      }

      // Test Wikipedia REST API
      const pageSummary = await this.getPageSummary("Poecilia reticulata", "en");
      if (!pageSummary) {
        logger.warn("Wikipedia test query returned no results");
        return false;
      }

      logger.info("Wikipedia/Wikidata API connection test successful");
      return true;
    } catch (error) {
      logger.error("Wikipedia/Wikidata API connection test failed", error);
      return false;
    }
  }

export function getWikipediaClient(): WikipediaClient {
  if (!clientInstance) {
    if (!config.wikipedia?.enableSync) {
      throw new Error("Wikipedia integration is disabled in configuration");
    }
    clientInstance = new WikipediaClient();
  }
  return clientInstance;
}

export async function checkAndUpdateMemberLevel(
  memberId: number,
  program: Program,
  options?: { disableEmails?: boolean }
): Promise<LevelCheckResult> {
  try {
    const member = await getMember(memberId);
    if (!member) {
      throw new Error(`Member ${memberId} not found`);
    }

    // Get all approved submissions for this member and program
    const submissions = await getSubmissionsByMember(
      memberId,
      false, // don't include unsubmitted
      false // don't include unapproved
    );

    // Filter submissions by program and extract points
    const programSubmissions = submissions.filter((sub) => sub.program === program);
    const pointsArray = programSubmissions.map((sub) => sub.total_points || sub.points || 0);

    // Calculate what level they should be
    const calculatedLevel = calculateLevel(levelRules[program], pointsArray);

    // Get current stored level
    const currentLevel = getCurrentLevel(member, program);

    // Check if level has chang

export async function checkAllMemberLevels(
  memberId: number,
  options?: { disableEmails?: boolean }
): Promise<{
  fish?: LevelCheckResult;
  plant?: LevelCheckResult;
  coral?: LevelCheckResult;
}> {

function getCurrentLevel(member: MemberRecord, program: Program): string | undefined {
  switch (program) {
    case "fish":
      return member.fish_level;
    case "plant":
      return member.plant_level;
    case "coral":
      return member.coral_level;
    default:
      return undefined;
  }
}

function shouldSendUpgradeEmail(oldLevel: string | undefined, newLevel: string): boolean {
  // Always send if going from null/undefined to a level
  if (!oldLevel && newLevel) {
    return true;
  }

  // Don't send if both are null/undefined
  if (!oldLevel && !newLevel) {
    return false;
  }

  // For now, send for any level change (could add level hierarchy checking later)
  return oldLevel !== newLevel;
}

function normalizeDateString(input: string | undefined): string | null {
  if (!input || !input.trim()) return null;

  const trimmed = input.trim();

  // ISO date passthrough (YYYY-MM-DD)
  if (/^\d{4}-\d{2}-\d{2}$/.test(trimmed)) {
    return trimmed;
  }

  // MM/DD/YYYY or M/D/YYYY
  const mdyMatch = trimmed.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/);
  if (mdyMatch) {
    const month = mdyMatch[1].padStart(2, "0");
    const day = mdyMatch[2].padStart(2, "0");
    return `${mdyMatch[3]}-${month}-${day}`;
  }

  const lower = trimmed.toLowerCase();

  // "Mid April 2024" → 2024-04-15
  const midMatch = lower.match(/^mid\s+(\w+)\s+(\d{4})$/);
  if (midMatch) {
    const month = MONTH_MAP[midMatch[1]];
    if (month) {
      return `${midMatch[2]}-${String(month).padStart(2, "0")}-15`;
    }
  }

  // "June/July 2024" or "June-July 2024" → midpoint of first month
  const rangeMatch = lower.match(/^(\w+)\s*[-/]\s*(\w+)\s+(\d{4})$/);
  if (rangeMatch) {
    const month = MONTH_MAP[range

function normalizeWaterType(input: string | undefined): string | null {
  if (!input || !input.trim()) return null;
  const lower = input.trim().toLowerCase();
  if (lower === "freshwater" || lower === "fresh") return "Fresh";
  if (lower === "brackish") return "Brackish";
  if (lower === "saltwater" || lower === "salt" || lower === "marine") return "Salt";
  // Return as-is if it's already a valid value
  if (["Fresh", "Brackish", "Salt"].includes(input.trim())) return input.trim();
  return input.trim();
}

function normalizeNullLike(input: string | undefined): string | null {
  if (!input) return null;
  const lower = input.trim().toLowerCase();
  if (
    lower === "" ||
    lower === "n/a" ||
    lower === "na" ||
    lower === "none" ||
    lower === "dont test" ||
    lower === "don't test" ||
    lower === "unknown" ||
    lower === "not tested" ||
    lower === "-" ||
    lower === "?"
  ) {
    return null;
  }
  return input.trim();
}