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

const router = Router();

// GET /api/blog/posts?sport=&page=
router.get('/posts', async (req: Request, res: Response) => {
  try {
    const sport = (req.query.sport as string || '').toLowerCase();
    const page = Math.max(1, parseInt(req.query.page as string) || 1);
    const limit = Math.min(50, Math.max(1, parseInt(req.query.limit as string) || 20));
    const offset = (page - 1) * limit;

    const conditions: string[] = ["status = 'published'"];
    const params: any[] = [];
    let paramIdx = 1;

    if (sport) {
      conditions.push(`sport = $${paramIdx++}`);
      params.push(sport);
    }

    const where = conditions.join(' AND ');

    const [postsResult, countResult] = await Promise.all([
      pool.query(
        `SELECT id, slug, sport, title, excerpt, tags, home_team, away_team, game_date, views, published_at
         FROM rm_blog_posts WHERE ${where}
         ORDER BY published_at DESC NULLS LAST
         LIMIT $${paramIdx++} OFFSET $${paramIdx++}`,
        [...params, limit, offset]
      ),
      pool.query(
        `SELECT COUNT(*) FROM rm_blog_posts WHERE ${where}`,
        params
      ),
    ]);

    const total = parseInt(countResult.rows[0].count);

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

// GET /api/blog/posts/:slug
router.get('/posts/:slug', async (req: Request, res: Response) => {
  try {
    const { slug } = req.params;

    const { rows } = await pool.query(
      `SELECT *
       FROM rm_blog_posts
       WHERE slug = $1 AND status = 'published'`,
      [slug]
    );

    if (rows.length === 0) {
      return res.status(404).json({ error: 'Post not found' });
    }

    res.json(rows[0]);
  } catch (err: any) {
    console.error('Blog post detail error:', err);
    res.status(500).json({ error: 'Failed to fetch post' });
  }
});

// GET /api/blog/sports
router.get('/sports', async (_req: Request, res: Response) => {
  try {
    const { rows } = await pool.query(
      `SELECT sport, COUNT(*) AS count
       FROM rm_blog_posts WHERE status = 'published'
       GROUP BY sport ORDER BY count DESC`
    );
    res.json(rows);
  } catch (err: any) {
    console.error('Blog sports error:', err);
    res.status(500).json({ error: 'Failed to fetch sports' });
  }
});

// GET /api/blog/sitemap
router.get('/sitemap', async (_req: Request, res: Response) => {
  try {
    const { rows } = await pool.query(
      `SELECT slug, sport, published_at, updated_at
       FROM rm_blog_posts WHERE status = 'published'
       ORDER BY published_at DESC`
    );
    res.json(rows);
  } catch (err: any) {
    console.error('Blog sitemap error:', err);
    res.status(500).json({ error: 'Failed to fetch sitemap data' });
  }
});

// GET /api/blog/feed.xml
router.get('/feed.xml', async (_req: Request, res: Response) => {
  try {
    const { rows } = await pool.query(
      `SELECT slug, sport, title, excerpt, home_team, away_team, published_at, updated_at
       FROM rm_blog_posts
       WHERE status = 'published'
       ORDER BY COALESCE(updated_at, published_at) DESC
       LIMIT 50`
    );

    const escapeXml = (value: string) => value
      .replace(/&/g, '&')
      .replace(/</g, '&lt;')
      .replace(/>/g, '&gt;')
      .replace(/"/g, '&quot;')
      .replace(/'/g, '&apos;');

    const items = rows.map((post: any) => {
      const url = `https://rainmakersports.app/rain-wire/${post.sport}/${post.slug}`;
      const description = post.excerpt || `${post.away_team || 'Away'} at ${post.home_team || 'Home'} preview from Rainmaker Sports.`;
      const pubDate = post.updated_at || post.published_at || new Date().toISOString();
      return `
      <item>
        <title>${escapeXml(post.title)}</title>
        <link>${url}</link>
        <guid>${url}</guid>
        <pubDate>${new Date(pubDate).toUTCString()}</pubDate>
        <description>${escapeXml(description)}</description>
        <category>${escapeXml(post.sport)}</category>
      </item>`;
    }).join('');

    const xml = `<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0">
  <channel>
    <title>Rainmaker Sports - Rain Wire</title>
    <link>https://rainmakersports.app/rain-wire</link>
    <description>AI-assisted sports previews, matchup analysis, and market context from Rainmaker Sports.</description>
    <language>en-us</language>
    <lastBuildDate>${new Date().toUTCString()}</lastBuildDate>
    ${items}
  </channel>
</rss>`;

    res.setHeader('Content-Type', 'application/rss+xml; charset=utf-8');
    res.setHeader('Cache-Control', 'public, max-age=3600');
    res.send(xml);
  } catch (err: any) {
    console.error('Blog feed error:', err);
    res.status(500).json({ error: 'Failed to fetch feed data' });
  }
});

// GET /api/blog/bucket-stats
router.get('/bucket-stats', async (_req: Request, res: Response) => {
  try {
    const { rows } = await pool.query(
      `SELECT bucket, total_forecasts, wins, losses, pushes, pending, win_rate
       FROM rm_archive_bucket_stats ORDER BY bucket`
    );
    res.json(rows);
  } catch (err: any) {
    console.error('Bucket stats error:', err);
    res.status(500).json({ error: 'Failed to fetch bucket stats' });
  }
});

export default router;