import { NextResponse } from 'next/server';
import pool from '../../../lib/db';

export async function GET(request: Request) {
    try {
        const { searchParams } = new URL(request.url);
        const filter = searchParams.get('filter') || 'all'; // 'viral48h', 'viral7d', 'all'

        // Base query: get the latest stats snapshot per channel
        let query = `
          SELECT
            c.id, c.title, c.description, c.thumbnailUrl, c.publishedAt,
            s.viewCount, s.subscriberCount, s.videoCount,
            s_2d.viewCount AS viewCount2dAgo,
            s_2d.subscriberCount AS subscriberCount2dAgo,
            s_7d.viewCount AS viewCount7dAgo,
            s_7d.subscriberCount AS subscriberCount7dAgo
          FROM channels c
          JOIN channel_stats s ON c.id = s.channel_id
            AND s.date = (SELECT MAX(date) FROM channel_stats WHERE channel_id = c.id)
          LEFT JOIN channel_stats s_2d ON c.id = s_2d.channel_id
            AND s_2d.date = DATE_SUB(CURDATE(), INTERVAL 2 DAY)
          LEFT JOIN channel_stats s_7d ON c.id = s_7d.channel_id
            AND s_7d.date = DATE_SUB(CURDATE(), INTERVAL 7 DAY)
        `;

        // Apply filter conditions
        if (filter === 'viral48h') {
            query += ` WHERE s_2d.subscriberCount IS NOT NULL`;
        } else if (filter === 'viral7d') {
            query += ` WHERE s_7d.subscriberCount IS NOT NULL`;
        }

        query += ` ORDER BY s.subscriberCount DESC LIMIT 50`;

        const [rows] = await pool.query(query);

        return NextResponse.json({ channels: rows });
    } catch (error: any) {
        console.error('Database API error:', error.message);
        return NextResponse.json({ error: 'Internal server error' }, { status: 500 });
    }
}
