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


namespace Wrox.WebModules.Forums.Data
{
	public class ForumDetails
	{
		public int CategoryID;
		public string CategoryName;
		public string CategoryImageUrl;
		public int CategoryPosition;
		public int ForumID;
		public string Name;
		public string Description;
		public int Position;
		public int Topics;
		public int Posts;
		public DateTime LastPostDate;
	}

	public class Forums : Wrox.WebModules.Data.DbObject
	{
		public Forums(string newConnectionString) : base(newConnectionString)
		{	}

		// return all the Forums
		public DataSet GetForums(int categoryID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@CategoryID", SqlDbType.Int, 4) };
			parameters[0].Value = categoryID;

			return RunProcedure("sp_Forums_GetForums", parameters, "Forums");
		}


		// return only the record with the specified ID
		public DataRow GetDetailsRow(int forumID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@ForumID", SqlDbType.Int, 4) };
			parameters[0].Value = forumID;
			
			using(DataSet forums = RunProcedure("sp_Forums_GetForumDetails", parameters, "Forums"))
			{
				// return the first row, which is the only one
				return forums.Tables[0].Rows[0];
			}
		}

		// return only the record with the specified ID
		public ForumDetails GetDetails(int forumID)
		{
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@ForumID", SqlDbType.Int, 4) };
			parameters[0].Value = forumID;
			
			using(DataSet forums = RunProcedure("sp_Forums_GetForumDetails", parameters, "Forums"))
			{
				ForumDetails details = new ForumDetails();
				// if the record was found, set the properties of the class instance
				if (forums.Tables[0].Rows.Count > 0)
				{
					DataRow rowForum = forums.Tables[0].Rows[0];
					details.CategoryID = (int)rowForum["CategoryID"];
					details.CategoryName = rowForum["CategoryName"].ToString();
					details.CategoryImageUrl = rowForum["CategoryImageUrl"].ToString();
					details.CategoryPosition = (rowForum["CategoryPosition"]==DBNull.Value ? 
						0 : (int)rowForum["CategoryPosition"]);
					details.ForumID = (int)rowForum["ForumID"];
					details.Name = rowForum["ForumName"].ToString();
					details.Description = rowForum["ForumDescription"].ToString();
					details.Position = (rowForum["ForumPosition"]==DBNull.Value ? 
						0 : (int)rowForum["ForumPosition"]);
					details.Topics = (int)rowForum["ForumTopics"];
					details.Posts = (int)rowForum["ForumPosts"];
					details.LastPostDate = (rowForum["ForumLastPostDate"]==DBNull.Value ? 
						new DateTime() : Convert.ToDateTime(rowForum["ForumLastPostDate"]));
				}
				else
					details.ForumID = -1;

				return details;
			}
		}


		// add a new record
		public int Add(int categoryID, string forumName, string forumDescription, int forumPosition)
		{
			int numAffected;
	
			// create the parameters
			SqlParameter[] parameters = { 
				new SqlParameter("@CategoryID", SqlDbType.Int, 4),
				new SqlParameter("@ForumName", SqlDbType.VarChar, 100),
				new SqlParameter("@ForumDescription", SqlDbType.VarChar, 250),
				new SqlParameter("@ForumPosition", SqlDbType.Int, 4),
				new SqlParameter("@ForumID", SqlDbType.Int, 4)
			};	
			
			// set the values
			parameters[0].Value = categoryID;
			parameters[1].Value = forumName.Trim();
			parameters[2].Value = forumDescription.Trim();
			parameters[3].Value = forumPosition;
			parameters[4].Direction = ParameterDirection.Output;

			// run the procedure
			RunProcedure("sp_Forums_InsertForum", parameters, out numAffected);

			return (int)parameters[4].Value;
		}


		// update all the fields of the specified category
		public bool Update(int forumID, string forumName, string forumDescription, int forumPosition)
		{
			int numAffected;
		
			// create the parameters
			SqlParameter[] parameters = {
				new SqlParameter("@ForumID", SqlDbType.Int, 4),
				new SqlParameter("@ForumName", SqlDbType.VarChar, 100),
				new SqlParameter("@ForumDescription", SqlDbType.VarChar, 250),
				new SqlParameter("@ForumPosition", SqlDbType.Int, 4)
			};
			
			// set the values
			parameters[0].Value = forumID;
			parameters[1].Value = forumName.Trim();
			parameters[2].Value = forumDescription.Trim();
			parameters[3].Value = forumPosition;

			RunProcedure("sp_Forums_UpdateForum", parameters, out numAffected);

			return (numAffected == 1);
		}


		// delete the record identified by the specified ID
		public bool Delete(int forumID)
		{
			int numAffected;
		
			// create the parameter
			SqlParameter[] parameters = { new SqlParameter("@ForumID", SqlDbType.Int, 4) };
			parameters[0].Value = forumID;
			
			RunProcedure("sp_Forums_DeleteForum", parameters, out numAffected);

			return (numAffected == 1);
		}

	}
}