import { eq } from "drizzle-orm";
import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
import { InsertUser, users, projects, InsertProject, Project } from "../drizzle/schema";

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

// Lazily create the drizzle instance so local tooling can run without a DB.
export async function getDb() {
  if (!_db && process.env.DATABASE_URL) {
    try {
      // 从DATABASE_URL提取文件路径（移除file:前缀）
      const dbPath = process.env.DATABASE_URL.replace('file:', '');
      const sqlite = new Database(dbPath);
      _db = drizzle(sqlite);
    } catch (error) {
      console.warn("[Database] Failed to connect:", error);
      _db = null;
    }
  }
  return _db;
}

// 本地模式: 简化用户管理,移除Manus相关逻辑
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] Cannot upsert user: database not available");
    return;
  }

  try {
    // SQLite: 使用INSERT OR REPLACE替代onDuplicateKeyUpdate
    const existingUser = await getUserByOpenId(user.openId);
    
    if (existingUser) {
      // 更新现有用户
      const updates: Partial<InsertUser> = {};
      if (user.name !== undefined) updates.name = user.name;
      if (user.email !== undefined) updates.email = user.email;
      if (user.loginMethod !== undefined) updates.loginMethod = user.loginMethod;
      if (user.role !== undefined) updates.role = user.role;
      if (user.lastSignedIn !== undefined) updates.lastSignedIn = user.lastSignedIn;
      
      await db.update(users).set(updates).where(eq(users.openId, user.openId));
    } else {
      // 插入新用户
      await db.insert(users).values({
        openId: user.openId,
        name: user.name || null,
        email: user.email || null,
        loginMethod: user.loginMethod || null,
        role: user.role || "user",
        lastSignedIn: user.lastSignedIn || new Date(),
      });
    }
  } catch (error) {
    console.error("[Database] Failed to upsert user:", error);
    throw error;
  }
}

export async function getUserByOpenId(openId: string) {
  const db = await getDb();
  if (!db) {
    console.warn("[Database] Cannot get user: database not available");
    return undefined;
  }

  const result = await db.select().from(users).where(eq(users.openId, openId)).limit(1);

  return result.length > 0 ? result[0] : undefined;
}

// ============================================================================
// PROJECT QUERIES
// ============================================================================

/**
 * Get all projects
 */
export async function getAllProjects(): Promise<Project[]> {
  const db = await getDb();
  if (!db) {
    console.warn("[Database] Cannot get projects: database not available");
    return [];
  }

  const result = await db.select().from(projects);
  return result;
}

/**
 * Get project by ID
 */
export async function getProjectById(id: number): Promise<Project | undefined> {
  const db = await getDb();
  if (!db) {
    console.warn("[Database] Cannot get project: database not available");
    return undefined;
  }

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

/**
 * Create a new project
 */
export async function createProject(project: InsertProject): Promise<Project> {
  const db = await getDb();
  if (!db) {
    throw new Error("Database not available");
  }

  const result = await db.insert(projects).values(project).returning();
  
  if (!result || result.length === 0) {
    throw new Error("Failed to create project");
  }
  
  return result[0];
}

/**
 * Update an existing project
 */
export async function updateProject(id: number, updates: Partial<InsertProject>): Promise<Project> {
  const db = await getDb();
  if (!db) {
    throw new Error("Database not available");
  }

  const result = await db.update(projects).set(updates).where(eq(projects.id, id)).returning();
  
  if (!result || result.length === 0) {
    throw new Error("Failed to update project");
  }
  
  return result[0];
}

/**
 * Delete a project
 */
export async function deleteProject(id: number): Promise<void> {
  const db = await getDb();
  if (!db) {
    throw new Error("Database not available");
  }

  await db.delete(projects).where(eq(projects.id, id));
}

/**
 * Batch replace all projects (clear and insert new)
 */
export async function batchReplaceProjects(newProjects: InsertProject[]): Promise<void> {
  const db = await getDb();
  if (!db) {
    throw new Error("Database not available");
  }

  // Delete all existing projects
  await db.delete(projects);
  
  // Insert new projects
  if (newProjects.length > 0) {
    await db.insert(projects).values(newProjects);
  }
}

/**
 * Clear all projects
 */
export async function clearAllProjects(): Promise<void> {
  const db = await getDb();
  if (!db) {
    throw new Error("Database not available");
  }

  await db.delete(projects);
}