using System;
using System.Data;
using System.Data.SqlClient;

namespace VoServices.Footprint.Web
{
	
	public struct UserFolder
	{
		public int Id;
		public string Name;
		public int Count;
	}

	public class User : BaseObject
	{
		public string Guid = "";
		public int GroupId = 0;
		public string Username = "";
		public string Password = "";
		public string Name = "";
		public string Institute = "";
		public string Email = "";

		public User():base()
		{
		}

		public User(SqlConnection databaseConnection):base(databaseConnection)
		{
		}

		public bool Login()
		{
			SqlCommand cmd = new SqlCommand("sp_LoginUser", DatabaseConnection);
			cmd.CommandType = CommandType.StoredProcedure;
			
			cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = Username;
			cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50).Value = Password;
		
			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);

			da.Dispose();
			cmd.Dispose();

			if (ds.Tables[0].Rows.Count == 0)
				return false;
			else
			{
				LoadFromDatarow(ds.Tables[0].Rows[0]);
				return true;
			}
		}

		public void LoadFromDatarow(DataRow dr)
		{
			Guid = ((Guid) dr["GUID"]).ToString();
			GroupId = (int) dr["GroupId"];
			Username = (string) dr["Username"];
			Password = (string) dr["Password"];
			Name = (string) dr["Name"];
			Institute = (string) dr["Institute"];
			Email = (string) dr["Email"];
		}

		public void Load()
		{
			SqlCommand cmd = new SqlCommand("sp_GetUser", DatabaseConnection);
			cmd.CommandType = CommandType.StoredProcedure;
			
			cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier).Value = new Guid(Guid);
		
			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);

			LoadFromDatarow(ds.Tables[0].Rows[0]);

			ds.Dispose();
			da.Dispose();
			cmd.Dispose();
		}

		public void Load(string username)
		{
			// used for lost password mailing
			SqlCommand cmd = new SqlCommand("sp_GetUserByUsername", DatabaseConnection);
			cmd.CommandType = CommandType.StoredProcedure;
			
			cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = username;
		
			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);

			if (ds.Tables[0].Rows.Count > 0)
				LoadFromDatarow(ds.Tables[0].Rows[0]);
	
			ds.Dispose();
			da.Dispose();
			cmd.Dispose();			
		}

		public void Save()
		{
			if (Guid == "")
			{

				// Creating a new user object in the database
				string sql = "sp_CreateUser";
				SqlCommand cmd = new SqlCommand(sql, DatabaseConnection);
				cmd.CommandType = CommandType.StoredProcedure;

				cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = GroupId;
				cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = Username;
				cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50).Value = Password;
				cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = Name;
				cmd.Parameters.Add("@Institute", SqlDbType.NVarChar, 50).Value = Institute;
				cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = Email;

				cmd.Parameters.Add("@NewGUID", SqlDbType.UniqueIdentifier).Direction = ParameterDirection.Output;

				ExecuteCommandNonquery(cmd);

				Guid = ((Guid) cmd.Parameters["@NewGUID"].Value).ToString();
			
				cmd.Dispose();
			}
			else
			{
				// Modifying an existing user object in the database

				string sql = "sp_ModifyUser";
				SqlCommand cmd = new SqlCommand(sql, DatabaseConnection);
				cmd.CommandType = CommandType.StoredProcedure;

				cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier).Value = new Guid(Guid);
				cmd.Parameters.Add("@GroupID", SqlDbType.Int).Value = GroupId;
				cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 50).Value = Username;
				cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50).Value = Password;
				cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = Name;
				cmd.Parameters.Add("@Institute", SqlDbType.NVarChar, 50).Value = Institute;
				cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = Email;

				ExecuteCommandNonquery(cmd);
			
				cmd.Dispose();
			}
		}

		public UserFolder[] QueryFolders()
		{
			SqlCommand cmd = new SqlCommand("sp_QueryUserFolders", DatabaseConnection);
			cmd.CommandType = CommandType.StoredProcedure;
			
			cmd.Parameters.Add("@UserGUID", SqlDbType.UniqueIdentifier).Value = new Guid(Guid);
		
			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);

			da.Dispose();
			cmd.Dispose();

			UserFolder[] res = new UserFolder[ds.Tables[0].Rows.Count];

			for (int i = 0; i < ds.Tables[0].Rows.Count; i ++)
			{
				res[i].Id = (int) ds.Tables[0].Rows[i]["ID"];
				res[i].Name = (string) ds.Tables[0].Rows[i]["Name"];
				res[i].Count = (int) ds.Tables[0].Rows[i]["SpecCount"];
			}

			return res;
		}

		public UserFolder GetFolder(int id)
		{
			SqlCommand cmd = new SqlCommand("sp_GetUserFolder", DatabaseConnection);
			cmd.CommandType = CommandType.StoredProcedure;
			
			cmd.Parameters.Add("@UserGUID", SqlDbType.UniqueIdentifier).Value = new Guid(Guid);
			cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
		
			DataSet ds = new DataSet();
			SqlDataAdapter da = new SqlDataAdapter(cmd);
			da.Fill(ds);

			da.Dispose();
			cmd.Dispose();

			UserFolder res;

			res.Id = (int) ds.Tables[0].Rows[0]["ID"];
			res.Name = (string) ds.Tables[0].Rows[0]["Name"];
			res.Count = (int) ds.Tables[0].Rows[0]["SpecCount"];

			return res;			
		}

		public UserFolder CreateFolder(string name)
		{
			SqlCommand cmd = new SqlCommand("sp_CreateUserFolder", DatabaseConnection);
			cmd.CommandType = CommandType.StoredProcedure;
			
			cmd.Parameters.Add("@UserGUID", SqlDbType.UniqueIdentifier).Value = new Guid(Guid);
			cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = name;
			cmd.Parameters.Add("@NewID", SqlDbType.Int).Direction = ParameterDirection.Output;
		
			cmd.ExecuteNonQuery();

			UserFolder res;

			res.Id = (int) cmd.Parameters["@NewID"].Value;
			res.Name = name;
			res.Count = 0;

			return res;			
		}

		public void RenameFolder(int id, string name)
		{
			SqlCommand cmd = new SqlCommand("sp_ModifyUserFolder", DatabaseConnection);
			cmd.CommandType = CommandType.StoredProcedure;
			
			cmd.Parameters.Add("@UserGUID", SqlDbType.UniqueIdentifier).Value = new Guid(Guid);
			cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
			cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = name;
		
			cmd.ExecuteNonQuery();
		}

		public void DeleteFolder(int id)
		{
			SqlCommand cmd = new SqlCommand("sp_DeleteUserFolder", DatabaseConnection);
			cmd.CommandType = CommandType.StoredProcedure;
			
			cmd.Parameters.Add("@UserGUID", SqlDbType.UniqueIdentifier).Value = new Guid(Guid);
			cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
		
			cmd.ExecuteNonQuery();
		}

		public void MoveFolder(int oldId, int newId)
		{
			SqlCommand cmd = new SqlCommand("sp_SetSpectraUserFolder", DatabaseConnection);
			cmd.CommandType = CommandType.StoredProcedure;
			
			cmd.Parameters.Add("@UserGUID", SqlDbType.UniqueIdentifier).Value = new Guid(Guid);
			cmd.Parameters.Add("@OldFolderID", SqlDbType.Int).Value = oldId;
			cmd.Parameters.Add("@NewFolderID", SqlDbType.Int).Value = newId;
		
			cmd.ExecuteNonQuery();
		}

	}
}
