using System;
using System.Collections.Generic;
using System.Text;
using ORMFramework2.Core.Schemas.Config;
using ORMFramework2.Core.Schemas.Mapping;
using ORMFramework2.Core.Libraries;

namespace ORMFramework2.Core.Connection
{
    public class ORMSession
    {
        private ORMConfig m_Config;
        private List<ORMEntity> m_Entities;
        private int m_MaxResults = -1;
        private int m_FirstResult = -1;

        public ORMSession(ORMConfig config)
        {
            m_Config = config;

            m_Entities = ORMSchemaBrowser.Instance.GetEntities(config);
        }

        public List<Object> CreateQuery(Type entityType)
        {
            return CreateQuery(entityType, "", null);
        }

        public List<Object> CreateQuery(Type entityType, string where)
        {
            return CreateQuery(entityType, where, null);
        }

        public List<Object> CreateQuery(Type entityType, string where, List<ORMParameter> parameters)
        {
            return CreateQuery(entityType, where, parameters, "");
        }

        public List<Object> CreateQuery(Type entityType, string where, List<ORMParameter> parameters, string orderBy)
        {
            List<Object> res = new List<Object>();

            ORMEntity entity = ORMSchemaBrowser.Instance.GetEntitySchema(m_Entities, entityType);

            IDBConnection cnx = ORMConnectionFactory.Instance.GetConnection(m_Config);
            ORMCommand cmd = cnx.CreateCommand();
            string szWhere = "";

            //Setting commmand parameters
            cmd.MaxResults = this.m_MaxResults;
            cmd.FirstResult = this.m_FirstResult;
            //End setting parameters

            if (parameters != null)
                foreach (ORMParameter parameter in parameters)
                    cmd.AddParameter(parameter.ParameterName, parameter.Value);

            if (!String.IsNullOrEmpty(where))
                szWhere += " WHERE " + where;

            if (!String.IsNullOrEmpty(orderBy))
                szWhere += " ORDER BY " + orderBy;

            cmd.CommandText = "SELECT * FROM " + entity.TableName + szWhere + ";";

            cnx.Open();

            try
            {
                ORMDataReader reader = cmd.ExecuteReader();

                int iStart = 0;
                int iCount = 0;
                while (reader.Read())
                {
                    if (cmd.FirstResult != -1)
                        if (iStart < cmd.FirstResult)
                        {
                            iStart++;
                            continue;
                        }

                    if(cmd.MaxResults != -1)
                        if (iCount >= cmd.MaxResults) break;

                    Object o = Reflector.Instance.CreateInstance(entityType); // t -> entityType

                    if (entity.IdField != null)
                        Reflector.Instance.SetPropertyValue(entity.IdField.FieldName, o, reader.GetValue(entity.IdField.ColName));

                    foreach (ORMEntityField field in entity.Fields)
                    {
                        Reflector.Instance.SetPropertyValue(field.FieldName, o, reader.GetValue(field.ColName));
                    }

                    res.Add(o);

                    iCount++;
                }
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }

            cnx.Close();

            return res;
        }

        public void Save(Object o)
        {
            IDBConnection cnx = ORMConnectionFactory.Instance.GetConnection(m_Config);

            try
            {
                cnx.Open();

                ORMCommand cmd = cnx.CreateCommand();

                ORMEntity entity = ORMSchemaBrowser.Instance.GetEntitySchema(m_Entities, o.GetType());
                String query = "INSERT INTO " + entity.TableName;

                foreach (ORMEntityField fe in entity.Fields)
                {
                    Object val = Reflector.Instance.GetPropertyValue(fe.FieldName, o);
                    cmd.AddParameter(fe.ColName, val);
                }

                List<ORMParameter> parameters = cmd.GetParameters();

                int i = 0;
                query += "(";
                foreach (ORMParameter field in parameters)
                {
                    query += field.ParameterName;

                    if (i++ < parameters.Count - 1)
                        query += ", ";
                }
                query += ") ";

                i = 0;
                query += "values (";
                foreach (ORMParameter field in parameters)
                {
                    query += "@" + field.ParameterName;

                    if (i++ < parameters.Count - 1)
                        query += ", ";
                }
                query += ");";

                cmd.CommandText = query;
                cmd.ExecuteNonQuery();

                //On définit le pk une fois que l'entité est persisté, incompatible avec SqlCommand
                if (entity.IdField != null)
                    Reflector.Instance.SetPropertyValue(entity.IdField.FieldName, o, (Object)cmd.LastInsertedId);
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);                
            }

            cnx.Close();
        }

        public void Update(Object o)
        {
            IDBConnection cnx = ORMConnectionFactory.Instance.GetConnection(m_Config);

            try
            {
                cnx.Open();

                ORMCommand cmd = cnx.CreateCommand();

                ORMEntity entity = ORMSchemaBrowser.Instance.GetEntitySchema(m_Entities, o.GetType());
                String query = "UPDATE " + entity.TableName + " SET ";

                List<ORMParameter> parameters = new List<ORMParameter>();

                foreach (ORMEntityField field in entity.Fields)
                {
                    Object val = Reflector.Instance.GetPropertyValue(field.FieldName, o);
                    ORMParameter parameter = new ORMParameter(field.ColName, val);
                    parameters.Add(parameter);
                    cmd.AddParameter(parameter.ParameterName, parameter.Value);
                }

                int i = 0;
                query += "";
                foreach (ORMParameter parameter in parameters)
                {
                    query += parameter.ParameterName + " = @" + parameter.ParameterName;

                    if (i++ < parameters.Count - 1)
                        query += ", ";
                }

                Object pkVal = Reflector.Instance.GetPropertyValue(entity.IdField.FieldName, o);

                query += " WHERE " + entity.IdField.ColName + " = '" + pkVal + "';";

                cmd.CommandText = query;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);    
            }

            cnx.Close();
        }

        public void Delete(Object o)
        {
             IDBConnection cnx = ORMConnectionFactory.Instance.GetConnection(m_Config);

            try
            {
                cnx.Open();

                ORMCommand cmd = cnx.CreateCommand();

                ORMEntity entity = ORMSchemaBrowser.Instance.GetEntitySchema(m_Entities, o.GetType());
                String query = "DELETE FROM " + entity.TableName + " WHERE ";

                Object val = Reflector.Instance.GetPropertyValue(entity.IdField.FieldName, o);
                ORMParameter parameter = new ORMParameter(entity.IdField.ColName, val);
                cmd.AddParameter(parameter.ParameterName, parameter.Value);
                query += entity.IdField.ColName + " = @" + entity.IdField.ColName;

                cmd.CommandText = query;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);    
            }

            cnx.Close();
        }

        public List<Object> ExecuteNonQuery(string query)
        {
            List<Object> res = new List<Object>();

            IDBConnection cnx = ORMConnectionFactory.Instance.GetConnection(m_Config);

            try
            {
                cnx.Open();

                ORMCommand cmd = cnx.CreateCommand();

                cmd.CommandText = query;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }

            cnx.Close();

            return res;
        }

        public int MaxResults
        {
            get { return m_MaxResults; }
            set { m_MaxResults = value; }
        }

        public int FirstResult
        {
            get { return m_FirstResult; }
            set { m_FirstResult = value; }
        }
    }
}