using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using WIDESEA_Core.DBManager;
using WIDESEA_Core.Extensions;
using static OfficeOpenXml.ExcelErrorValue;

namespace WIDESEA_Comm.StoredProcedure
{
    public class CreateStoredProcedure
    {
        /// <summary>
        /// 创建存储过程
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="MethodName"></param>
        public static void Main<T>(T entity, string MethodName) where T : class
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(DBServerProvider.GetConnectionString()))
                {
                    string ProcedureName = MethodName + entity.GetType().Name;


                    string sqlCommand = $"CREATE PROCEDURE {ProcedureName} ";
                    connection.Open();

                    if (MethodName == "Add")
                        sqlCommand += Add(entity);
                    else if (MethodName == "Delete")
                        sqlCommand += Delete(entity);
                    else if (MethodName == "Update")
                        sqlCommand += Update(entity);
                    else if (MethodName == "Query")
                        sqlCommand += Query(entity);

                    using (SqlCommand command = new SqlCommand(sqlCommand, connection))
                    {
                        #region MyRegion
                        command.ExecuteNonQuery();
                        //Console.WriteLine($"存储过程 {ProcedureName} 已创建。");
                        #endregion
                        //if (count < 1)
                        //    throw new Exception($"存储过程 {ProcedureName} 创建失败！");
                    }
                }
            }
            catch (Exception ex)
            {

            }
        }

        #region 添加的存储过程
        public static string Add<T>(T entity) where T : class
        {
            string sql = string.Empty;
            string values = string.Empty;
            string Params = string.Empty;

            PropertyInfo[] propertyInfos = typeof(T).GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfos)
            {
                var ValueType = propertyInfo.GetColumnType(true).Value;
                sql += sql == string.Empty ? $"@{propertyInfo.Name} {ValueType}" : $",@{propertyInfo.Name} {ValueType}";
                values += values == string.Empty ? propertyInfo.Name : "," + propertyInfo.Name;
                Params += Params == string.Empty ? "@" + propertyInfo.Name : ",@" + propertyInfo.Name;
            }
            return $"{sql} AS\nBEGIN\nINSERT INTO {entity.GetType().Name} ({values}) VALUES ({Params}) END";
        }
        #endregion

        #region 删除的存储过程
        public static string Delete<T>(T entity) where T : class
        {
            PropertyInfo[] propertyInfos = typeof(T).GetProperties();
            var key = propertyInfos.GetKeyName(true);
            var name = propertyInfos.GetKeyProperty().Name;
            var value = propertyInfos.GetKeyProperty().GetValue(entity, null);
            string sql = $"@{name} {key} AS\nBEGIN\nDELETE FROM {entity.GetType().Name} WHERE {name}='{value}' END GO";
            return sql;
        }
        #endregion

        #region 更新的存储过程
        public static string Update<T>(T entity) where T : class
        {
            string sql = string.Empty;
            string values = string.Empty;
            string Params = string.Empty;
            try
            {
                PropertyInfo[] propertyInfos = typeof(T).GetProperties();
                foreach (PropertyInfo propertyInfo in propertyInfos)
                {
                    var ValueType = propertyInfo.GetColumnType(true).Value;
                    sql += sql == string.Empty ? $"@{propertyInfo.Name} {ValueType}" : $",@{propertyInfo.Name} {ValueType}";
                    values += values == string.Empty ? propertyInfo.Name : "," + propertyInfo.Name;
                    Params += Params == string.Empty ? "@" + propertyInfo.Name : ",@" + propertyInfo.Name;
                }
            }
            catch (Exception ex)
            {

                throw;
            }
            return sql;
        }
        #endregion

        #region 查询的存储过程
        public static string Query<T>(T entity) where T : class
        {
            string sql = string.Empty;
            string values = string.Empty;
            string Params = string.Empty;

            PropertyInfo[] propertyInfos = typeof(T).GetProperties();
            foreach (PropertyInfo propertyInfo in propertyInfos)
            {
                var ValueType = propertyInfo.GetColumnType(true).Value;
                if (ValueType != "uniqueidentifier" && ValueType != "datetime")
                {
                    sql += sql == string.Empty ? $"@{propertyInfo.Name} {ValueType}" : $",@{propertyInfo.Name} {ValueType}";
                    values += values == string.Empty ? ($"{propertyInfo.Name}= {propertyInfo.Name}") : ($" AND {propertyInfo.Name}= {propertyInfo.Name}");
                    //values += values == string.Empty ? propertyInfo.Name : "," + propertyInfo.Name;
                    //Params += Params == string.Empty ? "@" + propertyInfo.Name : ",@" + propertyInfo.Name;
                }
            }
            return $"{sql} AS\nBEGIN\nSELECT * FROM {entity.GetType().Name} WHERE {values}  END";
        }
        #endregion


    }
}