VLEX/Pdf_Analyser/Service (3)/Service/Controllers/WorkArea/WorkAreaApiController.cs

using GreenPipes.Filters;
using LAPS_XMLQC_Service.App_Data;
using LAPS_XMLQC_Service.Models;
using LAPS_XMLQC_Service.Services;
using MassTransit.Initializers.Variables;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.CodeAnalysis;
using Microsoft.Extensions.Configuration;
using Microsoft.IdentityModel.Logging;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Npgsql;
using RabbitMQ.Client.Framing.Impl;
using RabbitMQ.Client.Impl;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Net.NetworkInformation;
using System.Reflection.Metadata;
using System.Security.Cryptography;
using System.Security.Principal;
using System.Threading.Tasks;
using System.Transactions;
using System.Xml.Linq;
using static MassTransit.Logging.LogCategoryName;

namespace LAPS_XMLQC_Service.Controllers.WorkArea
{
    [Route("api/[controller]")]
    [ApiController]
    public class WorkAreaApiController : ControllerBase
    {
        private readonly FileSearchService _fileSearchService;
        private readonly IWorkAreaService oWorkAreaService;
        private readonly CommonRepository oCommonRepository;
        private readonly GrantFolderPermission oGrantFolderPermission;
        private readonly string _connectionString;

        //public WorkAreaApiController(IConfiguration configuration)
        //{
        //    _connectionString = configuration.GetConnectionString("DbConnection");
        //}
        public WorkAreaApiController(FileSearchService fileSearchService, IWorkAreaService workAreaService, CommonRepository commonRepository, GrantFolderPermission grantFolderPermission, IConfiguration configuration)
        {
            _fileSearchService = fileSearchService;
            oWorkAreaService = workAreaService;
            oCommonRepository = commonRepository;
            oGrantFolderPermission = grantFolderPermission;
            _connectionString = configuration.GetConnectionString("DbConnection");
        }

        [Authorize]
        [HttpGet("GetServerPath")]
        public ActionResult<string> GetServerPath(string tranid)
        {
            string serverpath = oWorkAreaService.GetServerPath(tranid, false, "serverinfo");
            if (!string.IsNullOrEmpty(serverpath))
                serverpath = System.IO.Path.Combine(serverpath, "IN");
            else
                serverpath = string.Empty;

            return Ok(serverpath);
        }

        // [Authorize]
        [HttpGet("XmlReviewQC")]

        public async Task<IActionResult> XmlReviewQC(string tranid, long jobid, long lotid, bool islot)
        {
            string xmlserverpath = string.Empty;
            string serverpath = string.Empty;
            string serverinpath = string.Empty;
            long transactionid = 0;
            var results = new List<MatchedResult>();
            // oGrantFolderPermission.FolderPermission("", "", "", jobid.ToString());
            //{
            try
            {
                using (var connection = new NpgsqlConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    Console.WriteLine("Database connection opened.");

                    using (var command = new NpgsqlCommand(
      "SELECT lt.transactionid, lt.batchid " +
      "FROM tbljobmaster jm " +
      "JOIN tbljobworkflowdefinition jwm ON jwm.jobid = jm.jobid " +
      "JOIN tbllottransaction lt ON jwm.jobworkflowid = lt.jobworkflowid " +
      "WHERE jm.jobid = @jobid AND jwm.stagename_alias = 'PDF Analysis' " +
      "ORDER BY lt.createdon DESC LIMIT 1;",
      connection))
                    {
                        command.Parameters.AddWithValue("@jobid", jobid);
                        // execute reader or scalar here
                    
                    // command.Parameters.AddWithValue("@lotid", lotid);

                    Console.WriteLine($"Executing SQL with jobid: {jobid}, lotid: {lotid}");

                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            if (await reader.ReadAsync())  // Use ReadAsync instead of Read
                            {
                                transactionid = reader.GetInt64(0);  // Read as long instead of string
                                Console.WriteLine("Transaction ID found: " + transactionid);
                            }
                            else
                            {
                                Console.WriteLine("No data found for the given jobid and lotid.");
                            }
                        }
                    }
                }
                if (islot == false)
                {
                    // Use async database operation


                    if (transactionid != 0)
                    {
                        serverpath = oWorkAreaService.GetServerPath(transactionid.ToString(), false, "serverinfo");
                        Console.WriteLine("Server path retrieved: " + serverpath);
                    }
                    else
                    {
                        Console.WriteLine("Transaction ID is empty, unable to retrieve server path.");
                    }
                }
                else
                {
                    serverpath = oWorkAreaService.GetServerPath(transactionid.ToString(), false, "serverinfo");
                }

                serverinpath = Path.Combine(serverpath, "IN");
                Console.WriteLine("Server IN Path: " + serverinpath);

                if (!string.IsNullOrEmpty(serverinpath))
                {
                    oGrantFolderPermission.FolderPermission_vlex(serverinpath, "", "", "", jobid.ToString());


                    var identity = oGrantFolderPermission.GetImpartunate()?.AllowAccesstoServer();
                    WindowsIdentity.RunImpersonated(identity.AccessToken, () =>
                    {
                        WindowsIdentity useri = WindowsIdentity.GetCurrent();
                    });

                    // Offload file system search to another thread asynchronously using Task.Run
                    //var filePaths = await Task.Run(() => Directory.GetFiles(serverinpath, "*.pdf", SearchOption.AllDirectories));
                    //results = filePaths.Select(path => new MatchedResult
                    //{
                    //    FilePath = path
                    //}).ToList();

                    var filePaths = await Task.Run(() =>
    Directory.GetFiles(serverinpath, "*.pdf", SearchOption.AllDirectories)
              .Where(f => !f.EndsWith("_High.pdf", StringComparison.OrdinalIgnoreCase))
);

                    results = filePaths.Select(path => new MatchedResult
                    {
                        FilePath = path
                    }).ToList();


                    //                var filePaths = await Task.Run(() =>
                    //Directory.GetFiles(serverinpath, "*.pdf", SearchOption.AllDirectories)
                    //         .Where(f => !f.EndsWith("_High.pdf", StringComparison.OrdinalIgnoreCase))
                    //         .ToArray());

                    Console.WriteLine($"Found {results.Count} files.");
                    NetworkShareUtility.DisconnectFromShare(serverinpath);
                }

                return Ok(results);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Running as: {Environment.UserDomainName}\\{Environment.UserName}");
                Console.WriteLine("An error occurred: " + ex.Message);
                return StatusCode(500, "Internal Server Error: " + string.Concat(ex.Message, Environment.UserName, Environment.UserDomainName));
            }
            // }

        }



        //public async Task<IActionResult> XmlReviewQC(string tranid, long jobid, long lotid, bool islot)
        //{
        //    string xmlserverpath = string.Empty;
        //    string serverpath = string.Empty;
        //    string serverinpath = string.Empty;
        //    long transactionid = 0;  // Use long instead of string for bigint column
        //    var results = new List<MatchedResult>();

        //    try
        //    {
        //        if (islot == false)
        //        {
        //            // Open database connection and fetch transaction id
        //            using (var connection = new NpgsqlConnection(_connectionString))
        //            {
        //                connection.Open();
        //                Console.WriteLine("Database connection opened.");

        //                using (var command = new NpgsqlCommand(
        //                    "SELECT lt.transactionid FROM tbljobmaster jm " +
        //                    "JOIN tbllotmaster lm ON jm.jobid = lm.jobid " +
        //                    "JOIN tbllottransaction lt ON lm.lotid = lt.lotid " +
        //                    "WHERE jm.jobid = @jobid AND lm.lotid = @lotid ORDER BY jm.createdon desc LIMIT 1", connection))
        //                {
        //                    // Pass the jobid and lotid as long (Int64)
        //                    command.Parameters.AddWithValue("@jobid", jobid);
        //                    command.Parameters.AddWithValue("@lotid", lotid);

        //                    using (var reader = command.ExecuteReader())
        //                    {
        //                        if (reader.Read())
        //                        {
        //                            transactionid = reader.GetInt64(0);  // Read as long instead of string
        //                            Console.WriteLine("Transaction ID found: " + transactionid);
        //                        }
        //                        else
        //                        {
        //                            Console.WriteLine("No data found for the given jobid and lotid.");
        //                        }
        //                    }
        //                }
        //            }

        //            // Check if transactionid is found before proceeding
        //            if (transactionid != 0)
        //            {
        //                // Retrieve server path based on the transaction id
        //                serverpath = oWorkAreaService.GetServerPath(transactionid.ToString(), false, "serverinfo");
        //                Console.WriteLine("Server path retrieved successfully.");
        //            }
        //            else
        //            {
        //                Console.WriteLine("Transaction ID is empty, unable to retrieve server path.");
        //            }
        //        }
        //        else
        //        {
        //            // If islot is true, use the provided tranid
        //            serverpath = oWorkAreaService.GetServerPath(tranid, false, "serverinfo");
        //        }

        //        // Construct the path for the "IN" folder
        //        serverinpath = Path.Combine(serverpath, "IN");

        //        // Optional permission logic (if applicable)
        //        // oGrantFolderPermission.FolderPermission("", "", "", jobid);

        //        // Perform file search if serverpath is valid
        //        if (!string.IsNullOrEmpty(serverinpath))
        //        {
        //            var filePaths = Directory.GetFiles(serverinpath, "*.pdf", SearchOption.AllDirectories);
        //            results = filePaths.Select(path => new MatchedResult
        //            {
        //                FilePath = path  // Adjust the property name as per your class
        //            }).ToList();
        //        }

        //        // Return the results as a response
        //        return Ok(results);
        //    }
        //    catch (Exception ex)
        //    {
        //        // Log the error and return an internal server error status code
        //        Console.WriteLine("An error occurred: " + ex.Message);
        //        return StatusCode(500, "Internal Server Error: " + ex.Message);
        //    }
        //}

        // [Authorize]

        //[HttpGet("PreviewPdf")]
        //public IActionResult PreviewPdf([FromQuery] string path)
        //{
        //    if (string.IsNullOrWhiteSpace(path) || !System.IO.File.Exists(path))
        //        return NotFound();

        //    var stream = new FileStream(path, FileMode.Open, FileAccess.Read);
        //    return File(stream, "application/pdf");
        //}


        //public async Task<IActionResult> PreviewPdf([FromQuery] string path)
        //{
        //    if (string.IsNullOrWhiteSpace(path))
        //        return BadRequest("Missing PDF path.");

        //    // Serve local file
        //    if (System.IO.File.Exists(path))
        //    {
        //        try
        //        {
        //            var stream = new FileStream(path, FileMode.Open, FileAccess.Read);
        //            return File(stream, "application/pdf");
        //        }
        //        catch (Exception ex)
        //        {
        //            Console.WriteLine($"Local file error: {ex.Message}");
        //            return StatusCode(500, "Error reading local file.");
        //        }
        //    }

        //    // Try remote download if not a local path
        //    if (Uri.TryCreate(path, UriKind.Absolute, out Uri? uriResult) && uriResult.Scheme.StartsWith("http"))
        //    {
        //        try
        //        {
        //            using var httpClient = new HttpClient();
        //            var response = await httpClient.GetAsync(path);

        //            if (!response.IsSuccessStatusCode)
        //                return StatusCode((int)response.StatusCode, "Unable to fetch PDF from URL.");

        //            var stream = await response.Content.ReadAsStreamAsync();
        //            return File(stream, "application/pdf");
        //        }
        //        catch (Exception ex)
        //        {
        //            Console.WriteLine($"Remote fetch error: {ex.Message}");
        //            return StatusCode(500, "Error fetching remote PDF.");
        //        }
        //    }

        //    return BadRequest("Invalid path or URL.");
        //}











        [HttpGet("PreviewPdf")]

        public async Task<IActionResult> PreviewPdf([FromQuery] string path)
        {
            if (string.IsNullOrWhiteSpace(path))
                return BadRequest("Missing PDF path.");

            // Local file case
            if (System.IO.File.Exists(path))
            {
                try
                {
                    var stream = new FileStream(path, FileMode.Open, FileAccess.Read);
                    var fileName = Path.GetFileName(path);

                    Response.Headers["Content-Disposition"] = $"inline; filename=\"{fileName}\"";
                    Response.Headers["Content-Type"] = "application/pdf";
                    Response.Headers["X-Content-Type-Options"] = "nosniff";
                    Response.Headers.Remove("X-Frame-Options"); // Optional: allow iframe embedding

                    return File(stream, "application/pdf");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Local file error: {ex.Message}");
                    return StatusCode(500, "Error reading local file.");
                }
            }

            // Remote file case
            if (Uri.TryCreate(path, UriKind.Absolute, out Uri? uriResult) && uriResult.Scheme.StartsWith("http"))
            {
                try
                {
                    using var httpClient = new HttpClient();
                    var response = await httpClient.GetAsync(path);

                    if (!response.IsSuccessStatusCode)
                        return StatusCode((int)response.StatusCode, "Unable to fetch PDF from URL.");

                    var stream = await response.Content.ReadAsStreamAsync();
                    var fileName = Path.GetFileName(uriResult.LocalPath);

                    Response.Headers["Content-Disposition"] = $"inline; filename=\"{fileName}\"";
                    Response.Headers["Content-Type"] = "application/pdf";
                    Response.Headers["X-Content-Type-Options"] = "nosniff";
                    Response.Headers.Remove("X-Frame-Options");

                    return File(stream, "application/pdf");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Remote fetch error: {ex.Message}");
                    return StatusCode(500, "Error fetching remote PDF.");
                }
            }

            return BadRequest("Invalid path or URL.");
        }


        //public async Task<IActionResult> PreviewPdf([FromQuery] string path)
        //{
        //    if (!Uri.TryCreate(path, UriKind.Absolute, out Uri? uriResult))
        //        return BadRequest("Invalid URL.");

        //    // Create a temporary file
        //    var tempFile = Path.Combine(Path.GetTempPath(), Path.GetFileName(uriResult.LocalPath));

        //    using (var httpClient = new HttpClient())
        //    using (var response = await httpClient.GetAsync(path, HttpCompletionOption.ResponseHeadersRead))
        //    {
        //        if (!response.IsSuccessStatusCode)
        //            return StatusCode((int)response.StatusCode, "Unable to fetch remote PDF.");

        //        using (var fs = new FileStream(tempFile, FileMode.Create, FileAccess.Write))
        //        {
        //            await response.Content.CopyToAsync(fs);
        //        }
        //    }

        //    // Serve the file with range support
        //    return PhysicalFile(tempFile, "application/pdf", Path.GetFileName(tempFile), enableRangeProcessing: true);
        //}




        //public async Task<IActionResult> PreviewPdf([FromQuery] string path)
        //{
        //    if (string.IsNullOrWhiteSpace(path))
        //        return BadRequest("Missing PDF path.");

        //    // Local file case
        //    if (System.IO.File.Exists(path))
        //    {
        //        try
        //        {
        //            var stream = new FileStream(path, FileMode.Open, FileAccess.Read);
        //            var fileName = Path.GetFileName(path);

        //            Response.Headers["Content-Disposition"] = $"inline; filename=\"{fileName}\"";
        //            Response.Headers["Content-Type"] = "application/pdf";
        //            Response.Headers["X-Content-Type-Options"] = "nosniff";
        //            Response.Headers.Remove("X-Frame-Options"); // Optional: allow iframe embedding

        //            return File(stream, "application/pdf");
        //        }
        //        catch (Exception ex)
        //        {
        //            Console.WriteLine($"Local file error: {ex.Message}");
        //            return StatusCode(500, "Error reading local file.");
        //        }
        //    }

        //    // Remote file case
        //    if (Uri.TryCreate(path, UriKind.Absolute, out Uri? uriResult) && uriResult.Scheme.StartsWith("http"))
        //    {
        //        try
        //        {
        //            using var httpClient = new HttpClient();
        //            var response = await httpClient.GetAsync(path);

        //            if (!response.IsSuccessStatusCode)
        //                return StatusCode((int)response.StatusCode, "Unable to fetch PDF from URL.");

        //            var stream = await response.Content.ReadAsStreamAsync();
        //            var fileName = Path.GetFileName(uriResult.LocalPath);

        //            Response.Headers["Content-Disposition"] = $"inline; filename=\"{fileName}\"";
        //            Response.Headers["Content-Type"] = "application/pdf";
        //            Response.Headers["X-Content-Type-Options"] = "nosniff";
        //            Response.Headers.Remove("X-Frame-Options");

        //            return File(stream, "application/pdf");
        //        }
        //        catch (Exception ex)
        //        {
        //            Console.WriteLine($"Remote fetch error: {ex.Message}");
        //            return StatusCode(500, "Error fetching remote PDF.");
        //        }
        //    }

        //    return BadRequest("Invalid path or URL.");
        //}




        //[HttpGet("PreviewPdf")]
        //public IActionResult PreviewPdf([FromQuery] string path)
        //{
        //    if (string.IsNullOrEmpty(path))
        //        return BadRequest("Path is required.");

        //    var baseFolder = @"\\172.20.254.139\files_internal_laps\ROOT_UAT\LNBPM\150\2547";

        //    // Normalize and validate the requested path
        //    var fullPath = Path.GetFullPath(path);

        //    //if (!fullPath.StartsWith(baseFolder, StringComparison.OrdinalIgnoreCase))
        //    //{
        //    //    return BadRequest("Invalid path.");
        //    //}

        //    if (!System.IO.File.Exists(fullPath))
        //    {
        //        return NotFound();
        //    }

        //    var fileBytes = System.IO.File.ReadAllBytes(fullPath);

        //    var fileName = Path.GetFileName(fullPath);

        //    return File(fileBytes, "application/pdf", fileName);
        //}



        // [Authorize]
        [HttpGet("GetLotTitles")]

        public IActionResult GetLotTitles(long jobid)
        {
            var titles = new List<LotTitleDto>();
            string imgTitle = null;

            using (var connection = new NpgsqlConnection(_connectionString))
            {
                connection.Open();
                using (var command = new NpgsqlCommand(
                     "SELECT lm.title, lm.lotid, tlp.mainpagerange, tlp.supplementrypagerange, lm.totalunits " +
                    "FROM tbljobmaster jm " +
                    "JOIN tbllotmaster lm ON jm.jobid = lm.jobid " +
                    "JOIN tbllotpagedetails tlp ON lm.lotid = tlp.lotid " +
                    "WHERE jm.jobid = @jobid order by lm.lotid", connection))
                {
                    command.Parameters.AddWithValue("@jobid", jobid);

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var title = reader.IsDBNull(0) ? null : reader.GetString(0); // Handle null for title (string)
                            var lotid = reader.IsDBNull(1) ? 0 : reader.GetInt32(1);   // Handle null for lotid (int)
                            var mainpagerange = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);  // Handle null for mainpagerange (string)
                            var supplementrypagerange = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);  // Handle null for supplementrypagerange (string)
                            var totalunits = reader.IsDBNull(4) ? 0 : reader.GetInt32(4);  // Handle null for totalunits (int)

                            // Determine if the title ends with '-img'
                            imgTitle = !string.IsNullOrEmpty(title) && title.EndsWith("-img", StringComparison.OrdinalIgnoreCase) ? "1" : "0";

                            // Add all the fields to the LotTitleDto object
                            var lotTitleDto = new LotTitleDto
                            {
                                Title = title ?? string.Empty, // Default to empty if null
                                lotid = lotid,
                                mainpagerange = mainpagerange, // Assuming it's a string, not converting to ToString()
                                supplementrypagerange = supplementrypagerange, // Same here
                                ImgTitle = imgTitle,
                                DefaultDocType = 1 // Assuming DefaultDocType is always 1
                            };

                            titles.Add(lotTitleDto);
                        }
                    }
                }
            }

            // Log the data to inspect it (if you're debugging)
            Console.WriteLine($"Returned Titles: {titles.Count} rows");
            foreach (var title in titles)
            {
                Console.WriteLine($"Title: {title.Title}, Lotid: {title.lotid}, ImgTitle: {title.ImgTitle}");
            }

            return Ok(titles); // Return the list of titles
        }




        //public IActionResult GetLotTitles(long jobid)
        //{
        //    var titles = new List<LotTitleDto>();

        //    using (var connection = new NpgsqlConnection(_connectionString))
        //    {
        //        connection.Open();
        //        using (var command = new NpgsqlCommand(
        //            "SELECT lm.title,lm.lotid, lm.totalunits " +
        //            "FROM tbljobmaster jm " +
        //            "JOIN tbllotmaster lm ON jm.jobid = lm.jobid " +
        //            "WHERE jm.jobid = @jobid", connection))
        //        {
        //            command.Parameters.AddWithValue("@jobid", jobid); // Now jobid is long

        //            using (var reader = command.ExecuteReader())
        //            {
        //                while (reader.Read())
        //                {
        //                    titles.Add(new LotTitleDto
        //                    {
        //                        Title = reader.GetString(0),
        //                        lotid = reader.GetInt32(1),
        //                        TotalUnits = reader.GetInt32(2)
        //                    });
        //                }
        //            }
        //        }
        //    }

        //    return Ok(titles);
        //}






        [Authorize]
        [HttpPost("usrstagemove")]
        public IActionResult usrstagemove([FromBody] lottraninfo data)
        {
            try
            {
                var _list = oWorkAreaService.usrstagemove(data);
                return Ok(_list);
            }
            catch (Exception ex)
            {
                return BadRequest(new { message = ex.Message });
            }
        }

        [Authorize]
        [HttpPost("getJobDetails")]
        public IActionResult getJobDetails([FromBody] object data)
        {
            var _list = oWorkAreaService.getJobDetails(data);
            return Ok(_list);
        }

        //  [Authorize]
        [HttpGet("GetAllFilesForDownload")]
        public List<FileDetails> GetAllFilesForDownload(int jobid, int tranid)
        {
            List<FileDetails> AllFiles = new List<FileDetails>();

            oGrantFolderPermission.FolderPermission("", "", "", tranid.ToString());


            string serverpath = oWorkAreaService.GetServerPath(tranid.ToString(), false, "serverinfo");
            serverpath = Path.Combine(serverpath, "IN");
            var filetype = oWorkAreaService.getFileTypeInput(tranid.ToString());
            var jsonList = new List<string>();
            string[] inputfiles;
            //if (filetype != null)
            //{
            //    var i_filetype = JsonConvert.DeserializeObject<object>(filetype.ToString());
            //    JArray a_filetype = JArray.Parse(i_filetype.ToString());
            //    foreach (var a in a_filetype)
            //    {
            //        JObject obj_filetype = JObject.Parse(a.ToString());
            //        jsonList.Add(obj_filetype["filetype"].ToString().ToLower());
            //    }
            //}
            if (jsonList.Count() == 1 && jsonList[0].ToString().Contains(".*"))
            {
                inputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.AllDirectories).ToArray();
            }
            else
            {

                inputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.TopDirectoryOnly).Where(x => jsonList.Contains(Path.GetExtension(x.ToString()).ToLower())).ToArray();
                if (inputfiles.Length == 0)
                {
                    inputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.AllDirectories).ToArray();
                }

            }
            foreach (var item in inputfiles)
            {
                FileDetails filedetails = new FileDetails();
                filedetails.FileName = Path.GetFileName(item);
                filedetails.FilePath = item;
                AllFiles.Add(filedetails);
            }


            return AllFiles;


        }



        //  [Authorize]
        [HttpGet("GetAllOutputFiles")]
        public List<FileDetails> GetAllOutputFiles(int jobid, int tranid)
        {
            List<FileDetails> AllFiles = new List<FileDetails>();

            oGrantFolderPermission.FolderPermission("", "", "", tranid.ToString());

            string serverpath = oWorkAreaService.GetServerPath(tranid.ToString(), false, "serverinfo");
            serverpath = Path.Combine(serverpath, "OUT");
            string[] outputfiles = null;

            if (Directory.Exists(serverpath))
            {
                outputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.AllDirectories);
            }
            else
            {
                AllFiles = null;
            }

            if (outputfiles.Length == 0)
            {
                outputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.AllDirectories);
            }
            foreach (var item in outputfiles)
            {
                FileDetails filedetails = new FileDetails();
                filedetails.FileName = Path.GetFileName(item);
                filedetails.FilePath = item;
                AllFiles.Add(filedetails);
            }


            return AllFiles;
        }


        //[Authorize]
        [HttpPost("DownloadSingleFile")]

        public IActionResult DownloadSingleFile([FromBody] filedownload fileData)
        {
            try
            {
                string filePath = fileData.path;
                string tranid = fileData.jobid.ToString();

                // 1️⃣ Validate file path
                if (string.IsNullOrWhiteSpace(filePath))
                    return BadRequest("File path is required.");

                // 2️⃣ Grant folder permission using your existing method
                oGrantFolderPermission.FolderPermission("", "", "", tranid);

                // 3️⃣ Check if file exists
                if (!System.IO.File.Exists(filePath))
                    return NotFound("File not found on server.");

                // 4️⃣ Open file as stream for large-file support
                var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);

                // 5️⃣ Detect MIME type
                string contentType = "application/octet-stream";
                var provider = new Microsoft.AspNetCore.StaticFiles.FileExtensionContentTypeProvider();
                provider.TryGetContentType(filePath, out contentType);

                string fileName = Path.GetFileName(filePath);

                // 6️⃣ Return file as FileStreamResult
                return File(stream, contentType, fileName);
            }
            catch (Exception ex)
            {
                // 7️⃣ Log the error
                var data_input = new
                {
                    errormessage = "Error: " + ex.Message,
                    createdby = 0,
                    controller = "UploaderApi: Downloader"
                };
                // oCommonRepository.addlog(data_input, "errorlog");

                return StatusCode(500, "An error occurred while downloading the file: " + ex.Message);
            }
        }







        //  [Authorize]
        [HttpPost("DownloadInOutFiles")]
        public IActionResult DownloadInOutFiles(filedownload fileData)
        {
            try
            {
                string jobid = fileData.jobid.ToString();
                string tranid = fileData.tranid.ToString();
                string pdid = fileData.pdid.ToString();
                string option = fileData.option.ToString();
                string jobtitle = fileData.jobtitle.ToString();
                string serverpath;
                serverpath = oWorkAreaService.GetServerPath(tranid, false, "serverinfo");
                serverpath = Path.Combine(serverpath, option);
                string zipname = string.Empty;
                if (option.IndexOf("IN") != -1 || option.IndexOf("OUT") != -1)
                {
                    zipname = jobtitle + "_" + tranid + "_" + option + ".zip";
                }
                else
                {
                    zipname = jobtitle + "_" + tranid + ".zip";
                }
                string[] inputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.TopDirectoryOnly).ToArray();
                byte[] fileBytes = null;
                using (System.IO.MemoryStream memoryStream = new System.IO.MemoryStream())
                {
                    using (System.IO.Compression.ZipArchive zip = new System.IO.Compression.ZipArchive(memoryStream, System.IO.Compression.ZipArchiveMode.Create, true))
                    {
                        foreach (string f in inputfiles)
                        {
                            // add the item name to the zip
                            System.IO.Compression.ZipArchiveEntry zipItem = zip.CreateEntry(Path.GetFileName(f) + "." + Path.GetExtension(f));
                            // add the item bytes to the zip entry by opening the original file and copying the bytes
                            using (System.IO.MemoryStream originalFileMemoryStream = new System.IO.MemoryStream(System.IO.File.ReadAllBytes(f)))
                            {
                                using (System.IO.Stream entryStream = zipItem.Open())
                                {
                                    originalFileMemoryStream.CopyTo(entryStream);
                                }
                            }
                        }
                    }
                    fileBytes = memoryStream.ToArray();
                    return Ok(new { File = fileBytes });
                    // return Ok(new { File = File(fileBytes, "application/zip", zipname) });
                }
            }
            catch (Exception ex)
            {
                var data_input = new
                {
                    errormessage = "Error: " + ex.Message,
                    createdby = 0,
                    controller = "UploaderApi: Downloader"
                };
                // oCommonRepository.addlog(data_input, "errorlog");
                return BadRequest(new { message = "File notfound" });
            }

        }

        // [Authorize]
        [HttpPost("DownloadSingleOutFile")]
        public IActionResult DownloadSingleOutFile(filedownload fileData)
        {
            try
            {
                string type = fileData.type.ToString();
                string path = fileData.path.ToString();
                string jobid = fileData.jobid.ToString();
                byte[] fileBytes = null;

                oGrantFolderPermission.FolderPermission_vlex("", "", "", jobid.ToString());

                fileBytes = System.IO.File.ReadAllBytes(path);
                string fileName = Path.GetFileName(path);

                return Ok(new { File = fileBytes });


            }
            catch (Exception ex)
            {
                var data_input = new
                {
                    errormessage = "Error: " + ex.Message,
                    createdby = 0,
                    controller = "UploaderApi: Downloader"
                };
                //oCommonRepository.addlog(data_input, "errorlog");
                throw;
            }
        }

        //  [Authorize]
        [HttpPost("RemoveIOFiles")]
        public object RemoveIOFiles(filedownload fileData)
        {

            string path = fileData.path.ToString();
            string jobid = fileData.jobid.ToString();

            try
            {

                oGrantFolderPermission.FolderPermission("", "", "", jobid.ToString());

                System.IO.File.Delete(path);


                var json = new { result = true, filename = Path.GetFileName(path) };
                return json;

            }
            catch (Exception ex)
            {
                var data_input = new
                {
                    errormessage = "Error: " + ex.Message,
                    createdby = 0,
                    controller = "UploaderApi: Downloader"
                };
                // oCommonRepository.addlog(data_input, "errorlog");
                var json = new { result = false, filename = Path.GetFileName(path) };
                return json;

            }
        }


        // [Authorize]
        [HttpGet("GetAllOutFilesForDownload")]
        public List<FileDetails> GetAllOutFilesForDownload(int jobid, int tranid)
        {

            List<FileDetails> AllFiles = new List<FileDetails>();
            string serverpath = oWorkAreaService.GetServerPath(tranid.ToString(), false, "serverinfo");
            serverpath = Path.Combine(serverpath, "OUT");
            var filetype = oWorkAreaService.getFileTypeOutput(tranid.ToString());
            var jsonList = new List<string>();
            if (filetype != null)
            {
                var i_filetype = JsonConvert.DeserializeObject<object>(filetype.ToString());
                JArray a_filetype = JArray.Parse(i_filetype.ToString());
                foreach (var a in a_filetype)
                {
                    JObject obj_filetype = JObject.Parse(a.ToString());
                    jsonList.Add(obj_filetype["filetype"].ToString().ToLower());
                }
            }

            string[] inputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.TopDirectoryOnly)
        .Where(x => jsonList.Contains(Path.GetExtension(x.ToString()).ToLower())).ToArray();
            if (inputfiles.Length == 0)
            {
                inputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.AllDirectories)
                    .Where(x => jsonList.Contains(Path.GetExtension(x.ToString()).ToLower())).ToArray();
            }
            foreach (var item in inputfiles)
            {
                FileDetails filedetails = new FileDetails();
                filedetails.FileName = Path.GetFileName(item);
                filedetails.FilePath = item;
                AllFiles.Add(filedetails);
            }


            return AllFiles;
        }

        [Authorize]
        [HttpPost("InsertPageDetails")]

        public async Task<IActionResult> InsertPageDetails([FromBody] PageDetailDto dto)
        {
            // If you expect file uploads, use IFormFile
            // If you're sending a file as part of FormData
            // public IFormFile File { get; set; }

            await using var conn = new NpgsqlConnection(_connectionString);
            await conn.OpenAsync();



            try
            {
                using (var cmd = new NpgsqlCommand("SELECT upsert_lotpagedetails(@jobid, @lotid, @maincount, @doctype,@updatedby ,@mode)", conn))
                {
                    cmd.Parameters.AddWithValue("jobid", dto.JobId);
                    cmd.Parameters.AddWithValue("lotid", dto.LotId);
                    cmd.Parameters.AddWithValue("maincount", dto.MainPage);
                    // cmd.Parameters.AddWithValue("supplementcount", dto.SupplementPage);
                    cmd.Parameters.AddWithValue("doctype", dto.DocType);
                    cmd.Parameters.AddWithValue("updatedby", dto.UpdatedBy);
                    cmd.Parameters.AddWithValue("mode", dto.Mode);  // Add this to match the new SP signature
                                                                    // Add this to match the new SP signature

                    // Execute and get the returned lotid
                    var result = cmd.ExecuteScalar();
                    int returnedLotId = result != null ? Convert.ToInt32(result) : 0;





                    return Ok(new { InsertedLotId = result });
                }
            }
            catch (Exception Ex)
            {

                throw;
            }

        }

       
        [HttpGet("getreporterkey")]
        public IActionResult GetReporterKey(int jobid, string jobtitle)
        {
            if (string.IsNullOrWhiteSpace(jobtitle))
                return BadRequest(new { message = "Job title is required." });

            try
            {
                // Extract reporter key (everything before "__")
                int secondUnderscore = jobtitle.IndexOf("_", jobtitle.IndexOf("_") + 1);

                string reporterKey = secondUnderscore != -1
                    ? jobtitle.Substring(0, secondUnderscore + 1)
                    : jobtitle;

                // 🔹 if it ends with "__", trim one underscore
                if (reporterKey.EndsWith("__"))
                    reporterKey = reporterKey.TrimEnd('_') + "_";
                int? reporterId = null;

                using (var conn = new NpgsqlConnection(_connectionString))
                {
                    conn.Open();

                    using (var cmd = new NpgsqlCommand(@"
                SELECT reporterid
                FROM tblvlexreporterkey
                WHERE reporter_key = @rkey
                LIMIT 1;", conn))
                    {
                        cmd.Parameters.AddWithValue("@rkey", reporterKey);

                        var result = cmd.ExecuteScalar();
                        if (result != null && result != DBNull.Value)
                            reporterId = Convert.ToInt32(result);
                    }
                }

                if (!reporterId.HasValue)
                    return NotFound(new { message = $"No reporter found for key '{reporterKey}'." });

                return Ok(new { reporterId = reporterId.Value });
            }
            catch (Exception ex)
            {
                return StatusCode(500, new { error = "An error occurred while fetching reporter key.", details = ex.Message });
            }
        }



        [Authorize]
        [HttpGet("getxmlfile")]

        public IActionResult getxmlfile(string tranid, string lotid, string userid)
        {
            if (string.IsNullOrWhiteSpace(tranid))
                return BadRequest("tranid is required.");
            oGrantFolderPermission.FolderPermission("", "", "", tranid.ToString());
            // Validate lotid before using it in query
            if (!long.TryParse(lotid, out long lotIdNumeric))
                return BadRequest("Invalid lotid format. It must be a numeric value.");

            // Validate and convert userid to int
            if (!int.TryParse(userid, out int userIdNumeric))
                return BadRequest("Invalid userid format. It must be an integer.");

            using var conn = new NpgsqlConnection(_connectionString);
            conn.OpenAsync();

            string serverpath = oWorkAreaService.GetServerPath(tranid, false, "serverinfo");
            serverpath = Path.Combine(serverpath, "OUT");

            // 1. Locate file
            string filePath = Path.Combine(serverpath, "pageinfo.xml");

            if (!System.IO.File.Exists(filePath))
                return NotFound("File not found for given tranid.");

            try
            {
                // 2. Read XML
                string xmlContent = System.IO.File.ReadAllText(filePath);
                var doc = XDocument.Parse(xmlContent);
                var files = doc.Descendants("file");

                foreach (var file in files)
                {
                    string fileName = file.Attribute("name")?.Value ?? "unknown";
                    string main = file.Element("main")?.Value ?? "";
                    string supp = file.Element("supp")?.Value ?? "";

                    using (var cmd = new NpgsqlCommand("UPDATE tbllotpagedetails SET supplementrypagerange=@supprange, mainpagerange=@mainrange, updatedby=@updatedby, updatedon=NOW() WHERE lotid=@lotid", conn))
                    {
                        cmd.Parameters.AddWithValue("supprange", supp);
                        cmd.Parameters.AddWithValue("mainrange", main);
                        cmd.Parameters.AddWithValue("updatedby", userIdNumeric);  // Convert userid to int
                        cmd.Parameters.AddWithValue("lotid", lotIdNumeric);

                        // Execute the update
                        var result = cmd.ExecuteScalar();
                        int returnedLotId = result != null ? Convert.ToInt32(result) : 0;
                    }
                }

                return Ok("XML processed and values saved to database.");
            }
            catch (Exception ex)
            {
                return StatusCode(500, $"Error processing XML: {ex.Message}");
            }
        }
        [HttpPost("SaveLots")]
        public IActionResult SaveLots([FromBody] List<LotDto> lots)
        {
            if (lots == null || lots.Count == 0)
                return BadRequest("No lots provided.");

            int jobId = lots[0].jobid;
            long ocrtranid = 0;
            int lastTransactionId = 0;
            bool firstLotUpdated = false;
            string serverPathIn = string.Empty;
            int batchId = 0;
            long nextTransactionId = 0;

            try
            {
                using (var conn = new NpgsqlConnection(_connectionString))
                {
                    conn.Open();

                    // 1️⃣ Get last transaction ID for PDF Analysis
                    using (var command = new NpgsqlCommand(@"
                SELECT lt.transactionid, lt.batchid
                FROM tbljobmaster jm
                JOIN tbljobworkflowdefinition jwm ON jwm.jobid = jm.jobid
                JOIN tbllottransaction lt ON jwm.jobworkflowid = lt.jobworkflowid
                WHERE jm.jobid = @jobid AND jwm.stagename_alias = 'PDF Analysis'
                ORDER BY lt.createdon DESC
                LIMIT 1", conn))
                    {
                        command.Parameters.AddWithValue("@jobid", jobId);
                        using (var reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                lastTransactionId = reader.GetInt32(0);
                                batchId = reader.GetInt32(1);
                            }
                        }
                    }

                    string pdfAnalysisServerPath = oWorkAreaService.GetServerPath(lastTransactionId.ToString(), false, "serverinfo");

                    // 🔹 Collect ALL split files from all lots
                    List<string> allSplitFiles = new List<string>();

                    // 2️⃣ Process each lot
                    foreach (var lot in lots)
                    {
                        int lotId = 0;
                        Guid lotKey = Guid.NewGuid();
                        int latestSequence = -1;

                        // 🔹 Check existing lots
                        int latestLotId = 0;
                        Guid latestLotKey = Guid.Empty;

                        using (var cmdCheck = new NpgsqlCommand(@"
                    SELECT lotid, sequence, lotkey
                    FROM tbllotmaster
                    WHERE jobid = @jobid
                    ORDER BY sequence DESC
                    LIMIT 1;", conn))
                        {
                            cmdCheck.Parameters.AddWithValue("@jobid", jobId);
                            using (var reader = cmdCheck.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    latestLotId = reader.GetInt32(0);
                                    latestSequence = reader.GetInt32(1);
                                    latestLotKey = reader.GetGuid(2);
                                }
                            }
                        }

                        using (var tran = conn.BeginTransaction())
                        {
                            if (latestLotId > 0 && !firstLotUpdated)
                            {
                                // ✅ Update the first lot only once
                                firstLotUpdated = true;
                                lotId = latestLotId;
                                lotKey = latestLotKey;

                                using (var cmdUpdate = new NpgsqlCommand(@"
                            UPDATE tbllotmaster
                            SET title = @title, filename = @filename, updatedon = @updatedon
                            WHERE jobid = @jobid AND lotid = @lotid;", conn, tran))
                                {
                                    string lotNameTitle = $"{lot.Volume} {lot.Edition} {lot.Printpage}";
                                    cmdUpdate.Parameters.AddWithValue("@title", lotNameTitle);
                                    cmdUpdate.Parameters.AddWithValue("@filename", lotNameTitle + ".pdf");
                                    cmdUpdate.Parameters.AddWithValue("@updatedon", DateTime.Now);
                                    cmdUpdate.Parameters.AddWithValue("@jobid", jobId);
                                    cmdUpdate.Parameters.AddWithValue("@lotid", lotId);
                                    cmdUpdate.ExecuteNonQuery();
                                }
                            }
                            else
                            {
                                // ✅ Insert new lot
                                int nextSequence = (latestSequence >= 0 ? latestSequence + 1 : 0);
                                lotKey = Guid.NewGuid();

                                using (var cmdInsertLot = new NpgsqlCommand("insert_lot_details", conn, tran))
                                {
                                    cmdInsertLot.CommandType = CommandType.StoredProcedure;
                                    cmdInsertLot.Parameters.AddWithValue("p_jobid", jobId);
                                    string lotNameTitle = $"{lot.Volume} {lot.Edition} {lot.Printpage}";
                                    cmdInsertLot.Parameters.AddWithValue("p_title", lotNameTitle);
                                    cmdInsertLot.Parameters.AddWithValue("p_sequence", nextSequence);
                                    cmdInsertLot.Parameters.AddWithValue("p_lotkey", lotKey);
                                    cmdInsertLot.Parameters.AddWithValue("p_filename", lotNameTitle + ".pdf");

                                    lotId = Convert.ToInt32(cmdInsertLot.ExecuteScalar());
                                }
                            }

                            // ✅ Insert transaction (common for both update/insert)
                            nextTransactionId = InsertLotTransaction(conn, tran, lotId, lotKey);

                            using (var cmdUpdate = new NpgsqlCommand(@"
                        UPDATE tbllottransaction
                        SET lotstatus = @lotstatus, pickedby = @pickedby
                        WHERE transactionid = @transactionid;", conn, tran))
                            {
                                cmdUpdate.Parameters.AddWithValue("@lotstatus", "C");
                                cmdUpdate.Parameters.AddWithValue("@pickedby", 0);
                                cmdUpdate.Parameters.AddWithValue("@transactionid", nextTransactionId);
                                cmdUpdate.ExecuteNonQuery();
                            }

                            tran.Commit();
                        }

                        // 3️⃣ Create server paths
                        string serverPath = oWorkAreaService.GetServerPath(nextTransactionId.ToString(), false, "serverinfo")
                                              ?? throw new Exception("Server path not found");
                        if (!Directory.Exists(serverPath)) Directory.CreateDirectory(serverPath);

                        oGrantFolderPermission.FolderPermission_vlex(serverPath, "", "", "", jobId.ToString());
                        WindowsIdentity newId = oGrantFolderPermission.GetImpartunate().AllowAccesstoServer();
                        WindowsIdentity.RunImpersonated(newId.AccessToken, () =>
                        {
                            WindowsIdentity useri = WindowsIdentity.GetCurrent();
                        });
                        serverPathIn = Path.Combine(serverPath, "IN");
                        string serverPathOut = Path.Combine(serverPath, "OUT");
                        string pdfAnalysisServerPathIn = Path.Combine(pdfAnalysisServerPath ?? throw new Exception("PDF Analysis path not found"), "IN");

                        Directory.CreateDirectory(serverPathIn);
                        Directory.CreateDirectory(serverPathOut);
                        Appsettings High_appset = new Appsettings();
                        //var pdfFiles = Directory.GetFiles(pdfAnalysisServerPathIn, "*.pdf");
                        var pdfFiles = Directory.GetFiles(pdfAnalysisServerPathIn, "*.pdf", SearchOption.TopDirectoryOnly)
                                               .Where(f => Path.GetFileName(f)
                                                            .EndsWith("_High"+ ".pdf", StringComparison.OrdinalIgnoreCase))
                                               .ToArray();
                        if (pdfFiles.Length == 0)
                            throw new FileNotFoundException("No PDF files found in PDF Analysis path.");
                        string pdfFile = pdfFiles[0];

                        // 4️⃣ Run splitter
                        RunSplitterExe(serverPathIn, pdfFile, lot.Range, jobId.ToString());

                        // 🔹 Collect split files
                        string splitterOutputFolder = Path.Combine(serverPathIn, "splitter_files");
                        if (!Directory.Exists(splitterOutputFolder))
                            throw new DirectoryNotFoundException("Splitter output folder not found: " + splitterOutputFolder);

                        var splitFiles = Directory.GetFiles(splitterOutputFolder, "*.pdf");
                        if (splitFiles.Length == 0)
                            throw new FileNotFoundException("No split PDF files found in splitter output folder.");

                        // 🔹 Rename split files with lot prefix
                        foreach (var file in splitFiles)
                        {
                            string fileNameOnly = Path.GetFileName(file);
                            string newFileName = $"{lot.Volume} {lot.Edition} {lot.Printpage}";
                            newFileName= string.Concat(newFileName, ".pdf");
                            string newFilePath = Path.Combine(splitterOutputFolder, newFileName);

                            if (System.IO.File.Exists(newFilePath))
                                System.IO.File.Delete(newFilePath);

                            System.IO.File.Move(file, newFilePath);
                        }

                        // 🔹 Add renamed files to list for OCR
                        allSplitFiles.AddRange(Directory.GetFiles(splitterOutputFolder, "*.pdf"));

                        // 5️⃣ Insert Vlex Report
                        long reportId = InsertVlexReport(conn, jobId,
                            lotId, lot.Report, lot.Volume, lot.Edition, lot.Issue, lot.Printpage,
                            lot.createadby, 1);
                    }

                    // 🔹 OCR Transaction only once after all lots
                    using (var tran = conn.BeginTransaction())
                    {
                        ocrtranid = InsertLotTransactionocr(conn, tran, batchId);
                        tran.Commit();
                    }

                    // 🔹 Create OCR paths
                    string serverocrPath = oWorkAreaService.GetServerPath(ocrtranid.ToString(), false, "serverinfo")
                                                    ?? throw new Exception("OCR server path not found");
                    string serverocrPathIn = Path.Combine(serverocrPath, "IN");
                    string serverocrPathOut = Path.Combine(serverocrPath, "OUT");

                    Directory.CreateDirectory(serverocrPathIn);
                    Directory.CreateDirectory(serverocrPathOut);

                    // 🔹 Move ALL renamed split files into OCR IN folder
                    foreach (var splitFile in allSplitFiles)
                    {
                        string destinationFilePath = Path.Combine(serverocrPathIn, Path.GetFileName(splitFile));

                        if (System.IO.File.Exists(destinationFilePath))
                            System.IO.File.Delete(destinationFilePath);

                        System.IO.File.Move(splitFile, destinationFilePath);
                    }

                    return Ok(new { message = "Lots saved/updated with transactions successfully!", lastTransactionId });
                }
            }
            catch (Exception ex)
            {
                return StatusCode(500, new { error = ex.Message });
            }
        }






        private long InsertLotTransaction(NpgsqlConnection conn, NpgsqlTransaction tran, int lotId, Guid lotkey)
        {
            int jobWorkflowId = 0;
            long transactionId = 0;

            // Get the workflow ID
            using (var cmdWF = new NpgsqlCommand(@"
        SELECT jwm.jobworkflowid 
        FROM tbllotmaster lm
        LEFT JOIN tbljobworkflowdefinition jwm 
          ON jwm.jobid = lm.jobid
        WHERE lm.lotid = @lotid AND jwm.stagename_alias='PDF Splitting'", conn, tran))
            {
                cmdWF.Parameters.AddWithValue("@lotid", lotId);
                var result = cmdWF.ExecuteScalar();
                if (result != null && result != DBNull.Value)
                    jobWorkflowId = Convert.ToInt32(result);
            }

            if (jobWorkflowId > 0)
            {
                using (var cmdTran = new NpgsqlCommand("insert_lot_transaction_details", conn, tran))
                {
                    cmdTran.CommandType = CommandType.StoredProcedure;
                    cmdTran.Parameters.AddWithValue("p_jobworkflowid", jobWorkflowId);
                    cmdTran.Parameters.AddWithValue("p_lotid", lotId);
                    cmdTran.Parameters.AddWithValue("p_lotkey", lotkey);

                    // ✅ ExecuteScalar will return the inserted transaction ID
                    var result = cmdTran.ExecuteScalar();
                    if (result != null && result != DBNull.Value)
                        transactionId = Convert.ToInt64(result);
                }
            }

            return transactionId;
        }

        private long InsertLotTransactionocr(NpgsqlConnection conn, NpgsqlTransaction tran, int lotId)
        {
            int jobWorkflowId = 0;
            long transactionId = 0;

            // Get the workflow ID
            using (var cmdWF = new NpgsqlCommand(@"
        SELECT jwm.jobworkflowid 
        FROM tbllotbatch lm
        LEFT JOIN tbljobworkflowdefinition jwm 
          ON jwm.jobid = lm.jobid
        WHERE lm.batchid = @lotid AND jwm.stagename_alias='OCR Conversion'", conn, tran))
            {
                cmdWF.Parameters.AddWithValue("@lotid", lotId);
                var result = cmdWF.ExecuteScalar();
                if (result != null && result != DBNull.Value)
                    jobWorkflowId = Convert.ToInt32(result);
            }

            if (jobWorkflowId > 0)
            {
                using (var cmdTran = new NpgsqlCommand("insert_lot_transaction_details_batch_only", conn, tran))
                {
                    cmdTran.CommandType = CommandType.StoredProcedure;
                    cmdTran.Parameters.AddWithValue("p_jobworkflowid", jobWorkflowId);
                    cmdTran.Parameters.AddWithValue("p_batchid", lotId);
                   // cmdTran.Parameters.AddWithValue("p_lotkey", lotkey);

                    // ✅ ExecuteScalar will return the inserted transaction ID
                    var result = cmdTran.ExecuteScalar();
                    if (result != null && result != DBNull.Value)
                        transactionId = Convert.ToInt64(result);
                }
            }

            return transactionId;
        }


        private void RunSplitterExe(string serverPathIn, string pdfAnalysisServerPathIn, string range, string jobId)
        {
            try
            {
                // 📌 Read exe path from appsettings.json
                //    string exePath = @"";

                oGrantFolderPermission.FolderPermission_vlex(serverPathIn, "", "", "", jobId.ToString());

                var exePath = Path.Combine(AppContext.BaseDirectory, "Tools", "Vlex_LAPS_PDF_Spiltter.exe");
                //var exePath = @"D:\pdfanalysis_vlex\Service\bin\Debug\netcoreapp3.1\Tools\Vlex_LAPS_PDF_Spiltter.exe";

                // if (string.IsNullOrEmpty(exePath) || !File.Exists(exePath))
                //  throw new FileNotFoundException("Splitter EXE path not found in configuration or file missing.", exePath);

                // 🔒 Apply folder permission before running EXE
                //oGrantFolderPermission.FolderPermission("", "", "", jobId);

                // Arguments: input folder, PDF Analysis folder, range
                var arguments = $"\"{serverPathIn},{pdfAnalysisServerPathIn},{range}\"";

                var processInfo = new ProcessStartInfo
                {
                    FileName = exePath,
                    Arguments = arguments,
                    CreateNoWindow = true,
                    UseShellExecute = false,
                    RedirectStandardOutput = true,
                    RedirectStandardError = true
                };

                using (var process = new Process { StartInfo = processInfo })
                {
                    process.Start();

                    string output = process.StandardOutput.ReadToEnd();
                    string error = process.StandardError.ReadToEnd();

                    process.WaitForExit();

                    if (process.ExitCode != 0)
                    {
                        throw new Exception($"Splitter EXE failed. Error: {error}. Output: {output}");
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"Failed to run splitter EXE for paths '{serverPathIn}' and '{pdfAnalysisServerPathIn}': {ex.Message}",
                    ex
                );
            }
        }


        private long InsertVlexReport(
            NpgsqlConnection conn,
            long jobId,
            long lotId,
            string report,
            string volume,
            string edition,
            string issue,
            string printpage,
            long createdBy,
            int active)
        {
            using (var cmd = new NpgsqlCommand("spvlexreport_insert", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("p_jobid", jobId);
                cmd.Parameters.AddWithValue("p_lotid", lotId);
                cmd.Parameters.AddWithValue("p_report", (object?)report ?? DBNull.Value);
                cmd.Parameters.AddWithValue("p_volume", (object?)volume ?? DBNull.Value);
                cmd.Parameters.AddWithValue("p_edition", (object?)edition ?? DBNull.Value);
                cmd.Parameters.AddWithValue("p_issue", (object?)issue ?? DBNull.Value);
                cmd.Parameters.AddWithValue("p_printpage", (object?)printpage ?? DBNull.Value);
                cmd.Parameters.AddWithValue("p_createdby", createdBy);
                cmd.Parameters.AddWithValue("p_active", active);

                object result = cmd.ExecuteScalar();
                return result != null && result != DBNull.Value ? Convert.ToInt64(result) : 0;
            }
        }



        public class MatchedResult
        {
            public string FilePath { get; set; }
            // Add other properties if needed
        }
        //public class LotTitleDto
        //{
        //    public string Title { get; set; }
        //    public int TotalUnits { get; set; }

        //    public int lotid { get; set; }

        //}

        public class PageDetailDto
        {
            public int JobId { get; set; }
            public int LotId { get; set; }
            public string MainPage { get; set; }
            // public string SupplementPage { get; set; }
            public int DocType { get; set; }
            public int UpdatedBy { get; set; }
            public string? Mode { get; set; }


        }



        public class LotTitleDto
        {
            public string Title { get; set; }
            public int lotid { get; set; }
            //public int TotalUnits { get; set; }

            public string mainpagerange { get; set; }
            public string supplementrypagerange { get; set; }


            public string ImgTitle { get; set; } // optional, for image title if found


            public int DefaultDocType { get; set; } = 1; // assuming 1 is main
        }

        public class LotDto
        {
            public string Report { get; set; }
            public string Volume { get; set; }
            public string Edition { get; set; }
            public string Issue { get; set; }
            public string Range { get; set; }

            public string Printpage { get; set; }

            public string LotName { get; set; }  // concatenated in Angular

            public int jobid { get; set; }
            public int? LotId { get; set; }
            public int createadby { get; set; }
        }


    }
}