app1.py

import streamlit as st
import gspread
from google.oauth2.service_account import Credentials
import pandas as pd
import plotly.express as px
import time
import os
import json

# Page config
st.set_page_config(page_title="FYI Migration Progress", layout="wide")

# Title and Refresh button
col_title, col_refresh = st.columns([5, 1])
with col_title:
    st.title("📊 FYI Migration Progress")
with col_refresh:
    st.write("")  # Spacer for alignment
    if st.button("🔄 Refresh"):
        st.cache_data.clear()
        st.rerun()

# Meta refresh tag to reload page every 5 minutes
st.markdown(
    """
    <meta http-equiv="refresh" content="300">
    """,
    unsafe_allow_html=True
)


def get_creds():
    raw = os.getenv("GCP_SERVICE_ACCOUNT")
    scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly']
    if raw is None:
        raise ValueError("Missing GCP_CREDENTIALS environment variable.")
    return Credentials.from_service_account_info(json.loads(raw), scopes=scopes)


# Google Sheets connection
@st.cache_data(ttl=300)  # Cache for 5 minutes (same as refresh interval)
def load_data():
    creds = get_creds()
    client = gspread.authorize(creds)
    
    sheet_id = '1J4YQEf8WvdlS3BjIOya0jsrPHolBEVodSAmhYF9XtSk'
    workbook = client.open_by_key(sheet_id)
    ws = workbook.worksheet("Customer Dashboard")
    
    # Get values from the sheet
    vals1 = ws.get("A5:B6")
    vals2 = ws.get("A20:B21")   
    vals3 = ws.get("A7:B8")   
    vals4 = ws.get("A22:B23") 
    
    # Create DataFrames
    cogeco_ingest_df = pd.DataFrame(vals1, columns=['Label', 'Count'])
    cogeco_regular_df = pd.DataFrame(vals3, columns=['Label', 'Count'])
    canada_ingest_df = pd.DataFrame(vals2, columns=['Label', 'Count'])
    canada_regular_df = pd.DataFrame(vals4, columns=['Label', 'Count'])
    
    return cogeco_ingest_df, cogeco_regular_df, canada_ingest_df, canada_regular_df

# Display last refresh time
st.caption(f"Last loaded: {time.strftime('%Y-%m-%d %H:%M:%S')}")

# Load data
cogeco_ingest_df, cogeco_regular_df, canada_ingest_df, canada_regular_df = load_data()

# Create explicit color mapping
color_map = {
    'Cogeco Missing Ingest': '#ea4335',
    'Cogeco Ready in Ingest': '#4285f4',
    'Canada Missing in Ingest': '#ea4335',
    'Canada Ready in Ingest': '#4285f4',
    'Cogeco Missing Regular': '#ea4335',
    'Cogeco Regular': '#4285f4',
    'Canada Missing Regular': '#ea4335',
    'Canada Regular': '#4285f4'
}

# Create 2x2 grid for pie charts
col1, col2 = st.columns(2)

with col1:
    # Cogeco Ingest
    fig1 = px.pie(cogeco_ingest_df, values='Count', names='Label', 
                  title='Cogeco Ingest Status', color='Label', color_discrete_map=color_map)
    st.plotly_chart(fig1, use_container_width=True)
    
    # Cogeco Regular
    fig3 = px.pie(cogeco_regular_df, values='Count', names='Label', 
                  title='Cogeco Regular Status', color='Label', color_discrete_map=color_map)
    st.plotly_chart(fig3, use_container_width=True)

with col2:
    # Canada Ingest
    fig2 = px.pie(canada_ingest_df, values='Count', names='Label', 
                  title='Canada Ingest Status', color='Label', color_discrete_map=color_map)
    st.plotly_chart(fig2, use_container_width=True)
    
    # Canada Regular
    fig4 = px.pie(canada_regular_df, values='Count', names='Label', 
                  title='Canada Regular Status', color='Label', color_discrete_map=color_map)
    st.plotly_chart(fig4, use_container_width=True)