using System;
using System.Collections.Generic;
using System.Text;
using Dapper;
using MySql.Data.MySqlClient;
using Bailun.DC.Models.Menu;
using System.Linq;
using Bailun.DC.Models;

namespace Bailun.DC.Services
{
    /// <summary>
    /// 菜单管理服务
    /// </summary>
    public class MenuServices
    {
        public List<Models.Menu.dc_menu> ListMenu(string username)
        {
            var list = new List<dc_menu>();
            var obj = new List<dc_menu>();

            var sql = $@"select * from (select t1.id,t1.parentid,t1.icon,t1.`name`,t1.path,t1.sort from dc_menu t1 
                        join dc_menu_permission t2 on t1.id=t2.menuid and t2.delstatus=0 and t2.username='{username}'
                        where t1.delstatus=0
                        union all
                        select t1.id,t1.parentid,t1.icon,t1.`name`,t1.path,t1.sort from dc_menu t1 where t1.delstatus=0 and 1 = (select isall from dc_menu_permission where username='{username}' and isall=1 and delstatus=0)) tb order by parentid,sort";
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                obj = cn.Query<dc_menu>(sql, null, null, true, 2 * 60).Distinct().ToList();
            }

            foreach (var item in obj.Where(a=>a.parentid==0))
            {
                GetChildrens(item, obj);
                list.Add(item);
            }

            return list;
        }

        /// <summary>
        /// 根据用户id获取用户已授权菜单
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <returns></returns>
        public List<Models.Menu.dc_menu> ListMenu(int uid)
        {
            var list = new List<dc_menu>();
            var obj = new List<dc_menu>();

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var objRoles = cn.Query<dc_user_role_relation>("select t1.* from dc_user_role_relation t1 join dc_user_role t2 on t1.role_id=t2.id and t2.delstatus=0 where t1.delstatus=0 and t1.userid=" + uid);

                if (objRoles.Count() == 0)
                {
                    return list;
                }

                var sql = $@"select DISTINCT * from (select t1.id,t1.parentid,t1.icon,t1.`name`,t1.path,t1.sort from dc_menu t1 
                        join dc_menu_permission t2 on t1.id=t2.menuid and t2.delstatus=0 and t2.role_id in ({string.Join(",",objRoles.Select(a=>a.role_id))})
                        where t1.delstatus=0
                        union all
                        select t1.id,t1.parentid,t1.icon,t1.`name`,t1.path,t1.sort from dc_menu t1 where t1.delstatus=0 and 1 = (select isall from dc_menu_permission where role_id in ({string.Join(",", objRoles.Select(a => a.role_id))}) and isall=1 and delstatus=0 order by isall desc limit 1)) tb order by parentid,sort";


                obj = cn.Query<dc_menu>(sql, null, null, true, 2 * 60).ToList();

                foreach (var item in obj.Where(a => a.parentid == 0))
                {
                    GetChildrens(item, obj);
                    list.Add(item);
                }

                return list;
            }
        }

        /// <summary>
        /// 检测用户是否有权限访问菜单
        /// </summary>
        /// <param name="menuname">菜单名称</param>
        /// <param name="username">用户名</param>
        /// <returns></returns>
        public bool CheckPermission(string menuname, string username)
        {
            var sql = $@"select count(id) from (select t1.* from dc_menu t1 
                        join dc_menu_permission t2 on t1.id=t2.menuid and t2.delstatus=0 and t2.username='{username}'
                        where t1.delstatus=0 and t1.name='{menuname}'
                        union all
                        select * from dc_menu where 1 = (select isall from dc_menu_permission where username='{username}' and isall=1 and delstatus=0)) tb";

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var result = cn.QueryFirstOrDefault<int>(sql, null, null, 2 * 60);

                return result > 0;

            }
        }

        /// <summary>
        /// 获取所有菜单
        /// </summary>
        /// <returns></returns>
        public List<dc_menu> ListMenu()
        {
            var sql = "select * from dc_menu where delstatus=0 order by sort";

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Query<dc_menu>(sql).ToList();

                return obj;
            }
        }

        /// <summary>
        /// 获取角色的菜单
        /// </summary>
        /// <param name="roleid">角色id</param>
        /// <returns></returns>
        public List<dc_menu_permission> GetRoleMenu(int roleid)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Query<dc_menu_permission>("select * from dc_menu_permission where delstatus=0 and role_id="+roleid);

                return obj.ToList();
            }
        }

        /// <summary>
        /// 获取用户所在的角色
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public List<dc_user_role_relation> GetUserRoleRela(int uid)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var objRoles = cn.Query<dc_user_role_relation>("select t1.* from dc_user_role_relation t1 join dc_user_role t2 on t1.role_id=t2.id and t2.delstatus=0 where t1.delstatus=0 and t1.userid=" + uid);

                return objRoles.ToList();
            }
            
        }

        #region Private

        private void GetChildrens(dc_menu m, List<dc_menu> list)
        {
            m.childrens = new List<dc_menu>();
            var childrens = list.Where(a => a.parentid == m.id);
            if (childrens.Count() > 0)
            {
                foreach(var item in childrens)
                {
                    GetChildrens(item, list);
                    m.childrens.Add(item);
                }
            }
        }

        #endregion

        #region 用户角色

        /// <summary>
        /// 角色列表
        /// </summary>
        /// <param name="parameter">分页信息</param>
        /// <param name="total">符合条件的记录数</param>
        /// <returns></returns>
        public List<Models.Menu.dc_user_role> ListUserRole(BtTableParameter parameter,string name,ref int total)
        {
            var sqlparam = new DynamicParameters();
            var sql = "select * from dc_user_role where delstatus=0 ";

            if (!string.IsNullOrEmpty(name))
            {
                sql += " and name like @name";
                sqlparam.Add("name", "%"+name+"%");
            }

            if (!string.IsNullOrWhiteSpace(parameter.sort))
            {
                sql += " order by "+parameter.sort+" "+parameter.order;
            }

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Page<dc_user_role>(parameter.pageIndex, parameter.limit, sql, ref total,sqlparam);

                return obj.ToList();

            }

        }

        /// <summary>
        /// 获取所有角色列表
        /// </summary>
        /// <returns></returns>
        public List<dc_user_role> ListUserRole()
        {
            var sql = "select * from dc_user_role where delstatus=0 ";
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.Query<dc_user_role>(sql).ToList();

                return obj;
            }

        }

        public Models.Menu.dc_user_role GetUserRole(int id)
        {
            var sql = "select * from dc_user_role where delstatus=0 and id="+id;

            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var obj = cn.QueryFirstOrDefault<dc_user_role>(sql, null, null, 2 * 60);
                return obj;
            }
        }

        /// <summary>
        /// 保存角色
        /// </summary>
        /// <param name="m"></param>
        /// <param name="uid">当前用户id</param>
        /// <param name="username">当前用户名称</param>
        /// <returns></returns>
        public string SaveRole(Models.Menu.dc_user_role m,int uid,string username)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var o = new dc_user_role() {
                    createtime = DateTime.Now,
                    createuserid = uid,
                    createusername = username,

                    delstatus = 0
                };

                if (m.id > 0)
                {
                    o = cn.QueryFirstOrDefault<Models.Menu.dc_user_role>("select * from dc_user_role where id=" + m.id);

                    if (o == null)
                    {
                        o = new dc_user_role
                        {
                            createtime = DateTime.Now,
                            createuserid = uid,
                            createusername = username,

                            delstatus = 0
                        };
                    }
                }
                

                o.name = m.name;
                o.note = m.note;
                o.lastupdatetime = DateTime.Now;
                o.lastupdateuserid = uid;
                o.lastupdateusername = username;
                o.delstatus = 0;

                if (o.id > 0)
                {
                    var result = cn.Update<dc_user_role>(o);

                    return result > 0 ? "" : "更新异常，请重试！";
                }
                else
                {
                    var result = cn.Insert<dc_user_role>(o);

                    return result > 0 ? "" : "保存异常，请重试！";
                }
            }
        }

        /// <summary>
        /// 删除角色
        /// </summary>
        /// <param name="id"></param>
        /// <param name="uid"></param>
        /// <param name="username"></param>
        /// <returns></returns>
        public string DelRole(int id,int uid,string username)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var m = cn.QueryFirstOrDefault<Models.Menu.dc_user_role>("select * from dc_user_role where id=" + id);

                if (m == null)
                {
                    return "找不到该角色！";
                }

                m.delstatus = 1;
                m.lastupdatetime = DateTime.Now;
                m.lastupdateuserid = uid;
                m.lastupdateusername = username;

                var result = cn.Update<dc_user_role>(m);

                return result > 0 ? "" : "更新异常，请重试！";
            }

        }

        /// <summary>
        /// 添加角色用户关联
        /// </summary>
        /// <param name="roleid">角色id</param>
        /// <param name="userid">用户id</param>
        /// <param name="uid">当前用户id</param>
        /// <param name="username">当前用户名称</param>
        /// <returns></returns>
        public string SaveRoleUser(int roleid, int userid,string username, int current_uid, string current_username,ref int id)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var objRole = GetUserRole(roleid);
                if (objRole == null)
                {
                    return "找不到该角色。";
                }

                var m = cn.QueryFirstOrDefault<dc_user_role_relation>("select * from dc_user_role_relation where userid=" + userid + " and role_id=" + roleid);
                if (m == null)
                {
                    m = new dc_user_role_relation
                    {
                        createtime = DateTime.Now,
                        createuserid = current_uid,
                        createusername = current_username,
                        delstatus = 0
                    };
                }

                m.lastupdatetime = DateTime.Now;
                m.delstatus = 0;
                m.lastupdateuserid = current_uid;
                m.lastupdateusername = current_username;

                m.userid = userid;
                m.username = username;
                m.role_id = roleid;

                if (m.id > 0)
                {
                    id = m.id;
                    return cn.Update<dc_user_role_relation>(m) > 0 ? "" : "更新失败，请重试！";
                }
                else
                {
                    var result = cn.Insert<dc_user_role_relation>(m);
                    id = result??0;

                    return (result??0) > 0 ? "" : "保存失败，请重试！";
                }
            }
        }

        /// <summary>
        /// 删除角色里面的用户
        /// </summary>
        /// <param name="id">角色用户关系id</param>
        /// <param name="uid">当前用户id</param>
        /// <param name="username">当前用户名称</param>
        /// <returns></returns>
        public string DelRoleUser(int id,int uid,string username)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if(cn.State== System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var m = cn.QueryFirstOrDefault<dc_user_role_relation>("select * from dc_user_role_relation where delstatus=0 and id="+id);
                if (m == null)
                {
                    return "没有该数据，请重试！";
                }

                m.lastupdatetime = DateTime.Now;
                m.lastupdateuserid = uid;
                m.lastupdateusername = username;
                m.delstatus = 1;

                return cn.Update<dc_user_role_relation>(m)>0?"":"更新失败，请重试！";
            }

        }

        /// <summary>
        /// 获取角色的用户
        /// </summary>
        /// <param name="roleid">角色id</param>
        /// <returns></returns>
        public List<dc_user_role_relation> ListUserRoleRelation(int roleid)
        {
            using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                {
                    cn.Open();
                }

                var list = cn.Query<dc_user_role_relation>("select * from dc_user_role_relation where delstatus=0 and role_id=" + roleid).ToList();

                return list;
            }
        }

        /// <summary>
        /// 保存角色菜单
        /// </summary>
        /// <param name="roleid">角色id</param>
        /// <param name="s">菜单id，格式：id1|id2|...</param>
        /// <param name="uid">当前用户id</param>
        /// <param name="username">当前用户名称</param>
        /// <returns></returns>
        public string SaveRoleMenus(int roleid, string s, int uid, string username,int isall=0)
        {
            try
            {
                using (var cn = new MySqlConnection(Common.GlobalConfig.ConnectionString))
                {
                    if (cn.State == System.Data.ConnectionState.Closed)
                    {
                        cn.Open();
                    }

                    var objRole = GetUserRole(roleid);
                    if (objRole == null)
                    {
                        return "找不到该角色。";
                    }

                    cn.Execute("update dc_menu_permission set delstatus=1 where role_id=" + roleid);

                    //授权所有菜单都能访问
                    if (isall == 1)
                    {
                        var obj = cn.QueryFirstOrDefault<dc_menu_permission>("select * from dc_menu_permission where role_id=" + roleid + " and menuid=0 and username=''");
                        if (obj == null)
                        {
                            obj = new dc_menu_permission
                            {
                                delstatus = 0,
                                isall = 1,
                                menuid = 0,
                                role_id = roleid,
                                username = "",
                                lastupdatetime = DateTime.Now,
                                lastupdateuserid = uid,
                                lastupdateusername = username,
                            };
                        }
                        else
                        {
                            obj.delstatus = 0;
                            obj.isall = 1;
                            obj.menuid = 0;
                            obj.lastupdatetime = DateTime.Now;
                            obj.lastupdateuserid = uid;
                            obj.lastupdateusername = username;

                            obj.role_id = roleid;
                            obj.username = "";
                        }

                        if (obj.id > 0)
                        {
                            cn.Update<dc_menu_permission>(obj);
                        }
                        else
                        {
                            cn.Insert<dc_menu_permission>(obj);
                        }
                    }
                    else  //指定菜单
                    {
                        var arr = s.Split("|").Where(a => !string.IsNullOrEmpty(a)).ToList();

                        foreach (var item in arr)
                        {
                            var obj = cn.QueryFirstOrDefault<dc_menu_permission>("select * from dc_menu_permission where role_id=" + roleid + " and menuid=" + item);

                            if (obj == null)
                            {
                                obj = new dc_menu_permission
                                {
                                    delstatus = 0,
                                    isall = 0,
                                    menuid = int.Parse(item),
                                    role_id = roleid,
                                    username = "",
                                    lastupdatetime = DateTime.Now,
                                    lastupdateuserid = uid,
                                    lastupdateusername = username,
                                };
                            }
                            else
                            {
                                obj.delstatus = 0;
                                obj.isall = 0;
                                obj.menuid = int.Parse(item);
                                obj.lastupdatetime = DateTime.Now;
                                obj.lastupdateuserid = uid;
                                obj.lastupdateusername = username;

                                obj.role_id = roleid;
                                obj.username = "";
                            }

                            if (obj.id > 0)
                            {
                                cn.Update<dc_menu_permission>(obj);
                            }
                            else
                            {
                                cn.Insert<dc_menu_permission>(obj);
                            }
                        }
                    }
                }

                return "";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

        #endregion

    }
}
