/**
 * Task message + attachment query helpers — tasks-detail-communication.
 *
 * All helpers are tenant-scoped. No raw Drizzle in routes — import from here.
 */
import { and, eq, asc, isNull, inArray } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import { taskMessages, taskMessageAttachments } from '../schema/task-comms'
import { users } from '../schema/users'

// ── Types ─────────────────────────────────────────────────────────────────────

export interface TaskAttachmentRow {
  id: string
  filename: string
  url: string
  r2Key: string
  sizeBytes: number
  mimeType: string
  createdAt: string
}

export interface TaskMessageRow {
  id: string
  taskId: string
  authorId: string | null
  authorName: string | null
  authorAvatarUrl: string | null
  messageType: 'comment' | 'system'
  content: string
  deleted: boolean
  attachments: TaskAttachmentRow[]
  createdAt: string
}

// ── Serializers ───────────────────────────────────────────────────────────────

function serializeAttachment(row: typeof taskMessageAttachments.$inferSelect): TaskAttachmentRow {
  return {
    id: row.id,
    filename: row.filename,
    url: row.url,
    r2Key: row.r2Key,
    sizeBytes: row.sizeBytes,
    mimeType: row.mimeType,
    createdAt: row.createdAt.toISOString(),
  }
}

function serializeMessage(
  row: typeof taskMessages.$inferSelect & {
    authorName: string | null
    authorAvatarUrl: string | null
  },
  attachments: TaskAttachmentRow[],
): TaskMessageRow {
  const deleted = row.deletedAt !== null && row.deletedAt !== undefined
  return {
    id: row.id,
    taskId: row.taskId,
    authorId: row.authorId,
    authorName: row.authorName,
    authorAvatarUrl: row.authorAvatarUrl,
    messageType: row.messageType as 'comment' | 'system',
    content: deleted ? '[deleted]' : row.content,
    deleted,
    attachments: deleted ? [] : attachments,
    createdAt: row.createdAt.toISOString(),
  }
}

// ── listTaskMessages ──────────────────────────────────────────────────────────

export async function listTaskMessages(
  db: Db,
  tenantId: string,
  taskId: string,
  opts: { limit: number; cursor?: string },
): Promise<{ rows: TaskMessageRow[]; nextCursor: string | null }> {
  const limit = Math.min(opts.limit, 100)

  // Fetch messages with author info via join
  const rows = await db
    .select({
      id: taskMessages.id,
      taskId: taskMessages.taskId,
      tenantId: taskMessages.tenantId,
      authorId: taskMessages.authorId,
      messageType: taskMessages.messageType,
      content: taskMessages.content,
      createdAt: taskMessages.createdAt,
      deletedAt: taskMessages.deletedAt,
      authorName: users.name,
      authorAvatarUrl: users.avatarUrl,
    })
    .from(taskMessages)
    .leftJoin(users, eq(taskMessages.authorId, users.id))
    .where(
      and(
        eq(taskMessages.tenantId, tenantId),
        eq(taskMessages.taskId, taskId),
        // cursor: createdAt-based keyset
        opts.cursor
          ? ((() => {
              try {
                const ts = Buffer.from(opts.cursor, 'base64url').toString('utf8')
                const { createdAt: _createdAt, id: _id } = JSON.parse(ts) as { createdAt: string; id: string }
                // Return messages strictly after the cursor
                // We use a raw expression for composite keyset
                return eq(taskMessages.tenantId, tenantId) // placeholder — replaced below
              } catch {
                return undefined
              }
            })())
          : undefined,
      ),
    )
    .orderBy(asc(taskMessages.createdAt), asc(taskMessages.id))
    .limit(limit + 1)

  // Apply cursor filtering in-memory if cursor was provided (simple approach for small pages)
  let filteredRows = rows
  if (opts.cursor) {
    try {
      const ts = Buffer.from(opts.cursor, 'base64url').toString('utf8')
      const { createdAt, id } = JSON.parse(ts) as { createdAt: string; id: string }
      const cursorDate = new Date(createdAt)
      filteredRows = rows.filter((r) => {
        const rDate = r.createdAt
        return rDate > cursorDate || (rDate.getTime() === cursorDate.getTime() && r.id > id)
      })
    } catch {
      // ignore invalid cursor
    }
  }

  const hasMore = filteredRows.length > limit
  const pageRows = hasMore ? filteredRows.slice(0, limit) : filteredRows

  // Fetch attachments for non-deleted messages in page
  const activeMessageIds = pageRows
    .filter((r) => r.deletedAt === null || r.deletedAt === undefined)
    .map((r) => r.id)

  const attachmentMap: Record<string, TaskAttachmentRow[]> = {}
  if (activeMessageIds.length > 0) {
    const attRows = await db
      .select()
      .from(taskMessageAttachments)
      .where(
        and(
          eq(taskMessageAttachments.tenantId, tenantId),
          inArray(taskMessageAttachments.messageId, activeMessageIds),
        ),
      )
      .orderBy(asc(taskMessageAttachments.createdAt))

    for (const att of attRows) {
      if (att.messageId) {
        if (!attachmentMap[att.messageId]) attachmentMap[att.messageId] = []
        attachmentMap[att.messageId]!.push(serializeAttachment(att))
      }
    }
  }

  let nextCursor: string | null = null
  if (hasMore) {
    const last = pageRows[pageRows.length - 1]
    if (last) {
      nextCursor = Buffer.from(
        JSON.stringify({ createdAt: last.createdAt.toISOString(), id: last.id }),
      ).toString('base64url')
    }
  }

  return {
    rows: pageRows.map((r) =>
      serializeMessage(r, attachmentMap[r.id] ?? []),
    ),
    nextCursor,
  }
}

// ── createTaskMessage ─────────────────────────────────────────────────────────

export async function createTaskMessage(
  db: Db | DbTx,
  args: {
    tenantId: string
    taskId: string
    authorId: string | null
    messageType: 'comment' | 'system'
    content: string
  },
): Promise<TaskMessageRow> {
  const [row] = await db
    .insert(taskMessages)
    .values({
      taskId: args.taskId,
      tenantId: args.tenantId,
      authorId: args.authorId,
      messageType: args.messageType,
      content: args.content,
    })
    .returning()

  if (!row) throw new Error('Failed to insert task message')

  // Fetch author info if present
  let authorName: string | null = null
  let authorAvatarUrl: string | null = null

  if (args.authorId) {
    const [user] = await db
      .select({ name: users.name, avatarUrl: users.avatarUrl })
      .from(users)
      .where(eq(users.id, args.authorId))
      .limit(1)
    if (user) {
      authorName = user.name
      authorAvatarUrl = user.avatarUrl ?? null
    }
  }

  return serializeMessage({ ...row, authorName, authorAvatarUrl }, [])
}

// ── recordTaskSystemMessage ───────────────────────────────────────────────────

export async function recordTaskSystemMessage(
  db: Db | DbTx,
  args: {
    tenantId: string
    taskId: string
    text: string
  },
): Promise<TaskMessageRow> {
  return createTaskMessage(db, {
    tenantId: args.tenantId,
    taskId: args.taskId,
    authorId: null,
    messageType: 'system',
    content: args.text,
  })
}

// ── softDeleteTaskMessage ─────────────────────────────────────────────────────

export async function softDeleteTaskMessage(
  db: Db,
  args: {
    tenantId: string
    messageId: string
    authorId: string
  },
): Promise<TaskMessageRow | null> {
  // Load the message first to validate ownership and type
  const [existing] = await db
    .select()
    .from(taskMessages)
    .where(
      and(
        eq(taskMessages.tenantId, args.tenantId),
        eq(taskMessages.id, args.messageId),
      ),
    )
    .limit(1)

  if (!existing) return null
  // Only comments can be deleted; system messages cannot
  if (existing.messageType !== 'comment') return null
  // Only the author can delete their own message
  if (existing.authorId !== args.authorId) return null
  // Already deleted
  if (existing.deletedAt !== null && existing.deletedAt !== undefined) {
    return serializeMessage({ ...existing, authorName: null, authorAvatarUrl: null }, [])
  }

  const now = new Date()
  const [updated] = await db
    .update(taskMessages)
    .set({ deletedAt: now })
    .where(
      and(
        eq(taskMessages.tenantId, args.tenantId),
        eq(taskMessages.id, args.messageId),
      ),
    )
    .returning()

  if (!updated) return null
  return serializeMessage({ ...updated, authorName: null, authorAvatarUrl: null }, [])
}

// ── addMessageAttachment ──────────────────────────────────────────────────────

export async function addMessageAttachment(
  db: Db | DbTx,
  args: {
    tenantId: string
    messageId: string
    filename: string
    url: string
    r2Key: string
    sizeBytes: number
    mimeType: string
  },
): Promise<TaskAttachmentRow> {
  const [row] = await db
    .insert(taskMessageAttachments)
    .values({
      messageId: args.messageId,
      tenantId: args.tenantId,
      filename: args.filename,
      url: args.url,
      r2Key: args.r2Key,
      sizeBytes: args.sizeBytes,
      mimeType: args.mimeType,
    })
    .returning()

  if (!row) throw new Error('Failed to insert attachment')
  return serializeAttachment(row)
}

// ── getTaskMessageById ────────────────────────────────────────────────────────

export async function getTaskMessageById(
  db: Db,
  tenantId: string,
  messageId: string,
): Promise<{ id: string } | null> {
  const [row] = await db
    .select({ id: taskMessages.id })
    .from(taskMessages)
    .where(and(eq(taskMessages.tenantId, tenantId), eq(taskMessages.id, messageId)))
    .limit(1)

  return row ?? null
}

// ── getMessageAttachment ──────────────────────────────────────────────────────

export async function getMessageAttachment(
  db: Db,
  tenantId: string,
  attachmentId: string,
): Promise<TaskAttachmentRow | null> {
  const [row] = await db
    .select()
    .from(taskMessageAttachments)
    .where(
      and(
        eq(taskMessageAttachments.tenantId, tenantId),
        eq(taskMessageAttachments.id, attachmentId),
      ),
    )
    .limit(1)

  return row ? serializeAttachment(row) : null
}

// ── deleteMessageAttachmentRow ────────────────────────────────────────────────

export async function deleteMessageAttachmentRow(
  db: Db,
  tenantId: string,
  attachmentId: string,
): Promise<void> {
  await db
    .delete(taskMessageAttachments)
    .where(
      and(
        eq(taskMessageAttachments.tenantId, tenantId),
        eq(taskMessageAttachments.id, attachmentId),
      ),
    )
}

// ── insertDraftAttachment ─────────────────────────────────────────────────────
// Creates an unparented (messageId = null) attachment row at upload time.
// The row is reparented when the comment is submitted.

export async function insertDraftAttachment(
  db: Db,
  args: {
    tenantId: string
    filename: string
    url: string
    r2Key: string
    sizeBytes: number
    mimeType: string
  },
): Promise<TaskAttachmentRow> {
  const [row] = await db
    .insert(taskMessageAttachments)
    .values({
      messageId: null,
      tenantId: args.tenantId,
      filename: args.filename,
      url: args.url,
      r2Key: args.r2Key,
      sizeBytes: args.sizeBytes,
      mimeType: args.mimeType,
    })
    .returning()

  if (!row) throw new Error('Failed to insert draft attachment')
  return serializeAttachment(row)
}

// ── reparentAttachments ───────────────────────────────────────────────────────
// Links previously-uploaded draft attachments to a real message.

export async function reparentAttachments(
  db: Db | DbTx,
  tenantId: string,
  attachmentIds: string[],
  messageId: string,
): Promise<TaskAttachmentRow[]> {
  if (attachmentIds.length === 0) return []

  const rows = await db
    .update(taskMessageAttachments)
    .set({ messageId })
    .where(
      and(
        eq(taskMessageAttachments.tenantId, tenantId),
        inArray(taskMessageAttachments.id, attachmentIds),
        isNull(taskMessageAttachments.messageId),
      ),
    )
    .returning()

  return rows.map(serializeAttachment)
}