Blame for app.py

# =========================================================
# 🚩 1. Install dependencies
# =========================================================


# =========================================================
# 🚩 2. Configuration
#=========================================================
ONEDRIVE_LINK = "https://onedrive.live.com/personal/c6536ba97412fbf8/_layouts/15/Doc.aspx?sourcedoc=%7Bae455bc0-7a7d-42a1-b346-22cb5e9d3753%7D&action=default&redeem=aHR0cHM6Ly8xZHJ2Lm1zL3gvYy9jNjUzNmJhOTc0MTJmYmY4L0VjQmJSYTU5ZXFGQ3MwWWl5MTZkTjFNQlBEb1hPV1VpbUNrTzlvQ1BEYURNZmc_ZT1YOHdPSTY&slrid=57d6e86a-2f3f-4dc8-9633-1eefdc5cc720&originalPath=aHR0cHM6Ly8xZHJ2Lm1zL3gvYy9jNjUzNmJhOTc0MTJmYmY4L0VjQmJSYTU5ZXFGQ3MwWWl5MTZkTjFNQlBEb1hPV1VpbUNrTzlvQ1BEYURNZmc_cnRpbWU9VjUweUdWLVYzVWc&CID=08b8850a-8ded-4b00-8e7a-5fae895488ef&_SRM=0:G:247"
OPENAI_API_KEY = "sk-proj-EQTDxpzroHcMO5uqytSmoM3VDBxC9OiRS9heyFSzG90fvKK1ffcFCrAbhXQm12NhYxIiOIjDdyQT3BlbkFJ5Xz2cTGDOtgACBFeqNKoWPgEj_9E-0cmE0oDN61yBgciBFF7td_rqlIdbozVJhYnksY5FHoNoA"

# =========================================================
# 🚩 3. Helpers
# =========================================================
import pandas as pd, requests, openai
from io import BytesIO
import os # Import the os module to save the file
import zipfile # Import zipfile to check if the content is a zip file

def to_direct_download(url: str) -> str:
    """Convert OneDrive view/redir link to direct download link (.xlsx)."""
    # This conversion might not be universally reliable for all OneDrive link types.
    # It works for common "?e=XXXXX" share links.
    return (
        url.replace("view.aspx?", "download.aspx?")
           .replace("redir?", "download?")
    )

def load_excel(url: str) -> pd.DataFrame:
    dl_url = to_direct_download(url)
    print(f"Attempting to download from: {dl_url}") # Print the direct download URL

    temp_file_path = "downloaded_content.bin" # Define temp file path outside try/except

    try:
        resp = requests.get(dl_url)
        resp.raise_for_status()                 # ⬅️ will fail if link is wrong (e.g., 404)

        # --- Added extensive diagnostic code ---
        content_bytes = BytesIO(resp.content)
        content_bytes.seek(0) # Reset stream position after creating BytesIO

        # Check the content type
        content_type = resp.headers.get('Content-Type', '').lower()
        print(f"Content-Type received: {content_type}")

        # Save the content to a file for inspection
        with open(temp_file_path, "wb") as f:
            f.write(resp.content)
        print(f"Content saved to {temp_file_path} for inspection.")
        print("Please check this file. It should be a valid .xlsx file.")

        # Additional checks for common issues
        if 'html' in content_type:
            print("Error: Content-Type is HTML. The link likely returned a web page instead of the file.")
            print("Inspect 'downloaded_content.bin' to see the HTML content.")
            raise ValueError("Downloaded content is HTML, expected Excel file.")

        # Check if the downloaded content is a zip file (Excel files are zip archives)
        # A more robust check is to see if it's a zip *and* contains typical Excel files
        try:
            # Use BytesIO to treat the content as a file for zipfile check
            with zipfile.ZipFile(content_bytes, 'r') as zf:
                zip_contents = zf.namelist()
                print(f"Content appears to be a zip file with contents: {zip_contents}")
                # Check for typical Excel structure files
                if '[Content_Types].xml' not in zip_contents or '_rels/.rels' not in zip_contents:
                     print("Warning: Zip file does not contain expected Excel structure files.")
                     # You might consider raising an error here if these are missing

                # Reset stream position before passing to pandas
                content_bytes.seek(0)

        except zipfile.BadZipFile:
            print("Error: Downloaded content is NOT a valid zip file. This is the most likely cause of the BadZipFile error.")
            # Re-raise the BadZipFile error with a more helpful message
            raise zipfile.BadZipFile("Downloaded content is not a valid zip file, expected an Excel (.xlsx) file.") from None
        except Exception as e:
             print(f"An unexpected error occurred while checking if the content is a zip file: {e}")
             # Still try to read as Excel, but note the potential issue


        # Attempt to read the excel file from the BytesIO object
        print("Attempting to read content as Excel...")
        # Pass the BytesIO object directly
        df = pd.read_excel(content_bytes, engine="openpyxl")

        # If successful, clean up the temp file
        if os.path.exists(temp_file_path):
             os.remove(temp_file_path)
             print(f"Cleaned up temporary file: {temp_file_path}")

        return df

    except requests.exceptions.RequestException as e:
        print(f"Error during file download: {e}")
        print("Please check the ONEDRIVE_LINK and your internet connection.")
        # Clean up temp file on download error
        if os.path.exists(temp_file_path):
             os.remove(temp_file_path)
             print(f"Cleaned up temporary file: {temp_file_path}")
        raise e # Re-raise the exception to see the full traceback

    except Exception as e:
        # Catch any other exceptions during read_excel or zipfile checks
        print(f"Error reading or processing downloaded file: {e}")
        print("This often happens if the downloaded content is not a readable Excel file even if it was a zip.")
        print(f"Inspect the file '{temp_file_path}' to understand what was downloaded.")
        # Do not clean up temp file on processing error, keep it for inspection
        raise e # Re-raise the exception after providing diagnostic info


openai.api_key = OPENAI_API_KEY

def ask_gpt(df: pd.DataFrame, question: str) -> str:
    context = df.to_string(index=False)
    prompt  = f"Use only this table to answer.\n\n{context}\n\nQuestion: {question}"
    resp = openai.ChatCompletion.create(
        model="gpt-3.5-turbo",  # or gpt-4 / gpt-4o
        messages=[{"role": "user", "content": prompt}]
    )
    return resp.choices[0].message.content.strip()

# =========================================================
# 🚩 4. Load Excel
# =========================================================
# Note: This will still fail if the file is not a valid Excel file,
# but the added diagnostics will provide more information about *why*.
try:
    df = load_excel(ONEDRIVE_LINK)
    print("✅ Excel loaded! Preview:")
    display(df.head())

    # =========================================================
    # 🚩 5. Ask a question
    # =========================================================
    question = "Replace this with your question"
    print("\nQ:", question)
    print("A:", ask_gpt(df, question))

except Exception as e:
    print(f"\nExecution stopped due to error loading Excel: {e}")
    # You might want to add error handling or alternative logic here
    # if the Excel loading fails.
    pass # Or handle the error gracefully