import { query } from './db';

export interface BlogPost {
  id: number;
  title: string;
  slug: string;
  excerpt: string | null;
  content: string;
  cover_image: string | null;
  category: string;
  tags: string[];
  author: string;
  is_published: boolean;
  published_at: string | null;
  seo_title: string | null;
  seo_description: string | null;
  created_at: string;
  updated_at: string;
}

export interface BlogCategory {
  category: string;
  count: number;
}

interface GetBlogPostsOptions {
  limit?: number;
  offset?: number;
  category?: string;
  published?: boolean;
}

export async function getBlogPosts(options: GetBlogPostsOptions = {}): Promise<{ posts: BlogPost[]; total: number }> {
  const { limit = 12, offset = 0, category, published = true } = options;
  const conditions: string[] = [];
  const params: any[] = [];
  let i = 1;

  if (published !== undefined) {
    conditions.push(`is_published = $${i++}`);
    params.push(published);
  }
  if (category) {
    conditions.push(`category = $${i++}`);
    params.push(category);
  }

  const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';

  const countResult = await query(`SELECT COUNT(*)::int as total FROM blog_posts ${where}`, params);
  const total = countResult.rows[0]?.total || 0;

  const result = await query<BlogPost>(
    `SELECT * FROM blog_posts ${where} ORDER BY published_at DESC NULLS LAST LIMIT $${i++} OFFSET $${i++}`,
    [...params, limit, offset]
  );

  return { posts: result.rows, total };
}

export async function getBlogPostBySlug(slug: string): Promise<BlogPost | null> {
  const result = await query<BlogPost>(
    'SELECT * FROM blog_posts WHERE slug = $1 AND is_published = true LIMIT 1',
    [slug]
  );
  return result.rows[0] || null;
}

export async function getBlogCategories(): Promise<BlogCategory[]> {
  const result = await query<BlogCategory>(
    `SELECT category, COUNT(*)::int as count FROM blog_posts WHERE is_published = true GROUP BY category ORDER BY count DESC`
  );
  return result.rows;
}

export async function getRelatedPosts(category: string, excludeSlug: string, limit = 3): Promise<BlogPost[]> {
  const result = await query<BlogPost>(
    `SELECT * FROM blog_posts WHERE is_published = true AND category = $1 AND slug != $2 ORDER BY published_at DESC LIMIT $3`,
    [category, excludeSlug, limit]
  );
  return result.rows;
}
