using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Web;
using System.Data.SqlClient;
using System.Data;


namespace framework_library
{
    public class dataInteraction
    {
        private string _tblPrefix = String.Empty;

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="tablePrefix">eg: evo_ or tbl</param>
        public dataInteraction(string tablePrefix)
        {
            _tblPrefix = tablePrefix;
        }

        #region interactions

        /// <summary>
        /// Method to handle an Interaction with the database, 
        /// this can handle inserts, updates, deletes, verify and selects for multiple objects or single objects
        /// </summary>
        /// <param name="interaction">by reference</param>
        public void HandleInteraction(ref oInteraction interaction, string key = "conn")
        {
            SqlCommand command = new SqlCommand();
            bool isCustom = false;
            try
            {
                //check if custom query being used
                if (interaction.query.Trim() != String.Empty)
                    isCustom = true;
                //instanitiate the SQL transaction
                command = dataTier.CommandMethods.BeginSQLCommand(key);

                //check which interaction is taking place
                switch (interaction.interactionType)
                {
                    #region Select Interaction
                    case enums.InteractionType.Select://Select Interaction
                        if (!isCustom)//generic select from the db
                            command.CommandText = buildSelectInteraction(interaction);
                        else //custom select query
                            command.CommandText = interaction.query;
                        //return dataset
                        interaction.interSet = dataTier.ReturnMethods.ReturnDataSet(ref command);
                        //convert dataset to ArrayList
                        interaction.interList = utils.ConvertDataTableToList(interaction.interSet.Tables[0], interaction.interType);
                        //set interaction to successful
                        interaction.interSucess = true;
                        break;
                    #endregion

                    #region Insert Interaction
                    case enums.InteractionType.Insert://Insert Interaction
                        if (!isCustom)//generic insert into db from an object or object collection
                        {
                            //build query for insert                                    
                            command.CommandText = buildInsertInteraction(interaction);

                            if (interaction.interObjects.Count > 0)//multiple insert of objects
                            {
                                //enumerate object collection for insert
                                foreach (Object obj in interaction.interObjects)
                                {
                                    //build parameters 
                                    buildParamsInteraction(obj, interaction.identityField, ref command);
                                    //save data
                                    interaction.interSucess = dataTier.SavingMethods.AddRecord(ref command);
                                    //clear parameters
                                    command.Parameters.Clear();
                                }
                            }
                            else//single insert of a object
                            {
                                //build parameters
                                buildParamsInteraction(interaction.interObject, interaction.identityField, ref command);
                                //save record and return id
                                interaction.interInt = dataTier.ReturnMethods.ReturnIntValue(ref command);
                                if (interaction.interInt > 0)
                                    interaction.interSucess = true;//successful
                                //clear parameters
                                command.Parameters.Clear();
                            }
                        }
                        else//custom query to perform a save
                        {
                            //set command text from custom query
                            command.CommandText = interaction.query;
                            //save record and return id
                            interaction.interInt = dataTier.ReturnMethods.ReturnIntValue(ref command);
                            if (interaction.interInt > 0)
                                interaction.interSucess = true;//successful
                        }
                        break;
                    #endregion

                    #region Update Interaction
                    case enums.InteractionType.Update://Update Interaction
                        if (!isCustom)//generic update to the db from an object or object collection
                        {
                            //build query for update                                    
                            command.CommandText = buildUpdateInteraction(interaction);

                            if (interaction.interObjects.Count > 0)//multiple update of objects
                            {
                                //enumerate object collection for update
                                foreach (Object obj in interaction.interObjects)
                                {
                                    //build parameters 
                                    buildParamsInteraction(obj, interaction.identityField, ref command);
                                    //update record
                                    interaction.interBool = dataTier.SavingMethods.UpdateRecord(ref command);
                                    if (interaction.interBool)//verify if update successful
                                        interaction.interSucess = true;
                                    //clear parameters
                                    command.Parameters.Clear();
                                }
                            }
                            else //single update of an object to the db
                            {
                                //build parameters 
                                buildParamsInteraction(interaction.interObject, interaction.identityField, ref command);
                                //update record
                                interaction.interBool = dataTier.SavingMethods.UpdateRecord(ref command);
                                if (interaction.interBool)//verify if update successful
                                    interaction.interSucess = true;
                                //clear parameters
                                command.Parameters.Clear();
                            }
                        }
                        else//custom update to the db
                        {
                            command.CommandText = interaction.query;
                            //update record
                            interaction.interBool = dataTier.SavingMethods.UpdateRecord(ref command);
                            if (interaction.interBool)//verify if update successful
                                interaction.interSucess = true;
                        }
                        break;
                    #endregion

                    #region Delete Interaction
                    case enums.InteractionType.Delete://Delete Interaction
                        if (!isCustom)//generic delete from an object 
                            command.CommandText = buildDeleteInteraction(interaction);
                        else//custom delete from the db
                            command.CommandText = interaction.query;
                        //delete record
                        interaction.interBool = dataTier.RemoveMethods.Delete(ref command);
                        if (interaction.interBool)//verify if delete successful
                            interaction.interSucess = true;
                        break;
                    #endregion

                    #region Verify Interaction
                    case enums.InteractionType.Verify://Verify Interaction
                        if (!isCustom)//generic verify  from an object 
                            command.CommandText = buildVerifyInteraction(interaction);
                        else//custom verify to the db
                            command.CommandText = interaction.query;
                        //verify record
                        interaction.interBool = dataTier.ReturnMethods.RecordExists(ref command);
                        break;
                    #endregion

                    #region Select Distinct Interaction
                    case enums.InteractionType.SelectDistinct://Select Interaction
                        if (!isCustom)//generic select from the db
                            command.CommandText = buildSelectDistinctInteraction(interaction);
                        else //custom select query
                            command.CommandText = interaction.query;
                        //return dataset
                        interaction.interSet = dataTier.ReturnMethods.ReturnDataSet(ref command);
                        //convert dataset to ArrayList
                        interaction.interList = utils.ConvertDataTableToList(interaction.interSet.Tables[0], interaction.interType);
                        //set interaction to successful
                        interaction.interSucess = true;
                        break;
                        #endregion
                }
            }
            catch (Exception ex)
            {
                ////roll back transaction
                //command.Transaction.Rollback();
                //append exception
                interaction.interExceptions.Add(ex.Message);
                //force result to false
                interaction.interSucess = false;
            }
            finally
            {
                //commit transaction
                //dataTier.TransactionalMethods.CompleteSQLTransaction(ref command);
                dataTier.CommandMethods.CompleteSQLCommand(ref command);
            }
        }


        #region private interaction Methods
        /// <summary>
        /// Build Select Query from an Interaction
        /// </summary>
        /// <param name="interaction"></param>
        private string buildSelectInteraction(oInteraction interaction)
        {
            string query = string.Empty;
            string tableName = _tblPrefix + interaction.interType.Name.Remove(0, 1);
            try
            {
                query += "SELECT ";
                //build column list
                foreach (PropertyInfo prop in interaction.interType.GetProperties())
                {
                    query += "[" + prop.Name + "], ";
                }
                //remove trailing comma
                query = query.Remove(query.Length - 2, 1);
                query += "FROM " + tableName + " ";
                if (interaction.whereClause != String.Empty)
                    query += "WHERE " + interaction.whereClause + " ";
                if (interaction.orderClause != String.Empty)
                    query += "Order BY " + interaction.orderClause;

            }
            catch (Exception ex)
            {
                exception.HandleException("bridge", MethodBase.GetCurrentMethod().Name, ex, HttpContext.Current.Session["userId"]);
                throw ex;
            }
            return query;
        }

        /// <summary>
        /// Build Insert Query from an Interaction
        /// </summary>
        /// <param name="interaction"></param>
        private string buildInsertInteraction(oInteraction interaction)
        {
            string query = string.Empty;
            string tableName = _tblPrefix + interaction.interType.Name.Remove(0, 1);
            PropertyInfo[] properties = interaction.interType.GetProperties();
            try
            {
                query += "INSERT INTO " + tableName + " ";
                query += "(";
                //build column list
                foreach (PropertyInfo prop in properties)
                {
                    if (prop.Name.ToLower() != interaction.identityField.ToLower())
                        query += "[" + prop.Name + "], ";
                }
                //remove trailing comma
                query = query.Remove(query.Length - 2, 1);
                query += ") ";
                query += "VALUES (";
                //build values list
                foreach (PropertyInfo prop in properties)
                {
                    if (prop.Name.ToLower() != interaction.identityField.ToLower())
                        query += "@" + prop.Name + ", ";
                }
                //remove trailing comma
                query = query.Remove(query.Length - 2, 1);
                query += ") ";
                query += "SELECT Ident_Current('" + tableName + "') AS Value ";
            }
            catch (Exception ex)
            {
                exception.HandleException("bridge", MethodBase.GetCurrentMethod().Name, ex, HttpContext.Current.Session["userId"]);
                throw ex;
            }
            return query;
        }

        /// <summary>
        /// Build Update Query from an Object
        /// </summary>
        /// <param name="interaction"></param>
        private string buildUpdateInteraction(oInteraction interaction)
        {
            string query = string.Empty;
            string tableName = _tblPrefix + interaction.interType.Name.Remove(0, 1);
            try
            {
                query += "UPDATE " + tableName + " ";
                query += "SET";
                //build column list
                foreach (PropertyInfo prop in interaction.interType.GetProperties())
                {
                    if (prop.Name.ToLower() != interaction.identityField.ToLower())
                        query += "[" + prop.Name + "] = @" + prop.Name + ", ";
                }
                //remove trailing comma
                query = query.Remove(query.Length - 2, 1);
                if (interaction.whereClause != String.Empty)
                    //add where clause
                    query += "WHERE " + interaction.whereClause;
            }
            catch (Exception ex)
            {
                exception.HandleException("bridge", MethodBase.GetCurrentMethod().Name, ex, HttpContext.Current.Session["userId"]);
                throw ex;
            }
            return query;
        }

        /// <summary>
        /// Build Verify Query from an Object
        /// </summary>
        /// <param name="interaction"></param>
        private string buildVerifyInteraction(oInteraction interaction)
        {
            string query = string.Empty;
            string tableName = _tblPrefix + interaction.interType.Name.Remove(0, 1);
            try
            {
                query += "Select " + interaction.identityField + " FROM " + tableName + " ";
                //add where clause
                query += "WHERE " + interaction.whereClause;
            }
            catch (Exception ex)
            {
                exception.HandleException("bridge", MethodBase.GetCurrentMethod().Name, ex, HttpContext.Current.Session["userId"]);
                throw ex;
            }
            return query;
        }

        /// <summary>
        /// Build Delete Query from an Interaction
        /// </summary>
        /// <param name="interaction"></param>
        private string buildDeleteInteraction(oInteraction interaction)
        {
            string query = string.Empty;
            string tableName = _tblPrefix + interaction.interType.Name.Remove(0, 1);
            try
            {
                query += "DELETE FROM " + tableName + " ";
                if (interaction.whereClause != String.Empty)
                    //add where clause
                    query += "WHERE " + interaction.whereClause;
            }
            catch (Exception ex)
            {
                exception.HandleException("bridge", MethodBase.GetCurrentMethod().Name, ex, HttpContext.Current.Session["userId"]);
                throw ex;
            }
            return query;
        }

        /// <summary>
        /// Build Select Query from an Interaction
        /// </summary>
        /// <param name="interaction"></param>
        private string buildSelectDistinctInteraction(oInteraction interaction)
        {
            string query = string.Empty;
            string tableName = _tblPrefix + interaction.interType.Name.Remove(0, 1);
            try
            {
                query += "SELECT DISTINCT(" + interaction.interDistinct + ") ";
                query += "FROM " + tableName + " ";
                if (interaction.whereClause != String.Empty)
                    query += "WHERE " + interaction.whereClause + " ";
                if (interaction.orderClause != String.Empty)
                    query += "Order BY " + interaction.orderClause;
            }
            catch (Exception ex)
            {
                exception.HandleException("bridge", MethodBase.GetCurrentMethod().Name, ex, HttpContext.Current.Session["userId"]);
                throw ex;
            }
            return query;
        }
        #endregion


        #endregion



        /// <summary>
        /// Method to Build Command Parameters from an Object
        /// </summary>
        /// <param name="interObject"></param>
        /// <param name="identityField"></param>
        /// <param name="command"></param>
        private void buildParamsInteraction(object interObject, string identityField, ref SqlCommand command)
        {
            try
            {
                //build value parameter list for the command
                foreach (PropertyInfo prop in interObject.GetType().GetProperties())
                {
                    if (prop.Name.ToLower() != identityField.ToLower())
                    {

                        //check the type
                        if (prop.PropertyType == typeof(Byte[]))
                        {
                            Byte[] myData = (Byte[])prop.GetValue(interObject, null);
                            command.Parameters.Add(new SqlParameter("@" + prop.Name, SqlDbType.Image, myData.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, myData));
                        }
                        else if (prop.PropertyType == typeof(DateTime))
                            command.Parameters.Add(new SqlParameter("@" + prop.Name, utils.fixDate(prop.GetValue(interObject, null))));

                        else if (prop.PropertyType == typeof(String))
                            command.Parameters.Add(new SqlParameter("@" + prop.Name, utils.formatSqlString(prop.GetValue(interObject, null).ToString())));

                        else
                            command.Parameters.Add(new SqlParameter("@" + prop.Name, prop.GetValue(interObject, null)));
                    }
                }
            }
            catch (Exception ex)
            {
                exception.HandleException("bridge", MethodBase.GetCurrentMethod().Name, ex, HttpContext.Current.Session["userId"]);
                throw ex;
            }
        }
    }
}