import { Router, Request, Response } from 'express';
import pool from '../db';

const router = Router();

/**
 * GET /api/news/headlines?sport=&limit=60&page=1
 *
 * UNION query combining rm_blog_posts + rm_news_links,
 * sorted by is_breaking DESC, is_featured DESC, published_at DESC.
 */
router.get('/headlines', async (req: Request, res: Response) => {
  try {
    const sport = (req.query.sport as string || '').toLowerCase();
    const limit = Math.min(Math.max(parseInt(req.query.limit as string) || 60, 1), 100);
    const page = Math.max(parseInt(req.query.page as string) || 1, 1);

    const sportFilter = sport ? `AND sport = $1` : '';

    // Fetch external news and blog posts separately, then interleave
    const externalQuery = `
      SELECT
        id::text, title, url, source, source_display, sport, description,
        is_featured, is_breaking, trending_score, clicks AS engagement,
        published_at, 'external' AS type, NULL AS slug, image_url,
        custom_headline, custom_summary, engagement_score, celebrity_names,
        source_type, is_curated
      FROM rm_news_links
      WHERE expires_at > NOW() ${sportFilter}
      ORDER BY published_at DESC
      LIMIT ${sport ? '$2' : '$1'}
    `;

    const blogQuery = `
      SELECT
        id::text, title, '/rain-wire/' || sport || '/' || slug AS url,
        'rainmaker' AS source, 'Rainmaker' AS source_display, sport,
        excerpt AS description, FALSE AS is_featured, FALSE AS is_breaking,
        0 AS trending_score, views AS engagement,
        COALESCE(published_at, created_at) AS published_at,
        'blog' AS type, slug, NULL AS image_url, NULL AS custom_headline,
        NULL AS custom_summary, 0 AS engagement_score, NULL AS celebrity_names,
        'blog' AS source_type, FALSE AS is_curated
      FROM rm_blog_posts
      WHERE status = 'published' ${sportFilter}
      ORDER BY COALESCE(published_at, created_at) DESC
      LIMIT ${sport ? '$2' : '$1'}
    `;

    const countQuery = `
      SELECT (
        (SELECT COUNT(*) FROM rm_news_links WHERE expires_at > NOW() ${sportFilter}) +
        (SELECT COUNT(*) FROM rm_blog_posts WHERE status = 'published' ${sportFilter})
      ) AS total
    `;

    // Fetch enough from each source to fill the page after interleaving
    const fetchLimit = limit * page;
    const extParams = sport ? [sport, fetchLimit] : [fetchLimit];
    const blogParams = sport ? [sport, Math.ceil(fetchLimit / 5)] : [Math.ceil(fetchLimit / 5)];

    const [extResult, blogResult, countResult] = await Promise.all([
      pool.query(externalQuery, extParams),
      pool.query(blogQuery, blogParams),
      pool.query(countQuery, sport ? [sport] : []),
    ]);

    // Interleave: insert 1 blog post every 5 external items
    const external = extResult.rows;
    const blogs = blogResult.rows;
    const merged: any[] = [];
    let ei = 0, bi = 0;
    while (ei < external.length || bi < blogs.length) {
      // Add up to 5 external items
      for (let n = 0; n < 5 && ei < external.length; n++, ei++) {
        merged.push(external[ei]);
      }
      // Add 1 blog item
      if (bi < blogs.length) {
        merged.push(blogs[bi++]);
      }
    }

    // Apply pagination to the merged result
    const offset = (page - 1) * limit;
    const headlines = merged.slice(offset, offset + limit);
    const total = parseInt(countResult.rows[0].total);

    res.json({
      headlines,
      pagination: { page, limit, total, pages: Math.ceil(total / limit) },
    });
  } catch (err: any) {
    console.error('News headlines error:', err);
    res.status(500).json({ error: 'Failed to fetch headlines' });
  }
});

/**
 * GET /api/news/trending
 *
 * Top 8 headlines by trending score from last 48h.
 * 6 external + 2 blog posts by views.
 */
router.get('/trending', async (req: Request, res: Response) => {
  try {
    const [externalResult, blogResult] = await Promise.all([
      pool.query(`
        SELECT
          id::text, title, url, source, source_display, sport,
          is_breaking, trending_score, clicks AS engagement,
          published_at, 'external' AS type, NULL AS slug,
          image_url, custom_headline, custom_summary,
          engagement_score, celebrity_names, source_type, is_curated
        FROM rm_news_links
        WHERE published_at > NOW() - INTERVAL '48 hours'
          AND expires_at > NOW()
        ORDER BY (trending_score + COALESCE(engagement_score, 0)) DESC
        LIMIT 6
      `),
      pool.query(`
        SELECT
          id::text, title,
          '/rain-wire/' || sport || '/' || slug AS url,
          'rainmaker' AS source, 'Rainmaker' AS source_display, sport,
          FALSE AS is_breaking, 0 AS trending_score, views AS engagement,
          COALESCE(published_at, created_at) AS published_at, 'blog' AS type, slug
        FROM rm_blog_posts
        WHERE status = 'published'
          AND COALESCE(published_at, created_at) > NOW() - INTERVAL '48 hours'
        ORDER BY views DESC
        LIMIT 2
      `),
    ]);

    const trending = [...externalResult.rows, ...blogResult.rows]
      .sort((a, b) => {
        // Breaking first, then by trending_score/engagement
        if (a.is_breaking && !b.is_breaking) return -1;
        if (!a.is_breaking && b.is_breaking) return 1;
        return (b.trending_score + b.engagement) - (a.trending_score + a.engagement);
      })
      .slice(0, 8);

    res.json(trending);
  } catch (err: any) {
    console.error('News trending error:', err);
    res.status(500).json({ error: 'Failed to fetch trending' });
  }
});

/**
 * POST /api/news/click
 *
 * Fire-and-forget click tracking. Increments clicks on news links or views on blog posts.
 * Body: { id: string, type: 'external' | 'blog' }
 */
router.post('/click', async (req: Request, res: Response) => {
  try {
    const { id, type } = req.body;

    if (!id || !type) {
      return res.status(400).json({ error: 'id and type required' });
    }

    if (type === 'external') {
      pool.query('UPDATE rm_news_links SET clicks = clicks + 1 WHERE id = $1', [id]).catch(() => {});
    } else if (type === 'blog') {
      pool.query('UPDATE rm_blog_posts SET views = views + 1 WHERE id = $1', [id]).catch(() => {});
    }

    res.json({ ok: true });
  } catch (err: any) {
    console.error('News click error:', err);
    res.status(500).json({ error: 'Failed to track click' });
  }
});

/**
 * GET /api/news/sports
 *
 * Returns distinct sports with counts from both tables, for filter pills.
 */
router.get('/sports', async (_req: Request, res: Response) => {
  try {
    const { rows } = await pool.query(`
      SELECT sport, SUM(cnt) AS count FROM (
        SELECT sport, COUNT(*) AS cnt FROM rm_news_links WHERE expires_at > NOW() GROUP BY sport
        UNION ALL
        SELECT sport, COUNT(*) AS cnt FROM rm_blog_posts WHERE status = 'published' GROUP BY sport
      ) combined
      GROUP BY sport
      ORDER BY count DESC
    `);
    res.json(rows);
  } catch (err: any) {
    console.error('News sports error:', err);
    res.status(500).json({ error: 'Failed to fetch sports' });
  }
});

/**
 * GET /api/news/curated?limit=8
 *
 * Only curated items (AI-generated headlines), sorted by engagement_score.
 */
router.get('/curated', async (req: Request, res: Response) => {
  try {
    const limit = Math.min(Math.max(parseInt(req.query.limit as string) || 10, 1), 20);

    // Return top items with REAL external photos (not generated canvas cards).
    // These are actual sports photos from ESPN, CBS, Yahoo, BBC, Sky Sports, etc.
    // Excludes Guardian (hotlink-protected) and generated card paths.
    const { rows } = await pool.query(`
      SELECT
        id::text, title, url, source, source_display, sport, description,
        is_featured, is_breaking, trending_score, clicks AS engagement,
        published_at, 'external' AS type, NULL AS slug,
        image_url, custom_headline, custom_summary,
        engagement_score, celebrity_names, source_type, is_curated
      FROM rm_news_links
      WHERE expires_at > NOW()
        AND image_url LIKE 'http%'
        AND image_url NOT LIKE '%guim.co.uk%'
        AND published_at > NOW() - INTERVAL '48 hours'
      ORDER BY is_curated DESC, (trending_score + COALESCE(engagement_score, 0)) DESC
      LIMIT $1
    `, [limit]);

    res.json(rows);
  } catch (err: any) {
    console.error('News curated error:', err);
    res.status(500).json({ error: 'Failed to fetch curated headlines' });
  }
});

export default router;