import type { APIRoute } from 'astro';
import { query } from '../../../../lib/db';

export const GET: APIRoute = async ({ url }) => {
  try {
    const feedId = url.searchParams.get('feed_id');

    // Per-feed health overview
    const feeds = await query(`
      SELECT f.id, f.name, f.is_enabled, f.last_sync_at, f.last_sync_status, f.last_error, f.product_count,
        sl.duration, sl.products_fetched, sl.products_new, sl.products_updated, sl.products_removed, sl.errors_count,
        CASE WHEN f.last_sync_at < NOW() - INTERVAL '24 hours' THEN true ELSE false END as stale,
        CASE WHEN sl.errors_count > 10 THEN true ELSE false END as high_errors,
        COALESCE(sneaker_ct.cnt, 0) as sneaker_count,
        CASE WHEN f.product_count > 0 THEN ROUND(COALESCE(sneaker_ct.cnt, 0)::numeric / f.product_count * 100, 1) ELSE 0 END as sneaker_ratio
      FROM feeds f
      LEFT JOIN LATERAL (
        SELECT finished_at - started_at as duration, products_fetched, products_new, products_updated, products_removed, errors_count
        FROM sync_logs WHERE feed_id = f.id ORDER BY started_at DESC LIMIT 1
      ) sl ON true
      LEFT JOIN (
        SELECT po.feed_id, COUNT(DISTINCT p.id) as cnt
        FROM product_offers po JOIN products p ON po.product_id = p.id
        WHERE p.is_sneaker = true GROUP BY po.feed_id
      ) sneaker_ct ON sneaker_ct.feed_id = f.id
      ORDER BY f.name
    `);

    // Sync history for specific feed or all
    if (feedId) {
      const history = await query(`
        SELECT started_at, finished_at, status, products_fetched, products_new, products_updated, products_removed, errors_count
        FROM sync_logs WHERE feed_id = $1 ORDER BY started_at DESC LIMIT 30
      `, [feedId]);
      return Response.json({ feeds: feeds.rows, history: history.rows });
    }

    return Response.json({ feeds: feeds.rows });
  } catch (err: any) {
    return Response.json({ error: err.message || 'Query failed' }, { status: 500 });
  }
};
