-- MySQL/MariaDB version of the fix
-- This handles the specific window title patterns in your data

-- Update project names for Cursor activities
UPDATE activity_records 
SET project_name = 
    CASE
        -- Pattern: "filename.sql - timesheet_new - Cursor"
        WHEN application_name = 'Cursor.exe' AND window_title LIKE '% - % - Cursor' THEN
            TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(window_title, ' - Cursor', 1), ' - ', -1))
            
        -- Pattern: "timesheet_new - Cursor"
        WHEN application_name = 'Cursor.exe' AND window_title LIKE '% - Cursor' THEN
            TRIM(SUBSTRING_INDEX(window_title, ' - Cursor', 1))
            
        -- Pattern for 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))
            
        -- Pattern: "project - Visual Studio Code"
        WHEN application_name = 'Code.exe' AND window_title LIKE '% - Visual Studio Code' THEN
            TRIM(SUBSTRING_INDEX(window_title, ' - Visual Studio Code', 1))
            
        ELSE project_name
    END
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURDATE()
AND application_name IN ('Cursor.exe', 'Code.exe')
AND (project_name = 'general' OR project_name IS NULL OR project_name = '');

-- Normalize timesheet variations
UPDATE activity_records
SET project_name = 'timesheet'
WHERE developer_id = 'ankita_gholap'
AND (
    window_title LIKE '%timesheet_new%' OR
    window_title LIKE '%timesheet_backup%' OR
    window_title LIKE '%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 LIKE '%Gmail%' OR
    window_title LIKE '%Inbox%' OR
    window_title LIKE '%localhost%' OR
    window_title LIKE '%Claude%' OR
    window_title IS NULL
);

-- Verify the results
SELECT 
    project_name,
    COUNT(*) as activity_count,
    COUNT(DISTINCT SUBSTRING_INDEX(window_title, ' - ', 1)) as file_count,
    ROUND(SUM(duration)/1000/60, 2) as total_minutes,
    GROUP_CONCAT(DISTINCT application_name) as apps_used
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURDATE()
AND application_name IN ('Cursor.exe', 'Code.exe')
AND project_name IS NOT NULL
GROUP BY project_name
ORDER BY total_minutes DESC;

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