Code.gs

/**
 * Sheet Radar AI - Sanadidari Project 6
 * Smart monitoring, anomaly detection, and trend analysis.
 */

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('🔍 Sheet Radar AI')
    .addItem('Scan Sheet for Anomalies', 'showSidebar')
    .addToUi();
}

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

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

/**
 * Sheet Radar Scan Engine
 */
function runRadarScan(userContext) {
  try {
    const sheet = SpreadsheetApp.getActiveSheet();
    const range = sheet.getDataRange();
    if (range.isBlank()) throw new Error("The sheet is empty. Please add some data to scan.");

    const values = range.getValues();
    const headers = values[0];
    const dataSample = values.slice(1, 21); // Analyze top 20 rows for pattern detection

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

    // AI Analysis for anomalies and trends
    const scanResult = callGeminiForRadar(headers, dataSample, userContext);
    
    // Credit deduction
    updateUserCredits(credits - 1);

    return { 
      status: 'success', 
      anomalies: scanResult.anomalies,
      trends: scanResult.trends,
      summary: scanResult.summary,
      credits: credits - 1
    };
  } catch (e) {
    return { status: 'error', message: e.toString() };
  }
}

/**
 * Gemini API - Radar Logic
 */
function callGeminiForRadar(headers, dataSample, context) {
  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'];

  const systemPrompt = "You are a Data Quality Auditor. Analyze this spreadsheet sample for anomalies (outliers, formatting errors, inconsistencies) and trends.\n" +
    "Headers: " + JSON.stringify(headers) + "\n" +
    "Data Sample: " + JSON.stringify(dataSample) + "\n" +
    "User Context: " + context + "\n\n" +
    "Strict JSON format: {\"anomalies\": [\"Row 4: Value 'X' looks like an outlier\"], \"trends\": [\"Sales increasing by 5% daily\"], \"summary\": \"Overall data quality is good.\"}";

  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("Radar engine unavailable.");
}

/**
 * Standard Credit Management (Sanadidari Protocol)
 */
function doPost(e) {
  try {
    const params = e.parameter;
    if (params.payment_status === "Completed" && params.item_number.indexOf("SR_") === 0) {
      let creditsToAdd = parseInt(params.item_number.replace("SR_PACK_", "")) || 0;
      if (creditsToAdd > 0 && params.payer_email) {
        const currentCredits = getCreditsForEmail(params.payer_email);
        updateCreditsForEmail(params.payer_email, currentCredits + creditsToAdd);
      }
      return ContentService.createTextOutput("SUCCESS");
    }
  } catch (err) { return ContentService.createTextOutput("ERROR"); }
  return ContentService.createTextOutput("IGNORED");
}

function getUserEmail() { return Session.getActiveUser().getEmail(); }
function getCreditsForEmail(email) {
  const scriptProps = PropertiesService.getScriptProperties();
  const db = JSON.parse(scriptProps.getProperty('CREDITS_DB') || "{}");
  return db[email] === undefined ? 10 : db[email];
}
function getUserCredits() { return getCreditsForEmail(getUserEmail()); }
function updateCreditsForEmail(email, count) {
  const scriptProps = PropertiesService.getScriptProperties();
  const db = JSON.parse(scriptProps.getProperty('CREDITS_DB') || "{}");
  db[email] = count;
  scriptProps.setProperty('CREDITS_DB', JSON.stringify(db));
}
function updateUserCredits(count) { updateCreditsForEmail(getUserEmail(), count); }

/**
 * Run once from the 🔍 Sheet Radar 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.');
  }
}