using HMI.Core.Util;
using HMI.MenuManage.Entity;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;

namespace HMI.MenuManage.Util
{
    public class UserRoleRelHelper
    {
        /// <summary>
        /// 根据roleCode获取rel数据
        /// </summary>
        /// <param name="sqliteHelper"></param>
        /// <param name="roleCode"></param>
        /// <returns></returns>
        public static ObservableCollection<UserRoleRel> SelectUserRoleData(SqliteHelper sqliteHelper, string username)
        {
            ObservableCollection<UserRoleRel> datas = new ObservableCollection<UserRoleRel>();
            DataTable dt = sqliteHelper.GetDataTable($@"SELECT r.*,t.username FROM role AS r LEFT JOIN 
(SELECT u.username,rel.roleCode FROM user AS u INNER JOIN user_role_rel AS rel ON u.username = rel.username
AND u.username='{username}') AS t
ON r.code = t.roleCode ORDER BY r.id;");
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    var role = new UserRoleRel
                    {
                        Id = dr["id"].ToString(),
                        RoleCode = dr["code"].ToString(),
                        RoleName = dr["name"].ToString(),
                        Selected = !(dr["username"] is DBNull)
                    };
                    datas.Add(role);
                }
            }
            return datas;
        }

        public static void SaveUserRoleRel(SqliteHelper sqliteHelper, IEnumerable<UserRoleRel> roles,
            UserData data, string firstSql, string lastSql)
        {
            var rels = roles.Where(d => d.Selected).ToList();
            var sqls = new List<string> {
                firstSql,
                $"DELETE FROM user_role_rel WHERE username = '{data.Username}';",
            };
            foreach (var rel in rels)
            {
                sqls.Add($"INSERT INTO user_role_rel (username,roleCode) " +
                    $"VALUES ('{data.Username}','{rel.RoleCode}');");
            }
            sqls.Add(lastSql);
            sqliteHelper.ExecuteBatchSql(sqls);
        }
    }
}