как можно оптимизировать этот скрипт? import streamlit as st
import pandas as pd
import altair as alt
from datetime import datetime, timedelta
import sqlite3

# Function to get database connection
def get_db_connection():
    return sqlite3.connect('gsc_shuba.db', check_same_thread=False)

# Function to load data from SQLite
@st.cache_data
def load_data(_conn, start_date, end_date, cluster=None, url=None, min_clicks=0, max_clicks=None, min_position=1, max_position=100):
    query = f"""
    SELECT date, url, query, clicks, impressions, ctr, position
    FROM daily_data
    WHERE date BETWEEN ? AND ?
    """
    params = [start_date, end_date]

    if cluster and cluster != 'All':
        query += " AND query LIKE ?"
        params.append(f"%{cluster}%")
    if url:
        query += " AND url LIKE ?"
        params.append(f"%{url}%")
    
    query += f" AND clicks BETWEEN ? AND ? AND position BETWEEN ? AND ?"
    params.extend([min_clicks, max_clicks if max_clicks is not None else 1e9, min_position, max_position])

    with get_db_connection() as conn:
        df = pd.read_sql_query(query, conn, params=params, parse_dates=['date'])
    return df

# Function to create clusters
def create_clusters(df, cluster_keywords):
    def assign_cluster(query):
        for cluster, keywords in cluster_keywords.items():
            if any(keyword in query.lower() for keyword in keywords):
                return cluster
        return 'Other'
    
    df['Cluster'] = df['query'].apply(assign_cluster)
    return df

# Function to calculate metrics
@st.cache_data
def calculate_metrics(_conn, start_date, end_date, prev_start_date, prev_end_date):
    query = """
    SELECT SUM(clicks) as clicks, SUM(impressions) as impressions, AVG(position) as position
    FROM daily_data
    WHERE date BETWEEN ? AND ?
    """
    
    with get_db_connection() as conn:
        current_df = pd.read_sql_query(query, conn, params=[start_date, end_date])
        prev_df = pd.read_sql_query(query, conn, params=[prev_start_date, prev_end_date])
    
    current_clicks, current_impressions, current_position = current_df.iloc[0]
    prev_clicks, prev_impressions, prev_position = prev_df.iloc[0]
    
    clicks_change = ((current_clicks - prev_clicks) / prev_clicks * 100) if prev_clicks != 0 else 0
    impressions_change = ((current_impressions - prev_impressions) / prev_impressions * 100) if prev_impressions != 0 else 0
    position_change = prev_position - current_position
    
    return {
        'clicks': (current_clicks, prev_clicks, f"{clicks_change:+.2f}%"),
        'impressions': (current_impressions, prev_impressions, f"{impressions_change:+.2f}%"),
        'position': (current_position, prev_position, f"{position_change:+.2f}")
    }

# Function to calculate cluster metrics
@st.cache_data
def calculate_cluster_metrics(_conn, start_date, end_date, prev_start_date, prev_end_date, cluster_keywords):
    query = """
    SELECT 
        CASE 
            {cluster_cases}
            ELSE 'Other'
        END AS Cluster,
        SUM(clicks) as Clicks,
        SUM(impressions) as Impressions,
        AVG(position) as Position
    FROM daily_data
    WHERE date BETWEEN ? AND ?
    GROUP BY Cluster
    """
    
    cluster_cases = "\n".join([f"WHEN query LIKE '%{keyword}%' THEN '{cluster}'" 
                               for cluster, keywords in cluster_keywords.items() 
                               for keyword in keywords])
    
    query = query.format(cluster_cases=cluster_cases)
    
    with get_db_connection() as conn:
        current_df = pd.read_sql_query(query, conn, params=[start_date, end_date])
        prev_df = pd.read_sql_query(query, conn, params=[prev_start_date, prev_end_date])
    
    merged = current_df.merge(prev_df, on='Cluster', suffixes=('', '_prev'))
    
    merged['Clicks_diff'] = merged['Clicks'] - merged['Clicks_prev']
    merged['Impressions_diff'] = merged['Impressions'] - merged['Impressions_prev']
    merged['Position_diff'] = merged['Position_prev'] - merged['Position']
    
    return merged.sort_values('Clicks', ascending=False)

# Function to calculate detailed metrics for cluster
@st.cache_data
def calculate_cluster_details(_conn, cluster, start_date, end_date, prev_start_date, prev_end_date):
    query = """
    SELECT 
        url,
        SUM(clicks) as Clicks,
        SUM(impressions) as Impressions,
        AVG(position) as Position
    FROM daily_data
    WHERE date BETWEEN ? AND ?
        AND query LIKE ?
    GROUP BY url
    """
    
    with get_db_connection() as conn:
        current_df = pd.read_sql_query(query, conn, params=[start_date, end_date, f"%{cluster}%"])
        prev_df = pd.read_sql_query(query, conn, params=[prev_start_date, prev_end_date, f"%{cluster}%"])
    
    merged = current_df.merge(prev_df, on='url', suffixes=('', '_prev'))
    
    merged['Clicks_diff'] = merged['Clicks'] - merged['Clicks_prev']
    merged['Impressions_diff'] = merged['Impressions'] - merged['Impressions_prev']
    merged['Position_diff'] = merged['Position_prev'] - merged['Position']
    
    return merged.sort_values('Clicks', ascending=False)

# Main function
def main():
    st.set_page_config(layout="wide", page_title="GSC Dashboard")
    st.title("Google Search Console Dashboard")

    # Define clusters
    cluster_keywords = {
        'картопля': ['картопл'],
        'капуста': ['капуст'],
        'баклажани': ['баклажан', 'синеньк'],
        'помідори': ['помідор', 'томат'],
        'перець': ['перец', 'перч'],
        'квасоля': ['квасол'],
        'малина': ['малин'],
        'ожина': ['ожин'],
        'сливи': ['слив'],
        'алича': ['алич'],
        'виноград': ['виноград'],
        'кизил': ['кизил'],
        'гарбуз': ['гарбуз'],
        'яблука': ['яблук'],
        'груші': ['груш']
    }

    # Sidebar filters
    st.sidebar.header("Filters")
    
    # Calculate default date ranges
    with get_db_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT MIN(date), MAX(date) FROM daily_data")
        min_date, max_date = map(lambda x: datetime.strptime(x, '%Y-%m-%d').date(), cursor.fetchone())
    
    total_days = (max_date - min_date).days + 1
    mid_point = min_date + timedelta(days=total_days // 2)
    
    if total_days % 2 != 0:
        current_start = mid_point
        prev_end = mid_point - timedelta(days=1)
    else:
        current_start = mid_point + timedelta(days=1)
        prev_end = mid_point

    # Date range for current period
    st.sidebar.subheader("Current Period")
    col1, col2 = st.sidebar.columns(2)
    start_date = col1.date_input("Start date", current_start, key="current_start")
    end_date = col2.date_input("End date", max_date, key="current_end")

    # Date range for previous period
    st.sidebar.subheader("Previous Period")
    compare_periods = st.sidebar.checkbox("Compare with previous period", value=True)
    if compare_periods:
        col3, col4 = st.sidebar.columns(2)
        prev_start_date = col3.date_input("Previous start date", min_date, key="prev_start")
        prev_end_date = col4.date_input("Previous end date", prev_end, key="prev_end")
    else:
        prev_start_date = prev_end_date = None

    # Cluster selection
    clusters = ['All'] + list(cluster_keywords.keys()) + ['Other']
    selected_cluster = st.sidebar.selectbox("Topic cluster", clusters)

    # URL filter
    url_filter = st.sidebar.text_input("Page URL (contains)")

    # Click range
    with get_db_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT MAX(clicks) FROM daily_data")
        max_clicks_db = cursor.fetchone()[0]
    min_clicks, max_clicks = st.sidebar.slider("Clicks", 0, int(max_clicks_db), (0, int(max_clicks_db)))

    # Position range
    min_position, max_position = st.sidebar.slider("Position", 1, 100, (1, 100))

    # Reset filters button
    if st.sidebar.button("Reset All Filters"):
        st.experimental_rerun()

    # Load data
    df = load_data(None, start_date, end_date, selected_cluster, url_filter, min_clicks, max_clicks, min_position, max_position)
    df = create_clusters(df, cluster_keywords)

    # Calculate metrics
    if compare_periods:
        metrics = calculate_metrics(None, start_date, end_date, prev_start_date, prev_end_date)
    else:
        metrics = calculate_metrics(None, start_date, end_date, start_date, end_date)

    # Display metrics
    col1, col2, col3 = st.columns(3)
    col1.metric("Clicks", f"{metrics['clicks'][0]:,}", f"{metrics['clicks'][2]} (Prev: {metrics['clicks'][1]:,})" if compare_periods else None)
    col2.metric("Impressions", f"{metrics['impressions'][0]:,}", f"{metrics['impressions'][2]} (Prev: {metrics['impressions'][1]:,})" if compare_periods else None)
    col3.metric("Avg Position", f"{metrics['position'][0]:.2f}", f"{metrics['position'][2]} (Prev: {metrics['position'][1]:.2f})" if compare_periods else None)

    # Create line chart
    chart_data = df.groupby('date').agg({'clicks': 'sum', 'impressions': 'sum'}).reset_index()
    clicks_chart = alt.Chart(chart_data).mark_line(color='blue').encode(
        x='date:T',
        y=alt.Y('clicks:Q', axis=alt.Axis(title='Clicks', titleColor='blue'))
    )
    impressions_chart = alt.Chart(chart_data).mark_line(color='red').encode(
        x='date:T',
        y=alt.Y('impressions:Q', axis=alt.Axis(title='Impressions', titleColor='red'))
    )
    chart = alt.layer(clicks_chart, impressions_chart).resolve_scale(
        y='independent'
    ).properties(
        width=800,
        height=400
    ).interactive()
    
    st.altair_chart(chart, use_container_width=True)

    # Display interactive table for Topic Clusters
    st.subheader("Topic Clusters")
    cluster_data = calculate_cluster_metrics(None, start_date, end_date, prev_start_date, prev_end_date, cluster_keywords)
    
    # Allow user to select visible columns
    st.sidebar.subheader("Select columns to display")
    all_columns = ['Cluster', 'Clicks', 'Clicks_prev', 'Clicks_diff', 'Impressions', 'Impressions_prev', 'Impressions_diff', 'Position', 'Position_prev', 'Position_diff']
    columns_to_display = st.sidebar.multiselect(
        "Choose columns",
        options=all_columns,
        default=all_columns
    )
    
    # Ensure 'Cluster' is always included
    if 'Cluster' not in columns_to_display:
        columns_to_display = ['Cluster'] + columns_to_display

    # Display cluster summary with comparative data
    st.dataframe(cluster_data[columns_to_display].style.format({
        'Clicks': '{:,.0f}',
        'Clicks_prev': '{:,.0f}',
        'Clicks_diff': '{:+,.0f}',
        'Impressions': '{:,.0f}',
        'Impressions_prev': '{:,.0f}',
        'Impressions_diff': '{:+,.0f}',
        'Position': '{:.2f}',
        'Position_prev': '{:.2f}',
        'Position_diff': '{:+.2f}'
    }).set_properties(**{'text-align': 'right'}))

    # Allow user to select a cluster to view details
    selected_cluster_details = st.selectbox("Select a cluster to view details (press Enter after selection):", cluster_data['Cluster'].tolist())

    if selected_cluster_details:
        st.subheader(f"Details for {selected_cluster_details}")
        cluster_details = calculate_cluster_details(None, selected_cluster_details, start_date, end_date, prev_start_date, prev_end_date)
        
        # Ensure we're only displaying columns that exist in cluster_details
        valid_columns = [col for col in columns_to_display if col in cluster_details.columns]
        valid_columns = ['url'] + [col for col in valid_columns if col != 'Cluster']
        
        st.dataframe(cluster_details[valid_columns].style.format({
            'Clicks': '{:,.0f}',
            'Clicks_prev': '{:,.0f}',
            'Clicks_diff': '{:+,.0f}',
            'Impressions': '{:,.0f}',
            'Impressions_prev': '{:,.0f}',
            'Impressions_diff': '{:+,.0f}',
            'Position': '{:.2f}',
            'Position_prev': '{:.2f}',
            'Position_diff': '{:+.2f}'
        }).set_properties(**{'text-align': 'right'}))

    # Export data
    if st.button('Export filtered data to CSV'):
        csv = df.to_csv(index=False)
        st.download_button(
            label="Download CSV",
            data=csv,
            file_name="filtered_gsc_data.csv",
            mime="text/csv",
        )

if __name__ == "__main__":
    main()