import { router, protectedProcedure } from "../_core/trpc.ts";
import { z } from "zod";
import { getDb } from "../db.ts";
import { 
  knowledgeArticles, 
  knowledgeArticleVotes,
  users 
} from "../../drizzle/schema.ts";
import { eq, like, desc } from "drizzle-orm";

export const adminKnowledgeBaseRouter = router({
  // Get knowledge articles with search
  getKnowledgeArticles: protectedProcedure
    .input(
      z.object({
        search: z.string().optional(),
        category: z.string().optional(),
        featured: z.boolean().optional(),
        limit: z.number().default(20),
        offset: z.number().default(0),
      })
    )
    .query(async ({ input }) => {
      const db = await getDb();
      let query = db.select().from(knowledgeArticles);

      if (input.search) {
        query = query.where(like(knowledgeArticles.title, `%${input.search}%`));
      }

      if (input.category) {
        query = query.where(eq(knowledgeArticles.category, input.category));
      }

      if (input.featured !== undefined) {
        query = query.where(eq(knowledgeArticles.featured, input.featured ? 1 : 0));
      }

      const articles = await query
        .orderBy(desc(knowledgeArticles.createdAt))
        .limit(input.limit)
        .offset(input.offset);

      return articles.map(article => ({
        ...article,
        tags: article.tags ? JSON.parse(article.tags as any) : [],
      }));
    }),

  // Get knowledge categories
  getKnowledgeCategories: protectedProcedure.query(async () => {
    const db = await getDb();
    const articles = await db.select({ category: knowledgeArticles.category }).from(knowledgeArticles);
    const categories = [...new Set(articles.map(a => a.category))];
    return categories;
  }),

  // Mark article as helpful/unhelpful
  markArticleHelpful: protectedProcedure
    .input(
      z.object({
        articleId: z.number(),
        helpful: z.boolean(),
      })
    )
    .mutation(async ({ input, ctx }) => {
      const db = await getDb();
      // Check if admin already voted
      const existing = await db
        .select()
        .from(knowledgeArticleVotes)
        .where(
          eq(knowledgeArticleVotes.articleId, input.articleId)
        );

      if (existing.length > 0) {
        // Update existing vote
        await db
          .update(knowledgeArticleVotes)
          .set({ helpful: input.helpful ? 1 : 0 })
          .where(eq(knowledgeArticleVotes.id, existing[0].id));
      } else {
        // Create new vote
        await db.insert(knowledgeArticleVotes).values({
          articleId: input.articleId,
          adminId: ctx.user?.id || 0,
          helpful: input.helpful ? 1 : 0,
        });
      }

      // Update article vote counts
      const votes = await db
        .select()
        .from(knowledgeArticleVotes)
        .where(eq(knowledgeArticleVotes.articleId, input.articleId));

      const helpfulCount = votes.filter(v => v.helpful === 1).length;
      const unhelpfulCount = votes.filter(v => v.helpful === 0).length;

      await db
        .update(knowledgeArticles)
        .set({ 
          helpfulCount,
          unhelpfulCount,
        })
        .where(eq(knowledgeArticles.id, input.articleId));

      return { success: true };
    }),

  // Create article
  createArticle: protectedProcedure
    .input(
      z.object({
        title: z.string(),
        category: z.string(),
        content: z.string(),
        tags: z.array(z.string()).optional(),
        featured: z.boolean().optional(),
      })
    )
    .mutation(async ({ input, ctx }) => {
      const db = await getDb();
      const result = await db.insert(knowledgeArticles).values({
        title: input.title,
        category: input.category,
        content: input.content,
        tags: input.tags ? JSON.stringify(input.tags) : null,
        author: ctx.user?.id || 0,
        featured: input.featured ? 1 : 0,
      });

      return { success: true, articleId: result.insertId };
    }),

  // Update article
  updateArticle: protectedProcedure
    .input(
      z.object({
        articleId: z.number(),
        title: z.string().optional(),
        category: z.string().optional(),
        content: z.string().optional(),
        tags: z.array(z.string()).optional(),
        featured: z.boolean().optional(),
      })
    )
    .mutation(async ({ input }) => {
      const db = await getDb();
      const updates: any = {};
      if (input.title) updates.title = input.title;
      if (input.category) updates.category = input.category;
      if (input.content) updates.content = input.content;
      if (input.tags) updates.tags = JSON.stringify(input.tags);
      if (input.featured !== undefined) updates.featured = input.featured ? 1 : 0;

      await db
        .update(knowledgeArticles)
        .set(updates)
        .where(eq(knowledgeArticles.id, input.articleId));

      return { success: true };
    }),

  // Delete article
  deleteArticle: protectedProcedure
    .input(z.object({ articleId: z.number() }))
    .mutation(async ({ input }) => {
      const db = await getDb();
      // Delete votes first
      await db
        .delete(knowledgeArticleVotes)
        .where(eq(knowledgeArticleVotes.articleId, input.articleId));

      // Then delete article
      await db
        .delete(knowledgeArticles)
        .where(eq(knowledgeArticles.id, input.articleId));

      return { success: true };
    }),

  // Get article by ID
  getArticleById: protectedProcedure
    .input(z.object({ articleId: z.number() }))
    .query(async ({ input }) => {
      const db = await getDb();
      const article = await db
        .select()
        .from(knowledgeArticles)
        .where(eq(knowledgeArticles.id, input.articleId));

      if (!article.length) return null;

      // Increment view count
      await db
        .update(knowledgeArticles)
        .set({ viewCount: article[0].viewCount + 1 })
        .where(eq(knowledgeArticles.id, input.articleId));

      return {
        ...article[0],
        tags: article[0].tags ? JSON.parse(article[0].tags as any) : [],
        viewCount: article[0].viewCount + 1,
      };
    }),

  // Get related articles
  getRelatedArticles: protectedProcedure
    .input(z.object({ articleId: z.number(), limit: z.number().default(5) }))
    .query(async ({ input }) => {
      const db = await getDb();
      const article = await db
        .select()
        .from(knowledgeArticles)
        .where(eq(knowledgeArticles.id, input.articleId));

      if (!article.length) return [];

      const related = await db
        .select()
        .from(knowledgeArticles)
        .where(eq(knowledgeArticles.category, article[0].category))
        .limit(input.limit + 1);

      return related
        .filter(a => a.id !== input.articleId)
        .slice(0, input.limit)
        .map(a => ({
          ...a,
          tags: a.tags ? JSON.parse(a.tags as any) : [],
        }));
    }),

  // Search articles
  searchArticles: protectedProcedure
    .input(z.object({ query: z.string() }))
    .query(async ({ input }) => {
      const db = await getDb();
      const results = await db
        .select()
        .from(knowledgeArticles)
        .where(like(knowledgeArticles.title, `%${input.query}%`));

      return results.map(a => ({
        ...a,
        tags: a.tags ? JSON.parse(a.tags as any) : [],
      }));
    }),

  // Get knowledge base analytics
  getKnowledgeBaseAnalytics: protectedProcedure.query(async () => {
    const db = await getDb();
    const articles = await db.select().from(knowledgeArticles);
    const votes = await db.select().from(knowledgeArticleVotes);

    const totalArticles = articles.length;
    const totalViews = articles.reduce((sum, a) => sum + a.viewCount, 0);
    const totalVotes = votes.length;
    const helpfulVotes = votes.filter(v => v.helpful === 1).length;

    const categories = [...new Set(articles.map(a => a.category))];

    return {
      totalArticles,
      totalViews,
      totalVotes,
      helpfulVotes,
      helpfulPercentage: totalVotes > 0 ? Math.round((helpfulVotes / totalVotes) * 100) : 0,
      averageViewsPerArticle: totalArticles > 0 ? Math.round(totalViews / totalArticles) : 0,
      categories: categories.length,
      featuredArticles: articles.filter(a => a.featured === 1).length,
    };
  }),

  // Increment article views
  incrementArticleViews: protectedProcedure
    .input(z.object({ articleId: z.number() }))
    .mutation(async ({ input }) => {
      const db = await getDb();
      const article = await db
        .select()
        .from(knowledgeArticles)
        .where(eq(knowledgeArticles.id, input.articleId));

      if (!article.length) return { success: false };

      await db
        .update(knowledgeArticles)
        .set({ viewCount: article[0].viewCount + 1 })
        .where(eq(knowledgeArticles.id, input.articleId));

      return { success: true };
    }),

  // Toggle featured status
  toggleFeatured: protectedProcedure
    .input(z.object({ articleId: z.number() }))
    .mutation(async ({ input }) => {
      const db = await getDb();
      const article = await db
        .select()
        .from(knowledgeArticles)
        .where(eq(knowledgeArticles.id, input.articleId));

      if (!article.length) return { success: false };

      await db
        .update(knowledgeArticles)
        .set({ featured: article[0].featured === 1 ? 0 : 1 })
        .where(eq(knowledgeArticles.id, input.articleId));

      return { success: true };
    }),

  // Get featured articles
  getFeaturedArticles: protectedProcedure
    .input(z.object({ limit: z.number().default(5) }))
    .query(async ({ input }) => {
      const db = await getDb();
      const featured = await db
        .select()
        .from(knowledgeArticles)
        .where(eq(knowledgeArticles.featured, 1))
        .orderBy(desc(knowledgeArticles.viewCount))
        .limit(input.limit);

      return featured.map(a => ({
        ...a,
        tags: a.tags ? JSON.parse(a.tags as any) : [],
      }));
    }),
});