Spaces:

Raykarr
/

Excel_Analysis

Running

File size: 56,675 Bytes

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Carver Excel Analyst</title>
    
    <link rel="preconnect" href="https://fonts.googleapis.com">
    <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
    <link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600&display=swap" rel="stylesheet">
    
    <script type="module" src="https://esm.run/@google/generative-ai"></script>
    <script src="https://cdn.jsdelivr.net/npm/marked/marked.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
    
    <style>
        /* --- CSS Reset & Base --- */
        :root {
            --font-main: 'Inter', -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, Helvetica, Arial, sans-serif;
            --color-bg: #f8f9fa;
            --color-card: #ffffff;
            --color-border: #e9ecef;
            --color-text: #212529;
            --color-text-light: #6c757d;
            --color-accent: #0d6efd;
            --color-user-msg: #0d6efd;
            --color-user-text: #ffffff;
            --color-assist-msg: #f1f3f5;
            --color-assist-text: #212529;
            --color-danger: #d90429;
            --color-danger-bg: #fff0f0;
            --color-success: #28a745;
            --color-success-bg: #f0fff4;
        }

        * {
            box-sizing: border-box;
            margin: 0;
            padding: 0;
        }

        body {
            font-family: var(--font-main);
            background-color: var(--color-bg);
            color: var(--color-text);
            display: flex;
            justify-content: center;
            align-items: flex-start;
            min-height: 100vh;
            padding: 20px;
        }

        /* --- Main App Container --- */
        .container {
            width: 100%;
            max-width: 1500 px;
            background-color: var(--color-card);
            border-radius: 12px;
            box-shadow: 0 10px 40px rgba(0, 0, 0, 0.05);
            overflow: hidden;
            display: flex;
            flex-direction: column;
            height: calc(100vh - 40px);
            border: 1px solid var(--color-border);
        }

        /* --- Header --- */
        header {
            padding: 20px 24px;
            border-bottom: 1px solid var(--color-border);
            background-color: var(--color-card);
        }

        h1 {
            margin: 0;
            color: var(--color-text);
            font-size: 1.4rem;
            font-weight: 600;
        }

        p.caption {
            margin: 4px 0 0;
            font-size: 0.9rem;
            color: var(--color-text-light);
        }

        /* --- Config Section (API Key) --- */
        .config-bar {
            padding: 16px 24px;
            background-color: #fdfdfe;
            border-bottom: 1px solid var(--color-border);
            display: flex;
            justify-content: space-between;
            align-items: center;
        }
        
        .api-key-group {
            display: flex;
            align-items: center;
            gap: 10px;
        }
        
        .api-key-group label {
            font-weight: 500;
            font-size: 0.9rem;
        }

        .api-key-group input {
            flex-grow: 1;
            padding: 8px 12px;
            border: 1px solid var(--color-border);
            border-radius: 6px;
            font-family: var(--font-main);
            font-size: 0.9rem;
        }
        
        .api-key-warning {
            font-size: 0.8rem;
            color: #e74c3c;
            font-weight: 500;
            margin-top: 8px;
        }

        /* --- Header Actions --- */
        .header-actions {
            display: flex;
            gap: 8px;
        }
        
        .action-button {
            padding: 6px 12px;
            border: 1px solid var(--color-border);
            background-color: var(--color-card);
            border-radius: 6px;
            font-size: 0.85rem;
            cursor: pointer;
            font-family: var(--font-main);
            transition: all 0.2s;
        }
        
        .action-button:hover {
            background-color: var(--color-assist-msg);
            border-color: var(--color-accent);
        }
        
        .action-button.danger {
            color: var(--color-danger);
            border-color: var(--color-danger);
        }
        
        .action-button.danger:hover {
            background-color: var(--color-danger-bg);
        }

        /* --- Chat Box --- */
        #chat-box {
            flex-grow: 1;
            padding: 24px;
            overflow-y: auto;
            display: flex;
            flex-direction: column;
            gap: 16px;
            /* Subtle dot background */
            background-image: radial-gradient(var(--color-border) 1px, transparent 1px);
            background-size: 10px 10px;
            background-color: #ffffff;
        }
        
        /* Minimal Scrollbar */
        #chat-box::-webkit-scrollbar {
            width: 6px;
        }
        #chat-box::-webkit-scrollbar-track {
            background: transparent;
        }
        #chat-box::-webkit-scrollbar-thumb {
            background: var(--color-border);
            border-radius: 3px;
        }
        #chat-box::-webkit-scrollbar-thumb:hover {
            background: #d0d5db;
        }

        .message {
            padding: 14px 20px;
            border-radius: 18px;
            max-width: 85%;
            line-height: 1.6;
            word-wrap: break-word;
            font-size: 0.95rem;
        }

        .message.user {
            background-color: var(--color-user-msg);
            color: var(--color-user-text);
            border-bottom-right-radius: 6px;
            align-self: flex-end;
        }

        .message.assistant {
            background-color: var(--color-assist-msg);
            color: var(--color-assist-text);
            border-bottom-left-radius: 6px;
            align-self: flex-start;
        }

        .message.error {
            background-color: var(--color-danger-bg);
            color: var(--color-danger);
            border: 1px solid var(--color-danger);
        }

        .message.success {
            background-color: var(--color-success-bg);
            color: var(--color-success);
            border: 1px solid var(--color-success);
        }

        /* --- Markdown Formatting for Assistant --- */
        .message.assistant strong { font-weight: 600; }
        .message.assistant h1, .message.assistant h2, .message.assistant h3 {
            margin-top: 1.2em;
            margin-bottom: 0.6em;
            border-bottom: 1px solid var(--color-border);
            padding-bottom: 5px;
            font-weight: 600;
        }
        .message.assistant code {
            font-family: monospace;
            background-color: #dfe7ed;
            padding: 2px 6px;
            border-radius: 4px;
            font-size: 0.9em;
        }
        .message.assistant pre {
            background-color: #2c3e50;
            color: #f4f7f6;
            padding: 16px;
            border-radius: 8px;
            overflow-x: auto;
            margin: 1em 0;
        }
        .message.assistant pre code {
            background-color: transparent;
            padding: 0;
            font-size: 0.85rem;
        }
        .message.assistant table {
            border-collapse: collapse;
            width: 100%;
            margin: 1em 0;
            font-size: 0.9rem;
        }
        .message.assistant th, .message.assistant td {
            border: 1px solid var(--color-border);
            padding: 10px;
            text-align: left;
        }
        .message.assistant th {
            background-color: var(--color-assist-msg);
            font-weight: 600;
        }

        /* --- Input Area --- */
        .input-area {
            border-top: 1px solid var(--color-border);
            padding: 16px 24px;
            background-color: var(--color-card);
        }
        
        #file-list {
            display: flex;
            flex-wrap: wrap;
            gap: 8px;
            margin-bottom: 12px;
            align-items: center;
        }
        
        .file-pill {
            display: inline-flex;
            align-items: center;
            gap: 6px;
            background-color: var(--color-assist-msg);
            color: var(--color-text);
            padding: 5px 12px;
            border-radius: 15px;
            font-size: 0.8rem;
            font-weight: 500;
        }
        
        .file-remove {
            background: none;
            border: none;
            color: var(--color-danger);
            cursor: pointer;
            font-size: 0.7rem;
            padding: 0;
            width: 16px;
            height: 16px;
            border-radius: 50%;
            display: flex;
            align-items: center;
            justify-content: center;
        }
        
        .file-remove:hover {
            background-color: var(--color-danger);
            color: white;
        }

        .prompt-input-group {
            display: flex;
            align-items: center;
            gap: 10px;
            border: 1px solid var(--color-border);
            border-radius: 25px;
            padding: 4px 4px 4px 12px;
        }
        .prompt-input-group:focus-within {
            border-color: var(--color-accent);
            box-shadow: 0 0 0 3px rgba(13,110,253,0.1);
        }

        #prompt-input {
            flex-grow: 1;
            border: none;
            outline: none;
            background: transparent;
            font-family: var(--font-main);
            font-size: 1rem;
            padding: 8px;
        }
        
        #file-input {
            display: none;
        }
        
        .icon-button {
            background-color: transparent;
            border: none;
            cursor: pointer;
            padding: 8px;
            border-radius: 50%;
            display: flex;
            align-items: center;
            justify-content: center;
            transition: background-color 0.2s;
        }
        .icon-button svg {
            width: 20px;
            height: 20px;
            fill: var(--color-text-light);
        }
        .icon-button:hover {
            background-color: var(--color-assist-msg);
        }
        
        #send-button {
            background-color: var(--color-accent);
        }
        #send-button svg {
            fill: var(--color-user-text);
        }
        #send-button:hover {
            background-color: #0b5ed7;
        }
        #send-button:disabled {
            background-color: #a0c7e4;
            cursor: not-allowed;
        }

        /* --- Status & Loading --- */
        #status-text {
            display: block;
            text-align: center;
            font-size: 0.85rem;
            color: var(--color-text-light);
            margin-bottom: 12px;
            height: 1.2em;
        }
        
        .spinner {
            display: inline-block;
            width: 1em;
            height: 1em;
            border: 2px solid currentColor;
            border-right-color: transparent;
            border-radius: 50%;
            animation: spin 0.6s linear infinite;
            margin-bottom: -3px;
            margin-right: 5px;
        }
        @keyframes spin {
            to { transform: rotate(360deg); }
        }

        /* --- Debug/Refined Prompt Area --- */
        .debug-container {
            padding: 12px 24px 4px;
            border-top: 1px solid var(--color-border);
            background-color: #f8f9fa;
        }
        
        .debug-container summary {
            font-size: 0.8rem;
            color: var(--color-text-light);
            cursor: pointer;
            font-weight: 500;
            padding: 8px 0;
            list-style: none;
        }
        
        .debug-container summary::-webkit-details-marker {
            display: none;
        }
        
        .debug-container summary::before {
            content: '▶';
            display: inline-block;
            margin-right: 8px;
            transition: transform 0.2s;
        }
        
        .debug-container[open] summary::before {
            transform: rotate(90deg);
        }
        
        #debug-box {
            display: none; /* Hidden by default */
            font-size: 0.8rem;
            background-color: var(--color-card);
            border: 1px solid var(--color-border);
            padding: 12px;
            margin: 8px 0 0 0;
            border-radius: 8px;
            font-family: monospace;
            line-height: 1.4;
            max-height: 200px;
            overflow-y: auto;
            white-space: pre-wrap;
            word-wrap: break-word;
        }
        
        /* Show debug box when details are open */
        .debug-container[open] #debug-box,
        .debug-box-visible {
            display: block;
        }
        
        /* Hide the native details arrow */
        .debug-container summary {
            display: none;
        }

        /* --- Conversation Info --- */
        .conversation-info {
            text-align: center;
            padding: 8px 16px;
            font-size: 0.85rem;
            color: var(--color-text-light);
            background-color: var(--color-success-bg);
            border-bottom: 1px solid var(--color-border);
        }

    </style>
</head>
<body>

    <div class="container">
        <header>
            <h1>🧠 Carver Excel Analyst</h1>
            <p class="caption">This AI uses a 2-step process to refine your query and provide an expert analysis. <b>Upload files once, ask multiple questions!</b></p>
        </header>

        <div class="config-bar">
            <div class="api-key-group">
                <label for="api-key-input">API Key:</label>
                <input type="password" id="api-key-input" placeholder="Enter your Google API Key">
            </div>
            <div class="header-actions">
                <button class="action-button success" id="export-pdf-btn" title="Export conversation to PDF">
                    📄 Export PDF
                </button>
                <button class="action-button" id="toggle-details-btn" title="Show/Hide analysis details">
                    👁️ Show Analysis Details
                </button>
                <button class="action-button" id="new-conversation-btn" title="Start a new conversation">
                    🔄 New Conversation
                </button>
            </div>
        </div>

        <div class="conversation-info" id="conversation-info" style="display: none;">
            💾 Files remain available for this conversation. Ask follow-up questions anytime!
        </div>

        <div id="chat-box">
            <div class="message assistant">
                Welcome to Carver Excel Analyst! This is a <b>conversational chatbot</b> that remembers your files and conversation.
                <br><br>
                <b>How it works:</b>
                <br>• Upload your Excel, PDF, or image files once
                <br>• Ask questions about the data
                <br>• Files stay available for follow-up questions
                <br>• Start a new conversation anytime to clear history
                <br><br>
                <b>Please provide your API key and upload your files to begin!</b>
            </div>
        </div>
        
        <div class="input-area">
            <div id="status-text"></div>
            
            <div id="file-list">
                </div>
            
            <div class="prompt-input-group">
                <input type="file" id="file-input" multiple>
                <button class="icon-button" id="attach-button" title="Upload files">
                    <svg viewBox="0 0 24 24"><path d="M16.5 6v11.5c0 2.21-1.79 4-4 4s-4-1.79-4-4V5c0-1.38 1.12-2.5 2.5-2.5s2.5 1.12 2.5 2.5v10.5c0 .55-.45 1-1 1s-1-.45-1-1V6H10v9.5c0 1.38 1.12 2.5 2.5 2.5s2.5-1.12 2.5-2.5V5c0-2.21-1.79-4-4-4S7 2.79 7 5v11.5c0 2.76 2.24 5 5 5s5-2.24 5-5V6h-1.5z"></path></svg>
                </button>
                
                <input type="text" id="prompt-input" placeholder="Ask a question about your files...">
                
                <button class="icon-button" id="send-button" title="Send message">
                    <svg viewBox="0 0 24 24"><path d="M2.01 21L23 12 2.01 3 2 10l15 2-15 2 .01 7z"></path></svg>
                </button>
            </div>
            
            <div class="debug-container" id="debug-container">
                <div id="debug-box">No analysis details yet.</div>
            </div>
        </div>
    </div>

    <script type="module">
        // --- Import Generative AI SDK ---
        const { GoogleGenerativeAI } = await import("https://esm.run/@google/generative-ai");

        // --- DOM Element References ---
        const apiKeyInput = document.getElementById("api-key-input");
        const fileInput = document.getElementById("file-input");
        const attachButton = document.getElementById("attach-button");
        const fileListDisplay = document.getElementById("file-list");
        const promptInput = document.getElementById("prompt-input");
        const sendButton = document.getElementById("send-button");
        const chatBox = document.getElementById("chat-box");
        const statusText = document.getElementById("status-text");
        const debugBox = document.getElementById("debug-box");
        const newConversationBtn = document.getElementById("new-conversation-btn");
        const conversationInfo = document.getElementById("conversation-info");
        const exportPdfBtn = document.getElementById("export-pdf-btn");
        const toggleDetailsBtn = document.getElementById("toggle-details-btn");

        // --- Model Configuration ---
        const GENERATOR_MODEL_NAME = "gemini-2.0-flash";
        const ANALYST_MODEL_NAME = "gemini-2.5-flash";
        
        // --- Global State ---
        let uploadedFiles = []; // Persistent file storage
        let conversationHistory = []; // Store conversation messages
        let conversationStartTime = Date.now();
        let analysisDetailsVisible = false;

        // --- The Main Carver Analyst System Prompt ---
        const CARVER_ANALYST_SYSTEM_PROMPT = `
You are **Carver Excel Analyst**, an internal AI specialist for Carver Procurement Consultancy.
Your role: Help Carver team members analyze Excel sheets (e.g., BOQs, comparative sheets, quotations, price lists, tender data) and produce clear summaries and structured tables.
NOTE: Excel files (.xlsx, .xls) have been pre-converted into structured CSV text, with each sheet as a separate text block, prefixed with [File: filename.xlsx | Sheet: SheetName]. Use this text data for your analysis.
### 🎯 Mission
Understand the provided data (CSV text, PDFs, images) and provide short, factual insights — **without making any assumptions**.  
If any required input is missing, unclear, or conflicting, you **must first ask a short, specific clarifying question** before proceeding. Never guess, invent, or assume.
Your goal is to make Carver's data analysis simple, reliable, and audit-ready.
### 🧩 Default Output (when all inputs are clear)
1. **Executive Summary (3–6 lines)** — concise explanation answering the user's question.
2. **Supporting Table (Markdown or CSV)** — key rows/columns only.
3. **Audit Block (3–6 lines)** — list of: file(s) used (or file/sheet name for CSVs), cell/range references (or row/column names), missing or ignored fields, and formulas applied.
Do **not** create or export a new Excel file unless the user explicitly asks for it.
### 🧱 Working Logic
**A. File Understanding**
- Detect columns and datatypes from the CSV text or tables in PDFs/images. Map columns like *Vendor*, *Item*, *Qty*, *Rate/Unit Price*, *Total*, *Lead Time*, *Remarks*, etc.
- Always confirm mappings if confidence <80%.
**B. Required Data Rules**
- For cost analysis → need \`Qty\` + \`UnitPrice\` or \`Total\`.
- For "vs budget" → need a \`Baseline\`, \`Budget\`, or explicit reference vendor.
- If missing, ask user directly.
**C. No-Assumptions Policy**
- If anything is missing or unclear: Ask before proceeding.
- Example: "No 'Budget' column found. Should I use the lowest vendor total as baseline?"
**D. Validations**
- Detect and report missing values, currency mismatches, etc., in the Audit Block.
**E. Computation Rules**
- \`Saving = Baseline – Selected\`
- \`Saving% = (Baseline – Selected) / Baseline × 100\`
**F. Output Formatting**
- Keep summary short. Use markdown tables. End every result with an **Audit Block**.
**H. Tone**
- Professional, precise, procurement-focused.
### 🧭 Objective
Act like Carver's in-house **Excel procurement analyst** — structured, audit-traceable, and transparent. Never make assumptions. Always verify.

IMPORTANT: This is a conversational chatbot. You have access to the conversation history and can refer back to previous analyses when answering follow-up questions.
`;

        // --- Event Listeners ---
        sendButton.addEventListener("click", handleSend);
        promptInput.addEventListener("keydown", (e) => {
            if (e.key === "Enter" && !e.shiftKey) {
                e.preventDefault();
                handleSend();
            }
        });
        
        // Trigger hidden file input
        attachButton.addEventListener("click", () => {
            fileInput.click();
        });
        
        // Handle new conversation
        newConversationBtn.addEventListener("click", startNewConversation);
        
        // Handle PDF export
        exportPdfBtn.addEventListener("click", exportToPDF);
        
        // Handle analysis details toggle
        toggleDetailsBtn.addEventListener("click", toggleAnalysisDetails);
        
        // Update file pills when files are selected
        fileInput.addEventListener("change", (e) => {
            const newFiles = Array.from(e.target.files);
            uploadedFiles = [...uploadedFiles, ...newFiles]; // Append, don't replace
            updateFileDisplay();
            fileInput.value = ""; // Reset input
            
            if (uploadedFiles.length > 0) {
                showSuccess(`📎 Added ${newFiles.length} file(s). Total files: ${uploadedFiles.length}`);
                conversationInfo.style.display = "block";
            }
        });

        // --- Main Function: handleSend ---
        async function handleSend() {
            const apiKey = apiKeyInput.value.trim();
            const userPrompt = promptInput.value.trim();

            // --- 1. Validations ---
            if (!apiKey) {
                displayError("Please enter your Google API Key.");
                return;
            }
            if (!userPrompt) {
                displayError("Please enter a prompt.");
                return;
            }
            if (uploadedFiles.length === 0) {
                displayError("Please upload at least one file first.");
                return;
            }

            // --- 2. Setup UI for Loading ---
            setLoadingState(true, "Processing your question...");
            displayMessage(userPrompt, "user");
            debugBox.textContent = "Generating refined prompt..."; // Clear old debug info
            
            const assistantMessageEl = displayMessage("", "assistant"); // Create empty bubble

            try {
                // --- 3. Process Files into Generative Parts ---
                const partsArrays = await Promise.all(
                    uploadedFiles.map(fileToGenerativeParts)
                );
                const fileParts = partsArrays.flat();

                // --- 4. Build conversation context ---
                let conversationContext = "";
                if (conversationHistory.length > 0) {
                    conversationContext = `
Previous conversation context:
${conversationHistory.slice(-6).map(msg => 
  `${msg.role.toUpperCase()}: ${msg.content.substring(0, 200)}${msg.content.length > 200 ? '...' : ''}`
).join('\n')}

`;
                }

                // --- 5. Initialize AI Client ---
                const genAI = new GoogleGenerativeAI(apiKey);

                // --- 6. STEP 1: Generate the "Better Prompt" (Normal Model) ---
                setLoadingState(true, `Step 1/2: Thinking about your question... (${GENERATOR_MODEL_NAME})`);
                
                const generatorModel = genAI.getGenerativeModel({ model: GENERATOR_MODEL_NAME });
                
                const step1SystemPrompt = `
You are a prompt engineering assistant. Your job is to take a user's simple question and the attached file(s) and generate a new, detailed, and specific prompt. This new prompt will be given to a second AI, which is an expert 'Carver Excel Analyst'.
NOTE: The attached files may include images, PDFs, or TEXT parts that are CSV conversions of Excel sheets. The analyst is aware of this.

The user's question is: "${userPrompt}"

${conversationContext}Based on the user's question and the file(s), generate a single, clear, and actionable instruction for the expert analyst.
- Be specific.
- Ask for the final output (summary, table, audit block) as defined by the analyst's role.
- Consider previous conversation context when generating the prompt.
- For example, if the user says "how much did I save?", you should generate a prompt like:
  "Please analyze the attached data (CSV text, PDFs, images). Identify the baseline or budget, compare all vendor totals against it, and calculate the saving amount and percentage for the lowest-cost compliant vendor. Present this in an executive summary, a comparison table, and an audit block."
`;
                
                const step1Contents = [
                    ...fileParts, 
                    { text: step1SystemPrompt }
                ];

                const resultStep1 = await generatorModel.generateContent({ 
                    contents: [{ parts: step1Contents }]
                });
                const generatedPrompt = resultStep1.response.text();
                
                // Display the refined prompt in the debug box
                debugBox.textContent = generatedPrompt;
                analysisDetailsVisible = true;
                updateDetailsVisibility();

                // --- 7. STEP 2: Get Final Answer (Reasoning Model) ---
                setLoadingState(true, `Step 2/2: The Carver Analyst is working... (${ANALYST_MODEL_NAME})`);

                const analystModel = genAI.getGenerativeModel({
                    model: ANALYST_MODEL_NAME,
                    systemInstruction: CARVER_ANALYST_SYSTEM_PROMPT
                });

                const step2Contents = [
                    ...fileParts,
                    { text: generatedPrompt }
                ];
                
                const result = await analystModel.generateContentStream({ 
                    contents: [{ parts: step2Contents }] 
                });

                // --- 8. Stream Response to Chat ---
                let fullResponse = "";
                for await (const chunk of result.stream) {
                    if (typeof chunk.text === 'function') {
                        const chunkText = chunk.text();
                        fullResponse += chunkText;
                        assistantMessageEl.innerHTML = marked.parse(fullResponse);
                        scrollToBottom();
                    }
                }

                // --- 9. Save to conversation history ---
                conversationHistory.push(
                    { role: "user", content: userPrompt },
                    { role: "assistant", content: fullResponse }
                );

                // Keep only last 20 messages to prevent context overflow
                if (conversationHistory.length > 20) {
                    conversationHistory = conversationHistory.slice(-20);
                }

            } catch (error) {
                console.error("Error:", error);
                displayError(`An error occurred: ${error.message}`);
                assistantMessageEl.remove(); // Remove the empty bubble
                analysisDetailsVisible = false;
                updateDetailsVisibility();
            } finally {
                // --- 10. Cleanup UI ---
                setLoadingState(false);
                promptInput.value = "";
            }
        }

        // --- Helper Functions ---

        /**
         * Start a new conversation - clears history and files
         */
        function startNewConversation() {
            if (confirm("Start a new conversation? This will clear all uploaded files and conversation history.")) {
                uploadedFiles = [];
                conversationHistory = [];
                conversationStartTime = Date.now();
                
                // Clear UI
                chatBox.innerHTML = `
                    <div class="message assistant">
                        🔄 <b>New conversation started!</b> Upload your files and ask questions about them.
                    </div>
                `;
                
                updateFileDisplay();
                conversationInfo.style.display = "none";
                promptInput.value = "";
                
                showSuccess("Started new conversation!");
            }
        }

        /**
         * Update file display pills
         */
        function updateFileDisplay() {
            fileListDisplay.innerHTML = "";
            
            if (uploadedFiles.length > 0) {
                uploadedFiles.forEach((file, index) => {
                    const pill = document.createElement("span");
                    pill.className = "file-pill";
                    pill.innerHTML = `
                        ${file.name}
                        <button class="file-remove" onclick="removeFile(${index})" title="Remove file">×</button>
                    `;
                    fileListDisplay.appendChild(pill);
                });
            }
        }

        /**
         * Remove a specific file
         */
        window.removeFile = function(index) {
            if (confirm(`Remove "${uploadedFiles[index].name}"?`)) {
                uploadedFiles.splice(index, 1);
                updateFileDisplay();
                
                if (uploadedFiles.length === 0) {
                    conversationInfo.style.display = "none";
                    showSuccess("All files removed");
                } else {
                    showSuccess(`Removed file. ${uploadedFiles.length} file(s) remaining`);
                }
            }
        };

        /**
         * Converts a File object to an array of GoogleGenerativeAI.Part objects.
         */
        async function fileToGenerativeParts(file) {
            const excelMimeTypes = [
                'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', // .xlsx
                'application/vnd.ms-excel' // .xls
            ];

            if (excelMimeTypes.includes(file.type) || file.name.endsWith('.xlsx') || file.name.endsWith('.xls')) {
                try {
                    const buffer = await file.arrayBuffer();
                    const workbook = XLSX.read(buffer, { type: 'buffer' });
                    const textParts = [];
                    
                    workbook.SheetNames.forEach(sheetName => {
                        const worksheet = workbook.Sheets[sheetName];
                        const csvData = XLSX.utils.sheet_to_csv(worksheet);
                        const partHeader = `[File: ${file.name} | Sheet: ${sheetName}]\n\n`;
                        textParts.push({ text: partHeader + csvData });
                    });
                    
                    return textParts;
                } catch (error) {
                    console.error("Error reading Excel file:", error);
                    return [{ text: `[Error processing Excel file ${file.name}: ${error.message}]` }];
                }
            } 
            else { // For all other files (PDF, images, etc.)
                return new Promise((resolve, reject) => {
                    const reader = new FileReader();
                    reader.readAsDataURL(file);
                    reader.onload = () => {
                        const dataUrl = reader.result;
                        const base64Data = dataUrl.split(',')[1];
                        const mimeType = dataUrl.split(';')[0].split(':')[1];
                        
                        resolve([{
                            inlineData: {
                                data: base64Data,
                                mimeType: mimeType
                            }
                        }]);
                    };
                    reader.onerror = (error) => reject(error);
                });
            }
        }

        /**
         * Sets the loading state of the UI
         */
        function setLoadingState(isLoading, message = "") {
            if (isLoading) {
                statusText.innerHTML = `<span class="spinner"></span> ${message}`;
                sendButton.disabled = true;
                promptInput.disabled = true;
                attachButton.disabled = true;
            } else {
                statusText.innerHTML = "";
                sendButton.disabled = false;
                promptInput.disabled = false;
                attachButton.disabled = false;
                promptInput.focus();
            }
        }

        /**
         * Displays a message in the chat box
         */
        function displayMessage(text, role) {
            const messageEl = document.createElement("div");
            messageEl.classList.add("message", role);
            
            if (role === 'error') {
                messageEl.textContent = text;
            } else if (role === 'assistant') {
                messageEl.innerHTML = marked.parse(text || "..."); // Show loading dots
            } else {
                messageEl.textContent = text; // Plain text for user
            }
            
            chatBox.appendChild(messageEl);
            scrollToBottom();
            return messageEl;
        }

        /**
         * Displays a success message in the chat
         */
        function showSuccess(text) {
            const messageEl = document.createElement("div");
            messageEl.className = "message success";
            messageEl.textContent = text;
            
            chatBox.appendChild(messageEl);
            scrollToBottom();
            
            // Auto-remove success message after 3 seconds
            setTimeout(() => {
                if (messageEl.parentNode) {
                    messageEl.remove();
                }
            }, 3000);
        }

        /**
         * Displays an error message in the chat
         */
        function displayError(text) {
            displayMessage(text, "error");
            setLoadingState(false);
        }

        /**
         * Scrolls the chat box to the bottom
         */
        function scrollToBottom() {
            chatBox.scrollTop = chatBox.scrollHeight;
        }
        
        /**
         * Toggle analysis details visibility
         */
        function toggleAnalysisDetails() {
            analysisDetailsVisible = !analysisDetailsVisible;
            updateDetailsVisibility();
        }
        
        /**
         * Update analysis details visibility
         */
        function updateDetailsVisibility() {
            if (analysisDetailsVisible) {
                debugBox.style.display = 'block';
                toggleDetailsBtn.innerHTML = '👁️ Hide Analysis Details';
            } else {
                debugBox.style.display = 'none';
                toggleDetailsBtn.innerHTML = '👁️ Show Analysis Details';
            }
        }
        
        /**
         * Export conversation to PDF
         */
        function exportToPDF() {
            if (conversationHistory.length === 0) {
                displayError("No conversation to export.");
                return;
            }
            
            try {
                // Create HTML content for PDF
                const currentDate = new Date().toLocaleDateString();
                const timestamp = new Date().toLocaleTimeString();
                let htmlContent = `
                <!DOCTYPE html>
                <html>
                <head>
                    <meta charset="UTF-8">
                    <title>Carver Excel Analyst - Export</title>
                    <style>
                        @import url('https://fonts.googleapis.com/css2?family=Inter:wght@300;400;500;600;700&display=swap');
                        
                        * {
                            margin: 0;
                            padding: 0;
                            box-sizing: border-box;
                        }
                        
                        body {
                            font-family: 'Inter', -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Helvetica, Arial, sans-serif;
                            line-height: 1.7;
                            color: #1a202c;
                            background-color: #f7fafc;
                            font-size: 14px;
                        }
                        
                        .page {
                            background: white;
                            max-width: 900px;
                            margin: 20px auto;
                            padding: 60px;
                            box-shadow: 0 10px 40px rgba(0, 0, 0, 0.1);
                            border-radius: 8px;
                            min-height: 800px;
                        }
                        
                        /* Header Styles */
                        .header {
                            text-align: center;
                            margin-bottom: 50px;
                            padding-bottom: 30px;
                            border-bottom: 3px solid #e2e8f0;
                            position: relative;
                        }
                        
                        .header::after {
                            content: '';
                            position: absolute;
                            bottom: -3px;
                            left: 50%;
                            transform: translateX(-50%);
                            width: 80px;
                            height: 3px;
                            background: linear-gradient(135deg, #3182ce, #63b3ed);
                        }
                        
                        .header h1 {
                            color: #2d3748;
                            font-size: 2.5rem;
                            font-weight: 700;
                            margin-bottom: 10px;
                            letter-spacing: -0.025em;
                        }
                        
                        .header .subtitle {
                            color: #718096;
                            font-size: 1.1rem;
                            font-weight: 400;
                            margin-bottom: 15px;
                        }
                        
                        .header .metadata {
                            display: flex;
                            justify-content: center;
                            gap: 30px;
                            margin-top: 20px;
                            font-size: 0.9rem;
                            color: #a0aec0;
                        }
                        
                        .metadata-item {
                            display: flex;
                            align-items: center;
                            gap: 5px;
                        }
                        
                        /* Files Section */
                        .files-section {
                            background: linear-gradient(135deg, #f7fafc, #edf2f7);
                            border: 2px solid #e2e8f0;
                            border-radius: 12px;
                            padding: 25px;
                            margin-bottom: 40px;
                        }
                        
                        .files-section h2 {
                            color: #2d3748;
                            font-size: 1.4rem;
                            font-weight: 600;
                            margin-bottom: 20px;
                            display: flex;
                            align-items: center;
                            gap: 10px;
                        }
                        
                        .files-grid {
                            display: grid;
                            grid-template-columns: repeat(auto-fit, minmax(250px, 1fr));
                            gap: 15px;
                            margin-top: 15px;
                        }
                        
                        .file-card {
                            background: white;
                            padding: 15px 20px;
                            border-radius: 8px;
                            border: 1px solid #e2e8f0;
                            box-shadow: 0 2px 4px rgba(0, 0, 0, 0.05);
                            transition: all 0.2s ease;
                        }
                        
                        .file-card:hover {
                            transform: translateY(-2px);
                            box-shadow: 0 4px 12px rgba(0, 0, 0, 0.1);
                        }
                        
                        .file-name {
                            font-weight: 600;
                            color: #2d3748;
                            margin-bottom: 5px;
                        }
                        
                        .file-size {
                            color: #718096;
                            font-size: 0.85rem;
                        }
                        
                        /* Conversation Entries */
                        .conversation-section {
                            margin-bottom: 40px;
                        }
                        
                        .conversation-entry {
                            background: white;
                            border-radius: 12px;
                            margin-bottom: 30px;
                            overflow: hidden;
                            box-shadow: 0 4px 12px rgba(0, 0, 0, 0.05);
                            border: 1px solid #e2e8f0;
                        }
                        
                        .user-entry {
                            border-left: 4px solid #3182ce;
                        }
                        
                        .assistant-entry {
                            border-left: 4px solid #38a169;
                        }
                        
                        .entry-header {
                            padding: 20px 25px 15px;
                            display: flex;
                            align-items: center;
                            gap: 12px;
                            background: linear-gradient(135deg, #f7fafc, #edf2f7);
                        }
                        
                        .user-entry .entry-header {
                            background: linear-gradient(135deg, #ebf8ff, #bee3f8);
                        }
                        
                        .assistant-entry .entry-header {
                            background: linear-gradient(135deg, #f0fff4, #c6f6d5);
                        }
                        
                        .role-icon {
                            width: 32px;
                            height: 32px;
                            border-radius: 50%;
                            display: flex;
                            align-items: center;
                            justify-content: center;
                            font-weight: 600;
                            font-size: 0.8rem;
                            color: white;
                        }
                        
                        .user-entry .role-icon {
                            background: #3182ce;
                        }
                        
                        .assistant-entry .role-icon {
                            background: #38a169;
                        }
                        
                        .role-text {
                            font-weight: 600;
                            font-size: 1.1rem;
                            color: #2d3748;
                        }
                        
                        .timestamp {
                            margin-left: auto;
                            color: #718096;
                            font-size: 0.85rem;
                        }
                        
                        .entry-content {
                            padding: 25px;
                            background: white;
                        }
                        
                        /* Message Content Styling */
                        .message-content {
                            font-size: 0.95rem;
                            line-height: 1.8;
                        }
                        
                        .user-entry .message-content {
                            color: #2d3748;
                        }
                        
                        .assistant-entry .message-content {
                            color: #2d3748;
                        }
                        
                        /* Markdown Styling */
                        .message-content h1, .message-content h2, .message-content h3 {
                            color: #2d3748;
                            margin-top: 1.5em;
                            margin-bottom: 0.8em;
                            font-weight: 600;
                        }
                        
                        .message-content h1 {
                            font-size: 1.4rem;
                            border-bottom: 2px solid #e2e8f0;
                            padding-bottom: 8px;
                        }
                        
                        .message-content h2 {
                            font-size: 1.25rem;
                            color: #3182ce;
                        }
                        
                        .message-content h3 {
                            font-size: 1.1rem;
                            color: #38a169;
                        }
                        
                        .message-content strong {
                            font-weight: 600;
                            color: #2d3748;
                        }
                        
                        .message-content table {
                            width: 100%;
                            border-collapse: collapse;
                            margin: 20px 0;
                            font-size: 0.9rem;
                            box-shadow: 0 2px 8px rgba(0, 0, 0, 0.1);
                            border-radius: 8px;
                            overflow: hidden;
                        }
                        
                        .message-content th {
                            background: linear-gradient(135deg, #3182ce, #63b3ed);
                            color: white;
                            padding: 15px 12px;
                            text-align: left;
                            font-weight: 600;
                        }
                        
                        .message-content td {
                            padding: 12px;
                            border-bottom: 1px solid #e2e8f0;
                            background: white;
                        }
                        
                        .message-content tr:nth-child(even) td {
                            background: #f7fafc;
                        }
                        
                        .message-content tr:hover td {
                            background: #edf2f7;
                        }
                        
                        .message-content code {
                            background: #f7fafc;
                            padding: 3px 8px;
                            border-radius: 4px;
                            font-family: 'Monaco', 'Consolas', 'Courier New', monospace;
                            font-size: 0.85rem;
                            color: #d53f8c;
                            border: 1px solid #e2e8f0;
                        }
                        
                        .message-content pre {
                            background: #1a202c;
                            color: #f7fafc;
                            padding: 20px;
                            border-radius: 8px;
                            overflow-x: auto;
                            margin: 20px 0;
                            border: 1px solid #2d3748;
                        }
                        
                        .message-content pre code {
                            background: transparent;
                            padding: 0;
                            color: #f7fafc;
                            border: none;
                            font-size: 0.85rem;
                        }
                        
                        .message-content blockquote {
                            border-left: 4px solid #3182ce;
                            padding-left: 20px;
                            margin: 20px 0;
                            background: #f7fafc;
                            padding: 15px 20px;
                            border-radius: 0 8px 8px 0;
                            color: #4a5568;
                        }
                        
                        /* Audit Block Styling */
                        .audit-block {
                            background: linear-gradient(135deg, #fed7d7, #feb2b2);
                            border: 2px solid #fc8181;
                            border-radius: 8px;
                            padding: 20px;
                            margin-top: 25px;
                        }
                        
                        .audit-block h3 {
                            color: #c53030;
                            margin-top: 0;
                            margin-bottom: 15px;
                            font-weight: 600;
                        }
                        
                        .audit-block ul {
                            margin-left: 20px;
                        }
                        
                        .audit-block li {
                            margin-bottom: 5px;
                        }
                        
                        /* Print Styles */
                        @media print {
                            body {
                                background: white;
                            }
                            
                            .page {
                                margin: 0;
                                padding: 40px;
                                box-shadow: none;
                                border-radius: 0;
                            }
                            
                            .conversation-entry {
                                break-inside: avoid;
                                page-break-inside: avoid;
                            }
                        }
                        
                        /* Page Breaks */
                        .page-break {
                            page-break-before: always;
                        }
                    </style>
                </head>
                <body>
                    <div class="page">
                        <div class="header">
                            <h1>🧠 Carver Excel Analyst</h1>
                            <div class="subtitle">Professional Data Analysis Report</div>
                            <div class="metadata">
                                <div class="metadata-item">
                                    <span>📅</span>
                                    <span>${currentDate}</span>
                                </div>
                                <div class="metadata-item">
                                    <span>🕒</span>
                                    <span>${timestamp}</span>
                                </div>
                                <div class="metadata-item">
                                    <span>💬</span>
                                    <span>${conversationHistory.length} messages</span>
                                </div>
                            </div>
                        </div>
                        
                        <div class="files-section">
                            <h2>📎 Analyzed Files</h2>
                            <div class="files-grid">
                `;
                
                uploadedFiles.forEach(file => {
                    htmlContent += `
                        <div class="file-card">
                            <div class="file-name">${file.name}</div>
                            <div class="file-size">${(file.size / 1024).toFixed(1)} KB</div>
                        </div>
                    `;
                });
                
                htmlContent += `
                            </div>
                        </div>
                        
                        <div class="conversation-section">
                            <h2>💬 Conversation Analysis</h2>
                `;
                
                conversationHistory.forEach((message, index) => {
                    const roleClass = message.role === 'user' ? 'user-entry' : 'assistant-entry';
                    const roleIcon = message.role === 'user' ? '👤' : '🤖';
                    const roleText = message.role === 'user' ? 'User' : 'Carver Analyst';
                    const messageTime = new Date(Date.now() - (conversationHistory.length - index) * 1000).toLocaleTimeString();
                    
                    htmlContent += `
                        <div class="conversation-entry ${roleClass}">
                            <div class="entry-header">
                                <div class="role-icon">${roleIcon}</div>
                                <div class="role-text">${roleText}</div>
                                <div class="timestamp">${messageTime}</div>
                            </div>
                            <div class="entry-content">
                                <div class="message-content">
                                    ${marked.parse(message.content)}
                                </div>
                            </div>
                        </div>
                    `;
                });
                
                htmlContent += `
                        </div>
                    </div>
                </body>
                </html>
                `;
                
                // Create and trigger download
                const blob = new Blob([htmlContent], { type: 'text/html' });
                const url = URL.createObjectURL(blob);
                const a = document.createElement('a');
                a.href = url;
                a.download = `carver-analyst-report-${new Date().toISOString().split('T')[0]}.html`;
                document.body.appendChild(a);
                a.click();
                document.body.removeChild(a);
                URL.revokeObjectURL(url);
                
                showSuccess("✅ Professional report exported! Open the HTML file and print as PDF.");
                
                // Show instructions for PDF conversion
                setTimeout(() => {
                    showSuccess("💡 To create PDF: Open the file → Print (Ctrl/Cmd+P) → Save as PDF");
                }, 2000);
                
            } catch (error) {
                console.error("Export error:", error);
                displayError(`Export failed: ${error.message}`);
            }
        }
        
    </script>
</body>
</html>