species 

  species,
  labelSubtype,
  callType

/**
 * Protected API route to fetch selections, species, and call types for a specific file
 * 
 * @route GET /api/files/:fileId/selections
 * @authentication Required
 * @param {string} fileId - File ID in URL path
 * @returns {Object} Response containing:
 *   - data: Array of selection objects with species and call type data
 * @error 400 - If fileId is invalid
 * @error 404 - If file not found
 * @error 500 - If database operation fails
 * @description Returns all active selections for a file with associated species and call types
 *   Each selection includes:
 *   - Selection timing (startTime, endTime)
 *   - Species information (id, name)
 *   - Call types (if any)
 */
files.get("/:fileId/selections", authenticate, async (c) => {
  try {
    const fileId = c.req.param("fileId");
    
    // Validate file ID format
    if (!isValidFileId(fileId)) {
      return c.json({
        error: "Invalid file ID format"
      }, 400);
    }
    
    // Connect to database
    const db = createDatabase(c.env);
    
    // First verify the file exists and is active
    const fileResult = await db
      .select({
        id: file.id,
        active: file.active
      })
      .from(file)
      .where(eq(file.id, fileId))
      .limit(1);
    
    if (fileResult.length === 0) {
      return c.json({
        error: "File not found"
      }, 404);
    }
    
    if (!fileResult[0].active) {
      return c.json({
        error: "File is not active"
      }, 404);
    }
    
    // Complex query to get selections with species and call types
    const results = await db
      .select({
        selectionId: selection.id,
        startTime: selection.startTime,
        endTime: selection.endTime,
        freqLow: selection.freqLow,
        freqHigh: selection.freqHigh,
        labelId: label.id,
        speciesId: species.id,
        speciesLabel: species.label,
        callTypeId: callType.id,
        callTypeLabel: callType.label,
        labelCertainty: label.certainty,
        subtypeCertainty: labelSubtype.certainty
      })
      .from(selection)
      .leftJoin(label, eq(selection.id, label.selectionId))
      .leftJoin(species, eq(label.speciesId, species.id))
      .leftJoin(labelSubtype, eq(label.id, labelSubtype.labelId))
      .leftJoin(callType, eq(labelSubtype.calltypeId, callType.id))
      .where(
        sqlExpr`${selection.fileId} = ${fileId} 
          AND ${selection.active} = true 
          AND (${label.active} IS NULL OR ${label.active} = true)
          AND (${species.active} IS NULL OR ${species.active} = true)
          AND (${labelSubtype.active} IS NULL OR ${labelSubtype.active} = true)
          AND (${callType.active} IS NULL OR ${callType.active} = true)`
      )
      .orderBy(selection.startTime);
    
    // Group results by selection and aggregate species/call types
    const selectionsMap = new Map<string, {
      id: string;
      startTime: number;
      endTime: number;
      freqLow: number | null;
      freqHigh: number | null;
      species: Array<{
        id: string;
        name: string;
        certainty: number | null;
        callTypes: Array<{
          id: string;
          name: string;
          certainty: number | null;
        }>;
      }>;
    }>();
    
    results.forEach(row => {
      const selectionId = row.selectionId;
      
      if (!selectionsMap.has(selectionId)) {
        selectionsMap.set(selectionId, {
          id: selectionId,
          startTime: Number(row.startTime),
          endTime: Number(row.endTime),
          freqLow: row.freqLow ? Number(row.freqLow) : null,
          freqHigh: row.freqHigh ? Number(row.freqHigh) : null,
          species: []
        });
      }
      
      const selection = selectionsMap.get(selectionId)!;
      
      // Add species if we have one and haven't added it yet
      if (row.speciesId && row.speciesLabel) {
        let species = selection.species.find(s => s.id === row.speciesId);
        
        if (!species) {
          species = {
            id: row.speciesId,
            name: row.speciesLabel,
            certainty: row.labelCertainty ? Number(row.labelCertainty) : null,
            callTypes: []
          };
          selection.species.push(species);
        }
        
        // Add call type if we have one and haven't added it yet
        if (row.callTypeId && row.callTypeLabel) {
          const existingCallType = species.callTypes.find(ct => ct.id === row.callTypeId);
          if (!existingCallType) {
            species.callTypes.push({
              id: row.callTypeId,
              name: row.callTypeLabel,
              certainty: row.subtypeCertainty ? Number(row.subtypeCertainty) : null
            });
          }
        }
      }
    });
    
    // Convert map to array
    const selectionsArray = Array.from(selectionsMap.values());
    
    return c.json({
      data: selectionsArray
    });
    
  } catch (error) {
    console.error("Error fetching file selections:", error);
    return c.json({
      error: "Failed to fetch file selections",
      details: error instanceof Error ? error.message : String(error),
    }, 500);
  }
});