using Newtonsoft.Json;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
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 Microsoft.EntityFrameworkCore.DbLoggerCategory.Database;

namespace WIDESEA_Comm.StoredProcedure
{
    public class EXECStoredProcedure
    {
        #region 调用添加的存储过程
        public static void EXECAdd<T>(T entity, string MethodName) where T : class
        {
            string ProcedureName = MethodName + entity.GetType().Name;
            if (StoredProcedureExists(DBServerProvider.GetConnectionString(), ProcedureName))
            {
                CreateStoredProcedure.Main(entity, MethodName);
            }
            using (SqlConnection conn = new SqlConnection(DBServerProvider.GetConnectionString()))
            {
                SqlCommand cmd = new SqlCommand(ProcedureName, conn);
                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();
            }
        }
        #endregion

        #region 调用修改的存储过程
        public static void EXECUpdate<T>(T entity, string MethodName) where T : class
        {
            string ProcedureName = MethodName + entity.GetType().Name;

            if (StoredProcedureExists(DBServerProvider.GetConnectionString(), ProcedureName))
            {
                CreateStoredProcedure.Main(entity, MethodName);
            }
            using (SqlConnection conn = new SqlConnection(DBServerProvider.GetConnectionString()))
            {
                SqlCommand cmd = new SqlCommand(ProcedureName, conn);
                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();
            }
        }
        #endregion


        #region 查询存储过程是否存在
        public static bool StoredProcedureExists(string connectionString, string storedProcedureName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                // 构建查询存储过程的SQL查询
                string query = @"SELECT COUNT(*) 
                             FROM INFORMATION_SCHEMA.ROUTINES 
                             WHERE SPECIFIC_NAME = @procedureName";

                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@procedureName", storedProcedureName);
                    int result = Convert.ToInt32(command.ExecuteScalar());

                    return result < 1;
                }
            }
        }
        #endregion

        #region 调用查询的存储过程  问题点！！！！！
        public static string GetUsersFromStoredProcedure<T>(T entity, string MethodName) where T : class
        {
            #region MyRegion
            List<T> users = new List<T>();
            List<string> roles = new List<string>();
            string ProcedureName = MethodName + entity.GetType().Name;
            if (StoredProcedureExists(DBServerProvider.GetConnectionString(), ProcedureName))
            {
                CreateStoredProcedure.Main(entity, MethodName);
            }
            using (SqlConnection conn = new SqlConnection(DBServerProvider.GetConnectionString()))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(ProcedureName, conn);
                cmd.CommandType = CommandType.StoredProcedure;

                PropertyInfo[] propertyInfos = typeof(T).GetProperties();
                foreach (PropertyInfo propertyInfo in propertyInfos)
                {
                    var ValueType = propertyInfo.GetColumnType(true).Value;
                    if (ValueType != "uniqueidentifier" && ValueType != "datetime")
                    {
                        var value = propertyInfo.GetValue(entity, null);
                        cmd.Parameters.AddWithValue("@" + propertyInfo.Name, value);
                    }
                }
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {

                    var a = reader.GetProviderSpecificValue(propertyInfos.Count() - 1);
                    for (int i = 0; i < propertyInfos.Count(); i++)
                    {
                        roles.Add(reader.GetValue(i).ToString());
                    }

                    return roles.ToString();
                    // 处理每一条记录
                    //var column1Value = reader["Column1"];
                    //var column2Value = reader["Column2"];
                    //var columnValues = reader.GetValues;
                    //var columnValue = reader.GetValue(0);
                    //var column1Value = reader.GetString(1);
                    //var column2Value = reader.GetString(2);

                    //Console.WriteLine($"Column1: {column1Value}, Column2: {column2Value}");
                }
                try
                {
                    //conn.Open();

                    //return cmd.ExecuteReader();
                    //using (var reader = cmd.ExecuteReader())
                    //{
                    //    //var readers = reader.ToString();
                    //    while (reader.Read())
                    //    {
                    //        var a = reader[$"id"];
                    //        var b = reader["describe"];
                    //    }
                    //}
                }
                catch (Exception)
                {

                    throw;
                }
                //var count = cmd.ExecuteNonQuery();
                //var dates = cmd.ExecuteReader();
            }
            #endregion

            //string ProcedureName = MethodName + entity.GetType().Name;//存储过程名称
            object data = entity.GetType();
            //var datas= JsonConvert.DeserializeObject<data>(reader);

            using (var connection = new SqlConnection(DBServerProvider.GetConnectionString()))
            {
                var command = new SqlCommand(ProcedureName, connection);
                command.CommandType = CommandType.StoredProcedure;

                try
                {
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        //return reader;
                        //return JsonConvert.DeserializeObject <data> (reader);
                        //return ((IObjectContextAdapter)connection).ObjectContext.Translate < entity.GetType().Name > (reader);//.ToList();
                    }
                }
                catch (Exception ex)
                {
                    // 处理异常
                    Console.WriteLine(ex.Message);
                    throw;
                }
            }

            return roles.ToString();
        }
        #endregion
    }
}