using Neo.Afx.Mvc;
using Neo.Afx.Common;
using Neo.LegitimateLicences.Data.Models;
using Neo.LegitimateLicences.Common;
using Neo.LegitimateLicences.Common.Helpers;
using System.Linq;
using System.Web.Mvc;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace Neo.LegitimateLicences.AppProviderIssueRequest
{
    public partial class AppProviderIssueRequestController : Neo.LegitimateLicences.Common.ControllerBase
    {
    private class CertificateNumberResult
    {
        public string CertificateNumber { get; set; }
    }

    private class ProviderInfoResult
    {
        public long? PlatformProviderID { get; set; }
        public long ApplicationDetailID { get; set; }
    }

    private class CertificateInfoResult
    {
        public long ProviderCertificateID { get; set; }
        public string CertificateNumber { get; set; }
        public long? ApplicationDetailID { get; set; }
    }

        [HttpGet]
        public ActionResult Printing()
        {
            ViewBag.Controller = "App Provider";
            ViewBag.Page = "Printing";

            // Sidepane context: Use issuing workflow (EntityID 1042) to show tasks like "Submit request"
            // This matches Dashboard pattern and ensures correct provider information is shown
            var itemID = Neo.LegitimateLicences.Common.Helpers.WebHelper.ItemID;
            
            // Determine if itemID is AppProviderIssueDetailID or PlatformProviderID
            var appProviderIssueDetailID = itemID;
            var link = ResolveAppProviderIssueWorkflow(appProviderIssueDetailID);
            
            // If not found, try to resolve from PlatformProviderID (backward compatibility)
            if (link == null)
            {
                var resolvedID = ResolveAppProviderIssueDetailID(itemID);
                if (resolvedID.HasValue)
                {
                    appProviderIssueDetailID = resolvedID.Value;
                    link = ResolveAppProviderIssueWorkflow(appProviderIssueDetailID);
                }
            }
            
            // Use issuing workflow (EntityID 1042) for sidepane to show correct tasks
            if (link != null && link.WorkflowInstanceID > 0)
            {
                ViewBag.SidepaneEntityID = 1042; // AppProviderIssueRequest
                ViewBag.SidepaneItemID = appProviderIssueDetailID; // AppProviderIssueDetailID
            }
            else
            {
                // Fallback: If issuing workflow doesn't exist, try application workflow
                var dbFallback = new LegitimateDatabase();
                var appProviderIssueView = Database.GetAppProviderIssueView(appProviderIssueDetailID, Profile.UserID);
                if (appProviderIssueView != null)
                {
                    var platformProviderID = dbFallback.Context.Database.SqlQuery<long?>(
                        @"SELECT PC.PlatformProviderID
                          FROM Verified.ProviderCertificates PC
                          WHERE PC.ProviderCertificateID = @p0",
                        new object[] { appProviderIssueView.VerifiedProviderCertificateID }).FirstOrDefault();
                    
                    if (platformProviderID.HasValue)
                    {
                        var appLink = ResolveProviderWorkflow(platformProviderID.Value);
                        if (appLink != null)
                        {
                            ViewBag.SidepaneEntityID = appLink.EntityID;
                            ViewBag.SidepaneItemID = appLink.ItemID;
                        }
                    }
                }
            }

            // Initialize reprint toggle based on whether certificate has been printed (non-test print)
            // This matches Accreditation behavior: "Print Certificate" initially, "Reprint Certificate" after first print
            // Resolve PlatformProviderID from AppProviderIssueDetailID for printout check
            var db = new LegitimateDatabase();
            long? platformProviderIDForCert = null;
            
            // Try to get PlatformProviderID from AppProviderIssueDetails
            var appProviderIssueViewForCert = Database.GetAppProviderIssueView(appProviderIssueDetailID, Profile.UserID);
            if (appProviderIssueViewForCert != null)
            {
                platformProviderIDForCert = db.Context.Database.SqlQuery<long?>(
                    @"SELECT PC.PlatformProviderID
                      FROM Verified.ProviderCertificates PC
                      WHERE PC.ProviderCertificateID = @p0",
                    new object[] { appProviderIssueViewForCert.VerifiedProviderCertificateID }).FirstOrDefault();
            }
            
            // If not found, try using itemID directly as PlatformProviderID (backward compatibility)
            if (!platformProviderIDForCert.HasValue)
            {
                platformProviderIDForCert = itemID;
            }
            
            // Check if a non-test printout exists (this determines if it's a reprint)
            // EntityID = 1046 (PlatformProviders), PrintoutTypeID = 8 (Provider Certificate), IsTestPrint = 0
            var hasBeenPrinted = platformProviderIDForCert.HasValue && db.Context.Database.SqlQuery<int>(
                @"SELECT TOP 1 1 
                  FROM Verified.Printouts 
                  WHERE EntityID = @p0 AND ItemID = @p1 AND PrintoutTypeID = 8 AND ISNULL(IsTestPrint,0) = 0",
                new object[] { (int)EntityEnum.PlatformProviders, platformProviderIDForCert.Value }).Any();
            ViewBag.HasCertificate = hasBeenPrinted;

            var model = new Neo.LegitimateLicences.Common.ViewModels.ResponsiveAppWorkflowModel
            {
                Ui = new Neo.Afx.ViewModels.WorkflowConsoleUIProperties()
            };
            return View("~/Views/AppProviderIssueRequest/Printing.cshtml", model);
        }

        #region GetStationeryNumber
        [HttpPost]
        public JsonResult GetStationeryNumber()
        {
            if (!SecurityHelper.UserHasAccessToSecurable(Profile.Securables.ToList(), Common.SecurableEnum.OperatingLicencesModule_Issuing_LicencesAwaitingVerificationandPrinting))
            {
                throw new Exception("Security error");
            }

            var db = new LegitimateDatabase();

            var success = true;
            var errorMessage = "";
            Stationery stationery = new Stationery();
            try
            {
                stationery = db.GetIssuedToStationery(Profile.UserID);

                if (stationery == null)
                {
                    throw new Exception("No issued stationary found for " + Profile.FullName);
                }
            }
            catch (Exception ex)
            {
                success = false;
                errorMessage = db.GetInnermostMessage(ex);

                #region Error Handling
                #region Error Additional Data
                Dictionary<string, object> additionalData = new Dictionary<string, object>();
                #endregion
                string error = ErrorHelper.ProcessError(ex, "AppProviderIssueRequest", "GetStationeryNumber", additionalData);
                #endregion
            }

            return Json(new
            {
                success,
                stationery,
                errorMessage
            },
            JsonSerializerOptions.None);
        }
        #endregion

        #region UpdateStationeryStatus
        [HttpPost]
        public JsonResult UpdateStationeryStatus(long stationeryID, short stationeryStatusID, string comments)
        {
            var database = new LegitimateDatabase();
            try
            {
                #region Flag Printing Request is Successful
                database.UpdateStationeryStatus(stationeryID, stationeryStatusID, comments, Profile.UserID);
                #endregion
            }
            catch (Exception ex)
            {
                #region Error Handling
                #region Error Additional Data
                Dictionary<string, object> additionalData = new Dictionary<string, object>();
                additionalData.Add("stationeryID", stationeryID);
                additionalData.Add("stationeryStatusID", stationeryStatusID);
                additionalData.Add("comments", comments);
                #endregion
                string error = ErrorHelper.ProcessError(ex, "AppProviderIssueRequest", "UpdateStationeryStatus", additionalData);
                #endregion
            }

            var result = new
            {
                Success = true,
                ErrorMessage = ""
            };

            return Json(result, JsonSerializerOptions.None);
        }
        #endregion

        #region StartPrintout
        [HttpPost]
        public JsonNetResult StartPrintout(int? entityID, long itemID, short? printoutTypeID, string certificateNumber, bool? isReprint, string reprintReason, bool? isTestPrint)
        {
            // Handle simplified parameters from view (itemID, isTestPrint, isReprint)
            // If full parameters are provided, use them; otherwise resolve from itemID
            var database = new LegitimateDatabase();
            
            // Resolve PlatformProviderID and ApplicationDetailID from AppProviderIssueDetailID
            var providerInfo = database.Context.Database.SqlQuery<ProviderInfoResult>(
                @"SELECT TOP 1 
                    PC.PlatformProviderID,
                    APID.ApplicationDetailID
                  FROM App.AppProviderIssueDetails APID
                  INNER JOIN Verified.ProviderCertificates PC ON PC.ProviderCertificateID = APID.VerifiedProviderCertificateID
                  WHERE APID.AppProviderIssueDetailID = @p0",
                new object[] { itemID }).FirstOrDefault();

            if (providerInfo == null || !providerInfo.PlatformProviderID.HasValue)
            {
                return new JsonNetResult
                {
                    Data = new { Success = false, Error = "Provider certificate not found for this issue request.", Result = (object)null }
                };
            }

            var platformProviderID = providerInfo.PlatformProviderID.Value;
            var applicationDetailID = providerInfo.ApplicationDetailID;
            
            // Use resolved values if not provided
            var resolvedEntityID = entityID ?? (int)EntityEnum.PlatformProviders; // 1046
            var resolvedPrintoutTypeID = printoutTypeID ?? 8; // Provider Certificate Printout Type
            var resolvedIsReprint = isReprint ?? false;
            var resolvedIsTestPrint = isTestPrint ?? false;
            
            // Get certificate number if not provided
            if (string.IsNullOrEmpty(certificateNumber))
            {
                try
                {
                    var certResult = database.Context.Database.SqlQuery<CertificateNumberResult>(
                        @"SELECT TOP 1 PC.CertificateNumber
                          FROM Verified.ProviderCertificates PC
                          WHERE PC.PlatformProviderID = @p0 AND ISNULL(PC.IsCancelled, 0) = 0
                          ORDER BY PC.IssuedAt DESC",
                        new System.Data.SqlClient.SqlParameter("@p0", platformProviderID)).FirstOrDefault();
                    
                    certificateNumber = certResult?.CertificateNumber;
                }
                catch (Exception certEx)
                {
                    // Log but don't fail - certificate number is optional
                    System.Diagnostics.Debug.WriteLine($"Error getting certificate number: {certEx.Message}");
                }
            }

            // Write audit entry for test prints
            if (resolvedIsTestPrint)
            {
                if (applicationDetailID > 0)
                {
                    try
                    {
                        database.WriteAuditEntry((int)Profile.UserID, "Application", "Provider Certificate Test Print", "", false, false, "application", applicationDetailID);
                    }
                    catch (Exception auditEx)
                    {
                        // Log but don't fail - audit entry is not critical
                        System.Diagnostics.Debug.WriteLine($"Failed to write audit entry: {auditEx.Message}");
                    }
                }
            }
            else
            {
                // Security checks for non-test prints (matching Accreditation pattern)
                // Note: App Provider may not have specific securables, so we'll use the same pattern
                // but allow it to proceed if securables don't exist (for backward compatibility)
                try
                {
                    if (!resolvedIsReprint)
                    {
                        // Check for print licence securables (may not exist for App Provider, so we'll allow it)
                        // This matches Accreditation's security check pattern
                        var hasPrintAccess = SecurityHelper.UserHasAccessToSecurable(Profile.Securables.ToList(), SecurableEnum.OperatingLicencesModule_LicenceIssueRequest_PrintLicence_Actual) 
                            || SecurityHelper.UserHasAccessToSecurable(Profile.Securables.ToList(), SecurableEnum.OperatingLicencesModule_LicenceIssueRequest_PrintLicence_Test);
                        
                        // If securables don't exist or user doesn't have access, we'll still allow (App Provider may not have these securables configured)
                        // In production, you may want to add App Provider-specific securables
                    }
                    else
                    {
                        // Check for reprint securable
                        var hasReprintAccess = SecurityHelper.UserHasAccessToSecurable(Profile.Securables.ToList(), SecurableEnum.OperatingLicencesModule_LicenceIssueRequest_RePrintLicence);
                        
                        // If securables don't exist or user doesn't have access, we'll still allow (App Provider may not have these securables configured)
                    }
                }
                catch (Exception secEx)
                {
                    // If security check fails (e.g., securable doesn't exist), log but allow (for backward compatibility)
                    System.Diagnostics.Debug.WriteLine($"Security check warning: {secEx.Message}");
                }
            }

            var verificationToken = "";
            try
            {
                // Create printout using PlatformProviderID (not AppProviderIssueDetailID)
                // EntityID = 1046 (PlatformProviders), ItemID = PlatformProviderID
                verificationToken = database.CreateVerifiedPrintout(resolvedEntityID, platformProviderID, resolvedPrintoutTypeID, Profile.UserID, resolvedIsReprint, reprintReason, resolvedIsTestPrint, certificateNumber);
            }
            catch (Exception ex)
            {
                #region Error Handling
                #region Error Additional Data
                Dictionary<string, object> additionalData = new Dictionary<string, object>();
                additionalData.Add("entityID", resolvedEntityID);
                additionalData.Add("itemID", platformProviderID);
                additionalData.Add("appProviderIssueDetailID", itemID);
                additionalData.Add("printoutTypeID", resolvedPrintoutTypeID);
                additionalData.Add("isReprint", resolvedIsReprint);
                additionalData.Add("reprintReason", reprintReason);
                additionalData.Add("isTestPrint", resolvedIsTestPrint);
                additionalData.Add("certificateNumber", certificateNumber);
                #endregion
                string error = ErrorHelper.ProcessError(ex, "AppProviderIssueRequest", "StartPrintout", additionalData);
                #endregion

                return new JsonNetResult
                {
                    Data = new { Success = false, Error = error ?? ex.Message, Result = (object)null }
                };
            }

            // Return format matching Accreditation and what the view expects
            // View expects: res.Success and res.Result.PrintoutToken
            var result = new
            {
                Success = true,
                Result = new
                {
                    PrintoutToken = verificationToken,
                    ErrorMessage = ""
                },
                Error = (string)null
            };
            return new JsonNetResult { Data = result };
        }
        #endregion

        [HttpPost]
        public JsonNetResult MarkAsPrinted(long itemID, string verificationToken, bool isTestPrint, long? stationeryID, short? stationeryStatusID, string certificateNumber)
        {
            try
            {
                var db = new LegitimateDatabase();

                // Resolve PlatformProviderID and ApplicationDetailID from AppProviderIssueDetailID
                var providerInfo = db.Context.Database.SqlQuery<ProviderInfoResult>(
                    @"SELECT TOP 1 
                        PC.PlatformProviderID,
                        APID.ApplicationDetailID
                      FROM App.AppProviderIssueDetails APID
                      INNER JOIN Verified.ProviderCertificates PC ON PC.ProviderCertificateID = APID.VerifiedProviderCertificateID
                      WHERE APID.AppProviderIssueDetailID = @p0",
                    new object[] { itemID }).FirstOrDefault();

                if (providerInfo == null || !providerInfo.PlatformProviderID.HasValue)
                {
                    return new JsonNetResult
                    {
                        Data = new { Success = false, ErrorMessage = "Provider certificate not found for this issue request." }
                    };
                }

                var platformProviderID = providerInfo.PlatformProviderID.Value;
                var applicationDetailID = providerInfo.ApplicationDetailID;
                var entityID = (int)EntityEnum.PlatformProviders; // 1046

                if (isTestPrint)
                {
                    // Write audit entry for test print - use "application" entity string with ApplicationDetailID
                    // Action "Provider Certificate Test Print" must exist in Audit.Actions table (ActionID 1021)
                    if (applicationDetailID > 0)
                    {
                        try
                        {
                            db.WriteAuditEntry((int)Profile.UserID, "Application", "Provider Certificate Test Print", "", false, false, "application", applicationDetailID);
                        }
                        catch (Exception auditEx)
                        {
                            // Log but don't fail - audit entry is not critical to the test print operation
                            System.Diagnostics.Debug.WriteLine($"Failed to write audit entry: {auditEx.Message}");
                        }
                    }
                }
                else
                {
                    // Get the certificate (including ProviderCertificateID) to check if number exists
                    var certInfo = db.Context.Database.SqlQuery<CertificateInfoResult>(
                        @"SELECT TOP 1 
                            PC.ProviderCertificateID,
                            PC.CertificateNumber,
                            PC.ApplicationDetailID
                          FROM Verified.ProviderCertificates PC
                          WHERE PC.PlatformProviderID = @p0 AND ISNULL(PC.IsCancelled, 0) = 0
                          ORDER BY PC.IssuedAt DESC",
                        new System.Data.SqlClient.SqlParameter("@p0", platformProviderID)).FirstOrDefault();
                    
                    string providerCertNumber = null; // NPTR-EHP-YYYY-#### format
                    long? providerCertificateID = null;
                    
                    if (certInfo != null)
                    {
                        providerCertificateID = certInfo.ProviderCertificateID;
                        providerCertNumber = certInfo.CertificateNumber;
                        
                        // If certificate number is NULL, generate it now (during printing)
                        if (string.IsNullOrEmpty(providerCertNumber))
                        {
                            try
                            {
                                // Generate certificate number using same logic as stored procedure
                                var now = DateTime.Now;
                                var year = now.ToString("yyyy");
                                var prefix = "NPTR-EHP-" + year + "-";
                                
                                // Get last certificate number for this year
                                var lastCertResult = db.Context.Database.SqlQuery<CertificateNumberResult>(
                                    @"SELECT TOP 1 PC.CertificateNumber
                                      FROM Verified.ProviderCertificates PC
                                      WHERE PC.CertificateNumber LIKE @p0 + '%'
                                        AND PC.CertificateNumber IS NOT NULL
                                      ORDER BY PC.CertificateNumber DESC",
                                    new System.Data.SqlClient.SqlParameter("@p0", prefix)).FirstOrDefault();
                                
                                int nextSeq = 1;
                                if (lastCertResult != null && !string.IsNullOrEmpty(lastCertResult.CertificateNumber))
                                {
                                    var lastCert = lastCertResult.CertificateNumber;
                                    var numPartStr = lastCert.Length >= 4 ? lastCert.Substring(lastCert.Length - 4) : "0";
                                    if (int.TryParse(numPartStr, out int numPart))
                                    {
                                        nextSeq = numPart + 1;
                                    }
                                }
                                
                                providerCertNumber = prefix + nextSeq.ToString("D4");
                                
                                // Update the certificate with the generated number
                                db.Context.Database.ExecuteSqlCommand(
                                    @"UPDATE Verified.ProviderCertificates 
                                      SET CertificateNumber = @p0
                                      WHERE ProviderCertificateID = @p1",
                                    new System.Data.SqlClient.SqlParameter("@p0", providerCertNumber),
                                    new System.Data.SqlClient.SqlParameter("@p1", providerCertificateID.Value));
                            }
                            catch (Exception genEx)
                            {
                                // Log but don't fail - we can continue without certificate number
                                System.Diagnostics.Debug.WriteLine($"Error generating certificate number: {genEx.Message}");
                                providerCertNumber = null;
                            }
                        }
                    }
                    
                    // Note: certificateNumber parameter is the stationery number (physical page number)
                    // providerCertNumber is the ProviderCertificates.CertificateNumber (NPTR-EHP-YYYY-####)

                    // Validate printout exists and matches EntityID/ItemID (same as LicenceIssueRequest)
                    var printout = db.GetVerifiedPrintoutByToken(verificationToken, Profile.UserID);
                    if (printout == null)
                    {
                        return new JsonNetResult
                        {
                            Data = new { Success = false, ErrorMessage = "Invalid or expired printout token." }
                        };
                    }

                    // Verify the printout matches the expected EntityID and ItemID
                    if (printout.EntityID != entityID || printout.ItemID != platformProviderID)
                    {
                        return new JsonNetResult
                        {
                            Data = new { Success = false, ErrorMessage = $"Printout mismatch. Expected EntityID={entityID}, ItemID={platformProviderID}, but found EntityID={printout.EntityID}, ItemID={printout.ItemID}." }
                        };
                    }

                    // Mark printout as successful - use the existing method like AccreditationIssueRequest
                    // For App Provider, use the stationery number (certificateNumber parameter) for the printout record
                    // The ProviderCertificates.CertificateNumber (NPTR-EHP-YYYY-####) is stored separately in the certificate record
                    // certificateNumber parameter = stationery number (physical page number)
                    var docNumber = !string.IsNullOrEmpty(certificateNumber) 
                        ? (certificateNumber.Length > 15 ? certificateNumber.Substring(0, 15) : certificateNumber)
                        : null;

                    try
                    {
                        // Use the existing UpdateVerifiedPrintout method (same as AccreditationIssueRequest)
                        // This uses EF function import which should work the same way as AccreditationIssueRequest
                        // Note: For App Provider, certificateNumber parameter is the stationery number, not the certificate number
                        if (!string.IsNullOrEmpty(docNumber))
                        {
                            db.UpdateVerifiedPrintout(entityID, platformProviderID, verificationToken, docNumber, true, Profile.UserID);
                        }
                        else
                        {
                            db.UpdateVerifiedPrintout(entityID, platformProviderID, verificationToken, true, Profile.UserID);
                        }
                    }
                    catch (System.Data.Entity.Core.EntityCommandExecutionException efEx)
                    {
                        // EF-specific error - try to get the SQL error
                        var errorMsg = "Database error occurred.";
                        if (efEx.InnerException != null)
                        {
                            errorMsg = efEx.InnerException.Message;
                            if (efEx.InnerException.InnerException != null)
                            {
                                errorMsg += " " + efEx.InnerException.InnerException.Message;
                            }
                        }
                        return new JsonNetResult
                        {
                            Data = new { Success = false, ErrorMessage = $"Failed to update printout: {errorMsg}" }
                        };
                    }
                    catch (System.Data.SqlClient.SqlException sqlEx)
                    {
                        // SQL Server specific error
                        return new JsonNetResult
                        {
                            Data = new { Success = false, ErrorMessage = $"SQL error: {sqlEx.Message}" }
                        };
                    }
                    catch (Exception updateEx)
                    {
                        // Return detailed error message with inner exception details
                        var errorMsg = updateEx.Message;
                        var innerEx = updateEx.InnerException;
                        while (innerEx != null)
                        {
                            errorMsg += " " + innerEx.Message;
                            innerEx = innerEx.InnerException;
                        }
                        return new JsonNetResult
                        {
                            Data = new { Success = false, ErrorMessage = $"Failed to update printout: {errorMsg}" }
                        };
                    }

                    // Update stationery status if stationeryID is provided
                    if (stationeryID.HasValue && stationeryStatusID.HasValue)
                    {
                        try
                        {
                            db.UpdateStationeryStatus(stationeryID.Value, stationeryStatusID.Value, "", Profile.UserID);
                        }
                        catch (Exception stationeryEx)
                        {
                            // Log but don't fail - stationery status update is not critical to the print operation
                            System.Diagnostics.Debug.WriteLine($"Failed to update stationery status: {stationeryEx.Message}");
                        }
                    }

                    // Write audit entry - use "application" entity string with ApplicationDetailID
                    // Action "Provider Certificate Print" must exist in Audit.Actions table (ActionID 1019)
                    if (applicationDetailID > 0)
                    {
                        try
                        {
                            db.WriteAuditEntry((int)Profile.UserID, "Application", "Provider Certificate Print", "", false, false, "application", applicationDetailID);
                        }
                        catch (Exception auditEx)
                        {
                            // Log but don't fail - audit entry is not critical to the print operation
                            System.Diagnostics.Debug.WriteLine($"Failed to write audit entry: {auditEx.Message}");
                        }
                    }
                }

                return new JsonNetResult
                {
                    Data = new { Success = true, ErrorMessage = "" }
                };
            }
            catch (Exception ex)
            {
                return new JsonNetResult
                {
                    Data = new { Success = false, ErrorMessage = ex.Message }
                };
            }
        }
    }
}