/**
 * Characterization tests for getTimelineSummary — the raw-SQL aggregation
 * behind invoice summaries (CLI `scribe summary`, invoice line items).
 *
 * Pins today's behavior, including the sharp edges:
 * - timestamps are Unix SECONDS; range bounds are INCLUSIVE on both ends
 * - projects are merged by DISPLAY NAME (COALESCE(p.name, pr.detected_name)),
 *   so two roots resolving to the same name aggregate into one entry
 * - firstEvent/lastEvent mix seconds (rows) with ms (Date) consistently
 * - deviceIds filters rows; "(unassigned)" is excluded from activeProjects
 */

import { Database } from "bun:sqlite";
import { afterEach, beforeEach, describe, expect, it } from "bun:test";
import { getTimelineSummary } from "./summary";

let db: Database;

// Minimal mirrors of the columns summary.ts reads (documented in its header:
// timeline_events.timestamp is unix seconds).
const DDL = `
CREATE TABLE timeline_events (
  id TEXT PRIMARY KEY,
  timestamp INTEGER NOT NULL,
  source TEXT NOT NULL,
  project_root_id TEXT,
  device_id TEXT
);
CREATE TABLE project_roots (
  id TEXT PRIMARY KEY,
  detected_name TEXT,
  project_id TEXT
);
CREATE TABLE projects (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  color TEXT
);
`;

beforeEach(() => {
  db = new Database(":memory:");
  db.exec(DDL);
});

afterEach(() => {
  db.close();
});

function insertEvent(
  id: string,
  tsSeconds: number,
  source = "git",
  projectRootId: string | null = null,
  deviceId: string | null = null,
): void {
  db.prepare(
    "INSERT INTO timeline_events (id, timestamp, source, project_root_id, device_id) VALUES (?, ?, ?, ?, ?)",
  ).run(id, tsSeconds, source, projectRootId, deviceId);
}

const T0 = 1_750_000_000; // arbitrary fixed epoch seconds

describe("getTimelineSummary", () => {
  it("range bounds are inclusive on both ends (>= since, <= until)", () => {
    insertEvent("at-since", T0);
    insertEvent("inside", T0 + 50);
    insertEvent("at-until", T0 + 100);
    insertEvent("before", T0 - 1);
    insertEvent("after", T0 + 101);

    const summary = getTimelineSummary(
      db,
      new Date(T0 * 1000),
      new Date((T0 + 100) * 1000),
    );
    expect(summary.totalEvents).toBe(3);
  });

  it("merges rows by display name: two roots with the same detected_name become one project", () => {
    db.exec(`
      INSERT INTO project_roots (id, detected_name, project_id) VALUES
        ('root-1', 'acme', NULL),
        ('root-2', 'acme', NULL);
    `);
    insertEvent("e1", T0 + 1, "git", "root-1");
    insertEvent("e2", T0 + 2, "claude", "root-2");
    insertEvent("e3", T0 + 3, "git", "root-2");

    const summary = getTimelineSummary(
      db,
      new Date(T0 * 1000),
      new Date((T0 + 10) * 1000),
    );
    expect(summary.byProject.length).toBe(1);
    expect(summary.byProject[0].projectName).toBe("acme");
    expect(summary.byProject[0].eventCount).toBe(3);
    expect(summary.byProject[0].bySource).toEqual({ git: 2, claude: 1 });
  });

  it("prefers the project-group name over the detected root name (COALESCE)", () => {
    db.exec(`
      INSERT INTO projects (id, name, color) VALUES ('proj-1', 'Client Work', '#fff');
      INSERT INTO project_roots (id, detected_name, project_id) VALUES
        ('root-named', 'repo-folder-name', 'proj-1');
    `);
    insertEvent("e1", T0 + 1, "git", "root-named");

    const summary = getTimelineSummary(
      db,
      new Date(T0 * 1000),
      new Date((T0 + 10) * 1000),
    );
    expect(summary.byProject[0].projectName).toBe("Client Work");
    expect(summary.byProject[0].projectColor).toBe("#fff");
  });

  it("computes firstEvent/lastEvent correctly across rows 1 second apart", () => {
    db.exec(
      "INSERT INTO project_roots (id, detected_name, project_id) VALUES ('r', 'p', NULL)",
    );
    // Two source groups so the merge path (seconds vs ms comparison) runs.
    insertEvent("early", T0 + 1, "git", "r");
    insertEvent("late", T0 + 2, "claude", "r");

    const summary = getTimelineSummary(
      db,
      new Date(T0 * 1000),
      new Date((T0 + 10) * 1000),
    );
    const project = summary.byProject[0];
    expect(project.firstEvent.getTime()).toBe((T0 + 1) * 1000);
    expect(project.lastEvent.getTime()).toBe((T0 + 2) * 1000);
  });

  it("events without a project root aggregate under (unassigned) and don't count as active projects", () => {
    insertEvent("loose", T0 + 1, "manual");

    const summary = getTimelineSummary(
      db,
      new Date(T0 * 1000),
      new Date((T0 + 10) * 1000),
    );
    expect(summary.totalEvents).toBe(1);
    expect(summary.byProject[0].projectName).toBe("(unassigned)");
    expect(summary.activeProjects).toBe(0);
  });

  it("deviceIds filter excludes other devices' events", () => {
    insertEvent("mine", T0 + 1, "git", null, "device-1");
    insertEvent("theirs", T0 + 2, "git", null, "device-2");

    const summary = getTimelineSummary(
      db,
      new Date(T0 * 1000),
      new Date((T0 + 10) * 1000),
      ["device-1"],
    );
    expect(summary.totalEvents).toBe(1);
    expect(summary.bySource).toEqual({ git: 1 });
  });

  it("empty range returns zero totals and no projects", () => {
    insertEvent("outside", T0 + 100);

    const summary = getTimelineSummary(
      db,
      new Date(T0 * 1000),
      new Date((T0 + 10) * 1000),
    );
    expect(summary.totalEvents).toBe(0);
    expect(summary.byProject).toEqual([]);
    expect(summary.bySource).toEqual({});
    expect(summary.activeProjects).toBe(0);
  });

  it("millisecond-precision boundaries truncate via Math.floor (sub-second since is widened)", () => {
    insertEvent("edge", T0);
    // since = T0 + 0.5s floors to T0, so the event at exactly T0 is INCLUDED
    // even though the requested range starts half a second later.
    const summary = getTimelineSummary(
      db,
      new Date(T0 * 1000 + 500),
      new Date((T0 + 10) * 1000),
    );
    expect(summary.totalEvents).toBe(1);
  });
});