// Google Ads to Google Sheets Integration - Apps Script
// This script fetches Google Ads data and populates Google Sheets


// =============================================================================
// CONFIGURATION - Update these with your credentials
// =============================================================================

const CONFIG = {
 // Google Ads API Configuration
 DEVELOPER_TOKEN: 'YOUR DEVELOPER TOKEN', //Your Google Ads Developer Token
 CLIENT_ID: 'CLIENT_ID', //Your Client ID that you've generated via Google Playground

 CLIENT_SECRET: 'CLIENT_SECRET',//Your Client SECRET that you've generated via Google Playground

 REFRESH_TOKEN: 'REFRESH_TOKEN', //Your Refresh Token that you've generated via Google Playground 
 LOGIN_CUSTOMER_ID: 'LOGIN_CUSTOMER_ID',//Your Google Ads Manager Account ID 

 CUSTOMER_ID: 'CUSTOMER_ID', //The Google Ads Account for the client 
  // Sheet Names
  SETUP_SHEET: 'Setup',
  GOOGLE_ADS_SHEET: 'Google Ads Data',
  MIXPANEL_SHEET: 'Mixpanel',
  
  // Google Ads API Configuration
  API_VERSION: 'v19',
  API_BASE_URL: 'https://googleads.googleapis.com'
};

// =============================================================================
// MAIN FUNCTIONS
// =============================================================================

/**
 * Main function to pull Google Ads data based on setup sheet configuration
 */
function pullGoogleAdsData() {
  try {
    console.log('🚀 Starting Google Ads data pull...');
    
    // Get date range from setup sheet
    const dateRange = getDateRangeFromSetup();
    if (!dateRange) {
      throw new Error('Invalid date range in setup sheet');
    }
    
    // Get access token
    const accessToken = getAccessToken();
    if (!accessToken) {
      throw new Error('Failed to get access token');
    }
    
    // Fetch Google Ads data
    const adsData = fetchGoogleAdsData(accessToken, dateRange);
    
    // Write data to Google Ads sheet
    writeDataToSheet(CONFIG.GOOGLE_ADS_SHEET, adsData, 'googleAds');
    
    // Process data for Mixpanel sheet
    const mixpanelData = transformToMixpanelFormat(adsData);
    writeDataToSheet(CONFIG.MIXPANEL_SHEET, mixpanelData, 'mixpanel');
    
    // Update last run timestamp
    updateLastRunTimestamp();
    
    console.log('✅ Google Ads data pull completed successfully');
    SpreadsheetApp.getUi().alert('Success!', 'Google Ads data has been updated successfully.', SpreadsheetApp.getUi().ButtonSet.OK);
    
  } catch (error) {
    console.error('❌ Error in pullGoogleAdsData:', error);
    SpreadsheetApp.getUi().alert('Error', `Failed to pull Google Ads data: ${error.message}`, SpreadsheetApp.getUi().ButtonSet.OK);
  }
}

/**
 * Initialize the spreadsheet with required sheets and setup
 */
function initializeSpreadsheet() {
  try {
    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    
    // Create Setup sheet
    createSetupSheet(spreadsheet);
    
    // Create Google Ads Data sheet
    createGoogleAdsSheet(spreadsheet);
    
    // Create Mixpanel sheet
    createMixpanelSheet(spreadsheet);
    
    SpreadsheetApp.getUi().alert('Success!', 'Spreadsheet has been initialized with all required sheets.', SpreadsheetApp.getUi().ButtonSet.OK);
    
  } catch (error) {
    console.error('❌ Error in initializeSpreadsheet:', error);
    SpreadsheetApp.getUi().alert('Error', `Failed to initialize spreadsheet: ${error.message}`, SpreadsheetApp.getUi().ButtonSet.OK);
  }
}

// =============================================================================
// SHEET CREATION FUNCTIONS
// =============================================================================

function createSetupSheet(spreadsheet) {
  let sheet = spreadsheet.getSheetByName(CONFIG.SETUP_SHEET);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(CONFIG.SETUP_SHEET);
  }
  
  // Clear existing content
  sheet.clear();
  
  // Setup headers and initial data
  const setupData = [
    ['Google Ads to Mixpanel Integration Setup', '', ''],
    ['', '', ''],
    ['Configuration', 'Value', 'Description'],
    ['Start Date', Utilities.formatDate(new Date(Date.now() - 7*24*60*60*1000), Session.getScriptTimeZone(), 'yyyy-MM-dd'), 'Start date for data pull (YYYY-MM-DD)'],
    ['End Date', Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd'), 'End date for data pull (YYYY-MM-DD)'],
    ['', '', ''],
    ['Status', '', ''],
    ['Last Run', '', 'Last successful data pull'],
    ['', '', ''],
    ['Instructions:', '', ''],
    ['1. Set your desired date range above', '', ''],
    ['2. Run the "Pull Google Ads Data" function', '', ''],
    ['3. Check the "Google Ads Data" and "Mixpanel" sheets for results', '', '']
  ];
  
  // Write data
  sheet.getRange(1, 1, setupData.length, 3).setValues(setupData);
  
  // Format the sheet
  sheet.getRange('A1:C1').merge().setFontWeight('bold').setFontSize(14).setHorizontalAlignment('center');
  sheet.getRange('A3:C3').setFontWeight('bold').setBackground('#E8F0FE');
  sheet.getRange('A7:C7').setFontWeight('bold').setBackground('#FFF3E0');
  sheet.getRange('A10:C10').setFontWeight('bold').setBackground('#E8F5E8');
  
  // Set column widths
  sheet.setColumnWidth(1, 200);
  sheet.setColumnWidth(2, 150);
  sheet.setColumnWidth(3, 300);
}

function createGoogleAdsSheet(spreadsheet) {
  let sheet = spreadsheet.getSheetByName(CONFIG.GOOGLE_ADS_SHEET);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(CONFIG.GOOGLE_ADS_SHEET);
  }
  
  // Clear existing content
  sheet.clear();
  
  // Headers for Google Ads data
  const headers = [
    'Date', 'Campaign ID', 'Campaign Name', 'Impressions', 
    'Clicks', 'Cost (USD)', 'Conversions', 'CPC', 'CPM', 'CTR (%)', 'CVR (%)'
  ];
  
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold').setBackground('#E8F0FE');
  
  // Auto-resize columns
  sheet.autoResizeColumns(1, headers.length);
}

function createMixpanelSheet(spreadsheet) {
  let sheet = spreadsheet.getSheetByName(CONFIG.MIXPANEL_SHEET);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(CONFIG.MIXPANEL_SHEET);
  }
  
  // Clear existing content
  sheet.clear();
  
  // Headers for Mixpanel data
  const headers = [
    'Event', 'Time', 'Insert ID', 'Network', 'Campaign ID', 'Campaign Name',
    'Date', 'Impressions', 'Clicks', 'Spend', 'Conversions', 'CPC', 'Source'
  ];
  
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold').setBackground('#FFF3E0');
  
  // Auto-resize columns
  sheet.autoResizeColumns(1, headers.length);
}

// =============================================================================
// DATA FETCHING FUNCTIONS
// =============================================================================

function getDateRangeFromSetup() {
  try {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SETUP_SHEET);
    const startDate = sheet.getRange('B4').getValue();
    const endDate = sheet.getRange('B5').getValue();
    
    if (!startDate || !endDate) {
      throw new Error('Start date and end date must be specified in the setup sheet');
    }
    
    return {
      start: Utilities.formatDate(new Date(startDate), Session.getScriptTimeZone(), 'yyyy-MM-dd'),
      end: Utilities.formatDate(new Date(endDate), Session.getScriptTimeZone(), 'yyyy-MM-dd')
    };
  } catch (error) {
    console.error('Error getting date range:', error);
    return null;
  }
}

function getAccessToken() {
  try {
    const url = 'https://oauth2.googleapis.com/token';
    const payload = {
      'client_id': CONFIG.CLIENT_ID,
      'client_secret': CONFIG.CLIENT_SECRET,
      'refresh_token': CONFIG.REFRESH_TOKEN,
      'grant_type': 'refresh_token'
    };
    
    const options = {
      'method': 'POST',
      'headers': {
        'Content-Type': 'application/x-www-form-urlencoded'
      },
      'payload': Object.keys(payload).map(key => key + '=' + encodeURIComponent(payload[key])).join('&')
    };
    
    const response = UrlFetchApp.fetch(url, options);
    const data = JSON.parse(response.getContentText());
    
    if (data.access_token) {
      console.log('✅ Access token obtained successfully');
      return data.access_token;
    } else {
      throw new Error('Failed to get access token: ' + JSON.stringify(data));
    }
  } catch (error) {
    console.error('Error getting access token:', error);
    return null;
  }
}

function fetchGoogleAdsData(accessToken, dateRange) {
  try {
    console.log(`📊 Fetching Google Ads data from ${dateRange.start} to ${dateRange.end}`);
    
    const query = `
      SELECT
        campaign.id,
        campaign.name,
        metrics.cost_micros,
        metrics.impressions,
        metrics.clicks,
        metrics.conversions,
        segments.date
      FROM campaign
      WHERE segments.date BETWEEN '${dateRange.start}' AND '${dateRange.end}'
      ORDER BY segments.date DESC, campaign.id
    `.replace(/\s+/g, ' ').trim();
    
    // Use searchStream endpoint to match your working Python code
    const url = `${CONFIG.API_BASE_URL}/${CONFIG.API_VERSION}/customers/${CONFIG.CUSTOMER_ID}/googleAds:searchStream`;
    
    const payload = {
      'query': query
    };
    
    const options = {
      'method': 'POST',
      'headers': {
        'Authorization': `Bearer ${accessToken}`,
        'developer-token': CONFIG.DEVELOPER_TOKEN,
        'login-customer-id': CONFIG.LOGIN_CUSTOMER_ID,
        'Content-Type': 'application/json'
      },
      'payload': JSON.stringify(payload),
      'muteHttpExceptions': true
    };
    
    console.log('Making request to:', url);
    console.log('With query:', query);
    
    const response = UrlFetchApp.fetch(url, options);
    const responseCode = response.getResponseCode();
    const responseText = response.getContentText();
    
    console.log('Response code:', responseCode);
    console.log('Response text (first 500 chars):', responseText.substring(0, 500));
    
    if (responseCode !== 200) {
      throw new Error(`API request failed with code ${responseCode}: ${responseText}`);
    }
    
    // SearchStream response is a JSON array, parse it as a whole
    const campaigns = [];
    
    try {
      // Parse the entire response as JSON
      const data = JSON.parse(responseText);
      console.log('Parsed data structure:', typeof data, Array.isArray(data));
      
      // Handle if data is an array of result objects
      if (Array.isArray(data)) {
        for (const item of data) {
          if (item.results) {
            for (const result of item.results) {
              const campaign = {
                date: result.segments.date,
                campaignId: result.campaign.id.toString(),
                campaignName: result.campaign.name,
                impressions: parseInt(result.metrics.impressions) || 0,
                clicks: parseInt(result.metrics.clicks) || 0,
                costMicros: parseInt(result.metrics.costMicros) || 0,
                conversions: parseFloat(result.metrics.conversions) || 0
              };
              campaigns.push(campaign);
              console.log('Added campaign:', campaign.campaignName, 'Date:', campaign.date);
            }
          }
        }
      }
      // Handle if data is a single object with results
      else if (data.results) {
        for (const result of data.results) {
          const campaign = {
            date: result.segments.date,
            campaignId: result.campaign.id.toString(),
            campaignName: result.campaign.name,
            impressions: parseInt(result.metrics.impressions) || 0,
            clicks: parseInt(result.metrics.clicks) || 0,
            costMicros: parseInt(result.metrics.costMicros) || 0,
            conversions: parseFloat(result.metrics.conversions) || 0
          };
          campaigns.push(campaign);
          console.log('Added campaign:', campaign.campaignName, 'Date:', campaign.date);
        }
      }
      
    } catch (parseError) {
      console.error('Error parsing full JSON response:', parseError);
      console.log('Raw response text:', responseText);
      throw new Error(`Failed to parse API response: ${parseError.message}`);
    }
    
    console.log(`✅ Fetched ${campaigns.length} campaign records`);
    return campaigns;
    
  } catch (error) {
    console.error('Error fetching Google Ads data:', error);
    throw error;
  }
}

// =============================================================================
// DATA TRANSFORMATION FUNCTIONS
// =============================================================================

function transformToMixpanelFormat(adsData) {
  console.log('🔄 Transforming data to Mixpanel format...');
  
  const mixpanelData = [];
  
  for (const campaign of adsData) {
    const costDollars = campaign.costMicros / 1000000;
    const cpc = campaign.clicks > 0 ? costDollars / campaign.clicks : 0;
    const cpm = campaign.impressions > 0 ? (costDollars * 1000) / campaign.impressions : 0;
    
    // For Mixpanel, use the date string directly to avoid any timezone conversion
    // This ensures the date stays exactly as intended
    const mixpanelTimeString = `${campaign.date}T12:00:00Z`; // Noon UTC
    const mixpanelDateString = `${campaign.date}T12:00:00Z`; // Same format for date field
    
    // For Google Sheets display, create a simple date object
    const dateParts = campaign.date.split('-');
    const year = parseInt(dateParts[0]);
    const month = parseInt(dateParts[1]) - 1;
    const day = parseInt(dateParts[2]);
    const displayDateObj = new Date(year, month, day, 12, 0, 0);
    
    console.log(`Campaign ${campaign.campaignName}: Original date: ${campaign.date}, Mixpanel time/date string: ${mixpanelTimeString}`);
    
    const mixpanelEvent = {
      event: 'Test Event',
      time: mixpanelTimeString, // Use ISO string directly - no timezone conversion
      insertId: `G-${campaign.date}-${campaign.campaignId}`,
      network: 'Google Ads',
      campaignId: campaign.campaignId,
      campaignName: campaign.campaignName,
      date: mixpanelDateString, // Use same ISO string format as Time to avoid timezone conversion
      impressions: campaign.impressions,
      clicks: campaign.clicks,
      spend: Math.round(costDollars * 100) / 100,
      conversions: campaign.conversions,
      cpc: Math.round(cpc * 100) / 100,
      source: 'Google Ads Integration'
    };
    
    mixpanelData.push(mixpanelEvent);
  }
  
  console.log(`✅ Transformed ${mixpanelData.length} events for Mixpanel`);
  return mixpanelData;
}

// =============================================================================
// SHEET WRITING FUNCTIONS
// =============================================================================

function writeDataToSheet(sheetName, data, dataType) {
  try {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    if (!sheet) {
      throw new Error(`Sheet "${sheetName}" not found`);
    }
    
    // Clear existing data (keep headers)
    if (sheet.getLastRow() > 1) {
      sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).clear();
    }
    
    if (data.length === 0) {
      console.log(`No data to write to ${sheetName}`);
      return;
    }
    
    let rows = [];
    
    if (dataType === 'googleAds') {
      rows = data.map(campaign => {
        const costDollars = campaign.costMicros / 1000000;
        const cpc = campaign.clicks > 0 ? costDollars / campaign.clicks : 0;
        const cpm = campaign.impressions > 0 ? (costDollars * 1000) / campaign.impressions : 0;
        const ctr = campaign.impressions > 0 ? (campaign.clicks / campaign.impressions) * 100 : 0;
        const cvr = campaign.clicks > 0 ? (campaign.conversions / campaign.clicks) * 100 : 0;
        
        return [
          campaign.date,
          campaign.campaignId,
          campaign.campaignName,
          campaign.impressions,
          campaign.clicks,
          Math.round(costDollars * 100) / 100,
          campaign.conversions,
          Math.round(cpc * 100) / 100,
          Math.round(cpm * 100) / 100,
          Math.round(ctr * 100) / 100,
          Math.round(cvr * 100) / 100
        ];
      });
    } else if (dataType === 'mixpanel') {
      rows = data.map(event => [
        event.event,
        event.time,
        event.insertId,
        event.network,
        event.campaignId,
        event.campaignName,
        event.date,
        event.impressions,
        event.clicks,
        event.spend,
        event.conversions,
        event.cpc,
        event.source
      ]);
    }
    
    if (rows.length > 0) {
      sheet.getRange(2, 1, rows.length, rows[0].length).setValues(rows);
      console.log(`✅ Wrote ${rows.length} rows to ${sheetName}`);
    }
    
  } catch (error) {
    console.error(`Error writing data to ${sheetName}:`, error);
    throw error;
  }
}

function updateLastRunTimestamp() {
  try {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.SETUP_SHEET);
    const now = new Date();
    const timestamp = Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss');
    sheet.getRange('B8').setValue(timestamp);
  } catch (error) {
    console.error('Error updating last run timestamp:', error);
  }
}

// =============================================================================
// MENU FUNCTIONS
// =============================================================================

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Google Ads Integration')
    .addItem('Initialize Spreadsheet', 'initializeSpreadsheet')
    .addSeparator()
    .addItem('Pull Google Ads Data', 'pullGoogleAdsData')
    .addSeparator()
    .addItem('About', 'showAbout')
    .addToUi();
}

function showAbout() {
  const ui = SpreadsheetApp.getUi();
  ui.alert(
    'Google Ads to Mixpanel Integration',
    'This tool pulls Google Ads campaign data and formats it for Mixpanel import.\n\n' +
    'Instructions:\n' +
    '1. Run "Initialize Spreadsheet" first\n' +
    '2. Set your date range in the Setup sheet\n' +
    '3. Run "Pull Google Ads Data" to fetch and process data\n\n' +
    'The data will be available in both "Google Ads Data" and "Mixpanel" sheets.',
    ui.ButtonSet.OK
  );
}