Metabase Field Filter

				
					## connections

dbconn = pq.dbconnect('dw_509')
metabase_api = pq.connect('Metabase')


st.title("Update METABASE queries with new variables")
st.text("Please provide the id and name of the query and variables we can consider as example")
template_query_id = st.text_input("Enter Template Query ID")
variable_name = st.text_input("Enter Variable Name")



#DASHBOARD SELECTOR : 
st.header("Dashboard Card Selector")
#get dashboars from the metabase connector
dashboards = dbconn.fetch('dw_509', 'metabase', 'dashboards')
# Extract dashboard names and IDs for dropdown
dashboard_names = [dashboard["name"] for dashboard in dashboards]
dashboard_ids = {dashboard["name"]: dashboard["id"] for dashboard in dashboards}
# Dropdown to select a dashboard
selected_dashboard_name = st.selectbox("Select a Dashboard", options=dashboard_names)

# Get the selected dashboard ID
selected_dashboard_id = dashboard_ids[selected_dashboard_name]


## GET CARDS 
dashboard = {
    'id': selected_dashboard_id,
}
data = metabase_api.get('dashboard', dashboard)


cards = data['dashcards']


card_options = [{'name': card['card']['name'], 'id': card['card']['id']} 
                    for card in cards 
                    if card['card'].get('query_type') == "native"]

# Dictionary to store selected card IDs
selected_card_ids = []
if card_options:
    st.write("Select the cards you want:")
    
    # Iterate over card options and create checkboxes
    for card in card_options:
        is_checked = st.checkbox(f"{card['name']} (ID: {card['id']})", value=True)
        if is_checked:
            selected_card_ids.append(card['id'])

    # Display selected card IDs
    #st.write("Selected Card IDs:", selected_card_ids)

    # Display selected card details
    selected_cards_details = [card for card in card_options if card['id'] in selected_card_ids]
    #st.write("Selected Card Details:", selected_cards_details)
else:
    st.write("No cards available for the selected dashboard.")
    exit()



## UPDATE CARDS

# Function to copy a variable parameter from template_query to new_query
def add_variable(template_query, new_query, variable):
    # Extract 'rate_targetgroup' from existing query
    template_tag = template_query['dataset_query']['native']['template-tags'].get(variable)
    parameter = next(
        (param for param in template_query['parameters'] if param['slug'] == variable), None
    )
    
    # Check if the parameter exists
    if not template_tag or not parameter:
        print("rate_targetgroup parameter not found in existing query.")
        return
    
    # 1. Add 'rate_targetgroup' to 'template-tags' in new query
    new_query['dataset_query']['native']['template-tags'][variable] = template_tag
    
    # 2. Add 'rate_targetgroup' to 'parameters' list in new query
    new_query['parameters'].append(parameter)
    
    # 3. Update the SQL query in the new query to include 'rate_targetgroup' in the WHERE clause
    replace_input = "WHERE"
    replace_output = f"WHERE {{{{{variable}}}}} AND "
    new_query['dataset_query']['native']['query'] = new_query['dataset_query']['native']['query'].replace(
        replace_input, replace_output
    )


    return new_query

if st.button("Upate queries"): 
    for card in selected_cards_details:
        st.subheader(f"updating card {card['name']} with id {card['id']}")
        if card['id'] == int(template_query_id):
            st.write("we skip this one as its the template query")
            continue 

        
        template_query = metabase_api.get('query', {'id': template_query_id,})

        # New query data (new_query)
        new_query =  metabase_api.get('query', {'id': card['id'],})    
        # Use the function to update new_query with the 'rate_targetgroup' parameter
        updated_new_query = add_variable(template_query, new_query,variable_name )

        result = metabase_api.update('query', updated_new_query)
        st.json(result, expanded=False)