import { eq, desc, and } from "drizzle-orm";
import { drizzle } from "drizzle-orm/mysql2";
import { InsertUser, users, projects, articles, contacts, articleViews, InsertProject, InsertArticle, InsertContact, InsertArticleView } 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;
}

// Portfolio queries
export async function getAllProjects(publishedOnly = true) {
  const db = await getDb();
  if (!db) return [];

  const conditions = publishedOnly ? eq(projects.published, true) : undefined;
  const result = await db
    .select()
    .from(projects)
    .where(conditions)
    .orderBy(desc(projects.completionDate));

  return result;
}

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

  const result = await db
    .select()
    .from(projects)
    .where(and(eq(projects.published, true), eq(projects.featured, true)))
    .orderBy(desc(projects.completionDate))
    .limit(3);

  return result;
}

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

  const result = await db
    .select()
    .from(projects)
    .where(eq(projects.slug, slug))
    .limit(1);

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

export async function createProject(project: InsertProject) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  const result = await db.insert(projects).values(project);
  return result;
}

export async function updateProject(id: number, updates: Partial<InsertProject>) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  await db.update(projects).set(updates).where(eq(projects.id, id));
}

export async function deleteProject(id: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  await db.delete(projects).where(eq(projects.id, id));
}

// Blog queries
export async function getAllArticles(publishedOnly = true) {
  const db = await getDb();
  if (!db) return [];

  const conditions = publishedOnly ? eq(articles.published, true) : undefined;
  const result = await db
    .select()
    .from(articles)
    .where(conditions)
    .orderBy(desc(articles.publishedAt));

  return result;
}

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

  const result = await db
    .select()
    .from(articles)
    .where(eq(articles.published, true))
    .orderBy(desc(articles.publishedAt))
    .limit(limit);

  return result;
}

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

  const result = await db
    .select()
    .from(articles)
    .where(eq(articles.slug, slug))
    .limit(1);

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

export async function createArticle(article: InsertArticle) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  const result = await db.insert(articles).values(article);
  return Number(result[0].insertId);
}

// Contact queries
export async function createContact(contact: InsertContact) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  const result = await db.insert(contacts).values(contact);
  return result;
}

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

  const result = await db
    .select()
    .from(contacts)
    .orderBy(desc(contacts.createdAt));

  return result;
}

export async function updateArticle(id: number, updates: Partial<InsertArticle>) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  await db.update(articles).set(updates).where(eq(articles.id, id));
}

export async function deleteArticle(id: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  await db.delete(articles).where(eq(articles.id, id));
}

export async function publishArticle(id: number, published: boolean) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  const updates: any = {
    published,
    publishedAt: published ? new Date() : undefined,
  };
  await db.update(articles).set(updates).where(eq(articles.id, id));
}

// Analytics queries
export async function trackArticleView(view: InsertArticleView) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");

  await db.insert(articleViews).values(view);
}

export async function getArticleViewCount(articleId: number) {
  const db = await getDb();
  if (!db) return 0;

  const result = await db
    .select()
    .from(articleViews)
    .where(eq(articleViews.articleId, articleId));

  return result.length;
}

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

  const result = await db.select().from(articleViews);
  return result.length;
}

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

  // Get view counts grouped by article
  const viewCounts = await db
    .select()
    .from(articleViews);

  // Count views per article
  const articleViewCounts = viewCounts.reduce((acc: Record<number, number>, view) => {
    acc[view.articleId] = (acc[view.articleId] || 0) + 1;
    return acc;
  }, {});

  // Sort by view count and get top articles
  const topArticleIds = Object.entries(articleViewCounts)
    .sort(([, a], [, b]) => (b as number) - (a as number))
    .slice(0, limit)
    .map(([id]) => parseInt(id));

  if (topArticleIds.length === 0) return [];

  // Fetch article details
  const topArticles = [];
  for (const articleId of topArticleIds) {
    const article = await db
      .select()
      .from(articles)
      .where(eq(articles.id, articleId))
      .limit(1);

    if (article.length > 0) {
      topArticles.push({
        ...article[0],
        viewCount: articleViewCounts[articleId],
      });
    }
  }

  return topArticles;
}

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

  const startDate = new Date();
  startDate.setDate(startDate.getDate() - days);

  const views = await db
    .select()
    .from(articleViews)
    .orderBy(articleViews.viewedAt);

  // Group by date
  const viewsByDate = views.reduce((acc: Record<string, number>, view) => {
    const date = view.viewedAt.toISOString().split('T')[0];
    acc[date!] = (acc[date!] || 0) + 1;
    return acc;
  }, {});

  return Object.entries(viewsByDate).map(([date, count]) => ({
    date,
    views: count,
  }));
}

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

  const allArticles = await db.select().from(articles);
  const allViews = await db.select().from(articleViews);

  // Count views per article
  const articleViewCounts = allViews.reduce((acc: Record<number, number>, view) => {
    acc[view.articleId] = (acc[view.articleId] || 0) + 1;
    return acc;
  }, {});

  // Group by category
  const categoryStats = allArticles.reduce((acc: Record<string, { articles: number; views: number }>, article) => {
    if (!acc[article.category]) {
      acc[article.category] = { articles: 0, views: 0 };
    }
    acc[article.category]!.articles += 1;
    acc[article.category]!.views += articleViewCounts[article.id] || 0;
    return acc;
  }, {});

  return Object.entries(categoryStats).map(([category, stats]) => ({
    category,
    ...stats,
  }));
}

export async function getBlogStats() {
  const db = await getDb();
  if (!db) return {
    totalArticles: 0,
    publishedArticles: 0,
    draftArticles: 0,
    totalViews: 0,
    avgViewsPerArticle: 0,
  };

  const allArticles = await db.select().from(articles);
  const allViews = await db.select().from(articleViews);

  const publishedArticles = allArticles.filter(a => a.published).length;
  const draftArticles = allArticles.filter(a => !a.published).length;

  return {
    totalArticles: allArticles.length,
    publishedArticles,
    draftArticles,
    totalViews: allViews.length,
    avgViewsPerArticle: allArticles.length > 0 ? Math.round(allViews.length / allArticles.length) : 0,
  };
}