utils/analytics.py

# utils/analytics.py
import streamlit as st
import pandas as pd
import sqlite3
from typing import Dict, List
from datetime import datetime, timedelta
import plotly.express as px
import plotly.graph_objects as go
from utils.database import conn_lock

def get_collection_analytics(conn: sqlite3.Connection) -> Dict:
    """Get analytics for collections and documents."""
    analytics = {}
    
    # Most used collections
    cursor = conn.cursor()
    cursor.execute("""
        SELECT 
            c.name,
            COUNT(DISTINCT cm.chat_id) as usage_count
        FROM collections c
        LEFT JOIN chats ch ON c.id = ch.collection_id
        LEFT JOIN chat_messages cm ON ch.id = cm.chat_id
        GROUP BY c.id
        ORDER BY usage_count DESC
        LIMIT 5
    """)
    analytics['top_collections'] = cursor.fetchall()
    
    # Most viewed documents
    cursor.execute("""
        SELECT 
            d.name,
            COUNT(q.id) as query_count
        FROM documents d
        LEFT JOIN queries q ON d.id = q.document_id
        GROUP BY d.id
        ORDER BY query_count DESC
        LIMIT 5
    """)
    analytics['top_documents'] = cursor.fetchall()
    
    # Recent questions
    cursor.execute("""
        SELECT DISTINCT query
        FROM queries
        ORDER BY query_date DESC
        LIMIT 5
    """)
    analytics['recent_questions'] = cursor.fetchall()
    
    return analytics

def display_analytics_dashboard():
    """Display analytics dashboard."""
    st.markdown("### 📊 Analytics Dashboard")
    
    analytics = get_collection_analytics(st.session_state.db_conn)
    
    col1, col2, col3 = st.columns(3)
    
    with col1:
        st.markdown("#### Top Collections")
        if analytics['top_collections']:
            chart_data = pd.DataFrame(
                analytics['top_collections'],
                columns=['Collection', 'Usage']
            )
            st.bar_chart(chart_data.set_index('Collection'))
    
    with col2:
        st.markdown("#### Most Viewed Documents")
        if analytics['top_documents']:
            for doc in analytics['top_documents']:
                st.metric(doc[0], f"{doc[1]} views")
    
    with col3:
        st.markdown("#### Recent Questions")
        if analytics['recent_questions']:
            for question in analytics['recent_questions']:
                st.write(f"• {question[0]}")