import { Router, Request, Response, NextFunction } from 'express';
import { query, queryOne } from '../db';

const router = Router();

// GET /api/analytics/overview — dashboard stats
router.get('/overview', async (_req: Request, res: Response, next: NextFunction) => {
  try {
    const [totalContacts, totalCampaigns, avgStats, recentCampaigns, dailySends] = await Promise.all([
      queryOne<{ count: string }>('SELECT COUNT(*) as count FROM contacts WHERE subscribed=true'),
      queryOne<{ count: string }>('SELECT COUNT(*) as count FROM campaigns'),
      queryOne<{ avg_open_rate: string; avg_click_rate: string }>(`
        SELECT ROUND(AVG(open_rate)::numeric, 2) as avg_open_rate,
               ROUND(AVG(click_rate)::numeric, 2) as avg_click_rate
        FROM campaign_stats WHERE status='sent'
      `),
      query(`
        SELECT campaign_id AS id, name, status, sent_at, open_rate, click_rate, sent_count
        FROM campaign_stats
        ORDER BY sent_at DESC NULLS LAST
        LIMIT 10
      `),
      query(`
        SELECT DATE(created_at) as date, COUNT(*) as sends
        FROM email_events WHERE type='sent'
          AND created_at >= now() - interval '7 days'
        GROUP BY DATE(created_at)
        ORDER BY date ASC
      `),
    ]);

    res.json({
      total_contacts: parseInt(totalContacts?.count || '0'),
      total_campaigns: parseInt(totalCampaigns?.count || '0'),
      avg_open_rate: parseFloat(avgStats?.avg_open_rate || '0'),
      avg_click_rate: parseFloat(avgStats?.avg_click_rate || '0'),
      recent_campaigns: recentCampaigns,
      daily_sends: dailySends,
    });
  } catch (err) { next(err); }
});

// GET /api/analytics/campaigns/:id — campaign event timeline
router.get('/campaigns/:id', async (req: Request, res: Response, next: NextFunction) => {
  try {
    const [stats, events, topLinks] = await Promise.all([
      queryOne(
        'SELECT * FROM campaign_stats WHERE campaign_id=$1', [req.params.id]
      ),
      query(`
        SELECT type, DATE_TRUNC('hour', created_at) as hour, COUNT(*) as count
        FROM email_events WHERE campaign_id=$1
        GROUP BY type, hour ORDER BY hour ASC
      `, [req.params.id]),
      query(`
        SELECT tl.original_url, tl.click_count
        FROM tracked_links tl
        WHERE tl.campaign_id=$1
        ORDER BY tl.click_count DESC
        LIMIT 20
      `, [req.params.id]),
    ]);

    res.json({ stats, events, top_links: topLinks });
  } catch (err) { next(err); }
});

export default router;