/**
 * FileRefService — pure DB repository for the `file_ref` polymorphic table.
 *
 * Phase status: Phase 1b.2 lands all read + mutation methods.
 *
 * ## Scope
 *
 * - **Pure DB.** Queries and mutations only; no dangling / orphan awareness.
 *   OrphanRefScanner in Phase 1b.4 is a separate service that *uses* this one.
 * - **Polymorphic sourceType keying.** No FK constraint on `sourceId` (see
 *   file schema). Producers MUST pass a `FileRefSourceType` literal that
 *   appears in the central registry (`src/shared/data/types/file/ref/index.ts`);
 *   schema variants for non-`temp_session` sourceTypes are registered
 *   incrementally in Phase 1b.2.
 *
 * ## Pull-model cleanup
 *
 * `cleanupBySource(sourceType, sourceId)` is the canonical delete hook —
 * business delete flows (ChatService, KnowledgeItemService, etc.) call it
 * when the source entity is removed. OrphanRefScanner (Phase 1b.4) is the
 * belt-and-suspenders safety net for missed paths.
 */

import { application } from '@application'
import { fileRefTable } from '@data/db/schemas/file'
import type { DbType } from '@data/db/types'
import type { FileEntryId, FileRef, FileRefSourceType } from '@shared/data/types/file'
import { FileRefSchema } from '@shared/data/types/file'
import { and, asc, count, eq, inArray } from 'drizzle-orm'
import { v4 as uuidv4 } from 'uuid'

export interface FileRefSourceKey {
  readonly sourceType: FileRefSourceType
  readonly sourceId: string
}

export interface CreateFileRefRow extends FileRefSourceKey {
  readonly fileEntryId: FileEntryId
  readonly role: string
}

export interface FileRefService {
  /** All refs pointing at a given file_entry. Respects CASCADE — deleted entries return `[]`. */
  findByEntryId(fileEntryId: FileEntryId): Promise<FileRef[]>

  /** All refs owned by a business source (chat message, knowledge item, …). */
  findBySource(source: FileRefSourceKey): Promise<FileRef[]>

  /**
   * Insert a new ref. Violating `file_ref_unique_idx` (same entry + source +
   * role) throws — callers SHOULD upsert by catching and re-querying, or use
   * `createMany` with on-conflict-ignore semantics.
   */
  create(values: CreateFileRefRow): Promise<FileRef>

  /** Batch variant. Rows that violate the uniqueness constraint are skipped. */
  createMany(values: readonly CreateFileRefRow[]): Promise<FileRef[]>

  /**
   * Transaction-aware source clone helper. Used when a business entity is
   * copied and its existing file ownership rows must be cloned to the new source
   * ids. Original source rows are not removed or reassigned. Uniqueness
   * conflicts indicate a broken source-id map and fail the caller's transaction.
   */
  copyBySourceIdMapTx(
    tx: Pick<DbType, 'select' | 'insert'>,
    sourceType: FileRefSourceType,
    sourceIdMap: ReadonlyMap<string, string>
  ): Promise<void>

  /**
   * Pull-model cleanup: remove all refs owned by the given source. Called
   * when the business entity itself is deleted. Thin wrapper that opens its
   * own transaction around {@link FileRefService.cleanupBySourceTx}.
   */
  cleanupBySource(source: FileRefSourceKey): Promise<number>

  /**
   * Transaction-aware variant of {@link FileRefService.cleanupBySource}. Lets
   * an owning service delete its row AND its file refs in one atomic boundary
   * (tx-first, `Tx` suffix — same convention as `TagService.purgeForEntityTx`).
   */
  cleanupBySourceTx(tx: Pick<DbType, 'delete'>, source: FileRefSourceKey): Promise<number>

  /** Batch variant of `cleanupBySource` — one `DELETE … IN (…)` per sourceType. */
  cleanupBySourceBatch(sourceType: FileRefSourceType, sourceIds: readonly string[]): Promise<number>

  /**
   * Distinct `sourceId` values currently held by refs of the given sourceType.
   * Backs OrphanRefScanner — the only consumer.
   */
  listDistinctSourceIds(sourceType: FileRefSourceType): Promise<string[]>

  /**
   * Pure-SQL ref-count aggregation for a batch of entry ids — `COUNT(*) … GROUP BY
   * fileEntryId`, chunked against SQLite's `IN (?, …)` parameter cap. Entries
   * with no refs are absent from the map; callers should treat missing keys as
   * zero.
   */
  countByEntryIds(ids: readonly FileEntryId[]): Promise<Map<FileEntryId, number>>
}

/**
 * SQLite parameter cap is configurable but defaults to 999; keep batches well
 * under that for `inArray()` even with comparison overhead. Same constant lives
 * in `orphanCheckerRegistry.knowledgeItemChecker` — kept lexically separate
 * because the two callers can diverge as their query shapes evolve.
 */
const SQLITE_INARRAY_CHUNK = 500
const SQLITE_INSERT_CHUNK = 100

type FileRefRow = typeof fileRefTable.$inferSelect

function rowToFileRef(row: FileRefRow): FileRef {
  return FileRefSchema.parse(row)
}

class FileRefServiceImpl implements FileRefService {
  private getDb() {
    return application.get('DbService').getDb()
  }

  async findByEntryId(fileEntryId: FileEntryId): Promise<FileRef[]> {
    const rows = await this.getDb()
      .select()
      .from(fileRefTable)
      .where(eq(fileRefTable.fileEntryId, fileEntryId))
      // Deterministic order so paginated / diff'd callers get stable results;
      // SQLite returns rows in arbitrary order without ORDER BY, which makes
      // tests flaky on rebuilds and observation noise indistinguishable from
      // real change. Tiebreaker on `id` keeps duplicate-createdAt batches
      // ordered consistently.
      .orderBy(asc(fileRefTable.createdAt), asc(fileRefTable.id))
    return rows.map(rowToFileRef)
  }

  async findBySource(source: FileRefSourceKey): Promise<FileRef[]> {
    const rows = await this.getDb()
      .select()
      .from(fileRefTable)
      .where(and(eq(fileRefTable.sourceType, source.sourceType), eq(fileRefTable.sourceId, source.sourceId)))
      .orderBy(asc(fileRefTable.createdAt), asc(fileRefTable.id))
    return rows.map(rowToFileRef)
  }

  async create(values: CreateFileRefRow): Promise<FileRef> {
    const now = Date.now()
    const rows = await this.getDb()
      .insert(fileRefTable)
      .values({
        id: uuidv4(),
        fileEntryId: values.fileEntryId,
        sourceType: values.sourceType,
        sourceId: values.sourceId,
        role: values.role,
        createdAt: now,
        updatedAt: now
      })
      .returning()
    return rowToFileRef(rows[0])
  }

  async createMany(values: readonly CreateFileRefRow[]): Promise<FileRef[]> {
    if (values.length === 0) return []
    const now = Date.now()
    const rows = await this.getDb()
      .insert(fileRefTable)
      .values(
        values.map((v) => ({
          id: uuidv4(),
          fileEntryId: v.fileEntryId,
          sourceType: v.sourceType,
          sourceId: v.sourceId,
          role: v.role,
          createdAt: now,
          updatedAt: now
        }))
      )
      .onConflictDoNothing()
      .returning()
    return rows.map(rowToFileRef)
  }

  async copyBySourceIdMapTx(
    tx: Pick<DbType, 'select' | 'insert'>,
    sourceType: FileRefSourceType,
    sourceIdMap: ReadonlyMap<string, string>
  ): Promise<void> {
    if (sourceIdMap.size === 0) return

    const sourceIds = [...sourceIdMap.keys()]
    const now = Date.now()

    for (let i = 0; i < sourceIds.length; i += SQLITE_INARRAY_CHUNK) {
      const chunk = sourceIds.slice(i, i + SQLITE_INARRAY_CHUNK)
      const sourceRefs = await tx
        .select()
        .from(fileRefTable)
        .where(and(eq(fileRefTable.sourceType, sourceType), inArray(fileRefTable.sourceId, chunk)))

      const values = sourceRefs.flatMap((ref) => {
        const copiedSourceId = sourceIdMap.get(ref.sourceId)
        if (!copiedSourceId) return []
        return [
          {
            id: uuidv4(),
            fileEntryId: ref.fileEntryId,
            sourceType,
            sourceId: copiedSourceId,
            role: ref.role,
            createdAt: now,
            updatedAt: now
          }
        ]
      })
      if (values.length === 0) continue

      for (let j = 0; j < values.length; j += SQLITE_INSERT_CHUNK) {
        await tx.insert(fileRefTable).values(values.slice(j, j + SQLITE_INSERT_CHUNK))
      }
    }
  }

  async cleanupBySource(source: FileRefSourceKey): Promise<number> {
    return this.getDb().transaction((tx) => this.cleanupBySourceTx(tx, source))
  }

  async cleanupBySourceTx(tx: Pick<DbType, 'delete'>, source: FileRefSourceKey): Promise<number> {
    const rows = await tx
      .delete(fileRefTable)
      .where(and(eq(fileRefTable.sourceType, source.sourceType), eq(fileRefTable.sourceId, source.sourceId)))
      .returning({ id: fileRefTable.id })
    return rows.length
  }

  async cleanupBySourceBatch(sourceType: FileRefSourceType, sourceIds: readonly string[]): Promise<number> {
    if (sourceIds.length === 0) return 0
    let total = 0
    // SQLite caps `IN (?, ?, …)` at SQLITE_LIMIT_VARIABLE_NUMBER (default 999;
    // sometimes 32766). Chunk so a long-tenured user with thousands of
    // orphaned source ids doesn't blow up the single-statement DELETE.
    for (let i = 0; i < sourceIds.length; i += SQLITE_INARRAY_CHUNK) {
      const chunk = sourceIds.slice(i, i + SQLITE_INARRAY_CHUNK)
      const rows = await this.getDb()
        .delete(fileRefTable)
        .where(and(eq(fileRefTable.sourceType, sourceType), inArray(fileRefTable.sourceId, chunk)))
        .returning({ id: fileRefTable.id })
      total += rows.length
    }
    return total
  }

  async listDistinctSourceIds(sourceType: FileRefSourceType): Promise<string[]> {
    const rows = await this.getDb()
      .selectDistinct({ sourceId: fileRefTable.sourceId })
      .from(fileRefTable)
      .where(eq(fileRefTable.sourceType, sourceType))
    return rows.map((r) => r.sourceId)
  }

  async countByEntryIds(ids: readonly FileEntryId[]): Promise<Map<FileEntryId, number>> {
    const counts = new Map<FileEntryId, number>()
    if (ids.length === 0) return counts
    for (let i = 0; i < ids.length; i += SQLITE_INARRAY_CHUNK) {
      const chunk = ids.slice(i, i + SQLITE_INARRAY_CHUNK)
      const rows = await this.getDb()
        .select({
          entryId: fileRefTable.fileEntryId,
          refCount: count()
        })
        .from(fileRefTable)
        .where(inArray(fileRefTable.fileEntryId, chunk))
        .groupBy(fileRefTable.fileEntryId)
      for (const r of rows) counts.set(r.entryId, r.refCount)
    }
    return counts
  }
}

export const fileRefService: FileRefService = new FileRefServiceImpl()
