-- Diagnostic queries to understand your data

-- 1. See what applications are being tracked
SELECT 
    application_name,
    COUNT(*) as count,
    SUM(duration)/1000/60 as total_minutes
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
GROUP BY application_name
ORDER BY total_minutes DESC;

-- 2. See what's currently in project_name field
SELECT 
    project_name,
    COUNT(*) as count,
    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 count DESC
LIMIT 20;

-- 3. Sample of window titles for code editors
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 LOWER(application_name) IN (
    'code.exe', 'code', 'visual studio code',
    'cursor.exe', 'cursor',
    'notepad++.exe', 'notepad++',
    'sublime_text.exe', 'sublime text'
)
ORDER BY duration DESC
LIMIT 30;

-- 4. Find what's being categorized as "general"
SELECT 
    window_title,
    application_name,
    category,
    duration/1000/60 as minutes
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
AND project_name = 'general'
ORDER BY duration DESC
LIMIT 20;

-- 5. Check emails and browser activities
SELECT 
    window_title,
    application_name,
    project_name,
    category,
    COUNT(*) as count
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
AND (
    LOWER(window_title) LIKE '%inbox%' OR
    LOWER(window_title) LIKE '%gmail%' OR
    LOWER(window_title) LIKE '%outlook%' OR
    LOWER(window_title) LIKE '%mail%' OR
    LOWER(window_title) LIKE '%http%' OR
    LOWER(window_title) LIKE '%contact%' OR
    LOWER(window_title) LIKE '%form%'
)
GROUP BY window_title, application_name, project_name, category
ORDER BY count DESC;

-- 6. Preview what projects would be extracted
SELECT 
    window_title,
    application_name,
    project_name as current_project,
    CASE
        WHEN LOWER(application_name) IN ('code.exe', 'code', 'visual studio code') 
            AND window_title LIKE '% - % - %' THEN
            TRIM(SPLIT_PART(window_title, ' - ', 2))
        WHEN LOWER(application_name) IN ('cursor.exe', 'cursor') 
            AND 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 'Not Extracted'
    END as would_be_project
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
AND LOWER(application_name) IN (
    'code.exe', 'code', 'visual studio code',
    'cursor.exe', 'cursor'
)
LIMIT 20;
