i have cfeated a serach engine using keyword for a folder having pdfs ...............i stored the extarcted text from the pdfs into SSMS tables by performing ocr as they are scannned and in nested folders ....now thorugh frontend i send a keyowrd to our bcakend search api ....which provides us filename and page no for the matches ...snippet also .....but the sql search query is returning the filename sin backend within 5 mins but the processing of these pdfs for keyowrd positoin is taking up the time by reading the whole pdfs which is redundant time ....so telll me how to reduce this redundant time


To reduce the redundant time spent on reading entire PDFs during the keyword search, you can optimize your search process with several strategies. Here are some recommendations to help improve the efficiency of your search engine:

1. Indexing Extracted Text:
Full-Text Indexing: Utilize full-text indexing available in SQL Server. This allows you to create an index on the extracted text, enabling faster keyword searches without needing to read each PDF again.
Create a full-text catalog and full-text indexes on your text columns. This will improve the search performance since SQL Server will utilize its indexing capabilities.
2. Maintain Keyword Mapping:
Keyword Position Tracking: During the text extraction process, maintain a mapping of keyword positions (filename, page number, text snippet) in a table. This way, the search can retrieve results directly from this mapping without reading the PDFs again.
You can structure this table to store filename, page number, keyword, and snippet, allowing quick lookups.
      how to implemnt these two points for const fs = require('fs').promises;
const path = require('path');
const sql = require('mssql');
const pdf = require('pdf-parse');
const { spawn } = require('child_process');
const Tesseract = require('tesseract.js');
const mammoth = require('mammoth');

const sqlconfig = {
    server: '192.168.13.30',
    database: 'ocr', 
    user: 'sa',
    password: 'sa@12345',
    options: {
        encrypt: false,
        trustServerCertificate: true,
        trustedConnection: true
    }
};

const pdfDirectory = '\\\\192.168.13.30\\c$\\GGN Office Repository';  


async function getAllFiles(dir) {
    let filesArray = [];
    try {
        const files = await fs.readdir(dir);
        for (const file of files) {
            const fullPath = path.join(dir, file);
            const stat = await fs.stat(fullPath);
            if (stat.isDirectory()) {
                const subDirFiles = await getAllFiles(fullPath);
                filesArray = filesArray.concat(subDirFiles);
            } else if (file.endsWith('.pdf') || file.endsWith('.docx')) {
                filesArray.push(fullPath);
            }
        }
    } catch (err) {
        console.error('Error reading directory:', err);
    }
    return filesArray;
}


async function convertPdfToImage(pdfPath) {
    return new Promise((resolve, reject) => {
        const outputPrefix = path.join(__dirname, 'output', path.basename(pdfPath, '.pdf')); 
        const convertProcess = spawn('pdftoppm', [pdfPath, outputPrefix, '-png']);

        convertProcess.stdout.on('data', (data) => {
            console.log(`stdout: ${data}`);
        });

        convertProcess.stderr.on('data', (data) => {
            console.error(`stderr: ${data}`);
        });

        convertProcess.on('close', (code) => {
            if (code !== 0) {
                return reject(new Error('Failed to convert PDF to image'));
            }
            console.log(`PDF converted to image at ${outputPrefix}-1.png`);
            resolve(`${outputPrefix}-1.png`);  
        });
    });
}


async function performOcr(imagePath) {
    return new Promise((resolve, reject) => {
        Tesseract.recognize(
            imagePath,
            'eng', 
            {
                logger: (m) => console.log(m), 
            }
        )
        .then(({ data: { text } }) => {
            resolve(text);
        })
        .catch((err) => reject(err));
    });
}


async function extractPdfText(pdfData) {
    const pdfText = await pdf(pdfData);
    const text = pdfText.text;

   
    if (!text.trim()) {
        console.log('No extractable text found. Attempting OCR...');
        return null; 

    return text;
}

// Function to handle DOCX text extraction
async function extractDocxText(docxPath) {
    try {
        const docxData = await fs.readFile(docxPath);
        const result = await mammoth.extractRawText({ buffer: docxData });
        return result.value;
    } catch (err) {
        console.error(`Error extracting text from DOCX file ${docxPath}:`, err);
        return null;
    }
}

// Function to insert the file data into SQL database
async function insertFileData(filename, text) {
    try {
        await sql.query`INSERT INTO dbo.files (Filename, PdfData) VALUES (${filename}, ${text})`;
    } catch (err) {
        console.error('Error inserting file into database:', err);
    }
}

// Function to process PDFs and DOCX files
async function preloadFiles() {
    try {
        await sql.connect(sqlconfig);
        const allFiles = await getAllFiles(pdfDirectory);  // Get both PDF and DOCX files
        let uploadedCount = 0;

        for (const file of allFiles) {
            try {
                const fileStat = await fs.stat(file);
                if (fileStat.size === 0) {
                    console.warn(`Skipping empty file: ${file}`);
                    continue;
                }

                let fileText = null;

                // Process PDF files
                if (file.endsWith('.pdf')) {
                    const pdfData = await fs.readFile(file);

                    // Try to extract text first from PDF
                    fileText = await extractPdfText(pdfData);

                    // If no text found (scanned PDF), apply OCR
                    if (!fileText) {
                        const imagePath = await convertPdfToImage(file);  // Convert to image
                        fileText = await performOcr(imagePath);  // Perform OCR on image
                    }
                }

                // Process DOCX files
                if (file.endsWith('.docx') && !fileText) {
                    fileText = await extractDocxText(file);
                }

                // If text is extracted (either from PDF or DOCX), insert into DB
                if (fileText) {
                    await insertFileData(file, fileText);
                    uploadedCount++;
                } else {
                    console.warn(`No text extracted from ${file}`);
                }

            } catch (err) {
                console.error(`Error processing file ${file}:`, err);
            }
        }

        console.log(`All files have been processed and stored in the database. Total files uploaded: ${uploadedCount}`);
    } catch (err) {
        console.error('Error pre-loading files:', err);
    } finally {
        await sql.close();
    }
}

preloadFiles();

}
  this is pdf loading operation and  const express = require('express');
const sql = require('mssql');
const cors = require('cors');
const NodeCache = require('node-cache');
const fs = require('fs').promises;
const path = require('path');
const pdfParse = require('pdf-parse');
const connectDB = require('../config/db');
const authRoutes = require("../src/routes/auth");
const LibraryRoute = require("../src/routes/Library");

const app = express();
app.use(cors());
app.use(express.json());
connectDB();

app.use("/api/auth", authRoutes);
app.use("/api/lib", LibraryRoute);

const sqlconfig = {
    server: '192.168.2.5',
    database: 'ocr',
    user: 'sa',
    password: 'sa@12345',
    options: {
        encrypt: false,
        trustServerCertificate: true,
    },
    pool: {
        max: 10,
        min: 0,
        idleTimeoutMillis: 30000,
    },
    requestTimeout: 50000,
};

// Caching
const searchCache = new NodeCache({
    stdTTL: 432000,
    checkperiod: 600,
});

const pool = new sql.ConnectionPool(sqlconfig);
const poolConnect = pool.connect();

pool.on('error', (err) => {
    console.error('SQL Pool Error: ', err);
});

// Get relative path from the full path
const getRelativePath = (fullPath) => {
    const basePath = '\\\\192.168.2.5\\c$\\GGN Office Repository\\';
    return fullPath.startsWith(basePath)
        ? fullPath.slice(basePath.length).replace(/\\/g, '/')
        : fullPath.replace(/\\/g, '/');
};

// Get a snippet of text around the keyword
const getSnippet = (text, keyword, contextLength = 100) => {
    const lowerText = text.toLowerCase();
    const lowerKeyword = keyword.toLowerCase();
    const startIndex = lowerText.indexOf(lowerKeyword);

    if (startIndex === -1) return null;

    const snippetStart = Math.max(0, startIndex - contextLength);
    const snippetEnd = Math.min(text.length, startIndex + keyword.length + contextLength);
    const snippet = text.substring(snippetStart, snippetEnd);

    return `...${snippet}...`;
};

// Extract keyword matches from a PDF
const extractKeywordMatches = async (pdfPath, keywords) => {
    console.log(`Starting extraction of keywords in: ${pdfPath}`);
    let matches = [];

    try {
        await fs.access(pdfPath);  // Check if the PDF file exists
        const dataBuffer = await fs.readFile(pdfPath);
        console.log(`Loaded PDF file: ${pdfPath}, Size: ${dataBuffer.length} bytes`);

        if (path.extname(pdfPath).toLowerCase() !== '.pdf') {
            console.warn(`File is not a PDF: ${pdfPath}`);
            return [];  // Return empty array on non-PDF file
        }

        const pdfData = await pdfParse(dataBuffer);
        const pages = pdfData.text.split('\n\n');  // Assuming pages are split by \n\n
        console.log(`PDF has ${pages.length} pages. Starting keyword search...`);

        for (let pageIndex = 0; pageIndex < pages.length; pageIndex++) {
            const pageText = pages[pageIndex];
            console.log(`Processing Page ${pageIndex + 1}: ${pageText.substring(0, 50)}...`);

            keywords.forEach(keyword => {
                if (pageText.toLowerCase().includes(keyword.toLowerCase())) {
                    const snippet = getSnippet(pageText, keyword, 100);
                    console.log(`Keyword "${keyword}" found on page ${pageIndex + 1}. Snippet: ${snippet}`);
                    matches.push({
                        page: pageIndex,
                        keyword,
                        snippet,
                    });
                } else {
                    console.log(`Keyword "${keyword}" not found on page ${pageIndex + 1}.`);
                }
            });
        }
    } catch (error) {
        console.error(`Error extracting keywords from PDF: ${pdfPath}`, error);
    }

    console.log(`Matches found in ${pdfPath}: ${matches.length}`);
    return matches;
};

// Search endpoint
app.get('/search', async (req, res) => {
    const { keyword, exclude } = req.query;

    if (!keyword || keyword.trim() === '') {
        return res.status(400).json({ error: 'No search keyword provided.' });
    }

    const cacheKey = `search:${keyword}:${exclude || 'noexclude'}`;
    const cachedResults = searchCache.get(cacheKey);

    if (cachedResults) {
        console.log(`Cache HIT for key: ${cacheKey}`);
        return res.json(cachedResults);
    }

    console.log(`Cache MISS for key: ${cacheKey}`);

    try {
        await poolConnect;
        console.log('Database connection established.');

        const request = pool.request();
        const sanitizedKeyword = keyword.replace(/[^\w\s\+\-]/g, '').trim();
        const terms = sanitizedKeyword.split('+').map(term => term.trim()).filter(Boolean);

        let query = 'SELECT Filename FROM files WHERE PdfData LIKE @term0';
        request.input('term0', sql.NVarChar, `%${terms[0]}%`);

        terms.slice(1).forEach((term, index) => {
            query += ` AND PdfData LIKE @term${index + 1}`;
            request.input(`term${index + 1}`, sql.NVarChar, `%${term}%`);
        });

        if (exclude && exclude.trim() !== '') {
            const excludedWords = exclude.split(/\s+/).filter(Boolean);
            excludedWords.forEach((word, index) => {
                const sanitizedExclude = word.replace(/[^\w\s]/g, '').trim();
                if (sanitizedExclude) {
                    query += ` AND PdfData NOT LIKE @exclude${index}`;
                    request.input(`exclude${index}`, sql.NVarChar, `%${sanitizedExclude}%`);
                }
            });
        }

        console.log('Executing SQL Query:', query);
        const result = await request.query(query);
        console.log(`Total files to process: ${result.recordset.length}`); 

        const responseData = [];
        const BATCH_SIZE = 10;  // Number of PDFs to process in parallel
        let fileProcessingPromises = [];

        for (let i = 0; i < result.recordset.length; i++) {
            const record = result.recordset[i];
            const fullPath = record.Filename;

            console.log(`Processing file: ${fullPath}`);

            if (path.extname(fullPath).toLowerCase() !== '.pdf') {
                console.log(`Skipping non-PDF file: ${fullPath}`);
                continue;  // Skip this non-PDF file
            }

            const relativePath = getRelativePath(fullPath);
            const absolutePath = path.join('\\\\192.168.2.5\\c$\\GGN Office Repository', relativePath);

            fileProcessingPromises.push(
                extractKeywordMatches(absolutePath, terms).then(matches => {
                    if (matches.length > 0) {
                        console.log(`Matches found for file: ${fullPath}`);
                    } else {
                        console.log(`No matches found for file: ${fullPath}`);
                    }

                    return {
                        Filename: fullPath,
                        relativePath,
                        matches,
                    };
                })
            );

            if (fileProcessingPromises.length >= BATCH_SIZE || i === result.recordset.length - 1) {
                const results = await Promise.all(fileProcessingPromises);
                responseData.push(...results.filter(record => record)); // Filter out null results
                fileProcessingPromises = []; // Reset for the next batch
            }
        }

        console.log(`Search completed. Found ${responseData.length} files.`); 
        searchCache.set(cacheKey, responseData);
        res.json(responseData);

    } catch (error) {
        console.error('Error processing search:', error);
        res.status(500).json({ error: 'An error occurred while processing your search.' });
    }
});

// File serving endpoint with directory handling
app.get('/files/*', (req, res) => {
    const relativePath = req.params[0];
    const basePath = '\\\\192.168.2.5\\c$\\GGN Office Repository';
    const filePath = path.join(basePath, relativePath);

    console.log(`Requesting information for: ${filePath}`);

    fs.stat(filePath)
        .then(stats => {
            if (stats.isDirectory()) {
                console.log(`Accessing directory: ${filePath}`);
                return fs.readdir(filePath)
                    .then(files => {
                        const fileLinks = files.map(file => {
                            return `<li><a href="/files/${path.join(relativePath, file).replace(/\\/g, '/')}">${file}</a></li>`;
                        }).join('');
                        
                        res.send(`
                            <h1>Contents of ${relativePath}</h1>
                            <ul>${fileLinks}</ul>
                        `);
                    });
            } else if (stats.isFile()) {
                console.log(`Sending file: ${filePath}`);
                res.sendFile(filePath, (err) => {
                    if (err) {
                        console.error(`Error sending file: ${filePath}`, err);
                        res.status(500).json({ error: 'Error sending file' });
                    }
                });
            } else {
                console.warn(`Path is neither a file nor a directory: ${filePath}`);
                res.status(400).json({ error: 'Neither a file nor a directory' });
            }
        })
        .catch(err => {
            console.error(`Error accessing path: ${filePath}`, err);
            res.status(404).json({ error: 'Path not found or inaccessible' });
        });
});

const PORT = process.env.PORT || 3006;
app.listen(PORT, '192.168.7.221', () => {
    console.log(`Server running at http://192.168.7.221:${PORT}`);
});   and this is server.js