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

/** GET /api/admin/feeds/stats — Overview stats */
export const GET: APIRoute = async () => {
  try {
    const [products, offers, feeds, byProvider, recentSyncs] = await Promise.all([
      query('SELECT COUNT(*) as total FROM products'),
      query('SELECT COUNT(*) as total FROM product_offers WHERE in_stock = true'),
      query('SELECT COUNT(*) as total, COUNT(*) FILTER (WHERE is_enabled) as active FROM feeds'),
      query(
        `SELECT p.slug, p.name, COUNT(f.id) as feed_count,
                COALESCE(SUM(f.product_count), 0) as product_count
         FROM providers p
         LEFT JOIN feeds f ON f.provider_id = p.id AND f.is_enabled = true
         GROUP BY p.id, p.slug, p.name
         ORDER BY p.slug`
      ),
      query(
        `SELECT
           COUNT(*) as total,
           COUNT(*) FILTER (WHERE status = 'success') as successful,
           COUNT(*) FILTER (WHERE status = 'failed') as failed
         FROM sync_logs
         WHERE started_at > NOW() - INTERVAL '24 hours'`
      ),
    ]);

    return new Response(JSON.stringify({
      total_products: parseInt(products.rows[0].total),
      total_offers: parseInt(offers.rows[0].total),
      total_feeds: parseInt(feeds.rows[0].total),
      active_feeds: parseInt(feeds.rows[0].active),
      by_provider: byProvider.rows.reduce((acc: any, r: any) => {
        acc[r.slug] = { name: r.name, feeds: parseInt(r.feed_count), products: parseInt(r.product_count) };
        return acc;
      }, {}),
      recent_syncs: {
        last_24h: parseInt(recentSyncs.rows[0].total),
        successful: parseInt(recentSyncs.rows[0].successful),
        failed: parseInt(recentSyncs.rows[0].failed),
      },
    }), {
      status: 200,
      headers: { 'Content-Type': 'application/json' },
    });
  } catch (err: any) {
    return new Response(JSON.stringify({ error: err.message }), { status: 500 });
  }
};
