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

const router = Router();

// GET /api/traffic/overview?days=30&site=rainmaker
router.get('/overview', async (req: Request, res: Response) => {
  try {
    const days = Math.min(parseInt(req.query.days as string) || 30, 90);
    const site = (req.query.site as string) || null;

    const siteFilter = site ? 'AND site = $2' : '';
    const params: any[] = [days];
    if (site) params.push(site);

    const todaySiteFilter = site ? 'AND site = $1' : '';
    const todayParams: any[] = site ? [site] : [];

    // Totals
    const totalsQ = await pool.query(
      `SELECT
         COUNT(*)::int AS pageviews,
         COUNT(DISTINCT visitor_id)::int AS uniques,
         COUNT(DISTINCT session_id)::int AS sessions
       FROM site_pageviews
       WHERE created_at >= NOW() - ($1 || ' days')::interval ${siteFilter}`,
      params
    );

    const conversionsQ = await pool.query(
      `SELECT COUNT(*)::int AS conversions
       FROM site_conversions
       WHERE created_at >= NOW() - ($1 || ' days')::interval ${siteFilter}`,
      params
    );

    // Today vs yesterday
    const todayQ = await pool.query(
      `SELECT COUNT(*)::int AS pv, COUNT(DISTINCT visitor_id)::int AS uv
       FROM site_pageviews
       WHERE created_at::date = CURRENT_DATE ${todaySiteFilter}`,
      todayParams
    );
    const yesterdayQ = await pool.query(
      `SELECT COUNT(*)::int AS pv, COUNT(DISTINCT visitor_id)::int AS uv
       FROM site_pageviews
       WHERE created_at::date = CURRENT_DATE - 1 ${todaySiteFilter}`,
      todayParams
    );

    // Per-site totals (always return both sites for the breakdown)
    const perSiteQ = await pool.query(
      `SELECT site,
         COUNT(*)::int AS pageviews,
         COUNT(DISTINCT visitor_id)::int AS uniques,
         COUNT(DISTINCT session_id)::int AS sessions
       FROM site_pageviews
       WHERE created_at >= NOW() - ($1 || ' days')::interval ${siteFilter}
       GROUP BY site
       ORDER BY site`,
      params
    );

    const perSiteConvQ = await pool.query(
      `SELECT site, COUNT(*)::int AS conversions
       FROM site_conversions
       WHERE created_at >= NOW() - ($1 || ' days')::interval ${siteFilter}
       GROUP BY site
       ORDER BY site`,
      params
    );

    // Daily pageviews by site
    const dailyPvQ = await pool.query(
      `SELECT created_at::date AS day, site, COUNT(*)::int AS count
       FROM site_pageviews
       WHERE created_at >= NOW() - ($1 || ' days')::interval ${siteFilter}
       GROUP BY 1, 2
       ORDER BY 1`,
      params
    );

    // Daily uniques by site
    const dailyUvQ = await pool.query(
      `SELECT created_at::date AS day, site, COUNT(DISTINCT visitor_id)::int AS count
       FROM site_pageviews
       WHERE created_at >= NOW() - ($1 || ' days')::interval ${siteFilter}
       GROUP BY 1, 2
       ORDER BY 1`,
      params
    );

    // Top pages
    const topPagesQ = await pool.query(
      `SELECT site, path, COUNT(*)::int AS views, COUNT(DISTINCT visitor_id)::int AS uniques
       FROM site_pageviews
       WHERE created_at >= NOW() - ($1 || ' days')::interval ${siteFilter}
       GROUP BY 1, 2
       ORDER BY views DESC
       LIMIT 20`,
      params
    );

    // Top referrers
    const topReferrersQ = await pool.query(
      `SELECT site, referrer, COUNT(*)::int AS hits, COUNT(DISTINCT visitor_id)::int AS uniques
       FROM site_pageviews
       WHERE created_at >= NOW() - ($1 || ' days')::interval
         AND referrer IS NOT NULL AND referrer != ''
         ${siteFilter}
       GROUP BY 1, 2
       ORDER BY hits DESC
       LIMIT 15`,
      params
    );

    // Device breakdown (from screen_w)
    const devicesQ = await pool.query(
      `SELECT
         CASE WHEN screen_w < 768 THEN 'mobile' WHEN screen_w < 1024 THEN 'tablet' ELSE 'desktop' END AS device,
         COUNT(*)::int AS count,
         COUNT(DISTINCT visitor_id)::int AS uniques
       FROM site_pageviews
       WHERE created_at >= NOW() - ($1 || ' days')::interval
         AND screen_w IS NOT NULL
         ${siteFilter}
       GROUP BY 1
       ORDER BY count DESC`,
      params
    );

    // Hourly distribution (ET timezone)
    const hourlyQ = await pool.query(
      `SELECT
         EXTRACT(hour FROM created_at AT TIME ZONE 'America/New_York')::int AS hour,
         COUNT(*)::int AS count
       FROM site_pageviews
       WHERE created_at >= NOW() - ($1 || ' days')::interval ${siteFilter}
       GROUP BY 1
       ORDER BY 1`,
      params
    );

    // Bounce rate: visitors with only 1 pageview / total visitors
    const bounceQ = await pool.query(
      `SELECT
         COUNT(*)::int AS total_visitors,
         COUNT(*) FILTER (WHERE pv_count = 1)::int AS single_page_visitors
       FROM (
         SELECT visitor_id, COUNT(*) AS pv_count
         FROM site_pageviews
         WHERE created_at >= NOW() - ($1 || ' days')::interval ${siteFilter}
           AND visitor_id IS NOT NULL
         GROUP BY visitor_id
       ) sub`,
      params
    );

    // Avg pages per session
    const ppsQ = await pool.query(
      `SELECT
         CASE WHEN COUNT(DISTINCT session_id) = 0 THEN 0
              ELSE ROUND(COUNT(*)::numeric / COUNT(DISTINCT session_id), 1)
         END AS pages_per_session
       FROM site_pageviews
       WHERE created_at >= NOW() - ($1 || ' days')::interval ${siteFilter}
         AND session_id IS NOT NULL`,
      params
    );

    // Conversions by event
    const convByEventQ = await pool.query(
      `SELECT site, event, COUNT(*)::int AS count
       FROM site_conversions
       WHERE created_at >= NOW() - ($1 || ' days')::interval ${siteFilter}
       GROUP BY 1, 2
       ORDER BY count DESC`,
      params
    );

    // Recent pageviews (last 25)
    const recentQ = await pool.query(
      `SELECT site, path, referrer, visitor_id, ip_address, user_agent, screen_w, created_at
       FROM site_pageviews
       WHERE 1=1 ${siteFilter.replace('$2', site ? `$${site ? 1 : 0}` : '')}
       ORDER BY created_at DESC
       LIMIT 25`,
      site ? [site] : []
    );

    const totals = totalsQ.rows[0];
    const today = todayQ.rows[0];
    const yesterday = yesterdayQ.rows[0];
    const bounce = bounceQ.rows[0];

    function pctChange(curr: number, prev: number): number {
      if (prev === 0) return curr > 0 ? 100 : 0;
      return Math.round(((curr - prev) / prev) * 100);
    }

    // Build per-site map
    const perSiteMap: Record<string, any> = {};
    for (const row of perSiteQ.rows) {
      perSiteMap[row.site] = { ...row, conversions: 0 };
    }
    for (const row of perSiteConvQ.rows) {
      if (perSiteMap[row.site]) perSiteMap[row.site].conversions = row.conversions;
    }

    const bounceRate = bounce.total_visitors > 0
      ? Math.round((bounce.single_page_visitors / bounce.total_visitors) * 100)
      : 0;

    const conversionRate = totals.uniques > 0
      ? parseFloat(((conversionsQ.rows[0].conversions / totals.uniques) * 100).toFixed(1))
      : 0;

    res.json({
      totals: {
        pageviews: totals.pageviews,
        uniques: totals.uniques,
        sessions: totals.sessions,
        conversions: conversionsQ.rows[0].conversions,
        bounceRate,
        pagesPerSession: parseFloat(ppsQ.rows[0].pages_per_session),
        conversionRate,
      },
      trends: {
        pv_today: today.pv,
        pv_yesterday: yesterday.pv,
        pv_change: pctChange(today.pv, yesterday.pv),
        uv_today: today.uv,
        uv_yesterday: yesterday.uv,
        uv_change: pctChange(today.uv, yesterday.uv),
      },
      perSite: perSiteMap,
      dailyPageviews: dailyPvQ.rows,
      dailyUniques: dailyUvQ.rows,
      topPages: topPagesQ.rows,
      topReferrers: topReferrersQ.rows,
      devices: devicesQ.rows,
      hourly: hourlyQ.rows,
      conversionsByEvent: convByEventQ.rows,
      recentPageviews: recentQ.rows,
    });
  } catch (err) {
    console.error('Traffic overview error:', err);
    res.status(500).json({ error: 'Failed to load traffic data' });
  }
});

export default router;