using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Neo.LegitimateLicences.Common;
using Neo.Afx.Security;
using System.Data.Entity;

namespace Neo.LegitimateLicences.Data.Models
{
    public partial class LegitimateDatabase 
    {
        #region CreateAdjudication
        public AdjudicationDetail CreateAdjudication(short adjudicationTypeID, DateTime meetingDate, DateTime meetingDateEnd, short maxAdjudicationAutoApplicationCount, long officeLocationID, long userID)
        {            
            return Context.Pr_AdjudicationCreate(adjudicationTypeID, meetingDate, meetingDateEnd, maxAdjudicationAutoApplicationCount, officeLocationID, userID).FirstOrDefault();
        }
        #endregion

        #region UpdateAdjudicationDates
        public void UpdateAdjudicationDates(long adjudicationDetailID, DateTime meetingDate, DateTime meetingDateEnd, long userID)
        {            
            Context.Pr_AdjudicationUpdateDates(adjudicationDetailID, meetingDate, meetingDateEnd, userID);
        }
        #endregion

        #region GetApplicationsAwaitingAdjudication
        public List<Pr_AdjudicationApplicationSearch_Result> GetApplicationsAwaitingAdjudicationDataSubset(short applicationSubStatusID, short? adjudicationTypeID, long userID)
        {            
            return Context.Pr_AdjudicationApplicationSearch(applicationSubStatusID, adjudicationTypeID, userID).ToList();
        }
        
        public List<Vw_AdjudicationApplicationDetails> GetApplicationsLinkedToAdjudication(long adjudicationDetailID, long userID)
        {           
            var apps = Context.Pr_AdjudicationApplicationListGet(adjudicationDetailID, userID).ToList();
            return apps;
        }
        #endregion

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

            return requestType;
        }
        #endregion

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

        #region GetAdjudicationDetail
        public AdjudicationDetail GetAdjudicationDetail(long adjudicationDetailID, short adjudicationTypeID, long officeLocationID, long userID)
        {
            if (adjudicationDetailID == -1)
            {
                var locationDetails = (from q in Context.Vw_OfficeLocations
                                     where q.OfficeLocationID == officeLocationID
                                     select q).FirstOrDefault();

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

                return new AdjudicationDetail()
                {
                    AdjudicationDetailID = -1,
                    AdjudicationDetailStatusID = (short)Common.AdjudicationDetailStatusEnum.AwaitingAgendaFinalisation,
                    AdjudicationTypeID = adjudicationTypeID,
                    MeetingDate = DateTime.Now,
                    OfficeLocationID = officeLocationID, 
                    PreAdjudicationNotificationsSent = false,
                    PreAdjudicationLettersSent = false,
                    PostAdjudicationNotificationsSent = false,
                    PostAdjudicationLettersSent = false,
                    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)
            {
                var appsLinked = GetApplicationsLinkedToAdjudication(details.AdjudicationDetailID, userID);
            }

            return details;
        }
        #endregion

        #region AdjudicationSearch
        public List<Pr_AdjudicationSearch_Result> AdjudicationSearch(short? adjudicationDetailStatusID, DateTime dateFrom, DateTime dateTo, UserProfile user)
        {            
            return Context.Pr_AdjudicationSearch(adjudicationDetailStatusID, dateFrom, dateTo, user.UserID).ToList();
        }
        #endregion

        #region GetApplicationAdjudication
        public Pr_ApplicationAdjudicationGet_Result GetApplicationAdjudication(long applicationAdjudicationID, long userID)
        {            
            return Context.Pr_ApplicationAdjudicationGet(applicationAdjudicationID, userID).FirstOrDefault();
        }
        #endregion

        ////#region GetAdjudicationsAwaitingPanel
        ////public List<AdjudicationDetail> GetAdjudicationsAwaitingPanel(UserProfile user)
        ////{
        ////    var list = Context.AdjudicationDetails.Where(a => (a.AdjudicationDetailStatusID == (short)Common.AdjudicationDetailStatusEnum.AwaitingHearingQuorum || a.AdjudicationDetailStatusID == (short)Common.AdjudicationDetailStatusEnum.AwaitingAdjudicationHearingDecisions || a.AdjudicationDetailStatusID == (short)Common.AdjudicationDetailStatusEnum.AdjudicationHearingFinalised) /*&& 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.LegitimateLicences.Common.RoleEnum.AgendaOfficer));
        ////    }

        ////    return list;
        ////}
        ////#endregion

        ////#region GetAdjudicationsAwaitingHearingDecision
        ////public List<AdjudicationDetail> GetAdjudicationsAwaitingHearingDecision(long regionID, UserProfile user)
        ////{
        ////    var list = Context.AdjudicationDetails.Where(a => (a.AdjudicationDetailStatusID == (short)Common.AdjudicationDetailStatusEnum.AwaitingHearingQuorum || a.AdjudicationDetailStatusID == (short)Common.AdjudicationDetailStatusEnum.AwaitingAdjudicationHearingDecisions || a.AdjudicationDetailStatusID == (short)Common.AdjudicationDetailStatusEnum.AdjudicationHearingFinalised) /*&& 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.LegitimateLicences.Common.RoleEnum.AgendaOfficer));
        ////    }

        ////    return list;
        ////}
        ////#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 officeLocationID, long userID)
        {
            AdjudicationDetail detail = null;

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

                if (currentAdjudication == null)
                {
                    detail = GetAdjudicationDetail(-1, adjudicationTypeID, officeLocationID, userID);
                }
                else
                {
                    detail = GetAdjudicationDetail(currentAdjudication.AdjudicationDetailID, adjudicationTypeID, officeLocationID, userID);
                }
            }
            else
            {
                detail = GetAdjudicationDetail(adjudicationDetailID, adjudicationTypeID, officeLocationID, userID);
            }

            return detail;
        }
        #endregion

        #region GetAdjudicationRegions
        public List<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 officeLocationID, 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, @OfficeLocationID, @UserID";

                db.Parameters.Add(new SqlParameter { ParameterName = "ApplicationsToAdjudication", Value = applicationsToAdjudication });
                db.Parameters.Add(new SqlParameter { ParameterName = "InputOfficeLocationID", Value = officeLocationID });
                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 applicationAdjudicationID, long userid, out string errorMessage)
        {
            errorMessage = "";

            try
            {
                Context.Pr_ApplicationAdjudicationRestart(applicationAdjudicationID, 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 applicationAdjudicationID, short decisionID, int? licenceDurationID, DateTime? licenceExpiryDate, string comment, long userID, out string errorMessage)
        {
            errorMessage = "";

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

            try
            {
                Context.Pr_ApplicationAdjudicationBypass(applicationAdjudicationID, 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.OfficeLocationID, userID);

            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
                {
                    Context.Entry(currentAdjudication).CurrentValues.SetValues(adjudicationDetail);
                    Context.Entry(currentAdjudication).State = System.Data.Entity.EntityState.Modified;
                }

                try
                {
                    Context.SaveChanges();
                }
                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, 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_AdjudicationPanelRemove @AdjudicationDetailID, @AdjudicationPanelID, @UserID";

                db.Parameters.Add(new SqlParameter { ParameterName = "AdjudicationDetailID", Value = adjudicationDetailID });
                db.Parameters.Add(new SqlParameter { ParameterName = "AdjudicationPanelID", Value = adjudicationPanelID });
                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 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 applicationAdjudicationID, long userID, string comments)
        {
            Context.Pr_ApplicationAdjudicationDecisionReverse(applicationAdjudicationID, 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(long applicationDetailID, long adjudicationDetailID, long userID)
        {
            Context.Pr_AdjudicationAddApplication(applicationDetailID, adjudicationDetailID, userID);
        }
        #endregion

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


        #region GetAdjudicationArchive
        public List<AdjudicationDetail> GetAdjudicationArchive(long officeLocationID)
        {
            return Context.AdjudicationDetails
                .Include("Status").Include("Type")
                .Where(a => a.OfficeLocationID == officeLocationID && a.AdjudicationDetailStatusID == (short)AdjudicationDetailStatusEnum.AdjudicationHearingFinalised).ToList();
        }
        #endregion
    }
}