import {
  boolean,
  integer,
  jsonb,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
} from "drizzle-orm/pg-core";

const revision = () => integer("revision").notNull().default(1);
const createdAt = () =>
  timestamp("created_at", { withTimezone: true, mode: "string" }).notNull().defaultNow();

export const projects = pgTable("projects", {
  id: text("id").primaryKey(),
  name: text("name").notNull().default(""),
  repositoryUrl: text("repository_url").notNull().default(""),
  requiredChecks: jsonb("required_checks").notNull().default([]),
  status: text("status").notNull().default("draft"),
  revision: revision(),
  createdAt: createdAt(),
});
export const projectVisionVersions = pgTable("project_vision_versions", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull(),
  sequence: integer("sequence").notNull(),
  supersedesId: text("supersedes_id"),
  summary: text("summary").notNull().default(""),
  createdAt: createdAt(),
});
export const goals = pgTable("goals", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull(),
  title: text("title").notNull().default(""),
  description: text("description"),
  status: text("status").notNull().default("draft"),
  priority: integer("priority"),
  targetDate: text("target_date"),
  successCriteria: jsonb("success_criteria").notNull().default([]),
  readiness: text("readiness").notNull().default("not-ready"),
  readinessReason: text("readiness_reason"),
  revision: revision(),
  createdAt: createdAt(),
});
export const plans = pgTable("plans", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull(),
  title: text("title").notNull().default(""),
  status: text("status").notNull().default("draft"),
  revision: revision(),
  createdAt: createdAt(),
});
export const planRevisions = pgTable("plan_revisions", {
  id: text("id").primaryKey(),
  planId: text("plan_id").notNull(),
  projectId: text("project_id").notNull(),
  sequence: integer("sequence").notNull(),
  title: text("title").notNull().default(""),
  projectVisionVersionId: text("project_vision_version_id"),
  goalIds: jsonb("goal_ids").notNull().default([]),
  createdAt: createdAt(),
});
export const tasks = pgTable("tasks", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull(),
  planRevisionId: text("plan_revision_id").notNull(),
  title: text("title").notNull().default(""),
  position: integer("position").notNull().default(0),
  status: text("status").notNull().default("planned"),
  dependencyIds: jsonb("dependency_ids").notNull().default([]),
  goalIds: jsonb("goal_ids").notNull().default([]),
  revision: revision(),
  createdAt: createdAt(),
});

export const planningSessions = pgTable("planning_sessions", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull(),
  planId: text("plan_id").notNull(),
  planRevisionId: text("plan_revision_id"),
  projectVisionVersionId: text("project_vision_version_id"),
  goalIds: jsonb("goal_ids").notNull().default([]),
  intent: text("intent").notNull(),
  mode: text("mode").notNull().default("simple"),
  profile: jsonb("profile").notNull(),
  status: text("status").notNull().default("active"),
  readiness: text("readiness").notNull().default("blocked"),
  activeItemKey: text("active_item_key"),
  draft: text("draft").notNull().default(""),
  plannerOverride: jsonb("planner_override"),
  turns: jsonb("turns").notNull().default([]),
  items: jsonb("items").notNull().default([]),
  deferrals: jsonb("deferrals").notNull().default([]),
  delivery: jsonb("delivery").notNull(),
  launch: jsonb("launch"),
  revision: revision(),
  createdAt: createdAt(),
  updatedAt: timestamp("updated_at", { withTimezone: true, mode: "string" }).notNull(),
});

export const projectPlanningDefaults = pgTable("project_planning_defaults", {
  projectId: text("project_id").primaryKey(),
  mode: text("mode").notNull().default("simple"),
  profileKind: text("profile_kind").notNull(),
  delivery: jsonb("delivery").notNull(),
  acceptedDecisionAnswers: jsonb("accepted_decision_answers").notNull().default({}),
  revision: revision(),
  createdAt: createdAt(),
});

export const taskDependencies = pgTable("task_dependencies", {
  taskId: text("task_id").notNull(),
  prerequisiteTaskId: text("prerequisite_task_id").notNull(),
  createdAt: createdAt(),
});
export const factoryRuns = pgTable(
  "factory_runs",
  {
    id: text("id").primaryKey(),
    projectId: text("project_id").notNull(),
    planRevisionId: text("plan_revision_id").notNull(),
    taskId: text("task_id"),
    accountId: text("account_id"),
    model: text("model"),
    status: text("status").notNull().default("planned"),
    reason: text("reason"),
    revision: revision(),
    createdAt: createdAt(),
  },
  (table) => [uniqueIndex("factory_runs_plan_revision_unique").on(table.planRevisionId)],
);
export const agentRuns = pgTable("agent_runs", {
  id: text("id").primaryKey(),
  factoryRunId: text("factory_run_id").notNull(),
  taskId: text("task_id").notNull(),
  agentPrincipalId: text("agent_principal_id").notNull().default("principal:agent:legacy"),
  role: text("role").notNull().default("coder"),
  status: text("status").notNull().default("planned"),
  reason: text("reason"),
  revision: revision(),
  createdAt: createdAt(),
});
export const workspaces = pgTable("workspaces", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull(),
  checkpointDigest: text("checkpoint_digest"),
  checkpointedAt: timestamp("checkpointed_at", { withTimezone: true, mode: "string" }),
  checkpointSource: text("checkpoint_source"),
  checkpointCollectedAt: timestamp("checkpoint_collected_at", {
    withTimezone: true,
    mode: "string",
  }),
  cleanedAt: timestamp("cleaned_at", { withTimezone: true, mode: "string" }),
  revision: revision(),
  createdAt: createdAt(),
});
export const attempts = pgTable("attempts", {
  id: text("id").primaryKey(),
  agentRunId: text("agent_run_id").notNull(),
  workspaceId: text("workspace_id").notNull(),
  status: text("status").notNull().default("created"),
  providerId: text("provider_id"),
  accountId: text("account_id"),
  model: text("model"),
  reason: text("reason"),
  toolCalls: jsonb("tool_calls").notNull().default([]),
  selectionProvenance: jsonb("selection_provenance").notNull(),
  providerReference: jsonb("provider_reference"),
  revision: revision(),
  createdAt: createdAt(),
});
export const changeSets = pgTable("change_sets", {
  id: text("id").primaryKey(),
  projectId: text("project_id").notNull(),
  taskId: text("task_id").notNull(),
  producerAttemptId: text("producer_attempt_id").notNull(),
  baseIdentity: text("base_identity").notNull(),
  candidateDigest: text("candidate_digest").notNull(),
  candidateManifest: jsonb("candidate_manifest").notNull(),
  diff: text("diff").notNull().default(""),
  repositoryId: text("repository_id"),
  repositoryKey: text("repository_key"),
  publicationReference: jsonb("publication_reference"),
  targetReference: text("target_reference"),
  targetRevision: text("target_revision"),
  mergeReference: jsonb("merge_reference"),
  resultingRevision: text("resulting_revision"),
  revision: revision(),
  status: text("status").notNull().default("collecting"),
  createdAt: createdAt(),
});
export const reviews = pgTable("reviews", {
  id: text("id").primaryKey(),
  changeSetId: text("change_set_id").notNull(),
  candidateDigest: text("candidate_digest").notNull(),
  reviewerPrincipalId: text("reviewer_principal_id").notNull().default("system"),
  reviewerAgentRunId: text("reviewer_agent_run_id"),
  status: text("status").notNull().default("requested"),
  disposition: text("disposition"),
  createdAt: createdAt(),
});
export const verificationEvidence = pgTable("verification_evidence", {
  id: text("id").primaryKey(),
  changeSetId: text("change_set_id").notNull(),
  candidateDigest: text("candidate_digest").notNull(),
  name: text("name").notNull(),
  state: text("state").notNull(),
  source: text("source").notNull(),
  observedAt: timestamp("observed_at", { withTimezone: true, mode: "string" }).notNull(),
  required: boolean("required").notNull().default(true),
  reference: jsonb("reference"),
  details: jsonb("details"),
  createdAt: createdAt(),
});
export const reviewFindings = pgTable("review_findings", {
  id: text("id").primaryKey(),
  changeSetId: text("change_set_id").notNull(),
  candidateDigest: text("candidate_digest").notNull(),
  severity: text("severity").notNull(),
  summary: text("summary").notNull(),
  source: text("source"),
  resolved: boolean("resolved").notNull().default(false),
  createdAt: createdAt(),
});
export const sessions = pgTable(
  "sessions",
  {
    id: text("id").primaryKey(),
    tokenHash: text("token_hash").notNull(),
    principalId: text("principal_id").notNull(),
    credentialVersionDigest: text("credential_version_digest").notNull(),
    createdAt: createdAt(),
    lastSeenAt: timestamp("last_seen_at", { withTimezone: true, mode: "string" }).notNull(),
    expiresAt: timestamp("expires_at", { withTimezone: true, mode: "string" }).notNull(),
    revokedAt: timestamp("revoked_at", { withTimezone: true, mode: "string" }),
    userAgentDigest: text("user_agent_digest"),
  },
  (table) => [uniqueIndex("sessions_token_hash_unique").on(table.tokenHash)],
);

export const decisions = pgTable("decisions", {
  id: text("id").primaryKey(),
  projectId: text("project_id"),
  revision: revision(),
  createdAt: createdAt(),
});
export const approvals = pgTable("approvals", {
  id: text("id").primaryKey(),
  decisionId: text("decision_id"),
  revision: revision(),
  createdAt: createdAt(),
});
export const credentialReferences = pgTable("credential_references", {
  id: text("id").primaryKey(),
  secretStoreKey: text("secret_store_key").notNull(),
  secretVersion: text("secret_version"),
  createdAt: createdAt(),
});
export const credentialAuthorities = pgTable("credential_authorities", {
  connectionId: text("connection_id").primaryKey(),
  credentialReferenceId: text("credential_reference_id").notNull(),
  ownerId: text("owner_id").notNull(),
  pendingOwnerId: text("pending_owner_id"),
  generation: integer("generation").notNull(),
  status: text("status").notNull(),
  revision: revision(),
  createdAt: createdAt(),
});
export const connections = pgTable("connections", {
  id: text("id").primaryKey(),
  providerId: text("provider_id").notNull(),
  credentialReferenceId: text("credential_reference_id").notNull(),
  status: text("status").notNull(),
  accountIdentity: text("account_identity"),
  capabilities: jsonb("capabilities").notNull().default([]),
  resources: jsonb("resources").notNull().default([]),
  revision: revision(),
  createdAt: createdAt(),
});
export const configurationDefinitions = pgTable("configuration_definitions", {
  key: text("key").primaryKey(),
  schemaVersion: integer("schema_version").notNull(),
  definition: jsonb("definition").notNull(),
  createdAt: createdAt(),
});
export const configurationOverrides = pgTable(
  "configuration_overrides",
  {
    definitionKey: text("definition_key").notNull(),
    scopeType: text("scope_type").notNull(),
    scopeId: text("scope_id").notNull(),
    value: jsonb("value").notNull(),
    resourceRevision: revision(),
    setBy: text("set_by").notNull(),
    setAt: timestamp("set_at", { withTimezone: true, mode: "string" }).notNull(),
  },
  (table) => [
    uniqueIndex("configuration_overrides_scope_unique").on(
      table.definitionKey,
      table.scopeType,
      table.scopeId,
    ),
  ],
);
export const businessEvents = pgTable("business_events", {
  id: text("id").primaryKey(),
  type: text("type").notNull(),
  schemaVersion: integer("schema_version").notNull(),
  occurredAt: timestamp("occurred_at", { withTimezone: true, mode: "string" }).notNull(),
  aggregateType: text("aggregate_type").notNull(),
  aggregateId: text("aggregate_id").notNull(),
  aggregateRevision: integer("aggregate_revision").notNull(),
  projectId: text("project_id"),
  principalId: text("principal_id"),
  correlationId: text("correlation_id").notNull(),
  causationId: text("causation_id"),
  payload: jsonb("payload").notNull(),
});
export const auditRecords = pgTable("audit_records", {
  id: text("id").primaryKey(),
  occurredAt: timestamp("occurred_at", { withTimezone: true, mode: "string" }).notNull(),
  principalId: text("principal_id").notNull(),
  action: text("action").notNull(),
  targetType: text("target_type").notNull(),
  targetId: text("target_id").notNull(),
  projectId: text("project_id"),
  disposition: text("disposition").notNull(),
  policyId: text("policy_id"),
  policyRevision: integer("policy_revision"),
  decisionId: text("decision_id"),
  approvalId: text("approval_id"),
  correlationId: text("correlation_id").notNull(),
  safeMetadata: jsonb("safe_metadata").notNull().default({}),
});
export const outboxMessages = pgTable("outbox_messages", {
  id: text("id").primaryKey(),
  topic: text("topic").notNull(),
  payload: jsonb("payload").notNull(),
  occurredAt: timestamp("occurred_at", { withTimezone: true, mode: "string" }).notNull(),
  published: boolean("published").notNull().default(false),
  publishedAt: timestamp("published_at", { withTimezone: true, mode: "string" }),
  attempts: integer("attempts").notNull().default(0),
});
export const outboxConsumerReceipts = pgTable(
  "outbox_consumer_receipts",
  {
    consumerId: text("consumer_id").notNull(),
    messageId: text("message_id").notNull(),
    processedAt: timestamp("processed_at", { withTimezone: true, mode: "string" }).notNull(),
  },
  (table) => [primaryKey({ columns: [table.consumerId, table.messageId] })],
);

export const workflowStepOutcomes = pgTable(
  "workflow_step_outcomes",
  {
    operationId: text("operation_id").notNull(),
    stepName: text("step_name").notNull(),
    stepKey: text("step_key").notNull(),
    outcome: jsonb("outcome").notNull(),
    recordedAt: timestamp("recorded_at", { withTimezone: true, mode: "string" }).notNull(),
  },
  (table) => [primaryKey({ columns: [table.operationId, table.stepName, table.stepKey] })],
);

export const schema = {
  projects,
  projectVisionVersions,
  goals,
  plans,
  planRevisions,
  planningSessions,
  projectPlanningDefaults,
  tasks,
  taskDependencies,
  factoryRuns,
  agentRuns,
  workspaces,
  attempts,
  changeSets,
  reviews,
  verificationEvidence,
  reviewFindings,
  sessions,
  decisions,
  approvals,
  credentialReferences,
  credentialAuthorities,
  connections,
  configurationDefinitions,
  configurationOverrides,
  businessEvents,
  auditRecords,
  outboxMessages,
  outboxConsumerReceipts,
  workflowStepOutcomes,
};