export interface TimelineSummary {
  totalEvents: number;
  activeProjects: number;
  bySource: Record<string, number>;
  byProject: Array<{
    projectName: string;
    projectColor: string | null;
    eventCount: number;
    bySource: Record<string, number>;
    firstEvent: Date;
    lastEvent: Date;
  }>;
  dateRange: { start: Date; end: Date };
}

/** Minimal DB interface compatible with both bun:sqlite and @libsql/client. */
interface ReadonlyDB {
  prepare(sql: string): { all(...params: unknown[]): unknown[] };
}

/**
 * Build a WHERE clause fragment and parameter list for the timestamp range
 * and optional device ID filter.
 */
function buildWhereClause(
  sinceTs: number,
  untilTs: number,
  deviceIds: string[] | undefined,
  tableAlias = "",
): { clause: string; params: unknown[] } {
  const prefix = tableAlias ? `${tableAlias}.` : "";
  let clause = `${prefix}timestamp >= ? AND ${prefix}timestamp <= ?`;
  const params: unknown[] = [sinceTs, untilTs];

  if (deviceIds && deviceIds.length > 0) {
    const placeholders = deviceIds.map(() => "?").join(", ");
    clause += ` AND ${prefix}device_id IN (${placeholders})`;
    params.push(...deviceIds);
  }

  return { clause, params };
}

/**
 * Aggregates timeline events for a date range.
 *
 * Uses raw SQL so the CLI can call this with `bun:sqlite` without pulling in
 * Drizzle. The DB interface is intentionally minimal.
 *
 * Column references match the Drizzle schema in `packages/core/src/db/schema.ts`:
 * - `timeline_events`: timestamp (unix seconds), source, project_root_id, device_id
 * - `project_roots`: id, detected_name, project_id
 * - `projects`: id, name, color
 */
export function getTimelineSummary(
  db: ReadonlyDB,
  since: Date,
  until: Date,
  deviceIds?: string[],
): TimelineSummary {
  const sinceTs = Math.floor(since.getTime() / 1000);
  const untilTs = Math.floor(until.getTime() / 1000);

  // --- Total event count ---
  const totalWhere = buildWhereClause(sinceTs, untilTs, deviceIds);
  const totalRows = db
    .prepare(
      `SELECT COUNT(*) AS count FROM timeline_events WHERE ${totalWhere.clause}`,
    )
    .all(...totalWhere.params) as Array<{ count: number }>;

  // --- Events grouped by source ---
  const sourceWhere = buildWhereClause(sinceTs, untilTs, deviceIds);
  const sourceRows = db
    .prepare(
      `SELECT source, COUNT(*) AS count FROM timeline_events WHERE ${sourceWhere.clause} GROUP BY source ORDER BY count DESC`,
    )
    .all(...sourceWhere.params) as Array<{ source: string; count: number }>;

  // --- Events grouped by project + source ---
  // Use COALESCE to prefer the project group name (projects.name) over the
  // auto-detected root name (project_roots.detected_name).
  const projectWhere = buildWhereClause(sinceTs, untilTs, deviceIds, "te");
  const projectRows = db
    .prepare(
      `SELECT
        COALESCE(p.name, pr.detected_name) AS projectName,
        p.color AS projectColor,
        te.source,
        COUNT(*) AS count,
        MIN(te.timestamp) AS firstTs,
        MAX(te.timestamp) AS lastTs
      FROM timeline_events te
      LEFT JOIN project_roots pr ON te.project_root_id = pr.id
      LEFT JOIN projects p ON pr.project_id = p.id
      WHERE ${projectWhere.clause}
      GROUP BY projectName, p.color, te.source
      ORDER BY count DESC`,
    )
    .all(...projectWhere.params) as Array<{
    projectName: string | null;
    projectColor: string | null;
    source: string;
    count: number;
    firstTs: number;
    lastTs: number;
  }>;

  // --- Aggregate per-source rows into per-project entries ---
  const projectMap = new Map<
    string,
    {
      projectName: string;
      projectColor: string | null;
      eventCount: number;
      bySource: Record<string, number>;
      firstEvent: Date;
      lastEvent: Date;
    }
  >();

  for (const row of projectRows) {
    const name = row.projectName || "(unassigned)";
    const existing = projectMap.get(name);
    if (existing) {
      existing.eventCount += row.count;
      existing.bySource[row.source] =
        (existing.bySource[row.source] || 0) + row.count;
      if (row.firstTs < existing.firstEvent.getTime() / 1000) {
        existing.firstEvent = new Date(row.firstTs * 1000);
      }
      if (row.lastTs > existing.lastEvent.getTime() / 1000) {
        existing.lastEvent = new Date(row.lastTs * 1000);
      }
    } else {
      projectMap.set(name, {
        projectName: name,
        projectColor: row.projectColor,
        eventCount: row.count,
        bySource: { [row.source]: row.count },
        firstEvent: new Date(row.firstTs * 1000),
        lastEvent: new Date(row.lastTs * 1000),
      });
    }
  }

  const byProject = Array.from(projectMap.values()).sort(
    (a, b) => b.eventCount - a.eventCount,
  );

  return {
    totalEvents: totalRows[0]?.count ?? 0,
    activeProjects: byProject.filter((p) => p.projectName !== "(unassigned)")
      .length,
    bySource: Object.fromEntries(sourceRows.map((r) => [r.source, r.count])),
    byProject,
    dateRange: { start: since, end: until },
  };
}