const fs = require('fs').promises;
const path = require('path');
const sql = require('mssql');
const pdf = require('pdf-parse');

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')) {
                filesArray.push(fullPath);
            }
        }
    } catch (err) {
        console.error('Error reading directory:', err);
    }
    return filesArray;
}

async function extractPdfTextWithPages(pdfPath) {
    try {
        const dataBuffer = await fs.readFile(pdfPath);
        const pdfData = await pdf(dataBuffer, { max: 0 });

        let indexedData = {};
        let currentPage = 1;

        pdfData.text.split('\n').forEach(line => {
            if (line.trim().toLowerCase().startsWith("page")) { 
                currentPage++;
            }
            if (!indexedData[currentPage]) {
                indexedData[currentPage] = [];
            }
            indexedData[currentPage].push(line.trim());
        });

        return { indexedData };
    } catch (err) {
        console.error(`Error extracting text from ${pdfPath}:`, err);
        return { indexedData: null };
    }
}

async function insertFileData(filename, indexedData) {
    try {
        const pool = await sql.connect(sqlconfig);

        for (const [pageNumber, lines] of Object.entries(indexedData)) {
            const pageText = lines.join(' ').trim();
            if (pageText) {
                await pool.request()
                    .input('Filename', sql.NVarChar(255), filename)
                    .input('PageNumber', sql.Int, parseInt(pageNumber))
                    .input('PageText', sql.NVarChar(sql.MAX), pageText)
                    .query(`
                        INSERT INTO dbo.filess (Filename, PageNumber, PageText) 
                        VALUES (@Filename, @PageNumber, @PageText)
                    `);
            }
        }

    } catch (err) {
        console.error('Error inserting file data into database:', err);
    }
}

async function preloadFiles() {
    try {
        await sql.connect(sqlconfig);
        const allFiles = await getAllFiles(pdfDirectory);
        let uploadedCount = 0;

        for (const file of allFiles) {
            try {
                const { indexedData } = await extractPdfTextWithPages(file);

                if (indexedData) {
                    await insertFileData(path.basename(file), indexedData);
                    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 indexed. Total files uploaded: ${uploadedCount}`);
    } catch (err) {
        console.error('Error preloading files:', err);
    } finally {
        await sql.close();
    }
}

preloadFiles();
   wanted to use pre indexing and getting this error     at Readable.<anonymous> (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\token-stream-parser.js:19:33)
    at Readable.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3)
    at Readable.push (node:internal/streams/readable:391:5)
    at nextAsync (node:internal/streams/from:194:22) {
  code: 'EREQUEST',
  originalError: Error: Invalid object name 'dbo.filess'.
      at handleError (C:\Users\Administrator\Desktop\quest-v8\node_modules\mssql\lib\tedious\request.js:382:19)
      at Connection.emit (node:events:519:28)
      at Connection.emit (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\connection.js:970:18)
      at RequestTokenHandler.onErrorMessage (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\handler.js:284:21)
      at Readable.<anonymous> (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\token-stream-parser.js:19:33)
      at Readable.emit (node:events:519:28)
      at addChunk (node:internal/streams/readable:559:12)
      at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3)
      at Readable.push (node:internal/streams/readable:391:5)
      at nextAsync (node:internal/streams/from:194:22) {
    info: ErrorMessageToken {
      name: 'ERROR',
      handlerName: 'onErrorMessage',
      number: 208,
      state: 1,
      class: 16,
      message: "Invalid object name 'dbo.filess'.",
      serverName: 'WEBPORTAL',
      procName: '',
      lineNumber: 2
    }
  },
  number: 208,
  lineNumber: 2,
  state: 1,
  class: 16,
  serverName: 'WEBPORTAL',
  procName: '',
  precedingErrors: []
}
Error inserting file data into database: RequestError: Invalid object name 'dbo.filess'.
    at handleError (C:\Users\Administrator\Desktop\quest-v8\node_modules\mssql\lib\tedious\request.js:384:15)
    at Connection.emit (node:events:519:28)
    at Connection.emit (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\connection.js:970:18)
    at RequestTokenHandler.onErrorMessage (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\handler.js:284:21)
    at Readable.<anonymous> (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\token-stream-parser.js:19:33)
    at Readable.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3)
    at Readable.push (node:internal/streams/readable:391:5)
    at nextAsync (node:internal/streams/from:194:22) {
  code: 'EREQUEST',
  originalError: Error: Invalid object name 'dbo.filess'.
      at handleError (C:\Users\Administrator\Desktop\quest-v8\node_modules\mssql\lib\tedious\request.js:382:19)
      at Connection.emit (node:events:519:28)
      at Connection.emit (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\connection.js:970:18)
      at RequestTokenHandler.onErrorMessage (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\handler.js:284:21)
      at Readable.<anonymous> (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\token-stream-parser.js:19:33)
      at Readable.emit (node:events:519:28)
      at addChunk (node:internal/streams/readable:559:12)
      at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3)
      at Readable.push (node:internal/streams/readable:391:5)
      at nextAsync (node:internal/streams/from:194:22) {
    info: ErrorMessageToken {
      name: 'ERROR',
      handlerName: 'onErrorMessage',
      number: 208,
      state: 1,
      class: 16,
      message: "Invalid object name 'dbo.filess'.",
      serverName: 'WEBPORTAL',
      procName: '',
      lineNumber: 2
    }
  },
  number: 208,
  lineNumber: 2,
  state: 1,
  class: 16,
  serverName: 'WEBPORTAL',
  procName: '',
  precedingErrors: []
}
Error inserting file data into database: RequestError: Invalid object name 'dbo.filess'.
    at handleError (C:\Users\Administrator\Desktop\quest-v8\node_modules\mssql\lib\tedious\request.js:384:15)
    at Connection.emit (node:events:519:28)
    at Connection.emit (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\connection.js:970:18)
    at RequestTokenHandler.onErrorMessage (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\handler.js:284:21)
    at Readable.<anonymous> (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\token-stream-parser.js:19:33)
    at Readable.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3)
    at Readable.push (node:internal/streams/readable:391:5)
    at nextAsync (node:internal/streams/from:194:22) {
  code: 'EREQUEST',
  originalError: Error: Invalid object name 'dbo.filess'.
      at handleError (C:\Users\Administrator\Desktop\quest-v8\node_modules\mssql\lib\tedious\request.js:382:19)
      at Connection.emit (node:events:519:28)
      at Connection.emit (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\connection.js:970:18)
      at RequestTokenHandler.onErrorMessage (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\handler.js:284:21)
      at Readable.<anonymous> (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\token-stream-parser.js:19:33)
      at Readable.emit (node:events:519:28)
      at addChunk (node:internal/streams/readable:559:12)
      at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3)
      at Readable.push (node:internal/streams/readable:391:5)
      at nextAsync (node:internal/streams/from:194:22) {
    info: ErrorMessageToken {
      name: 'ERROR',
      handlerName: 'onErrorMessage',
      number: 208,
      state: 1,
      class: 16,
      message: "Invalid object name 'dbo.filess'.",
      serverName: 'WEBPORTAL',
      procName: '',
      lineNumber: 2
    }
  },
  number: 208,
  lineNumber: 2,
  state: 1,
  class: 16,
  serverName: 'WEBPORTAL',
  procName: '',
  precedingErrors: []
}
Error inserting file data into database: RequestError: Invalid object name 'dbo.filess'.
    at handleError (C:\Users\Administrator\Desktop\quest-v8\node_modules\mssql\lib\tedious\request.js:384:15)
    at Connection.emit (node:events:519:28)
    at Connection.emit (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\connection.js:970:18)
    at RequestTokenHandler.onErrorMessage (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\handler.js:284:21)
    at Readable.<anonymous> (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\token-stream-parser.js:19:33)
    at Readable.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3)
    at Readable.push (node:internal/streams/readable:391:5)
    at nextAsync (node:internal/streams/from:194:22) {
  code: 'EREQUEST',
  originalError: Error: Invalid object name 'dbo.filess'.
      at handleError (C:\Users\Administrator\Desktop\quest-v8\node_modules\mssql\lib\tedious\request.js:382:19)
      at Connection.emit (node:events:519:28)
      at Connection.emit (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\connection.js:970:18)
      at RequestTokenHandler.onErrorMessage (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\handler.js:284:21)
      at Readable.<anonymous> (C:\Users\Administrator\Desktop\quest-v8\node_modules\tedious\lib\token\token-stream-parser.js:19:33)
      at Readable.emit (node:events:519:28)
      at addChunk (node:internal/streams/readable:559:12)
      at readableAddChunkPushObjectMode (node:internal/streams/readable:536:3)
      at Readable.push (node:internal/streams/readable:391:5)
      at nextAsync (node:internal/streams/from:194:22) {
    info: ErrorMessageToken {
      name: 'ERROR',
      handlerName: 'onErrorMessage',
      number: 208,
      state: 1,
      class: 16,
      message: "Invalid object name 'dbo.filess'.",
      serverName: 'WEBPORTAL',
      procName: '',
      lineNumber: 2
    }
  },
  number: 208,
  lineNumber: 2,
  state: 1,
  class: 16,
  serverName: 'WEBPORTAL',
  procName: '',
  precedingErrors: []   while i have ocr dtaabase and the table dbo.filess inside it having ID, FileName , PageNumber ,PagefText column