using Infrastructure;
using LitJson;
using Model;
using Model.Custom;
using Model.Source;

namespace Util
{
    public class LogicNodeHelper
    {
        /// <summary>
        /// 如果-否则节点
        /// </summary>
        /// <param name="node">节点数据</param>
        /// <param name="paramList">参数列表</param>
        public static List<bool> ifElse(NodeList node, List<ParamList> paramList)
        {
            List<bool> ifElseResult = new List<bool>(); // 如果-否则节点结果
            JsonData nodeData = node.nodeData;
            JsonData ifCondition = nodeData["data"]["formData"]["ifelse"]["value"]; // 如果条件
            int ifIndex = 0; // 如果条件索引
            foreach (JsonData item in ifCondition)
            {
                string conditionExpression = ""; // 条件表达式
                string logicTag = item["logic"].ToString(); // 逻辑
                JsonData conditions = item["conditions"]; // 条件
                foreach (JsonData condition in conditions)
                {
                    string oper = condition["operator"].ToString(); // 运算符
                    string value = condition["value"].ToString(); // 值
                    string variable = condition["variable"].ToString(); // 变量
                    if (variable.Contains(".") && variable.Contains("output"))
                    {
                        variable = variable.Substring(variable.LastIndexOf(".") + 1);
                        var param = paramList.FirstOrDefault(x => x.paramName == variable);
                        if (param != null)
                        {
                            string paramValue = param.paramValue;
                            string paramType = param.paramType;
                            conditionExpression += operatorTransfer(param, oper, paramValue);
                            if (logicTag == "and")
                            {
                                conditionExpression += " && ";
                            }
                            else if (logicTag == "or")
                            {
                                conditionExpression += " || ";
                            }
                        }
                    }
                }
                if (conditionExpression.EndsWith(" && ") || conditionExpression.EndsWith(" || "))
                {
                    conditionExpression = conditionExpression.Substring(0, conditionExpression.Length - 4);
                }
                ifElseResult.Add(Utils.EvaluateBoolean(conditionExpression));
                ifIndex += 1;
            }
            return ifElseResult;
        }

        /// <summary>
        /// 数据库查询节点
        /// </summary>
        /// <param name="node">节点数据</param>
        /// <param name="paramList">参数列表</param>
        public static void dbQuery(NodeList node, DatabaseInfo dbData, List<DatabaseTable> tables, List<ParamList> paramList)
        { 
            JsonData nodeData = node.nodeData;
            JsonData dbInfo = nodeData["data"]["formData"]["dbLabel"]["value"]; // 数据库查询信息
            JsonData queryField = nodeData["data"]["formData"]["output"]["value"]; // 查询字段
            JsonData conditions = nodeData["data"]["formData"]["ifelse"]["value"][0]["conditions"]; // 条件
            JsonData sortConditions = nodeData["data"]["formData"]["sort"]["value"]; // 条件
            string queryLimit = nodeData["data"]["formData"]["queryLimit"]["value"].ToString(); // 查询上限
            string logicTag = nodeData["data"]["formData"]["ifelse"]["value"][0]["logic"].ToString(); // 逻辑
            string queryFieldString = "";
            foreach (JsonData field in queryField)
            {
                string fieldName = field["label"].ToString(); // 字段名
                queryFieldString += fieldName + ",";
            }
            if(string.IsNullOrEmpty(queryFieldString)) queryFieldString = "*";
            int dbTableId = int.Parse(dbInfo["dbTableId"].ToString()); // 数据库表ID
            var queryTable = tables.FirstOrDefault(x => x.id == dbTableId);
            if (queryTable != null)
            {
                string tableName = queryTable.tableName; // 数据库表名
                string sql = "select " + queryFieldString.TrimEnd(',') + " from " + tableName;
                if (conditions.Count > 0)
                {
                    sql += " where ";
                }
                foreach (JsonData condition in conditions)
                {
                    string oper = condition["operator"].ToString(); // 运算符
                    string value = condition["value"].ToString(); // 值
                    string variable = condition["variable"].ToString(); // 变量
                    if (variable.Contains(".") && variable.Contains("output"))
                    {
                        variable = variable.Substring(variable.LastIndexOf(".") + 1);
                        var param = paramList.FirstOrDefault(x => x.paramName == variable) ?? new ParamList();
                        string paramValue = param.paramValue;
                        sql += operatorTransferForSql(param, oper, paramValue, variable);
                        sql += " " + logicTag + " ";
                    }
                }
                if (sql.EndsWith(" " + logicTag + " "))
                {
                    sql = sql.Substring(0, sql.Length - logicTag.Length - 2);
                }
                if (sortConditions.Count > 0)
                {
                    sql += " order by ";
                    foreach (JsonData sortCondition in sortConditions)
                    {
                        string variable = sortCondition["variable"].ToString(); // 变量
                        if (variable.Contains("."))
                        {
                            variable = variable.Substring(variable.LastIndexOf(".") + 1);
                        }
                        string sort = sortCondition["sort"].ToString(); // 排序
                        sql += variable + " " + sort + ",";
                    }
                    sql = sql.TrimEnd(',');
                }
                if (!string.IsNullOrEmpty(queryLimit))
                {
                    sql += " limit " + queryLimit;
                }
                SqlSugarClient db = initDb(dbData);
                var items = db.Ado.GetDataTable(sql);
                if (items.Rows.Count > 0)
                { 
                    foreach (JsonData field in queryField)
                    {
                        string fieldName = field["label"].ToString(); // 字段名
                        paramList.Add(new ParamList()
                        {
                            nodeId = node.nodeId,
                            paramName = fieldName,
                            paramValue = items.Rows[0][fieldName].ToString(),
                            paramType = field["type"][0].ToString(),
                        });
                    }
                }
            }
        }

        /// <summary>
        /// 数据库SQL查询节点
        /// </summary>
        /// <param name="node">节点数据</param>
        /// <param name="paramList">参数列表</param>
        public static void dbSqlQuery(NodeList node, DatabaseInfo dbData, List<ParamList> paramList)
        { 
            JsonData nodeData = node.nodeData;
            JsonData dbInfo = nodeData["data"]["formData"]["dbLabel"]["value"]; // 数据库查询信息
            JsonData queryField = nodeData["data"]["formData"]["output"]["value"]; // 查询字段
            string sql = nodeData["data"]["formData"]["sql"]["value"]["code"]["content"].ToString(); // SQL查询语句
            SqlSugarClient db = initDb(dbData);
            var items = db.Ado.GetDataTable(sql);
            if (items.Rows.Count > 0)
            {  
                foreach (JsonData field in queryField)
                {
                    string fieldName = field["label"].ToString(); // 字段名
                    paramList.Add(new ParamList()
                    {
                        nodeId = node.nodeId,
                        paramName = fieldName,
                        paramValue = items.Rows[0][fieldName].ToString(),
                        paramType = field["type"][0].ToString(),
                    });
                }
            }
        }

        public static SqlSugarClient initDb(DatabaseInfo dbInfo)
        {
            string server = dbInfo.hostAddress;
            int port = dbInfo.port;
            string user = dbInfo.username;
            string password = dbInfo.pwd;
            string database = dbInfo.dbName;
            DbType dbType = DbType.MySql;
            var db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = "server=" + server + ";port=" + port + ";user=" + user + ";password=" + password + ";database=" + database + ";charset=utf8;",
                DbType = dbType,
                IsAutoCloseConnection = true,
            });
            return db;
        }

        /// <summary>
        /// 运算符转换
        /// </summary>
        /// <param name="oper">运算符</param>
        /// <returns>转换后的运算符</returns>
        public static string operatorTransfer(ParamList param, string oper, string paramValue)
        {
            if (oper == "equal") //等于
            { 
                if(param.paramType == "string" || param.paramType == "time")
                {
                    return "'" + param.paramValue + "' == '" + paramValue + "'";
                }
                return param.paramValue + " == " + paramValue;
            }
            else if (oper == "not_equal") //不等于
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    return "'" + param.paramValue + "' != '" + paramValue + "'";
                }
                return param.paramValue + " != " + paramValue;
            }
            else if (oper == "greater") //大于
            {
                return param.paramValue + " > " + paramValue;
            }
            else if (oper == "greater_equal") //大于等于
            {
                return param.paramValue + " >= " + paramValue;
            }
            else if (oper == "less") //小于
            {
                return param.paramValue + " < " + paramValue;
            }
            else if (oper == "less_equal") //小于等于
            {
                return param.paramValue + " <= " + paramValue;
            }
            else if (oper == "between") //在之间
            {
                string[] betweenValues = paramValue.Split(',');
                return param.paramValue + " >= " + betweenValues[0] + " && " + param.paramValue + " <= " + betweenValues[1];
            }
            else if (oper == "not_between") //不在之间
            {
                string[] betweenValues = paramValue.Split(',');
                return "(" + param.paramValue + " < " + betweenValues[0] + " || " + param.paramValue + " > " + betweenValues[1] + ")";
            }
            else if (oper == "contains") //包含
            {
                return "Contains('" + param.paramValue + "', '" + paramValue + "')";
            }
            else if (oper == "not_contains") //不包含
            {
                return "!Contains('" + param.paramValue + "', '" + paramValue + "')";
            }
            else if (oper == "starts_with") //以...开头
            {
                return "StartsWith('" + param.paramValue + "', '" + paramValue + "')";
            }
            else if (oper == "ends_with") //以...结尾
            {
                return "EndsWith('" + param.paramValue + "', '" + paramValue + "')";
            }
            else if (oper == "matches_regex") //匹配正则表达式
            {
                return "Match('" + param.paramValue + "', '" + paramValue + "')";
            }
            else if (oper == "not_matches_regex") //不匹配正则表达式
            {
                return "!Match('" + param.paramValue + "', '" + paramValue + "')";
            }
            else if (oper == "is_empty")
            {
                return "IsNullOrEmpty('" + param.paramValue + "')";
            }
            else if (oper == "is_not_empty") //不为空
            {
                return "!" + "IsNullOrEmpty('" + param.paramValue + "')";
            }
            else if (oper == "is_null") //为空
            {
                return "('" + param.paramValue + "'=='{}' || '" + param.paramValue + "'=='[]')";
            }
            else if (oper == "is_not_null") //不为空
            {
                return "('" + param.paramValue + "'!='{}' || '" + param.paramValue + "'!='[]')";
            }
            else if (oper == "in") //在...中
            {
                return "In('" + param.paramValue + "', '" + paramValue + "')";
            }
            else if (oper == "not_in") //不在...中
            {
                return "!In('" + param.paramValue + "', '" + paramValue + "')";
            }
            else if (oper == "array_contains") //数组包含
            {
                return "In('" + param.paramValue + "', '" + paramValue + "')";
            }
            else if (oper == "array_not_contains") //数组不包含 
            {
                return "!In('" + param.paramValue + "', '" + paramValue + "')";
            }
            else if (oper == "array_length_equal") //数组长度等于
            {
                return "ArrayLen('" + param.paramValue + "') == " + paramValue;
            }
            else if (oper == "array_length_greater")
            {
                return "ArrayLen('" + param.paramValue + "') > " + paramValue;
            }
            else if (oper == "array_length_less") //数组长度小于
            {
                return "ArrayLen('" + param.paramValue + "') < " + paramValue;
            }
            else if (oper == "is_true") //为真
            {
                return param.paramValue + " == true";
            }
            else if (oper == "is_false") //为假
            {
                return param.paramValue + " == false";
            }
            else if (oper == "date_equal") //日期等于
            {
                return "'" + param.paramValue + "' == '" + paramValue + "'";
            }
            else if (oper == "date_before") //日期之前
            {
                return "'" + param.paramValue + "' < '" + paramValue + "'";
            }
            else if (oper == "date_after") //日期之后
            {
                return "'" + param.paramValue + "' > '" + paramValue + "'";
            }
            else if (oper == "date_between") //日期之间
            {
                string[] betweenValues = paramValue.Split(" - ", StringSplitOptions.None);
                return "'" + param.paramValue + "' >= '" + betweenValues[0] + "' && '" + param.paramValue + "' <= '" + betweenValues[1] + "'";
            }
            return "";
        }
    
        /// <summary>
        /// 运算符转换（SQL）
        /// </summary>
        /// <param name="oper">运算符</param>
        /// <returns>转换后的运算符</returns>
        public static string operatorTransferForSql(ParamList param, string oper, string paramValue, string fieldName)
        {
            if (oper == "equal") //等于
            { 
                if(param.paramType == "string" || param.paramType == "time")
                {
                    return fieldName + "='" + paramValue + "'";
                }
                return fieldName + "=" + paramValue;
            }
            else if (oper == "not_equal") //不等于
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    return fieldName + "!='" + paramValue + "'";
                }
                return fieldName + "!=" + paramValue;
            }
            else if (oper == "greater") //大于
            {
                return fieldName + ">" + paramValue;
            }
            else if (oper == "greater_equal") //大于等于
            {
                return fieldName + " >= " + paramValue;
            }
            else if (oper == "less") //小于
            {
                return fieldName + " < " + paramValue;
            }
            else if (oper == "less_equal") //小于等于
            {
                return fieldName + " <= " + paramValue;
            }
            else if (oper == "between") //在之间
            {
                string[] betweenValues = paramValue.Split(',');
                return fieldName + " >= " + betweenValues[0] + " and " + fieldName + " <= " + betweenValues[1];
            }
            else if (oper == "not_between") //不在之间
            {
                string[] betweenValues = paramValue.Split(',');
                return "(" + fieldName + " < " + betweenValues[0] + " or " + fieldName + " > " + betweenValues[1] + ")";
            }
            else if (oper == "contains") //包含
            {
                return fieldName + " like '%" + paramValue + "%'";
            }
            else if (oper == "not_contains") //不包含
            {
                return fieldName + " not like '%" + paramValue + "%'";
            }
            else if (oper == "starts_with") //以...开头
            {
                return fieldName + " like '" + paramValue + "%'";
            }
            else if (oper == "ends_with") //以...结尾
            {
                return fieldName + " like '%" + paramValue + "'";
            }
            else if (oper == "is_empty")
            {
                return fieldName + " is null";
            }
            else if (oper == "is_not_empty") //不为空
            {
                return fieldName + " is not null";
            }
            else if (oper == "is_null") //为空
            {
                return fieldName + " is null";
            }
            else if (oper == "is_not_null") //不为空
            {
                return fieldName + " is not null";
            }
            else if (oper == "in") //在...中
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    paramValue = paramValue.Replace(",", "','");
                    return fieldName + " in ('" + paramValue + "')";
                }
                return fieldName + " in (" + paramValue + ")";
            }
            else if (oper == "not_in") //不在...中
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    paramValue = paramValue.Replace(",", "','");
                    return fieldName + " in ('" + paramValue + "')";
                }
                return fieldName + " not in (" + paramValue + ")";
            }
            else if (oper == "array_contains") //数组包含
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    paramValue = paramValue.Replace(",", "','");
                    return fieldName + " in ('" + paramValue + "')";
                }
                return fieldName + " in (" + paramValue + ")";
            }
            else if (oper == "array_not_contains") //数组不包含 
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    paramValue = paramValue.Replace(",", "','");
                    return fieldName + " not in ('" + paramValue + "')";
                }
                return fieldName + " not in (" + paramValue + ")";
            }
            else if (oper == "array_length_equal") //数组长度等于
            {
                return "Length(" + fieldName + ")=" + paramValue;
            }
            else if (oper == "array_length_greater")
            {
                return "Length(" + fieldName + ") > " + paramValue;
            }
            else if (oper == "array_length_less") //数组长度小于
            {
                return "Length(" + fieldName + ") < " + paramValue;
            }
            else if (oper == "is_true") //为真
            {
                return fieldName + "=true";
            }
            else if (oper == "is_false") //为假
            {
                return fieldName + "=false";
            }
            else if (oper == "date_equal") //日期等于
            {
                return fieldName + "='" + paramValue + "'";
            }
            else if (oper == "date_before") //日期之前
            {
                return fieldName + " < '" + paramValue + "'";
            }
            else if (oper == "date_after") //日期之后
            {
                return fieldName + " > '" + paramValue + "'";
            }
            else if (oper == "date_between") //日期之间
            {
                string[] betweenValues = paramValue.Split(" - ", StringSplitOptions.None);
                return fieldName + " >= '" + betweenValues[0] + "' and " + fieldName + " <= '" + betweenValues[1] + "'";
            }
            return "";
        }
    
    }
}