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

const KEY = process.env.YOUTUBE_API_KEY;
const BASE = 'https://www.googleapis.com/youtube/v3';

// GET /api/watchlist — fetch all watchlisted channels
export async function GET() {
    try {
        const [rows] = await pool.query(
            `SELECT
        w.channel_id AS id,
        w.channel_title AS title,
        w.channel_thumbnail AS thumbnail,
        w.channel_description AS description,
        w.publishedAt,
        w.added_at AS addedAt,
        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 watchlist w
      LEFT JOIN channel_stats s ON w.channel_id = s.channel_id
        AND s.date = (SELECT MAX(date) FROM channel_stats WHERE channel_id = w.channel_id)
      LEFT JOIN channel_stats s_2d ON w.channel_id = s_2d.channel_id
        AND s_2d.date = DATE_SUB(CURDATE(), INTERVAL 2 DAY)
      LEFT JOIN channel_stats s_7d ON w.channel_id = s_7d.channel_id
        AND s_7d.date = DATE_SUB(CURDATE(), INTERVAL 7 DAY)
      ORDER BY w.added_at DESC`
        ) as any[][];

        return NextResponse.json({ watchlist: rows });
    } catch (err: any) {
        console.error('Watchlist GET error:', err.message);
        return NextResponse.json({ error: err.message }, { status: 500 });
    }
}

// POST /api/watchlist — add a channel to watchlist
// Body: { id, title, thumbnail, description, publishedAt, viewCount, subscriberCount, videoCount }
export async function POST(req: Request) {
    try {
        const body = await req.json();
        const { id, title, thumbnail, description, publishedAt, viewCount, subscriberCount, videoCount } = body;

        if (!id || !title) {
            return NextResponse.json({ error: 'Missing channel id or title' }, { status: 400 });
        }

        // Upsert into channels table so FK constraint is satisfied
        await pool.query(
            `INSERT INTO channels (id, title, description, customUrl, publishedAt, thumbnailUrl, country)
       VALUES (?, ?, ?, '', ?, ?, '')
       ON DUPLICATE KEY UPDATE title=VALUES(title), description=VALUES(description), thumbnailUrl=VALUES(thumbnailUrl)`,
            [id, title, description || '', publishedAt ? new Date(publishedAt) : new Date(), thumbnail || '']
        );

        // Save current snapshot to channel_stats so cron can track growth from today
        const today = new Date().toISOString().split('T')[0];
        await pool.query(
            `INSERT INTO channel_stats (channel_id, date, viewCount, subscriberCount, videoCount)
       VALUES (?, ?, ?, ?, ?)
       ON DUPLICATE KEY UPDATE viewCount=VALUES(viewCount), subscriberCount=VALUES(subscriberCount), videoCount=VALUES(videoCount)`,
            [id, today, parseInt(viewCount || '0', 10), parseInt(subscriberCount || '0', 10), parseInt(videoCount || '0', 10)]
        );

        // Add to watchlist
        await pool.query(
            `INSERT INTO watchlist (channel_id, channel_title, channel_thumbnail, channel_description, publishedAt)
       VALUES (?, ?, ?, ?, ?)
       ON DUPLICATE KEY UPDATE channel_title=VALUES(channel_title), channel_thumbnail=VALUES(channel_thumbnail)`,
            [id, title, thumbnail || '', description || '', publishedAt ? new Date(publishedAt) : new Date()]
        );

        // Optionally refresh stats from YouTube API for accuracy
        try {
            const chRes = await axios.get(`${BASE}/channels`, {
                params: { part: 'statistics', id, key: KEY },
            });
            const stats = chRes.data.items?.[0]?.statistics;
            if (stats) {
                await pool.query(
                    `INSERT INTO channel_stats (channel_id, date, viewCount, subscriberCount, videoCount)
           VALUES (?, ?, ?, ?, ?)
           ON DUPLICATE KEY UPDATE viewCount=VALUES(viewCount), subscriberCount=VALUES(subscriberCount), videoCount=VALUES(videoCount)`,
                    [id, today, parseInt(stats.viewCount || '0', 10), parseInt(stats.subscriberCount || '0', 10), parseInt(stats.videoCount || '0', 10)]
                );
            }
        } catch { /* best-effort stats refresh */ }

        return NextResponse.json({ success: true });
    } catch (err: any) {
        console.error('Watchlist POST error:', err.message);
        return NextResponse.json({ error: err.message }, { status: 500 });
    }
}

// DELETE /api/watchlist?id=CHANNEL_ID — remove from watchlist
export async function DELETE(req: Request) {
    try {
        const { searchParams } = new URL(req.url);
        const id = searchParams.get('id');
        if (!id) return NextResponse.json({ error: 'Missing id' }, { status: 400 });

        await pool.query(`DELETE FROM watchlist WHERE channel_id = ?`, [id]);
        return NextResponse.json({ success: true });
    } catch (err: any) {
        console.error('Watchlist DELETE error:', err.message);
        return NextResponse.json({ error: err.message }, { status: 500 });
    }
}
