← back to Wine Finder

services/googleSheets.js

257 lines

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

class GoogleSheetsService {
  constructor() {
    this.sheets = null;
    this.auth = null;
    this.spreadsheetId = process.env.GOOGLE_SHEET_ID;
    this.sheetName = 'Wine Tracker'; // Default sheet name
  }

  // Initialize Google Sheets API
  async initialize() {
    try {
      // Load credentials from file
      const credentialsPath = process.env.GOOGLE_APPLICATION_CREDENTIALS || './google-credentials.json';

      if (!fs.existsSync(credentialsPath)) {
        console.warn('Google credentials file not found. Google Sheets integration disabled.');
        return false;
      }

      const credentials = JSON.parse(fs.readFileSync(credentialsPath, 'utf8'));

      // Create auth client
      this.auth = new google.auth.GoogleAuth({
        credentials,
        scopes: ['https://www.googleapis.com/auth/spreadsheets']
      });

      const authClient = await this.auth.getClient();
      this.sheets = google.sheets({ version: 'v4', auth: authClient });

      console.log('✅ Google Sheets API initialized successfully');
      return true;

    } catch (error) {
      console.error('Failed to initialize Google Sheets API:', error.message);
      return false;
    }
  }

  // Ensure the sheet has proper headers
  async ensureHeaders() {
    try {
      if (!this.sheets) {
        await this.initialize();
      }

      if (!this.sheets) {
        return false;
      }

      // Check if headers exist
      const response = await this.sheets.spreadsheets.values.get({
        spreadsheetId: this.spreadsheetId,
        range: `${this.sheetName}!A1:I1`
      });

      // If no data or headers don't match, write headers
      if (!response.data.values || response.data.values.length === 0) {
        await this.sheets.spreadsheets.values.update({
          spreadsheetId: this.spreadsheetId,
          range: `${this.sheetName}!A1:I1`,
          valueInputOption: 'RAW',
          resource: {
            values: [[
              'Timestamp',
              'Wine Name',
              'Vintage',
              'Price',
              'Rating',
              'Source',
              'Availability',
              'URL',
              'Search Query'
            ]]
          }
        });

        console.log('✅ Headers created in Google Sheet');
      }

      return true;

    } catch (error) {
      console.error('Error ensuring headers:', error.message);
      return false;
    }
  }

  // Log wine data to Google Sheets
  async logWineData(wineData) {
    try {
      if (!this.sheets) {
        const initialized = await this.initialize();
        if (!initialized) {
          return { success: false, error: 'Google Sheets not initialized' };
        }
      }

      await this.ensureHeaders();

      // Prepare row data
      const row = [
        new Date().toISOString(),
        wineData.name || '',
        wineData.vintage || '',
        wineData.price || '',
        wineData.rating || '',
        wineData.source || 'K&L Wine Merchants',
        wineData.availability || '',
        wineData.url || '',
        wineData.searchQuery || ''
      ];

      // Append to sheet
      await this.sheets.spreadsheets.values.append({
        spreadsheetId: this.spreadsheetId,
        range: `${this.sheetName}!A:I`,
        valueInputOption: 'RAW',
        insertDataOption: 'INSERT_ROWS',
        resource: {
          values: [row]
        }
      });

      console.log(`✅ Logged wine data: ${wineData.name}`);
      return { success: true };

    } catch (error) {
      console.error('Error logging wine data:', error.message);
      return { success: false, error: error.message };
    }
  }

  // Bulk log multiple wines
  async logMultipleWines(wines) {
    try {
      if (!wines || wines.length === 0) {
        return { success: true, count: 0 };
      }

      if (!this.sheets) {
        const initialized = await this.initialize();
        if (!initialized) {
          return { success: false, error: 'Google Sheets not initialized' };
        }
      }

      await this.ensureHeaders();

      // Prepare rows
      const rows = wines.map(wine => [
        new Date().toISOString(),
        wine.name || '',
        wine.vintage || '',
        wine.price || '',
        wine.rating || '',
        wine.source || 'K&L Wine Merchants',
        wine.availability || '',
        wine.url || '',
        wine.searchQuery || ''
      ]);

      // Append all rows at once
      await this.sheets.spreadsheets.values.append({
        spreadsheetId: this.spreadsheetId,
        range: `${this.sheetName}!A:I`,
        valueInputOption: 'RAW',
        insertDataOption: 'INSERT_ROWS',
        resource: {
          values: rows
        }
      });

      console.log(`✅ Logged ${wines.length} wines to Google Sheets`);
      return { success: true, count: wines.length };

    } catch (error) {
      console.error('Error logging multiple wines:', error.message);
      return { success: false, error: error.message };
    }
  }

  // Get historical data for a specific wine
  async getWineHistory(wineName) {
    try {
      if (!this.sheets) {
        const initialized = await this.initialize();
        if (!initialized) {
          return { success: false, error: 'Google Sheets not initialized' };
        }
      }

      // Get all data
      const response = await this.sheets.spreadsheets.values.get({
        spreadsheetId: this.spreadsheetId,
        range: `${this.sheetName}!A:I`
      });

      const rows = response.data.values || [];
      if (rows.length <= 1) {
        return { success: true, history: [] };
      }

      // Filter rows for this wine (skip header)
      const history = [];
      for (let i = 1; i < rows.length; i++) {
        const row = rows[i];
        const rowWineName = row[1] || '';

        // Case-insensitive partial match
        if (rowWineName.toLowerCase().includes(wineName.toLowerCase())) {
          history.push({
            timestamp: row[0],
            name: row[1],
            vintage: row[2],
            price: parseFloat(row[3]) || 0,
            rating: row[4],
            source: row[5],
            availability: row[6],
            url: row[7],
            searchQuery: row[8]
          });
        }
      }

      return { success: true, history };

    } catch (error) {
      console.error('Error getting wine history:', error.message);
      return { success: false, error: error.message };
    }
  }

  // Get last recorded price for a wine
  async getLastPrice(wineName) {
    try {
      const result = await this.getWineHistory(wineName);
      if (!result.success || result.history.length === 0) {
        return null;
      }

      // Return most recent entry
      return result.history[result.history.length - 1];

    } catch (error) {
      console.error('Error getting last price:', error.message);
      return null;
    }
  }
}

module.exports = new GoogleSheetsService();