db_recordings.c

#define _POSIX_C_SOURCE 200809L
#define _XOPEN_SOURCE 700
#define _GNU_SOURCE

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>
#include <time.h>
#include <unistd.h>
#include <sqlite3.h>
#include <stdbool.h>

#include "database/db_recordings.h"
#include "database/db_core.h"
#include "core/logger.h"

#define MAX_MULTI_FILTER_VALUES 32
#define MAX_MULTI_FILTER_VALUE_LEN 128

static void trim_whitespace(char *value) {
    if (!value) {
        return;
    }

    char *start = value;
    while (*start && isspace((unsigned char)*start)) {
        start++;
    }

    if (start != value) {
        memmove(value, start, strlen(start) + 1);
    }

    size_t len = strlen(value);
    while (len > 0 && isspace((unsigned char)value[len - 1])) {
        value[--len] = '\0';
    }
}

static int parse_csv_filter_values(const char *csv,
                                   char values[][MAX_MULTI_FILTER_VALUE_LEN],
                                   int max_values) {
    if (!csv || !*csv || max_values <= 0) {
        return 0;
    }

    char buffer[MAX_MULTI_FILTER_VALUES * MAX_MULTI_FILTER_VALUE_LEN];
    strncpy(buffer, csv, sizeof(buffer) - 1);
    buffer[sizeof(buffer) - 1] = '\0';

    int count = 0;
    char *saveptr = NULL;
    char *token = strtok_r(buffer, ",", &saveptr);

    while (token && count < max_values) {
        trim_whitespace(token);
        if (*token) {
            bool duplicate = false;
            for (int i = 0; i < count; i++) {
                if (strcmp(values[i], token) == 0) {
                    duplicate = true;
                    break;
                }
            }

            if (!duplicate) {
                strncpy(values[count], token, MAX_MULTI_FILTER_VALUE_LEN - 1);
                values[count][MAX_MULTI_FILTER_VALUE_LEN - 1] = '\0';
                count++;
            }
        }

        token = strtok_r(NULL, ",", &saveptr);
    }

    return count;
}

// Add recording metadata to the database
uint64_t add_recording_metadata(const recording_metadata_t *metadata) {
    int rc;
    sqlite3_stmt *stmt;
    uint64_t recording_id = 0;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return 0;
    }

    if (!metadata) {
        log_error("Recording metadata is required");
        return 0;
    }

    pthread_mutex_lock(db_mutex);

    const char *sql = "INSERT INTO recordings (stream_name, file_path, start_time, end_time, "
                      "size_bytes, width, height, fps, codec, is_complete, trigger_type, "
                      "retention_tier, disk_pressure_eligible) "
                      "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return 0;
    }

    // No longer tracking statements - each function is responsible for finalizing its own statements

    // Bind parameters
    sqlite3_bind_text(stmt, 1, metadata->stream_name, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, metadata->file_path, -1, SQLITE_STATIC);
    sqlite3_bind_int64(stmt, 3, (sqlite3_int64)metadata->start_time);

    if (metadata->end_time > 0) {
        sqlite3_bind_int64(stmt, 4, (sqlite3_int64)metadata->end_time);
    } else {
        sqlite3_bind_null(stmt, 4);
    }

    sqlite3_bind_int64(stmt, 5, (sqlite3_int64)metadata->size_bytes);
    sqlite3_bind_int(stmt, 6, metadata->width);
    sqlite3_bind_int(stmt, 7, metadata->height);
    sqlite3_bind_int(stmt, 8, metadata->fps);
    sqlite3_bind_text(stmt, 9, metadata->codec, -1, SQLITE_STATIC);
    sqlite3_bind_int(stmt, 10, metadata->is_complete ? 1 : 0);

    // Bind trigger_type, default to 'scheduled' if not set
    const char *trigger_type = (metadata->trigger_type[0] != '\0') ? metadata->trigger_type : "scheduled";
    sqlite3_bind_text(stmt, 11, trigger_type, -1, SQLITE_STATIC);

    // Bind retention tier and disk pressure eligibility
    sqlite3_bind_int(stmt, 12, metadata->retention_tier);
    sqlite3_bind_int(stmt, 13, metadata->disk_pressure_eligible ? 1 : 0);

    // Execute statement
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        log_error("Failed to add recording metadata: %s", sqlite3_errmsg(db));
    } else {
        recording_id = (uint64_t)sqlite3_last_insert_rowid(db);
        log_debug("Added recording metadata with ID %llu", (unsigned long long)recording_id);
    }

    // Finalize the prepared statement
    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    return recording_id;
}

// Update recording metadata in the database
int update_recording_metadata(uint64_t id, time_t end_time,
                             uint64_t size_bytes, bool is_complete) {
    int rc;
    sqlite3_stmt *stmt;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    const char *sql = "UPDATE recordings SET end_time = ?, size_bytes = ?, is_complete = ? "
                      "WHERE id = ?;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    // No longer tracking statements - each function is responsible for finalizing its own statements

    // Bind parameters
    sqlite3_bind_int64(stmt, 1, (sqlite3_int64)end_time);
    sqlite3_bind_int64(stmt, 2, (sqlite3_int64)size_bytes);
    sqlite3_bind_int(stmt, 3, is_complete ? 1 : 0);
    sqlite3_bind_int64(stmt, 4, (sqlite3_int64)id);

    // Execute statement
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        log_error("Failed to update recording metadata: %s", sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    // Finalize the prepared statement
    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    return 0;
}

/**
 * Correct the start_time of an existing recording in the database.
 *
 * Used after flushing the pre-event circular buffer into a detection recording
 * so that the stored start_time matches the actual first packet timestamp
 * rather than the time mp4_writer_create() was called.
 */
int update_recording_start_time(uint64_t id, time_t start_time) {
    int rc;
    sqlite3_stmt *stmt;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    const char *sql = "UPDATE recordings SET start_time = ? WHERE id = ?;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare update_recording_start_time statement: %s",
                  sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    sqlite3_bind_int64(stmt, 1, (sqlite3_int64)start_time);
    sqlite3_bind_int64(stmt, 2, (sqlite3_int64)id);

    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        log_error("Failed to update recording start_time (id=%llu): %s",
                  (unsigned long long)id, sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    log_debug("Corrected start_time for recording ID %llu to %ld",
              (unsigned long long)id, (long)start_time);
    return 0;
}

// Get recording metadata by ID
int get_recording_metadata_by_id(uint64_t id, recording_metadata_t *metadata) {
    int rc;
    sqlite3_stmt *stmt;
    int result = -1;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    if (!metadata) {
        log_error("Invalid parameters for get_recording_metadata_by_id");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    const char *sql = "SELECT id, stream_name, file_path, start_time, end_time, "
                      "size_bytes, width, height, fps, codec, is_complete, trigger_type, "
                      "protected, retention_override_days, retention_tier, disk_pressure_eligible "
                      "FROM recordings WHERE id = ?;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    // No longer tracking statements - each function is responsible for finalizing its own statements

    // Bind parameters
    sqlite3_bind_int64(stmt, 1, (sqlite3_int64)id);

    // Execute query and fetch result
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        metadata->id = (uint64_t)sqlite3_column_int64(stmt, 0);

        const char *stream = (const char *)sqlite3_column_text(stmt, 1);
        if (stream) {
            strncpy(metadata->stream_name, stream, sizeof(metadata->stream_name) - 1);
            metadata->stream_name[sizeof(metadata->stream_name) - 1] = '\0';
        } else {
            metadata->stream_name[0] = '\0';
        }

        const char *path = (const char *)sqlite3_column_text(stmt, 2);
        if (path) {
            strncpy(metadata->file_path, path, sizeof(metadata->file_path) - 1);
            metadata->file_path[sizeof(metadata->file_path) - 1] = '\0';
        } else {
            metadata->file_path[0] = '\0';
        }

        metadata->start_time = (time_t)sqlite3_column_int64(stmt, 3);

        if (sqlite3_column_type(stmt, 4) != SQLITE_NULL) {
            metadata->end_time = (time_t)sqlite3_column_int64(stmt, 4);
        } else {
            metadata->end_time = 0;
        }

        metadata->size_bytes = (uint64_t)sqlite3_column_int64(stmt, 5);
        metadata->width = sqlite3_column_int(stmt, 6);
        metadata->height = sqlite3_column_int(stmt, 7);
        metadata->fps = sqlite3_column_int(stmt, 8);

        const char *codec = (const char *)sqlite3_column_text(stmt, 9);
        if (codec) {
            strncpy(metadata->codec, codec, sizeof(metadata->codec) - 1);
            metadata->codec[sizeof(metadata->codec) - 1] = '\0';
        } else {
            metadata->codec[0] = '\0';
        }

        metadata->is_complete = sqlite3_column_int(stmt, 10) != 0;

        const char *trigger_type = (const char *)sqlite3_column_text(stmt, 11);
        if (trigger_type) {
            strncpy(metadata->trigger_type, trigger_type, sizeof(metadata->trigger_type) - 1);
            metadata->trigger_type[sizeof(metadata->trigger_type) - 1] = '\0';
        } else {
            strncpy(metadata->trigger_type, "scheduled", sizeof(metadata->trigger_type) - 1);
        }

        metadata->protected = sqlite3_column_int(stmt, 12) != 0;

        if (sqlite3_column_type(stmt, 13) != SQLITE_NULL) {
            metadata->retention_override_days = sqlite3_column_int(stmt, 13);
        } else {
            metadata->retention_override_days = -1;
        }

        metadata->retention_tier = (sqlite3_column_type(stmt, 14) != SQLITE_NULL)
            ? sqlite3_column_int(stmt, 14) : RETENTION_TIER_STANDARD;
        metadata->disk_pressure_eligible = (sqlite3_column_type(stmt, 15) != SQLITE_NULL)
            ? (sqlite3_column_int(stmt, 15) != 0) : true;

        result = 0; // Success
    }

    // Finalize the prepared statement
    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    return result;
}

// Get recording metadata by file path
int get_recording_metadata_by_path(const char *file_path, recording_metadata_t *metadata) {
    int rc;
    sqlite3_stmt *stmt;
    int result = -1;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    if (!file_path || !metadata) {
        log_error("Invalid parameters for get_recording_metadata_by_path");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    const char *sql = "SELECT id, stream_name, file_path, start_time, end_time, "
                      "size_bytes, width, height, fps, codec, is_complete, trigger_type, "
                      "protected, retention_override_days, retention_tier, disk_pressure_eligible "
                      "FROM recordings WHERE file_path = ?;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    sqlite3_bind_text(stmt, 1, file_path, -1, SQLITE_STATIC);

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        metadata->id = (uint64_t)sqlite3_column_int64(stmt, 0);

        const char *stream = (const char *)sqlite3_column_text(stmt, 1);
        if (stream) {
            strncpy(metadata->stream_name, stream, sizeof(metadata->stream_name) - 1);
            metadata->stream_name[sizeof(metadata->stream_name) - 1] = '\0';
        } else {
            metadata->stream_name[0] = '\0';
        }

        const char *path = (const char *)sqlite3_column_text(stmt, 2);
        if (path) {
            strncpy(metadata->file_path, path, sizeof(metadata->file_path) - 1);
            metadata->file_path[sizeof(metadata->file_path) - 1] = '\0';
        } else {
            metadata->file_path[0] = '\0';
        }

        metadata->start_time = (time_t)sqlite3_column_int64(stmt, 3);

        if (sqlite3_column_type(stmt, 4) != SQLITE_NULL) {
            metadata->end_time = (time_t)sqlite3_column_int64(stmt, 4);
        } else {
            metadata->end_time = 0;
        }

        metadata->size_bytes = (uint64_t)sqlite3_column_int64(stmt, 5);
        metadata->width = sqlite3_column_int(stmt, 6);
        metadata->height = sqlite3_column_int(stmt, 7);
        metadata->fps = sqlite3_column_int(stmt, 8);

        const char *codec = (const char *)sqlite3_column_text(stmt, 9);
        if (codec) {
            strncpy(metadata->codec, codec, sizeof(metadata->codec) - 1);
            metadata->codec[sizeof(metadata->codec) - 1] = '\0';
        } else {
            metadata->codec[0] = '\0';
        }

        metadata->is_complete = sqlite3_column_int(stmt, 10) != 0;

        const char *trigger_type = (const char *)sqlite3_column_text(stmt, 11);
        if (trigger_type) {
            strncpy(metadata->trigger_type, trigger_type, sizeof(metadata->trigger_type) - 1);
            metadata->trigger_type[sizeof(metadata->trigger_type) - 1] = '\0';
        } else {
            strncpy(metadata->trigger_type, "scheduled", sizeof(metadata->trigger_type) - 1);
        }

        metadata->protected = sqlite3_column_int(stmt, 12) != 0;

        if (sqlite3_column_type(stmt, 13) != SQLITE_NULL) {
            metadata->retention_override_days = sqlite3_column_int(stmt, 13);
        } else {
            metadata->retention_override_days = -1;
        }

        metadata->retention_tier = (sqlite3_column_type(stmt, 14) != SQLITE_NULL)
            ? sqlite3_column_int(stmt, 14) : RETENTION_TIER_STANDARD;
        metadata->disk_pressure_eligible = (sqlite3_column_type(stmt, 15) != SQLITE_NULL)
            ? (sqlite3_column_int(stmt, 15) != 0) : true;

        result = 0; // Success
    }

    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    return result;
}

// Get recording metadata from the database
int get_recording_metadata(time_t start_time, time_t end_time,
                          const char *stream_name, recording_metadata_t *metadata,
                          int max_count) {
    int rc;
    sqlite3_stmt *stmt;
    int count = 0;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    if (!metadata || max_count <= 0) {
        log_error("Invalid parameters for get_recording_metadata");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    // Build query based on filters
    char sql[1024];
    snprintf(sql, sizeof(sql), "SELECT id, stream_name, file_path, start_time, end_time, "
                 "size_bytes, width, height, fps, codec, is_complete, trigger_type, "
                 "protected, retention_override_days, retention_tier, disk_pressure_eligible "
                 "FROM recordings WHERE is_complete = 1 AND end_time IS NOT NULL"); // Only complete recordings with end_time set

    if (start_time > 0) {
        strncat(sql, " AND start_time >= ?", sizeof(sql) - strlen(sql) - 1);
    }

    if (end_time > 0) {
        strncat(sql, " AND start_time <= ?", sizeof(sql) - strlen(sql) - 1);
    }

    if (stream_name) {
        strncat(sql, " AND stream_name = ?", sizeof(sql) - strlen(sql) - 1);
    }

    strncat(sql, " ORDER BY start_time DESC LIMIT ?;", sizeof(sql) - strlen(sql) - 1);

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    // No longer tracking statements - each function is responsible for finalizing its own statements

    // Bind parameters
    int param_index = 1;

    if (start_time > 0) {
        sqlite3_bind_int64(stmt, param_index++, (sqlite3_int64)start_time);
    }

    if (end_time > 0) {
        sqlite3_bind_int64(stmt, param_index++, (sqlite3_int64)end_time);
    }

    if (stream_name) {
        sqlite3_bind_text(stmt, param_index++, stream_name, -1, SQLITE_STATIC);
    }

    sqlite3_bind_int(stmt, param_index, max_count);

    // Execute query and fetch results
    int rc_step;
    while ((rc_step = sqlite3_step(stmt)) == SQLITE_ROW && count < max_count) {
        // Safely copy data to metadata structure
        {
            metadata[count].id = (uint64_t)sqlite3_column_int64(stmt, 0);

            const char *stream = (const char *)sqlite3_column_text(stmt, 1);
            if (stream) {
                strncpy(metadata[count].stream_name, stream, sizeof(metadata[count].stream_name) - 1);
                metadata[count].stream_name[sizeof(metadata[count].stream_name) - 1] = '\0';
            } else {
                metadata[count].stream_name[0] = '\0';
            }

            const char *path = (const char *)sqlite3_column_text(stmt, 2);
            if (path) {
                strncpy(metadata[count].file_path, path, sizeof(metadata[count].file_path) - 1);
                metadata[count].file_path[sizeof(metadata[count].file_path) - 1] = '\0';
            } else {
                metadata[count].file_path[0] = '\0';
            }

            metadata[count].start_time = (time_t)sqlite3_column_int64(stmt, 3);

            if (sqlite3_column_type(stmt, 4) != SQLITE_NULL) {
                metadata[count].end_time = (time_t)sqlite3_column_int64(stmt, 4);
            } else {
                metadata[count].end_time = 0;
            }

            metadata[count].size_bytes = (uint64_t)sqlite3_column_int64(stmt, 5);
            metadata[count].width = sqlite3_column_int(stmt, 6);
            metadata[count].height = sqlite3_column_int(stmt, 7);
            metadata[count].fps = sqlite3_column_int(stmt, 8);

            const char *codec = (const char *)sqlite3_column_text(stmt, 9);
            if (codec) {
                strncpy(metadata[count].codec, codec, sizeof(metadata[count].codec) - 1);
                metadata[count].codec[sizeof(metadata[count].codec) - 1] = '\0';
            } else {
                metadata[count].codec[0] = '\0';
            }

            metadata[count].is_complete = sqlite3_column_int(stmt, 10) != 0;

            const char *trigger_type = (const char *)sqlite3_column_text(stmt, 11);
            if (trigger_type) {
                strncpy(metadata[count].trigger_type, trigger_type, sizeof(metadata[count].trigger_type) - 1);
                metadata[count].trigger_type[sizeof(metadata[count].trigger_type) - 1] = '\0';
            } else {
                strncpy(metadata[count].trigger_type, "scheduled", sizeof(metadata[count].trigger_type) - 1);
            }

            metadata[count].protected = sqlite3_column_int(stmt, 12) != 0;

            if (sqlite3_column_type(stmt, 13) != SQLITE_NULL) {
                metadata[count].retention_override_days = sqlite3_column_int(stmt, 13);
            } else {
                metadata[count].retention_override_days = -1;
            }

            metadata[count].retention_tier = (sqlite3_column_type(stmt, 14) != SQLITE_NULL)
                ? sqlite3_column_int(stmt, 14) : RETENTION_TIER_STANDARD;
            metadata[count].disk_pressure_eligible = (sqlite3_column_type(stmt, 15) != SQLITE_NULL)
                ? (sqlite3_column_int(stmt, 15) != 0) : true;

            count++;
        }
    }

    if (rc_step != SQLITE_DONE && rc_step != SQLITE_ROW) {
        log_error("Error while fetching recordings: %s", sqlite3_errmsg(db));
    }

    // Finalize the prepared statement
    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    log_info("Found %d recordings in database matching criteria", count);
    return count;
}

// Get total count of recordings matching filter criteria
int get_recording_count(time_t start_time, time_t end_time,
                       const char *stream_name, int has_detection,
                       const char *detection_label, int protected_filter,
                       const char * const *allowed_streams, int allowed_streams_count,
                       const char *tag_filter, const char *capture_method_filter) {
    int rc;
    sqlite3_stmt *stmt;
    int count = 0;
    char stream_filters[MAX_MULTI_FILTER_VALUES][MAX_MULTI_FILTER_VALUE_LEN] = {{0}};
    char detection_label_filters[MAX_MULTI_FILTER_VALUES][MAX_MULTI_FILTER_VALUE_LEN] = {{0}};
    char tag_filters[MAX_MULTI_FILTER_VALUES][MAX_MULTI_FILTER_VALUE_LEN] = {{0}};
    char capture_method_filters[MAX_MULTI_FILTER_VALUES][MAX_MULTI_FILTER_VALUE_LEN] = {{0}};
    int stream_filter_count = parse_csv_filter_values(stream_name, stream_filters, MAX_MULTI_FILTER_VALUES);
    int detection_label_count = parse_csv_filter_values(detection_label, detection_label_filters, MAX_MULTI_FILTER_VALUES);
    int tag_filter_count = parse_csv_filter_values(tag_filter, tag_filters, MAX_MULTI_FILTER_VALUES);
    int capture_method_count = parse_csv_filter_values(capture_method_filter, capture_method_filters, MAX_MULTI_FILTER_VALUES);

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    // Build query based on filters
    char sql[8192];

    // Use trigger_type and/or detections table to filter detection-based recordings
    snprintf(sql, sizeof(sql), "SELECT COUNT(*) FROM recordings r WHERE r.is_complete = 1 AND r.end_time IS NOT NULL");

    if (has_detection == 1) {
        // Filter by trigger_type = 'detection' OR existence of linked detections via recording_id (fast index lookup)
        // Falls back to timestamp range scan for legacy detections without recording_id
        strncat(sql, " AND (r.trigger_type = 'detection'"
                    " OR EXISTS (SELECT 1 FROM detections d WHERE d.recording_id = r.id)"
                    " OR EXISTS (SELECT 1 FROM detections d WHERE d.stream_name = r.stream_name"
                    " AND d.timestamp >= r.start_time AND d.timestamp <= r.end_time))",
                    sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding detection filter (trigger_type OR recording_id OR timestamp range)");
    } else if (has_detection == -1) {
        // Filter to recordings with NO detections
        strncat(sql, " AND (r.trigger_type != 'detection' OR r.trigger_type IS NULL)"
                    " AND NOT EXISTS (SELECT 1 FROM detections d WHERE d.recording_id = r.id)"
                    " AND NOT EXISTS (SELECT 1 FROM detections d WHERE d.stream_name = r.stream_name"
                    " AND d.timestamp >= r.start_time AND d.timestamp <= r.end_time)",
                    sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding no-detection filter (no trigger_type AND no linked detections)");
    }

    if (detection_label_count > 0) {
        // Filter by specific detection label - prefer recording_id FK lookup, fall back to timestamp range
        strncat(sql, " AND (EXISTS (SELECT 1 FROM detections d WHERE d.recording_id = r.id AND (",
                sizeof(sql) - strlen(sql) - 1);
        for (int i = 0; i < detection_label_count; i++) {
            if (i > 0) strncat(sql, " OR ", sizeof(sql) - strlen(sql) - 1);
            strncat(sql, "d.label LIKE ?", sizeof(sql) - strlen(sql) - 1);
        }
        strncat(sql, ")) OR EXISTS (SELECT 1 FROM detections d WHERE d.recording_id IS NULL"
                    " AND d.stream_name = r.stream_name AND d.timestamp >= r.start_time"
                    " AND d.timestamp <= r.end_time AND (",
                    sizeof(sql) - strlen(sql) - 1);
        for (int i = 0; i < detection_label_count; i++) {
            if (i > 0) strncat(sql, " OR ", sizeof(sql) - strlen(sql) - 1);
            strncat(sql, "d.label LIKE ?", sizeof(sql) - strlen(sql) - 1);
        }
        strncat(sql, ")))", sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding %d detection_label filters", detection_label_count);
    }

    if (start_time > 0) {
        strncat(sql, " AND r.start_time >= ?", sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding start_time filter: %ld", (long)start_time);
    }

    if (end_time > 0) {
        strncat(sql, " AND r.start_time <= ?", sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding end_time filter: %ld", (long)end_time);
    }

    if (stream_filter_count > 0) {
        strncat(sql, " AND r.stream_name IN (", sizeof(sql) - strlen(sql) - 1);
        for (int i = 0; i < stream_filter_count; i++) {
            if (i > 0) strncat(sql, ",", sizeof(sql) - strlen(sql) - 1);
            strncat(sql, "?", sizeof(sql) - strlen(sql) - 1);
        }
        strncat(sql, ")", sizeof(sql) - strlen(sql) - 1);
    } else if (allowed_streams && allowed_streams_count > 0) {
        // Tag-based RBAC: restrict to the user's whitelisted streams via IN clause
        strncat(sql, " AND r.stream_name IN (", sizeof(sql) - strlen(sql) - 1);
        for (int i = 0; i < allowed_streams_count; i++) {
            if (i > 0) strncat(sql, ",", sizeof(sql) - strlen(sql) - 1);
            strncat(sql, "?", sizeof(sql) - strlen(sql) - 1);
        }
        strncat(sql, ")", sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding allowed_streams IN filter (%d streams)", allowed_streams_count);
    }

    if (protected_filter == 0) {
        strncat(sql, " AND r.protected = 0", sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding protected_filter=0 (unprotected only)");
    } else if (protected_filter == 1) {
        strncat(sql, " AND r.protected = 1", sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding protected_filter=1 (protected only)");
    }

    if (tag_filter_count > 0) {
        strncat(sql, " AND EXISTS (SELECT 1 FROM recording_tags rt WHERE rt.recording_id = r.id AND rt.tag IN (",
                sizeof(sql) - strlen(sql) - 1);
        for (int i = 0; i < tag_filter_count; i++) {
            if (i > 0) strncat(sql, ",", sizeof(sql) - strlen(sql) - 1);
            strncat(sql, "?", sizeof(sql) - strlen(sql) - 1);
        }
        strncat(sql, "))", sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding %d tag filters", tag_filter_count);
    }

    if (capture_method_count > 0) {
        strncat(sql, " AND COALESCE(r.trigger_type, 'scheduled') IN (", sizeof(sql) - strlen(sql) - 1);
        for (int i = 0; i < capture_method_count; i++) {
            if (i > 0) strncat(sql, ",", sizeof(sql) - strlen(sql) - 1);
            strncat(sql, "?", sizeof(sql) - strlen(sql) - 1);
        }
        strncat(sql, ")", sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding %d capture_method filters", capture_method_count);
    }

    log_debug("SQL query for get_recording_count: %s", sql);

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    // No longer tracking statements - each function is responsible for finalizing its own statements

    // Bind parameters
    int param_index = 1;

    if (detection_label_count > 0) {
        for (int i = 0; i < detection_label_count; i++) {
            char label_pattern[MAX_MULTI_FILTER_VALUE_LEN + 2];
            snprintf(label_pattern, sizeof(label_pattern), "%%%s%%", detection_label_filters[i]);
            sqlite3_bind_text(stmt, param_index++, label_pattern, -1, SQLITE_TRANSIENT);
        }
        for (int i = 0; i < detection_label_count; i++) {
            char label_pattern[MAX_MULTI_FILTER_VALUE_LEN + 2];
            snprintf(label_pattern, sizeof(label_pattern), "%%%s%%", detection_label_filters[i]);
            sqlite3_bind_text(stmt, param_index++, label_pattern, -1, SQLITE_TRANSIENT);
        }
    }

    if (start_time > 0) {
        sqlite3_bind_int64(stmt, param_index++, (sqlite3_int64)start_time);
    }

    if (end_time > 0) {
        sqlite3_bind_int64(stmt, param_index++, (sqlite3_int64)end_time);
    }

    if (stream_filter_count > 0) {
        for (int i = 0; i < stream_filter_count; i++) {
            sqlite3_bind_text(stmt, param_index++, stream_filters[i], -1, SQLITE_TRANSIENT);
        }
    } else if (allowed_streams && allowed_streams_count > 0) {
        for (int i = 0; i < allowed_streams_count; i++) {
            sqlite3_bind_text(stmt, param_index++, allowed_streams[i], -1, SQLITE_STATIC);
        }
    }

    for (int i = 0; i < tag_filter_count; i++) {
        sqlite3_bind_text(stmt, param_index++, tag_filters[i], -1, SQLITE_TRANSIENT);
    }

    for (int i = 0; i < capture_method_count; i++) {
        sqlite3_bind_text(stmt, param_index++, capture_method_filters[i], -1, SQLITE_TRANSIENT);
    }

    // Execute query and get count
    if (sqlite3_step(stmt) == SQLITE_ROW) {
        count = sqlite3_column_int(stmt, 0);
    } else {
        log_error("Error while getting recording count: %s", sqlite3_errmsg(db));
        count = -1;
    }

    // Finalize the prepared statement
    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    log_debug("Total count of recordings matching criteria: %d", count);
    return count;
}

// Get paginated recording metadata from the database with sorting
int get_recording_metadata_paginated(time_t start_time, time_t end_time,
                                   const char *stream_name, int has_detection,
                                   const char *detection_label,
                                   int protected_filter,
                                   const char *sort_field, const char *sort_order,
                                   recording_metadata_t *metadata,
                                   int limit, int offset,
                                   const char * const *allowed_streams, int allowed_streams_count,
                                   const char *tag_filter, const char *capture_method_filter) {
    int rc;
    sqlite3_stmt *stmt;
    int count = 0;
    char stream_filters[MAX_MULTI_FILTER_VALUES][MAX_MULTI_FILTER_VALUE_LEN] = {{0}};
    char detection_label_filters[MAX_MULTI_FILTER_VALUES][MAX_MULTI_FILTER_VALUE_LEN] = {{0}};
    char tag_filters[MAX_MULTI_FILTER_VALUES][MAX_MULTI_FILTER_VALUE_LEN] = {{0}};
    char capture_method_filters[MAX_MULTI_FILTER_VALUES][MAX_MULTI_FILTER_VALUE_LEN] = {{0}};
    int stream_filter_count = parse_csv_filter_values(stream_name, stream_filters, MAX_MULTI_FILTER_VALUES);
    int detection_label_count = parse_csv_filter_values(detection_label, detection_label_filters, MAX_MULTI_FILTER_VALUES);
    int tag_filter_count = parse_csv_filter_values(tag_filter, tag_filters, MAX_MULTI_FILTER_VALUES);
    int capture_method_count = parse_csv_filter_values(capture_method_filter, capture_method_filters, MAX_MULTI_FILTER_VALUES);

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    if (!metadata || limit <= 0) {
        log_error("Invalid parameters for get_recording_metadata_paginated");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    // Validate and sanitize sort field to prevent SQL injection
    char safe_sort_field[32] = "start_time"; // Default sort field
    if (sort_field) {
        if (strcmp(sort_field, "id") == 0 ||
            strcmp(sort_field, "stream_name") == 0 ||
            strcmp(sort_field, "start_time") == 0 ||
            strcmp(sort_field, "end_time") == 0 ||
            strcmp(sort_field, "size_bytes") == 0) {
            strncpy(safe_sort_field, sort_field, sizeof(safe_sort_field) - 1);
            safe_sort_field[sizeof(safe_sort_field) - 1] = '\0';
        } else {
            log_warn("Invalid sort field: %s, using default", sort_field);
        }
    }

    // Validate sort order
    char safe_sort_order[8] = "DESC"; // Default sort order
    if (sort_order) {
        if (strcasecmp(sort_order, "asc") == 0) {
            strcpy(safe_sort_order, "ASC");
        } else if (strcasecmp(sort_order, "desc") == 0) {
            strcpy(safe_sort_order, "DESC");
        } else {
            log_warn("Invalid sort order: %s, using default", sort_order);
        }
    }

    // Build query based on filters
    char sql[8192];

    // Use trigger_type and/or detections table to filter detection-based recordings
    snprintf(sql, sizeof(sql),
            "SELECT r.id, r.stream_name, r.file_path, r.start_time, r.end_time, "
            "r.size_bytes, r.width, r.height, r.fps, r.codec, r.is_complete, r.trigger_type, "
            "r.protected, r.retention_override_days, r.retention_tier, r.disk_pressure_eligible "
            "FROM recordings r WHERE r.is_complete = 1 AND r.end_time IS NOT NULL");

    if (has_detection == 1) {
        // Filter by trigger_type = 'detection' OR existence of linked detections via recording_id (fast index lookup)
        // Falls back to timestamp range scan for legacy detections without recording_id
        strncat(sql, " AND (r.trigger_type = 'detection'"
                    " OR EXISTS (SELECT 1 FROM detections d WHERE d.recording_id = r.id)"
                    " OR EXISTS (SELECT 1 FROM detections d WHERE d.stream_name = r.stream_name"
                    " AND d.timestamp >= r.start_time AND d.timestamp <= r.end_time))",
                    sizeof(sql) - strlen(sql) - 1);
        log_info("Adding detection filter (trigger_type OR recording_id OR timestamp range)");
    } else if (has_detection == -1) {
        // Filter to recordings with NO detections
        strncat(sql, " AND (r.trigger_type != 'detection' OR r.trigger_type IS NULL)"
                    " AND NOT EXISTS (SELECT 1 FROM detections d WHERE d.recording_id = r.id)"
                    " AND NOT EXISTS (SELECT 1 FROM detections d WHERE d.stream_name = r.stream_name"
                    " AND d.timestamp >= r.start_time AND d.timestamp <= r.end_time)",
                    sizeof(sql) - strlen(sql) - 1);
        log_info("Adding no-detection filter (no trigger_type AND no linked detections)");
    }

    if (detection_label_count > 0) {
        // Filter by specific detection label - prefer recording_id FK lookup, fall back to timestamp range
        strncat(sql, " AND (EXISTS (SELECT 1 FROM detections d WHERE d.recording_id = r.id AND (",
                sizeof(sql) - strlen(sql) - 1);
        for (int i = 0; i < detection_label_count; i++) {
            if (i > 0) strncat(sql, " OR ", sizeof(sql) - strlen(sql) - 1);
            strncat(sql, "d.label LIKE ?", sizeof(sql) - strlen(sql) - 1);
        }
        strncat(sql, ")) OR EXISTS (SELECT 1 FROM detections d WHERE d.recording_id IS NULL"
                    " AND d.stream_name = r.stream_name AND d.timestamp >= r.start_time"
                    " AND d.timestamp <= r.end_time AND (",
                    sizeof(sql) - strlen(sql) - 1);
        for (int i = 0; i < detection_label_count; i++) {
            if (i > 0) strncat(sql, " OR ", sizeof(sql) - strlen(sql) - 1);
            strncat(sql, "d.label LIKE ?", sizeof(sql) - strlen(sql) - 1);
        }
        strncat(sql, ")))", sizeof(sql) - strlen(sql) - 1);
        log_info("Adding %d detection_label filters", detection_label_count);
    }

    if (start_time > 0) {
        strncat(sql, " AND r.start_time >= ?", sizeof(sql) - strlen(sql) - 1);
        log_info("Adding start_time filter to paginated query: %ld", (long)start_time);
    }

    if (end_time > 0) {
        strncat(sql, " AND r.start_time <= ?", sizeof(sql) - strlen(sql) - 1);
        log_info("Adding end_time filter to paginated query: %ld", (long)end_time);
    }

    if (stream_filter_count > 0) {
        strncat(sql, " AND r.stream_name IN (", sizeof(sql) - strlen(sql) - 1);
        for (int i = 0; i < stream_filter_count; i++) {
            if (i > 0) strncat(sql, ",", sizeof(sql) - strlen(sql) - 1);
            strncat(sql, "?", sizeof(sql) - strlen(sql) - 1);
        }
        strncat(sql, ")", sizeof(sql) - strlen(sql) - 1);
    } else if (allowed_streams && allowed_streams_count > 0) {
        // Tag-based RBAC: restrict to the user's whitelisted streams via IN clause
        strncat(sql, " AND r.stream_name IN (", sizeof(sql) - strlen(sql) - 1);
        for (int i = 0; i < allowed_streams_count; i++) {
            if (i > 0) strncat(sql, ",", sizeof(sql) - strlen(sql) - 1);
            strncat(sql, "?", sizeof(sql) - strlen(sql) - 1);
        }
        strncat(sql, ")", sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding allowed_streams IN filter (%d streams) to paginated query", allowed_streams_count);
    }

    if (protected_filter == 0) {
        strncat(sql, " AND r.protected = 0", sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding protected_filter=0 (unprotected only) to paginated query");
    } else if (protected_filter == 1) {
        strncat(sql, " AND r.protected = 1", sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding protected_filter=1 (protected only) to paginated query");
    }

    if (tag_filter_count > 0) {
        strncat(sql, " AND EXISTS (SELECT 1 FROM recording_tags rt WHERE rt.recording_id = r.id AND rt.tag IN (",
                sizeof(sql) - strlen(sql) - 1);
        for (int i = 0; i < tag_filter_count; i++) {
            if (i > 0) strncat(sql, ",", sizeof(sql) - strlen(sql) - 1);
            strncat(sql, "?", sizeof(sql) - strlen(sql) - 1);
        }
        strncat(sql, "))", sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding %d tag filters to paginated query", tag_filter_count);
    }

    if (capture_method_count > 0) {
        strncat(sql, " AND COALESCE(r.trigger_type, 'scheduled') IN (", sizeof(sql) - strlen(sql) - 1);
        for (int i = 0; i < capture_method_count; i++) {
            if (i > 0) strncat(sql, ",", sizeof(sql) - strlen(sql) - 1);
            strncat(sql, "?", sizeof(sql) - strlen(sql) - 1);
        }
        strncat(sql, ")", sizeof(sql) - strlen(sql) - 1);
        log_debug("Adding %d capture_method filters to paginated query", capture_method_count);
    }

    // Add ORDER BY clause with sanitized field and order
    char order_clause[64];
    snprintf(order_clause, sizeof(order_clause), " ORDER BY r.%s %s", safe_sort_field, safe_sort_order);
    strncat(sql, order_clause, sizeof(sql) - strlen(sql) - 1);

    // Add LIMIT and OFFSET for pagination
    char limit_clause[64];
    snprintf(limit_clause, sizeof(limit_clause), " LIMIT ? OFFSET ?");
    strncat(sql, limit_clause, sizeof(sql) - strlen(sql) - 1);

    log_debug("SQL query for get_recording_metadata_paginated: %s", sql);

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    // No longer tracking statements - each function is responsible for finalizing its own statements

    // Bind parameters
    int param_index = 1;

    if (detection_label_count > 0) {
        for (int i = 0; i < detection_label_count; i++) {
            char label_pattern[MAX_MULTI_FILTER_VALUE_LEN + 2];
            snprintf(label_pattern, sizeof(label_pattern), "%%%s%%", detection_label_filters[i]);
            sqlite3_bind_text(stmt, param_index++, label_pattern, -1, SQLITE_TRANSIENT);
        }
        for (int i = 0; i < detection_label_count; i++) {
            char label_pattern[MAX_MULTI_FILTER_VALUE_LEN + 2];
            snprintf(label_pattern, sizeof(label_pattern), "%%%s%%", detection_label_filters[i]);
            sqlite3_bind_text(stmt, param_index++, label_pattern, -1, SQLITE_TRANSIENT);
        }
    }

    if (start_time > 0) {
        sqlite3_bind_int64(stmt, param_index++, (sqlite3_int64)start_time);
    }

    if (end_time > 0) {
        sqlite3_bind_int64(stmt, param_index++, (sqlite3_int64)end_time);
    }

    if (stream_filter_count > 0) {
        for (int i = 0; i < stream_filter_count; i++) {
            sqlite3_bind_text(stmt, param_index++, stream_filters[i], -1, SQLITE_TRANSIENT);
        }
    } else if (allowed_streams && allowed_streams_count > 0) {
        for (int i = 0; i < allowed_streams_count; i++) {
            sqlite3_bind_text(stmt, param_index++, allowed_streams[i], -1, SQLITE_STATIC);
        }
    }

    for (int i = 0; i < tag_filter_count; i++) {
        sqlite3_bind_text(stmt, param_index++, tag_filters[i], -1, SQLITE_TRANSIENT);
    }

    for (int i = 0; i < capture_method_count; i++) {
        sqlite3_bind_text(stmt, param_index++, capture_method_filters[i], -1, SQLITE_TRANSIENT);
    }

    // Bind LIMIT and OFFSET parameters
    sqlite3_bind_int(stmt, param_index++, limit);
    sqlite3_bind_int(stmt, param_index, offset);

    // Execute query and fetch results
    int rc_step;
    while ((rc_step = sqlite3_step(stmt)) == SQLITE_ROW && count < limit) {
        // Safely copy data to metadata structure
        {
            metadata[count].id = (uint64_t)sqlite3_column_int64(stmt, 0);

            const char *stream = (const char *)sqlite3_column_text(stmt, 1);
            if (stream) {
                strncpy(metadata[count].stream_name, stream, sizeof(metadata[count].stream_name) - 1);
                metadata[count].stream_name[sizeof(metadata[count].stream_name) - 1] = '\0';
            } else {
                metadata[count].stream_name[0] = '\0';
            }

            const char *path = (const char *)sqlite3_column_text(stmt, 2);
            if (path) {
                strncpy(metadata[count].file_path, path, sizeof(metadata[count].file_path) - 1);
                metadata[count].file_path[sizeof(metadata[count].file_path) - 1] = '\0';
            } else {
                metadata[count].file_path[0] = '\0';
            }

            metadata[count].start_time = (time_t)sqlite3_column_int64(stmt, 3);

            if (sqlite3_column_type(stmt, 4) != SQLITE_NULL) {
                metadata[count].end_time = (time_t)sqlite3_column_int64(stmt, 4);
            } else {
                metadata[count].end_time = 0;
            }

            metadata[count].size_bytes = (uint64_t)sqlite3_column_int64(stmt, 5);
            metadata[count].width = sqlite3_column_int(stmt, 6);
            metadata[count].height = sqlite3_column_int(stmt, 7);
            metadata[count].fps = sqlite3_column_int(stmt, 8);

            const char *codec = (const char *)sqlite3_column_text(stmt, 9);
            if (codec) {
                strncpy(metadata[count].codec, codec, sizeof(metadata[count].codec) - 1);
                metadata[count].codec[sizeof(metadata[count].codec) - 1] = '\0';
            } else {
                metadata[count].codec[0] = '\0';
            }

            metadata[count].is_complete = sqlite3_column_int(stmt, 10) != 0;

            const char *trigger_type = (const char *)sqlite3_column_text(stmt, 11);
            if (trigger_type) {
                strncpy(metadata[count].trigger_type, trigger_type, sizeof(metadata[count].trigger_type) - 1);
                metadata[count].trigger_type[sizeof(metadata[count].trigger_type) - 1] = '\0';
            } else {
                strncpy(metadata[count].trigger_type, "scheduled", sizeof(metadata[count].trigger_type) - 1);
            }

            metadata[count].protected = sqlite3_column_int(stmt, 12) != 0;

            if (sqlite3_column_type(stmt, 13) != SQLITE_NULL) {
                metadata[count].retention_override_days = sqlite3_column_int(stmt, 13);
            } else {
                metadata[count].retention_override_days = -1;
            }

            metadata[count].retention_tier = (sqlite3_column_type(stmt, 14) != SQLITE_NULL)
                ? sqlite3_column_int(stmt, 14) : RETENTION_TIER_STANDARD;
            metadata[count].disk_pressure_eligible = (sqlite3_column_type(stmt, 15) != SQLITE_NULL)
                ? (sqlite3_column_int(stmt, 15) != 0) : true;

            count++;
        }
    }

    if (rc_step != SQLITE_DONE && rc_step != SQLITE_ROW) {
        log_error("Error while fetching recordings: %s", sqlite3_errmsg(db));
    }

    // Finalize the prepared statement
    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    log_debug("Found %d recordings in database matching criteria (page %d, limit %d)",
             count, (offset / limit) + 1, limit);
    return count;
}

// Delete recording metadata from the database
int delete_recording_metadata(uint64_t id) {
    int rc;
    sqlite3_stmt *stmt;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    // First, clear any foreign key references in the detections table
    // The detections table has FOREIGN KEY (recording_id) REFERENCES recordings(id)
    // without ON DELETE CASCADE, so we must nullify references before deleting
    const char *clear_fk_sql = "UPDATE detections SET recording_id = NULL WHERE recording_id = ?;";
    rc = sqlite3_prepare_v2(db, clear_fk_sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare detections FK cleanup: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }
    sqlite3_bind_int64(stmt, 1, (sqlite3_int64)id);
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        log_error("Failed to clear detections FK for recording %llu: %s",
                  (unsigned long long)id, sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        pthread_mutex_unlock(db_mutex);
        return -1;
    }
    sqlite3_finalize(stmt);

    // Now delete the recording
    const char *sql = "DELETE FROM recordings WHERE id = ?;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    // Bind parameters
    sqlite3_bind_int64(stmt, 1, (sqlite3_int64)id);

    // Execute statement
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        log_error("Failed to delete recording metadata: %s", sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    // Finalize the prepared statement
    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    return 0;
}

// Delete old recording metadata from the database
int delete_old_recording_metadata(uint64_t max_age) {
    int rc;
    sqlite3_stmt *stmt;
    int deleted_count = 0;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    // Calculate cutoff time
    time_t cutoff_time = time(NULL) - (time_t)max_age;

    pthread_mutex_lock(db_mutex);

    // First, clear foreign key references in detections for recordings about to be deleted
    const char *clear_fk_sql = "UPDATE detections SET recording_id = NULL "
                               "WHERE recording_id IN (SELECT id FROM recordings WHERE end_time < ?);";
    rc = sqlite3_prepare_v2(db, clear_fk_sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare detections FK cleanup for old recordings: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }
    sqlite3_bind_int64(stmt, 1, (sqlite3_int64)cutoff_time);
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        log_error("Failed to clear detections FK for old recordings: %s", sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        pthread_mutex_unlock(db_mutex);
        return -1;
    }
    sqlite3_finalize(stmt);

    // Now delete the old recordings
    const char *sql = "DELETE FROM recordings WHERE end_time < ?;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    // Bind parameters
    sqlite3_bind_int64(stmt, 1, (sqlite3_int64)cutoff_time);

    // Execute statement
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        log_error("Failed to delete old recording metadata: %s", sqlite3_errmsg(db));
        sqlite3_finalize(stmt);
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    deleted_count = sqlite3_changes(db);

    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    return deleted_count;
}

/**
 * Set protection status for a recording
 *
 * @param id Recording ID
 * @param protected Whether to protect the recording
 * @return 0 on success, non-zero on failure
 */
int set_recording_protected(uint64_t id, bool protected) {
    int rc;
    sqlite3_stmt *stmt;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    const char *sql = "UPDATE recordings SET protected = ? WHERE id = ?;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    sqlite3_bind_int(stmt, 1, protected ? 1 : 0);
    sqlite3_bind_int64(stmt, 2, (sqlite3_int64)id);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    if (rc != SQLITE_DONE) {
        log_error("Failed to update recording protection: %s", sqlite3_errmsg(db));
        return -1;
    }

    log_info("Recording %llu protection set to %s", (unsigned long long)id, protected ? "true" : "false");
    return 0;
}

/**
 * Set custom retention override for a recording
 *
 * @param id Recording ID
 * @param days Custom retention days (-1 to remove override)
 * @return 0 on success, non-zero on failure
 */
int set_recording_retention_override(uint64_t id, int days) {
    int rc;
    sqlite3_stmt *stmt;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    const char *sql = "UPDATE recordings SET retention_override_days = ? WHERE id = ?;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    if (days < 0) {
        sqlite3_bind_null(stmt, 1);
    } else {
        sqlite3_bind_int(stmt, 1, days);
    }
    sqlite3_bind_int64(stmt, 2, (sqlite3_int64)id);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    if (rc != SQLITE_DONE) {
        log_error("Failed to update recording retention override: %s", sqlite3_errmsg(db));
        return -1;
    }

    log_info("Recording %llu retention override set to %d days", (unsigned long long)id, days);
    return 0;
}

/**
 * Get count of protected recordings for a stream
 *
 * @param stream_name Stream name (NULL for all streams)
 * @return Count of protected recordings, or -1 on error
 */
int get_protected_recordings_count(const char *stream_name) {
    int rc;
    sqlite3_stmt *stmt;
    int count = -1;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    const char *sql;
    if (stream_name) {
        sql = "SELECT COUNT(*) FROM recordings WHERE protected = 1 AND stream_name = ?;";
    } else {
        sql = "SELECT COUNT(*) FROM recordings WHERE protected = 1;";
    }

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    if (stream_name) {
        sqlite3_bind_text(stmt, 1, stream_name, -1, SQLITE_STATIC);
    }

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        count = sqlite3_column_int(stmt, 0);
    }

    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    return count;
}


/**
 * Get recordings eligible for deletion based on retention policy
 * Priority 1: Regular recordings past retention period
 * Priority 2: Detection recordings past detection retention period
 * Protected recordings are never returned
 *
 * @param stream_name Stream name to filter
 * @param retention_days Regular recordings retention in days
 * @param detection_retention_days Detection recordings retention in days
 * @param recordings Array to fill with recording metadata
 * @param max_count Maximum number of recordings to return
 * @return Number of recordings found, or -1 on error
 */
int get_recordings_for_retention(const char *stream_name,
                                 int retention_days,
                                 int detection_retention_days,
                                 recording_metadata_t *recordings,
                                 int max_count) {
    int rc;
    sqlite3_stmt *stmt;
    int count = 0;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    if (!stream_name || !recordings || max_count <= 0) {
        log_error("Invalid parameters for get_recordings_for_retention");
        return -1;
    }

    // Calculate cutoff times
    time_t now = time(NULL);
    time_t regular_cutoff = (retention_days > 0) ? now - ((time_t)retention_days * 86400) : 0;
    time_t detection_cutoff = (detection_retention_days > 0) ? now - ((time_t)detection_retention_days * 86400) : 0;

    pthread_mutex_lock(db_mutex);

    // Query for recordings past retention, ordered by priority (regular first, then detection)
    // and by start_time (oldest first)
    // Protected recordings are excluded
    // Also exclude recordings with retention_override_days that haven't expired yet
    const char *sql =
        "SELECT id, stream_name, file_path, start_time, end_time, "
        "size_bytes, width, height, fps, codec, is_complete, trigger_type, protected, retention_override_days "
        "FROM recordings "
        "WHERE stream_name = ? "
        "AND protected = 0 "
        "AND is_complete = 1 "
        "AND ("
        "  (trigger_type != 'detection' AND ? > 0 AND start_time < ?) "
        "  OR "
        "  (trigger_type = 'detection' AND ? > 0 AND start_time < ?)"
        ") "
        "AND ("
        "  retention_override_days IS NULL "
        "  OR start_time < (strftime('%s', 'now') - retention_override_days * 86400)"
        ") "
        "ORDER BY "
        "  CASE WHEN trigger_type = 'detection' THEN 1 ELSE 0 END ASC, "
        "  start_time ASC "
        "LIMIT ?;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    sqlite3_bind_text(stmt, 1, stream_name, -1, SQLITE_STATIC);
    sqlite3_bind_int(stmt, 2, retention_days);
    sqlite3_bind_int64(stmt, 3, (sqlite3_int64)regular_cutoff);
    sqlite3_bind_int(stmt, 4, detection_retention_days);
    sqlite3_bind_int64(stmt, 5, (sqlite3_int64)detection_cutoff);
    sqlite3_bind_int(stmt, 6, max_count);

    while (sqlite3_step(stmt) == SQLITE_ROW && count < max_count) {
        recordings[count].id = (uint64_t)sqlite3_column_int64(stmt, 0);

        const char *stream = (const char *)sqlite3_column_text(stmt, 1);
        if (stream) {
            strncpy(recordings[count].stream_name, stream, sizeof(recordings[count].stream_name) - 1);
            recordings[count].stream_name[sizeof(recordings[count].stream_name) - 1] = '\0';
        } else {
            recordings[count].stream_name[0] = '\0';
        }

        const char *path = (const char *)sqlite3_column_text(stmt, 2);
        if (path) {
            strncpy(recordings[count].file_path, path, sizeof(recordings[count].file_path) - 1);
            recordings[count].file_path[sizeof(recordings[count].file_path) - 1] = '\0';
        } else {
            recordings[count].file_path[0] = '\0';
        }

        recordings[count].start_time = (time_t)sqlite3_column_int64(stmt, 3);

        if (sqlite3_column_type(stmt, 4) != SQLITE_NULL) {
            recordings[count].end_time = (time_t)sqlite3_column_int64(stmt, 4);
        } else {
            recordings[count].end_time = 0;
        }

        recordings[count].size_bytes = (uint64_t)sqlite3_column_int64(stmt, 5);
        recordings[count].width = sqlite3_column_int(stmt, 6);
        recordings[count].height = sqlite3_column_int(stmt, 7);
        recordings[count].fps = sqlite3_column_int(stmt, 8);

        const char *codec = (const char *)sqlite3_column_text(stmt, 9);
        if (codec) {
            strncpy(recordings[count].codec, codec, sizeof(recordings[count].codec) - 1);
            recordings[count].codec[sizeof(recordings[count].codec) - 1] = '\0';
        } else {
            recordings[count].codec[0] = '\0';
        }

        recordings[count].is_complete = sqlite3_column_int(stmt, 10) != 0;

        const char *trigger_type = (const char *)sqlite3_column_text(stmt, 11);
        if (trigger_type) {
            strncpy(recordings[count].trigger_type, trigger_type, sizeof(recordings[count].trigger_type) - 1);
            recordings[count].trigger_type[sizeof(recordings[count].trigger_type) - 1] = '\0';
        } else {
            strncpy(recordings[count].trigger_type, "scheduled", sizeof(recordings[count].trigger_type) - 1);
        }

        recordings[count].protected = sqlite3_column_int(stmt, 12) != 0;

        if (sqlite3_column_type(stmt, 13) != SQLITE_NULL) {
            recordings[count].retention_override_days = sqlite3_column_int(stmt, 13);
        } else {
            recordings[count].retention_override_days = -1;
        }

        count++;
    }

    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    return count;
}

/**
 * Get recordings for quota enforcement.
 *
 * Returns lower-priority recordings first so quota cleanup preserves more
 * important clips: lower retention tier first, then no manual override,
 * then non-detection, then oldest first.
 *
 * @param stream_name Stream name
 * @param recordings Array to fill with recording metadata
 * @param max_count Maximum number of recordings to return
 * @return Number of recordings found, or -1 on error
 */
int get_recordings_for_quota_enforcement(const char *stream_name,
                                         recording_metadata_t *recordings,
                                         int max_count) {
    int rc;
    sqlite3_stmt *stmt;
    int count = 0;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    if (!stream_name || !recordings || max_count <= 0) {
        log_error("Invalid parameters for get_recordings_for_quota_enforcement");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    // Get lower-priority unprotected recordings first.
    const char *sql =
        "SELECT id, stream_name, file_path, start_time, end_time, "
        "size_bytes, width, height, fps, codec, is_complete, trigger_type, protected, retention_override_days "
        "FROM recordings "
        "WHERE stream_name = ? "
        "AND protected = 0 "
        "AND is_complete = 1 "
        "ORDER BY retention_tier DESC, "
        "CASE WHEN retention_override_days IS NULL OR retention_override_days < 0 THEN 0 ELSE 1 END ASC, "
        "CASE WHEN trigger_type = 'detection' THEN 1 ELSE 0 END ASC, "
        "start_time ASC "
        "LIMIT ?;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    sqlite3_bind_text(stmt, 1, stream_name, -1, SQLITE_STATIC);
    sqlite3_bind_int(stmt, 2, max_count);

    while (sqlite3_step(stmt) == SQLITE_ROW && count < max_count) {
        recordings[count].id = (uint64_t)sqlite3_column_int64(stmt, 0);

        const char *stream = (const char *)sqlite3_column_text(stmt, 1);
        if (stream) {
            strncpy(recordings[count].stream_name, stream, sizeof(recordings[count].stream_name) - 1);
            recordings[count].stream_name[sizeof(recordings[count].stream_name) - 1] = '\0';
        } else {
            recordings[count].stream_name[0] = '\0';
        }

        const char *path = (const char *)sqlite3_column_text(stmt, 2);
        if (path) {
            strncpy(recordings[count].file_path, path, sizeof(recordings[count].file_path) - 1);
            recordings[count].file_path[sizeof(recordings[count].file_path) - 1] = '\0';
        } else {
            recordings[count].file_path[0] = '\0';
        }

        recordings[count].start_time = (time_t)sqlite3_column_int64(stmt, 3);

        if (sqlite3_column_type(stmt, 4) != SQLITE_NULL) {
            recordings[count].end_time = (time_t)sqlite3_column_int64(stmt, 4);
        } else {
            recordings[count].end_time = 0;
        }

        recordings[count].size_bytes = (uint64_t)sqlite3_column_int64(stmt, 5);
        recordings[count].width = sqlite3_column_int(stmt, 6);
        recordings[count].height = sqlite3_column_int(stmt, 7);
        recordings[count].fps = sqlite3_column_int(stmt, 8);

        const char *codec = (const char *)sqlite3_column_text(stmt, 9);
        if (codec) {
            strncpy(recordings[count].codec, codec, sizeof(recordings[count].codec) - 1);
            recordings[count].codec[sizeof(recordings[count].codec) - 1] = '\0';
        } else {
            recordings[count].codec[0] = '\0';
        }

        recordings[count].is_complete = sqlite3_column_int(stmt, 10) != 0;

        const char *trigger_type = (const char *)sqlite3_column_text(stmt, 11);
        if (trigger_type) {
            strncpy(recordings[count].trigger_type, trigger_type, sizeof(recordings[count].trigger_type) - 1);
            recordings[count].trigger_type[sizeof(recordings[count].trigger_type) - 1] = '\0';
        } else {
            strncpy(recordings[count].trigger_type, "scheduled", sizeof(recordings[count].trigger_type) - 1);
        }

        recordings[count].protected = sqlite3_column_int(stmt, 12) != 0;

        if (sqlite3_column_type(stmt, 13) != SQLITE_NULL) {
            recordings[count].retention_override_days = sqlite3_column_int(stmt, 13);
        } else {
            recordings[count].retention_override_days = -1;
        }

        count++;
    }

    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    return count;
}

/**
 * Get orphaned recording entries (DB entries without files on disk)
 * Protected recordings are excluded (never considered orphaned).
 *
 * @param recordings Array to fill with recording metadata
 * @param max_count Maximum number of recordings to return
 * @param total_checked If non-NULL, receives the total number of recordings checked.
 *                      The caller can use this together with the return value to
 *                      compute an orphan ratio for safety thresholding.
 * @return Number of orphaned recordings found, or -1 on error
 */
int get_orphaned_db_entries(recording_metadata_t *recordings, int max_count,
                            int *total_checked) {
    int rc;
    sqlite3_stmt *stmt;
    int count = 0;
    int checked = 0;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    if (!recordings || max_count <= 0) {
        log_error("Invalid parameters for get_orphaned_db_entries");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    // Get all unprotected complete recordings and check if files exist.
    // Protected recordings are never considered orphaned — they must be
    // explicitly unprotected before any automatic cleanup can touch them.
    const char *sql =
        "SELECT id, stream_name, file_path, start_time, end_time, "
        "size_bytes, width, height, fps, codec, is_complete, trigger_type "
        "FROM recordings "
        "WHERE is_complete = 1 "
        "AND protected = 0 "
        "ORDER BY start_time ASC;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    // Keep iterating all rows even after max_count orphans are found so that
    // 'checked' reflects the true total — the caller needs this for ratio checks.
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        checked++;
        const char *path = (const char *)sqlite3_column_text(stmt, 2);

        // Check if file exists
        if (path && count < max_count && access(path, F_OK) != 0) {
            // File doesn't exist - this is an orphaned entry
            recordings[count].id = (uint64_t)sqlite3_column_int64(stmt, 0);

            const char *stream = (const char *)sqlite3_column_text(stmt, 1);
            if (stream) {
                strncpy(recordings[count].stream_name, stream, sizeof(recordings[count].stream_name) - 1);
                recordings[count].stream_name[sizeof(recordings[count].stream_name) - 1] = '\0';
            } else {
                recordings[count].stream_name[0] = '\0';
            }

            strncpy(recordings[count].file_path, path, sizeof(recordings[count].file_path) - 1);
            recordings[count].file_path[sizeof(recordings[count].file_path) - 1] = '\0';

            recordings[count].start_time = (time_t)sqlite3_column_int64(stmt, 3);

            if (sqlite3_column_type(stmt, 4) != SQLITE_NULL) {
                recordings[count].end_time = (time_t)sqlite3_column_int64(stmt, 4);
            } else {
                recordings[count].end_time = 0;
            }

            recordings[count].size_bytes = (uint64_t)sqlite3_column_int64(stmt, 5);
            recordings[count].width = sqlite3_column_int(stmt, 6);
            recordings[count].height = sqlite3_column_int(stmt, 7);
            recordings[count].fps = sqlite3_column_int(stmt, 8);

            const char *codec = (const char *)sqlite3_column_text(stmt, 9);
            if (codec) {
                strncpy(recordings[count].codec, codec, sizeof(recordings[count].codec) - 1);
                recordings[count].codec[sizeof(recordings[count].codec) - 1] = '\0';
            } else {
                recordings[count].codec[0] = '\0';
            }

            recordings[count].is_complete = sqlite3_column_int(stmt, 10) != 0;

            const char *trigger_type = (const char *)sqlite3_column_text(stmt, 11);
            if (trigger_type) {
                strncpy(recordings[count].trigger_type, trigger_type, sizeof(recordings[count].trigger_type) - 1);
                recordings[count].trigger_type[sizeof(recordings[count].trigger_type) - 1] = '\0';
            } else {
                strncpy(recordings[count].trigger_type, "scheduled", sizeof(recordings[count].trigger_type) - 1);
            }

            count++;
        }
    }

    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    if (total_checked) {
        *total_checked = checked;
    }

    log_info("Checked %d recordings, found %d orphaned DB entries", checked, count);
    return count;
}

/**
 * Get recordings eligible for deletion based on tiered retention policy.
 * Returns recordings ordered by tier (ephemeral first) then by age (oldest first).
 */
int get_recordings_for_tiered_retention(const char *stream_name,
                                        int base_retention_days,
                                        const double *tier_multipliers,
                                        recording_metadata_t *recordings,
                                        int max_count) {
    int rc;
    sqlite3_stmt *stmt;
    int count = 0;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    if (!tier_multipliers || !recordings || max_count <= 0) {
        log_error("Invalid parameters for get_recordings_for_tiered_retention");
        return -1;
    }

    time_t now = time(NULL);

    // Calculate cutoff times for each tier
    time_t cutoff_critical = now - (time_t)(base_retention_days * tier_multipliers[RETENTION_TIER_CRITICAL] * 86400);
    time_t cutoff_important = now - (time_t)(base_retention_days * tier_multipliers[RETENTION_TIER_IMPORTANT] * 86400);
    time_t cutoff_standard = now - (time_t)(base_retention_days * tier_multipliers[RETENTION_TIER_STANDARD] * 86400);
    time_t cutoff_ephemeral = now - (time_t)(base_retention_days * tier_multipliers[RETENTION_TIER_EPHEMERAL] * 86400);

    pthread_mutex_lock(db_mutex);

    // Select recordings past their tier-specific retention cutoff
    // Order by tier descending (ephemeral=3 first) then oldest first
    const char *sql;
    if (stream_name) {
        sql = "SELECT id, stream_name, file_path, start_time, end_time, "
              "size_bytes, width, height, fps, codec, is_complete, trigger_type, "
              "protected, retention_override_days, retention_tier, disk_pressure_eligible "
              "FROM recordings "
              "WHERE stream_name = ? "
              "AND protected = 0 "
              "AND is_complete = 1 "
              "AND ("
              "  (retention_tier = 0 AND start_time < ?) OR "
              "  (retention_tier = 1 AND start_time < ?) OR "
              "  (retention_tier = 2 AND start_time < ?) OR "
              "  (retention_tier = 3 AND start_time < ?)"
              ") "
              "AND (retention_override_days IS NULL "
              "  OR start_time < (strftime('%s', 'now') - retention_override_days * 86400)) "
              "ORDER BY retention_tier DESC, start_time ASC "
              "LIMIT ?;";
    } else {
        sql = "SELECT id, stream_name, file_path, start_time, end_time, "
              "size_bytes, width, height, fps, codec, is_complete, trigger_type, "
              "protected, retention_override_days, retention_tier, disk_pressure_eligible "
              "FROM recordings "
              "WHERE protected = 0 "
              "AND is_complete = 1 "
              "AND ("
              "  (retention_tier = 0 AND start_time < ?) OR "
              "  (retention_tier = 1 AND start_time < ?) OR "
              "  (retention_tier = 2 AND start_time < ?) OR "
              "  (retention_tier = 3 AND start_time < ?)"
              ") "
              "AND (retention_override_days IS NULL "
              "  OR start_time < (strftime('%s', 'now') - retention_override_days * 86400)) "
              "ORDER BY retention_tier DESC, start_time ASC "
              "LIMIT ?;";
    }

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare tiered retention query: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    int param = 1;
    if (stream_name) {
        sqlite3_bind_text(stmt, param++, stream_name, -1, SQLITE_STATIC);
    }
    sqlite3_bind_int64(stmt, param++, (sqlite3_int64)cutoff_critical);
    sqlite3_bind_int64(stmt, param++, (sqlite3_int64)cutoff_important);
    sqlite3_bind_int64(stmt, param++, (sqlite3_int64)cutoff_standard);
    sqlite3_bind_int64(stmt, param++, (sqlite3_int64)cutoff_ephemeral);
    sqlite3_bind_int(stmt, param++, max_count);

    while (sqlite3_step(stmt) == SQLITE_ROW && count < max_count) {
        recordings[count].id = (uint64_t)sqlite3_column_int64(stmt, 0);

        const char *sname = (const char *)sqlite3_column_text(stmt, 1);
        if (sname) {
            strncpy(recordings[count].stream_name, sname, sizeof(recordings[count].stream_name) - 1);
            recordings[count].stream_name[sizeof(recordings[count].stream_name) - 1] = '\0';
        }

        const char *fpath = (const char *)sqlite3_column_text(stmt, 2);
        if (fpath) {
            strncpy(recordings[count].file_path, fpath, sizeof(recordings[count].file_path) - 1);
            recordings[count].file_path[sizeof(recordings[count].file_path) - 1] = '\0';
        }

        recordings[count].start_time = (time_t)sqlite3_column_int64(stmt, 3);
        recordings[count].end_time = (sqlite3_column_type(stmt, 4) != SQLITE_NULL)
            ? (time_t)sqlite3_column_int64(stmt, 4) : 0;
        recordings[count].size_bytes = (uint64_t)sqlite3_column_int64(stmt, 5);
        recordings[count].width = sqlite3_column_int(stmt, 6);
        recordings[count].height = sqlite3_column_int(stmt, 7);
        recordings[count].fps = sqlite3_column_int(stmt, 8);

        const char *codec = (const char *)sqlite3_column_text(stmt, 9);
        if (codec) {
            strncpy(recordings[count].codec, codec, sizeof(recordings[count].codec) - 1);
            recordings[count].codec[sizeof(recordings[count].codec) - 1] = '\0';
        }

        recordings[count].is_complete = sqlite3_column_int(stmt, 10) != 0;

        const char *ttype = (const char *)sqlite3_column_text(stmt, 11);
        if (ttype) {
            strncpy(recordings[count].trigger_type, ttype, sizeof(recordings[count].trigger_type) - 1);
            recordings[count].trigger_type[sizeof(recordings[count].trigger_type) - 1] = '\0';
        } else {
            strncpy(recordings[count].trigger_type, "scheduled", sizeof(recordings[count].trigger_type) - 1);
        }

        recordings[count].protected = sqlite3_column_int(stmt, 12) != 0;
        recordings[count].retention_override_days = (sqlite3_column_type(stmt, 13) != SQLITE_NULL)
            ? sqlite3_column_int(stmt, 13) : -1;
        recordings[count].retention_tier = (sqlite3_column_type(stmt, 14) != SQLITE_NULL)
            ? sqlite3_column_int(stmt, 14) : RETENTION_TIER_STANDARD;
        recordings[count].disk_pressure_eligible = (sqlite3_column_type(stmt, 15) != SQLITE_NULL)
            ? (sqlite3_column_int(stmt, 15) != 0) : true;

        count++;
    }

    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    log_info("Found %d recordings eligible for tiered retention cleanup", count);
    return count;
}


/**
 * Get recordings eligible for disk pressure cleanup.
 * Returns disk_pressure_eligible recordings ordered by deletion priority:
 * lower retention tier first, then no manual override, then non-detection,
 * then oldest first; protected recordings are excluded.
 */
int get_recordings_for_pressure_cleanup(recording_metadata_t *recordings,
                                        int max_count) {
    int rc;
    sqlite3_stmt *stmt;
    int count = 0;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    if (!recordings || max_count <= 0) {
        log_error("Invalid parameters for get_recordings_for_pressure_cleanup");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    const char *sql =
        "SELECT id, stream_name, file_path, start_time, end_time, "
        "size_bytes, width, height, fps, codec, is_complete, trigger_type, "
        "protected, retention_override_days, retention_tier, disk_pressure_eligible "
        "FROM recordings "
        "WHERE protected = 0 "
        "AND disk_pressure_eligible = 1 "
        "AND is_complete = 1 "
        "ORDER BY retention_tier DESC, "
        "CASE WHEN retention_override_days IS NULL OR retention_override_days < 0 THEN 0 ELSE 1 END ASC, "
        "CASE WHEN trigger_type = 'detection' THEN 1 ELSE 0 END ASC, "
        "start_time ASC "
        "LIMIT ?;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare pressure cleanup query: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    sqlite3_bind_int(stmt, 1, max_count);

    while (sqlite3_step(stmt) == SQLITE_ROW && count < max_count) {
        recordings[count].id = (uint64_t)sqlite3_column_int64(stmt, 0);

        const char *sname = (const char *)sqlite3_column_text(stmt, 1);
        if (sname) {
            strncpy(recordings[count].stream_name, sname, sizeof(recordings[count].stream_name) - 1);
            recordings[count].stream_name[sizeof(recordings[count].stream_name) - 1] = '\0';
        }

        const char *fpath = (const char *)sqlite3_column_text(stmt, 2);
        if (fpath) {
            strncpy(recordings[count].file_path, fpath, sizeof(recordings[count].file_path) - 1);
            recordings[count].file_path[sizeof(recordings[count].file_path) - 1] = '\0';
        }

        recordings[count].start_time = (time_t)sqlite3_column_int64(stmt, 3);
        recordings[count].end_time = (sqlite3_column_type(stmt, 4) != SQLITE_NULL)
            ? (time_t)sqlite3_column_int64(stmt, 4) : 0;
        recordings[count].size_bytes = (uint64_t)sqlite3_column_int64(stmt, 5);
        recordings[count].width = sqlite3_column_int(stmt, 6);
        recordings[count].height = sqlite3_column_int(stmt, 7);
        recordings[count].fps = sqlite3_column_int(stmt, 8);

        const char *codec = (const char *)sqlite3_column_text(stmt, 9);
        if (codec) {
            strncpy(recordings[count].codec, codec, sizeof(recordings[count].codec) - 1);
            recordings[count].codec[sizeof(recordings[count].codec) - 1] = '\0';
        }

        recordings[count].is_complete = sqlite3_column_int(stmt, 10) != 0;

        const char *ttype = (const char *)sqlite3_column_text(stmt, 11);
        if (ttype) {
            strncpy(recordings[count].trigger_type, ttype, sizeof(recordings[count].trigger_type) - 1);
            recordings[count].trigger_type[sizeof(recordings[count].trigger_type) - 1] = '\0';
        } else {
            strncpy(recordings[count].trigger_type, "scheduled", sizeof(recordings[count].trigger_type) - 1);
        }

        recordings[count].protected = sqlite3_column_int(stmt, 12) != 0;
        recordings[count].retention_override_days = (sqlite3_column_type(stmt, 13) != SQLITE_NULL)
            ? sqlite3_column_int(stmt, 13) : -1;
        recordings[count].retention_tier = (sqlite3_column_type(stmt, 14) != SQLITE_NULL)
            ? sqlite3_column_int(stmt, 14) : RETENTION_TIER_STANDARD;
        recordings[count].disk_pressure_eligible = (sqlite3_column_type(stmt, 15) != SQLITE_NULL)
            ? (sqlite3_column_int(stmt, 15) != 0) : true;

        count++;
    }

    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    log_info("Found %d recordings eligible for disk pressure cleanup", count);
    return count;
}

/**
 * Get total storage bytes used by a stream from the database.
 */
int64_t get_stream_storage_bytes(const char *stream_name) {
    int rc;
    sqlite3_stmt *stmt;
    int64_t total_bytes = -1;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    const char *sql;
    if (stream_name) {
        sql = "SELECT COALESCE(SUM(size_bytes), 0) FROM recordings WHERE stream_name = ? AND is_complete = 1;";
    } else {
        sql = "SELECT COALESCE(SUM(size_bytes), 0) FROM recordings WHERE is_complete = 1;";
    }

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare storage bytes query: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    if (stream_name) {
        sqlite3_bind_text(stmt, 1, stream_name, -1, SQLITE_STATIC);
    }

    if (sqlite3_step(stmt) == SQLITE_ROW) {
        total_bytes = sqlite3_column_int64(stmt, 0);
    }

    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    return total_bytes;
}

/**
 * Set retention tier for a recording.
 */
int set_recording_retention_tier(uint64_t id, int tier) {
    int rc;
    sqlite3_stmt *stmt;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    if (tier < RETENTION_TIER_CRITICAL || tier > RETENTION_TIER_EPHEMERAL) {
        log_error("Invalid retention tier: %d", tier);
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    const char *sql = "UPDATE recordings SET retention_tier = ? WHERE id = ?;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    sqlite3_bind_int(stmt, 1, tier);
    sqlite3_bind_int64(stmt, 2, (sqlite3_int64)id);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    if (rc != SQLITE_DONE) {
        log_error("Failed to set retention tier for recording %llu: %s",
                  (unsigned long long)id, sqlite3_errmsg(db));
        return -1;
    }

    log_debug("Recording %llu retention tier set to %d", (unsigned long long)id, tier);
    return 0;
}

/**
 * Set disk pressure eligibility for a recording.
 */
int set_recording_disk_pressure_eligible(uint64_t id, bool eligible) {
    int rc;
    sqlite3_stmt *stmt;

    sqlite3 *db = get_db_handle();
    pthread_mutex_t *db_mutex = get_db_mutex();

    if (!db) {
        log_error("Database not initialized");
        return -1;
    }

    pthread_mutex_lock(db_mutex);

    const char *sql = "UPDATE recordings SET disk_pressure_eligible = ? WHERE id = ?;";

    rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
    if (rc != SQLITE_OK) {
        log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
        pthread_mutex_unlock(db_mutex);
        return -1;
    }

    sqlite3_bind_int(stmt, 1, eligible ? 1 : 0);
    sqlite3_bind_int64(stmt, 2, (sqlite3_int64)id);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    pthread_mutex_unlock(db_mutex);

    if (rc != SQLITE_DONE) {
        log_error("Failed to set disk pressure eligibility for recording %llu: %s",
                  (unsigned long long)id, sqlite3_errmsg(db));
        return -1;
    }

    log_debug("Recording %llu disk pressure eligible set to %s",
              (unsigned long long)id, eligible ? "true" : "false");
    return 0;
}