using Neo.Afx.Mvc;
using Neo.Afx.Common;
using Neo.Afx.Security;
using Neo.Afx.Services.Workflows.Entities;
using Neo.LegitimateLicences.Common;
using Neo.LegitimateLicences.Common.Helpers;
using Neo.LegitimateLicences.Common.ViewModels;
using Neo.LegitimateLicences.Data.Models;
using Neo.LegitimateLicences.AppProviderIssueRequest.Models.Database;
using System;
using System.Linq;
using System.Web.Mvc;
using System.Collections.Generic;

namespace Neo.LegitimateLicences.AppProviderIssueRequest
{
    public partial class AppProviderIssueRequestController : Neo.LegitimateLicences.Common.ControllerBase
    {
        private AppProviderIssueDatabase Database = new AppProviderIssueDatabase();

        private class WorkflowLink
        {
            public int EntityID { get; set; }
            public long ItemID { get; set; }
            public long WorkflowInstanceID { get; set; }
            public string ReferenceNumber { get; set; }
            public double? Step { get; set; }
        }

        /// <summary>
        /// Resolves AppProviderIssueDetailID from PlatformProviderID (for backward compatibility)
        /// </summary>
        private long? ResolveAppProviderIssueDetailID(long platformProviderID)
        {
            var db = new LegitimateDatabase();
            var result = db.Context.Database.SqlQuery<long?>(
                @"SELECT TOP 1 APID.AppProviderIssueDetailID
                  FROM App.AppProviderIssueDetails APID
                  INNER JOIN Verified.ProviderCertificates PC ON PC.ProviderCertificateID = APID.VerifiedProviderCertificateID
                  WHERE PC.PlatformProviderID = @p0
                  ORDER BY APID.CreatedDate DESC",
                new object[] { platformProviderID }).FirstOrDefault();
            return result;
        }

        /// <summary>
        /// Resolves issuing workflow for AppProviderIssueDetailID (EntityID 1042)
        /// </summary>
        private WorkflowLink ResolveAppProviderIssueWorkflow(long appProviderIssueDetailID)
        {
            var db = new LegitimateDatabase();
            var sql = @"
                        SELECT TOP 1
                            CAST(1042 AS INT) AS EntityID, /*AppProviderIssueRequest*/
                            APID.AppProviderIssueDetailID AS ItemID,
                            WI.WorkflowInstanceID,
                            WI.ReferenceNumber,
                            WTS.Step
                        FROM App.AppProviderIssueDetails APID
                        LEFT JOIN Wf.WorkflowInstances WI ON WI.WorkflowInstanceID = APID.WorkflowInstanceID
                        LEFT JOIN Wf.WorkflowTemplateSteps WTS ON WTS.WorkflowTemplateStepID = WI.WorkflowTemplateStepID
                        WHERE APID.AppProviderIssueDetailID = @AppProviderIssueDetailID";
            return db.Context.Database.SqlQuery<WorkflowLink>(sql,
                new System.Data.SqlClient.SqlParameter("@AppProviderIssueDetailID", appProviderIssueDetailID)).FirstOrDefault();
        }

        /// <summary>
        /// Legacy method: Resolves application workflow from PlatformProviderID (for backward compatibility)
        /// </summary>
        private WorkflowLink ResolveProviderWorkflow(long platformProviderID)
        {
            var db = new LegitimateDatabase();
            var sql = @"
                        SELECT TOP 1
                            CAST(1000 AS INT) AS EntityID, /*Applications*/
                            AD.ApplicationDetailID        AS ItemID,
                            WI.WorkflowInstanceID,
                            WI.ReferenceNumber,
                            WTS.Step
                        FROM App.ApplicationDetails AD
                        INNER JOIN App.ApplicationApplicantDetails AAD ON AAD.ApplicationDetailID = AD.ApplicationDetailID
                        INNER JOIN Wf.WorkflowInstances WI ON WI.WorkflowInstanceID = AD.WorkflowInstanceID
                        INNER JOIN Wf.WorkflowTemplateSteps WTS ON WTS.WorkflowTemplateStepID = WI.WorkflowTemplateStepID
                        INNER JOIN Verified.PlatformProviders PP ON PP.PlatformProviderID = @PlatformProviderID
                        WHERE AD.ApplicationRequestTypeID IN (60,61,62) /*App provider request types*/
                          AND (
                                (NULLIF(LTRIM(RTRIM(PP.BusinessRegNumber)), '') IS NOT NULL AND LTRIM(RTRIM(ISNULL(AAD.IDNumberOrBusinessRegistrationNo, ''))) = LTRIM(RTRIM(PP.BusinessRegNumber)))
                                OR
                                (NULLIF(LTRIM(RTRIM(PP.TradingName)), '') IS NOT NULL AND LTRIM(RTRIM(ISNULL(AAD.TradeName, ''))) = LTRIM(RTRIM(PP.TradingName)))
                                OR
                                (NULLIF(LTRIM(RTRIM(PP.LegalName)), '') IS NOT NULL AND LTRIM(RTRIM(ISNULL(AAD.SurnameOrCompanyName, ''))) = LTRIM(RTRIM(PP.LegalName)))
                              )
                        ORDER BY WI.UpdatedDate DESC, WI.CreatedDate DESC";
            return db.Context.Database.SqlQuery<WorkflowLink>(sql,
                new System.Data.SqlClient.SqlParameter("@PlatformProviderID", platformProviderID)).FirstOrDefault();
        }

        private class ProviderSummary
        {
            public long PlatformProviderID { get; set; }
            public string LegalName { get; set; }
            public string TradingName { get; set; }
            public string BusinessRegNumber { get; set; }
            public string CertificateNumber { get; set; }
            public System.DateTime? IssuedAt { get; set; }
            public System.DateTime? ExpiresAt { get; set; }
        }

        [HttpGet]
        public ActionResult Console(long itemID)
        {
            // Security same as accreditation style
            var securables = (Profile != null && Profile.Securables != null) ? Profile.Securables.ToList() : null;
            var canAccess = Profile.IsNPTR || SecurityHelper.UserHasAccessToSecurable(securables, SecurableEnum.ApplicationsModule);
            if (!canAccess)
            {
                return RedirectToAction("ShowError", "Error", new { msg = "Security error" });
            }

            // Determine if itemID is AppProviderIssueDetailID or PlatformProviderID
            // Try to resolve as AppProviderIssueDetailID first (issuing workflow)
            var appProviderIssueDetailID = itemID;
            var linkCtx = ResolveAppProviderIssueWorkflow(appProviderIssueDetailID);
            
            // If not found, try to resolve from PlatformProviderID (backward compatibility)
            if (linkCtx == null)
            {
                var resolvedID = ResolveAppProviderIssueDetailID(itemID);
                if (resolvedID.HasValue)
                {
                    appProviderIssueDetailID = resolvedID.Value;
                    linkCtx = ResolveAppProviderIssueWorkflow(appProviderIssueDetailID);
                }
            }

            // Get AppProviderIssueDetails view
            var appProviderIssueView = Database.GetAppProviderIssueView(appProviderIssueDetailID, Profile.UserID);
            
            if (appProviderIssueView == null)
            {
                return RedirectToAction("ShowError", "Error", new { msg = "App Provider Issue Details not found" });
            }

            // Sidepane context: Use related application workflow for sidepane
            // Get PlatformProviderID from the certificate to resolve application workflow
            var db = new LegitimateDatabase();
            var platformProviderID = db.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;
                }
            }

            ViewBag.Controller = "App Provider";
            ViewBag.Page = "Provider Console";
            ViewBag.AppProviderIssueView = appProviderIssueView;

            // Populate ViewBag.Provider for Console view (similar to GetConsoleData)
            if (platformProviderID.HasValue)
            {
                var provider = db.Context.Database.SqlQuery<ProviderSummary>(
                    @"SELECT PP.PlatformProviderID, PP.LegalName, PP.TradingName, PP.BusinessRegNumber,
                             VC.CertificateNumber, VC.IssuedAt, VC.ExpiresAt
                      FROM Verified.PlatformProviders PP
                      OUTER APPLY (
                          SELECT TOP 1 CertificateNumber, IssuedAt, ExpiresAt
                          FROM Verified.ProviderCertificates PC
                          WHERE PC.PlatformProviderID = PP.PlatformProviderID
                          ORDER BY PC.IssuedAt DESC
                      ) VC
                      WHERE PP.PlatformProviderID = @p0", new object[] { platformProviderID.Value }).FirstOrDefault();
                ViewBag.Provider = provider;
            }

            // Get workflow instance for issuing workflow (EntityID 1042)
            var workflowInstance = (linkCtx != null && linkCtx.WorkflowInstanceID > 0) 
                ? WorkflowHelper.GetWorkflowInstanceView(1042, appProviderIssueDetailID, Profile.UserID)
                : null;

            // Get active user task (EntityID 1042)
            Vw_WorkflowInstanceTask activeUserTask = null;
            if (linkCtx != null && linkCtx.WorkflowInstanceID > 0)
            {
                activeUserTask = WorkflowHelper.GetActiveUserTask(Profile.UserID, 1042, appProviderIssueDetailID);
            }

            var model = new ResponsiveAppWorkflowModel
            {
                Ui = new Neo.Afx.ViewModels.WorkflowConsoleUIProperties
                {
                    canSubmit = (activeUserTask != null && activeUserTask.WorkflowTemplateStepTypeID == (short)WorkflowTemplateStepTypeEnum.DataCapture),
                    canViewDocuments = true,
                    canViewForm = true,
                    canViewComments = true,
                    canViewTasks = true,
                    canViewCorrespondence = true,
                    canApproveReject = (activeUserTask != null && activeUserTask.WorkflowTemplateStepTypeID == (short)WorkflowTemplateStepTypeEnum.Approval),
                    canRequestCorrection = (activeUserTask != null && activeUserTask.WorkflowTemplateStepNumber > 1),
                    canDelete = (activeUserTask != null)
                }
            };
            return View("Console", model);
        }

        // StartPrintout method moved to AppProviderIssueRequestController.Printing.cs
        // This duplicate method has been removed to resolve ambiguity

        [HttpGet]
        public JsonNetResult GetDashboardData(long itemID)
        {
            try
            {
                var link = ResolveProviderWorkflow(itemID);
                if (link == null)
                {
                    return new JsonNetResult { Data = new { Success = false, Error = "No related application found." }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
                }
                return new JsonNetResult { Data = new { Success = true, Result = link }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
            }
            catch (Exception ex)
            {
                return new JsonNetResult { Data = new { Success = false, Error = ex.Message }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
            }
        }

        [HttpGet]
        public JsonNetResult GetPlatformProviderID(long appProviderIssueDetailID)
        {
            try
            {
                var db = new LegitimateDatabase();
                var platformProviderID = db.Context.Database.SqlQuery<long?>(
                    @"SELECT TOP 1 PC.PlatformProviderID
                      FROM App.AppProviderIssueDetails APID
                      INNER JOIN Verified.ProviderCertificates PC ON PC.ProviderCertificateID = APID.VerifiedProviderCertificateID
                      WHERE APID.AppProviderIssueDetailID = @p0
                        AND APID.IsCancelled = 0",
                    new object[] { appProviderIssueDetailID }).FirstOrDefault();

                if (!platformProviderID.HasValue)
                {
                    return new JsonNetResult { Data = new { Success = false, Error = "PlatformProviderID not found for this App Provider Issue Request." }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
                }

                return new JsonNetResult { Data = new { Success = true, Result = new { PlatformProviderID = platformProviderID.Value } }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
            }
            catch (Exception ex)
            {
                return new JsonNetResult { Data = new { Success = false, Error = ex.Message }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
            }
        }

        [HttpGet]
        public JsonNetResult GetPrintingContext(long itemID)
        {
            try
            {
                var link = ResolveProviderWorkflow(itemID);
                if (link == null)
                {
                    return new JsonNetResult { Data = new { Success = false, Error = "No related application found." }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
                }
                var wd = new Neo.Afx.Services.Workflows.WorkflowsDatabase();
                var at = WorkflowHelper.GetActiveUserTask(Profile.UserID, link.EntityID, link.ItemID);
                var canAssignToSelf = false;
                if (at == null)
                {
                    // Get any outstanding task and check if can assign to me
                    var wi = wd.GetWorkflowInstance(link.EntityID, link.ItemID);
                    var nextOutstanding = wi.WorkflowInstanceTasks.FirstOrDefault(t => t.WorkflowInstanceTaskStatusID == 1 /*outstanding*/);
                    if (nextOutstanding != null)
                    {
                        canAssignToSelf = wd.WorkflowInstanceTaskCanAssignToMe(nextOutstanding.WorkflowInstanceTaskID, Profile.UserID);
                    }
                }
                var assignedToMe = (at != null);
                return new JsonNetResult { Data = new { Success = true, Result = new { link.EntityID, link.ItemID, AssignedToMe = assignedToMe, CanAssignToSelf = canAssignToSelf } }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
            }
            catch (Exception ex)
            {
                return new JsonNetResult { Data = new { Success = false, Error = ex.Message }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
            }
        }

        [HttpPost]
        public JsonNetResult AssignTaskToMe(long itemID)
        {
            try
            {
                var link = ResolveProviderWorkflow(itemID);
                if (link == null)
                {
                    return new JsonNetResult { Data = new { Success = false, Error = "No related application found." } };
                }
                var wd = new Neo.Afx.Services.Workflows.WorkflowsDatabase();
                var wi = wd.GetWorkflowInstance(link.EntityID, link.ItemID);
                var nextOutstanding = wi.WorkflowInstanceTasks.FirstOrDefault(t => t.WorkflowInstanceTaskStatusID == 1 /*outstanding*/);
                if (nextOutstanding == null)
                {
                    return new JsonNetResult { Data = new { Success = false, Error = "No outstanding task to assign." } };
                }
                var canAssign = wd.WorkflowInstanceTaskCanAssignToMe(nextOutstanding.WorkflowInstanceTaskID, Profile.UserID);
                if (!canAssign)
                {
                    return new JsonNetResult { Data = new { Success = false, Error = "You cannot assign this task to yourself." } };
                }
                wd.ReAssignWorkflowInstanceTask(nextOutstanding.WorkflowInstanceTaskID, Profile.UserID, "Self-assigned", User.Identity.Name);
                return new JsonNetResult { Data = new { Success = true } };
            }
            catch (Exception ex)
            {
                return new JsonNetResult { Data = new { Success = false, Error = ex.Message } };
            }
        }

        [HttpGet]
        public JsonNetResult GetConsoleData(long itemID)
        {
            try
            {
                var db = new LegitimateDatabase();

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

                if (!platformProviderID.HasValue)
                {
                    return new JsonNetResult
                    {
                        Data = new { Success = false, Error = "Provider certificate not found for this issue request." },
                        JsonRequestBehavior = JsonRequestBehavior.AllowGet
                    };
                }

                var provider = db.Context.Database.SqlQuery<ProviderSummary>(
                    @"SELECT PP.PlatformProviderID, PP.LegalName, PP.TradingName, PP.BusinessRegNumber,
                             VC.CertificateNumber, VC.IssuedAt, VC.ExpiresAt
                      FROM Verified.PlatformProviders PP
                      OUTER APPLY (
                          SELECT TOP 1 CertificateNumber, IssuedAt, ExpiresAt
                          FROM Verified.ProviderCertificates PC
                          WHERE PC.PlatformProviderID = PP.PlatformProviderID
                          ORDER BY PC.IssuedAt DESC
                      ) VC
                      WHERE PP.PlatformProviderID = @p0", new object[] { platformProviderID.Value }).FirstOrDefault();

                var wf = db.Context.Database.SqlQuery<dynamic>(
                    @"SELECT Wi.WorkflowInstanceID, Wi.ReferenceNumber, Wi.Description AS WorkflowInstanceDescription, 
                             Wts.StepTitle AS CurrentStepTitle, Wts.Step
                      FROM Wf.WorkflowInstances Wi
                      INNER JOIN Wf.WorkflowTemplateSteps Wts ON Wts.WorkflowTemplateStepID = Wi.WorkflowTemplateStepID
                      WHERE Wi.EntityID = @p0 AND Wi.ItemID = @p1",
                    new object[] { (int)EntityEnum.AppProviderIssueRequest, itemID }).FirstOrDefault();

                // Determine whether any non-test Provider Certificate print exists for this provider
                var hasNonTestPrint = 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, platformProviderID.Value }).Any();

                return new JsonNetResult
                {
                    Data = new { Success = true, Result = new { Provider = provider, Workflow = wf, HasNonTestPrint = hasNonTestPrint } },
                    JsonRequestBehavior = JsonRequestBehavior.AllowGet
                };
            }
            catch (Exception ex)
            {
                return new JsonNetResult { Data = new { Success = false, Error = ex.Message }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
            }
        }
    }
}