/**
 * Assistant Service - handles assistant CRUD operations
 *
 * Provides business logic for:
 * - Assistant CRUD operations
 * - Listing with optional filters
 */

import { application } from '@application'
import { assistantTable } from '@data/db/schemas/assistant'
import { assistantKnowledgeBaseTable, assistantMcpServerTable } from '@data/db/schemas/assistantRelations'
import { pinTable } from '@data/db/schemas/pin'
import { userModelTable } from '@data/db/schemas/userModel'
import type { DbType } from '@data/db/types'
import { loggerService } from '@logger'
import { DataApiError, DataApiErrorFactory, ErrorCode } from '@shared/data/api'
import type { OrderRequest } from '@shared/data/api/schemas/_endpointHelpers'
import type { CreateAssistantDto, ListAssistantsQuery, UpdateAssistantDto } from '@shared/data/api/schemas/assistants'
import type { EntitySearchItem } from '@shared/data/api/schemas/search'
import { type Assistant, DEFAULT_ASSISTANT_SETTINGS } from '@shared/data/types/assistant'
import type { UniqueModelId } from '@shared/data/types/model'
import type { Tag } from '@shared/data/types/tag'
import { and, asc, desc, eq, gte, inArray, isNull, or, type SQL, sql } from 'drizzle-orm'

import { modelService } from './ModelService'
import { pinService } from './PinService'
import { tagService } from './TagService'
import { applyMoves, insertWithOrderKey } from './utils/orderKey'
import { nullsToUndefined, timestampToISO } from './utils/rowMappers'

const logger = loggerService.withContext('DataApi:AssistantService')

type AssistantRow = typeof assistantTable.$inferSelect

type AssistantRelationIds = Pick<Assistant, 'mcpServerIds' | 'knowledgeBaseIds'>
type AssistantEntitySearchItem = Extract<EntitySearchItem, { type: 'assistant' }>
type AssistantRowWithModelName = {
  assistant: AssistantRow
  modelName: string | null
}

function createEmptyRelations(): AssistantRelationIds {
  return {
    mcpServerIds: [],
    knowledgeBaseIds: []
  }
}

function rowToAssistant(
  row: AssistantRow,
  relations: AssistantRelationIds = createEmptyRelations(),
  tags: Tag[] = [],
  modelName: string | null = null
): Assistant {
  const clean = nullsToUndefined(row)
  return {
    ...clean,
    // Preserve the T | null contract: `modelId` is legitimately nullable (R3 exception).
    modelId: row.modelId as UniqueModelId | null,
    mcpServerIds: relations.mcpServerIds,
    knowledgeBaseIds: relations.knowledgeBaseIds,
    createdAt: timestampToISO(row.createdAt),
    updatedAt: timestampToISO(row.updatedAt),
    tags,
    modelName
  }
}

function buildSearchPredicate(q: string | undefined): SQL | undefined {
  const trimmed = q?.trim()
  if (!trimmed) return undefined

  const pattern = `%${trimmed.replace(/[\\%_]/g, '\\$&')}%`
  const nameMatch = sql`${assistantTable.name} LIKE ${pattern} ESCAPE '\\'`
  const descMatch = sql`${assistantTable.description} LIKE ${pattern} ESCAPE '\\'`

  return or(nameMatch, descMatch)
}

function rethrowAssistantOrderError(error: unknown): never {
  if (error instanceof DataApiError && error.code === ErrorCode.NOT_FOUND && error.details?.resource === 'assistant') {
    throw DataApiErrorFactory.notFound('Assistant', error.details.id)
  }

  throw error
}

export class AssistantDataService {
  private get db() {
    return application.get('DbService').getDb()
  }

  private async getActiveRowById(id: string): Promise<AssistantRow> {
    const [row] = await this.db
      .select()
      .from(assistantTable)
      .where(and(eq(assistantTable.id, id), isNull(assistantTable.deletedAt)))
      .limit(1)

    if (!row) {
      throw DataApiErrorFactory.notFound('Assistant', id)
    }

    return row
  }

  private async getActiveRowWithModelNameById(
    id: string,
    db: Pick<DbType, 'select'> = this.db
  ): Promise<AssistantRowWithModelName> {
    const [row] = await db
      .select({ assistant: assistantTable, modelName: userModelTable.name })
      .from(assistantTable)
      .leftJoin(userModelTable, eq(assistantTable.modelId, userModelTable.id))
      .where(and(eq(assistantTable.id, id), isNull(assistantTable.deletedAt)))
      .limit(1)

    if (!row) {
      throw DataApiErrorFactory.notFound('Assistant', id)
    }

    return {
      assistant: row.assistant,
      modelName: row.modelName || null
    }
  }

  /**
   * Resolve the effective `modelId` for a create request.
   * v2 transition: this replaces the legacy pattern where the renderer
   * looked up Redux `state.llm.defaultModel` and pushed a UniqueModelId
   * that was not guaranteed to exist in `user_model`.
   */
  private async resolveCreateModelId(
    tx: Pick<DbType, 'select'>,
    dtoModelId: string | null | undefined
  ): Promise<string | null> {
    if (dtoModelId !== undefined) {
      if (dtoModelId && !(await modelService.findByIdTx(tx, dtoModelId))) {
        throw DataApiErrorFactory.validation(
          { modelId: [`Model '${dtoModelId}' is not registered in user_model`] },
          `Assistant modelId '${dtoModelId}' is not registered — add the model first or pass null`
        )
      }
      return dtoModelId
    }
    const preferred = application.get('PreferenceService').get('chat.default_model_id') ?? null
    if (!preferred) return null

    if (!(await modelService.findByIdTx(tx, preferred))) {
      logger.warn('chat.default_model_id is stale; creating assistant without a bound model', {
        preferred
      })
      return null
    }
    return preferred
  }

  private async getRelationIdsByAssistantIds(assistantIds: string[]): Promise<Map<string, AssistantRelationIds>> {
    const relationMap = new Map<string, AssistantRelationIds>()

    if (assistantIds.length === 0) {
      return relationMap
    }

    for (const assistantId of assistantIds) {
      relationMap.set(assistantId, createEmptyRelations())
    }

    const [mcpServerRows, knowledgeBaseRows] = await Promise.all([
      this.db
        .select({ assistantId: assistantMcpServerTable.assistantId, mcpServerId: assistantMcpServerTable.mcpServerId })
        .from(assistantMcpServerTable)
        .where(inArray(assistantMcpServerTable.assistantId, assistantIds))
        .orderBy(asc(assistantMcpServerTable.assistantId), asc(assistantMcpServerTable.createdAt)),
      this.db
        .select({
          assistantId: assistantKnowledgeBaseTable.assistantId,
          knowledgeBaseId: assistantKnowledgeBaseTable.knowledgeBaseId
        })
        .from(assistantKnowledgeBaseTable)
        .where(inArray(assistantKnowledgeBaseTable.assistantId, assistantIds))
        .orderBy(asc(assistantKnowledgeBaseTable.assistantId), asc(assistantKnowledgeBaseTable.createdAt))
    ])

    for (const row of mcpServerRows) {
      relationMap.get(row.assistantId)?.mcpServerIds.push(row.mcpServerId)
    }
    for (const row of knowledgeBaseRows) {
      relationMap.get(row.assistantId)?.knowledgeBaseIds.push(row.knowledgeBaseId)
    }

    return relationMap
  }

  /**
   * Get an assistant by ID.
   * @param options.includeDeleted - If true, also returns soft-deleted assistants (for historical display)
   */
  async getById(id: string, options?: { includeDeleted?: boolean }): Promise<Assistant> {
    const conditions = [eq(assistantTable.id, id)]
    if (!options?.includeDeleted) {
      conditions.push(isNull(assistantTable.deletedAt))
    }
    const [row] = await this.db
      .select({ assistant: assistantTable, modelName: userModelTable.name })
      .from(assistantTable)
      .leftJoin(userModelTable, eq(assistantTable.modelId, userModelTable.id))
      .where(and(...conditions))
      .limit(1)
    if (!row) {
      throw DataApiErrorFactory.notFound('Assistant', id)
    }
    const [relations, tags] = await Promise.all([
      this.getRelationIdsByAssistantIds([id]),
      tagService.getTagsByEntitiesTx(this.db, 'assistant', [id])
    ])
    return rowToAssistant(row.assistant, relations.get(id), tags.get(id), row.modelName || null)
  }

  async search(query: { q: string; limit: number; updatedAtFrom?: number }): Promise<AssistantEntitySearchItem[]> {
    const conditions: SQL[] = [isNull(assistantTable.deletedAt)]
    const searchClause = buildSearchPredicate(query.q)
    if (searchClause) conditions.push(searchClause)
    if (query.updatedAtFrom !== undefined) {
      conditions.push(gte(assistantTable.updatedAt, query.updatedAtFrom))
    }

    const rows = await this.db
      .select({
        id: assistantTable.id,
        name: assistantTable.name,
        description: assistantTable.description,
        emoji: assistantTable.emoji,
        updatedAt: assistantTable.updatedAt
      })
      .from(assistantTable)
      .where(and(...conditions))
      .orderBy(desc(assistantTable.updatedAt), asc(assistantTable.id))
      .limit(query.limit)

    return rows.map((row) => ({
      type: 'assistant',
      id: row.id,
      title: row.name,
      subtitle: row.description || undefined,
      emoji: row.emoji,
      updatedAt: timestampToISO(row.updatedAt),
      target: { assistantId: row.id }
    }))
  }

  /**
   * List assistants with optional filters.
   *
   * Filter composition:
   * - `id` / `search` / `tagIds` AND together (tag-scoped text search).
   * - `search` runs LIKE %kw% against `name` OR `description` (case-insensitive
   *   for ASCII, byte-wise substring for CJK — both expected by the UI).
   *   SQLite LIKE wildcards (`%`/`_`) in the raw input are escaped.
   * - `tagIds` uses a correlated subquery on `entity_tag` for union semantics:
   *   an assistant is kept if it has ANY of the given tag ids. Kept in the
   *   WHERE clause (not a JOIN) so pagination's `count(*)` stays correct
   *   without `DISTINCT` gymnastics.
   *
   * `page` and `limit` are filled by the schema default — no runtime fallback.
   */
  async list(query: ListAssistantsQuery): Promise<{ items: Assistant[]; total: number; page: number }> {
    const { page, limit } = query
    const offset = (page - 1) * limit

    const conditions: SQL[] = [isNull(assistantTable.deletedAt)]
    if (query.id !== undefined) {
      conditions.push(eq(assistantTable.id, query.id))
    }
    if (query.search) {
      const pattern = `%${query.search.replace(/[\\%_]/g, '\\$&')}%`
      // `\` escape clause so literal %/_ typed by the user don't act as wildcards.
      const nameMatch = sql`${assistantTable.name} LIKE ${pattern} ESCAPE '\\'`
      const descMatch = sql`${assistantTable.description} LIKE ${pattern} ESCAPE '\\'`
      const searchClause = or(nameMatch, descMatch)
      if (searchClause) conditions.push(searchClause)
    }
    if (query.updatedAtFrom !== undefined) {
      conditions.push(gte(assistantTable.updatedAt, Date.parse(query.updatedAtFrom)))
    }
    if (query.tagIds && query.tagIds.length > 0) {
      const assistantIds = await tagService.getEntityIdsByTagsTx(this.db, 'assistant', query.tagIds)
      conditions.push(assistantIds.length > 0 ? inArray(assistantTable.id, assistantIds) : sql`0 = 1`)
    }

    const whereClause = and(...conditions)
    const sortBy = query.sortBy ?? 'orderKey'
    const sortOrder = query.sortOrder ?? (sortBy === 'orderKey' || sortBy === 'name' ? 'asc' : 'desc')
    const orderFn = sortOrder === 'asc' ? asc : desc
    const sortByToColumn = {
      createdAt: assistantTable.createdAt,
      updatedAt: assistantTable.updatedAt,
      name: assistantTable.name,
      orderKey: assistantTable.orderKey
    } as const
    const sortColumn = sortByToColumn[sortBy] ?? assistantTable.orderKey
    const orderByClauses =
      sortBy === 'updatedAt'
        ? [orderFn(sortColumn), asc(assistantTable.id)]
        : [
            sql`CASE WHEN ${pinTable.orderKey} IS NULL THEN 1 ELSE 0 END`,
            asc(pinTable.orderKey),
            orderFn(sortColumn),
            asc(assistantTable.createdAt)
          ]

    // Pin-aware ordering is the default library view: pinned rows first by
    // pin.orderKey, then the requested secondary sort. Freshness queries
    // (`sortBy=updatedAt`) deliberately bypass pins so incremental consumers get
    // strict timestamp ordering.
    const [rows, [{ count }]] = await Promise.all([
      this.db
        .select({ assistant: assistantTable, modelName: userModelTable.name, pinOrderKey: pinTable.orderKey })
        .from(assistantTable)
        .leftJoin(userModelTable, eq(assistantTable.modelId, userModelTable.id))
        .leftJoin(pinTable, and(eq(pinTable.entityType, 'assistant'), eq(pinTable.entityId, assistantTable.id)))
        .where(whereClause)
        .orderBy(...orderByClauses)
        .limit(limit)
        .offset(offset),
      this.db.select({ count: sql<number>`count(*)` }).from(assistantTable).where(whereClause)
    ])

    const assistantIds = rows.map((row) => row.assistant.id)
    const [relations, tags] = await Promise.all([
      this.getRelationIdsByAssistantIds(assistantIds),
      tagService.getTagsByEntitiesTx(this.db, 'assistant', assistantIds)
    ])
    const items = rows.map((row) =>
      rowToAssistant(row.assistant, relations.get(row.assistant.id), tags.get(row.assistant.id), row.modelName || null)
    )

    return {
      items,
      total: Number(count),
      page
    }
  }

  /**
   * Create a new assistant.
   *
   * `tagIds`, `mcpServerIds`, `knowledgeBaseIds` all land inside the same
   * transaction as the insert — one failed binding rolls the assistant row
   * back so callers never observe a half-written record.
   */
  async create(dto: CreateAssistantDto): Promise<Assistant> {
    this.validateName(dto.name)

    const { row, tags, modelName } = await application.get('DbService').withWriteTx(async (tx) => {
      // Resolve modelId: explicit values strictly validated; omission falls
      // back to `chat.default_model_id` preference (stale → null with a
      // logger.warn).
      const modelId = await this.resolveCreateModelId(tx, dto.modelId)

      // Split relation/tag fields from columns. Service owns emoji/settings
      // defaults; prompt/description stay omitted when undefined so DB DEFAULTs apply.
      // orderKey is omitted — `insertWithOrderKey` computes the next fractional
      // key from the existing max and injects it before the DB write.
      const { mcpServerIds, knowledgeBaseIds, tagIds, ...columnDto } = dto
      const insertValues = {
        ...columnDto,
        modelId,
        emoji: dto.emoji ?? '🌟',
        settings: dto.settings ?? DEFAULT_ASSISTANT_SETTINGS
      } satisfies Omit<typeof assistantTable.$inferInsert, 'orderKey'>

      const inserted = (await insertWithOrderKey(tx, assistantTable, insertValues, {
        pkColumn: assistantTable.id,
        scope: isNull(assistantTable.deletedAt)
      })) as AssistantRow

      // Insert junction table rows
      await this.syncRelationsTx(tx, inserted.id, { mcpServerIds, knowledgeBaseIds })

      if (tagIds !== undefined) {
        await tagService.syncEntityTagsTx(tx, 'assistant', inserted.id, tagIds)
      }

      // Re-read the bound tags inside the tx so the response reflects the
      // freshly-written bindings (name/color/timestamps all resolved in one trip).
      const tagMap = await tagService.getTagsByEntitiesTx(tx, 'assistant', [inserted.id])
      const readRow = await this.getActiveRowWithModelNameById(inserted.id, tx)
      return {
        row: readRow.assistant,
        tags: tagMap.get(inserted.id) ?? [],
        modelName: readRow.modelName
      }
    })

    logger.info('Created assistant', { id: row.id, name: row.name })

    return rowToAssistant(
      row,
      {
        mcpServerIds: dto.mcpServerIds ?? [],
        knowledgeBaseIds: dto.knowledgeBaseIds ?? []
      },
      tags,
      modelName
    )
  }

  /**
   * Update an existing assistant.
   *
   * Column write, junction-table syncs (mcpServer / knowledgeBase / tag) all
   * run under one transaction so save-time failures cannot leave the entity
   * desynced from its bindings.
   *
   * **Soft-delete TOCTOU guard**: every write inside the transaction is gated
   * by `isNull(deletedAt)`. If another window soft-deleted the assistant
   * between the entry `getById` and the transaction, we throw `NOT_FOUND` and
   * roll back — the client can never observe a "saved successfully" response
   * on an already-deleted row.
   */
  async update(id: string, dto: UpdateAssistantDto): Promise<Assistant> {
    const current = await this.getById(id)

    if (dto.name !== undefined) {
      this.validateName(dto.name)
    }

    // Strip relation fields — these are synced to junction tables, not assistant columns
    const { mcpServerIds, knowledgeBaseIds, tagIds, settings: settingsPatch, ...columnFields } = dto
    const updates = Object.fromEntries(Object.entries(columnFields).filter(([, v]) => v !== undefined)) as Partial<
      typeof assistantTable.$inferInsert
    >
    if (settingsPatch !== undefined) {
      updates.settings = { ...current.settings, ...settingsPatch }
    }
    const hasColumnUpdates = Object.keys(updates).length > 0
    const hasRelationUpdates = mcpServerIds !== undefined || knowledgeBaseIds !== undefined
    const hasTagUpdate = tagIds !== undefined

    if (!hasColumnUpdates && !hasRelationUpdates && !hasTagUpdate) {
      return current
    }

    const nextRelations: AssistantRelationIds = {
      mcpServerIds: mcpServerIds ?? current.mcpServerIds,
      knowledgeBaseIds: knowledgeBaseIds ?? current.knowledgeBaseIds
    }

    const aliveFilter = and(eq(assistantTable.id, id), isNull(assistantTable.deletedAt))

    const { row, tags, modelName } = await application.get('DbService').withWriteTx(async (tx) => {
      // Pre-validate the new FK target before any write — same reasoning as
      // in `create`. Skipped when the caller is unbinding (null) or leaving
      // the existing modelId untouched (undefined/empty).
      if (dto.modelId && !(await modelService.findByIdTx(tx, dto.modelId))) {
        throw DataApiErrorFactory.validation(
          { modelId: [`Model '${dto.modelId}' is not registered in user_model`] },
          `Assistant modelId '${dto.modelId}' is not registered — add the model first or pass null`
        )
      }

      let next: AssistantRow
      if (hasColumnUpdates) {
        const [updated] = await tx.update(assistantTable).set(updates).where(aliveFilter).returning()
        if (!updated) {
          throw DataApiErrorFactory.notFound('Assistant', id)
        }
        next = updated
      } else {
        // Relation-only / tag-only edits still need the same liveness guard,
        // otherwise a concurrent soft-delete would let us write junction rows
        // against a deleted assistant.
        const [existing] = await tx.select().from(assistantTable).where(aliveFilter).limit(1)
        if (!existing) {
          throw DataApiErrorFactory.notFound('Assistant', id)
        }
        next = existing
      }

      // Sync junction table rows if relation fields are provided
      await this.syncRelationsTx(tx, id, { mcpServerIds, knowledgeBaseIds })

      if (hasTagUpdate) {
        await tagService.syncEntityTagsTx(tx, 'assistant', id, tagIds)
      }

      // Re-read bound tags inside the tx when they were touched; otherwise
      // reuse the snapshot taken on entry (saves a query on column-only edits).
      const nextTags = hasTagUpdate
        ? ((await tagService.getTagsByEntitiesTx(tx, 'assistant', [id])).get(id) ?? [])
        : current.tags

      const nextModelName =
        dto.modelId !== undefined && dto.modelId !== current.modelId
          ? (await this.getActiveRowWithModelNameById(id, tx)).modelName
          : current.modelName

      return { row: next, tags: nextTags, modelName: nextModelName }
    })

    logger.info('Updated assistant', { id, changes: Object.keys(dto) })

    return rowToAssistant(row, nextRelations, tags, modelName)
  }

  /** Move a single assistant within the active (non-deleted) assistant list. */
  async reorder(id: string, anchor: OrderRequest): Promise<void> {
    try {
      await application.get('DbService').withWriteTx(async (tx) => {
        await applyMoves(tx, assistantTable, [{ id, anchor }], {
          pkColumn: assistantTable.id,
          scope: isNull(assistantTable.deletedAt)
        })
      })
    } catch (error) {
      rethrowAssistantOrderError(error)
    }
  }

  /** Apply multiple assistant moves atomically within the active assistant list. */
  async reorderBatch(moves: Array<{ id: string; anchor: OrderRequest }>): Promise<void> {
    if (moves.length === 0) return

    try {
      await application.get('DbService').withWriteTx(async (tx) => {
        await applyMoves(tx, assistantTable, moves, {
          pkColumn: assistantTable.id,
          scope: isNull(assistantTable.deletedAt)
        })
      })
    } catch (error) {
      rethrowAssistantOrderError(error)
    }
  }

  /**
   * Soft-delete an assistant (sets deletedAt timestamp).
   * The row is preserved so topic.assistantId FK remains valid
   * and junction table data (mcpServers, knowledgeBases) is retained.
   * Tag bindings are intentionally removed during delete, so restoring a
   * soft-deleted assistant does not restore its previous tags.
   */
  async delete(id: string): Promise<void> {
    await this.getActiveRowById(id)

    await application.get('DbService').withWriteTx(async (tx) => {
      await tx.update(assistantTable).set({ deletedAt: Date.now() }).where(eq(assistantTable.id, id))
      await tagService.purgeForEntityTx(tx, 'assistant', id)
      await pinService.purgeForEntityTx(tx, 'assistant', id)
    })

    logger.info('Soft-deleted assistant', { id })
  }

  /**
   * Sync junction table rows for an assistant.
   * If an array is provided, it replaces all existing rows (delete + insert).
   * If undefined, the existing rows are left unchanged.
   * Runs within the caller's transaction for atomicity.
   */
  private async syncRelationsTx(
    tx: Pick<DbType, 'delete' | 'insert' | 'select'>,
    assistantId: string,
    dto: { mcpServerIds?: string[]; knowledgeBaseIds?: string[] }
  ): Promise<void> {
    if (dto.mcpServerIds !== undefined) {
      const existing = await tx
        .select({ mcpServerId: assistantMcpServerTable.mcpServerId })
        .from(assistantMcpServerTable)
        .where(eq(assistantMcpServerTable.assistantId, assistantId))
      const existingIds = new Set(existing.map((r) => r.mcpServerId))
      const desiredIds = new Set(dto.mcpServerIds)

      const removeIds = existing.filter((r) => !desiredIds.has(r.mcpServerId)).map((r) => r.mcpServerId)
      const toAdd = dto.mcpServerIds.filter((id) => !existingIds.has(id))

      if (removeIds.length > 0) {
        await tx
          .delete(assistantMcpServerTable)
          .where(
            and(
              eq(assistantMcpServerTable.assistantId, assistantId),
              inArray(assistantMcpServerTable.mcpServerId, removeIds)
            )
          )
      }
      if (toAdd.length > 0) {
        await tx.insert(assistantMcpServerTable).values(toAdd.map((mcpServerId) => ({ assistantId, mcpServerId })))
      }
    }

    if (dto.knowledgeBaseIds !== undefined) {
      const existing = await tx
        .select({ knowledgeBaseId: assistantKnowledgeBaseTable.knowledgeBaseId })
        .from(assistantKnowledgeBaseTable)
        .where(eq(assistantKnowledgeBaseTable.assistantId, assistantId))
      const existingIds = new Set(existing.map((r) => r.knowledgeBaseId))
      const desiredIds = new Set(dto.knowledgeBaseIds)

      const removeIds = existing.filter((r) => !desiredIds.has(r.knowledgeBaseId)).map((r) => r.knowledgeBaseId)
      const toAdd = dto.knowledgeBaseIds.filter((id) => !existingIds.has(id))

      if (removeIds.length > 0) {
        await tx
          .delete(assistantKnowledgeBaseTable)
          .where(
            and(
              eq(assistantKnowledgeBaseTable.assistantId, assistantId),
              inArray(assistantKnowledgeBaseTable.knowledgeBaseId, removeIds)
            )
          )
      }
      if (toAdd.length > 0) {
        await tx
          .insert(assistantKnowledgeBaseTable)
          .values(toAdd.map((knowledgeBaseId) => ({ assistantId, knowledgeBaseId })))
      }
    }
  }

  private validateName(name: string): void {
    if (!name?.trim()) {
      throw DataApiErrorFactory.validation({ name: ['Name is required'] })
    }
  }
}

export const assistantDataService = new AssistantDataService()
