// Get the JWT payload (user info)
    // const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    // const userId = jwtPayload.sub; // User ID from JWT
    

    // We'll handle speciesId filter separately if needed when joining with the selection and label tables
    

    // If filtering by species, we need a more complex query that joins with selections and labels

          count: sqlExpr<number>`COUNT(*)`

          count: sqlExpr<number>`COUNT(1)`

    // First get the files using the same conditions as the count query

      
    // Get all file IDs to fetch metadata
    const fileIds = filesResult.map(f => f.id);

    // Fetch metadata, moth metadata, and species data for these files
    let metadataMap: Record<string, unknown> = {};
    let mothMetadataMap: Record<string, {
      gain: string | null;
      batteryV: number | null;
      tempC: number | null;
    }> = {};
    let speciesMap: Record<string, Array<{
      id: string;
      label: string;
      ebirdCode: string | null;
      description: string | null;
    }>> = {};

    // Early return if no files found
    if (filesResult.length === 0) {
      return c.json({
        data: [],
        pagination: {
          currentPage: page,
          pageSize: limitedPageSize,
          totalPages: totalPages,
          totalItems: totalFiles,
          hasNextPage: page < totalPages,
          hasPreviousPage: page > 1,
        },
        filters: {
          solarNight: solarNight === 'true' ? true : solarNight === 'false' ? false : null,
          civilNight: civilNight === 'true' ? true : civilNight === 'false' ? false : null,
          speciesId: speciesId || null,
        }
      });
    }

    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
        // This query gets all species that have been labeled in any selection within these files
        const speciesQueryCondition = speciesId 
          ? sqlExpr`${selection.fileId} IN (${sqlExpr.raw(fileIdsQuoted)}) AND ${label.speciesId} = ${speciesId} AND ${label.active} = true` 
          : sqlExpr`${selection.fileId} IN (${sqlExpr.raw(fileIdsQuoted)}) AND ${label.active} = true`;
          
        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(speciesQueryCondition);
          
        
        // 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
      }

    // Get all file IDs for metadata queries
    const fileIds = filesResult.map(f => f.id);
    
    // Safety check - if no files found, return empty results
    if (fileIds.length === 0) {
      return c.json({
        data: [],
        pagination: {
          currentPage: page,
          pageSize: limitedPageSize,
          totalPages: totalPages,
          totalItems: totalFiles,
          hasNextPage: page < totalPages,
          hasPreviousPage: page > 1,
        },
        filters: {
          solarNight: solarNight === 'true' ? true : solarNight === 'false' ? false : null,
          civilNight: civilNight === 'true' ? true : civilNight === 'false' ? false : null,
          speciesId: speciesId || null,
        }
      });

    // 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;
      }> = [];

    // Convert array to proper SQL format for IN clause
    const fileIdsQuoted = fileIds.map(id => `'${id}'`).join(',');
    
    // Execute metadata queries in parallel using Promise.all
    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 via selections and labels
      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(
        speciesId
          ? sqlExpr`${selection.fileId} IN (${sqlExpr.raw(fileIdsQuoted)}) AND ${label.active} = true AND ${label.speciesId} = ${speciesId}`
          : sqlExpr`${selection.fileId} IN (${sqlExpr.raw(fileIdsQuoted)}) AND ${label.active} = true`
      )
    ]);
    
    // Process metadata results
    const metadataMap = metadataResults.reduce((acc, item) => {
      let processedJson = item.json;

        // 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
          }

        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);

        
        // Get moth metadata if available
        mothData = mothMetadataMap[fileData.id] || null;
        
        // Get species data if available
        speciesData = speciesMap[fileData.id] || [];

        console.error(`Error processing metadata for file ${fileData.id}:`, e);
        metadata = null;
        mothData = null;
        speciesData = [];

        console.error("Error processing metadata JSON:", e);
        processedJson = item.json;

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

      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, {
      gain: string | null;
      batteryV: number | null;
      tempC: number | null;
    }>);

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

    // Process species data
    const speciesMap = speciesResults.reduce((acc, item) => {
      if (!acc[item.fileId]) {
        acc[item.fileId] = [];
      }
      
      // Deduplicate species entries
      const existingSpecies = acc[item.fileId].find(s => 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;
    }>>);
    
    // Combine file data with metadata
    const files = filesResult.map(fileData => ({
      ...fileData,
      metadata: metadataMap[fileData.id] || null,
      mothMetadata: mothMetadataMap[fileData.id] || null,
      species: speciesMap[fileData.id] || []
    }));
    
    // Return paginated results with metadata and filters
    return c.json({

    };
    
    
    return c.json(response);

    });