import { sql } from 'drizzle-orm'
import { check, index, integer, sqliteTable, text, uniqueIndex } from 'drizzle-orm/sqlite-core'
import { agent } from './agent-tables'
import { user } from './auth-tables'

export const agentIdentity = sqliteTable(
  'agent_identity',
  {
    id: text('id').primaryKey(),
    issuer: text('issuer').notNull(),
    subject: text('subject').notNull(),
    username: text('username'),
    name: text('name').notNull(),
    runtime: text('runtime'),
    ownerUserId: text('owner_user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'restrict' }),
    status: text('status', { enum: ['active', 'inactive'] })
      .notNull()
      .default('active'),
    deletedAt: integer('deleted_at', { mode: 'timestamp_ms' }),
    createdAt: integer('created_at', { mode: 'timestamp_ms' }).notNull(),
    updatedAt: integer('updated_at', { mode: 'timestamp_ms' }).notNull(),
  },
  (table) => [
    // Stable Agent identities are historical principals and must never be reassigned after deletion.
    uniqueIndex('agentIdentity_issuer_subject_unique').on(table.issuer, table.subject),
    uniqueIndex('agentIdentity_username_unique').on(table.username).where(sql`${table.username} is not null`),
    index('agentIdentity_ownerUserId_idx').on(table.ownerUserId),
    index('agentIdentity_status_idx').on(table.status),
    index('agentIdentity_deletedAt_idx').on(table.deletedAt),
  ],
)

export const agentIdentityBinding = sqliteTable(
  'agent_identity_binding',
  {
    id: text('id').primaryKey(),
    agentIdentityId: text('agent_identity_id')
      .notNull()
      .references(() => agentIdentity.id, { onDelete: 'restrict' }),
    protocolAgentId: text('protocol_agent_id')
      .notNull()
      .references(() => agent.id, { onDelete: 'restrict' }),
    status: text('status').notNull().default('active'),
    boundAt: integer('bound_at', { mode: 'timestamp_ms' }).notNull(),
    revokedAt: integer('revoked_at', { mode: 'timestamp_ms' }),
    createdAt: integer('created_at', { mode: 'timestamp_ms' }).notNull(),
    updatedAt: integer('updated_at', { mode: 'timestamp_ms' }).notNull(),
  },
  (table) => [
    uniqueIndex('agentIdentityBinding_protocolAgentId_unique').on(table.protocolAgentId),
    index('agentIdentityBinding_agentIdentityId_idx').on(table.agentIdentityId),
    index('agentIdentityBinding_status_idx').on(table.status),
  ],
)

export const agentApplicationCreation = sqliteTable(
  'agent_application_creation',
  {
    id: text('id').primaryKey(),
    // The authenticated Application identifier is an immutable invocation-boundary value, not an Application row FK.
    applicationId: text('application_id').notNull(),
    actorUserId: text('actor_user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'restrict' }),
    idempotencyKey: text('idempotency_key').notNull(),
    requestFingerprint: text('request_fingerprint').notNull(),
    agentIdentityId: text('agent_identity_id')
      .notNull()
      .references(() => agentIdentity.id, { onDelete: 'restrict' }),
    createdAt: integer('created_at', { mode: 'timestamp_ms' }).notNull(),
  },
  (table) => [
    uniqueIndex('agentApplicationCreation_applicationActorKey_unique').on(
      table.applicationId,
      table.actorUserId,
      table.idempotencyKey,
    ),
    uniqueIndex('agentApplicationCreation_agentIdentityId_unique').on(table.agentIdentityId),
  ],
)

export const agentEnrollmentIntent = sqliteTable(
  'agent_enrollment_intent',
  {
    id: text('id').primaryKey(),
    agentIdentityId: text('agent_identity_id').references(() => agentIdentity.id, { onDelete: 'restrict' }),
    requestedName: text('requested_name'),
    requestedUsername: text('requested_username'),
    requestedRuntime: text('requested_runtime'),
    ownerUserId: text('owner_user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'restrict' }),
    protocolAgentId: text('protocol_agent_id')
      .notNull()
      .references(() => agent.id, { onDelete: 'restrict' }),
    idempotencyKey: text('idempotency_key'),
    status: text('status').notNull().default('pending'),
    createdByUserId: text('created_by_user_id')
      .notNull()
      .references(() => user.id, { onDelete: 'restrict' }),
    approvedByUserId: text('approved_by_user_id').references(() => user.id, { onDelete: 'restrict' }),
    expiresAt: integer('expires_at', { mode: 'timestamp_ms' }).notNull(),
    approvedAt: integer('approved_at', { mode: 'timestamp_ms' }),
    createdAt: integer('created_at', { mode: 'timestamp_ms' }).notNull(),
    updatedAt: integer('updated_at', { mode: 'timestamp_ms' }).notNull(),
  },
  (table) => [
    index('agentEnrollmentIntent_agentIdentityId_idx').on(table.agentIdentityId),
    index('agentEnrollmentIntent_protocolAgentId_idx').on(table.protocolAgentId),
    uniqueIndex('agentEnrollmentIntent_protocolAgentIdempotencyKey_uidx')
      .on(table.protocolAgentId, table.idempotencyKey)
      .where(sql`${table.idempotencyKey} is not null`),
    index('agentEnrollmentIntent_status_idx').on(table.status),
    index('agentEnrollmentIntent_expiresAt_idx').on(table.expiresAt),
    check(
      'agentEnrollmentIntent_new_or_existing_identity_check',
      sql`(${table.agentIdentityId} is not null) or ((${table.requestedName} is not null) and (${table.requestedUsername} is not null) and (${table.requestedRuntime} is not null))`,
    ),
  ],
)