import { NextResponse } from 'next/server';
import db from '@/db';

export async function GET() {
  const photos = db.prepare(`
    SELECT 
      m.*,
      COALESCE(b.bookmark_count, 0) as bookmark_count,
      COALESCE(s.avg_rating, 0) as avg_rating,
      COALESCE(s.star_count, 0) as star_count
    FROM media m
    LEFT JOIN (
      SELECT media_id, COUNT(*) as bookmark_count 
      FROM bookmarks 
      GROUP BY media_id
    ) b ON m.id = b.media_id
    LEFT JOIN (
      SELECT 
        media_id, 
        AVG(rating) as avg_rating,
        COUNT(*) as star_count
      FROM stars 
      GROUP BY media_id
    ) s ON m.id = s.media_id
    WHERE m.type = ?
  `).all('photo');
  return NextResponse.json(photos);
}