-- Fix project extraction in activity_records table
-- This script properly extracts project names and filters out non-project items

-- First, let's see what's currently marked as projects
SELECT 
    project_name,
    window_title,
    application_name,
    COUNT(*) as count,
    SUM(duration)/1000/60 as minutes
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
GROUP BY project_name, window_title, application_name
ORDER BY count DESC
LIMIT 20;

-- Reset project names for non-code activities to NULL
UPDATE activity_records
SET project_name = NULL
WHERE developer_id = 'ankita_gholap'
AND (
    -- Email related
    LOWER(window_title) LIKE '%inbox%' OR
    LOWER(window_title) LIKE '%gmail%' OR
    LOWER(window_title) LIKE '%outlook%' OR
    LOWER(window_title) LIKE '%fw:%' OR
    LOWER(window_title) LIKE '%re:%' OR
    LOWER(window_title) LIKE '%fwd:%' OR
    -- Browser tabs
    LOWER(window_title) LIKE '%http://%' OR
    LOWER(window_title) LIKE '%https://%' OR
    LOWER(window_title) LIKE '%www.%' OR
    LOWER(window_title) LIKE '%generate captcha%' OR
    LOWER(window_title) LIKE '%file manager%' OR
    LOWER(window_title) LIKE '%websites & domains%' OR
    LOWER(window_title) LIKE '%repository system proposal%' OR
    LOWER(window_title) LIKE '%contact-form%' OR
    -- Non-code applications
    LOWER(application_name) NOT IN (
        'code.exe', 'code', 'visual studio code',
        'cursor.exe', 'cursor',
        'notepad++.exe', 'notepad++',
        'sublime_text.exe', 'sublime text',
        'phpstorm', 'phpstorm64.exe',
        'webstorm', 'webstorm64.exe',
        'pycharm', 'pycharm64.exe',
        'intellij', 'idea64.exe',
        'atom.exe', 'atom',
        'brackets.exe', 'brackets',
        'vim', 'nvim', 'gvim',
        'emacs',
        'filezilla.exe', 'filezilla'
    )
);

-- Now update project names for actual code activities
UPDATE activity_records 
SET project_name = 
    CASE
        -- VS Code pattern: "filename.ext - projectname - Visual Studio Code"
        WHEN LOWER(application_name) IN ('code.exe', 'code', 'visual studio code') 
            AND window_title LIKE '% - % - %' THEN
            TRIM(SPLIT_PART(window_title, ' - ', 2))
            
        -- Cursor pattern: "projectname - filename.ext - Cursor" or "filename.ext - projectname - Cursor"
        WHEN LOWER(application_name) IN ('cursor.exe', 'cursor') 
            AND window_title LIKE '% - % - %' THEN
            CASE
                -- Check if second part has file extension
                WHEN SPLIT_PART(window_title, ' - ', 2) LIKE '%.%' THEN 
                    TRIM(SPLIT_PART(window_title, ' - ', 1))
                ELSE 
                    TRIM(SPLIT_PART(window_title, ' - ', 2))
            END
            
        -- FileZilla pattern: "projectname - FileZilla"
        WHEN LOWER(application_name) IN ('filezilla.exe', 'filezilla') 
            AND window_title LIKE '% - FileZilla' THEN
            TRIM(SPLIT_PART(window_title, ' - ', 1))
            
        -- Two-part pattern: "filename.ext - projectname"
        WHEN window_title LIKE '% - %' 
            AND window_title NOT LIKE '% - % - %' THEN
            TRIM(SPLIT_PART(window_title, ' - ', 2))
            
        -- Keep existing valid project names
        WHEN project_name IS NOT NULL 
            AND project_name != '' 
            AND project_name != 'general' 
            AND project_name NOT LIKE '%inbox%'
            AND project_name NOT LIKE '%http%' THEN
            project_name
            
        ELSE NULL
    END
WHERE developer_id = 'ankita_gholap'
AND LOWER(application_name) IN (
    'code.exe', 'code', 'visual studio code',
    'cursor.exe', 'cursor',
    'notepad++.exe', 'notepad++',
    'sublime_text.exe', 'sublime text',
    'phpstorm', 'phpstorm64.exe',
    'webstorm', 'webstorm64.exe',
    'pycharm', 'pycharm64.exe',
    'intellij', 'idea64.exe',
    'atom.exe', 'atom',
    'brackets.exe', 'brackets',
    'vim', 'nvim', 'gvim',
    'emacs',
    'filezilla.exe', 'filezilla'
);

-- Clean up extracted project names
UPDATE activity_records
SET project_name = NULL
WHERE project_name IN (
    'Visual Studio Code', 'VS Code', 'Code',
    'Cursor', 'Notepad++', 'Sublime Text',
    'PhpStorm', 'WebStorm', 'PyCharm', 'IntelliJ',
    'Atom', 'Brackets', 'vim', 'emacs',
    '', 'null', 'undefined'
);

-- Set NULL project names to 'general' for code activities
UPDATE activity_records
SET project_name = 'general'
WHERE project_name IS NULL
AND developer_id = 'ankita_gholap'
AND LOWER(application_name) IN (
    'code.exe', 'code', 'visual studio code',
    'cursor.exe', 'cursor',
    'notepad++.exe', 'notepad++',
    'sublime_text.exe', 'sublime text',
    'phpstorm', 'phpstorm64.exe',
    'webstorm', 'webstorm64.exe',
    'pycharm', 'pycharm64.exe',
    'intellij', 'idea64.exe',
    'atom.exe', 'atom',
    'brackets.exe', 'brackets',
    'vim', 'nvim', 'gvim',
    'emacs',
    'filezilla.exe', 'filezilla'
);

-- Verify the results - should only show actual projects now
SELECT 
    project_name,
    COUNT(*) as activity_count,
    COUNT(DISTINCT 
        CASE 
            WHEN window_title LIKE '%.%' THEN SPLIT_PART(window_title, ' - ', 1)
            ELSE window_title 
        END
    ) as file_count,
    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 project_name IS NOT NULL
GROUP BY project_name
ORDER BY total_minutes DESC;

-- Show sample of properly categorized activities
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 project_name IS NOT NULL
AND project_name != 'general'
ORDER BY duration DESC
LIMIT 20;
