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

namespace WenSkin.SQL
{

    public class SqlConnect
    {

        public SqlConnect() : this("SqlServer")
        {
        }
        public SqlConnect(string server, string database, string uid, string pad)
        {
            this.Server = server;
            this.Database = database;
            this.Uid = uid;
            this.Pad = pad;

            Contr = $"Data Source={this.Server};Initial Catalog={this.Database};User ID={this.Uid};Password={this.Pad};Persist Security Info=True;TrustServerCertificate=True";
        }

        public SqlConnect(string configName)
        {
            WenConfig config = new WenConfig();
            this.Server = config.Groups[configName, "Server"];
            this.Database = config.Groups[configName, "Database"];
            this.Uid = config.Groups[configName, "Uid"];
            this.Pad = config.Groups[configName, "Pad"];

            Contr = $"Data Source={this.Server};Initial Catalog={this.Database};User ID={this.Uid};Password={this.Pad};Persist Security Info=True;TrustServerCertificate=True";
        }
        public SqlConnect(WenConfig config, string configName = "SqlServer")
        {
            this.Server = config.Groups[configName, "Server"];
            this.Database = config.Groups[configName, "Database"];
            this.Uid = config.Groups[configName, "Uid"];
            this.Pad = config.Groups[configName, "Pad"];

            Contr = $"Data Source={this.Server};Initial Catalog={this.Database};User ID={this.Uid};Password={this.Pad};Persist Security Info=True;TrustServerCertificate=True";  
        }

        #region 公有属性

        public SqlConnection SqlCon => SqlConAsync().Result;
        public async Task<SqlConnection> SqlConAsync() => await Task.Run(() => new SqlConnection(this.Contr));
        public string Contr { get; set; }
        public string Server { get; set; }
        public string Database { get; set; }
        public string Uid { get; set; }
        public string Pad { get; set; }

        #endregion

        #region 重写

        public override string ToString()
        {
            return Contr;
        }

        #endregion

        /// <summary>
        /// 测试数据库是否能成功过链接
        /// </summary>
        /// <returns></returns>
        public bool SqlTestLook()
        {
            try
            {
                var con = new SqlConnection(Contr);
                con.Open();
            }
            catch
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 返回查询的数据表集合
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet SqlDataSet(string sql)
        {
            SqlConnection con = new SqlConnection(Contr);
            DataSet ds = new DataSet();
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                da.Fill(ds);
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
                con.Dispose();
            }
        }
        /// <summary>
        /// 返回查询后的第一个表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable SqlDataTable(string sql)
        {
            SqlConnection con = new SqlConnection(Contr);
            DataSet ds = new DataSet();
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                da.Fill(ds);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                con.Dispose();
                ds.Dispose();
            }

        }

        public async Task<DataTable> SqlDataTableAsync(string sql) => await Task.Run(() => SqlDataTable(sql));

        /// <summary>
        /// 返回查询指定的表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="from"></param>
        /// <returns></returns>
        public DataTable SqlDataTable(string sql, int from)
        {
            SqlConnection con = new SqlConnection(Contr);
            DataSet ds = new DataSet();
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                da.Fill(ds);
                return ds.Tables[from];
            }
            catch
            {
                return null;
            }
            finally
            {
                con.Dispose();
                ds.Dispose();
            }

        }
        public async Task<object> SqlToOneObjectAsync(string sql) => await Task.Run(() => SqlToOneObject(sql));
        /// <summary>
        ///  返回数据第一行第一个值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object SqlToOneObject(string sql)
        {
            SqlConnection con = new SqlConnection(Contr);
            SqlCommand com = new SqlCommand(sql, con);
            try
            {
                con.Open();
                return com.ExecuteScalar();
            }
            catch
            {
                return null;
            }
            finally
            {
                con.Dispose();
                com.Dispose();
            }
        }
        /// <summary>
        /// 返回受影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int SqlToInt(string sql)
        {
            SqlConnection con = new SqlConnection(Contr);
            SqlCommand com = new SqlCommand(sql, con);
            try
            {
                con.Open();
                if (sql.Contains("insert") || sql.Contains("update") || sql.Contains("delete"))
                    return com.ExecuteNonQuery();
                else
                {
                    return SqlDataTable(sql).Rows.Count;
                }
            }
            catch
            {
                return 0;
            }
            finally
            {
                con.Dispose();
                com.Dispose();
            }
        }
        /// <summary>
        /// 返回执行结果
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool SqlBool(string sql)
        {
            SqlConnection con = new SqlConnection(Contr);
            SqlCommand com = new SqlCommand(sql, con);
            try
            {
                con.Open();
                com.ExecuteScalar();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                con.Dispose();
                com.Dispose();
            }
        }
        public async Task<bool> SqlBoolAsync(string sql) => await Task.Run(() => SqlBool(sql));

        /// <summary>
        /// 返回查询数据是否存在
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool SqlSelectValueBool(string sql)
        {
            if (sql.Contains("insert") || sql.Contains("update") || sql.Contains("delete"))
            {
                if (SqlToInt(sql) > 0)
                    return true;
            }
            else
            {
                if (SqlToOneObject(sql) != null)
                    return true;
            }
            return false;
        }
        /// <summary>
        /// 返回数据后输出的消息内容
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string SqlToPrint(string sql)
        {
            SqlConnection con = new SqlConnection(Contr);
            SqlCommand com = new SqlCommand(sql, con);
            try
            {
                string message = "";
                void InfoMessage_SqlInfoMessageEventHandler(object sender, SqlInfoMessageEventArgs e)
                {
                    message = e.Message;
                }
                con.InfoMessage += new SqlInfoMessageEventHandler(InfoMessage_SqlInfoMessageEventHandler);
                con.Open();
                com.ExecuteNonQuery();
                return message;
            }
            catch
            {
                return null;
            }
            finally
            {
                con.Dispose();
                com.Dispose();
            }
        }
        /// <summary>
        /// sql查询语句返回list数组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<T> SqlToList<T>(string sql) where T : new()
        {
            DataTable dt = SqlDataTable(sql);
            List<T> ts = DLL.DataTableList.ConvertToModel<T>(dt);
            return ts;
        }

        #region list 转数据库插入语句，并执行

        /// <summary>
        /// list 转数据库插入语句，并执行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool ListToSqlInsert<T>(List<T> t)
        {
            var props = typeof(T).GetProperties();

            string sql = string.Empty;

            string tableName = typeof(T).Name;

            string tableCloumns = string.Empty;
            foreach (PropertyInfo pi in props)
            {
                tableCloumns += pi.Name + ",";
            }
            tableCloumns = tableCloumns.Remove(tableCloumns.Length - 1);

            if (t.Count() > 0)
            {
                for (int i = 0; i < t.Count(); i++)
                {
                    string sqlInsert = "insert into {0} ({1})values({2}) \r\n";
                    string tableValue = string.Empty;
                    foreach (PropertyInfo pi in props)
                    {
                        object obj = pi.GetValue(t.ElementAt(i), null);
                        tableValue += string.Format("N'{0}',", obj?.ToString());
                    }
                    tableValue = tableValue.Remove(tableValue.Length - 1);
                    sql += string.Format(sqlInsert, tableName, tableCloumns, tableValue);
                }
            }
            return SqlBool(sql);
        }
        public bool ToSqlInsert<T>(T t)
        {
            var props = typeof(T).GetProperties();

            string sql = string.Empty;

            string tableName = typeof(T).Name;

            string tableCloumns = string.Empty;
            foreach (PropertyInfo pi in props)
            {
                tableCloumns += pi.Name + ",";
            }
            tableCloumns = tableCloumns.Remove(tableCloumns.Length - 1);


            string sqlInsert = "insert into {0} ({1})values({2}) \r\n";
            string tableValue = string.Empty;
            foreach (PropertyInfo pi in props)
            {
                object obj = pi.GetValue(t, null);
                tableValue += string.Format("N'{0}',", obj?.ToString());
            }
            tableValue = tableValue.Remove(tableValue.Length - 1);
            sql += string.Format(sqlInsert, tableName, tableCloumns, tableValue);

            return SqlBool(sql);
        }

        #endregion

        #region 参数化数据查询，返回第一个datatable 避免数据库注入

        /// <summary>
        /// 参数化数据查询，返回第一个datatable 避免数据库注入
        /// </summary>
        /// <param name="comText">数据库语句</param>
        /// <param name="id">参数id</param>
        /// <returns></returns>
        public DataTable ParameterDataTable(string comText, string id)
        {
            SqlCommand cmd = new SqlCommand
            {
                Connection = SqlCon,
                CommandText = comText
            };
            cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.NVarChar) { Value = id });
            cmd.ExecuteNonQuery();
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            ada.Fill(ds);
            return ds.Tables[0];
        }
        public DataTable ParameterDataTable(string comText, params Parameter[] parameters)
        {
            SqlCommand cmd = new SqlCommand
            {
                Connection = SqlCon,
                CommandText = comText
            };

            List<SqlParameter> sqlParameters = new List<SqlParameter>();
            foreach (var item in parameters)
            {
                sqlParameters.Add(new SqlParameter("@" + item.Name, SqlDbType.NVarChar) { Value = item.Value });
            }
            cmd.Parameters.AddRange(sqlParameters.ToArray());
            cmd.ExecuteNonQuery();
            SqlDataAdapter ada = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            ada.Fill(ds);
            return ds.Tables[0];
        }
        /// <summary>
        /// 数据库参数化声明
        /// </summary>
        public class Parameter
        {
            public Parameter()
            {
                Name = "id";
                Value = "";
            }
            public Parameter(string name, string value)
            {
                Name = name;
                Value = value;
            }
            public string Name { get; set; }
            public string Value { get; set; }
        }

        #endregion
    }
}