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

const router = Router();

// GET /api/archives/dashboard
router.get('/dashboard', async (_req: Request, res: Response) => {
  try {
    const [bucketStats, summary, leagueBreakdown] = await Promise.all([
      pool.query(`SELECT * FROM rm_archive_bucket_stats ORDER BY bucket`),
      pool.query(`
        SELECT
          COUNT(*) AS total,
          COUNT(*) FILTER (WHERE outcome = 'win') AS wins,
          COUNT(*) FILTER (WHERE outcome = 'loss') AS losses,
          COUNT(*) FILTER (WHERE outcome = 'push') AS pushes,
          COUNT(*) FILTER (WHERE outcome = 'pending') AS pending,
          CASE WHEN COUNT(*) FILTER (WHERE outcome IN ('win','loss')) > 0
            THEN ROUND(COUNT(*) FILTER (WHERE outcome = 'win')::numeric /
              COUNT(*) FILTER (WHERE outcome IN ('win','loss')) * 100, 1)
            ELSE NULL END AS win_rate
        FROM rm_archived_forecasts
      `),
      pool.query(`
        SELECT league, COUNT(*) AS total,
          COUNT(*) FILTER (WHERE outcome = 'win') AS wins,
          COUNT(*) FILTER (WHERE outcome = 'loss') AS losses,
          COUNT(*) FILTER (WHERE outcome = 'pending') AS pending
        FROM rm_archived_forecasts
        GROUP BY league ORDER BY total DESC
      `),
    ]);

    res.json({
      buckets: bucketStats.rows,
      summary: summary.rows[0],
      byLeague: leagueBreakdown.rows,
    });
  } catch (err: any) {
    console.error('Archives dashboard error:', err);
    res.status(500).json({ error: 'Failed to load dashboard' });
  }
});

// GET /api/archives/list?league=&bucket=&outcome=&page=
router.get('/list', async (req: Request, res: Response) => {
  try {
    const league = req.query.league as string;
    const bucket = req.query.bucket as string;
    const outcome = req.query.outcome as string;
    const page = Math.max(1, parseInt(req.query.page as string) || 1);
    const limit = 25;
    const offset = (page - 1) * limit;

    const conditions: string[] = [];
    const params: any[] = [];
    let idx = 1;

    if (league) { conditions.push(`league = $${idx++}`); params.push(league); }
    if (bucket) { conditions.push(`probability_bucket = $${idx++}`); params.push(parseInt(bucket)); }
    if (outcome) { conditions.push(`outcome = $${idx++}`); params.push(outcome); }

    const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

    const [rows, count] = await Promise.all([
      pool.query(
        `SELECT id, league, home_team, away_team, starts_at, winner_pick,
                probability_raw, probability_bucket, outcome, actual_winner,
                actual_score, settled_at, blog_post_id, created_at
         FROM rm_archived_forecasts ${where}
         ORDER BY starts_at DESC
         LIMIT $${idx++} OFFSET $${idx++}`,
        [...params, limit, offset]
      ),
      pool.query(`SELECT COUNT(*) FROM rm_archived_forecasts ${where}`, params),
    ]);

    res.json({
      archives: rows.rows,
      pagination: { page, limit, total: parseInt(count.rows[0].count), pages: Math.ceil(parseInt(count.rows[0].count) / limit) },
    });
  } catch (err: any) {
    console.error('Archives list error:', err);
    res.status(500).json({ error: 'Failed to load archives' });
  }
});

// GET /api/archives/:id
router.get('/:id', async (req: Request, res: Response) => {
  try {
    const { rows } = await pool.query(
      `SELECT * FROM rm_archived_forecasts WHERE id = $1`,
      [req.params.id]
    );
    if (rows.length === 0) return res.status(404).json({ error: 'Not found' });
    res.json(rows[0]);
  } catch (err: any) {
    console.error('Archive detail error:', err);
    res.status(500).json({ error: 'Failed to load archive' });
  }
});

// GET /api/archives/blogs?sport=&status=&page=
router.get('/blogs/list', async (req: Request, res: Response) => {
  try {
    const sport = req.query.sport as string;
    const status = req.query.status as string;
    const page = Math.max(1, parseInt(req.query.page as string) || 1);
    const limit = 25;
    const offset = (page - 1) * limit;

    const conditions: string[] = [];
    const params: any[] = [];
    let idx = 1;

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

    const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

    const [rows, count] = await Promise.all([
      pool.query(
        `SELECT id, slug, sport, title, status, views, game_date, published_at, created_at
         FROM rm_blog_posts ${where}
         ORDER BY created_at DESC
         LIMIT $${idx++} OFFSET $${idx++}`,
        [...params, limit, offset]
      ),
      pool.query(`SELECT COUNT(*) FROM rm_blog_posts ${where}`, params),
    ]);

    res.json({
      blogs: rows.rows,
      pagination: { page, limit, total: parseInt(count.rows[0].count), pages: Math.ceil(parseInt(count.rows[0].count) / limit) },
    });
  } catch (err: any) {
    console.error('Blog list error:', err);
    res.status(500).json({ error: 'Failed to load blogs' });
  }
});

// PATCH /api/archives/blogs/:id
router.patch('/blogs/:id', async (req: Request, res: Response) => {
  try {
    const { id } = req.params;
    const { status, title, content, meta_description } = req.body;

    const updates: string[] = [];
    const params: any[] = [];
    let idx = 1;

    if (status !== undefined) {
      updates.push(`status = $${idx++}`); params.push(status);
      if (status === 'published') {
        updates.push(`published_at = COALESCE(published_at, NOW())`);
      }
    }
    if (title !== undefined) { updates.push(`title = $${idx++}`); params.push(title); }
    if (content !== undefined) { updates.push(`content = $${idx++}`); params.push(content); }
    if (meta_description !== undefined) { updates.push(`meta_description = $${idx++}`); params.push(meta_description); }

    updates.push('updated_at = NOW()');

    if (updates.length <= 1) return res.status(400).json({ error: 'No fields to update' });

    params.push(id);
    const { rows } = await pool.query(
      `UPDATE rm_blog_posts SET ${updates.join(', ')} WHERE id = $${idx} RETURNING id, status, title`,
      params
    );

    if (rows.length === 0) return res.status(404).json({ error: 'Not found' });
    res.json(rows[0]);
  } catch (err: any) {
    console.error('Blog update error:', err);
    res.status(500).json({ error: 'Failed to update blog' });
  }
});

export default router;