import type {
  BoardAction,
  CreateTaskInput,
  IdentityType,
  Task,
  TaskAction,
  TaskActionType,
  TaskStatus,
  TaskWithNotes,
} from "@vtit-agent-coding/shared";
import { hasNoScheduleTaint, validateTransition } from "@vtit-agent-coding/shared";
import { HTTPException } from "hono/http-exception";
import { getDefaultBoard } from "./boardRepo";
import { recordBoardRepository } from "./boardRepositoryRepo";
import { asJson, type D1, MAX_TASK_PARTITION_ROWS, newLongId, parseJsonFields, queryDb } from "./db";
import { isRuntimeAvailable } from "./machineRepo";
import { computeBlocked, detectCycle, getDependencies, setDependencies } from "./taskDeps";

const parseTask = <T extends Task>(row: T & { result?: string | null }): T => {
  const task = parseJsonFields(row, ["labels", "input", "metadata"]) as T & { result?: string | null };
  delete task.result;
  return task;
};

const taskPhaseColumnPresence = new WeakMap<object, boolean>();

async function tasksTableHasPhase(db: D1): Promise<boolean> {
  const cached = taskPhaseColumnPresence.get(db as object);
  if (cached !== undefined) return cached;

  const queryable = db as D1 & {
    prepare?: (sql: string) => {
      all: <T>() => Promise<{ results: T[] }>;
    };
    query?: unknown;
  };
  if (typeof queryable.prepare !== "function" || typeof queryable.query === "function") {
    taskPhaseColumnPresence.set(db as object, true);
    return true;
  }

  const res = await queryable.prepare("PRAGMA table_info(tasks)").all<{ name?: string }>();
  const present = res.results.some((column) => column.name === "phase");
  taskPhaseColumnPresence.set(db as object, present);
  return present;
}

async function assertKnownLabels(db: D1, boardId: string, labels: string[] | null | undefined): Promise<void> {
  if (!labels?.length) return;
  const res = await queryDb<{ labels: string }>(db, "SELECT labels FROM boards WHERE id = $1", [boardId]);
  const board = res.rows[0];
  if (!board) throw new HTTPException(400, { message: "Board not found" });
  const knownLabels = new Set(asJson<{ name: string }[]>(board.labels, []).map((label) => label.name));
  const unknown = labels.find((label) => !knownLabels.has(label));
  if (unknown) throw new HTTPException(400, { message: `Label not found: ${unknown}` });
}

async function assertRepositoryBelongsToBoardOwner(db: D1, boardId: string, repositoryId: string): Promise<void> {
  const res = await queryDb(
    db,
    `
    SELECT 1
    FROM boards b
    JOIN repositories r ON r.owner_id = b.owner_id
    WHERE b.id = $1 AND r.id = $2
    `,
    [boardId, repositoryId],
  );
  if (res.rows.length === 0) throw new HTTPException(404, { message: "Repository not found" });
}

function enforceTransition(action: TaskActionType, currentStatus: TaskStatus, identity: IdentityType): void {
  const error = validateTransition(action as any, currentStatus, identity);
  if (error) {
    const status = error.code === "FORBIDDEN" ? 403 : 409;
    throw new HTTPException(status, { message: error.message });
  }
}

async function assertAssignableWorkerAgent(
  db: D1,
  ownerId: string,
  agentId: string,
  missingStatus: 400 | 404,
  skipRuntimeAvailability = false,
): Promise<void> {
  const res = await queryDb<{ kind: string; runtime: string; taints: string | null }>(
    db,
    "SELECT kind, runtime, taints FROM agents WHERE id = $1 AND owner_id = $2",
    [agentId, ownerId],
  );
  const agent = res.rows[0];
  if (!agent) throw new HTTPException(missingStatus, { message: "Agent not found" });
  if (agent.kind !== "worker") throw new HTTPException(400, { message: "Tasks can only be assigned to worker agents" });
  if (hasNoScheduleTaint(asJson(agent.taints, null))) {
    throw new HTTPException(409, { message: "Agent is tainted NoSchedule and cannot be assigned normal tasks" });
  }
  if (skipRuntimeAvailability) return;
  if (!(await isRuntimeAvailable(db, ownerId, agent.runtime))) {
    throw new HTTPException(409, {
      message: `Runtime "${agent.runtime}" is not available on any online machine. Choose or create a worker that uses an available runtime.`,
    });
  }
}

export async function createTask(
  db: D1,
  ownerId: string,
  input: CreateTaskInput & { actorType?: string; actorId?: string; assigned_to?: string; skipRuntimeAvailability?: boolean; phase?: string },
): Promise<Task> {
  const actorType = input.actorType ?? "machine";
  const actorId = input.actorId ?? "system";
  const initialStatus: TaskStatus = "todo";
  let board: { id: string; type: string } | null = null;
  if (input.board_id) {
    const boardRes = await queryDb<{ id: string; type: string }>(db, "SELECT id, type FROM boards WHERE id = $1 AND owner_id = $2", [
      input.board_id,
      ownerId,
    ]);
    board = boardRes.rows[0] || null;
  } else {
    board = await getDefaultBoard(db, ownerId);
  }

  if (!board) throw new HTTPException(400, { message: input.board_id ? "Board not found" : "No board exists. Create a board first." });

  if (board.type === "dev" && !input.repository_id) {
    throw new HTTPException(400, { message: "repository_id is required for dev board tasks" });
  }
  if (board.type === "ops" && input.repository_id) {
    throw new HTTPException(400, { message: "repository_id is not allowed for ops board tasks" });
  }
  if (input.repository_id) await assertRepositoryBelongsToBoardOwner(db, board.id, input.repository_id);

  const maxPosRes = await queryDb<{ max_pos: number }>(
    db,
    "SELECT COALESCE(MAX(position), -1) as max_pos FROM tasks WHERE board_id = $1 AND status = $2",
    [board.id, initialStatus],
  );
  const maxPos = maxPosRes.rows[0];

  const taskId = newLongId();
  const logId = newLongId();
  const now = new Date().toISOString();
  const labelsJson = input.labels ? JSON.stringify(input.labels) : null;
  const inputJson = input.input ? JSON.stringify(input.input) : null;
  const metadataJson = input.metadata ? JSON.stringify(input.metadata) : "{}";
  const position = (maxPos?.max_pos ?? -1) + 1;
  const phase = input.phase || "Phase 1";
  const hasPhaseColumn = await tasksTableHasPhase(db);

  if (input.depends_on?.length) {
    const hasCycle = await detectCycle(db, taskId, input.depends_on);
    if (hasCycle) throw new HTTPException(400, { message: "Circular dependency detected" });
  }

  if (input.created_from) {
    const parentRes = await queryDb(db, "SELECT id FROM tasks WHERE id = $1", [input.created_from]);
    if (!parentRes.rows[0]) throw new HTTPException(400, { message: "Parent task not found" });
  }

  if (input.assigned_to) {
    await assertAssignableWorkerAgent(db, ownerId, input.assigned_to, 400, input.skipRuntimeAvailability);
  }
  await assertKnownLabels(db, board.id, input.labels);

  const seqResult = await queryDb<{ task_seq: number }>(db, "UPDATE boards SET task_seq = task_seq + 1 WHERE id = $1 RETURNING task_seq", [board.id]);
  const seq = seqResult.rows[0]?.task_seq ?? 1;

  const taskColumns = hasPhaseColumn
    ? "(id, board_id, seq, phase, status, title, description, repository_id, labels, created_by, assigned_to, result, pr_url, input, metadata, created_from, scheduled_at, position, created_at, updated_at)"
    : "(id, board_id, seq, status, title, description, repository_id, labels, created_by, assigned_to, result, pr_url, input, metadata, created_from, scheduled_at, position, created_at, updated_at)";
  const taskValues = hasPhaseColumn
    ? "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, NULL, NULL, $12, $13, $14, $15, $16, $17, $18)"
    : "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, NULL, NULL, $11, $12, $13, $14, $15, $16, $17)";
  const taskParams = hasPhaseColumn
    ? [
        taskId,
        board.id,
        seq,
        phase,
        initialStatus,
        input.title,
        input.description || null,
        input.repository_id || null,
        labelsJson,
        actorId,
        input.assigned_to || null,
        inputJson,
        metadataJson,
        input.created_from || null,
        input.scheduled_at || null,
        position,
        now,
        now,
      ]
    : [
        taskId,
        board.id,
        seq,
        initialStatus,
        input.title,
        input.description || null,
        input.repository_id || null,
        labelsJson,
        actorId,
        input.assigned_to || null,
        inputJson,
        metadataJson,
        input.created_from || null,
        input.scheduled_at || null,
        position,
        now,
        now,
      ];
  await queryDb(db, `INSERT INTO tasks ${taskColumns} VALUES ${taskValues}`, taskParams);

  await queryDb(
    db,
    "INSERT INTO task_actions (id, task_id, actor_type, actor_id, action, detail, session_id, created_at) VALUES ($1, $2, $3, $4, 'created', NULL, NULL, NOW())",
    [logId, taskId, actorType, actorId],
  );

  if (input.assigned_to) {
    await queryDb(
      db,
      "INSERT INTO task_actions (id, task_id, actor_type, actor_id, action, detail, session_id, created_at) VALUES ($1, $2, $3, $4, 'assigned', NULL, NULL, NOW())",
      [newLongId(), taskId, actorType, actorId],
    );
  }

  for (const depId of input.depends_on || []) {
    await queryDb(db, "INSERT INTO task_dependencies (task_id, depends_on) VALUES ($1, $2)", [taskId, depId]);
  }

  if (input.repository_id) await recordBoardRepository(db, board.id, input.repository_id);

  return {
    id: taskId,
    board_id: board.id,
    seq,
    phase,
    status: initialStatus,
    title: input.title,
    description: input.description || null,
    repository_id: input.repository_id || null,
    labels: input.labels || null,
    created_by: actorId,
    assigned_to: input.assigned_to || null,
    pr_url: null,
    input: input.input || null,
    metadata: input.metadata || {},
    origin: (input as any).origin || "manual",
    branch: (input as any).branch || null,
    position,
    scheduled_at: input.scheduled_at || null,
    created_at: now,
    updated_at: now,
  } as any;
}

export async function assertTaskOwner(db: D1, taskId: string, ownerId: string): Promise<void> {
  const res = await queryDb(db, "SELECT 1 FROM tasks t JOIN boards b ON t.board_id = b.id WHERE t.id = $1 AND b.owner_id = $2", [taskId, ownerId]);
  if (!res.rows[0]) throw new HTTPException(404, { message: "Task not found" });
}

export async function listTasks(
  db: D1,
  ownerId: string,
  filters: {
    repository_id?: string;
    status?: string;
    label?: string;
    board_id?: string;
    parent?: string;
    assigned_to?: string;
    runtime_source?: "ama" | "legacy";
    phase?: string;
  },
): Promise<Task[]> {
  let query = `
    SELECT t.*, r.name as repository_name, b.type as board_type FROM tasks t
    LEFT JOIN repositories r ON t.repository_id = r.id
    JOIN boards b ON t.board_id = b.id
    WHERE b.owner_id = $1
  `;
  const binds: unknown[] = [ownerId];
  let paramIdx = 2;

  if (filters.board_id) {
    query += ` AND t.board_id = $${paramIdx++}`;
    binds.push(filters.board_id);
  }
  if (filters.repository_id) {
    query += ` AND t.repository_id = $${paramIdx++}`;
    binds.push(filters.repository_id);
  }
  if (filters.status) {
    query += ` AND t.status = $${paramIdx++}`;
    binds.push(filters.status);
  }
  if (filters.label) {
    // t.labels is JSONB. Containment lets this use idx_tasks_labels_gin rather
    // than expanding every row's array. COALESCE covers tasks with no labels.
    query += ` AND COALESCE(t.labels, '[]'::jsonb) @> to_jsonb($${paramIdx++}::text)`;
    binds.push(filters.label);
  }
  if (filters.parent) {
    query += ` AND t.created_from = $${paramIdx++}`;
    binds.push(filters.parent);
  }
  if (filters.assigned_to) {
    query += ` AND t.assigned_to = $${paramIdx++}`;
    binds.push(filters.assigned_to);
  }
  if (filters.runtime_source) {
    query += ` AND (t.metadata::jsonb->'annotations'->>'runtime.source') = $${paramIdx++}`;
    binds.push(filters.runtime_source);
  }
  if (filters.phase) {
    query += ` AND t.phase = $${paramIdx++}`;
    binds.push(filters.phase);
  }

  query += " ORDER BY t.position";

  const res = await queryDb<Task>(db, query, binds);
  const tasks = res.rows.map(parseTask);

  const taskIds = tasks.map((t) => t.id);
  if (taskIds.length > 0) {
    const blockedSet = await computeBlocked(db, taskIds);
    const depsMap = new Map<string, string[]>();
    for (let i = 0; i < taskIds.length; i += 90) {
      const chunk = taskIds.slice(i, i + 90);
      const placeholders = chunk.map((_, idx) => `$${idx + 1}`).join(",");
      const depsResult = await queryDb<{ task_id: string; depends_on: string }>(
        db,
        `SELECT task_id, depends_on FROM task_dependencies WHERE task_id IN (${placeholders})`,
        chunk,
      );
      for (const row of depsResult.rows) {
        const arr = depsMap.get(row.task_id) || [];
        arr.push(row.depends_on);
        depsMap.set(row.task_id, arr);
      }
    }
    for (const task of tasks) {
      task.blocked = blockedSet.has(task.id);
      (task as any).depends_on = depsMap.get(task.id) || [];
    }
  }

  return tasks;
}

export async function getTask(db: D1, taskId: string, ownerId: string): Promise<TaskWithNotes | null> {
  const res = await queryDb<Task & { subtask_count: number; active_session_id: string | null }>(
    db,
    `
    SELECT t.*, a.name as agent_name, a.public_key as agent_public_key, a.fingerprint as agent_fingerprint,
      r.name as repository_name,
      (SELECT COUNT(*) FROM tasks sub WHERE sub.created_from = t.id) as subtask_count,
      (SELECT ta.session_id FROM task_actions ta WHERE ta.task_id = t.id AND ta.session_id IS NOT NULL ORDER BY ta.created_at DESC LIMIT 1) as active_session_id,
      (
        COALESCE((SELECT SUM(s.cost_micro_usd) FROM agent_sessions s WHERE s.id IN (SELECT session_id FROM task_actions ta WHERE ta.task_id = t.id)), 0) +
        COALESCE((SELECT SUM(s.cost_micro_usd) FROM ama_agent_sessions s WHERE s.id IN (SELECT session_id FROM task_actions ta WHERE ta.task_id = t.id)), 0)
      ) as cost_micro_usd
    FROM tasks t
    LEFT JOIN agents a ON t.assigned_to = a.id
    LEFT JOIN repositories r ON t.repository_id = r.id
    JOIN boards b ON t.board_id = b.id
    WHERE t.id = $1 AND b.owner_id = $2
  `,
    [taskId, ownerId],
  );
  const task = res.rows[0];
  if (!task) return null;
  parseTask(task);

  const [actions, deps, blockedSet] = await Promise.all([getTaskActions(db, taskId), getDependencies(db, taskId), computeBlocked(db, [taskId])]);

  const duration = computeDuration(actions, task.status);
  task.blocked = blockedSet.has(taskId);

  return {
    ...task,
    notes: actions,
    duration_minutes: duration,
    depends_on: deps,
    subtask_count: task.subtask_count,
    cost_micro_usd: (task as any).cost_micro_usd,
  };
}

export async function updateTask(
  db: D1,
  taskId: string,
  updates: Partial<
    Pick<
      Task,
      "title" | "description" | "repository_id" | "labels" | "pr_url" | "input" | "position" | "scheduled_at" | "phase" | "status" | "assigned_to"
    >
  > & {
    metadata?: Record<string, unknown>;
    depends_on?: string[];
  },
): Promise<Task | null> {
  const res = await queryDb<Task>(db, "SELECT * FROM tasks WHERE id = $1", [taskId]);
  const task = res.rows[0];
  if (!task) return null;

  if (updates.depends_on !== undefined) {
    if (updates.depends_on.length > 0) {
      const hasCycle = await detectCycle(db, taskId, updates.depends_on);
      if (hasCycle) throw new HTTPException(400, { message: "Circular dependency detected" });
    }
    await setDependencies(db, taskId, updates.depends_on);
  }
  if (updates.labels !== undefined) {
    await assertKnownLabels(db, task.board_id, updates.labels);
  }
  if (updates.repository_id) {
    await assertRepositoryBelongsToBoardOwner(db, task.board_id, updates.repository_id);
  }

  // Automatically check all DoD checkboxes if task is moved to in_review or done
  if (updates.status === "in_review" || updates.status === "done") {
    const currentDesc = updates.description !== undefined ? updates.description : task.description;
    if (currentDesc) {
      updates.description = currentDesc.replace(/^(\s*-\s*)\[ \]/gm, "$1[x]");
    }
  }

  const now = new Date().toISOString();
  const sets: string[] = ["updated_at = NOW()"];
  const binds: unknown[] = [];
  let paramIdx = 1;
  const hasPhaseColumn = await tasksTableHasPhase(db);

  const jsonFields = new Set(["labels", "input", "metadata"]);
  const allowedFields = [
    "title",
    "description",
    "repository_id",
    "labels",
    "pr_url",
    "input",
    "metadata",
    "position",
    "scheduled_at",
    ...(hasPhaseColumn ? ["phase"] : []),
    "status",
    "assigned_to",
  ];
  for (const field of allowedFields) {
    if (field in updates && (updates as any)[field] !== undefined) {
      sets.push(`${field} = $${paramIdx++}`);
      const val = (updates as any)[field];
      binds.push(jsonFields.has(field) && val != null ? JSON.stringify(val) : val);
    }
  }

  binds.push(taskId);
  await queryDb(db, `UPDATE tasks SET ${sets.join(", ")} WHERE id = $${paramIdx}`, binds);
  if (updates.repository_id) await recordBoardRepository(db, task.board_id, updates.repository_id);

  return parseTask({ ...task, ...updates, updated_at: now } as Task);
}

export async function deleteTask(db: D1, taskId: string): Promise<boolean> {
  const res = await queryDb<{ status: string; assigned_to: string | null }>(db, "SELECT status, assigned_to FROM tasks WHERE id = $1", [taskId]);
  const task = res.rows[0];
  if (!task) return false;

  const canDelete = task.status === "todo" || task.status === "cancelled";
  if (!canDelete) {
    throw new HTTPException(409, { message: `Cannot delete task in ${task.status}${task.assigned_to ? " (assigned)" : ""} status` });
  }

  const result = await queryDb(db, "DELETE FROM tasks WHERE id = $1", [taskId]);
  return (result.rowCount ?? 0) > 0;
}

export async function deleteTaskAfterFailedDispatch(db: D1, taskId: string): Promise<void> {
  await queryDb(db, "DELETE FROM tasks WHERE id = $1", [taskId]);
}

export async function claimTask(
  db: D1,
  taskId: string,
  agentId: string,
  identity: IdentityType,
  sessionId: string | null = null,
  expectedRuntimeSource?: "ama" | "legacy",
): Promise<Task | null> {
  const res = await queryDb<Task>(db, "SELECT * FROM tasks WHERE id = $1", [taskId]);
  const task = res.rows[0];
  if (!task) return null;
  if (task.assigned_to !== agentId) throw new HTTPException(409, { message: "Task is not assigned to this agent" });
  enforceTransition("claim" as any, task.status as TaskStatus, identity);

  const logId = newLongId();
  let sourceGuard = "";
  const currentMeta = task.metadata ? (typeof task.metadata === "string" ? JSON.parse(task.metadata) : task.metadata) : {};
  const nextMeta = {
    ...currentMeta,
    annotations: {
      ...(currentMeta.annotations || {}),
      "runtime.claimToken": logId,
    },
  };

  const params: unknown[] = [JSON.stringify(nextMeta), taskId, agentId];
  let paramIdx = 4;

  if (expectedRuntimeSource === "ama") {
    sourceGuard = ` AND (
      (metadata::jsonb->'annotations'->>'runtime.source') = $${paramIdx}
      OR (
        (metadata::jsonb->'annotations'->>'runtime.source') IS NULL
        AND (
          (metadata::jsonb->'annotations'->>'ama.sessionId') IS NOT NULL
          OR (metadata::jsonb->'annotations'->>'ama.dispatch.result') IS NOT NULL
        )
      )
    )`;
    params.push(expectedRuntimeSource);
    paramIdx++;
  } else if (expectedRuntimeSource === "legacy") {
    sourceGuard = ` AND (metadata::jsonb->'annotations'->>'runtime.source') = $${paramIdx}`;
    params.push(expectedRuntimeSource);
    paramIdx++;
  }

  const claimRes = await queryDb(
    db,
    `
    UPDATE tasks SET
      status = 'in_progress',
      updated_at = NOW(),
      metadata = $1
    WHERE id = $2 AND status IN ('todo', 'in_progress') AND assigned_to = $3 ${sourceGuard}
    `,
    params,
  );

  if ((claimRes.rowCount ?? 0) === 0) {
    throw new HTTPException(409, { message: "Task status, assignment, or runtime source changed before claim" });
  }

  await queryDb(
    db,
    `
    INSERT INTO task_actions (id, task_id, actor_type, actor_id, action, detail, session_id, created_at)
    SELECT $1, $2, $3, $4, 'claimed', NULL, $5, NOW()
    FROM tasks
    WHERE id = $6 AND (metadata::jsonb->'annotations'->>'runtime.claimToken') = $7
    `,
    [logId, taskId, identity, agentId, sessionId, taskId, logId],
  );

  const cleanMeta = { ...nextMeta };
  if (cleanMeta.annotations) {
    delete cleanMeta.annotations["runtime.claimToken"];
  }

  await queryDb(
    db,
    `
    UPDATE tasks SET metadata = $1
    WHERE id = $2 AND (metadata::jsonb->'annotations'->>'runtime.claimToken') = $3
    `,
    [JSON.stringify(cleanMeta), taskId, logId],
  );

  return parseTask({ ...task, status: "in_progress" as const, metadata: cleanMeta, updated_at: new Date().toISOString() });
}

export async function assignTask(
  db: D1,
  taskId: string,
  targetAgentId: string,
  actorType: string,
  actorId: string,
  sessionId: string | null = null,
  options: { skipRuntimeAvailability?: boolean; metadata?: Record<string, unknown>; assignmentToken?: string } = {},
): Promise<Task | null> {
  const res = await queryDb<Task & { board_owner_id: string }>(
    db,
    "SELECT t.*, b.owner_id as board_owner_id FROM tasks t JOIN boards b ON t.board_id = b.id WHERE t.id = $1",
    [taskId],
  );
  const task = res.rows[0];
  if (!task) return null;
  if (task.status !== "todo") throw new HTTPException(409, { message: "Can only assign tasks in todo status" });
  if (task.assigned_to) throw new HTTPException(409, { message: "Task is already assigned" });

  const { board_owner_id: ownerId, ...taskRow } = task;
  await assertAssignableWorkerAgent(db, ownerId, targetAgentId, 404, options.skipRuntimeAvailability);

  const logId = options.assignmentToken ?? newLongId();
  const currentMeta = (options.metadata ?? parseTask(taskRow).metadata) || {};
  const nextMeta = {
    ...currentMeta,
    annotations: {
      ...((currentMeta as any).annotations || {}),
      "runtime.assignmentToken": logId,
    },
  };

  const updateRes = await queryDb(
    db,
    `
    UPDATE tasks SET
      assigned_to = $1,
      metadata = $2,
      updated_at = NOW()
    WHERE id = $3 AND status = 'todo' AND assigned_to IS NULL
    `,
    [targetAgentId, JSON.stringify(nextMeta), taskId],
  );

  if ((updateRes.rowCount ?? 0) === 0) {
    throw new HTTPException(409, { message: "Task status or assignment changed before assignment" });
  }

  await queryDb(
    db,
    `
    INSERT INTO task_actions (id, task_id, actor_type, actor_id, action, detail, session_id, created_at)
    SELECT $1, $2, $3, $4, 'assigned', NULL, $5, NOW()
    FROM tasks
    WHERE id = $6 AND (metadata::jsonb->'annotations'->>'runtime.assignmentToken') = $7
    `,
    [logId, taskId, actorType, actorId, sessionId, taskId, logId],
  );

  if (!options.assignmentToken) {
    const cleanMeta = { ...nextMeta };
    if (cleanMeta.annotations) {
      delete cleanMeta.annotations["runtime.assignmentToken"];
    }
    await queryDb(
      db,
      `
      UPDATE tasks SET metadata = $1
      WHERE id = $2 AND (metadata::jsonb->'annotations'->>'runtime.assignmentToken') = $3
      `,
      [JSON.stringify(cleanMeta), taskId, logId],
    );
  }

  return parseTask({ ...taskRow, assigned_to: targetAgentId, metadata: nextMeta, updated_at: new Date().toISOString() } as Task);
}

export async function finalizeTaskAssignment(db: D1, taskId: string, assignmentToken: string): Promise<Task | null> {
  const res = await queryDb<Task>(db, "SELECT * FROM tasks WHERE id = $1", [taskId]);
  const task = res.rows[0];
  if (!task) return null;
  const currentMeta = task.metadata ? (typeof task.metadata === "string" ? JSON.parse(task.metadata) : task.metadata) : {};
  if (currentMeta.annotations) {
    delete currentMeta.annotations["runtime.assignmentToken"];
  }
  await queryDb(
    db,
    `
    UPDATE tasks SET metadata = $1
    WHERE id = $2 AND (metadata::jsonb->'annotations'->>'runtime.assignmentToken') = $3
    `,
    [JSON.stringify(currentMeta), taskId, assignmentToken],
  );
  const nextRes = await queryDb<Task>(db, "SELECT * FROM tasks WHERE id = $1", [taskId]);
  return nextRes.rows[0] ? parseTask(nextRes.rows[0]) : null;
}

export async function rollbackTaskAssignment(
  db: D1,
  taskId: string,
  targetAgentId: string,
  assignmentToken: string,
  metadata: Record<string, unknown> | null,
  updatedAt: string,
): Promise<boolean> {
  const updateRes = await queryDb(
    db,
    `
    UPDATE tasks SET assigned_to = NULL, updated_at = $1
    WHERE id = $2
      AND status = 'todo'
      AND assigned_to = $3
      AND (metadata::jsonb->'annotations'->>'runtime.assignmentToken') = $4
    `,
    [updatedAt, taskId, targetAgentId, assignmentToken],
  );

  if ((updateRes.rowCount ?? 0) > 0) {
    await queryDb(
      db,
      `
      DELETE FROM task_actions
      WHERE id = $1 AND task_id = $2
        AND EXISTS (
          SELECT 1 FROM tasks
          WHERE id = $2
            AND assigned_to IS NULL
            AND (metadata::jsonb->'annotations'->>'runtime.assignmentToken') = $1
        )
      `,
      [assignmentToken, taskId],
    );

    await queryDb(
      db,
      `
      UPDATE tasks SET metadata = $1
      WHERE id = $2
        AND assigned_to IS NULL
        AND (metadata::jsonb->'annotations'->>'runtime.assignmentToken') = $3
      `,
      [JSON.stringify(metadata ?? {}), taskId, assignmentToken],
    );
  }

  return (updateRes.rowCount ?? 0) > 0;
}

export async function completeTask(
  db: D1,
  taskId: string,
  actorType: string,
  actorId: string,
  identity: IdentityType,
  sessionId: string | null = null,
): Promise<Task | null> {
  const res = await queryDb<Task>(db, "SELECT * FROM tasks WHERE id = $1", [taskId]);
  const task = res.rows[0];
  if (!task) return null;
  enforceTransition("complete" as any, task.status as TaskStatus, identity);

  const now = new Date().toISOString();
  const logId = newLongId();

  let newDesc = task.description;
  if (newDesc) {
    newDesc = newDesc.replace(/^(\s*-\s*)\[ \]/gm, "$1[x]");
  }

  await queryDb(db, "UPDATE tasks SET status = 'done', description = $1, updated_at = NOW() WHERE id = $2", [newDesc, taskId]);
  await queryDb(
    db,
    "INSERT INTO task_actions (id, task_id, actor_type, actor_id, action, detail, session_id, created_at) VALUES ($1, $2, $3, $4, 'completed', $5, $6, NOW())",
    [logId, taskId, actorType, actorId, null, sessionId],
  );

  return parseTask({ ...task, status: "done" as const, description: newDesc, updated_at: now });
}

export async function cancelTask(
  db: D1,
  taskId: string,
  actorType: string,
  actorId: string,
  identity: IdentityType,
  sessionId: string | null = null,
): Promise<Task | null> {
  const res = await queryDb<Task>(db, "SELECT * FROM tasks WHERE id = $1", [taskId]);
  const task = res.rows[0];
  if (!task) return null;
  enforceTransition("cancel" as any, task.status as TaskStatus, identity);

  const now = new Date().toISOString();
  const logId = newLongId();

  await queryDb(db, "UPDATE tasks SET status = 'cancelled', assigned_to = NULL, updated_at = NOW() WHERE id = $1", [taskId]);
  await queryDb(
    db,
    "INSERT INTO task_actions (id, task_id, actor_type, actor_id, action, detail, session_id, created_at) VALUES ($1, $2, $3, $4, 'cancelled', NULL, $5, NOW())",
    [logId, taskId, actorType, actorId, sessionId],
  );

  return parseTask({ ...task, status: "cancelled" as const, assigned_to: null, updated_at: now });
}

export async function reviewTask(
  db: D1,
  taskId: string,
  actorType: string,
  actorId: string,
  prUrl: string | null,
  identity: IdentityType,
  sessionId: string | null = null,
): Promise<Task | null> {
  const res = await queryDb<Task>(db, "SELECT * FROM tasks WHERE id = $1", [taskId]);
  const task = res.rows[0];
  if (!task) return null;
  enforceTransition("review" as any, task.status as TaskStatus, identity);

  const now = new Date().toISOString();
  const logId = newLongId();

  await queryDb(db, "UPDATE tasks SET status = 'in_review', pr_url = COALESCE($1, pr_url), updated_at = NOW() WHERE id = $2", [prUrl, taskId]);
  await queryDb(
    db,
    "INSERT INTO task_actions (id, task_id, actor_type, actor_id, action, detail, session_id, created_at) VALUES ($1, $2, $3, $4, 'review_requested', NULL, $5, NOW())",
    [logId, taskId, actorType, actorId, sessionId],
  );

  return parseTask({ ...task, status: "in_review" as const, pr_url: prUrl || task.pr_url, updated_at: now });
}

export async function releaseTask(
  db: D1,
  taskId: string,
  actorType: string,
  actorId: string,
  identity: IdentityType,
  action: "released" | "timed_out" = "released",
  sessionId: string | null = null,
): Promise<Task | null> {
  const res = await queryDb<Task>(db, "SELECT * FROM tasks WHERE id = $1", [taskId]);
  const task = res.rows[0];
  if (!task) return null;
  enforceTransition("release" as any, task.status as TaskStatus, identity);

  const now = new Date().toISOString();
  const logId = newLongId();

  await queryDb(db, "UPDATE tasks SET status = 'todo', scheduled_at = NULL, updated_at = NOW() WHERE id = $1", [taskId]);
  await queryDb(
    db,
    "INSERT INTO task_actions (id, task_id, actor_type, actor_id, action, detail, session_id, created_at) VALUES ($1, $2, $3, $4, $5, NULL, $6, NOW())",
    [logId, taskId, actorType, actorId, action, sessionId],
  );

  return parseTask({ ...task, status: "todo" as const, updated_at: now });
}

export async function rejectTask(
  db: D1,
  taskId: string,
  actorType: string,
  actorId: string,
  identity: IdentityType,
  reason?: string,
  sessionId: string | null = null,
): Promise<Task | null> {
  const res = await queryDb<Task>(db, "SELECT * FROM tasks WHERE id = $1", [taskId]);
  const task = res.rows[0];
  if (!task) return null;
  enforceTransition("reject" as any, task.status as TaskStatus, identity);

  const now = new Date().toISOString();
  const logId = newLongId();

  await queryDb(db, "UPDATE tasks SET status = 'in_progress', updated_at = NOW() WHERE id = $1", [taskId]);
  await queryDb(
    db,
    "INSERT INTO task_actions (id, task_id, actor_type, actor_id, action, detail, session_id, created_at) VALUES ($1, $2, $3, $4, 'rejected', $5, $6, NOW())",
    [logId, taskId, actorType, actorId, reason || null, sessionId],
  );

  return parseTask({ ...task, status: "in_progress" as const, updated_at: now });
}

export async function addTaskAction(
  db: D1,
  taskId: string,
  actorType: string,
  actorId: string,
  action: string,
  detail: string | null,
  sessionId: string | null = null,
): Promise<TaskAction> {
  const actionId = newLongId();
  const now = new Date().toISOString();

  await queryDb(
    db,
    "INSERT INTO task_actions (id, task_id, actor_type, actor_id, action, detail, session_id, created_at) VALUES ($1, $2, $3, $4, $5, $6, $7, NOW())",
    [actionId, taskId, actorType, actorId, action, detail, sessionId],
  );

  return {
    id: actionId,
    task_id: taskId,
    actor_type: actorType as any,
    actor_id: actorId,
    actor_name: null,
    actor_public_key: null,
    action: action as any,
    detail,
    session_id: sessionId,
    created_at: now,
  };
}

export async function getTaskActions(db: D1, taskId: string, since?: string, limit: number = MAX_TASK_PARTITION_ROWS): Promise<TaskAction[]> {
  const base =
    "SELECT n.*, ag.name as actor_name, ag.public_key as actor_public_key FROM task_actions n LEFT JOIN agents ag ON n.actor_type LIKE 'agent:%' AND n.actor_id = ag.id WHERE n.task_id = $1";

  if (since) {
    const result = await queryDb<TaskAction>(db, `${base} AND n.created_at > $2 ORDER BY n.created_at ASC LIMIT $3`, [taskId, since, limit]);
    return result.rows;
  }
  const result = await queryDb<TaskAction>(db, `${base} ORDER BY n.created_at DESC LIMIT $2`, [taskId, limit]);
  return result.rows.reverse();
}

export async function getBoardActionsByBoardId(db: D1, boardId: string, since: string): Promise<BoardAction[]> {
  const result = await queryDb<BoardAction>(
    db,
    `
      SELECT n.*, ag.name as actor_name, ag.public_key as actor_public_key, ag.kind as agent_kind
      FROM task_actions n
      JOIN tasks t ON n.task_id = t.id
      LEFT JOIN agents ag ON n.actor_type LIKE 'agent:%' AND n.actor_id = ag.id
      WHERE t.board_id = $1 AND n.created_at > $2
      ORDER BY n.created_at ASC
      LIMIT 100
    `,
    [boardId, since],
  );
  return result.rows;
}

export async function getBoardActions(db: D1, boardId: string, ownerId: string, since: string): Promise<BoardAction[]> {
  const result = await queryDb<BoardAction>(
    db,
    `
      SELECT n.*, ag.name as actor_name, ag.public_key as actor_public_key, ag.kind as agent_kind
      FROM task_actions n
      JOIN tasks t ON n.task_id = t.id
      JOIN boards b ON t.board_id = b.id
      LEFT JOIN agents ag ON n.actor_type LIKE 'agent:%' AND n.actor_id = ag.id
      WHERE t.board_id = $1 AND b.owner_id = $2 AND n.created_at > $3
      ORDER BY n.created_at ASC
      LIMIT 100
    `,
    [boardId, ownerId, since],
  );
  return result.rows;
}

function computeDuration(actions: TaskAction[], status?: string): number | null {
  const claimed = actions.find((l) => l.action === "claimed");
  if (!claimed) return null;

  if (status === "todo" || status === "in_progress") {
    return null;
  }

  let end = actions.findLast
    ? actions.findLast((l) => l.action === "completed" || l.action === "cancelled")
    : [...actions].reverse().find((l) => l.action === "completed" || l.action === "cancelled");

  if (!end && status === "in_review") {
    end = actions.findLast
      ? actions.findLast((l) => l.action === "review_requested")
      : [...actions].reverse().find((l) => l.action === "review_requested");
  }

  if (!end) return null;
  return Math.round((new Date(end.created_at).getTime() - new Date(claimed.created_at).getTime()) / 60000);
}