using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace lib
{
    internal class DbTableDeliveryInstruction
    {
        protected LogWriter logger = LogWriter.getInstance();
        private string TABLE;
        private Dictionary<string, object> COLUMNS;

        public virtual string __TABLE { get { return TABLE; } set { TABLE = value; } }
        public virtual Dictionary<string, object> __COLUMNS { get { return COLUMNS; } set { COLUMNS = value; } }

        protected static DbImport6 db = null;
        protected int totalPages;
        //public readonly string WHERE_PERIOD = @"AND (
        //            ( period_to IS NULL AND period_from IS NULL )
        //            OR SYSDATETIME() BETWEEN period_from AND period_to
        //            OR (period_to IS NULL AND SYSDATETIME() > period_from)
        //            OR (period_from IS NULL AND SYSDATETIME() < period_to)
        //        )";

        public DbTableDeliveryInstruction()
        {
            if (db == null)
            {
                db = DbImport6.getInstance();
            }
        }

        public static Dictionary<string, Object> toDict(List<Object[]> rows, int iKey, int iValue)
        {
            Dictionary<string, Object> dict = new Dictionary<string, object>();
            foreach (Object[] row in rows)
            {
                if (row[iKey] is int && row[iValue] is int)
                {
                    dict.Add(String.Format("{0}", row[iKey]), String.Format("{0}", row[iValue]));
                }
                else if (row[iKey] is int && row[iValue] is string)
                {
                    dict.Add(String.Format("{0}", row[iKey]), String.Format("{0}", row[iValue]));
                }
                else if (row[iKey] is string && row[iValue] is int)
                {
                    dict.Add(String.Format("{0}", row[iKey]), String.Format("{0}", row[iValue]));
                }
            }
            return dict;
        }

        protected string toCsvFormat(List<Object[]> rows)
        {
            StringBuilder stringBuilder = new StringBuilder();
            foreach (Object[] row in rows)
            {
                int i = 0;
                foreach (Object obj in row)
                {
                    logger.Debug(obj.ToString() + " : " + obj.GetType().ToString());
                    string val = "\"" + obj.ToString() + "\"";
                    if (obj.GetType().ToString().Equals("System.DateTime"))
                    {
                        val = String.Format(@"""{0}""", ((DateTime)obj).ToString("yyMMdd HHmmss"));
                    }
                    stringBuilder.Append((i++ > 0 ? "," : "") + val);
                }
                stringBuilder.AppendLine();
            }

            return stringBuilder.ToString();
        }

        protected string toQsParams(Dictionary<string, object> cols)
        {
            string qstr = "";
            foreach (KeyValuePair<string, object> col in cols)
            {
                qstr += string.Format("@{0},", col.Key);
            }
            return qstr.Length > 0 ? qstr.Substring(0, qstr.Length - 1) : "";
        }

        protected string getColumns(Dictionary<string, object> cols)
        {
            string qstr = "";
            foreach (KeyValuePair<string, object> col in cols)
            {
                qstr += col.Key + ",";
            }
            return qstr.Length > 0 ? qstr.Substring(0, qstr.Length - 1) : "";
        }

        protected string toQueryString(Dictionary<string, object> cols, string delimiter = ",")
        {
            int i = 0;
            string qstr = "";
            foreach (KeyValuePair<string, object> col in cols)
            {
                if (col is int || col is long || col is double)
                {
                    qstr += (i++ > 0 ? delimiter : "") + col.Key + "=" + col.Value;
                }
                else if (col is DateTime)
                {
                    qstr += (i++ > 0 ? delimiter : "") + col.Key + "=" + ((DateTime)col.Value).ToString("yyyy-MM-dd HH:mm:ss");
                }
                else
                {
                    qstr += (i++ > 0 ? delimiter : "") + col.Key + "='" + col.Value + "'";
                }
            }
            return qstr;
        }

        public int add(Dictionary<string, object> cols, bool returnId = false)
        {
            int ret = 0;
            try
            {
                string sql = @"INSERT INTO {0}
                    ({1}) VALUES ({2}); 
                    " + (returnId ? "SELECT SCOPE_IDENTITY()" : "");
                sql = String.Format(sql, __TABLE, getColumns(cols), toQsParams(cols));

                //logger.Debug("SQL: " + sql);

                try
                {
                    //db.conn.Open();

                    if (db.conn.State != ConnectionState.Open)
                    {
                        db.conn.Open();
                    }

                    /**
                     * 
                     * 
                     * 
                     SqlCommand command = new SqlCommand(null, connection);

        // Create and prepare an SQL statement.
        command.CommandText =
            "INSERT INTO Region (RegionID, RegionDescription) " +
            "VALUES (@id, @desc)";
        SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int, 0);
        SqlParameter descParam =
            new SqlParameter("@desc", SqlDbType.Text, 100);
        idParam.Value = 20;
        descParam.Value = "First Region";
        command.Parameters.Add(idParam);
        command.Parameters.Add(descParam);

        // Call Prepare after setting the Commandtext and Parameters.
        command.Prepare();
        command.ExecuteNonQuery();
                     */

                    SqlCommand command = new SqlCommand(null, db.conn);
                    // Create and prepare an SQL statement.
                    command.CommandText = sql;
                    Int32 intLenString = 0;

                    foreach (KeyValuePair<string, object> col in cols)
                    {
                        SqlParameter param = null;
                        var key = "@" + col.Key;
                        //logger.Debug(string.Format("add new param[{0}] => {1}", key, col.Value));
                        if (col.Value is int)
                        {
                            param = new SqlParameter(key, SqlDbType.Int, 0);
                        }
                        else if (col.Value is string)
                        {
                            if (((String)col.Value).Length == 0)
                            {
                                intLenString = 1;
                            }
                            else
                            {
                                intLenString = ((String)col.Value).Length;
                            }
                            param = new SqlParameter(key, SqlDbType.Text, intLenString);
                        }
                        else if (col.Value is DateTime)
                        {
                            param = new SqlParameter(key, SqlDbType.DateTime);
                        }

                        if (param != null)
                        {
                            param.Value = col.Value;
                            command.Parameters.Add(param);
                        }
                    }

                    // Call Prepare after setting the Commandtext and Parameters.
                    command.Prepare();

                    if (returnId)
                    {
                        SqlDataReader reader = command.ExecuteReader();
                        reader.Read();
                        ret = (int)reader.GetValue(0);
                        reader.Close();
                    }
                    else
                    {
                        ret = command.ExecuteNonQuery();
                        //logger.Debug("executed return " + ret);
                    }

                    command.Dispose();
                    
                }
                catch (Exception e)
                {
                    db.conn.Close();
                }
                finally
                {
                    db.conn.Close();
                }

            }
            catch (Exception e)
            {
                if (!e.Message.Contains("Violation of UNIQUE KEY constraint"))
                {
                    logger.Error(e.Message + e.StackTrace);
                }
                db.conn.Close();
            }

            return ret;
        }

        public Task<List<Object[]>> executeQueryAsync(string sql)
        {
            return Task.Run(() =>
            {
                List<Object[]> rows = new List<Object[]>();

                logger.Debug("SQL: " + sql);

                try
                {
                    DbImport6 db = new DbImport6(DbConfig.getInstance());
                    db.conn.Open();

                    SqlCommand command = new SqlCommand(sql, db.conn);
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Object[] objects = new Object[reader.FieldCount];
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            objects[i] = reader.GetValue(i);
                        }
                        rows.Add(objects);
                    }

                    reader.Close();
                    command.Dispose();


                }
                finally
                {
                    db.conn.Close();
                }

                return rows;
            });
        }

        public List<Object[]> executeQuery(string sql)
        {
            List<Object[]> rows = new List<Object[]>();

            logger.Debug("SQL: " + sql);

            try
            {
                db.conn.Open();

                SqlCommand command = new SqlCommand(sql, db.conn);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Object[] objects = new Object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        objects[i] = reader.GetValue(i);
                    }
                    rows.Add(objects);
                }

                reader.Close();
                command.Dispose();

            }
            finally
            {
                db.conn.Close();
            }

            return rows;
        }

        public int executeUpdate(string sql)
        {
            int effected_rows = 0;

            logger.Debug("SQL: " + sql);

            try
            {
                db.conn.Open();

                SqlCommand command = new SqlCommand(sql, db.conn);
                effected_rows = command.ExecuteNonQuery();

                command.Dispose();

            }
            finally
            {
                db.conn.Close();
            }

            return effected_rows;
        }


        /**
         * Inquiry data with conditions
         *
         * @return inquire rows
         */
        public List<Object[]> get(Dictionary<string, object> cols, string where = null, string limit = null, string orderby = null, string ordertype = "Asc")
        {
            List<Object[]> rows = new List<Object[]>();
            string sql = "SELECT " +
            (limit != null ? " TOP " + limit + " " : "") +
              getColumns(cols) +
                " FROM " + __TABLE +
               (where != null ? " WHERE " + where : "") +
               (orderby != null ? " ORDER BY " + orderby + " " + ordertype : "");


            logger.Debug("SQL: " + sql);

            try
            {
                db.conn.Open();

                SqlCommand command = new SqlCommand(sql, db.conn);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Object[] objects = new Object[reader.FieldCount];
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        objects[i] = reader.GetValue(i);
                    }
                    rows.Add(objects);
                }

                reader.Close();
                command.Dispose();

            }
            catch (Exception e)
            {
                logger.Error(e.Message + ":" + e.StackTrace);
            }
            finally
            {
                db.conn.Close();
            }

            return rows;
        }

        public int set(Dictionary<string, object> cols, string where = "1=1")
        {
            int ret = 0;

            string sql = "UPDATE " + __TABLE +
                " SET " + toQueryString(cols) +
            " WHERE " + where;

            //logger.Debug("SQL: " + sql);

            try
            {
                //db.conn.Open();

                if (db.conn.State != ConnectionState.Open)
                {
                    db.conn.Open();
                }

                SqlCommand command = new SqlCommand(sql, db.conn);
                ret = command.ExecuteNonQuery();
                //logger.Debug("executed return " + ret);

                command.Dispose();
                

            }
            catch (Exception e)
            {
                logger.Error(e.Message + ":" + e.StackTrace);
                db.conn.Close();
            }
            finally
            {
                db.conn.Close();
            }

            return ret;
        }

        public int remove(string where = null)
        {
            int effected_rows = 0;

            if (where == null)
            {
                Console.WriteLine("Removing without where condition is not allowed!");
                return effected_rows;
            }

            string sql = "DELETE " +
                " FROM " + __TABLE +
               " WHERE " + where;

            logger.Debug("SQL: " + sql);

            try
            {
                db.conn.Open();

                SqlCommand command = new SqlCommand(sql, db.conn);
                effected_rows = command.ExecuteNonQuery();

                command.Dispose();

            }
            catch (Exception e)
            {
                logger.Error(e.Message + ":" + e.StackTrace);
            }
            finally
            {
                db.conn.Close();
            }

            return effected_rows;
        }

        /**
         * Inquiry all data
         *
         * @return fetched rows
         */
        public virtual List<Object[]> getAll() { return _getAll(); }
        public List<Object[]> _getAll()
        {
            return get(__COLUMNS);
        }

        public virtual string getAllCsv() { return _getAllCsv(); }

        public string _getAllCsv()
        {
            List<Object[]> rows = get(__COLUMNS);
            return toCsvFormat(rows);
        }
    }
}
