import { Hono } from "hono";
import { sql } from "drizzle-orm";
import { authenticate, checkUserPermission } from "../middleware/auth";
import { createDatabase } from "../utils/database";
import type { Env, JWTPayload } from "../types";
import type { CallRatePerHour, CallRateMetadata, TimeFilter } from "../../types/statistics";
const statistics = new Hono<{ Bindings: Env }>();

 * Get all species associated with a specific dataset

 * Statistics routes - refactored version

 * @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.

 * This file now imports from the modular statistics implementation
 * for improved maintainability and reduced code duplication.

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
    );
  }
});
/**
 * 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
    );
  }
});
/**
 * Calculate call rate per hour for a specific species and filter in a cluster
 * 
 * @route GET /api/statistics/call-rate-per-hour
 * @authentication Required
 * @query {string} clusterId - The cluster ID to analyze
 * @query {string} speciesId - The species ID to filter for
 * @query {string} filterId - The filter ID to use for call detection
 * @query {string} timeFilter - Time filter (civil_night, solar_night, all) - defaults to 'civil_night'
 * @returns {Object} Response containing:
 *   - data: Array of hourly call rate statistics
 *     - hour_of_day: Hour (0-23)
 *     - call_count: Number of calls detected in that hour
 *     - total_recording_hours: Total recording time in that hour
 *     - call_rate_per_hour: Calls per recording hour
 * @description Calculates the call rate per hour for a specific species in a cluster.
 *   Requires READ permission on the dataset containing the cluster.
 */
statistics.get("/call-rate-per-hour", 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");
    const speciesId = c.req.query("speciesId");
    const filterId = c.req.query("filterId");
    const timeFilter = c.req.query("timeFilter") || "civil_night";
    const callTypeId = c.req.query("callTypeId"); // Optional parameter
    
    // Validate required parameters
    if (!clusterId) {
      return c.json({
        error: "Missing required query parameter: clusterId"
      }, 400);
    }
    
    if (!speciesId) {
      return c.json({
        error: "Missing required query parameter: speciesId"
      }, 400);
    }
    
    if (!filterId) {
      return c.json({
        error: "Missing required query parameter: filterId"
      }, 400);
    }
    
    // Validate timeFilter
    const validTimeFilters: TimeFilter[] = ["civil_night", "civil_day", "solar_night", "solar_day", "all"];
    if (!validTimeFilters.includes(timeFilter as TimeFilter)) {
      return c.json({
        error: `Invalid timeFilter. Must be one of: ${validTimeFilters.join(", ")}`
      }, 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);
    }
    
    
    // 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``;
    
    // Execute the call rate calculation query
    let result;
    
    if (timeFilter === "civil_night") {
      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 = true
            AND l.active = true
            AND s.active = true
            AND f.active = true
            ${callTypeFilter}
          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_civil_night = true
            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
            AND f.active = true
            ${callTypeFilter}
          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_civil_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
      `);
    } else if (timeFilter === "solar_night") {
      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 = true
            AND l.active = true
            AND s.active = true
            AND f.active = true
            ${callTypeFilter}
          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 = true
            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 === "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
            ${callTypeFilter}
          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
      `);
    } else {
      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 l.active = true
            AND s.active = true
            AND f.active = true
            ${callTypeFilter}
          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.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
      `);
    }
    
    // Transform the result to match our expected format
    const callRateData: CallRatePerHour[] = result.rows.map(row => ({
      hour_of_day: Number(row.hour_of_day),
      call_count: Number(row.call_count),
      total_recording_hours: Number(row.total_recording_hours),
      call_rate_per_hour: Number(row.call_rate_per_hour)
    }));
    
    const metadata: CallRateMetadata = {
      clusterId,
      speciesId,
      filterId,
      timeFilter,
      totalHours: callRateData.length,
      totalCalls: callRateData.reduce((sum, hour) => sum + hour.call_count, 0),
      totalRecordingHours: callRateData.reduce((sum, hour) => sum + hour.total_recording_hours, 0)
    };
    
    return c.json({
      data: callRateData,
      metadata
    });
  } catch (error) {
    console.error("Error calculating call rate per hour:", error);
    return c.json(
      {
        error: "Failed to calculate call rate per hour",
        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});
/**
 * Get all filters used in a specific dataset
 * 
 * @route GET /api/statistics/dataset-filters
 * @authentication Required
 * @query {string} datasetId - The dataset 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 dataset.
 *   Requires READ permission on the dataset.
 */
statistics.get("/dataset-filters", 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 unique filters used in this dataset
    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
      JOIN cluster c ON fl.cluster_id = c.id
      WHERE c.dataset_id = ${datasetId}
        AND l.active = true
        AND s.active = true
        AND fl.active = true
        AND c.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 dataset filters:", error);
    return c.json(
      {
        error: "Failed to fetch dataset filters",
        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});
/**
 * Calculate call rate per hour for a specific species and filter in a dataset
 * 
 * @route GET /api/statistics/dataset-call-rate-per-hour
 * @authentication Required
 * @query {string} datasetId - The dataset ID to analyze
 * @query {string} speciesId - The species ID to filter for
 * @query {string} filterId - The filter ID to use for call detection
 * @query {string} timeFilter - Time filter (civil_night, solar_night, civil_day, solar_day, all) - defaults to 'civil_night'
 * @query {string} callTypeId - Optional call type ID to filter for specific call types
 * @returns {Object} Response containing:
 *   - data: Array of hourly call rate statistics
 *     - hour_of_day: Hour (0-23)
 *     - call_count: Number of calls detected in that hour
 *     - total_recording_hours: Total recording time in that hour
 *     - call_rate_per_hour: Calls per recording hour
 * @description Calculates the call rate per hour for a specific species in a dataset.
 *   Requires READ permission on the dataset.
 */
statistics.get("/dataset-call-rate-per-hour", 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");
    const speciesId = c.req.query("speciesId");
    const filterId = c.req.query("filterId");
    const timeFilter = c.req.query("timeFilter") || "civil_night";
    const callTypeId = c.req.query("callTypeId"); // Optional parameter
    
    // Validate required parameters
    if (!datasetId) {
      return c.json({
        error: "Missing required query parameter: datasetId"
      }, 400);
    }
    
    if (!speciesId) {
      return c.json({
        error: "Missing required query parameter: speciesId"
      }, 400);
    }
    
    if (!filterId) {
      return c.json({
        error: "Missing required query parameter: filterId"
      }, 400);
    }
    
    // Validate timeFilter
    const validTimeFilters: TimeFilter[] = ["civil_night", "civil_day", "solar_night", "solar_day", "all"];
    if (!validTimeFilters.includes(timeFilter as TimeFilter)) {
      return c.json({
        error: `Invalid timeFilter. Must be one of: ${validTimeFilters.join(", ")}`
      }, 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);
    }
    
    // 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``;
    
    // Execute the call rate calculation query
    let result;
    
    if (timeFilter === "civil_night") {
      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
          JOIN cluster c ON f.cluster_id = c.id
          WHERE c.dataset_id = ${datasetId}
            AND l.species_id = ${speciesId}
            AND l.filter_id = ${filterId}
            AND s.is_civil_night = true
            AND l.active = true
            AND s.active = true
            AND f.active = true
            AND c.active = true
            ${callTypeFilter}
          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
          JOIN cluster c ON f.cluster_id = c.id
          WHERE c.dataset_id = ${datasetId}
            AND f.maybe_civil_night = true
            AND f.active = true
            AND c.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
          JOIN cluster c ON f.cluster_id = c.id
          WHERE c.dataset_id = ${datasetId}
            AND l.species_id = ${speciesId}
            AND l.filter_id = ${filterId}
            AND s.is_civil_night = false
            AND l.active = true
            AND s.active = true
            AND f.active = true
            AND c.active = true
            ${callTypeFilter}
          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
          JOIN cluster c ON f.cluster_id = c.id
          WHERE c.dataset_id = ${datasetId}
            AND f.maybe_civil_night = false
            AND f.active = true
            AND c.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 === "solar_night") {
      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
          JOIN cluster c ON f.cluster_id = c.id
          WHERE c.dataset_id = ${datasetId}
            AND l.species_id = ${speciesId}
            AND l.filter_id = ${filterId}
            AND s.is_solar_night = true
            AND l.active = true
            AND s.active = true
            AND f.active = true
            AND c.active = true
            ${callTypeFilter}
          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
          JOIN cluster c ON f.cluster_id = c.id
          WHERE c.dataset_id = ${datasetId}
            AND f.maybe_solar_night = true
            AND f.active = true
            AND c.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 === "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
          JOIN cluster c ON f.cluster_id = c.id
          WHERE c.dataset_id = ${datasetId}
            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
            AND c.active = true
            ${callTypeFilter}
          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
          JOIN cluster c ON f.cluster_id = c.id
          WHERE c.dataset_id = ${datasetId}
            AND f.maybe_solar_night = false
            AND f.active = true
            AND c.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 {
      // timeFilter === "all"
      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
          JOIN cluster c ON f.cluster_id = c.id
          WHERE c.dataset_id = ${datasetId}
            AND l.species_id = ${speciesId}
            AND l.filter_id = ${filterId}
            AND l.active = true
            AND s.active = true
            AND f.active = true
            AND c.active = true
            ${callTypeFilter}
          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
          JOIN cluster c ON f.cluster_id = c.id
          WHERE c.dataset_id = ${datasetId}
            AND f.active = true
            AND c.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
      `);
    }
    
    // Transform the result to match our expected format
    const callRateData: CallRatePerHour[] = result.rows.map(row => ({
      hour_of_day: Number(row.hour_of_day),
      call_count: Number(row.call_count),
      total_recording_hours: Number(row.total_recording_hours),
      call_rate_per_hour: Number(row.call_rate_per_hour)
    }));
    
    const metadata: CallRateMetadata = {
      datasetId,
      speciesId,
      filterId,
      timeFilter,
      totalHours: callRateData.length,
      totalCalls: callRateData.reduce((sum, hour) => sum + hour.call_count, 0),
      totalRecordingHours: callRateData.reduce((sum, hour) => sum + hour.total_recording_hours, 0)
    };
    
    return c.json({
      data: callRateData,
      metadata
    });
  } catch (error) {
    console.error("Error calculating dataset call rate per hour:", error);
    return c.json(
      {
        error: "Failed to calculate dataset call rate per hour",
        details: error instanceof Error ? error.message : String(error),
      },
      500
    );
  }
});
export default statistics;

export { default } from "./statistics/index";

/**
 * Utility functions for statistics module
 */
import type { TimeFilter, TimeFilterCondition, CallRateQueryParams } from "./types";
// Valid time filter values
export const VALID_TIME_FILTERS: TimeFilter[] = [
  "civil_night", 
  "civil_day", 
  "solar_night", 
  "solar_day", 
  "all"
];
/**
 * Validates a time filter value
 */
export function isValidTimeFilter(timeFilter: string): timeFilter is TimeFilter {
  return VALID_TIME_FILTERS.includes(timeFilter as TimeFilter);
}
/**
 * Gets the time filter conditions for SQL queries
 */
export function getTimeFilterConditions(timeFilter: TimeFilter): TimeFilterCondition | null {
  switch (timeFilter) {
    case "civil_night":
      return {
        fileTimeColumn: "maybe_civil_night",
        fileTimeValue: true,
        selectionTimeColumn: "is_civil_night", 
        selectionTimeValue: true
      };
    case "civil_day":
      return {
        fileTimeColumn: "maybe_civil_night",
        fileTimeValue: false,
        selectionTimeColumn: "is_civil_night",
        selectionTimeValue: false
      };
    case "solar_night":
      return {
        fileTimeColumn: "maybe_solar_night", 
        fileTimeValue: true,
        selectionTimeColumn: "is_solar_night",
        selectionTimeValue: true
      };
    case "solar_day":
      return {
        fileTimeColumn: "maybe_solar_night",
        fileTimeValue: false,
        selectionTimeColumn: "is_solar_night", 
        selectionTimeValue: false
      };
    case "all":
      return null; // No time filtering
    default:
      return null;
  }
}
/**
 * Validates required query parameters for call rate endpoints
 */
export function validateCallRateParams(params: Partial<CallRateQueryParams>): string | null {
  if (!params.speciesId) {
    return "Missing required query parameter: speciesId";
  }
  
  if (!params.filterId) {
    return "Missing required query parameter: filterId";
  }
  
  if (params.timeFilter && !isValidTimeFilter(params.timeFilter)) {
    return `Invalid timeFilter. Must be one of: ${VALID_TIME_FILTERS.join(", ")}`;
  }
  
  return null;
}
/**
 * Validates cluster-specific call rate parameters
 */
export function validateClusterCallRateParams(params: Partial<CallRateQueryParams>): string | null {
  if (!params.clusterId) {
    return "Missing required query parameter: clusterId";
  }
  
  return validateCallRateParams(params);
}
/**
 * Validates dataset-specific call rate parameters  
 */
export function validateDatasetCallRateParams(params: Partial<CallRateQueryParams>): string | null {
  if (!params.datasetId) {
    return "Missing required query parameter: datasetId";
  }
  
  return validateCallRateParams(params);
}
/**
 * Creates standard error response structure
 */
export function createErrorResponse(message: string, details?: string) {
  return {
    error: message,
    ...(details && { details })
  };
}
/**
 * Creates standard success response structure
 */
export function createSuccessResponse<T>(data: T) {
  return { data };
}
/**
 * Transforms database rows to response format
 */
export function transformCallRateRows(rows: any[]): import("./types").CallRateRow[] {
  return rows.map(row => ({
    hour_of_day: Number(row.hour_of_day),
    call_count: Number(row.call_count),
    total_recording_hours: Number(row.total_recording_hours),
    call_rate_per_hour: Number(row.call_rate_per_hour)
  }));
}
/**
 * Transforms species database rows to response format
 */
export function transformSpeciesRows(rows: any[]): import("./types").SpeciesRow[] {
  return rows.map(row => ({
    id: row.id as string,
    label: row.label as string,
    ebird_code: row.ebird_code as string | null
  }));
}
/**
 * Transforms call type database rows to response format
 */
export function transformCallTypeRows(rows: any[]): import("./types").CallTypeRow[] {
  return rows.map(row => ({
    id: row.id as string,
    label: row.label as string
  }));
}
/**
 * Transforms filter database rows to response format
 */
export function transformFilterRows(rows: any[]): import("./types").FilterRow[] {
  return rows.map(row => ({
    id: row.id as string,
    name: row.name as string
  }));
}

/**
 * Internal types for statistics module
 */
import type { TimeFilter } from "../../../types/statistics";
// Re-export the main types for convenience
export type { 
  CallRatePerHour, 
  CallRateMetadata, 
  CallRateResponse,
  TimeFilter,
  FilterOption,
  FiltersResponse,
  StatisticsSpeciesOption,
  SpeciesResponse,
  CallTypeOption,
  CallTypeResponse
} from "../../../types/statistics";
// Internal query parameters interface
export interface CallRateQueryParams {
  clusterId?: string | undefined;
  datasetId?: string | undefined;
  speciesId: string;
  filterId: string;
  timeFilter: TimeFilter;
  callTypeId?: string | undefined;
}
// Time filter conditions for SQL queries
export interface TimeFilterCondition {
  fileTimeColumn: string;
  fileTimeValue: boolean;
  selectionTimeColumn: string;
  selectionTimeValue: boolean;
}
// Database result row interfaces
export interface CallRateRow {
  hour_of_day: number;
  call_count: number;
  total_recording_hours: number;
  call_rate_per_hour: number;
}
export interface SpeciesRow {
  id: string;
  label: string;
  ebird_code: string | null;
}
export interface CallTypeRow {
  id: string;
  label: string;
}
export interface FilterRow {
  id: string;
  name: string;
}
export interface ClusterRow {
  dataset_id: string;
}
// Query metadata for totals calculations
export interface QueryMetadata {
  totalCalls: number;
  totalRecordingHours: number;
}

/**
 * Reusable query builders for statistics module
 */
import { sql } from "drizzle-orm";
import type { CallRateQueryParams, QueryMetadata } from "./types";
import { getTimeFilterConditions } from "./utils";
/**
 * Gets all species associated with a specific dataset
 */
export async function getDatasetSpecies(db: any, datasetId: string) {
  return 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
  `);
}
/**
 * Gets all call types for a specific species
 */
export async function getSpeciesCallTypes(db: any, speciesId: string) {
  return db.execute(sql`
    SELECT id, label
    FROM call_type
    WHERE species_id = ${speciesId}
      AND active = true
    ORDER BY label
  `);
}
/**
 * Gets all filters for a specific cluster
 */
export async function getClusterFilters(db: any, clusterId: string) {
  return 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 file ON s.file_id = file.id
    WHERE file.cluster_id = ${clusterId}
      AND l.active = true
      AND s.active = true
      AND file.active = true
      AND f.active = true
    ORDER BY f.name
  `);
}
/**
 * Gets all filters for a specific dataset
 */
export async function getDatasetFilters(db: any, datasetId: string) {
  return 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_dataset fd ON s.file_id = fd.file_id
    WHERE fd.dataset_id = ${datasetId}
      AND l.active = true
      AND s.active = true
      AND f.active = true
    ORDER BY f.name
  `);
}
/**
 * Gets the dataset ID for a cluster
 */
export async function getClusterDataset(db: any, clusterId: string) {
  return db.execute(sql`
    SELECT dataset_id FROM cluster WHERE id = ${clusterId} AND active = true
  `);
}
/**
 * Builds the call type filter fragment for SQL queries
 */
function buildCallTypeFilter(callTypeId?: string) {
  if (!callTypeId) {
    return sql``;
  }
  
  return sql`
    AND EXISTS (
      SELECT 1 FROM label_subtype ls 
      WHERE ls.label_id = l.id 
        AND ls.calltype_id = ${callTypeId}
        AND ls.active = true
    )
  `;
}
/**
 * Builds the call rate query for cluster-based analysis
 */
export function buildClusterCallRateQuery(params: CallRateQueryParams) {
  const timeConditions = getTimeFilterConditions(params.timeFilter);
  const callTypeFilter = buildCallTypeFilter(params.callTypeId);
  
  // Time filtering conditions for selections
  let selectionTimeFilter = sql``;
  if (timeConditions) {
    selectionTimeFilter = sql`
      AND s.${sql.identifier(timeConditions.selectionTimeColumn)} = ${timeConditions.selectionTimeValue}
    `;
  }
  
  // Time filtering conditions for files
  let fileTimeFilter = sql``;
  if (timeConditions && params.timeFilter !== "all") {
    fileTimeFilter = sql`
      AND f.${sql.identifier(timeConditions.fileTimeColumn)} = ${timeConditions.fileTimeValue}
    `;
  }
  
  return 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 = ${params.clusterId}
        AND l.species_id = ${params.speciesId}
        AND l.filter_id = ${params.filterId}
        AND l.active = true
        AND s.active = true
        AND f.active = true
        ${callTypeFilter}
        ${selectionTimeFilter}
      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 = ${params.clusterId}
        AND f.active = true
        ${fileTimeFilter}
      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
  `;
}
/**
 * Builds the call rate query for dataset-based analysis
 */
export function buildDatasetCallRateQuery(params: CallRateQueryParams) {
  const timeConditions = getTimeFilterConditions(params.timeFilter);
  const callTypeFilter = buildCallTypeFilter(params.callTypeId);
  
  // Time filtering conditions for selections
  let selectionTimeFilter = sql``;
  if (timeConditions) {
    selectionTimeFilter = sql`
      AND s.${sql.identifier(timeConditions.selectionTimeColumn)} = ${timeConditions.selectionTimeValue}
    `;
  }
  
  // Time filtering conditions for files
  let fileTimeFilter = sql``;
  if (timeConditions && params.timeFilter !== "all") {
    fileTimeFilter = sql`
      AND f.${sql.identifier(timeConditions.fileTimeColumn)} = ${timeConditions.fileTimeValue}
    `;
  }
  
  return 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
      JOIN file_dataset fd ON f.id = fd.file_id
      WHERE fd.dataset_id = ${params.datasetId}
        AND l.species_id = ${params.speciesId}
        AND l.filter_id = ${params.filterId}
        AND l.active = true
        AND s.active = true
        AND f.active = true
        ${callTypeFilter}
        ${selectionTimeFilter}
      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
      JOIN file_dataset fd ON f.id = fd.file_id
      WHERE fd.dataset_id = ${params.datasetId}
        AND f.active = true
        ${fileTimeFilter}
      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
  `;
}
/**
 * Gets call rate per hour for a cluster
 */
export async function getClusterCallRatePerHour(db: any, params: CallRateQueryParams) {
  if (!params.clusterId) {
    throw new Error("clusterId is required for cluster call rate");
  }
  
  const query = buildClusterCallRateQuery(params);
  return db.execute(query);
}
/**
 * Gets call rate per hour for a dataset
 */
export async function getDatasetCallRatePerHour(db: any, params: CallRateQueryParams) {
  if (!params.datasetId) {
    throw new Error("datasetId is required for dataset call rate");
  }
  
  const query = buildDatasetCallRateQuery(params);
  return db.execute(query);
}
/**
 * Gets metadata totals for cluster call rate
 */
export async function getClusterCallRateMetadata(db: any, params: CallRateQueryParams): Promise<QueryMetadata> {
  if (!params.clusterId) {
    throw new Error("clusterId is required for cluster call rate metadata");
  }
  
  const timeConditions = getTimeFilterConditions(params.timeFilter);
  const callTypeFilter = buildCallTypeFilter(params.callTypeId);
  
  // Time filtering conditions for selections
  let selectionTimeFilter = sql``;
  let fileTimeFilter = sql``;
  
  if (timeConditions) {
    selectionTimeFilter = sql`
      AND s.${sql.identifier(timeConditions.selectionTimeColumn)} = ${timeConditions.selectionTimeValue}
    `;
    
    if (params.timeFilter !== "all") {
      fileTimeFilter = sql`
        AND f.${sql.identifier(timeConditions.fileTimeColumn)} = ${timeConditions.fileTimeValue}
      `;
    }
  }
  
  const result = await db.execute(sql`
    SELECT 
      COUNT(l.id) as total_calls,
      ROUND((SUM(f.duration) / 3600.0)::numeric, 2) as total_recording_hours
    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 = ${params.clusterId}
      AND l.species_id = ${params.speciesId}
      AND l.filter_id = ${params.filterId}
      AND l.active = true
      AND s.active = true
      AND f.active = true
      ${callTypeFilter}
      ${selectionTimeFilter}
      ${fileTimeFilter}
  `);
  
  const row = result.rows[0];
  return {
    totalCalls: Number(row?.total_calls || 0),
    totalRecordingHours: Number(row?.total_recording_hours || 0)
  };
}
/**
 * Gets metadata totals for dataset call rate
 */
export async function getDatasetCallRateMetadata(db: any, params: CallRateQueryParams): Promise<QueryMetadata> {
  if (!params.datasetId) {
    throw new Error("datasetId is required for dataset call rate metadata");
  }
  
  const timeConditions = getTimeFilterConditions(params.timeFilter);
  const callTypeFilter = buildCallTypeFilter(params.callTypeId);
  
  // Time filtering conditions for selections
  let selectionTimeFilter = sql``;
  let fileTimeFilter = sql``;
  
  if (timeConditions) {
    selectionTimeFilter = sql`
      AND s.${sql.identifier(timeConditions.selectionTimeColumn)} = ${timeConditions.selectionTimeValue}
    `;
    
    if (params.timeFilter !== "all") {
      fileTimeFilter = sql`
        AND f.${sql.identifier(timeConditions.fileTimeColumn)} = ${timeConditions.fileTimeValue}
      `;
    }
  }
  
  const result = await db.execute(sql`
    SELECT 
      COUNT(l.id) as total_calls,
      ROUND((SUM(f.duration) / 3600.0)::numeric, 2) as total_recording_hours
    FROM selection s
    JOIN label l ON s.id = l.selection_id
    JOIN file f ON s.file_id = f.id
    JOIN file_dataset fd ON f.id = fd.file_id
    WHERE fd.dataset_id = ${params.datasetId}
      AND l.species_id = ${params.speciesId}
      AND l.filter_id = ${params.filterId}
      AND l.active = true
      AND s.active = true
      AND f.active = true
      ${callTypeFilter}
      ${selectionTimeFilter}
      ${fileTimeFilter}
  `);
  
  const row = result.rows[0];
  return {
    totalCalls: Number(row?.total_calls || 0),
    totalRecordingHours: Number(row?.total_recording_hours || 0)
  };
}

/**
 * Statistics API routes - refactored for maintainability
 */
import { Hono } from "hono";
import { authenticate, checkUserPermission } from "../../middleware/auth";
import { createDatabase } from "../../utils/database";
import type { Env, JWTPayload } from "../../types";
import type { 
  CallRateQueryParams, 
  CallRateResponse,
  TimeFilter 
} from "./types";
import {
  validateClusterCallRateParams,
  validateDatasetCallRateParams,
  createErrorResponse,
  createSuccessResponse,
  transformCallRateRows,
  transformSpeciesRows,
  transformCallTypeRows,
  transformFilterRows
} from "./utils";
import {
  getDatasetSpecies,
  getSpeciesCallTypes,
  getClusterFilters,
  getDatasetFilters,
  getClusterDataset,
  getClusterCallRatePerHour,
  getDatasetCallRatePerHour,
  getClusterCallRateMetadata,
  getDatasetCallRateMetadata
} from "./queries";
const statistics = new Hono<{ Bindings: Env }>();
/**
 * 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
 */
statistics.get("/dataset-species", authenticate, async (c) => {
  try {
    const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    const userId = jwtPayload.sub;
    
    const datasetId = c.req.query("datasetId");
    
    if (!datasetId) {
      return c.json(createErrorResponse("Missing required query parameter: datasetId"), 400);
    }
    
    const db = createDatabase(c.env);
    
    // Check permissions
    const hasPermission = await checkUserPermission(db, userId, datasetId, 'READ');
    if (!hasPermission) {
      return c.json(createErrorResponse("Access denied: No READ permission for this dataset"), 403);
    }
    
    // Get species
    const result = await getDatasetSpecies(db, datasetId);
    const species = transformSpeciesRows(result.rows);
    
    return c.json(createSuccessResponse(species));
  } catch (error) {
    console.error("Error fetching dataset species:", error);
    return c.json(
      createErrorResponse(
        "Failed to fetch dataset species",
        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
 */
statistics.get("/species-call-types", authenticate, async (c) => {
  try {
    const speciesId = c.req.query("speciesId");
    
    if (!speciesId) {
      return c.json(createErrorResponse("Missing required query parameter: speciesId"), 400);
    }
    
    const db = createDatabase(c.env);
    
    // For call types, we need to ensure the user has access to at least one dataset
    // containing this species. This is a simplified check.
    // In a more robust implementation, you might want to validate specific access.
    
    const result = await getSpeciesCallTypes(db, speciesId);
    const callTypes = transformCallTypeRows(result.rows);
    
    return c.json(createSuccessResponse(callTypes));
  } catch (error) {
    console.error("Error fetching species call types:", error);
    return c.json(
      createErrorResponse(
        "Failed to fetch species call types",
        error instanceof Error ? error.message : String(error)
      ),
      500
    );
  }
});
/**
 * Get all filters for a specific cluster
 * 
 * @route GET /api/statistics/cluster-filters
 * @authentication Required
 * @query {string} clusterId - The cluster ID to get filters for
 * @returns {Object} Response containing:
 *   - data: Array of filter objects with id and name
 */
statistics.get("/cluster-filters", authenticate, async (c) => {
  try {
    const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    const userId = jwtPayload.sub;
    
    const clusterId = c.req.query("clusterId");
    
    if (!clusterId) {
      return c.json(createErrorResponse("Missing required query parameter: clusterId"), 400);
    }
    
    const db = createDatabase(c.env);
    
    // Get dataset for permission check
    const clusterResult = await getClusterDataset(db, clusterId);
    if (clusterResult.rows.length === 0) {
      return c.json(createErrorResponse("Cluster not found or inactive"), 404);
    }
    
    const datasetId = clusterResult.rows[0].dataset_id as string;
    
    // Check permissions
    const hasPermission = await checkUserPermission(db, userId, datasetId, 'READ');
    if (!hasPermission) {
      return c.json(createErrorResponse("Access denied: No READ permission for this dataset"), 403);
    }
    
    // Get filters
    const result = await getClusterFilters(db, clusterId);
    const filters = transformFilterRows(result.rows);
    
    return c.json(createSuccessResponse(filters));
  } catch (error) {
    console.error("Error fetching cluster filters:", error);
    return c.json(
      createErrorResponse(
        "Failed to fetch cluster filters",
        error instanceof Error ? error.message : String(error)
      ),
      500
    );
  }
});
/**
 * Get all filters for a specific dataset
 * 
 * @route GET /api/statistics/dataset-filters
 * @authentication Required
 * @query {string} datasetId - The dataset ID to get filters for
 * @returns {Object} Response containing:
 *   - data: Array of filter objects with id and name
 */
statistics.get("/dataset-filters", authenticate, async (c) => {
  try {
    const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    const userId = jwtPayload.sub;
    
    const datasetId = c.req.query("datasetId");
    
    if (!datasetId) {
      return c.json(createErrorResponse("Missing required query parameter: datasetId"), 400);
    }
    
    const db = createDatabase(c.env);
    
    // Check permissions
    const hasPermission = await checkUserPermission(db, userId, datasetId, 'READ');
    if (!hasPermission) {
      return c.json(createErrorResponse("Access denied: No READ permission for this dataset"), 403);
    }
    
    // Get filters
    const result = await getDatasetFilters(db, datasetId);
    const filters = transformFilterRows(result.rows);
    
    return c.json(createSuccessResponse(filters));
  } catch (error) {
    console.error("Error fetching dataset filters:", error);
    return c.json(
      createErrorResponse(
        "Failed to fetch dataset filters",
        error instanceof Error ? error.message : String(error)
      ),
      500
    );
  }
});
/**
 * Get call rate per hour statistics for a cluster
 * 
 * @route GET /api/statistics/call-rate-per-hour
 * @authentication Required
 * @query {string} clusterId - The cluster ID to analyze
 * @query {string} speciesId - The species ID to filter by
 * @query {string} filterId - The filter ID to apply
 * @query {string} [timeFilter=civil_night] - Time filter: civil_night, civil_day, solar_night, solar_day, all
 * @query {string} [callTypeId] - Optional call type filter
 * @returns {Object} Response containing:
 *   - data: Array of hourly call rate data
 *   - metadata: Statistics metadata
 */
statistics.get("/call-rate-per-hour", authenticate, async (c) => {
  try {
    const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    const userId = jwtPayload.sub;
    
    // Parse query parameters
    const params: CallRateQueryParams = {
      clusterId: c.req.query("clusterId"),
      speciesId: c.req.query("speciesId") || "",
      filterId: c.req.query("filterId") || "",
      timeFilter: (c.req.query("timeFilter") || "civil_night") as TimeFilter,
      callTypeId: c.req.query("callTypeId")
    };
    
    // Validate parameters
    const validationError = validateClusterCallRateParams(params);
    if (validationError) {
      return c.json(createErrorResponse(validationError), 400);
    }
    
    const db = createDatabase(c.env);
    
    // Get dataset for permission check
    const clusterResult = await getClusterDataset(db, params.clusterId!);
    if (clusterResult.rows.length === 0) {
      return c.json(createErrorResponse("Cluster not found or inactive"), 404);
    }
    
    const datasetId = clusterResult.rows[0].dataset_id as string;
    
    // Check permissions
    const hasPermission = await checkUserPermission(db, userId, datasetId, 'READ');
    if (!hasPermission) {
      return c.json(createErrorResponse("Access denied: No READ permission for this dataset"), 403);
    }
    
    // Get call rate data and metadata in parallel
    const [callRateResult, metadata] = await Promise.all([
      getClusterCallRatePerHour(db, params),
      getClusterCallRateMetadata(db, params)
    ]);
    
    const callRateData = transformCallRateRows(callRateResult.rows);
    
    const response: CallRateResponse = {
      data: callRateData,
      metadata: {
        clusterId: params.clusterId,
        speciesId: params.speciesId,
        filterId: params.filterId,
        timeFilter: params.timeFilter,
        totalHours: 24, // Always 24 hours in a day
        totalCalls: metadata.totalCalls,
        totalRecordingHours: metadata.totalRecordingHours
      }
    };
    
    return c.json(response);
  } catch (error) {
    console.error("Error fetching call rate per hour:", error);
    return c.json(
      createErrorResponse(
        "Failed to fetch call rate per hour",
        error instanceof Error ? error.message : String(error)
      ),
      500
    );
  }
});
/**
 * Get call rate per hour statistics for a dataset
 * 
 * @route GET /api/statistics/dataset-call-rate-per-hour
 * @authentication Required
 * @query {string} datasetId - The dataset ID to analyze
 * @query {string} speciesId - The species ID to filter by
 * @query {string} filterId - The filter ID to apply
 * @query {string} [timeFilter=civil_night] - Time filter: civil_night, civil_day, solar_night, solar_day, all
 * @query {string} [callTypeId] - Optional call type filter
 * @returns {Object} Response containing:
 *   - data: Array of hourly call rate data
 *   - metadata: Statistics metadata
 */
statistics.get("/dataset-call-rate-per-hour", authenticate, async (c) => {
  try {
    const jwtPayload = (c as unknown as { jwtPayload: JWTPayload }).jwtPayload;
    const userId = jwtPayload.sub;
    
    // Parse query parameters
    const params: CallRateQueryParams = {
      datasetId: c.req.query("datasetId"),
      speciesId: c.req.query("speciesId") || "",
      filterId: c.req.query("filterId") || "",
      timeFilter: (c.req.query("timeFilter") || "civil_night") as TimeFilter,
      callTypeId: c.req.query("callTypeId")
    };
    
    // Validate parameters
    const validationError = validateDatasetCallRateParams(params);
    if (validationError) {
      return c.json(createErrorResponse(validationError), 400);
    }
    
    const db = createDatabase(c.env);
    
    // Check permissions
    const hasPermission = await checkUserPermission(db, userId, params.datasetId!, 'READ');
    if (!hasPermission) {
      return c.json(createErrorResponse("Access denied: No READ permission for this dataset"), 403);
    }
    
    // Get call rate data and metadata in parallel
    const [callRateResult, metadata] = await Promise.all([
      getDatasetCallRatePerHour(db, params),
      getDatasetCallRateMetadata(db, params)
    ]);
    
    const callRateData = transformCallRateRows(callRateResult.rows);
    
    const response: CallRateResponse = {
      data: callRateData,
      metadata: {
        datasetId: params.datasetId,
        speciesId: params.speciesId,
        filterId: params.filterId,
        timeFilter: params.timeFilter,
        totalHours: 24, // Always 24 hours in a day
        totalCalls: metadata.totalCalls,
        totalRecordingHours: metadata.totalRecordingHours
      }
    };
    
    return c.json(response);
  } catch (error) {
    console.error("Error fetching dataset call rate per hour:", error);
    return c.json(
      createErrorResponse(
        "Failed to fetch dataset call rate per hour",
        error instanceof Error ? error.message : String(error)
      ),
      500
    );
  }
});
export default statistics;

  clusterId?: string;
  datasetId?: string;

  clusterId?: string | undefined;
  datasetId?: string | undefined;

        setLoading(true);
        setError(null);
        

        <p>No data available</p>

        <p>Loading statistics...</p>