buzz-sheet/lib/media/repository.ts

import "server-only";

import { and, eq, isNull, sql } from "drizzle-orm";
import sharp from "sharp";

import { getDb } from "@/db";
import {
  extraSectionRows,
  guides,
  guidePosterBleeding,
  guideSections,
  media,
} from "@/db/schema";
import { getMediaStorage, publicMediaUrl } from "@/lib/media/storage";
import {
  hasValidImageSignature,
  MAX_MEDIA_BYTES,
  mediaUploadSchema,
  safeObjectFileName,
  type MediaUploadInput,
} from "@/lib/media/validation";

export async function createPendingMedia(
  input: MediaUploadInput,
  authorId: string,
): Promise<{ id: string; objectKey: string; uploadUrl: string; expiresIn: number }> {
  const id = crypto.randomUUID();
  const objectKey = `media/${id}/${safeObjectFileName(input.fileName)}`;
  const expiresIn = 5 * 60;
  await getDb().insert(media).values({
    id,
    objectKey,
    fileName: input.fileName,
    mimeType: input.mimeType,
    byteSize: input.byteSize,
    status: "pending",
    createdById: authorId,
  });
  const uploadUrl = (await getMediaStorage()).presign(objectKey, {
    method: "PUT",
    expiresIn,
    type: input.mimeType,
    acl: "private",
  });
  return { id, objectKey, uploadUrl, expiresIn };
}

export async function uploadPublicMedia(
  file: File,
  authorId: string,
): Promise<{ id: string; url: string; fileName: string; width: number; height: number }> {
  const parsed = mediaUploadSchema.parse({
    fileName: file.name,
    mimeType: file.type,
    byteSize: file.size,
  });
  const bytes = new Uint8Array(await file.arrayBuffer());
  if (!hasValidImageSignature(bytes.subarray(0, 16), parsed.mimeType)) {
    throw new Error("ไฟล์ไม่ตรงกับชนิดรูปภาพที่ระบุ");
  }
  const metadata = await sharp(bytes).metadata();
  const width = metadata.autoOrient.width || metadata.width;
  const height = metadata.autoOrient.height || metadata.height;
  if (!width || !height) {
    throw new Error("ไม่สามารถอ่านขนาดรูปภาพได้");
  }

  const id = crypto.randomUUID();
  const objectKey = `uploads/${id}/${safeObjectFileName(parsed.fileName)}`;
  const storage = await getMediaStorage();
  await storage.write(objectKey, bytes, { type: parsed.mimeType, acl: "public-read" });
  try {
    await getDb().insert(media).values({
      id,
      objectKey,
      fileName: parsed.fileName,
      mimeType: parsed.mimeType,
      byteSize: parsed.byteSize,
      width,
      height,
      status: "ready",
      currentReferenceCount: 0,
      createdById: authorId,
    });
  } catch (cause) {
    await storage.delete(objectKey).catch(() => undefined);
    throw cause;
  }
  return {
    id,
    url: publicMediaUrl(objectKey),
    fileName: parsed.fileName,
    width,
    height,
  };
}

export async function addCurrentMediaReference(mediaId: string): Promise<void> {
  await getDb()
    .update(media)
    .set({ currentReferenceCount: sql`${media.currentReferenceCount} + 1` })
    .where(eq(media.id, mediaId));
}

export async function completePendingMedia(mediaId: string) {
  const [record] = await getDb()
    .select()
    .from(media)
    .where(and(eq(media.id, mediaId), eq(media.status, "pending")))
    .limit(1);
  if (!record) return { status: "not-found" as const };

  const file = (await getMediaStorage()).file(record.objectKey);
  try {
    const stat = await file.stat();
    const prefix = await file.slice(0, 16).bytes();
    const valid =
      stat.size > 0 &&
      stat.size <= MAX_MEDIA_BYTES &&
      stat.size === record.byteSize &&
      hasValidImageSignature(prefix, record.mimeType as Parameters<typeof hasValidImageSignature>[1]);
    if (!valid) {
      await file.delete().catch(() => undefined);
      await getDb().update(media).set({ status: "failed" }).where(eq(media.id, record.id));
      return { status: "invalid" as const };
    }
    const [updated] = await getDb()
      .update(media)
      .set({
        status: "ready",
        byteSize: stat.size,
        checksum: stat.etag,
      })
      .where(and(eq(media.id, record.id), eq(media.status, "pending")))
      .returning();
    return updated
      ? { status: "ready" as const, media: updated }
      : { status: "not-found" as const };
  } catch {
    return { status: "missing" as const };
  }
}

export async function discardUnreferencedMedia(mediaId: string): Promise<boolean> {
  const [record] = await getDb().select().from(media).where(eq(media.id, mediaId)).limit(1);
  if (!record || record.currentReferenceCount > 0) {
    return false;
  }
  await (await getMediaStorage()).delete(record.objectKey).catch(() => undefined);
  await getDb().delete(media).where(eq(media.id, mediaId));
  return true;
}

export async function getReadyMedia(mediaId: string) {
  const [record] = await getDb()
    .select()
    .from(media)
    .where(and(eq(media.id, mediaId), eq(media.status, "ready")))
    .limit(1);
  return record ?? null;
}

export async function getMediaVisibility(mediaId: string): Promise<{
  public: boolean;
  retained: boolean;
}> {
  const db = getDb();
  const [covers, posters, bleeding, extras, [record]] = await Promise.all([
    db.select({ id: guides.id }).from(guides).where(and(
      eq(guides.coverMediaId, mediaId),
      eq(guides.isPublic, true),
      isNull(guides.trashedAt),
    )).limit(1),
    db.select({ id: guides.id }).from(guides).where(and(
      eq(guides.posterArtMediaId, mediaId),
      eq(guides.isPublic, true),
      isNull(guides.trashedAt),
    )).limit(1),
    db
      .select({ id: guides.id })
      .from(guidePosterBleeding)
      .innerJoin(guides, eq(guides.id, guidePosterBleeding.guideId))
      .where(and(
        eq(guidePosterBleeding.mediaId, mediaId),
        eq(guides.isPublic, true),
        isNull(guides.trashedAt),
      ))
      .limit(1),
    db
      .select({ id: guides.id })
      .from(extraSectionRows)
      .innerJoin(guideSections, eq(guideSections.id, extraSectionRows.sectionId))
      .innerJoin(guides, eq(guides.id, guideSections.guideId))
      .where(and(
        eq(extraSectionRows.mediaId, mediaId),
        eq(guideSections.enabled, true),
        eq(guides.isPublic, true),
        isNull(guides.trashedAt),
      ))
      .limit(1),
    db.select({
      currentReferenceCount: media.currentReferenceCount,
    }).from(media).where(eq(media.id, mediaId)).limit(1),
  ]);
  return {
    public: Boolean(covers.length || posters.length || bleeding.length || extras.length),
    retained: Boolean(record && record.currentReferenceCount > 0),
  };
}