using Neo.Afx.Mvc;
using Neo.Afx.Common;
using Neo.Afx.Security;
using Neo.LegitimateLicences.Common;
using Neo.LegitimateLicences.Data.Models;
using System;
using System.Linq;
using System.Web.Mvc;

namespace Neo.LegitimateLicences.ProvidersRegistry.Controllers
{
    public class ProvidersRegistryController : Neo.LegitimateLicences.Common.ControllerBase
    {
        [HttpGet]
        public ActionResult List(string type)
        {
            // feature flag
            try
            {
                var db = new LegitimateDatabase();
                var flagVal = db.Context.Database.SqlQuery<string>("SELECT SettingValue FROM [System].[Settings] WHERE SettingName = @p0", new object[] { "ehailing.providers.enabled" }).FirstOrDefault();
                var enabled = string.Equals(flagVal, "TRUE", StringComparison.OrdinalIgnoreCase) || string.Equals(flagVal, "1");
                if (!enabled) return HttpNotFound();
            }
            catch { }

            ViewBag.TargetPage = "Dashboard";
            ViewBag.ViewName = (string.Equals(type, "awaitingprint", StringComparison.OrdinalIgnoreCase)) ? "Verification and final print" : "Search";
            ViewBag.Controller = "App Provider Registry";
            ViewBag.Page = ViewBag.ViewName;

            var m = new Neo.LegitimateLicences.Common.ViewModels.ResponsiveAppBaseModel();
            return View("List", m);
        }

        [HttpGet]
        public JsonNetResult IssueSearch(string steps = "1023")
        {
            try
            {
                var db = new LegitimateDatabase();
                var rows = db.Context.Database.SqlQuery<ProviderIssueRow>(
                    "EXEC Verified.Pr_ProviderIssueSearch @Steps, @UserID",
                    new System.Data.SqlClient.SqlParameter("@Steps", steps ?? "1023"),
                    new System.Data.SqlClient.SqlParameter("@UserID", Profile.UserID)
                ).ToList();

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

        [HttpGet]
        public JsonNetResult AppProviderIssueSearch(string steps = "58")
        {
            try
            {
                var db = new LegitimateDatabase();
                var rows = db.Context.Database.SqlQuery<AppProviderIssueSearchRow>(
                    "EXEC App.Pr_AppProviderIssueSearch @Steps, @UserID",
                    new System.Data.SqlClient.SqlParameter("@Steps", steps ?? "58"),
                    new System.Data.SqlClient.SqlParameter("@UserID", Profile.UserID)
                ).ToList();

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

        [HttpPost]
        public JsonNetResult SubmitIssueRequest(long platformProviderID)
        {
            try
            {
                var db = new LegitimateDatabase();
                var error = db.Context.Database.SqlQuery<string>(
                    "EXEC App.Pr_ProviderIssueSubmit @PlatformProviderID, @UserID",
                    new System.Data.SqlClient.SqlParameter("@PlatformProviderID", platformProviderID),
                    new System.Data.SqlClient.SqlParameter("@UserID", Profile.UserID)
                ).FirstOrDefault();

                if (!string.IsNullOrEmpty(error))
                {
                    return new JsonNetResult { Data = new { Success = false, Error = error } };
                }
                return new JsonNetResult { Data = new { Success = true } };
            }
            catch (Exception ex)
            {
                return new JsonNetResult { Data = new { Success = false, Error = ex.Message } };
            }
        }
        [HttpGet]
        public ActionResult Search()
        {
            // Feature flag gate via direct SQL read (no new project coupling)
            try
            {
                var db = new LegitimateDatabase();
                var flagVal = db.Context.Database.SqlQuery<string>("SELECT SettingValue FROM [System].[Settings] WHERE SettingName = @p0", new object[] { "ehailing.providers.enabled" }).FirstOrDefault();
                var enabled = string.Equals(flagVal, "TRUE", StringComparison.OrdinalIgnoreCase) || string.Equals(flagVal, "1");
                if (!enabled) return HttpNotFound();
            }
            catch { }

            var m = new Neo.LegitimateLicences.Common.ViewModels.ResponsiveAppBaseModel();
            return View(m);
        }

        [HttpGet]
        public JsonNetResult GetWorkflowDocumentsLink(long platformProviderID)
        {
            try
            {
                var db = new LegitimateDatabase();
                
                // First, try to find the issuing workflow (EntityID 1042) - this is the preferred option
                // Match by PlatformProviderID through ProviderCertificates
                var issuingWorkflowLink = db.Context.Database.SqlQuery<WorkflowDocLink>(
                    @"SELECT TOP 1
                        CAST(1042 AS INT) AS EntityID, /*AppProviderIssueRequest*/
                        APID.AppProviderIssueDetailID AS ItemID,
                        WI.WorkflowInstanceID,
                        WI.ReferenceNumber,
                        WTS.Step
                      FROM Verified.PlatformProviders PP
                      INNER JOIN Verified.ProviderCertificates PC ON PC.PlatformProviderID = PP.PlatformProviderID
                      INNER JOIN App.AppProviderIssueDetails APID ON APID.VerifiedProviderCertificateID = PC.ProviderCertificateID
                      INNER JOIN Wf.WorkflowInstances WI ON WI.WorkflowInstanceID = APID.WorkflowInstanceID
                      INNER JOIN Wf.WorkflowTemplateSteps WTS ON WTS.WorkflowTemplateStepID = WI.WorkflowTemplateStepID
                      WHERE PP.PlatformProviderID = @PlatformProviderID
                        AND WI.EntityID = 1042
                        AND APID.IsCancelled = 0
                      ORDER BY WI.UpdatedDate DESC, WI.CreatedDate DESC",
                    new System.Data.SqlClient.SqlParameter("@PlatformProviderID", platformProviderID)
                ).FirstOrDefault();

                if (issuingWorkflowLink != null)
                {
                    var url = string.Format("#/Afx/Documents?entityID={0}&itemID={1}", issuingWorkflowLink.EntityID, issuingWorkflowLink.ItemID);
                    return new JsonNetResult { Data = new { Success = true, Result = new { Url = url } }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
                }

                // Fallback: Use application workflow (EntityID 1000) if issuing workflow doesn't exist
                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";

                var link = db.Context.Database.SqlQuery<WorkflowDocLink>(
                    sql,
                    new System.Data.SqlClient.SqlParameter("@PlatformProviderID", platformProviderID)
                ).FirstOrDefault();

                if (link == null)
                {
                    return new JsonNetResult { Data = new { Success = false, Error = "No related workflow found." }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
                }

                var fallbackUrl = string.Format("#/Afx/Documents?entityID={0}&itemID={1}", link.EntityID, link.ItemID);
                return new JsonNetResult { Data = new { Success = true, Result = new { Url = fallbackUrl } }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
            }
            catch (Exception ex)
            {
                return new JsonNetResult { Data = new { Success = false, Error = ex.Message }, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
            }
        }

        public class WorkflowDocLink
        {
            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; }
        }
        [HttpGet]
        public JsonNetResult Query(string q, string status = null, int expiringWithinDays = 0, int page = 1, int pageSize = 50)
        {
            try
            {
                var db = new LegitimateDatabase();
                var flagVal = db.Context.Database.SqlQuery<string>("SELECT SettingValue FROM [System].[Settings] WHERE SettingName = @p0", new object[] { "ehailing.providers.enabled" }).FirstOrDefault();
                var enabled = string.Equals(flagVal, "TRUE", StringComparison.OrdinalIgnoreCase) || string.Equals(flagVal, "1");
                if (!enabled)
                {
                    return new JsonNetResult { Data = new { Success = false, Error = "#EXPOSE_ERROR#Feature disabled" } };
                }
            }
            catch { }

            var securables = (Profile != null && Profile.Securables != null) ? Profile.Securables.ToList() : null;
            var canAccess = Profile.IsNPTR || SecurityHelper.UserHasAccessToSecurable(securables, SecurableEnum.OperatingLicencesModule);
            if (!canAccess)
            {
                return new JsonNetResult
                {
                    Data = new { Success = false, Error = "#EXPOSE_ERROR#Security error" }
                };
            }

            var dbData = new LegitimateDatabase();
            var data = dbData.Context.Database.SqlQuery<ProviderRegistryRow>(
                @"SELECT * FROM Verified.Vw_ProviderRegistry").ToList();

            if (!string.IsNullOrWhiteSpace(q))
            {
                var ql = q.Trim().ToLower();
                data = data.Where(x => (x.LegalName ?? "").ToLower().Contains(ql)
                                     || (x.TradingName ?? "").ToLower().Contains(ql)
                                     || (x.BusinessRegNumber ?? "").ToLower().Contains(ql)
                                     || (x.CertificateNumber ?? "").ToLower().Contains(ql)).ToList();
            }

            if (!string.IsNullOrWhiteSpace(status))
            {
                data = data.Where(x => string.Equals(x.Status, status, StringComparison.OrdinalIgnoreCase)).ToList();
            }

            if (expiringWithinDays > 0)
            {
                var threshold = DateTime.UtcNow.AddDays(expiringWithinDays);
                data = data.Where(x => x.ExpiresAt != null && x.ExpiresAt >= DateTime.UtcNow && x.ExpiresAt <= threshold).ToList();
            }

            var total = data.Count();
            var items = data.OrderBy(x => x.LegalName).Skip((page - 1) * pageSize).Take(pageSize).ToList();

            return new JsonNetResult
            {
                Data = new { Success = true, Result = new { items, total } },
                JsonRequestBehavior = JsonRequestBehavior.AllowGet
            };
        }

        [HttpGet]
        public ActionResult Console(long platformProviderID)
        {
            // Feature flag gate
            try
            {
                var db = new LegitimateDatabase();
                var flagVal = db.Context.Database.SqlQuery<string>("SELECT SettingValue FROM [System].[Settings] WHERE SettingName = @p0", new object[] { "ehailing.providers.enabled" }).FirstOrDefault();
                var enabled = string.Equals(flagVal, "TRUE", StringComparison.OrdinalIgnoreCase) || string.Equals(flagVal, "1");
                if (!enabled) return HttpNotFound();
            }
            catch { }

            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" });
            }

            // Safest path: redirect to the dedicated workflow console with proper query params
            var url = string.Format("/AppProviderIssueRequest/Console?entityID={0}&itemID={1}#Dashboard", (int)EntityEnum.PlatformProviders, platformProviderID);
            return Redirect(url);
        }

        [HttpPost]
        public JsonNetResult StartProviderPrintout(long platformProviderID, bool isTestPrint, bool isReprint)
        {
            try
            {
                var db = new LegitimateDatabase();
                var token = db.CreateVerifiedPrintout((int)EntityEnum.PlatformProviders,
                    platformProviderID,
                    (short)PrintoutTypeEnum.ProviderCertificate,
                    Profile.UserID,
                    isReprint,
                    null,
                    isTestPrint,
                    null);

                return new JsonNetResult
                {
                    Data = new { Success = true, Result = new { PrintoutToken = token } }
                };
            }
            catch (Exception ex)
            {
                return new JsonNetResult
                {
                    Data = new { Success = false, Error = ex.Message }
                };
            }
        }

        public class ProviderRegistryRow
        {
            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 DateTime? IssuedAt { get; set; }
            public DateTime? ExpiresAt { get; set; }
            public string Status { get; set; }
        }

        public class ProviderIssueRow
        {
            public long PlatformProviderID { get; set; }
            public string LegalName { get; set; }
            public string BusinessRegNumber { get; set; }
            public string CertificateNumber { get; set; }
            public DateTime? IssuedAt { get; set; }
            public DateTime? ExpiresAt { get; set; }
            public string Status { get; set; }
        }

        public class AppProviderIssueSearchRow
        {
            public long AppProviderIssueDetailID { get; set; }
            public long ApplicationDetailID { get; set; }
            public string ApplicationNumber { get; set; }
            public string LegalName { get; set; }
            public string BusinessRegNumber { get; set; }
            public string CertificateNumber { get; set; }
            public DateTime? IssuedAt { get; set; }
            public DateTime? ExpiresAt { get; set; }
            public string ReferenceNumber { get; set; }
            public string CurrentStepTitle { get; set; }
            public long WorkflowTemplateStepID { get; set; }
            public string Status { get; set; }
        }
    }
}