import { sql } from "drizzle-orm";
import { timelineEvents } from "../db/schema";
import type { SyncDb } from "./types";

/** Per-week event count bucket for the sync-coverage heatmap. */
export interface SyncCoverageWeek {
  /** ISO-ish week key, e.g. "2026-W27". */
  week: string;
  /** Number of events in the week. */
  count: number;
  /** Earliest event timestamp in the week (unix seconds). */
  minDate: number;
  /** Latest event timestamp in the week (unix seconds). */
  maxDate: number;
}

/** Aggregate sync coverage: per-week counts plus the overall date range. */
export interface SyncCoverage {
  weeks: SyncCoverageWeek[];
  range: {
    /** ISO string of the earliest event, or null when empty. */
    minDate: string | null;
    /** ISO string of the latest event, or null when empty. */
    maxDate: string | null;
    /** Total event count. */
    total: number;
  };
}

/**
 * Compute per-week event counts for the sync-coverage heatmap.
 *
 * The aggregation happens entirely in SQL (`GROUP BY` ISO week), so wide date
 * ranges never load raw events into memory. Works with any Drizzle SQLite
 * instance (bun:sqlite on desktop, libsql on the hub).
 *
 * Shared by the desktop and hub `GET /api/sync/coverage` routes so the query
 * has a single source of truth (OPL-2656).
 */
export async function getSyncCoverage(db: SyncDb): Promise<SyncCoverage> {
  // Events grouped by week (ISO week format: YYYY-WW).
  // SQLite strftime('%W') gives week number 00-53.
  const weeks: SyncCoverageWeek[] = await db
    .select({
      week: sql<string>`strftime('%Y-W%W', datetime(${timelineEvents.timestamp}, 'unixepoch'))`.as(
        "week",
      ),
      count: sql<number>`count(*)`.as("count"),
      minDate: sql<number>`min(${timelineEvents.timestamp})`.as("min_date"),
      maxDate: sql<number>`max(${timelineEvents.timestamp})`.as("max_date"),
    })
    .from(timelineEvents)
    .groupBy(
      sql`strftime('%Y-W%W', datetime(${timelineEvents.timestamp}, 'unixepoch'))`,
    )
    .orderBy(
      sql`strftime('%Y-W%W', datetime(${timelineEvents.timestamp}, 'unixepoch'))`,
    );

  // Overall date range + total.
  const dateRange = await db
    .select({
      minDate: sql<number>`min(${timelineEvents.timestamp})`.as("min_date"),
      maxDate: sql<number>`max(${timelineEvents.timestamp})`.as("max_date"),
      total: sql<number>`count(*)`.as("total"),
    })
    .from(timelineEvents)
    .then(
      (
        r: Array<{
          minDate: number | null;
          maxDate: number | null;
          total: number;
        }>,
      ) => r[0],
    );

  return {
    weeks,
    range: {
      minDate: dateRange?.minDate
        ? new Date(dateRange.minDate * 1000).toISOString()
        : null,
      maxDate: dateRange?.maxDate
        ? new Date(dateRange.maxDate * 1000).toISOString()
        : null,
      total: dateRange?.total || 0,
    },
  };
}