/**
 * @file src/session_history_storage.cpp
 * @brief Internal SQLite storage and schema helpers for session history.
 */

// standard includes
#include <algorithm>
#include <chrono>
#include <cstdint>
#include <filesystem>
#include <optional>
#include <string>
#include <vector>

// local includes
#include "session_history_storage.h"
#include "session_history_storage_diagnostics.h"
#include "utility.h"

#ifdef _WIN32
  #include <winsock2.h>
  #include <AclAPI.h>
#endif

namespace session_history::storage {
  namespace {

    constexpr const char *SCHEMA_SQL = R"(
      PRAGMA journal_mode = WAL;
      PRAGMA synchronous = NORMAL;

      CREATE TABLE IF NOT EXISTS sessions (
        uuid TEXT PRIMARY KEY,
        protocol TEXT NOT NULL,
        client_name TEXT,
        device_name TEXT,
        app_name TEXT,
        width INTEGER,
        height INTEGER,
        target_fps INTEGER,
        encoder_bitrate_kbps INTEGER,
        requested_bitrate_kbps INTEGER,
        codec TEXT,
        hdr INTEGER DEFAULT 0,
        yuv444 INTEGER DEFAULT 0,
        audio_channels INTEGER,
        start_time_unix REAL NOT NULL,
        end_time_unix REAL,
        duration_seconds REAL,
        verdict TEXT DEFAULT 'unknown',
        server_version TEXT,
        host_cpu_model TEXT,
        host_gpu_model TEXT,
        stream_gpu_model TEXT
      );

      CREATE TABLE IF NOT EXISTS samples (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        session_uuid TEXT NOT NULL REFERENCES sessions(uuid) ON DELETE CASCADE,
        timestamp_unix REAL NOT NULL,
        bytes_sent_total INTEGER DEFAULT 0,
        packets_sent_video INTEGER DEFAULT 0,
        frames_sent INTEGER DEFAULT 0,
        last_frame_index INTEGER DEFAULT 0,
        video_dropped INTEGER DEFAULT 0,
        audio_dropped INTEGER DEFAULT 0,
        client_reported_losses INTEGER DEFAULT 0,
        idr_requests INTEGER DEFAULT 0,
        ref_invalidations INTEGER DEFAULT 0,
        encode_latency_ms REAL DEFAULT 0,
        actual_fps REAL DEFAULT 0,
        actual_bitrate_kbps REAL DEFAULT 0,
        frame_interval_jitter_ms REAL DEFAULT 0,
        host_cpu_percent REAL DEFAULT -1,
        host_gpu_percent REAL DEFAULT -1,
        host_gpu_encoder_percent REAL DEFAULT -1,
        host_ram_percent REAL DEFAULT -1,
        host_vram_percent REAL DEFAULT -1,
        host_cpu_temp_c REAL DEFAULT -1,
        host_gpu_temp_c REAL DEFAULT -1,
        host_net_rx_bps REAL DEFAULT -1,
        host_net_tx_bps REAL DEFAULT -1
      );

      CREATE INDEX IF NOT EXISTS idx_samples_session ON samples(session_uuid);
      CREATE INDEX IF NOT EXISTS idx_samples_time ON samples(session_uuid, timestamp_unix);

      CREATE TABLE IF NOT EXISTS events (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        session_uuid TEXT NOT NULL REFERENCES sessions(uuid) ON DELETE CASCADE,
        timestamp_unix REAL NOT NULL,
        event_type TEXT NOT NULL,
        payload TEXT
      );

      CREATE INDEX IF NOT EXISTS idx_events_session ON events(session_uuid);
      CREATE INDEX IF NOT EXISTS idx_events_time ON events(session_uuid, timestamp_unix);
      CREATE INDEX IF NOT EXISTS idx_sessions_end_time
      ON sessions(end_time_unix DESC)
      WHERE end_time_unix IS NOT NULL;
    )";

    bool session_accepts_live_updates(sqlite3 *db, const std::string &uuid) {
      auto stmt = prepare(db, "SELECT end_time_unix FROM sessions WHERE uuid = ?");
      if (!stmt) return false;

      sqlite3_bind_text(stmt.get(), 1, uuid.c_str(), -1, SQLITE_TRANSIENT);
      if (sqlite3_step(stmt.get()) != SQLITE_ROW) {
        return false;
      }

      return sqlite3_column_type(stmt.get(), 0) == SQLITE_NULL;
    }

    session_summary_t row_to_summary(sqlite3_stmt *stmt) {
      session_summary_t out;
      out.uuid = reinterpret_cast<const char *>(sqlite3_column_text(stmt, 0));
      out.protocol = reinterpret_cast<const char *>(sqlite3_column_text(stmt, 1));
      auto col2 = sqlite3_column_text(stmt, 2);
      out.client_name = col2 ? reinterpret_cast<const char *>(col2) : "";
      auto col3 = sqlite3_column_text(stmt, 3);
      out.device_name = col3 ? reinterpret_cast<const char *>(col3) : "";
      auto col4 = sqlite3_column_text(stmt, 4);
      out.app_name = col4 ? reinterpret_cast<const char *>(col4) : "";
      out.width = sqlite3_column_int(stmt, 5);
      out.height = sqlite3_column_int(stmt, 6);
      out.target_fps = sqlite3_column_int(stmt, 7);
      out.encoder_bitrate_kbps = sqlite3_column_int(stmt, 8);
      auto col9 = sqlite3_column_text(stmt, 9);
      out.codec = col9 ? reinterpret_cast<const char *>(col9) : "";
      out.hdr = sqlite3_column_int(stmt, 10) != 0;
      out.yuv444 = sqlite3_column_int(stmt, 11) != 0;
      out.audio_channels = sqlite3_column_int(stmt, 12);
      out.start_time_unix = sqlite3_column_double(stmt, 13);
      out.end_time_unix = sqlite3_column_double(stmt, 14);
      out.duration_seconds = sqlite3_column_double(stmt, 15);
      auto col16 = sqlite3_column_text(stmt, 16);
      out.verdict = col16 ? reinterpret_cast<const char *>(col16) : "unknown";
      if (sqlite3_column_count(stmt) > 17 && sqlite3_column_type(stmt, 17) != SQLITE_NULL) {
        out.requested_bitrate_kbps = sqlite3_column_int(stmt, 17);
      } else {
        out.requested_bitrate_kbps = out.encoder_bitrate_kbps;
      }
      if (sqlite3_column_count(stmt) > 18 && sqlite3_column_type(stmt, 18) != SQLITE_NULL) {
        auto col18 = sqlite3_column_text(stmt, 18);
        out.server_version = col18 ? reinterpret_cast<const char *>(col18) : "";
      }
      if (sqlite3_column_count(stmt) > 19 && sqlite3_column_type(stmt, 19) != SQLITE_NULL) {
        auto col19 = sqlite3_column_text(stmt, 19);
        out.host_cpu_model = col19 ? reinterpret_cast<const char *>(col19) : "";
      }
      if (sqlite3_column_count(stmt) > 20 && sqlite3_column_type(stmt, 20) != SQLITE_NULL) {
        auto col20 = sqlite3_column_text(stmt, 20);
        out.host_gpu_model = col20 ? reinterpret_cast<const char *>(col20) : "";
      }
      if (sqlite3_column_count(stmt) > 21 && sqlite3_column_type(stmt, 21) != SQLITE_NULL) {
        auto col21 = sqlite3_column_text(stmt, 21);
        out.stream_gpu_model = col21 ? reinterpret_cast<const char *>(col21) : "";
      }
      return out;
    }

    std::size_t read_count(sqlite3 *db, const char *sql, const std::string &uuid) {
      auto stmt = prepare(db, sql);
      if (!stmt) {
        return 0;
      }
      sqlite3_bind_text(stmt.get(), 1, uuid.c_str(), -1, SQLITE_TRANSIENT);
      if (sqlite3_step(stmt.get()) != SQLITE_ROW) {
        return 0;
      }
      return static_cast<std::size_t>(sqlite3_column_int64(stmt.get(), 0));
    }

    int read_pragma_int(sqlite3 *db, const char *sql) {
      auto stmt = prepare(db, sql);
      if (!stmt) {
        return 0;
      }
      if (sqlite3_step(stmt.get()) != SQLITE_ROW) {
        return 0;
      }
      return sqlite3_column_int(stmt.get(), 0);
    }

    std::int64_t read_pragma_int64(sqlite3 *db, const char *sql) {
      auto stmt = prepare(db, sql);
      if (!stmt) {
        return 0;
      }
      if (sqlite3_step(stmt.get()) != SQLITE_ROW) {
        return 0;
      }
      return sqlite3_column_int64(stmt.get(), 0);
    }

    bool table_exists(sqlite3 *db, const char *table_name) {
      auto stmt = prepare(
        db,
        "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ? LIMIT 1"
      );
      if (!stmt) {
        return false;
      }

      sqlite3_bind_text(stmt.get(), 1, table_name, -1, SQLITE_TRANSIENT);
      return sqlite3_step(stmt.get()) == SQLITE_ROW;
    }

    bool table_has_delete_cascade(sqlite3 *db, const char *table_name, const char *parent_table, const char *from_column) {
      sqlite3_stmt *check = nullptr;
      const std::string sql = std::string("PRAGMA foreign_key_list(") + table_name + ")";
      bool found = false;
      if (sqlite3_prepare_v2(db, sql.c_str(), -1, &check, nullptr) != SQLITE_OK) {
        return false;
      }

      while (sqlite3_step(check) == SQLITE_ROW) {
        const auto *table = sqlite3_column_text(check, 2);
        const auto *from = sqlite3_column_text(check, 3);
        const auto *on_delete = sqlite3_column_text(check, 6);
        if (table && from && on_delete &&
            std::string(reinterpret_cast<const char *>(table)) == parent_table &&
            std::string(reinterpret_cast<const char *>(from)) == from_column &&
            std::string(reinterpret_cast<const char *>(on_delete)) == "CASCADE") {
          found = true;
          break;
        }
      }

      sqlite3_finalize(check);
      return found;
    }

    bool rebuild_child_table_with_cascade(sqlite3 *db, const char *table_name, const char *temp_name, const char *create_sql, const char *column_list, const char *index_sql) {
      if (!exec(db, "PRAGMA foreign_keys = OFF")) {
        return false;
      }

      const auto foreign_keys_guard = util::fail_guard([db]() {
        (void) exec(db, "PRAGMA foreign_keys = ON");
      });

      if (!exec(db, (std::string("ALTER TABLE ") + table_name + " RENAME TO " + temp_name).c_str())) {
        return false;
      }
      if (!exec(db, create_sql)) {
        return false;
      }
      if (!exec(db, (std::string("INSERT INTO ") + table_name + " (" + column_list + ") SELECT " + column_list + " FROM " + temp_name).c_str())) {
        return false;
      }
      if (!exec(db, (std::string("DROP TABLE ") + temp_name).c_str())) {
        return false;
      }
      if (!exec(db, index_sql)) {
        return false;
      }
      return exec(db, "PRAGMA foreign_keys = ON");
    }

    bool delete_session_rows(sqlite3 *db, const std::string &uuid, bool track_changes, int &affected) {
      constexpr const char *DELETE_SESSION = "DELETE FROM sessions WHERE uuid = ?";

      affected = 0;
      auto stmt = prepare(db, DELETE_SESSION);
      if (!stmt) {
        return false;
      }

      sqlite3_bind_text(stmt.get(), 1, uuid.c_str(), -1, SQLITE_TRANSIENT);
      if (sqlite3_step(stmt.get()) != SQLITE_DONE) {
        diagnostics::error() << "session_history: delete failed for uuid=" << uuid << ": " << sqlite3_errmsg(db);
        return false;
      }
      if (track_changes) {
        affected = sqlite3_changes(db);
      }

      return true;
    }

    bool prune_sessions_by_limit(sqlite3 *db, int max_history_sessions) {
      const auto limit_str = std::to_string(max_history_sessions);
      const std::string delete_sessions =
        "DELETE FROM sessions WHERE end_time_unix IS NOT NULL "
        "AND uuid NOT IN ("
        "  SELECT uuid FROM sessions WHERE end_time_unix IS NOT NULL "
        "  ORDER BY end_time_unix DESC LIMIT " + limit_str +
        ")";
      return exec(db, delete_sessions.c_str());
    }

    bool prune_sessions_older_than(sqlite3 *db, double cutoff_unix) {
      auto delete_sessions = prepare(
        db,
        "DELETE FROM sessions WHERE end_time_unix IS NOT NULL AND end_time_unix < ?"
      );
      if (!delete_sessions) return false;
      sqlite3_bind_double(delete_sessions.get(), 1, cutoff_unix);
      if (sqlite3_step(delete_sessions.get()) != SQLITE_DONE) {
        diagnostics::error() << "session_history: TTL session prune failed: " << sqlite3_errmsg(db);
        return false;
      }

      return true;
    }

    std::optional<std::string> oldest_ended_session_uuid(sqlite3 *db) {
      auto stmt = prepare(
        db,
        "SELECT uuid FROM sessions WHERE end_time_unix IS NOT NULL ORDER BY end_time_unix ASC LIMIT 1"
      );
      if (!stmt) {
        return std::nullopt;
      }
      if (sqlite3_step(stmt.get()) != SQLITE_ROW) {
        return std::string {};
      }
      auto text = sqlite3_column_text(stmt.get(), 0);
      if (!text) {
        return std::string {};
      }
      return std::string(reinterpret_cast<const char *>(text));
    }

    std::uint64_t estimate_live_db_bytes(sqlite3 *db) {
      const auto page_size = static_cast<std::uint64_t>(std::max<std::int64_t>(read_pragma_int64(db, "PRAGMA page_size"), 0));
      const auto page_count = static_cast<std::uint64_t>(std::max<std::int64_t>(read_pragma_int64(db, "PRAGMA page_count"), 0));
      const auto freelist_count = static_cast<std::uint64_t>(std::max<std::int64_t>(read_pragma_int64(db, "PRAGMA freelist_count"), 0));
      const auto live_pages = page_count > freelist_count ? page_count - freelist_count : 0;
      return live_pages * page_size;
    }

  }  // namespace

  void sqlite_deleter::operator()(sqlite3 *db) const {
    if (db) sqlite3_close(db);
  }

  void stmt_deleter::operator()(sqlite3_stmt *stmt) const {
    if (stmt) sqlite3_finalize(stmt);
  }

  stmt_ptr prepare(sqlite3 *db, const char *sql) {
    sqlite3_stmt *raw = nullptr;
    int rc = sqlite3_prepare_v2(db, sql, -1, &raw, nullptr);
    if (rc != SQLITE_OK) {
      diagnostics::error() << "session_history: prepare failed (" << rc << "): " << sqlite3_errmsg(db)
                           << " | SQL: " << sql;
      return nullptr;
    }
    return stmt_ptr {raw};
  }

  bool exec(sqlite3 *db, const char *sql) {
    char *err = nullptr;
    int rc = sqlite3_exec(db, sql, nullptr, nullptr, &err);
    if (rc != SQLITE_OK) {
      diagnostics::error() << "session_history: exec failed (" << rc << "): " << (err ? err : "unknown");
      sqlite3_free(err);
      return false;
    }
    return true;
  }

  bool checkpoint(sqlite3 *db) {
    return exec(db, "PRAGMA wal_checkpoint(TRUNCATE)");
  }

#ifdef SUNSHINE_TESTS
  bool force_set_end_time(sqlite3 *db, const std::string &uuid, double end_time_unix) {
    auto stmt = prepare(
      db,
      "UPDATE sessions SET end_time_unix = ?, duration_seconds = ? - start_time_unix WHERE uuid = ?"
    );
    if (!stmt) return false;

    sqlite3_bind_double(stmt.get(), 1, end_time_unix);
    sqlite3_bind_double(stmt.get(), 2, end_time_unix);
    sqlite3_bind_text(stmt.get(), 3, uuid.c_str(), -1, SQLITE_TRANSIENT);
    if (sqlite3_step(stmt.get()) != SQLITE_DONE) {
      diagnostics::error() << "session_history: force_set_end_time failed for uuid=" << uuid
                           << ": " << sqlite3_errmsg(db);
      return false;
    }
    return sqlite3_changes(db) > 0;
  }
#endif

  double now_unix() {
    auto tp = std::chrono::system_clock::now();
    return std::chrono::duration<double>(tp.time_since_epoch()).count();
  }

  void tighten_history_db_permissions(const std::filesystem::path &db_path) {
#ifdef _WIN32
    auto apply_windows_permissions = [&](const std::filesystem::path &path, bool directory) {
      std::error_code ec;
      if (!std::filesystem::exists(path, ec) || ec) {
        return;
      }

      SID_IDENTIFIER_AUTHORITY nt_authority = SECURITY_NT_AUTHORITY;
      PSID admin_sid = nullptr;
      PSID system_sid = nullptr;
      if (!AllocateAndInitializeSid(&nt_authority, 2, SECURITY_BUILTIN_DOMAIN_RID, DOMAIN_ALIAS_RID_ADMINS,
                                    0, 0, 0, 0, 0, 0, &admin_sid)) {
        diagnostics::warning() << "session_history: failed to allocate Administrators SID for " << path.string();
        return;
      }
      auto free_admin_sid = util::fail_guard([admin_sid]() {
        FreeSid(admin_sid);
      });

      if (!AllocateAndInitializeSid(&nt_authority, 1, SECURITY_LOCAL_SYSTEM_RID,
                                    0, 0, 0, 0, 0, 0, 0, &system_sid)) {
        diagnostics::warning() << "session_history: failed to allocate SYSTEM SID for " << path.string();
        return;
      }
      auto free_system_sid = util::fail_guard([system_sid]() {
        FreeSid(system_sid);
      });

      HANDLE token = nullptr;
      std::vector<unsigned char> token_user_buffer;
      PSID current_user_sid = nullptr;
      if (OpenProcessToken(GetCurrentProcess(), TOKEN_QUERY, &token)) {
        auto close_token = util::fail_guard([token]() {
          CloseHandle(token);
        });

        DWORD token_user_size = 0;
        (void) GetTokenInformation(token, TokenUser, nullptr, 0, &token_user_size);
        if (token_user_size > 0) {
          token_user_buffer.resize(token_user_size);
          if (GetTokenInformation(token, TokenUser, token_user_buffer.data(), token_user_size, &token_user_size)) {
            auto *token_user = reinterpret_cast<TOKEN_USER *>(token_user_buffer.data());
            if (token_user && IsValidSid(token_user->User.Sid)) {
              current_user_sid = token_user->User.Sid;
            }
          }
        }
      }

      std::vector<EXPLICIT_ACCESSW> access(2);
      access[0].grfAccessPermissions = GENERIC_ALL;
      access[0].grfAccessMode = SET_ACCESS;
      access[0].grfInheritance = directory ? SUB_CONTAINERS_AND_OBJECTS_INHERIT : NO_INHERITANCE;
      access[0].Trustee.TrusteeForm = TRUSTEE_IS_SID;
      access[0].Trustee.TrusteeType = TRUSTEE_IS_GROUP;
      access[0].Trustee.ptstrName = static_cast<LPWSTR>(admin_sid);

      access[1].grfAccessPermissions = GENERIC_ALL;
      access[1].grfAccessMode = SET_ACCESS;
      access[1].grfInheritance = directory ? SUB_CONTAINERS_AND_OBJECTS_INHERIT : NO_INHERITANCE;
      access[1].Trustee.TrusteeForm = TRUSTEE_IS_SID;
      access[1].Trustee.TrusteeType = TRUSTEE_IS_USER;
      access[1].Trustee.ptstrName = static_cast<LPWSTR>(system_sid);

      if (current_user_sid &&
          !EqualSid(current_user_sid, admin_sid) &&
          !EqualSid(current_user_sid, system_sid)) {
        EXPLICIT_ACCESSW current_user_access {};
        current_user_access.grfAccessPermissions = GENERIC_ALL;
        current_user_access.grfAccessMode = SET_ACCESS;
        current_user_access.grfInheritance = directory ? SUB_CONTAINERS_AND_OBJECTS_INHERIT : NO_INHERITANCE;
        current_user_access.Trustee.TrusteeForm = TRUSTEE_IS_SID;
        current_user_access.Trustee.TrusteeType = TRUSTEE_IS_USER;
        current_user_access.Trustee.ptstrName = static_cast<LPWSTR>(current_user_sid);
        access.push_back(current_user_access);
      }

      PACL raw_acl = nullptr;
      DWORD acl_status = SetEntriesInAclW(static_cast<ULONG>(access.size()), access.data(), nullptr, &raw_acl);
      if (acl_status != ERROR_SUCCESS) {
        diagnostics::warning() << "session_history: SetEntriesInAclW failed for " << path.string()
                               << " (error=" << acl_status << ")";
        return;
      }
      auto free_acl = util::fail_guard([raw_acl]() {
        LocalFree(raw_acl);
      });

      DWORD sec_status = SetNamedSecurityInfoW(
        const_cast<LPWSTR>(path.c_str()),
        SE_FILE_OBJECT,
        DACL_SECURITY_INFORMATION | PROTECTED_DACL_SECURITY_INFORMATION,
        nullptr,
        nullptr,
        raw_acl,
        nullptr);
      if (sec_status != ERROR_SUCCESS) {
        diagnostics::warning() << "session_history: SetNamedSecurityInfoW failed for " << path.string()
                               << " (error=" << sec_status << ")";
      }
    };

    const auto parent_path = db_path.parent_path();
    if (!parent_path.empty() && parent_path.filename() == "session_history") {
      apply_windows_permissions(parent_path, true);
    }
    apply_windows_permissions(db_path, false);
    apply_windows_permissions(db_path.string() + "-wal", false);
    apply_windows_permissions(db_path.string() + "-shm", false);
#else
    const auto apply_posix_permissions = [&](const std::filesystem::path &path) {
      std::error_code ec;
      if (!std::filesystem::exists(path, ec) || ec) {
        return;
      }
      std::filesystem::permissions(
        path,
        std::filesystem::perms::owner_read | std::filesystem::perms::owner_write,
        std::filesystem::perm_options::replace,
        ec);
      if (ec) {
        diagnostics::warning() << "session_history: failed to tighten permissions for " << path.string()
                               << ": " << ec.message();
      }
    };

    const auto parent_path = db_path.parent_path();
    if (!parent_path.empty() && parent_path.filename() == "session_history") {
      std::error_code ec;
      if (std::filesystem::exists(parent_path, ec) && !ec) {
        std::filesystem::permissions(
          parent_path,
          std::filesystem::perms::owner_all,
          std::filesystem::perm_options::replace,
          ec);
        if (ec) {
          diagnostics::warning() << "session_history: failed to tighten permissions for " << parent_path.string()
                                 << ": " << ec.message();
        }
      }
    }
    apply_posix_permissions(db_path);
    apply_posix_permissions(db_path.string() + "-wal");
    apply_posix_permissions(db_path.string() + "-shm");
#endif
  }

  bool open_write_db(const std::string &db_path, db_ptr &out_db) {
    const std::filesystem::path path {db_path};
    if (!path.parent_path().empty()) {
      std::error_code ec;
      std::filesystem::create_directories(path.parent_path(), ec);
      if (ec) {
        diagnostics::error() << "session_history: failed to create DB directory " << path.parent_path().string()
                             << ": " << ec.message();
        return false;
      }
    }

    tighten_history_db_permissions(path);

    sqlite3 *raw = nullptr;
    int rc = sqlite3_open(db_path.c_str(), &raw);
    if (rc != SQLITE_OK) {
      diagnostics::error() << "session_history: failed to open write DB: " << sqlite3_errmsg(raw);
      sqlite3_close(raw);
      return false;
    }
    out_db.reset(raw);
    exec(out_db.get(), "PRAGMA foreign_keys = ON");
    return true;
  }

  bool open_read_db(const std::string &db_path, db_ptr &out_db) {
    sqlite3 *raw = nullptr;
    int rc = sqlite3_open_v2(db_path.c_str(), &raw, SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX, nullptr);
    if (rc != SQLITE_OK) {
      diagnostics::error() << "session_history: failed to open read DB: " << sqlite3_errmsg(raw);
      sqlite3_close(raw);
      return false;
    }
    out_db.reset(raw);
    exec(out_db.get(), "PRAGMA foreign_keys = ON");
    return true;
  }

  bool open_memory_db(db_ptr &out_db) {
    sqlite3 *raw = nullptr;
    if (sqlite3_open(":memory:", &raw) != SQLITE_OK) {
      sqlite3_close(raw);
      return false;
    }
    out_db.reset(raw);
    return exec(out_db.get(), "PRAGMA foreign_keys = ON");
  }

  bool apply_schema_and_migrations(sqlite3 *db, int schema_version) {
    const bool had_existing_history_tables =
      table_exists(db, "sessions") ||
      table_exists(db, "samples") ||
      table_exists(db, "events");

    if (!exec(db, SCHEMA_SQL)) {
      diagnostics::error() << "session_history: schema creation failed";
      return false;
    }

    if (!had_existing_history_tables) {
      return exec(db, ("PRAGMA user_version = " + std::to_string(schema_version)).c_str());
    }

    auto column_exists = [&](const char *table, const char *column) -> bool {
      sqlite3_stmt *check = nullptr;
      std::string sql = std::string("PRAGMA table_info(") + table + ")";
      bool found = false;
      if (sqlite3_prepare_v2(db, sql.c_str(), -1, &check, nullptr) == SQLITE_OK) {
        while (sqlite3_step(check) == SQLITE_ROW) {
          const unsigned char *name = sqlite3_column_text(check, 1);
          if (name && std::string(reinterpret_cast<const char *>(name)) == column) {
            found = true;
            break;
          }
        }
      }
      if (check) sqlite3_finalize(check);
      return found;
    };

    auto add_column = [&](const char *table, const char *column, const char *type_default) {
      if (!column_exists(table, column)) {
        std::string sql = std::string("ALTER TABLE ") + table + " ADD COLUMN " + column + " " + type_default;
        if (!exec(db, sql.c_str())) {
          diagnostics::error() << "session_history: failed to add column " << table << "." << column;
          return false;
        }
      }
      return true;
    };

    auto rename_column = [&](const char *table, const char *old_name, const char *new_name) {
      if (!column_exists(table, old_name) || column_exists(table, new_name)) {
        return true;
      }
      const std::string sql = std::string("ALTER TABLE ") + table + " RENAME COLUMN " + old_name + " TO " + new_name;
      if (!exec(db, sql.c_str())) {
        diagnostics::error() << "session_history: failed to rename " << table << "." << old_name << " to " << new_name;
        return false;
      }
      return true;
    };

    const int current_schema_version = read_pragma_int(db, "PRAGMA user_version");
    (void) schema_version;

    if (current_schema_version < 2) {
      if (!add_column("sessions", "target_requested_bitrate_kbps", "INTEGER")) {
        return false;
      }
    }
    if (current_schema_version < 3) {
      if (!add_column("sessions", "host_cpu_model", "TEXT") ||
          !add_column("sessions", "host_gpu_model", "TEXT") ||
          !add_column("samples", "host_cpu_percent", "REAL DEFAULT -1") ||
          !add_column("samples", "host_gpu_percent", "REAL DEFAULT -1") ||
          !add_column("samples", "host_gpu_encoder_percent", "REAL DEFAULT -1") ||
          !add_column("samples", "host_ram_percent", "REAL DEFAULT -1") ||
          !add_column("samples", "host_vram_percent", "REAL DEFAULT -1") ||
          !add_column("samples", "host_cpu_temp_c", "REAL DEFAULT -1") ||
          !add_column("samples", "host_gpu_temp_c", "REAL DEFAULT -1") ||
          !add_column("samples", "host_net_rx_bps", "REAL DEFAULT -1") ||
          !add_column("samples", "host_net_tx_bps", "REAL DEFAULT -1")) {
        return false;
      }
    }
    if (current_schema_version < 4) {
      if (!add_column("sessions", "yuv444", "INTEGER DEFAULT 0") ||
          !add_column("sessions", "server_version", "TEXT")) {
        return false;
      }
    }
    if (current_schema_version < 5) {
      if (!add_column("sessions", "stream_gpu_model", "TEXT")) {
        return false;
      }
    }
    if (current_schema_version < 6) {
      if (!rename_column("sessions", "target_bitrate_kbps", "encoder_bitrate_kbps")) {
        return false;
      }
      if (!rename_column("sessions", "target_requested_bitrate_kbps", "requested_bitrate_kbps")) {
        return false;
      }
    }
    if (current_schema_version < 7 ||
        !table_has_delete_cascade(db, "samples", "sessions", "session_uuid") ||
        !table_has_delete_cascade(db, "events", "sessions", "session_uuid")) {
      constexpr const char *CREATE_SAMPLES_TABLE = R"(
        CREATE TABLE samples (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          session_uuid TEXT NOT NULL REFERENCES sessions(uuid) ON DELETE CASCADE,
          timestamp_unix REAL NOT NULL,
          bytes_sent_total INTEGER DEFAULT 0,
          packets_sent_video INTEGER DEFAULT 0,
          frames_sent INTEGER DEFAULT 0,
          last_frame_index INTEGER DEFAULT 0,
          video_dropped INTEGER DEFAULT 0,
          audio_dropped INTEGER DEFAULT 0,
          client_reported_losses INTEGER DEFAULT 0,
          idr_requests INTEGER DEFAULT 0,
          ref_invalidations INTEGER DEFAULT 0,
          encode_latency_ms REAL DEFAULT 0,
          actual_fps REAL DEFAULT 0,
          actual_bitrate_kbps REAL DEFAULT 0,
          frame_interval_jitter_ms REAL DEFAULT 0,
          host_cpu_percent REAL DEFAULT -1,
          host_gpu_percent REAL DEFAULT -1,
          host_gpu_encoder_percent REAL DEFAULT -1,
          host_ram_percent REAL DEFAULT -1,
          host_vram_percent REAL DEFAULT -1,
          host_cpu_temp_c REAL DEFAULT -1,
          host_gpu_temp_c REAL DEFAULT -1,
          host_net_rx_bps REAL DEFAULT -1,
          host_net_tx_bps REAL DEFAULT -1
        );
      )";
      constexpr const char *SAMPLES_COLUMNS =
        "id, session_uuid, timestamp_unix, bytes_sent_total, packets_sent_video, "
        "frames_sent, last_frame_index, video_dropped, audio_dropped, client_reported_losses, "
        "idr_requests, ref_invalidations, encode_latency_ms, actual_fps, actual_bitrate_kbps, "
        "frame_interval_jitter_ms, host_cpu_percent, host_gpu_percent, host_gpu_encoder_percent, "
        "host_ram_percent, host_vram_percent, host_cpu_temp_c, host_gpu_temp_c, host_net_rx_bps, host_net_tx_bps";
      constexpr const char *SAMPLES_INDEXES =
        "CREATE INDEX IF NOT EXISTS idx_samples_session ON samples(session_uuid);"
        "CREATE INDEX IF NOT EXISTS idx_samples_time ON samples(session_uuid, timestamp_unix);";
      if (!rebuild_child_table_with_cascade(
            db,
            "samples",
            "samples_legacy_no_cascade",
            CREATE_SAMPLES_TABLE,
            SAMPLES_COLUMNS,
            SAMPLES_INDEXES)) {
        diagnostics::error() << "session_history: failed to rebuild samples table with ON DELETE CASCADE";
        return false;
      }

      constexpr const char *CREATE_EVENTS_TABLE = R"(
        CREATE TABLE events (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          session_uuid TEXT NOT NULL REFERENCES sessions(uuid) ON DELETE CASCADE,
          timestamp_unix REAL NOT NULL,
          event_type TEXT NOT NULL,
          payload TEXT
        );
      )";
      constexpr const char *EVENTS_COLUMNS = "id, session_uuid, timestamp_unix, event_type, payload";
      constexpr const char *EVENTS_INDEXES =
        "CREATE INDEX IF NOT EXISTS idx_events_session ON events(session_uuid);"
        "CREATE INDEX IF NOT EXISTS idx_events_time ON events(session_uuid, timestamp_unix);";
      if (!rebuild_child_table_with_cascade(
            db,
            "events",
            "events_legacy_no_cascade",
            CREATE_EVENTS_TABLE,
            EVENTS_COLUMNS,
            EVENTS_INDEXES)) {
        diagnostics::error() << "session_history: failed to rebuild events table with ON DELETE CASCADE";
        return false;
      }
    }

    return exec(db, ("PRAGMA user_version = " + std::to_string(schema_version)).c_str());
  }

  bool process_begin(sqlite3 *db, const session_metadata_t &metadata) {
    return process_begin_at(db, metadata, now_unix());
  }

  bool process_begin_at(sqlite3 *db, const session_metadata_t &metadata, double start_time_unix) {
    auto stmt = prepare(db,
      "INSERT OR IGNORE INTO sessions "
      "(uuid, protocol, client_name, device_name, app_name, "
      " width, height, target_fps, encoder_bitrate_kbps, requested_bitrate_kbps, "
      " codec, hdr, yuv444, audio_channels, start_time_unix, server_version, host_cpu_model, host_gpu_model, stream_gpu_model) "
      "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    if (!stmt) return false;

    sqlite3_bind_text(stmt.get(), 1, metadata.uuid.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt.get(), 2, metadata.protocol.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt.get(), 3, metadata.client_name.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt.get(), 4, metadata.device_name.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt.get(), 5, metadata.app_name.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt.get(), 6, metadata.width);
    sqlite3_bind_int(stmt.get(), 7, metadata.height);
    sqlite3_bind_int(stmt.get(), 8, metadata.target_fps);
    sqlite3_bind_int(stmt.get(), 9, metadata.encoder_bitrate_kbps);
    sqlite3_bind_int(stmt.get(), 10, metadata.requested_bitrate_kbps);
    sqlite3_bind_text(stmt.get(), 11, metadata.codec.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(stmt.get(), 12, metadata.hdr ? 1 : 0);
    sqlite3_bind_int(stmt.get(), 13, metadata.yuv444 ? 1 : 0);
    sqlite3_bind_int(stmt.get(), 14, metadata.audio_channels);
    sqlite3_bind_double(stmt.get(), 15, start_time_unix);
    sqlite3_bind_text(stmt.get(), 16, metadata.server_version.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt.get(), 17, metadata.host_cpu_model.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt.get(), 18, metadata.host_gpu_model.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt.get(), 19, metadata.stream_gpu_model.c_str(), -1, SQLITE_TRANSIENT);

    if (sqlite3_step(stmt.get()) != SQLITE_DONE) {
      diagnostics::error() << "session_history: begin_session insert failed for uuid=" << metadata.uuid
                           << ": " << sqlite3_errmsg(db);
      return false;
    }

    if (sqlite3_changes(db) == 0) {
      diagnostics::warning()
        << "session_history: begin_session ignored - uuid already present in DB: "
        << metadata.uuid << " (sessions will be merged into the existing row)";
    }
    return true;
  }

  bool process_end(sqlite3 *db, const std::string &uuid) {
    return process_end_at(db, uuid, now_unix());
  }

  bool process_end_at(sqlite3 *db, const std::string &uuid, double end_time) {

    std::string verdict = "unknown";
    {
      auto stmt = prepare(db,
        "SELECT COUNT(*), "
        "SUM(CASE WHEN encode_latency_ms > 16 THEN 1 ELSE 0 END), "
        "MAX(client_reported_losses), "
        "MAX(frames_sent) "
        "FROM samples WHERE session_uuid = ?");
      if (stmt) {
        sqlite3_bind_text(stmt.get(), 1, uuid.c_str(), -1, SQLITE_TRANSIENT);
        if (sqlite3_step(stmt.get()) == SQLITE_ROW) {
          int sample_count = sqlite3_column_int(stmt.get(), 0);
          int high_latency_count = sqlite3_column_int(stmt.get(), 1);
          std::int64_t max_reported_losses = sqlite3_column_int64(stmt.get(), 2);
          std::int64_t max_frames_sent = sqlite3_column_int64(stmt.get(), 3);

          if (sample_count > 0) {
            double loss_ratio = max_frames_sent > 0 ? static_cast<double>(max_reported_losses) / static_cast<double>(max_frames_sent) : 0;

            if (loss_ratio > 0.05) {
              verdict = "failed";
            } else if (high_latency_count > 0 || max_reported_losses > 0) {
              verdict = "degraded";
            } else {
              verdict = "healthy";
            }
          }
        }
      }
    }

    auto stmt = prepare(db,
      "UPDATE sessions SET end_time_unix = ?, "
      "duration_seconds = ? - start_time_unix, "
      "verdict = ? "
      "WHERE uuid = ?");
    if (!stmt) return false;

    sqlite3_bind_double(stmt.get(), 1, end_time);
    sqlite3_bind_double(stmt.get(), 2, end_time);
    sqlite3_bind_text(stmt.get(), 3, verdict.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt.get(), 4, uuid.c_str(), -1, SQLITE_TRANSIENT);
    if (sqlite3_step(stmt.get()) != SQLITE_DONE) {
      diagnostics::error() << "session_history: end_session update failed for uuid=" << uuid
                           << ": " << sqlite3_errmsg(db);
      return false;
    }
    return true;
  }

  bool process_sample(sqlite3 *db, const session_sample_t &sample, int max_samples_per_session) {
    if (!session_accepts_live_updates(db, sample.session_uuid)) {
      diagnostics::debug() << "session_history: dropping stale sample for ended or missing session "
                           << sample.session_uuid;
      return true;
    }
    auto stmt = prepare(db,
      "INSERT INTO samples "
      "(session_uuid, timestamp_unix, bytes_sent_total, packets_sent_video, "
      " frames_sent, last_frame_index, video_dropped, audio_dropped, "
      " client_reported_losses, idr_requests, ref_invalidations, "
      " encode_latency_ms, actual_fps, actual_bitrate_kbps, frame_interval_jitter_ms, "
      " host_cpu_percent, host_gpu_percent, host_gpu_encoder_percent, "
      " host_ram_percent, host_vram_percent, host_cpu_temp_c, host_gpu_temp_c, "
      " host_net_rx_bps, host_net_tx_bps) "
      "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    if (!stmt) return false;

    sqlite3_bind_text(stmt.get(), 1, sample.session_uuid.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_double(stmt.get(), 2, sample.timestamp_unix);
    sqlite3_bind_int64(stmt.get(), 3, static_cast<std::int64_t>(sample.bytes_sent_total));
    sqlite3_bind_int64(stmt.get(), 4, static_cast<std::int64_t>(sample.packets_sent_video));
    sqlite3_bind_int64(stmt.get(), 5, static_cast<std::int64_t>(sample.frames_sent));
    sqlite3_bind_int64(stmt.get(), 6, sample.last_frame_index);
    sqlite3_bind_int64(stmt.get(), 7, static_cast<std::int64_t>(sample.video_dropped));
    sqlite3_bind_int64(stmt.get(), 8, static_cast<std::int64_t>(sample.audio_dropped));
    sqlite3_bind_int64(stmt.get(), 9, sample.client_reported_losses);
    sqlite3_bind_int(stmt.get(), 10, static_cast<int>(sample.idr_requests));
    sqlite3_bind_int(stmt.get(), 11, static_cast<int>(sample.ref_invalidations));
    sqlite3_bind_double(stmt.get(), 12, sample.encode_latency_ms);
    sqlite3_bind_double(stmt.get(), 13, sample.actual_fps);
    sqlite3_bind_double(stmt.get(), 14, sample.actual_bitrate_kbps);
    sqlite3_bind_double(stmt.get(), 15, sample.frame_interval_jitter_ms);
    sqlite3_bind_double(stmt.get(), 16, sample.host_cpu_percent);
    sqlite3_bind_double(stmt.get(), 17, sample.host_gpu_percent);
    sqlite3_bind_double(stmt.get(), 18, sample.host_gpu_encoder_percent);
    sqlite3_bind_double(stmt.get(), 19, sample.host_ram_percent);
    sqlite3_bind_double(stmt.get(), 20, sample.host_vram_percent);
    sqlite3_bind_double(stmt.get(), 21, sample.host_cpu_temp_c);
    sqlite3_bind_double(stmt.get(), 22, sample.host_gpu_temp_c);
    sqlite3_bind_double(stmt.get(), 23, sample.host_net_rx_bps);
    sqlite3_bind_double(stmt.get(), 24, sample.host_net_tx_bps);

    if (sqlite3_step(stmt.get()) != SQLITE_DONE) {
      diagnostics::error() << "session_history: sample insert failed for uuid=" << sample.session_uuid
                           << ": " << sqlite3_errmsg(db);
      return false;
    }

    auto trim = prepare(
      db,
      "DELETE FROM samples WHERE id IN ("
      "  SELECT id FROM samples WHERE session_uuid = ? "
      "  ORDER BY timestamp_unix DESC, id DESC LIMIT -1 OFFSET ?"
      ")");
    if (!trim) {
      return false;
    }
    sqlite3_bind_text(trim.get(), 1, sample.session_uuid.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(trim.get(), 2, max_samples_per_session);
    if (sqlite3_step(trim.get()) != SQLITE_DONE) {
      diagnostics::error() << "session_history: sample trim failed for uuid=" << sample.session_uuid
                           << ": " << sqlite3_errmsg(db);
      return false;
    }
    return true;
  }

  bool process_event(sqlite3 *db, const session_event_t &event, int max_events_per_session) {
    if (!session_accepts_live_updates(db, event.session_uuid)) {
      diagnostics::debug() << "session_history: dropping stale event for ended or missing session "
                           << event.session_uuid << " type=" << event.event_type;
      return true;
    }
    auto stmt = prepare(db,
      "INSERT INTO events (session_uuid, timestamp_unix, event_type, payload) "
      "VALUES (?,?,?,?)");
    if (!stmt) return false;

    sqlite3_bind_text(stmt.get(), 1, event.session_uuid.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_double(stmt.get(), 2, event.timestamp_unix);
    sqlite3_bind_text(stmt.get(), 3, event.event_type.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt.get(), 4, event.payload.c_str(), -1, SQLITE_TRANSIENT);

    if (sqlite3_step(stmt.get()) != SQLITE_DONE) {
      diagnostics::error() << "session_history: event insert failed for uuid=" << event.session_uuid
                           << " type=" << event.event_type << ": " << sqlite3_errmsg(db);
      return false;
    }

    auto trim = prepare(
      db,
      "DELETE FROM events WHERE id IN ("
      "  SELECT id FROM events WHERE session_uuid = ? "
      "  ORDER BY timestamp_unix DESC, id DESC LIMIT -1 OFFSET ?"
      ")");
    if (!trim) {
      return false;
    }
    sqlite3_bind_text(trim.get(), 1, event.session_uuid.c_str(), -1, SQLITE_TRANSIENT);
    sqlite3_bind_int(trim.get(), 2, max_events_per_session);
    if (sqlite3_step(trim.get()) != SQLITE_DONE) {
      diagnostics::error() << "session_history: event trim failed for uuid=" << event.session_uuid
                           << " type=" << event.event_type << ": " << sqlite3_errmsg(db);
      return false;
    }
    return true;
  }

  delete_apply_e process_delete(sqlite3 *db, const std::string &uuid) {
    int affected = 0;
    if (!delete_session_rows(db, uuid, true, affected)) {
      return delete_apply_e::failed;
    }

    diagnostics::info() << "Deleted session " << uuid << " from history (rows=" << affected << ")";
    return affected > 0 ? delete_apply_e::deleted : delete_apply_e::not_found;
  }

  bool process_prune(sqlite3 *db, const prune_options_t &options) {
    if (options.max_history_sessions > 0 && !prune_sessions_by_limit(db, options.max_history_sessions)) {
      return false;
    }

    if (options.prune_sessions_ended_before_unix > 0 &&
        !prune_sessions_older_than(db, options.prune_sessions_ended_before_unix)) {
      return false;
    }

    if (options.max_db_size_bytes > 0) {
      while (estimate_live_db_bytes(db) > options.max_db_size_bytes) {
        auto oldest = oldest_ended_session_uuid(db);
        if (!oldest.has_value()) {
          return false;
        }
        if (oldest->empty()) {
          break;
        }

        int affected = 0;
        if (!delete_session_rows(db, *oldest, false, affected)) {
          return false;
        }
      }
    }

    return true;
  }

  std::vector<session_summary_t> read_session_summaries(sqlite3 *db, int limit, int offset) {
    std::vector<session_summary_t> result;
    auto stmt = prepare(db,
      "SELECT uuid, protocol, client_name, device_name, app_name, "
      "width, height, target_fps, encoder_bitrate_kbps, codec, hdr, yuv444, audio_channels, "
      "start_time_unix, end_time_unix, duration_seconds, verdict, requested_bitrate_kbps, server_version, "
      "host_cpu_model, host_gpu_model, stream_gpu_model "
      "FROM sessions WHERE end_time_unix IS NOT NULL "
      "ORDER BY end_time_unix DESC LIMIT ? OFFSET ?");
    if (!stmt) return result;

    sqlite3_bind_int(stmt.get(), 1, limit);
    sqlite3_bind_int(stmt.get(), 2, offset);

    while (sqlite3_step(stmt.get()) == SQLITE_ROW) {
      result.push_back(row_to_summary(stmt.get()));
    }
    return result;
  }

  std::optional<session_detail_t> read_session_detail(
    sqlite3 *db,
    const std::string &uuid,
    bool include_all,
    int default_detail_sample_limit,
    int default_detail_event_limit) {
    auto stmt = prepare(db,
      "SELECT uuid, protocol, client_name, device_name, app_name, "
      "width, height, target_fps, encoder_bitrate_kbps, codec, hdr, yuv444, audio_channels, "
      "start_time_unix, end_time_unix, duration_seconds, verdict, requested_bitrate_kbps, server_version, "
      "host_cpu_model, host_gpu_model, stream_gpu_model "
      "FROM sessions WHERE uuid = ?");
    if (!stmt) return std::nullopt;
    sqlite3_bind_text(stmt.get(), 1, uuid.c_str(), -1, SQLITE_TRANSIENT);
    if (sqlite3_step(stmt.get()) != SQLITE_ROW) return std::nullopt;

    session_detail_t detail;
    detail.summary = row_to_summary(stmt.get());
    detail.total_samples = read_count(db, "SELECT COUNT(*) FROM samples WHERE session_uuid = ?", uuid);
    detail.total_events = read_count(db, "SELECT COUNT(*) FROM events WHERE session_uuid = ?", uuid);
    detail.samples_truncated = !include_all && detail.total_samples > default_detail_sample_limit;
    detail.events_truncated = !include_all && detail.total_events > default_detail_event_limit;

    auto sample_stmt = prepare(db,
      include_all ?
        "SELECT session_uuid, timestamp_unix, bytes_sent_total, packets_sent_video, "
        "frames_sent, last_frame_index, video_dropped, audio_dropped, "
        "client_reported_losses, idr_requests, ref_invalidations, "
        "encode_latency_ms, actual_fps, actual_bitrate_kbps, frame_interval_jitter_ms, "
        "host_cpu_percent, host_gpu_percent, host_gpu_encoder_percent, "
        "host_ram_percent, host_vram_percent, host_cpu_temp_c, host_gpu_temp_c, "
        "host_net_rx_bps, host_net_tx_bps "
        "FROM samples WHERE session_uuid = ? ORDER BY timestamp_unix"
        :
        "SELECT session_uuid, timestamp_unix, bytes_sent_total, packets_sent_video, "
        "frames_sent, last_frame_index, video_dropped, audio_dropped, "
        "client_reported_losses, idr_requests, ref_invalidations, "
        "encode_latency_ms, actual_fps, actual_bitrate_kbps, frame_interval_jitter_ms, "
        "host_cpu_percent, host_gpu_percent, host_gpu_encoder_percent, "
        "host_ram_percent, host_vram_percent, host_cpu_temp_c, host_gpu_temp_c, "
        "host_net_rx_bps, host_net_tx_bps "
        "FROM ("
        "  SELECT session_uuid, timestamp_unix, bytes_sent_total, packets_sent_video, "
        "  frames_sent, last_frame_index, video_dropped, audio_dropped, "
        "  client_reported_losses, idr_requests, ref_invalidations, "
        "  encode_latency_ms, actual_fps, actual_bitrate_kbps, frame_interval_jitter_ms, "
        "  host_cpu_percent, host_gpu_percent, host_gpu_encoder_percent, "
        "  host_ram_percent, host_vram_percent, host_cpu_temp_c, host_gpu_temp_c, "
        "  host_net_rx_bps, host_net_tx_bps "
        "  FROM samples WHERE session_uuid = ? ORDER BY timestamp_unix DESC LIMIT ?"
        ") ORDER BY timestamp_unix");
    if (sample_stmt) {
      sqlite3_bind_text(sample_stmt.get(), 1, uuid.c_str(), -1, SQLITE_TRANSIENT);
      if (!include_all) {
        sqlite3_bind_int(sample_stmt.get(), 2, default_detail_sample_limit);
      }
      while (sqlite3_step(sample_stmt.get()) == SQLITE_ROW) {
        session_sample_t sample;
        auto col0 = sqlite3_column_text(sample_stmt.get(), 0);
        sample.session_uuid = col0 ? reinterpret_cast<const char *>(col0) : "";
        sample.timestamp_unix = sqlite3_column_double(sample_stmt.get(), 1);
        sample.bytes_sent_total = static_cast<std::uint64_t>(sqlite3_column_int64(sample_stmt.get(), 2));
        sample.packets_sent_video = static_cast<std::uint64_t>(sqlite3_column_int64(sample_stmt.get(), 3));
        sample.frames_sent = static_cast<std::uint64_t>(sqlite3_column_int64(sample_stmt.get(), 4));
        sample.last_frame_index = sqlite3_column_int64(sample_stmt.get(), 5);
        sample.video_dropped = static_cast<std::uint64_t>(sqlite3_column_int64(sample_stmt.get(), 6));
        sample.audio_dropped = static_cast<std::uint64_t>(sqlite3_column_int64(sample_stmt.get(), 7));
        sample.client_reported_losses = sqlite3_column_int64(sample_stmt.get(), 8);
        sample.idr_requests = static_cast<std::uint32_t>(sqlite3_column_int(sample_stmt.get(), 9));
        sample.ref_invalidations = static_cast<std::uint32_t>(sqlite3_column_int(sample_stmt.get(), 10));
        sample.encode_latency_ms = sqlite3_column_double(sample_stmt.get(), 11);
        sample.actual_fps = sqlite3_column_double(sample_stmt.get(), 12);
        sample.actual_bitrate_kbps = sqlite3_column_double(sample_stmt.get(), 13);
        sample.frame_interval_jitter_ms = sqlite3_column_double(sample_stmt.get(), 14);
        auto read_optional_real = [&](int col, double fallback) {
          if (sqlite3_column_count(sample_stmt.get()) > col && sqlite3_column_type(sample_stmt.get(), col) != SQLITE_NULL) {
            return sqlite3_column_double(sample_stmt.get(), col);
          }
          return fallback;
        };
        sample.host_cpu_percent = read_optional_real(15, -1);
        sample.host_gpu_percent = read_optional_real(16, -1);
        sample.host_gpu_encoder_percent = read_optional_real(17, -1);
        sample.host_ram_percent = read_optional_real(18, -1);
        sample.host_vram_percent = read_optional_real(19, -1);
        sample.host_cpu_temp_c = read_optional_real(20, -1);
        sample.host_gpu_temp_c = read_optional_real(21, -1);
        sample.host_net_rx_bps = read_optional_real(22, -1);
        sample.host_net_tx_bps = read_optional_real(23, -1);
        detail.samples.push_back(std::move(sample));
      }
    }

    auto event_stmt = prepare(db,
      include_all ?
        "SELECT session_uuid, timestamp_unix, event_type, payload "
        "FROM events WHERE session_uuid = ? ORDER BY timestamp_unix"
        :
        "SELECT session_uuid, timestamp_unix, event_type, payload "
        "FROM ("
        "  SELECT session_uuid, timestamp_unix, event_type, payload "
        "  FROM events WHERE session_uuid = ? ORDER BY timestamp_unix DESC LIMIT ?"
        ") ORDER BY timestamp_unix");
    if (event_stmt) {
      sqlite3_bind_text(event_stmt.get(), 1, uuid.c_str(), -1, SQLITE_TRANSIENT);
      if (!include_all) {
        sqlite3_bind_int(event_stmt.get(), 2, default_detail_event_limit);
      }
      while (sqlite3_step(event_stmt.get()) == SQLITE_ROW) {
        session_event_t event;
        auto col0 = sqlite3_column_text(event_stmt.get(), 0);
        event.session_uuid = col0 ? reinterpret_cast<const char *>(col0) : "";
        event.timestamp_unix = sqlite3_column_double(event_stmt.get(), 1);
        auto col2 = sqlite3_column_text(event_stmt.get(), 2);
        event.event_type = col2 ? reinterpret_cast<const char *>(col2) : "";
        auto col3 = sqlite3_column_text(event_stmt.get(), 3);
        event.payload = col3 ? reinterpret_cast<const char *>(col3) : "";
        detail.events.push_back(std::move(event));
      }
    }

    return detail;
  }

}  // namespace session_history::storage