import { dataset, location, cluster, cyclicRecordingPattern, file, fileMetadata, mothMetadata } from "../../db/schema";

import { 
  dataset, 
  location, 
  cluster, 
  cyclicRecordingPattern, 
  file, 
  fileMetadata, 
  mothMetadata, 
  species, 
  speciesDataset, 
  callType 
} from "../../db/schema";

        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});
// Species API route - get species and call types for a dataset
app.get("/api/species", authenticate, async (c) => {
  try {
    // Get the JWT payload (user info)
    // const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    // const userId = jwtPayload.sub; // User ID from JWT
    
    // Get query parameter for datasetId
    const datasetId = c.req.query("datasetId");
    
    // Validate parameters
    if (!datasetId) {
      return c.json({
        error: "Missing required query parameter: datasetId"
      }, 400);
    }
    
    // Connect to the database
    const sql = neon(c.env.DATABASE_URL);
    const db = drizzle(sql);
    
    // First, query species_dataset junction table to get species IDs for this dataset
    const speciesIdsResult = await db
      .select({
        speciesId: speciesDataset.speciesId
      })
      .from(speciesDataset)
      .where(eq(speciesDataset.datasetId, datasetId));
    
    // If no species are found, return an empty array
    if (speciesIdsResult.length === 0) {
      return c.json({
        data: []
      });
    }
    
    // Extract the species IDs
    const speciesIds = speciesIdsResult.map(result => result.speciesId);
    const speciesIdsQuoted = speciesIds.map(id => `'${id}'`).join(',');
    
    // Get the species details
    const speciesResults = await db
      .select({
        id: species.id,
        label: species.label,
        ebirdCode: species.ebirdCode,
        description: species.description
      })
      .from(species)
      .where(sqlExpr`${species.id} IN (${sqlExpr.raw(speciesIdsQuoted)})`);
    
    // Get call types for all these species
    const callTypesResults = await db
      .select({
        speciesId: callType.speciesId,
        id: callType.id,
        label: callType.label
      })
      .from(callType)
      .where(sqlExpr`${callType.speciesId} IN (${sqlExpr.raw(speciesIdsQuoted)}) AND ${callType.active} = true`);
    
    // Group call types by species ID for easier mapping
    const callTypesBySpecies: Record<string, { id: string, label: string }[]> = {};
    
    callTypesResults.forEach(callTypeItem => {
      if (!callTypesBySpecies[callTypeItem.speciesId]) {
        callTypesBySpecies[callTypeItem.speciesId] = [];
      }
      
      callTypesBySpecies[callTypeItem.speciesId].push({
        id: callTypeItem.id,
        label: callTypeItem.label
      });
    });
    
    // Combine species with their call types
    const enrichedSpecies = speciesResults.map(speciesItem => ({
      id: speciesItem.id,
      label: speciesItem.label,
      ebirdCode: speciesItem.ebirdCode,
      description: speciesItem.description,
      callTypes: callTypesBySpecies[speciesItem.id] || []
    }));
    
    // Return the enriched species data
    return c.json({
      data: enrichedSpecies
    });
    
  } catch (error) {
    console.error("Error fetching species:", error);
    return c.json(
      {
        error: "Failed to fetch species",