import type { Agent, AgentStatus, AgentWithActivity, CreateAgentInput } from "@vtit-agent-coding/shared";
import { type AgentRuntime, type AnyAgentRuntime, BUILTIN_TEMPLATES, hasNoScheduleTaint } from "@vtit-agent-coding/shared";
import { asJson, type D1, parseJsonFields, queryDb } from "./db";
import { addSubkey, getOrCreateRootKey } from "./gpgKeyRepo";
import { getCachedAvailableRuntimesByOwner, isRuntimeAvailable, listAvailableRuntimesByOwner } from "./machineRepo";

const parseAgent = <T extends Agent>(row: T) => {
  const parsed = parseJsonFields(row, ["skills", "subagents", "taints", "handoff_to", "metadata"]) as T;
  if ("builtin" in parsed) {
    const b = (parsed as any).builtin;
    (parsed as any).builtin = b === true || b === 1 || b === "true" || b === "1" ? 1 : 0;
  }
  return parsed;
};

export type AgentListFilters = {
  kind?: "worker" | "leader";
  role?: string;
  runtime?: AnyAgentRuntime;
  available?: boolean;
};

async function shortHash(value: string): Promise<string> {
  const bytes = new TextEncoder().encode(value);
  const hash = await crypto.subtle.digest("SHA-1", bytes);
  return [...new Uint8Array(hash)]
    .map((b) => b.toString(16).padStart(2, "0"))
    .join("")
    .slice(0, 10);
}

type AgentProfile = Pick<Agent, "name" | "bio" | "soul" | "role" | "kind" | "handoff_to" | "runtime" | "model" | "skills" | "subagents" | "taints">;
type AgentActivityRow = Agent & {
  runtime_ready: number | boolean;
  todo_task_count: number;
  in_progress_task_count: number;
  in_review_task_count: number;
  done_task_count: number;
  cancelled_task_count: number;
  input_tokens: number;
  output_tokens: number;
  cache_read_tokens: number;
  cache_creation_tokens: number;
  cost_micro_usd: number;
};

type AgentBaseRow = Agent & {
  runtime_ready: number | boolean;
};

type AgentTaskCounts = Pick<
  AgentActivityRow,
  "todo_task_count" | "in_progress_task_count" | "in_review_task_count" | "done_task_count" | "cancelled_task_count"
>;

type AgentUsageTotals = Pick<AgentActivityRow, "input_tokens" | "output_tokens" | "cache_read_tokens" | "cache_creation_tokens" | "cost_micro_usd">;

function buildAgentStatus(agent: AgentActivityRow, runtimeAvailable: boolean): AgentStatus {
  return {
    schedulable: agent.kind === "worker" && !hasNoScheduleTaint(agent.taints) && runtimeAvailable,
    tasks: {
      todo: Number(agent.todo_task_count ?? 0),
      in_progress: Number(agent.in_progress_task_count ?? 0),
      in_review: Number(agent.in_review_task_count ?? 0),
      done: Number(agent.done_task_count ?? 0),
      cancelled: Number(agent.cancelled_task_count ?? 0),
    },
  };
}

export function withAgentStatus(agent: AgentWithActivity, runtimeAvailable: boolean): AgentWithActivity {
  return {
    ...agent,
    status: buildAgentStatus(
      {
        ...agent,
        runtime_ready: runtimeAvailable,
        todo_task_count: agent.status.tasks.todo,
        in_progress_task_count: agent.status.tasks.in_progress,
        in_review_task_count: agent.status.tasks.in_review,
        done_task_count: agent.status.tasks.done,
        cancelled_task_count: agent.status.tasks.cancelled,
      },
      runtimeAvailable,
    ),
  };
}

function parseAgentActivity(row: AgentActivityRow): AgentWithActivity {
  const parsed = parseAgent(row) as AgentActivityRow;
  const runtimeAvailable = !!parsed.runtime_ready;
  const {
    runtime_ready: _runtimeReady,
    todo_task_count: _todoTaskCount,
    in_progress_task_count: _inProgressTaskCount,
    in_review_task_count: _inReviewTaskCount,
    done_task_count: _doneTaskCount,
    cancelled_task_count: _cancelledTaskCount,
    ...agent
  } = parsed;
  return {
    ...agent,
    email: `${parsed.username}@mails.vtit-agent-coding.dev`,
    status: buildAgentStatus(parsed, runtimeAvailable),
  };
}

function profileJson(agent: AgentProfile): string {
  return JSON.stringify({
    name: agent.name,
    bio: agent.bio,
    soul: agent.soul,
    role: agent.role,
    kind: agent.kind,
    handoff_to: agent.handoff_to ?? [],
    runtime: agent.runtime,
    model: agent.model,
    skills: agent.skills ?? [],
    subagents: agent.subagents ?? [],
    taints: agent.taints ?? [],
  });
}

async function profileVersion(
  agent: Pick<Agent, "name" | "bio" | "soul" | "role" | "kind" | "handoff_to" | "runtime" | "model" | "skills" | "subagents" | "taints">,
): Promise<string> {
  return shortHash(profileJson(agent));
}

export interface PreparedAgent extends Agent {
  privateKeyJwk: JsonWebKey;
}

export interface AgentIdentity {
  id: string;
  publicKeyBase64: string;
  fingerprint: string;
  privateKeyJwk: JsonWebKey;
}

export async function prepareAgent(
  db: D1,
  ownerId: string,
  input: CreateAgentInput,
  identity: AgentIdentity,
  builtin = false,
  amaAgentId: string | null = null,
): Promise<PreparedAgent> {
  const { id, publicKeyBase64, fingerprint, privateKeyJwk } = identity;
  const now = new Date().toISOString();
  const soul = input.soul ?? null;
  return {
    id,
    owner_id: ownerId,
    name: input.name || input.username,
    username: input.username,
    gpg_subkey_id: null,
    bio: input.bio ?? null,
    soul,
    role: input.role ?? null,
    kind: input.kind ?? "worker",
    handoff_to: input.handoff_to ?? null,
    runtime: input.runtime,
    model: input.model ?? null,
    skills: input.skills ?? null,
    subagents: input.subagents ?? null,
    taints: input.taints ?? null,
    version: "latest",
    public_key: publicKeyBase64,
    fingerprint,
    builtin: builtin ? 1 : 0,
    ama_agent_id: amaAgentId,
    metadata: {},
    created_at: now,
    updated_at: now,
    privateKeyJwk,
  };
}

export async function insertAgent(db: D1, agent: PreparedAgent, extras?: { mailboxToken?: string; gpgSubkeyId?: string }): Promise<Agent> {
  const skillsJson = agent.skills ? JSON.stringify(agent.skills) : null;
  const subagentsJson = agent.subagents ? JSON.stringify(agent.subagents) : null;
  const taintsJson = agent.taints ? JSON.stringify(agent.taints) : null;
  const handoffJson = agent.handoff_to ? JSON.stringify(agent.handoff_to) : null;
  const metadataJson = JSON.stringify(agent.metadata ?? {});

  try {
    await queryDb(
      db,
      `
      INSERT INTO agents (id, owner_id, name, username, bio, soul, role, kind, handoff_to, runtime, model, skills, subagents, taints, version, public_key, private_key, fingerprint, builtin, mailbox_token, gpg_subkey_id, ama_agent_id, metadata, created_at, updated_at)
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, NOW(), NOW())
      `,
      [
        agent.id,
        agent.owner_id,
        agent.name,
        agent.username,
        agent.bio,
        agent.soul,
        agent.role,
        agent.kind,
        handoffJson,
        agent.runtime,
        agent.model,
        skillsJson,
        subagentsJson,
        taintsJson,
        agent.version,
        agent.public_key,
        JSON.stringify(agent.privateKeyJwk),
        agent.fingerprint,
        agent.builtin ? 1 : 0,
        extras?.mailboxToken ?? null,
        extras?.gpgSubkeyId ?? null,
        agent.ama_agent_id ?? null,
        metadataJson,
      ],
    );
  } catch (err: any) {
    if (err?.message?.includes("24 columns")) {
      await queryDb(
        db,
        `
        INSERT INTO agents (id, owner_id, name, username, bio, soul, role, kind, handoff_to, runtime, model, skills, subagents, taints, version, public_key, private_key, fingerprint, builtin, mailbox_token, ama_agent_id, metadata, created_at, updated_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, NOW(), NOW())
        `,
        [
          agent.id,
          agent.owner_id,
          agent.name,
          agent.username,
          agent.bio,
          agent.soul,
          agent.role,
          agent.kind,
          handoffJson,
          agent.runtime,
          agent.model,
          skillsJson,
          subagentsJson,
          taintsJson,
          agent.version,
          agent.public_key,
          JSON.stringify(agent.privateKeyJwk),
          agent.fingerprint,
          agent.builtin ? 1 : 0,
          extras?.mailboxToken ?? null,
          agent.ama_agent_id ?? null,
          metadataJson,
        ],
      );
    } else {
      throw err;
    }
  }

  const { privateKeyJwk: _, ...result } = agent;
  if (extras?.gpgSubkeyId) result.gpg_subkey_id = extras.gpgSubkeyId;
  return result;
}

export async function createAgentIdentity(db: D1, ownerId: string, agentEmail: string): Promise<AgentIdentity> {
  await getOrCreateRootKey(db, ownerId);
  const subkey = await addSubkey(db, ownerId, agentEmail);
  if (!subkey) throw new Error("addSubkey returned null after getOrCreateRootKey — should not happen");
  const { x, d } = subkey.privateKeyJwk;
  if (!x || !d) throw new Error("GPG subkey produced invalid JWK — missing x or d field");
  return {
    id: subkey.keyId,
    publicKeyBase64: x,
    fingerprint: subkey.fingerprint,
    privateKeyJwk: subkey.privateKeyJwk,
  };
}

export async function createAgent(db: D1, ownerId: string, input: CreateAgentInput, identity: AgentIdentity, builtin = false): Promise<Agent> {
  const prepared = await prepareAgent(db, ownerId, input, identity, builtin);
  return upsertLatestAgent(db, prepared);
}

export async function seedBuiltinAgents(db: D1, ownerId: string): Promise<void> {
  const res = await queryDb<{ role: string }>(db, "SELECT role FROM agents WHERE owner_id = $1 AND (builtin = 1)", [ownerId]);
  const existingRoles = new Set(res.rows.map((a) => a.role));

  const hash = Array.from(new TextEncoder().encode(ownerId)).reduce((h, b) => ((h << 5) - h + b) >>> 0, 0);
  const ownerSuffix = hash.toString(36).slice(0, 6);
  for (const tpl of BUILTIN_TEMPLATES) {
    if (tpl.role && existingRoles.has(tpl.role)) continue;
    const username = `${tpl.username ?? tpl.role!}-${ownerSuffix}`;
    const input = { ...tpl, username, runtime: tpl.runtime as AgentRuntime } as CreateAgentInput;
    const identity = await createAgentIdentity(db, ownerId, `${username}@mails.vtit-agent-coding.dev`);
    await createAgent(db, ownerId, input, identity, true);
  }
}

export async function listAgents(db: D1, ownerId: string, filters: AgentListFilters = {}): Promise<AgentWithActivity[]> {
  let query = `
    SELECT a.id, a.owner_id, a.name, a.username, a.gpg_subkey_id, a.bio, a.soul, a.role, a.kind, a.handoff_to, a.runtime, a.model, a.skills, a.subagents, a.taints,
      a.version,
      a.public_key, a.fingerprint, a.builtin, a.ama_agent_id, a.metadata, a.created_at, a.updated_at,
      0 as runtime_ready,
      COALESCE(tc.todo_task_count, 0) as todo_task_count,
      COALESCE(tc.in_progress_task_count, 0) as in_progress_task_count,
      COALESCE(tc.in_review_task_count, 0) as in_review_task_count,
      COALESCE(tc.done_task_count, 0) as done_task_count,
      COALESCE(tc.cancelled_task_count, 0) as cancelled_task_count,
      COALESCE(su.input_tokens, 0) as input_tokens,
      COALESCE(su.output_tokens, 0) as output_tokens,
      COALESCE(su.cache_read_tokens, 0) as cache_read_tokens,
      COALESCE(su.cache_creation_tokens, 0) as cache_creation_tokens,
      COALESCE(su.cost_micro_usd, 0) as cost_micro_usd
    FROM agents a
    LEFT JOIN (
      SELECT assigned_to,
        SUM(CASE WHEN status = 'todo' THEN 1 ELSE 0 END) as todo_task_count,
        SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) as in_progress_task_count,
        SUM(CASE WHEN status = 'in_review' THEN 1 ELSE 0 END) as in_review_task_count,
        SUM(CASE WHEN status = 'done' THEN 1 ELSE 0 END) as done_task_count,
        SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_task_count
      FROM tasks
      WHERE assigned_to IS NOT NULL
      GROUP BY assigned_to
    ) tc ON tc.assigned_to = a.id
    LEFT JOIN (
      SELECT
        agent_id,
        SUM(input_tokens) AS input_tokens,
        SUM(output_tokens) AS output_tokens,
        SUM(cache_read_tokens) AS cache_read_tokens,
        SUM(cache_creation_tokens) AS cache_creation_tokens,
        SUM(cost_micro_usd) AS cost_micro_usd
      FROM (
        SELECT agent_id, input_tokens, output_tokens, cache_read_tokens, cache_creation_tokens, cost_micro_usd
        FROM agent_sessions
        UNION ALL
        SELECT agent_id, input_tokens, output_tokens, cache_read_tokens, cache_creation_tokens, cost_micro_usd
        FROM ama_agent_sessions
      ) sub
      GROUP BY agent_id
    ) su ON su.agent_id = a.id
    WHERE a.owner_id = $1 AND COALESCE(a.version, 'latest') = 'latest'
  `;
  const binds: unknown[] = [ownerId];
  let paramIdx = 2;

  if (filters.kind) {
    query += ` AND a.kind = $${paramIdx++}`;
    binds.push(filters.kind);
  }
  if (filters.role) {
    query += ` AND a.role = $${paramIdx++}`;
    binds.push(filters.role);
  }
  if (filters.runtime) {
    query += ` AND a.runtime = $${paramIdx++}`;
    binds.push(filters.runtime);
  }
  query += " ORDER BY a.created_at DESC";
  const res = await queryDb<AgentActivityRow>(db, query, binds);
  const availableRuntimes = getCachedAvailableRuntimesByOwner(ownerId) ?? (await listAvailableRuntimesByOwner(db, ownerId));
  const agents = await Promise.all(
    res.rows.map(async (row) => {
      const runtimeAvailable = availableRuntimes.has(row.runtime);
      return parseAgentActivity({ ...row, runtime_ready: runtimeAvailable ? 1 : 0 });
    }),
  );
  if (filters.available === undefined) return agents;
  return agents.filter((agent) => agent.status.schedulable === filters.available);
}

export async function getAgent(db: D1, agentId: string, ownerId: string): Promise<AgentWithActivity | null> {
  const agentRes = await queryDb<AgentBaseRow>(
    db,
    `
    SELECT a.id, a.owner_id, a.name, a.username, a.gpg_subkey_id, a.bio, a.soul, a.role, a.kind, a.handoff_to, a.runtime, a.model, a.skills, a.subagents, a.taints,
      a.version,
      a.public_key, a.fingerprint, a.builtin, a.ama_agent_id, a.metadata, a.created_at, a.updated_at
    FROM agents a
    WHERE a.id = $1 AND a.owner_id = $2
    `,
    [agentId, ownerId],
  );
  const agent = agentRes.rows[0];
  if (!agent) return null;

  const taskCountRes = await queryDb<AgentTaskCounts>(
    db,
    `
    SELECT
      SUM(CASE WHEN status = 'todo' THEN 1 ELSE 0 END) as todo_task_count,
      SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) as in_progress_task_count,
      SUM(CASE WHEN status = 'in_review' THEN 1 ELSE 0 END) as in_review_task_count,
      SUM(CASE WHEN status = 'done' THEN 1 ELSE 0 END) as done_task_count,
      SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_task_count
    FROM tasks
    WHERE assigned_to = $1
    `,
    [agentId],
  );
  const taskCounts = taskCountRes.rows[0] || {
    todo_task_count: 0,
    in_progress_task_count: 0,
    in_review_task_count: 0,
    done_task_count: 0,
    cancelled_task_count: 0,
  };

  const usageRes = await queryDb<AgentUsageTotals>(
    db,
    `
    SELECT
      COALESCE(SUM(input_tokens), 0) AS input_tokens,
      COALESCE(SUM(output_tokens), 0) AS output_tokens,
      COALESCE(SUM(cache_read_tokens), 0) AS cache_read_tokens,
      COALESCE(SUM(cache_creation_tokens), 0) AS cache_creation_tokens,
      COALESCE(SUM(cost_micro_usd), 0) AS cost_micro_usd
    FROM (
      SELECT input_tokens, output_tokens, cache_read_tokens, cache_creation_tokens, cost_micro_usd
      FROM agent_sessions
      WHERE agent_id = $1
      UNION ALL
      SELECT input_tokens, output_tokens, cache_read_tokens, cache_creation_tokens, cost_micro_usd
      FROM ama_agent_sessions
      WHERE agent_id = $1
    ) sub
    `,
    [agentId],
  );
  const usage = usageRes.rows[0] || {
    input_tokens: 0,
    output_tokens: 0,
    cache_read_tokens: 0,
    cache_creation_tokens: 0,
    cost_micro_usd: 0,
  };

  const runtimeAvailable = await isRuntimeAvailable(db, ownerId, agent.runtime);
  return parseAgentActivity({ ...agent, runtime_ready: runtimeAvailable ? 1 : 0, ...taskCounts, ...usage });
}

export async function updateAgent(
  db: D1,
  agentId: string,
  updates: Partial<Pick<Agent, "name" | "bio" | "soul" | "role" | "handoff_to" | "runtime" | "model" | "skills" | "subagents" | "taints">>,
): Promise<Agent | null> {
  const res = await queryDb<Agent & { private_key: string; mailbox_token: string | null }>(
    db,
    "SELECT id, owner_id, name, username, gpg_subkey_id, bio, soul, role, kind, handoff_to, runtime, model, skills, subagents, taints, version, public_key, private_key, fingerprint, builtin, mailbox_token, metadata, created_at, updated_at FROM agents WHERE id = $1",
    [agentId],
  );
  const agent = res.rows[0];
  if (!agent) return null;
  if (agent.version !== "latest") return null;

  const now = new Date().toISOString();
  const sets: string[] = ["updated_at = NOW()"];
  const binds: unknown[] = [];
  let paramIdx = 1;
  const applied: Partial<Agent> = {};

  const jsonFields = new Set(["skills", "subagents", "taints", "handoff_to"]);
  const fields = ["name", "bio", "soul", "role", "handoff_to", "runtime", "model", "skills", "subagents", "taints"] as const;
  for (const field of fields) {
    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);
      (applied as any)[field] = val;
    }
  }
  const updatedProfile = { ...parseAgent(agent), ...applied } as AgentSnapshot;
  if (profileJson(parseAgent(agent) as AgentSnapshot) === profileJson(updatedProfile)) {
    return getAgent(db, agentId, agent.owner_id);
  }

  await insertAgentSnapshot(db, parseAgent(agent) as AgentSnapshot, await profileVersion(parseAgent(agent) as AgentSnapshot), now);
  binds.push(agentId);
  await queryDb(db, `UPDATE agents SET ${sets.join(", ")} WHERE id = $${paramIdx}`, binds);
  return getAgent(db, agentId, agent.owner_id);
}

type AgentSnapshot = Agent & { private_key: string; mailbox_token: string | null };

function jsonOrNull(value: unknown | null): string | null {
  return value ? JSON.stringify(value) : null;
}

async function getLatestAgentSnapshot(db: D1, username: string, ownerId: string): Promise<AgentSnapshot | null> {
  const res = await queryDb<Agent & { private_key: string; mailbox_token: string | null }>(
    db,
    "SELECT id, owner_id, name, username, gpg_subkey_id, bio, soul, role, kind, handoff_to, runtime, model, skills, subagents, taints, version, public_key, private_key, fingerprint, builtin, mailbox_token, metadata, created_at, updated_at FROM agents WHERE username = $1 AND owner_id = $2 AND version = 'latest'",
    [username, ownerId],
  );
  return res.rows[0] ? (parseAgent(res.rows[0]) as AgentSnapshot) : null;
}

async function insertAgentSnapshot(db: D1, source: AgentSnapshot, version: string, now: string): Promise<string> {
  const res = await queryDb<AgentProfile & { id: string }>(
    db,
    "SELECT id, name, bio, soul, role, kind, handoff_to, runtime, model, skills, subagents, taints FROM agents WHERE username = $1 AND version = $2",
    [source.username, version],
  );
  const existing = res.rows[0];
  if (existing) {
    if (profileJson(parseAgent(existing as Agent)) !== profileJson(source)) {
      throw new Error(`Agent snapshot hash collision: ${source.username}@${version}`);
    }
    return existing.id;
  }

  const snapshotId = crypto.randomUUID();
  try {
    await queryDb(
      db,
      `
      INSERT INTO agents (id, owner_id, name, username, gpg_subkey_id, bio, soul, role, kind, handoff_to, runtime, model, skills, subagents, taints, version, public_key, private_key, fingerprint, builtin, mailbox_token, metadata, created_at, updated_at)
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, NOW(), NOW())
      `,
      [
        snapshotId,
        source.owner_id,
        source.name,
        source.username,
        source.gpg_subkey_id,
        source.bio,
        source.soul,
        source.role,
        source.kind,
        jsonOrNull(source.handoff_to),
        source.runtime,
        source.model,
        jsonOrNull(source.skills),
        jsonOrNull(source.subagents),
        jsonOrNull(source.taints),
        version,
        source.public_key,
        source.private_key,
        source.fingerprint,
        source.builtin ? 1 : 0,
        source.mailbox_token,
        JSON.stringify(source.metadata ?? {}),
      ],
    );
  } catch (err: any) {
    if (err?.message?.includes("23 columns") || err?.message?.includes("24 columns")) {
      await queryDb(
        db,
        `
        INSERT INTO agents (id, owner_id, name, username, bio, soul, role, kind, handoff_to, runtime, model, skills, subagents, taints, version, public_key, private_key, fingerprint, builtin, mailbox_token, metadata, created_at, updated_at)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, NOW(), NOW())
        `,
        [
          snapshotId,
          source.owner_id,
          source.name,
          source.username,
          source.bio,
          source.soul,
          source.role,
          source.kind,
          jsonOrNull(source.handoff_to),
          source.runtime,
          source.model,
          jsonOrNull(source.skills),
          jsonOrNull(source.subagents),
          jsonOrNull(source.taints),
          version,
          source.public_key,
          source.private_key,
          source.fingerprint,
          source.builtin ? 1 : 0,
          source.mailbox_token,
          JSON.stringify(source.metadata ?? {}),
        ],
      );
    } else {
      throw err;
    }
  }
  return snapshotId;
}

async function updateLatestFromPrepared(
  db: D1,
  latest: AgentSnapshot,
  agent: PreparedAgent,
  extras: { mailboxToken?: string; gpgSubkeyId?: string } | undefined,
  now: string,
): Promise<void> {
  await queryDb(
    db,
    `
    UPDATE agents
    SET name = $1, gpg_subkey_id = $2, bio = $3, soul = $4, role = $5, kind = $6, handoff_to = $7, runtime = $8, model = $9,
        skills = $10, subagents = $11, taints = $12, public_key = $13, private_key = $14, fingerprint = $15, builtin = $16, mailbox_token = $17, metadata = $18, updated_at = NOW()
    WHERE id = $19
    `,
    [
      agent.name,
      extras?.gpgSubkeyId ?? latest.gpg_subkey_id,
      agent.bio,
      agent.soul,
      agent.role,
      agent.kind,
      jsonOrNull(agent.handoff_to),
      agent.runtime,
      agent.model,
      jsonOrNull(agent.skills),
      jsonOrNull(agent.subagents),
      jsonOrNull(agent.taints),
      latest.public_key,
      latest.private_key,
      latest.fingerprint,
      Boolean(agent.builtin),
      extras?.mailboxToken ?? latest.mailbox_token,
      JSON.stringify(latest.metadata ?? {}),
      latest.id,
    ],
  );
}

export async function upsertLatestAgent(db: D1, agent: PreparedAgent, extras?: { mailboxToken?: string; gpgSubkeyId?: string }): Promise<Agent> {
  const latest = await getLatestAgentSnapshot(db, agent.username, agent.owner_id);
  if (!latest) return insertAgent(db, agent, extras);

  const now = new Date().toISOString();
  if (profileJson(latest) === profileJson(agent)) {
    const current = await getAgent(db, latest.id, agent.owner_id);
    if (!current) throw new Error("Latest agent missing during update");
    return current;
  }

  await insertAgentSnapshot(db, latest, await profileVersion(latest), now);
  await updateLatestFromPrepared(db, latest, agent, extras, now);
  const updated = await getAgent(db, latest.id, agent.owner_id);
  if (!updated) throw new Error("Latest agent missing after update");
  return updated;
}

export async function deleteAgent(db: D1, agentId: string): Promise<boolean> {
  const res = await queryDb<Pick<Agent, "owner_id" | "username" | "version">>(db, "SELECT owner_id, username, version FROM agents WHERE id = $1", [
    agentId,
  ]);
  const agent = res.rows[0];
  if (!agent || agent.version !== "latest") return false;

  await queryDb(
    db,
    "UPDATE tasks SET assigned_to = NULL WHERE assigned_to IN (SELECT id FROM agents WHERE owner_id = $1 AND username = $2) AND status IN ('todo', 'in_progress')",
    [agent.owner_id, agent.username],
  );
  const delRes = await queryDb(db, "DELETE FROM agents WHERE owner_id = $1 AND username = $2", [agent.owner_id, agent.username]);
  return (delRes.rowCount ?? 0) > 0;
}

export async function getAgentLogs(db: D1, agentId: string): Promise<any[]> {
  const res = await queryDb(
    db,
    "SELECT tl.*, t.title as task_title FROM task_actions tl JOIN tasks t ON tl.task_id = t.id WHERE tl.actor_id = $1 ORDER BY tl.created_at DESC LIMIT 100",
    [agentId],
  );
  return res.rows;
}

export async function getAgentPrivateKey(db: D1, agentId: string): Promise<JsonWebKey | null> {
  const res = await queryDb<{ private_key: string }>(db, "SELECT private_key FROM agents WHERE id = $1", [agentId]);
  return res.rows[0] ? JSON.parse(res.rows[0].private_key) : null;
}

export async function updateAgentMetadataAnnotations(db: D1, ownerId: string, agentId: string, annotations: Record<string, unknown>): Promise<void> {
  const res = await queryDb<{ metadata: string }>(db, "SELECT metadata FROM agents WHERE id = $1 AND owner_id = $2", [agentId, ownerId]);
  const row = res.rows[0];
  if (!row) throw new Error("Agent not found");
  const metadata = asJson<Record<string, unknown>>(row.metadata, {});
  const existing =
    metadata.annotations && typeof metadata.annotations === "object" && !Array.isArray(metadata.annotations) ? metadata.annotations : {};
  metadata.annotations = { ...(existing as Record<string, unknown>), ...annotations };
  await queryDb(db, "UPDATE agents SET metadata = $1, updated_at = NOW() WHERE id = $2 AND owner_id = $3", [
    JSON.stringify(metadata),
    agentId,
    ownerId,
  ]);
}

export async function setAgentGpgSubkeyId(db: D1, agentId: string, gpgSubkeyId: string): Promise<void> {
  await queryDb(db, "UPDATE agents SET gpg_subkey_id = $1 WHERE id = $2", [gpgSubkeyId, agentId]);
}

export async function setAgentAmaId(db: D1, ownerId: string, agentId: string, amaAgentId: string): Promise<void> {
  const res = await queryDb<{ username: string }>(db, "SELECT username FROM agents WHERE id = $1 AND owner_id = $2", [agentId, ownerId]);
  const row = res.rows[0];
  if (!row) throw new Error("Agent not found");
  await queryDb(db, "UPDATE agents SET ama_agent_id = $1 WHERE owner_id = $2 AND username = $3", [amaAgentId, ownerId, row.username]);
}

export async function getAgentAmaId(db: D1, agentId: string): Promise<string | null> {
  const res = await queryDb<{ ama_agent_id: string | null }>(db, "SELECT ama_agent_id FROM agents WHERE id = $1", [agentId]);
  return res.rows[0]?.ama_agent_id ?? null;
}

export async function listAgentsMissingAmaAgent(db: D1, ownerId: string): Promise<{ id: string; username: string; runtime: string }[]> {
  const res = await queryDb<{ id: string; username: string; runtime: string }>(
    db,
    "SELECT id, username, runtime FROM agents WHERE owner_id = $1 AND version = 'latest' AND builtin = false AND kind = 'worker' AND ama_agent_id IS NULL",
    [ownerId],
  );
  return res.rows;
}

export async function getAgentMailboxToken(db: D1, agentId: string): Promise<string | null> {
  const res = await queryDb<{ mailbox_token: string | null }>(db, "SELECT mailbox_token FROM agents WHERE id = $1", [agentId]);
  return res.rows[0]?.mailbox_token ?? null;
}