-- PostgreSQL version of project name extraction
-- This script updates the project_name field based on window title patterns

-- First, create a backup of current data (optional but recommended)
CREATE TEMP TABLE activity_records_backup AS 
SELECT * FROM activity_records 
WHERE developer_id = 'ankita_gholap' 
AND DATE(timestamp) = CURRENT_DATE;

-- Update project names using PostgreSQL string functions
UPDATE activity_records 
SET project_name = 
    CASE
        -- Pattern: "filename.ext - projectname - Application"
        WHEN window_title ~ '^[^-]+ - [^-]+ - [^-]+$' THEN
            TRIM(SPLIT_PART(window_title, ' - ', 2))
            
        -- Pattern: "filename.ext - projectname" (no application name)
        WHEN window_title ~ '^[^-]+ - [^-]+$' THEN
            TRIM(SPLIT_PART(window_title, ' - ', 2))
            
        -- Specific projects from your data
        WHEN window_title ILIKE '%mahindra-manulife-retail%' THEN 'mahindra-manulife-retail'
        WHEN window_title ILIKE '%waree-master%' THEN 'waree-master'
        WHEN window_title ILIKE '%crownlam%' THEN 'crownlam'
        WHEN window_title ILIKE '%timesheet%' AND window_title NOT ILIKE '%timesheetphp%' THEN 'timesheet'
        WHEN window_title ILIKE '%kiki%' THEN 'kiki'
        
        -- FileZilla pattern: "projectname - FileZilla"
        WHEN application_name ILIKE '%filezilla%' AND window_title ~ '^[^-]+ - FileZilla' THEN
            TRIM(SPLIT_PART(window_title, ' - ', 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 (project_name IS NULL OR project_name = '' OR project_name = 'general')
AND category = 'productive';

-- Create a function to extract project names (reusable)
CREATE OR REPLACE FUNCTION extract_project_from_window_title(window_title TEXT)
RETURNS TEXT AS $$
BEGIN
    -- Pattern: "filename - projectname - App"
    IF window_title ~ '^[^-]+ - [^-]+ - [^-]+$' THEN
        RETURN TRIM(SPLIT_PART(window_title, ' - ', 2));
    -- Pattern: "filename - projectname"
    ELSIF window_title ~ '^[^-]+ - [^-]+$' THEN
        RETURN TRIM(SPLIT_PART(window_title, ' - ', 2));
    -- Specific patterns
    ELSIF window_title ILIKE '%mahindra-manulife-retail%' THEN
        RETURN 'mahindra-manulife-retail';
    ELSIF window_title ILIKE '%waree-master%' THEN
        RETURN 'waree-master';
    ELSIF window_title ILIKE '%crownlam%' THEN
        RETURN 'crownlam';
    ELSE
        RETURN NULL;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Use the function to update more records
UPDATE activity_records
SET project_name = COALESCE(
    extract_project_from_window_title(window_title),
    project_name,
    'general'
)
WHERE developer_id = 'ankita_gholap'
AND project_name IN ('', 'general')
OR project_name IS NULL;

-- Verify the results
SELECT 
    project_name,
    COUNT(*) as activities,
    COUNT(DISTINCT SPLIT_PART(window_title, ' - ', 1)) as files,
    ROUND(SUM(duration::numeric)/1000/60, 2) as total_minutes,
    ARRAY_AGG(DISTINCT LEFT(window_title, 50) ORDER BY window_title) FILTER (WHERE window_title IS NOT NULL) as sample_windows
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
GROUP BY project_name
ORDER BY total_minutes DESC;

-- Show activities that still need project assignment
SELECT 
    window_title,
    application_name,
    project_name,
    ROUND(duration::numeric/1000/60, 2) as minutes
FROM activity_records
WHERE developer_id = 'ankita_gholap'
AND DATE(timestamp) = CURRENT_DATE
AND project_name = 'general'
AND category = 'productive'
ORDER BY duration DESC
LIMIT 10;
