-- IMMEDIATE FIX for your database
-- Run this SQL to extract project names from your existing data

-- First, let's 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(SPLIT_PART(SPLIT_PART(window_title, ' - Cursor', 1), ' - ', -1))
            
        -- Pattern: "timesheet_new - Cursor" (just project name)
        WHEN application_name = 'Cursor.exe' AND window_title LIKE '% - Cursor' THEN
            TRIM(SPLIT_PART(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(SPLIT_PART(SPLIT_PART(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(SPLIT_PART(window_title, ' - Visual Studio Code', 1))
            
        -- Keep existing if already set
        WHEN project_name IS NOT NULL AND project_name != '' AND project_name != 'general' THEN
            project_name
            
        ELSE 'general'
    END
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
AND application_name IN ('Cursor.exe', 'Code.exe')
AND (project_name = 'general' OR project_name IS NULL);

-- Specific fix for your timesheet project 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 - %'
)
AND application_name IN ('Cursor.exe', 'Code.exe');

-- Clean up browser/email entries that shouldn't be 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 '%localhost%' OR
    window_title LIKE '%Real Estate%' OR
    window_title LIKE '%Claude%' OR
    window_title IS NULL OR
    window_title = 'Unknown'
);

-- Verify the fix
SELECT 
    project_name,
    COUNT(*) as activity_count,
    COUNT(DISTINCT window_title) as unique_files,
    SUM(duration)/1000/60 as total_minutes,
    STRING_AGG(DISTINCT application_name, ', ') as apps_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 total_minutes DESC;

-- See a sample of the updated data
SELECT 
    project_name,
    window_title,
    application_name,
    duration/1000/60 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 != 'general'
ORDER BY duration DESC
LIMIT 20;
