using System.Diagnostics;
using System.Net;
using System.Net.NetworkInformation;
using System.Net.Sockets;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Fast.Shared.Logic.FileService;
using Hangfire;
using Hangfire.Storage;
using Renci.SshNet;

namespace Fast.Web.Logic;

internal enum EnvType
{
    Production,
    Test,
    Dev,
    DevDeployed,
    Legacy,
    Unknown
}

public class JobsHelper
{
    const string appName = FastValues.AppName;
    const string notifierEmail = @"bpippitt@superiornatgas.com";
    const string catalogFileNamePrefix = "_File_Catalog_";
    const int sftpPort = 22;
    const string sftpPrefix = "sngc";
    const string sourceCodeFolderName = "source-code";
    private List<string> addedJobIds = new();
    private readonly int numDirectoriesToTraverseUp;
    private readonly IRecurringJobManager recurringJobs;
    private readonly DirectoryInfo? localJobsDirectory;
    private readonly DirectoryInfo? topDirectoryToSearch;
    private readonly EnvType envType;
    private bool IsProduction { get { return envType == EnvType.Production; } }
    private readonly IConfiguration configuration;
    public readonly IWebHostEnvironment env;
    private readonly FileService fileService;

    readonly List<string> excludedKeys = new()
        {
            Path.Join("inetpub", "custerr"),
            Path.Join("inetpub", "ftproot"),
            Path.Join("inetpub", "history"),
            Path.Join("inetpub", "logs"),
            Path.Join("inetpub", "temp"),
            @"code/superior/sngc/", //old Panton site, needs trailing slash and must be lowercase
        };

    private static readonly string InvoicesGas = @"Invoices" + Path.DirectorySeparatorChar + "Gas";
    private static readonly string InvoicesCrude = @"Invoices" + Path.DirectorySeparatorChar + "Crude";

    private readonly List<string> documentFolderNames = new()
        {
            @"CreditLimitApprovalDocs",
            @"CreditLimitCollateralDocs",
            @"ContractDocs",
            @"PaymentInstructions",
            @"PipelineContracts",
            @"PipelineRates",
            @"PipelineDiscounts",
            @"ReportTemplates",
            @"SalesTaxExemptionCertificates",
            @"W9Forms",
            @"DealConfirms",
            @"DealTickets",
            InvoicesGas,
            InvoicesCrude
        };

    private readonly Dictionary<string, string> documentFolderNamesAndPaths = new();

    public JobsHelper(IRecurringJobManager recurringJobs, IWebHostEnvironment env, IConfiguration configuration)
    {
        this.configuration = configuration;
        this.env = env;

        var db = Main.CreateContext();
#pragma warning disable CA1862 // Prefer 'string.Equals(string, StringComparison)' for case-insensitive comparison
        string envMetadata = db.AppSettings.FirstOrDefault(x => x.Name.ToLower() == "homeurl")?.Metadata ?? "";
#pragma warning restore CA1862

        if (env.IsDevelopment())
            envType = EnvType.Dev;
        else if (envMetadata.Contains("dev", StringComparison.OrdinalIgnoreCase))
            envType = EnvType.DevDeployed;
        else if (envMetadata.Contains("prod", StringComparison.OrdinalIgnoreCase))
            envType = EnvType.Production;
        else if (envMetadata.Contains("test", StringComparison.OrdinalIgnoreCase))
            envType = EnvType.Test;
        else if (envMetadata.Contains("legacy", StringComparison.OrdinalIgnoreCase))
            envType = EnvType.Legacy;
        else
            envType = EnvType.Unknown;

        //clear documentFolderNames if legacy or unknown
        if (envType == EnvType.Legacy || envType == EnvType.Unknown)
            documentFolderNames = new List<string>();

        this.recurringJobs = recurringJobs;
        if (envType == EnvType.Dev)
            numDirectoriesToTraverseUp = 2;
        else
            numDirectoriesToTraverseUp = 1;

        localJobsDirectory = new DirectoryInfo(env.ContentRootPath);
        topDirectoryToSearch = localJobsDirectory;

        for (int i = 0; i < numDirectoriesToTraverseUp; i++)
        {
            if (topDirectoryToSearch != null && topDirectoryToSearch.Parent != null)
                topDirectoryToSearch = topDirectoryToSearch.Parent;
        }

        if (topDirectoryToSearch != null && Main.UseLocalFiles)
            UpdateLocalDocumentFolderPaths();

        if (!Main.UseLocalFiles)
        {
            foreach (var folder in documentFolderNames)
            {
                documentFolderNamesAndPaths.TryAdd(folder, folder);
            }
        }

        var config = new FileServiceConfig(env.ContentRootPath);
        config.LocalFolderMappings = documentFolderNamesAndPaths;
        fileService = new FileService(config);
    }

    public void CreateJobs()
    {
        //cron * * * * *
        //minute hour dayOfMonth month dayOfWeek(Sun-Sat,0-6)
        var recurringJobOps = new RecurringJobOptions() { TimeZone = TimeZoneInfo.Local };

        addedJobIds = new List<string>();

        if (envType == EnvType.Legacy)
        {
            //every day at 11pm
            AddJob("Trim large tables", () => TrimLargeTables(), "0 23 * * *", recurringJobOps);
        }

        if (envType == EnvType.Production || envType == EnvType.Test || envType == EnvType.Dev || envType == EnvType.DevDeployed)
        {
            //every hour
            AddJob("Create catalog: Credit Limit Approval", () => CreateCreditLimitApprovalCatalog(), "0 * * * *", recurringJobOps);
            AddJob("Create catalog: Credit Limit Collateral", () => CreateCreditLimitCollateralCatalog(), "0 * * * *", recurringJobOps);
            AddJob("Create catalog: Contract", () => CreateContractCatalog(), "0 * * * *", recurringJobOps);
            AddJob("Create catalog: Payment Instructions", () => CreatePaymentInstructionsCatalog(), "0 * * * *", recurringJobOps);
            AddJob("Create catalog: Pipeline Contract", () => CreatePipeContractCatalog(), "0 * * * *", recurringJobOps);
            AddJob("Create catalog: Pipeline Rate", () => CreatePipeRateCatalog(), "0 * * * *", recurringJobOps);
            AddJob("Create catalog: Pipeline Discount", () => CreatePipeDiscountCatalog(), "0 * * * *", recurringJobOps);
            AddJob("Create catalog: Sales Tax Exemption", () => CreateSalesTaxExemptionCatalog(), "0 * * * *", recurringJobOps);
            AddJob("Create catalog: W9 Form", () => CreateW9FormCatalog(), "0 * * * *", recurringJobOps);
            AddJob("Create catalog: Invoices Gas", () => CreateInvoiceGasCatalog(), "0 * * * *", recurringJobOps);
            AddJob("Create catalog: Invoices Crude", () => CreateInvoiceCrudeCatalog(), "0 * * * *", recurringJobOps);
            AddJob("Create catalog: Deal Confirms", () => CreateDealConfirmsCatalog(), "0 * * * *", recurringJobOps);
            AddJob("Create catalog: Deal Tickets", () => CreateDealTicketsCatalog(), "0 * * * *", recurringJobOps);

            //every day at 5am
            AddJob("Remove unused files", () => RemoveUnusedFiles(), "0 5 * * *", recurringJobOps);

            //every day at 11pm
            AddJob("Trim large tables", () => TrimLargeTables(), "0 23 * * *", recurringJobOps);
        }

        if (envType == EnvType.Production)
        {
            //every Friday at 8pm
            AddJob("Download source code", () => DownloadSourceCode(), "0 20 * * 5", recurringJobOps);

            //every day at 2am
            AddJob("CME Prices", () => new Cme(env).DownloadPrices(), "0 2 * * *", recurringJobOps);

            //every day at 6:45am
            AddJob("Platts Prices", () => new Platts(env).DownloadPrices(), "45 6 * * *", recurringJobOps);

            //every day at 6:45pm
            AddJob("Argus Prices", () => new Argus(env).Import(), "45 18 * * *", recurringJobOps);
        }

        //remove any recurring jobs not in addedJobIds
        var recurringJobsIds = GetRecurringJobIds();
        foreach (string recurringJobId in recurringJobsIds)
        {
            if (!addedJobIds.Contains(recurringJobId))
                recurringJobs.RemoveIfExists(recurringJobId);
        }
    }

    private void AddJob(string recurringJobId, System.Linq.Expressions.Expression<Action> methodCall, string cronExpression, RecurringJobOptions options)
    {
        recurringJobs.AddOrUpdate(recurringJobId, methodCall, cronExpression, options);
        addedJobIds.Add(recurringJobId);
    }

    private void AddJob(string recurringJobId, System.Linq.Expressions.Expression<Func<Task>> methodCall, string cronExpression, RecurringJobOptions options)
    {
        recurringJobs.AddOrUpdate(recurringJobId, methodCall, cronExpression, options);
        addedJobIds.Add(recurringJobId);
    }

    public static void DownloadSourceCode()
    {
        string baseFtpUrl = "ftp.implefast.com";
        string userName = "superior";
        string password = "sngc fast";

        string downloadDirectoryName = sourceCodeFolderName;
        if (!Directory.Exists(downloadDirectoryName))
            Directory.CreateDirectory(downloadDirectoryName);

        using SftpClient client = new(baseFtpUrl, sftpPort, userName, password);
        client.Connect();

        List<string> fileNames = GetFtpDirectoryFileNames(sourceCodeFolderName, client);

        foreach (string fileName in fileNames)
        {
            DateTime fileDateTimeStamp = GetDateTimeStamp(sourceCodeFolderName + "/" + fileName, client);
            DateTime currentDateTime = DateTime.Now;
            double daysOld = (currentDateTime - fileDateTimeStamp).TotalDays;

            if (daysOld >= 8)
                throw new Exception("A source code file on the FTP has not been updated in 8 days or more.  Please contact the FTP operator.");

            DownloadFtpFile(fileName, client);
        }
    }

    private static List<string> GetFtpDirectoryFileNames(string ftpDirectory, SftpClient client)
    {
        List<string> fileNames;
        fileNames = client.ListDirectory(ftpDirectory).Where(x => x.Name.Contains(sftpPrefix)).Select(x => x.Name).ToList();

        return fileNames;
    }

    private static DateTime GetDateTimeStamp(string ftpFileUrl, SftpClient client)
    {
        DateTime dateTimeStamp = client.GetLastWriteTime(ftpFileUrl);
        return dateTimeStamp;
    }

    private static void DownloadFtpFile(string fileName, SftpClient client)
    {
        string localFilePath = Path.Join(sourceCodeFolderName, fileName);
        using Stream targetStream = File.Create(localFilePath);
        client.DownloadFile(sourceCodeFolderName + "/" + fileName, targetStream);
    }

    private void UpdateLocalDocumentFolderPaths()
    {
        if (envType == EnvType.Unknown)
            throw new Exception("Unknown environment type. Check the HomeURL metadata in the database Settings table.");

        string searchPattern = "";
        if (envType == EnvType.Production)
            searchPattern = "*";
        else if (envType == EnvType.Test)
            searchPattern = "*test*";
        else if (envType == EnvType.Legacy)
            searchPattern = "*legacy*";
        else if (envType == EnvType.Dev || envType == EnvType.DevDeployed)
            searchPattern = "*sngc*";

        List<DirectoryInfo> topEligibleFolders = new();
        List<DirectoryInfo> eligibleFolders = new();

        if (topDirectoryToSearch != null)
            topEligibleFolders = topDirectoryToSearch.GetDirectories(searchPattern, SearchOption.TopDirectoryOnly).ToList();

        foreach (var topEligibleFolder in topEligibleFolders)
        {
            string folderName = topEligibleFolder.FullName.ToLower();
            if (!excludedKeys.Any(x => folderName.Contains(x, StringComparison.OrdinalIgnoreCase)))
            {
                Debug.WriteLine($"JobsHelper reading sub-folders from {topEligibleFolder.FullName}");
                //in dev the search pattern is * and we don't need to exclude any folders
                if (envType == EnvType.Dev)
                    eligibleFolders.AddRange(topEligibleFolder.GetDirectories("*", SearchOption.AllDirectories).ToList());
                //in production the search patterns is *, so we need to exclude folders with "test" or "legacy" in the name
                else if (envType == EnvType.Production && !folderName.Contains("test") && !folderName.Contains("legacy"))
                    eligibleFolders.AddRange(topEligibleFolder.GetDirectories("*", SearchOption.AllDirectories).ToList());
                //in test, legacy, and devdeployed the search patterns are *test*, *legacy*, or *sngc*, so we don't need to exclude any folders
                else if (envType == EnvType.Test || envType == EnvType.Legacy || envType == EnvType.DevDeployed)
                    eligibleFolders.AddRange(topEligibleFolder.GetDirectories("*", SearchOption.AllDirectories).ToList());
            }
        }

        eligibleFolders = eligibleFolders.Where(e => !excludedKeys.Any(x => e.FullName.Contains(x, StringComparison.OrdinalIgnoreCase))).ToList();

        ILookup<string, DirectoryInfo> eligibleFoldersLookup = eligibleFolders.ToLookup(x => x.Name);

        foreach (var docFolderName in documentFolderNames)
        {
            var docFolderSplit = docFolderName.Split(Path.DirectorySeparatorChar);
            string docFolderSubName = docFolderName;
            string? docFolderParentName = null;
            if (docFolderSplit.Length > 1)
            {
                docFolderParentName = docFolderSplit[0];
                docFolderSubName = docFolderSplit[1];
            }

            if (eligibleFoldersLookup.Contains(docFolderSubName))
            {
                if (eligibleFoldersLookup[docFolderSubName].Count() > 1)
                {
                    List<string> foundPlaces = new();
                    foreach (var eligibleFolder in eligibleFoldersLookup[docFolderSubName])
                    {
                        if (docFolderParentName == null || eligibleFolder?.Parent?.Name == docFolderParentName)
                            foundPlaces.Add(eligibleFolder.FullName);
                    }
                    if (foundPlaces.Count >= 2)
                        throw new Exception($"The {docFolderSubName} folder was found in two places.\r\n{foundPlaces[0]}\r\n{foundPlaces[1]}");
                }

                string path;
                if (docFolderParentName == null)
                    path = eligibleFoldersLookup[docFolderSubName].First().FullName;
                else
                    path = eligibleFoldersLookup[docFolderSubName].Where(x => x.Parent?.Name == docFolderParentName).First().FullName;

                documentFolderNamesAndPaths.Add(docFolderName, path);
            }
            else
            {
                throw new Exception($"The {docFolderSubName} folder was not found in any subfolder starting from {topDirectoryToSearch?.FullName ?? ""}");
            }
        }
    }

    public static void TrimLargeTables()
    {
        var db = Main.CreateContext();
        var cutoffDate = DateTime.UtcNow.Date.AddDays(-31);
        FormattableString sql = @$"delete from app_log where raise_date <= {cutoffDate}";
        db.Database.ExecuteSqlInterpolated(sql);
    }

    public class DatabaseFiles
    {
        public string FileNameOriginal { get; set; } = "";
        public string FileNameOnDisk { get; set; } = "";
        public string? FilePath { get; set; }
    }

    [AutomaticRetry(Attempts = 0)]
    public async Task RemoveUnusedFiles()
    {
        var guid = Guid.NewGuid().ToString("N")[..12];
        static string GetTimestamp() => $"[{DateTime.Now:yyyy-MM-dd HH:mm:ss.fff}]";
        Console.WriteLine($"{GetTimestamp()} INFO: RemoveUnusedFiles({guid}): {documentFolderNames.Count} folders to process");

        var db = Main.CreateContext();

        foreach (var folderName in documentFolderNames)
        {
            Console.WriteLine($"{GetTimestamp()} INFO: RemoveUnusedFiles({guid}): {folderName}");

            List<DatabaseFiles> databaseFiles = GetDatabaseFilesForFolder(db, folderName);
            if (databaseFiles.Count == 0)
                continue;

            var dbFilesHashset = databaseFiles
                .Select(x => x.FileNameOnDisk)
                .Where(f => !string.IsNullOrWhiteSpace(f))
                .Distinct(StringComparer.OrdinalIgnoreCase)
                .ToHashSet(StringComparer.OrdinalIgnoreCase);

            string missingFilesStr = await GetMissingFilesReportAsync(databaseFiles, folderName);
            var hasMissingFiles = !string.IsNullOrWhiteSpace(missingFilesStr);
            if (hasMissingFiles)
            {
                if (IsProduction)
                {
                    Console.WriteLine($"{GetTimestamp()} ACTION: RemoveUnusedFiles({guid}): {folderName} - SendMissingFileEmail()");
                    SendMissingFileEmail(missingFilesStr, folderName);
                }
                else
                {
                    Console.WriteLine($"{GetTimestamp()} INFO: RemoveUnusedFiles({guid}): {folderName} - missing files found (email skipped, not production)");
                }
            }
            else
            {
                Console.WriteLine($"{GetTimestamp()} INFO: RemoveUnusedFiles({guid}): {folderName} - no missing files");
            }

            await RemoveOrphanedFilesAsync(folderName, dbFilesHashset);
        }
    }

    private static List<DatabaseFiles> GetDatabaseFilesForFolder(MyDbContext db, string folderName)
    {
        return folderName switch
        {
            "CreditLimitApprovalDocs" => db.CreditLimitApprovals
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.FileNameOriginal,
                                        FileNameOnDisk = x.FileNameOnDisk ?? ""
                                    }).AsNoTracking().ToList(),
            "CreditLimitCollateralDocs" => db.CreditLimitCollateralDocs
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.FileNameOriginal,
                                        FileNameOnDisk = x.FileNameOnDisk
                                    }).AsNoTracking().ToList(),
            "ContractDocs" => db.ContractDocs
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.FileNameOriginal,
                                        FileNameOnDisk = x.FileNameOnDisk
                                    }).AsNoTracking().ToList(),
            "DealConfirms" => db.DealConfirmations
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.ConfirmFile ?? "",
                                        FileNameOnDisk = x.ConfirmFile ?? ""
                                    }).AsNoTracking().ToList(),
            "DealTickets" => db.DealConfirmations
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.TicketFile ?? "",
                                        FileNameOnDisk = x.TicketFile ?? ""
                                    }).AsNoTracking().ToList(),
            "PaymentInstructions" => db.PaymentInstructionDocs
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.FileNameOriginal,
                                        FileNameOnDisk = x.FileNameOnDisk
                                    }).AsNoTracking().ToList(),
            "PipelineContracts" => db.PipeContractDocs
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.FileNameOriginal,
                                        FileNameOnDisk = x.FileNameOnDisk
                                    }).AsNoTracking().ToList(),
            "PipelineRates" => db.PipeRateDocs
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.FileNameOriginal,
                                        FileNameOnDisk = x.FileNameOnDisk
                                    }).AsNoTracking().ToList(),
            "PipelineDiscounts" => db.PipeDiscountDocs
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.FileNameOriginal,
                                        FileNameOnDisk = x.FileNameOnDisk
                                    }).AsNoTracking().ToList(),
            "ReportTemplates" => db.Reports
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.TemplateFileNameOriginal,
                                        FileNameOnDisk = x.TemplateFileNameOnDisk
                                    }).AsNoTracking().ToList(),
            "SalesTaxExemptionCertificates" => db.SalesTaxExemptionDocs
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.FileNameOriginal,
                                        FileNameOnDisk = x.FileNameOnDisk
                                    }).AsNoTracking().ToList(),
            "W9Forms" => db.W9FormDocs
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.FileNameOriginal,
                                        FileNameOnDisk = x.FileNameOnDisk
                                    }).AsNoTracking().ToList(),
            _ when folderName == InvoicesGas => db.InvoiceGas
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.FileNameOriginal ?? "",
                                        FileNameOnDisk = x.FileNameOnDisk ?? ""
                                    }).AsNoTracking().ToList(),
            _ when folderName == InvoicesCrude => db.InvoiceCrudes
                                    .Select(x => new DatabaseFiles
                                    {
                                        FileNameOriginal = x.FileNameOriginal ?? "",
                                        FileNameOnDisk = x.FileNameOnDisk ?? ""
                                    }).AsNoTracking().ToList(),
            _ => new List<DatabaseFiles>()
        };
    }

    private async Task<string> GetMissingFilesReportAsync(List<DatabaseFiles> databaseFiles, string folderName)
    {
        // Get all files from storage using the abstraction
        var allFiles = await fileService.GetFilesWithInfoAsync(folderName, subFolder: null, searchPattern: "*", recursive: true);
        var storageFilesHashSet = allFiles
            .Where(f => !string.IsNullOrWhiteSpace(f.Name))
            .Select(f => f.Name)
            .ToHashSet(StringComparer.OrdinalIgnoreCase);

        var sb = new StringBuilder();
        foreach (var fileFromDb in databaseFiles.Distinct())
        {
            if (!string.IsNullOrWhiteSpace(fileFromDb.FileNameOnDisk) && !storageFilesHashSet.Contains(fileFromDb.FileNameOnDisk))
            {
                fileFromDb.FilePath = fileService.ResolvePath(folderName, fileFromDb.FileNameOnDisk, useSharding: true);
                sb.AppendLine($"<pre>\"{fileFromDb.FileNameOriginal}\"\t\"{fileFromDb.FilePath}\"</pre>");
            }
        }
        return sb.ToString();
    }

    private void SendMissingFileEmail(string missingFilesStr, string folderName)
    {
        string hostName = Dns.GetHostName();
        string ipAddress = GetLocalIpAddress();

        using var db = Main.CreateContext();
        string dbName = db.Database.GetDbConnection().Database;

        string subject = $"{appName} Missing {folderName} - {hostName} ({ipAddress})";
        string body = $"Server: {hostName} ({ipAddress})<br>Database: {dbName}<br>Environment: {envType}<br><br>";
        body += $"There are files referenced in the {appName} database that are missing.\r\n<br>";
        body += "Please contact an admisistrator for more info about these files.\r\n\r\n<br><br>";
        body += $"Missing {folderName}:\r\n\r\n<br><br>";
        body += missingFilesStr;

        EmailMissingFileMessage(subject, body);
    }


    private static void EmailMissingFileMessage(string subject, string body)
    {
        var db = Main.CreateContext();
        var emailer = new Util.Email(db, notifierEmail);
        emailer.SendEmail(notifierEmail, subject, body, null);
    }

    /// <summary>
    /// Removes orphaned files from storage that are not referenced in the database.
    /// Uses the IFileService abstraction for storage-agnostic file operations.
    /// Files must be at least 7 days old to be deleted (to avoid race conditions with uploads).
    /// Catalog files are skipped as they are managed by the catalog creation process.
    /// </summary>
    private async Task RemoveOrphanedFilesAsync(string folderName, HashSet<string> dbFiles)
    {
        var utcNow = DateTime.UtcNow;
        var minAge = TimeSpan.FromDays(7);

        // Get all files from storage using the abstraction (includes LastModifiedDate)
        var allFiles = await fileService.GetFilesWithInfoAsync(folderName, subFolder: null, searchPattern: "*", recursive: true);

        // Identify files to delete
        var filesToDelete = new List<(string folderName, string fileName)>();

        foreach (var file in allFiles)
        {
            var fileName = file.Name ?? "";

            // Skip if file is in database
            if (dbFiles.Contains(fileName))
                continue;

            // Skip catalog files (they are managed by catalog creation jobs)
            if (fileName.Contains(catalogFileNamePrefix, StringComparison.OrdinalIgnoreCase))
                continue;

            // Skip files that are too new (to avoid race conditions with ongoing uploads)
            var fileModified = file.LastModifiedDate ?? utcNow;
            if ((utcNow - fileModified) < minAge)
                continue;

            filesToDelete.Add((folderName, fileName));
        }

        // Batch delete orphaned files
        if (filesToDelete.Count > 0)
        {
            var results = await fileService.DeleteFilesBatchedAsync(filesToDelete, useSharding: true, maxDegreeOfParallelism: 4);

            // Log any failures (but don't throw)
            foreach (var result in results.Where(r => !r.Success))
            {
                Console.WriteLine($"Failed to delete orphaned file {result.FileName}: {result.ErrorMessage}");
            }
        }
    }

    /// <summary>
    /// Gets a dictionary mapping file names to their web URLs for catalog hyperlinking.
    /// Uses the IFileService abstraction for storage-agnostic file enumeration.
    /// </summary>
    private async Task<Dictionary<string, string>> GetCatalogFileUrlsAsync(string folderName)
    {
        var files = await fileService.GetFilesWithInfoAsync(folderName, subFolder: null, searchPattern: "*", recursive: true);

        var fileUrlPairs = files
            .Where(f => !string.IsNullOrWhiteSpace(f.Name) && !string.IsNullOrWhiteSpace(f.WebUrl))
            .DistinctBy(f => f.Name, StringComparer.OrdinalIgnoreCase)
            .ToDictionary(f => f.Name!, f => f.WebUrl!, StringComparer.OrdinalIgnoreCase);

        return fileUrlPairs;
    }

    /// Creates an Excel catalog file from the provided data and saves it to storage.
    /// Uses OpenXml for Excel generation and supports both local and SharePoint storage.
    private async Task CreateCatalogAsync<T>(string documentFolderName, IList<T> data)
    {
        Console.WriteLine($"Creating catalog file in folder: {documentFolderName}");

        await DeleteOldCatalogFilesAsync(documentFolderName);

        using var stream = new MemoryStream();
        using (var doc = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
        {
            OpenXmlHelper.InitializeUniversalStylesheet(doc);
            OpenXmlHelper.AddEmptySheet(doc, "Sheet1");
            OpenXmlHelper.FillSheet(doc, "Sheet1", data);
            await FormatCatalogFileCellsAsync(doc, "Sheet1", documentFolderName);
            OpenXmlHelper.SaveAllChanges(doc);
        }

        stream.Position = 0;
        await SaveCatalogFileAsync(stream, documentFolderName);
    }

    public async Task CreateW9FormCatalog()
    {
        var db = Main.CreateContext();
        string documentFolderName = "W9Forms";

        var data = (
            from wf in db.W9Forms
            join docs in db.W9FormDocs on wf.Id equals docs.W9FormId into j1
            from docs in j1.DefaultIfEmpty()
            let taxClassificationPrefix = wf.CounterpartyParent.BusinessType != null && wf.CounterpartyParent.BusinessType.Name == "LLC" ? "LLC - " : wf.CounterpartyParent.BusinessSubTypeId == null && wf.CounterpartyParent.BusinessType != null ? wf.CounterpartyParent.BusinessType.Name : ""
            let taxClassificationSuffix = wf.CounterpartyParent.BusinessSubType != null ? wf.CounterpartyParent.BusinessSubType!.Name : ""
            select new
            {
                ParentCounterparty = wf.CounterpartyParent.Name,
                Counterparty = wf.Counterparty == null ? "" : wf.Counterparty!.Name,
                TaxClassification = taxClassificationPrefix + taxClassificationSuffix,
                TaxpayerIdNum = wf.CounterpartyParent.FederalTaxId,
                W9Version = wf.VersionMonth,
                docs.FileNameOriginal,
                docs.FileNameOnDisk
            }
        ).AsNoTracking().ToList();

        await CreateCatalogAsync(documentFolderName, data);
    }

    public async Task CreateContractCatalog()
    {
        var db = Main.CreateContext();
        string documentFolderName = "ContractDocs";

        var data = (
            from q in db.ContractDocs
            select new
            {
                q.Contract.ContractName,
                q.Contract.ContractNum,
                Product = q.Contract.Product.Name,
                InternalEntity = q.Contract.InternalEntity.Name,
                Counterparty = q.Contract.Counterparty.Name,
                ContractStatus = q.Contract.Status.Name,
                ContractSigner = q.Contract.Signer == null ? "" : q.Contract.Signer.LegalName,
                PaymentNettingWithinContract = q.Contract.IsPaymentNettingAllowed ? "Allow" : "Deny",
                q.Contract.EffectiveDate,
                q.Contract.ExecutionDate,
                q.Contract.TerminationNoticeDate,
                q.Contract.TerminationEffectiveDate,
                q.Contract.ExhibitBAmendmentDate,
                ExhibitCAmendmentDate = q.Contract.ContractExhibitC != null ? q.Contract.ContractExhibitC.AmendmentDate : null,
                NeasbAmendmentDate = q.Contract.ContractNaesb != null ? q.Contract.ContractNaesb.AmendmentDate : null,
                q.FileNameOriginal,
                q.FileNameOnDisk
            }
        ).AsNoTracking().ToList();

        await CreateCatalogAsync(documentFolderName, data);
    }

    public async Task CreateCreditLimitApprovalCatalog()
    {
        var db = Main.CreateContext();
        string documentFolderName = "CreditLimitApprovalDocs";

        var data = (
            from q in db.CreditLimitApprovals
            select new
            {
                ParentCounterparty = q.CreditLimit.ParentCounterparty.Name,
                q.CreditLimit.ApprovedDate,
                q.CreditLimit.ReviewedDate,
                q.CreditLimit.ExpirationDate,
                q.FileNameOriginal,
                q.FileNameOnDisk
            }
        ).AsNoTracking().ToList();

        await CreateCatalogAsync(documentFolderName, data);
    }

    public async Task CreateCreditLimitCollateralCatalog()
    {
        var db = Main.CreateContext();
        string documentFolderName = "CreditLimitCollateralDocs";

        var data = (
            from clco in db.VwCreditLimitCollateralOverviews
            join clc in db.CreditLimitCollaterals on clco.Id equals clc.Id
            join docs in db.CreditLimitCollateralDocs on clc.Id equals docs.CreditLimitCollateralId into j1
            from docs in j1.DefaultIfEmpty()
            select new
            {
                ParentCounterparty = clc.CreditLimitCounterparty.CreditLimit.ParentCounterparty.Name,
                ProductCategory = clc.CreditLimitCounterparty.ProductCategory.Name,
                ProductCounterparty = clc.CreditLimitCounterparty.Counterparty.Name,
                clc.CollateralAmount,
                CollateralType = clc.CollateralType.Name,
                clc.EffectiveDate,
                clc.ExpirationDate,
                clco.Beneficiaries,
                clco.Providers,
                IsAmendment = clc.IsAmendment ? "Yes" : "No",
                docs.FileNameOriginal,
                docs.FileNameOnDisk,
            }
        ).AsNoTracking().ToList();

        await CreateCatalogAsync(documentFolderName, data);
    }

    public async Task CreateSalesTaxExemptionCatalog()
    {
        var db = Main.CreateContext();
        string documentFolderName = "SalesTaxExemptionCertificates";

        var data = (
            from sted in db.SalesTaxExemptionDocs
            join ste in db.SalesTaxExemptions on sted.SalesTaxExemptionId equals ste.Id into j1
            from ste in j1.DefaultIfEmpty()
            select new
            {
                Company = ste.Company.Name,
                State = ste.State.Name,
                ste.EffectiveDate,
                ste.ExpirationDate,
                sted.FileNameOriginal,
                sted.FileNameOnDisk
            }
        ).AsNoTracking().ToList();

        await CreateCatalogAsync(documentFolderName, data);
    }

    public async Task CreatePaymentInstructionsCatalog()
    {
        var db = Main.CreateContext();
        string documentFolderName = "PaymentInstructions";

        var data = (
            from pid in db.PaymentInstructionDocs
            join pi in db.PaymentInstructions on pid.PaymentInstructionId equals pi.Id into j1
            from pi in j1.DefaultIfEmpty()
            select new
            {
                Counterparty = pi.Counterparty.Name,
                pi.AccountHolder,
                pi.EffectiveDate,
                pi.BankName,
                pi.BankCity,
                BankState = pi.BankState == null ? "" : pi.BankState.Name,
                pi.BankWireNum,
                pi.BankAchNum,
                pi.AccountNum,
                pi.SwiftCode,
                pi.FurtherCreditTo,
                pi.FurtherCreditAccountNum,
                pid.FileNameOriginal,
                pid.FileNameOnDisk
            }
        ).AsNoTracking().ToList();

        await CreateCatalogAsync(documentFolderName, data);
    }

    public async Task CreatePipeContractCatalog()
    {
        var db = Main.CreateContext();
        string documentFolderName = "PipelineContracts";

        DateOnly noStartDate = new(1900, 1, 1);
        DateOnly noEndDate = new(2099, 12, 31);

        var data = (
            from pcd in db.PipeContractDocs
            join oi in db.VwPipeContractOverviewInfos on pcd.PipeContractId equals oi.PipeContractId into j1
            from oi in j1.DefaultIfEmpty()
            join pc in db.PipelineContracts on oi.PipeContractId equals pc.Id
            select new
            {
                ContractNum = pc.ContractId,
                StartDate = pc.StartDate == null ? noStartDate : pc.StartDate,
                EndDate = pc.EndDate == noEndDate ? null : pc.EndDate,
                Pipeline = pc.Pipeline.Name,
                ContractType = pc.ContractType == null ? "" : pc.ContractType.Name,
                RateSchedule = pc.RateSchedule == null ? "" : pc.RateSchedule.Name,
                Shipper = pc.InternalEntity == null ? "" : pc.InternalEntity.Name,
                ContractOwner = pc.ContractOwner == null ? "" : pc.ContractOwner.Name,
                CapacityRelease = pc.IsCapacityRelease ? "yes" : "no",
                CapacityCounterparty = pc.Counterparty == null ? "" : pc.Counterparty.Name,
                oi.ReceiptZones,
                oi.ReceiptMeters,
                oi.DeliveryZones,
                oi.DeliveryMeters,
                IsDefaultContract = pc.PipelineContractDefaults.Any(pcd => pcd.ProductId == pc.ProductId && pcd.PipelineId == pc.PipelineId) ? "yes" : "no",
                IsPtrContract = pc.IsPtrContract ? "yes" : "no",
                IsDefaultPtrContract = pc.Pipeline.DefaultPtrPipelineContractId == pc.Id ? "yes" : "no",
                pcd.FileNameOriginal,
                pcd.FileNameOnDisk
            }
        ).AsNoTracking().ToList();

        await CreateCatalogAsync(documentFolderName, data);
    }

    public async Task CreatePipeRateCatalog()
    {
        var db = Main.CreateContext();
        string documentFolderName = "PipelineRates";

        var data = (
            from prd in db.PipeRateDocs
            join pt in db.PipelineTariffs on prd.PipelineTariffId equals pt.Id into j1
            from pt in j1.DefaultIfEmpty()
            select new
            {
                RateSchedule = pt.RateSchedule.Name,
                Pipeline = pt.RateSchedule.Pipeline.Name,
                ContractType = pt.RateSchedule.ContractTypeNavigation.Name,
                RateType = pt.TariffType.Name,
                pt.EffectiveDate,
                Rate = pt.TariffRate,
                ApplicationRule = pt.ApplicationRule.Name,
                prd.FileNameOriginal,
                prd.FileNameOnDisk
            }
        ).AsNoTracking().ToList();

        await CreateCatalogAsync(documentFolderName, data);
    }

    public async Task CreatePipeDiscountCatalog()
    {
        var db = Main.CreateContext();
        string documentFolderName = "PipelineDiscounts";

        var data = (
            from pdd in db.PipeDiscountDocs
            join prd in db.PipelineRateDiscounteds on pdd.PipelineRateDiscountedId equals prd.Id into j1
            from prd in j1.DefaultIfEmpty()
            select new
            {
                RateSchedule = prd.RateSchedule.Name,
                Pipeline = prd.RateSchedule.Pipeline.Name,
                ContractType = prd.RateSchedule.ContractTypeNavigation.Name,
                RateType = prd.TariffType.Name,
                prd.EffectiveDate,
                prd.EndDate,
                prd.Rate,
                pdd.FileNameOriginal,
                pdd.FileNameOnDisk
            }
        ).AsNoTracking().ToList();

        await CreateCatalogAsync(documentFolderName, data);
    }

    public async Task CreateInvoiceGasCatalog()
    {
        var db = Main.CreateContext();
        string documentFolderName = InvoicesGas;

        var data = (
            from q in db.InvoiceGas
            select new
            {
                q.InvoiceNum,
                q.Month,
                Counterparty = q.Counterparty.Name,
                InternalEntity = q.InternalEntity.Name,
                q.FileNameOriginal,
                q.FileNameOnDisk
            }
        ).AsNoTracking().ToList();

        await CreateCatalogAsync(documentFolderName, data);
    }

    public async Task CreateInvoiceCrudeCatalog()
    {
        var db = Main.CreateContext();
        string documentFolderName = InvoicesCrude;

        var data = (
            from q in db.InvoiceCrudes
            select new
            {
                q.InvoiceNum,
                q.Month,
                Counterparty = q.Counterparty.Name,
                InternalEntity = q.InternalEntity.Name,
                q.FileNameOriginal,
                q.FileNameOnDisk
            }
        ).AsNoTracking().ToList();

        await CreateCatalogAsync(documentFolderName, data);
    }

    public async Task CreateDealConfirmsCatalog()
    {
        var db = Main.CreateContext();
        string documentFolderName = "DealConfirms";

        var data = (
            from dc in db.DealConfirmations
            join d in db.Deals on dc.TicketNum equals d.TicketNum
            where dc.ConfirmFile != null && dc.ConfirmFile != ""
            select new
            {
                d.TicketNum,
                d.TradingDate,
                Counterparty = d.Counterparty == null ? "" : d.Counterparty.Name,
                InternalEntity = d.InternalEntity == null ? "" : d.InternalEntity.Name,
                Product = d.Product.Name,
                dc.ConfirmDate,
                FileNameOriginal = dc.ConfirmFile,
                FileNameOnDisk = dc.ConfirmFileNameOnDisk
            }
        ).AsNoTracking().ToList();

        await CreateCatalogAsync(documentFolderName, data);
    }

    public async Task CreateDealTicketsCatalog()
    {
        var db = Main.CreateContext();
        string documentFolderName = "DealTickets";

        var data = (
            from dc in db.DealConfirmations
            join d in db.Deals on dc.TicketNum equals d.TicketNum
            where dc.TicketFile != null && dc.TicketFile != ""
            select new
            {
                d.TicketNum,
                d.TradingDate,
                Counterparty = d.Counterparty == null ? "" : d.Counterparty.Name,
                InternalEntity = d.InternalEntity == null ? "" : d.InternalEntity.Name,
                Product = d.Product.Name,
                dc.TicketDate,
                FileNameOriginal = dc.TicketFile,
                FileNameOnDisk = dc.TicketFileNameOnDisk
            }
        ).AsNoTracking().ToList();

        await CreateCatalogAsync(documentFolderName, data);
    }

    private async Task FormatCatalogFileCellsAsync(SpreadsheetDocument doc, string sheetName, string documentFolderName)
    {
        var wsPart = GetWorksheetPartByName(doc, sheetName);
        if (wsPart == null)
            return;

        var ws = wsPart.Worksheet;
        var sheetData = ws.GetFirstChild<SheetData>();
        if (sheetData == null)
            return;

        // Get last row and column
        int lastRow = sheetData.Elements<Row>().Max(r => (int)(r.RowIndex?.Value ?? 0));
        int lastCol = sheetData.Elements<Row>()
            .SelectMany(r => r.Elements<Cell>())
            .Select(c => OpenXmlHelper.GetColumnIndex(c.CellReference?.Value ?? ""))
            .DefaultIfEmpty(0)
            .Max();

        if (lastRow < 1 || lastCol < 1)
            return;

        // Get SharePoint file URLs if not using local files
        Dictionary<string, string>? sharePointFileUrls = null;
        if (!Main.UseLocalFiles)
            sharePointFileUrls = await GetCatalogFileUrlsAsync(documentFolderName);

        // Build list of hyperlinks to add (only add Hyperlinks element if there are actual hyperlinks)
        var hyperlinkItems = new List<(string cellRef, string url)>();

        for (int row = 2; row <= lastRow; row++) // Start at 2 to skip header
        {
            string cellRef = OpenXmlHelper.GetColumnName(lastCol) + row;
            var dataRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == (uint)row);
            var cell = dataRow?.Elements<Cell>().FirstOrDefault(c => c.CellReference?.Value == cellRef);

            string? fileName = GetCellValue(cell, doc.WorkbookPart);
            if (string.IsNullOrWhiteSpace(fileName))
                continue;

            string? hyperlinkUrl = null;

            if (Main.UseLocalFiles)
            {
                string directoryPath = documentFolderNamesAndPaths[documentFolderName];
                string shardedPart = fileService.GetShardFolderName(fileName);
                string fullPathToCheck = Path.Combine(directoryPath, shardedPart, fileName);
                if (File.Exists(fullPathToCheck))
                {
                    hyperlinkUrl = Path.Join(shardedPart, fileName);
                }
            }
            else if (sharePointFileUrls != null && sharePointFileUrls.TryGetValue(fileName, out var fileUrl) && !string.IsNullOrEmpty(fileUrl))
                hyperlinkUrl = fileUrl;

            if (!string.IsNullOrEmpty(hyperlinkUrl))
                hyperlinkItems.Add((cellRef, hyperlinkUrl));
        }

        // Only add Hyperlinks element if there are actual hyperlinks to add
        // (an empty <hyperlinks/> element is invalid per ECMA-376 and causes Excel to require repair)
        if (hyperlinkItems.Count > 0)
        {
            var hyperlinks = new Hyperlinks();
            foreach (var (cellRef, url) in hyperlinkItems)
            {
                var hyperlinkRelationship = wsPart.AddHyperlinkRelationship(new Uri(url, UriKind.RelativeOrAbsolute), true);
                hyperlinks.Append(new Hyperlink
                {
                    Reference = cellRef,
                    Id = hyperlinkRelationship.Id
                });
            }

            // Insert Hyperlinks in the correct position (after SheetData, before TableParts)
            var sheetDataElement = ws.GetFirstChild<SheetData>();
            if (sheetDataElement != null)
            {
                ws.InsertAfter(hyperlinks, sheetDataElement);
            }
            else
            {
                ws.Append(hyperlinks);
            }
        }

        // Add table to the sheet
        OpenXmlHelper.AddTableToSheet(doc, sheetName);

        // Freeze first row
        OpenXmlHelper.FreezeFirstRow(ws);

        // Auto-fit columns
        OpenXmlHelper.AutoFitColumns(ws, doc.WorkbookPart!);
    }

    private static WorksheetPart? GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
    {
        var sheets = document.WorkbookPart?.Workbook.GetFirstChild<Sheets>()?.Elements<Sheet>()
            .Where(s => s.Name == sheetName);
        if (sheets == null || !sheets.Any())
            return null;

        string? relationshipId = sheets.First().Id?.Value;
        if (relationshipId == null)
            return null;

        return (WorksheetPart)document.WorkbookPart!.GetPartById(relationshipId);
    }

    private static string? GetCellValue(Cell? cell, WorkbookPart? workbookPart)
    {
        if (cell == null || workbookPart == null || cell.CellValue == null)
            return null;

        string value = cell.CellValue.InnerText;

        if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
        {
            var sstPart = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
            if (sstPart?.SharedStringTable != null && int.TryParse(value, out int ssid))
            {
                return sstPart.SharedStringTable.Elements<SharedStringItem>().ElementAtOrDefault(ssid)?.InnerText;
            }
            return null;
        }

        return value;
    }

    private async Task SaveCatalogFileAsync(MemoryStream stream, string documentFolderName)
    {
        string fileNameTimestamp = DateTime.Now.ToString("yyyyMMdd_HHmm");
        string catalogFileName = catalogFileNamePrefix + fileNameTimestamp + ".xlsx";

        if (Main.UseLocalFiles)
        {
            string directoryPath = documentFolderNamesAndPaths[documentFolderName];
            string fullFileName = Path.Join(directoryPath, catalogFileName);
            Console.WriteLine($"Saving catalog file: {fullFileName}");
            await File.WriteAllBytesAsync(fullFileName, stream.ToArray());
        }
        else
        {
            var content = stream.ToArray();
            string fileName = Path.Join(documentFolderName, catalogFileName);
            Console.WriteLine($"Saving catalog file: {fileName}");
            await fileService.WriteAllBytesAsync(documentFolderName, catalogFileName, content, useSharding: false);
        }
    }

    /// <summary>
    /// Deletes old catalog files from storage before creating new ones.
    /// Uses the IFileService abstraction for storage-agnostic file operations.
    /// </summary>
    private async Task DeleteOldCatalogFilesAsync(string documentFolderName)
    {
        // Ensure the directory exists
        await fileService.CreateDirectoryAsync(documentFolderName);

        // Get all files from storage using the abstraction
        var allFiles = await fileService.GetFilesWithInfoAsync(documentFolderName, subFolder: null, searchPattern: "*", recursive: true);

        // Identify catalog files to delete
        var catalogFilesToDelete = allFiles
            .Where(f => !string.IsNullOrWhiteSpace(f.Name) && f.Name.Contains(catalogFileNamePrefix, StringComparison.OrdinalIgnoreCase))
            .Select(f => (documentFolderName, f.Name))
            .ToList();

        if (catalogFilesToDelete.Count == 0)
            return;

        // Batch delete catalog files (no sharding for catalog files)
        var results = await fileService.DeleteFilesBatchedAsync(catalogFilesToDelete, useSharding: false, maxDegreeOfParallelism: 4);

        // Log results
        foreach (var result in results)
        {
            var fileName = Path.Combine(result.FolderName, result.FileName);
            if (result.Success)
            {
                Console.WriteLine($"Deleted catalog file: {fileName}");
            }
            else
            {
                Console.WriteLine($"Failed to delete catalog {fileName}: {result.ErrorMessage}");
            }
        }
    }

    private static string GetLocalIpAddress()
    {
        string ipAddress = "Unknown IP";
        try
        {
            ipAddress = NetworkInterface.GetAllNetworkInterfaces()
                .Where(n => n.OperationalStatus == OperationalStatus.Up && n.NetworkInterfaceType != NetworkInterfaceType.Loopback)
                .SelectMany(n => n.GetIPProperties().UnicastAddresses)
                .Where(ua => ua.Address.AddressFamily == AddressFamily.InterNetwork)
                .Select(ua => ua.Address.ToString())
                .FirstOrDefault() ?? "Unknown IP";
        }
        catch
        {
            //ignore
        }
        return ipAddress;
    }

    internal static List<string> GetRecurringJobIds()
    {
        // Access Hangfire's storage to fetch connection and fetch all recurring jobs
        var storage = JobStorage.Current;
        using var connection = storage.GetConnection();
        var recurringJobs = connection.GetRecurringJobs();
        List<string> jobNames = new();

        // Extract job names
        foreach (var job in recurringJobs)
            jobNames.Add(job.Id);

        return jobNames;
    }
}
