using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Neo.Legitimate.Common;
using Pilotfish.Afx.Security;
using System.Data.Entity;
using Pilotfish.Afx.Services.Audits;

namespace Neo.Legitimate.Data.Models
{
    public class GetApplicationsAwaitingAdjudicationDataSubsetResult {
        public long ApplicationRequestDetailID { get; set; }
        public string ApplicationNumber { get; set; }
        public string ApplicationDescription { get; set; }
        public string SurnameOrCompanyName { get; set; }
        public long? AdjudicationCount { get; set; }
    }

    public partial class LegitimateDatabase 
    {
        #region GetApplicationsAwaitingAdjudication
        public List<GetApplicationsAwaitingAdjudicationDataSubsetResult> GetApplicationsAwaitingAdjudicationDataSubset(long userID, string steps, long regionID)
        {
            var results = SearchApplicationRequestDetails(-1, -1, steps, true, userID);
            var regionsAdjudicatedInRegion = GetRegionIDsAdjudicatedInRegion(regionID);

            var objects = from r in results
                          where regionsAdjudicatedInRegion.Contains(r.AdjudicationRegionID.Value)
                          select new GetApplicationsAwaitingAdjudicationDataSubsetResult() {
                              ApplicationRequestDetailID = r.ApplicationRequestDetailID,
                              ApplicationNumber = r.ApplicationNumber,
                              AdjudicationCount  = r.AdjudicationCount,
                              ApplicationDescription = r.ApplicationDescription,
                              SurnameOrCompanyName = r.SurnameOrCompanyName
                          };
            var objectCount = objects.Count();
            var objectToReturn = objects.ToList();

            var objectToReturnCount = objectToReturn.Count();

            return objectToReturn;            
        }

        public List<Pr_ApplicationRequestDetailsSearch_Result> GetApplicationsAwaitingAdjudication(long userID, string steps, long regionID)
        {
            var apps = SearchApplicationRequestDetails(-1, -1, steps, true, userID);
            var regionsAdjudicatedInRegion = GetRegionIDsAdjudicatedInRegion(regionID);

            return apps.Where(a => regionsAdjudicatedInRegion.Contains(a.AdjudicationRegionID.Value)).ToList();
        }

        public int GetApplicationsAwaitingAdjudicationCount(long adjudicationDetailID, long regionID)
        {
            var systemUserID = 1;
            var apps = Context.Pr_ApplicationRequestDetailsSearch(-1, -1, "", true, adjudicationDetailID, null, systemUserID).ToList();
            var regionsAdjudicatedInRegion = GetRegionIDsAdjudicatedInRegion(regionID);

            return apps.Where(a => regionsAdjudicatedInRegion.Contains(a.AdjudicationRegionID.Value)).ToList().Count();
        }

        private List<long> GetRegionIDsAdjudicatedInRegion(long regionID)
        {
            return (from r in GetRegions()
                    where r.AdjudicationRegionID == regionID
                    select r.RegionID).ToList();
        }
        #endregion

        #region GetAdjudicationDecisions
        public IEnumerable<AdjudicationDecision> GetAdjudicationDecisions()
        {
            var requestType = Context.AdjudicationDecisions.Where(a => !a.IsCancelled).ToList();

            return requestType;
        }
        #endregion

        #region GetAdjudicationTypes
        public IEnumerable<AdjudicationType> GetAdjudicationTypes()
        {
            return Context.AdjudicationTypes.Where(a => !a.IsCancelled).ToList();
        }
        #endregion

        #region GetAdjudicationDetail
        public AdjudicationDetail GetAdjudicationDetail(long adjudicationDetailID, short adjudicationTypeID, long regionID, long userID)
        {
            if (adjudicationDetailID == -1)
            {
                var regionDetails = (from q in Context.Regions
                                     where q.RegionID == regionID
                                     select q).FirstOrDefault();

                var locationName = (regionDetails != null) ? regionDetails.LocationName : null;
                var locationAddress = (regionDetails != null) ? regionDetails.LocationAddress : null;

                return new AdjudicationDetail()
                {
                    AdjudicationDetailID = -1,
                    AdjudicationDetailStatusID = (short)Common.AdjudicationDetailStatus.AwaitingAgendaFinalisation,
                    AdjudicationTypeID = adjudicationTypeID,
                    MeetingDate = DateTime.Now,
                    ProvinceID = (short)Common.Province.KwaZuluNatal,
                    PreAdjudicationNotificationsSent = false,
                    PreAdjudicationLettersSent = false,
                    PostAdjudicationNotificationsSent = false,
                    PostAdjudicationLettersSent = false,
                    RegionID = regionID,
                    CreatedByID = userID,
                    CreatedDate = DateTime.Now,
                    UpdatedByID = userID,
                    UpdatedDate = DateTime.Now,
                    LocationName = locationName,
                    LocationAddress = locationAddress
                };
            }

            var details = (from q in Context.AdjudicationDetails.Include("Status").Include("Type")
                           where q.AdjudicationDetailID == adjudicationDetailID
                           select q).FirstOrDefault();

            if (details != null)
            {
                details.TotalApplicationCount = GetApplicationsAwaitingAdjudicationCount(details.AdjudicationDetailID, details.RegionID.Value);
            }

            return details;
        }
        #endregion

        #region GetAdjudicationsAwaitingHearingDecision
        public IEnumerable<AdjudicationDetail> GetAdjudicationsAwaitingHearingDecision(UserProfile user, long regionID)
        {
            var list = Context.AdjudicationDetails.Where(a => (a.AdjudicationDetailStatusID == (short)Common.AdjudicationDetailStatus.AwaitingHearingQuorum || a.AdjudicationDetailStatusID == (short)Common.AdjudicationDetailStatus.AwaitingAdjudicationHearingDecisions) && a.RegionID == regionID).OrderBy(a => a.MeetingDate).ToList();

            foreach (var item in list)
            {
                item.MeetingDate = new DateTime(item.MeetingDate.Year, item.MeetingDate.Month, item.MeetingDate.Day, 0, 0, 0);
                item.IsDecisionCaptureAllowed = (item.MeetingDate <= DateTime.Now) || (SecurityHelper.UserHasRole(user.Roles, Neo.Legitimate.Common.Role.AgendaOfficer));
            }

            return list;
        }
        #endregion

        #region GetAdjudicationApplications
        public IEnumerable<Vw_AdjudicationApplicantDetails> GetAdjudicationApplications(long adjudicationDetailID)
        {
            return Context.Vw_AdjudicationApplicantDetails.Where(a => a.AdjudicationDetailID == adjudicationDetailID).ToList();
        }
        #endregion

        #region GetAdjudicationPanelMembers
        public List<Vw_AdjudicationPanel> GetAdjudicationPanelMembers(long adjudicationDetailID)
        {
            return Context.Vw_AdjudicationPanel.Where(a => a.AdjudicationDetailID == adjudicationDetailID).ToList();
        }
        #endregion

        #region GetCurrentAdjudicationDetail
        public AdjudicationDetail GetCurrentAdjudicationDetail(long adjudicationDetailID, short adjudicationTypeID, long regionID, long userID)
        {
            AdjudicationDetail detail = null;

            if (adjudicationDetailID == -1)
            {
                var currentAdjudication = Context.AdjudicationDetails.Where(a => a.AdjudicationDetailStatusID == (short)Common.AdjudicationDetailStatus.AwaitingAgendaFinalisation && a.AdjudicationTypeID == adjudicationTypeID && a.RegionID == regionID).FirstOrDefault();

                if (currentAdjudication == null)
                {
                    detail = GetAdjudicationDetail(adjudicationDetailID, adjudicationTypeID, regionID, userID);
                }
                else
                {
                    detail = currentAdjudication;

                    if (detail != null)
                    {
                        detail.TotalApplicationCount = GetApplicationsAwaitingAdjudicationCount(detail.AdjudicationDetailID, detail.RegionID.Value);
                    }
                }
            }
            else
            {
                detail = GetAdjudicationDetail(adjudicationDetailID, adjudicationTypeID, regionID, userID);
            }

            return detail;
        }
        #endregion

        #region GetAdjudicationRegions
        public IEnumerable<Region> GetAdjudicationRegions()
        {
            var regions = Context.Regions.Where(a => !a.IsCancelled).OrderBy(a => a.Description).ToList();
            var adjudicationRegionIDs = new List<long>();

            foreach (var r in regions)
            {
                if (!adjudicationRegionIDs.Any(a => a == r.AdjudicationRegionID.Value))
                {
                    adjudicationRegionIDs.Add(r.AdjudicationRegionID.Value);
                }
            }

            return regions.Where(a => adjudicationRegionIDs.Contains(a.RegionID)).ToList();
        }
        #endregion

        #region AddApplicationsToAdjudication
        public bool AddApplicationsToAdjudication(string applicationsToAdjudication, long userID, out string errorMessage)
        {
            var adjudicationID = -1L;
            errorMessage = "";

            var db = Context.Database.Connection.CreateCommand();

            try
            {
                if (db.Connection.State != ConnectionState.Open)
                {
                    db.Connection.Open();
                }

                db.CommandText = "EXEC App.Pr_AdjudicationAddApplications @ApplicationsToAdjudication, @UserID";

                db.Parameters.Add(new SqlParameter { ParameterName = "ApplicationsToAdjudication", Value = applicationsToAdjudication });
                db.Parameters.Add(new SqlParameter { ParameterName = "UserID", Value = userID });

                var intFound = false;

                var reader = db.ExecuteReader();

                //find the first int in the result sets returned. This caters for multiple results sets which are returned in certain instances
                do
                {
                    while (reader.Read() && !intFound)
                    {
                        if (Int64.TryParse(reader[0].ToString(), out adjudicationID))
                        {
                            intFound = true;
                        }
                    }
                } while (!intFound && reader.NextResult());
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);
            }
            finally
            {
                if (db.Connection.State == ConnectionState.Open)
                {
                    db.Connection.Close();
                }
            }

            return adjudicationID > 0;
        }
        #endregion

        #region AdjudicationStatusUpdate
        public bool AdjudicationStatusUpdate(long adjudicationDetailID, short status, long userID, string comments, out string errorMessage)
        {
            errorMessage = "";

            try
            {
                Context.Pr_AdjudicationDetailStatusUpdate(userID, comments, adjudicationDetailID, status, true);
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);
            }

            return String.IsNullOrWhiteSpace(errorMessage);
        }
        #endregion

        #region RestartAdjudicationDecisions
        public bool RestartAdjudicationDecisions(long applicationrequestDetailID, long adjudicationDetailID, long userid, out string errorMessage)
        {
            errorMessage = "";

            try
            {
                Context.Pr_ApplicationAdjudicationRestart(applicationrequestDetailID, adjudicationDetailID, userid);
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);
            }

            return String.IsNullOrWhiteSpace(errorMessage);
        }
        #endregion

        #region UpdateAttendance
        public void UpdateAttendance(long adjudicationDetailID, long panelMemberUserID, out string errorMessage)
        {
            errorMessage = "";
            try
            {
                Context.Pr_AdjudicationPanelUpdate(adjudicationDetailID, panelMemberUserID);
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);
            }
        }
        #endregion

        #region CommitAdjudicationDecision
        public void CommitAdjudicationDecision(long applicationrequestDetailID, long adjudicationDetailID, short decisionID, int? licenceDurationID, DateTime? licenceExpiryDate, string comment, long userID, out string errorMessage)
        {
            errorMessage = "";

            try
            {
                Context.Pr_ApplicationRequestAdjudicationDecisionCommit(applicationrequestDetailID, adjudicationDetailID, decisionID, licenceDurationID, licenceExpiryDate, comment, userID);
            }
            catch (Exception ex)
            {
                errorMessage = ex.InnerException.Message;
            }
        }
        #endregion
        
        #region BypassAdjudication
        public void BypassAdjudication(long applicationrequestDetailID, short decisionID, int? licenceDurationID, DateTime? licenceExpiryDate, DateTime actualAdjudicationDate, string comment, long userID, out string errorMessage)
        {
            errorMessage = "";

            try
            {
                Context.Pr_ApplicationRequestAdjudicationBypass(applicationrequestDetailID, decisionID, licenceDurationID, licenceExpiryDate, actualAdjudicationDate, comment, userID);
            }
            catch (Exception ex)
            {
                errorMessage = ex.InnerException.Message;
            }
        }
        #endregion

        #region SaveAdjudicationDetail
        public AdjudicationDetail SaveAdjudicationDetail(AdjudicationDetail adjudicationDetail, long userID, out string errorMessage)
        {
            var currentAdjudication = GetCurrentAdjudicationDetail(adjudicationDetail.AdjudicationDetailID, adjudicationDetail.AdjudicationTypeID, adjudicationDetail.RegionID.Value, userID);
            var changes = new List<string>();
            var auditDb = new AuditsDatabase();

            errorMessage = "";

            if (currentAdjudication != null)
            {
                adjudicationDetail.UpdatedByID = userID;
                adjudicationDetail.UpdatedDate = DateTime.Now;

                if (adjudicationDetail.AdjudicationDetailID == -1)
                {
                    adjudicationDetail.CreatedByID = userID;
                    adjudicationDetail.CreatedDate = DateTime.Now;

                    Context.AdjudicationDetails.Add(adjudicationDetail);
                }
                else
                {
                    changes.AddRange(auditDb.GetEntityDifferences<AdjudicationDetail>(currentAdjudication, adjudicationDetail, "AdjudicationDetailID"));

                    Context.Entry(currentAdjudication).CurrentValues.SetValues(adjudicationDetail);
                    Context.Entry(currentAdjudication).State = System.Data.Entity.EntityState.Modified;
                }

                try
                {
                    Context.SaveChanges();

                    if (changes.Count > 0)
                    {
                        auditDb.WriteEntries(2 /*Application*/, 2 /*Updated*/, changes, 1007 /*AdjudicationDetails*/, currentAdjudication.AdjudicationDetailID, userID);
                    }                     
                }
                catch (Exception ex)
                {
                    errorMessage = GetInnermostMessage(ex);
                }

                currentAdjudication = adjudicationDetail;
            }
            else
            {
                errorMessage = "Can not have more than 1 active Adjudication per region/type at a time";
            }

            return currentAdjudication;
        }
        #endregion

        #region StartAdjudication
        public bool StartAdjudication(long adjudicationDetailID, long chairmanID, long userID, out string errorMessage)
        {
            errorMessage = "";

            var db = Context.Database.Connection.CreateCommand();
            var success = false;

            try
            {
                if (db.Connection.State != ConnectionState.Open)
                {
                    db.Connection.Open();
                }

                db.CommandText = "EXEC App.Pr_AdjudicationStart @AdjudicationDetailID, @ChairmanID, @UserID";

                db.Parameters.Add(new SqlParameter { ParameterName = "AdjudicationDetailID", Value = adjudicationDetailID });
                db.Parameters.Add(new SqlParameter { ParameterName = "ChairmanID", Value = chairmanID });
                db.Parameters.Add(new SqlParameter { ParameterName = "UserID", Value = userID });

                db.ExecuteNonQuery();

                success = true;
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);
            }
            finally
            {
                if (db.Connection.State == ConnectionState.Open)
                {
                    db.Connection.Close();
                }
            }

            return success;
        }
        #endregion

        #region PostponeAdjudication
        public bool PostponeAdjudication(long adjudicationDetailID, string reason, long userID, out string errorMessage)
        {
            errorMessage = "";

            var db = Context.Database.Connection.CreateCommand();
            var success = false;

            try
            {
                if (db.Connection.State != ConnectionState.Open)
                {
                    db.Connection.Open();
                }


                db.CommandText = "EXEC App.Pr_AdjudicationPostpone @AdjudicationDetailID, @Reason, @UserID";
                db.CommandTimeout = 300; //increase timeout as some adjudications have quite a lot of applicatoins on them
                db.Parameters.Add(new SqlParameter { ParameterName = "AdjudicationDetailID", Value = adjudicationDetailID });
                db.Parameters.Add(new SqlParameter { ParameterName = "Reason", Value = reason });
                db.Parameters.Add(new SqlParameter { ParameterName = "UserID", Value = userID });

                db.ExecuteNonQuery();

                success = true;
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);
            }
            finally
            {
                if (db.Connection.State == ConnectionState.Open)
                {
                    db.Connection.Close();
                }
            }

            return success;
        }
        #endregion

        #region RemoveAdjudicationPanelMember
        public bool RemoveAdjudicationPanelMember(long adjudicationDetailID, long adjudicationPanelID, out string errorMessage)
        {
            errorMessage = "";

            var db = Context.Database.Connection.CreateCommand();
            var success = false;

            try
            {
                if (db.Connection.State != ConnectionState.Open)
                {
                    db.Connection.Open();
                }

                db.CommandText = "EXEC App.Pr_AdjudicationPanelRemove @AdjudicationDetailID, @AdjudicationPanelID";

                db.Parameters.Add(new SqlParameter { ParameterName = "AdjudicationDetailID", Value = adjudicationDetailID });
                db.Parameters.Add(new SqlParameter { ParameterName = "AdjudicationPanelID", Value = adjudicationPanelID });

                db.ExecuteNonQuery();

                success = true;
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);
            }
            finally
            {
                if (db.Connection.State == ConnectionState.Open)
                {
                    db.Connection.Close();
                }
            }

            return success;
        }
        #endregion

        #region ReplaceAdjudicationPanelMember
        public bool ReplaceAdjudicationPanelMember(long adjudicationDetailID, long fromAdjudicationPanelID, long toAdjudicationPanelID, long userID, out string errorMessage)
        {
            errorMessage = "";

            var db = Context.Database.Connection.CreateCommand();
            var success = false;

            try
            {
                if (db.Connection.State != ConnectionState.Open)
                {
                    db.Connection.Open();
                }

                db.CommandText = "EXEC App.Pr_AdjudicationPanelReplace @AdjudicationDetailID, @FromAdjudicationPanelID, @ToAdjudicationPanelID, @UserID";

                db.Parameters.Add(new SqlParameter { ParameterName = "AdjudicationDetailID", Value = adjudicationDetailID });
                db.Parameters.Add(new SqlParameter { ParameterName = "FromAdjudicationPanelID", Value = fromAdjudicationPanelID });
                db.Parameters.Add(new SqlParameter { ParameterName = "ToAdjudicationPanelID", Value = toAdjudicationPanelID });
                db.Parameters.Add(new SqlParameter { ParameterName = "UserID", Value = userID });

                db.ExecuteNonQuery();

                success = true;
            }
            catch (Exception ex)
            {
                errorMessage = GetInnermostMessage(ex);
            }
            finally
            {
                if (db.Connection.State == ConnectionState.Open)
                {
                    db.Connection.Close();
                }
            }

            return success;
        }
        #endregion

        #region ReverseAdjudicationDecision
        public void ReverseAdjudicationDecision(long adjudicationDetailID, long applicationrequestDetailID, long userID, string comments)
        {
            Context.Pr_ApplicationAdjudicationDecisionReverse(adjudicationDetailID, applicationrequestDetailID, userID, comments);
        }
        #endregion

        #region RemoveApplicationFromAdjudication
        public void RemoveApplicationFromAdjudication(long applicationrequestDetailID, long adjudicationDetailID, string comments, long userID)
        {
            Context.Pr_AdjudicationRemoveApplication(applicationrequestDetailID, adjudicationDetailID, comments, userID);
        }
        #endregion

        #region AddApplicationToAdjudication
        public void AddApplicationToAdjudication(string applicationReferenceNumber, long adjudicationDetailID, long userID)
        {
            Context.Pr_AdjudicationAddApplication(applicationReferenceNumber, adjudicationDetailID, userID);
        }
        #endregion

        #region GetAdjudicationAgendaEmails
        public IEnumerable<string> GetAdjudicationAgendaEmails()
        {
            return Context.Pr_GetAdjudicationAgendeUserEmails();
        }
        #endregion

    }
}