/**
 * Activity insert/query helpers.
 *
 * All functions obtain the SQLite instance internally via `getDatabase()`.
 * Queries use positional `?` placeholders throughout (better-sqlite3).
 */

import { getDatabase } from '@myco/db/client.js';
import { appendProjectCondition, type ProjectScope } from '@myco/db/queries/project-scope.js';
import { resolveMycoToolIdentity } from '@myco/db/queries/myco-tool-identity.js';

// ---------------------------------------------------------------------------
// Constants
// ---------------------------------------------------------------------------

/** Default number of activities returned by listActivities when no limit given. */
const DEFAULT_LIST_LIMIT = 100;

/** Default success flag for new activities. */
const DEFAULT_SUCCESS = 1;

/** Default processed flag for new activities. */
const DEFAULT_PROCESSED = 0;

// ---------------------------------------------------------------------------
// Types
// ---------------------------------------------------------------------------

/** Fields required (or optional) when inserting an activity. */
export interface ActivityInsert {
  project_id?: string | null;
  session_id: string;
  tool_name: string;
  timestamp: number;
  created_at: number;
  prompt_batch_id?: number | null;
  tool_input?: string | null;
  tool_output_summary?: string | null;
  file_path?: string | null;
  files_affected?: string | null;
  duration_ms?: number | null;
  success?: number;
  error_message?: string | null;
  processed?: number;
  content_hash?: string | null;
}

/** Row shape returned from activity queries. */
export interface ActivityRow {
  id: number;
  project_id: string | null;
  session_id: string;
  prompt_batch_id: number | null;
  tool_name: string;
  tool_input: string | null;
  tool_output_summary: string | null;
  file_path: string | null;
  files_affected: string | null;
  duration_ms: number | null;
  success: number;
  error_message: string | null;
  timestamp: number;
  processed: number;
  content_hash: string | null;
  created_at: number;
  canopy_injection_tokens: number | null;
  /**
   * Canonical Myco tool identity this activity represents, resolved ONCE at the
   * capture write boundary (see resolveMycoToolIdentity) regardless of entry
   * point — MCP (`mcp__myco__myco_search`) or CLI (a `Bash` activity wrapping
   * `myco tool call myco_search`). Null for non-Myco activities. Consumers read
   * these instead of re-deriving identity from `tool_name`/`tool_input`.
   */
  myco_tool: string | null;
  myco_op: string | null;
}

/** Filter options for `listActivities`. */
export interface ListActivitiesOptions {
  session_id?: string;
  prompt_batch_id?: number;
  scope: ProjectScope;
  limit?: number;
}

// ---------------------------------------------------------------------------
// Column list
// ---------------------------------------------------------------------------

const ACTIVITY_COLUMNS = [
  'id',
  'project_id',
  'session_id',
  'prompt_batch_id',
  'tool_name',
  'tool_input',
  'tool_output_summary',
  'file_path',
  'files_affected',
  'duration_ms',
  'success',
  'error_message',
  'timestamp',
  'processed',
  'content_hash',
  'created_at',
  'canopy_injection_tokens',
  'myco_tool',
  'myco_op',
] as const;

const SELECT_COLUMNS = ACTIVITY_COLUMNS.join(', ');

// ---------------------------------------------------------------------------
// Helpers
// ---------------------------------------------------------------------------

/** Normalize a SQLite result row into a typed ActivityRow. */
function toActivityRow(row: Record<string, unknown>): ActivityRow {
  return {
    id: row.id as number,
    project_id: (row.project_id as string) ?? null,
    session_id: row.session_id as string,
    prompt_batch_id: (row.prompt_batch_id as number) ?? null,
    tool_name: row.tool_name as string,
    tool_input: (row.tool_input as string) ?? null,
    tool_output_summary: (row.tool_output_summary as string) ?? null,
    file_path: (row.file_path as string) ?? null,
    files_affected: (row.files_affected as string) ?? null,
    duration_ms: (row.duration_ms as number) ?? null,
    success: row.success as number,
    error_message: (row.error_message as string) ?? null,
    timestamp: row.timestamp as number,
    processed: row.processed as number,
    content_hash: (row.content_hash as string) ?? null,
    created_at: row.created_at as number,
    canopy_injection_tokens: (row.canopy_injection_tokens as number) ?? null,
    myco_tool: (row.myco_tool as string) ?? null,
    myco_op: (row.myco_op as string) ?? null,
  };
}

// ---------------------------------------------------------------------------
// Public API
// ---------------------------------------------------------------------------

/**
 * Insert a new activity row.
 *
 * The `id` is auto-generated by the INTEGER PRIMARY KEY (AUTOINCREMENT).
 * activities_fts is kept in sync by SQLite triggers declared in schema-ddl.ts.
 */
export function insertActivity(data: ActivityInsert): ActivityRow {
  const db = getDatabase();

  const myco = resolveMycoToolIdentity(data.tool_name, data.tool_input ?? null);
  const info = db.prepare(
    `INSERT INTO activities (
       project_id, session_id, prompt_batch_id, tool_name, tool_input,
       tool_output_summary, file_path, files_affected, duration_ms,
       success, error_message, timestamp, processed,
       content_hash, created_at, myco_tool, myco_op
     ) VALUES (
       COALESCE(?, (SELECT project_id FROM sessions WHERE id = ?)), ?, ?, ?, ?,
       ?, ?, ?, ?,
       ?, ?, ?, ?,
       ?, ?, ?, ?
     )`,
  ).run(
    data.project_id ?? null,
    data.session_id,
    data.session_id,
    data.prompt_batch_id ?? null,
    data.tool_name,
    data.tool_input ?? null,
    data.tool_output_summary ?? null,
    data.file_path ?? null,
    data.files_affected ?? null,
    data.duration_ms ?? null,
    data.success ?? DEFAULT_SUCCESS,
    data.error_message ?? null,
    data.timestamp,
    data.processed ?? DEFAULT_PROCESSED,
    data.content_hash ?? null,
    data.created_at,
    myco?.tool ?? null,
    myco?.op ?? null,
  );

  return toActivityRow(
    db.prepare(`SELECT ${SELECT_COLUMNS} FROM activities WHERE id = ?`).get(Number(info.lastInsertRowid)) as Record<string, unknown>,
  );
}

/** Fields required when inserting an activity with inline batch linkage. */
export interface StatelessActivityInsert {
  session_id: string;
  tool_name: string;
  timestamp: number;
  created_at: number;
  tool_input?: string | null;
  tool_output_summary?: string | null;
  file_path?: string | null;
  files_affected?: string | null;
  duration_ms?: number | null;
  success?: number;
  error_message?: string | null;
  content_hash?: string | null;
  /**
   * Optional canopy_injection_tokens — set when the PostToolUse handler has
   * already consumed a pending Canopy injection for this row. Folded into
   * the initial INSERT so the Pre→Post linkage doesn't require a follow-up
   * UPDATE on the new row.
   */
  canopy_injection_tokens?: number | null;
}

/**
 * Insert an activity with batch linkage resolved via inline subquery.
 *
 * Linkage prefers the latest open batch for the session, then falls back
 * to the most-recently-created batch (closed batches included). The fallback
 * keeps bookkeeping events (`subagent_stop`, `task_completed`, `compact`,
 * `stop_failure`) that arrive *after* the turn's Stop attached to that turn's
 * just-closed INITIAL batch instead of fabricating a phantom recovered row.
 * `prompt_batch_id` is NULL only when the session has zero batches.
 *
 * activities_fts is kept in sync by SQLite triggers declared in schema-ddl.ts.
 */
export function insertActivityWithBatch(
  data: StatelessActivityInsert,
): ActivityRow {
  const db = getDatabase();

  // Wrap the insert + `sessions.tool_count` bump in one transaction. The
  // cache bump on the session row USED to be a
  // separate `incrementSessionToolCount` call in `handleToolUse` —
  // any other call site that inserts an activity (the injection
  // record path, future helpers) would have silently skipped the
  // bump and drifted the cached counter. The single-writer tenet
  // (AGENTS.md) applies here too: this function is THE writer for
  // `activities`, so it also owns the cache the column maintains.
  const myco = resolveMycoToolIdentity(data.tool_name, data.tool_input ?? null);
  const tx = db.transaction(() => {
    const info = db.prepare(
      `INSERT INTO activities (
         project_id, session_id, prompt_batch_id, tool_name, tool_input,
         tool_output_summary, file_path, files_affected, duration_ms,
         success, error_message, timestamp, processed,
         content_hash, created_at, canopy_injection_tokens, myco_tool, myco_op
       ) VALUES (
         (SELECT project_id FROM sessions WHERE id = ?),
         ?,
         (SELECT id FROM prompt_batches WHERE session_id = ?
            ORDER BY (ended_at IS NULL) DESC, (origin = 'human') DESC, id DESC LIMIT 1),
         ?, ?,
         ?, ?, ?, ?,
         ?, ?, ?, ?,
         ?, ?, ?, ?, ?
       )`,
    ).run(
      data.session_id,
      data.session_id,
      data.session_id,
      data.tool_name,
      data.tool_input ?? null,
      data.tool_output_summary ?? null,
      data.file_path ?? null,
      data.files_affected ?? null,
      data.duration_ms ?? null,
      data.success ?? DEFAULT_SUCCESS,
      data.error_message ?? null,
      data.timestamp,
      DEFAULT_PROCESSED,
      data.content_hash ?? null,
      data.created_at,
      data.canopy_injection_tokens ?? null,
      myco?.tool ?? null,
      myco?.op ?? null,
    );

    // Atomic counter bump — folded into the single-writer for
    // `activities`.
    db.prepare(
      `UPDATE sessions SET tool_count = COALESCE(tool_count, 0) + 1 WHERE id = ?`,
    ).run(data.session_id);

    return Number(info.lastInsertRowid);
  });

  const activityId = tx();

  return toActivityRow(
    db.prepare(`SELECT ${SELECT_COLUMNS} FROM activities WHERE id = ?`).get(activityId) as Record<string, unknown>,
  );
}

/**
 * List activities with optional filters, ordered by timestamp ASC.
 *
 * At least one of `session_id` or `prompt_batch_id` should be provided
 * to avoid unbounded queries.
 */
export function listActivities(
  options: ListActivitiesOptions,
): ActivityRow[] {
  const db = getDatabase();

  const conditions: string[] = [];
  const params: unknown[] = [];

  if (options.session_id !== undefined) {
    conditions.push(`session_id = ?`);
    params.push(options.session_id);
  }

  if (options.prompt_batch_id !== undefined) {
    conditions.push(`prompt_batch_id = ?`);
    params.push(options.prompt_batch_id);
  }

  appendProjectCondition(conditions, params, options.scope);

  const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
  const limit = options.limit ?? DEFAULT_LIST_LIMIT;

  params.push(limit);

  const rows = db.prepare(
    `SELECT ${SELECT_COLUMNS}
     FROM activities
     ${where}
     ORDER BY timestamp ASC
     LIMIT ?`,
  ).all(...params) as Record<string, unknown>[];

  return rows.map(toActivityRow);
}

/**
 * List a session's file-touching activities (every row with a non-null
 * `file_path`), ordered by timestamp ASC.
 *
 * This is the authorship-evidence feed for plan capture: which files a session
 * actually wrote. The caller decides which of these are *plan* writes via the
 * shared `isPlanWriteEvent` predicate (single source of truth for plan-write
 * detection across every symbiont), so this query intentionally does NOT filter
 * by tool name. Unbounded by design — one session's file activity set is small,
 * and a `LIMIT` could silently drop an authoring write and resurrect the
 * over-association bug.
 */
export function listSessionFileActivities(
  sessionId: string,
  scope: ProjectScope,
): ActivityRow[] {
  const db = getDatabase();
  const conditions = ['session_id = ?', 'file_path IS NOT NULL'];
  const params: unknown[] = [sessionId];
  appendProjectCondition(conditions, params, scope);

  const rows = db.prepare(
    `SELECT ${SELECT_COLUMNS}
     FROM activities
     WHERE ${conditions.join(' AND ')}
     ORDER BY timestamp ASC`,
  ).all(...params) as Record<string, unknown>[];

  return rows.map(toActivityRow);
}

/**
 * List all activities for a specific batch, ordered by timestamp ASC.
 */
export function listActivitiesByBatch(
  batchId: number,
  options: { scope: ProjectScope },
): ActivityRow[] {
  const db = getDatabase();
  const conditions = ['a.prompt_batch_id = ?'];
  const params: unknown[] = [batchId];
  appendProjectCondition(conditions, params, options.scope, 'b');
  const selectColumns = ACTIVITY_COLUMNS.map((column) => `a.${column} AS ${column}`).join(', ');

  const rows = db.prepare(
    `SELECT ${selectColumns}
     FROM activities a
     JOIN prompt_batches b ON b.id = a.prompt_batch_id
     WHERE ${conditions.join(' AND ')}
     ORDER BY a.timestamp ASC`,
  ).all(...params) as Record<string, unknown>[];

  return rows.map(toActivityRow);
}

/**
 * Latest activity timestamp (epoch seconds) attached to a batch, or null
 * when the batch has no activities. Single indexed MAX() — the liveness
 * signal the stop-replay freshness guard reads for open batches.
 */
export function latestActivityTimestampForBatch(promptBatchId: number): number | null {
  const row = getDatabase().prepare(
    `SELECT MAX(timestamp) AS ts FROM activities WHERE prompt_batch_id = ?`,
  ).get(promptBatchId) as { ts: number | null };
  return row.ts ?? null;
}

/**
 * Count total activities for a given session.
 */
export function countActivities(sessionId: string): number {
  const db = getDatabase();

  const row = db.prepare(
    `SELECT COUNT(*) AS count FROM activities WHERE session_id = ?`,
  ).get(sessionId) as Record<string, unknown>;

  return row.count as number;
}

/**
 * Bulk derived activity counts for a list of session ids — single GROUP BY
 * scan, paired with `countBatchesBySessions` for the sessions list endpoint.
 *
 * Returns a Map keyed by session id; sessions with zero activities are
 * absent (caller treats missing as 0). R4.18 audit.
 */
export function countActivitiesBySessions(sessionIds: readonly string[]): Map<string, number> {
  const result = new Map<string, number>();
  if (sessionIds.length === 0) return result;
  const placeholders = sessionIds.map(() => '?').join(', ');
  const rows = getDatabase().prepare(
    `SELECT session_id, COUNT(*) AS n FROM activities
     WHERE session_id IN (${placeholders})
     GROUP BY session_id`,
  ).all(...sessionIds) as Array<{ session_id: string; n: number }>;
  for (const row of rows) result.set(row.session_id, row.n);
  return result;
}
