import time
import gspread
from google.oauth2.service_account import Credentials
import pandas as pd
import streamlit as st

SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"]
sheet_id = "10nGgqXxunGXo_GI1LxybvsAr1TYSDdNiqqZX6DSTbDA"
key_path = "service_account_credentials.json"

headers = ['Catalog', 'Mapping status', 'Priority', 'Program kinds', 'Customers', 'Size', 'Size Aprox', 'Needed by', 'Recommendations',	'Scraping?','Custom provider deeplinks', "Scraping link"]


def load_gsheet(tab_name: str) -> pd.DataFrame:
    creds = Credentials.from_service_account_file(key_path, scopes=SCOPES)
    client = gspread.authorize(creds)
    w = client.open_by_key(sheet_id)

    for attempt in range(3):  # retry loop
        try:
            ws = w.worksheet(tab_name)
            if tab_name == "Catalog Status":
                df = pd.DataFrame(ws.get_all_records(expected_headers=headers))
            else:
                df= pd.DataFrame(ws.get_all_records())
            return df
        except gspread.exceptions.APIError as e:
            if attempt < 2:
                st.warning(f"Retrying Google API for {tab_name}... ({attempt+1}/3)")
                time.sleep(2)  # avoid hammering API
            else:
                st.error(f"Failed to load '{tab_name}': {e}")
                raise e
            
def get_data():
    onboarding = load_gsheet("Catalog Onboarding")
    time.sleep(1)
    metadata = load_gsheet("NEW Catalog Data levels")
    time.sleep(1)
    mapping = load_gsheet("Catalog Status")
    return onboarding, metadata, mapping