using FreeSql;
using Newtonsoft.Json;
using OfficeOpenXml.FormulaParsing.Excel.Functions.DateTime;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Text;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using WIDESEA_Comm.DBItem;
using WIDESEA_Core.DBManager;
using WIDESEA_Core.Extensions;
using static Dapper.SqlMapper;

namespace WIDESEA_WCS.JobsPart.Common
{
    public class StoredProcedure
    {
        /// <summary>
        /// 创建存储过程
        /// </summary>
        public static void Main<T>(T entity, string MethodName) where T : class
        {
            //string storedProcedureName = "MyNewStoredProcedure";

            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);
                if (MethodName == "Delete")
                    sqlCommand += Delete(entity);
                // 创建存储过程的SQL脚本
                //string sqlCommand = $@"
                //CREATE PROCEDURE {ProcedureName}
                //AS
                //BEGIN
                //SELECT * FROM {TableName}
                //END";

                using (SqlCommand command = new SqlCommand(sqlCommand, connection))
                {
                    command.ExecuteNonQuery();
                    Console.WriteLine($"存储过程 {ProcedureName} 已创建。");
                }
            }
        }

        #region 添加的存储过程
        public static string Add<T>(T entity) where T : class
        {

            //string sqlCommand = $"CREATE PROCEDURE {ProcedureName} ";

            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;
                //sqlCommand += $@"@{propertyInfo.Name} {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;
                //var name = "@" + propertyInfo.Name;
                //var value = propertyInfo.GetValue(entity, null);
            }
            //sqlCommand += $"{sql} AS\nBEGIN\nINSERT INTO {entity.GetType().Name} ({values}) VALUES ({Params}) END GO";


            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 调用存储过程
        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="ProcedureName">存储过程名称</param>
        /// <param name="Param1">参数1</param>
        public static void EXEC(string ProcedureName, object Param1)
        {
            using (SqlConnection conn = new SqlConnection(DBServerProvider.GetConnectionString()))
            {
                SqlCommand cmd = new SqlCommand("DeleteOldRecords", conn);//存储过程名称
                cmd.CommandType = CommandType.StoredProcedure;
                //PropertyInfo[] propertyInfos = typeof(Param1).GetProperties();
                //cmd.Parameters.AddWithValue("@Param1", "Value1");//参数1
                cmd.Parameters.AddWithValue("@MonthsAgo", 6);//参数2
                conn.Open();
                var count = cmd.ExecuteNonQuery();
                if (count > 0)
                {

                }
            }
        }
        #endregion

        /// <summary>
        /// 调用添加存储过程
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="ProcedureName">存储过程名称</param>
        public static void EXECAdd<T>(T entity, string MethodName) where T : class
        {
            string ProcedureName = MethodName + entity.GetType().Name;
            using (SqlConnection conn = new SqlConnection(DBServerProvider.GetConnectionString()))
            {
                SqlCommand cmd = new SqlCommand(ProcedureName, conn);//存储过程名称"Add_inventory"
                cmd.CommandType = CommandType.StoredProcedure;

                PropertyInfo[] propertyInfos = typeof(T).GetProperties();
                foreach (PropertyInfo propertyInfo in propertyInfos)
                {
                    var value = propertyInfo.GetValue(entity, null);
                    cmd.Parameters.AddWithValue("@" + propertyInfo.Name, value);//参数
                }

                conn.Open();
                cmd.ExecuteNonQuery();
                //var count = cmd.ExecuteNonQuery();
                //if (count > 0)
                //{
                //    Console.WriteLine($"存储过程 {ProcedureName} 调用成功。");
                //}
            }
        }
    }
}