sheets.js

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

class SheetsModule {
    constructor() {
        // Fallback to the ID explicitly provided by the user if the env variable isn't attached to Hugging Face Secrets yet
        this.sheetId = process.env.GOOGLE_SHEETS_ID || '1dY59KqOVfvP5-SnGOELfWY-4oyOWPHR6wIsuJ_0_W50';
        this.authJson = JSON.parse(process.env.GOOGLE_SERVICE_ACCOUNT_JSON || '{}');
    }

    async getClient() {
        if (!this.authJson.client_email) return null;
        
        const auth = new google.auth.GoogleAuth({
            credentials: this.authJson,
            scopes: ['https://www.googleapis.com/auth/spreadsheets']
        });
        return await auth.getClient();
    }

    async initializeSheet() {
        if (!this.sheetId) return { success: false, error: 'GOOGLE_SHEETS_ID not set' };

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

            // 1. Check if '📅 Calendar' tab exists
            const spreadsheet = await sheets.spreadsheets.get({
                spreadsheetId: this.sheetId
            });

            const calendarTab = spreadsheet.data.sheets.find(s => s.properties.title === '📅 Calendar');
            if (!calendarTab) {
                // Create tab + headers
                await sheets.spreadsheets.batchUpdate({
                    spreadsheetId: this.sheetId,
                    requestBody: {
                        requests: [
                            { addSheet: { properties: { title: '📅 Calendar' } } }
                        ]
                    }
                });

                const headers = ['ID', 'Date', 'Platform', 'Status', 'Caption EN', 'Caption ID', 'Image URL', 'Scheduled For', 'Zernio Post ID', 'Likes', 'Comments', 'Shares'];
                await sheets.spreadsheets.values.update({
                    spreadsheetId: this.sheetId,
                    range: '📅 Calendar!A1:L1',
                    valueInputOption: 'RAW',
                    requestBody: { values: [headers] }
                });
            }

            // 2. Bonus: Create '📊 Analytics' tab for trending data
            const analyticsTab = spreadsheet.data.sheets.find(s => s.properties.title === '📊 Analytics');
            if (!analyticsTab) {
                await sheets.spreadsheets.batchUpdate({
                    spreadsheetId: this.sheetId,
                    requestBody: {
                        requests: [{ addSheet: { properties: { title: '📊 Analytics' } } }]
                    }
                });
                await sheets.spreadsheets.values.update({
                    spreadsheetId: this.sheetId,
                    range: '📊 Analytics!A1:D1',
                    valueInputOption: 'RAW',
                    requestBody: { values: [['Platform', 'Total Posts', 'Avg Likes', 'Followers']] }
                });
            }

            return { 
                success: true, 
                url: `https://docs.google.com/spreadsheets/d/${this.sheetId}/edit` 
            };
        } catch (error) {
            console.error('[Sheets] Init failed:', error.message);
            return { success: false, error: error.message };
        }
    }

    async appendPost(rowData) {
        if (!this.sheetId) return { success: false, error: 'GOOGLE_SHEETS_ID not set' };

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

            // rowData: [ID, Date, Platform, Status, CaptionEN, CaptionID, ImageURL, ScheduledTime, ZernioID, ...]
            await sheets.spreadsheets.values.append({
                spreadsheetId: this.sheetId,
                range: '📅 Calendar!A:L',
                valueInputOption: 'RAW',
                insertDataOption: 'INSERT_ROWS',
                requestBody: { values: [rowData] }
            });
            return { success: true };
        } catch (error) {
            console.error('[Sheets] Append failed:', error.message);
            return { success: false, error: error.message };
        }
    }

    async updatePostStatus(zernioPostId, status, analytics = {}) {
        if (!this.sheetId) return { success: false, error: 'GOOGLE_SHEETS_ID not set' };

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

            // 1. Find the row by Zernio Post ID (Column I = Index 8)
            const response = await sheets.spreadsheets.values.get({
                spreadsheetId: this.sheetId,
                range: '📅 Calendar!A:L'
            });

            const rows = response.data.values;
            const rowIndex = rows.findIndex(r => r[8] === zernioPostId);
            if (rowIndex === -1) return { success: false, error: 'Post ID not found in sheet' };

            const realIndex = rowIndex + 1; // 1-indexed for range

            // 2. Update status (Column D = index 3) and analytics (J,K,L = 9,10,11)
            await sheets.spreadsheets.values.update({
                spreadsheetId: this.sheetId,
                range: `📅 Calendar!D${realIndex}:L${realIndex}`,
                valueInputOption: 'RAW',
                requestBody: {
                    values: [[
                        status,
                        rows[rowIndex][4], rows[rowIndex][5], rows[rowIndex][6], rows[rowIndex][7], rows[rowIndex][8],
                        analytics.likes || '-',
                        analytics.comments || '-',
                        analytics.shares || '-'
                    ]]
                }
            });

            return { success: true };
        } catch (error) {
            console.error('[Sheets] Update failed:', error.message);
            return { success: false, error: error.message };
        }
    }
    async appendSale(txn, offer) {
        try {
            const client = await this.getClient();
            if (!client) return { success: false, error: 'No Google auth' };
            const sheets = google.sheets({ version: 'v4', auth: client });

            // Ensure Revenue tab exists
            const spreadsheet = await sheets.spreadsheets.get({ spreadsheetId: this.sheetId });
            const revenueTab = spreadsheet.data.sheets.find(s => s.properties.title === '💰 Revenue');
            if (!revenueTab) {
                await sheets.spreadsheets.batchUpdate({
                    spreadsheetId: this.sheetId,
                    requestBody: { requests: [{ addSheet: { properties: { title: '💰 Revenue' } } }] }
                });
                // Add headers
                await sheets.spreadsheets.values.update({
                    spreadsheetId: this.sheetId,
                    range: '💰 Revenue!A1:G1',
                    valueInputOption: 'RAW',
                    requestBody: { values: [['Date', 'Transaction ID', 'Offer', 'Amount (IDR)', 'Buyer', 'Pillar', 'Offer ID']] }
                });
            }

            // Append sale row
            await sheets.spreadsheets.values.append({
                spreadsheetId: this.sheetId,
                range: '💰 Revenue!A:G',
                valueInputOption: 'RAW',
                requestBody: {
                    values: [[
                        txn.ts,
                        txn.id,
                        offer.title,
                        txn.amount,
                        txn.buyerEmail || 'Anonymous',
                        offer.sourcePillar || 'general',
                        offer.id
                    ]]
                }
            });

            return { success: true };
        } catch (error) {
            console.error('[Sheets] appendSale failed:', error.message);
            return { success: false, error: error.message };
        }
    }
}

module.exports = new SheetsModule();