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;

namespace Sleis.Data
{
	public class RoleData : BaseData<UserRoleModel>, Sleis.Data.IRoleData
	{
		public bool IsLastFacilityAdmin (ISession session, int userId, int facilityId) {
			/*var sql = @"SELECT SEC_USER_ID 
						FROM SEC_USER_ROLE
						WHERE ROLE_NAME = 'FacilityAdmin' AND FAC_SITE_ID = :facilityId ";
			ISQLQuery query = session.CreateSQLQuery(sql);
			query.SetInt32("facilityId", facilityId);
            */
            var results = (from r in session.Query<UserRoleModel>()
                           where r.Role == AppUserRoleType.FacilityAdmin &&
                           r.FacilityId == facilityId
                           select r);
			//List<int> result = (List<int>) query.List<int>(); //does not work against Oracle since datatype is Number 10,0.  Oracle likes decimal.  Which MS SQL doesnt like.
            
			if (results.Any(x=>x.UserId == userId) && results.Count() == 1)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		public List<AppUserRoleType> GetByUserId(int userId)
		{
			return (from d in GetSession().Query<UserRoleModel>()
					where d.UserId == userId &&
						  d.FacilityId == null
					select d.Role).ToList();
		}

		public List<AppUserRoleType> GetAllByUserId(int userId)
		{
			return (from d in GetSession().Query<UserRoleModel>()
					where d.UserId == userId
					select d.Role).ToList();
		}

		public List<AppUserRoleType> GetFacilityRoles(int userId, int facilityId)
		{
			List<AppUserRoleType> list= (from   d in GetSession().Query<UserRoleModel>()
					where  d.UserId == userId &&  
						   d.FacilityId.Value == facilityId
					select d.Role).ToList<AppUserRoleType>();

			List<AppUserRoleType> roles= (from d in list
					orderby d ascending
					select d).ToList<AppUserRoleType>();
			return roles;
		}

		public List<AppUserRoleType> GetAgencyRoles(int userId, int facilityId)
		{
			List<AppUserRoleType> list = (from d in GetSession().Query<UserRoleModel>()
										  where d.UserId == userId &&
												d.FacilityId.Value == null
										  select d.Role).ToList<AppUserRoleType>();

			List<AppUserRoleType> roles = (from d in list
										   orderby d ascending
										   select d).ToList<AppUserRoleType>();
			return roles;
		}

		public void SaveRoles(ISession session, int userId, int? facilityId, List<AppUserRoleType> list)
		{
			var sql = "DELETE FROM SEC_USER_ROLE WHERE SEC_USER_ID=:userId";
			if (facilityId.HasValue && facilityId > 0)
			{
				sql += " AND FAC_SITE_ID=:facId";
			}

			ISQLQuery query = session.CreateSQLQuery(sql);
			query.SetInt32("userId", userId);
			if (facilityId.HasValue && facilityId > 0)
			{
				query.SetInt32("facId", facilityId.Value);
			}
			query.ExecuteUpdate();

			foreach (AppUserRoleType obj in list)
			{
				UserRoleModel role = new UserRoleModel();
				role.FacilityId = facilityId.HasValue && facilityId > 0 ? facilityId : null; //make sure facilityId wasn't accidently zero.
				role.Role = obj;
				role.UserId = userId;
				session.Save(role);
			}
		}

		public void DeleteUserRoles(ISession session, int userId, int facilityId)
		{
			List<UserRoleModel> list = (from d in GetSession().Query<UserRoleModel>()
										where d.UserId == userId &&
											   d.FacilityId.Value == facilityId
										select d).ToList();
			foreach (UserRoleModel userRole in list)
			{
				Delete(userRole, session);
			}
		}

	}
}