app.py

import streamlit as st
import pandas as pd
import psycopg2
import os

# Load DB credentials from Hugging Face secrets or environment variables
DB_HOST = os.getenv("DB_HOST")
DB_PORT = os.getenv("DB_PORT", "5432")
DB_NAME = os.getenv("DB_NAME")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")

@st.cache_data(ttl=600)
def get_data():
    try:
        conn = psycopg2.connect(
            host=DB_HOST,
            port=DB_PORT,
            dbname=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD,
            sslmode="require"

        )
        query = "SELECT country, year, section, question_code, question_text, answer_code, answer_text FROM survey_info;"
        df = pd.read_sql_query(query, conn)
        conn.close()
        return df
    except Exception as e:
        st.error(f"Failed to connect to the database: {e}")
        st.stop()

# Load data
df = get_data()

# Streamlit UI
st.title("🌍 CGD Survey Explorer (Live DB)")

st.sidebar.header("🔎 Filter Questions")

# COUNTRY filter with "All" toggle
all_countries = sorted(df["country"].dropna().unique())
use_all_countries = st.sidebar.checkbox("Include All Countries", value=True)

if use_all_countries:
    selected_countries = all_countries
else:
    selected_countries = st.sidebar.multiselect("Select Countries", all_countries)

# YEAR filter with "All" toggle
all_years = sorted(df["year"].dropna().unique())
use_all_years = st.sidebar.checkbox("Include All Years", value=True)

if use_all_years:
    selected_years = all_years
else:
    selected_years = st.sidebar.multiselect("Select Years", all_years)

# Free-text keyword filter
keyword = st.sidebar.text_input("Keyword Search (in question)", "")

# Column selection
all_columns = df.columns.tolist()
default_columns = ["country", "year", "question_text", "answer_text"]
selected_columns = st.sidebar.multiselect("Columns to Display", all_columns, default=default_columns)