package db

import (
	"World/club"
	"World/common"
	pb "World/pb"
	"common/logger"
	"strconv"
	"time"
)

func AddAlliance(pa *club.AllianceParams) (uint32, error) {
	//INSERT INTO dtb_alliance(alliance_name, clubs_max, creater_club_id, create_time) VALUES ('nb', '4', '100269', '0');INSERT INTO dtb_alliance(alliance_name, clubs_max, creater_club_id, create_time) VALUES ('nb', '4', '100269', '0');
	cmd := "INSERT INTO dtb_alliance(alliance_name, clubs_max, creater_club_id, create_time, reviewed) VALUES  ('" + pa.Name + "'," + strconv.Itoa(pa.MemberMax) + "," + strconv.Itoa(pa.ClubId) + "," + strconv.Itoa(int(time.Now().Unix())) + "," + strconv.Itoa(pa.Reviewed) + ")"
	ExcuteCmd(cmd)

	sql := "select max(id) from dtb_alliance"
	clubId, err := GetSingleU32(sql)
	return clubId, err
}

func AddAllianceApplyMsg(allianceId uint32, clubId uint32, msg string) {
	//INSERT INTO dtb_alliance_apply(user_id, club_id, apply_msg, add_time) VALUES ('409', '100344', 'hi', '1483620096');
	cmd := "INSERT INTO dtb_alliance_apply (alliance_id, club_id,apply_msg,add_time) VALUES ('" + strconv.Itoa(int(allianceId)) + "','" + strconv.Itoa(int(clubId)) + "','" + msg + "'" + "," + strconv.Itoa(int(time.Now().Unix())) + ")"
	ExcuteCmd(cmd)
}

func AddAllianceMember(allianceId uint32, CreaterId uint32, clubId uint32) error {
	// INSERT INTO dtb_alliance_member(alliance_id, creater_club_id, club_id, add_time) VALUES (NULL, NULL, NULL, NULL);
	cmd := "INSERT INTO dtb_alliance_member(alliance_id, creater_club_id, club_id, add_time) VALUES (" + common.U32toa(allianceId) + "," + common.U32toa(CreaterId) + "," + common.U32toa(clubId) + "," + strconv.Itoa(int(time.Now().Unix())) + ")"
	return ExcuteCmd(cmd)
}

func AddAlliancePending(noti pb.NoticeJoinAllianceToMember, clubAdminId uint32) {
	alliance_id := noti.AllianceId
	club_id := noti.ClubId
	chk_status := noti.Result
	reason := noti.Reason
	cmd := "INSERT INTO t_alliance_pending (alliance_id, club_id,club_admin_id,chk_status,reason) VALUES ('" + strconv.Itoa(int(alliance_id)) + "','" + strconv.Itoa(int(club_id)) + "','" + strconv.Itoa(int(clubAdminId)) + "','" + strconv.Itoa(int(chk_status)) + "','" + reason + "')"
	ExcuteCmd(cmd)
}

func AddLeaveallianceNotice(noti pb.NoticeLeaveAlliance, clubAdminId uint32) {
	alliance_id := noti.AllianceId
	club_id := noti.ClubId
	AllianceName := noti.AllianceName
	ClubName := noti.ClubName

	//INSERT INTO t_leavealliance_notice(alliance_id, club_id, club_admin_id, alliance_name, club_name) VALUES (NULL, NULL, NULL, NULL, NULL);
	cmd := "INSERT INTO t_leavealliance_notice(alliance_id, club_id, club_admin_id, alliance_name, club_name) VALUES ('" + strconv.Itoa(int(alliance_id)) + "','" + strconv.Itoa(int(club_id)) + "','" + strconv.Itoa(int(clubAdminId)) + "','" + AllianceName + "','" + ClubName + "')"
	ExcuteCmd(cmd)
}

func DeleteAllianceApplyInfo(allianceId uint32, clubId uint32) error {
	//DELETE FROM dtb_alliance_apply WHERE alliance_id =999 and club_id = 32
	cmd := "DELETE FROM dtb_alliance_apply WHERE alliance_id =" + common.U32toa(allianceId) + " and club_id =" + common.U32toa(clubId)
	err := ExcuteCmd(cmd)
	return err
}

func DeleteAlliancePendingList(uid uint32, allianceId uint32) error {
	//DELETE FROM t_alliance_pending WHERE alliance_id = 101003 and club_admin_id = 2251
	cmd := "DELETE FROM t_alliance_pending WHERE alliance_id = " + common.U32toa(allianceId) + " and club_admin_id =" + common.U32toa(uid)
	err := ExcuteCmd(cmd)
	return err
}

//单个联盟中的该成员
func RemoveAllianceMember(targetId uint32, AllianceId uint32) error {
	//DELETE FROM dtb_alliance_member WHERE alliance_id =100068 and club_id =303
	cmd := "DELETE FROM dtb_alliance_member WHERE alliance_id =" + common.U32toa(AllianceId) + " and club_id =" + common.U32toa(targetId)
	err := ExcuteCmd(cmd)
	return err
}

//多个联盟中的该成员
func RemoveAlliancesNormalMember(targetId uint32) error {
	//DELETE FROM dtb_alliance_member WHERE creater_club_id <> 1 and club_id =1
	cmd := "DELETE FROM dtb_alliance_member WHERE creater_club_id <> " + common.U32toa(targetId) + " and club_id =" + common.U32toa(targetId)
	err := ExcuteCmd(cmd)
	return err
}

func ModifyAllianceApplyCheckTime(AllianceId uint32, clubId uint32) error {
	//UPDATE dtb_alliance_apply SET chk_time = 12345678 WHERE alliance_id = 100269 and club_id = 389
	cmd := "UPDATE dtb_alliance_apply SET chk_time = " + strconv.Itoa(int(time.Now().Unix())) + " WHERE alliance_id = " + common.U32toa(AllianceId) + " and club_id = " + common.U32toa(clubId)
	err := ExcuteCmd(cmd)
	return err
}

func GetAllianceCreater(allianceId uint32) (uint32, error) {
	//select COALESCE(creater_club_id,0) from dtb_alliance where id = 6
	cmd := "select COALESCE(creater_club_id,0) from dtb_alliance where id = " + common.U32toa(allianceId)
	rows, err := m_game_db.Query(cmd)
	if rows != nil {
		defer rows.Close()
	}
	if err != nil {
		logger.Notic("GetAllianceCreater Query error:%v cmd:%v", err, cmd)
		return 0, err
	}
	for rows.Next() {
		var creater_club_id uint32

		err = rows.Scan(&creater_club_id)
		if err != nil {
			logger.Notic("GetAllianceCreater Scan error:%v", err)
			return 0, err
		}

		return creater_club_id, err
	}
	return 0, err
}

func GetAllianceInfo4List(AllianceId uint32, clubId uint32) (*pb.AllianceListParams, error) {
	//select COALESCE(alliance_name,''),COALESCE(clubs_max,0),COALESCE(creater_club_id,0) from dtb_alliance where id = 100014
	cmd := "select COALESCE(alliance_name,''),COALESCE(clubs_max,0),COALESCE(creater_club_id,0),COALESCE(reviewed,0) from dtb_alliance where id =" + common.U32toa(AllianceId)
	rows, err := m_game_db.Query(cmd)
	if rows != nil {
		defer rows.Close()
	}
	if err != nil {
		logger.Notic("GetAllianceInfo4List Query error:%v cmd:%v", err, cmd)
		return nil, err
	}
	for rows.Next() {
		var creater_club_id uint32

		info := new(pb.AllianceListParams)

		err = rows.Scan(&info.AllianceName, &info.ClubMax, &creater_club_id, &info.Reviewed)
		if err != nil {
			logger.Notic("GetAllianceInfo4List Scan error:%v", err)
			return nil, err
		}

		info.AllianceId = AllianceId
		info.IsCreater = (clubId == creater_club_id)

		ids, _, e := GetAllianceMemberList(AllianceId)
		ids = common.RemoveDuplicates(ids)
		var num int
		if e == nil {
			num = len(ids)
			info.ClubCount = int32(num)
		}

		return info, err
	}

	return nil, err
}

func GetClubInfo4Alliance(AllianceId uint32, clubId uint32) (*pb.ClubItemInfo, error) {
	//select COALESCE(club_name,''),COALESCE(creater_id,0),COALESCE(current_members,0),COALESCE(members_max,0) from dtb_club where id =13269
	cmdItem := "select COALESCE(club_name,''),COALESCE(creater_id,0),COALESCE(current_members,0),COALESCE(members_max,0),COALESCE(club_thumb,'') from dtb_club where id =" + common.U32toa(clubId)
	rows, err := m_game_db.Query(cmdItem)
	if rows != nil {
		defer rows.Close()
	}
	var clubName string
	var createrId uint32
	var curNum int
	var maxNum int
	var club_thumb string

	if err != nil {
		logger.Notic("GetClubInfo4Alliance Query error:%v cmdItem:%v", err, cmdItem)
		return nil, err
	}

	ItemInfo := new(pb.ClubItemInfo)
	for rows.Next() {
		err = rows.Scan(&clubName, &createrId, &curNum, &maxNum, &club_thumb)
		if err != nil {
			logger.Notic("GetClubInfo4Alliance Scan error:%v", err)
			return nil, err
		}

		ItemInfo.ClubId = clubId
		//ItemInfo.ClubMemberCount = int32(curNum)
		ids, er := GetClubMemberList(clubId)
		if er == nil {
			ItemInfo.ClubMemberCount = int32(len(ids))
		} else {
			ItemInfo.ClubMemberCount = int32(curNum)
		}
		ItemInfo.ClubMemberMax = int32(maxNum)
		ItemInfo.ClubName = clubName
		ItemInfo.ClubThumb = club_thumb
		if name, e := GetNickName(createrId); e == nil {
			ItemInfo.CreaterName = name
		}
		if MaxBuyinLimit, CurBuyinLimit, bOpen, OK := GetAllianceClubInfo(AllianceId, clubId); OK == nil {
			ItemInfo.MaxBuyinLimit = MaxBuyinLimit
			ItemInfo.CurBuyinLimit = CurBuyinLimit
			ItemInfo.ControlBuyin = bOpen
		}

		return ItemInfo, err
	}
	return nil, err
}

func GetAllianceInfo(AllianceId uint32) (*pb.NoticeSearchAlliance, error) {
	//select COALESCE(alliance_name,''),COALESCE(clubs_max,0) from dtb_alliance where id = 100014
	cmd := "select COALESCE(creater_club_id,0),COALESCE(alliance_name,''),COALESCE(clubs_max,0) from dtb_alliance where id = " + common.U32toa(AllianceId)

	rows, err := m_game_db.Query(cmd)
	if rows != nil {
		defer rows.Close()
	}
	if err != nil {
		logger.Notic("GetAllianceInfo Query error:%v", err)
		return nil, err
	}

	for rows.Next() {
		noti := new(pb.NoticeSearchAlliance)
		var alliance_name string
		var clubs_max int32

		err = rows.Scan(&noti.CreaterClubId, &alliance_name, &clubs_max)
		if err != nil {
			logger.Notic("GetAllianceInfo Scan error:%v", err)
			return noti, err
		}

		noti.AllianceId = AllianceId
		noti.AllianceName = string(alliance_name)
		noti.AllianceClubMax = int32(clubs_max)
		ids, _, e := GetAllianceMemberList(AllianceId)
		ids = common.RemoveDuplicates(ids)

		if e == nil {
			noti.AllianceClubCount = int32(len(ids))
		}

		for _, clubId := range ids {
			info, e := GetClubInfo4Alliance(AllianceId, clubId)
			if e == nil && info != nil {
				noti.ClubItems = append(noti.ClubItems, info)
			}
		}

		return noti, err
	}

	return nil, err
}

func GetAllianceName(AllianceId uint32) (string, error) {
	cmd := "select COALESCE(alliance_name,'') from dtb_alliance where id = " + common.U32toa(AllianceId)
	rows, err := m_game_db.Query(cmd)
	if rows != nil {
		defer rows.Close()
	}
	if err != nil {
		logger.Notic("GetAllianceName Query error:%v cmd:%v", err, cmd)
		return "", err
	}
	for rows.Next() {
		var alliance_name string

		err = rows.Scan(&alliance_name)
		if err != nil {
			logger.Notic("GetAllianceName Scan error:%v", err)
			return "", err
		}
		return alliance_name, err
	}

	return "", err
}

func GetAllianceCreaterId(AllianceId uint32) (uint32, error) {
	cmd := "select COALESCE(creater_club_id,0) from dtb_alliance where id = " + common.U32toa(AllianceId)
	rows, err := m_game_db.Query(cmd)
	if rows != nil {
		defer rows.Close()
	}
	if err != nil {
		logger.Notic("GetAllianceCreaterId Query error:%v cmd:%v", err, cmd)
		return 0, err
	}
	for rows.Next() {
		var creater_club_id uint32

		err = rows.Scan(&creater_club_id)
		if err != nil {
			logger.Notic("GetAllianceCreaterId Scan error:%v", err)
			return 0, err
		}
		return creater_club_id, err
	}

	return 0, err
}

func GetAllianceCreaterUID(AllianceId uint32) (uint32, error) {
	cmd := "select COALESCE(creater_club_id,0) from dtb_alliance where id = " + common.U32toa(AllianceId)
	var err error
	var creater_club_id uint32 = 0
	var club_creater_id uint32 = 0
	if creater_club_id, err = GetSingleU32(cmd); err == nil {
		if club_creater_id, err = GetClubCreaterId(creater_club_id); err == nil {
			return club_creater_id, err
		}
	}
	return 0, err
}

func GetAlliancePendingInfoList(uid uint32) ([]*pb.NoticeJoinAllianceToMember, error) {
	//select COALESCE(alliance_id,0),COALESCE(club_id,0),COALESCE(chk_status,0),COALESCE(reason,'') from t_alliance_pending where club_admin_id =2285
	cmd := "select COALESCE(alliance_id,0),COALESCE(club_id,0),COALESCE(chk_status,0),COALESCE(reason,'') from t_alliance_pending where club_admin_id =" + common.U32toa(uid)
	rows, err := m_game_db.Query(cmd)
	if rows != nil {
		defer rows.Close()
	}
	if err != nil {
		logger.Notic("GetAlliancePendingInfoList Query error:%v cmd:%v", err, cmd)
		return nil, err
	}

	var list []*pb.NoticeJoinAllianceToMember
	for rows.Next() {
		var alliance_id uint32
		var chk_status int
		var reason string
		Info := new(pb.NoticeJoinAllianceToMember)
		err = rows.Scan(&Info.AllianceId, &Info.ClubId, &chk_status, &reason)
		if err != nil {
			logger.Notic("GetAlliancePendingInfoList Scan error:%v", err)
			return nil, err
		}

		if allianceName, err := GetAllianceName(alliance_id); err == nil {
			Info.AllianceName = allianceName
		}

		Info.Result = int32(chk_status)
		Info.Reason = string(reason)
		Info.ClubAdminId = uid

		list = append(list, Info)
	}
	return list, err
}

func GetAllianceMemberList(AllianceId uint32) ([]uint32, []uint32, error) {
	//select club_id,add_time from dtb_alliance_member WHERE alliance_id = 101204
	cmd := "select club_id,add_time from dtb_alliance_member WHERE alliance_id = " + common.U32toa(AllianceId)
	rows, err := m_game_db.Query(cmd)
	var cids []uint32
	var t_list []uint32
	if rows != nil {
		defer rows.Close()
	}
	if err != nil {
		logger.Notic("GetSingleIntValueList Query error:%d cmd:%v", err, cmd)
		return cids, t_list, err
	}

	for rows.Next() {
		var cid uint32
		var time uint32
		err = rows.Scan(&cid, &time)
		if err != nil {
			logger.Notic("GetSingleIntValueList Scan error:%v", err)
			return cids, t_list, err
		}
		cids = append(cids, cid)
		t_list = append(t_list, time)
	}
	return cids, t_list, err
}

func ModifyAllianceMaxBuyinLimit(AllianceId uint32, clubId uint32, amount int) error {
	//UPDATE dtb_alliance_member SET max_buyin_limit = 100 WHERE alliance_id = 101096 and club_id = 100269
	cmd := "UPDATE dtb_alliance_member SET max_buyin_limit = " + strconv.Itoa(amount) + " WHERE alliance_id = " + strconv.Itoa(int(AllianceId)) + " and club_id = " + strconv.Itoa(int(clubId))
	err := ExcuteCmd(cmd)
	return err
}

func ModifyAllianceCurBuyinLimit(AllianceId uint32, clubId uint32, curAmount int) error {
	//UPDATE dtb_alliance_member SET curAmount = 100 WHERE alliance_id = 101096 and club_id = 100269
	cmd := "UPDATE dtb_alliance_member SET cur_buyin_limit = " + strconv.Itoa(curAmount) + " WHERE alliance_id = " + strconv.Itoa(int(AllianceId)) + " and club_id = " + strconv.Itoa(int(clubId))
	err := ExcuteCmd(cmd)
	return err
}

func ModifyAllianceControlBuyin(AllianceId uint32, clubId uint32, ControlBuyin bool) error {
	var control int = 1
	if !ControlBuyin {
		control = 2
	}
	//UPDATE dtb_alliance_member SET control_buyin = 2 WHERE alliance_id = 101096 and club_id = 100269
	cmd := "UPDATE dtb_alliance_member SET control_buyin = " + strconv.Itoa(control) + " WHERE alliance_id = " + strconv.Itoa(int(AllianceId)) + " and club_id = " + strconv.Itoa(int(clubId))
	err := ExcuteCmd(cmd)
	return err
}

func GetAllianceClubInfo(AllianceId uint32, clubid uint32) (int64, int64, bool, error) {
	//select COALESCE(max_buyin_limit,0),COALESCE(cur_buyin_limit,0),COALESCE(control_buyin,0) from dtb_alliance_member WHERE alliance_id = 101096 and club_id = 100269
	cmd := "select COALESCE(max_buyin_limit,0),COALESCE(cur_buyin_limit,0),COALESCE(control_buyin,0) from dtb_alliance_member WHERE alliance_id =" + common.U32toa(AllianceId) + " and club_id = " + common.U32toa(clubid)
	rows, err := m_game_db.Query(cmd)
	if rows != nil {
		defer rows.Close()
	}
	if err != nil {
		logger.Notic("GetAllianceClubInfo Query error:%v cmd:%v", err, cmd)
		return 0, 0, true, err
	}

	for rows.Next() {
		var MaxBuyinLimit int64
		var CurBuyinLimit int64
		var ControlBuyin int

		err = rows.Scan(&MaxBuyinLimit, &CurBuyinLimit, &ControlBuyin)
		if err != nil {
			logger.Notic("GetAllianceClubInfo Scan error:%v", err)
			return 0, 0, true, err
		}

		var bOpen bool = true
		if ControlBuyin == 2 {
			bOpen = false
		}

		return MaxBuyinLimit, CurBuyinLimit, bOpen, err
	}
	return 0, 0, true, err
}

func ModifyAllianceStatus(AllianceId uint32, result int, reason string) error {
	//UPDATE dtb_alliance SET reviewed = 2,review_time=123,reason='coming' WHERE id = 101219
	cmd := "UPDATE dtb_alliance SET reviewed = " + strconv.Itoa(result) + ",review_time=" + strconv.Itoa(int(time.Now().Unix())) + ",reason='" + reason + "' WHERE id = " + common.U32toa(AllianceId)
	logger.Info("ModifyAllianceStatus:%v", cmd)
	err := ExcuteCmd(cmd)
	return err
}

func IsAllianceNameExist(name string) (bool, error) {
	//SELECT count(id) from dtb_alliance WHERE alliance_name = '阿正联盟'
	cmd := "SELECT count(id) from dtb_alliance WHERE alliance_name ='" + name + "'"
	count, err := GetSingleIntValue(cmd)
	if count > 0 {
		return true, err
	}

	return false, err
}

func IsTargetAllianceCreater(tid uint32, AllianceId uint32) (bool, error) {
	//select count(id) from dtb_alliance WHERE id =31 and creater_club_id = 14
	cmd := "select count(id) from dtb_alliance WHERE id =" + strconv.Itoa(int(AllianceId)) + " and creater_club_id =" + strconv.Itoa(int(tid))
	count, err := GetSingleIntValue(cmd)
	if count > 0 {
		return true, err
	}
	return false, err
}

func IsAllianceCreater(tid uint32) (bool, error) {
	//select count(id) from dtb_alliance WHERE creater_club_id = 14
	cmd := "select count(id) from dtb_alliance WHERE " + " creater_club_id =" + strconv.Itoa(int(tid))
	count, err := GetSingleIntValue(cmd)
	if count > 0 {
		return true, err
	}
	return false, err
}

func HasAlliancePrivilege(AllianceId uint32, ClubId uint32) bool {
	//select count(id) from dtb_alliance_member WHERE alliance_id =31 and creater_club_id = 14
	//是不是创建者
	cmd := "select count(id) from dtb_alliance_member WHERE alliance_id =" + strconv.Itoa(int(AllianceId)) + " and creater_club_id =" + strconv.Itoa(int(ClubId))
	cnt, _ := GetSingleIntValue(cmd)
	if cnt > 0 {
		return true
	}

	return false
}

func DeleteFromAlliance(AllianceId uint32) error {
	//DELETE FROM dtb_alliance WHERE id = 31
	cmd := "DELETE FROM dtb_alliance WHERE id = " + strconv.Itoa(int(AllianceId))
	err := ExcuteCmd(cmd)
	return err
}

func ModifyAllianceApplyAddTime(allianceId uint32, clubId uint32) error {
	//UPDATE dtb_alliance_apply SET add_time = 12345678 WHERE club_id = 100269 and user_id = 389
	cmd := "UPDATE dtb_alliance_apply SET add_time = " + strconv.Itoa(int(time.Now().Unix())) + " where alliance_id =" + strconv.Itoa(int(allianceId)) + " and club_id =" + strconv.Itoa(int(clubId))
	err := ExcuteCmd(cmd)
	return err
}

func GetApplyAllianceList(allianceId uint32) ([]*pb.RequestJoinAlliance, error) {
	//select COALESCE(club_id,0),COALESCE(apply_msg,'') from dtb_alliance_apply where alliance_id = 9
	cmd := "select COALESCE(club_id,0),COALESCE(apply_msg,'') from dtb_alliance_apply where alliance_id = " + common.U32toa(allianceId) + " and add_time > chk_time"
	rows, err := m_game_db.Query(cmd)
	if rows != nil {
		defer rows.Close()
	}
	if err != nil {
		logger.Notic("GetApplyAllianceList Query error:%v", err)
		return nil, err
	}

	var list []*pb.RequestJoinAlliance
	for rows.Next() {
		var club_id uint32
		var msg string

		applyInfo := new(pb.RequestJoinAlliance)
		err = rows.Scan(&applyInfo.ClubId, &msg)
		if err != nil {
			logger.Notic("GetApplyAllianceList Scan error:%v", err)
			return nil, err
		}

		applyInfo.AllianceId = allianceId
		applyInfo.Message = string(msg)

		if len(list) > 0 {
			bExist := false
			for _, info := range list {
				if club_id == info.ClubId {
					bExist = true
				}
			}
			if !bExist {
				list = append(list, applyInfo)
			}
		} else {
			list = append(list, applyInfo)
		}

	}
	return list, err
}

func GetLeaveallianceList(uid uint32) ([]*pb.NoticeLeaveAlliance, error) {
	cmd := "select COALESCE(alliance_id,0),COALESCE(club_id,0),COALESCE(club_admin_id,0),COALESCE(alliance_name,''),COALESCE(club_name,'') from t_leavealliance_notice where club_admin_id =" + common.U32toa(uid)
	rows, err := m_game_db.Query(cmd)
	if rows != nil {
		defer rows.Close()
	}
	if err != nil {
		logger.Notic("GetLeaveallianceList Query error:%v cmd:%v", err, cmd)
		return nil, err
	}

	var list []*pb.NoticeLeaveAlliance
	for rows.Next() {
		var alliance_name string
		var club_name string
		Info := new(pb.NoticeLeaveAlliance)
		err = rows.Scan(&Info.AllianceId, &Info.ClubId, &Info.ClubAdminId, &alliance_name, &club_name)
		if err != nil {
			logger.Notic("GetLeaveallianceList Scan error:%v", err)
			return nil, err
		}

		Info.AllianceName = string(alliance_name)
		Info.ClubName = string(club_name)

		list = append(list, Info)
	}
	return list, err
}


func SetAllianceInvitationCode(allianceId uint32, code string) error {
	cmd := "update dtb_alliance set invitation_code='" + code + "' WHERE id=" + common.U32toa(allianceId)
	err := ExcuteCmd(cmd)
	return err
}