server/db.ts

import { eq, desc, asc, sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import { InsertUser, User, users, projects, iterations, codeReferences, statistics } from "../drizzle/schema";
import { ENV } from './_core/env';

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

// In-memory storage for fallback mode
const memoryStore = {
  users: [] as User[],
  projects: [] as any[],
  iterations: [] as any[],
  codeReferences: [] as any[],
  statistics: [] as any[],
};

// Lazily create the drizzle instance so local tooling can run without a DB.
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, using memory fallback:", error);
      _db = null;
    }
  }
  return _db;
}

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) {
    console.warn("[Database] Using memory fallback for upsertUser");
    const existingIdx = memoryStore.users.findIndex(u => u.openId === user.openId);
    const userData: User = {
      id: existingIdx >= 0 ? memoryStore.users[existingIdx].id : memoryStore.users.length + 1,
      openId: user.openId,
      name: user.name ?? null,
      email: user.email ?? null,
      loginMethod: user.loginMethod ?? null,
      role: user.role ?? (user.openId === ENV.ownerOpenId ? 'admin' : 'user'),
      createdAt: existingIdx >= 0 ? memoryStore.users[existingIdx].createdAt : new Date(),
      updatedAt: new Date(),
      lastSignedIn: user.lastSignedIn ?? new Date(),
    };
    
    if (existingIdx >= 0) {
      memoryStore.users[existingIdx] = userData;
    } else {
      memoryStore.users.push(userData);
    }
    return;
  }

  try {
    const values: InsertUser = {
      openId: user.openId,
    };
    const updateSet: Record<string, unknown> = {};

    const textFields = ["name", "email", "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,
    });
  } catch (error) {
    console.error("[Database] Failed to upsert user:", error);
    throw error;
  }
}

export async function getUserByOpenId(openId: string) {
  const db = await getDb();
  if (!db) {
    return memoryStore.users.find(u => u.openId === openId);
  }

  const result = await db.select().from(users).where(eq(users.openId, openId)).limit(1);
  return result.length > 0 ? result[0] : undefined;
}

export async function getOrCreateGuestUser() {
  const guestOpenId = "guest-user";
  let user = await getUserByOpenId(guestOpenId);
  
  if (!user) {
    await upsertUser({
      openId: guestOpenId,
      name: "Guest User",
      email: "guest@example.com",
      role: "admin",
    });
    user = await getUserByOpenId(guestOpenId);
  }
  
  return user;
}

// Projects queries
export async function createProject(userId: number, data: {
  name: string;
  description?: string;
  mode: string;
  contentType: string;
  originalPrompt: string;
}) {
  const db = await getDb();
  if (!db) {
    const newProject = {
      id: memoryStore.projects.length + 1,
      userId,
      name: data.name,
      description: data.description ?? null,
      mode: data.mode,
      contentType: data.contentType,
      originalPrompt: data.originalPrompt,
      status: "pending",
      createdAt: new Date(),
      updatedAt: new Date(),
      finalOutput: null,
      finalScore: 0,
      completedAt: null,
    };
    memoryStore.projects.push(newProject);
    return newProject;
  }
  
  const result = await db.insert(projects).values({
    userId,
    name: data.name,
    description: data.description,
    mode: data.mode as any,
    contentType: data.contentType as any,
    originalPrompt: data.originalPrompt,
    status: "pending",
  });
  
  const projectId = (result as any).insertId || 0;
  if (projectId > 0) {
    const newProject = await db.select().from(projects).where(eq(projects.id, projectId)).limit(1);
    return newProject[0] || null;
  }
  
  return null;
}

export async function getUserProjects(userId: number, limit = 50) {
  const db = await getDb();
  if (!db) {
    return memoryStore.projects
      .filter(p => p.userId === userId)
      .sort((a, b) => b.createdAt.getTime() - a.createdAt.getTime())
      .slice(0, limit);
  }
  
  return await db.select().from(projects)
    .where(eq(projects.userId, userId))
    .orderBy(desc(projects.createdAt))
    .limit(limit);
}

export async function getProjectById(projectId: number) {
  const db = await getDb();
  if (!db) {
    return memoryStore.projects.find(p => p.id === projectId) || null;
  }
  
  const result = await db.select().from(projects)
    .where(eq(projects.id, projectId))
    .limit(1);
  
  return result[0] || null;
}

export async function updateProjectStatus(projectId: number, status: string, finalOutput?: string, finalScore?: number) {
  const db = await getDb();
  if (!db) {
    const project = memoryStore.projects.find(p => p.id === projectId);
    if (project) {
      project.status = status;
      if (finalOutput !== undefined) project.finalOutput = finalOutput;
      if (finalScore !== undefined) project.finalScore = finalScore;
      project.updatedAt = new Date();
      if (status === "completed") project.completedAt = new Date();
      return true;
    }
    return false;
  }
  
  await db.update(projects)
    .set({
      status: status as any,
      finalOutput,
      finalScore,
      completedAt: status === "completed" ? new Date() : undefined,
    })
    .where(eq(projects.id, projectId));
  
  return true;
}

// Iterations queries
export async function addIteration(projectId: number, version: number, data: {
  qwenOutput?: string;
  deepseekAnalysis?: string;
  score: number;
  passed: boolean;
  scorecard?: any;
  feedback?: any;
}) {
  const db = await getDb();
  if (!db) {
    const newIteration = {
      id: memoryStore.iterations.length + 1,
      projectId,
      version,
      qwenOutput: data.qwenOutput ?? null,
      deepseekAnalysis: data.deepseekAnalysis ?? null,
      score: data.score,
      passed: data.passed ? 1 : 0,
      scorecard: data.scorecard ? JSON.stringify(data.scorecard) : null,
      feedback: data.feedback ? JSON.stringify(data.feedback) : null,
      createdAt: new Date(),
    };
    memoryStore.iterations.push(newIteration);
    return { insertId: newIteration.id };
  }
  
  const result = await db.insert(iterations).values({
    projectId,
    version,
    qwenOutput: data.qwenOutput,
    deepseekAnalysis: data.deepseekAnalysis,
    score: data.score,
    passed: data.passed ? 1 : 0,
    scorecard: data.scorecard ? JSON.stringify(data.scorecard) : null,
    feedback: data.feedback ? JSON.stringify(data.feedback) : null,
  });
  
  return result;
}

export async function getProjectIterations(projectId: number) {
  const db = await getDb();
  if (!db) {
    return memoryStore.iterations
      .filter(i => i.projectId === projectId)
      .sort((a, b) => a.version - b.version);
  }
  
  return await db.select().from(iterations)
    .where(eq(iterations.projectId, projectId))
    .orderBy(asc(iterations.version));
}

// Code References queries
export async function uploadCodeReference(userId: number, data: {
  filename: string;
  category: string;
  description?: string;
  content?: string;
  s3Key?: string;
  s3Url?: string;
  mimeType?: string;
  fileSize?: number;
  analysis?: any;
  tags?: string[];
}) {
  const db = await getDb();
  if (!db) {
    const newRef = {
      id: memoryStore.codeReferences.length + 1,
      userId,
      filename: data.filename,
      category: data.category,
      description: data.description ?? null,
      content: data.content ?? null,
      s3Key: data.s3Key ?? null,
      s3Url: data.s3Url ?? null,
      mimeType: data.mimeType ?? null,
      fileSize: data.fileSize ?? null,
      analysis: data.analysis ? JSON.stringify(data.analysis) : null,
      tags: data.tags ? JSON.stringify(data.tags) : null,
      createdAt: new Date(),
      updatedAt: new Date(),
    };
    memoryStore.codeReferences.push(newRef);
    return { insertId: newRef.id };
  }
  
  const result = await db.insert(codeReferences).values({
    userId,
    filename: data.filename,
    category: data.category as any,
    description: data.description,
    content: data.content,
    s3Key: data.s3Key,
    s3Url: data.s3Url,
    mimeType: data.mimeType,
    fileSize: data.fileSize,
    analysis: data.analysis ? JSON.stringify(data.analysis) : null,
    tags: data.tags ? JSON.stringify(data.tags) : null,
  });
  
  return result;
}

export async function getUserCodeReferences(userId: number, category?: string) {
  const db = await getDb();
  if (!db) {
    return memoryStore.codeReferences
      .filter(r => r.userId === userId && (!category || r.category === category))
      .sort((a, b) => b.createdAt.getTime() - a.createdAt.getTime());
  }
  
  return await db.select().from(codeReferences)
    .where(eq(codeReferences.userId, userId))
    .orderBy(desc(codeReferences.createdAt));
}

export async function searchCodeReferences(userId: number, searchTerm: string) {
  const db = await getDb();
  if (!db) {
    return memoryStore.codeReferences
      .filter(r => r.userId === userId && (r.filename.includes(searchTerm) || (r.description && r.description.includes(searchTerm))))
      .sort((a, b) => b.createdAt.getTime() - a.createdAt.getTime());
  }
  
  return await db.select().from(codeReferences)
    .where(eq(codeReferences.userId, userId))
    .orderBy(desc(codeReferences.createdAt));
}

// Statistics queries
export async function updateDailyStatistics(date: string) {
  const db = await getDb();
  if (!db) return true;
  
  const projectsData = await db.select({
    total: sql`COUNT(*) as total`,
    successful: sql`SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as successful`,
    failed: sql`SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed`,
    avgScore: sql`AVG(finalScore) as avgScore`,
  }).from(projects)
    .where(sql`DATE(createdAt) = ${date}`);
  
  const data = projectsData[0] || {};
  
  await db.insert(statistics).values({
    date,
    totalProjects: (data.total as number) || 0,
    successfulProjects: (data.successful as number) || 0,
    failedProjects: (data.failed as number) || 0,
    avgScore: Math.round((data.avgScore as number) || 0),
  }).onDuplicateKeyUpdate({
    set: {
      totalProjects: (data.total as number) || 0,
      successfulProjects: (data.successful as number) || 0,
      failedProjects: (data.failed as number) || 0,
      avgScore: Math.round((data.avgScore as number) || 0),
    },
  });
  
  return true;
}