import { and, asc, desc, eq, inArray, isNull, or, sql } from "drizzle-orm";
import type { Transaction } from "@platform-modules/db";
import {
  OutboxConsumerReceiptConflictError,
  WorkflowStepOutcomeConflictError,
} from "@awp/application";
import type {
  AgentRunRepository,
  ApplicationTransaction,
  AttemptRepository,
  AuditRepository,
  BusinessEventRepository,
  ChangeSetRepository,
  ConfigurationDefinitionRepository,
  ConfigurationOverrideRepository,
  CredentialAuthorityRepository,
  FactoryRunRepository,
  GoalRepository,
  OutboxConsumerReceipt,
  OutboxConsumerReceiptRepository,
  OutboxMessage,
  OutboxRepository,
  PendingOutboxMessage,
  WorkflowStepOutcome,
  WorkflowStepOutcomeRepository,
  PlanRepository,
  PlanRevisionRepository,
  PlanningSessionRepository,
  ProjectPlanningDefaultsRepository,
  ProjectRepository,
  ProjectVisionRepository,
  ReviewRepository,
  ReviewFindingRepository,
  SessionRecord,
  SessionRepository,
  VerificationEvidenceRepository,
  TaskRepository,
  UnitOfWork,
  WorkspaceRepository,
} from "@awp/application";
import type {
  AgentRunId,
  AttemptId,
  AuditRecord,
  BusinessEvent,
  ChangeSetId,
  ConfigurationDefinition,
  ConfigurationOverride,
  ConfigurationScope,
  ConnectionId,
  CredentialReferenceId,
  FactoryRunId,
  GoalId,
  OperationId,
  OutboxMessageId,
  PlanId,
  PlanRevisionId,
  PlanningSessionId,
  ProjectId,
  ProjectVisionVersionId,
  ProviderReference,
  PrincipalId,
  ReviewId,
  ReviewFindingId,
  SessionId,
  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,
  PlanningSession,
  PlanningParticipationMode,
  PlanningProfileKind,
  PlanningReadiness,
  PlanningSessionStatus,
  ProjectPlanningDefaults,
  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,
  configurationDefinitions,
  configurationOverrides,
  credentialAuthorities,
  factoryRuns,
  goals,
  outboxConsumerReceipts,
  outboxMessages,
  workflowStepOutcomes,
  planRevisions,
  planningSessions,
  projectPlanningDefaults,
  plans,
  projectVisionVersions,
  projects,
  reviews,
  reviewFindings,
  sessions,
  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[],
      readiness: row.readiness as NonNullable<Goal["readiness"]>,
      revision: row.revision,
      ...(row.readinessReason === null ? {} : { readinessReason: row.readinessReason }),
      ...(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 ?? null,
        status: value.status,
        priority: value.priority ?? null,
        targetDate: value.targetDate ?? null,
        successCriteria: [...value.successCriteria],
        readiness: value.readiness ?? "not-ready",
        readinessReason: value.readinessReason ?? null,
        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 PgPlanningSessionRepository implements PlanningSessionRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof planningSessions.$inferSelect): PlanningSession {
    return {
      id: unsafeOpaqueId<PlanningSessionId>(row.id),
      projectId: unsafeOpaqueId<ProjectId>(row.projectId),
      planId: unsafeOpaqueId<PlanId>(row.planId),
      ...(row.planRevisionId === null
        ? {}
        : { planRevisionId: unsafeOpaqueId<PlanRevisionId>(row.planRevisionId) }),
      ...(row.projectVisionVersionId === null
        ? {}
        : {
            projectVisionVersionId: unsafeOpaqueId<ProjectVisionVersionId>(
              row.projectVisionVersionId,
            ),
          }),
      goalIds: (row.goalIds as string[]).map((value) => unsafeOpaqueId<GoalId>(value)),
      intent: row.intent,
      mode: row.mode as PlanningParticipationMode,
      profile: row.profile as PlanningSession["profile"],
      status: row.status as PlanningSessionStatus,
      readiness: row.readiness as PlanningReadiness,
      ...(row.activeItemKey === null ? {} : { activeItemKey: row.activeItemKey }),
      draft: row.draft,
      ...(row.plannerOverride === null
        ? {}
        : { plannerOverride: row.plannerOverride as PlanningSession["plannerOverride"] }),
      turns: row.turns as PlanningSession["turns"],
      items: row.items as PlanningSession["items"],
      deferrals: row.deferrals as PlanningSession["deferrals"],
      delivery: row.delivery as PlanningSession["delivery"],
      ...(row.launch === null ? {} : { launch: row.launch as PlanningSession["launch"] }),
      revision: row.revision,
      createdAt: row.createdAt,
      updatedAt: row.updatedAt,
    };
  }
  async getById(id: PlanningSessionId) {
    const row = (
      await this.tx.select().from(planningSessions).where(eq(planningSessions.id, id)).limit(1)
    )[0];
    return row ? this.map(row) : undefined;
  }
  async getByPlanId(planId: PlanId) {
    const row = (
      await this.tx
        .select()
        .from(planningSessions)
        .where(eq(planningSessions.planId, planId))
        .orderBy(desc(planningSessions.updatedAt))
        .limit(1)
    )[0];
    return row ? this.map(row) : undefined;
  }
  async listByProject(projectId: ProjectId) {
    return (
      await this.tx
        .select()
        .from(planningSessions)
        .where(eq(planningSessions.projectId, projectId))
        .orderBy(desc(planningSessions.updatedAt))
    ).map((row) => this.map(row));
  }
  async insert(value: PlanningSession) {
    await this.tx.insert(planningSessions).values({
      ...value,
      goalIds: [...value.goalIds],
      profile: value.profile,
      plannerOverride: value.plannerOverride ?? null,
      turns: [...value.turns],
      items: [...value.items],
      deferrals: [...value.deferrals],
      delivery: value.delivery,
      launch: value.launch ?? null,
    });
  }
  async update(value: PlanningSession) {
    await this.tx
      .update(planningSessions)
      .set({
        planRevisionId: value.planRevisionId ?? null,
        projectVisionVersionId: value.projectVisionVersionId ?? null,
        goalIds: [...value.goalIds],
        intent: value.intent,
        mode: value.mode,
        profile: value.profile,
        status: value.status,
        readiness: value.readiness,
        activeItemKey: value.activeItemKey ?? null,
        draft: value.draft,
        plannerOverride: value.plannerOverride ?? null,
        turns: [...value.turns],
        items: [...value.items],
        deferrals: [...value.deferrals],
        delivery: value.delivery,
        launch: value.launch ?? null,
        revision: value.revision,
        updatedAt: value.updatedAt,
      })
      .where(eq(planningSessions.id, value.id));
  }
}

class PgProjectPlanningDefaultsRepository implements ProjectPlanningDefaultsRepository {
  constructor(private readonly tx: Tx) {}
  async getByProjectId(projectId: ProjectId): Promise<ProjectPlanningDefaults | undefined> {
    const row = (
      await this.tx
        .select()
        .from(projectPlanningDefaults)
        .where(eq(projectPlanningDefaults.projectId, projectId))
        .limit(1)
    )[0];
    return row
      ? {
          projectId: unsafeOpaqueId<ProjectId>(row.projectId),
          mode: row.mode as PlanningParticipationMode,
          profileKind: row.profileKind as PlanningProfileKind,
          delivery: row.delivery as ProjectPlanningDefaults["delivery"],
          acceptedDecisionAnswers: row.acceptedDecisionAnswers as Readonly<Record<string, string>>,
          revision: row.revision,
        }
      : undefined;
  }
  async upsert(value: ProjectPlanningDefaults): Promise<void> {
    await this.tx
      .insert(projectPlanningDefaults)
      .values({
        projectId: value.projectId,
        mode: value.mode,
        profileKind: value.profileKind,
        delivery: value.delivery,
        acceptedDecisionAnswers: value.acceptedDecisionAnswers,
        revision: value.revision,
      })
      .onConflictDoUpdate({
        target: projectPlanningDefaults.projectId,
        set: {
          mode: value.mode,
          profileKind: value.profileKind,
          delivery: value.delivery,
          acceptedDecisionAnswers: value.acceptedDecisionAnswers,
          revision: value.revision,
        },
      });
  }
}

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 PgConfigurationDefinitionRepository implements ConfigurationDefinitionRepository {
  constructor(private readonly tx: Tx) {}

  async getByKey(key: string): Promise<ConfigurationDefinition<unknown> | undefined> {
    const row = (
      await this.tx
        .select()
        .from(configurationDefinitions)
        .where(eq(configurationDefinitions.key, key))
        .limit(1)
    )[0];
    if (!row) return undefined;
    return {
      ...(row.definition as Omit<ConfigurationDefinition<unknown>, "key" | "schemaVersion">),
      key: row.key,
      schemaVersion: row.schemaVersion,
    };
  }

  async list(): Promise<readonly ConfigurationDefinition<unknown>[]> {
    const rows = await this.tx
      .select()
      .from(configurationDefinitions)
      .orderBy(asc(configurationDefinitions.key));
    return rows.map((row) => ({
      ...(row.definition as Omit<ConfigurationDefinition<unknown>, "key" | "schemaVersion">),
      key: row.key,
      schemaVersion: row.schemaVersion,
    }));
  }

  async insert(definition: ConfigurationDefinition<unknown>): Promise<void> {
    const { key, schemaVersion, ...body } = definition;
    await this.tx
      .insert(configurationDefinitions)
      .values({
        key,
        schemaVersion,
        definition: body,
      })
      .onConflictDoNothing({ target: configurationDefinitions.key });
  }
}

class PgConfigurationOverrideRepository implements ConfigurationOverrideRepository {
  constructor(private readonly tx: Tx) {}

  private map(row: typeof configurationOverrides.$inferSelect): ConfigurationOverride<unknown> {
    return {
      definitionKey: row.definitionKey,
      scopeType: row.scopeType as ConfigurationScope,
      scopeId: row.scopeId,
      value: row.value,
      resourceRevision: row.resourceRevision,
      setBy: row.setBy,
      setAt: row.setAt,
    };
  }

  async get(
    definitionKey: string,
    scopeType: ConfigurationScope,
    scopeId: string,
  ): Promise<ConfigurationOverride<unknown> | undefined> {
    const row = (
      await this.tx
        .select()
        .from(configurationOverrides)
        .where(
          and(
            eq(configurationOverrides.definitionKey, definitionKey),
            eq(configurationOverrides.scopeType, scopeType),
            eq(configurationOverrides.scopeId, scopeId),
          ),
        )
        .limit(1)
    )[0];
    return row ? this.map(row) : undefined;
  }

  async listByDefinition(
    definitionKey: string,
  ): Promise<readonly ConfigurationOverride<unknown>[]> {
    const rows = await this.tx
      .select()
      .from(configurationOverrides)
      .where(eq(configurationOverrides.definitionKey, definitionKey))
      .orderBy(asc(configurationOverrides.scopeType), asc(configurationOverrides.scopeId));
    return rows.map((row) => this.map(row));
  }

  async upsert(value: ConfigurationOverride<unknown>): Promise<void> {
    await this.tx
      .insert(configurationOverrides)
      .values({ ...value })
      .onConflictDoUpdate({
        target: [
          configurationOverrides.definitionKey,
          configurationOverrides.scopeType,
          configurationOverrides.scopeId,
        ],
        set: {
          value: value.value,
          resourceRevision: value.resourceRevision,
          setBy: value.setBy,
          setAt: value.setAt,
        },
      });
  }

  async delete(
    definitionKey: string,
    scopeType: ConfigurationScope,
    scopeId: string,
  ): Promise<void> {
    await this.tx
      .delete(configurationOverrides)
      .where(
        and(
          eq(configurationOverrides.definitionKey, definitionKey),
          eq(configurationOverrides.scopeType, scopeType),
          eq(configurationOverrides.scopeId, scopeId),
        ),
      );
  }
}

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.reference === null
        ? {}
        : { reference: row.reference as NonNullable<VerificationEvidence["reference"]> }),
      ...(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 ||
      JSON.stringify(previous.reference ?? null) !== JSON.stringify(value.reference ?? null)
    ) {
      throw new Error("VerificationEvidence immutable identity cannot change");
    }
    await this.tx
      .update(verificationEvidence)
      .set({
        state: value.state,
        required: value.required,
        reference: value.reference,
        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 PgSessionRepository implements SessionRepository {
  constructor(private readonly tx: Tx) {}
  private map(row: typeof sessions.$inferSelect): SessionRecord {
    return {
      id: unsafeOpaqueId<SessionId>(row.id),
      tokenHash: row.tokenHash,
      principalId: unsafeOpaqueId<PrincipalId>(row.principalId),
      credentialVersionDigest: row.credentialVersionDigest,
      createdAt: new Date(row.createdAt).toISOString(),
      lastSeenAt: new Date(row.lastSeenAt).toISOString(),
      expiresAt: new Date(row.expiresAt).toISOString(),
      ...(row.revokedAt === null ? {} : { revokedAt: new Date(row.revokedAt).toISOString() }),
      ...(row.userAgentDigest === null ? {} : { userAgentDigest: row.userAgentDigest }),
    };
  }
  async getById(id: SessionId) {
    const row = (await this.tx.select().from(sessions).where(eq(sessions.id, id)).limit(1))[0];
    return row ? this.map(row) : undefined;
  }
  async getByTokenHash(tokenHash: string) {
    const row = (
      await this.tx.select().from(sessions).where(eq(sessions.tokenHash, tokenHash)).limit(1)
    )[0];
    return row ? this.map(row) : undefined;
  }
  async insert(value: SessionRecord) {
    await this.tx.insert(sessions).values({
      id: value.id,
      tokenHash: value.tokenHash,
      principalId: value.principalId,
      credentialVersionDigest: value.credentialVersionDigest,
      createdAt: value.createdAt,
      lastSeenAt: value.lastSeenAt,
      expiresAt: value.expiresAt,
      revokedAt: value.revokedAt,
      userAgentDigest: value.userAgentDigest,
    });
  }
  async updateLastSeen(id: SessionId, lastSeenAt: string) {
    await this.tx.update(sessions).set({ lastSeenAt }).where(eq(sessions.id, id));
  }
  async revoke(id: SessionId, revokedAt: string) {
    await this.tx.update(sessions).set({ revokedAt }).where(eq(sessions.id, id));
  }
  async revokeAllByPrincipal(principalId: PrincipalId, revokedAt: string) {
    const rows = await this.tx
      .update(sessions)
      .set({ revokedAt })
      .where(eq(sessions.principalId, principalId))
      .returning({ id: sessions.id });
    return rows.length;
  }
}

class PgBusinessEventRepository implements BusinessEventRepository {
  constructor(private readonly tx: Tx) {}
  async append(value: BusinessEvent) {
    await this.tx.insert(businessEvents).values({ ...value });
  }
  async listByProject(projectId: ProjectId): Promise<readonly BusinessEvent[]> {
    const rows = await this.tx
      .select()
      .from(businessEvents)
      .where(eq(businessEvents.projectId, projectId))
      .orderBy(asc(businessEvents.occurredAt), asc(businessEvents.id));
    return rows.map((row) => ({
      id: unsafeOpaqueId(row.id),
      type: row.type,
      schemaVersion: row.schemaVersion,
      occurredAt: row.occurredAt,
      aggregateType: row.aggregateType,
      aggregateId: row.aggregateId,
      aggregateRevision: row.aggregateRevision,
      projectId: unsafeOpaqueId<ProjectId>(row.projectId!),
      ...(row.principalId === null
        ? {}
        : { principalId: unsafeOpaqueId<PrincipalId>(row.principalId) }),
      correlationId: unsafeOpaqueId(row.correlationId),
      ...(row.causationId === null ? {} : { causationId: unsafeOpaqueId(row.causationId) }),
      payload: row.payload,
    }));
  }
}
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 });
  }
  async listPending(limit: number): Promise<readonly PendingOutboxMessage[]> {
    if (!Number.isInteger(limit) || limit <= 0)
      throw new Error("Outbox pending limit must be positive");
    const rows = await this.tx
      .select()
      .from(outboxMessages)
      .where(eq(outboxMessages.published, false))
      .orderBy(asc(outboxMessages.occurredAt), asc(outboxMessages.id))
      .limit(limit);
    return rows.map((row) => ({
      id: unsafeOpaqueId<OutboxMessageId>(row.id),
      topic: row.topic,
      payload: row.payload,
      occurredAt: row.occurredAt,
      published: row.published,
      ...(row.publishedAt === null ? {} : { publishedAt: row.publishedAt }),
      attempts: row.attempts,
    }));
  }
  async recordAttempt(id: OutboxMessageId): Promise<void> {
    await this.tx
      .update(outboxMessages)
      .set({ attempts: sql`${outboxMessages.attempts} + 1` })
      .where(eq(outboxMessages.id, id));
  }
  async markPublished(id: OutboxMessageId, publishedAt: string): Promise<void> {
    await this.tx
      .update(outboxMessages)
      .set({ published: true, publishedAt })
      .where(eq(outboxMessages.id, id));
  }
}

class PgOutboxConsumerReceiptRepository implements OutboxConsumerReceiptRepository {
  constructor(private readonly tx: Tx) {}

  async get(
    consumerId: string,
    messageId: OutboxMessageId,
  ): Promise<OutboxConsumerReceipt | undefined> {
    const row = (
      await this.tx
        .select()
        .from(outboxConsumerReceipts)
        .where(
          and(
            eq(outboxConsumerReceipts.consumerId, consumerId),
            eq(outboxConsumerReceipts.messageId, messageId),
          ),
        )
        .limit(1)
    )[0];
    return row
      ? {
          consumerId: row.consumerId,
          messageId: unsafeOpaqueId<OutboxMessageId>(row.messageId),
          processedAt: row.processedAt,
        }
      : undefined;
  }

  async insert(value: OutboxConsumerReceipt): Promise<void> {
    try {
      await this.tx.insert(outboxConsumerReceipts).values({ ...value });
    } catch (error) {
      if (isUniqueViolationFor(error, "outbox_consumer_receipts")) {
        throw new OutboxConsumerReceiptConflictError(value.consumerId, value.messageId);
      }
      throw error;
    }
  }
}

function isUniqueViolationFor(error: unknown, relation: string): boolean {
  let candidate: unknown = error;
  for (let depth = 0; depth < 4 && candidate && typeof candidate === "object"; depth += 1) {
    const details = candidate as {
      code?: unknown;
      constraint?: unknown;
      table?: unknown;
      message?: unknown;
      cause?: unknown;
    };
    const unique =
      details.code === "23505" ||
      /duplicate key|unique constraint/i.test(String(details.message ?? ""));
    const matches =
      details.table === relation ||
      String(details.constraint ?? "").includes(relation) ||
      String(details.message ?? "").includes(relation);
    if (unique && matches) return true;
    candidate = details.cause;
  }
  return false;
}

function isWorkflowStepOutcomeUniqueViolation(error: unknown): boolean {
  return isUniqueViolationFor(error, "workflow_step_outcomes");
}

class PgWorkflowStepOutcomeRepository implements WorkflowStepOutcomeRepository {
  constructor(private readonly tx: Tx) {}

  async get(
    operationId: OperationId,
    stepName: string,
    stepKey: string,
  ): Promise<WorkflowStepOutcome | undefined> {
    const row = (
      await this.tx
        .select()
        .from(workflowStepOutcomes)
        .where(
          and(
            eq(workflowStepOutcomes.operationId, operationId),
            eq(workflowStepOutcomes.stepName, stepName),
            eq(workflowStepOutcomes.stepKey, stepKey),
          ),
        )
        .limit(1)
    )[0];
    return row
      ? {
          operationId: unsafeOpaqueId<OperationId>(row.operationId),
          stepName: row.stepName,
          stepKey: row.stepKey,
          outcome: row.outcome,
          recordedAt: row.recordedAt,
        }
      : undefined;
  }

  async insert(value: WorkflowStepOutcome): Promise<void> {
    try {
      await this.tx.insert(workflowStepOutcomes).values({ ...value });
    } catch (error) {
      if (isWorkflowStepOutcomeUniqueViolation(error)) {
        throw new WorkflowStepOutcomeConflictError(
          value.operationId,
          value.stepName,
          value.stepKey,
        );
      }
      throw error;
    }
  }
}

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),
        planningSessions: new PgPlanningSessionRepository(tx),
        projectPlanningDefaults: new PgProjectPlanningDefaultsRepository(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),
        sessions: new PgSessionRepository(tx),
        credentialAuthorities: new PgCredentialAuthorityRepository(tx),
        configurationDefinitions: new PgConfigurationDefinitionRepository(tx),
        configurationOverrides: new PgConfigurationOverrideRepository(tx),
        events: new PgBusinessEventRepository(tx),
        audit: new PgAuditRepository(tx),
        outbox: new PgOutboxRepository(tx),
        outboxConsumerReceipts: new PgOutboxConsumerReceiptRepository(tx),
        workflowStepOutcomes: new PgWorkflowStepOutcomeRepository(tx),
      }),
    );
  }
}