sheetsHandler.js

/**
 * Google Sheets integration for tracking Elite Dangerous activities
 */
import { google } from 'googleapis';
import { readFileSync, existsSync } from 'fs';
import * as config from './config.js';

export class SheetsHandler {
    constructor() {
        this.spreadsheetId = config.SPREADSHEET_ID;
        this.credentials = null;
        this.auth = null;
        this.sheets = null;
    }

    /**
     * Authenticate with Google Sheets API
     * @returns {Promise<boolean>}
     */
    async authenticate() {
        try {
            // Try to use service account credentials
            if (existsSync(config.CREDENTIALS_FILE)) {
                const credentialsContent = readFileSync(config.CREDENTIALS_FILE, 'utf8');
                const credentials = JSON.parse(credentialsContent);
                
                this.auth = new google.auth.GoogleAuth({
                    credentials: credentials,
                    scopes: ['https://www.googleapis.com/auth/spreadsheets']
                });
                
                this.sheets = google.sheets({ version: 'v4', auth: this.auth });
                console.log('✓ Authenticated with Google Sheets using service account');
                return true;
            } else {
                console.log('✗ credentials.json not found. Please set up Google Sheets API credentials.');
                return false;
            }
        } catch (error) {
            console.error('✗ Authentication error:', error.message);
            return false;
        }
    }

    /**
     * Connect to the specified spreadsheet
     * @returns {Promise<boolean>}
     */
    async connectSpreadsheet() {
        try {
            const response = await this.sheets.spreadsheets.get({
                spreadsheetId: this.spreadsheetId
            });
            console.log(`✓ Connected to spreadsheet: ${response.data.properties.title}`);
            return true;
        } catch (error) {
            console.error('✗ Error connecting to spreadsheet:', error.message);
            return false;
        }
    }

    /**
     * Create or verify worksheets exist with proper headers
     * @returns {Promise<boolean>}
     */
    async setupWorksheets() {
        try {
            // Get existing sheets
            const spreadsheet = await this.sheets.spreadsheets.get({
                spreadsheetId: this.spreadsheetId
            });
            
            const existingSheets = spreadsheet.data.sheets.map(sheet => sheet.properties.title);
            
            // Setup BGS tracking sheet
            await this._getOrCreateWorksheet(config.WORKSHEETS.BGS, existingSheets);
            await this._ensureHeaders(config.WORKSHEETS.BGS, config.BGS_COLUMNS);
            console.log('✓ Set up BGS tracking sheet');
            
            // Setup Powerplay tracking sheet
            await this._getOrCreateWorksheet(config.WORKSHEETS.POWERPLAY, existingSheets);
            await this._ensureHeaders(config.WORKSHEETS.POWERPLAY, config.POWERPLAY_COLUMNS);
            console.log('✓ Set up Powerplay tracking sheet');
            
            // Setup Summary sheet
            await this._getOrCreateWorksheet(config.WORKSHEETS.SUMMARY, existingSheets);
            console.log('✓ Worksheets ready');
            
            return true;
        } catch (error) {
            console.error('✗ Error setting up worksheets:', error.message);
            return false;
        }
    }

    /**
     * Get existing worksheet or create new one
     * @private
     */
    async _getOrCreateWorksheet(title, existingSheets) {
        if (!existingSheets.includes(title)) {
            await this.sheets.spreadsheets.batchUpdate({
                spreadsheetId: this.spreadsheetId,
                requestBody: {
                    requests: [{
                        addSheet: {
                            properties: {
                                title: title,
                                gridProperties: {
                                    rowCount: 1000,
                                    columnCount: 20
                                }
                            }
                        }
                    }]
                }
            });
            console.log(`✓ Created worksheet: ${title}`);
        }
    }

    /**
     * Ensure worksheet has proper headers
     * @private
     */
    async _ensureHeaders(sheetName, headers) {
        try {
            // Check if first row has data
            const response = await this.sheets.spreadsheets.values.get({
                spreadsheetId: this.spreadsheetId,
                range: `${sheetName}!A1:Z1`
            });
            
            // If no data or wrong headers, write headers
            if (!response.data.values || response.data.values.length === 0) {
                await this.sheets.spreadsheets.values.update({
                    spreadsheetId: this.spreadsheetId,
                    range: `${sheetName}!A1`,
                    valueInputOption: 'RAW',
                    requestBody: {
                        values: [headers]
                    }
                });
            }
        } catch (error) {
            console.error(`Error ensuring headers for ${sheetName}:`, error.message);
        }
    }

    /**
     * Append BGS data to tracking sheet
     * @param {Array} data - Row data to append
     * @returns {Promise<boolean>}
     */
    async appendBGSData(data) {
        try {
            await this.sheets.spreadsheets.values.append({
                spreadsheetId: this.spreadsheetId,
                range: `${config.WORKSHEETS.BGS}!A:N`,
                valueInputOption: 'RAW',
                requestBody: {
                    values: [data]
                }
            });
            console.log(`✓ Added BGS data for ${data[1]} in ${data[2]}`);
            return true;
        } catch (error) {
            console.error('✗ Error appending BGS data:', error.message);
            return false;
        }
    }

    /**
     * Append Powerplay data to tracking sheet
     * @param {Array} data - Row data to append
     * @returns {Promise<boolean>}
     */
    async appendPowerplayData(data) {
        try {
            await this.sheets.spreadsheets.values.append({
                spreadsheetId: this.spreadsheetId,
                range: `${config.WORKSHEETS.POWERPLAY}!A:F`,
                valueInputOption: 'RAW',
                requestBody: {
                    values: [data]
                }
            });
            console.log(`✓ Added Powerplay data for ${data[1]}: ${data[3]} merits`);
            return true;
        } catch (error) {
            console.error('✗ Error appending Powerplay data:', error.message);
            return false;
        }
    }

    /**
     * Get summary statistics for a commander
     * @param {string} commanderName - Commander name to look up
     * @returns {Promise<Object|null>}
     */
    async getCommanderSummary(commanderName) {
        try {
            const summary = {
                bgs: { total_inf: 0, total_bounties: 0, systems: new Set() },
                powerplay: { total_merits: 0, systems: new Set() }
            };
            
            // Get BGS data
            const bgsResponse = await this.sheets.spreadsheets.values.get({
                spreadsheetId: this.spreadsheetId,
                range: `${config.WORKSHEETS.BGS}!A2:N`
            });
            
            if (bgsResponse.data.values) {
                for (const row of bgsResponse.data.values) {
                    if (row[1] && row[1].toLowerCase() === commanderName.toLowerCase()) {
                        summary.bgs.total_inf += parseInt(row[4]) || 0;
                        summary.bgs.total_bounties += parseInt(row[5]) || 0;
                        if (row[2]) {
                            summary.bgs.systems.add(row[2]);
                        }
                    }
                }
            }
            
            // Get Powerplay data
            const ppResponse = await this.sheets.spreadsheets.values.get({
                spreadsheetId: this.spreadsheetId,
                range: `${config.WORKSHEETS.POWERPLAY}!A2:F`
            });
            
            if (ppResponse.data.values) {
                for (const row of ppResponse.data.values) {
                    if (row[1] && row[1].toLowerCase() === commanderName.toLowerCase()) {
                        summary.powerplay.total_merits += parseInt(row[3]) || 0;
                        if (row[2]) {
                            summary.powerplay.systems.add(row[2]);
                        }
                    }
                }
            }
            
            // Convert sets to arrays
            summary.bgs.systems = Array.from(summary.bgs.systems);
            summary.powerplay.systems = Array.from(summary.powerplay.systems);
            
            return summary;
        } catch (error) {
            console.error('✗ Error getting commander summary:', error.message);
            return null;
        }
    }
}