using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections.Specialized;
using LYB.DAL.Helpers;

// COMPOSITIONS 
// A class to hold compositions data and a static class for the behavior according to the 
// Table Data Gateway pattern

namespace LYB.DAL
{

    // COMPOSITION 
    // Holds information about the Composition
    public class Composition
    {
        private int _CompositionID;
        private string _CompositionName;
        private int _ComposerID;
        private string _DurationMinutes;
        private string _CommentBeforeComposer;
        private string _CommentAfterComposer;
        private string _KeyWords;

        public Composition()
        {
            // initializing couldbe null fields
            _ComposerID = 0;
            _DurationMinutes = "";
            _CommentBeforeComposer = "";
            _CommentAfterComposer = "";
            _KeyWords = "";
        }

        #region PROPERTIES
        public int CompositionID
        {
            get { return _CompositionID; }
            set { _CompositionID = value; }
        }
        public string CompositionName
        {
            get { return _CompositionName; }
            set { _CompositionName = value; }
        }
        public int ComposerID
        {
            get { return _ComposerID; }
            set { _ComposerID = value; }
        }
        public string DurationMinutes
        {
            get { return _DurationMinutes; }
            set { _DurationMinutes = value; }
        }
        public string CommentBeforeComposer
        {
            get { return _CommentBeforeComposer; }
            set { _CommentBeforeComposer = value; }
        }
        public string CommentAfterComposer
        {
            get { return _CommentAfterComposer; }
            set { _CommentAfterComposer = value; }
        }
        public string KeyWords
        {
            get { return _KeyWords; }
            set { _KeyWords = value; }
        }
        #endregion 

    }
    public class NoComposition : Composition
    {
    }
    // CompositionCOLLECTION
    // Collection of Composition objects
    public class CompositionCollection : List<Composition>
    {
    }
    // CompositionS--customer gateway
    // Behavior of the Composition
    public class Compositions
    {
        public static string ConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["MyLocalSqlServer"].ConnectionString; }
        }

        #region METHOD: Load
        // Load
        public static Composition Load(string id)
        {
            if (String.IsNullOrEmpty(id))
                return null; // new NoComposition();  

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(CompositionCommands.cmdLoad, conn);
                cmd.Parameters.AddWithValue("@Compositionid", id);

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                reader.Read();
                Composition Composition = HelperMethods.GetComposition(reader, "all");
                reader.Close();
                conn.Close();

                return Composition;
            }
        }
        #endregion
        #region METHOD: LoadAll
        // LoadAll
        public static CompositionCollection LoadAll()
        {
            return LoadAll(-1, 0);
        }
        public static CompositionCollection LoadAll(int totalRows, int firstRow)
        {
            return LoadAll(totalRows, firstRow, "");
        }
        public static CompositionCollection LoadAll(int totalRows, int firstRow, string sortExpression)
        {
            CompositionCollection coll = new CompositionCollection();

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(CompositionCommands.cmdLoadAll, conn);
                if (!String.IsNullOrEmpty(sortExpression))
                    cmd.CommandText += " ORDER BY " + sortExpression;

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                HelperMethods.FillCompositionList(coll, reader, totalRows, firstRow);
                reader.Close();
                conn.Close();
            }

            return coll;
        }
        #endregion
        #region METHOD: Save
        // Save 
        public static void Save(int CompositionID, string CompositionName, int ComposerID, string DurationMinutes, string CommentBeforeComposer, string CommentAfterComposer, string KeyWords)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(CompositionCommands.cmdSave, conn);
                cmd.Parameters.AddWithValue("@Compositionid", CompositionID);
                cmd.Parameters.AddWithValue("@Compositionname", CompositionName);
                cmd.Parameters.AddWithValue("@composerid", ComposerID);
                if (!(DurationMinutes == null))
                    cmd.Parameters.AddWithValue("@DurationMinutes", DurationMinutes);
                else
                    cmd.Parameters.AddWithValue("@DurationMinutes", "");
                if (!(CommentBeforeComposer == null))
                    cmd.Parameters.AddWithValue("@commentbeforecomposer", CommentBeforeComposer);
                else
                    cmd.Parameters.AddWithValue("@commentbeforecomposer", "");
                if (!(CommentAfterComposer == null))
                    cmd.Parameters.AddWithValue("@commentaftercomposer", CommentAfterComposer);
                else
                    cmd.Parameters.AddWithValue("@commentaftercomposer", "");
                if (!(KeyWords == null))
                    cmd.Parameters.AddWithValue("@keywords", KeyWords);
                else
                    cmd.Parameters.AddWithValue("@keywords", "");
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                throw new System.Exception("Refresh Compositions");
            }
        }

        #endregion


        #region METHOD: Add
        // Add 
        public static void Add(int Compositionid, string CompositionName, int ComposerID, string DurationMinutes, string CommentBeforeComposer, string CommentAfterComposer, string KeyWords)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(CompositionCommands.cmdInsert, conn);
                cmd.Parameters.AddWithValue("@Compositionid", GetNextCompositionID());
                if (CompositionName == null)
                    CompositionName = "Namnlös";
                cmd.Parameters.AddWithValue("@Compositionname", CompositionName);
                cmd.Parameters.AddWithValue("@Composerid", ComposerID);
                if (!(KeyWords == null))
                    cmd.Parameters.AddWithValue("@keywords", KeyWords);
                else
                    cmd.Parameters.AddWithValue("@keywords", "");
                if (!(DurationMinutes == null))
                    cmd.Parameters.AddWithValue("@DurationMinutes", DurationMinutes);
                else
                    cmd.Parameters.AddWithValue("@DurationMinutes", "");
                if (!(CommentBeforeComposer == null))
                    cmd.Parameters.AddWithValue("@commentbeforecomposer", CommentBeforeComposer);
                else
                    cmd.Parameters.AddWithValue("@commentbeforecomposer", "");
                if (!(CommentAfterComposer == null))
                    cmd.Parameters.AddWithValue("@commentaftercomposer", CommentAfterComposer);
                else 
                    cmd.Parameters.AddWithValue("@commentaftercomposer", "");
                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    throw new System.Exception("Refresh Compositions");
                }
                catch (Exception e)
                {
                    if (e.Message.Contains("duplicate key"))
                    {
                        if (e.Message.Contains("PK"))
                            throw new System.Exception("ID finns redan. Sparades inte! " + e.Message);
                        else
                        {
                            throw new System.Exception("Namn finns redan. Sparades inte! " + e.Message);
                        }
                    }
                    else
                    {
                        if (e.Message.Contains("Refresh"))
                        {
                            throw new System.Exception(e.Message);
                        }
                        else
                        {
                            throw new System.Exception("Fel vid insert: " + e.Message);
                        }
                    }
                }
            }
        }

        #endregion


        #region METHOD: Delete
        // Delete 
        public static void Delete(int Compositionid)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(CompositionCommands.cmdDelete, conn);
                cmd.Parameters.AddWithValue("@Compositionid", Compositionid);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                throw new System.Exception("Refresh Compositions");
            }
        }
        public static void Delete(Composition Composition)
        {
            Delete(Composition.CompositionID);
        }
        #endregion
        #region METHOD: GetNextCompositionID

        private static int GetNextCompositionID()
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(CompositionCommands.cmdGetNextCompositionID, conn);
                int NextCompositionID = 9999;
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    NextCompositionID = (int)reader["highestid"] + 1;
                }
                conn.Close();
                return NextCompositionID;
            }

        }
        #endregion

    }
    // Helpers
    public static partial class HelperMethods
    {
        // GetComposition - used to fill the the GridView
        public static Composition GetComposition(SqlDataReader reader)
        {
            Composition Composition = new Composition();
            if (reader.IsClosed)
                reader.Read();

            try
            {
                Composition.CompositionID = (int)reader["Compositionid"];
                Composition.CompositionName = reader["CompositionName"].ToString();
                if (!reader.IsDBNull(1))
                    Composition.KeyWords = reader["KeyWords"].ToString();
                else
                    Composition.KeyWords = "";
            }
            catch (InvalidOperationException)
            {
                // let pass
            }
            catch (Exception e)
            {
                throw new Exception("Fel vid GetComposition: " + e.Message);
            }

            return Composition;
        }
        // GetComposition - used to fill the the DetailsView
        public static Composition GetComposition(SqlDataReader reader, string all)
        {
            Composition Composition = new Composition();
            if (reader.IsClosed)
                reader.Read();

            try
            {
                Composition.CompositionName = reader["CompositionName"].ToString();
                if (!reader.IsDBNull(1))
                    Composition.KeyWords = reader["KeyWords"].ToString();
                else
                    Composition.KeyWords = "";
                Composition.CompositionID = (int)reader["Compositionid"];
                if (!reader.IsDBNull(3))
                    Composition.ComposerID = (int)reader["ComposerID"];
                else
                    Composition.ComposerID = 0;
                if (!reader.IsDBNull(4))
                    Composition.DurationMinutes = reader["DurationMinutes"].ToString();
                else
                    Composition.DurationMinutes = "";
                if (!reader.IsDBNull(5))
                    Composition.CommentBeforeComposer = reader["CommentBeforeComposer"].ToString();
                else
                    Composition.CommentBeforeComposer = "";
                if (!reader.IsDBNull(6))
                    Composition.CommentAfterComposer = reader["CommentAfterComposer"].ToString();
                else
                    Composition.CommentAfterComposer = "";
            }
            catch (InvalidOperationException)
            {
                // let pass
            }
            catch (Exception e)
            {
                throw new Exception("Fel vid GetComposition: " + e.Message);
            }

            return Composition;
        }

        // FillCompositionList
        public static void FillCompositionList(CompositionCollection coll, SqlDataReader reader)
        {
            FillCompositionList(coll, reader, -1, 0);
        }
        public static void FillCompositionList(CompositionCollection coll, SqlDataReader reader, int totalRows, int firstRow)
        {
            int index = 0;
            bool readMore = true;

            while (reader.Read())
            {
                if (index >= firstRow && readMore)
                {
                    if (coll.Count >= totalRows && totalRows > 0)
                        readMore = false;
                    else
                    {
                        Composition cust = HelperMethods.GetComposition(reader);
                        coll.Add(cust);
                    }
                }
                index++;
            }
        }

    }

}