import { and, asc, desc, eq, inArray, isNull, or, sql } from "drizzle-orm";
import type { Transaction } from "@platform-modules/db";
import type {
  AgentRunRepository,
  ApplicationTransaction,
  AttemptRepository,
  AuditRepository,
  BusinessEventRepository,
  ChangeSetRepository,
  CredentialAuthorityRepository,
  FactoryRunRepository,
  GoalRepository,
  OutboxMessage,
  OutboxRepository,
  PlanRepository,
  PlanRevisionRepository,
  ProjectRepository,
  ProjectVisionRepository,
  ReviewRepository,
  ReviewFindingRepository,
  VerificationEvidenceRepository,
  TaskRepository,
  UnitOfWork,
  WorkspaceRepository,
} from "@awp/application";
import type {
  AgentRunId,
  AttemptId,
  AuditRecord,
  BusinessEvent,
  ChangeSetId,
  ConnectionId,
  CredentialReferenceId,
  FactoryRunId,
  GoalId,
  PlanId,
  PlanRevisionId,
  ProjectId,
  ProjectVisionVersionId,
  ProviderReference,
  PrincipalId,
  ReviewId,
  ReviewFindingId,
  VerificationEvidenceId,
  TaskId,
  WorkspaceId,
} from "@awp/contracts";
import { unsafeOpaqueId } from "@awp/contracts";
import type {
  AgentRun,
  AgentRunRole,
  AgentRunStatus,
  Attempt,
  AttemptStatus,
  ChangeSet,
  ChangeSetStatus,
  CredentialAuthority,
  CredentialAuthorityStatus,
  FactoryRun,
  FactoryRunStatus,
  Goal,
  GoalStatus,
  Plan,
  PlanRevision,
  PlanStatus,
  Project,
  ProjectStatus,
  ProjectVisionVersion,
  Review,
  ReviewDisposition,
  ReviewFinding,
  ReviewFindingSeverity,
  ReviewStatus,
  VerificationEvidence,
  VerificationEvidenceState,
  Task,
  TaskStatus,
  Workspace,
} from "@awp/domain";
import {
  CredentialAuthorityConflictError,
  ImmutableAttemptProvenanceError,
  assertAttemptProvenanceImmutable,
  assertCandidateIdentityImmutable,
  createCandidateManifest,
} from "@awp/domain";
import {
  agentRuns,
  attempts,
  auditRecords,
  businessEvents,
  changeSets,
  credentialAuthorities,
  factoryRuns,
  goals,
  outboxMessages,
  planRevisions,
  plans,
  projectVisionVersions,
  projects,
  reviews,
  reviewFindings,
  verificationEvidence,
  tasks,
  taskDependencies,
  workspaces,
} from "./schema.js";
import type { schema } from "./schema.js";

type Tx = Transaction<typeof schema>;
interface Database {
  transaction<T>(work: (tx: Tx) => Promise<T>): Promise<T>;
}

class PgProjectRepository implements ProjectRepository {
  constructor(private readonly tx: Tx) {}
  async getById(id: ProjectId): Promise<Project | undefined> {
    const row = (await this.tx.select().from(projects).where(eq(projects.id, id)).limit(1))[0];
    return row
      ? {
          id: unsafeOpaqueId<ProjectId>(row.id),
          name: row.name,
          repositoryUrl: row.repositoryUrl,
          requiredChecks: row.requiredChecks as readonly string[],
          status: row.status as ProjectStatus,
          revision: row.revision,
        }
      : undefined;
  }
  async list(): Promise<readonly Project[]> {
    const rows = await this.tx
      .select()
      .from(projects)
      .orderBy(asc(projects.name), asc(projects.id));
    return rows.map((row) => ({
      id: unsafeOpaqueId<ProjectId>(row.id),
      name: row.name,
      repositoryUrl: row.repositoryUrl,
      requiredChecks: row.requiredChecks as readonly string[],
      status: row.status as ProjectStatus,
      revision: row.revision,
    }));
  }
  async insert(value: Project): Promise<void> {
    await this.tx.insert(projects).values(value);
  }
  async update(value: Project): Promise<void> {
    await this.tx
      .update(projects)
      .set({
        name: value.name,
        repositoryUrl: value.repositoryUrl,
        requiredChecks: value.requiredChecks,
        status: value.status,
        revision: value.revision,
      })
      .where(eq(projects.id, value.id));
  }
}
class PgProjectVisionRepository implements ProjectVisionRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof projectVisionVersions.$inferSelect): ProjectVisionVersion {
    return {
      id: unsafeOpaqueId<ProjectVisionVersionId>(row.id),
      projectId: unsafeOpaqueId<ProjectId>(row.projectId),
      sequence: row.sequence,
      summary: row.summary,
      ...(row.supersedesId === null
        ? {}
        : { supersedesId: unsafeOpaqueId<ProjectVisionVersionId>(row.supersedesId) }),
    };
  }
  async getById(id: ProjectVisionVersionId) {
    const row = (
      await this.tx
        .select()
        .from(projectVisionVersions)
        .where(eq(projectVisionVersions.id, id))
        .limit(1)
    )[0];
    return row ? this.map(row) : undefined;
  }
  async getCurrent(projectId: ProjectId) {
    const row = (
      await this.tx
        .select()
        .from(projectVisionVersions)
        .where(eq(projectVisionVersions.projectId, projectId))
        .orderBy(desc(projectVisionVersions.sequence))
        .limit(1)
    )[0];
    return row ? this.map(row) : undefined;
  }
  async insert(value: ProjectVisionVersion) {
    await this.tx.insert(projectVisionVersions).values(value);
  }
}
class PgGoalRepository implements GoalRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof goals.$inferSelect): Goal {
    return {
      id: unsafeOpaqueId<GoalId>(row.id),
      projectId: unsafeOpaqueId<ProjectId>(row.projectId),
      title: row.title,
      status: row.status as GoalStatus,
      successCriteria: row.successCriteria as string[],
      revision: row.revision,
      ...(row.description === null ? {} : { description: row.description }),
      ...(row.priority === null ? {} : { priority: row.priority }),
      ...(row.targetDate === null ? {} : { targetDate: row.targetDate }),
    };
  }
  async getById(id: GoalId) {
    const row = (await this.tx.select().from(goals).where(eq(goals.id, id)).limit(1))[0];
    return row ? this.map(row) : undefined;
  }
  async listByProject(id: ProjectId) {
    return (await this.tx.select().from(goals).where(eq(goals.projectId, id))).map((row) =>
      this.map(row),
    );
  }
  async insert(value: Goal) {
    await this.tx.insert(goals).values({ ...value, successCriteria: [...value.successCriteria] });
  }
  async update(value: Goal) {
    await this.tx
      .update(goals)
      .set({
        title: value.title,
        description: value.description,
        status: value.status,
        priority: value.priority,
        targetDate: value.targetDate,
        successCriteria: [...value.successCriteria],
        revision: value.revision,
      })
      .where(eq(goals.id, value.id));
  }
}
class PgPlanRepository implements PlanRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof plans.$inferSelect): Plan {
    return {
      id: unsafeOpaqueId<PlanId>(row.id),
      projectId: unsafeOpaqueId<ProjectId>(row.projectId),
      title: row.title,
      status: row.status as PlanStatus,
      revision: row.revision,
    };
  }
  async getById(id: PlanId) {
    const row = (await this.tx.select().from(plans).where(eq(plans.id, id)).limit(1))[0];
    return row ? this.map(row) : undefined;
  }
  async listByProject(id: ProjectId) {
    return (await this.tx.select().from(plans).where(eq(plans.projectId, id))).map((row) =>
      this.map(row),
    );
  }
  async insert(value: Plan) {
    await this.tx.insert(plans).values(value);
  }
  async update(value: Plan) {
    await this.tx
      .update(plans)
      .set({ title: value.title, status: value.status, revision: value.revision })
      .where(eq(plans.id, value.id));
  }
}
class PgPlanRevisionRepository implements PlanRevisionRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof planRevisions.$inferSelect): PlanRevision {
    return {
      id: unsafeOpaqueId<PlanRevisionId>(row.id),
      planId: unsafeOpaqueId<PlanId>(row.planId),
      projectId: unsafeOpaqueId<ProjectId>(row.projectId),
      sequence: row.sequence,
      title: row.title,
      goalIds: (row.goalIds as string[]).map((x) => unsafeOpaqueId<GoalId>(x)),
      ...(row.projectVisionVersionId === null
        ? {}
        : {
            projectVisionVersionId: unsafeOpaqueId<ProjectVisionVersionId>(
              row.projectVisionVersionId,
            ),
          }),
    };
  }
  async getById(id: PlanRevisionId) {
    const row = (
      await this.tx.select().from(planRevisions).where(eq(planRevisions.id, id)).limit(1)
    )[0];
    return row ? this.map(row) : undefined;
  }
  async getCurrent(planId: PlanId) {
    const row = (
      await this.tx
        .select()
        .from(planRevisions)
        .where(eq(planRevisions.planId, planId))
        .orderBy(desc(planRevisions.sequence))
        .limit(1)
    )[0];
    return row ? this.map(row) : undefined;
  }
  async insert(value: PlanRevision) {
    await this.tx.insert(planRevisions).values({ ...value, goalIds: [...value.goalIds] });
  }
}
class PgTaskRepository implements TaskRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof tasks.$inferSelect): Task {
    return {
      id: unsafeOpaqueId<TaskId>(row.id),
      projectId: unsafeOpaqueId<ProjectId>(row.projectId),
      planRevisionId: unsafeOpaqueId<PlanRevisionId>(row.planRevisionId),
      title: row.title,
      position: row.position,
      status: row.status as TaskStatus,
      dependencyIds: (row.dependencyIds as string[]).map((x) => unsafeOpaqueId<TaskId>(x)),
      goalIds: (row.goalIds as string[]).map((x) => unsafeOpaqueId<GoalId>(x)),
      revision: row.revision,
    };
  }
  async getById(id: TaskId) {
    const row = (await this.tx.select().from(tasks).where(eq(tasks.id, id)).limit(1))[0];
    return row ? this.map(row) : undefined;
  }
  async listByProject(id: ProjectId) {
    return (
      await this.tx
        .select()
        .from(tasks)
        .where(eq(tasks.projectId, id))
        .orderBy(asc(tasks.createdAt), asc(tasks.position), asc(tasks.id))
    ).map((row) => this.map(row));
  }
  async insert(value: Task) {
    await this.tx.insert(tasks).values({
      ...value,
      dependencyIds: [...value.dependencyIds],
      goalIds: [...(value.goalIds ?? [])],
    });
    if (value.dependencyIds.length > 0) {
      await this.tx.insert(taskDependencies).values(
        value.dependencyIds.map((prerequisiteTaskId) => ({
          taskId: value.id,
          prerequisiteTaskId,
        })),
      );
    }
  }
  async update(value: Task) {
    await this.tx
      .update(tasks)
      .set({
        title: value.title,
        position: value.position ?? 0,
        status: value.status,
        dependencyIds: [...value.dependencyIds],
        goalIds: [...(value.goalIds ?? [])],
        revision: value.revision,
      })
      .where(eq(tasks.id, value.id));
    await this.tx.delete(taskDependencies).where(eq(taskDependencies.taskId, value.id));
    if (value.dependencyIds.length > 0) {
      await this.tx.insert(taskDependencies).values(
        value.dependencyIds.map((prerequisiteTaskId) => ({
          taskId: value.id,
          prerequisiteTaskId,
        })),
      );
    }
  }
}
class PgFactoryRunRepository implements FactoryRunRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof factoryRuns.$inferSelect): FactoryRun {
    return {
      id: unsafeOpaqueId<FactoryRunId>(row.id),
      projectId: unsafeOpaqueId<ProjectId>(row.projectId),
      planRevisionId: unsafeOpaqueId<PlanRevisionId>(row.planRevisionId),
      ...(row.taskId === null ? {} : { taskId: unsafeOpaqueId<TaskId>(row.taskId) }),
      ...(row.accountId === null ? {} : { accountId: row.accountId }),
      ...(row.model === null ? {} : { model: row.model }),
      status: row.status as FactoryRunStatus,
      revision: row.revision,
      ...(row.reason === null ? {} : { reason: row.reason }),
    };
  }
  async getById(id: FactoryRunId) {
    const row = (
      await this.tx.select().from(factoryRuns).where(eq(factoryRuns.id, id)).limit(1)
    )[0];
    return row ? this.map(row) : undefined;
  }
  async listByProject(id: ProjectId) {
    return (await this.tx.select().from(factoryRuns).where(eq(factoryRuns.projectId, id))).map(
      (row) => this.map(row),
    );
  }
  async insert(value: FactoryRun) {
    await this.tx.insert(factoryRuns).values(value);
  }
  async update(value: FactoryRun) {
    await this.tx
      .update(factoryRuns)
      .set({
        taskId: value.taskId,
        accountId: value.accountId,
        model: value.model,
        status: value.status,
        reason: value.reason,
        revision: value.revision,
      })
      .where(eq(factoryRuns.id, value.id));
  }
}
class PgAgentRunRepository implements AgentRunRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof agentRuns.$inferSelect): AgentRun {
    return {
      id: unsafeOpaqueId<AgentRunId>(row.id),
      factoryRunId: unsafeOpaqueId<FactoryRunId>(row.factoryRunId),
      taskId: unsafeOpaqueId<TaskId>(row.taskId),
      agentPrincipalId: unsafeOpaqueId<PrincipalId>(row.agentPrincipalId),
      role: row.role as AgentRunRole,
      status: row.status as AgentRunStatus,
      revision: row.revision,
      ...(row.reason === null ? {} : { reason: row.reason }),
    };
  }
  async getById(id: AgentRunId) {
    const row = (await this.tx.select().from(agentRuns).where(eq(agentRuns.id, id)).limit(1))[0];
    return row ? this.map(row) : undefined;
  }
  async listByProject(projectId: ProjectId) {
    const rows = await this.tx
      .select({ agentRun: agentRuns })
      .from(agentRuns)
      .innerJoin(factoryRuns, eq(agentRuns.factoryRunId, factoryRuns.id))
      .where(eq(factoryRuns.projectId, projectId))
      .orderBy(asc(agentRuns.createdAt));
    return rows.map(({ agentRun }) => this.map(agentRun));
  }
  async insert(value: AgentRun) {
    await this.tx.insert(agentRuns).values(value);
  }
  async update(value: AgentRun) {
    await this.tx
      .update(agentRuns)
      .set({
        agentPrincipalId: value.agentPrincipalId,
        role: value.role,
        status: value.status,
        reason: value.reason,
        revision: value.revision,
      })
      .where(eq(agentRuns.id, value.id));
  }
}
class PgWorkspaceRepository implements WorkspaceRepository {
  constructor(private readonly tx: Tx) {}
  async getById(id: WorkspaceId) {
    const row = (await this.tx.select().from(workspaces).where(eq(workspaces.id, id)).limit(1))[0];
    return row
      ? {
          id: unsafeOpaqueId<WorkspaceId>(row.id),
          projectId: unsafeOpaqueId<ProjectId>(row.projectId),
          revision: row.revision,
          ...(row.checkpointDigest === null ? {} : { checkpointDigest: row.checkpointDigest }),
          ...(row.checkpointedAt === null
            ? {}
            : { checkpointedAt: new Date(row.checkpointedAt).toISOString() }),
          ...(row.checkpointSource === null ? {} : { checkpointSource: row.checkpointSource }),
          ...(row.checkpointCollectedAt === null
            ? {}
            : { checkpointCollectedAt: new Date(row.checkpointCollectedAt).toISOString() }),
          ...(row.cleanedAt === null ? {} : { cleanedAt: new Date(row.cleanedAt).toISOString() }),
        }
      : undefined;
  }
  async insert(value: Workspace) {
    await this.tx.insert(workspaces).values(value);
  }
  async update(value: Workspace) {
    await this.tx
      .update(workspaces)
      .set({
        checkpointDigest: value.checkpointDigest,
        checkpointedAt: value.checkpointedAt,
        checkpointSource: value.checkpointSource,
        checkpointCollectedAt: value.checkpointCollectedAt,
        cleanedAt: value.cleanedAt,
        revision: value.revision,
      })
      .where(eq(workspaces.id, value.id));
  }
}
class PgAttemptRepository implements AttemptRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof attempts.$inferSelect): Attempt {
    if (row.selectionProvenance === null) {
      throw new ImmutableAttemptProvenanceError(
        "Attempt is missing immutable selection provenance",
      );
    }
    return {
      id: unsafeOpaqueId<AttemptId>(row.id),
      agentRunId: unsafeOpaqueId<AgentRunId>(row.agentRunId),
      workspaceId: unsafeOpaqueId<WorkspaceId>(row.workspaceId),
      status: row.status as AttemptStatus,
      revision: row.revision,
      selection: row.selectionProvenance as Attempt["selection"],
      ...(row.providerId === null ? {} : { providerId: unsafeOpaqueId(row.providerId) }),
      ...(row.accountId === null ? {} : { accountId: row.accountId }),
      ...(row.model === null ? {} : { model: row.model }),
      ...(row.reason === null ? {} : { reason: row.reason }),
      toolCalls: row.toolCalls as NonNullable<Attempt["toolCalls"]>,
      ...(row.providerReference === null
        ? {}
        : { providerReference: row.providerReference as ProviderReference }),
    };
  }
  async getById(id: AttemptId) {
    const row = (await this.tx.select().from(attempts).where(eq(attempts.id, id)).limit(1))[0];
    return row ? this.map(row) : undefined;
  }
  async listByAgentRunIds(agentRunIds: readonly AgentRunId[]) {
    if (agentRunIds.length === 0) return [];
    const rows = await this.tx
      .select()
      .from(attempts)
      .where(inArray(attempts.agentRunId, agentRunIds))
      .orderBy(asc(attempts.createdAt));
    return rows.map((row) => this.map(row));
  }
  async insert(value: Attempt) {
    await this.tx.insert(attempts).values({
      id: value.id,
      agentRunId: value.agentRunId,
      workspaceId: value.workspaceId,
      status: value.status,
      providerId: value.providerId,
      accountId: value.accountId,
      model: value.model,
      reason: value.reason,
      toolCalls: [...(value.toolCalls ?? [])],
      selectionProvenance: value.selection,
      providerReference: value.providerReference,
      revision: value.revision,
    });
  }
  async update(value: Attempt) {
    const previous = await this.getById(value.id);
    if (previous === undefined) throw new Error(`Attempt ${String(value.id)} does not exist`);
    assertAttemptProvenanceImmutable(previous, value);
    await this.tx
      .update(attempts)
      .set({
        status: value.status,
        reason: value.reason,
        toolCalls: [...(value.toolCalls ?? [])],
        revision: value.revision,
      })
      .where(eq(attempts.id, value.id));
  }
  async bindProviderReference(
    attemptId: AttemptId,
    reference: ProviderReference,
  ): Promise<Attempt> {
    const previous = await this.getById(attemptId);
    if (previous === undefined) throw new Error(`Attempt ${String(attemptId)} does not exist`);
    if (previous.providerId !== undefined && previous.providerId !== reference.providerId) {
      throw new ImmutableAttemptProvenanceError(
        "Attempt provider reference does not match selected provider",
      );
    }
    if (previous.providerReference !== undefined) {
      const current = previous.providerReference;
      if (
        current.providerId === reference.providerId &&
        current.accountId === reference.accountId &&
        current.resourceType === reference.resourceType &&
        current.nativeId === reference.nativeId &&
        current.nativeRevision === reference.nativeRevision
      )
        return previous;
      throw new ImmutableAttemptProvenanceError("Attempt provider reference is already bound");
    }
    const rows = await this.tx
      .update(attempts)
      .set({ providerReference: reference })
      .where(and(eq(attempts.id, attemptId), isNull(attempts.providerReference)))
      .returning();
    if (rows[0] !== undefined) return this.map(rows[0]);
    const raced = await this.getById(attemptId);
    if (
      raced?.providerReference?.providerId === reference.providerId &&
      raced.providerReference.resourceType === reference.resourceType &&
      raced.providerReference.nativeId === reference.nativeId &&
      raced.providerReference.nativeRevision === reference.nativeRevision
    )
      return raced;
    throw new ImmutableAttemptProvenanceError(
      "Attempt provider reference was concurrently rebound",
    );
  }
}
class PgChangeSetRepository implements ChangeSetRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof changeSets.$inferSelect): ChangeSet {
    if (row.candidateManifest === null) {
      throw new Error(`ChangeSet ${row.id} is missing canonical candidate manifest`);
    }
    const candidateManifest = createCandidateManifest(
      row.candidateManifest as ChangeSet["candidateManifest"],
    );
    return {
      id: unsafeOpaqueId<ChangeSetId>(row.id),
      projectId: unsafeOpaqueId<ProjectId>(row.projectId),
      taskId: unsafeOpaqueId<TaskId>(row.taskId),
      producerAttemptId: unsafeOpaqueId(row.producerAttemptId),
      baseIdentity: row.baseIdentity,
      candidateDigest: row.candidateDigest,
      candidateManifest,
      diff: row.diff,
      revision: row.revision,
      status: row.status as ChangeSetStatus,
      ...(row.repositoryId === null ? {} : { repositoryId: unsafeOpaqueId(row.repositoryId) }),
      ...(row.repositoryKey === null ? {} : { repositoryKey: row.repositoryKey }),
      ...(row.publicationReference === null
        ? {}
        : { publicationReference: row.publicationReference as ProviderReference }),
      ...(row.targetReference === null ? {} : { targetReference: row.targetReference }),
      ...(row.targetRevision === null ? {} : { targetRevision: row.targetRevision }),
      ...(row.mergeReference === null
        ? {}
        : { mergeReference: row.mergeReference as ProviderReference }),
      ...(row.resultingRevision === null ? {} : { resultingRevision: row.resultingRevision }),
    };
  }
  async getById(id: ChangeSetId) {
    const row = (await this.tx.select().from(changeSets).where(eq(changeSets.id, id)).limit(1))[0];
    return row ? this.map(row) : undefined;
  }
  async listByProject(projectId: ProjectId) {
    const rows = await this.tx
      .select()
      .from(changeSets)
      .where(eq(changeSets.projectId, projectId))
      .orderBy(asc(changeSets.createdAt), asc(changeSets.id));
    return rows.map((row) => this.map(row));
  }
  async insert(value: ChangeSet) {
    assertCandidateIdentityImmutable(
      value.candidateDigest,
      value.candidateManifest,
      value.candidateDigest,
      value.candidateManifest,
    );
    await this.tx.insert(changeSets).values({
      ...value,
      candidateManifest: value.candidateManifest,
      diff: value.diff,
    });
  }
  async update(value: ChangeSet) {
    const previous = await this.getById(value.id);
    if (previous === undefined) throw new Error(`ChangeSet ${String(value.id)} does not exist`);
    assertCandidateIdentityImmutable(
      previous.candidateDigest,
      previous.candidateManifest,
      value.candidateDigest,
      value.candidateManifest,
    );
    if (previous.repositoryKey !== undefined && previous.repositoryKey !== value.repositoryKey) {
      throw new Error("ChangeSet repository identity is immutable once bound");
    }
    const immutableReferenceChanged =
      (previous.publicationReference !== undefined &&
        JSON.stringify(previous.publicationReference) !==
          JSON.stringify(value.publicationReference)) ||
      (previous.mergeReference !== undefined &&
        JSON.stringify(previous.mergeReference) !== JSON.stringify(value.mergeReference));
    if (immutableReferenceChanged) {
      throw new Error("ChangeSet provider publication identity is immutable once bound");
    }
    if (
      previous.targetReference !== undefined &&
      previous.targetReference !== value.targetReference
    ) {
      throw new Error("ChangeSet target reference is immutable once bound");
    }
    if (previous.targetRevision !== undefined && previous.targetRevision !== value.targetRevision) {
      throw new Error("ChangeSet target revision is immutable once bound");
    }
    if (
      previous.resultingRevision !== undefined &&
      previous.resultingRevision !== value.resultingRevision
    ) {
      throw new Error("ChangeSet resulting revision is immutable once bound");
    }
    await this.tx
      .update(changeSets)
      .set({
        repositoryKey: value.repositoryKey,
        publicationReference: value.publicationReference,
        targetReference: value.targetReference,
        targetRevision: value.targetRevision,
        mergeReference: value.mergeReference,
        resultingRevision: value.resultingRevision,
        revision: value.revision,
        status: value.status,
      })
      .where(eq(changeSets.id, value.id));
  }
}
class PgCredentialAuthorityRepository implements CredentialAuthorityRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof credentialAuthorities.$inferSelect): CredentialAuthority {
    return {
      connectionId: unsafeOpaqueId<ConnectionId>(row.connectionId),
      credentialReferenceId: unsafeOpaqueId<CredentialReferenceId>(row.credentialReferenceId),
      ownerId: unsafeOpaqueId<PrincipalId>(row.ownerId),
      generation: row.generation,
      status: row.status as CredentialAuthorityStatus,
      revision: row.revision,
      ...(row.pendingOwnerId === null
        ? {}
        : { pendingOwnerId: unsafeOpaqueId<PrincipalId>(row.pendingOwnerId) }),
    };
  }
  async getByConnectionId(connectionId: ConnectionId) {
    const row = (
      await this.tx
        .select()
        .from(credentialAuthorities)
        .where(eq(credentialAuthorities.connectionId, connectionId))
        .limit(1)
    )[0];
    return row ? this.map(row) : undefined;
  }
  private stale(): never {
    throw new CredentialAuthorityConflictError(
      "STALE_CREDENTIAL_AUTHORITY",
      "Credential authority generation/owner/state no longer permits this mutation",
    );
  }
  async claim(input: {
    readonly connectionId: ConnectionId;
    readonly credentialReferenceId: CredentialReferenceId;
    readonly ownerId: PrincipalId;
  }) {
    const rows = await this.tx
      .insert(credentialAuthorities)
      .values({
        connectionId: input.connectionId,
        credentialReferenceId: input.credentialReferenceId,
        ownerId: input.ownerId,
        generation: 1,
        status: "active",
        revision: 1,
      })
      .onConflictDoNothing()
      .returning();
    if (rows[0] === undefined) {
      throw new CredentialAuthorityConflictError(
        "CREDENTIAL_AUTHORITY_ALREADY_CLAIMED",
        "Credential authority already has an owner",
      );
    }
    return this.map(rows[0]);
  }
  async prepareHandoff(input: {
    readonly connectionId: ConnectionId;
    readonly credentialReferenceId: CredentialReferenceId;
    readonly ownerId: PrincipalId;
    readonly nextOwnerId: PrincipalId;
    readonly expectedGeneration: number;
  }) {
    const rows = await this.tx
      .update(credentialAuthorities)
      .set({
        status: "handoff",
        pendingOwnerId: input.nextOwnerId,
        revision: sql`${credentialAuthorities.revision} + 1`,
      })
      .where(
        and(
          eq(credentialAuthorities.connectionId, input.connectionId),
          eq(credentialAuthorities.credentialReferenceId, input.credentialReferenceId),
          eq(credentialAuthorities.ownerId, input.ownerId),
          eq(credentialAuthorities.generation, input.expectedGeneration),
          eq(credentialAuthorities.status, "active"),
        ),
      )
      .returning();
    if (rows[0] === undefined) this.stale();
    return this.map(rows[0]);
  }
  async fence(input: {
    readonly connectionId: ConnectionId;
    readonly credentialReferenceId: CredentialReferenceId;
    readonly ownerId: PrincipalId;
    readonly expectedGeneration: number;
  }) {
    const rows = await this.tx
      .update(credentialAuthorities)
      .set({ status: "fenced", revision: sql`${credentialAuthorities.revision} + 1` })
      .where(
        and(
          eq(credentialAuthorities.connectionId, input.connectionId),
          eq(credentialAuthorities.credentialReferenceId, input.credentialReferenceId),
          eq(credentialAuthorities.ownerId, input.ownerId),
          eq(credentialAuthorities.generation, input.expectedGeneration),
          or(
            eq(credentialAuthorities.status, "active"),
            eq(credentialAuthorities.status, "handoff"),
          ),
        ),
      )
      .returning();
    if (rows[0] === undefined) this.stale();
    return this.map(rows[0]);
  }
  async transfer(input: {
    readonly connectionId: ConnectionId;
    readonly credentialReferenceId: CredentialReferenceId;
    readonly ownerId: PrincipalId;
    readonly nextOwnerId: PrincipalId;
    readonly expectedGeneration: number;
  }) {
    const rows = await this.tx
      .update(credentialAuthorities)
      .set({
        ownerId: input.nextOwnerId,
        pendingOwnerId: null,
        generation: input.expectedGeneration + 1,
        status: "active",
        revision: sql`${credentialAuthorities.revision} + 1`,
      })
      .where(
        and(
          eq(credentialAuthorities.connectionId, input.connectionId),
          eq(credentialAuthorities.credentialReferenceId, input.credentialReferenceId),
          eq(credentialAuthorities.ownerId, input.ownerId),
          eq(credentialAuthorities.pendingOwnerId, input.nextOwnerId),
          eq(credentialAuthorities.generation, input.expectedGeneration),
          eq(credentialAuthorities.status, "fenced"),
        ),
      )
      .returning();
    if (rows[0] === undefined) {
      throw new CredentialAuthorityConflictError(
        "INVALID_CREDENTIAL_AUTHORITY_HANDOFF",
        "Credential authority transfer requires the prepared next owner and a fenced old owner",
      );
    }
    return this.map(rows[0]);
  }
}
class PgReviewRepository implements ReviewRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof reviews.$inferSelect): Review {
    return {
      id: unsafeOpaqueId<ReviewId>(row.id),
      changeSetId: unsafeOpaqueId<ChangeSetId>(row.changeSetId),
      candidateDigest: row.candidateDigest,
      reviewerPrincipalId: unsafeOpaqueId(row.reviewerPrincipalId),
      ...(row.reviewerAgentRunId === null
        ? {}
        : { reviewerAgentRunId: unsafeOpaqueId<AgentRunId>(row.reviewerAgentRunId) }),
      status: row.status as ReviewStatus,
      ...(row.disposition === null ? {} : { disposition: row.disposition as ReviewDisposition }),
    };
  }
  async getById(id: ReviewId) {
    const row = (await this.tx.select().from(reviews).where(eq(reviews.id, id)).limit(1))[0];
    return row ? this.map(row) : undefined;
  }
  async listByChangeSetIds(changeSetIds: readonly ChangeSetId[]) {
    if (changeSetIds.length === 0) return [];
    const rows = await this.tx
      .select()
      .from(reviews)
      .where(inArray(reviews.changeSetId, [...changeSetIds]))
      .orderBy(asc(reviews.createdAt), asc(reviews.id));
    return rows.map((row) => this.map(row));
  }
  async insert(value: Review) {
    await this.tx.insert(reviews).values(value).onConflictDoNothing();
  }
  async update(value: Review) {
    await this.tx
      .update(reviews)
      .set({
        reviewerPrincipalId: value.reviewerPrincipalId,
        reviewerAgentRunId: value.reviewerAgentRunId,
        status: value.status,
        disposition: value.disposition,
      })
      .where(eq(reviews.id, value.id));
  }
}
class PgVerificationEvidenceRepository implements VerificationEvidenceRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof verificationEvidence.$inferSelect): VerificationEvidence {
    return {
      id: unsafeOpaqueId<VerificationEvidenceId>(row.id),
      changeSetId: unsafeOpaqueId<ChangeSetId>(row.changeSetId),
      candidateDigest: row.candidateDigest,
      name: row.name,
      state: row.state as VerificationEvidenceState,
      source: row.source,
      observedAt: row.observedAt,
      required: row.required,
      ...(row.details === null
        ? {}
        : { details: row.details as Readonly<Record<string, unknown>> }),
    };
  }
  async getById(id: VerificationEvidenceId) {
    const row = (
      await this.tx
        .select()
        .from(verificationEvidence)
        .where(eq(verificationEvidence.id, id))
        .limit(1)
    )[0];
    return row ? this.map(row) : undefined;
  }
  async listByChangeSetIds(changeSetIds: readonly ChangeSetId[]) {
    if (changeSetIds.length === 0) return [];
    const rows = await this.tx
      .select()
      .from(verificationEvidence)
      .where(inArray(verificationEvidence.changeSetId, [...changeSetIds]))
      .orderBy(asc(verificationEvidence.observedAt), asc(verificationEvidence.id));
    return rows.map((row) => this.map(row));
  }
  async insert(value: VerificationEvidence) {
    await this.tx.insert(verificationEvidence).values(value);
  }
  async update(value: VerificationEvidence) {
    const previous = await this.getById(value.id);
    if (!previous) throw new Error(`VerificationEvidence ${String(value.id)} does not exist`);
    if (
      previous.changeSetId !== value.changeSetId ||
      previous.candidateDigest !== value.candidateDigest ||
      previous.name !== value.name ||
      previous.source !== value.source ||
      previous.observedAt !== value.observedAt
    ) {
      throw new Error("VerificationEvidence immutable identity cannot change");
    }
    await this.tx
      .update(verificationEvidence)
      .set({ state: value.state, required: value.required, details: value.details })
      .where(eq(verificationEvidence.id, value.id));
  }
}
class PgReviewFindingRepository implements ReviewFindingRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof reviewFindings.$inferSelect): ReviewFinding {
    return {
      id: unsafeOpaqueId<ReviewFindingId>(row.id),
      changeSetId: unsafeOpaqueId<ChangeSetId>(row.changeSetId),
      candidateDigest: row.candidateDigest,
      severity: row.severity as ReviewFindingSeverity,
      summary: row.summary,
      resolved: row.resolved,
      ...(row.source === null ? {} : { source: row.source }),
    };
  }
  async getById(id: ReviewFindingId) {
    const row = (
      await this.tx.select().from(reviewFindings).where(eq(reviewFindings.id, id)).limit(1)
    )[0];
    return row ? this.map(row) : undefined;
  }
  async listByChangeSetIds(changeSetIds: readonly ChangeSetId[]) {
    if (changeSetIds.length === 0) return [];
    const rows = await this.tx
      .select()
      .from(reviewFindings)
      .where(inArray(reviewFindings.changeSetId, [...changeSetIds]))
      .orderBy(asc(reviewFindings.createdAt), asc(reviewFindings.id));
    return rows.map((row) => this.map(row));
  }
  async insert(value: ReviewFinding) {
    await this.tx.insert(reviewFindings).values(value);
  }
  async update(value: ReviewFinding) {
    const previous = await this.getById(value.id);
    if (!previous) throw new Error(`ReviewFinding ${String(value.id)} does not exist`);
    if (
      previous.changeSetId !== value.changeSetId ||
      previous.candidateDigest !== value.candidateDigest ||
      previous.severity !== value.severity ||
      previous.summary !== value.summary ||
      previous.source !== value.source
    ) {
      throw new Error("ReviewFinding immutable identity cannot change");
    }
    await this.tx
      .update(reviewFindings)
      .set({ resolved: value.resolved })
      .where(eq(reviewFindings.id, value.id));
  }
}
class PgBusinessEventRepository implements BusinessEventRepository {
  constructor(private readonly tx: Tx) {}
  async append(value: BusinessEvent) {
    await this.tx.insert(businessEvents).values({ ...value });
  }
}
class PgAuditRepository implements AuditRepository {
  constructor(private readonly tx: Tx) {}
  async append(value: AuditRecord) {
    await this.tx.insert(auditRecords).values({ ...value });
  }
}
class PgOutboxRepository implements OutboxRepository {
  constructor(private readonly tx: Tx) {}
  async append(value: OutboxMessage) {
    await this.tx.insert(outboxMessages).values({ ...value });
  }
}

export class PostgresUnitOfWork implements UnitOfWork {
  constructor(private readonly db: Database) {}
  async transaction<T>(work: (tx: ApplicationTransaction) => Promise<T>): Promise<T> {
    return this.db.transaction(async (tx) =>
      work({
        projects: new PgProjectRepository(tx),
        projectVisions: new PgProjectVisionRepository(tx),
        goals: new PgGoalRepository(tx),
        plans: new PgPlanRepository(tx),
        planRevisions: new PgPlanRevisionRepository(tx),
        tasks: new PgTaskRepository(tx),
        factoryRuns: new PgFactoryRunRepository(tx),
        agentRuns: new PgAgentRunRepository(tx),
        workspaces: new PgWorkspaceRepository(tx),
        attempts: new PgAttemptRepository(tx),
        changeSets: new PgChangeSetRepository(tx),
        reviews: new PgReviewRepository(tx),
        verificationEvidence: new PgVerificationEvidenceRepository(tx),
        reviewFindings: new PgReviewFindingRepository(tx),
        credentialAuthorities: new PgCredentialAuthorityRepository(tx),
        events: new PgBusinessEventRepository(tx),
        audit: new PgAuditRepository(tx),
        outbox: new PgOutboxRepository(tx),
      }),
    );
  }
}