const { google } = require('googleapis');
const { v4: uuidv4 } = require('uuid');

// Replace with your Google Sheets ID and Sheet Name
const spreadsheetId = '1_TJeEsTmen7vMDQCpVckyRGVhlfdSnR-FCLnjyNuSck';
const sheetName = 'Sheet1';

async function getGoogleSheetsClient() {
    const auth = new google.auth.GoogleAuth({
        keyFile: 'credentials.json', // Update the path to your credentials
        scopes: ['https://www.googleapis.com/auth/spreadsheets'],
    });

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

async function findAll() {
    const googleSheets = await getGoogleSheetsClient();
    const response = await googleSheets.spreadsheets.values.get({
        spreadsheetId,
        range: `${sheetName}`,
    });
    return response.data.values || [];
}

async function findById(id) {
    const data = await findAll();
    return data.find(row => row[0] === id) || null;
}

async function insert(data) {
    const googleSheets = await getGoogleSheetsClient();
    const id = uuidv4();
    const values = [id, ...Object.values(data)];   

    await googleSheets.spreadsheets.values.append({
        spreadsheetId,
        range: `${sheetName}`,
        valueInputOption: 'USER_ENTERED',
        resource: { values: [values] },
    });

    return { id, ...data };
}

async function updateById(id, newData) {
    const googleSheets = await getGoogleSheetsClient();
    const existingData = await findAll();
    const rowNumber = existingData.findIndex(row => row[0] === id);

    if (rowNumber === -1) {
        return null;
    }

    // Create an array that represents the updated row.
    // Ensure the ID is preserved and newData is placed correctly.
    const updatedRow = [id, ...Object.values(newData)];

    const range = `${sheetName}!A${rowNumber + 1}`; // Assuming the ID is in column A
    await googleSheets.spreadsheets.values.update({
        spreadsheetId,
        range,
        valueInputOption: 'USER_ENTERED',
        resource: { values: [updatedRow] },
    });

    return { id, ...newData };
}


async function deleteById(id) {
    const googleSheets = await getGoogleSheetsClient();
    const existingData = await findAll();
    const rowNumber = existingData.findIndex(row => row[0] === id);

    if (rowNumber === -1) {
        return false;
    }

    const range = `${sheetName}!A${rowNumber + 1}:Z${rowNumber + 1}`;
    await googleSheets.spreadsheets.values.clear({
        spreadsheetId,
        range,
    });

    return true;
}

async function createNewSheet(sheetTitle) {
    const googleSheets = await getGoogleSheetsClient();
    
    const request = {
        spreadsheetId,
        resource: {
            requests: [{
                addSheet: {
                    properties: {
                        title: sheetTitle
                    }
                }
            }]
        }
    };

    await googleSheets.spreadsheets.batchUpdate(request);
    return `Sheet ${sheetTitle} created`;
}
async function getSheetNames() {
    const googleSheets = await getGoogleSheetsClient();
    const response = await googleSheets.spreadsheets.get({
        spreadsheetId,
    });

    const sheets = response.data.sheets;
    return sheets.map(sheet => sheet.properties.title);
}
async function deleteSheet(sheetName) {
    const googleSheets = await getGoogleSheetsClient();

    // First, get the ID of the sheet you want to delete
    const spreadsheetData = await googleSheets.spreadsheets.get({
        spreadsheetId,
    });

    const sheet = spreadsheetData.data.sheets.find(s => s.properties.title === sheetName);
    if (!sheet) {
        throw new Error(`Sheet with name "${sheetName}" not found`);
    }

    const sheetId = sheet.properties.sheetId;

    // Now, delete the sheet with the found ID
    await googleSheets.spreadsheets.batchUpdate({
        spreadsheetId,
        resource: {
            requests: [
                {
                    deleteSheet: {
                        sheetId,
                    },
                },
            ],
        },
    });

    return `Sheet "${sheetName}" deleted`;
}



// Export the functions for use in other files
module.exports = { findAll, findById, insert, updateById, deleteById, createNewSheet, getSheetNames, deleteSheet };