-- ================================================================
-- Populate Last Seen Data for Active Users
-- ================================================================
-- This script updates the last_seen field for all active users
-- who don't currently have last_seen data.
--
-- Purpose: Enable the Active Users List feature on the Dashboard
--
-- Usage:
--   mysql -u root -p cms_db < populate_last_seen.sql
--   (or run this in your MySQL client)
-- ================================================================

-- Show current state
SELECT 
    COUNT(*) as total_active_users,
    SUM(CASE WHEN last_seen IS NOT NULL THEN 1 ELSE 0 END) as users_with_last_seen,
    SUM(CASE WHEN last_seen IS NULL THEN 1 ELSE 0 END) as users_without_last_seen
FROM users 
WHERE is_active = 1;

-- Update users without last_seen
UPDATE users 
SET last_seen = NOW() 
WHERE is_active = 1 AND last_seen IS NULL;

-- Show results
SELECT 
    COUNT(*) as total_active_users,
    SUM(CASE WHEN last_seen IS NOT NULL THEN 1 ELSE 0 END) as users_with_last_seen,
    SUM(CASE WHEN last_seen IS NULL THEN 1 ELSE 0 END) as users_without_last_seen
FROM users 
WHERE is_active = 1;

-- Show sample of recently active users
SELECT 
    id,
    name,
    email,
    rank_id,
    last_seen,
    TIMESTAMPDIFF(MINUTE, last_seen, NOW()) as minutes_since_last_seen
FROM users 
WHERE is_active = 1 AND last_seen IS NOT NULL
ORDER BY last_seen DESC
LIMIT 10;

-- ================================================================
-- Notes:
-- - This only affects users where is_active = 1
-- - Users who already have last_seen data are not affected
-- - The timestamp will be set to the current date/time
-- - This is safe to run multiple times
-- ================================================================

