quietlight/skraak.app - Change 7FCKFQUZIWQR7GYO7KNQDTZBM72PAE7EQBHSWM2J3MXBGDUVWQZAC

reorganised worker/index.ts, tidier now

Created by AEj8dahVWy718uSSFPe9VSRJ5qX5G8pC2zvFzJJ8yzBd on May 26, 2025

7FCKFQUZIWQR7GYO7KNQDTZBM72PAE7EQBHSWM2J3MXBGDUVWQZAC

Dependencies

In channels

main

Change contents

Insertion in src/worker/index.ts at line 220 [11.240196]

[10.11600]


// ============================================================================
// API ROUTES - Organized by resource
// ============================================================================

Insertion in src/worker/index.ts at line 226 [11.240196]

[10.11601]

[12.1155]

// ============================================================================
// DATASETS
// ============================================================================

Insertion in src/worker/index.ts at line 363 [11.240196]
[11.240328]
[12.1257]

Replacement in src/worker/index.ts at line 365 [11.240196]

B:BD[12.1261] → [12.1261:1326]

 * Protected API route to fetch locations for a specific dataset

[12.1261]

[13.4948]

 * Protected API route to create a new dataset

Replacement in src/worker/index.ts at line 367 [11.240196]
B:BD[13.4952] → [13.4952:4981]
```
 * @route GET /api/locations
```
[13.4952]
[13.4981]
```
 * @route POST /api/datasets
```

Replacement in src/worker/index.ts at line 369 [11.240196]

B:BD[13.5009] → [13.5009:5272]

 * @param {string} datasetId - Required query parameter specifying the dataset to fetch locations from
 * @param {number} [page=1] - Optional page number for pagination (starts at 1)
 * @param {number} [pageSize=100] - Optional page size (1-100, defaults to 100)

[13.5009]

[13.5272]

 * @body {Object} Dataset creation payload:
 *   - id: string (nanoid(12) - user generated)
 *   - name: string (required, max 255 chars)
 *   - description?: string (optional, max 255 chars)
 *   - public?: boolean (optional, defaults to false)
 *   - type?: string (optional, defaults to 'organise')

Replacement in src/worker/index.ts at line 376 [11.240196]

B:BD[13.5314] → [13.5314:5656]

 *   - data: Array of location objects with id, name, latitude, longitude, description
 *   - pagination: Object with pagination metadata (currentPage, pageSize, totalPages, totalItems, etc.)
 * @error 400 - If datasetId is missing or page is invalid
 * @description Returns active locations for the specified dataset with pagination support

[13.5314]

[12.1392]

 *   - data: The created dataset object
 * @error 400 - If required fields are missing or invalid
 * @error 500 - If database operation fails
 * @description Creates a new dataset for the authenticated user
 *   The user becomes the owner, creator, and modifier of the dataset

Replacement in src/worker/index.ts at line 382 [11.240196]
∅:D[14.97] → [15.5750:5805]
∅:D[12.1396] → [15.5750:5805]
B:BD[15.5750] → [15.5750:5805]
```
app.get("/api/locations", authenticate, async (c) => {
```
[12.1396]
[15.5805]
```
app.post("/api/datasets", authenticate, async (c) => {
```

Replacement in src/worker/index.ts at line 386 [11.240196]

B:BD[16.18084] → [17.113:214]

    const userId = jwtPayload.sub; // User ID from JWT // Subject claim usually contains the user ID

[16.18084]

[15.5993]

    const userId = jwtPayload.sub; // User ID from JWT
    // Connect to the database first to check permissions
    const sql = neon(c.env.DATABASE_URL);
    const db = drizzle(sql);

Replacement in src/worker/index.ts at line 392 [11.240196]

B:BD[15.5998] → [15.5998:6093]

B:BD[15.6093] → [18.350:409]

B:BD[18.409] → [19.11:80]

    // Get the dataset ID from query parameter
    const datasetId = c.req.query("datasetId");
    const page = parseInt(c.req.query("page") || "1", 10);
    const pageSize = parseInt(c.req.query("pageSize") || "100", 10);

[15.5998]

[15.6093]

    // Check if user has permission to create datasets (ADMIN or CURATOR roles)
    const userRoleResult = await db
      .select({ role: userRole.role })
      .from(userRole)
      .where(eq(userRole.userId, userId))
      .limit(1);

Replacement in src/worker/index.ts at line 399 [11.240196]

B:BD[15.6098] → [18.478:580]

    console.log("Locations API called with datasetId:", datasetId, "userId:", userId, "page:", page);

[15.6098]

[15.6185]

    const userRoleName = userRoleResult.length > 0 ? userRoleResult[0].role : 'USER';

Replacement in src/worker/index.ts at line 401 [11.240196]

B:BD[15.6190] → [15.6190:6263]

    if (!datasetId) {
      console.log("Missing datasetId in request");

[15.6190]

[15.6263]

    if (userRoleName !== 'ADMIN' && userRoleName !== 'CURATOR') {

Replacement in src/worker/index.ts at line 403 [11.240196]

B:BD[15.6285] → [15.6285:6361]

        error: "Missing required query parameter: datasetId"
      }, 400);

[15.6285]

[15.6361]

        error: "You don't have permission to create datasets"
      }, 403);

Replacement in src/worker/index.ts at line 406 [11.240196]

B:BD[15.6367] → [15.6367:6372]

B:BD[15.6372] → [18.581:629]

    
    // Validate page number
    if (page < 1) {

[15.6367]

[18.629]


    // Parse request body
    const body = await c.req.json();
    const { id, name, description, public: isPublic, type } = body;
    // Validate required fields
    if (!id || typeof id !== 'string') {

Replacement in src/worker/index.ts at line 414 [11.240196]

B:BD[18.651] → [18.651:715]

        error: "Invalid page parameter: must be greater than 0"

[18.651]

[18.715]

        error: "Missing or invalid required field: id"

Deletion in src/worker/index.ts at line 417 [11.240196]

B:BD[18.736] → [18.736:902]

∅:D[18.902] → [15.6372:6479]

B:BD[15.6372] → [15.6372:6479]

B:BD[15.6479] → [18.903:1112]

∅:D[18.1112] → [15.7370:7392]

B:BD[15.7370] → [15.7370:7392]

B:BD[15.7392] → [18.1113:1339]

B:BD[18.1339] → [19.81:175]

∅:D[19.175] → [18.1404:1880]

B:BD[18.1404] → [18.1404:1880]

∅:D[18.1880] → [15.7441:7446]

B:BD[15.7441] → [15.7441:7446]

B:BD[15.7446] → [18.1881:1974]

∅:D[18.1974] → [14.98:123]

B:BD[15.7524] → [14.98:123]

B:BD[14.123] → [18.1975:2229]

∅:D[18.2229] → [14.143:404]

B:BD[14.143] → [14.143:404]

    
    // Limit page size between 1 and 100
    const limitedPageSize = Math.min(Math.max(pageSize, 1), 100);
    const offset = (page - 1) * limitedPageSize;
    
    // Connect to the database
    const sql = neon(c.env.DATABASE_URL);
    const db = drizzle(sql);
    
    // First, get total count for pagination
    console.log("Counting locations for datasetId:", datasetId);
    const countResult = await db
      .select({
        count: sqlExpr<number>`COUNT(*)`
      })
      .from(location)
      .where(sqlExpr`${location.datasetId} = ${datasetId} AND ${location.active} = true`);
    
    const totalLocations = Number(countResult[0].count);
    const totalPages = Math.ceil(totalLocations / limitedPageSize);
    
    // Query locations for the specified dataset with pagination (using 100 as default limit)
    console.log("Querying locations for datasetId:", datasetId, "page:", page);
    const results = await db.select({
      id: location.id,
      name: location.name,
      latitude: location.latitude,
      longitude: location.longitude,
      description: location.description,
    }).from(location)
      .where(sqlExpr`${location.datasetId} = ${datasetId} AND ${location.active} = true`)
      .orderBy(location.name)
      .limit(limitedPageSize)
      .offset(offset);
    
    console.log("Found", results.length, "locations for dataset", datasetId, "page:", page);
    
    return c.json({
      data: results,
      pagination: {
        currentPage: page,
        pageSize: limitedPageSize,
        totalPages: totalPages,
        totalItems: totalLocations,
        hasNextPage: page < totalPages,
        hasPreviousPage: page > 1,
      }
    });
  } catch (error) {
    console.error("Error fetching locations:", error);
    return c.json(
      {
        error: "Failed to fetch locations",
        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});

Replacement in src/worker/index.ts at line 418 [11.240196]

B:BD[14.405] → [14.405:406]

B:BD[14.406] → [12.1397:1466]

B:BD[12.1466] → [13.5657:6265]

∅:D[13.6265] → [12.1528:1532]

B:BD[12.1528] → [12.1528:1532]

∅:D[12.1532] → [14.429:530]

∅:D[20.5268] → [14.429:530]

B:BD[14.429] → [14.429:530]

B:BD[14.530] → [16.18085:18165]

B:BD[16.18165] → [17.215:316]

∅:D[17.316] → [15.7524:7529]

∅:D[14.671] → [15.7524:7529]

B:BD[15.7524] → [15.7524:7529]

B:BD[15.7529] → [20.5269:5317]

∅:D[20.5317] → [14.719:941]

B:BD[14.719] → [14.719:941]


/**
 * Protected API route to fetch clusters for a specific location
 * 
 * @route GET /api/clusters
 * @authentication Required
 * @param {string} locationId - Required query parameter specifying the location to fetch clusters from
 * @returns {Object} Response containing:
 *   - data: Array of cluster objects with recording pattern information
 * @error 400 - If locationId is missing
 * @description Returns clusters for the specified location along with their recording patterns
 *   Performs a LEFT JOIN with the cyclicRecordingPattern table to include recording duration data
 *   Results are returned with recording pattern information embedded in each cluster object
 */
app.get("/api/clusters", 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 // Subject claim usually contains the user ID
    
    // Get the location ID from query parameter
    const locationId = c.req.query("locationId");
    
    console.log("Cluster API called with locationId:", locationId, "userId:", userId);
    
    if (!locationId) {
      console.log("Missing locationId in request");

[14.405]

[14.941]

    if (!name || typeof name !== 'string' || name.trim().length === 0) {

Replacement in src/worker/index.ts at line 420 [11.240196]

B:BD[14.963] → [14.963:1025]

        error: "Missing required query parameter: locationId"

[14.963]

[14.1025]

        error: "Missing or invalid required field: name"

Deletion in src/worker/index.ts at line 423 [11.240196]

B:BD[14.1046] → [14.1046:1158]

B:BD[14.1158] → [2.11:93]

∅:D[2.93] → [14.1208:1274]

∅:D[20.5374] → [14.1208:1274]

B:BD[14.1208] → [14.1208:1274]

B:BD[14.1274] → [2.94:162]

∅:D[2.162] → [21.56:504]

B:BD[21.56] → [21.56:504]

B:BD[21.504] → [2.163:326]

∅:D[2.326] → [21.541:567]

B:BD[21.541] → [21.541:567]

B:BD[21.567] → [2.327:524]

∅:D[2.524] → [14.1383:1388]

∅:D[21.615] → [14.1383:1388]

B:BD[14.1383] → [14.1383:1388]

B:BD[14.1388] → [2.525:610]

∅:D[2.610] → [20.5507:5512]

B:BD[20.5507] → [20.5507:5512]

B:BD[20.5512] → [2.611:1313]

∅:D[2.1313] → [14.1466:1471]

∅:D[20.6896] → [14.1466:1471]

B:BD[14.1466] → [14.1466:1471]

∅:D[14.1471] → [15.7529:7549]

B:BD[15.7529] → [15.7529:7549]

B:BD[15.7549] → [20.6897:6925]

∅:D[20.6925] → [15.7569:7597]

B:BD[15.7569] → [15.7569:7597]

B:BD[15.7597] → [20.6926:6980]

∅:D[20.6980] → [15.7652:7679]

B:BD[15.7652] → [15.7652:7679]

B:BD[15.7679] → [20.6981:7024]

∅:D[20.7024] → [15.7723:7830]

B:BD[15.7723] → [15.7723:7830]

    
    // Connect to the database
    const sql = neon(c.env.DATABASE_URL);
    const db = drizzle(sql);
    
    // Query clusters with a LEFT JOIN on recording patterns to avoid N+1 queries
    console.log("Querying clusters for locationId:", locationId);
    const joinedResults = await db.select({
      // Cluster fields
      id: cluster.id,
      datasetId: cluster.datasetId,
      locationId: cluster.locationId,
      name: cluster.name,
      description: cluster.description,
      createdBy: cluster.createdBy,
      createdAt: cluster.createdAt,
      lastModified: cluster.lastModified,
      modifiedBy: cluster.modifiedBy,
      active: cluster.active,
      timezoneId: cluster.timezoneId,
      cyclicRecordingPatternId: cluster.cyclicRecordingPatternId,
      sampleRate: cluster.sampleRate,
      // Recording pattern fields
      recordS: cyclicRecordingPattern.recordS,
      sleepS: cyclicRecordingPattern.sleepS
    })
    .from(cluster)
    .leftJoin(
      cyclicRecordingPattern, 
      eq(cluster.cyclicRecordingPatternId, cyclicRecordingPattern.id)
    )
    .where(eq(cluster.locationId, locationId))
    .orderBy(cluster.name);
    
    console.log("Found", joinedResults.length, "clusters for location", locationId);
    
    // Transform results to match the expected structure
    const enrichedResults = joinedResults.map(row => ({
      id: row.id,
      datasetId: row.datasetId,
      locationId: row.locationId,
      name: row.name,
      description: row.description,
      createdBy: row.createdBy,
      createdAt: row.createdAt,
      lastModified: row.lastModified,
      modifiedBy: row.modifiedBy,
      active: row.active,
      timezoneId: row.timezoneId,
      cyclicRecordingPatternId: row.cyclicRecordingPatternId,
      sampleRate: row.sampleRate,
      recordingPattern: (row.recordS !== null && row.sleepS !== null) ? {
        recordS: row.recordS,
        sleepS: row.sleepS
      } : null
    }));
    
    return c.json({
      data: enrichedResults
    });
  } catch (error) {
    console.error("Error fetching clusters:", error);
    return c.json(
      {
        error: "Failed to fetch clusters",
        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});

Replacement in src/worker/index.ts at line 424 [11.240196]

B:BD[15.7831] → [12.1533:1608]

B:BD[12.1608] → [13.6266:7627]

∅:D[13.7627] → [12.1863:1867]

B:BD[12.1863] → [12.1863:1867]

∅:D[12.1867] → [16.18211:18270]

B:BD[16.18211] → [16.18211:18270]

B:BD[16.18429] → [16.18429:18633]

B:BD[16.18633] → [17.459:559]

B:BD[17.559] → [22.45:124]

∅:D[22.124] → [16.18633:18638]

∅:D[17.559] → [16.18633:18638]

B:BD[16.18633] → [16.18633:18638]

B:BD[16.18766] → [16.18766:18866]

/**
 * Protected API route to fetch audio files for a specific cluster
 * 
 * @route GET /api/files
 * @authentication Required
 * @param {string} clusterId - Required query parameter specifying the cluster to fetch files from
 * @param {number} [page=1] - Optional page number for pagination (starts at 1)
 * @param {number} [pageSize=100] - Optional page size (10-500, defaults to 100)
 * @param {string} [solarNight] - Optional filter for files recorded during solar night ('true'/'false')
 * @param {string} [civilNight] - Optional filter for files recorded during civil night ('true'/'false')
 * @param {string} [speciesId] - Optional filter for files with selections labeled with specific species
 * @returns {Object} Response containing:
 *   - data: Array of file objects with metadata, mothMetadata, and species information
 *   - pagination: Object with pagination metadata
 *   - filters: Object showing the filters that were applied
 * @error 400 - If clusterId is missing or page is invalid
 * @description Returns audio files for the specified cluster with comprehensive metadata:
 *   - Basic file information (name, path, timestamp, duration, etc.)
 *   - File metadata (JSON format)
 *   - Recording device metadata (gain, battery voltage, temperature)
 *   - Species found in each file
 *   
 *   When speciesId is provided, only returns files that have at least one
 *   selection labeled with the specified species.
 */
app.get("/api/files", authenticate, async (c) => {
  try {
    // Get query parameters
    const clusterId = c.req.query("clusterId");
    const page = parseInt(c.req.query("page") || "1", 10);
    const pageSize = parseInt(c.req.query("pageSize") || "100", 10);
    const solarNight = c.req.query("solarNight");
    const civilNight = c.req.query("civilNight");
    const speciesId = c.req.query("speciesId"); // Optional filter for species
    
    // Validate parameters
    if (!clusterId) {
      console.log("Missing clusterId in request");

[15.7831]

[16.18866]

    // Validate field lengths
    if (id.length !== 12) {

Replacement in src/worker/index.ts at line 427 [11.240196]

B:BD[16.18888] → [16.18888:18949]

        error: "Missing required query parameter: clusterId"

[16.18888]

[16.18949]

        error: "Field 'id' must be exactly 12 characters (nanoid)"

Replacement in src/worker/index.ts at line 430 [11.240196]

B:BD[16.18970] → [16.18970:19269]

    
    // Validate and limit page size to prevent excessive queries
    const limitedPageSize = Math.min(Math.max(pageSize, 10), 500);
    const offset = (page - 1) * limitedPageSize;
    
    // Validate page number
    if (page < 1) {
      console.log("Invalid page number in request:", page);

[16.18970]

[16.19269]


    if (name.length > 255) {

Replacement in src/worker/index.ts at line 433 [11.240196]

B:BD[16.19291] → [16.19291:19355]

        error: "Invalid page parameter: must be greater than 0"

[16.19291]

[16.19355]

        error: "Field 'name' must be 255 characters or less"

Deletion in src/worker/index.ts at line 435 [11.240196]

B:BD[16.19370] → [16.19370:19483]

B:BD[16.19483] → [17.560:1377]

    }
    
    // Connect to the database
    const sql = neon(c.env.DATABASE_URL);
    const db = drizzle(sql);
    
    // Build filter conditions
    let whereConditions = sqlExpr`${file.clusterId} = ${clusterId} AND ${file.active} = true`;
    
    // Add filters for solarNight if specified
    if (solarNight === 'true') {
      whereConditions = sqlExpr`${whereConditions} AND ${file.maybeSolarNight} = true`;
    } else if (solarNight === 'false') {
      whereConditions = sqlExpr`${whereConditions} AND (${file.maybeSolarNight} = false OR ${file.maybeSolarNight} IS NULL)`;
    }
    
    // Add filters for civilNight if specified
    if (civilNight === 'true') {
      whereConditions = sqlExpr`${whereConditions} AND ${file.maybeCivilNight} = true`;
    } else if (civilNight === 'false') {
      whereConditions = sqlExpr`${whereConditions} AND (${file.maybeCivilNight} = false OR ${file.maybeCivilNight} IS NULL)`;

Replacement in src/worker/index.ts at line 436 [11.240196]

∅:D[17.1383] → [16.19483:19488]

B:BD[16.19483] → [16.19483:19488]

∅:D[22.237] → [16.19488:19555]

B:BD[16.19488] → [16.19488:19555]

B:BD[22.337] → [22.337:958]

B:BD[22.958] → [3.11:54]

∅:D[3.54] → [22.1001:1071]

B:BD[22.1001] → [22.1001:1071]

∅:D[22.1071] → [16.19771:19959]

∅:D[17.1415] → [16.19771:19959]

B:BD[16.19771] → [16.19771:19959]

B:BD[17.1488] → [22.1072:2946]

    
    // First, get the total count of files for pagination metadata
    let countResult;
    
    if (speciesId) {
      // Count only files that have at least one selection labeled with the specified species
      countResult = await db
        .select({
          count: sqlExpr<number>`COUNT(DISTINCT ${file.id})`
        })
        .from(file)
        .innerJoin(selection, eq(selection.fileId, file.id))
        .innerJoin(label, eq(label.selectionId, selection.id))
        .where(sqlExpr`${whereConditions} AND ${label.speciesId} = ${speciesId} AND ${label.active} = true`);
    } else {
      // Standard count without species filter
      countResult = await db
        .select({
          count: sqlExpr<number>`COUNT(1)`
        })
        .from(file)
        .where(whereConditions);
    }
    
    const totalFiles = Number(countResult[0].count);
    const totalPages = Math.ceil(totalFiles / limitedPageSize);
    
    // Query files for the specified cluster with pagination
    let filesResult;
    
    if (speciesId) {
      // Get only files that have at least one selection labeled with the specified species
      filesResult = await db
        .select({
          id: file.id,
          fileName: file.fileName,
          path: file.path,
          timestampLocal: file.timestampLocal,
          duration: file.duration,
          sampleRate: file.sampleRate,
          locationId: file.locationId,
          description: file.description,
          maybeSolarNight: file.maybeSolarNight,
          maybeCivilNight: file.maybeCivilNight,
          moonPhase: file.moonPhase,
        })
        .from(file)
        .innerJoin(selection, eq(selection.fileId, file.id))
        .innerJoin(label, eq(label.selectionId, selection.id))
        .where(sqlExpr`${whereConditions} AND ${label.speciesId} = ${speciesId} AND ${label.active} = true`)
        .orderBy(file.timestampLocal)
        .groupBy(file.id, file.fileName, file.path, file.timestampLocal, file.duration, 
          file.sampleRate, file.locationId, file.description, file.maybeSolarNight, 
          file.maybeCivilNight, file.moonPhase)
        .limit(limitedPageSize)
        .offset(offset);
    } else {
      // Standard query without species filter
      filesResult = await db
        .select({
          id: file.id,
          fileName: file.fileName,
          path: file.path,
          timestampLocal: file.timestampLocal,
          duration: file.duration,
          sampleRate: file.sampleRate,
          locationId: file.locationId,
          description: file.description,
          maybeSolarNight: file.maybeSolarNight,
          maybeCivilNight: file.maybeCivilNight,
          moonPhase: file.moonPhase,
        })
        .from(file)
        .where(whereConditions)
        .orderBy(file.timestampLocal)
        .limit(limitedPageSize)
        .offset(offset);

[17.1383]

[22.2946]


    if (description && description.length > 255) {
      return c.json({
        error: "Field 'description' must be 255 characters or less"
      }, 400);

Replacement in src/worker/index.ts at line 442 [11.240196]

B:BD[16.20745] → [16.20745:20750]

B:BD[16.20750] → [3.55:129]

    
    // Early return if no files found
    if (filesResult.length === 0) {

[22.2952]

[3.129]


    // Validate type if provided
    const validTypes = ['organise', 'test', 'train'];
    const datasetType = type || 'organise';
    if (!validTypes.includes(datasetType)) {

Replacement in src/worker/index.ts at line 448 [11.240196]

B:BD[3.151] → [3.151:678]

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

[3.151]

[3.678]

        error: `Field 'type' must be one of: ${validTypes.join(', ')}`
      }, 400);

Replacement in src/worker/index.ts at line 451 [11.240196]

∅:D[23.356] → [16.20852:20857]

∅:D[3.684] → [16.20852:20857]

∅:D[22.3187] → [16.20852:20857]

B:BD[16.20852] → [16.20852:20857]

B:BD[16.20857] → [3.685:778]

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

[3.684]

[3.778]


    // Create the dataset
    const now = new Date();
    const newDataset = {
      id: id.trim(),
      name: name.trim(),
      description: description?.trim() || null,
      public: Boolean(isPublic),
      type: datasetType,
      createdBy: userId,
      createdAt: now,
      lastModified: now,
      modifiedBy: userId,
      owner: userId,
      active: true,
    };
    // Insert the dataset
    const result = await db.insert(dataset).values(newDataset).returning({
      id: dataset.id,
      name: dataset.name,
      description: dataset.description,
      public: dataset.public,
      type: dataset.type,
      createdAt: dataset.createdAt,
      owner: dataset.owner,
    });
    console.log("Created dataset:", result[0].id, "for user:", userId);
    return c.json({
      data: result[0]
    }, 201);
  } catch (error) {
    console.error("Error creating dataset:", error);

Replacement in src/worker/index.ts at line 487 [11.240196]

B:BD[3.783] → [3.783:877]

    // Safety check - if no files found, return empty results
    if (fileIds.length === 0) {

[3.783]

[3.877]

    // Handle unique constraint violations
    if (error instanceof Error && error.message.includes('duplicate key')) {

Replacement in src/worker/index.ts at line 490 [11.240196]

B:BD[3.899] → [3.899:1426]

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

[3.899]

[16.23260]

        error: "A dataset with this ID already exists"
      }, 400);

Replacement in src/worker/index.ts at line 493 [11.240196]

B:BD[16.23266] → [16.23266:23271]

B:BD[16.23271] → [3.1427:3094]

∅:D[23.1496] → [16.23384:23403]

∅:D[3.3094] → [16.23384:23403]

∅:D[22.5351] → [16.23384:23403]

B:BD[16.23384] → [16.23384:23403]

B:BD[16.23403] → [3.3095:3439]

∅:D[3.3439] → [16.24137:24147]

B:BD[16.24137] → [16.24137:24147]

∅:D[23.1642] → [16.24147:24167]

∅:D[22.5455] → [16.24147:24167]

B:BD[16.24147] → [16.24147:24167]

B:BD[16.24167] → [3.3440:3536]

∅:D[23.1668] → [16.24272:24280]

∅:D[3.3536] → [16.24272:24280]

∅:D[22.5482] → [16.24272:24280]

B:BD[16.24272] → [16.24272:24280]

B:BD[16.24359] → [16.24359:24366]

B:BD[16.24366] → [3.3537:3928]

∅:D[23.1718] → [16.24419:24428]

∅:D[3.3928] → [16.24419:24428]

∅:D[22.5544] → [16.24419:24428]

B:BD[16.24419] → [16.24419:24428]

B:BD[16.24428] → [3.3929:4072]

∅:D[3.4072] → [16.24436:24441]

B:BD[16.24436] → [16.24436:24441]

B:BD[16.24441] → [3.4073:5118]

∅:D[3.5118] → [16.24510:24750]

B:BD[16.24510] → [16.24510:24750]

B:BD[16.24750] → [17.1587:1793]

B:BD[17.1793] → [22.5545:5583]

∅:D[17.1793] → [16.24750:24758]

∅:D[22.5583] → [16.24750:24758]

B:BD[16.24750] → [16.24750:24758]

B:BD[16.24758] → [3.5119:5127]

∅:D[3.5127] → [16.24906:24977]

B:BD[16.24906] → [16.24906:24977]

    
    // 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;
      
      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, {
      gain: string | null;
      batteryV: number | null;
      tempC: number | null;
    }>);
    
    // 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({
      data: files,
      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,
      }
    });
  } catch (error) {
    console.error("Error fetching files:", error);

[16.23266]

[16.24977]

Replacement in src/worker/index.ts at line 496 [11.240196]
B:BD[16.25004] → [16.25004:25044]
```
        error: "Failed to fetch files",
```
[16.25004]
[24.198]
```
        error: "Failed to create dataset",
```
Insertion in src/worker/index.ts at line 504 [11.240196]
[24.306]
[12.1868]

Replacement in src/worker/index.ts at line 506 [11.240196]

B:BD[12.1872] → [12.1872:1941]

 * Protected API route to fetch species and call types for a dataset

[12.1872]

[12.1941]

 * Protected API route to update an existing dataset

Replacement in src/worker/index.ts at line 508 [11.240196]
B:BD[12.1945] → [13.7628:7655]
```
 * @route GET /api/species
```
[12.1945]
[13.7655]
```
 * @route PUT /api/datasets/:id
```

Replacement in src/worker/index.ts at line 510 [11.240196]

B:BD[13.7683] → [13.7683:7784]

 * @param {string} datasetId - Required query parameter specifying the dataset to fetch species from

[13.7683]

[13.7784]

 * @param {string} id - Dataset ID in URL path
 * @body {Object} Dataset update payload:
 *   - name?: string (optional, max 255 chars)
 *   - description?: string (optional, max 255 chars)
 *   - public?: boolean (optional)
 *   - type?: string (optional)
 *   - active?: boolean (optional, for soft delete)

Replacement in src/worker/index.ts at line 518 [11.240196]

B:BD[13.7826] → [13.7826:8387]

 *   - data: Array of species objects, each with an array of associated call types
 * @error 400 - If datasetId is missing
 * @description Returns species associated with the specified dataset along with their call types
 *   Each species object includes:
 *   - id, label, ebirdCode, description
 *   - callTypes: Array of call type objects with id and label
 *   
 *   Uses an efficient JOIN approach to fetch data in a single query to avoid N+1 query problems
 *   Results are transformed to provide a nested structure with call types inside species objects

[13.7826]

[12.2096]

 *   - data: The updated dataset object
 * @error 400 - If fields are invalid or dataset not found
 * @error 403 - If user doesn't own the dataset
 * @error 500 - If database operation fails
 * @description Updates an existing dataset owned by the authenticated user
 *   Only the dataset owner can modify it

Replacement in src/worker/index.ts at line 525 [11.240196]
∅:D[12.2100] → [24.370:423]
B:BD[24.370] → [24.370:423]
```
app.get("/api/species", authenticate, async (c) => {
```
[12.2100]
[24.423]
```
app.put("/api/datasets/:id", authenticate, async (c) => {
```

Replacement in src/worker/index.ts at line 527 [11.240196]

B:BD[24.616] → [24.616:705]

    // Get query parameter for datasetId
    const datasetId = c.req.query("datasetId");

[24.431]

[24.705]

    // Get the JWT payload (user info)
    const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    const userId = jwtPayload.sub; // User ID from JWT
    // Get dataset ID from URL parameters
    const datasetId = c.req.param("id");

Deletion in src/worker/index.ts at line 534 [11.240196]
B:BD[24.710] → [24.710:737]
```
    // Validate parameters
```

Replacement in src/worker/index.ts at line 536 [11.240196]

B:BD[24.781] → [24.781:842]

        error: "Missing required query parameter: datasetId"

[24.781]

[24.842]

        error: "Missing dataset ID in URL"

Replacement in src/worker/index.ts at line 539 [11.240196]

B:BD[24.863] → [24.863:868]

[24.863]

[24.868]


    // Parse request body
    const body = await c.req.json();
    const { name, description, public: isPublic, type, active } = body;

Replacement in src/worker/index.ts at line 547 [11.240196]

B:BD[24.970] → [24.970:975]

B:BD[24.975] → [4.11:203]

    
    // Use JOIN approach to get species and call types in a single query
    // This eliminates the N+1 query problem and reduces round trips to the database
    const queryResults = await db

[24.970]

[24.1100]


    // First, check if the dataset exists and if the user owns it
    const existingDataset = await db

Replacement in src/worker/index.ts at line 551 [11.240196]

B:BD[24.1116] → [4.204:409]

        id: species.id,
        label: species.label,
        ebirdCode: species.ebirdCode,
        description: species.description,
        callTypeId: callType.id,
        callTypeLabel: callType.label

[24.1116]

[24.1160]

        id: dataset.id,
        owner: dataset.owner,
        active: dataset.active

Replacement in src/worker/index.ts at line 555 [11.240196]

B:BD[24.1169] → [4.410:743]

∅:D[4.743] → [24.1252:1257]

B:BD[24.1252] → [24.1252:1257]

B:BD[24.1257] → [4.744:835]

      .from(species)
      .innerJoin(
        speciesDataset,
        eq(species.id, speciesDataset.speciesId)
      )
      .leftJoin(
        callType,
        sqlExpr`${callType.speciesId} = ${species.id} AND ${callType.active} = true`
      )
      .where(eq(speciesDataset.datasetId, datasetId))
      .orderBy(species.label);
    
    // If no results are found, return an empty array
    if (queryResults.length === 0) {

[24.1169]

[24.1352]

      .from(dataset)
      .where(eq(dataset.id, datasetId))
      .limit(1);
    if (existingDataset.length === 0) {

Replacement in src/worker/index.ts at line 561 [11.240196]
B:BD[24.1374] → [24.1374:1401]
```
        data: []
      });
```
[24.1374]
[24.1401]
```
        error: "Dataset not found"
      }, 404);
```

Replacement in src/worker/index.ts at line 564 [11.240196]

B:BD[24.1407] → [24.1407:1412]

B:BD[24.1412] → [4.836:986]

    
    // Transform the flat results into nested objects
    // Using a Map for better performance with large datasets
    const speciesMap = new Map();

[24.1407]

[24.1588]


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

Replacement in src/worker/index.ts at line 568 [11.240196]

B:BD[24.1593] → [4.987:1317]

    queryResults.forEach(row => {
      if (!speciesMap.has(row.id)) {
        // Create a new species entry if not already in the map
        speciesMap.set(row.id, {
          id: row.id,
          label: row.label,
          ebirdCode: row.ebirdCode,
          description: row.description,
          callTypes: []
        });

[24.1593]

[24.2569]

    if (!hasEditPermission) {
      return c.json({
        error: "You don't have permission to modify this dataset"
      }, 403);
    }
    // Validate fields if provided
    if (name !== undefined) {
      if (typeof name !== 'string' || name.trim().length === 0) {
        return c.json({
          error: "Invalid field: name must be a non-empty string"
        }, 400);

Replacement in src/worker/index.ts at line 582 [11.240196]

B:BD[24.2584] → [4.1318:1742]

      // Add the call type if it exists and isn't already in the array
      if (row.callTypeId) {
        const species = speciesMap.get(row.id);
        const existingCallType = species.callTypes.find((ct: { id: string }) => ct.id === row.callTypeId);
        
        if (!existingCallType) {
          species.callTypes.push({
            id: row.callTypeId,
            label: row.callTypeLabel
          });
        }

[24.2584]

[4.1742]

      if (name.length > 255) {
        return c.json({
          error: "Field 'name' must be 255 characters or less"
        }, 400);

Replacement in src/worker/index.ts at line 587 [11.240196]

∅:D[4.1750] → [24.2713:2726]

B:BD[24.2713] → [24.2713:2726]

B:BD[24.2726] → [4.1751:1840]

∅:D[4.1840] → [24.3045:3146]

B:BD[24.3045] → [24.3045:3146]

B:BD[24.3151] → [24.3151:3293]

∅:D[24.3293] → [16.25044:25151]

B:BD[16.25044] → [16.25044:25151]

    });
    
    // Convert map to array
    const enrichedSpecies = Array.from(speciesMap.values());
    
    // 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",
        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});

[4.1750]

[16.25151]

Replacement in src/worker/index.ts at line 589 [11.240196]

B:BD[16.25152] → [12.2101:2105]

B:BD[12.2105] → [9.101:741]

/**
 * Protected API route to search eBird taxonomy
 * 
 * @route GET /api/ebird/search
 * @authentication Required
 * @param {string} q - Search query (common name, scientific name, family, or species code)
 * @returns {Object} Response containing:
 *   - data: Array of matching eBird taxonomy entries
 * @description Searches the eBird taxonomy v2024 materialized view for species matching the query.
 *   Searches across primary_com_name, sci_name, family, and species_code fields.
 */
app.get("/api/ebird/search", authenticate, async (c) => {
  try {
    const query = c.req.query("q");
    
    if (!query || query.trim().length === 0) {

[16.25152]

[9.741]

    if (description !== undefined && description !== null && description.length > 255) {

Replacement in src/worker/index.ts at line 591 [11.240196]

B:BD[9.763] → [9.763:817]

        error: "Missing or empty query parameter 'q'"

[9.763]

[9.817]

        error: "Field 'description' must be 255 characters or less"

Replacement in src/worker/index.ts at line 595 [11.240196]

B:BD[9.839] → [9.839:962]

    if (query.length < 2) {
      return c.json({
        error: "Query must be at least 2 characters long"
      }, 400);

[9.839]

[9.962]

    if (type !== undefined) {
      const validTypes = ['organise', 'test', 'train'];
      if (!validTypes.includes(type)) {
        return c.json({
          error: `Field 'type' must be one of: ${validTypes.join(', ')}`
        }, 400);
      }

Replacement in src/worker/index.ts at line 604 [11.240196]

B:BD[9.969] → [9.969:1067]

    // Database connection
    const sql = neon(c.env.DATABASE_URL);
    const db = drizzle(sql);

[9.969]

[9.1067]

    // Build update object with only provided fields
    const updateData: Record<string, unknown> = {
      lastModified: new Date(),
      modifiedBy: userId,
    };

Replacement in src/worker/index.ts at line 610 [11.240196]

B:BD[9.1068] → [9.1068:1202]

    // Search across multiple fields with case-insensitive partial matching
    const searchTerm = `%${query.trim().toLowerCase()}%`;

[9.1068]

[9.1202]

    if (name !== undefined) {
      updateData.name = name.trim();
    }

Replacement in src/worker/index.ts at line 614 [11.240196]

B:BD[9.1207] → [9.1207:1869]

    // Use raw SQL query for materialized view until Drizzle typing issue is resolved
    const results = await db.execute(
      sqlExpr`
        SELECT 
          id,
          species_code as "speciesCode",
          primary_com_name as "primaryComName", 
          sci_name as "sciName",
          bird_order as "birdOrder",
          family
        FROM ebird_taxonomy_v2024
        WHERE 
          LOWER(primary_com_name) LIKE ${searchTerm} OR
          LOWER(sci_name) LIKE ${searchTerm} OR  
          LOWER(family) LIKE ${searchTerm} OR
          LOWER(species_code) LIKE ${searchTerm}
        ORDER BY primary_com_name
        LIMIT 20
      `
    );

[9.1207]

[9.1869]

    if (description !== undefined) {
      updateData.description = description?.trim() || null;
    }
    
    if (isPublic !== undefined) {
      updateData.public = Boolean(isPublic);
    }
    
    if (type !== undefined) {
      updateData.type = type;
    }
    
    if (active !== undefined) {
      updateData.active = Boolean(active);
    }
    // Update the dataset
    const result = await db
      .update(dataset)
      .set(updateData)
      .where(eq(dataset.id, datasetId))
      .returning({
        id: dataset.id,
        name: dataset.name,
        description: dataset.description,
        public: dataset.public,
        type: dataset.type,
        createdAt: dataset.createdAt,
        lastModified: dataset.lastModified,
        owner: dataset.owner,
        active: dataset.active,
      });
    if (result.length === 0) {
      return c.json({
        error: "Failed to update dataset"
      }, 500);
    }
    console.log("Updated dataset:", result[0].id, "for user:", userId);

Replacement in src/worker/index.ts at line 656 [11.240196]
B:BD[9.1890] → [9.1890:1926]
```
      data: results.rows || results
```
[9.1890]
[9.1926]
```
      data: result[0]
```

Replacement in src/worker/index.ts at line 659 [11.240196]

B:BD[9.1954] → [9.1954:2015]

    console.error("Error searching eBird taxonomy:", error);

[9.1954]

[9.2015]

    console.error("Error updating dataset:", error);

Replacement in src/worker/index.ts at line 663 [11.240196]

B:BD[9.2047] → [9.2047:2097]

        error: "Failed to search eBird taxonomy",

[9.2047]

[9.2097]

        error: "Failed to update dataset",

Insertion in src/worker/index.ts at line 671 [11.240196]

[9.2205]


// ============================================================================
// LOCATIONS
// ============================================================================

Replacement in src/worker/index.ts at line 677 [11.240196]

∅:D[9.2209] → [12.2105:2194]

B:BD[12.2105] → [12.2105:2194]

 * Protected API route to fetch files with selections for a specific dataset and species

[9.2209]

[12.2194]

 * Protected API route to fetch locations for a specific dataset

Replacement in src/worker/index.ts at line 679 [11.240196]
B:BD[12.2198] → [6.11:40]
```
 * @route GET /api/selection
```
[12.2198]
[13.8418]
```
 * @route GET /api/locations
```

Replacement in src/worker/index.ts at line 681 [11.240196]

B:BD[13.8446] → [13.8446:8617]

 * @param {string} datasetId - Required query parameter specifying the dataset
 * @param {string} speciesId - Required query parameter specifying the species to filter by

[13.8446]

[13.8617]

 * @param {string} datasetId - Required query parameter specifying the dataset to fetch locations from

Replacement in src/worker/index.ts at line 683 [11.240196]

B:BD[13.8697] → [13.8697:8989]

 * @param {number} [pageSize=100] - Optional page size (10-500, defaults to 100)
 * @param {string} [solarNight] - Optional filter for files recorded during solar night ('true'/'false') 
 * @param {string} [civilNight] - Optional filter for files recorded during civil night ('true'/'false')

[13.8697]

[13.8989]

 * @param {number} [pageSize=100] - Optional page size (1-100, defaults to 100)

Replacement in src/worker/index.ts at line 685 [11.240196]

B:BD[13.9031] → [13.9031:9905]

 *   - data: Array of file objects with metadata, mothMetadata, and species information
 *   - pagination: Object with pagination metadata
 *   - filters: Object showing the filters that were applied
 * @error 400 - If datasetId or speciesId is missing or page is invalid
 * @description Returns files that contain selections labeled with the specified species
 *   Designed for cross-cluster searches within a dataset
 *   
 *   Each file object includes:
 *   - Basic file information (name, path, timestamp, duration, etc.)
 *   - File metadata (JSON format)
 *   - Recording device metadata (gain, battery voltage, temperature)
 *   - Species information
 *   
 *   Uses efficient query optimization with:
 *   - Parallel Promise.all for metadata queries
 *   - Proper SQL JOINs to avoid N+1 query problems
 *   - Data transformation for optimal client-side consumption

[13.9031]

[12.2377]

 *   - data: Array of location objects with id, name, latitude, longitude, description
 *   - pagination: Object with pagination metadata (currentPage, pageSize, totalPages, totalItems, etc.)
 * @error 400 - If datasetId is missing or page is invalid
 * @description Returns active locations for the specified dataset with pagination support

Replacement in src/worker/index.ts at line 690 [11.240196]

B:BD[12.2381] → [6.41:96]

app.get("/api/selection", authenticate, async (c) => {

[12.2381]

[25.27334]

app.get("/api/locations", authenticate, async (c) => {

Replacement in src/worker/index.ts at line 692 [11.240196]

B:BD[25.27342] → [25.27342:27370]

    // Get query parameters

[25.27342]

[25.27370]

    // Get the JWT payload (user info)
    const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    const userId = jwtPayload.sub; // User ID from JWT // Subject claim usually contains the user ID
    
    // Get the dataset ID from query parameter

Deletion in src/worker/index.ts at line 698 [11.240196]
B:BD[25.27418] → [25.27418:27466]
```
    const speciesId = c.req.query("speciesId");
```

Replacement in src/worker/index.ts at line 700 [11.240196]

B:BD[25.27594] → [25.27594:27694]

    const solarNight = c.req.query("solarNight");
    const civilNight = c.req.query("civilNight");

[25.27594]

[25.27694]

    
    console.log("Locations API called with datasetId:", datasetId, "userId:", userId, "page:", page);

Deletion in src/worker/index.ts at line 703 [11.240196]
B:BD[25.27699] → [25.27699:27726]
```
    // Validate parameters
```
Insertion in src/worker/index.ts at line 704 [11.240196]
[25.27748]
[25.27748]
```
      console.log("Missing datasetId in request");
```

Deletion in src/worker/index.ts at line 707 [11.240196]

B:BD[25.27831] → [25.27831:27962]

      }, 400);
    }
    
    if (!speciesId) {
      return c.json({
        error: "Missing required query parameter: speciesId"

Deletion in src/worker/index.ts at line 709 [11.240196]

B:BD[25.27983] → [25.27983:28169]

    
    // Validate and limit page size to prevent excessive queries
    const limitedPageSize = Math.min(Math.max(pageSize, 10), 500);
    const offset = (page - 1) * limitedPageSize;

Insertion in src/worker/index.ts at line 717 [11.240196]

[25.28334]

    // Limit page size between 1 and 100
    const limitedPageSize = Math.min(Math.max(pageSize, 1), 100);
    const offset = (page - 1) * limitedPageSize;

Replacement in src/worker/index.ts at line 725 [11.240196]

B:BD[25.28441] → [5.11:360]

∅:D[5.360] → [25.28611:28616]

B:BD[25.28611] → [25.28611:28616]

B:BD[25.28616] → [5.361:407]

∅:D[5.407] → [25.28663:28696]

B:BD[25.28663] → [25.28663:28696]

B:BD[25.28696] → [5.408:492]

∅:D[5.492] → [25.28784:28825]

B:BD[25.28784] → [25.28784:28825]

B:BD[25.28825] → [5.493:615]

∅:D[5.615] → [25.28951:28962]

B:BD[25.28951] → [25.28951:28962]

B:BD[25.29009] → [25.29009:29042]

B:BD[25.29042] → [5.616:700]

∅:D[5.700] → [25.29130:29171]

B:BD[25.29130] → [25.29130:29171]

B:BD[25.29171] → [5.701:823]

∅:D[5.823] → [25.29297:29308]

B:BD[25.29297] → [25.29297:29308]

B:BD[25.29308] → [5.824:894]

    // 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 day/night if specified
    if (solarNight === 'true') {
      baseCondition = sqlExpr`${baseCondition} AND ${file.maybeSolarNight} = true`;
    } else if (solarNight === 'false') {
      baseCondition = sqlExpr`${baseCondition} AND (${file.maybeSolarNight} = false OR ${file.maybeSolarNight} IS NULL)`;
    }
    
    if (civilNight === 'true') {
      baseCondition = sqlExpr`${baseCondition} AND ${file.maybeCivilNight} = true`;
    } else if (civilNight === 'false') {
      baseCondition = sqlExpr`${baseCondition} AND (${file.maybeCivilNight} = false OR ${file.maybeCivilNight} IS NULL)`;
    }
    
    // Get total count for pagination using a more efficient COUNT(1)

[25.28441]

[25.29440]

    // First, get total count for pagination
    console.log("Counting locations for datasetId:", datasetId);

Replacement in src/worker/index.ts at line 729 [11.240196]

B:BD[25.29489] → [25.29489:29548]

        count: sqlExpr<number>`COUNT(DISTINCT ${file.id})`

[25.29489]

[25.29548]

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

Replacement in src/worker/index.ts at line 731 [11.240196]

B:BD[25.29557] → [25.29557:29817]

B:BD[25.29817] → [5.895:924]

      .from(file)
      .innerJoin(cluster, eq(file.clusterId, cluster.id))
      .innerJoin(location, eq(cluster.locationId, location.id))
      .innerJoin(selection, eq(selection.fileId, file.id))
      .innerJoin(label, eq(label.selectionId, selection.id))
      .where(baseCondition);

[25.29557]

[25.30008]

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

Replacement in src/worker/index.ts at line 734 [11.240196]

B:BD[25.30013] → [25.30013:30130]

    const totalFiles = Number(countResult[0].count);
    const totalPages = Math.ceil(totalFiles / limitedPageSize);

[25.30013]

[25.30130]

    const totalLocations = Number(countResult[0].count);
    const totalPages = Math.ceil(totalLocations / limitedPageSize);

Replacement in src/worker/index.ts at line 737 [11.240196]

B:BD[25.30135] → [5.925:1568]

∅:D[5.1568] → [25.30225:30977]

B:BD[25.30225] → [25.30225:30977]

B:BD[25.30977] → [5.1569:1597]

∅:D[5.1597] → [25.31167:31435]

B:BD[25.31167] → [25.31167:31435]

    // 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
    const filesResult = await db
      .select({
        id: file.id,
        fileName: file.fileName,
        path: file.path,
        timestampLocal: file.timestampLocal,
        duration: file.duration,
        sampleRate: file.sampleRate,
        locationId: file.locationId,
        clusterId: file.clusterId,
        description: file.description,
        maybeSolarNight: file.maybeSolarNight,
        maybeCivilNight: file.maybeCivilNight,
        moonPhase: file.moonPhase,
      })
      .from(file)
      .innerJoin(cluster, eq(file.clusterId, cluster.id))
      .innerJoin(location, eq(cluster.locationId, location.id))
      .innerJoin(selection, eq(selection.fileId, file.id))
      .innerJoin(label, eq(label.selectionId, selection.id))
      .where(baseCondition)
      .orderBy(file.timestampLocal)
      .groupBy(file.id, file.fileName, file.path, file.timestampLocal, file.duration, 
        file.sampleRate, file.locationId, file.clusterId, file.description, file.maybeSolarNight, 
        file.maybeCivilNight, file.moonPhase)

[25.30135]

[25.31435]

    // Query locations for the specified dataset with pagination (using 100 as default limit)
    console.log("Querying locations for datasetId:", datasetId, "page:", page);
    const results = await db.select({
      id: location.id,
      name: location.name,
      latitude: location.latitude,
      longitude: location.longitude,
      description: location.description,
    }).from(location)
      .where(sqlExpr`${location.datasetId} = ${datasetId} AND ${location.active} = true`)
      .orderBy(location.name)

Deletion in src/worker/index.ts at line 750 [11.240196]
B:BD[25.31488] → [25.31488:31495]
B:BD[25.31495] → [5.1598:1643]
∅:D[5.1643] → [25.31537:31585]
B:BD[25.31537] → [25.31537:31585]
```
      
    // Get all file IDs for metadata queries
    const fileIds = filesResult.map(f => f.id);
```

Replacement in src/worker/index.ts at line 751 [11.240196]

B:BD[25.31590] → [5.1644:3787]

    // 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
      `)
    ]);

[25.31590]

[5.3787]

    console.log("Found", results.length, "locations for dataset", datasetId, "page:", page);

Deletion in src/worker/index.ts at line 753 [11.240196]

B:BD[5.3792] → [5.3792:4909]

∅:D[5.4909] → [25.31754:31840]

B:BD[25.31754] → [25.31754:31840]

B:BD[25.31840] → [5.4910:5031]

∅:D[5.5031] → [25.31896:32001]

B:BD[25.31896] → [25.31896:32001]

B:BD[25.32001] → [5.5032:5043]

∅:D[5.5043] → [25.32015:32020]

B:BD[25.32015] → [25.32015:32020]

B:BD[25.32020] → [5.5044:5164]

∅:D[5.5164] → [25.36541:36549]

B:BD[25.36541] → [25.36541:36549]

B:BD[25.36908] → [25.36908:36915]

B:BD[25.36915] → [5.5165:5727]

∅:D[5.5727] → [25.38094:38102]

B:BD[25.38094] → [25.38094:38102]

B:BD[25.38233] → [25.38233:38246]

B:BD[25.38246] → [5.5728:6086]

    // 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, {
      gain: string | null;
      batteryV: number | null;
      tempC: number | null;
    }>);
    
    // Process species data with Map for better performance
    const speciesMap = new Map<string, Array<{
      id: string;
      label: string;
      ebirdCode: string | null;
      description: string | null;
    }>>();
    
    speciesResults.forEach(item => {
      if (!speciesMap.has(item.fileId)) {
        speciesMap.set(item.fileId, []);
      }
      
      // 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
        });
      }
    });
    
    // 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

Replacement in src/worker/index.ts at line 754 [11.240196]
∅:D[5.6106] → [25.38335:38354]
B:BD[25.38335] → [25.38335:38354]
```
      data: files,
```
[5.6106]
[25.38354]
```
      data: results,
```
Replacement in src/worker/index.ts at line 759 [11.240196]
B:BD[25.38468] → [25.38468:38500]
```
        totalItems: totalFiles,
```
[25.38468]
[25.38500]
```
        totalItems: totalLocations,
```

Deletion in src/worker/index.ts at line 762 [11.240196]

B:BD[25.38575] → [25.38575:38819]

      },
      filters: {
        datasetId,
        speciesId,
        solarNight: solarNight === 'true' ? true : solarNight === 'false' ? false : null,
        civilNight: civilNight === 'true' ? true : civilNight === 'false' ? false : null,

Replacement in src/worker/index.ts at line 765 [11.240196]

B:BD[25.38888] → [25.38888:38962]

    console.error("Error fetching files by dataset and species:", error);

[25.38888]

[25.38962]

    console.error("Error fetching locations:", error);

Replacement in src/worker/index.ts at line 768 [11.240196]

B:BD[25.38989] → [25.38989:39052]

        error: "Failed to fetch files by dataset and species",

[25.38989]

[25.39052]

        error: "Failed to fetch locations",

Deletion in src/worker/index.ts at line 775 [11.240196]

B:BD[25.39159] → [25.39159:39160]

B:BD[25.39160] → [12.2382:2386]

B:BD[12.2386] → [26.11:982]

B:BD[26.982] → [7.5307:5945]

∅:D[7.5945] → [26.982:2216]

B:BD[26.982] → [26.982:2216]

B:BD[26.2319] → [26.2319:3088]


/**
 * Protected API route to create a new dataset
 * 
 * @route POST /api/datasets
 * @authentication Required
 * @body {Object} Dataset creation payload:
 *   - id: string (nanoid(12) - user generated)
 *   - name: string (required, max 255 chars)
 *   - description?: string (optional, max 255 chars)
 *   - public?: boolean (optional, defaults to false)
 *   - type?: string (optional, defaults to 'organise')
 * @returns {Object} Response containing:
 *   - data: The created dataset object
 * @error 400 - If required fields are missing or invalid
 * @error 500 - If database operation fails
 * @description Creates a new dataset for the authenticated user
 *   The user becomes the owner, creator, and modifier of the dataset
 */
app.post("/api/datasets", 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
    // Connect to the database first to check permissions
    const sql = neon(c.env.DATABASE_URL);
    const db = drizzle(sql);
    
    // Check if user has permission to create datasets (ADMIN or CURATOR roles)
    const userRoleResult = await db
      .select({ role: userRole.role })
      .from(userRole)
      .where(eq(userRole.userId, userId))
      .limit(1);
    
    const userRoleName = userRoleResult.length > 0 ? userRoleResult[0].role : 'USER';
    
    if (userRoleName !== 'ADMIN' && userRoleName !== 'CURATOR') {
      return c.json({
        error: "You don't have permission to create datasets"
      }, 403);
    }
    // Parse request body
    const body = await c.req.json();
    const { id, name, description, public: isPublic, type } = body;
    // Validate required fields
    if (!id || typeof id !== 'string') {
      return c.json({
        error: "Missing or invalid required field: id"
      }, 400);
    }
    if (!name || typeof name !== 'string' || name.trim().length === 0) {
      return c.json({
        error: "Missing or invalid required field: name"
      }, 400);
    }
    // Validate field lengths
    if (id.length !== 12) {
      return c.json({
        error: "Field 'id' must be exactly 12 characters (nanoid)"
      }, 400);
    }
    if (name.length > 255) {
      return c.json({
        error: "Field 'name' must be 255 characters or less"
      }, 400);
    }
    if (description && description.length > 255) {
      return c.json({
        error: "Field 'description' must be 255 characters or less"
      }, 400);
    }
    // Validate type if provided
    const validTypes = ['organise', 'test', 'train'];
    const datasetType = type || 'organise';
    if (!validTypes.includes(datasetType)) {
      return c.json({
        error: `Field 'type' must be one of: ${validTypes.join(', ')}`
      }, 400);
    }
    // Create the dataset
    const now = new Date();
    const newDataset = {
      id: id.trim(),
      name: name.trim(),
      description: description?.trim() || null,
      public: Boolean(isPublic),
      type: datasetType,
      createdBy: userId,
      createdAt: now,
      lastModified: now,
      modifiedBy: userId,
      owner: userId,
      active: true,
    };
    // Insert the dataset
    const result = await db.insert(dataset).values(newDataset).returning({
      id: dataset.id,
      name: dataset.name,
      description: dataset.description,
      public: dataset.public,
      type: dataset.type,
      createdAt: dataset.createdAt,
      owner: dataset.owner,
    });
    console.log("Created dataset:", result[0].id, "for user:", userId);

Deletion in src/worker/index.ts at line 776 [11.240196]

B:BD[26.3089] → [26.3089:3440]

    return c.json({
      data: result[0]
    }, 201);
  } catch (error) {
    console.error("Error creating dataset:", error);
    
    // Handle unique constraint violations
    if (error instanceof Error && error.message.includes('duplicate key')) {
      return c.json({
        error: "A dataset with this ID already exists"
      }, 400);
    }

Deletion in src/worker/index.ts at line 777 [11.240196]

B:BD[26.3441] → [26.3441:3511]

B:BD[26.3511] → [27.11:118]

    return c.json(
      {
        error: "Failed to create dataset",
        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});

Insertion in src/worker/index.ts at line 924 [11.240196]
[28.119]
[28.119]

Insertion in src/worker/index.ts at line 1092 [11.240196]

[28.5104]

[8.11]

        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});
// ============================================================================
// CLUSTERS
// ============================================================================
/**
 * Protected API route to fetch clusters for a specific location
 * 
 * @route GET /api/clusters
 * @authentication Required
 * @param {string} locationId - Required query parameter specifying the location to fetch clusters from
 * @returns {Object} Response containing:
 *   - data: Array of cluster objects with recording pattern information
 * @error 400 - If locationId is missing
 * @description Returns clusters for the specified location along with their recording patterns
 *   Performs a LEFT JOIN with the cyclicRecordingPattern table to include recording duration data
 *   Results are returned with recording pattern information embedded in each cluster object
 */
app.get("/api/clusters", 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 // Subject claim usually contains the user ID
    
    // Get the location ID from query parameter
    const locationId = c.req.query("locationId");
    
    console.log("Cluster API called with locationId:", locationId, "userId:", userId);
    
    if (!locationId) {
      console.log("Missing locationId in request");
      return c.json({
        error: "Missing required query parameter: locationId"
      }, 400);
    }
    
    // Connect to the database
    const sql = neon(c.env.DATABASE_URL);
    const db = drizzle(sql);
    
    // Query clusters with a LEFT JOIN on recording patterns to avoid N+1 queries
    console.log("Querying clusters for locationId:", locationId);
    const joinedResults = await db.select({
      // Cluster fields
      id: cluster.id,
      datasetId: cluster.datasetId,
      locationId: cluster.locationId,
      name: cluster.name,
      description: cluster.description,
      createdBy: cluster.createdBy,
      createdAt: cluster.createdAt,
      lastModified: cluster.lastModified,
      modifiedBy: cluster.modifiedBy,
      active: cluster.active,
      timezoneId: cluster.timezoneId,
      cyclicRecordingPatternId: cluster.cyclicRecordingPatternId,
      sampleRate: cluster.sampleRate,
      // Recording pattern fields
      recordS: cyclicRecordingPattern.recordS,
      sleepS: cyclicRecordingPattern.sleepS
    })
    .from(cluster)
    .leftJoin(
      cyclicRecordingPattern, 
      eq(cluster.cyclicRecordingPatternId, cyclicRecordingPattern.id)
    )
    .where(eq(cluster.locationId, locationId))
    .orderBy(cluster.name);
    
    console.log("Found", joinedResults.length, "clusters for location", locationId);
    
    // Transform results to match the expected structure
    const enrichedResults = joinedResults.map(row => ({
      id: row.id,
      datasetId: row.datasetId,
      locationId: row.locationId,
      name: row.name,
      description: row.description,
      createdBy: row.createdBy,
      createdAt: row.createdAt,
      lastModified: row.lastModified,
      modifiedBy: row.modifiedBy,
      active: row.active,
      timezoneId: row.timezoneId,
      cyclicRecordingPatternId: row.cyclicRecordingPatternId,
      sampleRate: row.sampleRate,
      recordingPattern: (row.recordS !== null && row.sleepS !== null) ? {
        recordS: row.recordS,
        sleepS: row.sleepS
      } : null
    }));
    
    return c.json({
      data: enrichedResults
    });
  } catch (error) {
    console.error("Error fetching clusters:", error);
    return c.json(
      {
        error: "Failed to fetch clusters",

Insertion in src/worker/index.ts at line 1206 [11.240196]
[8.119]
[8.119]
Insertion in src/worker/index.ts at line 1356 [11.240196]
[8.4556]
[8.4556]

Insertion in src/worker/index.ts at line 1521 [11.240196]

[8.9524]

[29.11]

        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});
// ============================================================================
// FILES
// ============================================================================
/**
 * Protected API route to fetch audio files for a specific cluster
 * 
 * @route GET /api/files
 * @authentication Required
 * @param {string} clusterId - Required query parameter specifying the cluster to fetch files from
 * @param {number} [page=1] - Optional page number for pagination (starts at 1)
 * @param {number} [pageSize=100] - Optional page size (10-500, defaults to 100)
 * @param {string} [solarNight] - Optional filter for files recorded during solar night ('true'/'false')
 * @param {string} [civilNight] - Optional filter for files recorded during civil night ('true'/'false')
 * @param {string} [speciesId] - Optional filter for files with selections labeled with specific species
 * @returns {Object} Response containing:
 *   - data: Array of file objects with metadata, mothMetadata, and species information
 *   - pagination: Object with pagination metadata
 *   - filters: Object showing the filters that were applied
 * @error 400 - If clusterId is missing or page is invalid
 * @description Returns audio files for the specified cluster with comprehensive metadata:
 *   - Basic file information (name, path, timestamp, duration, etc.)
 *   - File metadata (JSON format)
 *   - Recording device metadata (gain, battery voltage, temperature)
 *   - Species found in each file
 *   
 *   When speciesId is provided, only returns files that have at least one
 *   selection labeled with the specified species.
 */
app.get("/api/files", authenticate, async (c) => {
  try {
    // Get query parameters
    const clusterId = c.req.query("clusterId");
    const page = parseInt(c.req.query("page") || "1", 10);
    const pageSize = parseInt(c.req.query("pageSize") || "100", 10);
    const solarNight = c.req.query("solarNight");
    const civilNight = c.req.query("civilNight");
    const speciesId = c.req.query("speciesId"); // Optional filter for species
    
    // Validate parameters
    if (!clusterId) {
      console.log("Missing clusterId in request");
      return c.json({
        error: "Missing required query parameter: clusterId"
      }, 400);
    }
    
    // Validate and limit page size to prevent excessive queries
    const limitedPageSize = Math.min(Math.max(pageSize, 10), 500);
    const offset = (page - 1) * limitedPageSize;
    
    // Validate page number
    if (page < 1) {
      console.log("Invalid page number in request:", page);
      return c.json({
        error: "Invalid page parameter: must be greater than 0"
      }, 400);
    }
    
    // Connect to the database
    const sql = neon(c.env.DATABASE_URL);
    const db = drizzle(sql);
    
    // Build filter conditions
    let whereConditions = sqlExpr`${file.clusterId} = ${clusterId} AND ${file.active} = true`;
    
    // Add filters for solarNight if specified
    if (solarNight === 'true') {
      whereConditions = sqlExpr`${whereConditions} AND ${file.maybeSolarNight} = true`;
    } else if (solarNight === 'false') {
      whereConditions = sqlExpr`${whereConditions} AND (${file.maybeSolarNight} = false OR ${file.maybeSolarNight} IS NULL)`;
    }
    
    // Add filters for civilNight if specified
    if (civilNight === 'true') {
      whereConditions = sqlExpr`${whereConditions} AND ${file.maybeCivilNight} = true`;
    } else if (civilNight === 'false') {
      whereConditions = sqlExpr`${whereConditions} AND (${file.maybeCivilNight} = false OR ${file.maybeCivilNight} IS NULL)`;
    }
    
    // First, get the total count of files for pagination metadata
    let countResult;
    
    if (speciesId) {
      // Count only files that have at least one selection labeled with the specified species
      countResult = await db
        .select({
          count: sqlExpr<number>`COUNT(DISTINCT ${file.id})`
        })
        .from(file)
        .innerJoin(selection, eq(selection.fileId, file.id))
        .innerJoin(label, eq(label.selectionId, selection.id))
        .where(sqlExpr`${whereConditions} AND ${label.speciesId} = ${speciesId} AND ${label.active} = true`);
    } else {
      // Standard count without species filter
      countResult = await db
        .select({
          count: sqlExpr<number>`COUNT(1)`
        })
        .from(file)
        .where(whereConditions);
    }
    
    const totalFiles = Number(countResult[0].count);
    const totalPages = Math.ceil(totalFiles / limitedPageSize);
    
    // Query files for the specified cluster with pagination
    let filesResult;
    
    if (speciesId) {
      // Get only files that have at least one selection labeled with the specified species
      filesResult = await db
        .select({
          id: file.id,
          fileName: file.fileName,
          path: file.path,
          timestampLocal: file.timestampLocal,
          duration: file.duration,
          sampleRate: file.sampleRate,
          locationId: file.locationId,
          description: file.description,
          maybeSolarNight: file.maybeSolarNight,
          maybeCivilNight: file.maybeCivilNight,
          moonPhase: file.moonPhase,
        })
        .from(file)
        .innerJoin(selection, eq(selection.fileId, file.id))
        .innerJoin(label, eq(label.selectionId, selection.id))
        .where(sqlExpr`${whereConditions} AND ${label.speciesId} = ${speciesId} AND ${label.active} = true`)
        .orderBy(file.timestampLocal)
        .groupBy(file.id, file.fileName, file.path, file.timestampLocal, file.duration, 
          file.sampleRate, file.locationId, file.description, file.maybeSolarNight, 
          file.maybeCivilNight, file.moonPhase)
        .limit(limitedPageSize)
        .offset(offset);
    } else {
      // Standard query without species filter
      filesResult = await db
        .select({
          id: file.id,
          fileName: file.fileName,
          path: file.path,
          timestampLocal: file.timestampLocal,
          duration: file.duration,
          sampleRate: file.sampleRate,
          locationId: file.locationId,
          description: file.description,
          maybeSolarNight: file.maybeSolarNight,
          maybeCivilNight: file.maybeCivilNight,
          moonPhase: file.moonPhase,
        })
        .from(file)
        .where(whereConditions)
        .orderBy(file.timestampLocal)
        .limit(limitedPageSize)
        .offset(offset);
    }
    
    // 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,
        }
      });
    }
    
    // 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,
        }
      });
    }
    
    // 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;
      
      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, {
      gain: string | null;
      batteryV: number | null;
      tempC: number | null;
    }>);
    
    // 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({
      data: files,
      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,
      }
    });
  } catch (error) {
    console.error("Error fetching files:", error);
    return c.json(
      {
        error: "Failed to fetch files",

Insertion in src/worker/index.ts at line 1866 [11.240196]
[29.118]
[29.118]

Insertion in src/worker/index.ts at line 1868 [11.240196]

[29.119]

// ============================================================================
// SPECIES
// ============================================================================

Insertion in src/worker/index.ts at line 1873 [11.240196]

[29.123]

[9.2210]

 * Protected API route to fetch species and call types for a dataset
 * 
 * @route GET /api/species
 * @authentication Required
 * @param {string} datasetId - Required query parameter specifying the dataset to fetch species from
 * @returns {Object} Response containing:
 *   - data: Array of species objects, each with an array of associated call types
 * @error 400 - If datasetId is missing
 * @description Returns species associated with the specified dataset along with their call types
 *   Each species object includes:
 *   - id, label, ebirdCode, description
 *   - callTypes: Array of call type objects with id and label
 *   
 *   Uses an efficient JOIN approach to fetch data in a single query to avoid N+1 query problems
 *   Results are transformed to provide a nested structure with call types inside species objects
 */
app.get("/api/species", authenticate, async (c) => {
  try {
    // 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);
    
    // Use JOIN approach to get species and call types in a single query
    // This eliminates the N+1 query problem and reduces round trips to the database
    const queryResults = await db
      .select({
        id: species.id,
        label: species.label,
        ebirdCode: species.ebirdCode,
        description: species.description,
        callTypeId: callType.id,
        callTypeLabel: callType.label
      })
      .from(species)
      .innerJoin(
        speciesDataset,
        eq(species.id, speciesDataset.speciesId)
      )
      .leftJoin(
        callType,
        sqlExpr`${callType.speciesId} = ${species.id} AND ${callType.active} = true`
      )
      .where(eq(speciesDataset.datasetId, datasetId))
      .orderBy(species.label);
    
    // If no results are found, return an empty array
    if (queryResults.length === 0) {
      return c.json({
        data: []
      });
    }
    
    // Transform the flat results into nested objects
    // Using a Map for better performance with large datasets
    const speciesMap = new Map();
    
    queryResults.forEach(row => {
      if (!speciesMap.has(row.id)) {
        // Create a new species entry if not already in the map
        speciesMap.set(row.id, {
          id: row.id,
          label: row.label,
          ebirdCode: row.ebirdCode,
          description: row.description,
          callTypes: []
        });
      }
      
      // Add the call type if it exists and isn't already in the array
      if (row.callTypeId) {
        const species = speciesMap.get(row.id);
        const existingCallType = species.callTypes.find((ct: { id: string }) => ct.id === row.callTypeId);
        
        if (!existingCallType) {
          species.callTypes.push({
            id: row.callTypeId,
            label: row.callTypeLabel
          });
        }
      }
    });
    
    // Convert map to array
    const enrichedSpecies = Array.from(speciesMap.values());
    
    // 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",
        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});
/**

Insertion in src/worker/index.ts at line 2369 [11.240196]

[9.13984]


// ============================================================================
// CALL-TYPES
// ============================================================================

Insertion in src/worker/index.ts at line 2623 [11.240196]

[9.21176]


// ============================================================================
// EBIRD SEARCH
// ============================================================================

Replacement in src/worker/index.ts at line 2629 [11.240196]
∅:D[9.21181] → [29.123:176]
B:BD[29.123] → [29.123:176]
```
 * Protected API route to update an existing dataset
```
[9.21181]
[29.176]
```
 * Protected API route to search eBird taxonomy
```
Replacement in src/worker/index.ts at line 2631 [11.240196]
B:BD[29.180] → [29.180:212]
```
 * @route PUT /api/datasets/:id
```
[29.180]
[29.212]
```
 * @route GET /api/ebird/search
```

Replacement in src/worker/index.ts at line 2633 [11.240196]

B:BD[29.240] → [29.240:549]

 * @param {string} id - Dataset ID in URL path
 * @body {Object} Dataset update payload:
 *   - name?: string (optional, max 255 chars)
 *   - description?: string (optional, max 255 chars)
 *   - public?: boolean (optional)
 *   - type?: string (optional)
 *   - active?: boolean (optional, for soft delete)

[29.240]

[29.549]

 * @param {string} q - Search query (common name, scientific name, family, or species code)

Replacement in src/worker/index.ts at line 2635 [11.240196]

B:BD[29.591] → [29.591:900]

 *   - data: The updated dataset object
 * @error 400 - If fields are invalid or dataset not found
 * @error 403 - If user doesn't own the dataset
 * @error 500 - If database operation fails
 * @description Updates an existing dataset owned by the authenticated user
 *   Only the dataset owner can modify it

[29.591]

[29.900]

 *   - data: Array of matching eBird taxonomy entries
 * @description Searches the eBird taxonomy v2024 materialized view for species matching the query.
 *   Searches across primary_com_name, sci_name, family, and species_code fields.

Replacement in src/worker/index.ts at line 2639 [11.240196]

B:BD[29.904] → [29.904:962]

app.put("/api/datasets/:id", authenticate, async (c) => {

[29.904]

[29.962]

app.get("/api/ebird/search", authenticate, async (c) => {

Replacement in src/worker/index.ts at line 2641 [11.240196]

B:BD[29.970] → [29.970:1228]

    // Get the JWT payload (user info)
    const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    const userId = jwtPayload.sub; // User ID from JWT
    // Get dataset ID from URL parameters
    const datasetId = c.req.param("id");

[29.970]

[29.1228]

    const query = c.req.query("q");

Replacement in src/worker/index.ts at line 2643 [11.240196]
B:BD[29.1233] → [29.1233:1255]
```
    if (!datasetId) {
```
[29.1233]
[29.1255]
```
    if (!query || query.trim().length === 0) {
```

Replacement in src/worker/index.ts at line 2645 [11.240196]

B:BD[29.1277] → [29.1277:1320]

        error: "Missing dataset ID in URL"

[29.1277]

[29.1320]

        error: "Missing or empty query parameter 'q'"

Replacement in src/worker/index.ts at line 2649 [11.240196]

B:BD[29.1342] → [29.1342:1477]

    // Parse request body
    const body = await c.req.json();
    const { name, description, public: isPublic, type, active } = body;

[29.1342]

[29.1477]

    if (query.length < 2) {
      return c.json({
        error: "Query must be at least 2 characters long"
      }, 400);
    }

Replacement in src/worker/index.ts at line 2655 [11.240196]
B:BD[29.1478] → [29.1478:1509]
```
    // Connect to the database
```
[29.1478]
[29.1509]
```
    // Database connection
```

Replacement in src/worker/index.ts at line 2659 [11.240196]

B:BD[29.1581] → [29.1581:1872]

    // First, check if the dataset exists and if the user owns it
    const existingDataset = await db
      .select({
        id: dataset.id,
        owner: dataset.owner,
        active: dataset.active
      })
      .from(dataset)
      .where(eq(dataset.id, datasetId))
      .limit(1);

[29.1581]

[29.1872]

    // Search across multiple fields with case-insensitive partial matching
    const searchTerm = `%${query.trim().toLowerCase()}%`;
    
    // Use raw SQL query for materialized view until Drizzle typing issue is resolved
    const results = await db.execute(
      sqlExpr`
        SELECT 
          id,
          species_code as "speciesCode",
          primary_com_name as "primaryComName", 
          sci_name as "sciName",
          bird_order as "birdOrder",
          family
        FROM ebird_taxonomy_v2024
        WHERE 
          LOWER(primary_com_name) LIKE ${searchTerm} OR
          LOWER(sci_name) LIKE ${searchTerm} OR  
          LOWER(family) LIKE ${searchTerm} OR
          LOWER(species_code) LIKE ${searchTerm}
        ORDER BY primary_com_name
        LIMIT 20
      `
    );

Replacement in src/worker/index.ts at line 2683 [11.240196]

B:BD[29.1873] → [29.1873:1991]

    if (existingDataset.length === 0) {
      return c.json({
        error: "Dataset not found"
      }, 404);
    }

[29.1873]

[29.1991]

    return c.json({
      data: results.rows || results
    });
  } catch (error) {
    console.error("Error searching eBird taxonomy:", error);
    
    return c.json(
      {
        error: "Failed to search eBird taxonomy",
        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});

Replacement in src/worker/index.ts at line 2700 [11.240196]

B:BD[29.1992] → [7.5946:6091]

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

[29.1992]

[7.6091]

// ============================================================================
// SELECTION
// ============================================================================
/**
 * Protected API route to fetch files with selections for a specific dataset and species
 * 
 * @route GET /api/selection
 * @authentication Required
 * @param {string} datasetId - Required query parameter specifying the dataset
 * @param {string} speciesId - Required query parameter specifying the species to filter by
 * @param {number} [page=1] - Optional page number for pagination (starts at 1)
 * @param {number} [pageSize=100] - Optional page size (10-500, defaults to 100)
 * @param {string} [solarNight] - Optional filter for files recorded during solar night ('true'/'false') 
 * @param {string} [civilNight] - Optional filter for files recorded during civil night ('true'/'false')
 * @returns {Object} Response containing:
 *   - data: Array of file objects with metadata, mothMetadata, and species information
 *   - pagination: Object with pagination metadata
 *   - filters: Object showing the filters that were applied
 * @error 400 - If datasetId or speciesId is missing or page is invalid
 * @description Returns files that contain selections labeled with the specified species
 *   Designed for cross-cluster searches within a dataset
 *   
 *   Each file object includes:
 *   - Basic file information (name, path, timestamp, duration, etc.)
 *   - File metadata (JSON format)
 *   - Recording device metadata (gain, battery voltage, temperature)
 *   - Species information
 *   
 *   Uses efficient query optimization with:
 *   - Parallel Promise.all for metadata queries
 *   - Proper SQL JOINs to avoid N+1 query problems
 *   - Data transformation for optimal client-side consumption
 */
app.get("/api/selection", authenticate, async (c) => {
  try {
    // Get query parameters
    const datasetId = c.req.query("datasetId");
    const speciesId = c.req.query("speciesId");
    const page = parseInt(c.req.query("page") || "1", 10);
    const pageSize = parseInt(c.req.query("pageSize") || "100", 10);
    const solarNight = c.req.query("solarNight");
    const civilNight = c.req.query("civilNight");

Replacement in src/worker/index.ts at line 2744 [11.240196]
B:BD[7.6096] → [7.6096:6126]
```
    if (!hasEditPermission) {
```
[7.6096]
[29.2062]
```
    // Validate parameters
    if (!datasetId) {
```

Replacement in src/worker/index.ts at line 2747 [11.240196]

B:BD[29.2084] → [29.2084:2165]

        error: "You don't have permission to modify this dataset"
      }, 403);

[29.2084]

[29.2165]

        error: "Missing required query parameter: datasetId"
      }, 400);

Replacement in src/worker/index.ts at line 2750 [11.240196]

B:BD[29.2171] → [29.2171:2568]


    // Validate fields if provided
    if (name !== undefined) {
      if (typeof name !== 'string' || name.trim().length === 0) {
        return c.json({
          error: "Invalid field: name must be a non-empty string"
        }, 400);
      }
      
      if (name.length > 255) {
        return c.json({
          error: "Field 'name' must be 255 characters or less"
        }, 400);
      }

[29.2171]

[29.2568]

    
    if (!speciesId) {
      return c.json({
        error: "Missing required query parameter: speciesId"
      }, 400);

Replacement in src/worker/index.ts at line 2756 [11.240196]

B:BD[29.2574] → [29.2574:2664]


    if (description !== undefined && description !== null && description.length > 255) {

[29.2574]

[29.2664]

    
    // Validate and limit page size to prevent excessive queries
    const limitedPageSize = Math.min(Math.max(pageSize, 10), 500);
    const offset = (page - 1) * limitedPageSize;
    
    // Validate page number
    if (page < 1) {

Replacement in src/worker/index.ts at line 2764 [11.240196]

B:BD[29.2686] → [29.2686:2754]

        error: "Field 'description' must be 255 characters or less"

[29.2686]

[29.2754]

        error: "Invalid page parameter: must be greater than 0"

Replacement in src/worker/index.ts at line 2767 [11.240196]

B:BD[29.2775] → [29.2775:3273]


    if (type !== undefined) {
      const validTypes = ['organise', 'test', 'train'];
      if (!validTypes.includes(type)) {
        return c.json({
          error: `Field 'type' must be one of: ${validTypes.join(', ')}`
        }, 400);
      }
    }
    // Build update object with only provided fields
    const updateData: Record<string, unknown> = {
      lastModified: new Date(),
      modifiedBy: userId,
    };
    if (name !== undefined) {
      updateData.name = name.trim();
    }

[29.2775]

[29.3273]

    
    // Connect to the database
    const sql = neon(c.env.DATABASE_URL);
    const db = drizzle(sql);

Replacement in src/worker/index.ts at line 2772 [11.240196]

B:BD[29.3278] → [29.3278:3381]

    if (description !== undefined) {
      updateData.description = description?.trim() || null;
    }

[29.3278]

[29.3381]

    // 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
    `;

Replacement in src/worker/index.ts at line 2781 [11.240196]

B:BD[29.3386] → [29.3386:3465]

    if (isPublic !== undefined) {
      updateData.public = Boolean(isPublic);

[29.3386]

[29.3465]

    // Add filters for day/night if specified
    if (solarNight === 'true') {
      baseCondition = sqlExpr`${baseCondition} AND ${file.maybeSolarNight} = true`;
    } else if (solarNight === 'false') {
      baseCondition = sqlExpr`${baseCondition} AND (${file.maybeSolarNight} = false OR ${file.maybeSolarNight} IS NULL)`;

Replacement in src/worker/index.ts at line 2788 [11.240196]

B:BD[29.3476] → [29.3476:3536]

    if (type !== undefined) {
      updateData.type = type;

[29.3476]

[29.3536]

    if (civilNight === 'true') {
      baseCondition = sqlExpr`${baseCondition} AND ${file.maybeCivilNight} = true`;
    } else if (civilNight === 'false') {
      baseCondition = sqlExpr`${baseCondition} AND (${file.maybeCivilNight} = false OR ${file.maybeCivilNight} IS NULL)`;

Replacement in src/worker/index.ts at line 2794 [11.240196]

B:BD[29.3547] → [29.3547:3622]

    if (active !== undefined) {
      updateData.active = Boolean(active);

[29.3547]

[29.3622]

    // Get total count for pagination using a more efficient COUNT(1)
    const countResult = await db
      .select({
        count: sqlExpr<number>`COUNT(DISTINCT ${file.id})`
      })
      .from(file)
      .innerJoin(cluster, eq(file.clusterId, cluster.id))
      .innerJoin(location, eq(cluster.locationId, location.id))
      .innerJoin(selection, eq(selection.fileId, file.id))
      .innerJoin(label, eq(label.selectionId, selection.id))
      .where(baseCondition);
    
    const totalFiles = Number(countResult[0].count);
    const totalPages = Math.ceil(totalFiles / limitedPageSize);
    
    // 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,
        }
      });

Replacement in src/worker/index.ts at line 2829 [11.240196]

B:BD[29.3628] → [29.3628:4086]


    // Update the dataset
    const result = await db
      .update(dataset)
      .set(updateData)
      .where(eq(dataset.id, datasetId))
      .returning({
        id: dataset.id,
        name: dataset.name,
        description: dataset.description,
        public: dataset.public,
        type: dataset.type,
        createdAt: dataset.createdAt,
        lastModified: dataset.lastModified,
        owner: dataset.owner,
        active: dataset.active,

[29.3628]

[29.4086]

    
    // Fetch files with pagination
    const filesResult = await db
      .select({
        id: file.id,
        fileName: file.fileName,
        path: file.path,
        timestampLocal: file.timestampLocal,
        duration: file.duration,
        sampleRate: file.sampleRate,
        locationId: file.locationId,
        clusterId: file.clusterId,
        description: file.description,
        maybeSolarNight: file.maybeSolarNight,
        maybeCivilNight: file.maybeCivilNight,
        moonPhase: file.moonPhase,
      })
      .from(file)
      .innerJoin(cluster, eq(file.clusterId, cluster.id))
      .innerJoin(location, eq(cluster.locationId, location.id))
      .innerJoin(selection, eq(selection.fileId, file.id))
      .innerJoin(label, eq(label.selectionId, selection.id))
      .where(baseCondition)
      .orderBy(file.timestampLocal)
      .groupBy(file.id, file.fileName, file.path, file.timestampLocal, file.duration, 
        file.sampleRate, file.locationId, file.clusterId, file.description, file.maybeSolarNight, 
        file.maybeCivilNight, file.moonPhase)
      .limit(limitedPageSize)
      .offset(offset);
      
    // Get all file IDs for metadata queries
    const fileIds = filesResult.map(f => f.id);
    
    // 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,
        }

Deletion in src/worker/index.ts at line 2881 [11.240196]

B:BD[29.4096] → [29.4096:4207]


    if (result.length === 0) {
      return c.json({
        error: "Failed to update dataset"
      }, 500);

Replacement in src/worker/index.ts at line 2882 [11.240196]

B:BD[29.4213] → [29.4213:4287]


    console.log("Updated dataset:", result[0].id, "for user:", userId);

[29.4213]

[29.4287]

    
    // 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, {
      gain: string | null;
      batteryV: number | null;
      tempC: number | null;
    }>);
    
    // Process species data with Map for better performance
    const speciesMap = new Map<string, Array<{
      id: string;
      label: string;
      ebirdCode: string | null;
      description: string | null;
    }>>();
    
    speciesResults.forEach(item => {
      if (!speciesMap.has(item.fileId)) {
        speciesMap.set(item.fileId, []);
      }
      
      // 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
        });
      }
    });
    
    // 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

Replacement in src/worker/index.ts at line 2998 [11.240196]

B:BD[29.4307] → [29.4307:4329]

      data: result[0]

[29.4307]

[29.4329]

      data: files,
      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,
      }

Replacement in src/worker/index.ts at line 3015 [11.240196]

B:BD[29.4357] → [29.4357:4415]

    console.error("Error updating dataset:", error);

[29.4357]

[29.4415]

    console.error("Error fetching files by dataset and species:", error);

Replacement in src/worker/index.ts at line 3018 [11.240196]

B:BD[29.4442] → [29.4442:4485]

        error: "Failed to update dataset",

[29.4442]

[26.3511]

        error: "Failed to fetch files by dataset and species",

Insertion in src/worker/index.ts at line 3026 [11.240196]
[26.3619]
[26.3619]