/**
 * Knowledge Base query helpers.
 *
 * All helpers are tenant-filtered. Route files MUST NOT import raw Drizzle tables
 * for KB data — they import from this module.
 *
 * Two namespaces:
 *   - kbTenantQuery(db, tenantId).* — staff access (all articles incl DRAFT)
 *   - kbPortalQuery(db, tenantId, customerId).* — portal access (PUBLISHED + accessible vaults only)
 */
import { and, eq, inArray, isNull, or, asc, sql, count } from 'drizzle-orm'
import type { Db } from '../client'
import {
  assertTenantOwnsOrThrow,
  assertTenantOwnsCustomer,
  assertTenantOwnsKbSpace,
  assertTenantOwnsKbArticle,
} from './tenant-guards'
import { KbSpaceHasArticlesError } from './kb-spaces'
import { kbSpaces, kbArticles, kbAttachments, customers } from '../schema'
import type {
  KbArticleRow,
  KbAttachmentRow,
  NewKbSpace,
  NewKbArticle,
  NewKbAttachment,
} from '../schema'
// @zync/types re-exports kb symbols from its barrel (added by controller post-wave)
import {
  serializeKbSpace,
  serializeKbArticle,
  serializeKbAttachment,
  duplicateSlug,
  MAX_TREE_DEPTH,
} from '@zync/types'
import type {
  KbSpace,
  KbArticle,
  KbArticleNode,
  KbAttachment,
} from '@zync/types'

// ── Tree builder ──────────────────────────────────────────────────────────────

function buildArticleTree(rows: KbArticleRow[]): KbArticleNode[] {
  const map = new Map<string, KbArticleNode>()
  const roots: KbArticleNode[] = []

  // First pass: create nodes
  for (const row of rows) {
    map.set(row.id, { ...serializeKbArticle(row), children: [] })
  }

  // Second pass: attach children to parents
  for (const row of rows) {
    const node = map.get(row.id)!
    if (row.parentId && map.has(row.parentId)) {
      map.get(row.parentId)!.children.push(node)
    } else {
      roots.push(node)
    }
  }

  return roots
}

// ── Staff query namespace ────────────────────────────────────────────────────

export function kbTenantQuery(db: Db, tenantId: string) {
  return {
    // ── Customer validation ──────────────────────────────────────────────────

    async verifyCustomerBelongsToTenant(customerId: string): Promise<boolean> {
      const [row] = await db
        .select({ id: customers.id })
        .from(customers)
        .where(and(eq(customers.tenantId, tenantId), eq(customers.id, customerId)))
        .limit(1)
      return !!row
    },

    // ── Spaces ──────────────────────────────────────────────────────────────

    async listSpaces(): Promise<KbSpace[]> {
      const rows = await db
        .select({
          id: kbSpaces.id,
          tenantId: kbSpaces.tenantId,
          name: kbSpaces.name,
          slug: kbSpaces.slug,
          type: kbSpaces.type,
          customerId: kbSpaces.customerId,
          icon: kbSpaces.icon,
          isPublic: kbSpaces.isPublic,
          description: kbSpaces.description,
          position: kbSpaces.position,
          createdBy: kbSpaces.createdBy,
          createdAt: kbSpaces.createdAt,
          customerName: customers.name,
          articleCount: count(kbArticles.id),
        })
        .from(kbSpaces)
        .leftJoin(
          kbArticles,
          and(eq(kbArticles.spaceId, kbSpaces.id), isNull(kbArticles.deletedAt)),
        )
        .leftJoin(customers, eq(kbSpaces.customerId, customers.id))
        .where(eq(kbSpaces.tenantId, tenantId))
        .groupBy(kbSpaces.id, customers.name)
        .orderBy(asc(kbSpaces.position), asc(kbSpaces.name))

      return rows.map((r) =>
        serializeKbSpace({
          ...r,
          articleCount: Number(r.articleCount),
        }),
      )
    },

    async getSpaceBySlug(slug: string): Promise<KbSpace | null> {
      const [row] = await db
        .select()
        .from(kbSpaces)
        .where(and(eq(kbSpaces.tenantId, tenantId), eq(kbSpaces.slug, slug)))
        .limit(1)
      return row ? serializeKbSpace(row) : null
    },

    async getSpaceById(id: string): Promise<KbSpace | null> {
      const [row] = await db
        .select()
        .from(kbSpaces)
        .where(and(eq(kbSpaces.tenantId, tenantId), eq(kbSpaces.id, id)))
        .limit(1)
      return row ? serializeKbSpace(row) : null
    },

    async createSpace(
      input: Omit<NewKbSpace, 'id' | 'tenantId' | 'createdAt'>,
    ): Promise<KbSpace> {
      const [row] = await db
        .insert(kbSpaces)
        .values({ ...input, tenantId })
        .returning()
      if (!row) throw new Error('Space not found after insert')
      return serializeKbSpace(row)
    },

    async updateSpace(id: string, patch: Partial<Omit<NewKbSpace, 'id' | 'tenantId' | 'createdAt' | 'type'>>): Promise<KbSpace> {
      if (patch.customerId !== undefined) {
        assertTenantOwnsOrThrow(
          'customer_id',
          await assertTenantOwnsCustomer(db, tenantId, patch.customerId),
        )
      }

      const [row] = await db
        .update(kbSpaces)
        .set(patch)
        .where(and(eq(kbSpaces.tenantId, tenantId), eq(kbSpaces.id, id)))
        .returning()
      if (!row) throw new Error('Space not found')
      return serializeKbSpace(row)
    },

    async deleteSpace(id: string): Promise<void> {
      const countResult = await db
        .select({ articleCount: count(kbArticles.id) })
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.spaceId, id),
            isNull(kbArticles.deletedAt),
          ),
        )

      const n = Number(countResult[0]?.articleCount ?? 0)
      if (n > 0) throw new KbSpaceHasArticlesError(n)

      const deleted = await db
        .delete(kbSpaces)
        .where(and(eq(kbSpaces.tenantId, tenantId), eq(kbSpaces.id, id)))
        .returning({ id: kbSpaces.id })

      if (deleted.length === 0) throw new Error('Space not found')
    },

    // ── Articles ─────────────────────────────────────────────────────────────

    async listArticleTree(spaceId: string): Promise<KbArticleNode[]> {
      const rows = await db
        .select()
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.spaceId, spaceId),
            isNull(kbArticles.deletedAt),
          ),
        )
        .orderBy(asc(kbArticles.position))
      return buildArticleTree(rows)
    },

    async getArticleById(id: string): Promise<KbArticle | null> {
      const [row] = await db
        .select()
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.id, id),
            isNull(kbArticles.deletedAt),
          ),
        )
        .limit(1)
      return row ? serializeKbArticle(row) : null
    },

    async getArticleBySlug(spaceSlug: string, articleSlug: string): Promise<KbArticle | null> {
      const [space] = await db
        .select({ id: kbSpaces.id })
        .from(kbSpaces)
        .where(and(eq(kbSpaces.tenantId, tenantId), eq(kbSpaces.slug, spaceSlug)))
        .limit(1)
      if (!space) return null

      const [row] = await db
        .select()
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.spaceId, space.id),
            eq(kbArticles.slug, articleSlug),
            isNull(kbArticles.deletedAt),
          ),
        )
        .limit(1)
      return row ? serializeKbArticle(row) : null
    },

    async createArticle(
      input: Omit<NewKbArticle, 'id' | 'tenantId' | 'createdAt' | 'updatedAt' | 'viewCount'>,
    ): Promise<KbArticle> {
      assertTenantOwnsOrThrow(
        'space_id',
        await assertTenantOwnsKbSpace(db, tenantId, input.spaceId),
      )
      assertTenantOwnsOrThrow(
        'parent_id',
        await assertTenantOwnsKbArticle(db, tenantId, input.parentId),
      )

      const newDepth = input.parentId ? (await this.treeDepth(input.parentId)) + 1 : 1
      if (newDepth > MAX_TREE_DEPTH) {
        throw new Error(`Move would exceed maximum tree depth of ${MAX_TREE_DEPTH}`)
      }

      const [row] = await db
        .insert(kbArticles)
        .values({ ...input, tenantId, viewCount: 0 })
        .returning()
      if (!row) throw new Error('Article not found after insert')
      return serializeKbArticle(row)
    },

    async updateArticle(id: string, patch: Partial<Omit<NewKbArticle, 'id' | 'tenantId'>>): Promise<KbArticle> {
      const [row] = await db
        .update(kbArticles)
        .set({ ...patch, updatedAt: new Date() })
        .where(and(eq(kbArticles.tenantId, tenantId), eq(kbArticles.id, id)))
        .returning()
      if (!row) throw new Error('Article not found')
      return serializeKbArticle(row)
    },

    async deleteArticle(id: string): Promise<void> {
      await db
        .delete(kbArticles)
        .where(and(eq(kbArticles.tenantId, tenantId), eq(kbArticles.id, id)))
    },

    async siblingArticles(
      spaceId: string,
      parentId: string | null,
      excludeId: string,
      limit = 5,
    ): Promise<KbArticle[]> {
      const parentCondition = parentId
        ? eq(kbArticles.parentId, parentId)
        : isNull(kbArticles.parentId)

      const rows = await db
        .select()
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.spaceId, spaceId),
            parentCondition,
            isNull(kbArticles.deletedAt),
            sql`${kbArticles.id} != ${excludeId}`,
          ),
        )
        .orderBy(asc(kbArticles.position))
        .limit(limit)

      return rows.map(serializeKbArticle)
    },

    async incrementViewCount(id: string): Promise<void> {
      await db
        .update(kbArticles)
        .set({ viewCount: sql`${kbArticles.viewCount} + 1` })
        .where(and(eq(kbArticles.tenantId, tenantId), eq(kbArticles.id, id)))
    },

    async getLastSiblingPosition(
      spaceId: string,
      parentId: string | null,
    ): Promise<number | null> {
      const parentCondition = parentId
        ? eq(kbArticles.parentId, parentId)
        : isNull(kbArticles.parentId)

      const [row] = await db
        .select({ position: kbArticles.position })
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.spaceId, spaceId),
            parentCondition,
          ),
        )
        .orderBy(sql`${kbArticles.position} DESC`)
        .limit(1)

      if (!row) return null
      return typeof row.position === 'string' ? parseFloat(row.position) : (row.position as unknown as number)
    },

    async listArticlesBySpaceIds(spaceIds: string[]): Promise<KbArticle[]> {
      if (spaceIds.length === 0) return []
      const rows = await db
        .select()
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            inArray(kbArticles.spaceId, spaceIds),
          ),
        )
      return rows.map(serializeKbArticle)
    },

    // ── Attachments ──────────────────────────────────────────────────────────

    async listAttachments(articleId: string): Promise<KbAttachment[]> {
      const rows = await db
        .select()
        .from(kbAttachments)
        .where(
          and(eq(kbAttachments.tenantId, tenantId), eq(kbAttachments.articleId, articleId)),
        )
        .orderBy(asc(kbAttachments.createdAt))
      return rows.map(serializeKbAttachment)
    },

    async createAttachment(
      input: Omit<NewKbAttachment, 'id' | 'tenantId' | 'createdAt'>,
    ): Promise<KbAttachmentRow> {
      const [row] = await db
        .insert(kbAttachments)
        .values({ ...input, tenantId })
        .returning()
      if (!row) throw new Error('Attachment not found after insert')
      return row
    },

    async getAttachment(id: string): Promise<KbAttachmentRow | null> {
      const [row] = await db
        .select()
        .from(kbAttachments)
        .where(and(eq(kbAttachments.tenantId, tenantId), eq(kbAttachments.id, id)))
        .limit(1)
      return row ?? null
    },

    // ── kb-article-editor additions ──────────────────────────────────────────

    async publishArticle(id: string, userId: string): Promise<KbArticle> {
      const [row] = await db
        .update(kbArticles)
        .set({
          status: 'PUBLISHED',
          publishedAt: new Date(),
          updatedBy: userId,
          updatedAt: new Date(),
        })
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.id, id),
            isNull(kbArticles.deletedAt),
          ),
        )
        .returning()
      if (!row) throw new Error('Article not found')
      return serializeKbArticle(row)
    },

    async unpublishArticle(id: string, userId: string): Promise<KbArticle> {
      const [row] = await db
        .update(kbArticles)
        .set({
          status: 'DRAFT',
          publishedAt: null,
          updatedBy: userId,
          updatedAt: new Date(),
        })
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.id, id),
            isNull(kbArticles.deletedAt),
          ),
        )
        .returning()
      if (!row) throw new Error('Article not found')
      return serializeKbArticle(row)
    },

    /**
     * Compute the depth of an article within the tree (root = 1).
     * Returns 0 if article not found.
     */
    async treeDepth(articleId: string): Promise<number> {
      let depth = 0
      let currentId: string | null = articleId

      while (currentId) {
        const [row] = await db
          .select({ parentId: kbArticles.parentId })
          .from(kbArticles)
          .where(
            and(
              eq(kbArticles.tenantId, tenantId),
              eq(kbArticles.id, currentId),
              isNull(kbArticles.deletedAt),
            ),
          )
          .limit(1)

        if (!row) break
        depth++
        currentId = row.parentId ?? null
      }

      return depth
    },

    /**
     * Compute the maximum height of the subtree rooted at articleId.
     * A leaf node has height 1; a node with one level of children has height 2.
     */
    async subtreeHeight(articleId: string): Promise<number> {
      // Load all live articles in the space as a flat list and compute locally
      const [article] = await db
        .select({ spaceId: kbArticles.spaceId })
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.id, articleId),
            isNull(kbArticles.deletedAt),
          ),
        )
        .limit(1)

      if (!article) return 0

      const allRows = await db
        .select({ id: kbArticles.id, parentId: kbArticles.parentId })
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.spaceId, article.spaceId),
            isNull(kbArticles.deletedAt),
          ),
        )

      // Build parent→children map
      const children = new Map<string, string[]>()
      for (const row of allRows) {
        if (row.parentId) {
          const list = children.get(row.parentId) ?? []
          list.push(row.id)
          children.set(row.parentId, list)
        }
      }

      function height(id: string): number {
        const kids = children.get(id) ?? []
        if (kids.length === 0) return 1
        return 1 + Math.max(...kids.map(height))
      }

      return height(articleId)
    },

    async duplicateArticle(id: string, userId: string): Promise<KbArticle> {
      const [source] = await db
        .select()
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.id, id),
            isNull(kbArticles.deletedAt),
          ),
        )
        .limit(1)

      if (!source) throw new Error('Article not found')

      // Uniquify the slug within the space
      let candidateSlug = duplicateSlug(source.slug)
      let suffix = 2
      while (true) {
        const [existing] = await db
          .select({ id: kbArticles.id })
          .from(kbArticles)
          .where(
            and(
              eq(kbArticles.spaceId, source.spaceId),
              eq(kbArticles.slug, candidateSlug),
              isNull(kbArticles.deletedAt),
            ),
          )
          .limit(1)
        if (!existing) break
        candidateSlug = `${source.slug}-copy-${suffix}`
        suffix++
      }

      // Position after last sibling (simple increment — no cross-package dep)
      const lastPosition = await this.getLastSiblingPosition(
        source.spaceId,
        source.parentId,
      )
      const position = (lastPosition ?? 0) + 1

      const [row] = await db
        .insert(kbArticles)
        .values({
          tenantId,
          spaceId: source.spaceId,
          parentId: source.parentId,
          title: source.title,
          slug: candidateSlug,
          content: source.content,
          status: 'DRAFT',
          position: String(position),
          viewCount: 0,
          publishedAt: null,
          metaTitle: source.metaTitle,
          metaDescription: source.metaDescription,
          createdBy: userId,
          updatedBy: null,
        })
        .returning()

      if (!row) throw new Error('Failed to duplicate article')
      return serializeKbArticle(row)
    },

    async softDeleteArticle(id: string, userId: string): Promise<void> {
      // Soft-delete the article and its entire subtree
      // Load all live articles in the space to find subtree
      const [article] = await db
        .select({ spaceId: kbArticles.spaceId })
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.id, id),
            isNull(kbArticles.deletedAt),
          ),
        )
        .limit(1)

      if (!article) return

      const allRows = await db
        .select({ id: kbArticles.id, parentId: kbArticles.parentId })
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.spaceId, article.spaceId),
            isNull(kbArticles.deletedAt),
          ),
        )

      // Collect subtree IDs (BFS)
      const children = new Map<string, string[]>()
      for (const row of allRows) {
        if (row.parentId) {
          const list = children.get(row.parentId) ?? []
          list.push(row.id)
          children.set(row.parentId, list)
        }
      }

      const toDelete: string[] = []
      const queue = [id]
      while (queue.length > 0) {
        const current = queue.shift()!
        toDelete.push(current)
        const kids = children.get(current) ?? []
        queue.push(...kids)
      }

      const now = new Date()
      // Batch soft-delete all subtree nodes
      for (const articleId of toDelete) {
        await db
          .update(kbArticles)
          .set({ deletedAt: now, updatedBy: userId, updatedAt: now })
          .where(and(eq(kbArticles.tenantId, tenantId), eq(kbArticles.id, articleId)))
      }
    },

    async moveArticle(
      id: string,
      { parentId, position }: { parentId: string | null; position: number },
    ): Promise<KbArticle> {
      // Validate depth won't exceed MAX_TREE_DEPTH
      const currentDepth = parentId ? await this.treeDepth(parentId) + 1 : 1
      const height = await this.subtreeHeight(id)

      if (currentDepth + height - 1 > MAX_TREE_DEPTH) {
        throw new Error(`Move would exceed maximum tree depth of ${MAX_TREE_DEPTH}`)
      }

      const [row] = await db
        .update(kbArticles)
        .set({
          parentId: parentId,
          position: String(position),
          updatedAt: new Date(),
        })
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.id, id),
            isNull(kbArticles.deletedAt),
          ),
        )
        .returning()

      if (!row) throw new Error('Article not found')
      return serializeKbArticle(row)
    },

    async updateArticleMeta(
      id: string,
      patch: {
        title?: string
        slug?: string
        parentId?: string | null
        metaTitle?: string | null
        metaDescription?: string | null
        content?: Record<string, unknown>
        position?: number
      },
      userId: string,
    ): Promise<KbArticle> {
      if (patch.parentId !== undefined) {
        assertTenantOwnsOrThrow(
          'parent_id',
          await assertTenantOwnsKbArticle(db, tenantId, patch.parentId),
        )

        const currentDepth = patch.parentId ? (await this.treeDepth(patch.parentId)) + 1 : 1
        const height = await this.subtreeHeight(id)
        if (currentDepth + height - 1 > MAX_TREE_DEPTH) {
          throw new Error(`Move would exceed maximum tree depth of ${MAX_TREE_DEPTH}`)
        }
      }

      const update: Record<string, unknown> = {
        updatedBy: userId,
        updatedAt: new Date(),
      }
      if (patch.title !== undefined) update['title'] = patch.title
      if (patch.slug !== undefined) update['slug'] = patch.slug
      if (patch.parentId !== undefined) update['parentId'] = patch.parentId
      if (patch.metaTitle !== undefined) update['metaTitle'] = patch.metaTitle
      if (patch.metaDescription !== undefined) update['metaDescription'] = patch.metaDescription
      if (patch.content !== undefined) update['content'] = patch.content
      if (patch.position !== undefined) update['position'] = String(patch.position)

      const [row] = await db
        .update(kbArticles)
        .set(update as Partial<typeof kbArticles.$inferInsert>)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.id, id),
            isNull(kbArticles.deletedAt),
          ),
        )
        .returning()

      if (!row) throw new Error('Article not found')
      return serializeKbArticle(row)
    },
  }
}

// ── Portal query namespace ────────────────────────────────────────────────────

export function kbPortalQuery(db: Db, tenantId: string, customerId: string) {
  /**
   * A space is accessible to this portal user when:
   *   - type='vault' AND customer_id = customerId
   *   - OR is_public = true
   */
  const spaceAccessCondition = or(
    and(eq(kbSpaces.type, 'vault'), eq(kbSpaces.customerId, customerId)),
    eq(kbSpaces.isPublic, true),
  )!

  return {
    async listAccessibleSpaces(): Promise<KbSpace[]> {
      const rows = await db
        .select()
        .from(kbSpaces)
        .where(and(eq(kbSpaces.tenantId, tenantId), spaceAccessCondition))
        .orderBy(asc(kbSpaces.name))
      return rows.map(serializeKbSpace)
    },

    async getSpaceBySlug(slug: string): Promise<KbSpace | null> {
      const [row] = await db
        .select()
        .from(kbSpaces)
        .where(
          and(eq(kbSpaces.tenantId, tenantId), eq(kbSpaces.slug, slug), spaceAccessCondition),
        )
        .limit(1)
      return row ? serializeKbSpace(row) : null
    },

    async getSpaceById(id: string): Promise<KbSpace | null> {
      const [row] = await db
        .select()
        .from(kbSpaces)
        .where(
          and(eq(kbSpaces.tenantId, tenantId), eq(kbSpaces.id, id), spaceAccessCondition),
        )
        .limit(1)
      return row ? serializeKbSpace(row) : null
    },

    async listPublishedArticleTree(spaceId: string): Promise<KbArticleNode[]> {
      // Verify space accessibility first
      const space = await this.getSpaceById(spaceId)
      if (!space) return []

      const rows = await db
        .select()
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.spaceId, spaceId),
            eq(kbArticles.status, 'PUBLISHED'),
            isNull(kbArticles.deletedAt),
          ),
        )
        .orderBy(asc(kbArticles.position))
      return buildArticleTree(rows)
    },

    async getPublishedArticleBySlug(
      spaceSlug: string,
      articleSlug: string,
    ): Promise<KbArticle | null> {
      const space = await this.getSpaceBySlug(spaceSlug)
      if (!space) return null

      const [row] = await db
        .select()
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.spaceId, space.id),
            eq(kbArticles.slug, articleSlug),
            eq(kbArticles.status, 'PUBLISHED'),
            isNull(kbArticles.deletedAt),
          ),
        )
        .limit(1)
      return row ? serializeKbArticle(row) : null
    },

    async getPublishedArticleById(articleId: string): Promise<KbArticle | null> {
      const [row] = await db
        .select()
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.id, articleId),
            eq(kbArticles.status, 'PUBLISHED'),
            isNull(kbArticles.deletedAt),
          ),
        )
        .limit(1)

      if (!row) return null

      // Verify space is accessible
      const space = await this.getSpaceById(row.spaceId)
      if (!space) return null

      return serializeKbArticle(row)
    },

    async listAttachments(articleId: string): Promise<KbAttachment[]> {
      // Verify article is PUBLISHED and in an accessible space first
      const [articleRow] = await db
        .select({ spaceId: kbArticles.spaceId, status: kbArticles.status })
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.id, articleId),
            eq(kbArticles.status, 'PUBLISHED'),
            isNull(kbArticles.deletedAt),
          ),
        )
        .limit(1)

      if (!articleRow) return []

      // Verify space is accessible
      const space = await this.getSpaceById(articleRow.spaceId)
      if (!space) return []

      const rows = await db
        .select()
        .from(kbAttachments)
        .where(
          and(eq(kbAttachments.tenantId, tenantId), eq(kbAttachments.articleId, articleId)),
        )
        .orderBy(asc(kbAttachments.createdAt))
      return rows.map(serializeKbAttachment)
    },

    async getAttachment(id: string): Promise<KbAttachmentRow | null> {
      const [attachment] = await db
        .select()
        .from(kbAttachments)
        .where(and(eq(kbAttachments.tenantId, tenantId), eq(kbAttachments.id, id)))
        .limit(1)

      if (!attachment) return null

      // Verify parent article is PUBLISHED and in an accessible space
      const [article] = await db
        .select({ spaceId: kbArticles.spaceId, status: kbArticles.status })
        .from(kbArticles)
        .where(
          and(
            eq(kbArticles.tenantId, tenantId),
            eq(kbArticles.id, attachment.articleId),
            eq(kbArticles.status, 'PUBLISHED'),
            isNull(kbArticles.deletedAt),
          ),
        )
        .limit(1)

      if (!article) return null

      const space = await this.getSpaceById(article.spaceId)
      if (!space) return null

      return attachment
    },
  }
}