VLEX/VLFEXJobcreator/VLFEXJobcreator_new/Program.cs

using CUP_POD_Mail_Reader;
using Ionic.Zip;
using iTextSharp.text;
using iTextSharp.text.pdf;
using Limilabs.FTP;
using MassTransit;
using Message.MessageTypes;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Net;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using WinSCP;
using static CUP_POD_Mail_Reader.FileDownloadingDBOperations;
using MailAddress = System.Net.Mail.MailAddress;
using MailMessage = System.Net.Mail.MailMessage;
using SmtpClient = System.Net.Mail.SmtpClient;

namespace LNBPMJobcreator
{

    public class OutLookreader
    {
        private readonly IRequestClient<Message.MessageTypes.ISimpleRequest, Message.MessageTypes.ISimpleResponse> _requestClient;
        public static string lastdate = string.Empty;
        public static DateTime? lastdate1;
        public static string reportsName = string.Empty;
        //        private static readonly string[] DatePatterns = {
        //    @"^\d{4}_\d{2}_\d{2}$",                        // 2023_06_23
        //    @"^\d{4}_\d{2}_\d{2}-\d{2}$",                  // 2023_07_05-14
        //    @"^\d{4}_\d{2}_\d{2}-\d{4}_\d{2}_\d{2}$",      // 2023_04_06-2023_08_14
        //    @"^3d-\d{4}_\d{2}_\d{2}-F$",                   // 3d-2021_07_12-F
        //    @"^\d{4}_\d{2}_\d{2} A$",                      // 2023_06_23 A
        //    @"^\d{4}_\d{2}_\d{2}_A$",                      // 2023_06_23_A
        //    @"^\d{4}\.\d{2}\.\d{2}$"                       // 2021.12.15
        //};

        private static readonly string[] DatePatterns = {
        @"^\d{4}_\d{2}_\d{2}$",                        // ID 0 → 2023_06_23
        @"^\d{4}_\d{2}_\d{2}-\d{2}$",                  // ID 1 → 2023_07_05-14
        @"^\d{4}_\d{2}_\d{2}-\d{4}_\d{2}_\d{2}$",      // ID 2 → 2023_04_06-2023_08_14
        @"^3d-\d{4}_\d{2}_\d{2}-F$",                   // ID 3 → 3d-2021_07_12-F
        @"^\d{4}_\d{2}_\d{2} [A-Z]{1}$",                      // ID 4 → 2023_06_23 A
        @"^\d{4}_\d{2}_\d{2}_(?:(?:[A-Z]{1})?)$",                      // ID 5 → 2023_06_23_A
        @"^\d{4}\.\d{2}\.\d{2}$"                       // ID 6 → 2021.12.15
    };
        // Regular expressions for folder names related to cases and opinions
        private static readonly string[] caseFolderPatterns = {
        @"\d{2}_cases",                    // 14_cases, 09_cases, etc.
        @"_Cases$",                        // *Cases
        @"Supreme Court Opinions$",       // *Supreme Court Opinions
        @"Court of Appeal Opinions$",     // *Court of Appeal Opinions
        @"Appellate Division of Superior Court Opinions$", // *Appellate Division of Superior Court Opinions
        @"Mchgn Supreme Court Cases$",    // *Mchgn Supreme Court Cases
        @"Cases Accepted by Supreme Court$" // *Cases Accepted by Supreme Court
    };
        DataTable dt = new DataTable();
        FileDownloadingDBOperations dbOperation = new FileDownloadingDBOperations();

        MessageId messageid = new MessageId();
        public string LogFile = AppDomain.CurrentDomain.BaseDirectory + "\\CUP_POD_LogFile.txt";
        string filenamechange = string.Empty;
        FTPServerDbOperations ftpServerDb = new FTPServerDbOperations();
        FileDownloadingDBOperations dbOperatios = new FileDownloadingDBOperations();
        string ftpDorectory;
        public bool serverdownloaded;
        long fileSize;
        public static string _lastFileName;
        DateTime? startdownloadtime = null;
        public static int jobid = 0;
        public static int lotid = 0;
        public static int tranid = 0;
        string parentPath = string.Empty;
        string sourcePath = string.Empty;
        //string reportname = string.Empty;
        int bmpcount;
        public bool fileexixts;
        List<DownloadedFile> listdownloadFile = new List<DownloadedFile>();
        public static bool isdownloading = false;

        //public bool GenerateMail(string projectcode, List<DataTable> dataTables)
        //{
        //    string mailBody = string.Empty;

        //    mailBody = "<div align=\"left\">";
        //    mailBody += "<font face=\"calibre\" style=\"color:black;font-size:15px;\">Dear Team,</font><br/><br/>";
        //    mailBody += "<p align=\"left\" style=\"padding-left:2px;\"><font face=\"calibre\" style=\"color:black;font-size:15px;\">The list of files and attachments are downloaded successfully.</font></p>";
        //    mailBody += "</div>";

        //    // Process each DataTable
        //    foreach (var DT in dataTables)
        //    {
        //        if (DT != null && DT.Rows.Count > 0)
        //        {
        //            string tableRows = string.Empty;

        //            // Add table headers
        //            tableRows += "<table border=\"1\" height=\"60\" width=\"400\" style=\"border:1px solid black;border-collapse: collapse\">";
        //            tableRows += "<tr><td><b>File Name</b></td><td><b>Size</b></td><td><b>StartTime</b></td><td><b>Status</b></td></tr>\r\n";

        //            // Iterate over each row in the DataTable and build the table rows
        //            foreach (DataRow row in DT.Rows)
        //            {
        //                string status = Convert.ToBoolean(row["DownloadStatus"]) ? "Success" : "Fail";
        //                tableRows += "<tr>";
        //                tableRows += "<td>" + row["FileName"] + "</td>";
        //                tableRows += "<td>" + row["totalFileSizeinLocal"] + "</td>";
        //                tableRows += "<td>" + row["DownloadingStartTime"] + "</td>";
        //                tableRows += "<td>" + status + "</td>";
        //                tableRows += "</tr>";
        //            }

        //            tableRows += "</table>";

        //            // Add the table to the email body
        //            mailBody += tableRows;
        //        }
        //    }

        //    // Add the footer to the email body
        //    mailBody += "<div align=\"left\"><br/>";
        //    mailBody += "<font face=\"calibre\" size=\"3\">Thanks & Regards,</font><br/>";

        //    // Add different sign-offs based on project code
        //    if (projectcode == "TF-01")
        //        mailBody += "<font face=\"calibre\" size=\"3\">T&F_US-TypeSetting Team</font><br/>";
        //    else if (projectcode == "CUP")
        //        mailBody += "<font face=\"calibre\" size=\"3\">CUP-TypeSetting Team</font><br/>";
        //    else if (projectcode == "CUP_POD")
        //        mailBody += "<font face=\"calibre\" size=\"3\">CUP POD-TypeSetting Team</font><br/>";
        //    else if (projectcode == "LN_BPM")
        //        mailBody += "<font face=\"calibre\" size=\"3\">LN-BPM Team</font><br/>";

        //    mailBody += "<font face=\"calibre\" size=\"3\">Lumina Datamatics Ltd - Puducherry</font><br/>";
        //    mailBody += "<font face=\"calibre\" size=\"3\">Tel: 0413 - 6604503/6604530.</font><br/>";
        //    mailBody += "<p align=\"left\"><font face=\"calibre\" style=\"font-size:12px;\">Note: This is a system generated email, do not reply to this email.</font></p>";
        //    mailBody += "</div>";

        //    // Send the email using the SendEmail method
        //    bool autoGeneratedMailStatus = SendEmail(projectcode, mailBody, string.Empty);
        //    if (autoGeneratedMailStatus)
        //    {
        //        Console.WriteLine("An auto-generated mail will be sent regarding the downloaded status.");
        //    }

        //    return autoGeneratedMailStatus;
        //}

        public static void SendEmail(string jobNo, int totalLots, DateTime createDateTime)
        {
            try
            {
                string fromAddress = "laps.projects@luminad.com";
                string toAddressesRaw = ConfigurationManager.AppSettings["EmailToAddresses"];
                if (string.IsNullOrWhiteSpace(toAddressesRaw))
                {
                    throw new InvalidOperationException("No To addresses configured in app settings (EmailToAddresses).");
                }

                // Split into individual email strings, trim whitespace
                var toAddresses = toAddressesRaw
                    .Split(new[] { ';', ',' }, StringSplitOptions.RemoveEmptyEntries)
                    .Select(addr => addr.Trim())
                    .Where(addr => !string.IsNullOrWhiteSpace(addr)); // remove any empty entries

                string smtpHost = "smtppro.zoho.in";
                int smtpPort = 587;
                string smtpUser = "laps.projects@luminad.com";
                string smtpPass = "lap@cdgLD687";

                using (MailMessage mail = new MailMessage())
                {
                    mail.From = new MailAddress(fromAddress);
                    foreach (var addr in toAddresses)
                    {
                        // Either use this:
                        mail.To.Add(new MailAddress(addr));
                        // or directly: mail.To.Add(addr); // there's an overload that takes string
                    }
                    mail.Subject = "WMS INVENTORY CREATION NOTIFICATION";
                    mail.IsBodyHtml = true;

                    mail.Body = $@"
                <html>
                <head>
                    <style>
                        table {{
                            border-collapse: collapse;
                            width: 70%;
                            font-family: Arial, sans-serif;
                            font-size: 14px;
                        }}
                        th {{
                            background-color: #0078D7;
                            color: white;
                            padding: 8px;
                            text-align: center;
                        }}
                        td {{
                            border: 1px solid #ddd;
                            padding: 8px;
                            text-align: center;
                        }}
                    </style>
                </head>
                <body>
                    <h3>VLEX CREATION NOTIFICATION</h3>
                    <table>
                        <tr>
                            <th>Job No</th>
                            <th>Total Lots</th>
                            <th>Create Date-time</th>
                        </tr>
                        <tr>
                            <td>{jobNo}</td>
                            <td>{totalLots}</td>
                            <td>{createDateTime}</td>
                        </tr>
                    </table>
                </body>
                </html>";

                    using (SmtpClient smtp = new SmtpClient(smtpHost, smtpPort))
                    {
                        smtp.Credentials = new NetworkCredential(smtpUser, smtpPass);
                        smtp.EnableSsl = true;
                        smtp.Send(mail);
                    }
                }

                Console.WriteLine("✅ Email sent successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ Failed to send email: {ex.Message}");
            }
        }


        public static void SendEmail(string subject, string bodyContent)
        {
            try
            {
                string fromAddress = "laps.projects@luminad.com";

                // Read "To" addresses from config
                string toAddressesRaw = ConfigurationManager.AppSettings["EmailToAddresses"];
                if (string.IsNullOrWhiteSpace(toAddressesRaw))
                {
                    throw new InvalidOperationException("No To addresses configured in app settings (EmailToAddresses).");
                }

                // Split into individual email strings, trim whitespace
                var toAddresses = toAddressesRaw
                    .Split(new[] { ';', ',' }, StringSplitOptions.RemoveEmptyEntries)
                    .Select(addr => addr.Trim())
                    .Where(addr => !string.IsNullOrWhiteSpace(addr)); // remove any empty entries

                string smtpHost = "smtppro.zoho.in";
                int smtpPort = 587;
                string smtpUser = "laps.projects@luminad.com";
                string smtpPass = "lap@cdgLD687";  // (you might want to move this to config/secure storage)

                // Build styled HTML body
                string body = $@"
<html>
<head>
  <style>
    body {{
      font-family: Arial, sans-serif;
      margin: 20px;
      font-size: 14px;
      color: #333;
    }}
    h2 {{
      color: #0073C0;
      text-align: center;
    }}
    table {{
      border-collapse: collapse;
      width: 80%;
      margin: 20px auto;
    }}
    th, td {{
      border: 1px solid #ddd;
      padding: 10px;
      text-align: center;
    }}
    th {{
      background-color: #0073C0;
      color: white;
    }}
  </style>
</head>
<body>
  <h2>📢 Notification</h2>
  <div>{bodyContent}</div>
</body>
</html>";

                using (MailMessage mail = new MailMessage())
                {
                    mail.From = new MailAddress(fromAddress);

                    // Add each "To" address
                    foreach (var addr in toAddresses)
                    {
                        // Either use this:
                        mail.To.Add(new MailAddress(addr));
                        // or directly: mail.To.Add(addr); // there's an overload that takes string
                    }

                    mail.Subject = subject;
                    mail.Body = body;
                    mail.IsBodyHtml = true;

                    using (SmtpClient smtp = new SmtpClient(smtpHost, smtpPort))
                    {
                        smtp.Credentials = new NetworkCredential(smtpUser, smtpPass);
                        smtp.EnableSsl = true;
                        smtp.Send(mail);
                    }
                }

                Console.WriteLine("✅ Email sent successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ Failed to send email: {ex.Message}");
            }
        }



        public static void SendEmail(string subject, string folderPath, string fileCount)
        {
            try
            {
                string fromAddress = "laps.projects@luminad.com";
                string toAddressesRaw = ConfigurationManager.AppSettings["EmailToAddresses"];
                if (string.IsNullOrWhiteSpace(toAddressesRaw))
                {
                    throw new InvalidOperationException("No To addresses configured in app settings (EmailToAddresses).");
                }

                // Split into individual email strings, trim whitespace
                var toAddresses = toAddressesRaw
                    .Split(new[] { ';', ',' }, StringSplitOptions.RemoveEmptyEntries)
                    .Select(addr => addr.Trim())
                    .Where(addr => !string.IsNullOrWhiteSpace(addr)); // remove any empty entries

                string smtpHost = "smtppro.zoho.in";
                int smtpPort = 587;
                string smtpUser = "laps.projects@luminad.com";
                string smtpPass = "lap@cdgLD687";           // your app password

                // ✅ HTML body with styled table
                string body = $@"
        <html>
        <head>
          <style>
            table {{
              border-collapse: collapse;
              width: 80%;
              font-family: Arial, sans-serif;
              margin: 20px auto;
              font-size: 14px;
            }}
            th {{
              background-color: #0073C0;
              color: white;
              padding: 10px;
              text-align: center;
            }}
            td {{
              border: 1px solid #ddd;
              padding: 10px;
              text-align: center;
            }}
            h2 {{
              text-align: center;
              font-family: Arial, sans-serif;
              color: #0073C0;
            }}
          </style>
        </head>
        <body>
          <h2>📢 BMP FILE UPDATE NOTIFICATION</h2>
          <table>
            <tr>
              <th>Folder Path</th>
              <th>Total Files Updated</th>
              <th>Create Date-Time</th>
            </tr>
            <tr>
              <td>{folderPath}</td>
              <td>{fileCount}</td>
              <td>{DateTime.Now}</td>
            </tr>
          </table>
        </body>
        </html>";

                using (MailMessage mail = new MailMessage())
                {
                    mail.From = new MailAddress(fromAddress);
                    foreach (var addr in toAddresses)
                    {
                        // Either use this:
                        mail.To.Add(new MailAddress(addr));
                        // or directly: mail.To.Add(addr); // there's an overload that takes string
                    }
                    mail.Subject = subject;
                    mail.Body = body;
                    mail.IsBodyHtml = true;

                    using (SmtpClient smtp = new SmtpClient(smtpHost, smtpPort))
                    {
                        smtp.Credentials = new NetworkCredential(smtpUser, smtpPass);
                        smtp.EnableSsl = true;
                        smtp.Send(mail);
                    }
                }

                Console.WriteLine("✅ Email sent successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ Failed to send email: {ex.Message}");
            }
        }


        public List<DownloadedFile> DownloadFromFTP1(string projectCode, string dwnserverpath, bool batch_folder)
        {

            List<DataTable> tableList = new List<DataTable>();

            string localWorkRoot = ConfigurationManager.AppSettings["localExtractRoot"].ToString();
            //  string reportsname = string.Empty;
            if (isdownloading)
            {
                Console.WriteLine("A folder is downloading kindly wait");
                return null;
            }
            else
            {
                try
                {
                   // isdownloading = true;
                    string projectid = FileDownloadingDBOperations.getscallervalue("SELECT projectdefinitionid::varchar FROM tblprojectdefinition WHERE project_subtitle='" + projectCode.Trim() + "' AND active=1", null);
                    //   Call_Msmq_watcher("130089", "0", "checkout_apps", "A");
                    if (listdownloadFile.Count() > 0)
                    {
                        listdownloadFile.Clear();
                    }
                    Console.WriteLine("Downloading files from FTP server...");
                    string privateKeyPath = ConfigurationManager.AppSettings["filepath"];
                    string qrys = @"SELECT hostname,username,password FROM tblftpcredentials where project_code='" + projectCode + "' ";
                    DataTable ftpcredentials = dbOperation.getdatatable(qrys);
                    Console.WriteLine("Credentials got ...");
                    var sessionOptions = new SessionOptions
                    {
                        Protocol = Protocol.Sftp,
                        HostName = ftpcredentials.Rows[0]["hostname"].ToString(),
                        UserName = ftpcredentials.Rows[0]["username"].ToString(),

                        //HostName="sftp.fastcase.com",
                        //UserName="sanaullah.noorullah@luminad.com",
                        PrivateKeyPassphrase = "orGpyaxejx!2MMs",

                        PortNumber = 22,
                       // SshPrivateKeyPath = @"C:\Users\kiruthiga.p\Downloads\VLEX FTP Details\VLEX FTP Details\sanaullah_noorullah_old.ppk",
                      SshPrivateKeyPath = privateKeyPath,
                        GiveUpSecurityAndAcceptAnySshHostKey = true
                    };

                    using (WinSCP.Session session = new WinSCP.Session())
                    {
                        TransferOptions TO = new TransferOptions();
                        Console.WriteLine("Connecting to FTP...");
                        session.FileTransferProgress += SessionFileTransferProgress;
                        session.Open(sessionOptions);

                        Console.WriteLine("Session opened.");
                        // ✅ Get allowed directories + their last processed date from DB
                        Dictionary<string, string> GetAllowedDirectoriesFromDB()
                        {
                            var dbOps = new FileDownloadingDBOperations();
                            string query = "SELECT reportname, datetocheck AS last_date FROM tblreportcheck WHERE active = 1";
                            DataTable dt = dbOps.getdatatable(query);

                            if (dt == null || dt.Rows.Count == 0)
                            {
                                Console.WriteLine("No data returned from database.");
                                return new Dictionary<string, string>();
                            }

                            //var result = dt.AsEnumerable()
                            //    .Where(r => !r.IsNull("reportname") && !string.IsNullOrWhiteSpace(r["reportname"].ToString()))
                            //    .ToDictionary(
                            //        r => r["reportname"].ToString().Trim(),
                            //        r => !r.IsNull("last_date") && DateTime.TryParse(r["last_date"].ToString(), out DateTime d) ? d : DateTime.MinValue
                            //    );

                            var result = dt.AsEnumerable()
       .Where(r => !r.IsNull("reportname") && !string.IsNullOrWhiteSpace(r["reportname"].ToString()))
       .ToDictionary(
           r => r["reportname"].ToString().Trim(),
           r => !r.IsNull("last_date")
                ? r["last_date"].ToString().Trim()   // keep as string
                : string.Empty                       // default if null
       );
                            return result;

                        }
                        // ✅ Load allowed directories from DB
                        var allowedDirs = GetAllowedDirectoriesFromDB();
                        if (allowedDirs.Count() > 0)
                        {
                            foreach (var data in allowedDirs)
                            {

                                //string reportsName = string.Empty;
                               // reportsName = "Maryland";
                               reportsName=data.Key;
                                string reportName1 = data.Key;
                                // Step 1: Locate "Hawai'i Reports" directory
                                string root = "/" + reportName1 + "";
                                lastdate = string.Empty;
                                lastdate = data.Value;
                                var remotePathss = session.EnumerateRemoteFiles("/", "*", EnumerationOptions.None)
                                 .Where(f => !f.IsDirectory);


                                RemoteDirectoryInfo rootList = session.ListDirectory(root);
                                //session.Open(sessionOptions);
                                Console.WriteLine("Searching recursively for folders with today's date...");

                                //List<RemoteFileInfo> allFiles = GetAllFiles(session, "/");

                                // This searches recursively and filters remotely using WinSCP file mask
                                //var remotePaths = session.EnumerateRemoteFiles(
                                //    "/",
                                //    $"*2025_09_09*/", // find folders with today’s date in name
                                //    EnumerationOptions.AllDirectories | EnumerationOptions.MatchDirectories);

                                ProcessDirectory(session, root, rootList, localWorkRoot);

                            }
                        }
                    }
                }


                catch (System.Exception ex)
                {
                    isdownloading = false;
                    Console.Write(ex);
                    return null;
                }
                return listdownloadFile;
            }
            isdownloading = false;
        }


        public object Call_Msmq_watcher(string p_tranid, string p_userid, string checkout, string cfalg)
        {
            RabbitMq sendmsg = new RabbitMq(_requestClient);
            sendmsg.SendMq_StageComplete(p_tranid.ToString(), p_userid, checkout, "server", "", cfalg, "");
            return true;
        }


        public void SessionFileTransferProgress(object sender, FileTransferProgressEventArgs e)
        {
            Console.WriteLine("try this");
            try
            {
                if ((_lastFileName != null) && (_lastFileName != e.FileName))
                {
                    Console.WriteLine();
                }
                string str = string.Format("\r{0} \r\n({1:P0})", e.FileName, e.FileProgress);
                File.AppendAllText(LogFile, str);
                Console.WriteLine(str);
                _lastFileName = e.FileName;
            }
            catch (System.Exception ex)
            {
                isdownloading = false;

                Console.WriteLine(ex);
            }
        }


        public static List<RemoteFileInfo> GetAllFiles(Session session, string remotePath)
        {
            var allFiles = new List<RemoteFileInfo>();
            try
            {
                RemoteDirectoryInfo dir = session.ListDirectory(remotePath);

                foreach (RemoteFileInfo file in dir.Files)
                {
                    // Skip "." and ".." directories
                    if (file.Name == "." || file.Name == "..")
                        continue;

                    if (file.IsDirectory)
                    {
                        // 🔁 Recurse into subdirectories
                        string subPath = remotePath.TrimEnd('/') + "/" + file.Name;
                        allFiles.AddRange(GetAllFiles(session, subPath));
                    }
                    else
                    {
                        allFiles.Add(file);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ Error while listing {remotePath}: {ex.Message}");
            }

            return allFiles;
        }

        static string FindCasesFolder(Session session, string parentPath, string localWorkRoot)
        {
            try
            {
                // ✅ Step 1: First search locally (parentPath could already have extracted ZIPs)
                var foundLocal = Directory.GetDirectories(parentPath, "*", SearchOption.AllDirectories)
                                          .OrderBy(d =>
                                          {
                                              string lname = Path.GetFileName(d).ToLowerInvariant();
                                              if (lname == "09_cases") return 0;
                                              if (lname == "14_cases") return 1;
                                              return 2;
                                          })
                                          .FirstOrDefault(d =>
                                              d.EndsWith("cases", StringComparison.OrdinalIgnoreCase) ||
                                              d.ToLowerInvariant().Contains("supreme court opinions") ||
                                              d.ToLowerInvariant().Contains("court of appeal opinions") ||
                                              d.ToLowerInvariant().Contains("appellate division of superior court opinions") ||
                                              d.ToLowerInvariant().Contains("mchgn supreme court cases") ||
                                              d.ToLowerInvariant().Contains("cases accepted by supreme court"));

                if (foundLocal != null)
                {
                    Console.WriteLine($"📂 Found cases locally: {foundLocal}");
                    return foundLocal;
                }

                // ✅ Step 2: If not found, check remote directory for zip files
                RemoteDirectoryInfo list = session.ListDirectory(parentPath);

                foreach (RemoteFileInfo file in list.Files)
                {
                    if (!file.IsDirectory && file.Name.EndsWith(".zip", StringComparison.OrdinalIgnoreCase))
                    {
                        string remoteZipPath = BuildRemotePath(parentPath, file.Name);
                        string extractPath = Path.Combine(localWorkRoot, Path.GetFileNameWithoutExtension(file.Name));

                        Console.WriteLine($"📦 Extracting zip: {file.Name} → {extractPath}");
                        OutLookreader.ExtractZip(session, remoteZipPath, extractPath);

                        // ✅ After extraction, search inside extracted folder
                        var foundInZip = Directory.GetDirectories(extractPath, "*", SearchOption.AllDirectories)
                                                  .OrderBy(d =>
                                                  {
                                                      string lname = Path.GetFileName(d).ToLowerInvariant();
                                                      if (lname == "09_cases") return 0;
                                                      if (lname == "14_cases") return 1;
                                                      return 2;
                                                  })
                                                  .FirstOrDefault(d =>
                                                      d.EndsWith("cases", StringComparison.OrdinalIgnoreCase) ||
                                                      d.ToLowerInvariant().Contains("supreme court opinions") ||
                                                      d.ToLowerInvariant().Contains("court of appeal opinions") ||
                                                      d.ToLowerInvariant().Contains("appellate division of superior court opinions") ||
                                                      d.ToLowerInvariant().Contains("mchgn supreme court cases") ||
                                                      d.ToLowerInvariant().Contains("cases accepted by supreme court"));

                        if (foundInZip != null)
                        {
                            Console.WriteLine($"📂 Found cases inside extracted zip: {foundInZip}");
                            return foundInZip;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ FindCasesFolder failed for '{parentPath}': {ex.Message}");
            }

            return null; // nothing found
        }




        static string FindLocalCasesFolder(string parentPath)
        {
            try
            {
                // ✅ Search in local Parent path for priority cases folders
                var found = Directory.GetDirectories(parentPath, "*", SearchOption.AllDirectories)
                                     .OrderBy(d =>
                                     {
                                         string lname = Path.GetFileName(d).ToLowerInvariant();
                                         if (lname == "09_cases") return 0;
                                         if (lname == "14_cases") return 1;
                                         return 2;
                                     })
                                     .FirstOrDefault(d =>
                                         d.EndsWith("cases", StringComparison.OrdinalIgnoreCase) ||
                                         d.ToLowerInvariant().Contains("supreme court opinions") ||
                                         d.ToLowerInvariant().Contains("court of appeal opinions") ||
                                         d.ToLowerInvariant().Contains("appellate division of superior court opinions") ||
                                         d.ToLowerInvariant().Contains("mchgn supreme court cases") ||
                                         d.ToLowerInvariant().Contains("cases accepted by supreme court"));

                if (found != null)
                {
                    Console.WriteLine($"📂 Found local cases folder: {found}");
                    return found;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ FindLocalCasesFolder failed for '{parentPath}': {ex.Message}");
            }

            return null;
        }




        static void ProcessDirectory(Session session, string remotePath, RemoteDirectoryInfo list, string localWorkRoot, bool isTopLevel = true)
        {
            //RemoteDirectoryInfo list;,
            string reportsName = string.Empty;
            string reportid = string.Empty;

            if (isTopLevel == false)
            {
                try
                {
                    list = session.ListDirectory(remotePath);
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Failed to list '{remotePath}': {ex.Message}");
                    return;
                }
            }


            // Separate date folders, non-date folders, and zip files
            List<RemoteFileInfo> dateFolders = new List<RemoteFileInfo>();
            List<RemoteFileInfo> nonDateFolders = new List<RemoteFileInfo>();
            List<RemoteFileInfo> zipFiles = new List<RemoteFileInfo>();

            // Directories to ignore at top level
            //string[] ignoreTopLevelDirs = { "DCL", "Massachusetts", "Paginated Reporters" };
            //var files = from p in list.Files
            //            where p.IsDirectory
            //            && p.Name != "."
            //            && p.Name != ".."
            //            select p;
            //if (files.Count() > 0)
            //{

            foreach (var obj in list.Files)
            {
                var file = obj as RemoteFileInfo;
                if (file == null || file.Name == "." || file.Name == "..") continue;

                // ✅ Skip ignored directories at top-level only
                //if (isTopLevel && file.IsDirectory &&
                //    ignoreTopLevelDirs.Any(d => file.Name.Equals(d, StringComparison.OrdinalIgnoreCase)))
                //{
                //    Console.WriteLine($"Skipping top-level directory: {file.Name}");
                //    continue;
                //}

                //string[] parts = file.FullName.TrimStart('/', '\\')
                //    .Split(new[] { '/', '\\' }, StringSplitOptions.RemoveEmptyEntries);
                //reportsName = parts.Length > 0 ? parts[0] : string.Empty;

                if (file.IsDirectory && IsDateFolder(file.Name))
                {
                    int regexid = GetMatchedPatternId(file.Name);
                    string folderDate = NormalizeToStandardDate(file.Name);
                    //if (TryGetFolderDate(file.Name, out DateTime folderDate))
                    //{
                    lastdate1 = DateTime.ParseExact(lastdate, "yyyy_MM_dd", CultureInfo.InvariantCulture);
                    DateTime folderDate_out = DateTime.ParseExact(folderDate, "yyyy_MM_dd", CultureInfo.InvariantCulture);
                    Console.WriteLine("date folder: {" + lastdate1 + "}");
                    if (folderDate_out >= lastdate1)
                    {
                        dateFolders.Add(file);
                    }
                    else
                    {
                        Console.WriteLine($"🕒 Skipping old date folder {file.Name} (< {lastdate:yyyy-MM-dd})");
                    }
                    //}
                    //else
                    //{
                    //    Console.WriteLine($"⚠️ Could not parse date folder: {file.Name}");
                    //}
                }
                else if (file.IsDirectory)
                {
                    nonDateFolders.Add(file);
                }
                else if (file.Name.EndsWith(".zip", StringComparison.OrdinalIgnoreCase))
                {
                    zipFiles.Add(file);
                }
            }
            //}
            // Process date folders
            foreach (var dateFolder in dateFolders)
            {
                string dateRemotePath = BuildRemotePath(remotePath, dateFolder.Name);
                //Console.WriteLine($"\n=== Processing DATE folder: {dateRemotePath} ===");
                //string dateRemotePath = BuildRemotePath(session,"/Maryland/2025_09_26", "Maryland");
                // Process each date folder: find cases and BMPs
                 ProcessDateFolder(session, dateRemotePath, localWorkRoot);
               // ProcessDateFolder(session, "/Maryland/2025_10_17", localWorkRoot);
                //ProcessDateFolder(session, "/North Eastern Reporter/N.E.3d/2025_08_13", localWorkRoot, "North Eastern Reporter");



            }

            // Recurse into non-date folders
            foreach (var nonDate in nonDateFolders)
            {
                string nonDateRemote = BuildRemotePath(remotePath.ToString(), nonDate.Name);
                Console.WriteLine($"\n--- Recursing into non-date folder: {nonDateRemote} ---");
                ProcessDirectory(session, nonDateRemote, list, localWorkRoot, false);

                ProcessDateFolder(session,"","");
            }

            // Handle zip files at this level (optional, skip top-level)
            foreach (var zip in zipFiles)
            {
                string zipRemote = BuildRemotePath(remotePath, zip.Name);
                if (isTopLevel)
                {
                    Console.WriteLine($"Skipping top-level zip: {zipRemote}");
                }
                else
                {
                    try
                    {
                        string extractPath = Path.Combine(localWorkRoot, Path.GetFileNameWithoutExtension(zip.Name));
                        OutLookreader.ExtractZip(session, zipRemote, extractPath);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Failed to extract zip '{zipRemote}': {ex.Message}");
                    }
                }
            }
        }
        public static bool TryGetFolderDate(string fileName, out DateTime folderDate)
        {
            folderDate = DateTime.MinValue;

            if (string.IsNullOrWhiteSpace(fileName))
                return false;

            string cleaned = fileName.Trim();

            // Replace separators
            cleaned = cleaned.Replace("_", "-").Replace(".", "-");

            // Split by special characters (for cases like "3d-2021_07_12-F")
            string[] parts = cleaned.Split(new char[] { '-', ' ', 'A', 'B', 'C', 'D', 'F' },
                                           StringSplitOptions.RemoveEmptyEntries);

            foreach (var part in parts)
            {
                if (DateTime.TryParseExact(part,
                    new[] { "yyyy-MM-dd", "yyyy.MM.dd", "yyyy_MM_dd", "yyyyMMdd" },
                    CultureInfo.InvariantCulture,
                    DateTimeStyles.None,
                    out folderDate))
                {
                    return true;
                }
            }

            // Try fallback full parse
            if (DateTime.TryParse(cleaned, out folderDate))
            {
                return true;
            }

            // If all fails, return false
            return false;
        }
        public static string NormalizeToStandardDate(string fileName)
        {
            if (string.IsNullOrWhiteSpace(fileName))
                return null;

            // Extract possible date part
            string cleaned = fileName.Trim();

            // Replace common separators with '-'
            cleaned = cleaned.Replace("_", "-").Replace(".", "-");

            // Handle ranges or suffixes like “2023-07-05-14” or “3d-2021-07-12-F”
            // Keep only the first valid date portion
            string[] parts = cleaned.Split(new char[] { '-', ' ', 'A', 'B', 'C', 'D', 'F' },
                                           StringSplitOptions.RemoveEmptyEntries);

            DateTime parsedDate;
            foreach (var part in parts)
            {
                if (DateTime.TryParseExact(part,
                    new[] { "yyyy-MM-dd", "yyyy-MM", "yyyyMMdd", "yyyy.MM.dd", "yyyy_MM_dd" },
                    CultureInfo.InvariantCulture,
                    DateTimeStyles.None,
                    out parsedDate))
                {
                    return parsedDate.ToString("yyyy_MM_dd");
                }
            }

            // Fallback: try general parse if specific formats fail
            if (DateTime.TryParse(cleaned, out parsedDate))
            {
                return parsedDate.ToString("yyyy_MM_dd");
            }

            // If all fails, return today's date (or you could return null)
            return DateTime.Now.ToString("yyyy_MM_dd");
        }
        static string FindCasesFolderOnFtp(Session session, string parentRemotePath)
        {
            try
            {
                RemoteDirectoryInfo list = session.ListDirectory(parentRemotePath);

                // collect candidate case folders with priority
                var candidateDirs = list.Files
                    .Where(f => f.IsDirectory)
                    .Select(f => new
                    {
                        Info = f,
                        Priority = GetPriority(f.Name)
                    })
                    .Where(x => x.Priority >= 0) // only case-like folders
                    .OrderBy(x => x.Priority)
                    .ToList();

                if (candidateDirs.Any())
                {
                    var best = candidateDirs.First();
                    string remoteCasesPath = BuildRemotePath(parentRemotePath, best.Info.Name);
                    Console.WriteLine($"📂 Found cases folder on FTP: {remoteCasesPath} (priority {best.Priority})");
                    return remoteCasesPath;
                }

                // Step 2: No direct cases folder → look for ZIP file
                foreach (RemoteFileInfo file in list.Files)
                {
                    if (!file.IsDirectory && file.Name.EndsWith(".zip", StringComparison.OrdinalIgnoreCase))
                    {
                        string remoteZipPath = BuildRemotePath(parentRemotePath, file.Name);
                        Console.WriteLine($"📦 Found zip file on FTP: {remoteZipPath}");
                        return remoteZipPath; // caller should handle extraction
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ FindCasesFolderOnFtp failed for '{parentRemotePath}': {ex.Message}");
            }

            return null; // Nothing found
        }

        /// <summary>
        /// Assign priority for cases-like folder names.
        /// </summary>
        static int GetPriority(string folderName)
        {
            string lname = folderName.ToLowerInvariant();

            if (lname == "09_cases") return 0;
            if (lname == "14_cases") return 1;

            if (lname.EndsWith("cases") ||
                lname.Contains("supreme court opinions") ||
                lname.Contains("court of appeal opinions") ||
                lname.Contains("appellate division of superior court opinions") ||
                lname.Contains("mchgn supreme court cases") ||
                lname.Contains("cases accepted by supreme court"))
                return 2;

            return -1; // not a cases folder
        }


        // -------------------------
        // Process date folder logic
        // -------------------------
        //     static void ProcessDateFolder(Session session, string dateRemotePath, string localWorkRoot)
        //     {
        //         try
        //         {
        //             int bmpcount = 0;
        //             string dateFolderName = Path.GetFileName(dateRemotePath);
        //             var dbOps = new FileDownloadingDBOperations();

        //             // 🔹 Get project and reporter info
        //             string projectCode = ConfigurationManager.AppSettings["VLEX"].ToString();
        //             string projectid = FileDownloadingDBOperations.getscallervalue(
        //                 $"SELECT projectdefinitionid::varchar FROM tblprojectdefinition WHERE project_subtitle='{projectCode.Trim()}'"
        //             );
        //             string reportid = FileDownloadingDBOperations.getscallervalue(
        //                 $"SELECT reporter_key::varchar FROM tblvlexreporterkey WHERE reporter_name='{reportsName}'"
        //             );

        //             string reportidname = FileDownloadingDBOperations.getscallervalue(
        //                $"SELECT reporterid::varchar FROM tblvlexreporterkey WHERE reporter_name='{reportsName}'");

        //             string jobTitle = $"{reportid}_2025_09_26";
        //             string lotTitle = $"{reportid}_2025_09_26";

        //             lotTitle = string.Concat(lotTitle, "01");

        //             // ✅ Check if job already exists
        //             string existingJobId = FileDownloadingDBOperations.getscallervalue(
        //                 $"SELECT jobid::varchar FROM tbljob WHERE jobname = '{jobTitle}'"
        //             );
        //             if (!string.IsNullOrEmpty(existingJobId))
        //             {
        //                 Console.WriteLine($"Job '{jobTitle}' already exists. Skipping folder.");
        //                 return;
        //             }

        //             // 1️⃣ List all files/folders in date folder
        //             RemoteDirectoryInfo dateFolderList = session.ListDirectory(dateRemotePath);

        //             // 2️⃣ Check for ZIP files
        //             var zipFiles = dateFolderList.Files
        //                             .Where(f => !f.IsDirectory && f.Name.EndsWith(".zip", StringComparison.OrdinalIgnoreCase))
        //                             .ToList();

        //             // If ZIPs exist but none updated today → skip folder
        //             // var zipFilesToday = zipFiles.Where(z => z.LastWriteTime.Date == DateTime.Today).ToList();
        //             //           var zipFilesToday = zipFiles
        //             //.Where(z => z.LastWriteTime.Date == DateTime.Today.AddDays(-1))
        //             //.ToList();

        //             var zipFilesToday = zipFiles
        //.Where(z => z.LastWriteTime.Date == DateTime.Today)
        //.ToList();
        //             // var zipFilesToday = new List<string> { "NW2d - 2022_11_01.zip" };

        //             if (zipFiles.Count > 0 && zipFilesToday.Count == 0)
        //             {
        //                 Console.WriteLine($"ZIP files exist but none updated today in {dateRemotePath}. Skipping folder.");
        //                 return; // skip this date folder entirely
        //             }

        //             // 3️⃣ Check for BMP if no ZIP today
        //             string casesFolderRemotePath = null;
        //             bool bmpUpdatedToday = false;
        //             List<RemoteFileInfo> bmpFilesToday = null;

        //             if (zipFilesToday.Count == 0)
        //             {
        //                 casesFolderRemotePath = FindCasesFolderOnFtp(session, dateRemotePath);

        //                 if (casesFolderRemotePath != null)
        //                 {
        //                     // 🔎 Get BMPs updated yesterday (including subfolders)
        //                     //bmpFilesToday = GetTodayUpdatedBmps(session, casesFolderRemotePath);
        //                     //bmpUpdatedToday = GetTodayUpdatedBmps(session, casesFolderRemotePath);
        //                     //if (bmpUpdatedToday == false)
        //                     //{

        //                     //    return;
        //                     //}
        //                     (bool found, int count) = GetTodayUpdatedBmps(session, casesFolderRemotePath);
        //                     bmpUpdatedToday = found;
        //                     if (!found)
        //                     {
        //                         Console.WriteLine("❌ No BMPs updated yesterday.");
        //                         //return;
        //                     }
        //                     bmpcount = count;
        //                     Console.WriteLine($"✅ Total BMPs found: {count}");
        //                     //Console.WriteLine($"📂 Found {bmpFilesToday.Count} BMP files updated yesterday in {casesFolderRemotePath}.");

        //                     // proceed with job creation, email sending, etc.
        //                     //foreach (var bmp in bmpFilesToday)
        //                     //{
        //                     //    Console.WriteLine($"➡ {bmp.FullName} ({bmp.LastWriteTime})");
        //                     //}
        //                 }
        //                 else
        //                 {
        //                     Console.WriteLine($"No ZIP or cases folder found in {dateRemotePath}. Skipping folder.");
        //                     return;
        //                 }
        //             }



        //             //            if (zipFilesToday.Count == 0)
        //             //            {
        //             //                casesFolderRemotePath = FindCasesFolderOnFtp(session, dateRemotePath);
        //             //                if (casesFolderRemotePath != null)
        //             //                {
        //             //                    RemoteDirectoryInfo casesList = session.ListDirectory(casesFolderRemotePath);
        //             //                    // bmpUpdatedToday = casesList.Files
        //             //                    //                    .Any(f => !f.IsDirectory &&
        //             //                    //                              f.Name.EndsWith(".bmp", StringComparison.OrdinalIgnoreCase) &&
        //             //                    //                              f.LastWriteTime.Date == DateTime.Today);

        //             //                    //  bmpFilesToday = casesList.Files
        //             //                    //.Where(f => !f.IsDirectory &&
        //             //                    //            f.Name.EndsWith(".bmp", StringComparison.OrdinalIgnoreCase) &&
        //             //                    //            f.LastWriteTime.Date == DateTime.Today)
        //             //                    //.ToList();

        //             //                    bmpUpdatedToday = casesList.Files
        //             //.Any(f => !f.IsDirectory &&
        //             //          f.Name.EndsWith(".bmp", StringComparison.OrdinalIgnoreCase) &&
        //             //          f.LastWriteTime.Date == DateTime.Today.AddDays(-1));

        //             //                    bmpFilesToday = casesList.Files
        //             //                        .Where(f => !f.IsDirectory &&
        //             //                                    f.Name.EndsWith(".bmp", StringComparison.OrdinalIgnoreCase) &&
        //             //                                    f.LastWriteTime.Date == DateTime.Today.AddDays(-1)).ToList();



        //             //                    if (!bmpUpdatedToday)
        //             //                    {
        //             //                        Console.WriteLine($"No BMP files updated today in {casesFolderRemotePath}. Skipping folder.");
        //             //                        return;
        //             //                    }
        //             //                }
        //             //                else
        //             //                {
        //             //                    Console.WriteLine($"No ZIP or cases folder found in {dateRemotePath}. Skipping folder.");
        //             //                    return;
        //             //                }
        //             //            }

        //             // 🔹 Send email before creating job
        //             if (zipFilesToday.Count > 0)
        //             {
        //                 //SendEmail(
        //                 //    subject: $"ZIP File Found - {reportsName}",
        //                 //    bodyContent: $"ZIP file(s) updated today found in {dateRemotePath}. Job '{jobTitle}' will be created and ZIPs will be extracted."
        //                 //);
        //             }
        //             else if (bmpUpdatedToday)
        //             {
        //                 //                    SendEmail(
        //                 //subject: $"BMP Files Found - {reportsName}",
        //                 //folderPath: $"BMP files were updated today in {casesFolderRemotePath}",
        //                 //fileCount: $"{bmpcount}.");
        //             }

        //             // 🔹 Create job
        //             int jobId = FileDownloadingDBOperations.fncreatejob(projectid, jobTitle, jobTitle, dateFolderName);
        //             //if (jobId <= 0)
        //             //{
        //             //    Console.WriteLine($"Failed to create job '{jobTitle}'. Skipping folder.");
        //             //    return;
        //             //}
        //             FileDownloadingDBOperations.fncreatejobconfiguration(jobId.ToString(), projectid);

        //             // 🔹 Get job path
        //             string jobPath = dbOps.GetServerPath(jobId.ToString(), false, "serverinfo_job");
        //             if (string.IsNullOrEmpty(jobPath))
        //             {
        //                 //Console.WriteLine($"Warning: GetServerPath returned empty for job {jobId}. Skipping folder.");
        //                 return;
        //             }

        //             string parentPath = Path.Combine(jobPath, "Parent");
        //             Directory.CreateDirectory(parentPath);

        //             // 🔹 Process ZIP files if any updated today
        //             if (zipFilesToday.Count > 0)
        //             {
        //                 foreach (var zip in zipFilesToday)
        //                 {
        //                     // OutLookreader.ExtractZip(session, BuildRemotePath(dateRemotePath, zip.Name), parentPath);
        //                     //string zipFileName = "NW2d - 2022_11_01.zip";

        //                     // Use it directly instead of zip.Name
        //                     // OutLookreader.ExtractZip(session, BuildRemotePath(dateRemotePath, zipFileName), parentPath);

        //                     OutLookreader.ExtractZip(session, BuildRemotePath(dateRemotePath, zip.Name), parentPath);

        //                     localWorkRoot = Path.Combine(parentPath, zip.Name);
        //                 }

        //                 // After extraction, find the cases folder locally inside Parent
        //                 //string extractedCasesFolder = FindCasesFolder(session, parentPath, localWorkRoot); // local path
        //                 //if (string.IsNullOrEmpty(extractedCasesFolder))
        //                 //{
        //                 //    Console.WriteLine($"No cases folder found inside extracted ZIP. Skipping job processing.");
        //                 //    return;
        //                 //}

        //                 // 🔹 Process the cases folder
        //                 //ProcessCasesFolder(session, extractedCasesFolder, jobId, jobPath, reportsName, lotTitle, reportidname);
        //             }
        //             // 🔹 Process BMP files if no ZIP today
        //             else if (bmpUpdatedToday)
        //             {
        //                 // ✅ Download the full date folder (including subfolders)
        //                 //session.SynchronizeDirectories(
        //                 //    SynchronizationMode.Local,
        //                 //    parentPath,          // Local target path
        //                 //    dateRemotePath,      // Remote source path
        //                 //    false                // Do not remove extra local files
        //                 //).Check();

        //                 //if (extractedCasesFolder == null)
        //                 //{
        //                 //    Console.WriteLine("No cases folder found under Parent. Skipping processing.");
        //                 //    return;
        //                 //}

        //                 // ✅ Process the cases folder


        //             }
        //             string extractedCasesFolder = FindLocalCasesFolder(parentPath);
        //             //ProcessCasesFolder(session, extractedCasesFolder, jobId, jobPath, reportsName, lotTitle, reportidname);
        //             Console.WriteLine($"Job '{jobTitle}' created and processed successfully.");
        //         }
        //         catch (Exception ex)
        //         {
        //             Console.WriteLine($"Error processing date folder '{dateRemotePath}': {ex.Message}");
        //         }
        //     }


        static void ProcessDateFolder(Session session, string dateRemotePath, string localWorkRoot)
        {
            try
            {
                int bmpcount = 0;
                string dateFolderName = Path.GetFileName(dateRemotePath);
                var dbOps = new FileDownloadingDBOperations();

                // 🔹 Get project and reporter info
                string projectCode = ConfigurationManager.AppSettings["VLEX"].ToString();
                string projectid = FileDownloadingDBOperations.getscallervalue(
                    $"SELECT projectdefinitionid::varchar FROM tblprojectdefinition WHERE project_subtitle='{projectCode.Trim()}'"
                );
                string reportid = FileDownloadingDBOperations.getscallervalue(
                    $"SELECT reporter_key::varchar FROM tblvlexreporterkey WHERE reporter_name='{reportsName}'"
                );

                string reportidname = FileDownloadingDBOperations.getscallervalue(
                   $"SELECT reporterid::varchar FROM tblvlexreporterkey WHERE reporter_name='{reportsName}'");

                string jobTitle = $"{reportid}_{dateFolderName}";
                string lotTitle = $"{reportid}_{dateFolderName}";
                lotTitle = string.Concat(lotTitle, "01");

                // ✅ Check if job already exists
                string existingJobId = FileDownloadingDBOperations.getscallervalue(
                    $"SELECT jobid::varchar FROM tbljob WHERE jobname = '{jobTitle}'"
                );
                if (!string.IsNullOrEmpty(existingJobId))
                {
                    Console.WriteLine($"Job '{jobTitle}' already exists. Skipping folder.");
                    return;
                }

                // 1️⃣ List all files/folders in date folder
                RemoteDirectoryInfo dateFolderList = session.ListDirectory(dateRemotePath);

                // 2️⃣ Check for ZIP files
                var zipFiles = dateFolderList.Files
                                .Where(f => !f.IsDirectory && f.Name.EndsWith(".zip", StringComparison.OrdinalIgnoreCase))
                                .ToList();

                // If ZIPs exist but none updated today → skip folder
                // var zipFilesToday = zipFiles.Where(z => z.LastWriteTime.Date == DateTime.Today).ToList();
                var zipFilesToday = zipFiles
     .Where(z => z.LastWriteTime.Date == DateTime.Today.AddDays(-1))
     .ToList();
                // var zipFilesToday = new List<string> { "NW2d - 2022_11_01.zip" };

                if (zipFiles.Count > 0 && zipFilesToday.Count == 0)
                {
                    Console.WriteLine($"ZIP files exist but none updated today in {dateRemotePath}. Skipping folder.");
                    return; // skip this date folder entirely
                }

                // 3️⃣ Check for BMP if no ZIP today
                string casesFolderRemotePath = null;
                bool bmpUpdatedToday = false;
                List<RemoteFileInfo> bmpFilesToday = null;

                if (zipFilesToday.Count == 0)
                {
                    casesFolderRemotePath = FindCasesFolderOnFtp(session, dateRemotePath);

                    if (casesFolderRemotePath != null)
                    {
                        // 🔎 Get BMPs updated yesterday (including subfolders)
                        //bmpFilesToday = GetTodayUpdatedBmps(session, casesFolderRemotePath);
                        //bmpUpdatedToday = GetTodayUpdatedBmps(session, casesFolderRemotePath);
                        //if (bmpUpdatedToday == false)
                        //{

                        //    return;
                        //}
                        (bool found, int count) = GetTodayUpdatedBmps(session, casesFolderRemotePath);
                        bmpUpdatedToday = found;
                        if (!found)
                        {
                            //Console.WriteLine("❌ No BMPs updated yesterday.");
                            // return;
                            RemoteDirectoryInfo casesList = session.ListDirectory(casesFolderRemotePath);
                            int totalFiles = casesList.Files.Count(f => !f.IsDirectory && f.Name != "." && f.Name != "..");

                            if (totalFiles == 0)
                            {
                                // Get folder date from name (e.g. 2025_11_10)
                                string dateFolderName_ = Path.GetFileName(dateRemotePath);
                                if (DateTime.TryParseExact(dateFolderName_, "yyyy_MM_dd", CultureInfo.InvariantCulture,
                                    DateTimeStyles.None, out DateTime folderDate))
                                {
                                    // Compare with Cases folder LastWriteTime
                                    RemoteFileInfo folderMeta = session
                                        .EnumerateRemoteFiles(Path.GetDirectoryName(casesFolderRemotePath), "*", EnumerationOptions.AllDirectories)
                                        .FirstOrDefault(f => f.Name.Equals(Path.GetFileName(casesFolderRemotePath), StringComparison.OrdinalIgnoreCase));

                                    if (folderMeta != null && folderMeta.LastWriteTime.Date == folderDate.Date)
                                    {
                                        Console.WriteLine($" 'Cases' folder has 0 files but date matches {folderDate:yyyy-MM-dd}. Creating empty job...");

                                        int jobId_ = FileDownloadingDBOperations.fncreatejob(projectid, jobTitle, jobTitle, dateFolderName);
                                        if (jobId_ > 0)
                                        {
                                            FileDownloadingDBOperations.fncreatejobconfiguration(jobId_.ToString(), projectid);
                                            string jobPath_ = dbOps.GetServerPath(jobId_.ToString(), false, "serverinfo_job");

                                            if (!string.IsNullOrEmpty(jobPath_))
                                            {
                                                string parentPath_ = Path.Combine(jobPath_, "Parent");
                                                Directory.CreateDirectory(parentPath_);
                                                Console.WriteLine($" Empty job '{jobTitle}' created successfully (Cases folder empty).");
                                            }
                                            else
                                            {
                                                Console.WriteLine(" Job path not found, skipping further processing.");
                                            }
                                        }
                                        else
                                        {
                                            Console.WriteLine("Job creation failed.");
                                        }

                                        return;
                                    }
                                    else
                                    {
                                        Console.WriteLine($" 'Cases' folder empty but date does not match {folderDate:yyyy-MM-dd}. Skipping.");
                                        return;
                                    }
                                }
                                else
                                {
                                    Console.WriteLine($" Could not parse date folder name '{dateFolderName_}'. Skipping.");
                                    return;
                                }
                            }
                            else
                            {
                                Console.WriteLine($"No BMPs but {totalFiles} other files exist. Skipping.");
                                return;
                            }


                        }
                        bmpcount = count;
                        Console.WriteLine($"✅ Total BMPs found: {count}");
                        //Console.WriteLine($"📂 Found {bmpFilesToday.Count} BMP files updated yesterday in {casesFolderRemotePath}.");

                        // proceed with job creation, email sending, etc.
                        //foreach (var bmp in bmpFilesToday)
                        //{
                        //    Console.WriteLine($"➡ {bmp.FullName} ({bmp.LastWriteTime})");
                        //}
                    }
                    else
                    {
                        Console.WriteLine($"No ZIP or cases folder found in {dateRemotePath}. Skipping folder.");
                        return;
                    }
                }



                //            if (zipFilesToday.Count == 0)
                //            {
                //                casesFolderRemotePath = FindCasesFolderOnFtp(session, dateRemotePath);
                //                if (casesFolderRemotePath != null)
                //                {
                //                    RemoteDirectoryInfo casesList = session.ListDirectory(casesFolderRemotePath);
                //                    // bmpUpdatedToday = casesList.Files
                //                    //                    .Any(f => !f.IsDirectory &&
                //                    //                              f.Name.EndsWith(".bmp", StringComparison.OrdinalIgnoreCase) &&
                //                    //                              f.LastWriteTime.Date == DateTime.Today);

                //                    //  bmpFilesToday = casesList.Files
                //                    //.Where(f => !f.IsDirectory &&
                //                    //            f.Name.EndsWith(".bmp", StringComparison.OrdinalIgnoreCase) &&
                //                    //            f.LastWriteTime.Date == DateTime.Today)
                //                    //.ToList();

                //                    bmpUpdatedToday = casesList.Files
                //.Any(f => !f.IsDirectory &&
                //          f.Name.EndsWith(".bmp", StringComparison.OrdinalIgnoreCase) &&
                //          f.LastWriteTime.Date == DateTime.Today.AddDays(-1));

                //                    bmpFilesToday = casesList.Files
                //                        .Where(f => !f.IsDirectory &&
                //                                    f.Name.EndsWith(".bmp", StringComparison.OrdinalIgnoreCase) &&
                //                                    f.LastWriteTime.Date == DateTime.Today.AddDays(-1)).ToList();



                //                    if (!bmpUpdatedToday)
                //                    {
                //                        Console.WriteLine($"No BMP files updated today in {casesFolderRemotePath}. Skipping folder.");
                //                        return;
                //                    }
                //                }
                //                else
                //                {
                //                    Console.WriteLine($"No ZIP or cases folder found in {dateRemotePath}. Skipping folder.");
                //                    return;
                //                }
                //            }

                // 🔹 Send email before creating job


                // 🔹 Create job
                int jobId = FileDownloadingDBOperations.fncreatejob(projectid, jobTitle, jobTitle, dateFolderName);
                if (jobId <= 0)
                {
                    Console.WriteLine($"Failed to create job '{jobTitle}'. Skipping folder.");
                    return;
                }
                FileDownloadingDBOperations.fncreatejobconfiguration(jobId.ToString(), projectid);

                // 🔹 Get job path
                string jobPath = dbOps.GetServerPath(jobId.ToString(), false, "serverinfo_job");
                if (string.IsNullOrEmpty(jobPath))
                {
                    Console.WriteLine($"Warning: GetServerPath returned empty for job {jobId}. Skipping folder.");
                    return;
                }

                string parentPath = Path.Combine(jobPath, "Parent");
                Directory.CreateDirectory(parentPath);

                // 🔹 Process ZIP files if any updated today
                if (zipFilesToday.Count > 0)
                {
                    foreach (var zip in zipFilesToday)
                    {
                        // OutLookreader.ExtractZip(session, BuildRemotePath(dateRemotePath, zip.Name), parentPath);
                        //string zipFileName = "NW2d - 2022_11_01.zip";

                        // Use it directly instead of zip.Name
                        // OutLookreader.ExtractZip(session, BuildRemotePath(dateRemotePath, zipFileName), parentPath);

                        OutLookreader.ExtractZip(session, BuildRemotePath(dateRemotePath, zip.Name), parentPath);

                        localWorkRoot = Path.Combine(parentPath, zip.Name);
                    }

                    // After extraction, find the cases folder locally inside Parent
                    string extractedCasesFolder = FindCasesFolder(session, parentPath, localWorkRoot); // local path
                    if (string.IsNullOrEmpty(extractedCasesFolder))
                    {
                        Console.WriteLine($"No cases folder found inside extracted ZIP. Skipping job processing.");
                        return;
                    }

                    // 🔹 Process the cases folder
                    ProcessCasesFolder(session, extractedCasesFolder, jobId, jobPath, reportsName, lotTitle, reportidname, bmpcount);
                }
                // 🔹 Process BMP files if no ZIP today
                else if (bmpUpdatedToday)
                {
                    // ✅ Download the full date folder (including subfolders)
                    session.SynchronizeDirectories(
                        SynchronizationMode.Local,
                        parentPath,          // Local target path
                        dateRemotePath,      // Remote source path
                        false                // Do not remove extra local files
                    ).Check();
                    string extractedCasesFolder = FindLocalCasesFolder(parentPath);
                    if (extractedCasesFolder == null)
                    {
                        Console.WriteLine("No cases folder found under Parent. Skipping processing.");
                        return;
                    }

                    // ✅ Process the cases folder
                    ProcessCasesFolder(session, extractedCasesFolder, jobId, jobPath, reportsName, lotTitle, reportidname, bmpcount);

                }

                if (zipFilesToday.Count > 0)
                {
                    SendEmail(
                        subject: $"ZIP File Found - {reportsName}",
                        bodyContent: $"ZIP file(s) updated today found in {dateRemotePath}. Job '{jobTitle}' will be created and ZIPs will be extracted."
                    );
                }
                else if (bmpUpdatedToday)
                {
                    SendEmail(
subject: $"BMP Files Found - {reportsName}",
folderPath: $"BMP files were updated today in {casesFolderRemotePath}",
fileCount: $"{bmpcount}.");
                }
                Console.WriteLine($"Job '{jobTitle}' created and processed successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error processing date folder '{dateRemotePath}': {ex.Message}");
            }
        }

        // Helper method to copy directories recursively
        private static void DirectoryCopy(string sourceDirName, string destDirName, bool copySubDirs)
        {
            DirectoryInfo dir = new DirectoryInfo(sourceDirName);
            if (!dir.Exists) throw new DirectoryNotFoundException($"Source directory does not exist: {sourceDirName}");

            DirectoryInfo[] dirs = dir.GetDirectories();
            Directory.CreateDirectory(destDirName);

            foreach (FileInfo file in dir.GetFiles())
            {
                string tempPath = Path.Combine(destDirName, file.Name);
                file.CopyTo(tempPath, true);
            }

            if (copySubDirs)
            {
                foreach (DirectoryInfo subdir in dirs)
                {
                    string tempPath = Path.Combine(destDirName, subdir.Name);
                    DirectoryCopy(subdir.FullName, tempPath, copySubDirs);
                }
            }
        }


        //public static (bool found, int count) GetTodayUpdatedBmps(Session session, string remotePath)
        //{
        //    int count = 0;
        //    bool found = false;

        //    try
        //    {
        //        RemoteDirectoryInfo dir = session.ListDirectory(remotePath);

        //        foreach (RemoteFileInfo file in dir.Files)
        //        {
        //            if (file.IsDirectory && file.Name != "." && file.Name != "..")
        //            {
        //                // 🔁 Recurse into subfolder
        //                string subPath = remotePath.TrimEnd('/') + "/" + file.Name;
        //                var (subFound, subCount) = GetTodayUpdatedBmps(session, subPath);

        //                if (subFound)
        //                    found = true;

        //                count += subCount;
        //            }
        //            else if (!file.IsDirectory && file.Name.EndsWith(".bmp", StringComparison.OrdinalIgnoreCase))
        //            {
        //                DateTime fileDate = file.LastWriteTime.ToLocalTime().Date;
        //                DateTime targetDate = DateTime.Today.AddDays(-1);

        //                if (fileDate == targetDate)
        //                {
        //                    Console.WriteLine($"✅ Found BMP updated on {fileDate}: {file.Name}");
        //                    count++;
        //                    found = true;
        //                }
        //                else
        //                {
        //                    Console.WriteLine($"Skipping {file.Name}, fileDate={fileDate}, expected={targetDate}");
        //                }
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Console.WriteLine($"❌ Error while checking {remotePath}: {ex.Message}");
        //    }

        //    return (found, count);
        //}

        public static (bool found, int count) GetTodayUpdatedBmps(Session session, string remotePath)
        {
            int count = 0;
            bool found = false;

            try
            {
                RemoteDirectoryInfo dir = session.ListDirectory(remotePath);

                foreach (RemoteFileInfo file in dir.Files)
                {
                    if (file.IsDirectory && file.Name != "." && file.Name != "..")
                    {
                        // 🔁 Recurse into subfolder
                        string subPath = remotePath.TrimEnd('/') + "/" + file.Name;
                        var (subFound, subCount) = GetTodayUpdatedBmps(session, subPath);

                        if (subFound)
                            found = true;

                        count += subCount;
                    }
                    else if (!file.IsDirectory && file.Name.EndsWith(".bmp", StringComparison.OrdinalIgnoreCase))
                    {
                        //DateTime fileDate = file.LastWriteTime.ToLocalTime().Date;
                        DateTime fileDate = file.LastWriteTime.ToLocalTime();

                         DateTime targetDate = DateTime.Today.AddDays(-1);
                        //DateTime targetDate = DateTime.Now.AddDays(-4);


                        // if (fileDate == targetDate)
                        if (fileDate >= targetDate && fileDate <= DateTime.Now)

                        {
                            Console.WriteLine($"✅ Found BMP updated on {fileDate}: {file.Name}");
                            count++;
                            found = true;
                        }
                        else
                        {
                            Console.WriteLine($"Skipping {file.Name}, fileDate={fileDate}, expected={targetDate}");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ Error while checking {remotePath}: {ex.Message}");
            }

            return (found, count);
        }


        private static string FindCasesFolderInternal(Session session, string parentPath, int remainingDepth)
        {
            RemoteDirectoryInfo list;
            try
            {
                list = session.ListDirectory(parentPath);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"FindCasesFolder -> ListDirectory failed for '{parentPath}': {ex.Message}");
                return null;
            }

            if (list?.Files == null || list.Files.Count == 0)
            {
                Console.WriteLine($"FindCasesFolder -> no entries under '{parentPath}'");
                return null;
            }

            // helper: get base name (strip any returned path parts)
            string GetBaseName(string raw)
            {
                if (string.IsNullOrEmpty(raw)) return string.Empty;
                raw = raw.Trim();
                int ix = Math.Max(raw.LastIndexOf('/'), raw.LastIndexOf('\\'));
                return ix >= 0 ? raw.Substring(ix + 1).Trim() : raw;
            }

            // collect sub-directories (skip '.' and '..')
            var dirs = new List<RemoteFileInfo>();
            foreach (var obj in list.Files)
            {
                var f = obj as RemoteFileInfo;
                if (f == null) continue;
                if (!f.IsDirectory) continue;
                if (f.Name == "." || f.Name == "..") continue;

                string raw = GetBaseName(f.Name);
                if (string.IsNullOrEmpty(raw) || raw == "." || raw == "..") continue;

                dirs.Add(f);
            }

            // 1) Highest priority: specifically 09_cases and 14_cases
            foreach (var f in dirs)
            {
                string baseName = GetBaseName(f.Name);
                string lower = baseName.ToLowerInvariant();
                if (lower == "09_cases" || lower == "14_cases")
                {
                    Console.WriteLine($"FindCasesFolder -> matched priority folder: {baseName} under {parentPath}");
                    return $"{parentPath}/{baseName}";
                }
            }

            // 2) Exact "cases"
            foreach (var f in dirs)
            {
                string baseName = GetBaseName(f.Name);
                string lower = baseName.ToLowerInvariant();
                if (lower == "cases")
                {
                    Console.WriteLine($"FindCasesFolder -> matched exact 'cases': {baseName} under {parentPath}");
                    return $"{parentPath}/{baseName}";
                }
            }

            // 3) Any NN_cases pattern (generic like 01_cases, 22_cases, etc.)
            foreach (var f in dirs)
            {
                string baseName = GetBaseName(f.Name);
                string lower = baseName.ToLowerInvariant();
                if (Regex.IsMatch(lower, @"^\d{2}_cases$"))
                {
                    Console.WriteLine($"FindCasesFolder -> matched generic NN_cases: {baseName} under {parentPath}");
                    return $"{parentPath}/{baseName}";
                }
            }

            // 4) Other ends-with patterns (" cases" or generic "_cases")
            foreach (var f in dirs)
            {
                string baseName = GetBaseName(f.Name);
                string lower = baseName.ToLowerInvariant();
                if (lower.EndsWith("_cases") || lower.EndsWith(" cases"))
                {
                    Console.WriteLine($"FindCasesFolder -> matched ends-with 'cases' pattern: {baseName} under {parentPath}");
                    return $"{parentPath}/{baseName}";
                }
            }

            // 5) Phrase matches
            var phrases = new[]
            {
        "supreme court opinions",
        "court of appeal opinions",
        "appellate division of superior court opinions",
        "mchgn supreme court cases",
        "cases accepted by supreme court"
    };
            foreach (var f in dirs)
            {
                string baseName = GetBaseName(f.Name);
                string lower = baseName.ToLowerInvariant();
                foreach (var p in phrases)
                {
                    if (lower.Contains(p))
                    {
                        Console.WriteLine($"FindCasesFolder -> matched phrase '{p}': {baseName} under {parentPath}");
                        return $"{parentPath}/{baseName}";
                    }
                }
            }

            // 6) Recurse deeper if allowed
            if (remainingDepth > 0)
            {
                foreach (var f in dirs)
                {
                    string baseName = GetBaseName(f.Name);
                    string childPath = $"{parentPath}/{baseName}";
                    var found = FindCasesFolderInternal(session, childPath, remainingDepth - 1);
                    if (!string.IsNullOrEmpty(found)) return found;
                }
            }

            Console.WriteLine($"FindCasesFolder -> no matching 'cases' or alternatives under '{parentPath}'");
            return null;
        }





        // helper to build remote paths safely
        static string BuildRemotePath(string parent, string child)
        {
            if (string.IsNullOrEmpty(parent) || parent == "/") return "/" + child;
            return parent.TrimEnd('/') + "/" + child;
        }

        static void ProcessCasesFolder(Session session, string casesFolderRemotePath, int jobid, string jobPath, string reportsName, string lottitle, string reportidname,int bmpcount)
        {
            string FileName = ConfigurationSettings.AppSettings["mergingtool"].ToString();
            var dbOps = new FileDownloadingDBOperations();

            // Use the already downloaded Parent folder
            string parentPath = Path.Combine(jobPath, "Parent");

            // 🔍 Find the local Cases folder inside Parent
            string casesFolderLocalPath = FindLocalCasesFolder(parentPath);
            if (casesFolderLocalPath == null)
            {
                Console.WriteLine("⚠️ No local Cases folder found under Parent. Skipping.");
                //return;
            }

            // 🎯 Take only BMP files
            string[] bmpFiles = Directory.GetFiles(casesFolderLocalPath, "*.bmp", SearchOption.AllDirectories);
            if (bmpFiles.Length == 0)
            {
                Console.WriteLine($"⚠️ No BMP files found in {casesFolderLocalPath}");
                return;
            }

           // Sort files for correct sequence (important for merging PDFs)
                    Array.Sort(bmpFiles, StringComparer.InvariantCulture);

            // 🔽 Database & batch processing logic
            Console.WriteLine("Fetching Batch details...");
            string batchqry = $"SELECT batchid FROM tbllotbatch WHERE jobid = {jobid} AND active = 1";
            DataTable batch_dt = dbOps.getdatatable(batchqry);

            string batchid;
            if (batch_dt.Rows.Count > 0)
                batchid = batch_dt.Rows[0]["batchid"].ToString();
            else
            {
                Console.WriteLine("Creating new batch...");
                string insertBatchQry =
                    $"INSERT INTO tbllotbatch(jobid, batchname, batchstatus, active, unmerged) " +
                    $"VALUES ({jobid}, 'Batch_{jobid}_1', 'R', 1, 0) RETURNING batchid;";
                batchid = FileDownloadingDBOperations.insertdataScalar(insertBatchQry).ToString();
            }

            int lotid = FileDownloadingDBOperations.fncreatlot(jobid.ToString(), lottitle, "0", bmpcount);

            string insLotBatchDetQry =
                $"INSERT INTO tbllotbatchdetails(lotid, batchid, active, merged, ignored) " +
                $"VALUES({lotid}, {batchid}, 1, 1, 0) RETURNING lotbatchdetailsid;";
            string lot_batch_detail_id = FileDownloadingDBOperations.insertdataScalar(insLotBatchDetQry).ToString();

            //// Prepare transaction folders
            string lotfilepath = Path.Combine(jobPath, $"B{batchid}");
            if (!Directory.Exists(lotfilepath)) Directory.CreateDirectory(lotfilepath);

            int tranid = FileDownloadingDBOperations.InsertLotTrasaction(Convert.ToInt64(batchid), "");
            string transactionPath = dbOps.GetServerPath(tranid.ToString(), false, "serverinfo");
            string transactionInPath = Path.Combine(transactionPath, "IN");
            string transactionOutPath = Path.Combine(transactionPath, "OUT");

            if (!Directory.Exists(transactionInPath)) Directory.CreateDirectory(transactionInPath);
            if (!Directory.Exists(transactionOutPath)) Directory.CreateDirectory(transactionOutPath);

            // Copy only BMP files into transaction IN folder
            int count = 0;
            foreach (string file in bmpFiles)
            {
                string fileNameWithExtension = Path.GetFileNameWithoutExtension(file);
                string pattern = @"(?<=\.)(\d{8}[A-Z0-9]{6})";
                Match match = Regex.Match(fileNameWithExtension, pattern);
                string filenamechange = match.Success ? match.Groups[1].Value : fileNameWithExtension;

                string ext = Path.GetExtension(file);
                string filenameChangedWithExt = $"{filenamechange}{ext}";

                File.Copy(file, Path.Combine(transactionInPath, filenameChangedWithExt), true);
                count++;
            }

            Console.WriteLine($"✅ {count} BMP files copied to {transactionInPath}");

            // Update lot status
            modcommon.nz(FileDownloadingDBOperations.insertdataScalar(
                $"update tbllottransaction set lotstatus = 'C', endtime=now() where transactionid={tranid}"
            ));

            // ⚡ Prepare for PDF merge
            string firstFileNumber = GetImageNumbers(Path.GetFileNameWithoutExtension(bmpFiles.First()));
            string lastFileNumber = GetImageNumbers(Path.GetFileNameWithoutExtension(bmpFiles.Last()));

            int nexttranid = FileDownloadingDBOperations.InsertLotTrasaction(Convert.ToInt64(batchid), "nexttraind");

            modcommon.nz(FileDownloadingDBOperations.insertdataScalar(
                $"update tbllottransaction set lotstatus = 'W', endtime=now() where transactionid={nexttranid}"
            ));

            string transactionnextPath = dbOps.GetServerPath(nexttranid.ToString(), false, "serverinfo");
            string transactionInnextPath = Path.Combine(transactionnextPath, "IN");
            string transactionOutnextPath = Path.Combine(transactionnextPath, "OUT");
            string outputPdfPath = Path.Combine(transactionInnextPath, $"{firstFileNumber}_{reportsName}_{lastFileNumber}.pdf");

            if (!Directory.Exists(transactionInnextPath)) Directory.CreateDirectory(transactionInnextPath);
            if (!Directory.Exists(transactionOutnextPath)) Directory.CreateDirectory(transactionOutnextPath);

            Console.WriteLine($"📄 Generating PDF: {outputPdfPath}");

            // ✅ Merge BMP → PDF using your external tool
            Program.Conv(FileName, outputPdfPath, transactionInPath);

            // Update lot status back to Ready
            modcommon.nz(FileDownloadingDBOperations.insertdataScalar(
                $"update tbllottransaction set lotstatus = 'R', endtime=now() where transactionid={nexttranid}"
            ));

            Console.WriteLine($"✅ Lot {lotid} processed for Job {jobid}");

            long reportId = InsertVlexReport(jobid,
                            lotid, reportidname, "", "", "", "", 0, 1);








        }



        static int GetMatchedPatternId(string input)
        {
            for (int i = 0; i < DatePatterns.Length; i++)
            {
                if (Regex.IsMatch(input, DatePatterns[i]))
                    return i; // Return ID/index of matched pattern
            }
            return -1; // No match
        }
        static bool IsDateFolder(string folderName)
        {

            foreach (var pattern in DatePatterns)
            {
                if (Regex.IsMatch(folderName, pattern))
                    return true;
            }
            return false;
        }

        static string GetImageNumbers(string filePath)
        {
            string[] parts = Path.GetFileNameWithoutExtension(filePath).Split('_');
            string lastPart = parts[parts.Length - 1];
            // Keep only digits
            return new string(lastPart.Where(char.IsDigit).ToArray());
        }



        static void ExtractZip(Session session, string zipPath, string extractTo)
        {
            try
            {
                // 🔹 Ensure extraction folder exists
                Directory.CreateDirectory(extractTo);

                // 🔹 Local path to download the zip
                string localZip = Path.Combine(extractTo, Path.GetFileName(zipPath));

                // 🔹 Ensure parent folder exists for local zip
                Directory.CreateDirectory(Path.GetDirectoryName(localZip));

                // 🔹 Download zip from remote
                session.GetFiles(zipPath, localZip, false).Check();

                // 🔹 Extract zip entries
                using (FileStream zipStream = new FileStream(localZip, FileMode.Open, FileAccess.Read))
                using (ZipArchive archive = new ZipArchive(zipStream, ZipArchiveMode.Read))
                {
                    foreach (ZipArchiveEntry entry in archive.Entries)
                    {
                        string destinationPath = Path.Combine(extractTo, entry.FullName);

                        if (string.IsNullOrEmpty(entry.Name))
                        {
                            // folder
                            Directory.CreateDirectory(destinationPath);
                        }
                        else
                        {
                            Directory.CreateDirectory(Path.GetDirectoryName(destinationPath));
                            using (var entryStream = entry.Open())
                            using (var destStream = File.Create(destinationPath))
                            {
                                entryStream.CopyTo(destStream);
                            }

                            // 🔹 Preserve timestamp from zip
                            File.SetLastWriteTime(destinationPath, entry.LastWriteTime.DateTime);
                        }
                    }
                }

                // 🔹 Optional: delete downloaded zip
                try { File.Delete(localZip); } catch { }
                

                Console.WriteLine($"✅ Extracted {Path.GetFileName(zipPath)} into {extractTo}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"❌ ExtractZip failed for '{zipPath}': {ex.Message}");
                throw;
            }
        }


        static long InsertVlexReport(
       long jobId, long lotId, string report, string volume,
       string edition, string issue, string printpage, long createdBy, int active)
        {
            string connStr = ConfigurationManager.AppSettings["npgconn"].ToString();

            using (var conn = new NpgsqlConnection(connStr))
            {
                using (var cmd = new NpgsqlCommand("spvlexreport_insert", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

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

                    // ✅ Open the connection before executing
                    conn.Open();

                    // Execute and return the new report ID
                    return Convert.ToInt64(cmd.ExecuteScalar());
                }
            }
        }




        static void CopyFolder(Session session, string sourcePath, string destinationPath)
        {
            foreach (RemoteFileInfo file in session.ListDirectory(sourcePath).Files)
            {
                if (file.Name == "." || file.Name == "..")
                    continue;

                string sourceFile = sourcePath.TrimEnd('/') + "/" + file.Name;
                string destFile = destinationPath.TrimEnd('/') + "/" + file.Name;

                if (file.IsDirectory)
                {
                    session.CreateDirectory(destFile);
                    CopyFolder(session, sourceFile, destFile);
                }
                else
                {
                    session.GetFiles(sourceFile, Path.GetTempPath() + file.Name).Check();
                    session.PutFiles(Path.GetTempPath() + file.Name, destFile, false).Check();
                }
            }
        }




        public int GetPageCount(string pdfPath)
        {
            using (PdfReader reader = new PdfReader(pdfPath))
            {
                // Get the number of pages in the PDF
                return reader.NumberOfPages;
            }
        }
    }

    class Program
    {
        private static string PrcRv = "";
        private static string PrcEr = "";
        private static string PrcOut = "";
        static void Main(string[] args)
        {

            try
            {
                string projectCode = ConfigurationManager.AppSettings["VLEX"].ToString();
                Console.WriteLine("Project Code = " + projectCode);
                var now = DateTime.Now;
                

                // Set time to 4:00 AM today
                var nextRunTime = DateTime.Today.AddHours(4);

                // Run once immediately
                //CallMethod(projectCode);

                // If 4 AM already passed today → schedule for tomorrow
                if (now > nextRunTime)
                {
                    nextRunTime = nextRunTime.AddDays(1);
                }

                var initialDelay = nextRunTime - now;       // Time until next 4 AM
                var dailyInterval = TimeSpan.FromDays(1);   // Run every 24 hours

                var timer = new System.Threading.Timer((e) =>
                {
                    CallMethod(projectCode);
                }, null, initialDelay, dailyInterval);

                Console.WriteLine($"Next run scheduled at: {nextRunTime}");
                Console.WriteLine("Press any key to exit...");
                Console.ReadKey();


            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.Message);

            }
        }

        public static void CallMethod(string projectCode)
        {
            Console.WriteLine("\nMailWatcher started at: " + DateTime.Now + "\n");
            OutLookreader outLookReader = new OutLookreader();
            //string dwnserverpath = @"\\172.20.254.139\internal_laps\DEV\LN-BPM\Download";
            string dwnserverpath = @"D:\internal_laps\UAT\JOB";
            outLookReader.DownloadFromFTP1(projectCode, dwnserverpath, true);
        }


        private static void Prc_Extd(object sender, EventArgs e)
        {
        }

        private static void Prc_ErDtRcvd(object sender, DataReceivedEventArgs e)
        {
            if (e.Data == null || !(e.Data != ""))
                return;
            Program.PrcEr = Program.PrcEr + "\r\n" + e.Data;
        }

        private static void Prc_OpDtRcvd(object sender, DataReceivedEventArgs e)
        {
            if (e.Data == null || !(e.Data != ""))
                return;
            Program.PrcOut = Program.PrcOut + "\r\n" + e.Data;
        }

        public static string Conv(string fileName, string parameter1, string parameter2)
        {
            string str;
            Program.PrcOut = str = "";
            Program.PrcEr = str;
            Program.PrcRv = str;
            Process process = null;

            try
            {
                process = new Process();
                process.StartInfo.FileName = fileName;
                Console.WriteLine(fileName);

                // Combine both parameters into the arguments string
                // process.StartInfo.Arguments = $"\"{parameter1}\" \"{parameter2}\"";
                process.StartInfo.Arguments = $"\"{parameter1},{parameter2}\"";


                process.StartInfo.WorkingDirectory = Path.GetDirectoryName(fileName);
                Console.WriteLine(Path.GetDirectoryName(fileName).ToString());

                process.StartInfo.CreateNoWindow = true;
                process.StartInfo.UseShellExecute = false;

                process.Exited += new EventHandler(Program.Prc_Extd);
                process.StartInfo.RedirectStandardError = true;
                process.StartInfo.RedirectStandardOutput = true;

                process.ErrorDataReceived += new DataReceivedEventHandler(Program.Prc_ErDtRcvd);
                process.OutputDataReceived += new DataReceivedEventHandler(Program.Prc_OpDtRcvd);

                process.Start();
                process.BeginErrorReadLine();
                process.BeginOutputReadLine();
                process.WaitForExit();

                if (Program.PrcEr != "")
                    Program.PrcRv = Program.PrcRv + "\r\n" + Program.PrcEr;
            }
            catch (Exception ex)
            {
                Program.PrcRv = Program.PrcRv + "Error while conversion, exception is: "
                                + ex.Message.Replace(fileName, "") + "::"
                                + ex.StackTrace.Replace(fileName, "");
            }
            finally
            {
                process?.Close();
            }

            return Program.PrcRv;
        }



    }

    public class PublishMessage
    {
        public PublishMessage(string customerId, string Tranid, string UserId, string AppType, string StageType, string Enviroment, object DynamicList, string Flag, string Batch = "", string PublicationType = "", string comment = "", string PublicationID = "")
        {
            _customerId = customerId;
            _TransId = Tranid;
            _UserId = UserId;
            _AppType = AppType;
            _StageType = StageType;
            _Enviroment = Enviroment;
            _timestamp = DateTime.UtcNow;
            _DynamicList = DynamicList;
            _Flag = Flag;
            _Batch = Batch;
            _PublicationType = PublicationType;
            _comment = comment;
            _PublicationID = PublicationID;
        }
        public string _customerId { get; set; }
        public string _TransId { get; set; }
        public string _UserId { get; set; }
        public string _AppType { get; set; }
        public string _StageType { get; set; }
        public string _Enviroment { get; set; }
        public DateTime _timestamp { get; set; }
        public object _DynamicList { get; set; }
        public string _Flag { get; set; }
        public string _Batch { get; set; }
        public string _PublicationType { get; set; }
        public string _comment { get; set; }
        public string _PublicationID { get; set; }
    }

}