/**
 * YaBaby Sales Bots • Google Sheets AI Command Center & CRM Sync
 * 
 * Powered by Google Gemini AI & JsProject Backend Platform
 * Base URL: https://therealbonz.com/JsProject/api/v1
 */

// Configuration Defaults
const DEFAULT_CONFIG = {
  BASE_URL: 'https://therealbonz.com/JsProject/api/v1',
  API_KEY: 'jsp_live_IrQxgkQsztd2RO5i7pS4b-SQqS0oB7MRkMYDINlY98A',
  PUBLIC_TRACK_URL: 'https://therealbonz.com/JsProject/track'
};

function getApiConfig() {
  const props = PropertiesService.getScriptProperties();
  return {
    baseUrl: props.getProperty('BASE_URL') || DEFAULT_CONFIG.BASE_URL,
    apiKey: props.getProperty('API_KEY') || DEFAULT_CONFIG.API_KEY
  };
}

/**
 * Creates custom toolbar menu on spreadsheet open
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('🤖 AI Sales Engine')
    .addItem('✨ Setup / Format Spreadsheet Tabs', 'setupAllSheets')
    .addSeparator()
    .addItem('🔄 Pull All Leads from CRM', 'syncLeadsFromCRM')
    .addItem('🔍 Research Selected Leads (Gemini AI)', 'researchSelectedLeads')
    .addItem('✉️ Draft Outreach Pitches (Catalog-Grounded)', 'draftSelectedPitches')
    .addItem('📨 Push Selected to Gmail Drafts', 'pushSelectedToGmailDraft')
    .addSeparator()
    .addItem('📦 Refresh Orders & Carrier Tracking', 'syncOrdersFromCRM')
    .addItem('🛡️ Refresh Pending Approvals (HITL)', 'syncApprovalsFromCRM')
    .addItem('✅ Approve Selected HITL Request', 'approveSelectedHITL')
    .addSeparator()
    .addItem('📅 Sync Appointments to Google Calendar', 'syncAppointmentsToGoogleCalendar')
    .addItem('📁 Save Selected Dossiers to Google Drive', 'saveSelectedDossiersToDrive')
    .addSeparator()
    .addItem('⚙️ Test API Connection', 'testApiConnection')
    .addToUi();
}

/**
 * 1. Setup and Format All 3 Main Sheets
 */
function setupAllSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 1. Leads Sheet
  let leadsSheet = ss.getSheetByName('🎯 Leads & Prospects');
  if (!leadsSheet) {
    leadsSheet = ss.insertSheet('🎯 Leads & Prospects');
  }
  leadsSheet.clear();
  const leadHeaders = [
    'Lead ID', 'Company Name', 'Contact Name', 'Email',
    'Stage', 'ICP Score', 'Pain Points & Angles (Gemini AI)',
    'Drafted Subject', 'Drafted Email Pitch (Gemini AI)', 'Gmail Status'
  ];
  leadsSheet.getRange(1, 1, 1, leadHeaders.length).setValues([leadHeaders]);
  formatHeaderRow(leadsSheet, leadHeaders.length, '#1e1b4b', '#ffffff');
  leadsSheet.setFrozenRows(1);
  leadsSheet.setColumnWidth(1, 120); // Lead ID
  leadsSheet.setColumnWidth(2, 180); // Company
  leadsSheet.setColumnWidth(3, 140); // Contact
  leadsSheet.setColumnWidth(4, 200); // Email
  leadsSheet.setColumnWidth(5, 100); // Stage
  leadsSheet.setColumnWidth(6, 90);  // ICP Score
  leadsSheet.setColumnWidth(7, 280); // Pain points
  leadsSheet.setColumnWidth(8, 220); // Subject
  leadsSheet.setColumnWidth(9, 380); // Pitch
  leadsSheet.setColumnWidth(10, 110); // Status

  // 2. Orders Sheet
  let ordersSheet = ss.getSheetByName('📦 Orders & Fulfillment');
  if (!ordersSheet) {
    ordersSheet = ss.insertSheet('📦 Orders & Fulfillment');
  }
  ordersSheet.clear();
  const orderHeaders = [
    'PO Number', 'Client Company', 'Supplier', 'Items Ordered',
    'Total Cost ($)', 'Status', 'Carrier & Tracking', 'Live Tracking Portal'
  ];
  ordersSheet.getRange(1, 1, 1, orderHeaders.length).setValues([orderHeaders]);
  formatHeaderRow(ordersSheet, orderHeaders.length, '#064e3b', '#ffffff');
  ordersSheet.setFrozenRows(1);
  ordersSheet.setColumnWidth(1, 140);
  ordersSheet.setColumnWidth(2, 200);
  ordersSheet.setColumnWidth(3, 130);
  ordersSheet.setColumnWidth(4, 240);
  ordersSheet.setColumnWidth(5, 110);
  ordersSheet.setColumnWidth(6, 110);
  ordersSheet.setColumnWidth(7, 200);
  ordersSheet.setColumnWidth(8, 280);

  // 3. Approvals Sheet
  let hitlSheet = ss.getSheetByName('🛡️ Approvals (HITL)');
  if (!hitlSheet) {
    hitlSheet = ss.insertSheet('🛡️ Approvals (HITL)');
  }
  hitlSheet.clear();
  const hitlHeaders = [
    'Request ID', 'Trigger Reason', 'Situation Summary',
    'AI Recommendation', 'Confidence', 'Status'
  ];
  hitlSheet.getRange(1, 1, 1, hitlHeaders.length).setValues([hitlHeaders]);
  formatHeaderRow(hitlSheet, hitlHeaders.length, '#7c2d12', '#ffffff');
  hitlSheet.setFrozenRows(1);
  hitlSheet.setColumnWidth(1, 120);
  hitlSheet.setColumnWidth(2, 180);
  hitlSheet.setColumnWidth(3, 350);
  hitlSheet.setColumnWidth(4, 300);
  hitlSheet.setColumnWidth(5, 100);
  hitlSheet.setColumnWidth(6, 110);

  SpreadsheetApp.getActiveSpreadsheet().toast('All 3 sheets formatted and ready for sales operations!', 'Setup Complete', 5);
}

function formatHeaderRow(sheet, numCols, bgColor, fgColor) {
  const range = sheet.getRange(1, 1, 1, numCols);
  range.setBackground(bgColor);
  range.setFontColor(fgColor);
  range.setFontWeight('bold');
  range.setFontSize(10);
  range.setVerticalAlignment('middle');
  sheet.setRowHeight(1, 32);
}

/**
 * 2. Sync Leads from CRM into "🎯 Leads & Prospects"
 */
function syncLeadsFromCRM() {
  const cfg = getApiConfig();
  const sheet = getOrSetupSheet('🎯 Leads & Prospects', setupAllSheets);

  SpreadsheetApp.getActiveSpreadsheet().toast('Connecting to YaBaby Sales Bots backend...', 'Syncing Leads', 3);

  const response = makeApiCall(`${cfg.baseUrl}/crm/leads`, 'get');
  if (!response || !Array.isArray(response)) {
    SpreadsheetApp.getUi().alert('Failed to retrieve leads. Verify server connection.');
    return;
  }

  const rows = [];
  response.forEach(item => {
    const compName = item.company ? item.company.name : '';
    const contactName = item.contact ? `${item.contact.first_name || ''} ${item.contact.last_name || ''}`.trim() : '';
    const contactEmail = item.contact ? item.contact.email : '';
    
    rows.push([
      item.id,
      compName,
      contactName,
      contactEmail,
      item.pipeline_stage || item.status || 'new',
      item.lead_score ? `${item.lead_score}/100` : '',
      item.research_summary || '',
      '', // Subject
      '', // Pitch
      'Ready'
    ]);
  });

  if (rows.length > 0) {
    sheet.getRange(2, 1, sheet.getMaxRows() - 1, 10).clearContent();
    sheet.getRange(2, 1, rows.length, 10).setValues(rows);
    SpreadsheetApp.getActiveSpreadsheet().toast(`Synced ${rows.length} leads successfully from CRM!`, 'CRM Sync Complete', 5);
  } else {
    SpreadsheetApp.getActiveSpreadsheet().toast('No leads found in CRM database.', 'CRM Notice', 4);
  }
}

/**
 * 3. Research Selected Leads using Google Gemini AI
 */
function researchSelectedLeads() {
  const cfg = getApiConfig();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (sheet.getName() !== '🎯 Leads & Prospects') {
    SpreadsheetApp.getUi().alert('Please switch to the "🎯 Leads & Prospects" tab first.');
    return;
  }

  const range = sheet.getActiveRange();
  const startRow = Math.max(range.getRow(), 2);
  const numRows = range.getNumRows();
  let count = 0;

  SpreadsheetApp.getActiveSpreadsheet().toast('Google Gemini analyzing lead pain points & ICP...', 'Researching Leads', 10);

  for (let r = startRow; r < startRow + numRows; r++) {
    const leadId = sheet.getRange(r, 1).getValue();
    if (!leadId) continue;

    const endpoint = `${cfg.baseUrl}/agent/leads/${leadId}/research`;
    const res = makeApiCall(endpoint, 'post', {});
    if (res) {
      if (res.lead_score) {
        sheet.getRange(r, 6).setValue(`${res.lead_score}/100`);
      }
      let summaryText = '';
      if (res.suggested_angle) summaryText += `[Angle] ${res.suggested_angle}\n`;
      if (res.pain_points && res.pain_points.length > 0) {
        summaryText += `[Pain Points] ${res.pain_points.join(', ')}`;
      }
      if (summaryText) {
        sheet.getRange(r, 7).setValue(summaryText);
      }
      count++;
    }
  }

  SpreadsheetApp.getActiveSpreadsheet().toast(`Gemini AI finished research on ${count} lead(s)!`, 'AI Success', 5);
}

/**
 * 4. Draft Catalog-Grounded Cold Outreach Pitches
 */
function draftSelectedPitches() {
  const cfg = getApiConfig();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (sheet.getName() !== '🎯 Leads & Prospects') {
    SpreadsheetApp.getUi().alert('Please switch to the "🎯 Leads & Prospects" tab first.');
    return;
  }

  const range = sheet.getActiveRange();
  const startRow = Math.max(range.getRow(), 2);
  const numRows = range.getNumRows();
  let count = 0;

  SpreadsheetApp.getActiveSpreadsheet().toast('Gemini drafting hyper-personalized, catalog-grounded pitches...', 'Drafting Pitches', 10);

  for (let r = startRow; r < startRow + numRows; r++) {
    const leadId = sheet.getRange(r, 1).getValue();
    if (!leadId) continue;

    const endpoint = `${cfg.baseUrl}/agent/leads/${leadId}/draft-outreach`;
    const res = makeApiCall(endpoint, 'post', {});
    if (res) {
      if (res.subject) {
        sheet.getRange(r, 8).setValue(res.subject);
      }
      if (res.body_text) {
        sheet.getRange(r, 9).setValue(res.body_text);
      }
      sheet.getRange(r, 10).setValue('Drafted');
      count++;
    }
  }

  SpreadsheetApp.getActiveSpreadsheet().toast(`Gemini drafted ${count} personalized pitch(es)!`, 'Outreach Ready', 5);
}

/**
 * 5. Push Selected Draft directly into Gmail Drafts
 */
function pushSelectedToGmailDraft() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (sheet.getName() !== '🎯 Leads & Prospects') {
    SpreadsheetApp.getUi().alert('Please switch to the "🎯 Leads & Prospects" tab first.');
    return;
  }

  const range = sheet.getActiveRange();
  const row = range.getRow();
  if (row < 2) {
    SpreadsheetApp.getUi().alert('Please select a lead row.');
    return;
  }

  const toEmail = sheet.getRange(row, 4).getValue();
  const subject = sheet.getRange(row, 8).getValue();
  const body = sheet.getRange(row, 9).getValue();

  if (!toEmail || !subject || !body) {
    SpreadsheetApp.getUi().alert('Selected row is missing Email, Subject, or Body text. Please draft first!');
    return;
  }

  // Create Gmail draft directly in the user's Gmail inbox
  GmailApp.createDraft(toEmail, subject, body);
  sheet.getRange(row, 10).setValue('In Gmail Drafts');

  SpreadsheetApp.getActiveSpreadsheet().toast(`Draft for ${toEmail} created in your Gmail!`, 'Gmail Draft Created', 5);
}

/**
 * 6. Refresh Orders & Tracking
 */
function syncOrdersFromCRM() {
  const cfg = getApiConfig();
  const sheet = getOrSetupSheet('📦 Orders & Fulfillment', setupAllSheets);

  SpreadsheetApp.getActiveSpreadsheet().toast('Syncing purchase orders & carrier logistics...', 'Fulfillment Sync', 3);

  const orders = makeApiCall(`${cfg.baseUrl}/fulfillment/orders`, 'get');
  if (!orders || !Array.isArray(orders)) {
    SpreadsheetApp.getUi().alert('Failed to retrieve orders.');
    return;
  }

  const rows = [];
  orders.forEach(po => {
    let carrierTracking = '';
    if (po.shipments && po.shipments.length > 0) {
      const s = po.shipments[0];
      carrierTracking = `${s.carrier || 'CARRIER'}: ${s.tracking_number || ''}`.trim();
    }
    const trackingPortal = `${DEFAULT_CONFIG.PUBLIC_TRACK_URL}/${po.po_number}`;

    rows.push([
      po.po_number,
      po.client_name || 'Commercial Client',
      po.supplier_name || 'Vendor',
      po.notes || (po.items_json ? JSON.stringify(po.items_json) : 'Commercial catalog items'),
      Number(po.total_cost || 0).toFixed(2),
      po.status || 'placed',
      carrierTracking || 'Pending Dispatch',
      trackingPortal
    ]);
  });

  if (rows.length > 0) {
    sheet.getRange(2, 1, sheet.getMaxRows() - 1, 8).clearContent();
    sheet.getRange(2, 1, rows.length, 8).setValues(rows);
    SpreadsheetApp.getActiveSpreadsheet().toast(`Loaded ${rows.length} procurement orders!`, 'Orders Synced', 5);
  }
}

/**
 * 7. Refresh Approvals (HITL)
 */
function syncApprovalsFromCRM() {
  const cfg = getApiConfig();
  const sheet = getOrSetupSheet('🛡️ Approvals (HITL)', setupAllSheets);

  SpreadsheetApp.getActiveSpreadsheet().toast('Checking Human-in-the-Loop review queue...', 'HITL Sync', 3);

  const requests = makeApiCall(`${cfg.baseUrl}/hitl/requests`, 'get');
  if (!requests || !Array.isArray(requests)) {
    SpreadsheetApp.getUi().alert('Failed to retrieve HITL queue.');
    return;
  }

  const rows = [];
  requests.forEach(req => {
    rows.push([
      req.id,
      req.trigger_reason,
      req.situation_summary,
      req.ai_recommendation,
      req.confidence_score ? `${(req.confidence_score * 100).toFixed(0)}%` : '90%',
      req.status
    ]);
  });

  if (rows.length > 0) {
    sheet.getRange(2, 1, sheet.getMaxRows() - 1, 6).clearContent();
    sheet.getRange(2, 1, rows.length, 6).setValues(rows);
    SpreadsheetApp.getActiveSpreadsheet().toast(`Loaded ${rows.length} HITL review items!`, 'HITL Synced', 5);
  } else {
    SpreadsheetApp.getActiveSpreadsheet().toast('No pending HITL approvals.', 'HITL Clean', 4);
  }
}

/**
 * 8. Approve Selected HITL Request
 */
function approveSelectedHITL() {
  const cfg = getApiConfig();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (sheet.getName() !== '🛡️ Approvals (HITL)') {
    SpreadsheetApp.getUi().alert('Please switch to the "🛡️ Approvals (HITL)" tab first.');
    return;
  }

  const row = sheet.getActiveRange().getRow();
  if (row < 2) {
    SpreadsheetApp.getUi().alert('Please select an approval request row.');
    return;
  }

  const reqId = sheet.getRange(row, 1).getValue();
  if (!reqId) return;

  const res = makeApiCall(`${cfg.baseUrl}/hitl/requests/${reqId}/action`, 'post', {
    action: 'approved',
    reviewer_notes: 'Approved via Google Sheets Sales Command Center'
  });

  if (res) {
    sheet.getRange(row, 6).setValue('approved');
    sheet.getRange(row, 6).setBackground('#bbf7d0'); // Light green
    SpreadsheetApp.getActiveSpreadsheet().toast(`Request ${reqId.substring(0, 8)} approved!`, 'HITL Approved', 5);
  }
}

/**
 * 9. Test API Connection
 */
function testApiConnection() {
  const cfg = getApiConfig();
  SpreadsheetApp.getActiveSpreadsheet().toast('Pinging backend server...', 'Connecting', 3);

  const leads = makeApiCall(`${cfg.baseUrl}/crm/pipeline/stats`, 'get');
  if (leads) {
    SpreadsheetApp.getUi().alert(
      '✅ Connection Successful!\n\n' +
      `Server: ${cfg.baseUrl}\n` +
      `API Key Prefix: ${cfg.apiKey.substring(0, 16)}...\n` +
      'Google Gemini AI and CRM services are connected and operational.'
    );
  } else {
    SpreadsheetApp.getUi().alert('❌ Connection Failed. Please verify your network and API key.');
  }
}

/**
 * Helper: Makes Authenticated REST calls to JsProject Backend
 */
function makeApiCall(url, method, payload) {
  const cfg = getApiConfig();
  const options = {
    method: method.toLowerCase(),
    headers: {
      'X-API-Key': cfg.apiKey,
      'Content-Type': 'application/json'
    },
    muteHttpExceptions: true
  };

  if (payload && (method.toLowerCase() === 'post' || method.toLowerCase() === 'put' || method.toLowerCase() === 'patch')) {
    options.payload = JSON.stringify(payload);
  }

  try {
    const resp = UrlFetchApp.fetch(url, options);
    const code = resp.getResponseCode();
    if (code >= 200 && code < 300) {
      return JSON.parse(resp.getContentText());
    } else {
      Logger.log(`API Error [${code}] at ${url}: ${resp.getContentText()}`);
      SpreadsheetApp.getActiveSpreadsheet().toast(`Error ${code}: ${resp.getContentText().substring(0, 80)}`, 'API Notice', 5);
      return null;
    }
  } catch (err) {
    Logger.log(`Fetch Exception: ${err}`);
    SpreadsheetApp.getActiveSpreadsheet().toast(`Network error: ${err.message}`, 'API Exception', 5);
    return null;
  }
}

/**
 * Helper: Find or Auto-Create Sheet
 */
function getOrSetupSheet(name, setupFunc) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(name);
  if (!sheet) {
    setupFunc();
    sheet = ss.getSheetByName(name);
  }
  return sheet;
}

/**
 * 10. Sync Closer Appointments to Google Calendar
 */
function syncAppointmentsToGoogleCalendar() {
  const cfg = getApiConfig();
  SpreadsheetApp.getActiveSpreadsheet().toast('Fetching scheduled closer calls from CRM...', 'Calendar Sync', 4);

  const appointments = makeApiCall(`${cfg.baseUrl}/crm/appointments`, 'get');
  if (!appointments || !Array.isArray(appointments)) {
    SpreadsheetApp.getUi().alert('Could not retrieve appointments from CRM server.');
    return;
  }

  if (appointments.length === 0) {
    SpreadsheetApp.getActiveSpreadsheet().toast('No upcoming appointments found in CRM.', 'Calendar Status', 4);
    return;
  }

  const calendar = CalendarApp.getDefaultCalendar();
  let createdCount = 0;

  appointments.forEach(apt => {
    try {
      const startTime = apt.start_time ? new Date(apt.start_time) : new Date(Date.now() + 86400000);
      const endTime = apt.end_time ? new Date(apt.end_time) : new Date(startTime.getTime() + (30 * 60000));
      const title = `AI Sales Demo: ${apt.title || apt.lead_name || 'Prospect Consultation'}`;
      const description = `YaBaby Sales Bots Strategy Session\nLead: ${apt.lead_name || 'Prospective Client'}\nNotes: ${apt.notes || 'Autonomous closer consultation'}\nCRM ID: ${apt.id}`;
      const location = apt.location || apt.meeting_url || 'Google Meet / Phone';

      calendar.createEvent(title, startTime, endTime, {
        description: description,
        location: location
      });
      createdCount++;
    } catch (e) {
      Logger.log(`Failed to create calendar event for apt ${apt.id}: ${e}`);
    }
  });

  SpreadsheetApp.getActiveSpreadsheet().toast(`Synced ${createdCount} appointment(s) directly to your Google Calendar!`, 'Google Calendar Ready', 5);
}

/**
 * 11. Save Selected Dossiers to Google Drive Sales Vault
 */
function saveSelectedDossiersToDrive() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (sheet.getName() !== '🎯 Leads & Prospects') {
    SpreadsheetApp.getUi().alert('Please switch to the "🎯 Leads & Prospects" tab and highlight lead rows.');
    return;
  }

  const range = sheet.getActiveRange();
  const startRow = Math.max(range.getRow(), 2);
  const numRows = range.getNumRows();

  // Find or create 'YaBaby Sales Vault' folder
  let vaultFolder;
  const folders = DriveApp.getFoldersByName('YaBaby Sales Vault');
  if (folders.hasNext()) {
    vaultFolder = folders.next();
  } else {
    vaultFolder = DriveApp.createFolder('YaBaby Sales Vault');
  }

  // Find or create 'Dossiers' subfolder
  let dossiersFolder;
  const subfolders = vaultFolder.getFoldersByName('Dossiers');
  if (subfolders.hasNext()) {
    dossiersFolder = subfolders.next();
  } else {
    dossiersFolder = vaultFolder.createFolder('Dossiers');
  }

  let savedCount = 0;
  for (let r = startRow; r < startRow + numRows; r++) {
    const leadId = sheet.getRange(r, 1).getValue();
    const company = sheet.getRange(r, 2).getValue();
    const contact = sheet.getRange(r, 3).getValue();
    const email = sheet.getRange(r, 4).getValue();
    const stage = sheet.getRange(r, 5).getValue();
    const icp = sheet.getRange(r, 6).getValue();
    const painPoints = sheet.getRange(r, 7).getValue();
    const pitch = sheet.getRange(r, 9).getValue();

    if (!leadId && !company) continue;

    const fileContent = 
      `=================================================================\n` +
      `YA BABY SALES BOTS • EXECUTIVE CLOSER DOSSIER\n` +
      `=================================================================\n\n` +
      `Company: ${company || 'N/A'}\n` +
      `Contact: ${contact || 'N/A'} (${email || 'N/A'})\n` +
      `Pipeline Stage: ${stage || 'Lead'}\n` +
      `ICP Score: ${icp || 'N/A'}\n\n` +
      `--- DISCOVERY & INTELLIGENCE ---\n` +
      `${painPoints || 'No pain points recorded yet.'}\n\n` +
      `--- TAILORED VALUE PROPOSITION ---\n` +
      `${pitch || 'Outreach pitch pending generation.'}\n\n` +
      `Generated by YaBaby Sales Bots Autonomous Revenue Engine\n` +
      `Timestamp: ${new Date().toISOString()}\n`;

    const sanitizedComp = (company || `Lead_${leadId}`).toString().replace(/[^a-zA-Z0-9_-]/g, '_');
    const fileName = `Closer_Dossier_${sanitizedComp}.txt`;

    dossiersFolder.createFile(fileName, fileContent, MimeType.PLAIN_TEXT);
    savedCount++;
  }

  SpreadsheetApp.getActiveSpreadsheet().toast(`Saved ${savedCount} dossier(s) to Google Drive (/YaBaby Sales Vault/Dossiers)!`, 'Drive Backup Complete', 5);
}