* Get all filters used in a specific cluster
 * 
 * @route GET /api/statistics/cluster-filters
 * @authentication Required
 * @query {string} clusterId - The cluster ID to analyze
 * @returns {Object} Response containing:
 *   - data: Array of filter objects with id and name
 * @description Returns all filters that have been used for labels in the specified cluster.
 *   Requires READ permission on the dataset containing the cluster.
 */
statistics.get("/cluster-filters", authenticate, async (c) => {
  try {
    const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    const userId = jwtPayload.sub;
    
    // Get query parameters
    const clusterId = c.req.query("clusterId");
    
    // Validate required parameters
    if (!clusterId) {
      return c.json({
        error: "Missing required query parameter: clusterId"
      }, 400);
    }
    
    const db = createDatabase(c.env);
    
    // Get the datasetId for this cluster to check permissions
    const clusterResult = await db.execute(sql`
      SELECT dataset_id FROM cluster WHERE id = ${clusterId} AND active = true
    `);
    
    if (clusterResult.rows.length === 0) {
      return c.json({
        error: "Cluster not found or inactive"
      }, 404);
    }
    
    const datasetId = clusterResult.rows[0].dataset_id as string;
    
    // 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 unique filters used in this cluster
    const filtersResult = await db.execute(sql`
      SELECT DISTINCT f.id, f.name
      FROM filter f
      JOIN label l ON f.id = l.filter_id
      JOIN selection s ON l.selection_id = s.id
      JOIN file fl ON s.file_id = fl.id
      WHERE fl.cluster_id = ${clusterId}
        AND l.active = true
        AND s.active = true
        AND fl.active = true
        AND f.active = true
      ORDER BY f.name
    `);
    
    // Transform the result
    const filterOptions = filtersResult.rows.map(row => ({
      id: row.id as string,
      name: row.name as string
    }));
    
    return c.json({
      data: filterOptions
    });
  } catch (error) {
    console.error("Error fetching cluster filters:", error);
    return c.json(
      {
        error: "Failed to fetch cluster filters",
        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});
/**

    const validTimeFilters: TimeFilter[] = ["civil_night", "solar_night", "all"];

    const validTimeFilters: TimeFilter[] = ["civil_night", "civil_day", "solar_night", "solar_day", "all"];

            AND f.active = true
          GROUP BY EXTRACT(HOUR FROM f.timestamp_local)
        )
        SELECT 
          afph.hour_of_day,
          COALESCE(ccph.call_count, 0) as call_count,
          ROUND(afph.total_recording_hours::numeric, 2) as total_recording_hours,
          CASE 
            WHEN afph.total_recording_hours > 0 THEN 
              ROUND((COALESCE(ccph.call_count, 0) / afph.total_recording_hours)::numeric, 2)
            ELSE 0
          END as call_rate_per_hour
        FROM all_files_per_hour afph
        LEFT JOIN call_counts_per_hour ccph ON afph.hour_of_day = ccph.hour_of_day
        ORDER BY afph.hour_of_day
      `);
    } else if (timeFilter === "civil_day") {
      result = await db.execute(sql`
        WITH call_counts_per_hour AS (
          SELECT 
            EXTRACT(HOUR FROM f.timestamp_local) as hour_of_day,
            COUNT(l.id) as call_count,
            COUNT(DISTINCT f.id) as files_with_calls
          FROM selection s
          JOIN label l ON s.id = l.selection_id
          JOIN file f ON s.file_id = f.id
          WHERE f.cluster_id = ${clusterId}
            AND l.species_id = ${speciesId}
            AND l.filter_id = ${filterId}
            AND s.is_civil_night = false
            AND l.active = true
            AND s.active = true

        ),
        all_files_per_hour AS (
          SELECT 
            EXTRACT(HOUR FROM f.timestamp_local) as hour_of_day,
            COUNT(f.id) as total_files,
            SUM(f.duration) / 3600.0 as total_recording_hours
          FROM file f
          WHERE f.cluster_id = ${clusterId}
            AND f.maybe_civil_night = false
            AND f.active = true
          GROUP BY EXTRACT(HOUR FROM f.timestamp_local)

    } else if (timeFilter === "solar_day") {
      result = await db.execute(sql`
        WITH call_counts_per_hour AS (
          SELECT 
            EXTRACT(HOUR FROM f.timestamp_local) as hour_of_day,
            COUNT(l.id) as call_count,
            COUNT(DISTINCT f.id) as files_with_calls
          FROM selection s
          JOIN label l ON s.id = l.selection_id
          JOIN file f ON s.file_id = f.id
          WHERE f.cluster_id = ${clusterId}
            AND l.species_id = ${speciesId}
            AND l.filter_id = ${filterId}
            AND s.is_solar_night = false
            AND l.active = true
            AND s.active = true
            AND f.active = true
          GROUP BY EXTRACT(HOUR FROM f.timestamp_local)
        ),
        all_files_per_hour AS (
          SELECT 
            EXTRACT(HOUR FROM f.timestamp_local) as hour_of_day,
            COUNT(f.id) as total_files,
            SUM(f.duration) / 3600.0 as total_recording_hours
          FROM file f
          WHERE f.cluster_id = ${clusterId}
            AND f.maybe_solar_night = false
            AND f.active = true
          GROUP BY EXTRACT(HOUR FROM f.timestamp_local)
        )
        SELECT 
          afph.hour_of_day,
          COALESCE(ccph.call_count, 0) as call_count,
          ROUND(afph.total_recording_hours::numeric, 2) as total_recording_hours,
          CASE 
            WHEN afph.total_recording_hours > 0 THEN 
              ROUND((COALESCE(ccph.call_count, 0) / afph.total_recording_hours)::numeric, 2)
            ELSE 0
          END as call_rate_per_hour
        FROM all_files_per_hour afph
        LEFT JOIN call_counts_per_hour ccph ON afph.hour_of_day = ccph.hour_of_day
        ORDER BY afph.hour_of_day
      `);

export type TimeFilter = "civil_night" | "civil_day" | "solar_night" | "solar_day" | "all";

export type TimeFilter = "civil_night" | "solar_night" | "all";

export interface FilterOption {
  id: string;
  name: string;
}
export interface FiltersResponse {
  data: FilterOption[];
}

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

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

  const [availableFilters, setAvailableFilters] = useState<FilterOption[]>([]);
  const [filtersLoading, setFiltersLoading] = useState(false);
  // State for filter controls
  const [timeFilter, setTimeFilter] = useState<TimeFilter>("solar_night");
  const [selectedFilterId, setSelectedFilterId] = useState<string>("uWoU4s46qSPd"); // opensoundscape-kiwi-1.0

  const filterId = "uWoU4s46qSPd"; // opensoundscape-kiwi-1.0
  const timeFilter: TimeFilter = "civil_night"; // Nocturnal

          filterId,

          filterId: selectedFilterId,

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

  }, [clusterId, speciesId, selectedFilterId, timeFilter, isAuthenticated, authLoading, getAccessToken]);
  // Fetch available filters for this cluster
  useEffect(() => {
    const fetchFilters = async () => {
      if (!isAuthenticated) {
        if (!authLoading) {
          setFiltersLoading(false);
        }
        return;
      }
      setFiltersLoading(true);
      
      try {
        const accessToken = await getAccessToken();
        
        const response = await fetch(`/api/statistics/cluster-filters?clusterId=${clusterId}`, {
          headers: {
            Authorization: `Bearer ${accessToken}`,
          },
        });
        
        if (!response.ok) {
          throw new Error(`Failed to fetch filters: ${response.statusText}`);
        }
        
        const data: FiltersResponse = await response.json();
        setAvailableFilters(data.data);
        
        // Set default filter if available and current selection is not in the list
        if (data.data.length > 0 && !data.data.find(f => f.id === selectedFilterId)) {
          const defaultFilter = data.data.find(f => f.id === "uWoU4s46qSPd") || data.data[0];
          setSelectedFilterId(defaultFilter.id);
        }
      } catch (err) {
        console.error("Error fetching filters:", err);
      } finally {
        setFiltersLoading(false);
      }
    };
    fetchFilters();
  }, [clusterId, isAuthenticated, authLoading, getAccessToken, selectedFilterId]);

  // Calculate totals
  const totalCalls = data.reduce((sum, hour) => sum + hour.call_count, 0);
  const totalRecordingHours = data.reduce((sum, hour) => sum + hour.total_recording_hours, 0);
  const overallCallRate = totalRecordingHours > 0 ? totalCalls / totalRecordingHours : 0;

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

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

        <div className="bg-gray-50 p-3 rounded">
          <div className="font-medium">Call Rate</div>
          <div className="text-lg">{overallCallRate.toFixed(2)}</div>
        </div>

        </div>
      </div>
      <div className="mb-6 flex flex-wrap items-center gap-4">
        <div className="flex items-center">
          <select
            id="timeFilter"
            value={timeFilter}
            onChange={(e) => setTimeFilter(e.target.value as TimeFilter)}
            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"
          >
            <option value="civil_night">Civil Night</option>
            <option value="civil_day">Civil Day</option>
            <option value="solar_night">Solar Night</option>
            <option value="solar_day">Solar Day</option>
            <option value="all">All Times</option>
          </select>

        <div className="bg-gray-50 p-3 rounded">
          <div className="font-medium">Time Filter</div>
          <div className="text-lg capitalize">{metadata.timeFilter.replace('_', ' ')}</div>

        
        <div className="flex items-center">
          <select
            id="filterSelect"
            value={selectedFilterId}
            onChange={(e) => setSelectedFilterId(e.target.value)}
            disabled={filtersLoading || availableFilters.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"
          >
            {filtersLoading ? (
              <option>Loading filters...</option>
            ) : availableFilters.length === 0 ? (
              <option>No filters available</option>
            ) : (
              availableFilters.map(filter => (
                <option key={filter.id} value={filter.id}>
                  {filter.name}
                </option>
              ))
            )}
          </select>

                <th className="text-left py-2">Hour</th>

                <th className="text-left py-2">Hour (UTC)</th>

              <tr className="border-t-2 border-gray-400 bg-gray-50 font-medium">
                <td className="py-3 font-semibold">Total</td>
                <td className="text-right py-3">{totalCalls}</td>
                <td className="text-right py-3">{totalRecordingHours.toFixed(2)}</td>
                <td className="text-right py-3 font-bold">
                  {overallCallRate.toFixed(2)}
                </td>
              </tr>