import pool from '../config/db.js';

// Get Overview Screen Top 8 KPI Metric Cards
export async function getOverviewStats(req, res) {
  try {
    const userRole = req.user.role;
    if (userRole !== 'super_admin' && userRole !== 'moderator') {
      return res.status(403).json({ error: "Access denied. Insufficient privileges to view overview metrics." });
    }

    // 1. Total Businesses (Approved listings) & Month-over-Month Growth
    const [bizRow] = await pool.query(`
      SELECT 
        COUNT(*) as total_approved,
        SUM(CASE WHEN created_at >= NOW() - INTERVAL 30 DAY THEN 1 ELSE 0 END) as approved_this_month,
        SUM(CASE WHEN created_at >= NOW() - INTERVAL 60 DAY AND created_at < NOW() - INTERVAL 30 DAY THEN 1 ELSE 0 END) as approved_prior_month
      FROM listings 
      WHERE status = 'approved'
    `);
    const totalApproved = bizRow[0]?.total_approved || 0;
    const approvedThisMonth = bizRow[0]?.approved_this_month || 0;
    const approvedPriorMonth = bizRow[0]?.approved_prior_month || 0;
    let bizGrowth = "+0.0%";
    if (approvedPriorMonth > 0) {
      const delta = ((approvedThisMonth - approvedPriorMonth) / approvedPriorMonth) * 100;
      bizGrowth = `${delta >= 0 ? '+' : ''}${delta.toFixed(1)}%`;
    } else if (approvedThisMonth > 0) {
      bizGrowth = `+100%`;
    }

    // 2. Pending Approvals (status = pending OR status = reviewing)
    const [pendingRow] = await pool.query(`
      SELECT 
        COUNT(*) as pending_total,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count,
        SUM(CASE WHEN status = 'reviewing' THEN 1 ELSE 0 END) as reviewing_count
      FROM listings 
      WHERE status = 'pending' OR status = 'reviewing'
    `);
    const pendingTotal = pendingRow[0]?.pending_total || 0;

    // 3. Active Users & Monthly Growth Delta
    const [usersRow] = await pool.query(`
      SELECT 
        COUNT(*) as total_users,
        SUM(CASE WHEN status = 'Active' OR status IS NULL THEN 1 ELSE 0 END) as active_users,
        SUM(CASE WHEN created_at >= NOW() - INTERVAL 30 DAY THEN 1 ELSE 0 END) as new_users_this_month
      FROM users
    `);
    const activeUsers = usersRow[0]?.active_users || 0;
    const newUsersThisMonth = usersRow[0]?.new_users_this_month || 0;

    // 4. New Listings (created in last 30 days & last 7 days)
    const [newListingsRow] = await pool.query(`
      SELECT 
        SUM(CASE WHEN created_at >= NOW() - INTERVAL 30 DAY THEN 1 ELSE 0 END) as new_listings_30d,
        SUM(CASE WHEN created_at >= NOW() - INTERVAL 7 DAY THEN 1 ELSE 0 END) as new_listings_7d
      FROM listings
    `);
    const newListings30d = newListingsRow[0]?.new_listings_30d || 0;
    const newListings7d = newListingsRow[0]?.new_listings_7d || 0;

    // 5. Total Visits Per Day (Card 4)
    // Count page_views and listing_views in the last 24h
    let visits24h = 0;
    let visitsGrowth = "+0.0%";
    try {
      const [visitsRow] = await pool.query(`
        SELECT 
          SUM(CASE WHEN created_at >= NOW() - INTERVAL 24 HOUR THEN 1 ELSE 0 END) as visits_24h,
          SUM(CASE WHEN created_at >= NOW() - INTERVAL 48 HOUR AND created_at < NOW() - INTERVAL 24 HOUR THEN 1 ELSE 0 END) as visits_prior_24h
        FROM platform_analytics
        WHERE event_type IN ('page_view', 'listing_view')
      `);
      visits24h = visitsRow[0]?.visits_24h || 0;
      const visitsPrior = visitsRow[0]?.visits_prior_24h || 0;
      if (visitsPrior > 0) {
        const delta = ((visits24h - visitsPrior) / visitsPrior) * 100;
        visitsGrowth = `${delta >= 0 ? '+' : ''}${delta.toFixed(1)}%`;
      } else if (visits24h > 0) {
        visitsGrowth = `+100%`;
      }
    } catch (e) {
      // Table might not exist yet before first event
    }

    // 6. User Engagement (Card 6)
    // Per user directive: includes business page visit + call_click + website_click + directions_click + review_submit
    let totalEngagement = 0;
    let engagementGrowth = "+0.0%";
    try {
      const [engRow] = await pool.query(`
        SELECT 
          COUNT(*) as total_engagement,
          SUM(CASE WHEN created_at >= NOW() - INTERVAL 30 DAY THEN 1 ELSE 0 END) as eng_this_month,
          SUM(CASE WHEN created_at >= NOW() - INTERVAL 60 DAY AND created_at < NOW() - INTERVAL 30 DAY THEN 1 ELSE 0 END) as eng_prior_month
        FROM platform_analytics
        WHERE event_type IN ('listing_view', 'call_click', 'website_click', 'directions_click', 'review_submit')
      `);
      totalEngagement = engRow[0]?.total_engagement || 0;
      const engThisMonth = engRow[0]?.eng_this_month || 0;
      const engPriorMonth = engRow[0]?.eng_prior_month || 0;
      if (engPriorMonth > 0) {
        const delta = ((engThisMonth - engPriorMonth) / engPriorMonth) * 100;
        engagementGrowth = `${delta >= 0 ? '+' : ''}${delta.toFixed(1)}%`;
      } else if (engThisMonth > 0) {
        engagementGrowth = `+100%`;
      }
    } catch (e) {}

    // 7. Site Visits - Real-Time Live Count of Active Visitors Right Now (Card 7)
    let activeVisitorsNow = 1; // At least the current admin
    try {
      const [liveRow] = await pool.query(`
        SELECT COUNT(DISTINCT COALESCE(a.visitor_id, a.ip_address)) as active_now
        FROM platform_analytics a
        LEFT JOIN platform_analytics l 
          ON COALESCE(l.visitor_id, l.ip_address) = COALESCE(a.visitor_id, a.ip_address)
         AND l.event_type = 'leave' 
         AND l.created_at >= a.created_at
        WHERE a.created_at >= NOW() - INTERVAL 75 SECOND
          AND a.event_type != 'leave'
          AND l.id IS NULL
      `);
      const count = liveRow[0]?.active_now || 0;
      if (count > 0) activeVisitorsNow = count;
    } catch (e) {}

    // 8. Pending Submissions Feed (Top 5 awaiting audit)
    const [pendingSubmissions] = await pool.query(`
      SELECT id, name, category, status, created_at
      FROM listings
      WHERE status = 'pending' OR status = 'reviewing'
      ORDER BY created_at DESC
      LIMIT 5
    `);

    return res.json({
      total_businesses: {
        value: totalApproved,
        growth: bizGrowth
      },
      pending_approvals: {
        value: pendingTotal,
        is_high_priority: pendingTotal > 0
      },
      active_users: {
        value: activeUsers,
        delta: `+${newUsersThisMonth}`
      },
      new_listings: {
        value: newListings30d,
        delta: `+${newListings7d}`
      },
      // Native Telemetry: Total visits per day
      total_visits_per_day: {
        value: visits24h,
        delta: visitsGrowth,
        is_static: false
      },
      // Native Telemetry: User Engagement (business visits + conversion clicks)
      user_engagement: {
        value: totalEngagement,
        growth: engagementGrowth,
        is_static: false
      },
      // Native Telemetry: Live real-time visitors right now
      site_visits: {
        value: activeVisitorsNow,
        label: "Live Now",
        is_static: false
      },
      // Revenue (Static $0 per user directive)
      revenue: {
        value: "$0",
        is_static: true
      },
      // Live Pending Submissions Feed (Top 5 awaiting audit)
      pending_submissions: pendingSubmissions || []
    });
  } catch (err) {
    console.error("getOverviewStats error:", err);
    return res.status(500).json({ error: "Internal server error fetching overview metrics." });
  }
}

// Lightweight real-time live visitors counter (called every 5s by overview dashboard)
export async function getLiveVisitors(req, res) {
  try {
    const [liveRow] = await pool.query(`
      SELECT COUNT(DISTINCT COALESCE(a.visitor_id, a.ip_address)) as live_now
      FROM platform_analytics a
      LEFT JOIN platform_analytics l 
        ON COALESCE(l.visitor_id, l.ip_address) = COALESCE(a.visitor_id, a.ip_address)
       AND l.event_type = 'leave' 
       AND l.created_at >= a.created_at
      WHERE a.created_at >= NOW() - INTERVAL 75 SECOND
        AND a.event_type != 'leave'
        AND l.id IS NULL
    `);
    const count = Math.max(1, liveRow[0]?.live_now || 1);
    return res.json({ live_visitors: count });
  } catch (err) {
    return res.json({ live_visitors: 1 });
  }
}
