// Build filter conditions for files in the dataset - files are linked to locations, clusters, and datasets
    let whereConditions = sqlExpr`${file.active} = true`;

    // Build base filter condition - active files with selections of the specified species
    // This forms the core of our WHERE clause for both queries
    let baseCondition = sqlExpr`
      ${file.active} = true 
      AND ${location.datasetId} = ${datasetId}
      AND ${label.speciesId} = ${speciesId} 
      AND ${label.active} = true
    `;

    // Add filters for solarNight if specified

    // Add filters for day/night if specified

      whereConditions = sqlExpr`${whereConditions} AND ${file.maybeSolarNight} = true`;

      baseCondition = sqlExpr`${baseCondition} AND ${file.maybeSolarNight} = true`;

      whereConditions = sqlExpr`${whereConditions} AND (${file.maybeSolarNight} = false OR ${file.maybeSolarNight} IS NULL)`;

      baseCondition = sqlExpr`${baseCondition} AND (${file.maybeSolarNight} = false OR ${file.maybeSolarNight} IS NULL)`;

    // Add filters for civilNight if specified

      whereConditions = sqlExpr`${whereConditions} AND ${file.maybeCivilNight} = true`;

      baseCondition = sqlExpr`${baseCondition} AND ${file.maybeCivilNight} = true`;

      whereConditions = sqlExpr`${whereConditions} AND (${file.maybeCivilNight} = false OR ${file.maybeCivilNight} IS NULL)`;

      baseCondition = sqlExpr`${baseCondition} AND (${file.maybeCivilNight} = false OR ${file.maybeCivilNight} IS NULL)`;

    // Count only files that have at least one selection labeled with the specified species
    // and are in the specified dataset

    // Get total count for pagination using a more efficient COUNT(1)

      .where(sqlExpr`
        ${whereConditions} 
        AND ${location.datasetId} = ${datasetId}
        AND ${label.speciesId} = ${speciesId} 
        AND ${label.active} = true
      `);

      .where(baseCondition);

    // Get only files that have at least one selection labeled with the specified species

    // Early return if there are no matching files
    if (totalFiles === 0) {
      return c.json({
        data: [],
        pagination: {
          currentPage: page,
          pageSize: limitedPageSize,
          totalPages: 0,
          totalItems: 0,
          hasNextPage: false,
          hasPreviousPage: false,
        },
        filters: {
          datasetId,
          speciesId,
          solarNight: solarNight === 'true' ? true : solarNight === 'false' ? false : null,
          civilNight: civilNight === 'true' ? true : civilNight === 'false' ? false : null,
        }
      });
    }
    
    // Fetch files with pagination

      .where(sqlExpr`
        ${whereConditions} 
        AND ${location.datasetId} = ${datasetId}
        AND ${label.speciesId} = ${speciesId} 
        AND ${label.active} = true
      `)

      .where(baseCondition)

    // Get all file IDs to fetch metadata

    // Get all file IDs for metadata queries

    // Fetch metadata, moth metadata, and species data for these files
    let metadataMap: Record<string, unknown> = {};
    let mothMetadataMap: Record<string, {

    // Safety check - shouldn't happen with our early return, but just in case
    if (fileIds.length === 0) {
      return c.json({
        data: [],
        pagination: {
          currentPage: page,
          pageSize: limitedPageSize,
          totalPages: totalPages,
          totalItems: totalFiles,
          hasNextPage: page < totalPages,
          hasPreviousPage: page > 1,
        },
        filters: {
          datasetId,
          speciesId,
          solarNight: solarNight === 'true' ? true : solarNight === 'false' ? false : null,
          civilNight: civilNight === 'true' ? true : civilNight === 'false' ? false : null,
        }
      });
    }
    
    // Convert file IDs to a properly formatted SQL string
    const fileIdsQuoted = fileIds.map(id => `'${id}'`).join(',');
    
    // Execute metadata queries in parallel using Promise.all for better performance
    const [metadataResults, mothMetadataResults, speciesResults] = await Promise.all([
      // Fetch file metadata
      db.select({
        fileId: fileMetadata.fileId,
        json: fileMetadata.json
      })
      .from(fileMetadata)
      .where(sqlExpr`${fileMetadata.fileId} IN (${sqlExpr.raw(fileIdsQuoted)})`),
      
      // Fetch moth metadata
      db.select({
        fileId: mothMetadata.fileId,
        gain: mothMetadata.gain,
        batteryV: mothMetadata.batteryV,
        tempC: mothMetadata.tempC
      })
      .from(mothMetadata)
      .where(sqlExpr`${mothMetadata.fileId} IN (${sqlExpr.raw(fileIdsQuoted)})`),
      
      // Fetch species data - pre-filtered to just get the relevant species
      db.select({
        fileId: selection.fileId,
        speciesId: species.id,
        speciesLabel: species.label,
        ebirdCode: species.ebirdCode,
        description: species.description
      })
      .from(selection)
      .innerJoin(label, eq(label.selectionId, selection.id))
      .innerJoin(species, eq(species.id, label.speciesId))
      .where(sqlExpr`
        ${selection.fileId} IN (${sqlExpr.raw(fileIdsQuoted)}) 
        AND ${label.speciesId} = ${speciesId} 
        AND ${label.active} = true
      `)
    ]);
    
    // Process metadata results into maps for efficient lookups
    const metadataMap = metadataResults.reduce((acc, item) => {
      let processedJson = item.json;
      
      try {
        if (typeof item.json === 'string' && (item.json.startsWith('{') || item.json.startsWith('['))) {
          processedJson = JSON.parse(item.json);
        } else if (typeof item.json === 'string' && item.json.includes('\\"')) {
          const unescaped = item.json.replace(/\\"/g, '"');
          processedJson = JSON.parse(unescaped);
        }
      } catch (e) {
        console.error("Error processing metadata JSON:", e);
        processedJson = item.json;
      }
      
      acc[item.fileId] = processedJson;
      return acc;
    }, {} as Record<string, unknown>);
    
    // Process moth metadata
    const mothMetadataMap = mothMetadataResults.reduce((acc, item) => {
      acc[item.fileId] = {
        gain: item.gain,
        batteryV: item.batteryV !== null ? Number(item.batteryV) : null,
        tempC: item.tempC !== null ? Number(item.tempC) : null
      };
      return acc;
    }, {} as Record<string, {

    }> = {};
    let speciesMap: Record<string, Array<{

    }>);
    
    // Process species data with Map for better performance
    const speciesMap = new Map<string, Array<{

    }>> = {};

    }>>();

    if (fileIds.length > 0) {
      try {
        // Use a simpler approach with a string literal for the IN clause
        const fileIdsQuoted = fileIds.map(id => `'${id}'`).join(',');
        
        // Fetch regular file metadata
        const metadataResults = await db
          .select({
            fileId: fileMetadata.fileId,
            json: fileMetadata.json
          })
          .from(fileMetadata)
          .where(sqlExpr`${fileMetadata.fileId} IN (${sqlExpr.raw(fileIdsQuoted)})`);
          
        // Fetch moth metadata in a separate query
        const mothMetadataResults = await db
          .select({
            fileId: mothMetadata.fileId,
            gain: mothMetadata.gain,
            batteryV: mothMetadata.batteryV,
            tempC: mothMetadata.tempC
          })
          .from(mothMetadata)
          .where(sqlExpr`${mothMetadata.fileId} IN (${sqlExpr.raw(fileIdsQuoted)})`);
          
        // Fetch species data via selections and labels - already filtered by the specified species
        const speciesResults = await db
          .select({
            fileId: selection.fileId,
            speciesId: species.id,
            speciesLabel: species.label,
            ebirdCode: species.ebirdCode,
            description: species.description
          })
          .from(selection)
          .innerJoin(label, eq(label.selectionId, selection.id))
          .innerJoin(species, eq(species.id, label.speciesId))
          .where(sqlExpr`
            ${selection.fileId} IN (${sqlExpr.raw(fileIdsQuoted)}) 
            AND ${label.speciesId} = ${speciesId} 
            AND ${label.active} = true
          `);
        
        // Create a map of file ID to metadata
        // Handle the possibility of metadata already being a string or needing parsing
        metadataMap = metadataResults.reduce((acc, item) => {
          let processedJson = item.json;
          
          try {
            // If it's already a string that looks like a JSON string, try to parse it
            if (typeof item.json === 'string' && 
                (item.json.startsWith('{') || item.json.startsWith('['))) {
              processedJson = JSON.parse(item.json);
            }
            // Some databases might return the json as a string with escaped quotes
            else if (typeof item.json === 'string' && item.json.includes('\\"')) {
              // Handle double-escaped JSON
              const unescaped = item.json.replace(/\\"/g, '"');
              processedJson = JSON.parse(unescaped);
            }
          } catch (e) {
            console.error("Error processing metadata JSON:", e);
            // Keep the original if parsing fails
            processedJson = item.json;
          }
          
          acc[item.fileId] = processedJson;
          return acc;
        }, {} as Record<string, unknown>);
        
        // Create a map of file ID to moth metadata
        mothMetadataMap = mothMetadataResults.reduce((acc, item) => {
          acc[item.fileId] = {
            gain: item.gain,
            batteryV: item.batteryV !== null ? Number(item.batteryV) : null,
            tempC: item.tempC !== null ? Number(item.tempC) : null
          };
          return acc;
        }, {} as Record<string, {
          gain: string | null;
          batteryV: number | null;
          tempC: number | null;
        }>);
        
        // Create a map of file ID to species data, grouping species by file
        speciesMap = speciesResults.reduce((acc, item) => {
          if (!acc[item.fileId]) {
            acc[item.fileId] = [];
          }
          
          // Only add the species if it's not already in the array for this file
          // (we might have multiple selections with the same species in one file)
          const existingSpecies = acc[item.fileId].find((s: { id: string }) => s.id === item.speciesId);
          if (!existingSpecies) {
            acc[item.fileId].push({
              id: item.speciesId,
              label: item.speciesLabel,
              ebirdCode: item.ebirdCode,
              description: item.description
            });
          }
          
          return acc;
        }, {} as Record<string, Array<{
          id: string;
          label: string;
          ebirdCode: string | null;
          description: string | null;
        }>>);
        
      } catch (metadataError) {
        console.error("Error fetching metadata:", metadataError);
        // Don't fail the whole request if metadata fetch fails

    speciesResults.forEach(item => {
      if (!speciesMap.has(item.fileId)) {
        speciesMap.set(item.fileId, []);

    }
    
    // Enrich file data with standard metadata, moth metadata, and species information
    const files = filesResult.map(fileData => {
      let metadata = null;
      let mothData = null;
      let speciesData: Array<{
        id: string;
        label: string;
        ebirdCode: string | null;
        description: string | null;
      }> = [];

      try {
        // Process regular metadata
        metadata = metadataMap[fileData.id] || null;
        
        // If we got metadata but it's still in a problematic format, try to parse it
        if (metadata && typeof metadata === 'string' && metadata.includes('noiseType')) {
          try {
            // Check for double-encoded JSON (a common issue)
            if (metadata.includes('\\"')) {
              const cleaned = metadata.replace(/\\"/g, '"');
              metadata = JSON.parse(cleaned);
            } else {
              metadata = JSON.parse(metadata);
            }
          } catch (parseError) {
            console.error(`Error parsing metadata string for file ${fileData.id}:`, parseError);
            // Keep as-is if parsing fails
          }
        }
        
        // Get moth metadata if available
        mothData = mothMetadataMap[fileData.id] || null;
        
        // Get species data if available
        speciesData = speciesMap[fileData.id] || [];
      } catch (e) {
        console.error(`Error processing metadata for file ${fileData.id}:`, e);
        metadata = null;
        mothData = null;
        speciesData = [];

      // Get the current species array for this file
      const fileSpecies = speciesMap.get(item.fileId)!;
      
      // Check if we already have this species (deduplication)
      const existingSpeciesIndex = fileSpecies.findIndex(s => s.id === item.speciesId);
      
      if (existingSpeciesIndex === -1) {
        // Add species if it doesn't already exist for this file
        fileSpecies.push({
          id: item.speciesId,
          label: item.speciesLabel,
          ebirdCode: item.ebirdCode,
          description: item.description
        });

      
      return {
        ...fileData,
        metadata,
        mothMetadata: mothData,
        species: speciesData
      };

    // Return paginated results with metadata and applied filters
    const response = {

    // Combine file data with metadata in a single operation
    const files = filesResult.map(fileData => ({
      ...fileData,
      metadata: metadataMap[fileData.id] || null,
      mothMetadata: mothMetadataMap[fileData.id] || null,
      species: speciesMap.get(fileData.id) || []
    }));
    
    // Return paginated results with metadata and filters
    return c.json({

    };
    
    return c.json(response);

    });