-- Quick SQL Fix for Gmail, YouTube, and other non-productive items in Productive category
-- Run this in your PostgreSQL database

-- First, let's see what we're dealing with
SELECT '=== Current Misplaced Activities ===' as info;

-- Check Gmail in productive
SELECT 
    'Gmail in Productive' as type,
    COUNT(*) as count,
    ROUND(SUM(duration)/3600.0, 1) as hours
FROM activity_records
WHERE category = 'productive'
AND (
    LOWER(window_title) LIKE '%gmail%' 
    OR LOWER(url) LIKE '%mail.google.com%'
);

-- Check YouTube in productive
SELECT 
    'YouTube in Productive' as type,
    COUNT(*) as count,
    ROUND(SUM(duration)/3600.0, 1) as hours
FROM activity_records
WHERE category = 'productive'
AND (
    LOWER(window_title) LIKE '%youtube%' 
    OR LOWER(url) LIKE '%youtube.com%'
    OR LOWER(url) LIKE '%youtu.be%'
);

-- Check social media in productive
SELECT 
    'Social Media in Productive' as type,
    COUNT(*) as count,
    ROUND(SUM(duration)/3600.0, 1) as hours
FROM activity_records
WHERE category = 'productive'
AND (
    LOWER(window_title) LIKE '%facebook%'
    OR LOWER(window_title) LIKE '%instagram%'
    OR LOWER(window_title) LIKE '%twitter%'
    OR LOWER(window_title) LIKE '%reddit%'
);

-- Now fix them
BEGIN;

-- Move Gmail to browser category
UPDATE activity_records
SET 
    category = 'browser',
    subcategory = 'email'
WHERE category = 'productive'
AND (
    LOWER(window_title) LIKE '%gmail%' 
    OR LOWER(url) LIKE '%mail.google.com%'
);

-- Move YouTube to browser category
UPDATE activity_records
SET 
    category = 'browser',
    subcategory = 'entertainment'
WHERE category = 'productive'
AND (
    LOWER(window_title) LIKE '%youtube%' 
    OR LOWER(url) LIKE '%youtube.com%'
    OR LOWER(url) LIKE '%youtu.be%'
);

-- Move social media to browser category
UPDATE activity_records
SET 
    category = 'browser',
    subcategory = 'social-media'
WHERE category = 'productive'
AND (
    LOWER(window_title) LIKE '%facebook%'
    OR LOWER(window_title) LIKE '%instagram%'
    OR LOWER(window_title) LIKE '%twitter%'
    OR LOWER(window_title) LIKE '%reddit%'
    OR LOWER(window_title) LIKE '%whatsapp%'
);

-- Move entertainment to browser category
UPDATE activity_records
SET 
    category = 'browser',
    subcategory = 'entertainment'
WHERE category = 'productive'
AND (
    LOWER(window_title) LIKE '%netflix%'
    OR LOWER(window_title) LIKE '%spotify%'
    OR LOWER(window_title) LIKE '%twitch%'
    OR LOWER(window_title) LIKE '%amazon prime%'
);

-- Move search queries to browser category
UPDATE activity_records
SET 
    category = 'browser',
    subcategory = 'search'
WHERE category = 'productive'
AND (
    LOWER(window_title) LIKE '%google.com/search%'
    OR LOWER(window_title) LIKE '%bing.com/search%'
    OR LOWER(window_title) LIKE '%duckduckgo%'
    OR LOWER(url) LIKE '%?q=%'
    OR LOWER(url) LIKE '%&q=%'
);

COMMIT;

-- Show the results
SELECT '=== After Fix - Category Distribution ===' as info;

SELECT 
    category,
    COUNT(*) as activities,
    ROUND(SUM(duration)/3600.0, 1) as hours,
    ROUND(100.0 * SUM(duration) / SUM(SUM(duration)) OVER (), 1) as percentage
FROM activity_records
WHERE timestamp >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY category
ORDER BY hours DESC;

-- Show sample of what's now in browser category
SELECT '=== Sample Browser Activities (should include Gmail/YouTube) ===' as info;

SELECT DISTINCT
    SUBSTRING(window_title, 1, 80) as window_title,
    application_name
FROM activity_records
WHERE category = 'browser'
AND timestamp >= CURRENT_DATE - INTERVAL '7 days'
AND (
    LOWER(window_title) LIKE '%gmail%'
    OR LOWER(window_title) LIKE '%youtube%'
)
LIMIT 10;