/**
 * Unified attachments query helpers — unified-attachments (P065).
 *
 * All helpers are tenant-scoped: every statement carries a tenant_id WHERE clause.
 * Routes MUST NOT import raw Drizzle tables — they call these helpers.
 *
 * R2 keys are NEVER returned raw to clients — routes sign and return URLs.
 * Soft-delete via deleted_at; hard-delete via deleteAttachmentRow (also deletes from R2).
 */
import { and, asc, eq, isNull } from 'drizzle-orm'
import type { Db } from '../client'
import { attachments } from '../schema/attachments'

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

/** Entity types supported by the unified attachments table (spec 41). */
export type AttachmentEntityType =
  | 'task_message'
  | 'expense'
  | 'ticket_message'
  | 'kb_article'
  | 'vendor'

export interface AttachmentObject {
  id: string
  tenant_id: string
  uploader_id: string
  entity_type: string
  entity_id: string
  filename: string
  mime_type: string
  file_size_bytes: number
  created_at: string
}

/** Internal row shape returned by queries that need the r2_key for signing */
export interface AttachmentRow {
  id: string
  tenant_id: string
  uploader_id: string
  entity_type: string
  entity_id: string
  filename: string
  mime_type: string
  file_size_bytes: number
  r2_key: string
  created_at: string
}

export interface CreateAttachmentInput {
  uploader_id: string
  entity_type: AttachmentEntityType
  entity_id: string
  filename: string
  mime_type: string
  file_size_bytes: number
  r2_key: string
}

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

function serializeAttachmentRow(row: typeof attachments.$inferSelect): AttachmentRow {
  return {
    id: row.id,
    tenant_id: row.tenantId,
    uploader_id: row.uploaderId,
    entity_type: row.entityType,
    entity_id: row.entityId,
    filename: row.filename,
    mime_type: row.mimeType,
    file_size_bytes: row.sizeBytes,
    r2_key: row.r2Key,
    created_at: row.createdAt.toISOString(),
  }
}

// ── listAttachments ───────────────────────────────────────────────────────────

/**
 * List live (not deleted) attachments for a given entity.
 * Returns raw rows (with r2_key) for the route layer.
 */
export async function listAttachmentsForEntity(
  db: Db,
  tenantId: string,
  entityType: AttachmentEntityType,
  entityId: string,
): Promise<AttachmentRow[]> {
  const rows = await db
    .select()
    .from(attachments)
    .where(
      and(
        eq(attachments.tenantId, tenantId),
        eq(attachments.entityType, entityType),
        eq(attachments.entityId, entityId),
        isNull(attachments.deletedAt),
      ),
    )
    .orderBy(asc(attachments.createdAt))

  return rows.map(serializeAttachmentRow)
}

// ── getAttachment ─────────────────────────────────────────────────────────────

/**
 * Fetch a single live attachment by id (for signing / deletion).
 * Returns null if not found or already deleted.
 */
export async function getAttachment(
  db: Db,
  tenantId: string,
  id: string,
): Promise<AttachmentRow | null> {
  const [row] = await db
    .select()
    .from(attachments)
    .where(
      and(
        eq(attachments.tenantId, tenantId),
        eq(attachments.id, id),
        isNull(attachments.deletedAt),
      ),
    )
    .limit(1)

  return row ? serializeAttachmentRow(row) : null
}

// ── insertAttachment ──────────────────────────────────────────────────────────

/**
 * Insert a new attachment record.
 * The route layer is responsible for uploading to R2 first.
 */
export async function insertAttachment(
  db: Db,
  tenantId: string,
  input: CreateAttachmentInput,
): Promise<AttachmentRow> {
  const [row] = await db
    .insert(attachments)
    .values({
      tenantId,
      uploaderId: input.uploader_id,
      entityType: input.entity_type,
      entityId: input.entity_id,
      filename: input.filename,
      mimeType: input.mime_type,
      sizeBytes: input.file_size_bytes,
      r2Key: input.r2_key,
    })
    .returning()

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

// ── softDeleteAttachment ──────────────────────────────────────────────────────

/**
 * Soft-delete an attachment. Returns the r2_key so the route can delete from R2.
 * Returns null if the attachment was not found or is already deleted.
 */
export async function softDeleteAttachment(
  db: Db,
  tenantId: string,
  id: string,
  deletedById: string,
): Promise<{ r2_key: string } | null> {
  const [row] = await db
    .update(attachments)
    .set({
      deletedAt: new Date(),
      deletedBy: deletedById,
      updatedAt: new Date(),
    })
    .where(
      and(
        eq(attachments.tenantId, tenantId),
        eq(attachments.id, id),
        isNull(attachments.deletedAt),
      ),
    )
    .returning({ r2Key: attachments.r2Key })

  return row ? { r2_key: row.r2Key } : null
}

// ── hardDeleteAttachmentRow ───────────────────────────────────────────────────

/**
 * Hard-delete an attachment DB row.
 * Used after R2 deletion is confirmed.
 * The caller must have already verified ownership + called softDeleteAttachment.
 */
export async function hardDeleteAttachmentRow(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  await db
    .delete(attachments)
    .where(and(eq(attachments.tenantId, tenantId), eq(attachments.id, id)))
}

// ── Legacy bridge: task-message attachment helpers ────────────────────────────
//
// The existing routes/attachments.ts (tasks-detail-communication) calls:
//   insertDraftAttachment, getMessageAttachment, deleteMessageAttachmentRow
// from @zync/db/queries.  Those were backed by task_message_attachments table.
// Post-unification, the integrator should either:
//  (a) re-export those names pointing here with entity_type='task_message', OR
//  (b) keep the legacy table and migrate lazily.
//
// Provided here as shims so the existing attachments.ts route compiles
// against the unified table with minimal changes:

export interface InsertDraftAttachmentInput {
  tenantId: string
  filename: string
  url: string
  r2Key: string
  sizeBytes: number
  mimeType: string
  /** Must be set when the comment is posted (re-parenting). Null = draft */
  uploaderId?: string
  entityId?: string
}

/**
 * Insert a "draft" (unparented) attachment for task messages.
 * entityId is nullable until the comment is posted.
 * Note: this shim uses uploaderId from input; the calling route must pass it.
 */
export async function insertDraftTaskAttachment(
  db: Db,
  tenantId: string,
  uploaderId: string,
  input: { filename: string; r2Key: string; sizeBytes: number; mimeType: string; signedUrl?: string },
): Promise<{ id: string; r2Key: string; filename: string; sizeBytes: number; mimeType: string }> {
  const [row] = await db
    .insert(attachments)
    .values({
      tenantId,
      uploaderId,
      entityType: 'task_message',
      // Unparented draft — entity_id will be updated when comment is posted
      entityId: 'draft',
      filename: input.filename,
      mimeType: input.mimeType,
      sizeBytes: input.sizeBytes,
      r2Key: input.r2Key,
      signedUrl: input.signedUrl ?? null,
    })
    .returning()

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

  return {
    id: row.id,
    r2Key: row.r2Key,
    filename: row.filename,
    sizeBytes: row.sizeBytes,
    mimeType: row.mimeType,
  }
}

/**
 * Re-parent a batch of draft attachment rows to a real task message ID.
 * Called when a task comment is committed.
 */
export async function reparentTaskAttachments(
  db: Db,
  tenantId: string,
  attachmentIds: string[],
  taskMessageId: string,
): Promise<void> {
  if (attachmentIds.length === 0) return
  // Update each draft row to point at the real message
  for (const id of attachmentIds) {
    await db
      .update(attachments)
      .set({ entityId: taskMessageId, updatedAt: new Date() })
      .where(
        and(
          eq(attachments.tenantId, tenantId),
          eq(attachments.id, id),
          eq(attachments.entityType, 'task_message'),
        ),
      )
  }
}