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.files1 (Filename, PdfData) VALUES (${filename}, ${text})`;
    } catch (err) {
        console.error('Error inserting into files1 database:', err);
    }
}

// Function to process PDFs and DOCX files
async function preloadFiles() {
    try {
        console.log('Connecting to the database...');
        await sql.connect(sqlconfig);
        console.log('Connected to the database.');

        console.log(`Searching for files in directory: ${pdfDirectory}`);
        const allFiles = await getAllFiles(pdfDirectory);
        console.log(`Total files found: ${allFiles.length}`);
        
        let uploadedCount = 0;

        for (const file of allFiles) {
            console.log(`Processing file: ${file}`);
            try {
                const fileStat = await fs.stat(file);
                console.log(`File size: ${fileStat.size} bytes`);

                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);
                    console.log(`Extracting text from PDF...`);
                    
                    // Try to extract text first from PDF
                    fileText = await extractPdfText(pdfData);
                    
                    // If no text found (scanned PDF), apply OCR
                    if (!fileText) {
                        console.log(`No extractable text found. Converting PDF to image for OCR.`);
                        const imagePath = await convertPdfToImage(file);
                        fileText = await performOcr(imagePath);
                    }
                }

                // Process DOCX files
                if (file.endsWith('.docx') && !fileText) {
                    console.log(`Extracting text from DOCX...`);
                    fileText = await extractDocxText(file);
                }

                // If text is extracted (either from PDF or DOCX), insert into DB
                if (fileText) {
                    console.log(`Inserting into database: Filename=${file}, Text Length=${fileText.length}`);
                    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. Total files uploaded: ${uploadedCount}`);
    } catch (err) {
        console.error('Error pre-loading files:', err);
    } finally {
        await sql.close();
        console.log('Database connection closed.');
    }
}

async function testDirectoryAccess() {
    try {
        const files = await fs.readdir(pdfDirectory);
        console.log('Files in directory:', files);
    } catch (err) {
        console.error('Error accessing directory:', err);
    }
}

// Initial directory accessibility test
testDirectoryAccess();

// Start the preload process
preloadFiles();
     this is ocr.js for preloading in database  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.13.30',
    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.13.30\\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}%`);
        // });
        const sanitizedKeyword = keyword.replace(/[^\w\s\+\-]/g, '').trim();
        const terms = sanitizedKeyword.split('+').map(term => term.trim()).filter(Boolean);

        let query = 'SELECT Filename FROM dbo.files1 WHERE CONTAINS(PdfData, @term0)';
        request.input('term0', sql.NVarChar, terms[0]); // Use full-text search

terms.slice(1).forEach((term, index) => {
    query += ` AND CONTAINS(PdfData, @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 = fullPath.replace('\\\\192.168.2.5\\c$\\GGN Office Repository\\', '');
            const absolutePath = path.join('\\\\192.168.13.30\\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.13.30\\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}`);
});   this is server.js ..in place pf this i wanted to use solr...i have strated the solr port at 8983