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

const router = Router();

function clampInt(value: unknown, fallback: number, min: number, max: number) {
  const parsed = Number.parseInt(String(value ?? ''), 10);
  if (!Number.isFinite(parsed)) return fallback;
  return Math.min(max, Math.max(min, parsed));
}

// ═══════════════════════════════════════════════
// CLAW VISIBILITY — X Bucket Agent Data
// Reads from existing sc_twitter_* tables via raw SQL
// ═══════════════════════════════════════════════

// Paginated tweet feed
router.get('/tweets', async (req: Request, res: Response) => {
  try {
    const page = clampInt(req.query.page, 1, 1, 10000);
    const limit = clampInt(req.query.limit, 50, 1, 200);
    const offset = (page - 1) * limit;
    const status = req.query.status as string;
    const contentType = req.query.content_type as string;
    const league = req.query.league as string;

    let where = 'WHERE 1=1';
    const params: any[] = [];
    let paramIdx = 1;

    if (status) { where += ` AND t.status = $${paramIdx++}`; params.push(status); }
    if (contentType) { where += ` AND t.content_type = $${paramIdx++}`; params.push(contentType); }
    if (league) { where += ` AND t.league = $${paramIdx++}`; params.push(league); }

    const countResult = await pool.query(
      `SELECT COUNT(*) as total FROM sc_twitter_tweets t ${where}`,
      params
    );

    const tweets = await pool.query(
      `SELECT t.*,
        (SELECT COUNT(*) FROM sc_twitter_tweets c WHERE c.thread_parent_id = t.id) as thread_count
       FROM sc_twitter_tweets t
       ${where}
       ORDER BY t.created_at DESC
       LIMIT $${paramIdx++} OFFSET $${paramIdx++}`,
      [...params, limit, offset]
    );

    res.json({
      tweets: tweets.rows,
      total: parseInt(countResult.rows[0].total),
      page,
      pages: Math.ceil(parseInt(countResult.rows[0].total) / limit),
    });
  } catch (err: any) {
    res.status(500).json({ error: err.message });
  }
});

// Aggregate tweet stats
router.get('/tweet-stats', async (req: Request, res: Response) => {
  try {
    const days = clampInt(req.query.days, 30, 1, 365);

    const stats = await pool.query(`
      SELECT
        COUNT(*) as total_tweets,
        COUNT(*) FILTER (WHERE status = 'posted') as posted,
        COUNT(*) FILTER (WHERE status = 'pending') as pending,
        COUNT(*) FILTER (WHERE status = 'failed') as failed,
        COUNT(DISTINCT content_type) as content_types_used,
        COUNT(DISTINCT league) FILTER (WHERE league IS NOT NULL) as leagues_covered,
        COALESCE(SUM(impressions), 0) as total_impressions,
        COALESCE(SUM(likes), 0) as total_likes,
        COALESCE(SUM(retweets), 0) as total_retweets,
        COALESCE(SUM(replies), 0) as total_replies,
        COALESCE(AVG(impressions) FILTER (WHERE status = 'posted'), 0) as avg_impressions,
        COALESCE(AVG(likes) FILTER (WHERE status = 'posted'), 0) as avg_likes
      FROM sc_twitter_tweets
      WHERE created_at >= NOW() - ($1::int * INTERVAL '1 day')
    `, [days]);

    const byType = await pool.query(`
      SELECT content_type, COUNT(*) as count,
        COALESCE(SUM(impressions), 0) as impressions,
        COALESCE(SUM(likes), 0) as likes
      FROM sc_twitter_tweets
      WHERE created_at >= NOW() - ($1::int * INTERVAL '1 day')
      GROUP BY content_type
      ORDER BY count DESC
    `, [days]);

    const byDay = await pool.query(`
      SELECT DATE(created_at) as date, COUNT(*) as count,
        COUNT(*) FILTER (WHERE status = 'posted') as posted
      FROM sc_twitter_tweets
      WHERE created_at >= NOW() - ($1::int * INTERVAL '1 day')
      GROUP BY DATE(created_at)
      ORDER BY date DESC
    `, [days]);

    res.json({
      summary: stats.rows[0],
      byType: byType.rows,
      byDay: byDay.rows,
    });
  } catch (err: any) {
    res.status(500).json({ error: err.message });
  }
});

// Engagement actions
router.get('/engagement', async (req: Request, res: Response) => {
  try {
    const page = clampInt(req.query.page, 1, 1, 10000);
    const limit = clampInt(req.query.limit, 50, 1, 200);
    const offset = (page - 1) * limit;

    const countResult = await pool.query('SELECT COUNT(*) as total FROM sc_twitter_engagement');

    const actions = await pool.query(`
      SELECT e.*, t.tweet_text as our_tweet_text, t.content_type as our_content_type
      FROM sc_twitter_engagement e
      LEFT JOIN sc_twitter_tweets t ON e.our_tweet_id = t.id
      ORDER BY e.created_at DESC
      LIMIT $1 OFFSET $2
    `, [limit, offset]);

    const summary = await pool.query(`
      SELECT action_type, COUNT(*) as count,
        COUNT(*) FILTER (WHERE status = 'completed') as completed,
        COUNT(*) FILTER (WHERE status = 'pending') as pending
      FROM sc_twitter_engagement
      GROUP BY action_type
      ORDER BY count DESC
    `);

    res.json({
      actions: actions.rows,
      total: parseInt(countResult.rows[0].total),
      page,
      pages: Math.ceil(parseInt(countResult.rows[0].total) / limit),
      summary: summary.rows,
    });
  } catch (err: any) {
    res.status(500).json({ error: err.message });
  }
});

// Pick consistency view
router.get('/picks', async (req: Request, res: Response) => {
  try {
    const picks = await pool.query(`
      SELECT game_key, pick_direction,
        COUNT(*) as tweet_count,
        ARRAY_AGG(DISTINCT content_type) as content_types,
        MIN(created_at) as first_tweet,
        MAX(created_at) as last_tweet,
        COALESCE(SUM(impressions), 0) as total_impressions,
        COALESCE(SUM(likes), 0) as total_likes
      FROM sc_twitter_tweets
      WHERE game_key IS NOT NULL
      GROUP BY game_key, pick_direction
      ORDER BY MAX(created_at) DESC
      LIMIT 100
    `);

    res.json(picks.rows);
  } catch (err: any) {
    res.status(500).json({ error: err.message });
  }
});

// Worker run history
router.get('/worker-runs', async (req: Request, res: Response) => {
  try {
    const limit = clampInt(req.query.limit, 50, 1, 200);

    const runs = await pool.query(`
      SELECT * FROM sc_twitter_worker_runs
      ORDER BY executed_at DESC
      LIMIT $1
    `, [limit]);

    const summary = await pool.query(`
      SELECT slot_name,
        COUNT(*) as total_runs,
        SUM(tweets_posted) as total_tweets,
        MAX(executed_at) as last_run,
        COUNT(*) FILTER (WHERE status = 'completed') as completed,
        COUNT(*) FILTER (WHERE status != 'completed') as failed
      FROM sc_twitter_worker_runs
      GROUP BY slot_name
      ORDER BY MAX(executed_at) DESC
    `);

    res.json({
      runs: runs.rows,
      summary: summary.rows,
    });
  } catch (err: any) {
    res.status(500).json({ error: err.message });
  }
});

export default router;