path: root/index.js

import express from 'express';
import { MongoClient } from 'mongodb';
import { google } from 'googleapis';
import dotenv from 'dotenv'; 
import fs from 'fs'; 
import cors from 'cors';

dotenv.config();  // Load .env variables

const app = express();
const port = process.env.PORT;

app.use(express.json());
app.use(cors());

// Mongo URI from .env
const uri = process.env.MONGO_URI;
const client = new MongoClient(uri);
const dbName = 'inventory';
const collectionName = 'items';

// Load Google credentials from the key.json file
const googleCredentialsPath = process.env.GOOGLE_CREDENTIALS_PATH;
const googleApisKey = JSON.parse(fs.readFileSync(googleCredentialsPath, 'utf8'));

// Initialize Google Sheets API authentication
const auth = new google.auth.JWT(
  googleApisKey.client_email,
  null,
  googleApisKey.private_key,
  ['https://www.googleapis.com/auth/spreadsheets']
);

const spreadsheetId = process.env.SPREADSHEET_ID;
const sheets = google.sheets({ version: 'v4', auth });
const range = 'Sheet1!A:I';

// Connect to MongoDB
async function connectDB() {
    try {
        await client.connect();
        console.log('Connected to MongoDB!');
    } catch (err) {
        console.error('Error connecting to MongoDB:', err);
        process.exit(1);
    }
}

connectDB();

async function generateSerialNumber() {
    const db = client.db(dbName);
    const collection = db.collection(collectionName);

    const year = new Date().getFullYear().toString().slice(-2); // Get last two digits of the year

    const latestDevice = await collection.find({ serialNumber: { $regex: `^JPC${year}` } })
        .sort({ serialNumber: -1 })
        .limit(1)
        .toArray();

    let nextNumber = 1; // Default start

    if (latestDevice.length > 0) {
        const lastSerial = latestDevice[0].serialNumber;
        const lastNumber = parseInt(lastSerial.substring(5, 11), 10); // Extract last 6 digits
        nextNumber = lastNumber + 1;
    }

    return `JPC${year}${String(nextNumber).padStart(6, '0')}`;
}

app.post('/upload', async (req, res) => {
    const { Verient, mac, os, status, productionNumber, customerAddress, customerNumber, customerName } = req.body;
  
    if (!Verient || !mac || !os || !productionNumber) {
        return res.status(400).send('Missing required fields: Verient, mac, os, or status');
    }
  
    try {
        const db = client.db(dbName);
        const collection = db.collection(collectionName);
  
        const existingDevice = await collection.findOne({ mac });
  
        if (existingDevice) {
            return res.status(400).send(`Device with the same MAC address is already registered, ${existingDevice.serialNumber}`);
        }

        const serialNumber = await generateSerialNumber();
        const customerNumbers = `+88${customerNumber}`;
  
        const newItem = {
            serialNumber,
            Verient,
            mac,
            os,
            status: status || 'registered',
            customerName,
            customerNumber: customerNumbers,
            customerAddress,
            productionNumber
        };
  
        await collection.insertOne(newItem);
        console.log('Item successfully added to MongoDB');
  
        try {
            const response = await sheets.spreadsheets.values.append({
                spreadsheetId,
                range,
                valueInputOption: 'USER_ENTERED',
                requestBody: {
                    values: [[serialNumber, Verient, mac, os, status, customerName, customerNumbers, customerAddress, productionNumber]],
                },
            });
            console.log('Data successfully appended to Google Sheets');
            res.status(200).send('Device uploaded and added to MongoDB and Google Sheets. Serial Number: ' + serialNumber);
        } catch (err) {
            console.error('Error appending to Google Sheets:', err);
            res.status(500).send('Error uploading to Google Sheets');
        }
    } catch (err) {
        console.error('Error inserting item into MongoDB:', err);
        res.status(500).send('Error uploading item to MongoDB');
    }
});

app.get('/check-status', async (req, res) => {
    const { mac, serialNumber } = req.query;

    if (!mac && !serialNumber) {
        return res.status(400).send('MAC or Serial Number required');
    }

    try {
        const db = client.db(dbName);
        const collection = db.collection(collectionName);
        const device = await collection.findOne(mac ? { mac } : { serialNumber });

        if (!device) {
            return res.status(200).json({
                serialNumber: null,
                status: 'new'  // Default status if not found
            });
        }

        res.status(200).json({
            serialNumber: device.serialNumber,
            status: device.status  // Default status if missing
        });
    } catch (err) {
        res.status(500).send('Error checking device status');
    }
});


app.get('/check-sn', async (req, res) => {
    const { serialNumber } = req.query;

    if (!serialNumber) {
        return res.status(400).json({ error: 'Serial Number required' });
    }

    try {
        const db = client.db(dbName);
        const collection = db.collection(collectionName);
        let device;
        if (serialNumber.startsWith("JPC")){
            device = await collection.findOne({ serialNumber });
        }else{
            device = await collection.findOne({productionNumber:serialNumber})
        }

        if (!device) {
            return res.status(200).json({
                serialNumber: null,
                status: '',
                customerName:null ,  // Default status if not found
            });
        }

        res.status(200).json({
            serialNumber: device.serialNumber,  // Keep serial number consistent
            status: device.status, // Handle missing status field
            customerName: device.customerName , // Handle missing customerName
        });
    } catch (err) {
        console.error('Error checking device status:', err);
        res.status(500).json({ error: 'Error checking device status' });
    }
});

app.put('/update', async (req, res) => {
    const { serialNumber, Verient, mac, os, status, customerName, customerNumber, customerAddress } = req.body;

    if (!serialNumber) {
        return res.status(400).json({ error: 'Serial Number is required' });
    }

    try {
        const db = client.db(dbName);
        const collection = db.collection(collectionName);
        let existingDevice;
        if (serialNumber.startsWith("JPC")){
            existingDevice = await collection.findOne({ serialNumber });
        }else{
            existingDevice = await collection.findOne({productionNumber:serialNumber})
        }

        if (!existingDevice) {
            return res.status(404).json({ error: 'Device not found' });
        }

        if (mac && mac !== existingDevice.mac) {
            const duplicateMac = await collection.findOne({ mac });
            if (duplicateMac) {
                return res.status(400).json({ error: 'MAC address is already registered' });
            }
        }

        // Update the database entry
        const updatedData = {
            ...(Verient && { Verient }),
            ...(mac && { mac }),
            ...(os && { os }),
            ...(status && { status }),
            ...(customerName && { customerName }),
            ...(customerNumber && { customerNumber }),
            ...(customerAddress && { customerAddress }),
        };

        await collection.updateOne({ serialNumber:existingDevice.serialNumber }, { $set: updatedData });

        console.log('Item successfully updated in MongoDB');

        // Update in Google Sheets
        try {
            const sheetData = await sheets.spreadsheets.values.get({ spreadsheetId, range });
            const rows = sheetData.data.values;
            const rowIndex = rows.findIndex(row => row[0] === existingDevice.serialNumber);

            if (rowIndex !== -1) {
                const updatedRow = [
                    existingDevice.serialNumber,
                    Verient || rows[rowIndex][1],
                    mac || rows[rowIndex][2],
                    os || rows[rowIndex][3],
                    status || rows[rowIndex][4],
                    customerName || rows[rowIndex][5],
                    customerNumber || rows[rowIndex][6],
                    customerAddress || rows[rowIndex][7],
                ];

                await sheets.spreadsheets.values.update({
                    spreadsheetId,
                    range: `Sheet1!A${rowIndex + 1}:H${rowIndex + 1}`,
                    valueInputOption: 'USER_ENTERED',
                    requestBody: { values: [updatedRow] },
                });

                console.log('Data successfully updated in Google Sheets');
            }

            res.status(200).json({ message: 'Device updated successfully' });
        } catch (err) {
            console.error('Error updating Google Sheets:', err);
            res.status(500).json({ error: 'Error updating Google Sheets' });
        }
    } catch (err) {
        console.error('Error updating item in MongoDB:', err);
        res.status(500).json({ error: 'Error updating device in MongoDB' });
    }
});



// Start the server
app.listen(port, () => console.log(`Server running on http://localhost:${port}`));