-- POSTGRESQL FIX - Run this in your PostgreSQL database
-- 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) = CURRENT_DATE
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(SPLIT_PART(SPLIT_PART(window_title, ' - Cursor', 1), ' - ', -1))
        
        -- Cursor: "project - Cursor"  
        WHEN application_name = 'Cursor.exe' AND window_title LIKE '% - Cursor' THEN
            TRIM(SPLIT_PART(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(SPLIT_PART(SPLIT_PART(window_title, ' - Visual Studio Code', 1), ' - ', -1))
            
        -- VS Code: "project - Visual Studio Code"
        WHEN application_name = 'Code.exe' AND window_title LIKE '% - Visual Studio Code' THEN
            TRIM(SPLIT_PART(window_title, ' - Visual Studio Code', 1))
            
        ELSE project_name
    END
WHERE developer_id = 'ankita_gholap'
AND application_name IN ('Cursor.exe', 'Code.exe')
AND (project_name = 'general' OR project_name IS NULL);

-- 3. Merge all timesheet variations into one
UPDATE activity_records
SET project_name = 'timesheet'
WHERE developer_id = 'ankita_gholap'
AND (
    project_name IN ('timesheet_new', 'timesheet_New', 'timesheet_backup') OR
    window_title LIKE '%timesheet_new%' OR
    window_title LIKE '%timesheet_backup%'
)
AND application_name IN ('Cursor.exe', 'Code.exe');

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

-- 5. Check the results - should show actual projects now
SELECT 
    project_name,
    COUNT(*) as activities,
    COUNT(DISTINCT SPLIT_PART(window_title, ' - ', 1)) as files,
    CONCAT(
        FLOOR(SUM(duration)/1000/3600), 'h ', 
        FLOOR((SUM(duration)/1000%3600)/60), 'm'
    ) as total_time
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
AND application_name IN ('Cursor.exe', 'Code.exe')
AND project_name IS NOT NULL
GROUP BY project_name
ORDER BY SUM(duration) DESC;

-- 6. See sample of fixed data
SELECT 
    project_name,
    window_title,
    application_name,
    ROUND(duration::numeric/1000/60, 2) as minutes
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
AND application_name IN ('Cursor.exe', 'Code.exe')
AND project_name IS NOT NULL
AND project_name != 'general'
ORDER BY duration DESC
LIMIT 20;
