using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Neo.Legitimate.Common;
using Pilotfish.Afx.ComponentModel;
using System.Linq;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System;

namespace Neo.Legitimate.NewRouteRequest.Models
{
    public class NewRouteDatabase : Database<NewRouteContext>
    {
        public NewRouteDatabase()
            : base(new NewRouteContext())
        {

        }

        #region GetNewRouteRequest
        public NewRouteRequestDetail GetNewRouteRequest(long NewRouteRequestDetailId, long userID)
        {
            if (NewRouteRequestDetailId == -1)
            {
                return new NewRouteRequestDetail()
                {
                    NewRouteRequestDetailID = NewRouteRequestDetailId,
                    Nodes = new Collection<NewRouteRouteNode>(),
                    CreatedByID = userID,
                    CreatedDate = DateTime.Now,
                    UpdatedByID = userID,
                    UpdatedDate = DateTime.Now,
                    NewRouteRequestTypeID = (short)Common.RouteRequestType.New
                };
            }

            var details = (from q in Context.NewRouteRequestDetails
                           where q.NewRouteRequestDetailID == NewRouteRequestDetailId
                           select q).FirstOrDefault();

            if (details != null)
            {
                details.Nodes = Context.NewRouteRouteNodes.Where(a => a.NewRouteRequestDetailID == NewRouteRequestDetailId).OrderBy(a => a.Sequence).ToList();

                var routeDetails = Context.ApplicationRouteDetails.Where(a => a.NewRouteRequestDetailID == NewRouteRequestDetailId).ToList();
                if (routeDetails.Count() > 0)
                {
                    var applicationList = new List<long>();
                    foreach (var r in routeDetails)
                    {
                        applicationList.Add(r.ApplicationRequestDetailID);
                    }

                    details.RelatedApplicationRequests = Context.Vw_ApplicationRequestDetails.Where(a => applicationList.Contains(a.ApplicationRequestDetailID)).ToList();
                }

                if (details.VerifiedRouteID.HasValue)
                {
                    details.LinkedOperatingLicences = Context.Pr_RouteLinkedLicences(details.VerifiedRouteID.Value, userID).ToList();
                }
                else
                {
                    details.LinkedOperatingLicences = new List<Pr_RouteLinkedLicences_Result>();
                }

            }
            
            return details;
        }
        #endregion

        #region SearchNewRouteRequestDetails
        public IEnumerable<Vw_NewRouteRequestDetails> SearchNewRouteRequestDetails(long newRouteRequestDetailID, short workflowInstanceStatusID)
        {
            return Context.Pr_NewRouteRequestDetailsSearch(newRouteRequestDetailID, workflowInstanceStatusID, "").ToList();
        }
        #endregion
    }
}