import { dbGlobal } from "drizzle-pkg/lib/db";
import { articles, articleItems } from "drizzle-pkg/lib/schema/collection";
import { items, itemTags, tags } from "drizzle-pkg/lib/schema/collection";
import { eq, and, desc, inArray } from "drizzle-orm";
import crypto from "node:crypto";

export async function listArticles(userId: number) {
  return dbGlobal
    .select()
    .from(articles)
    .where(eq(articles.userId, userId))
    .orderBy(desc(articles.updatedAt));
}

export async function getArticle(id: number, userId: number) {
  const [article] = await dbGlobal
    .select()
    .from(articles)
    .where(and(eq(articles.id, id), eq(articles.userId, userId)));

  if (!article) return null;

  // Load linked items
  const linkedItems = await dbGlobal
    .select()
    .from(articleItems)
    .innerJoin(items, eq(articleItems.itemId, items.id))
    .where(eq(articleItems.articleId, id));

  return { ...article, linkedItems };
}

export async function createArticle(userId: number, data: { title?: string; content?: string }) {
  const [article] = await dbGlobal
    .insert(articles)
    .values({
      userId,
      title: data.title || "无标题文章",
      content: data.content || "",
      wordCount: (data.content || "").length,
    })
    .returning();
  return article;
}

export async function updateArticle(
  id: number,
  userId: number,
  data: { title?: string; content?: string; status?: "draft" | "published"; coverUrl?: string }
) {
  const [existing] = await dbGlobal
    .select({ id: articles.id })
    .from(articles)
    .where(and(eq(articles.id, id), eq(articles.userId, userId)));

  if (!existing) return null;

  const updateData: Record<string, any> = { ...data };
  if (data.content !== undefined) {
    updateData.wordCount = data.content.length;
  }

  await dbGlobal.update(articles).set(updateData).where(eq(articles.id, id));
  return getArticle(id, userId);
}

export async function deleteArticle(id: number, userId: number) {
  const [existing] = await dbGlobal
    .select({ id: articles.id })
    .from(articles)
    .where(and(eq(articles.id, id), eq(articles.userId, userId)));

  if (!existing) return false;
  await dbGlobal.delete(articles).where(eq(articles.id, id));
  return true;
}

export async function generateShareToken(id: number, userId: number) {
  const token = crypto.randomUUID().replace(/-/g, "").substring(0, 12);
  await dbGlobal
    .update(articles)
    .set({ shareToken: token })
    .where(and(eq(articles.id, id), eq(articles.userId, userId)));
  return token;
}

export async function getArticleByShareToken(token: string) {
  const [article] = await dbGlobal
    .select()
    .from(articles)
    .where(eq(articles.shareToken, token));

  if (!article) return null;

  const linkedItems = await dbGlobal
    .select()
    .from(articleItems)
    .innerJoin(items, eq(articleItems.itemId, items.id))
    .where(eq(articleItems.articleId, article.id));

  return { ...article, linkedItems };
}