using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using P;
using IMLibrary;
using Ourmsg.Enum; 
using Ourmsg.Server.MemoryObject;
using Ourmsg.Server.BaseObject;

namespace Ourmsg.Server
{
    public class DBO
    {
        #region 获得用户登录认证基本信息
        /// <summary>
        /// 获得用户登录认证基本信息
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public static Auth GetUserAuth(string authID)
        {
            List<Auth> userAuths = null;

            if (IsNumberic(authID))//查找邮件表
            {
                if (authID.Length == 11)//如果是电话号码
                    userAuths = DBAccess.Select<Auth>(string.Format("select * from Users where userID in(select userID from UserPhone where UserPhone = '{0}') ;", authID));
                else if (IsNumberic(authID))//查找基础表
                    userAuths = DBAccess.Select<Auth>(string.Format("select * from Users where userID={0};", authID));
            }
            else if (authID.Contains('@'))//查找邮件表
                userAuths = DBAccess.Select<Auth>(string.Format("select * from Users where  userID in(select userID from userEmail where  UserEmail='{0}'); ", authID));

            if (userAuths.Count > 0)
                return userAuths[0];
            return null;
        }
        #endregion

        #region 获取用户动态密码
        /// <summary>
        /// 获取用户动态密码
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static UserDynPassword GetUserDynPassword(UInt32 UserID)
        { 
            var sql = string.Format("select DynPassword from Users where  userID={0}", UserID);
            var   dynPasswords = DBAccess.Select<UserDynPassword>(sql);
            if (dynPasswords.Count > 0)
                return dynPasswords[0];
            else
                return null;
        }
        #endregion

        #region 更新登录信息
        /// <summary>
        /// 更新登录信息
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="dynPassword"></param>
        /// <param name="ip"></param>
        /// <param name="showType"></param>
        /// <returns></returns>
        public static int UpdateLoginInfo(UInt32 userID, string dynPassword, string ip, ShowType showType)
        {
            var sql = string.Format("update users set DynPassword='{0}',LastLoginIP='{1}',LastLoginTime='{2}',ShowType={3} where userID={4};",
                                   dynPassword, ip, DateTime.Now.ToString(), (byte)showType, userID);
            return DBAccess.ExecSql(sql, sql); 
        }
        #endregion

        #region 将用户设置为离线
        /// <summary>
        /// 将用户设置为离线
        /// </summary>
        /// <param name="userID"></param>
        public static int SetUserOffline(UInt32 userID)
        {
            var dynPassword = new Random().Next(100000, 100000000).ToString();
            string sql = string.Format("update users set ShowType=0,dynPassword='{1}' where userID={0}", userID, dynPassword);
            return DBAccess.ExecSql(sql, sql);
        }
        #endregion

        #region 获取好友分组信息
        /// <summary>
        /// 获取好友分组信息
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public static List<Group> GetGroups(UInt32 userID)
        {
            string sql = string.Format("select *,IsSystem=1 from SysGroups");
            var sysGroup = DBAccess.Select<Group>(sql);
            sql = string.Format("select * from UserGroups where userID={0} ", userID);
            var userGroup = DBAccess.Select<Group>(sql);
            sysGroup.AddRange(userGroup);
            return sysGroup;
        }
        #endregion

        #region 获取群分组信息
        /// <summary>
        /// 获取群分组信息
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public static List<RoomGroup> GetRoomGroups(UInt32 userID)
        { 
            var sql = string.Format("select * from  UserRoomGroups  where userID={0} ", userID);
            return DBAccess.Select<RoomGroup>(sql); 
        }
        #endregion

        #region 获取好友基本信息（含在线状态）
        /// <summary>
        /// 获取好友基本信息（含在线状态）
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public static List<FriendBaseInfo> GetFriendBaseInfos(UInt32 userID)
        {
            string sql = string.Format("select u.*,f.GroupID,f.RemarkName from Users u,UserFriends f where u.userID=f.friendID and f.userID={0}", userID);
            var fs = DBAccess.Select<FriendBaseInfo>(sql);
              sql = string.Format("select * from users where SysUser=1");
            fs.AddRange(DBAccess.Select<FriendBaseInfo>(sql));
            return fs;
        }

        /// <summary>
        /// 获取用户和一个好友的基本信息
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="friendID"></param>
        /// <returns></returns>
        public static List<FriendBaseInfo> GetUserAndFriendBaseInfos(UInt32 userID, UInt32 friendID)
        {
            var id1 = userID + "_" + friendID;
            var id2 = friendID + "_" + userID; 
            string sql = string.Format("select u.*,f.GroupID,f.RemarkName,f.RemarkName from Users u,UserFriends f where u.userID=f.friendID and f.ID in('{0}','{1}')", id1,id2);
            return DBAccess.Select<FriendBaseInfo>(sql);
        }
        #endregion

        #region 获取用户基本信息（含在线状态）
        /// <summary>
        /// 获取用户基本信息（含在线状态）
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public static FriendBaseInfo GetUserBaseInfo(UInt32 userID)
        {
            string sql = string.Format("select * from Users where UserID={0} ", userID);
            var users = DBAccess.Select<FriendBaseInfo>(sql);
            if (users.Count > 0)
                return users[0];
            else
                return null;
        }
        #endregion

        #region 获取群基本信息
        /// <summary>
        /// 获取群基本信息
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public static List<RoomBaseInfo> GetRoomBaseInfos(UInt32 userID)
        {
            string sql = string.Format("select * from Rooms where roomID in(select roomID from RoomUsers where UserID={0})", userID);
            return DBAccess.Select<RoomBaseInfo>(sql);
        }
        #endregion

        #region 设置群管理员
        /// <summary>
        /// 设置群管理员
        /// </summary>
        /// <param name="roomID">群号</param>
        /// <param name="userID">用户ID</param>
        /// <param name="admin"></param>
        /// <returns></returns>
        public static int SetRoomAdmin(UInt32 roomID, UInt32 userID, bool admin)
        {
            var id = roomID + "_" + userID;
            var sql = string.Format("update RoomUsers set admin={1} where ID='{0}';", id, admin ? 1 : 0);
            return DBAccess.ExecSql(sql, sql);
        }
        #endregion

        #region 设置群用户名片
        /// <summary>
        /// 设置群用户名片
        /// </summary>
        /// <param name="roomID">群号</param>
        /// <param name="userID">用户ID</param>
        /// <param name="userCard"></param>
        /// <returns></returns>
        public static int SetUserVcard(UInt32 roomID, UInt32 userID,string userCard)
        {
            var id = roomID + "_" + userID;
            var sql = string.Format("update RoomUsers set UserCard='{1}' where ID='{0}';", id, userCard);
            return DBAccess.ExecSql(sql, sql);
        }
        #endregion

        #region 设置群基本信息
        /// <summary>
        /// 设置群基本信息
        /// </summary>
        /// <param name="roomID"></param>
        /// <param name="roomName"></param>
        /// <param name="addCondition"></param>
        /// <param name="notice"></param>
        /// <param name="remark"></param>
        /// <returns></returns>
        public static int SetRoomBaseInfo(UInt32 roomID, string roomName, byte addCondition, string notice, string remark)
        { 
            var sql = string.Format("update Rooms set roomName='{1}',addCondition={2},notice='{3}',remark='{4}' where RoomID={0};",
                                    roomID, roomName, addCondition, notice, remark);
            return DBAccess.ExecSql(sql, sql);
        }
        #endregion
        
        #region 获得用户群缓存列表
        /// <summary>
        /// 获得用户群缓存列表
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public static List<UserRoom> GetUserRooms(UInt32 userID)
        {
            return DBAccess.Select<UserRoom>("RoomUsers", "userID=" + userID);
        }
        #endregion

        #region 判断字符串是否数字
        /// <summary>
        /// 判断字符串是否数字
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static bool IsNumberic(string str)
        {
            System.Text.RegularExpressions.Regex rex = new System.Text.RegularExpressions.Regex(@"^\d+$");

            if (rex.IsMatch(str))
                return true;
            else
                return false;
        }
        #endregion

        #region 获取群用户信息(含在线状态)
        /// <summary>
        /// 获取群用户信息(含在线状态)
        /// </summary>
        /// <param name="roomID"></param>
        /// <returns></returns>
        public static List<P.RoomUser> GetRoomUsers(UInt32 roomID)
        {
            var sql = "SELECT  r.*,u.UserName ,u.ShowType,u.HeadID FROM  RoomUsers  r,Users u where r.UserID =u.UserID and r.roomID=" + roomID;
            return DBAccess.Select<P.RoomUser>(sql);
        }
        #endregion

        #region 查询用户
        /// <summary>
        /// 查询用户
        /// </summary>
        /// <param name="q"></param>
        /// <returns></returns>
        public static List<QueryUser> QueryUsers(Query q)
        {
            if (q.NameOrID == null) q.NameOrID = string.Empty;
            int ageMin = 0;
            int ageMax = 0;
            if (q.Age == QueryAge.Old_17)
            {
                ageMax = 17;
            }
            else if (q.Age == QueryAge.Old18_22)
            {
                ageMin = 18; ageMax = 22;
            }
            else if (q.Age == QueryAge.Old23_30)
            {
                ageMin = 23; ageMax = 30;
            }
            else if (q.Age == QueryAge.Old31_40)
            {
                ageMin = 31; ageMax = 40;
            }
            else if (q.Age == QueryAge.Old41)
            {
                ageMin = 41; ageMax = 255;
            }

            byte sex = 255;

            if (q.Sex == QuerySex.Male)
                sex = 0;
            else if (q.Sex == QuerySex.Female)
                sex = 1;

            byte ShowType = 0;
            if (q.ShowType)
                ShowType = 1;

            var sql = "select top 20  * from Users where 1=1";

            return DBAccess.Select<QueryUser>(sql);

            //if(DBAccess.DBtype == DBAccess.DBType.SqlServer )
            //using (var dr = DBAccess.GetReaderByProc("pro_QueryUsers", q.NameOrID, sex, ageMin, ageMax, ShowType))
            //{
            //    if (dr != null)
            //    {
            //        qr = new QueryResult();
            //        qr.QueryClass = QueryClass.User;
            //        while (dr.Read())
            //        {
            //            var qu = new QueryUser();
            //            qu.UserID = IsDBNull(dr, "UserID") ? 0 : Convert.ToUInt32(dr["UserID"]);
            //            qu.UserName = IsDBNull(dr, "UserName") ? "" : Convert.ToString(dr["UserName"]);
            //            qu.Sex = IsDBNull(dr, "Sex") ? false : Convert.ToBoolean(dr["Sex"]);
            //            qu.Age = IsDBNull(dr, "Age") ? (byte)0 : Convert.ToByte(dr["Age"]);
            //            qu.AddCondition = IsDBNull(dr, "AddCondition") ? (byte)0 : Convert.ToByte(dr["AddCondition"]);
            //            qu.HeadID = IsDBNull(dr, "HeadID") ? 0 : Convert.ToInt32(dr["HeadID"]);
            //            qu.ShowType = IsDBNull(dr, "ShowType") ? 0 : Convert.ToInt32(dr["ShowType"]);
            //            qr.Results.Add(qu);
            //        }
            //    }
            //    if (dr != null)
            //        dr.Close();
            //    dr.Dispose();
            //}
            //return qr;
        }
        #endregion

        #region 查询群
        /// <summary>
        /// 查询群
        /// </summary>
        /// <param name="q"></param>
        /// <returns></returns>
        public static List<QueryRoom> QueryRooms(Query q)
        {
            //,fun_GetRoomUsersCount(roomID) as UsersCount

            if (q.NameOrID == null) q.NameOrID = string.Empty;
            var sql = "select top 20  roomID,roomName,AddCondition,MaxUsersCount from Rooms where 1=1  ";
            if (DBO.IsNumberic(q.NameOrID))
                sql += " and RoomID=" + q.NameOrID;
            else if (q.NameOrID.Trim().Length > 0)
                sql += " and roomName like'%" + q.NameOrID + "%'";

            return DBAccess.Select<QueryRoom>(sql);
        }
        #endregion

        #region 设置数据库中所有用户在线状态为离线
        /// <summary>
        /// 设置数据库中所有用户在线状态为离线
        /// </summary>
        public static int SetDBAllUsersOffline()
        {
            var sql = "update users set showtype=0;";
            return DBAccess.ExecSql(sql, sql);
        }
        #endregion

        #region 创建群
        /// <summary>
        /// 创建群
        /// </summary>
        /// <param name="room">群基本信息</param>
        /// <param name="users">群初始成员</param>
        /// <returns></returns>
        public static int CreateRoom(RoomBaseInfo room, List<UInt32> users)
        {
            if (room == null) return 0;
            var sql = "";
            var mysql = "";

            ///添加群基本信息
            {
                if (room.Notice == null) room.Notice = "";
                if (room.Remark == null) room.Remark = "";
                if (room.RemarkName == null) room.RemarkName = "";
                if (room.RoomName == null) room.RoomName = "我的群";
                room.MaxUsersCount = 500;
                 sql = string.Format("insert into Rooms(roomID,roomName,CreateUserID,Notice,Remark,AddCondition,MaxUsersCount) select {0},'{1}',{2},'{3}','{4}',{5},{6}  where NOT EXISTS(SELECT * FROM Rooms WHERE roomID={0});",
                  room.RoomID, room.RoomName, room.CreateUserID, room.Notice, room.Remark, room.AddCondition, room.MaxUsersCount);
                mysql = string.Format("insert into Rooms(roomID,roomName,CreateUserID,Notice,Remark,AddCondition,MaxUsersCount) select {0},'{1}',{2},'{3}','{4}',{5},{6}  FROM dual  where NOT EXISTS(SELECT * FROM Rooms WHERE roomID={0});",
                  room.RoomID, room.RoomName, room.CreateUserID, room.Notice, room.Remark, room.AddCondition, room.MaxUsersCount);
            }

            var id = "";

            ///添加群创建者为管理员
            {
                id = room.RoomID + "_" + room.CreateUserID;
                  sql += string.Format("INSERT INTO RoomUsers(ID,RoomID,UserID,GroupID,Admin,RemarkName) select '{0}',{1},{2},{3},{4},'{5}'  where NOT EXISTS(SELECT * FROM RoomUsers WHERE ID='{0}');",
                  id, room.RoomID, room.CreateUserID, -1, 1, room.RemarkName);
                mysql += string.Format("INSERT INTO RoomUsers(ID,RoomID,UserID,GroupID,Admin,RemarkName) select '{0}',{1},{2},{3},{4},'{5}' FROM dual where NOT EXISTS(SELECT * FROM RoomUsers WHERE ID='{0}');",
                  id, room.RoomID, room.CreateUserID, -1, 1, room.RemarkName);
            }

            if (users != null)
                foreach (var userID in users)
                {
                    id = room.RoomID + "_" + userID;
                    sql  += string.Format("INSERT INTO RoomUsers(ID,RoomID,UserID,GroupID) select '{0}',{1},{2},{3} where NOT EXISTS(SELECT * FROM RoomUsers WHERE ID='{0}');",
                      id, room.RoomID, userID, -1);
                    mysql += string.Format("INSERT INTO RoomUsers(ID,RoomID,UserID,GroupID) select '{0}',{1},{2},{3} FROM dual where NOT EXISTS(SELECT * FROM RoomUsers WHERE ID='{0}');",
                   id, room.RoomID, userID, -1);
                }
            return DBAccess.ExecSql(sql , mysql);
        }

        #endregion

        #region 添加用户到群
        /// <summary>
        /// 添加用户到群
        /// </summary>
        /// <param name="roomID"></param>
        /// <param name="userID"></param>
        /// <param name="groupID">用户设置群的分组</param>
        /// <param name="remarkName">用户设置群的分组别名</param>
        /// <param name="ban">是否禁止发言</param>
        /// <param name="admin">是否管理员</param>
        public static void AddUserToRoom(UInt32 roomID, UInt32 userID, int groupID, bool admin, string remarkName)
        {
            var id = roomID + "_" + userID;
            int Admin = (admin ? 1 : 0);
            var sql  = string.Format("INSERT INTO RoomUsers(ID,RoomID,UserID,GroupID,Admin,RemarkName) select '{0}',{1},{2},{3},{4},'{5}'  where NOT EXISTS(SELECT * FROM RoomUsers WHERE ID='{0}');",
                id, roomID, userID, groupID, Admin, remarkName);
            var mysql = string.Format("INSERT INTO RoomUsers(ID,RoomID,UserID,GroupID,Admin,RemarkName) select '{0}',{1},{2},{3},{4},'{5}' FROM dual where NOT EXISTS(SELECT * FROM RoomUsers WHERE ID='{0}');",
             id, roomID, userID, groupID, Admin, remarkName);

            DBAccess.ExecSql(sql,mysql );
        }
        /// <summary>
        /// 添加多个用户到群
        /// </summary>
        /// <param name="roomID"></param>
        /// <param name="users"></param>
        public static void AddUsersToRoom(UInt32 roomID, List<UInt32> users)
        {
            var sql  = "";
            var mysql = "";
            var id = "";
            if (users != null)
                foreach (var userID in users)
                {
                    id = roomID + "_" + userID;
                    sql  += string.Format("INSERT INTO RoomUsers(ID,RoomID,UserID,GroupID) select '{0}',{1},{2},{3} where NOT EXISTS(SELECT * FROM RoomUsers WHERE ID='{0}');",
                      id, roomID, userID, -1);
                    mysql += string.Format("INSERT INTO RoomUsers(ID,RoomID,UserID,GroupID) select '{0}',{1},{2},{3} FROM dual where NOT EXISTS(SELECT * FROM RoomUsers WHERE ID='{0}');",
                   id, roomID, userID, -1);
                }
            DBAccess.ExecSql(sql,mysql  );
        }
        #endregion

        #region 删除群用户
        /// <summary>
        /// 删除群用户
        /// </summary>
        /// <param name="roomID"></param>
        /// <param name="userID"></param>
        /// <returns></returns>
        public static int DelUserFromRoom(UInt32 roomID, UInt32 userID)
        {
            var id = roomID + "_" + userID;
            var sql = string.Format("delete from RoomUsers where ID='{0}';", id);
            return DBAccess.ExecSql(sql,sql );
        }
        #endregion

        #region 添加用户好友
        /// <summary>
        /// 添加用户好友
        /// </summary>
        /// <param name="userID">请求用户</param>
        /// <param name="friendID">好友</param>
        /// <param name="groupID">好友在请求用户中的分组ID</param>
        /// <param name="remarkName">好友在请求用户中的备注</param>
        /// <param name="groupIDInFriend">请求用户在好友中的分组ID</param>
        /// <param name="remarkNameInFriend">请求用户在好友中的备注</param>
        public static void AddFriend(UInt32 userID, UInt32 friendID, int groupID, string remarkName, int groupIDInFriend, string remarkNameInFriend)
        {
            var id1 = userID + "_" + friendID;
            var id2 = friendID + "_" + userID;
            if (remarkName == null) remarkName = "";
            if (remarkNameInFriend == null) remarkNameInFriend = "";
            var sql  = string.Format("INSERT INTO UserFriends(ID,UserID,FriendID,GroupID,RemarkName) select '{0}',{1},{2},{3},'{4}'  where NOT EXISTS(SELECT * FROM UserFriends WHERE ID='{0}');" +
                            "INSERT INTO UserFriends(ID,UserID,FriendID,GroupID,RemarkName) select '{5}',{2},{1},{6},'{7}'  where NOT EXISTS(SELECT * FROM UserFriends WHERE ID='{5}');",
                id1, userID, friendID, groupID, remarkName, id2, groupIDInFriend, remarkNameInFriend);
            var mysql = string.Format("INSERT INTO UserFriends(ID,UserID,FriendID,GroupID,RemarkName) select '{0}',{1},{2},{3},'{4}'  FROM dual  where NOT EXISTS(SELECT * FROM UserFriends WHERE ID='{0}');" +
                           "INSERT INTO UserFriends(ID,UserID,FriendID,GroupID,RemarkName) select '{5}',{2},{1},{6},'{7}'  FROM dual  where NOT EXISTS(SELECT * FROM UserFriends WHERE ID='{5}');",
                id1, userID, friendID, groupID, remarkName, id2, groupIDInFriend, remarkNameInFriend);
            DBAccess.ExecSql(sql,mysql );
        }
        #endregion

        #region 注册用户写入数据库
        public static int RegisterUser(UserRegister rInfo)
        {
            var sql = string.Format("INSERT INTO Users(UserID,UserName,Password,Sex,Age,AddCondition,HeadID) " +
                       "select  {0},'{1}','{2}',{3},{4},{5},{6}  where NOT EXISTS(SELECT * FROM Users WHERE UserID={0});",
                        rInfo.UserID, rInfo.UserName, rInfo.Password, rInfo.Sex, rInfo.Age, (byte)rInfo.AddCondition, rInfo.HeadID);
            var mysql = string.Format("INSERT INTO Users(UserID,UserName,Password,Sex,Age,AddCondition,HeadID) " +
                       "select  {0},'{1}','{2}',{3},{4},{5},{6}  FROM dual  where NOT EXISTS(SELECT * FROM Users WHERE UserID={0});",
                        rInfo.UserID, rInfo.UserName, rInfo.Password, rInfo.Sex, rInfo.Age, (byte)rInfo.AddCondition, rInfo.HeadID);

            return DBAccess.ExecSql(sql,mysql );
        }
        #endregion



    }
}
