# Database Functions Reference

This document describes the 9 database utility functions that the dynamic Puck components import from `@/lib/utils/db`.

When implementing on a new site, you must either:
1. Map these to **existing** functions in the target site's codebase, OR
2. **Implement them** from scratch against the site's database schema

---

## Common Return Types

### Article

```typescript
interface Article {
  slug: string;           // URL slug (e.g., "my-article-title")
  title: string;          // Article title
  cardImage?: string;     // URL to thumbnail/card image
  excerpt?: string;       // Short description (1-2 sentences)
  category?: string;      // Category name (e.g., "Finance", "Health")
  author?: string;        // Author display name
  publishedAt?: Date;     // ISO date when published
  readingTime?: number;   // Estimated reading time in minutes
}
```

### AuthorInfo

```typescript
interface AuthorInfo {
  authorBio?: string;       // Short biography text
  authorAvatar?: string;    // URL to avatar/profile image
  authorRole?: string;      // Role or title (e.g., "Senior Editor", "Founder")
  authorProfileUrl?: string; // URL to full author profile page
  socialTwitter?: string;   // Twitter / X profile URL
  socialInstagram?: string; // Instagram profile URL
  socialYoutube?: string;   // YouTube channel URL
  socialLinkedin?: string;  // LinkedIn profile URL
  socialWebsite?: string;   // Personal website URL
}
```

### CategoryWithCount

```typescript
interface CategoryWithCount {
  name: string;           // Category display name
  count: number;          // Number of articles in this category
}
```

---

## Function Specifications

### 1. `getLatestArticles(count: number): Promise<Article[]>`

**Used by**: BlogGrid, HeroBlogCarousel, BlogShowcaseGrid

**Description**: Returns the N most recently published articles, ordered by `publishedAt` DESC. Only includes published/active articles.

**Example Drizzle Implementation**:
```typescript
export async function getLatestArticles(count: number) {
  return db
    .select()
    .from(articles)
    .where(eq(articles.status, 'published'))
    .orderBy(desc(articles.publishedAt))
    .limit(count);
}
```

---

### 2. `getTrendingArticles(count: number): Promise<Article[]>`

**Used by**: ArticleGridTrendingRanking, TwoColumnLayout

**Description**: Returns the N most "trending" articles. This could mean:
- Most viewed in the last 7 days (if view tracking exists)
- Most recently updated
- Articles tagged as "trending"

If no trending concept exists, return latest articles as a fallback.

---

### 3. `getMostReadArticles(count: number): Promise<Article[]>`

**Used by**: ArticleGridTrendingRanking, TwoColumnLayout, BlogShowcaseGrid

**Description**: Returns the N most-read articles of all time. Could use:
- View count column
- Total engagement metric
- Manual ordering

If no view tracking exists, return latest articles as a fallback.

---

### 4. `getFeaturedArticle(): Promise<Article | null>`

**Used by**: FeaturedArticleHero

**Description**: Returns the single most important "featured" article. This could be:
- The article with a `featured: true` flag
- The article pinned to the homepage
- The most recent article if no featured system exists

Returns `null` if no featured article is found.

---

### 5. `getFeaturedArticles(count: number): Promise<Article[]>`

**Used by**: FeaturedArticleSpotlight

**Description**: Returns N featured/spotlight articles. Similar to `getFeaturedArticle()` but returns multiple.

---

### 6. `getPrimaryAuthor(): Promise<string>`

**Used by**: AuthorBox

**Description**: Returns the display name of the site's primary/default author. This is typically the site owner or editorial team name.

**Simple implementation**: Just return a hardcoded string if the site has a single author.

```typescript
export async function getPrimaryAuthor() {
  return 'Editorial Team'; // or fetch from a settings table
}
```

---

### 7. `getAuthorInfo(authorName: string): Promise<AuthorInfo | null>`

**Used by**: AuthorBox

**Description**: Returns the biography, avatar, role, and social link URLs for a given author name. Looks up the author in the database by name/slug.

Returns `null` if the author isn't found.

**Note**: Social link fields (`socialTwitter`, `socialInstagram`, `socialYoutube`, `socialLinkedin`, `socialWebsite`) are **optional**. If the target site's database doesn't have these columns, the component will simply not render social links. The `authorRole` and `authorAvatar` fields are also optional — the component falls back to a default icon and no role text if they're missing.

---

### 8. `getAllCategoriesWithCount(): Promise<CategoryWithCount[]>`

**Used by**: ArticleGridCategorySections

**Description**: Returns all article categories with the count of published articles in each. Used to build the category sections grid.

**Example Drizzle Implementation**:
```typescript
export async function getAllCategoriesWithCount() {
  const result = await db
    .select({
      name: articles.category,
      count: sql<number>`count(*)`,
    })
    .from(articles)
    .where(eq(articles.status, 'published'))
    .groupBy(articles.category)
    .orderBy(desc(sql`count(*)`));
  
  return result.filter(r => r.name); // Remove null categories
}
```

---

### 9. `getCategoryArticles(categoryName: string, limit: number): Promise<Article[]>`

**Used by**: ArticleGridCategorySections

**Description**: Returns the N most recent articles for a specific category. Used to populate each category section in the grid.

---

## Fallback Strategy

If a function cannot be meaningfully implemented for the target site (e.g., no "trending" or "featured" concept exists), there are two options:

1. **Return empty**: Return `[]` or `null`. The component will show an empty state. Users can switch to "Manual" mode in the editor to enter content manually.

2. **Fallback to latest**: Return the latest articles instead. This keeps the component populated while the site evolves.

Option 1 is safer. Option 2 is better for first impressions.

---

## Import Path

All 9 server components import from the same path:

```typescript
import { getLatestArticles } from '@/lib/utils/db';
```

If the target site uses a different path for DB utilities (e.g., `@/lib/queries`, `@/db/queries`), you must update the imports in ALL 9 component files listed below:

| File | Functions Imported |
|---|---|
| `BlogGrid.tsx` | `getLatestArticles` |
| `HeroBlogCarousel.tsx` | `getLatestArticles` |
| `BlogShowcaseGrid.tsx` | `getLatestArticles`, `getMostReadArticles` |
| `ArticleGridTrendingRanking.tsx` | `getTrendingArticles`, `getMostReadArticles` |
| `TwoColumnLayout.tsx` | `getTrendingArticles`, `getMostReadArticles` |
| `FeaturedArticleHero.tsx` | `getFeaturedArticle` |
| `FeaturedArticleSpotlight.tsx` | `getFeaturedArticles` |
| `AuthorBox.tsx` | `getPrimaryAuthor`, `getAuthorInfo` |
| `ArticleGridCategorySections.tsx` | `getAllCategoriesWithCount`, `getCategoryArticles` |
