-- TEST QUERY FOR POSTGRESQL
-- Run this AFTER applying the fix to see if it worked

-- 1. Show project summary
SELECT 
    COALESCE(project_name, 'No Project Set') as project,
    COUNT(*) as total_activities,
    COUNT(DISTINCT 
        CASE 
            WHEN position('.' in split_part(window_title, ' - ', 1)) > 0 
            THEN split_part(window_title, ' - ', 1)
            ELSE NULL 
        END
    ) as unique_files,
    TO_CHAR(SUM(duration) / 1000.0 / 3600, 'FM999.0') || 'h' as hours_spent,
    STRING_AGG(DISTINCT application_name, ', ') as editors_used
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
AND application_name IN ('Cursor.exe', 'Code.exe')
GROUP BY project_name
ORDER BY SUM(duration) DESC;

-- 2. If you still see mostly "general", show what's in there
SELECT 
    window_title,
    application_name,
    project_name,
    duration/1000/60 as minutes
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
AND project_name = 'general'
AND application_name IN ('Cursor.exe', 'Code.exe')
LIMIT 10;

-- 3. Show successfully extracted projects (not general)
SELECT 
    project_name,
    window_title,
    duration/1000/60 as minutes
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
AND project_name IS NOT NULL
AND project_name != 'general'
AND application_name IN ('Cursor.exe', 'Code.exe')
ORDER BY duration DESC
LIMIT 10;
