import { describe, it, expect, beforeAll, afterAll } from 'vitest';
import postgres from 'postgres';
import { readFileSync } from 'node:fs';
import { resolve } from 'node:path';
import { fileURLToPath } from 'node:url';

// v1.13.10: integration tests for the tool_cost_stats view. Skipped unless
// DATABASE_URL is set so they don't break `pnpm test` on a fresh checkout.
// Run with:
//   DATABASE_URL=postgres://boocode:<pw>@localhost:5500/boocode pnpm -C apps/server test
//
// Isolation: each test uses a unique tool_name suffix derived from a per-test
// counter. The view aggregates globally across all chats, so without unique
// tool names parallel test runs would interfere. Cleanup deletes by tool_name
// suffix in afterAll.

const DB_URL = process.env.DATABASE_URL;
const describeFn = DB_URL ? describe : describe.skip;

const TEST_RUN_ID = `v13_10_${Date.now()}`;
const tname = (suffix: string) => `${TEST_RUN_ID}_${suffix}`;

describeFn('tool_cost_stats view (v1.13.10)', () => {
  let sql: ReturnType<typeof postgres>;
  let projectId: string;
  let sessionId: string;
  let chatId: string;

  beforeAll(async () => {
    if (!DB_URL) return;
    sql = postgres(DB_URL, { max: 2, idle_timeout: 5, connect_timeout: 5, onnotice: () => {} });

    // Apply the schema before fixtures so the view exists. Idempotent via
    // CREATE OR REPLACE VIEW + CREATE TABLE IF NOT EXISTS; safe to run on a
    // pre-populated DB. Mirrors apps/server/src/db.ts:applySchema.
    const here = fileURLToPath(import.meta.url);
    const schemaPath = resolve(here, '../../../schema.sql');
    const ddl = readFileSync(schemaPath, 'utf8');
    await sql.unsafe(ddl);

    // Fixture project + session + chat for all inserts in this file.
    const proj = await sql<{ id: string }[]>`
      INSERT INTO projects (name, path)
      VALUES (${`tool_cost_stats_test_${TEST_RUN_ID}`}, ${`/tmp/${TEST_RUN_ID}`})
      RETURNING id
    `;
    projectId = proj[0]!.id;
    const sess = await sql<{ id: string }[]>`
      INSERT INTO sessions (project_id, name, model)
      VALUES (${projectId}, ${'test'}, ${'test-model'})
      RETURNING id
    `;
    sessionId = sess[0]!.id;
    const chat = await sql<{ id: string }[]>`
      INSERT INTO chats (session_id, name) VALUES (${sessionId}, ${'test'}) RETURNING id
    `;
    chatId = chat[0]!.id;
  });

  afterAll(async () => {
    if (!DB_URL) return;
    // Project FK CASCADE cleans sessions/chats/messages/parts in one shot.
    await sql`DELETE FROM projects WHERE id = ${projectId}`;
    await sql.end({ timeout: 5 });
  });

  async function insertAssistantTurn(opts: {
    toolNames: string[];
    tokensUsed: number | null;
    ctxUsed: number | null;
    status?: 'streaming' | 'complete' | 'failed' | 'cancelled';
    metadata?: { kind: string } | null;
    createdAt?: Date;
  }): Promise<string> {
    const toolCalls = opts.toolNames.map((name, i) => ({
      id: `call_${TEST_RUN_ID}_${name}_${i}`,
      name,
      args: {},
    }));
    const created = opts.createdAt ?? new Date();
    // v1.13.20: parts-only. messages.tool_calls column was dropped; the
    // tool_cost_stats view reads through messages_with_parts which derives
    // tool_calls from message_parts rows.
    const rows = await sql<{ id: string }[]>`
      INSERT INTO messages (
        session_id, chat_id, role, content, kind, status,
        tokens_used, ctx_used,
        metadata, created_at
      )
      VALUES (
        ${sessionId}, ${chatId}, 'assistant', '', 'message',
        ${opts.status ?? 'complete'},
        ${opts.tokensUsed},
        ${opts.ctxUsed},
        ${opts.metadata ? sql.json(opts.metadata as never) : null},
        ${created}
      )
      RETURNING id
    `;
    const messageId = rows[0]!.id;
    for (let i = 0; i < toolCalls.length; i++) {
      await sql`
        INSERT INTO message_parts (message_id, sequence, kind, payload)
        VALUES (${messageId}, ${i}, 'tool_call', ${sql.json(toolCalls[i] as never)})
      `;
    }
    return messageId;
  }

  it('returns empty when no tool calls exist for a tool name', async () => {
    const t = tname('absent');
    const stats = await sql<{ tool_name: string }[]>`
      SELECT * FROM tool_cost_stats WHERE tool_name = ${t}
    `;
    expect(stats).toEqual([]);
  });

  it('attributes single-tool turn fully to that tool', async () => {
    const t = tname('single');
    await insertAssistantTurn({ toolNames: [t], tokensUsed: 300, ctxUsed: 15000 });
    const stats = await sql<{
      tool_name: string;
      prompt_tokens_sum: number;
      completion_tokens_sum: number;
      n_calls: number;
    }[]>`SELECT * FROM tool_cost_stats WHERE tool_name = ${t}`;
    expect(stats[0]).toMatchObject({
      tool_name: t,
      prompt_tokens_sum: 15000,
      completion_tokens_sum: 300,
      n_calls: 1,
    });
  });

  it('splits multi-tool turn equally across tools', async () => {
    const a = tname('multi_a');
    const b = tname('multi_b');
    const c = tname('multi_c');
    // 3 tools, 300 completion / 15000 prompt → each gets 100 / 5000
    await insertAssistantTurn({ toolNames: [a, b, c], tokensUsed: 300, ctxUsed: 15000 });
    const stats = await sql<{
      tool_name: string;
      prompt_tokens_sum: number;
      completion_tokens_sum: number;
      n_calls: number;
    }[]>`
      SELECT * FROM tool_cost_stats
      WHERE tool_name IN (${a}, ${b}, ${c})
      ORDER BY tool_name
    `;
    expect(stats).toHaveLength(3);
    for (const s of stats) {
      expect(s.completion_tokens_sum).toBe(100);
      expect(s.prompt_tokens_sum).toBe(5000);
      expect(s.n_calls).toBe(1);
    }
  });

  it('limits to last 100 calls per tool (FIFO window)', async () => {
    const t = tname('window');
    // Insert 110 turns with monotonically-increasing created_at and tokensUsed.
    // Expect view to keep only the most recent 100.
    const base = Date.now() + 1_000_000; // distant future to avoid colliding with other tests
    for (let i = 1; i <= 110; i++) {
      await insertAssistantTurn({
        toolNames: [t],
        tokensUsed: i, // 1..110
        ctxUsed: i * 10,
        createdAt: new Date(base + i),
      });
    }
    const [stat] = await sql<{
      n_calls: number;
      completion_tokens_sum: number;
    }[]>`SELECT n_calls, completion_tokens_sum FROM tool_cost_stats WHERE tool_name = ${t}`;
    expect(stat!.n_calls).toBe(100);
    // Last 100 are tokensUsed=11..110, sum = (11+110)*100/2 = 6050.
    expect(stat!.completion_tokens_sum).toBe(6050);
  });

  it('excludes turns with NULL tokens_used (pre-v1.13.7 latent regression)', async () => {
    const t = tname('null_tokens');
    await insertAssistantTurn({ toolNames: [t], tokensUsed: null, ctxUsed: 1000 });
    await insertAssistantTurn({ toolNames: [t], tokensUsed: 100, ctxUsed: null });
    const stats = await sql`SELECT * FROM tool_cost_stats WHERE tool_name = ${t}`;
    expect(stats).toEqual([]);
  });

  it('excludes failed/cancelled turns and cap_hit/doom_loop sentinel rows', async () => {
    const t = tname('filtered');
    // A: status='failed'                              — excluded
    // B: status='cancelled'                           — excluded
    // C: status='complete', metadata={kind:'cap_hit'} — excluded
    // D: status='complete', metadata={kind:'doom_loop'} — excluded
    // E: status='complete', metadata=null             — included
    await insertAssistantTurn({ toolNames: [t], tokensUsed: 100, ctxUsed: 1000, status: 'failed' });
    await insertAssistantTurn({ toolNames: [t], tokensUsed: 100, ctxUsed: 1000, status: 'cancelled' });
    await insertAssistantTurn({ toolNames: [t], tokensUsed: 100, ctxUsed: 1000, metadata: { kind: 'cap_hit' } });
    await insertAssistantTurn({ toolNames: [t], tokensUsed: 100, ctxUsed: 1000, metadata: { kind: 'doom_loop' } });
    await insertAssistantTurn({ toolNames: [t], tokensUsed: 100, ctxUsed: 1000, metadata: null });
    const [stat] = await sql<{ n_calls: number }[]>`
      SELECT n_calls FROM tool_cost_stats WHERE tool_name = ${t}
    `;
    expect(stat!.n_calls).toBe(1);
  });

  it('reads tool_calls via messages_with_parts (parts-authoritative)', async () => {
    const t = tname('parts');
    // v1.13.20: post-column-drop the only source for tool_calls is
    // message_parts. This test asserts the same path the view always took
    // (parts-derived), now that the legacy column COALESCE fallback is gone.
    const rows = await sql<{ id: string }[]>`
      INSERT INTO messages (
        session_id, chat_id, role, content, kind, status,
        tokens_used, ctx_used
      )
      VALUES (
        ${sessionId}, ${chatId}, 'assistant', '', 'message', 'complete',
        200, 5000
      )
      RETURNING id
    `;
    const messageId = rows[0]!.id;
    await sql`
      INSERT INTO message_parts (message_id, sequence, kind, payload)
      VALUES (
        ${messageId}, 0, 'tool_call',
        ${sql.json({ id: `tc_parts_${TEST_RUN_ID}`, name: t, args: {} } as never)}
      )
    `;
    const [stat] = await sql<{ n_calls: number }[]>`
      SELECT n_calls FROM tool_cost_stats WHERE tool_name = ${t}
    `;
    expect(stat!.n_calls).toBe(1);
  });
});
