server.js

const express = require('express');
const { google } = require('googleapis');
const { GoogleGenerativeAI } = require('@google/generative-ai');
const cors = require('cors');
const path = require('path');
require('dotenv').config();

const app = express();
app.use(express.json());

const frontendUrl = process.env.FRONTEND_URL || 'http://localhost:5173';
app.use(cors({ origin: frontendUrl }));

const PORT = process.env.PORT || 7860;

const genAI = new GoogleGenerativeAI(process.env.GEMINI_API_KEY);
const model = genAI.getGenerativeModel({ model: "gemini-1.5-flash" });

let processedStaffCache = null;
let rawSheetCache = null;
const CACHE_DURATION_MS = 300000;

const isValidName = (name) => name && name.trim() !== '' && name.trim() !== '-';

function getGoogleAuth() {
    if (process.env.GOOGLE_CREDENTIALS_JSON) {
        const credentials = JSON.parse(process.env.GOOGLE_CREDENTIALS_JSON);
        return new google.auth.GoogleAuth({
            credentials,
            scopes: ['https://www.googleapis.com/auth/spreadsheets.readonly'],
        });
    }
    return new google.auth.GoogleAuth({
        keyFile: 'google-credentials.json',
        scopes: ['https://www.googleapis.com/auth/spreadsheets.readonly'],
    });
}

async function updateSheetCache() {
    try {
        console.log('Refreshing spreadsheet data cache...');
        const auth = getGoogleAuth();
        const sheets = google.sheets({ version: 'v4', auth });
        const response = await sheets.spreadsheets.values.get({
            spreadsheetId: process.env.GOOGLE_SHEET_ID,
            range: 'Sheet1!A:H',
        });
        const rows = response.data.values;
        if (!rows || rows.length === 0) {
            console.log('No data found in spreadsheet.');
            return;
        }
        rawSheetCache = rows;
        const headers = rows[0];
        const dataRows = rows.slice(1);
        let currentLocation = '';
        let currentOfficeExt = 'N/A';
        const allStaff = [];
        dataRows.forEach((row, rowIndex) => {
            const rowData = {};
            headers.forEach((header, index) => rowData[header] = row[index] || '');
            if (isValidName(rowData.Location)) {
                currentLocation = rowData.Location;
                if (currentLocation.toLowerCase().includes('liveoak')) {
                    currentOfficeExt = 'Main Branch';
                } else {
                    const extMatch = currentLocation.match(/Ext\s*(\d+)/i);
                    currentOfficeExt = extMatch ? extMatch[1] : 'N/A';
                }
            }
            rowData.Location = currentLocation;
            rowData.OfficeExtension = currentOfficeExt;
            const baseId = `r${rowIndex}`;
            if (isValidName(rowData.Provider)) allStaff.push({ ...rowData, id: `${baseId}-p`, role: 'Provider' });
            if (isValidName(rowData.MA)) allStaff.push({ ...rowData, id: `${baseId}-ma`, role: 'Medical Assistant', Extension: rowData.Extension });
            if (isValidName(rowData.VA)) allStaff.push({ ...rowData, id: `${baseId}-va`, role: 'Virtual Assistant', Extension: rowData.Ext });
            if (isValidName(rowData['Team Leads/Manager'])) allStaff.push({ ...rowData, id: `${baseId}-tl`, role: 'Team Lead / Manager', Extension: rowData['Ext/Phone#'] });
            if (isValidName(rowData['Ext/Phone#']) && /^[a-zA-Z]+-\s*\d+$/.test(rowData['Ext/Phone#'])) {
                const [name, ext] = rowData['Ext/Phone#'].split('-');
                allStaff.push({ ...rowData, id: `${baseId}-other`, role: 'Other Staff', OtherStaffName: name.trim(), Extension: ext.trim() });
            }
        });
        processedStaffCache = allStaff;
        console.log('Cache updated. Processed records:', processedStaffCache.length);
    } catch (error) {
        console.error('Error refreshing spreadsheet cache:', error.message);
    }
}

const delay = (ms) => new Promise(resolve => setTimeout(resolve, ms));

async function generateWithRetry(model, prompt, maxRetries = 3) {
    let attempt = 0;
    while (attempt < maxRetries) {
        try {
            return await model.generateContentStream(prompt);
        } catch (error) {
            if (error.status === 503 && attempt < maxRetries - 1) {
                const delayTime = Math.pow(2, attempt) * 1000 + Math.random() * 1000;
                await delay(delayTime);
                attempt++;
            } else {
                throw error;
            }
        }
    }
}

app.get('/', (req, res) => res.sendFile(path.join(__dirname, 'index.html')));

app.get('/api/data', (req, res) => {
    if (!processedStaffCache) return res.status(503).json({ message: 'Data is being fetched.' });
    res.json(processedStaffCache);
});

app.get('/api/raw-data', (req, res) => {
    if (!rawSheetCache) return res.status(503).json({ message: 'Data is being fetched.' });
    res.json(rawSheetCache);
});

app.get('/api/ask-luis', async (req, res) => {
    const { question } = req.query;
    if (!question || !processedStaffCache) {
        return res.status(400).send('Bad request.');
    }
    res.setHeader('Content-Type', 'text/event-stream');
    res.setHeader('Cache-Control', 'no-cache');
    res.setHeader('Connection', 'keep-alive');
    res.flushHeaders();

    const prompt = `
      You are "Luis", an AI data analyst for the Hillside Medical Group staff directory.
      Your primary function is to answer questions about staff based ONLY on the JSON data provided.
      **Your Core Instructions:**
      1.  **Data Relationships:**
          - The 'Location' key groups all staff at a specific clinic.
          - 'MA' (Medical Assistant) is directly associated with the 'Provider' in the same row.
          - 'VA' (Virtual Assistant) is associated with the 'Location' of its row. Their extension is in the 'Ext' column.
          - The 'Team Leads/Manager' column lists supervisors for a 'Location'. Their extension is in 'Ext/Phone#'.
      2.  **Structured Output:** When asked to list multiple people (e.g., "list the VAs for LiveOak"), you MUST format the response as a clean Markdown table. Include relevant columns like Name and Extension.
      3.  **Highlighting:** To make important data like names, locations, or extensions stand out in your text or tables, enclose them in single backticks. For example: "The main office for \`LiveOak\` is the \`Main Branch\`."
      4.  **Aggregations (Counts):** If asked for a count ("how many MAs?", "VA count"), calculate the total from the data and give a precise number.
      5.  **Handling Ambiguity:** If a question is ambiguous (e.g., "give me the list of MAs" without a location), you MUST ask a clarifying question (e.g., "Of which location would you like the list of MAs? You can also ask for all of them.").
      **Directory Data:**
      ${JSON.stringify(processedStaffCache)}
      **User's Question:**
      "${question}"
    `;

    const AI_TIMEOUT = 20000; // 20 seconds
    const timeoutPromise = new Promise((_, reject) => 
        setTimeout(() => reject(new Error('AI_TIMEOUT')), AI_TIMEOUT)
    );

    try {
        const resultPromise = generateWithRetry(model, prompt);
        const result = await Promise.race([resultPromise, timeoutPromise]);
        
        for await (const chunk of result.stream) {
            if (chunk.text()) {
                res.write(`data: ${JSON.stringify({ chunk: chunk.text() })}\n\n`);
            }
        }
    } catch (error) {
        let errorMessage = "I'm having a bit of trouble connecting to my brain right now. Please try again in a moment.";
        if (error.message === 'AI_TIMEOUT') {
            errorMessage = "Sorry, my connection to the AI service timed out. The service may be busy. Please try your question again in a few moments.";
        } else if (error.status === 503) {
            errorMessage = "The directory AI service is currently busy. Please try again.";
        }
        res.write(`data: ${JSON.stringify({ error: errorMessage })}\n\n`);
    } finally {
        res.write('data: [DONE]\n\n');
        res.end();
    }
});

app.listen(PORT, () => {
    console.log(`Backend server is running and listening on port ${PORT}`);
    updateSheetCache();
    setInterval(updateSheetCache, CACHE_DURATION_MS);
});