-- PostgreSQL version of the fix
-- This handles the specific window title patterns in your data

-- First, create a function to extract project names
CREATE OR REPLACE FUNCTION extract_project_from_title(window_title TEXT, app_name TEXT)
RETURNS TEXT AS $$
BEGIN
    -- Handle Cursor patterns
    IF app_name = 'Cursor.exe' THEN
        IF window_title LIKE '% - % - Cursor' THEN
            -- Pattern: "file - project - Cursor"
            RETURN TRIM(SUBSTRING(window_title FROM '.*- (.*) - Cursor'));
        ELSIF window_title LIKE '% - Cursor' THEN
            -- Pattern: "project - Cursor"
            RETURN TRIM(SUBSTRING(window_title FROM '(.*) - Cursor'));
        END IF;
    
    -- Handle VS Code patterns
    ELSIF app_name = 'Code.exe' THEN
        IF window_title LIKE '% - % - Visual Studio Code' THEN
            -- Pattern: "file - project - Visual Studio Code"
            RETURN TRIM(SUBSTRING(window_title FROM '.*- (.*) - Visual Studio Code'));
        ELSIF window_title LIKE '% - Visual Studio Code' THEN
            -- Pattern: "project - Visual Studio Code"
            RETURN TRIM(SUBSTRING(window_title FROM '(.*) - Visual Studio Code'));
        END IF;
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Update all activities to extract project names
UPDATE activity_records
SET project_name = COALESCE(
    extract_project_from_title(window_title, application_name),
    project_name
)
WHERE developer_id = 'ankita_gholap'
AND application_name IN ('Cursor.exe', 'Code.exe')
AND (project_name = 'general' OR project_name IS NULL);

-- Normalize timesheet variations
UPDATE activity_records
SET project_name = 'timesheet'
WHERE developer_id = 'ankita_gholap'
AND project_name IN ('timesheet_new', 'timesheet_backup', 'timesheet_New')
AND application_name IN ('Cursor.exe', 'Code.exe');

-- Remove non-project entries
UPDATE activity_records
SET project_name = NULL
WHERE developer_id = 'ankita_gholap'
AND (
    application_name = 'chrome.exe' OR
    window_title ILIKE '%gmail%' OR
    window_title ILIKE '%inbox%' OR
    window_title ILIKE '%localhost%'
);

-- Verify the results
SELECT 
    project_name,
    COUNT(*) as activity_count,
    COUNT(DISTINCT SPLIT_PART(window_title, ' - ', 1)) as file_count,
    ROUND(SUM(duration::numeric)/1000/60, 2) as total_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
GROUP BY project_name
ORDER BY total_minutes DESC;
