import type { Database } from "bun:sqlite";

interface RootActivityRow {
  project_root_id: string | null;
  last_activity: number | null;
}

/**
 * Map each project root to the timestamp (epoch milliseconds) of its most
 * recent timeline event.
 *
 * This is the "recently modified/active" signal for the Projects roots list:
 * `project_roots.created_at` only records when a root row was inserted (≈ when
 * discovery ran, uniform across a batch), so it can't float actively-worked
 * roots to the top. The latest event per root is the meaningful recency signal
 * and is derivable at query time — no schema/migration needed. `timestamp` is
 * stored in epoch SECONDS (`integer` timestamp mode), so it is scaled to
 * milliseconds to match `Date#getTime()` on the client.
 */
export function getRootLastActivity(sqlite: Database): Map<string, number> {
  const rows = sqlite
    .query<RootActivityRow, []>(
      `SELECT project_root_id, MAX(timestamp) AS last_activity
       FROM timeline_events
       WHERE project_root_id IS NOT NULL
       GROUP BY project_root_id`,
    )
    .all();

  const activity = new Map<string, number>();
  for (const row of rows) {
    if (row.project_root_id && row.last_activity != null) {
      activity.set(row.project_root_id, row.last_activity * 1000);
    }
  }
  return activity;
}