U ؏i@sdZddlZddlZddlmZmZddlmZddlZej dej ej e ddl mZejejddeeZdd Zd d Zed kreedS) z> Script to verify and show category distribution after update N) create_enginetext)tabulate)settingsz)%(asctime)s - %(levelname)s - %(message)s)levelformatc Csttj}|}tdtdtdtd}||}ddddd g}tt||d d tdtd tdtd }||}dddg}tt||d d tdtdtdddddg}|D]}td| dtd}||d|i}|D]j}|dr$|dnd}|dr:|dnd} |drP|dnd} td|td | d!| qqtdtd"tdtd#}||}|rd$d%dg}tt||d d ntd&W5QRXd'S)(z#Show detailed category distributionQ ================================================================================zOVERALL CATEGORY DISTRIBUTIONP================================================================================a SELECT COALESCE(category, 'uncategorized') as category, COUNT(*) as activity_count, COUNT(DISTINCT developer_id) as developers, ROUND(SUM(duration) / 3600.0, 2) as total_hours, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) as percentage FROM activity_records GROUP BY category ORDER BY activity_count DESC ZCategoryZ ActivitiesZ DevelopersZHoursZ PercentagegridheadersZtablefmtzPRODUCTIVE SUBCATEGORIESa] SELECT COALESCE(subcategory, 'general') as subcategory, COUNT(*) as count, ROUND(SUM(duration) / 3600.0, 2) as hours FROM activity_records WHERE category = 'productive' GROUP BY subcategory ORDER BY count DESC LIMIT 15 Z SubcategoryZCountzSAMPLE ACTIVITIES BY CATEGORYZ productiveZbrowserZserverznon-workz --- z ---a_ SELECT LEFT(window_title, 80) as title, application_name as app, subcategory FROM activity_records WHERE category = :category AND window_title IS NOT NULL ORDER BY RANDOM() LIMIT 5 categoryrzNo titlezNo appZgeneralu • z App: z | Subcategory: z&UNCATEGORIZED ACTIVITIES (Need Review)aG SELECT LEFT(window_title, 60) as title, application_name as app, COUNT(*) as count FROM activity_records WHERE category IS NULL GROUP BY window_title, application_name ORDER BY count DESC LIMIT 10 z Window TitleZ Applicationu#✓ All activities are categorized!N) rr DATABASE_URLconnectprintrexecutefetchallrupper) engineconnqueryresultr categoriesr rowtitleZappZsubcatr./verify_categories.pyverify_categoriessN         rc Csrttj}|V}tdtdtdtd}||}ddddd d g}tt||d d W5QRXd S)z)Show productivity statistics by developerrzPRODUCTIVITY BY DEVELOPERr a WITH developer_stats AS ( SELECT developer_id, category, SUM(duration) as category_duration FROM activity_records WHERE category IS NOT NULL GROUP BY developer_id, category ), developer_totals AS ( SELECT developer_id, SUM(duration) as total_duration FROM activity_records GROUP BY developer_id ) SELECT ds.developer_id, ROUND(dt.total_duration / 3600.0, 2) as total_hours, ROUND(SUM(CASE WHEN ds.category = 'productive' THEN ds.category_duration ELSE 0 END) / 3600.0, 2) as productive_hours, ROUND(SUM(CASE WHEN ds.category = 'browser' THEN ds.category_duration ELSE 0 END) / 3600.0, 2) as browser_hours, ROUND(SUM(CASE WHEN ds.category = 'server' THEN ds.category_duration ELSE 0 END) / 3600.0, 2) as server_hours, ROUND(100.0 * SUM(CASE WHEN ds.category = 'productive' THEN ds.category_duration ELSE 0 END) / dt.total_duration, 2) as productivity_percentage FROM developer_stats ds JOIN developer_totals dt ON ds.developer_id = dt.developer_id GROUP BY ds.developer_id, dt.total_duration ORDER BY productivity_percentage DESC Z Developerz Total HoursZ ProductiveZBrowserZServerzProductivity %r r N) rrrrrrrrr)rrrrr rrrshow_productivity_by_developers  r __main__)__doc__sysosZ sqlalchemyrrrZloggingpathinsertdirnameabspath__file__ZconfigrZ basicConfigINFOZ getLogger__name__loggerrr rrrrs"   j-