/**
 * RainWire Unified Feed — merges news + social into a single endpoint.
 */
import { Router, Request, Response } from 'express';
import pool from '../db';
import {
  getPostedContent,
  getPostedContentCount,
} from '../workers/social-engine/data-queries';

const router = Router();

/**
 * GET /api/rainwire/feed?filter=all|news|social&sport=&persona=&page=1&limit=40
 *
 * Unified feed combining news headlines and social content.
 */
router.get('/feed', async (req: Request, res: Response) => {
  try {
    const filter = (req.query.filter as string) || 'all';
    const sport = (req.query.sport as string || '').toLowerCase() || undefined;
    const persona = (req.query.persona as string) || undefined;
    const limit = Math.min(Math.max(parseInt(req.query.limit as string) || 40, 1), 100);
    const page = Math.max(parseInt(req.query.page as string) || 1, 1);
    const offset = (page - 1) * limit;

    let newsItems: any[] = [];
    let socialItems: any[] = [];
    let newsTotal = 0;
    let socialTotal = 0;

    // Fetch news if needed
    if (filter === 'all' || filter === 'news') {
      const sportFilter = sport ? `AND sport = $1` : '';
      const newsParams: any[] = sport ? [sport, limit, offset] : [limit, offset];
      const limitIdx = sport ? '$2' : '$1';
      const offsetIdx = sport ? '$3' : '$2';

      const newsQuery = `
        SELECT * FROM (
          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}
          UNION ALL
          SELECT
            id::text, title,
            '/rain-wire/' || sport || '/' || slug AS url,
            'rainmaker' AS source, 'Rainmaker' AS source_display, sport,
            excerpt AS description,
            COALESCE(is_featured, FALSE) AS is_featured, FALSE AS is_breaking,
            CASE WHEN COALESCE(published_at, created_at) > NOW() - INTERVAL '24 hours' THEN 1500 ELSE 500 END 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}
        ) AS combined
        ORDER BY is_breaking DESC,
          CASE WHEN source = 'rainmaker' THEN published_at + INTERVAL '8 hours' ELSE published_at END DESC
        LIMIT ${limitIdx} OFFSET ${offsetIdx}
      `;

      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
      `;

      const [headlinesResult, countResult] = await Promise.all([
        pool.query(newsQuery, newsParams),
        pool.query(countQuery, sport ? [sport] : []),
      ]);

      newsItems = headlinesResult.rows.map((r: any) => ({ ...r, feed_source: 'rainwire' }));
      newsTotal = parseInt(countResult.rows[0].total);
    }

    // Fetch social if needed
    if (filter === 'all' || filter === 'social') {
      const socialLimit = filter === 'all' ? 6 : limit;
      const socialOffset = filter === 'all' ? 0 : offset;
      let includePreview = false;

      if (socialOffset === 0) {
        const params: any[] = [];
        let idx = 1;
        let recentWhere = `status = 'posted' AND posted_at > NOW() - INTERVAL '24 hours'`;

        if (persona) {
          recentWhere += ` AND persona_id IN (SELECT id FROM rm_social_personas WHERE slug = $${idx})`;
          params.push(persona);
          idx++;
        }
        if (sport) {
          recentWhere += ` AND sport = $${idx}`;
          params.push(sport);
          idx++;
        }

        const { rows } = await pool.query(
          `SELECT COUNT(*)::int AS cnt FROM rm_social_content WHERE ${recentWhere}`,
          params
        );

        if ((rows[0]?.cnt || 0) === 0) {
          includePreview = true;
        }
      }

      const [rawSocial, rawSocialCount] = await Promise.all([
        getPostedContent(socialLimit, socialOffset, persona, sport, { includePreview }),
        getPostedContentCount(persona, sport, { includePreview }),
      ]);

      socialItems = rawSocial.map((r: any) => ({ ...r, feed_source: 'social' }));
      socialTotal = rawSocialCount;
    }

    // Merge results
    let items: any[];
    let total: number;

    if (filter === 'all') {
      // News items are the primary feed; social items attached separately
      items = newsItems;
      total = newsTotal;
    } else if (filter === 'news') {
      items = newsItems;
      total = newsTotal;
    } else {
      items = socialItems;
      total = socialTotal;
    }

    // Fetch sport counts (combined from both sources)
    const { rows: sportRows } = await pool.query(`
      SELECT sport, SUM(cnt)::int 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
        UNION ALL
        SELECT sport, COUNT(*) AS cnt
        FROM rm_social_content
        WHERE status = 'posted'
          AND sport IS NOT NULL
          AND format != 'thread_reply'
          AND tweet_id IS NOT NULL
          AND tweet_id NOT LIKE 'dry_%'
        GROUP BY sport
      ) combined
      GROUP BY sport
      ORDER BY count DESC
    `);

    res.json({
      items,
      social_items: filter === 'all' ? socialItems : undefined,
      pagination: {
        page,
        limit,
        total,
        pages: Math.ceil(total / limit),
      },
      sports: sportRows,
      filter,
    });
  } catch (err: any) {
    console.error('[rainwire] Feed error:', err.message);
    res.status(500).json({ error: 'Failed to fetch unified feed' });
  }
});

export default router;