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 }>();
/**
* 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";
// 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", "solar_night", "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);
}
// 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
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 === "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
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 {
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
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
);
}
});
export default statistics;