import { eq, desc, and } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import { InsertUser, users, documents, InsertDocument, Document, files, File, InsertFile } 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;
}

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 {
    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) { 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;
}

// ─── File helpers ────────────────────────────────────────────────────────

export async function getFilesByUser(userId: number): Promise<File[]> {
  const db = await getDb();
  if (!db) return [];
  return db.select().from(files).where(eq(files.userId, userId)).orderBy(desc(files.updatedAt));
}

export async function getFileById(id: number, userId: number): Promise<File | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(files)
    .where(and(eq(files.id, id), eq(files.userId, userId)))
    .limit(1);
  return result[0];
}

export async function createFile(userId: number, name: string): Promise<number> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const result = await db.insert(files).values({ userId, name });
  return (result as any)[0].insertId as number;
}

export async function updateFile(id: number, userId: number, name: string): Promise<void> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.update(files).set({ name }).where(and(eq(files.id, id), eq(files.userId, userId)));
}

export async function deleteFile(id: number, userId: number): Promise<void> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  // Delete all documents in this file first
  await db.delete(documents).where(eq(documents.fileId, id));
  // Then delete the file
  await db.delete(files).where(and(eq(files.id, id), eq(files.userId, userId)));
}

// ─── Document helpers ────────────────────────────────────────────────────────

export async function getDocumentsByUser(userId: number, fileId?: number): Promise<Document[]> {
  const db = await getDb();
  if (!db) return [];
  if (fileId !== undefined) {
    return db.select().from(documents)
      .where(and(eq(documents.userId, userId), eq(documents.fileId, fileId)))
      .orderBy(desc(documents.updatedAt));
  }
  return db.select().from(documents)
    .where(eq(documents.userId, userId))
    .orderBy(desc(documents.updatedAt));
}

export async function getDocumentById(id: number, userId: number): Promise<Document | undefined> {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db.select().from(documents)
    .where(and(eq(documents.id, id), eq(documents.userId, userId)))
    .limit(1);
  return result[0];
}

export async function createDocument(userId: number, title: string, wordCountGoal: number, fileId?: number): Promise<number> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const result = await db.insert(documents).values({ userId, title, content: '', wordCountGoal, fileId });
  return (result as any)[0].insertId as number;
}

export async function saveDocument(id: number, userId: number, data: { title?: string; content?: string; wordCountGoal?: number; fileId?: number }): Promise<void> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  const updateSet: Partial<InsertDocument> = {};
  if (data.title !== undefined) updateSet.title = data.title;
  if (data.content !== undefined) updateSet.content = data.content;
  if (data.wordCountGoal !== undefined) updateSet.wordCountGoal = data.wordCountGoal;
  if (data.fileId !== undefined) updateSet.fileId = data.fileId;
  if (Object.keys(updateSet).length === 0) return;
  await db.update(documents).set(updateSet).where(and(eq(documents.id, id), eq(documents.userId, userId)));
}

export async function deleteDocument(id: number, userId: number): Promise<void> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  await db.delete(documents).where(and(eq(documents.id, id), eq(documents.userId, userId)));
}