using ClosedXML.Excel;
using IronPython.Hosting;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Http.HttpResults;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.Extensions.Caching.Memory;
using MySqlConnector;
using NLog;
using System.Data;
using System.Text;
using ViewModels;
using WebAPIServer.Common;
using WebAPIServer.Models;

namespace WebAPIServer.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class DataHandleController : ControllerBase
    {

        private readonly Logger _logger = LogManager.GetCurrentClassLogger();
        public readonly IMemoryCache _memoryCache;

        public DataHandleController(IMemoryCache memoryCache)
        {
            this._memoryCache = memoryCache;
        }
        [HttpPost()]
        public string NNN()
        {
            return "hello";
        }
        [HttpPost()]
        [Authorize()]
        public ReturnInfo R_InfoCommon([FromBody] PageQueryData S)
        {
            ReturnInfo returnInfo = new ReturnInfo();

            try
            {
                List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();

                string KKK = "";
                if (S.DBQueryData.Count > 0)
                {
                    KKK = string.Join('_', S.DBQueryData);
                }
                string NK = S.Key + S.CmdType??"" + KKK;
                var ooo = _memoryCache.Get<List<Dictionary<string, object>>>(NK);
                if (ooo != null)
                {
                    list = ooo;
                }
                else
                {

                    using (var q = new UtsManageContext())
                    {
                        using IDbConnection conn = q.Database.GetDbConnection();

                        if (conn.State != ConnectionState.Open)
                        {
                            conn.Open();
                        }
                        IDbCommand cmd = conn.CreateCommand();
                        string VariableName = S.Key;
                        string result = "";
                        if (S.isFunction)
                        {
                            if (S.FunParameters.Count == 1)
                            {
                                result = StaticData.scope.GetVariable(VariableName)(S.FunParameters[0]);
                            }
                            if (S.FunParameters.Count == 2)
                            {
                                result = StaticData.scope.GetVariable(VariableName)(S.FunParameters[0], S.FunParameters[1]);
                            }
                            if (S.FunParameters.Count == 3)
                            {
                                result = StaticData.scope.GetVariable(VariableName)(S.FunParameters[0], S.FunParameters[1], S.FunParameters[2]);
                            }
                            if (S.FunParameters.Count == 4)
                            {
                                result = StaticData.scope.GetVariable(VariableName)(S.FunParameters[0], S.FunParameters[1], S.FunParameters[2], S.FunParameters[3]);
                            }
                            if (S.FunParameters.Count == 5)
                            {
                                result = StaticData.scope.GetVariable(VariableName)(S.FunParameters[0], S.FunParameters[1], S.FunParameters[2], S.FunParameters[3], S.FunParameters[4]);
                            }
                        }
                        else
                        {
                            result = StaticData.scope.GetVariable(VariableName);
                        }
                        var Q = S.DBQueryData;
                        if (S.CmdType.Equals("sys"))
                        {
                            foreach (var item in Q)
                            {
                                result = result.Replace(item.Key, item.Value);
                            }
                        }
                        else
                        {
                            foreach (QueryString item in Q)
                            {
                                if (item.ValueType.Equals("string"))
                                {
                                    MySqlParameter mmm = new MySqlParameter(item.Key, item.Value);
                                    cmd.Parameters.Add(mmm);
                                }
                                else if (item.ValueType.Equals("number"))
                                {
                                    decimal a = 0;
                                    decimal.TryParse(item.Value, out a);
                                    MySqlParameter mmm = new MySqlParameter(item.Key, a);
                                    cmd.Parameters.Add(mmm);
                                }
                            }
                        }

                        cmd.CommandText = result;


                        cmd.CommandType = CommandType.Text;

                        MySqlCommand command = cmd as MySqlCommand;
                        MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);

                        DataTable dt = ds.Tables[0];

                        //string str= System.Text.Json.JsonSerializer.Serialize(dt);

                        foreach (DataRow item in dt.Rows)
                        {
                            Dictionary<string, object> dic = new Dictionary<string, object>();
                            foreach (DataColumn item1 in dt.Columns)
                            {
                                string CName = item1.ColumnName;
                                dic.Add(CName, item[CName]);
                            }
                            list.Add(dic);
                        }
                    }
                }
                returnInfo.isok = true;
                returnInfo.response = list;

            }
            catch (Exception ex)
            {
                returnInfo.isok = false;
                returnInfo.message = ex.Message;
            }
            return returnInfo;
        }


        [HttpPost()]
        [Authorize()]
        public ReturnInfo CUD_InfoCommon([FromBody] PageQueryData S)
        {
            ReturnInfo returnInfo = new ReturnInfo();

            try
            {
                using (var q = new UtsManageContext())
                {
                    using IDbConnection conn = q.Database.GetDbConnection();

                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    IDbCommand cmd = conn.CreateCommand();
                    string VariableName = S.Key;
                    string result = "";
                    if (S.isFunction)
                    {
                        var LG = S.FunParameters;
                        result = StaticData.scope.GetVariable(VariableName)(S.FunParameters);
                    }
                    else
                    {
                        result = StaticData.scope.GetVariable(VariableName);
                    }
                    var Q = S.DBQueryData;
                    if (S.CmdType.Equals("sys"))
                    {
                        foreach (var item in Q)
                        {
                            result = result.Replace(item.Key, item.Value);
                        }
                    }
                    else
                    {
                        foreach (QueryString item in Q)
                        {
                            if (item.ValueType.Equals("string"))
                            {
                                MySqlParameter mmm = new MySqlParameter(item.Key, item.Value);
                                cmd.Parameters.Add(mmm);
                            }
                            else if (item.ValueType.Equals("number"))
                            {
                                decimal a = 0;
                                decimal.TryParse(item.Value, out a);
                                MySqlParameter mmm = new MySqlParameter(item.Key, a);
                                cmd.Parameters.Add(mmm);
                            }
                        }
                    }

                    cmd.CommandText = result;
                    cmd.CommandType = CommandType.Text;

                    object ooo = cmd.ExecuteNonQuery();
                    returnInfo.response = ooo;
                    returnInfo.isok = true;
                }
            }
            catch (Exception ex)
            {
                returnInfo.isok = false;
                returnInfo.message = ex.Message;
            }
            return returnInfo;
        }

        [HttpPost()]
        public ReturnInfo III([FromBody] PageQueryData S)
        {
            var NNN = S.FunParameters;
            var result = StaticData.scope.GetVariable("a")(NNN);
            return null;
        }

    }



    public record PageQueryData
    {
        public string Key { get; set; }
        public bool isFunction { get; set; } = false;

        /// <summary>
        /// 参数，最多直接5个参数
        /// </summary>
        public List<string>? FunParameters { get; set; }
        //sys
        //customer
        public string? CmdType { get; set; }
        public List<QueryString>? DBQueryData { get; set; }


    }
    public class FunParameter
    {
        public string PKey { get; set; }
        public string PValue { get; set; }
    }
    public record QueryString
    {
        public string Key { get; set; }
        public string Value { get; set; }
        public string ValueType { get; set; }
    }
}