/**
 * Graph edge CRUD query helpers.
 *
 * Unlike the `edges` table (which has FK constraints to entities), `graph_edges`
 * supports edges between any node types (session, batch, spore, entity).
 *
 * All functions obtain the SQLite instance internally via `getDatabase()`.
 * Queries use positional `?` placeholders throughout (better-sqlite3).
 */

import crypto from 'node:crypto';
import { getDatabase } from '@myco/db/client.js';
import { QUERY_DEFAULT_LIST_LIMIT, GRAPH_EDGE_DEFAULT_CONFIDENCE } from '@myco/constants.js';
import { getTeamMachineId } from '@myco/team/context.js';
import { syncRow } from '@myco/db/queries/team-outbox.js';
import { appendProjectCondition, type ProjectScope } from '@myco/db/queries/project-scope.js';

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

/** Default BFS traversal depth. */
const DEFAULT_BFS_DEPTH = 2;

/** Maximum BFS traversal depth (capped for performance). */
const MAX_BFS_DEPTH = 5;

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

/** Valid node types in the graph. */
export type GraphNodeType = 'session' | 'batch' | 'spore' | 'plan';

/** Lineage edge types (daemon-created, no LLM). */
export type LineageEdgeType =
  | 'FROM_SESSION'
  | 'EXTRACTED_FROM'
  | 'DERIVED_FROM'
  | 'HAS_BATCH'
  | 'SUPERSEDED_BY'
  | 'PLAN_REFERENCED'
  | 'PLAN_ADVANCED';

/** All valid graph edge types. Only lineage types are produced today;
 * the semantic layer was retired in schema v21. */
export type GraphEdgeType = LineageEdgeType;

/** Fields required (or optional) when inserting a graph edge. */
export interface GraphEdgeInsert {
  agent_id: string;
  project_id?: string | null;
  source_id: string;
  source_type: GraphNodeType;
  target_id: string;
  target_type: GraphNodeType;
  type: GraphEdgeType;
  created_at: number;
  session_id?: string;
  confidence?: number;
  properties?: string;
  machine_id?: string;
}

/** Row shape returned from graph edge queries. */
export interface GraphEdgeRow {
  id: string;
  agent_id: string;
  project_id: string | null;
  source_id: string;
  source_type: string;
  target_id: string;
  target_type: string;
  type: string;
  session_id: string | null;
  confidence: number;
  properties: string | null;
  created_at: number;
  machine_id: string;
  synced_at: number | null;
}

/** Filter options for `listGraphEdges`. */
export interface ListGraphEdgesOptions {
  sourceId?: string;
  targetId?: string;
  type?: string;
  agentId?: string;
  scope: ProjectScope;
  limit?: number;
}

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

const GRAPH_EDGE_COLUMNS = [
  'id',
  'agent_id',
  'project_id',
  'source_id',
  'source_type',
  'target_id',
  'target_type',
  'type',
  'session_id',
  'confidence',
  'properties',
  'created_at',
  'machine_id',
  'synced_at',
] as const;

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

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

/** Normalize a SQLite result row into a typed GraphEdgeRow. */
function toGraphEdgeRow(row: Record<string, unknown>): GraphEdgeRow {
  return {
    id: row.id as string,
    agent_id: row.agent_id as string,
    project_id: (row.project_id as string) ?? null,
    source_id: row.source_id as string,
    source_type: row.source_type as string,
    target_id: row.target_id as string,
    target_type: row.target_type as string,
    type: row.type as string,
    session_id: (row.session_id as string) ?? null,
    confidence: row.confidence as number,
    properties: (row.properties as string) ?? null,
    created_at: row.created_at as number,
    machine_id: (row.machine_id as string) ?? getTeamMachineId(),
    synced_at: (row.synced_at as number) ?? null,
  };
}

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

/**
 * Insert a new graph edge.
 *
 * Generates a UUID id automatically.
 */
export function insertGraphEdge(data: GraphEdgeInsert): GraphEdgeRow {
  const db = getDatabase();
  const id = crypto.randomUUID();

  db.prepare(
    `INSERT INTO graph_edges (
       id, agent_id, project_id, source_id, source_type, target_id, target_type,
       type, session_id, confidence, properties, created_at, machine_id
     ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
  ).run(
    id,
    data.agent_id,
    data.project_id ?? null,
    data.source_id,
    data.source_type,
    data.target_id,
    data.target_type,
    data.type,
    data.session_id ?? null,
    data.confidence ?? GRAPH_EDGE_DEFAULT_CONFIDENCE,
    data.properties ?? null,
    data.created_at,
    data.machine_id ?? getTeamMachineId(),
  );

  const row = toGraphEdgeRow(
    db.prepare(`SELECT ${SELECT_COLUMNS} FROM graph_edges WHERE id = ?`).get(id) as Record<string, unknown>,
  );

  syncRow('graph_edges', row);

  return row;
}

/**
 * List graph edges with optional filters, ordered by created_at DESC.
 */
export function listGraphEdges(
  options: ListGraphEdgesOptions,
): GraphEdgeRow[] {
  const db = getDatabase();

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

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

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

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

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

  appendProjectCondition(conditions, params, options.scope);

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

  params.push(limit);

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

  return rows.map(toGraphEdgeRow);
}

/**
 * BFS traversal from a node across graph edges.
 *
 * Returns all edges reachable within `depth` hops from the starting node.
 *
 * @param nodeId   - The starting node ID.
 * @param nodeType - The starting node type.
 * @param options  - Optional depth limit (default 2, max 5).
 */
export function getGraphForNode(
  nodeId: string,
  nodeType: GraphNodeType,
  options: { depth?: number; scope: ProjectScope },
): { edges: GraphEdgeRow[] } {
  const db = getDatabase();
  const depth = Math.min(Math.max(options.depth ?? DEFAULT_BFS_DEPTH, 1), MAX_BFS_DEPTH);

  const seenEdgeIds = new Set<string>();
  const collectedEdges: GraphEdgeRow[] = [];
  const visited = new Set<string>([`${nodeType}:${nodeId}`]);
  let frontier = new Set<string>([nodeId]);

  for (let hop = 0; hop < depth; hop++) {
    if (frontier.size === 0) break;

    const frontierArray = Array.from(frontier);
    const placeholders = frontierArray.map(() => `?`).join(', ');

    const conditions = [
      `(source_id IN (${placeholders}) OR target_id IN (${placeholders}))`,
    ];
    const params: unknown[] = [...frontierArray, ...frontierArray];
    appendProjectCondition(conditions, params, options.scope);

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

    const nextFrontier = new Set<string>();

    for (const row of rows) {
      const edge = toGraphEdgeRow(row);
      if (!seenEdgeIds.has(edge.id)) {
        seenEdgeIds.add(edge.id);
        collectedEdges.push(edge);
      }
      const sourceKey = `${edge.source_type}:${edge.source_id}`;
      const targetKey = `${edge.target_type}:${edge.target_id}`;
      if (!visited.has(sourceKey)) {
        visited.add(sourceKey);
        nextFrontier.add(edge.source_id);
      }
      if (!visited.has(targetKey)) {
        visited.add(targetKey);
        nextFrontier.add(edge.target_id);
      }
    }

    frontier = nextFrontier;
  }

  return { edges: collectedEdges };
}
