// lib/utils/db.ts
// ⚠️  SHARED DATABASE — All queries MUST filter by siteId = SITE_ID
//     to prevent reading or writing data that belongs to other websites.
import { cache } from 'react';
import { db } from '@/lib/db';
import { blogPosts, categories } from '@/shared/schema';
import { eq, and, ne, desc, count } from 'drizzle-orm';

// Site ID for this website — used to scope all DB queries
const SITE_ID = 'ten-sura-game.com';

// Article type used by Puck editor components
export interface Article {
  slug: string;
  title: string;
  cardImage?: string | null;
  excerpt?: string | null;
  category?: string | null;
  author?: string | null;
  publicationDate?: Date | null;
  readingTime?: number | null;
  metaDescription?: string | null;
}

/**
 * Cached query wrapper for fetching articles by category
 * Uses React's cache() to deduplicate requests within a single render
 * Supports search and sort order
 * Filtered by SITE_ID to only return this site's content
 */
export const getCategoryArticles = cache(
  async (
    categoryName: string,
    limit: number = 10,
    offset: number = 0,
    filters: {
      sortOrder?: 'asc' | 'desc';
      searchQuery?: string;
    } = {}
  ) => {
    const { sortOrder = 'desc', searchQuery } = filters;
    const { like, asc } = await import('drizzle-orm');

    // Build where conditions — always scoped to this site
    const conditions = [
      eq(blogPosts.siteId, SITE_ID),
      eq(blogPosts.category, categoryName),
      eq(blogPosts.isActive, true),
    ];

    if (searchQuery) {
      conditions.push(like(blogPosts.title, `%${searchQuery}%`));
    }

    return await db
      .select({
        slug: blogPosts.slug,
        title: blogPosts.title,
        excerpt: blogPosts.excerpt,
        cardImage: blogPosts.cardImage,
        publicationDate: blogPosts.publicationDate,
        category: blogPosts.category,
        readingTime: blogPosts.readingTime,
        author: blogPosts.author,
      })
      .from(blogPosts)
      .where(and(...conditions))
      .orderBy(
        sortOrder === 'desc'
          ? desc(blogPosts.publicationDate)
          : asc(blogPosts.publicationDate)
      )
      .limit(limit)
      .offset(offset);
  }
);

/**
 * Cached query wrapper for fetching article count by category
 * Supports search query filtering
 * Filtered by SITE_ID
 */
export const getCategoryArticleCount = cache(
  async (categoryName: string, searchQuery?: string) => {
    const { like } = await import('drizzle-orm');

    const conditions = [
      eq(blogPosts.siteId, SITE_ID),
      eq(blogPosts.category, categoryName),
      eq(blogPosts.isActive, true),
    ];

    if (searchQuery) {
      conditions.push(like(blogPosts.title, `%${searchQuery}%`));
    }

    const result = await db
      .select({ count: count() })
      .from(blogPosts)
      .where(and(...conditions));

    return result[0]?.count || 0;
  }
);

/**
 * Cached query wrapper for fetching latest articles
 * Filtered by SITE_ID
 */
export const getLatestArticles = cache(
  async (limit: number = 12, offset: number = 0) => {
    return await db
      .select({
        slug: blogPosts.slug,
        title: blogPosts.title,
        excerpt: blogPosts.excerpt,
        cardImage: blogPosts.cardImage,
        publicationDate: blogPosts.publicationDate,
        category: blogPosts.category,
        readingTime: blogPosts.readingTime,
        author: blogPosts.author,
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.isActive, true)
        )
      )
      .orderBy(desc(blogPosts.publicationDate))
      .limit(limit)
      .offset(offset);
  }
);

/**
 * Get filtered and sorted articles with search, category filter, and sort order
 * Filtered by SITE_ID
 */
export const getFilteredArticles = cache(
  async (
    limit: number = 12,
    offset: number = 0,
    filters: {
      category?: string;
      sortOrder?: 'asc' | 'desc';
      searchQuery?: string;
    } = {}
  ) => {
    const { category, sortOrder = 'desc', searchQuery } = filters;
    const { like, asc } = await import('drizzle-orm');

    const conditions = [
      eq(blogPosts.siteId, SITE_ID),
      eq(blogPosts.isActive, true),
    ];

    if (category) {
      conditions.push(eq(blogPosts.category, category));
    }

    if (searchQuery) {
      conditions.push(like(blogPosts.title, `%${searchQuery}%`));
    }

    const query = db
      .select({
        slug: blogPosts.slug,
        title: blogPosts.title,
        excerpt: blogPosts.excerpt,
        cardImage: blogPosts.cardImage,
        publicationDate: blogPosts.publicationDate,
        category: blogPosts.category,
        readingTime: blogPosts.readingTime,
        author: blogPosts.author,
      })
      .from(blogPosts)
      .where(and(...conditions))
      .orderBy(
        sortOrder === 'desc'
          ? desc(blogPosts.publicationDate)
          : asc(blogPosts.publicationDate)
      )
      .limit(limit)
      .offset(offset);

    return await query;
  }
);

/**
 * Get count of filtered articles
 * Filtered by SITE_ID
 */
export const getFilteredArticlesCount = cache(
  async (filters: {
    category?: string;
    searchQuery?: string;
  } = {}) => {
    const { category, searchQuery } = filters;
    const { like } = await import('drizzle-orm');

    const conditions = [
      eq(blogPosts.siteId, SITE_ID),
      eq(blogPosts.isActive, true),
    ];

    if (category) {
      conditions.push(eq(blogPosts.category, category));
    }

    if (searchQuery) {
      conditions.push(like(blogPosts.title, `%${searchQuery}%`));
    }

    const result = await db
      .select({ count: count() })
      .from(blogPosts)
      .where(and(...conditions));

    return result[0]?.count || 0;
  }
);

/**
 * Cached query wrapper for fetching featured article
 * Falls back to latest article if none are marked featured
 * Filtered by SITE_ID
 */
export const getFeaturedArticle = cache(
  async () => {
    // Try featured first
    const featured = await db
      .select({
        slug: blogPosts.slug,
        title: blogPosts.title,
        excerpt: blogPosts.excerpt,
        cardImage: blogPosts.cardImage,
        publicationDate: blogPosts.publicationDate,
        category: blogPosts.category,
        readingTime: blogPosts.readingTime,
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.featured, true),
          eq(blogPosts.isActive, true)
        )
      )
      .orderBy(desc(blogPosts.publicationDate))
      .limit(1);

    if (featured[0]) return featured[0];

    // Fallback to latest article
    const latest = await db
      .select({
        slug: blogPosts.slug,
        title: blogPosts.title,
        excerpt: blogPosts.excerpt,
        cardImage: blogPosts.cardImage,
        publicationDate: blogPosts.publicationDate,
        category: blogPosts.category,
        readingTime: blogPosts.readingTime,
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.isActive, true)
        )
      )
      .orderBy(desc(blogPosts.publicationDate))
      .limit(1);

    return latest[0] || null;
  }
);

/**
 * Cached query wrapper for fetching multiple featured articles
 * Falls back to latest articles if none are marked featured
 * Filtered by SITE_ID
 */
export const getFeaturedArticles = cache(
  async (limit: number = 3) => {
    // Try featured first
    const featured = await db
      .select({
        slug: blogPosts.slug,
        title: blogPosts.title,
        excerpt: blogPosts.excerpt,
        cardImage: blogPosts.cardImage,
        publicationDate: blogPosts.publicationDate,
        category: blogPosts.category,
        readingTime: blogPosts.readingTime,
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.featured, true),
          eq(blogPosts.isActive, true)
        )
      )
      .orderBy(desc(blogPosts.publicationDate))
      .limit(limit);

    if (featured.length > 0) return featured;

    // Fallback to latest articles
    return await db
      .select({
        slug: blogPosts.slug,
        title: blogPosts.title,
        excerpt: blogPosts.excerpt,
        cardImage: blogPosts.cardImage,
        publicationDate: blogPosts.publicationDate,
        category: blogPosts.category,
        readingTime: blogPosts.readingTime,
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.isActive, true)
        )
      )
      .orderBy(desc(blogPosts.publicationDate))
      .limit(limit);
  }
);

/**
 * Get all categories with article count for this site
 * Used by Puck library components
 */
export const getAllCategoriesWithCount = cache(
  async () => {
    const { sql } = await import('drizzle-orm');
    const result = await db
      .select({
        name: blogPosts.category,
        count: count(),
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.isActive, true)
        )
      )
      .groupBy(blogPosts.category)
      .orderBy(desc(count()));

    return result.filter((r): r is { name: string; count: number } => r.name !== null);
  }
);

/**
 * Get the primary author name for this site
 */
export const getPrimaryAuthor = cache(
  async () => {
    const result = await db
      .select({ author: blogPosts.author })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.isActive, true)
        )
      )
      .orderBy(desc(blogPosts.publicationDate))
      .limit(1);

    return result[0]?.author || 'ten-sura-game.com編集部';
  }
);

/**
 * Get the latest author data (used by Puck editor data API and AuthorProfile component)
 */
export const getLatestAuthorData = cache(
  async () => {
    const result = await db
      .select({
        author: blogPosts.author,
        authorBio: blogPosts.authorBio,
        authorProfileUrl: blogPosts.authorProfileUrl,
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.isActive, true)
        )
      )
      .orderBy(desc(blogPosts.publicationDate))
      .limit(1);

    if (!result[0]) return null;
    return {
      authorName: result[0].author,
      authorBio: result[0].authorBio,
      authorProfileUrl: result[0].authorProfileUrl,
      socialTwitter: '',
      socialInstagram: '',
      socialYoutube: '',
      socialLinkedin: '',
      socialWebsite: '',
      authorAvatar: '',
      authorRole: '',
    };
  }
);

/**
 * Cached query wrapper for fetching popular posts
 * Returns latest articles ordered by publication date
 * Filtered by SITE_ID
 */
export const getPopularPosts = cache(
  async (limit: number = 5) => {
    return await db
      .select({
        slug: blogPosts.slug,
        title: blogPosts.title,
        cardImage: blogPosts.cardImage,
        publicationDate: blogPosts.publicationDate,
        category: blogPosts.category,
        readingTime: blogPosts.readingTime,
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.isActive, true)
        )
      )
      .orderBy(desc(blogPosts.publicationDate))
      .limit(limit);
  }
);

/**
 * Cached query wrapper for fetching trending articles
 * Returns latest articles ordered by publication date
 * Filtered by SITE_ID
 */
export const getTrendingArticles = cache(
  async (limit: number = 5) => {
    return await db
      .select({
        slug: blogPosts.slug,
        title: blogPosts.title,
        publicationDate: blogPosts.publicationDate,
        readingTime: blogPosts.readingTime,
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.isActive, true)
        )
      )
      .orderBy(desc(blogPosts.publicationDate))
      .limit(limit);
  }
);

/**
 * Cached query wrapper for fetching most read articles
 * Returns articles ordered by views count
 * Filtered by SITE_ID
 */
export const getMostReadArticles = cache(
  async (limit: number = 5) => {
    return await db
      .select({
        slug: blogPosts.slug,
        title: blogPosts.title,
        cardImage: blogPosts.cardImage,
        publicationDate: blogPosts.publicationDate,
        category: blogPosts.category,
        readingTime: blogPosts.readingTime,
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.isActive, true)
        )
      )
      .orderBy(desc(blogPosts.viewsCount), desc(blogPosts.publicationDate))
      .limit(limit);
  }
);

/**
 * Cached query wrapper for fetching article by slug
 * Filtered by SITE_ID
 */
export const getArticleBySlug = cache(
  async (slug: string) => {
    const result = await db
      .select()
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.slug, slug),
          eq(blogPosts.isActive, true)
        )
      )
      .limit(1);

    return result[0] || null;
  }
);

/**
 * Cached query wrapper for fetching related articles
 * Filtered by SITE_ID
 */
export const getRelatedArticles = cache(
  async (category: string | null, currentId: number, limit: number = 4) => {
    if (!category) return [];

    return await db
      .select({
        id: blogPosts.id,
        slug: blogPosts.slug,
        title: blogPosts.title,
        excerpt: blogPosts.excerpt,
        cardImage: blogPosts.cardImage,
        publicationDate: blogPosts.publicationDate,
        category: blogPosts.category,
        readingTime: blogPosts.readingTime,
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.category, category),
          ne(blogPosts.id, currentId),
          eq(blogPosts.isActive, true)
        )
      )
      .orderBy(desc(blogPosts.publicationDate))
      .limit(limit);
  }
);

/**
 * Pagination helper
 * Calculates offset and returns pagination metadata
 */
export function getPaginationParams(
  currentPage: number,
  itemsPerPage: number = 10
) {
  const page = Math.max(1, currentPage);
  const offset = (page - 1) * itemsPerPage;

  return {
    page,
    limit: itemsPerPage,
    offset,
  };
}

/**
 * Calculate total pages from total count
 */
export function calculateTotalPages(
  totalCount: number,
  itemsPerPage: number = 10
) {
  return Math.ceil(totalCount / itemsPerPage);
}

/**
 * Cached query wrapper for fetching all categories for this site
 * Filtered by SITE_ID
 */
export const getAllCategories = cache(
  async () => {
    const { desc } = await import('drizzle-orm');

    return await db
      .select({
        id: categories.id,
        japanese: categories.japanese,
        english: categories.english,
        slug: categories.slug,
        description: categories.description,
      })
      .from(categories)
      .where(eq(categories.siteId, SITE_ID))
      .orderBy(desc(categories.createdAt));
  }
);

/**
 * Get distinct categories that have active articles for this site
 * Filtered by SITE_ID
 */
export const getCategoriesWithArticles = cache(
  async () => {
    const result = await db
      .selectDistinct({ category: blogPosts.category })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.isActive, true)
        )
      );

    // Filter out null categories and return array of category names
    return result
      .map(r => r.category)
      .filter((cat): cat is string => cat !== null)
      .sort();
  }
);

/**
 * Cached query wrapper for fetching category by slug
 * Filtered by SITE_ID
 */
export const getCategoryBySlug = cache(
  async (slug: string) => {
    const { eq } = await import('drizzle-orm');

    const result = await db
      .select()
      .from(categories)
      .where(
        and(
          eq(categories.siteId, SITE_ID),
          eq(categories.slug, slug)
        )
      )
      .limit(1);

    return result[0] || null;
  }
);

/**
 * Cached query wrapper for fetching articles by author
 * Filtered by SITE_ID
 */
export const getArticlesByAuthor = cache(
  async (authorName: string, limit: number = 12, offset: number = 0) => {
    return await db
      .select({
        slug: blogPosts.slug,
        title: blogPosts.title,
        excerpt: blogPosts.excerpt,
        cardImage: blogPosts.cardImage,
        publicationDate: blogPosts.publicationDate,
        category: blogPosts.category,
        readingTime: blogPosts.readingTime,
        author: blogPosts.author,
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.author, authorName),
          eq(blogPosts.isActive, true)
        )
      )
      .orderBy(desc(blogPosts.publicationDate))
      .limit(limit)
      .offset(offset);
  }
);

/**
 * Cached query wrapper for fetching article count by author
 * Filtered by SITE_ID
 */
export const getAuthorArticleCount = cache(
  async (authorName: string) => {
    const result = await db
      .select({ count: count() })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.author, authorName),
          eq(blogPosts.isActive, true)
        )
      );

    return result[0]?.count || 0;
  }
);

/**
 * Cached query wrapper for fetching author bio by author name
 * Returns the most recent article's author data
 * Filtered by SITE_ID
 */
export const getAuthorInfo = cache(
  async (authorName: string) => {
    const result = await db
      .select({
        author: blogPosts.author,
        authorBio: blogPosts.authorBio,
        authorProfileUrl: blogPosts.authorProfileUrl,
      })
      .from(blogPosts)
      .where(
        and(
          eq(blogPosts.siteId, SITE_ID),
          eq(blogPosts.author, authorName),
          eq(blogPosts.isActive, true)
        )
      )
      .orderBy(desc(blogPosts.publicationDate))
      .limit(1);

    return result[0] || null;
  }
);
