-- RUN THIS SQL IN phpMyAdmin or MySQL
-- This will fix your project grouping issues

-- 1. First, let's see what we're working with
SELECT project_name, COUNT(*) as count 
FROM activity_records 
WHERE developer_id = 'ankita_gholap' 
AND DATE(timestamp) = CURDATE()
GROUP BY project_name
ORDER BY count DESC
LIMIT 10;

-- 2. Fix project names for Cursor and VS Code
UPDATE activity_records 
SET project_name = 
    CASE
        -- Cursor: "file - project - Cursor"
        WHEN application_name = 'Cursor.exe' AND window_title LIKE '% - % - Cursor' THEN
            TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(window_title, ' - Cursor', 1), ' - ', -1))
        
        -- Cursor: "project - Cursor"  
        WHEN application_name = 'Cursor.exe' AND window_title LIKE '% - Cursor' THEN
            TRIM(SUBSTRING_INDEX(window_title, ' - Cursor', 1))
            
        -- VS Code: "file - project - Visual Studio Code"
        WHEN application_name = 'Code.exe' AND window_title LIKE '% - % - Visual Studio Code' THEN
            TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(window_title, ' - Visual Studio Code', 1), ' - ', -1))
            
        ELSE project_name
    END
WHERE developer_id = 'ankita_gholap'
AND application_name IN ('Cursor.exe', 'Code.exe');

-- 3. Merge all timesheet variations
UPDATE activity_records
SET project_name = 'timesheet'
WHERE project_name IN ('timesheet_new', 'timesheet_New', 'timesheet_backup');

-- 4. Remove emails/browser stuff from projects
UPDATE activity_records
SET project_name = NULL
WHERE application_name = 'chrome.exe'
OR window_title LIKE '%Gmail%'
OR window_title LIKE '%Inbox%';

-- 5. Check the results
SELECT 
    project_name,
    COUNT(*) as activities,
    CONCAT(FLOOR(SUM(duration)/1000/3600), 'h ', FLOOR((SUM(duration)/1000%3600)/60), 'm') as time
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURDATE()
AND application_name IN ('Cursor.exe', 'Code.exe')
GROUP BY project_name
ORDER BY SUM(duration) DESC;
