import { and, asc, desc, eq } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import {
  Attachment,
  attachments,
  ChatMessage,
  Conversation,
  InsertUser,
  messages,
  users,
  conversations,
} 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;
  }

  const values: InsertUser = { openId: user.openId };
  const updateSet: Record<string, unknown> = {};
  const textFields = ["name", "email", "loginMethod"] as const;
  type TextField = (typeof textFields)[number];

  for (const field of textFields) {
    const value = user[field];
    if (value !== undefined) {
      const normalized = value ?? null;
      values[field] = normalized;
      updateSet[field] = normalized;
    }
  }

  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[0];
}

function requireDb() {
  if (!_db) throw new Error("Database is not available");
  return _db;
}

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

export async function getConversation(userId: number, conversationId: number) {
  const db = await getDb();
  if (!db) return undefined;
  const result = await db
    .select()
    .from(conversations)
    .where(and(eq(conversations.id, conversationId), eq(conversations.userId, userId)))
    .limit(1);
  return result[0];
}

export async function createConversation(userId: number, title = "New conversation") {
  const db = await getDb();
  if (!db) throw new Error("Database is not available");
  const result = await db.insert(conversations).values({ userId, title });
  const insertId = Number((result as unknown as { insertId?: number }).insertId ?? 0);
  return getConversation(userId, insertId);
}

export async function renameConversation(userId: number, conversationId: number, title: string) {
  const db = await getDb();
  if (!db) throw new Error("Database is not available");
  await db
    .update(conversations)
    .set({ title, updatedAt: new Date() })
    .where(and(eq(conversations.id, conversationId), eq(conversations.userId, userId)));
  return getConversation(userId, conversationId);
}

export async function touchConversation(userId: number, conversationId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database is not available");
  await db
    .update(conversations)
    .set({ updatedAt: new Date() })
    .where(and(eq(conversations.id, conversationId), eq(conversations.userId, userId)));
}

export async function addMessage(
  userId: number,
  conversationId: number,
  role: "user" | "assistant",
  content: string,
) {
  const conversation = await getConversation(userId, conversationId);
  if (!conversation) throw new Error("Conversation not found");

  const db = requireDb();
  const result = await db.insert(messages).values({ conversationId, role, content });
  const insertId = Number((result as unknown as { insertId?: number }).insertId ?? 0);
  await touchConversation(userId, conversationId);
  return insertId;
}

export async function addAttachment(input: {
  userId: number;
  conversationId: number;
  messageId: number;
  fileName: string;
  mimeType: string;
  fileSize: number;
  storageKey: string;
  storageUrl: string;
}) {
  const conversation = await getConversation(input.userId, input.conversationId);
  if (!conversation) throw new Error("Conversation not found");

  const db = requireDb();
  await db.insert(attachments).values({
    conversationId: input.conversationId,
    messageId: input.messageId,
    fileName: input.fileName,
    mimeType: input.mimeType,
    fileSize: input.fileSize,
    storageKey: input.storageKey,
    storageUrl: input.storageUrl,
  });
}

export type MessageWithAttachments = ChatMessage & { attachments: Attachment[] };

export async function listMessages(userId: number, conversationId: number): Promise<MessageWithAttachments[]> {
  const conversation = await getConversation(userId, conversationId);
  if (!conversation) throw new Error("Conversation not found");

  const db = requireDb();
  const rows = await db
    .select()
    .from(messages)
    .where(eq(messages.conversationId, conversationId))
    .orderBy(asc(messages.createdAt));

  const result: MessageWithAttachments[] = [];
  for (const message of rows) {
    const refs = await db
      .select()
      .from(attachments)
      .where(eq(attachments.messageId, message.id));
    result.push({ ...message, attachments: refs });
  }
  return result;
}