// /api/watchlist/insights — Feature 10: Weekly channel growth insights
// Returns watchlist channels ranked by view growth (7d) and subscriber growth (7d)
import { NextResponse } from 'next/server';
import pool from '../../../../lib/db';

export async function GET() {
    try {
        // Join watchlist → channels → channel_stats for growth calculation
        const [rows] = await pool.execute<any[]>(`
            SELECT
                c.id,
                c.title,
                c.thumbnailUrl,
                s_now.subscriberCount   AS subs_now,
                s_7d.subscriberCount    AS subs_7d,
                s_now.viewCount         AS views_now,
                s_7d.viewCount          AS views_7d,
                ROUND((s_now.viewCount - s_7d.viewCount) / NULLIF(s_7d.viewCount, 0) * 100, 1) AS view_growth_pct,
                ROUND((s_now.subscriberCount - s_7d.subscriberCount) / NULLIF(s_7d.subscriberCount, 0) * 100, 1) AS sub_growth_pct,
                (s_now.viewCount - s_7d.viewCount) AS view_gain,
                (s_now.subscriberCount - s_7d.subscriberCount) AS sub_gain
            FROM watchlist w
            JOIN channels c ON w.channel_id = c.id
            JOIN channel_stats s_now ON c.id = s_now.channel_id
                AND s_now.date = (SELECT MAX(date) FROM channel_stats WHERE channel_id = c.id)
            LEFT JOIN channel_stats s_7d ON c.id = s_7d.channel_id
                AND s_7d.date = DATE_SUB(CURDATE(), INTERVAL 7 DAY)
            WHERE s_7d.viewCount IS NOT NULL
            HAVING view_gain > 0
            ORDER BY view_growth_pct DESC
            LIMIT 10
        `);

        // Top sub gainers
        const [subRows] = await pool.execute<any[]>(`
            SELECT
                c.id,
                c.title,
                c.thumbnailUrl,
                s_now.subscriberCount   AS subs_now,
                (s_now.subscriberCount - s_7d.subscriberCount) AS sub_gain,
                ROUND((s_now.subscriberCount - s_7d.subscriberCount) / NULLIF(s_7d.subscriberCount, 0) * 100, 1) AS sub_growth_pct
            FROM watchlist w
            JOIN channels c ON w.channel_id = c.id
            JOIN channel_stats s_now ON c.id = s_now.channel_id
                AND s_now.date = (SELECT MAX(date) FROM channel_stats WHERE channel_id = c.id)
            LEFT JOIN channel_stats s_7d ON c.id = s_7d.channel_id
                AND s_7d.date = DATE_SUB(CURDATE(), INTERVAL 7 DAY)
            WHERE s_7d.subscriberCount IS NOT NULL
            HAVING sub_gain > 0
            ORDER BY sub_gain DESC
            LIMIT 10
        `);

        return NextResponse.json({
            topViewGrowth: rows,
            topSubGrowth: subRows,
            asOf: new Date().toISOString(),
        });
    } catch (err: any) {
        return NextResponse.json({ error: err.message }, { status: 500 });
    }
}
