using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

using FourLayer.BusinessEntity;

namespace FourLayer.DataAccessObject
{

   /// <summary>
   /// Summary description for PublisherDAO
   /// </summary>
   public class BookDAO : AbstractDAO<Book>
   {
      public BookDAO()
      {
      }

      protected override string SelectStatement
      {
         get { return "SELECT ISBN,Title,YearPublished,BriefDescription,PublisherName,Books.PublisherId As BookPublisherId,SeriesName,Books.SeriesId As BookSeriesId,CategoryName,Books.CategoryId As BookCategoryId FROM Series  INNER JOIN (Publishers INNER JOIN (Categories INNER JOIN Books ON Categories.CategoryId = Books.CategoryId) ON Publishers.PublisherId = Books.PublisherId) ON Series.SeriesId = Books.SeriesId"; }
      }
      protected override string PrimaryKeyName
      {
         get { return "ISBN"; }
      }
      public override bool IsGetAllCached
      {
         get { return false; }
      }
      public override string CacheName
      {
         get { return "Books"; }
      }

      protected override Book CreateAndFillEntity(DbDataReader recordJustRead)
      {
         // grab the record values and put them into temporary variables
         string isbn = (string)recordJustRead["ISBN"];
         string title = (string)recordJustRead["Title"];
         int yearPub = (int)recordJustRead["YearPublished"];
         string brief = (string)recordJustRead["BriefDescription"];
         string pubName = (string)recordJustRead["PublisherName"];
         int pubId = (int)recordJustRead["BookPublisherId"];
         string seriesName = (string)recordJustRead["SeriesName"];
         int seriesId = (int)recordJustRead["BookSeriesId"];
         string catName = (string)recordJustRead["CategoryName"];
         int catId = (int)recordJustRead["BookCategoryId"];

         // create child objects first
         Publisher pub = new Publisher(pubId, pubName);
         Category cat = new Category(catId, catName);
         Series ser = new Series(seriesId, seriesName);

         // build and populate object from the data
         Book book = new Book(isbn, title, pub, ser, cat, yearPub, brief);

         // get all the authors for this book
         AuthorDAO dao = new AuthorDAO();
         EntityCollection<Author> authors = dao.GetAuthorsForIsbn(isbn);
         foreach (Author author in authors)
         {
            book.AddAuthor(author);
         }

         return book;
      }



      public override DataTable AdaptCollectionToDataTable(EntityCollection<Book> collection)
      {
         DataTable dt = new DataTable();
         dt.Columns.Add(new DataColumn("Isbn", Type.GetType("System.String")));
         dt.Columns.Add(new DataColumn("Title", Type.GetType("System.String")));
         dt.Columns.Add(new DataColumn("YearPublished", Type.GetType("System.Int32")));
         dt.Columns.Add(new DataColumn("BriefDescription", Type.GetType("System.String")));
         dt.Columns.Add(new DataColumn("PublisherName", Type.GetType("System.String")));
         dt.Columns.Add(new DataColumn("PublisherId", Type.GetType("System.Int32")));
         dt.Columns.Add(new DataColumn("SeriesName", Type.GetType("System.String")));
         dt.Columns.Add(new DataColumn("SeriesId", Type.GetType("System.Int32")));
         dt.Columns.Add(new DataColumn("CategoryName", Type.GetType("System.String")));
         dt.Columns.Add(new DataColumn("CategoryId", Type.GetType("System.Int32")));
         foreach (Book b in collection)
         {
            DataRow dr = dt.NewRow();
            dr["Isbn"] = b.Isbn;
            dr["Title"] = b.Title;
            dr["YearPublished"] = b.YearPublished;
            dr["BriefDescription"] = b.BriefDescription;
            dr["PublisherName"] = b.BookPublisher.Name;
            dr["PublisherId"] = b.BookPublisher.Id;
            dr["SeriesName"] = b.BookSeries.Name;
            dr["SeriesId"] = b.BookSeries.Id;
            dr["CategoryName"] = b.BookCategory.Name;
            dr["CategoryId"] = b.BookCategory.Id;
            dt.Rows.Add(dr);
         }
         return dt;
      }


      public override void Update(Book book)
      {
         string sql = "UPDATE Books SET Title=@Title,YearPublished=@YearPublished,";
         sql += "BriefDescription=@BriefDescription,";
         sql += "PublisherId=@PublisherId,SeriesId=@SeriesId,CategoryId=@CategoryId";
         sql += " WHERE ISBN=@ISBN";

         // construct array of parameters
         DbParameter[] parameters = new DbParameter[] {
            DatabaseActions.MakeParameter("@ISBN", book.Isbn, DbType.String),
            DatabaseActions.MakeParameter("@Title", book.Title, DbType.String),
            DatabaseActions.MakeParameter("@YearPublished", book.YearPublished, DbType.Int32),
            DatabaseActions.MakeParameter("@BriefDescription", book.BriefDescription, DbType.String),
            DatabaseActions.MakeParameter("@PublisherId", book.BookPublisher.Id, DbType.Int32),
            DatabaseActions.MakeParameter("@SeriesId", book.BookSeries.Id, DbType.Int32),
            DatabaseActions.MakeParameter("@CategoryId", book.BookCategory.Id, DbType.Int32),
			};

         // run the specified command
         DatabaseActions.RunNonQuery(sql, parameters);
      }

      public override void Insert(Book book)
      {
         string sql = "INSERT INTO Books (ISBN,Title,YearPublished,Description,BriefDescription,PublisherId,SeriesId,CategoryId) ";
         sql += " VALUES (@ISBN,@Title,@YearPublished,@BriefDescription,@PublisherId,@SeriesId,@CategoryId)";

         // construct array of parameters
         DbParameter[] parameters = new DbParameter[] {
            DatabaseActions.MakeParameter("@ISBN", book.Isbn, DbType.String),
            DatabaseActions.MakeParameter("@Title", book.Title, DbType.String),
            DatabaseActions.MakeParameter("@YearPublished", book.YearPublished, DbType.Int32),
            DatabaseActions.MakeParameter("@BriefDescription", book.BriefDescription, DbType.String),
            DatabaseActions.MakeParameter("@PublisherId", book.BookPublisher.Id, DbType.Int32),
            DatabaseActions.MakeParameter("@SeriesId", book.BookSeries.Id, DbType.Int32),
            DatabaseActions.MakeParameter("@CategoryId", book.BookCategory.Id, DbType.Int32),
			};


         // run the specified command
         DatabaseActions.RunNonQuery(sql, parameters);
      }

      public override void Delete(Book book)
      {
         string sql = "DELETE FROM Books WHERE ISBN=@ISBN";

         // construct array of parameters
         DbParameter[] parameters = new DbParameter[] {
            DatabaseActions.MakeParameter("@ISBN", book.Isbn, DbType.String)
			};

         // run the specified command
         DatabaseActions.RunNonQuery(sql, parameters);
      }
   }
}