using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using NHibernate;
using NHibernate.Linq;

using Sleis.Models;
using Sleis.Infrastructure;
using Sleis.Utility;
using System.Collections;
using System.Text;
using System.Linq.Expressions;
using NHibernate.Criterion;
using NHibernate.Impl;
using NHibernate.Transform;

namespace Sleis.Data
{
	public class AgencyFacilityData : BaseData<AgencyFacilityModel>, IFacilityData
	{
		public override void Init()
		{
			base.Init();
		}

		public List<int> GetReportingYears()
		{
			List<int> list = null;

			using (ISession session = GetSession())
			{
				list = (from d in session.Query<AgencyReport>()
						where d.ReceivedDate != null
						select d.Number).Distinct<int>().ToList<int>();
			}

			return list; //.As<List<int?>>();
		}

		public List<EpaFacilityCategory> GetCategories()
		{
			List<EpaFacilityCategory> list = null;

			using (ISession session = GetSession())
			{
				list = (from d in session.Query<EpaFacilityCategory>()
						select d).ToList <EpaFacilityCategory>();
			}

			return list;
		}
        public FacilityModel GetByUnitProcess(int unitProcessId)
        {
            using (ISession session = GetSession())
            {
                return (from f in session.Query<AgencyFacilityModel>()
                       join eu in session.Query<AgencyEmissionUnit>() on f.Id equals eu.FacilityId
                       join up in session.Query<AgencyUnitProcess>() on eu.Id equals up.EmissionUnitId
                       where up.Id == unitProcessId
                       select f).FirstOrDefault();

            }
        }
        public AgencyFacilityModel GetReportFacility(int reportId)
        {
            using (ISession session = GetSession())
            {
                return session.Query<AgencyFacilityModel>().SingleOrDefault(f=>f.AgencyReportId == reportId);
            }
        }
        public TObj GetReportFacility<TObj>(int reportId) where TObj : FacilityModel
        {
            using (ISession session = GetSession())
            {
                return session.Query<TObj>().SingleOrDefault(f => f.ReportId==reportId);
            }
        }
		public List<object> GetFacilitiesLookUp(string searchTerm)
		{
			using (ISession session = GetSession())
			{
				ISQLQuery query = session.CreateSQLQuery(@"
							SELECT DISTINCT FAC_SITE_NAME, FAC_SITE_IDEN, FAC_SITE_ID
							FROM (
								SELECT M_FAC_SITE.FAC_SITE_ID, M_FAC_SITE.FAC_SITE_NAME, M_FAC_IDEN.FAC_SITE_IDEN, M_ALT_FAC_NAME.ALT_NAME, M_FAC_IDEN.A_PROG_SYS_CD, M_FAC_IDEN.EFF_DATE, M_FAC_IDEN.END_DATE, FAC_SITE_MSTR_IND
								FROM M_FAC_SITE
								LEFT JOIN M_FAC_IDEN ON M_FAC_IDEN.FAC_SITE_ID = M_FAC_SITE.FAC_SITE_ID
								LEFT JOIN M_ALT_FAC_NAME ON M_ALT_FAC_NAME.FAC_SITE_ID = M_FAC_SITE.FAC_SITE_ID
								WHERE (M_FAC_SITE.FAC_SITE_NAME like '%' + :term + '%') 
									OR (M_FAC_IDEN.FAC_SITE_IDEN like '%' + :term + '%')
									OR (M_ALT_FAC_NAME.ALT_NAME like '%' + :term + '%')
							) mfs
							WHERE FAC_SITE_MSTR_IND = 1 AND A_PROG_SYS_CD = 'SLEIS' AND EFF_DATE IS NOT NULL AND (END_DATE IS NULL OR END_DATE < " + DateTime.Now + ")");
				query.SetString(":term", searchTerm);

				return new List<object>();

			}
		}

		// For My Facilities
		public List<CurrentUserFacility> GetFacilities(int userId)
		{
			using (ISession session = GetSession())
			{
				ISQLQuery query = session.CreateSQLQuery(@"SELECT DISTINCT FAC_SITE_ID, FAC_SITE_NAME, FAC_SITE_IDEN 
														   FROM V_USR_SLEIS_FAC 
														   WHERE SEC_USER_ID=:userId");

				query.SetInt32("userId", userId);
				query.AddEntity(typeof(CurrentUserFacility));

				return query.List<CurrentUserFacility>().ToList();

			}
		}
        /*
        [Obsolete]
		public IList<SimpleNumericItem> GetFacilityReports(int userId, int facilityId)
		{
			IList<SimpleNumericItem> list = null;
			using (ISession session = GetSession())
			{
				ISQLQuery query = session.CreateSQLQuery(@"SELECT DISTINCT AGN_RPT_ID, RPT_YEAR 
														FROM V_USR_SLEIS_FAC
														WHERE SEC_USER_ID=:userId 
														  AND FAC_SITE_ID=:facId
														  AND RPT_RDY_SUBM_IND=1
														  AND RPT_SUBM_IND=0
														ORDER BY RPT_YEAR DESC");

				query.SetInt32("userId", userId);
				query.SetInt32("facId", facilityId);

				query.AddEntity(typeof(SimpleNumericItem));

				list = query.List<SimpleNumericItem>();
			}
			return list;
		}
        */
		public SimpleFacilityModel GetSimpleAgencyFacility(int facilityId)
		{
			SimpleFacilityModel facility = null;
			using (ISession session = GetSession())
			{
				ISQLQuery query = session.CreateSQLQuery(@"SELECT DISTINCT s.FAC_SITE_ID, s.FAC_SITE_NAME, s.FAC_SITE_IDEN 
															FROM           M_FAC_SITE s      
															LEFT JOIN      M_FAC_IDEN i      ON s.FAC_SITE_ID = i.FAC_SITE_ID 
															WHERE s.FAC_SITE_ID=:facilityId");
				query.SetInt32("facilityId", facilityId);

				query.AddEntity(typeof(SimpleFacilityModel));

				facility = query.UniqueResult<SimpleFacilityModel>();
			}

			return facility;
		}

		//For facility Detail
		public SimpleFacilityModel GetSimpleFacility(int facilityId)
		{
			SimpleFacilityModel facility = null;
			using (ISession session = GetSession())
			{
				ISQLQuery query = session.CreateSQLQuery(@"SELECT DISTINCT FAC_SITE_ID, FAC_SITE_NAME, FAC_SITE_IDEN 
															 FROM M_FAC_SITE 
															WHERE FAC_SITE_ID=:facilityId");
				query.SetInt32("facilityId", facilityId);

				query.AddEntity(typeof(SimpleFacilityModel));

				facility = query.UniqueResult<SimpleFacilityModel>();
			}

			return facility;
		}

		
		public AgencyReport GetReport(int facilityId, int reportId)
		{
			using (ISession session = GetSession())
			{

				AgencyReport report = (from d in session.Query<AgencyReport>()
									  where d.FacilityId == facilityId && d.Id == reportId
									  select d).First<AgencyReport>();
				AgencyReport.LoadReportStatus(session, report);
				return report;
			}
		}

		public List<AgencyReport> GetReportList(int facilityId)
		{
			List<AgencyReport> list = null;

			using (ISession session = GetSession())
			{
				list = (from d in session.Query<AgencyReport>()
                        where d.FacilityId == facilityId 
                        //Removed per Bret - published logic applies if using the quick find from public app. agency it see pub and unpub. - 10/26/2011 
                        //&& d.IsPublished == 1
						select d).ToList<AgencyReport>();

				foreach (AgencyReport reportModel in list)
				{
					AgencyReport.LoadReportStatus(session, reportModel);                 
				}

			}

			return list;
		}

		public List<AgencyReport> GetAgencyReportList(int facilityId)
		{
			List<AgencyReport> list = null;

			using (ISession session = GetSession())
			{
				list = (from d in session.Query<AgencyReport>()
						where d.FacilityId == facilityId
						select d).ToList<AgencyReport>();

				foreach (AgencyReport reportModel in list)
				{
					AgencyReport.LoadReportStatus(session, reportModel);
				}

			}

			return list;
		}
        public List<AgencyReport> GetReportsReadyForSubmissionList(int facilityId)
        {
            throw new NotImplementedException();
        }
		public FacilityAddressModel GetFacilityAddress(int facilityId)
		{
			using (ISession session = GetSession())
			{
				var tmp =  (from d in session.Query<ManagmentFacilitySiteAddressModel>()
						where d.FacilityId == facilityId
							select d).FirstOrDefault<ManagmentFacilitySiteAddressModel>();
				return tmp;
			}
		}


		public int GetVersionOfEmissionReportSubnission(int reportId)
		{
			using (ISession session = GetSession())
			{
				return (from d in session.Query<SubmissionDocumentModel>()
						where d.ReportId == reportId &&
							(d.Status == SubmissionDocumentStatus.Submitted
								|| d.Status == SubmissionDocumentStatus.Repudiated
								|| d.Status == SubmissionDocumentStatus.RepudiationRequested)
						select d.Id).Count<Int32>();
			}

		}
        public List<FacilityModel> GetUserFacilities(int userId)
        {
            using (ISession session = GetSession())
            {
                return (from f in session.Query<AgencyFacilityModel>()
                        join r in session.Query<UserRoleModel>() on f.Id equals r.FacilityId
                        where r.UserId == userId
                        select new AgencyFacilityModel
                        {
                            Id = f.Id,
                            FacName = f.FacName
                        }).Distinct().ToList<FacilityModel>();
            }
        }
        public T GetById<T>(int id, bool isMaster) where T : AgencyFacilityModel
        {
            using (ISession session = GetSession())
            {
                return session.Query<T>().SingleOrDefault(f => f.Id == id && f.IsMaster == isMaster);
            }
        }
        public AgencyFacilityModel GetAgencyFacility(int id)
        {
            using (ISession session = GetSession())
            {
                return session.Query<AgencyFacilityModel>().SingleOrDefault(f => f.Id == id);                        
            }
        }

        public List<AgencyFacilityModel> GetFacilitiesByCategoriesAndYears(string[]categoryCodes, int[] years, bool hasNullCategoryCode, bool hasNoReportsSubmitted)
        {       
             
            //Expression<Func<AgencyFacilityModel, bool>> predicate = PredicateBuilder.True<AgencyFacilityModel>(); 
            //predicate = predicate.And(ValidFacility());
            AgencyFacilityModel facAlias = null;
            DetachedCriteria query = DetachedCriteria.For(typeof(AgencyFacilityModel), "facility");            
            query.SetProjection(Projections.Distinct(Projections.ProjectionList()
                                                                .Add(Projections.Property<AgencyFacilityModel>(f=>f.Id).WithAlias(()=>facAlias.Id)) 
                                                                .Add(Projections.Property<AgencyFacilityModel>(f=>f.FacilityIdentifier).WithAlias(()=>facAlias.FacilityIdentifier))
                                                                .Add(Projections.Property<AgencyFacilityModel>(f=>f.FacName).WithAlias(()=>facAlias.FacName))                                                                
                                                                .Add(Projections.Property<AgencyFacilityModel>(f=>f.FacilityStatus).WithAlias(()=>facAlias.FacilityStatus))
                                                                .Add(Projections.Property<AgencyFacilityModel>(f=>f.FacilityStatusAsOfDate).WithAlias(()=>facAlias.FacilityStatusAsOfDate))
                                                                .Add(Projections.Property<AgencyFacilityModel>(f=>f.FacilityCategory).WithAlias(()=>facAlias.FacilityCategory))
                                                             ));
            //query.CreateAlias("FacilityAgencyIdentifiers", "identifiers", NHibernate.SqlCommand.JoinType.InnerJoin);
            query.CreateAlias("AgencyReports", "reports", NHibernate.SqlCommand.JoinType.LeftOuterJoin);

            AddValidFacilityQuery(query);                       
            AddFacilityCategoriesQuery(query, categoryCodes, hasNullCategoryCode);
            AddReportDatesQuery(query, years, hasNoReportsSubmitted);

            query.SetResultTransformer(Transformers.AliasToBean(typeof(AgencyFacilityModel)));

            using (ISession session = GetSession())
            {                
               return query.GetExecutableCriteria(session).List<AgencyFacilityModel>().ToList();                
            }
        }
        private void AddValidFacilityQuery(DetachedCriteria query)
        {
            // && (i.EndDate >= DateTime.Now || !i.EndDate.HasValue)
            //&& (i.EffectiveDate <= DateTime.Now || i.EffectiveDate.HasValue!=null)
            //ICriterion isMaster = NHibernate.Criterion.Expression.Eq("IsMaster", true);
            DetachedCriteria validQuery = null;
            validQuery = query.GetCriteriaByAlias("facility");
             validQuery.Add(NHibernate.Criterion.Expression.Eq("IsMaster", true));
            //validQuery = query.GetCriteriaByPath("FacilityAgencyIdentifiers");
            //validQuery.Add(NHibernate.Criterion.Expression.Or(
            //                                                    NHibernate.Criterion.Expression.IsNull("EndDate")
            //                                                    , NHibernate.Criterion.Expression.Ge("EndDate", DateTime.Now)
            //                                                ));
            //validQuery.Add(NHibernate.Criterion.Expression.Or(NHibernate.Criterion.Expression.IsNotNull("EffectiveDate")
            //                                                    , NHibernate.Criterion.Expression.Le("EffectiveDate", DateTime.Now)
            //                                                    ));
            //validQuery.Add(NHibernate.Criterion.Expression.Eq("ProSysCode", "SLEIS"));
        }
        private void AddReportDatesQuery(DetachedCriteria query, int[] years, bool hasNoReportsSubmitted)
        {
           DetachedCriteria reportCriteria = null;
           ICriterion yearsReportedIn = NHibernate.Criterion.Expression.In("Number", years);
           ICriterion reportsNotSubmitted = NHibernate.Criterion.Expression.IsNull("Number");
           //check only reported yeras
           if (years != null && years.Count() > 0 && !hasNoReportsSubmitted)
           {
               reportCriteria = query.GetCriteriaByPath("AgencyReports");
               reportCriteria.Add(yearsReportedIn);
           }
           //check only not reported
           else if((years==null || years.Count()==0) && hasNoReportsSubmitted)
           {
               reportCriteria = query.GetCriteriaByPath("AgencyReports");
               reportCriteria.Add(reportsNotSubmitted);
           }
           //check both
           else if (years != null && years.Count() > 0 && hasNoReportsSubmitted)
           {
               reportCriteria = query.GetCriteriaByPath("AgencyReports");
               reportCriteria.Add(NHibernate.Criterion.Expression.Or(yearsReportedIn, reportsNotSubmitted));
           }
            //do not filter
        }
        private void AddFacilityCategoriesQuery(DetachedCriteria query, string[]categoryCodes, bool hasNullCategoryCode)
        {
            DetachedCriteria categoriesQuery = null;           
            ICriterion nullCatCode = NHibernate.Criterion.Expression.IsNull("FacilityCategory");
            ICriterion catCodesIn = NHibernate.Criterion.Expression.In("FacilityCategory", categoryCodes);
            //check only if category codes
            if (categoryCodes != null && categoryCodes.Count() > 0 && !hasNullCategoryCode)
            {                
                categoriesQuery = query;                
                categoriesQuery.Add(catCodesIn);
            }
            //check only if searching null categories
            else if ((categoryCodes == null || categoryCodes.Count()==0) && hasNullCategoryCode)
            {
                categoriesQuery = query;
                categoriesQuery.Add(nullCatCode);
            }
            //check if has category code or null
            else if(categoryCodes!=null && categoryCodes.Count() > 0 && hasNullCategoryCode)
            {
                categoriesQuery = query;
                categoriesQuery.Add(NHibernate.Criterion.Expression.Or(catCodesIn, nullCatCode));
            }
            //if not match...search everything (dont add "where" logic")
        }

        private static Expression<Func<AgencyFacilityModel, bool>> ContainsCategoryCodes(string[] categoryCodes, bool hasNullCategoryCodes)
        {
            var predicate = PredicateBuilder.False<AgencyFacilityModel>();
            foreach(string code in categoryCodes)
            {
                string temp = code;
                predicate = predicate.Or(f=>f.FacilityCategory.Contains(temp));
            }
            return predicate;
        }
        
        /// <summary>
        /// Giant hack to generate parameterized dynamic where clause
        /// </summary>
        /// <param name="query"></param>
        /// <param name="categoryCodes"></param>
        /// <param name="hasNullCategoryCodes"></param>
        /*
        private void ContainsCategoryCodes(IDetachedQuery query, string[] categoryCodes, bool hasNullCategoryCodes)
        {
            StringBuilder whereClause = new StringBuilder(); 
            //fc.E_FAC_CATG_CD IN ('HAP', 'CAP', 'HAPCAP') OR fs.E_FAC_CATG_CD IS NULL) --The list values selected on selection page - Dynamic
            int counter = 1;
            string categoryParamaterPrefix = "param_category_code_{0}";
            if (categoryCodes != null && categoryCodes.Count() > 0)
            {
                whereClause.Append("fc.E_FAC_CATG_CD IN (");
                foreach (string code in categoryCodes)
                {
                    whereClause.Append(String.Format(":" + categoryParamaterPrefix, code));
                    if (counter < categoryCodes.Count())
                    {
                        whereClause.Append(",");
                    }
                    query.SetString(String.Format(categoryParamaterPrefix, code), code);
                }
                whereClause.Append("");
            }
            if (hasNullCategoryCodes)
            {
                whereClause.Append(" fs.E_FAC_CATG_CD IS NULL");
            }            
            query.QueryString.Replace("%WHERE_CATEGORY_CODES%", String.IsNullOrEmpty(whereClause.ToString()) ? String.Format("AND ({0})", whereClause.ToString()): "");
        }
          */   
        private static Expression<Func<AgencyFacilityModel, bool>> ContainsReportingYears(int[] years, bool hasNoReportsSubmitted)
        {
            var predicate = PredicateBuilder.False<AgencyFacilityModel>();            
            foreach (int year in years)
            {
                int temp = year;
                predicate = predicate.Or(ContainsReportingYear(temp));
            }
            return predicate;
        }
        private static Expression<Func<AgencyFacilityModel, bool>> ValidFacility()
        {

            return (f => f.Identifiers.Where(i=>i.ProSysCode=="SLEIS"
                               // && (i.EndDate >= DateTime.Now || !i.EndDate.HasValue)
                                //&& (i.EffectiveDate <= DateTime.Now || i.EffectiveDate.HasValue!=null)
                                ).Count() > 0);


            //return f=> f.FacilityAgencyIdentifiers.Where(i => i.ProSysCode == "SLEIS"
            //                                                && (i.EndDate >= DateTime.Now || i.EndDate==null)
            //                                                && (i.EffectiveDate <= DateTime.Now || i.EffectiveDate!=null )).Count() > 0;
        }
        private static Expression<Func<AgencyFacilityModel, bool>> ContainsReportingYear(int year)
        {
            return f => f.AgencyReports.Where(r => r.Number==year).Count() > 0;
        }
        
        #region John's Stupid Facility Query
        /*
         --Facility List
        SELECT DISTINCT fi.FAC_SITE_IDEN,fs.FAC_SITE_NAME,fss.E_FAC_SITE_STAT_CD,fss.FAC_SITE_STAT_DESC,fs.FAC_SITE_STAT_CD_DATE, fc.E_FAC_CATG_CD,fc.FAC_CATG_NAME
        FROM M_FAC_IDEN fi
	         INNER JOIN M_FAC_SITE fs ON fi.FAC_SITE_ID = fs.FAC_SITE_ID
	         LEFT JOIN M_AGN_RPT ar ON fs.FAC_SITE_ID = ar.FAC_SITE_ID
	         LEFT JOIN E_FAC_CATG fc ON fs.E_FAC_CATG_CD = fc.E_FAC_CATG_CD
	         INNER JOIN E_FAC_SITE_STAT fss ON fs.E_FAC_SITE_STAT_CD = fss.E_FAC_SITE_STAT_CD
        WHERE
	        (
		        fi.A_PROG_SYS_CD = 'SLEIS'
		        AND (fi.END_DATE IS NULL OR END_DATE >= GETDATE())
		        AND (fi.EFF_DATE <= GETDATE() OR EFF_DATE IS NOT NULL)
	        )
	        AND 
	        (
		        (fc.E_FAC_CATG_CD IN ('HAP', 'CAP', 'HAPCAP') OR fs.E_FAC_CATG_CD IS NULL) --The list values selected on selection page - Dynamic
	        )
	        AND
	        (
		        (ar.RPT_YEAR IN (2008,2009) OR ar.RPT_YEAR IS NULL)  --The list values selected on selection page, or allow for no selection  - Dynamic
	        )
         */
        #endregion
    }
}