using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using BookingModule.Entity;
using BookingModule.Exceptions;
using CoreLibrary.Util;

namespace BookingModule.DataAccess
{
    /// <summary>
    /// Very simplified version of a DataAccessManager with classic ADO.NET 
    /// </summary>
    public static class BookingModuleDataAccessManager
    {
        #region Constants

        private const string ConnectionStringKey = "BookingEntities";       
        private const string INSERT_AVAILABILITY_REQUEST = "INSERT INTO [AvailabilityRequest] ([DepartureDate], [Destination], [Origin], [ReturnDate])" +
                                                           " OUTPUT INSERTED.Id VALUES (@DepartureDate, @Destination, @Origin, @ReturnDate)";
        private const string LOAD_LAST_AVAILABILITY_REQUEST = "SELECT TOP @n [DepartureDate], [Destination], [Origin], [ReturnDate] FROM [AvailabilityRequest] ORDER BY [Id] Desc";
       
        #endregion

        #region Public Methods

        /// <summary>
        /// Read a list of the last availability requests made on the system
        /// </summary>
        /// <param name="loadNumber">Number of objets to load</param>
        /// <returns></returns>
        public static List<AvailabilityRequest> LoadLastAvailabilityRequestList(int loadNumber)
        {
            List<AvailabilityRequest> result = new List<AvailabilityRequest>();
            try
            {
                using (SqlConnection sqlcon = new SqlConnection(Configuration.GetConnectionString(BookingModuleDataAccessManager.ConnectionStringKey)))
                {
                    sqlcon.Open();
                    using (SqlCommand sqlCmd = sqlcon.CreateCommand())
                    {
                        sqlCmd.CommandText = BookingModuleDataAccessManager.LOAD_LAST_AVAILABILITY_REQUEST.Replace("@n", loadNumber.ToString());
                        
                        using (DbDataReader reader = sqlCmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                result.Add(generateDtoFromReader(reader));
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new BookingException("Error inserting Availability request ", ex, BookingFault.DataAccess);
            }
            return result;
        }

        /// <summary>
        /// Insert an availability request on the database
        /// </summary>
        /// <param name="request">Availability request</param>
        /// <returns>id that was inserted</returns>
        public static int InsertRequest(AvailabilityRequest request)
        {
            try
            {
                using (SqlConnection sqlcon = new SqlConnection(Configuration.GetConnectionString(BookingModuleDataAccessManager.ConnectionStringKey)))
                {
                    sqlcon.Open();
                    using (SqlCommand sqlCmd = sqlcon.CreateCommand())
                    {
                        sqlCmd.CommandText = BookingModuleDataAccessManager.INSERT_AVAILABILITY_REQUEST;
                        sqlCmd.Parameters.Add("@DepartureDate", SqlDbType.DateTime).Value = request.DepartureDate;
                        sqlCmd.Parameters.Add("@Destination", SqlDbType.NVarChar).Value = request.Destination;
                        sqlCmd.Parameters.Add("@Origin", SqlDbType.NVarChar).Value = request.Origin;
                        sqlCmd.Parameters.Add("@ReturnDate", SqlDbType.DateTime).Value = request.DepartureDate;
                        return (int)(sqlCmd.ExecuteScalar());
                    }
                }
            }
            catch (Exception ex)
            {
                throw new BookingException("Error inserting Availability request ", ex, BookingFault.DataAccess);    //  Throw specific exception
            }
        }

        #endregion

        #region Private Methods

        /// <summary>
        /// Fill a Dto from the reader object on a query
        /// </summary>
        /// <param name="reader">Reader object</param>
        /// <returns>Data transfer object</returns>
        private static AvailabilityRequest generateDtoFromReader(DbDataReader reader)
        {
            AvailabilityRequest dto = new AvailabilityRequest();
            dto.DepartureDate = (DateTime) reader.GetValue(0);
            dto.Destination = (string) reader.GetValue(1);
            dto.Origin = (string) reader.GetValue(2);
            dto.ReturnDate = (DateTime) reader.GetValue(3);
            return dto;
        }

        #endregion

    }
}
