retrotoon-studio/server/db.ts

import { eq, and, desc, asc } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import {
  InsertUser, users,
  projects, InsertProject, Project,
  sequences, InsertSequence,
  frames, InsertFrame,
  layers, InsertLayer,
  characters, InsertCharacter,
  aiEngines, InsertAiEngine,
  generationJobs, InsertGenerationJob,
  assistantMessages, InsertAssistantMessage,
  frameVariants, InsertFrameVariant,
} from "../drizzle/schema";
import { ENV } from './_core/env';

let _db: ReturnType<typeof drizzle> | null = null;

export async function getDb() {
  if (!_db && process.env.DATABASE_URL) {
    try {
      _db = drizzle(process.env.DATABASE_URL);
    } catch (error) {
      console.warn("[Database] Failed to connect:", error);
      _db = null;
    }
  }
  return _db;
}

// ============ USERS ============
export async function upsertUser(user: InsertUser): Promise<void> {
  if (!user.openId) throw new Error("User openId is required for upsert");
  const db = await getDb();
  if (!db) return;

  const values: InsertUser = { openId: user.openId };
  const updateSet: Record<string, unknown> = {};
  const textFields = ["name", "email", "passwordHash", "loginMethod"] as const;
  type TextField = (typeof textFields)[number];
  const assignNullable = (field: TextField) => {
    const value = user[field];
    if (value === undefined) return;
    const normalized = value ?? null;
    values[field] = normalized;
    updateSet[field] = normalized;
  };
  textFields.forEach(assignNullable);
  if (user.lastSignedIn !== undefined) { values.lastSignedIn = user.lastSignedIn; updateSet.lastSignedIn = user.lastSignedIn; }
  if (user.role !== undefined) { values.role = user.role; updateSet.role = user.role; }
  else if (user.openId === ENV.ownerOpenId) { values.role = 'admin'; updateSet.role = 'admin'; }
  if (!values.lastSignedIn) values.lastSignedIn = new Date();
  if (Object.keys(updateSet).length === 0) updateSet.lastSignedIn = new Date();
  await db.insert(users).values(values).onDuplicateKeyUpdate({ set: updateSet });
}

export async function getUserByOpenId(openId: string) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(users).where(eq(users.openId, openId)).limit(1);
  return result.length > 0 ? result[0] : undefined;
}

export async function getUserByEmail(email: string) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(users).where(eq(users.email, email)).limit(1);
  return result.length > 0 ? result[0] : undefined;
}

// ============ PROJECTS ============
export async function createProject(data: InsertProject) {
  const db = await getDb();
  if (!db) throw new Error("DB not available");
  const result = await db.insert(projects).values(data);
  return { id: result[0].insertId };
}

export async function getProject(id: number) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.select().from(projects).where(eq(projects.id, id)).limit(1);
  return result[0] ?? null;
}

export async function listProjects(userId: number) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(projects).where(eq(projects.userId, userId)).orderBy(desc(projects.createdAt));
}

export async function listAllProjects() {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(projects).orderBy(desc(projects.createdAt));
}

export async function updateProject(id: number, data: Partial<InsertProject>) {
  const db = await getDb();
  if (!db) return;
  await db.update(projects).set(data).where(eq(projects.id, id));
}

export async function deleteProject(id: number) {
  const db = await getDb();
  if (!db) return;
  // Delete related data first (cascade)
  await db.delete(assistantMessages).where(eq(assistantMessages.projectId, id));
  await db.delete(generationJobs).where(eq(generationJobs.projectId, id));
  await db.delete(layers).where(eq(layers.projectId, id));
  await db.delete(characters).where(eq(characters.projectId, id));
  await db.delete(sequences).where(eq(sequences.projectId, id));
  await db.delete(frames).where(eq(frames.projectId, id));
  await db.delete(projects).where(eq(projects.id, id));
}

// ============ SEQUENCES ============
export async function createSequence(data: InsertSequence) {
  const db = await getDb();
  if (!db) throw new Error("DB not available");
  const result = await db.insert(sequences).values(data);
  return { id: result[0].insertId };
}

export async function listSequences(projectId: number) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(sequences).where(eq(sequences.projectId, projectId)).orderBy(asc(sequences.startFrame));
}

export async function updateSequence(id: number, data: Partial<InsertSequence>) {
  const db = await getDb();
  if (!db) return;
  await db.update(sequences).set(data).where(eq(sequences.id, id));
}

// ============ FRAMES ============
export async function createFrames(data: InsertFrame[]) {
  const db = await getDb();
  if (!db) throw new Error("DB not available");
  if (data.length === 0) return;
  await db.insert(frames).values(data);
}

export async function listFrames(projectId: number) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(frames).where(eq(frames.projectId, projectId)).orderBy(asc(frames.frameIndex));
}

export async function getFrame(projectId: number, frameIndex: number) {
  const db = await getDb();
  if (!db) return null;
  const result = await db.select().from(frames)
    .where(and(eq(frames.projectId, projectId), eq(frames.frameIndex, frameIndex)))
    .limit(1);
  return result[0] ?? null;
}

export async function updateFrame(id: number, data: Partial<InsertFrame>) {
  const db = await getDb();
  if (!db) return;
  await db.update(frames).set(data).where(eq(frames.id, id));
}

// ============ LAYERS ============
export async function createLayer(data: InsertLayer) {
  const db = await getDb();
  if (!db) throw new Error("DB not available");
  const result = await db.insert(layers).values(data);
  return { id: result[0].insertId };
}

export async function listLayers(projectId: number) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(layers).where(eq(layers.projectId, projectId)).orderBy(asc(layers.order));
}

export async function updateLayer(id: number, data: Partial<InsertLayer>) {
  const db = await getDb();
  if (!db) return;
  await db.update(layers).set(data).where(eq(layers.id, id));
}

export async function deleteLayer(id: number) {
  const db = await getDb();
  if (!db) return;
  await db.delete(layers).where(eq(layers.id, id));
}

export async function reorderLayers(ids: number[]) {
  const db = await getDb();
  if (!db) return;
  for (let i = 0; i < ids.length; i++) {
    await db.update(layers).set({ order: i }).where(eq(layers.id, ids[i]));
  }
}

/**
 * List layers for a specific frame (composition is per-frame)
 */
export async function listLayersForFrame(frameId: number) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(layers).where(eq(layers.frameId, frameId)).orderBy(asc(layers.order));
}

/**
 * Upsert a layer that sources from a variant (auto-created during generation)
 * - If a layer of this type exists for the frame, update its sourceVariantId
 * - Otherwise create a new layer
 */
export async function upsertLayerForVariant(params: {
  frameId: number;
  projectId: number;
  type: "background" | "character" | "object" | "effect";
  name: string;
  sourceVariantId: number;
  order?: number;
}) {
  const db = await getDb();
  if (!db) return;

  // Look for existing layer of this type for this frame
  const existing = await db
    .select()
    .from(layers)
    .where(and(eq(layers.frameId, params.frameId), eq(layers.type, params.type)))
    .limit(1);

  if (existing.length > 0) {
    await db
      .update(layers)
      .set({ sourceVariantId: params.sourceVariantId, name: params.name })
      .where(eq(layers.id, existing[0].id));
    return { id: existing[0].id, created: false };
  }

  // Compute next order if not provided
  let order = params.order;
  if (order === undefined) {
    const all = await db.select({ order: layers.order }).from(layers).where(eq(layers.frameId, params.frameId));
    order = all.length > 0 ? Math.max(...all.map(l => l.order ?? 0)) + 1 : 0;
  }

  const result = await db.insert(layers).values({
    frameId: params.frameId,
    projectId: params.projectId,
    name: params.name,
    type: params.type,
    sourceVariantId: params.sourceVariantId,
    order,
    visible: true,
    locked: false,
    opacity: 100,
    blendMode: "normal",
  });

  return { id: result[0].insertId, created: true };
}

/**
 * Update layer properties (partial)
 */
export async function patchLayer(id: number, patch: Partial<InsertLayer>) {
  const db = await getDb();
  if (!db) return;
  await db.update(layers).set(patch).where(eq(layers.id, id));
}

/**
 * Duplicate a layer (new layer with same properties, increment order)
 */
export async function duplicateLayer(layerId: number) {
  const db = await getDb();
  if (!db) return;
  const [src] = await db.select().from(layers).where(eq(layers.id, layerId)).limit(1);
  if (!src) return;
  const all = await db.select({ order: layers.order }).from(layers).where(eq(layers.frameId, src.frameId!));
  const nextOrder = Math.max(...all.map(l => l.order ?? 0)) + 1;
  const result = await db.insert(layers).values({
    ...src,
    id: undefined as any,
    name: `${src.name} (copie)`,
    order: nextOrder,
    createdAt: undefined as any,
    updatedAt: undefined as any,
  });
  return { id: result[0].insertId };
}

/**
 * Create an empty effect/object layer (manual add)
 */
/**
 * Propagate a single layer's properties to all frames in the same sequence.
 * For each target frame, upserts a layer of the same type with same source/transform/etc.
 * Optionally limits to a specific frame range.
 */
export async function propagateLayerToSequence(params: {
  sourceLayerId: number;
  startFrame: number;
  endFrame: number;
}): Promise<{ updated: number; created: number; skipped: number }> {
  const db = await getDb();
  if (!db) return { updated: 0, created: 0, skipped: 0 };

  const [src] = await db.select().from(layers).where(eq(layers.id, params.sourceLayerId)).limit(1);
  if (!src || !src.frameId) return { updated: 0, created: 0, skipped: 0 };

  // Get the project's frames in the range
  const allFrames = await db
    .select()
    .from(frames)
    .where(eq(frames.projectId, src.projectId));
  const targetFrames = allFrames.filter(
    f => f.frameIndex >= params.startFrame && f.frameIndex <= params.endFrame && f.id !== src.frameId
  );

  // Batch fetch all existing layers of this type on target frames
  const targetFrameIds = targetFrames.map(f => f.id);
  let existing: any[] = [];
  if (targetFrameIds.length > 0) {
    const { inArray } = await import("drizzle-orm");
    existing = await db
      .select()
      .from(layers)
      .where(and(inArray(layers.frameId, targetFrameIds), eq(layers.type, src.type)));
  }
  const existingByFrame = new Map(existing.map(l => [l.frameId!, l]));

  const payload = {
    sourceVariantId: src.sourceVariantId,
    imageUrl: src.imageUrl,
    name: src.name,
    opacity: src.opacity,
    blendMode: src.blendMode,
    visible: src.visible,
    transform: src.transform as any,
    maskUrl: src.maskUrl,
  };

  let updated = 0, created = 0, skipped = 0;
  const toInsert: any[] = [];
  const toUpdate: number[] = [];

  for (const tgt of targetFrames) {
    const exist = existingByFrame.get(tgt.id);
    if (exist) {
      if (exist.locked) { skipped++; continue; }
      toUpdate.push(exist.id);
    } else {
      toInsert.push({
        frameId: tgt.id,
        projectId: src.projectId,
        type: src.type,
        order: src.order,
        locked: false,
        ...payload,
      });
    }
  }

  // Batch insert (chunks of 100 to stay within query size limits)
  for (let i = 0; i < toInsert.length; i += 100) {
    const chunk = toInsert.slice(i, i + 100);
    await db.insert(layers).values(chunk);
    created += chunk.length;
  }

  // Batch updates: each update needs WHERE id=X so loop, but in parallel chunks
  const updateChunkSize = 20;
  for (let i = 0; i < toUpdate.length; i += updateChunkSize) {
    const chunk = toUpdate.slice(i, i + updateChunkSize);
    await Promise.all(chunk.map(id => db.update(layers).set(payload).where(eq(layers.id, id))));
    updated += chunk.length;
  }

  return { updated, created, skipped };
}

export async function createCustomLayer(params: {
  frameId: number;
  projectId: number;
  name: string;
  type: "effect" | "object" | "background" | "character";
  imageUrl?: string;
}) {
  const db = await getDb();
  if (!db) return;
  const all = await db.select({ order: layers.order }).from(layers).where(eq(layers.frameId, params.frameId));
  const nextOrder = all.length > 0 ? Math.max(...all.map(l => l.order ?? 0)) + 1 : 0;
  const result = await db.insert(layers).values({
    frameId: params.frameId,
    projectId: params.projectId,
    name: params.name,
    type: params.type,
    imageUrl: params.imageUrl,
    order: nextOrder,
    visible: true,
    locked: false,
    opacity: 100,
    blendMode: "normal",
  });
  return { id: result[0].insertId };
}

// ============ CHARACTERS ============
export async function createCharacter(data: InsertCharacter) {
  const db = await getDb();
  if (!db) throw new Error("DB not available");
  const result = await db.insert(characters).values(data);
  return { id: result[0].insertId };
}

export async function listCharacters(projectId: number) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(characters).where(eq(characters.projectId, projectId));
}

export async function updateCharacter(id: number, data: Partial<InsertCharacter>) {
  const db = await getDb();
  if (!db) return;
  await db.update(characters).set(data).where(eq(characters.id, id));
}

// ============ AI ENGINES ============
export async function createAiEngine(data: InsertAiEngine) {
  const db = await getDb();
  if (!db) throw new Error("DB not available");
  const result = await db.insert(aiEngines).values(data);
  return { id: result[0].insertId };
}

export async function listAiEngines() {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(aiEngines).orderBy(desc(aiEngines.createdAt));
}

export async function updateAiEngine(id: number, data: Partial<InsertAiEngine>) {
  const db = await getDb();
  if (!db) return;
  await db.update(aiEngines).set(data).where(eq(aiEngines.id, id));
}

export async function deleteAiEngine(id: number) {
  const db = await getDb();
  if (!db) return;
  await db.delete(aiEngines).where(eq(aiEngines.id, id));
}

export async function getDefaultEngine(taskType: string) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(aiEngines)
    .where(and(eq(aiEngines.taskType, taskType as any), eq(aiEngines.isDefault, true), eq(aiEngines.isActive, true)))
    .limit(1);
  return result[0];
}

// ============ GENERATION JOBS ============
export async function createGenerationJob(data: InsertGenerationJob) {
  const db = await getDb();
  if (!db) throw new Error("DB not available");
  const result = await db.insert(generationJobs).values(data);
  return { id: result[0].insertId };
}

export async function listGenerationJobs(projectId: number) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(generationJobs).where(eq(generationJobs.projectId, projectId)).orderBy(desc(generationJobs.createdAt));
}

export async function updateGenerationJob(id: number, data: Partial<InsertGenerationJob>) {
  const db = await getDb();
  if (!db) return;
  await db.update(generationJobs).set(data).where(eq(generationJobs.id, id));
}

// ============ ASSISTANT MESSAGES ============
export async function createAssistantMessage(data: InsertAssistantMessage) {
  const db = await getDb();
  if (!db) throw new Error("DB not available");
  await db.insert(assistantMessages).values(data);
}

export async function listAssistantMessages(projectId: number) {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(assistantMessages).where(eq(assistantMessages.projectId, projectId)).orderBy(asc(assistantMessages.createdAt));
}

// ============ FRAME VARIANTS (non-destructive version history) ============
export async function createFrameVariant(data: InsertFrameVariant) {
  const db = await getDb();
  if (!db) throw new Error("DB not available");

  // If isActive=true, deactivate other variants of the same type for this frame
  if (data.isActive) {
    await db
      .update(frameVariants)
      .set({ isActive: false })
      .where(and(eq(frameVariants.frameId, data.frameId), eq(frameVariants.type, data.type)));
  }

  const result = await db.insert(frameVariants).values(data);
  return { id: result[0].insertId };
}

export async function listFrameVariants(frameId: number, type?: "background" | "character" | "composite") {
  const db = await getDb();
  if (!db) return [];
  const whereClause = type
    ? and(eq(frameVariants.frameId, frameId), eq(frameVariants.type, type))
    : eq(frameVariants.frameId, frameId);
  return db.select().from(frameVariants).where(whereClause).orderBy(desc(frameVariants.createdAt));
}

export async function getActiveVariant(frameId: number, type: "background" | "character" | "composite") {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db
    .select()
    .from(frameVariants)
    .where(and(eq(frameVariants.frameId, frameId), eq(frameVariants.type, type), eq(frameVariants.isActive, true)))
    .limit(1);
  return result[0];
}

export async function setActiveVariant(variantId: number) {
  const db = await getDb();
  if (!db) return;
  const [variant] = await db.select().from(frameVariants).where(eq(frameVariants.id, variantId)).limit(1);
  if (!variant) return;
  await db
    .update(frameVariants)
    .set({ isActive: false })
    .where(and(eq(frameVariants.frameId, variant.frameId), eq(frameVariants.type, variant.type)));
  await db.update(frameVariants).set({ isActive: true }).where(eq(frameVariants.id, variantId));
  return variant;
}

export async function deleteFrameVariant(variantId: number) {
  const db = await getDb();
  if (!db) return;
  await db.delete(frameVariants).where(eq(frameVariants.id, variantId));
}

export async function toggleVariantPin(variantId: number) {
  const db = await getDb();
  if (!db) return;
  const [v] = await db.select({ isPinned: frameVariants.isPinned }).from(frameVariants).where(eq(frameVariants.id, variantId)).limit(1);
  if (!v) return;
  await db.update(frameVariants).set({ isPinned: !v.isPinned }).where(eq(frameVariants.id, variantId));
}

export async function renameVariant(variantId: number, label: string | null) {
  const db = await getDb();
  if (!db) return;
  await db.update(frameVariants).set({ label }).where(eq(frameVariants.id, variantId));
}