# Test the updated project filtering query
from sqlalchemy import create_engine, text
import os

DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://postgres:asdf1234@localhost:5432/timesheet")
engine = create_engine(DATABASE_URL)

print("Testing updated project filter query...")
print("=" * 80)

with engine.connect() as conn:
    # Test the new query
    result = conn.execute(text("""
        SELECT
            project_name,
            COUNT(*) as activity_count,
            SUM(duration) / 3600.0 as total_hours,
            COUNT(DISTINCT developer_id) as developer_count,
            COUNT(DISTINCT DATE(timestamp)) as active_days
        FROM activity_records
        WHERE project_name IS NOT NULL
        AND project_name != ''
        AND LENGTH(project_name) >= 4
        AND category = 'productive'
        AND project_name !~ '^[0-9]+\\.'
        AND project_name !~ '^\\?'
        AND project_name !~ '^Merging:'
        AND project_name !~ '\\(Working Tree\\)'
        AND project_name !~ '\\(Index\\)'
        AND project_name !~ ' and [0-9]+ more tab'
        AND project_name !~ '(Error|Exception|SQLSTATE|HTTP Method)'
        AND project_name !~ '\\(.:?\\)'
        AND project_name !~ ' messaged you'
        AND project_name !~ '^(New Volume|Windows-SSD|Local Disk)'
        AND project_name !~* '^(new tab|blank)$'
        AND project_name !~* '^(dow futures|error test|wrong |scheme list|test cases)'
        AND project_name !~ '(FTP|Hosting|DB|API|localhost):'
        AND project_name !~ '^http'
        AND project_name !~ '^[0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+'
        AND project_name !~ '/'
        AND project_name !~ '\\.(com|org|net|io|dev|in|co)$'
        AND project_name !~ '\\.co\\.'
        AND project_name !~ '\\.(js|ts|jsx|tsx|vue|py|php|java|rb|go|rs|c|cpp|cs|h)$'
        AND project_name !~ '\\.(css|scss|sass|less|html|htm|xml|svg)$'
        AND project_name !~ '\\.(json|yml|yaml|toml|ini|cfg|conf|env|lock)$'
        AND project_name !~ '\\.(md|txt|doc|docx|pdf|rtf)$'
        AND project_name !~ '\\.(png|jpg|jpeg|gif|ico|webp|bmp)$'
        AND project_name !~ '\\.(sh|bat|cmd|ps1|exe|dll|so)$'
        AND project_name !~ '\\.(sql|db|sqlite|log|bak|tmp|cache)$'
        AND project_name !~ '\\.(zip|tar|gz|rar|7z|aspx?|cs)$'
        AND project_name !~ '\\.(xls|xlsx|xlsm|csv)$'
        AND project_name !~ '\\.(blade|pem|key|crt|cer|p12|pfx)$'
        AND project_name !~ '\\.(git|gitignore|dockerignore)$'
        AND project_name !~ '__'
        AND project_name !~ '^\\*'
        AND LENGTH(project_name) >= 4
        AND project_name !~* '^(login|logout|signin|signout|register|signup)$'
        AND project_name !~* '^(home|dashboard|profile|settings|admin)$'
        AND project_name !~* '^(success|error|404|403|500)$'
        AND project_name !~* '^(customers?|users?|products?|orders?|items?)$'
        AND project_name !~* '^(meet|zoom|teams|skype|slack)$'
        AND project_name !~* '^(termius|putty|winscp)$'
        AND project_name !~* '^(body exfoliator|gold rate|gift nifty|task list|new folder|new request|open file|invalid endpoint)$'
        AND project_name !~* '^(salicylic acid|vitamin|protein|retinol|hyaluronic)'
        AND project_name !~* '(form|password|managment|management)$'
        AND project_name !~* '(futures|index live|messaged|asset)'
        AND project_name !~* '^(get |create |download |import |generate |call to |update |register )'
        AND LOWER(project_name) NOT IN (
            'web browsing', 'ide work', 'database work', 'api development',
            'filezilla/ftp', 'cpanel/hosting', 'unknown', 'general',
            'system32', 'windows', 'desktop', 'documents', 'downloads', 'downlaods',
            'program', 'settings', 'extensions', 'terminal', 'output',
            'debug', 'problems', 'console', 'welcome', 'untitled',
            'inbox', 'drafts', 'sent', 'trash', 'spam',
            'google', 'gmail', 'outlook', 'yahoo', 'chrome', 'firefox', 'edge',
            'termius', 'meet', 'login', 'logout', 'profile', 'home', 'dashboard',
            'success', 'error', 'customers', 'users', 'products', 'orders',
            'bitcoin', 'scratches', 'startup', 'runner', 'minimalist',
            'authentication', 'overview', 'nav', 'keyfiles', 'backend', 'frontend',
            'components', 'validation', 'projects', 'activitywatch',
            'contact-form-lead', 'share-single-url', 'share-folder-url',
            'view-single-doc', 'store-collaterals', 'collaterals',
            'change-password', 'forgot-password', 'update-profile',
            'marketing-support', 'registration', 'xampp', 'htdocs', 'assets',
            'opening', 'filters', 'coll', 'solution', 'solution1', 'claude',
            'tradingview', 'navandidcw', 'src', 'dist', 'build', 'public',
            'static', 'images', 'uploads', 'temp', 'tmp',
            'activitywatch sync', 'activitywatch sync setup', 'new tab',
            'login form', 'change password', 'profile managment', 'register broker',
            'validation error', 'javascript quiz data reference error', 'all indices'
        )
        GROUP BY project_name
        ORDER BY total_hours DESC
        LIMIT 50
    """))

    projects = result.fetchall()

    print(f"\nFound {len(projects)} projects after filtering:")
    print("-" * 80)
    print(f"{'Project Name':<40} {'Activities':<12} {'Hours':<10}")
    print("-" * 80)

    for row in projects:
        project_name = row[0][:40]
        activity_count = row[1]
        total_hours = row[2]
        print(f"{project_name:<40} {activity_count:<12} {total_hours:.2f}")

    print("\n" + "=" * 80)
    print("Checking for any remaining file extensions or invalid names...")
    print("-" * 80)

    # Check if any have spaces
    spaces = [p for p in projects if ' ' in p[0]]
    print(f"Projects with spaces: {len(spaces)}")
    if spaces:
        for p in spaces[:10]:
            print(f"  - {p[0]}")

    # Check if any have file extensions
    extensions = ['.js', '.ts', '.py', '.css', '.html', '.json', '.php']
    for ext in extensions:
        matching = [p for p in projects if p[0].lower().endswith(ext)]
        if matching:
            print(f"\nProjects ending with {ext}: {len(matching)}")
            for p in matching[:5]:
                print(f"  - {p[0]}")

print("\n" + "=" * 80)
print("Test completed!")