import { eq, desc, and } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import {
  InsertUser,
  users,
  conversations,
  messages,
  images,
  feedback,
} from "../drizzle/schema";
import { ENV } from "./_core/env";

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 {
      _db = drizzle(process.env.DATABASE_URL);
    } catch (error) {
      console.warn("[Database] Failed to connect:", error);
      _db = null;
    }
  }
  return _db;
}

/**
 * Section 2: User Management
 */
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";
    }

    // Set user tier
    if (user.tier !== undefined) {
      values.tier = user.tier;
      updateSet.tier = user.tier;
    }

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

/**
 * Section 2: Conversation Management
 */
export async function createConversation(
  userId: number,
  title?: string,
  mode: "ask" | "imagine" = "ask"
) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  const result = await db.insert(conversations).values({
    userId,
    title: title || `Conversation ${new Date().toLocaleDateString()}`,
    mode,
  });

  return result;
}

export async function getUserConversations(userId: number) {
  const db = await getDb();
  if (!db) return [];

  return await db
    .select()
    .from(conversations)
    .where(eq(conversations.userId, userId))
    .orderBy(desc(conversations.updatedAt));
}

export async function getConversationById(conversationId: number) {
  const db = await getDb();
  if (!db) return null;

  const result = await db
    .select()
    .from(conversations)
    .where(eq(conversations.id, conversationId))
    .limit(1);

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

/**
 * Section 2: Message Management
 */
export async function saveMessage(
  conversationId: number,
  role: "user" | "assistant",
  content: string,
  reasoning?: string,
  metadata?: Record<string, unknown>
) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  return await db.insert(messages).values({
    conversationId,
    role,
    content,
    reasoning,
    metadata: metadata ? JSON.stringify(metadata) : null,
  });
}

export async function getConversationMessages(conversationId: number) {
  const db = await getDb();
  if (!db) return [];

  return await db
    .select()
    .from(messages)
    .where(eq(messages.conversationId, conversationId))
    .orderBy(messages.createdAt);
}

export async function getLastMessage(conversationId: number) {
  const db = await getDb();
  if (!db) return null;

  const result = await db
    .select()
    .from(messages)
    .where(eq(messages.conversationId, conversationId))
    .orderBy(desc(messages.createdAt))
    .limit(1);

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

/**
 * Section 8: Image Management
 */
export async function saveImage(
  userId: number,
  prompt: string,
  url: string,
  conversationId?: number,
  metadata?: Record<string, unknown>
) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  return await db.insert(images).values({
    userId,
    conversationId,
    prompt,
    url,
    metadata: metadata ? JSON.stringify(metadata) : null,
  });
}

export async function getUserImages(userId: number, limit = 20) {
  const db = await getDb();
  if (!db) return [];

  return await db
    .select()
    .from(images)
    .where(eq(images.userId, userId))
    .orderBy(desc(images.createdAt))
    .limit(limit);
}

export async function getConversationImages(conversationId: number) {
  const db = await getDb();
  if (!db) return [];

  return await db
    .select()
    .from(images)
    .where(eq(images.conversationId, conversationId))
    .orderBy(desc(images.createdAt));
}

/**
 * Section 2: Feedback Management (for Google Sheets logging)
 */
export async function saveFeedback(
  userId: number,
  rating: "like" | "dislike",
  messageId?: number,
  imageId?: number,
  comment?: string
) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  return await db.insert(feedback).values({
    userId,
    messageId,
    imageId,
    rating,
    comment,
  });
}

export async function getUserFeedback(userId: number, limit = 100) {
  const db = await getDb();
  if (!db) return [];

  return await db
    .select()
    .from(feedback)
    .where(eq(feedback.userId, userId))
    .orderBy(desc(feedback.createdAt))
    .limit(limit);
}

export async function getRecentFeedback(limit = 50) {
  const db = await getDb();
  if (!db) return [];

  return await db
    .select()
    .from(feedback)
    .orderBy(desc(feedback.createdAt))
    .limit(limit);
}

/**
 * Industrial Standard: Analytics and Monitoring
 */
export async function getUserStats(userId: number) {
  const db = await getDb();
  if (!db) return null;

  const userConversations = await db
    .select()
    .from(conversations)
    .where(eq(conversations.userId, userId));

  const userMessages = await db
    .select()
    .from(messages)
    .where(
      eq(
        messages.conversationId,
        userConversations.length > 0 ? userConversations[0].id : -1
      )
    );

  const userImages = await db
    .select()
    .from(images)
    .where(eq(images.userId, userId));

  const userFeedback = await db
    .select()
    .from(feedback)
    .where(eq(feedback.userId, userId));

  return {
    totalConversations: userConversations.length,
    totalMessages: userMessages.length,
    totalImages: userImages.length,
    totalFeedback: userFeedback.length,
    likes: userFeedback.filter((f) => f.rating === "like").length,
    dislikes: userFeedback.filter((f) => f.rating === "dislike").length,
  };
}

export async function getSystemStats() {
  const db = await getDb();
  if (!db) return null;

  const totalUsers = await db.select().from(users);
  const totalConversations = await db.select().from(conversations);
  const totalMessages = await db.select().from(messages);
  const totalImages = await db.select().from(images);
  const totalFeedback = await db.select().from(feedback);

  return {
    totalUsers: totalUsers.length,
    totalConversations: totalConversations.length,
    totalMessages: totalMessages.length,
    totalImages: totalImages.length,
    totalFeedback: totalFeedback.length,
  };
}