/**
 * Get all species associated with a specific dataset
 * 
 * @route GET /api/statistics/dataset-species
 * @authentication Required
 * @query {string} datasetId - The dataset ID to get species for
 * @returns {Object} Response containing:
 *   - data: Array of species objects with id, label, and optional ebird_code
 * @description Returns all species that are linked to the specified dataset through the species_dataset junction table.
 *   Requires READ permission on the dataset.
 */
statistics.get("/dataset-species", authenticate, async (c) => {
  try {
    const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    const userId = jwtPayload.sub;
    
    // Get query parameters
    const datasetId = c.req.query("datasetId");
    
    // Validate required parameters
    if (!datasetId) {
      return c.json({
        error: "Missing required query parameter: datasetId"
      }, 400);
    }
    
    const db = createDatabase(c.env);
    
    // Check if user has READ permission for this dataset
    const hasPermission = await checkUserPermission(db, userId, datasetId, 'READ');
    
    if (!hasPermission) {
      return c.json({
        error: "Access denied: No READ permission for this dataset"
      }, 403);
    }
    
    // Get all species linked to this dataset
    const speciesResult = await db.execute(sql`
      SELECT DISTINCT s.id, s.label, s.ebird_code
      FROM species s
      JOIN species_dataset sd ON s.id = sd.species_id
      WHERE sd.dataset_id = ${datasetId}
        AND s.active = true
      ORDER BY s.label
    `);
    
    // Transform the result
    const speciesOptions = speciesResult.rows.map(row => ({
      id: row.id as string,
      label: row.label as string,
      ebird_code: row.ebird_code as string | null
    }));
    
    return c.json({
      data: speciesOptions
    });
  } catch (error) {
    console.error("Error fetching dataset species:", error);
    return c.json(
      {
        error: "Failed to fetch dataset species",
        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});
/**
 * Get all call types for a specific species
 * 
 * @route GET /api/statistics/species-call-types
 * @authentication Required
 * @query {string} speciesId - The species ID to get call types for
 * @returns {Object} Response containing:
 *   - data: Array of call type objects with id and label
 * @description Returns all call types associated with the specified species.
 *   Requires READ permission on at least one dataset containing the species.
 */
statistics.get("/species-call-types", authenticate, async (c) => {
  try {
    const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    const userId = jwtPayload.sub;
    
    // Get query parameters
    const speciesId = c.req.query("speciesId");
    
    // Validate required parameters
    if (!speciesId) {
      return c.json({
        error: "Missing required query parameter: speciesId"
      }, 400);
    }
    
    const db = createDatabase(c.env);
    
    // Check if user has READ permission for any dataset containing this species
    const permissionResult = await db.execute(sql`
      SELECT DISTINCT sd.dataset_id
      FROM species_dataset sd
      WHERE sd.species_id = ${speciesId}
    `);
    
    if (permissionResult.rows.length === 0) {
      return c.json({
        error: "Species not found in any dataset"
      }, 404);
    }
    
    // Check permissions for at least one dataset
    let hasPermission = false;
    for (const row of permissionResult.rows) {
      const datasetId = row.dataset_id as string;
      if (await checkUserPermission(db, userId, datasetId, 'READ')) {
        hasPermission = true;
        break;
      }
    }
    
    if (!hasPermission) {
      return c.json({
        error: "Access denied: No READ permission for datasets containing this species"
      }, 403);
    }
    
    // Get all call types for this species
    const callTypesResult = await db.execute(sql`
      SELECT id, label
      FROM call_type
      WHERE species_id = ${speciesId}
        AND active = true
      ORDER BY label
    `);
    
    // Transform the result
    const callTypeOptions = callTypesResult.rows.map(row => ({
      id: row.id as string,
      label: row.label as string
    }));
    
    return c.json({
      data: callTypeOptions
    });
  } catch (error) {
    console.error("Error fetching species call types:", error);
    return c.json(
      {
        error: "Failed to fetch species call types",
        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});

    const callTypeId = c.req.query("callTypeId"); // Optional parameter

    // Build call type filter condition if callTypeId is provided
    const callTypeFilter = callTypeId ? sql`
      AND EXISTS (
        SELECT 1 FROM label_subtype ls 
        WHERE ls.label_id = l.id 
        AND ls.calltype_id = ${callTypeId}
        AND ls.active = true
      )
    ` : sql``;
    

            ${callTypeFilter}

            ${callTypeFilter}

            ${callTypeFilter}

            ${callTypeFilter}

            ${callTypeFilter}

    const { label, description, ebirdCode, active } = body;

    const { label, description, ebirdCode, active, callTypes } = body;

    // Start transaction for atomic update
    const result = await db.transaction(async (tx) => {
      // Build update object with only provided fields
      const updateData: Record<string, unknown> = {
        lastModified: new Date(),
        modifiedBy: userId,
      };

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

      // Validate and add label if provided
      if (label !== undefined) {
        if (typeof label !== 'string' || label.trim().length === 0) {
          return c.json({
            error: "Field 'label' must be a non-empty string"
          }, 400);
        }
        if (label.length > 100) {
          return c.json({
            error: "Field 'label' must be 100 characters or less"
          }, 400);
        }
        updateData.label = label.trim();

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

      updateData.label = label.trim();
    }

      // Validate and add description if provided
      if (description !== undefined) {
        if (description !== null && typeof description !== 'string') {
          return c.json({
            error: "Field 'description' must be a string or null"
          }, 400);
        }
        if (description && description.length > 255) {
          return c.json({
            error: "Field 'description' must be 255 characters or less"
          }, 400);
        }
        updateData.description = description?.trim() || null;

    // Validate and add description if provided
    if (description !== undefined) {
      if (description !== null && typeof description !== 'string') {
        return c.json({
          error: "Field 'description' must be a string or null"
        }, 400);
      }
      if (description && description.length > 255) {
        return c.json({
          error: "Field 'description' must be 255 characters or less"
        }, 400);
      }
      updateData.description = description?.trim() || null;
    }
    // Validate and add eBird code if provided
    if (ebirdCode !== undefined) {
      if (ebirdCode !== null && typeof ebirdCode !== 'string') {
        return c.json({
          error: "Field 'ebirdCode' must be a string or null"
        }, 400);
      }
      if (ebirdCode && ebirdCode.length > 12) {
        return c.json({
          error: "Field 'ebirdCode' must be 12 characters or less"
        }, 400);

      updateData.ebirdCode = ebirdCode?.trim() || null;
      updateData.taxonomyVersion = ebirdCode ? '2024' : null;
    }

      // Validate and add eBird code if provided
      if (ebirdCode !== undefined) {
        if (ebirdCode !== null && typeof ebirdCode !== 'string') {

    // Add active status if provided (for soft delete)
    if (active !== undefined) {
      if (typeof active !== 'boolean') {
        return c.json({
          error: "Field 'active' must be a boolean"
        }, 400);
      }
      updateData.active = active;
      
      // If soft deleting species, also soft delete related records
      if (!active) {
        // Soft delete all call types for this species
        await db
          .update(callType)
          .set({ 
            active: false,
            lastModified: new Date(),
            modifiedBy: userId
          })
          .where(eq(callType.speciesId, speciesId));
        
        // Delete all species-dataset associations for this species (hard delete since no active field)
        await db
          .delete(speciesDataset)
          .where(eq(speciesDataset.speciesId, speciesId));
      }
    }
    // Update the species
    const [updatedSpecies] = await db
      .update(species)
      .set(updateData)
      .where(eq(species.id, speciesId))
      .returning({
        id: species.id,
        label: species.label,
        ebirdCode: species.ebirdCode,
        taxonomyVersion: species.taxonomyVersion,
        description: species.description,
        createdAt: species.createdAt,
        createdBy: species.createdBy,
        lastModified: species.lastModified,
        modifiedBy: species.modifiedBy,
        active: species.active,
      });
    // Handle call type updates if provided
    if (callTypes && Array.isArray(callTypes)) {
      // Validate call types
      for (const ct of callTypes) {
        if (!ct.label || typeof ct.label !== 'string' || ct.label.trim().length === 0) {

            error: "Field 'ebirdCode' must be a string or null"

            error: "Each call type must have a non-empty label"

        if (ebirdCode && ebirdCode.length > 12) {

        if (ct.label.length > 100) {

            error: "Field 'ebirdCode' must be 12 characters or less"

            error: "Call type labels must be 100 characters or less"

        updateData.ebirdCode = ebirdCode?.trim() || null;
        updateData.taxonomyVersion = ebirdCode ? '2024' : null;

      // Get existing call types
      const existingCallTypes = await db
        .select({
          id: callType.id,
          label: callType.label,
        })
        .from(callType)
        .where(and(eq(callType.speciesId, speciesId), eq(callType.active, true)));
      // Update existing call types
      for (let i = 0; i < callTypes.length; i++) {
        const newCallType = callTypes[i];
        const existingCallType = existingCallTypes[i];

      // Add active status if provided (for soft delete)
      if (active !== undefined) {
        if (typeof active !== 'boolean') {
          return c.json({
            error: "Field 'active' must be a boolean"
          }, 400);

        if (existingCallType && existingCallType.label !== newCallType.label.trim()) {
          // Update existing call type
          await db
            .update(callType)
            .set({
              label: newCallType.label.trim(),
              lastModified: new Date(),
              modifiedBy: userId
            })
            .where(eq(callType.id, existingCallType.id));
        } else if (!existingCallType) {
          // Create new call type
          const callTypeId = nanoid(12);
          await db.insert(callType).values({
            id: callTypeId,
            speciesId: speciesId,
            label: newCallType.label.trim(),
            createdBy: userId,
            createdAt: new Date(),
            lastModified: new Date(),
            modifiedBy: userId,
            active: true,
          });

        updateData.active = active;
        
        // If soft deleting species, also soft delete related records
        if (!active) {
          // Soft delete all call types for this species
          await tx

      }
      // Soft delete any excess existing call types
      if (existingCallTypes.length > callTypes.length) {
        for (let i = callTypes.length; i < existingCallTypes.length; i++) {
          await db

            .set({ 

            .set({

            .where(eq(callType.speciesId, speciesId));
          
          // Delete all species-dataset associations for this species (hard delete since no active field)
          await tx
            .delete(speciesDataset)
            .where(eq(speciesDataset.speciesId, speciesId));

            .where(eq(callType.id, existingCallTypes[i].id));

    }

      // Update the species
      const [updatedSpecies] = await tx
        .update(species)
        .set(updateData)
        .where(eq(species.id, speciesId))
        .returning({
          id: species.id,
          label: species.label,
          ebirdCode: species.ebirdCode,
          taxonomyVersion: species.taxonomyVersion,
          description: species.description,
          createdAt: species.createdAt,
          createdBy: species.createdBy,
          lastModified: species.lastModified,
          modifiedBy: species.modifiedBy,
          active: species.active,
        });

    // Get updated call types
    const updatedCallTypes = await db
      .select({
        id: callType.id,
        label: callType.label,
      })
      .from(callType)
      .where(and(eq(callType.speciesId, speciesId), eq(callType.active, true)));

      // Get updated call types
      const callTypes = await tx
        .select({
          id: callType.id,
          label: callType.label,
        })
        .from(callType)
        .where(and(eq(callType.speciesId, speciesId), eq(callType.active, true)));

    const result = {
      ...updatedSpecies,
      callTypes: updatedCallTypes
    };

      return {
        ...updatedSpecies,
        callTypes
      };
    });

}
export interface SpeciesOption {
  id: string;
  label: string;
  ebird_code?: string;
}
export interface SpeciesResponse {
  data: SpeciesOption[];
}
export interface CallTypeOption {
  id: string;
  label: string;
}
export interface CallTypeResponse {
  data: CallTypeOption[];

import type { CallRateResponse, FilterOption, FiltersResponse, TimeFilter } from "../types/statistics";

import type { CallRateResponse, CallTypeOption, CallTypeResponse, FilterOption, FiltersResponse, SpeciesOption, SpeciesResponse, TimeFilter } from "../types/statistics";

  datasetId,

  const [availableSpecies, setAvailableSpecies] = useState<SpeciesOption[]>([]);
  const [speciesLoading, setSpeciesLoading] = useState(false);
  const [availableCallTypes, setAvailableCallTypes] = useState<CallTypeOption[]>([]);
  const [callTypesLoading, setCallTypesLoading] = useState(false);

  // Default values for Haast Tokoeka
  const speciesId = "ANhV8iZPfIh8"; // Haast Tokoeka

  const [selectedSpeciesId, setSelectedSpeciesId] = useState<string>("ANhV8iZPfIh8"); // Default to Haast Tokoeka
  const [selectedCallTypeId, setSelectedCallTypeId] = useState<string>(""); // Empty means "All Call Types"

          speciesId,

          speciesId: selectedSpeciesId,

        // Add callTypeId if a specific call type is selected
        if (selectedCallTypeId) {
          params.append("callTypeId", selectedCallTypeId);
        }

  }, [clusterId, speciesId, selectedFilterId, timeFilter, isAuthenticated, authLoading, getAccessToken]);

  }, [clusterId, selectedSpeciesId, selectedFilterId, selectedCallTypeId, timeFilter, isAuthenticated, authLoading, getAccessToken]);

  // Fetch available species for this dataset
  useEffect(() => {
    const fetchSpecies = async () => {
      if (!isAuthenticated) {
        if (!authLoading) {
          setSpeciesLoading(false);
        }
        return;
      }
      setSpeciesLoading(true);
      try {
        const accessToken = await getAccessToken();
        const response = await fetch(`/api/statistics/dataset-species?datasetId=${datasetId}`, {
          headers: {
            Authorization: `Bearer ${accessToken}`,
          },
        });
        if (!response.ok) {
          throw new Error(`Failed to fetch species: ${response.statusText}`);
        }
        const data: SpeciesResponse = await response.json();
        setAvailableSpecies(data.data);

        // Set default species if available and current selection is not in the list
        if (data.data.length > 0 && !data.data.find(s => s.id === selectedSpeciesId)) {
          const defaultSpecies = data.data.find(s => s.id === "ANhV8iZPfIh8") || data.data[0];
          setSelectedSpeciesId(defaultSpecies.id);
        }
      } catch (err) {
        console.error("Error fetching species:", err);
      } finally {
        setSpeciesLoading(false);
      }
    };
    fetchSpecies();
  }, [datasetId, isAuthenticated, authLoading, getAccessToken, selectedSpeciesId]);
  // Fetch available call types for the selected species
  useEffect(() => {
    const fetchCallTypes = async () => {
      if (!isAuthenticated || !selectedSpeciesId) {
        if (!authLoading) {
          setCallTypesLoading(false);
        }
        setAvailableCallTypes([]);
        setSelectedCallTypeId("");
        return;
      }
      setCallTypesLoading(true);
      try {
        const accessToken = await getAccessToken();
        const response = await fetch(`/api/statistics/species-call-types?speciesId=${selectedSpeciesId}`, {
          headers: {
            Authorization: `Bearer ${accessToken}`,
          },
        });
        if (!response.ok) {
          throw new Error(`Failed to fetch call types: ${response.statusText}`);
        }
        const data: CallTypeResponse = await response.json();
        setAvailableCallTypes(data.data);
        // Reset call type selection when species changes
        setSelectedCallTypeId("");
      } catch (err) {
        console.error("Error fetching call types:", err);
        setAvailableCallTypes([]);
        setSelectedCallTypeId("");
      } finally {
        setCallTypesLoading(false);
      }
    };
    fetchCallTypes();
  }, [selectedSpeciesId, isAuthenticated, authLoading, getAccessToken]);

  // Get the selected species label for display
  const selectedSpecies = availableSpecies.find(s => s.id === selectedSpeciesId);
  const speciesDisplayName = selectedSpecies?.label || "Species";

      <h3 className="text-lg font-semibold mb-4">Haast Tokoeka</h3>

      <h3 className="text-lg font-semibold mb-4">{speciesDisplayName}</h3>

            id="speciesSelect"
            value={selectedSpeciesId}
            onChange={(e) => setSelectedSpeciesId(e.target.value)}
            disabled={speciesLoading || availableSpecies.length === 0}
            className="rounded-md border border-gray-300 bg-white py-1 px-3 text-sm shadow-sm focus:border-primary focus:outline-none focus:ring-1 focus:ring-primary disabled:bg-gray-100"
          >
            {speciesLoading
              ? <option>Loading species...</option>
              : availableSpecies.length === 0
              ? <option>No species available</option>
              : (
                availableSpecies.map(species => (
                  <option key={species.id} value={species.id}>
                    {species.label}
                  </option>
                ))
              )}
          </select>
        </div>
        {availableCallTypes.length > 0 && (
          <div className="flex items-center">
            <select
              id="callTypeSelect"
              value={selectedCallTypeId}
              onChange={(e) => setSelectedCallTypeId(e.target.value)}
              disabled={callTypesLoading}
              className="rounded-md border border-gray-300 bg-white py-1 px-3 text-sm shadow-sm focus:border-primary focus:outline-none focus:ring-1 focus:ring-primary disabled:bg-gray-100"
            >
              <option value="">All Call Types</option>
              {availableCallTypes.map(callType => (
                <option key={callType.id} value={callType.id}>
                  {callType.label}
                </option>
              ))}
            </select>
          </div>
        )}
        <div className="flex items-center">
          <select