/**
 * Unified schema file — all tables defined here.
 * drizzle-kit reads this file directly (avoids CJS/ESM extension issues).
 */

import {
  pgTable,
  pgEnum,
  text,
  timestamp,
  boolean,
  uuid,
  jsonb,
  index,
  uniqueIndex,
  real,
  integer,
  customType,
} from 'drizzle-orm/pg-core';

// ─── Auth (BetterAuth-compatible) ────────────────────────────────────────────

export const users = pgTable('users', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  emailVerified: boolean('email_verified').notNull().default(false),
  image: text('image'),
  role: text('role').notNull().default('member'),
  banned: boolean('banned').default(false),
  banReason: text('ban_reason'),
  banExpires: timestamp('ban_expires', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

export const sessions = pgTable(
  'sessions',
  {
    id: text('id').primaryKey(),
    expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
    token: text('token').notNull().unique(),
    ipAddress: text('ip_address'),
    userAgent: text('user_agent'),
    userId: text('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    // Auth hot path: look up all sessions for a user (BetterAuth session list).
    index('sessions_user_id_idx').on(t.userId),
    // Session expiry cleanup queries.
    index('sessions_expires_at_idx').on(t.expiresAt),
  ],
);

export const accounts = pgTable(
  'accounts',
  {
    id: text('id').primaryKey(),
    accountId: text('account_id').notNull(),
    providerId: text('provider_id').notNull(),
    userId: text('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    accessToken: text('access_token'),
    refreshToken: text('refresh_token'),
    idToken: text('id_token'),
    accessTokenExpiresAt: timestamp('access_token_expires_at', { withTimezone: true }),
    refreshTokenExpiresAt: timestamp('refresh_token_expires_at', { withTimezone: true }),
    scope: text('scope'),
    password: text('password'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    // BetterAuth looks up accounts by (provider_id, account_id) on OAuth callback.
    index('accounts_provider_account_idx').on(t.providerId, t.accountId),
    // Also used in session validation to find linked accounts for a user.
    index('accounts_user_id_idx').on(t.userId),
  ],
);

export const verifications = pgTable('verifications', {
  id: text('id').primaryKey(),
  identifier: text('identifier').notNull(),
  value: text('value').notNull(),
  expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

// ─── Admin API Tokens ───────────────────────────────────────────────────────

export const adminTokens = pgTable(
  'admin_tokens',
  {
    id: text('id').primaryKey(),
    userId: text('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    tokenHash: text('token_hash').notNull(),
    label: text('label').notNull(),
    scope: text('scope').notNull().default('admin'),
    expiresAt: timestamp('expires_at', { withTimezone: true }),
    lastUsedAt: timestamp('last_used_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('admin_tokens_user_id_idx').on(t.userId),
    uniqueIndex('admin_tokens_hash_idx').on(t.tokenHash),
  ],
);

// ─── Teams ───────────────────────────────────────────────────────────────────
// Declared before projects because projects references teams.

export const teams = pgTable('teams', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  slug: text('slug').notNull().unique(),
  ownerId: text('owner_id')
    .notNull()
    .references(() => users.id, { onDelete: 'restrict' }),
  managerId: text('manager_id')
    .notNull()
    .references(() => users.id, { onDelete: 'restrict' }),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

export const teamMembers = pgTable(
  'team_members',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    teamId: uuid('team_id')
      .notNull()
      .references(() => teams.id, { onDelete: 'cascade' }),
    userId: text('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    role: text('role', { enum: ['manager', 'member'] })
      .notNull()
      .default('member'),
    invitedBy: text('invited_by').references(() => users.id, { onDelete: 'set null' }),
    joinedAt: timestamp('joined_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    uniq: uniqueIndex('team_members_team_user_idx').on(t.teamId, t.userId),
  }),
);

// ─── Brain ───────────────────────────────────────────────────────────────────
// Declared before Chat because conversations references projects.

export const projects = pgTable('projects', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull(),
  description: text('description'),
  status: text('status', { enum: ['active', 'paused', 'completed', 'archived'] })
    .notNull()
    .default('active'),
  ownerId: text('owner_id').references(() => users.id, { onDelete: 'set null' }),
  teamId: uuid('team_id').references(() => teams.id, { onDelete: 'cascade' }),
  ownerType: text('owner_type', { enum: ['user', 'team'] })
    .notNull()
    .default('user'),
  metadata: jsonb('metadata'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

export const missions = pgTable(
  'missions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    name: text('name').notNull(),
    description: text('description'),
    status: text('status', { enum: ['planning', 'active', 'paused', 'completed', 'failed'] })
      .notNull()
      .default('planning'),
    projectId: uuid('project_id').references(() => projects.id, { onDelete: 'set null' }),
    userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
    phase: text('phase'),
    milestones: jsonb('milestones').$type<Record<string, unknown>[]>(),
    config: jsonb('config'),
    metadata: jsonb('metadata'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('missions_project_id_idx').on(t.projectId),
    index('missions_user_id_idx').on(t.userId),
  ],
);

export const tasks = pgTable(
  'tasks',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    title: text('title').notNull(),
    description: text('description'),
    status: text('status', {
      enum: ['not-started', 'in-progress', 'blocked', 'done', 'cancelled'],
    })
      .notNull()
      .default('not-started'),
    priority: text('priority', { enum: ['critical', 'high', 'medium', 'low'] })
      .notNull()
      .default('medium'),
    projectId: uuid('project_id').references(() => projects.id, { onDelete: 'set null' }),
    missionId: uuid('mission_id').references(() => missions.id, { onDelete: 'set null' }),
    assignee: text('assignee'),
    tags: jsonb('tags').$type<string[]>(),
    dueDate: timestamp('due_date', { withTimezone: true }),
    metadata: jsonb('metadata'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('tasks_project_id_idx').on(t.projectId),
    index('tasks_mission_id_idx').on(t.missionId),
    index('tasks_status_idx').on(t.status),
  ],
);

// ─── Coord Mission Tasks ─────────────────────────────────────────────────────
// Join table tracking coord-managed tasks within a mission.
// Scoped to userId for multi-tenant RBAC isolation.

export const missionTasks = pgTable(
  'mission_tasks',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    missionId: uuid('mission_id')
      .notNull()
      .references(() => missions.id, { onDelete: 'cascade' }),
    taskId: uuid('task_id').references(() => tasks.id, { onDelete: 'set null' }),
    userId: text('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    status: text('status', {
      enum: ['not-started', 'in-progress', 'blocked', 'done', 'cancelled'],
    })
      .notNull()
      .default('not-started'),
    description: text('description'),
    notes: text('notes'),
    pr: text('pr'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('mission_tasks_mission_id_idx').on(t.missionId),
    index('mission_tasks_task_id_idx').on(t.taskId),
    index('mission_tasks_user_id_idx').on(t.userId),
    index('mission_tasks_status_idx').on(t.status),
  ],
);

export const events = pgTable(
  'events',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    type: text('type').notNull(),
    title: text('title').notNull(),
    description: text('description'),
    date: timestamp('date', { withTimezone: true }).notNull().defaultNow(),
    metadata: jsonb('metadata'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('events_type_idx').on(t.type), index('events_date_idx').on(t.date)],
);

export const agents = pgTable(
  'agents',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    name: text('name').notNull(),
    provider: text('provider').notNull(),
    model: text('model').notNull(),
    status: text('status', { enum: ['idle', 'active', 'error', 'offline'] })
      .notNull()
      .default('idle'),
    projectId: uuid('project_id').references(() => projects.id, { onDelete: 'set null' }),
    ownerId: text('owner_id').references(() => users.id, { onDelete: 'set null' }),
    systemPrompt: text('system_prompt'),
    allowedTools: jsonb('allowed_tools').$type<string[]>(),
    skills: jsonb('skills').$type<string[]>(),
    isSystem: boolean('is_system').notNull().default(false),
    config: jsonb('config'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('agents_project_id_idx').on(t.projectId),
    index('agents_owner_id_idx').on(t.ownerId),
    index('agents_is_system_idx').on(t.isSystem),
  ],
);

export const tickets = pgTable(
  'tickets',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    title: text('title').notNull(),
    description: text('description'),
    status: text('status', { enum: ['open', 'in-progress', 'resolved', 'closed'] })
      .notNull()
      .default('open'),
    priority: text('priority', { enum: ['critical', 'high', 'medium', 'low'] })
      .notNull()
      .default('medium'),
    source: text('source'),
    metadata: jsonb('metadata'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('tickets_status_idx').on(t.status)],
);

export const appreciations = pgTable('appreciations', {
  id: uuid('id').primaryKey().defaultRandom(),
  fromUser: text('from_user'),
  toUser: text('to_user'),
  message: text('message').notNull(),
  metadata: jsonb('metadata'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});

// ─── Chat ────────────────────────────────────────────────────────────────────

export const conversations = pgTable(
  'conversations',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    title: text('title'),
    userId: text('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    projectId: uuid('project_id').references(() => projects.id, { onDelete: 'set null' }),
    agentId: uuid('agent_id').references(() => agents.id, { onDelete: 'set null' }),
    /** M5-004: Agent session ID bound to this conversation. Nullable — set when a session is created. */
    sessionId: text('session_id'),
    archived: boolean('archived').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    // Compound index for the most common query: conversations for a user filtered by archived.
    index('conversations_user_archived_idx').on(t.userId, t.archived),
    index('conversations_project_id_idx').on(t.projectId),
    index('conversations_agent_id_idx').on(t.agentId),
  ],
);

export const messages = pgTable(
  'messages',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    conversationId: uuid('conversation_id')
      .notNull()
      .references(() => conversations.id, { onDelete: 'cascade' }),
    role: text('role', { enum: ['user', 'assistant', 'system'] }).notNull(),
    content: text('content').notNull(),
    metadata: jsonb('metadata'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('messages_conversation_id_idx').on(t.conversationId)],
);

// ─── pgvector custom type ───────────────────────────────────────────────────

export const vector = customType<{
  data: number[];
  driverParam: string;
  config: { dimensions: number };
}>({
  dataType(config) {
    return `vector(${config?.dimensions ?? 1536})`;
  },
  fromDriver(value: unknown): number[] {
    const str = value as string;
    return str
      .slice(1, -1)
      .split(',')
      .map((v) => Number(v));
  },
  toDriver(value: number[]): string {
    return `[${value.join(',')}]`;
  },
});

// ─── Memory ─────────────────────────────────────────────────────────────────

export const preferences = pgTable(
  'preferences',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: text('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    key: text('key').notNull(),
    value: jsonb('value').notNull(),
    category: text('category', {
      enum: ['communication', 'coding', 'workflow', 'appearance', 'general'],
    })
      .notNull()
      .default('general'),
    source: text('source'),
    mutable: boolean('mutable').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    index('preferences_user_id_idx').on(t.userId),
    // Unique constraint enables single-round-trip INSERT … ON CONFLICT DO UPDATE.
    uniqueIndex('preferences_user_key_idx').on(t.userId, t.key),
  ],
);

export const insights = pgTable(
  'insights',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: text('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    content: text('content').notNull(),
    embedding: vector('embedding', { dimensions: 1536 }),
    source: text('source', {
      enum: ['agent', 'user', 'summarization', 'system'],
    })
      .notNull()
      .default('agent'),
    category: text('category', {
      enum: ['decision', 'learning', 'preference', 'fact', 'pattern', 'general'],
    })
      .notNull()
      .default('general'),
    relevanceScore: real('relevance_score').notNull().default(1.0),
    metadata: jsonb('metadata'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
    decayedAt: timestamp('decayed_at', { withTimezone: true }),
  },
  (t) => [
    index('insights_user_id_idx').on(t.userId),
    index('insights_category_idx').on(t.category),
    index('insights_relevance_idx').on(t.relevanceScore),
  ],
);

// ─── Agent Logs ─────────────────────────────────────────────────────────────

export const agentLogs = pgTable(
  'agent_logs',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    sessionId: text('session_id').notNull(),
    userId: text('user_id').references(() => users.id, { onDelete: 'set null' }),
    level: text('level', { enum: ['debug', 'info', 'warn', 'error'] })
      .notNull()
      .default('info'),
    category: text('category', {
      enum: ['decision', 'tool_use', 'learning', 'error', 'general'],
    })
      .notNull()
      .default('general'),
    content: text('content').notNull(),
    metadata: jsonb('metadata'),
    tier: text('tier', { enum: ['hot', 'warm', 'cold'] })
      .notNull()
      .default('hot'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    summarizedAt: timestamp('summarized_at', { withTimezone: true }),
    archivedAt: timestamp('archived_at', { withTimezone: true }),
  },
  (t) => [
    // Compound index for session log queries (most common: session + tier filter).
    index('agent_logs_session_tier_idx').on(t.sessionId, t.tier),
    index('agent_logs_user_id_idx').on(t.userId),
    // Used by summarization cron to find hot logs older than a cutoff.
    index('agent_logs_tier_created_at_idx').on(t.tier, t.createdAt),
  ],
);

// ─── Tess durable session state ─────────────────────────────────────────────
// PostgreSQL is canonical for restart-safe Tess session recovery. The state
// machine lives in @mosaicstack/agent; these records are its durable adapter.

export const interactionInboxStatusEnum = pgEnum('interaction_inbox_status', [
  'pending',
  'processing',
  'processed',
]);
export const interactionOutboxStatusEnum = pgEnum('interaction_outbox_status', [
  'pending',
  'processing',
  'delivered',
]);
export const interactionHandoffStatusEnum = pgEnum('interaction_handoff_status', [
  'pending',
  'accepted',
]);

export const interactionSessions = pgTable('interaction_sessions', {
  id: text('id').primaryKey(),
  agentName: text('agent_name').notNull(),
  tenantId: text('tenant_id').notNull(),
  ownerId: text('owner_id')
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  providerId: text('provider_id').notNull(),
  runtimeSessionId: text('runtime_session_id').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

export const interactionInbox = pgTable(
  'interaction_inbox',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    sessionId: text('session_id')
      .notNull()
      .references(() => interactionSessions.id, { onDelete: 'cascade' }),
    idempotencyKey: text('idempotency_key').notNull(),
    correlationId: text('correlation_id').notNull(),
    content: text('content').notNull(),
    contentDigest: text('content_digest').notNull(),
    status: interactionInboxStatusEnum('status').notNull().default('pending'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('interaction_inbox_session_idempotency_idx').on(t.sessionId, t.idempotencyKey),
    index('interaction_inbox_session_status_created_idx').on(t.sessionId, t.status, t.createdAt),
  ],
);

export const interactionOutbox = pgTable(
  'interaction_outbox',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    sessionId: text('session_id')
      .notNull()
      .references(() => interactionSessions.id, { onDelete: 'cascade' }),
    idempotencyKey: text('idempotency_key').notNull(),
    correlationId: text('correlation_id').notNull(),
    channelId: text('channel_id').notNull(),
    kind: text('kind').notNull(),
    content: text('content').notNull(),
    contentDigest: text('content_digest').notNull(),
    status: interactionOutboxStatusEnum('status').notNull().default('pending'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('interaction_outbox_session_idempotency_idx').on(t.sessionId, t.idempotencyKey),
    index('interaction_outbox_session_status_created_idx').on(t.sessionId, t.status, t.createdAt),
  ],
);

export const interactionCheckpoints = pgTable(
  'interaction_checkpoints',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    sessionId: text('session_id')
      .notNull()
      .references(() => interactionSessions.id, { onDelete: 'cascade' }),
    checkpointId: text('checkpoint_id').notNull(),
    contentDigest: text('content_digest').notNull(),
    cursor: text('cursor').notNull(),
    summary: text('summary').notNull(),
    compactionEpoch: integer('compaction_epoch').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex('interaction_checkpoints_session_idempotency_idx').on(t.sessionId, t.checkpointId),
    index('interaction_checkpoints_session_epoch_idx').on(t.sessionId, t.compactionEpoch),
  ],
);

export const interactionHandoffs = pgTable(
  'interaction_handoffs',
  {
    handoffId: text('handoff_id').primaryKey(),
    sessionId: text('session_id')
      .notNull()
      .references(() => interactionSessions.id, { onDelete: 'cascade' }),
    destination: text('destination').notNull(),
    correlationId: text('correlation_id').notNull(),
    checkpointId: text('checkpoint_id').notNull(),
    status: interactionHandoffStatusEnum('status').notNull().default('pending'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('interaction_handoffs_session_status_idx').on(t.sessionId, t.status)],
);

// ─── Skills ─────────────────────────────────────────────────────────────────

export const skills = pgTable(
  'skills',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    name: text('name').notNull().unique(),
    description: text('description'),
    version: text('version'),
    source: text('source', { enum: ['builtin', 'community', 'custom'] })
      .notNull()
      .default('custom'),
    config: jsonb('config'),
    enabled: boolean('enabled').notNull().default(true),
    installedBy: text('installed_by').references(() => users.id, { onDelete: 'set null' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('skills_enabled_idx').on(t.enabled)],
);

// ─── Routing Rules ──────────────────────────────────────────────────────────

export const routingRules = pgTable(
  'routing_rules',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    /** Human-readable rule name */
    name: text('name').notNull(),
    /** Lower number = higher priority; unique per scope */
    priority: integer('priority').notNull(),
    /** 'system' rules apply globally; 'user' rules are scoped to a specific user */
    scope: text('scope', { enum: ['system', 'user'] })
      .notNull()
      .default('system'),
    /** Null for system-scoped rules; FK to users.id for user-scoped rules */
    userId: text('user_id').references(() => users.id, { onDelete: 'cascade' }),
    /** Array of condition objects that must all match for the rule to fire */
    conditions: jsonb('conditions').notNull().$type<Record<string, unknown>[]>(),
    /** Routing action to take when all conditions are satisfied */
    action: jsonb('action').notNull().$type<Record<string, unknown>>(),
    /** Whether this rule is active */
    enabled: boolean('enabled').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    // Lookup by scope + priority for ordered rule evaluation
    index('routing_rules_scope_priority_idx').on(t.scope, t.priority),
    // User-scoped rules lookup
    index('routing_rules_user_id_idx').on(t.userId),
    // Filter enabled rules efficiently
    index('routing_rules_enabled_idx').on(t.enabled),
  ],
);

// ─── Provider Credentials ────────────────────────────────────────────────────

export const providerCredentials = pgTable(
  'provider_credentials',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: text('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    provider: text('provider').notNull(),
    credentialType: text('credential_type', { enum: ['api_key', 'oauth_token'] }).notNull(),
    encryptedValue: text('encrypted_value').notNull(),
    refreshToken: text('refresh_token'),
    expiresAt: timestamp('expires_at', { withTimezone: true }),
    metadata: jsonb('metadata'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    // Unique constraint: one credential entry per user per provider
    uniqueIndex('provider_credentials_user_provider_idx').on(t.userId, t.provider),
    index('provider_credentials_user_id_idx').on(t.userId),
  ],
);

// ─── Summarization Jobs ─────────────────────────────────────────────────────

export const summarizationJobs = pgTable(
  'summarization_jobs',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    status: text('status', { enum: ['pending', 'running', 'completed', 'failed'] })
      .notNull()
      .default('pending'),
    logsProcessed: integer('logs_processed').notNull().default(0),
    insightsCreated: integer('insights_created').notNull().default(0),
    errorMessage: text('error_message'),
    startedAt: timestamp('started_at', { withTimezone: true }),
    completedAt: timestamp('completed_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [index('summarization_jobs_status_idx').on(t.status)],
);

// ─── Fleet Backlog ────────────────────────────────────────────────────────────
// Mosaic-native backlog-of-record (card A4). This REPLACES the former Hermes
// adapter — there is NO runtime dependency on Hermes. Cards form a dependency
// DAG (`depends_on`), are claimed atomically by fleet workers via
// `SELECT ... FOR UPDATE SKIP LOCKED`, and auto-expire via a TTL so a crashed
// claimer's card returns to the pool.

/**
 * Lifecycle status of a backlog card.
 * - ready:   eligible to be claimed (once its deps are all `done`).
 * - claimed: a worker holds it (claim_owner + claimed_at set); may expire via TTL.
 * - blocked: explicitly parked; never auto-claimed.
 * - done:    completed; satisfies dependents.
 */
export const backlogStatusEnum = pgEnum('backlog_status', ['ready', 'claimed', 'blocked', 'done']);

export const backlog = pgTable(
  'backlog',
  {
    /** Stable, caller-supplied card id (e.g. "A4", "fleet-001"). PK. */
    id: text('id').primaryKey(),
    title: text('title').notNull(),
    body: text('body'),
    /** Board/phase grouping (e.g. "M1", "fleet"). Free-form. */
    phase: text('phase'),
    /** Higher number = higher priority; claim picks the max-priority ready card. */
    priority: integer('priority').notNull().default(0),
    status: backlogStatusEnum('status').notNull().default('ready'),
    /** DAG edges: ids of cards this one depends on. "ready" requires all done. */
    dependsOn: jsonb('depends_on').notNull().$type<string[]>().default([]),
    /** Owner token of the current claim (worker/agent id). NULL when unclaimed. */
    claimOwner: text('claim_owner'),
    /** TTL of the active claim in seconds. NULL when unclaimed. */
    claimTtlSeconds: integer('claim_ttl_seconds'),
    /** When the active claim was taken. NULL when unclaimed. claimed_at + ttl = expiry. */
    claimedAt: timestamp('claimed_at', { withTimezone: true }),
    /** Count of times this card has been claimed (incremented on each claim). */
    attempts: integer('attempts').notNull().default(0),
    /** Optional dedup key for `create`; a repeat key returns the existing card. */
    idempotencyKey: text('idempotency_key'),
    /** Acceptance criteria — free-form JSON (array of strings or object). */
    acceptance: jsonb('acceptance'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => [
    // Hot path: claim scans ready cards ordered by priority then age.
    index('backlog_status_priority_idx').on(t.status, t.priority),
    // reclaim sweeps claimed cards by claimed_at to find expired ones.
    index('backlog_status_claimed_at_idx').on(t.status, t.claimedAt),
    // Idempotent create dedups on this key (NULLs are distinct in Postgres, so
    // many unkeyed cards coexist; a repeated non-null key collides).
    uniqueIndex('backlog_idempotency_key_idx').on(t.idempotencyKey),
  ],
);

// ─── Federation ──────────────────────────────────────────────────────────────
// Enums declared before tables that reference them.
// All federation definitions live in this file (avoids CJS/ESM cross-import
// issues when drizzle-kit loads schema files via esbuild-register).
// Application code imports from `federation.ts` which re-exports from here.

/**
 * Lifecycle state of a federation peer.
 * - pending:   registered but not yet approved / TLS handshake not confirmed
 * - active:    fully operational; mTLS verified
 * - suspended: temporarily blocked; cert still valid
 * - revoked:   cert revoked; no traffic allowed
 */
export const peerStateEnum = pgEnum('peer_state', ['pending', 'active', 'suspended', 'revoked']);

/**
 * Lifecycle state of a federation grant.
 * - pending: created but not yet activated (awaiting cert enrollment, M2-07)
 * - active:  grant is in effect
 * - revoked: manually revoked before expiry
 * - expired: natural expiry (expires_at passed)
 */
export const grantStatusEnum = pgEnum('grant_status', ['pending', 'active', 'revoked', 'expired']);

/**
 * A registered peer gateway identified by its Step-CA certificate CN.
 * Represents both inbound peers (other gateways querying us) and outbound
 * peers (gateways we query — identified by client_key_pem being set).
 */
export const federationPeers = pgTable(
  'federation_peers',
  {
    id: uuid('id').primaryKey().defaultRandom(),

    /** Certificate CN, e.g. "gateway-uscllc-com". Unique — one row per peer identity. */
    commonName: text('common_name').notNull().unique(),

    /** Human-friendly label shown in admin UI. */
    displayName: text('display_name').notNull(),

    /** Pinned PEM certificate used for mTLS verification. */
    certPem: text('cert_pem').notNull(),

    /** Certificate serial number — used for CRL / revocation lookup. */
    certSerial: text('cert_serial').notNull().unique(),

    /** Certificate expiry — used by the renewal scheduler (FED-M6). */
    certNotAfter: timestamp('cert_not_after', { withTimezone: true }).notNull(),

    /**
     * Sealed (encrypted) private key for outbound connections TO this peer.
     * NULL for inbound-only peer rows (we serve them; we don't call them).
     */
    clientKeyPem: text('client_key_pem'),

    /** Current peer lifecycle state. */
    state: peerStateEnum('state').notNull().default('pending'),

    /** Base URL for outbound queries, e.g. "https://woltje.com:443". NULL for inbound-only peers. */
    endpointUrl: text('endpoint_url'),

    /** Timestamp of the most recent successful inbound or outbound request. */
    lastSeenAt: timestamp('last_seen_at', { withTimezone: true }),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),

    /** Populated when the cert is revoked; NULL while the peer is active. */
    revokedAt: timestamp('revoked_at', { withTimezone: true }),
  },
  (t) => [
    // CRL / revocation lookups by serial.
    index('federation_peers_cert_serial_idx').on(t.certSerial),
    // Filter peers by state (e.g. find all active peers for outbound routing).
    index('federation_peers_state_idx').on(t.state),
  ],
);

/**
 * A grant lets a specific peer cert query a specific local user's data within
 * a defined scope. Scopes are validated by JSON Schema in M2-03; this table
 * stores them as raw jsonb.
 */
export const federationGrants = pgTable(
  'federation_grants',
  {
    id: uuid('id').primaryKey().defaultRandom(),

    /**
     * The local user whose data this grant exposes.
     * Cascade delete: if the user account is deleted, revoke all their grants.
     */
    subjectUserId: text('subject_user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),

    /**
     * The peer gateway holding the grant.
     * Cascade delete: if the peer record is removed, the grant is moot.
     */
    peerId: uuid('peer_id')
      .notNull()
      .references(() => federationPeers.id, { onDelete: 'cascade' }),

    /**
     * Scope object — validated by JSON Schema (M2-03).
     * Example: { "resources": ["tasks", "notes"], "operations": ["list", "get"] }
     */
    scope: jsonb('scope').notNull(),

    /** Current grant lifecycle state. */
    status: grantStatusEnum('status').notNull().default('pending'),

    /** Optional hard expiry. NULL means the grant does not expire automatically. */
    expiresAt: timestamp('expires_at', { withTimezone: true }),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),

    /** Populated when the grant is explicitly revoked. */
    revokedAt: timestamp('revoked_at', { withTimezone: true }),

    /** Human-readable reason for revocation (audit trail). */
    revokedReason: text('revoked_reason'),
  },
  (t) => [
    // Hot path: look up active grants for a subject user (auth middleware).
    index('federation_grants_subject_status_idx').on(t.subjectUserId, t.status),
    // Hot path: look up active grants held by a peer (inbound request check).
    index('federation_grants_peer_status_idx').on(t.peerId, t.status),
  ],
);

/**
 * Append-only audit log of all federation requests.
 * M4 writes rows here. M2 only creates the table.
 *
 * All FKs use SET NULL so audit rows survive peer/user/grant deletion.
 */
export const federationAuditLog = pgTable(
  'federation_audit_log',
  {
    id: uuid('id').primaryKey().defaultRandom(),

    /** UUIDv7 from the X-Request-ID header — correlates with OTEL traces. */
    requestId: text('request_id').notNull(),

    /** Peer that made the request. SET NULL if the peer is later deleted. */
    peerId: uuid('peer_id').references(() => federationPeers.id, { onDelete: 'set null' }),

    /** Subject user whose data was queried. SET NULL if the user is deleted. */
    subjectUserId: text('subject_user_id').references(() => users.id, { onDelete: 'set null' }),

    /** Grant under which the request was authorised. SET NULL if the grant is deleted. */
    grantId: uuid('grant_id').references(() => federationGrants.id, { onDelete: 'set null' }),

    /** Request verb: "list" | "get" | "search". */
    verb: text('verb').notNull(),

    /** Resource type: "tasks" | "notes" | "memory" | etc. */
    resource: text('resource').notNull(),

    /** HTTP status code returned to the peer. */
    statusCode: integer('status_code').notNull(),

    /** Number of items returned (NULL for non-list requests or errors). */
    resultCount: integer('result_count'),

    /** Why the request was denied (NULL when allowed). */
    deniedReason: text('denied_reason'),

    /** End-to-end latency in milliseconds. */
    latencyMs: integer('latency_ms'),

    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),

    // Reserved for M4 — see PRD 7.3
    /** SHA-256 of the normalised GraphQL/REST query string; written by M4 search. */
    queryHash: text('query_hash'),
    /** Request outcome: "allowed" | "denied" | "partial"; written by M4. */
    outcome: text('outcome'),
    /** Response payload size in bytes; written by M4. */
    bytesOut: integer('bytes_out'),
  },
  (t) => [
    // Per-peer request history in reverse chronological order.
    index('federation_audit_log_peer_created_at_idx').on(t.peerId, t.createdAt.desc()),
    // Per-user access log in reverse chronological order.
    index('federation_audit_log_subject_created_at_idx').on(t.subjectUserId, t.createdAt.desc()),
    // Global time-range scans (dashboards, rate-limit windows).
    index('federation_audit_log_created_at_idx').on(t.createdAt.desc()),
  ],
);

/**
 * Single-use enrollment tokens — M2-07.
 *
 * An admin creates a token (with a TTL) and hands it out-of-band to the
 * remote peer operator.  The peer redeems it exactly once by posting its
 * CSR to POST /api/federation/enrollment/:token.  The token is atomically
 * marked as used to prevent replay attacks.
 */
export const federationEnrollmentTokens = pgTable('federation_enrollment_tokens', {
  /** 32-byte hex token — crypto.randomBytes(32).toString('hex') */
  token: text('token').primaryKey(),

  /** The federation grant this enrollment activates. */
  grantId: uuid('grant_id')
    .notNull()
    .references(() => federationGrants.id, { onDelete: 'cascade' }),

  /** The peer record that will be updated on successful enrollment. */
  peerId: uuid('peer_id')
    .notNull()
    .references(() => federationPeers.id, { onDelete: 'cascade' }),

  /** Hard expiry — token rejected after this time even if not used. */
  expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),

  /** NULL until the token is redeemed.  Set atomically to prevent replay. */
  usedAt: timestamp('used_at', { withTimezone: true }),

  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});
