-- Fixed SQL for Gmail, YouTube, and other non-productive items in Productive category
-- Works with PostgreSQL without type errors

-- First, let's see what we're dealing with
\echo === Current Misplaced Activities ===

-- Check Gmail in productive
SELECT 
    'Gmail in Productive' as type,
    COUNT(*) as count,
    CAST(SUM(duration)/3600.0 AS DECIMAL(10,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,
    CAST(SUM(duration)/3600.0 AS DECIMAL(10,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,
    CAST(SUM(duration)/3600.0 AS DECIMAL(10,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%'
);

\echo
\echo === Fixing Categories ===

-- 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%'
);

\echo Gmail activities updated

-- 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%'
);

\echo YouTube activities updated

-- 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%'
);

\echo Social media activities updated

-- 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%'
);

\echo Entertainment activities updated

-- 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=%'
);

\echo Search activities updated

COMMIT;

\echo
\echo === After Fix - Category Distribution ===

-- Show the results without ROUND function to avoid type errors
SELECT 
    category,
    COUNT(*) as activities,
    SUM(duration)::numeric/3600 as hours,
    (100.0 * SUM(duration) / NULLIF(SUM(SUM(duration)) OVER (), 0))::numeric(5,1) as percentage
FROM activity_records
WHERE timestamp >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY category
ORDER BY hours DESC;

\echo
\echo === Sample Browser Activities (should include Gmail/YouTube) ===

-- Show sample of what's now in browser category
SELECT DISTINCT
    LEFT(window_title, 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;

\echo
\echo Fix completed! Gmail, YouTube, and social media have been moved to the Browser category.