-- Test queries to verify project grouping

-- 1. Check current project distribution
SELECT 
    project_name,
    COUNT(*) as activity_count,
    COUNT(DISTINCT window_title) as unique_windows,
    SUM(duration)/1000/60 as total_minutes,
    STRING_AGG(DISTINCT SUBSTRING(window_title, 1, 50), ' | ') as sample_titles
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
GROUP BY project_name
ORDER BY total_minutes DESC;

-- 2. Preview what projects would be extracted from window titles
SELECT 
    window_title,
    project_name as current_project,
    CASE
        WHEN window_title LIKE '% - % - %' THEN 
            TRIM(SPLIT_PART(window_title, ' - ', 2))
        WHEN window_title LIKE '% - %' AND window_title NOT LIKE '% - % - %' THEN
            TRIM(SPLIT_PART(window_title, ' - ', 2))
        ELSE 'No Project Extracted'
    END as extracted_project
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
AND (project_name IS NULL OR project_name = '' OR project_name = 'general')
LIMIT 20;

-- 3. Group by extracted project names (preview without updating)
WITH extracted_projects AS (
    SELECT 
        *,
        CASE
            WHEN window_title LIKE '% - % - %' THEN 
                TRIM(SPLIT_PART(window_title, ' - ', 2))
            WHEN window_title LIKE '% - %' AND window_title NOT LIKE '% - % - %' THEN
                TRIM(SPLIT_PART(window_title, ' - ', 2))
            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'
            ELSE COALESCE(project_name, 'No Project')
        END as final_project_name
    FROM activity_records
    WHERE developer_id = 'ankita_gholap'
    AND DATE(timestamp) = CURRENT_DATE
)
SELECT 
    final_project_name,
    COUNT(*) as activity_count,
    COUNT(DISTINCT window_title) as file_count,
    SUM(duration)/1000/60 as total_minutes
FROM extracted_projects
GROUP BY final_project_name
ORDER BY total_minutes DESC;

-- 4. Find activities that might need manual project assignment
SELECT 
    window_title,
    application_name,
    project_name,
    duration/1000/60 as minutes
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
AND (project_name IS NULL OR project_name = '' OR project_name = 'general')
AND category = 'productive'
ORDER BY duration DESC
LIMIT 20;
