import { eq, desc, and, like, sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import { InsertUser, users, articles, categories, comments, tags, articleTags, siteStats } 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;
}

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

// ============ ARTICLES ============

export async function getArticles(limit: number = 10, offset: number = 0, categoryId?: number) {
  const db = await getDb();
  if (!db) return [];

  try {
    const conditions: any[] = [eq(articles.isPublished, true)];
    if (categoryId) {
      conditions.push(eq(articles.categoryId, categoryId));
    }
    const result = await db.select().from(articles)
      .where(and(...conditions))
      .orderBy(desc(articles.publishedAt))
      .limit(limit)
      .offset(offset);
    return result;
  } catch (error) {
    console.error("[Database] Failed to get articles:", error);
    return [];
  }
}

export async function getFeaturedArticles(limit: number = 3) {
  const db = await getDb();
  if (!db) return [];

  try {
    const result = await db.select().from(articles)
      .where(and(eq(articles.isPublished, true), eq(articles.isFeatured, true)))
      .orderBy(desc(articles.publishedAt))
      .limit(limit);
    return result;
  } catch (error) {
    console.error("[Database] Failed to get featured articles:", error);
    return [];
  }
}

export async function getTrendingArticles(limit: number = 5) {
  const db = await getDb();
  if (!db) return [];

  try {
    const result = await db.select().from(articles)
      .where(eq(articles.isPublished, true))
      .orderBy(desc(articles.viewsCount))
      .limit(limit);
    return result;
  } catch (error) {
    console.error("[Database] Failed to get trending articles:", error);
    return [];
  }
}

export async function getArticleBySlug(slug: string) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    const result = await db.select().from(articles).where(eq(articles.slug, slug)).limit(1);
    return result.length > 0 ? result[0] : undefined;
  } catch (error) {
    console.error("[Database] Failed to get article by slug:", error);
    return undefined;
  }
}

export async function getArticleById(id: number) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    const result = await db.select().from(articles).where(eq(articles.id, id)).limit(1);
    return result.length > 0 ? result[0] : undefined;
  } catch (error) {
    console.error("[Database] Failed to get article by id:", error);
    return undefined;
  }
}

export async function createArticle(data: {
  title: string;
  slug: string;
  excerpt?: string;
  content: string;
  featuredImage?: string;
  categoryId: number;
  authorId: number;
  isFeatured?: boolean;
}) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    const result = await db.insert(articles).values(data);
    return result;
  } catch (error) {
    console.error("[Database] Failed to create article:", error);
    throw error;
  }
}

export async function updateArticle(id: number, data: Partial<typeof articles.$inferInsert>) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    const result = await db.update(articles).set(data).where(eq(articles.id, id));
    return result;
  } catch (error) {
    console.error("[Database] Failed to update article:", error);
    throw error;
  }
}

export async function deleteArticle(id: number) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    await db.delete(articleTags).where(eq(articleTags.articleId, id));
    await db.delete(comments).where(eq(comments.articleId, id));
    const result = await db.delete(articles).where(eq(articles.id, id));
    return result;
  } catch (error) {
    console.error("[Database] Failed to delete article:", error);
    throw error;
  }
}

export async function incrementArticleViews(id: number) {
  const db = await getDb();
  if (!db) return;

  try {
    await db.update(articles).set({
      viewsCount: sql`${articles.viewsCount} + 1`
    }).where(eq(articles.id, id));
  } catch (error) {
    console.error("[Database] Failed to increment article views:", error);
  }
}

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

  try {
    const result = await db.select().from(articles)
      .where(and(
        eq(articles.isPublished, true),
        like(articles.title, `%${query}%`)
      ))
      .orderBy(desc(articles.publishedAt))
      .limit(limit);
    return result;
  } catch (error) {
    console.error("[Database] Failed to search articles:", error);
    return [];
  }
}

// ============ CATEGORIES ============

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

  try {
    const result = await db.select().from(categories).orderBy(categories.name);
    return result;
  } catch (error) {
    console.error("[Database] Failed to get categories:", error);
    return [];
  }
}

export async function getCategoryBySlug(slug: string) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    const result = await db.select().from(categories).where(eq(categories.slug, slug)).limit(1);
    return result.length > 0 ? result[0] : undefined;
  } catch (error) {
    console.error("[Database] Failed to get category by slug:", error);
    return undefined;
  }
}

export async function createCategory(data: { name: string; slug: string; description?: string }) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    const result = await db.insert(categories).values(data);
    return result;
  } catch (error) {
    console.error("[Database] Failed to create category:", error);
    throw error;
  }
}

export async function updateCategory(id: number, data: Partial<typeof categories.$inferInsert>) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    const result = await db.update(categories).set(data).where(eq(categories.id, id));
    return result;
  } catch (error) {
    console.error("[Database] Failed to update category:", error);
    throw error;
  }
}

export async function deleteCategory(id: number) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    const result = await db.delete(categories).where(eq(categories.id, id));
    return result;
  } catch (error) {
    console.error("[Database] Failed to delete category:", error);
    throw error;
  }
}

// ============ COMMENTS ============

export async function getArticleComments(articleId: number, limit: number = 50) {
  const db = await getDb();
  if (!db) return [];

  try {
    const result = await db.select().from(comments)
      .where(and(eq(comments.articleId, articleId), eq(comments.isApproved, true)))
      .orderBy(desc(comments.createdAt))
      .limit(limit);
    return result;
  } catch (error) {
    console.error("[Database] Failed to get article comments:", error);
    return [];
  }
}

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

  try {
    const result = await db.select().from(comments)
      .orderBy(desc(comments.createdAt))
      .limit(limit)
      .offset(offset);
    return result;
  } catch (error) {
    console.error("[Database] Failed to get all comments:", error);
    return [];
  }
}

export async function createComment(data: { content: string; articleId: number; userId: number }) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    const result = await db.insert(comments).values(data);
    return result;
  } catch (error) {
    console.error("[Database] Failed to create comment:", error);
    throw error;
  }
}

export async function approveComment(id: number) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    const result = await db.update(comments).set({ isApproved: true }).where(eq(comments.id, id));
    return result;
  } catch (error) {
    console.error("[Database] Failed to approve comment:", error);
    throw error;
  }
}

export async function deleteComment(id: number) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    const result = await db.delete(comments).where(eq(comments.id, id));
    return result;
  } catch (error) {
    console.error("[Database] Failed to delete comment:", error);
    throw error;
  }
}

// ============ TAGS ============

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

  try {
    const result = await db.select().from(tags).orderBy(tags.name);
    return result;
  } catch (error) {
    console.error("[Database] Failed to get tags:", error);
    return [];
  }
}

export async function createTag(data: { name: string; slug: string }) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    const result = await db.insert(tags).values(data);
    return result;
  } catch (error) {
    console.error("[Database] Failed to create tag:", error);
    throw error;
  }
}

export async function deleteTag(id: number) {
  const db = await getDb();
  if (!db) return undefined;

  try {
    await db.delete(articleTags).where(eq(articleTags.tagId, id));
    const result = await db.delete(tags).where(eq(tags.id, id));
    return result;
  } catch (error) {
    console.error("[Database] Failed to delete tag:", error);
    throw error;
  }
}

// ============ STATS ============

export async function getSiteStats() {
  const db = await getDb();
  if (!db) return undefined;

  try {
    const result = await db.select().from(siteStats).limit(1);
    return result.length > 0 ? result[0] : undefined;
  } catch (error) {
    console.error("[Database] Failed to get site stats:", error);
    return undefined;
  }
}

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

  try {
    const totalArticles = await db.select({ count: sql`COUNT(*)` }).from(articles);
    const totalComments = await db.select({ count: sql`COUNT(*)` }).from(comments);
    const totalUsers = await db.select({ count: sql`COUNT(*)` }).from(users);
    const totalViews = await db.select({ sum: sql`SUM(${articles.viewsCount})` }).from(articles);

    const stats = await getSiteStats();
    if (stats) {
      await db.update(siteStats).set({
        totalArticles: Number((totalArticles[0]?.count as any) || 0),
        totalComments: Number((totalComments[0]?.count as any) || 0),
        totalUsers: Number((totalUsers[0]?.count as any) || 0),
        totalViews: Number((totalViews[0]?.sum as any) || 0),
      }).where(eq(siteStats.id, stats.id));
    } else {
      await db.insert(siteStats).values({
        totalArticles: Number((totalArticles[0]?.count as any) || 0),
        totalComments: Number((totalComments[0]?.count as any) || 0),
        totalUsers: Number((totalUsers[0]?.count as any) || 0),
        totalViews: Number((totalViews[0]?.sum as any) || 0),
      });
    }
  } catch (error) {
    console.error("[Database] Failed to update site stats:", error);
  }
}