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

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

namespace LYB.DAL
{
    // COMPOSER 
    // Holds information about the composer
    public class Composer
    {
        private int _ComposerID;
        private string _ComposerName;
        //private string _LifeDates;

        public Composer()
        {
        }
        #region PROPERTIES
        public int ComposerID
        {
            get { return _ComposerID; }
            set { _ComposerID = value; }
        }
        public string ComposerName
        {
            get { return _ComposerName; }
            set { _ComposerName = value; }
        }
        //public string LifeDates
        //{
        //    get { return _LifeDates; }
        //    set { _LifeDates = value; }
        //}
        #endregion

        #region METHODS

        // ToString
        //public override string ToString()
        //{
        //    return String.Format("{0} [{1}]", _companyname, _id);
        //}
        #endregion
    }
    public class NoComposer : Composer
    {
    }
    // COMPOSERCOLLECTION
    // Collection of Composer objects
    public class ComposerCollection : List<Composer>
    {
    }
    // COMPOSERS--customer gateway
    // Behavior of the composer
    public class Composers
    {
        public static string ConnectionString
        {
            get { return ConfigurationManager.ConnectionStrings["MyLocalSqlServer"].ConnectionString; }
        }

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

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

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                reader.Read();
                Composer composer = HelperMethods.GetComposer(reader);
                reader.Close();
                conn.Close();

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

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

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

            return coll;
        }
        #endregion
        #region METHOD: Save
        // Save 
        public static void Save(int composerid, string composername)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(ComposerCommands.cmdSave, conn);
                cmd.Parameters.AddWithValue("@composerid", composerid);
                cmd.Parameters.AddWithValue("@composername", composername);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                throw new System.Exception("Refresh Composers");
            }
        }

        #endregion


        #region METHOD: Add
        // Add 
        public static void Add(int composerid, string composername)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(ComposerCommands.cmdInsert, conn);
                cmd.Parameters.AddWithValue("@composerid", GetNextComposerID());
                cmd.Parameters.AddWithValue("@composername", composername);

                try
                {
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    throw new System.Exception("Refresh Composers");
                }
                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 composerid)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(ComposerCommands.cmdDelete, conn);
                cmd.Parameters.AddWithValue("@composerid", composerid);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                throw new System.Exception("Refresh Composers");
            }
        }
        public static void Delete(Composer composer)
        {
            Delete(composer.ComposerID);
        }
        #endregion
        #region METHOD: GetNextComposerID

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

        }
        #endregion

    }
    // Helpers
    public static partial class HelperMethods
    {
        // GetComposer
        public static Composer GetComposer(SqlDataReader reader)
        {
            Composer composer = new Composer();
            if (reader.IsClosed)
                reader.Read();

            try
            {
                composer.ComposerID = (int)reader["Composerid"];
                composer.ComposerName = reader["ComposerName"].ToString();
                //composer.LifeDates = reader["LifeDates"].ToString();
            }
            catch (InvalidOperationException)
            {
                // let pass
            }

            return composer;
        }

        // FillComposerList
        public static void FillComposerList(ComposerCollection coll, SqlDataReader reader)
        {
            FillComposerList(coll, reader, -1, 0);
        }
        public static void FillComposerList(ComposerCollection 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
                    {
                        Composer cust = HelperMethods.GetComposer(reader);
                        coll.Add(cust);
                    }
                }
                index++;
            }
        }

    }

}