-- Update project names in activity_records table
-- This script extracts project names from window titles

-- First, let's see what we have
SELECT 
    window_title,
    project_name,
    COUNT(*) as count
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
GROUP BY window_title, project_name
ORDER BY count DESC
LIMIT 20;

-- Update project names based on window title patterns
UPDATE activity_records 
SET project_name = CASE
    -- Pattern: "filename - projectname - Application"
    WHEN window_title LIKE '% - % - %' THEN 
        TRIM(SPLIT_PART(window_title, ' - ', 2))
    
    -- Pattern: "filename - projectname"
    WHEN window_title LIKE '% - %' AND window_title NOT LIKE '% - % - %' THEN
        TRIM(SPLIT_PART(window_title, ' - ', 2))
    
    -- Specific project patterns you mentioned
    WHEN window_title LIKE '%mahindra-manulife-retail%' THEN 'mahindra-manulife-retail'
    WHEN window_title LIKE '%waree-master%' THEN 'waree-master'
    WHEN window_title LIKE '%crownlam%' THEN 'crownlam'
    WHEN window_title LIKE '%timesheet%' AND window_title NOT LIKE '%timesheetphp%' THEN 'timesheet'
    WHEN window_title LIKE '%kiki%' THEN 'kiki'
    
    -- Keep existing project_name if it's already set and not "general"
    WHEN project_name IS NOT NULL AND project_name != '' AND project_name != 'general' THEN project_name
    
    -- Default to extracting from window title
    ELSE 'general'
END
WHERE developer_id = 'ankita_gholap'
AND (project_name IS NULL OR project_name = '' OR project_name = 'general');

-- Verify the update
SELECT 
    project_name,
    COUNT(*) as activity_count,
    COUNT(DISTINCT window_title) as unique_files,
    SUM(duration)/1000/60 as total_minutes
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
GROUP BY project_name
ORDER BY total_minutes DESC;

-- Additional update for common patterns in your environment
UPDATE activity_records 
SET project_name = 
    CASE 
        -- Extract project name from common VS Code patterns
        WHEN application_name LIKE '%Visual Studio Code%' OR application_name LIKE '%Code.exe%' THEN
            CASE
                WHEN window_title LIKE '%.js - % - %' THEN TRIM(SPLIT_PART(SPLIT_PART(window_title, ' - ', 2), ' ', 1))
                WHEN window_title LIKE '%.py - % - %' THEN TRIM(SPLIT_PART(SPLIT_PART(window_title, ' - ', 2), ' ', 1))
                WHEN window_title LIKE '%.php - % - %' THEN TRIM(SPLIT_PART(SPLIT_PART(window_title, ' - ', 2), ' ', 1))
                WHEN window_title LIKE '%.html - % - %' THEN TRIM(SPLIT_PART(SPLIT_PART(window_title, ' - ', 2), ' ', 1))
                WHEN window_title LIKE '%.css - % - %' THEN TRIM(SPLIT_PART(SPLIT_PART(window_title, ' - ', 2), ' ', 1))
                ELSE project_name
            END
        
        -- Extract from Cursor patterns
        WHEN application_name LIKE '%Cursor%' THEN
            CASE
                WHEN window_title LIKE '% - % - Cursor' THEN TRIM(SPLIT_PART(window_title, ' - ', 2))
                ELSE project_name
            END
            
        -- Extract from FileZilla
        WHEN application_name LIKE '%FileZilla%' AND window_title LIKE '% - FileZilla' THEN
            TRIM(SPLIT_PART(window_title, ' - ', 1))
            
        ELSE project_name
    END
WHERE developer_id = 'ankita_gholap'
AND project_name = 'general';
