// Check if user has ADMIN or CURATOR permission for this dataset
    const hasAdminPermission = await checkUserPermission(db, userId, datasetId, 'ADMIN');
    const hasCuratorPermission = await checkUserPermission(db, userId, datasetId, 'CURATOR');

    // Check if user has EDIT permission for this dataset
    const hasEditPermission = await checkUserPermission(db, userId, datasetId, 'EDIT');

    if (!hasAdminPermission && !hasCuratorPermission) {

    if (!hasEditPermission) {

        error: "Access denied: ADMIN or CURATOR permission required for this dataset"

        error: "Access denied: EDIT permission required for this dataset"

    // Start transaction
    const result = await db.transaction(async (tx) => {

    // Process without transaction (Neon HTTP driver doesn't support transactions)
    // Verify all referenced entities exist first

      const filterExists = await tx.select({ id: filter.id })

      const filterExists = await db.select({ id: filter.id })

      const fileResults = await tx.select({

      const fileResults = await db.select({

      const speciesResults = await tx.select({ id: species.id })

      const speciesResults = await db.select({ id: species.id })

        const callTypeResults = await tx.select({ id: callType.id })

        const callTypeResults = await db.select({ id: callType.id })

        await tx.insert(selection).values({

        await db.insert(selection).values({

          await tx.insert(label).values({

          await db.insert(label).values({

              await tx.insert(labelSubtype).values({

              await db.insert(labelSubtype).values({

                filterId: filterId,

      return {

      const result = {

    });

/**
 * Protected API route to validate selection files against database files
 * 
 * @route POST /api/files/validate-selection-files
 * @authentication Required
 * @param {string} clusterId - Required body parameter specifying the cluster to check against
 * @param {string[]} baseFileNames - Array of base file names from selections to validate
 * @returns {Object} Response containing:
 *   - data: Array of matching file objects with id, fileName, and baseName
 * @error 400 - If clusterId or baseFileNames are missing/invalid
 * @error 403 - If user doesn't have permission to access the cluster
 * @error 500 - If database operation fails
 * @description Efficiently validates that selection base file names exist in the database
 *   for the specified cluster. Uses SQL IN clause for optimal performance with large datasets.
 *   
 *   Request body:
 *   {
 *     "clusterId": "cluster123",
 *     "baseFileNames": ["20241201_214500", "20241201_214600", ...]
 *   }
 *   
 *   Response includes only files that match the provided base names:
 *   {
 *     "data": [
 *       { "id": "file123", "fileName": "20241201_214500.wav", "baseName": "20241201_214500" },
 *       { "id": "file124", "fileName": "folder/20241201_214600.wav", "baseName": "20241201_214600" }
 *     ]
 *   }
 */
files.post("/validate-selection-files", authenticate, async (c) => {
  try {
    const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    const userId = jwtPayload.sub;
    
    // Parse request body
    const body = await c.req.json();
    const { clusterId, baseFileNames } = body;
    
    // Validate required parameters
    if (!clusterId) {
      return c.json({
        error: "Missing required parameter: clusterId"
      }, 400);
    }
    
    if (!Array.isArray(baseFileNames) || baseFileNames.length === 0) {
      return c.json({
        error: "Missing or invalid parameter: baseFileNames must be a non-empty array"
      }, 400);
    }
    
    // Validate baseFileNames are strings
    if (!baseFileNames.every(name => typeof name === 'string')) {
      return c.json({
        error: "Invalid parameter: all baseFileNames must be strings"
      }, 400);
    }
    
    // Connect to the database
    const db = createDatabase(c.env);
    
    // Get the datasetId for this cluster to check permissions
    const clusterResult = await db
      .select({ datasetId: cluster.datasetId })
      .from(cluster)
      .where(eq(cluster.id, clusterId))
      .limit(1);
    
    if (clusterResult.length === 0) {
      return c.json({
        error: "Access denied"
      }, 403);
    }
    
    const datasetId = clusterResult[0].datasetId;
    
    // Check if user has READ permission for this dataset
    const hasPermission = await checkUserPermission(db, userId, datasetId, 'READ');
    
    if (!hasPermission) {
      return c.json({
        error: "Access denied: No READ permission for this dataset"
      }, 403);
    }
    
    // Query all files for this cluster
    const filesResult = await db
      .select({
        id: file.id,
        fileName: file.fileName,
      })
      .from(file)
      .where(
        and(
          eq(file.clusterId, clusterId),
          eq(file.active, true)
        )
      );
    
    // Process files and match against base names
    const matchingFiles: Array<{ id: string; fileName: string; baseName: string }> = [];
    const baseFileNamesSet = new Set(baseFileNames);
    
    for (const dbFile of filesResult) {
      // Skip hidden files (starting with .)
      if (dbFile.fileName.startsWith('.')) {
        continue;
      }
      
      // Extract base name from database file name (remove extension and path)
      let dbFileName = dbFile.fileName;
      
      // Remove path if present (take last part after /)
      if (dbFileName.includes('/')) {
        dbFileName = dbFileName.split('/').pop() || dbFileName;
      }
      
      // Remove extension (.wav or .WAV)
      const baseName = dbFileName.replace(/\.(wav|WAV)$/i, '');
      
      // Check if this base name matches any selection base name
      if (baseFileNamesSet.has(baseName)) {
        matchingFiles.push({
          id: dbFile.id,
          fileName: dbFile.fileName,
          baseName: baseName
        });
      }
    }
    
    return c.json({
      data: matchingFiles
    });
    
  } catch (error) {
    return c.json(standardErrorResponse(error, "validating selection files"), 500);
  }
});

import filters from "./routes/filters";

app.route("/api/filters", filters);

  // Filter state
  const [filterName, setFilterName] = useState<string | null>(null);
  const [filterId, setFilterId] = useState<string | null>(null);
  

      // Parse filter name from folder name
      const parsedFilterName = parseFilterNameFromFolder(directoryHandle.name);
      setFilterName(parsedFilterName);
      console.log('Parsed filter name:', parsedFilterName);
      

      setFilterName(null);
      setFilterId(null);

    setFilterName(null);
    setFilterId(null);

      // First, validate the filter if we have a filter name from the folder
      if (filterName) {
        console.log('Validating filter:', filterName);
        const filterResponse = await fetch('/api/filters/validate', {
          method: 'POST',
          headers: { 
            Authorization: `Bearer ${accessToken}`,
            'Content-Type': 'application/json'
          },
          body: JSON.stringify({ name: filterName })
        });
        
        if (!filterResponse.ok) {
          const errorText = await filterResponse.text();
          console.error('Filter validation failed:', filterResponse.status, errorText);
          throw new Error(`Filter '${filterName}' not found. Please ensure the filter exists in the database.`);
        }
        
        const filterData = await filterResponse.json();
        const validatedFilterId = filterData.data.id;
        setFilterId(validatedFilterId);
        console.log('Filter validation successful:', filterName, '->', validatedFilterId);
      } else {
        throw new Error('No filter name could be parsed from the folder name. Please ensure the folder follows the pattern: Clips_{filterName}_{date}');
      }
      

      // Fetch files for this cluster to map base names to file IDs
      console.log('Fetching files for clusterId:', clusterId);
      const filesResponse = await fetch(`/api/files?clusterId=${encodeURIComponent(clusterId)}&pageSize=500`, {
        headers: { Authorization: `Bearer ${accessToken}` },

      // Validate selection files against database using new efficient API endpoint
      // This replaces the inefficient pagination approach with a targeted validation
      console.log('Validating selection files for clusterId:', clusterId, 'with', fileBaseNames.length, 'base names');
      console.log('Selection base names to validate:', fileBaseNames.slice(0, 10), `... (${fileBaseNames.length} total)`);
      
      const filesResponse = await fetch('/api/files/validate-selection-files', {
        method: 'POST',
        headers: { 
          Authorization: `Bearer ${accessToken}`,
          'Content-Type': 'application/json'
        },
        body: JSON.stringify({
          clusterId: clusterId,
          baseFileNames: fileBaseNames
        })

        console.error('Files API error:', filesResponse.status, errorText);
        throw new Error(`Failed to fetch cluster files: ${filesResponse.status} ${errorText}`);

        console.error('File validation API error:', filesResponse.status, errorText);
        throw new Error(`Failed to validate selection files: ${filesResponse.status} ${errorText}`);

      const fileMap = new Map<string, string>();
      const availableFiles = filesData.data || [];

      const fileMap = new Map<string, string>();  // Maps selection base name -> database file ID
      const matchingFiles = filesData.data || [];

      // Create a map of database file base names to file IDs for efficient lookup
      const dbFileMap = new Map<string, { id: string; fileName: string }>();

      console.log('Matching files found:', matchingFiles.length, 'out of', fileBaseNames.length, 'requested');

      for (const dbFile of availableFiles) {
        // Skip hidden files (starting with .)
        if (dbFile.fileName.startsWith('.')) {
          continue;
        }
        
        // Extract base name from database file name (remove extension and path)
        let dbFileName = dbFile.fileName;
        
        // Remove path if present (take last part after /)
        if (dbFileName.includes('/')) {
          dbFileName = dbFileName.split('/').pop() || dbFileName;
        }
        
        // Remove extension (.wav or .WAV)
        dbFileName = dbFileName.replace(/\.(wav|WAV)$/i, '');
        
        dbFileMap.set(dbFileName, dbFile);

      // Create mapping from validated files
      // The API already did the heavy lifting of matching base names to database files
      for (const matchedFile of matchingFiles) {
        fileMap.set(matchedFile.baseName, matchedFile.id);
        console.log(`✓ Validated: ${matchedFile.baseName} -> ${matchedFile.id} (${matchedFile.fileName})`);

      
      console.log('Database files available:', Array.from(dbFileMap.keys()).slice(0, 10), `... (${dbFileMap.size} total)`);
      console.log('Selection base names to match:', fileBaseNames.slice(0, 10), `... (${fileBaseNames.length} total)`);
      
      // Show first few examples for comparison
      console.log('First 5 database files:', Array.from(dbFileMap.keys()).slice(0, 5));
      console.log('First 5 selection base names:', fileBaseNames.slice(0, 5));

      // Map base file names from selections to file IDs
      for (const baseName of fileBaseNames) {
        const dbFile = dbFileMap.get(baseName);
        if (dbFile) {
          fileMap.set(baseName, dbFile.id);
          console.log(`✓ Mapped: ${baseName} -> ${dbFile.id} (${dbFile.fileName})`);
        } else {
          console.log(`✗ No match found for: ${baseName}`);
        }

      // Log any missing files for debugging
      const foundBaseNames = new Set(matchingFiles.map((f: { baseName: string }) => f.baseName));
      const missingBaseNames = fileBaseNames.filter(baseName => !foundBaseNames.has(baseName));
      if (missingBaseNames.length > 0) {
        console.log(`✗ Missing files (${missingBaseNames.length}):`, missingBaseNames.slice(0, 10), missingBaseNames.length > 10 ? '...' : '');

  }, [isAuthenticated, getAccessToken, datasetId, clusterId]);

  }, [isAuthenticated, getAccessToken, datasetId, clusterId, filterName]);

  const parseFilterNameFromFolder = (folderName: string): string | null => {
    // Parse filter name from folder pattern: Clips_{filterName}_{date}
    // Example: "Clips_opensoundscape-kiwi-1.2_2025-06-28" -> "opensoundscape-kiwi-1.2"
    
    const clipsPrefix = 'Clips_';
    if (!folderName.startsWith(clipsPrefix)) {
      console.warn('Folder name does not start with expected "Clips_" prefix:', folderName);
      return null;
    }
    
    // Remove the "Clips_" prefix
    const withoutPrefix = folderName.substring(clipsPrefix.length);
    
    // Split by underscore and get all parts except the last one (which should be the date)
    const parts = withoutPrefix.split('_');
    if (parts.length < 2) {
      console.warn('Folder name does not follow expected pattern Clips_{filterName}_{date}:', folderName);
      return null;
    }
    
    // Join all parts except the last one to reconstruct the filter name
    // This handles filter names that might contain underscores
    const filterName = parts.slice(0, -1).join('_');
    
    if (!filterName) {
      console.warn('Could not extract filter name from folder:', folderName);
      return null;
    }
    
    console.log('Parsed filter name:', filterName, 'from folder:', folderName);
    return filterName;
  };

    // Remove extension

    // Parse selection filename to extract base name and timing information
    // Expected format: "basename-startTime-endTime.ext"
    // e.g., "20241201_214500-105.5-133.2.wav" -> baseName="20241201_214500", start=105.5, end=133.2
    
    // Remove file extension (.png or .wav, case insensitive)

    // Split on last dash to get timing info

    // Split on dashes to separate base name from timing info

      // Invalid format: need at least basename-start-end

    // Get start and end times (last two parts)

    // Extract timing from last two parts (start and end times in seconds)

    // Get base name (everything except last two parts)

    // Reconstruct base name from all parts except the last two
    // This handles cases where the original filename contains dashes
    // e.g., "recording-site-01-20241201_214500-105-133" -> baseName="recording-site-01-20241201_214500"

    // Validate timing values

      // Invalid timing: non-numeric values or end time before start time

    // This map stores file pairs (wav/png) grouped by their base name
    // Key: full path + base name (e.g., "Species1/CallType1/20241201_214500-105")
    // Value: object containing wav/png handles, species, call type, and folder path

    // First, count total files for progress

    // First pass: count total files for progress tracking
    // This recursively walks the directory tree and counts non-hidden files

          // Skip hidden files (starting with . or ._) in counting too

          // Skip hidden files (starting with . including ._DS_Store, ._filename, etc.)
          // These are macOS metadata files that we don't want to process

    // Recursive function to scan directories

    // Second pass: recursively scan directories and collect file pairs
    // This function understands the expected folder structure:
    // Root -> Species folders -> Optional call type folders -> WAV/PNG files

          // Skip hidden files (starting with . or ._)

          // Skip hidden files during processing (same logic as counting)
          // This prevents processing ._filename files that macOS creates

          // Check if this is a WAV or PNG file (case insensitive)

            // Parse the filename to extract base name and timing info
            // Expected format: basename-startTime-endTime.ext
            // e.g., "20241201_214500-105-133.wav" -> baseName="20241201_214500", start=105, end=133

              // Create a unique key for this file pair using full path + base name

              // Initialize the file entry if it doesn't exist

              // Store the file handle in the appropriate slot (wav or png)

          // Update progress counter for each file processed

          // Determine if this is a species folder or call type folder

          // Determine folder hierarchy level based on whether we already have a species

            // This is a species folder

            // This is a top-level species folder (e.g., "Acanthiza reguloides")

            // This is a call type folder

            // This is a call type folder within a species (e.g., "Contact Call")

    // Validate files and create results

    // Third pass: validate file pairs and create final results
    // Go through all collected files and check if they have both WAV and PNG

        // Parse timing from PNG filename

        // For valid pairs, parse timing information from the PNG filename
        // We use PNG filename as the reference since it typically contains the selection bounds

          // Create a valid file entry with all required information

            baseName: parsed.baseName,
            startTime: parsed.startTime,
            endTime: parsed.endTime,
            wavFile: fileEntry.wav,
            pngFile: fileEntry.png,
            species: fileEntry.species,
            callType: fileEntry.callType || undefined,
            folderPath: fileEntry.folderPath

            baseName: parsed.baseName,  // Base audio file name (without timing suffix)
            startTime: parsed.startTime,  // Selection start time in seconds
            endTime: parsed.endTime,      // Selection end time in seconds
            wavFile: fileEntry.wav,       // Handle to WAV file
            pngFile: fileEntry.png,       // Handle to PNG file
            species: fileEntry.species,   // Species name from folder structure
            callType: fileEntry.callType || undefined,  // Call type from folder structure (optional)
            folderPath: fileEntry.folderPath  // Full folder path for reference

          // Track unique species and call types found

          // File pair exists but filename format is invalid

        // File pair is incomplete (missing WAV or PNG)

      validFiles,
      invalidFiles,
      species: Array.from(species),
      callTypes: Array.from(callTypes),
      totalFiles: validFiles.length + invalidFiles.length

      validFiles,       // Array of complete, valid file pairs with parsed timing
      invalidFiles,     // Array of files with issues (missing pair or invalid format)
      species: Array.from(species),      // Unique species found in folder structure
      callTypes: Array.from(callTypes),  // Unique call types found in folder structure
      totalFiles: validFiles.length + invalidFiles.length  // Total processed files

      // Get first available filter for this dataset - we'll use a simple approach

      // Use the validated filter ID from the folder parsing

      // Fetch filters to get a valid filter ID
      const filtersResponse = await fetch('/api/filters', {
        headers: { Authorization: `Bearer ${accessToken}` },
      });

      if (!filterId) {
        throw new Error('No valid filter ID available. Filter validation may have failed.');
      }

      let filterId = 'default-filter-id'; // fallback
      if (filtersResponse.ok) {
        const filtersData = await filtersResponse.json();
        const filters = filtersData.data || [];
        if (filters.length > 0) {
          filterId = filters[0].id; // Use first available filter
        }
      }

      console.log('Using validated filter ID for import:', filterId);

      const response = await fetch('/api/selections', {

      const response = await fetch('/api/selection', {

  }, [validationResult, databaseMapping, isAuthenticated, datasetId, getAccessToken, onImportComplete, handleClose]);

  }, [validationResult, databaseMapping, isAuthenticated, datasetId, getAccessToken, onImportComplete, handleClose, filterId]);

                  <div className="flex items-center">

                  <div className="flex items-center mb-2">

                  {filterName && (
                    <div className="text-xs text-green-700 ml-7">
                      Filter: <span className="font-mono">{filterName}</span>
                    </div>
                  )}
                  {!filterName && (
                    <div className="text-xs text-red-600 ml-7">
                      ⚠ Could not parse filter name from folder
                    </div>
                  )}