utils/googleSheetsService.js

const { google } = require('googleapis');
const path = require('path');
const fs = require('fs');

const SHEET_ID = process.env.GOOGLE_SHEET_ID;
const SHEET_NAME = 'Orders'; // The tab name in your Google Sheet

// Path to the service account credentials JSON file
const CREDENTIALS_PATH = path.join(__dirname, '../config/samoulla-490417-daa7176c0091.json');

const getAuth = () => {
  // Option 1: Load from environment variable (useful for production like Vercel/Heroku)
  if (process.env.GOOGLE_SERVICE_ACCOUNT_JSON) {
    try {
      const credentials = JSON.parse(process.env.GOOGLE_SERVICE_ACCOUNT_JSON);
      return new google.auth.GoogleAuth({
        credentials,
        scopes: ['https://www.googleapis.com/auth/spreadsheets'],
      });
    } catch (err) {
      console.error('❌ Failed to parse GOOGLE_SERVICE_ACCOUNT_JSON:', err.message);
    }
  }

  // Option 2: Load from file
  if (fs.existsSync(CREDENTIALS_PATH)) {
    return new google.auth.GoogleAuth({
      keyFile: CREDENTIALS_PATH,
      scopes: ['https://www.googleapis.com/auth/spreadsheets'],
    });
  }

  throw new Error(`Google credentials not found (JSON file missing at ${CREDENTIALS_PATH} and no GOOGLE_SERVICE_ACCOUNT_JSON env var)`);
};

/**
 * Syncs an order with the Google Sheet.
 * If the order doesn't exist (based on Order Number), it appends it.
 * If it exists, it updates the existing row.
 */
const syncOrderWithSheet = async (order) => {
  if (!SHEET_ID) {
    console.warn('⚠️  GOOGLE_SHEET_ID not set in env — skipping Google Sheets sync');
    return;
  }

  try {
    const auth = await getAuth();
    const sheets = google.sheets({ version: 'v4', auth });

    // 1. Format the data row
    const createdAt = order.createdAt
      ? new Date(order.createdAt).toLocaleString('ar-EG', { timeZone: 'Africa/Cairo' })
      : new Date().toLocaleString('ar-EG', { timeZone: 'Africa/Cairo' });

    const row = [
      order.orderNumber ? String(order.orderNumber) : '', // A: رقم الطلب
      order.name || '',                                 // B: الاسم
      order.mobile || '',                               // C: الهاتف
      order.address?.governorate || '',                 // D: المحافظة
      order.address?.city || '',                        // E: المدينة
      order.address?.district || '',                    // F: المنطقة
      order.address?.street || '',                      // G: الشارع
      order.totalPrice || 0,                            // H: الإجمالي
      order.shippingPrice || 0,                         // I: الشحن
      order.payment?.method || '',                      // J: طريقة الدفع
      order.payment?.status || '',                      // K: حالة الدفع
      order.orderStatus || 'created',                   // L: حالة الطلب
      order.source || 'direct',                         // M: المصدر
      order.promoCode || '',                            // N: كود الخصم
      order.discountAmount || 0,                        // O: قيمة الخصم
      order.items?.length || 0,                         // P: عدد المنتجات
      order.items?.map(i => `${i.name} (${i.quantity})`).join(' | ') || '',  // Q: المنتجات
      createdAt,                                        // R: التاريخ
    ];

    // 2. Try to find the existing row by Order Number (Column A)
    const response = await sheets.spreadsheets.values.get({
      spreadsheetId: SHEET_ID,
      range: `${SHEET_NAME}!A:A`,
    });

    const rows = response.data.values || [];
    const orderNumberStr = order.orderNumber ? String(order.orderNumber) : null;
    
    let rowIndex = -1;
    if (orderNumberStr) {
        rowIndex = rows.findIndex(r => r[0] === orderNumberStr) + 1;
    }

    if (rowIndex > 0) {
      // 3a. UPDATE existing row
      await sheets.spreadsheets.values.update({
        spreadsheetId: SHEET_ID,
        range: `${SHEET_NAME}!A${rowIndex}:R${rowIndex}`,
        valueInputOption: 'USER_ENTERED',
        resource: { values: [row] },
      });
      console.log(`🔄 Order synced (Updated): Success for #${order.orderNumber}`);
    } else {
      // 3b. APPEND new row (if not found)
      await sheets.spreadsheets.values.append({
        spreadsheetId: SHEET_ID,
        range: `${SHEET_NAME}!A:R`,
        valueInputOption: 'USER_ENTERED',
        insertDataOption: 'INSERT_ROWS',
        resource: { values: [row] },
      });
      console.log(`✅ Order synced (Appended): Success for #${order.orderNumber}`);
    }
  } catch (error) {
    console.error(`❌ Google Sheets sync FAILED for order #${order.orderNumber || 'unknown'}:`, error.message);
  }
};

module.exports = { 
    syncOrderWithSheet, 
    appendOrderToSheet: syncOrderWithSheet // backwards compatibility
};