using System.Data;
using Neo.Afx.ComponentModel;
using System.Linq;
using System.Collections.Generic;
using Neo.LegitimateLicences.Common;
using System;
using Neo.LegitimateLicences.Data.Models;

namespace Neo.LegitimateLicences.ApplicationRequest.Models.Database
{
    public class ApplicationDatabase : Database<ApplicationContext>
    {
        public class ProviderCertificateInfo
        {
            public long PlatformProviderID { get; set; }
            public string CertificateNumber { get; set; }
            public DateTime IssuedAt { get; set; }
            public DateTime ExpiresAt { get; set; }
            public long? ReissuedFromID { get; set; }
        }
        #region ApplicationDatabase
        public ApplicationDatabase()
            : base(new ApplicationContext())
        {

        }
        #endregion

        #region IssueProviderCertificate
        public ProviderCertificateInfo IssueProviderCertificate(long applicationDetailID, long userID)
        {
            var result = Context.Database.SqlQuery<ProviderCertificateInfo>(
                "EXEC App.Pr_IssueProviderCertificate @ApplicationDetailID, @UserID",
                new System.Data.SqlClient.SqlParameter("@ApplicationDetailID", applicationDetailID),
                new System.Data.SqlClient.SqlParameter("@UserID", userID)
            ).FirstOrDefault();

            return result ?? new ProviderCertificateInfo();
        }
        public ProviderCertificateInfo ReissueProviderCertificate(long applicationDetailID, string reason, long userID)
        {
            var result = Context.Database.SqlQuery<ProviderCertificateInfo>(
                "EXEC App.Pr_ReissueProviderCertificate @ApplicationDetailID, @Reason, @UserID",
                new System.Data.SqlClient.SqlParameter("@ApplicationDetailID", applicationDetailID),
                new System.Data.SqlClient.SqlParameter("@Reason", (object)reason ?? string.Empty),
                new System.Data.SqlClient.SqlParameter("@UserID", userID)
            ).FirstOrDefault();

            return result ?? new ProviderCertificateInfo();
        }
        #endregion
        #region GetApplicationRequestTypeDocuments
        /// <summary>
        /// Retrieve the list of documents required/optional for an application based
        /// on precheck results, application type and (optionally) primary service type.
        /// When <paramref name="primaryServiceTypeID"/> is supplied the underlying
        /// stored procedure will first apply any per-service-type rules and then
        /// fall back to generic rules; passing null preserves existing behaviour.
        /// </summary>
        public List<Pr_GetApplicationRequestTypeDocuments_Result> GetApplicationRequestTypeDocuments(
            long applicationPrecheckDetailID,
            short? applicationRequestTypeID,
            long workflowID,
            long? memberID,
            long userID,
            short? primaryServiceTypeID = null)
        {
            return Context.Pr_GetApplicationRequestTypeDocuments(
                applicationPrecheckDetailID,
                applicationRequestTypeID,
                workflowID,
                memberID,
                userID,
                primaryServiceTypeID
            ).ToList();
        }
        #endregion

        #region GetOperatingLicenceTransferSummary
        public List<Pr_ApplicationCreateGetTransferSummary_Result> GetOperatingLicenceTransferSummary(string tranferToIdNumber, string transferOperatingLicenceNumbers, long userID)
        {
            return Context.Pr_ApplicationCreateGetTransferSummary(tranferToIdNumber, transferOperatingLicenceNumbers, userID).ToList();
        } 
        #endregion

        #region GetApplicantApplicationsInProgress
        public List<Pr_ApplicationCreateGetApplicationsInProgress_Result> GetApplicantApplicationsInProgress(string idNumber, long userID)
        {
            return Context.Pr_ApplicationCreateGetApplicationsInProgress(idNumber, userID).ToList();
        } 
        #endregion

        #region CreateApplication
        public Vw_ApplicationDetails_Light CreateApplication(long applicationPrecheckDetailID, long userID, bool? isContractedService = null)
        {
            // Pr_ApplicationCreate populates routes/vehicles/services from source data and can
            // run well over the .NET 30 s default. Set on both DbContext and ObjectContext —
            // EF6 function imports don't reliably honor Database.CommandTimeout alone.
            const int createTimeoutSeconds = 300;
            Context.Database.CommandTimeout = createTimeoutSeconds;
            ((System.Data.Entity.Infrastructure.IObjectContextAdapter)Context).ObjectContext.CommandTimeout = createTimeoutSeconds;
            var result = Context.Pr_ApplicationCreate(applicationPrecheckDetailID, userID).FirstOrDefault();

            // Scholar "Is this a contracted service? = Yes" must NOT go to gazette.
            // Pr_ApplicationCreate has already calculated the gazetting rules, but the workflow
            // has not started yet — so flag the application as gazetting-bypassed and recompute
            // the rules so IsGazettingRequired reflects it before the workflow branches.
            // (Non-contracted scholar gazettes via the IsGazettingRequiredRule; no new column used.)
            if (isContractedService == true && result != null)
            {
                Context.Database.ExecuteSqlCommand(
                    "UPDATE App.ApplicationDetails SET IsGazettingBypassed = 1, UpdatedByID = @userID, UpdatedDate = GETDATE() WHERE ApplicationDetailID = @id",
                    new System.Data.SqlClient.SqlParameter("@id", result.ApplicationDetailID),
                    new System.Data.SqlClient.SqlParameter("@userID", userID));

                Context.Database.ExecuteSqlCommand(
                    "EXEC App.Pr_ApplicationRequiredRulesCalculate @id, @userID",
                    new System.Data.SqlClient.SqlParameter("@id", result.ApplicationDetailID),
                    new System.Data.SqlClient.SqlParameter("@userID", userID));
            }

            return result;
        }
        #endregion

        #region SyncWorkflowDescription
        public void SyncWorkflowDescription(long applicationDetailID, long userID)
        {
            Context.Database.ExecuteSqlCommand("EXEC App.Pr_ApplicationSyncWorkflowDescription @ApplicationDetailID, @UserID",
                new System.Data.SqlClient.SqlParameter("@ApplicationDetailID", applicationDetailID),
                new System.Data.SqlClient.SqlParameter("@UserID", userID));
        }
        #endregion

        #region Vehicle eNatis description columns
        /// <summary>
        /// Persists VehicleModelDescription / VehicleTypeDescription to
        /// App.ApplicationLicenceVehicleDetails. These columns were added by DBUp
        /// script 109 and are not yet in the .edmx, so EF can't save them through
        /// the normal Update flow. This is a stop-gap until the .edmx is regenerated.
        /// </summary>
        public void UpdateVehicleENatisDescriptions(long applicationLicenceID, string modelDescription, string typeDescription)
        {
            Context.Database.ExecuteSqlCommand(
                @"UPDATE App.ApplicationLicenceVehicleDetails
                  SET VehicleModelDescription = @ModelDesc,
                      VehicleTypeDescription = @TypeDesc
                  WHERE ApplicationLicenceID = @ApplicationLicenceID",
                new System.Data.SqlClient.SqlParameter("@ApplicationLicenceID", applicationLicenceID),
                new System.Data.SqlClient.SqlParameter("@ModelDesc", (object)modelDescription ?? DBNull.Value),
                new System.Data.SqlClient.SqlParameter("@TypeDesc", (object)typeDescription ?? DBNull.Value));
        }
        #endregion

        #region Conversion helper queries
        /// <summary>
        /// Find an existing, non-cancelled conversion application (type 63) that
        /// was created for a specific source application detail/licence pair.
        /// This queries ApplicationDetails directly to avoid additional view
        /// dependencies; callers can still access the reference number from
        /// ApplicationNumber on the returned entity.
        /// </summary>
        public ApplicationDetail GetExistingConversionApplication(
            long sourceApplicationDetailID,
            long sourceApplicationLicenceID)
        {
            var existing = Context.ApplicationDetails.FirstOrDefault(x =>
                x.ApplicationRequestTypeID == 63 &&
                x.CopiedFromApplicationDetailID == sourceApplicationDetailID &&
                x.CopiedFromApplicationLicenceID == sourceApplicationLicenceID &&
                x.IsCancelled == false);

            return existing;
        }
        #endregion

        #region GetConversionEligibleApplications
        /// <summary>
        /// Returns applications (one row per application+licence) eligible as source for
        /// Convert Metered Taxi to E-hailing (type 63). Rules: PRE office, not type 63, not cancelled,
        /// non-cancelled app licence, applicant ID match, verified licence status Issued (2) or
        /// Expiring within 60 days (3), at least one App.ApplicationLicenceServiceDetails row with
        /// ServiceTypeID 10 (metered taxi), none with 9 (E-hailing). Verified row: prefer
        /// <c>CurrentVerifiedLicenceID</c>; if missing or not in (2,3), fall back to latest
        /// <c>Verified.Licences</c> row matching <c>CleanOperatingLicenceNumber</c> (QA data often has NULL CurrentVerifiedLicenceID).
        /// <paramref name="userID"/> reserved for future row-level security parity with ApplicantSearch.
        /// </summary>
        public class ConversionEligibleItem
        {
            public long ApplicationDetailID { get; set; }
            public long ApplicationLicenceID { get; set; }
            public string ApplicationNumber { get; set; }
        }

        public List<ConversionEligibleItem> GetConversionEligibleApplications(string idOrBusinessRegistrationNumber, long userID)
        {
            if (string.IsNullOrWhiteSpace(idOrBusinessRegistrationNumber))
                return new List<ConversionEligibleItem>();

            var list = Context.Database.SqlQuery<ConversionEligibleItem>(
                @"SELECT ad.ApplicationDetailID, al.ApplicationLicenceID, ad.ApplicationNumber
                  FROM App.ApplicationDetails ad
                  INNER JOIN App.ApplicationApplicantDetails aad ON aad.ApplicationDetailID = ad.ApplicationDetailID
                  INNER JOIN App.ApplicationLicences al ON al.ApplicationDetailID = ad.ApplicationDetailID AND al.IsCancelled = 0
                  OUTER APPLY (
                      SELECT TOP (1) v.LicenceID, v.LicenceStatusID
                      FROM Verified.Licences v
                      WHERE v.LicenceID = al.CurrentVerifiedLicenceID
                        AND v.LicenceStatusID IN (2, 3)
                  ) AS vlPreferred
                  OUTER APPLY (
                      SELECT TOP (1) v.LicenceID, v.LicenceStatusID
                      FROM Verified.Licences v
                      WHERE vlPreferred.LicenceID IS NULL
                        AND al.CleanOperatingLicenceNumber IS NOT NULL
                        AND v.CleanOperatingLicenceNumber = al.CleanOperatingLicenceNumber
                        AND v.LicenceStatusID IN (2, 3)
                      ORDER BY v.LicenceID DESC
                  ) AS vlByOln
                  WHERE aad.IDNumberOrBusinessRegistrationNo = @p0
                    AND ad.OfficeLocationDivisionTypeID = @p1
                    AND ad.ApplicationRequestTypeID <> 63
                    AND ad.IsCancelled = 0
                    AND COALESCE(vlPreferred.LicenceID, vlByOln.LicenceID) IS NOT NULL
                    AND EXISTS (
                        SELECT 1 FROM App.ApplicationLicenceServiceDetails s
                        WHERE s.ApplicationLicenceID = al.ApplicationLicenceID AND s.ServiceTypeID = 10)
                    AND NOT EXISTS (
                        SELECT 1 FROM App.ApplicationLicenceServiceDetails s
                        WHERE s.ApplicationLicenceID = al.ApplicationLicenceID AND s.ServiceTypeID = 9)",
                new System.Data.SqlClient.SqlParameter("@p0", idOrBusinessRegistrationNumber.Trim()),
                new System.Data.SqlClient.SqlParameter("@p1", (short)DivisionTypeEnum.PRE)
            ).ToList();

            return list ?? new List<ConversionEligibleItem>();
        }

        /// <summary>
        /// True when <see cref="GetConversionEligibleApplications"/> would return at least one row (type 63 in ApplicantSearch).
        /// </summary>
        public bool HasConversionEligibleApplications(string idOrBusinessRegistrationNumber, long userID)
        {
            var list = GetConversionEligibleApplications(idOrBusinessRegistrationNumber, userID);
            return list != null && list.Count > 0;
        }
        #endregion

        #region CreateConversionApplication
        /// <summary>
        /// Create a dedicated conversion application (type 63) for a specific
        /// source application/licence and selected platform provider.
        /// This delegates to App.Pr_ConversionApplicationCreate, which is
        /// responsible for seeding ApplicationDetails and linking it back to
        /// the source via CopiedFromApplicationDetailID/CopiedFromApplicationLicenceID.
        /// </summary>
        public Vw_ApplicationDetails_Light CreateConversionApplication(
            long sourceApplicationDetailID,
            long sourceApplicationLicenceID,
            long platformProviderID,
            long userID)
        {
            var result = Context.Database.SqlQuery<Vw_ApplicationDetails_Light>(
                "EXEC App.Pr_ConversionApplicationCreate @SourceApplicationDetailID, @SourceApplicationLicenceID, @PlatformProviderID, @UserID",
                new System.Data.SqlClient.SqlParameter("@SourceApplicationDetailID", sourceApplicationDetailID),
                new System.Data.SqlClient.SqlParameter("@SourceApplicationLicenceID", sourceApplicationLicenceID),
                new System.Data.SqlClient.SqlParameter("@PlatformProviderID", platformProviderID),
                new System.Data.SqlClient.SqlParameter("@UserID", userID)
            ).FirstOrDefault();

            return result;
        }
        #endregion

        #region TAT_CreateApplication
        public Vw_ApplicationDetails_Light TAT_CreateApplication(long applicationPrecheckDetailID, long userID)
        {
            return Context.Pr_TAT_ApplicationCreate(applicationPrecheckDetailID, userID).FirstOrDefault();
        }
        #endregion

        #region GetApplicationForm
        public ApplicationDetail GetApplicationForm(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails
                            .Include("Applicant")
                            .Include("Proxy")
                            .Include("Contract")
                            .Include("PreviousLicenceHolder")
                            .Include("NPTR")
                            .Include("ApplicationTATDetail")
                            .Include("ApplicationTATDetail.AdjudicationDecision")
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            if (details.ApplicationTATDetail == null)
            {
                details.ApplicationTATDetail = new ApplicationTATDetail();
            }

            return details;
        }
        #endregion

        #region GetApplicationDetais
        public ApplicationDetail GetApplicationDetais(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails                           
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();
            return details;
        }
        #endregion

        #region GetApplicationTATDetails
        public ApplicationDetail GetApplicationTATDetails(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails                            
                            .Include("ApplicationTATDetail")
                            .Include("ApplicationTATDetail.AdjudicationDecision")
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            if (details.ApplicationTATDetail == null)
            {
                details.ApplicationTATDetail = new ApplicationTATDetail();
            }
            else
            {
                details.Adjudications = (from q in Context.ApplicationAdjudications
                               where q.ApplicationDetailID == applicationDetailId
                               && q.AdjudicationDetailID == details.AdjudicationDetailID
                               && q.IsActiveAdjudication
                               select q).ToList();
            }

            return details;
        }
        #endregion

        #region GetApplicationApplicant
        public ApplicationApplicantDetail GetApplicationApplicant(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationApplicantDetails                           
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            return details;
        }
        #endregion

        #region GetApplicationCreateMemberLicences
        public List<Pr_ApplicationCreateGetMemberLicences_Result> GetApplicationCreateMemberLicences(string idNumber, long userID)
        {            
            return Context.Pr_ApplicationCreateGetMemberLicences(idNumber, userID).ToList();
        }
        #endregion

        #region GetApplicationCreateMemberAccreditations
        public List<Pr_ApplicationCreateGetMemberAccreditation_Result> GetApplicationCreateMemberAccreditations(long memberID, long userID)
        {            
            return Context.Pr_ApplicationCreateGetMemberAccreditation(memberID, userID).ToList();
        }
        #endregion

        #region GetApplicationCreateMemberAllowedNewSummary
        public List<Pr_ApplicationCreateGetMemberAllowedNewSummary_Result> GetApplicationCreateMemberAllowedNewSummary(string idNumberOrBusinessRegistrationNumber, long userID)
        {            
            return Context.Pr_ApplicationCreateGetMemberAllowedNewSummary(idNumberOrBusinessRegistrationNumber, userID).ToList();
        }
        #endregion

        #region GetApplicationLicences
        public ApplicationDetail GetApplicationLicences(long applicationDetailId)
        {
            var details = (from q in Context.ApplicationDetails
                            .Include("Applicant")
                            .Include("Licences")                 
                            .Include("Licences.Routes")
                            .Include("Licences.Services")
                            .Include("Licences.Services.ServiceType")
                            .Include("Licences.CurrentVehicle")
                            .Include("Licences.NewVehicle")
                            .Include("Licences.PreviousVehicle")
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            if(details == null)
            {
                details = new ApplicationDetail();
            }

            return details;
        }
        #endregion

        #region GetAdjudicationApplicationLicences
        public ApplicationDetail GetAdjudicationApplicationLicences(long applicationDetailId, long? applicationLicenceID, long userID)
        {
            var details = (from q in Context.ApplicationDetails
                            .Include("Applicant")
                            .Include("Proxy")
                            .Include("PreviousLicenceHolder")
                            .Include("NPTR")
                            .Include("Objections")
                            .Include("Referrals")
                            .Include("Referrals.ReferralType")
                            .Include("Receipts")
                            .Include("Receipts.PaymentType")
                            .Include("InspectionDetails")
                            .Include("Adjudications")
                            .Include("Adjudications.Decisions")
                            .Include("Licences")
                            .Include("Licences.ApplicationObjections")
                            .Include("Licences.Routes")
                            .Include("Licences.Services")
                            .Include("Licences.Services.ServiceType")
                            .Include("Licences.CurrentVehicle")
                            .Include("Licences.NewVehicle")
                            .Include("Licences.PreviousVehicle")
                            .Include("ApplicationTATDetail")
                            .Include("ApplicationTATDetail.AdjudicationDecision")
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            #region Cleanup
            if (applicationLicenceID == null)
            {
                if (details.Objections == null)
                {
                    details.Objections = new List<ApplicationObjection>();
                }

                if (details.Referrals == null)
                {
                    details.Referrals = new List<ApplicationReferral>();
                }
            } else {
                details.Objections = Context.ApplicationObjections.Where(x => x.ApplicationLicenceID == applicationLicenceID).ToList();
                details.Referrals = Context.ApplicationReferrals.Where(x => x.ApplicationLicenceID == applicationLicenceID).ToList();
            }

            if (details.NPTR == null)
            {
                details.NPTR = new ApplicationNPTRDetail();
            }

            if (details.Receipts == null)
            {
                details.Receipts = new List<ReceiptDetail>();
            }

            if (details.InspectionDetails == null)
            {
                details.InspectionDetails = new ApplicationInspectionDetail();
            }

            if (details.Adjudications == null)
            {
                details.Adjudications = new List<ApplicationAdjudication>();
            }

            foreach(var adjudication in details.Adjudications)
            {
                if(adjudication.Decisions == null)
                {
                    adjudication.Decisions = new List<ApplicationAdjudicationDecision>();
                }
            }

            if (details.Adjudications == null)
            {
                details.Adjudications = new List<ApplicationAdjudication>();
            }

            foreach (var license in details.Licences)
            {
                if (license.ApplicationObjections == null)
                {
                    license.ApplicationObjections = new List<ApplicationObjection>();
                }

                if (license.ApplicationReferrals == null)
                {
                    license.ApplicationReferrals = new List<ApplicationReferral>();
                }
            }

            if (details.ApplicationTATDetail == null)
            {
                details.ApplicationTATDetail = new ApplicationTATDetail
                {
                    AdjudicationDecision = new AdjudicationDecision()
                };
            }

            if(details.ApplicationTATDetail.AdjudicationDecision == null)
            {
                details.ApplicationTATDetail.AdjudicationDecision = new AdjudicationDecision();
            }
            #endregion

            return details;
        }
        #endregion

        #region GetPaymentDetails
        public List<ReceiptDetail> GetPaymentDetails(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ReceiptDetails
                            .Include("PaymentType")
                           where q.ApplicationDetailID == applicationDetailId
                           select q).ToList();

            if (details == null)
            {
                details = new List<ReceiptDetail>();
            }

            return details;
        }
        #endregion

        #region GetApplicationLicencesView
        public Vw_ApplicationLicences GetApplicationLicencesView(long ApplicationLicenceID, long userID)
        {
            var details = (from q in Context.Vw_ApplicationLicences
                           where q.ApplicationLicenceID == ApplicationLicenceID
                           select q).FirstOrDefault();

            if (details == null)
            {
                details = new Vw_ApplicationLicences();
            }

            return details;
        }

        public Vw_ApplicationLicences GetLicenceIssueDetailsView(long licenceIssueDetailID, long userID)
        {
            var licenseIssueDetails = (from q in Context.LicenceIssueDetails
                           where q.LicenceIssueDetailID == licenceIssueDetailID
                           select q).FirstOrDefault();

            var details = (from q in Context.Vw_ApplicationLicences
                           where q.ApplicationLicenceID == licenseIssueDetails.ApplicationLicenceID
                           select q).FirstOrDefault();

            if (details == null)
            {
                details = new Vw_ApplicationLicences();
            }

            return details;
        }
        #endregion 

        #region GetApplicationLicenceVehicleDetailsView
        public Vw_ApplicationLicenceVehicleDetails GetLicenceIssueVehicleDetailsView(long applicationLicenceID, long userID)
        {
            Vw_ApplicationLicenceVehicleDetails details = new Vw_ApplicationLicenceVehicleDetails();

            var licenseIssueVehicleDetails = (from q in Context.Vw_LicenceIssueVehicleDetails
                           where q.ApplicationLicenceID == applicationLicenceID
                           select q).FirstOrDefault();

            if (licenseIssueVehicleDetails != null)
            {
                details.ApplicationLicenceID = licenseIssueVehicleDetails.ApplicationLicenceID == null ? 0 : (long)licenseIssueVehicleDetails.ApplicationLicenceID;
                details.CarryingCapacity = licenseIssueVehicleDetails.CarryingCapacity;
                details.CurrentOdometerReading = licenseIssueVehicleDetails.CurrentOdometerReading;
                details.EngineNumber = licenseIssueVehicleDetails.EngineNumber;
                details.GrossMass = licenseIssueVehicleDetails.GrossMass;
                details.SeatedCapacity = (short?)(licenseIssueVehicleDetails.SeatedCapacity == null ? 0 : short.Parse(licenseIssueVehicleDetails.SeatedCapacity.ToString()));
                details.StandingCapacity = (short?)(licenseIssueVehicleDetails.StandingCapacity == null ? 0 : short.Parse(licenseIssueVehicleDetails.StandingCapacity.ToString()));
                details.Tare = licenseIssueVehicleDetails.Tare;
                details.VehicleMake = licenseIssueVehicleDetails.VehicleMake;
                details.VehicleMakeDescription = licenseIssueVehicleDetails.VehicleMakeDescription;
                details.VehicleModel = licenseIssueVehicleDetails.VehicleModel;
                details.VehicleRegistrationNumber = licenseIssueVehicleDetails.VehicleRegistrationNumber;
                details.VehicleType = licenseIssueVehicleDetails.VehicleType;
                details.VIN = licenseIssueVehicleDetails.VIN;
                details.YearOfManufacture = licenseIssueVehicleDetails.YearOfManufacture;
            }

            return details;
        }

        public Vw_ApplicationLicenceVehicleDetails GetApplicationLicenceVehicleDetailsView(long ApplicationLicenceID, long userID)
        {
            var details = (from q in Context.Vw_ApplicationLicenceVehicleDetails
                           where q.ApplicationLicenceID == ApplicationLicenceID
                           select q).FirstOrDefault();

            if (details == null)
            {
                details = new Vw_ApplicationLicenceVehicleDetails();
            }

            return details;
        }
        #endregion 

        #region GetApplicationInspectionDetails
        public ApplicationDetail GetApplicationInspectionDetails(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails
                            .Include("InspectionDetails")                            
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            if (details.InspectionDetails == null)
            {
                details.InspectionDetails = new ApplicationInspectionDetail();
            }

            return details;
        }
        #endregion

        #region GetApplicationReferrals
        public ApplicationDetail GetApplicationReferrals(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails 
                            .Include("Referrals")
                            .Include("Referrals.ApplicationLicence")
                            .Include("Referrals.ReferralType") 
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            if (details.Referrals == null)
            {
                details.Referrals = new List<ApplicationReferral>();
            } else
            {
                details.Referrals = details.Referrals.Where(a => !a.IsCancelled).ToList();
            }

            return details;
        }
        #endregion

        #region GetApplicationObjections
        public ApplicationDetail GetApplicationObjections(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails
                            .Include("Objections")
                            .Include("Objections.ApplicationLicence")
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            if (details.Objections == null)
            {
                details.Objections = new List<ApplicationObjection>();
            }
            else
            {
                details.Objections = details.Objections.Where(a => !a.IsCancelled).ToList();
            }            

            return details;
        }
        #endregion

        #region GetLicence
        public ApplicationLicence GetLicence(long applicationLicenceID, long userID)
        {
            var details = (from q in Context.ApplicationLicences
                           where q.ApplicationLicenceID == applicationLicenceID
                           select q).FirstOrDefault();

            if (details == null)
            {
                details = new ApplicationLicence();
            }

            return details;
        }
        #endregion

        #region GetLicenceVehicle
        public ApplicationLicenceVehicleDetail GetLicenceVehicle(long applicationLicenceID, long userID)
        {
            var details = (from q in Context.ApplicationLicenceVehicleDetails
                           where q.ApplicationLicenceID == applicationLicenceID
                           select q).FirstOrDefault();

            if (details == null)
            {
                details = new ApplicationLicenceVehicleDetail();
            }
            else
            {
                // VehicleModelDescription / VehicleTypeDescription were added by DBUp
                // script 109 but aren't in the .edmx yet, so EF doesn't hydrate them.
                // Stamp them onto the entity with a direct SELECT. Mirror of the
                // raw-SQL save in UpdateVehicleENatisDescriptions.
                var descriptions = Context.Database.SqlQuery<VehicleDescriptionRow>(
                    @"SELECT VehicleModelDescription, VehicleTypeDescription
                      FROM App.ApplicationLicenceVehicleDetails
                      WHERE ApplicationLicenceID = @ApplicationLicenceID",
                    new System.Data.SqlClient.SqlParameter("@ApplicationLicenceID", applicationLicenceID))
                    .FirstOrDefault();

                if (descriptions != null)
                {
                    details.VehicleModelDescription = descriptions.VehicleModelDescription;
                    details.VehicleTypeDescription = descriptions.VehicleTypeDescription;
                }
            }

            return details;
        }

        private class VehicleDescriptionRow
        {
            public string VehicleModelDescription { get; set; }
            public string VehicleTypeDescription { get; set; }
        }
        #endregion

        #region GetLicencePreviousVehicle
        public ApplicationLicencePreviousVehicleDetail GetLicencePreviousVehicle(long applicationLicenceID, long userID)
        {
            var details = (from q in Context.ApplicationLicencePreviousVehicleDetails
                           where q.ApplicationLicenceID == applicationLicenceID
                           select q).FirstOrDefault();

            if (details == null)
            {
                details = new ApplicationLicencePreviousVehicleDetail();
            }

            return details;
        }
        #endregion

        #region GetLicenceServiceDetails
        public ApplicationLicence GetLicenceServiceDetails(long applicationLicenceID, long userID)
        {
            var details = (from q in Context.ApplicationLicences
                           .Include("Services")
                           .Include("Services.ServiceType")
                           where q.ApplicationLicenceID == applicationLicenceID
                           select q).FirstOrDefault();

            details.RemovedServices = new List<ApplicationLicenceServiceDetail>();
            
            return details;
        }
        #endregion

        #region GetApplicationView
        public Vw_ApplicationDetails_Light GetApplicationView(long applicationDetailId, long userID)
        {
            var details = (from q in Context.Vw_ApplicationDetails_Light
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            return details;
        }
        #endregion

        #region AddApplicationLicenceRoute_Existing
        public ApplicationLicenceRouteDetail AddApplicationLicenceRoute_Existing(long applicationLicenceID, long routeID, short serviceTypeID, long userID)
        {
            return Context.Pr_ApplicationLicenceRoute_AddExisting(applicationLicenceID, routeID, serviceTypeID, userID).FirstOrDefault();
        }
        #endregion

        #region GetApplicationWorkflowStatusSummary
        public List<Pr_Dashboard_ApplicationWorkflowStatusGet_Result> GetApplicationWorkflowStatusSummary(long applicationDetailId, long userID)
        {            
            return Context.Pr_Dashboard_ApplicationWorkflowStatusGet(applicationDetailId, userID).ToList();
        }
        #endregion

        #region GetApplicationAdjudicationDetails
        public ApplicationDetail GetApplicationAdjudicationDetails(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationDetails
                            .Include("Adjudications")
                            .Include("Adjudications.AdjudicationDetail")           
                            .Include("Adjudications.AdjudicationDetail.AdjudicationStatus")
                            .Include("Adjudications.Decisions")                            
                            .Include("Adjudications.Decisions.Decision")
                            .Include("Adjudications.Decisions.AdjudicatedBy")
                            .Include("Adjudications.Decisions.LicenceDuration")                       
                           where q.ApplicationDetailID == applicationDetailId
                           select q).FirstOrDefault();

            if (details == null)
            {
                details = new ApplicationDetail();
                details.Adjudications = new List<ApplicationAdjudication>();
            }

            return details;
        }
        #endregion

        #region GetApplicationLicenceAdjudicationDetails
        public List<ApplicationAdjudication> GetApplicationLicenceAdjudicationDetails(long applicationDetailId, long applicationLicenceID, long userID)
        {
            var details = (from q in Context.ApplicationAdjudications
                            .Include("AdjudicationDetail")
                            .Include("AdjudicationDetail.AdjudicationStatus")
                            .Include("Decisions")
                            .Include("Decisions.Decision")
                            .Include("Decisions.AdjudicatedBy")
                            .Include("Decisions.LicenceDuration")
                           where q.ApplicationDetailID == applicationDetailId
                           && q.ApplicationLicenceID == applicationLicenceID
                           select q).ToList();

            if (details == null)
            {
                details = new List<ApplicationAdjudication>();                
            }
            else
            {
                foreach(var applicationAdjudication in details)
                {

                    if(applicationAdjudication.AdjudicationDetail == null)
                    {
                        applicationAdjudication.AdjudicationDetail = new AdjudicationDetail();
                    }

                    if (applicationAdjudication.Decisions == null)
                    {
                        applicationAdjudication.Decisions = new List<ApplicationAdjudicationDecision>();
                    }
                    else
                    {
                        foreach(var decision in applicationAdjudication.Decisions)
                        {
                            if (decision.Decision == null)
                            {
                                decision.Decision = new AdjudicationDecision();
                            }

                            if (decision.AdjudicatedBy == null)
                            {
                                decision.AdjudicatedBy = new User();
                            }

                            if (decision.LicenceDuration == null)
                            {
                                decision.LicenceDuration = new LicenceDuration();
                            }
                        }
                    }
                }
            }

            return details;
        }

        public List<ApplicationAdjudication> GetApplicationLicenceAdjudicationDetails(long applicationDetailId, long userID)
        {
            var details = (from q in Context.ApplicationAdjudications
                            .Include("AdjudicationDetail")
                            .Include("AdjudicationDetail.AdjudicationStatus")
                            .Include("Decisions")
                            .Include("Decisions.Decision")
                            .Include("Decisions.AdjudicatedBy")
                            .Include("Decisions.LicenceDuration")
                           where q.ApplicationDetailID == applicationDetailId
                           && q.ApplicationLicenceID == null
                           select q).ToList();

            if (details == null)
            {
                details = new List<ApplicationAdjudication>();
            }
            else
            {
                foreach (var applicationAdjudication in details)
                {

                    if (applicationAdjudication.AdjudicationDetail == null)
                    {
                        applicationAdjudication.AdjudicationDetail = new AdjudicationDetail();
                    }

                    if (applicationAdjudication.Decisions == null)
                    {
                        applicationAdjudication.Decisions = new List<ApplicationAdjudicationDecision>();
                    }
                    else
                    {
                        foreach (var decision in applicationAdjudication.Decisions)
                        {
                            if (decision.Decision == null)
                            {
                                decision.Decision = new AdjudicationDecision();
                            }

                            if (decision.AdjudicatedBy == null)
                            {
                                decision.AdjudicatedBy = new User();
                            }

                            if (decision.LicenceDuration == null)
                            {
                                decision.LicenceDuration = new LicenceDuration();
                            }
                        }
                    }
                }
            }

            return details;
        }

        /// <summary>
        /// Resolves ApplicationDetailID / ApplicationLicenceID for an App.ApplicationAdjudications row (used for TAT → PRE parent linkage via ApplicationTATDetails.ApplicationAdjudicationID).
        /// </summary>
        public bool TryGetApplicationAdjudicationKeys(long applicationAdjudicationId, out long applicationDetailId, out long? applicationLicenceId)
        {
            applicationDetailId = 0;
            applicationLicenceId = null;
            var row = Context.ApplicationAdjudications
                .Where(a => a.ApplicationAdjudicationID == applicationAdjudicationId)
                .Select(a => new { a.ApplicationDetailID, a.ApplicationLicenceID })
                .FirstOrDefault();
            if (row == null)
            {
                return false;
            }

            applicationDetailId = row.ApplicationDetailID;
            applicationLicenceId = row.ApplicationLicenceID;
            return true;
        }

        /// <summary>
        /// Same as GetApplicationLicenceAdjudicationDetails overloads, plus FinalDecision for displaying PRE recorded resolution on TAT appeal adjudication screens (Change 8).
        /// </summary>
        public List<ApplicationAdjudication> GetApplicationLicenceAdjudicationDetailsWithFinalDecision(long applicationDetailId, long? applicationLicenceId, long userID)
        {
            var query = Context.ApplicationAdjudications
                .Include("AdjudicationDetail")
                .Include("AdjudicationDetail.AdjudicationStatus")
                .Include("Decisions")
                .Include("Decisions.Decision")
                .Include("Decisions.AdjudicatedBy")
                .Include("Decisions.LicenceDuration")
                .Include("FinalDecision")
                .Where(a => a.ApplicationDetailID == applicationDetailId);

            query = applicationLicenceId.HasValue
                ? query.Where(a => a.ApplicationLicenceID == applicationLicenceId.Value)
                : query.Where(a => a.ApplicationLicenceID == null);

            var details = query.ToList();
            if (details == null)
            {
                return new List<ApplicationAdjudication>();
            }

            foreach (var applicationAdjudication in details)
            {
                if (applicationAdjudication.AdjudicationDetail == null)
                {
                    applicationAdjudication.AdjudicationDetail = new AdjudicationDetail();
                }

                if (applicationAdjudication.FinalDecision == null)
                {
                    applicationAdjudication.FinalDecision = new AdjudicationDecision();
                }

                if (applicationAdjudication.Decisions == null)
                {
                    applicationAdjudication.Decisions = new List<ApplicationAdjudicationDecision>();
                }
                else
                {
                    foreach (var decision in applicationAdjudication.Decisions)
                    {
                        if (decision.Decision == null)
                        {
                            decision.Decision = new AdjudicationDecision();
                        }

                        if (decision.AdjudicatedBy == null)
                        {
                            decision.AdjudicatedBy = new User();
                        }

                        if (decision.LicenceDuration == null)
                        {
                            decision.LicenceDuration = new LicenceDuration();
                        }
                    }
                }
            }

            return details;
        }
        #endregion 

        #region GetApplicationReceipts
        public List<Pr_ApplicationReceiptsGet_Result> GetApplicationReceipts(long applicationDetailId, long userID)
        {
            var result = Context.Pr_ApplicationReceiptsGet(applicationDetailId, userID).ToList();
            if (result == null)
            {
                result = new List<Pr_ApplicationReceiptsGet_Result>();
            }
            return result;
        }
        #endregion

        #region GetApplicationReceipt
        public Pr_ApplicationReceiptsGet_Result GetApplicationReceipt(long applicationDetailId, long receiptDetailID, long userID)
        {            
            return GetApplicationReceipts(applicationDetailId, userID).Where(a => a.ReceiptDetailID == receiptDetailID).FirstOrDefault();
        }
        #endregion

        #region GetApplicationRequesTypes
        public List<ApplicationRequestType> GetApplicationRequesTypes()
        {            
            return Context.ApplicationRequestTypes.Where(a => a.IsCancelled != true).ToList();
        }
        #endregion
        
        #region GetApplicationLicenceRouteDetail
        public ApplicationLicenceRouteDetail GetApplicationLicenceRouteDetail(long applicationLicenceRouteDetailID, long applicationLicenceID, short serviceTypeID, long userID)
        {            
            var details = (from q in Context.ApplicationLicenceRouteDetails
                           .Include("ApplicationLicenceRouteDetailPlaces")
                           where q.ApplicationLicenceRouteDetailID == applicationLicenceRouteDetailID
                           select q).FirstOrDefault();

            #region Cleanup
            if(details == null || details.ApplicationLicenceRouteDetailID <= 0)
            {
                details = new ApplicationLicenceRouteDetail();
                details.ApplicationLicenceRouteDetailPlaces = new List<ApplicationLicenceRouteDetailPlace>();
                details.ApplicationLicenceID = applicationLicenceID;
                details.ApplicationRouteDetailStatusID = (short)ApplicationRouteDetailStatusEnum.New;
                details.RouteOrigin = "";
                details.RouteDestination = "";
                details.RouteDescription = "";
                details.IsOriginSameAsPhysicalAddress = true;
                details.IsDestinationArea = true;
                details.RouteChangeTypeID = (short)RouteChangeTypeEnum.AddBrandNewRouteToLicence;
                details.ServiceTypeID = serviceTypeID;
            }            
            else
            {
                if (details.ApplicationLicenceRouteDetailPlaces == null)
                {
                    details.ApplicationLicenceRouteDetailPlaces = new List<ApplicationLicenceRouteDetailPlace>();
                }
            }

            details.ExistingApplicationLicenceRouteDetailPlaces = details.ApplicationLicenceRouteDetailPlaces.ToList();
            #endregion

            return details;
        }
        #endregion

        #region GetApplicationLicenceRouteDetail
        public List<ApplicationLicenceRouteDetail> GetApplicationLicenceRouteDetails(long applicationLicenceID, long userID)
        {
            var applicationLicenceRouteDetail = (from q in Context.ApplicationLicenceRouteDetails
                           .Include("ApplicationLicenceRouteDetailPlaces")
                           where q.ApplicationLicenceID == applicationLicenceID
                           select q).ToList();

            #region Cleanup
            if (applicationLicenceRouteDetail == null)
            {
                applicationLicenceRouteDetail = new List<ApplicationLicenceRouteDetail>();

            }
            #endregion

            return applicationLicenceRouteDetail;
        }
        #endregion

        #region GetApplicationLicencesRouteDetails
        public List<ApplicationLicenceRouteDetail> GetApplicationLicencesRouteDetails(long applicationDetailId, long userID)
        {
            List <ApplicationLicenceRouteDetail> applicationLicenceRouteDetail = new List<ApplicationLicenceRouteDetail>();

            var application = (from q in Context.ApplicationDetails
                           .Include("Licences")                            
                            .Include("Licences.Routes")
                            .Include("Licences.Routes.ApplicationLicenceRouteDetailPlaces")
                               where q.ApplicationDetailID == applicationDetailId
                                                 select q).FirstOrDefault();

            #region Populate applicationLicenceRouteDetail
            if (application != null)
            {
                foreach(var licence in application.Licences)
                {
                    foreach(var route in licence.Routes)
                    {
                        applicationLicenceRouteDetail.Add(route);
                    }
                }
            }
            #endregion

            return applicationLicenceRouteDetail;
        }
        #endregion

        #region GetApplicationLicenceRouteDetails
        public string CalculateApplicationLicenceRouteDetails(long applicationLicenceRouteDetailID, long userID)
        {
            return Context.Pr_ApplicationLicenceRouteDescriptionCalculate(applicationLicenceRouteDetailID, userID).FirstOrDefault();
        }
        #endregion

        #region CalculateApplicationLicenceRadiusRouteDetails
        public string CalculateApplicationLicenceRadiusRouteDetails(long applicationLicenceRouteDetailID, long userID)
        {
            return Context.Pr_ApplicationLicenceRadiusRouteDescriptionCalculate(applicationLicenceRouteDetailID, userID).FirstOrDefault();
        }
        #endregion

        #region GetFailedApplicationDocumentVerifications
        public List<Pr_ApplicationFailedDocumentVerificationsGet_Result> GetFailedApplicationDocumentVerifications(long applicationDetailID, long userID)
        {
            var returnValue = Context.Pr_ApplicationFailedDocumentVerificationsGet(applicationDetailID, userID).ToList();

            if (returnValue == null)
            {
                returnValue = new List<Pr_ApplicationFailedDocumentVerificationsGet_Result>();
            }

            return returnValue;
        }
        #endregion

        #region GetApplicationReferral
        public ApplicationReferral GetApplicationReferral(long applicationReferralDetailID, long applicationDetailID, long userID)
        {
            var applicationReferral = (from q in Context.ApplicationReferrals
                                                 where q.ApplicationReferralDetailID == applicationReferralDetailID
                                                 && q.ApplicationDetailID == applicationDetailID
                                                 && q.IsCancelled == false
                                       select q).FirstOrDefault();

            if (applicationReferral == null)
            {
                applicationReferral = new ApplicationReferral();
                applicationReferral.ApplicationDetailID = applicationDetailID;
            }

            return applicationReferral;
        }

        public ReferralType GetReferralType(short referralTypeID)
        {
            var referralType = (from q in Context.ReferralTypes
                                       where q.ReferralTypeID == referralTypeID
                                       && q.IsCancelled == false
                                       select q).FirstOrDefault();            

            return referralType;
        }
        #endregion

        #region GetApplicationObjectionl
        public ApplicationObjection GetApplicationObjection(long applicationObjectionID, long applicationDetailID, long userID)
        {
            var applicationObjection = (from q in Context.ApplicationObjections
                                       where q.ApplicationObjectionID == applicationObjectionID
                                       && q.ApplicationDetailID == applicationDetailID
                                       && q.IsCancelled == false
                                       select q).FirstOrDefault();

            if (applicationObjection == null)
            {
                applicationObjection = new ApplicationObjection();
                applicationObjection.ApplicationDetailID = applicationDetailID;
                applicationObjection.ObjectionDate = DateTime.Now;
            }

            return applicationObjection;
        }        
        #endregion

        #region Remove Licence Route
        public string RemoveLicenceRoute(long applicationLicenceID, long applicationLicenceRouteDetailID, long userID)
        {            
            return Context.Pr_ApplicationLicenceRoute_RemoveRoute(applicationLicenceID, applicationLicenceRouteDetailID, userID).FirstOrDefault();
        }
        #endregion

        #region CreateApplicationPrecheck
        public List<Pr_ApplicationPrecheckCreate_Result> CreateApplicationPrecheck(short applicationRequestTypeID, short sourceApplicationID, string applicantName, string applicantCell, short idTypeID, string idOrBusinessRegistrationNumber, string originatingLicenceNumbers, string originatingAccreditationNumber, short? vehicleTypeID, string serviceTypeIDs, short? numberOfLicencesRequired, short? applicationProvinceID, short? applicationRegionID, long? applicationAssociationID, string applicationAllowedNewLicenceIDs, string selectedRouteIDs, long? parentApplicationDetailID, long? parentApplicationLicenceID, long userID)
        {
            // Precheck proc currently takes ~70 s for licences with many routes. EF6 function imports
            // don't always pick up DbContext.Database.CommandTimeout, so we also set it on the
            // underlying ObjectContext to guarantee the value is honored.
            const int precheckTimeoutSeconds = 300;
            Context.Database.CommandTimeout = precheckTimeoutSeconds;
            ((System.Data.Entity.Infrastructure.IObjectContextAdapter)Context).ObjectContext.CommandTimeout = precheckTimeoutSeconds;
            return Context.Pr_ApplicationPrecheckCreate(applicationRequestTypeID, sourceApplicationID, applicantName, applicantCell, idTypeID, idOrBusinessRegistrationNumber, originatingLicenceNumbers, originatingAccreditationNumber, vehicleTypeID, serviceTypeIDs, numberOfLicencesRequired, applicationProvinceID, applicationRegionID, applicationAssociationID, applicationAllowedNewLicenceIDs, selectedRouteIDs, parentApplicationDetailID, parentApplicationLicenceID, userID).ToList();
        }
        #endregion

        #region UpdateApplicationLicenceDetails
        public void UpdateApplicationLicenceDetails(long applicationLicenceID, DateTime? specialLicenceDateFrom, DateTime? specialLicenceDateTo, string specialLicenceDetails, string reasonForAmendment, long userID)
        {            
            Context.Pr_ApplicationLicenceDetailUpdate(applicationLicenceID, specialLicenceDateFrom, specialLicenceDateTo, specialLicenceDetails, reasonForAmendment, userID);
        }
        #endregion

        #region UpdateApplicationLicenceDetails
        public void UpdateApplicationLicenceRouteDetails(long applicationLicenceID, long applicationLicenceRouteDetailID, string routeOrigin, string routeDestination, string routeDescription, long userID)
        {
            Context.Pr_ApplicationLicenceRouteDetailUpdate(applicationLicenceID, applicationLicenceRouteDetailID, routeOrigin, routeDestination, routeDescription, userID);
        }
        #endregion

        #region SearchMemberAssociation
        public List<Pr_MemberAssociationSearch_Result> SearchMemberAssociation(long memberID, long userID)
        {
            return Context.Pr_MemberAssociationSearch(memberID, userID).ToList();
        }
        #endregion

        #region GetApplicationTags
        public List<Vw_ApplicationTags> GetApplicationTags(long applicationDetailID, long userID)
        {
            return Context.Vw_ApplicationTags.Where(a => a.ApplicationDetailID == applicationDetailID).ToList();
        }
        #endregion

        #region ApplicationTagCreate
        public List<Vw_ApplicationTags> ApplicationTagCreate(long applicationDetailID, short tagID, bool isUserTag, bool allowDuplicate, long userID)
        {
            Context.Pr_ApplicationTagCreate(applicationDetailID, tagID, isUserTag, allowDuplicate, userID);

            return Context.Vw_ApplicationTags.Where(a => a.ApplicationDetailID == applicationDetailID).ToList();
        }
        #endregion

        #region ApplicationTagRemove
        public List<Vw_ApplicationTags> ApplicationTagRemove(long applicationDetailID, short tagID, long userID)
        {
            Context.Pr_ApplicationTagRemove(applicationDetailID, tagID, userID);

            return Context.Vw_ApplicationTags.Where(a => a.ApplicationDetailID == applicationDetailID).ToList();
        }
        #endregion

        #region ApplicationTagClear
        public void ApplicationTagClear(long applicationTagID, long applicationDetailID, bool isTagCleared, string comments, long userID)
        {
            Context.Pr_ApplicationTagClear(applicationTagID, applicationDetailID, isTagCleared, comments, userID);           
        }
        #endregion

        #region ReceiptApplicationPaymentTypeUpdate
        public void ReceiptApplicationPaymentTypeUpdate(long applicationDetailID, long receiptDetailID, short paymentTypeID, long userID)
        {
            Context.Pr_ReceiptApplicationPaymentTypeUpdate(applicationDetailID, receiptDetailID, paymentTypeID, userID);
        }
        #endregion

        #region ReceiptApplicationPaymentTypeUpdate
        public void ReceiptApplicationProofOfPaymentRefNoUpdate(long applicationDetailID, long receiptDetailID, string proofOfPaymentRefNo, long userID)
        {
            Context.Pr_ReceiptApplicationProofOfPaymentRefNoUpdate(applicationDetailID, receiptDetailID, proofOfPaymentRefNo, userID);
        }
        #endregion

        #region GetApplicantUnassignedReceipt
        public List<ReceiptDetail> GetApplicantUnassignedReceipt(long applicationDetailID, long userID)
        {
            return Context.Pr_ApplicantUnassignedReceiptGet(applicationDetailID, userID).ToList();
        }
        #endregion

        #region AssignReceiptToApplication
        public void AssignReceiptToApplication(long receiptDetailID, long applicationDetailID, long userID)
        {
            Context.Pr_ReceiptAddToApplication(receiptDetailID, applicationDetailID, userID);
        }
        #endregion

        #region TATPrecheckAdjudicationSearch
        public List<Pr_TAT_AdjudicationPrecheckSearch_Result> TATPrecheckAdjudicationSearch(string applicantIDNumber, string applicantName, string olPermitNumber, string applicationLicenceReferenceNumber, long userID)
        {
            return Context.Pr_TAT_AdjudicationPrecheckSearch(applicantIDNumber, applicantName, olPermitNumber, applicationLicenceReferenceNumber, userID).ToList();
        }
        #endregion
        
        #region ApplicationCreateGetAssociationRoutes
        public List<Pr_ApplicationCreateGetAssociationRoutes_Result> ApplicationCreateGetAssociationRoutes(long associationID, long userID)
        {
            return Context.Pr_ApplicationCreateGetAssociationRoutes(associationID, userID).ToList();
        }
        #endregion

        #region GazetteApplicationsGet
        public List<Pr_ApplicationGazetteGet_Result> GazetteApplicationsGet(long applicationDetailID, long userID)
        {
            return Context.Pr_ApplicationGazetteGet(applicationDetailID, userID).ToList();
        }
        #endregion

        #region GetApplicationRequestNewRouteRequests
        public List<Pr_ApplicationRequestNewRouteRequestsGet_Result> GetApplicationRequestNewRouteRequests(long applicationDetailId, long userID)
        {
            return Context.Pr_ApplicationRequestNewRouteRequestsGet(applicationDetailId, userID).ToList();
        }
        #endregion

        #region DocumentPrecheckReceivedUpdate
        public bool DocumentPrecheckReceivedUpdate(long documentsPrecheckID, bool received)
        {
            Context.Pr_DocumentsPrecheckUpdate(documentsPrecheckID, received);

            return true;
        }
        #endregion

        #region PopulateTatParentServiceVehicleDisplay
        /// <summary>
        /// Read-only labels for parent PRE service and vehicle (TAT appeal — Change 3).
        /// </summary>
        public void PopulateTatParentServiceVehicleDisplay(ApplicationRequestBaseModel model, ApplicationDetail application)
        {
            model.TatParentServiceTypeDisplay = null;
            model.TatParentVehicleTypeDisplay = null;
            if (application == null || application.ApplicationRequestTypeID != (short)ApplicationRequestTypeEnum.NewTATRequest)
            {
                return;
            }

            if (!application.CopiedFromApplicationDetailID.HasValue)
            {
                return;
            }

            var parentId = application.CopiedFromApplicationDetailID.Value;
            var licenceFilter = application.CopiedFromApplicationLicenceID;

            var q = Context.Vw_ApplicationLicences.Where(v => v.ApplicationDetailID == parentId);
            if (licenceFilter.HasValue)
            {
                q = q.Where(v => v.ApplicationLicenceID == licenceFilter.Value);
            }

            var row = q.OrderBy(v => v.ApplicationLicenceID).FirstOrDefault();
            if (row == null)
            {
                return;
            }

            model.TatParentVehicleTypeDisplay = row.VehicleType;
            model.TatParentServiceTypeDisplay = (from s in Context.ApplicationLicenceServiceDetails
                                                   join st in Context.ServiceTypes on s.ServiceTypeID equals st.ServiceTypeID
                                                   where s.ApplicationLicenceID == row.ApplicationLicenceID
                                                   orderby s.ServiceTypeID
                                                   select st.Description).FirstOrDefault();
        }
        #endregion
    }
}