Code.gs

/**
 * FlashBoard AI - Sanadidari Project 3
 * AI-Powered Visual Dashboard Generation
 */

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('⚡ FlashBoard AI')
    .addItem('Generate Dashboard', 'showSidebar')
    .addToUi();
}

/**
 * Displays the FlashBoard Sidebar
 */
function showSidebar() {
  const html = HtmlService.createTemplateFromFile('Sidebar')
    .evaluate()
    .setTitle('FlashBoard AI')
    .setWidth(350);
  SpreadsheetApp.getUi().showSidebar(html);
}

/**
 * Includes HTML files (CSS/JS)
 */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

/**
 * FlashBoard Analysis Engine
 */
function processFlashBoard(prompt) {
  try {
    const sheet = SpreadsheetApp.getActiveSheet();
    const selection = sheet.getActiveRange();
    if (!selection || selection.isBlank()) throw new Error("Please select a data range with numbers.");

    const values = selection.getValues();
    const headers = values[0];
    const sourceRange = {
      sheetName: sheet.getName(),
      row: selection.getRow(),
      col: selection.getColumn(),
      numRows: selection.getNumRows(),
      numCols: selection.getNumColumns(),
      headers: headers,
      data: values
    };

    const email = Session.getActiveUser().getEmail();
    const credits = getUserCredits(email);
    if (credits <= 0) return { status: 'error', message: '⚠️ Out of credits.' };

    // Gemini call to decide charts
    const dashboardPlan = callGeminiForDashboard(sourceRange, prompt);
    
    // Credit deduction
    updateUserCredits(credits - 1);

    return { 
      status: 'success', 
      plan: dashboardPlan,
      sourceRange: sourceRange,
      credits: credits - 1
    };
  } catch (e) {
    return { status: 'error', message: e.toString() };
  }
}

/**
 * Gemini API Call (Fallback 10 models) for Dashboard Plan
 */
function callGeminiForDashboard(sourceRange, userGoal) {
  const apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');
  if (!apiKey) throw new Error('GEMINI_API_KEY not configured. Run setupApiKey() first.');
  const models = ['gemini-2.0-flash', 'gemini-pro-latest', 'gemini-2.5-flash'];

  const dataString = JSON.stringify(sourceRange.data).substring(0, 50000); // 50k chars max
  const systemPrompt = "You are a BI data expert. You MUST create a precise Dashboard for this real data.\n" +
    "Detected headers: " + JSON.stringify(sourceRange.headers) + "\n" +
    "Raw data rows to analyze: " + dataString + "\n\n" +
    "User Goal: " + userGoal + "\n\n" +
    "CRITICAL RULES: \n" +
    "1. For KPIs, you must CALCULATE the true values based strictly on the raw data rows provided. Do the exact math (e.g., correct count, true average, accurate max/sum).\n" +
    "2. If the user asks for specific names, text, or categories (e.g., 'Who is over 30?'), you MUST include the exact names/text found in the data as the KPI value (e.g., {\"label\": \"Names Over 30\", \"value\": \"Hassan, Rachid\"}).\n" +
    "3. For charts, 'columns' must contain exactly TWO array indices: [Label_Index, Value_Index]. Index 0 is the very first column of the selection.\n\n" +
    "Return STRICT JSON format only: {\"kpis\": [{\"label\": \"Total Over 30\", \"value\": \"2\"}, {\"label\": \"Names\", \"value\": \"Hassan, Rachid\"}], \"charts\": [{\"type\": \"COLUMN\", \"title\": \"Age Distribution\", \"columns\": [0, 1]}]}";

  const payload = { contents: [{ parts: [{ text: systemPrompt }] }] };

  for (let model of models) {
    try {
      const url = 'https://generativelanguage.googleapis.com/v1beta/models/' + model + ':generateContent?key=' + apiKey;
      const response = UrlFetchApp.fetch(url, {
        method: 'post', contentType: 'application/json', payload: JSON.stringify(payload), muteHttpExceptions: true
      });
      if (response.getResponseCode() === 200) {
        let aiText = JSON.parse(response.getContentText()).candidates[0].content.parts[0].text;
        const jsonMatch = aiText.match(/\{[\s\S]*\}/);
        return JSON.parse(jsonMatch ? jsonMatch[0] : aiText);
      }
    } catch (e) { continue; }
  }
  throw new Error("AI Engine unavailable.");
}

/**
 * Creates charts on a new Dashboard sheet
 */
function executeDashboard(plan, sourceRange) {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    let dashSheet = ss.getSheetByName("⚡ Flash Dashboard");
    if (dashSheet) ss.deleteSheet(dashSheet);
    dashSheet = ss.insertSheet("⚡ Flash Dashboard");
    
    dashSheet.setTabColor("#FFD700");
    const sourceSheet = ss.getSheetByName(sourceRange.sheetName);

    // KPI Insertion
    plan.kpis.forEach((kpi, i) => {
      let col = 2 + (i * 2);
      dashSheet.getRange(2, col).setValue(kpi.label).setFontWeight("bold").setBackground("#002366").setFontColor("white");
      dashSheet.getRange(3, col).setValue(kpi.value).setFontSize(14).setHorizontalAlignment("center");
    });

    // Chart Creation
    plan.charts.forEach((c, i) => {
      const labelRange = sourceSheet.getRange(sourceRange.row, sourceRange.col + c.columns[0], sourceRange.numRows);
      const dataRange = sourceSheet.getRange(sourceRange.row, sourceRange.col + c.columns[1], sourceRange.numRows);

      const chart = dashSheet.newChart()
        .setChartType(Charts.ChartType[c.type] || Charts.ChartType.COLUMN)
        .addRange(labelRange)
        .addRange(dataRange)
        .setPosition(6 + (i * 15), 2, 0, 0)
        .setOption('title', c.title)
        .setOption('width', 500)
        .setOption('height', 300)
        .build();

      dashSheet.insertChart(chart);
    });

    dashSheet.activate();
    return { status: 'success' };
  } catch (e) {
    return { status: 'error', message: e.toString() };
  }
}

/**
 * Handles PayPal payment notifications (Via Sanadidari Hub Router)
 */
function doPost(e) {
  try {
    const params = e.parameter;
    const paymentStatus = params.payment_status;
    const payerEmail = params.payer_email; // Sent by Hub Router
    const itemNumber = params.item_number || ""; // e.g., FB_PACK_50

    if (paymentStatus === "Completed" && itemNumber.indexOf("FB_") === 0) {
      let creditsToAdd = 0;
      if (itemNumber === "FB_PACK_10" || itemNumber.includes("10")) creditsToAdd = 10;
      if (itemNumber === "FB_PACK_50" || itemNumber.includes("50")) creditsToAdd = 50;
      if (itemNumber === "FB_PACK_100" || itemNumber.includes("100")) creditsToAdd = 100;
      if (itemNumber === "FB_PACK_500" || itemNumber.includes("500")) creditsToAdd = 500;

      if (creditsToAdd > 0 && payerEmail) {
        const currentCredits = getCreditsForEmail(payerEmail);
        updateCreditsForEmail(payerEmail, currentCredits + creditsToAdd);
        console.log(`Payment validated: +${creditsToAdd} credits for ${payerEmail}`);
      }
      return ContentService.createTextOutput("SUCCESS");
    }
  } catch (err) {
    console.error("FlashBoard IPN Error: " + err.toString());
    return ContentService.createTextOutput("ERROR");
  }
  return ContentService.createTextOutput("IGNORED");
}

function getUserEmail() {
  return Session.getActiveUser().getEmail();
}

/**
 * Retrieves credits for a specific email (used by doPost)
 */
function getCreditsForEmail(email) {
  const scriptProps = PropertiesService.getScriptProperties();
  const creditsDB = JSON.parse(scriptProps.getProperty('CREDITS_DB') || "{}");
  return creditsDB[email] === undefined ? 10 : creditsDB[email];
}

/**
 * Retrieves current user's credits
 */
function getUserCredits() {
  return getCreditsForEmail(getUserEmail());
}

/**
 * Updates credits for a specific email (used by doPost)
 */
function updateCreditsForEmail(email, newCount) {
  const scriptProps = PropertiesService.getScriptProperties();
  const creditsDB = JSON.parse(scriptProps.getProperty('CREDITS_DB') || "{}");
  creditsDB[email] = newCount;
  scriptProps.setProperty('CREDITS_DB', JSON.stringify(creditsDB));
}

/**
 * Updates current user's credits
 */
function updateUserCredits(newCount) {
  updateCreditsForEmail(getUserEmail(), newCount);
}

/**
 * Run once from the ⚡ FlashBoard AI menu or Apps Script editor to store your Gemini API key.
 */
function setupApiKey() {
  const ui = SpreadsheetApp.getUi();
  const result = ui.prompt('Setup', 'Enter your Gemini API Key:', ui.ButtonSet.OK_CANCEL);
  if (result.getSelectedButton() === ui.Button.OK) {
    PropertiesService.getScriptProperties().setProperty('GEMINI_API_KEY', result.getResponseText().trim());
    ui.alert('✅ API Key saved securely.');
  }
}