package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	_"github.com/go-sql-driver/mysql"
	"log"
	"strconv"
)

type bid struct {
	blockId int
	eventId int
	preHash string
	hash string
	nodeMac string
	nodeIP string
	nodePe string
}
type brTrf struct {
	blockId int
	blockType string
	preHash string
	hash string
	eventId string
	srcAddress string
	dstAddress string
	messageHash string
}
type bTrf struct {
	blockId int
	blockType string
	preHash string
	hash string
	eventId int64
	srcAddress string
	dstAddress string
	messageHash string
}
type clientBuffer struct {
	bufferId int
	message string
	digest string
	timeStamp int64
	operation string
	clientAddress string
}
type commit struct {
	nodeId int
	eventId int
	view int
	digest string
}
type event struct {
	eventId 		int64
	view 			string
	message 		string
	msgDigest 		string
	digest 			string
	timeStamp 		int64
	operation 		operation
	clientAddress 	string
}
type prepare struct {
	NodeId int		//节点序号
	EventId int64		//时间序号
	View int			//试图序号
	//message string		//消息文本
	Digest string		//消息摘要
}
type prePrepare struct {
	EventId int64
	View int
	Message string
	Digest string
	Prepared string
}
type reply struct {
	eventId int64
	nodeId int
	view int
	response string
	timeStamp int64
}
type checkPoint struct{
	EventId	int64
	NodeId	int
	Digest	string
}


//数据库指针
//var db *sql.DB

//初始化数据库连接，init()方法系统会在动在main方法之前执行。
func ConnectDB(nodeID int)(db *sql.DB){
	database, err := sql.Open("mysql",
		"root:794613@tcp(127.0.0.1:3306)/port" +
		strconv.Itoa(nodeID))
	if err != nil {
		log.Println("open mysql failed,", err)
	}
	db = database
	Create(db)
	return
}

func Create(db *sql.DB){
	CreateBid(db)
	CreateBrTrf(db)
	CreateBTrf(db)
	CreateClientBuffer(db)
	CreateCommit(db)
	CreateEvent(db)
	CreatePrepare(db)
	CreatePrePrepare(db)
	CreateReply(db)
	CreateCheckPoint(db)
}

//创建数据库
func CreateBid(db *sql.DB){
	//create bid_table
	createTable := "CREATE TABLE `bid_table` (" +
		"`blockId` INT(11) NOT NULL AUTO_INCREMENT," +
		"`eventId` BIGINT(20) NULL DEFAULT NULL," +
		"`preHash` VARCHAR(64) NULL DEFAULT NULL," +
		"`hash` VARCHAR(64) NOT NULL," +
		"`nodeMac` VARCHAR(50) NOT NULL," +
		"`nodeIP` VARCHAR(20) NOT NULL," +
		"`nodePe` VARCHAR(300) NOT NULL," +
		"PRIMARY KEY (`blockId`)" +
		")" +
		"COLLATE='latin1_swedish_ci'" +
		"ENGINE=InnoDB;"
	_, err := db.Exec(createTable)
	if err != nil {
		log.Println(err)
	}else{
		log.Println("bid_table创建成功")
	}

}
func CreateBrTrf(db *sql.DB){
	createTable := "CREATE TABLE `brTrf_table` (" +
		"`blockId` INT(11) NOT NULL AUTO_INCREMENT," +
		"`blockType` VARCHAR(20) NOT NULL," +
		"`preHash` VARCHAR(64) NULL," +
		"`hash` VARCHAR(64) NOT NULL," +
		"`eventId` BIGINT(20) NOT NULL," +
		"`srcAddress` VARCHAR(20) NOT NULL," +
		"`dstAddress` VARCHAR(20) NOT NULL," +
		"`messageHash` VARCHAR(64) NOT NULL," +
		"PRIMARY KEY (`blockId`)" +
		")" +
		"COLLATE='latin1_swedish_ci'" +
		"ENGINE=InnoDB;"
	_, err := db.Exec(createTable)
	if err != nil {
		log.Println(err)
	}else{
		log.Println("brTrf_table创建成功")
	}
}
func CreateBTrf(db *sql.DB){
	createTable := "CREATE TABLE `bTrf_table` (" +
		"`blockId` INT(11) NOT NULL AUTO_INCREMENT," +
		"`blockType` VARCHAR(20) NOT NULL," +
		"`preHash` VARCHAR(64) NULL," +
		"`hash` VARCHAR(64) NOT NULL," +
		"`eventId` BIGINT(20) NOT NULL," +
		"`srcAddress` VARCHAR(20) NOT NULL," +
		"`dstAddress` VARCHAR(20) NOT NULL," +
		"`messageHash` VARCHAR(64) NOT NULL," +
		"PRIMARY KEY (`blockId`))" +
		"COLLATE='latin1_swedish_ci'" +
		"ENGINE=InnoDB;"
	_, err := db.Exec(createTable)
	if err != nil {
		log.Println(err)
	}else{
		log.Println("bTrf_table创建成功")
	}
}
func CreateEvent(db *sql.DB){
	//create bid_table
	createTable := "CREATE TABLE `event_table` (" +
		"`eventId` BIGINT NOT NULL," +
		"`view` VARCHAR(20) NOT NULL," +
		"`message` VARCHAR(1024) NOT NULL," +
		"`msgDigest` VARCHAR(64) NOT NULL," +
		"`digest` VARCHAR(64) NOT NULL," +
		"`timeStamp` BIGINT NOT NULL," +
		"`operation` VARCHAR(50) NULL DEFAULT NULL," +
		"`clientAddress` VARCHAR(30) NOT NULL," +
		"PRIMARY KEY (`eventId`))" +
		"COMMENT='事件列表（由主节点生成，为验证通过的用户请求分配序号）'" +
		"COLLATE='latin1_swedish_ci'" +
		"ENGINE=InnoDB;"
	_, err := db.Exec(createTable)
	if err != nil {
		log.Println(err)
	}else{
		log.Println("event_table创建成功")
	}
}
func CreateClientBuffer(db *sql.DB){
	//TODO
	//使buffer快满的时候可以重设自增
	createTable := "CREATE TABLE `client_buffer` (" +
		"`bufferId` INT(11) NOT NULL AUTO_INCREMENT," +
		"`message` VARCHAR(1024) NOT NULL," +
		"`digest` VARCHAR(64) NOT NULL," +
		"`timeStamp` BIGINT NOT NULL," +
		"`operation` VARCHAR(50) NOT NULL," +
		"`clientAddress` VARCHAR(30) NOT NULL," +
		"PRIMARY KEY (`bufferId`)," +
		"INDEX `timeStamp` (`timeStamp`))" +
		"COLLATE='latin1_swedish_ci'" +
		"ENGINE=InnoDB;"
	_, err := db.Exec(createTable)
	if err != nil {
		log.Println(err)
	}else{
		log.Println("client_request创建成功")
	}
}
func CreatePrePrepare(db *sql.DB){
	//create bid_table
	createTable := "CREATE TABLE `prePrepare_table` (" +
		"`eventId` BIGINT NOT NULL," +
		"`view` INT(11) NOT NULL," +
		"`message` VARCHAR(1024) NOT NULL," +
		"`digest` VARCHAR(64) NOT NULL," +
		"`prepared` VARCHAR(5) NULL DEFAULT 'No',"+
		"PRIMARY KEY (`eventId`))" +
		"COLLATE='latin1_swedish_ci'" +
		"ENGINE=InnoDB;"
	_, err := db.Exec(createTable)
	if err != nil {
		log.Println(err)
	}else{
		log.Println("prePrepare_table创建成功")
	}
}
func CreatePrepare(db *sql.DB){
	//create bid_table
	createTable := "CREATE TABLE `prepare_table` (" +
		"`eventId` BIGINT NOT NULL," +
		"`nodeId` INT(11) NOT NULL," +
		"`view` INT(11) NOT NULL," +
		//"`message` VARCHAR(50) NOT NULL," +
		"`digest` VARCHAR(64) NOT NULL," +
		"PRIMARY KEY (`eventId`, `nodeId`))" +
		"COLLATE='latin1_swedish_ci'" +
		"ENGINE=InnoDB;"
	_, err := db.Exec(createTable)
	if err != nil {
		log.Println(err)
	}else{
		log.Println("prepare_table创建成功")
	}
}
func CreateCommit(db *sql.DB){
	//create bid_table
	createTable := "CREATE TABLE `commit_table` (" +
		"`nodeId` INT(11) NOT NULL," +
		"`eventId` BIGINT NOT NULL," +
		"`view` INT(11) NOT NULL," +
		"`digest` VARCHAR(64) NOT NULL," +
		"PRIMARY KEY (`eventId`,`nodeId`))" +
		"COLLATE='latin1_swedish_ci'" +
		"ENGINE=InnoDB;"
	_, err := db.Exec(createTable)
	if err != nil {
		log.Println(err)
	}else{
		log.Println("commit_table创建成功")
	}
}
func CreateReply(db *sql.DB){
	//create bid_table
	createTable := "CREATE TABLE `reply_table` (" +
		"`nodeId` INT(11) NOT NULL," +
		"`view` INT(11) NOT NULL," +
		"`timeStamp` BIGINT UNSIGNED NOT NULL," +
		"`clientAddress` VARCHAR(30) NOT NULL," +
		"`response` VARCHAR(20) NOT NULL," +
		"PRIMARY KEY (`timeStamp`))" +
		"COLLATE='latin1_swedish_ci'" +
		"ENGINE=InnoDB;"
	_, err := db.Exec(createTable)
	if err != nil {
		log.Println(err)
	}else{
		log.Println("reply_table创建成功")
	}
}
func CreateCheckPoint(db *sql.DB){
	createTable := "CREATE TABLE `check_point` (" +
		"`eventId` BIGINT(20) NOT NULL," +
		"`nodeId` INT(11) NOT NULL," +
		"`digest` VARCHAR(64) NULL DEFAULT NULL," +
		"PRIMARY KEY (`eventID`, `nodeID`))" +
		"ENGINE=InnoDB;"
	_, err := db.Exec(createTable)
	if err != nil {
		log.Println(err)
	}else{
		log.Println("reply_table创建成功")
	}
}

//单行查询
func QueryOneBid(db *sql.DB, id int)(bidMessage bid, err error) {
	sqlStr := `select blockId, eventId, preHash, hash, nodeMac, 
       nodeIP, nodePe from bid_table where blockId=?;`
	err = db.QueryRow(sqlStr, id).Scan(&bidMessage.blockId,
		&bidMessage.eventId, &bidMessage.preHash,
		&bidMessage.hash, &bidMessage.nodeMac,
		&bidMessage.nodeIP, &bidMessage.nodePe)
	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	if err != nil {
		return
	}
	return
}
func NowBidHash(db *sql.DB, id int64)(hash string) {
	hash = ""
	sqlStr := `select hash from bid_table where eventId in( 
    select MAX(eventId) from bid_table where eventId <= ?);`
	err := db.QueryRow(sqlStr, id).Scan(&hash)
	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	if err != nil {
		log.Println(err)
	}
	return
}
func QueryBidPubKeyByAddr(db *sql.DB, addr string) (pubKey []byte) {
	sqlStr := `select nodePe from bid_table where nodeIP=?;`
	err := db.QueryRow(sqlStr, addr).Scan(&pubKey)
	if err != nil {
		log.Println(err)
	}
	return
}
func QueryOneBrTrf(db *sql.DB, id int)(brtrf brTrf, err error) {
	sqlStr := `select blockId, blockType, preHash, hash,
		eventId, srcAddress, dstAddress, messageHash 
		from brTrf_table where blockId=?;`
	err = db.QueryRow(sqlStr, id).Scan(&brtrf.blockId,
		&brtrf.blockType, &brtrf.preHash, &brtrf.hash,
		&brtrf.eventId, &brtrf.srcAddress,
		&brtrf.dstAddress, &brtrf.messageHash)
	if err != nil {
		return
	}
	return
}
func NowBrTrfHash(db *sql.DB, id int64)(hash string) {
	hash = ""
	sqlStr := `select hash from brtrf_table where eventId in( 
    select MAX(eventId) from brtrf_table where eventId <= ?);`
	err := db.QueryRow(sqlStr, id).Scan(&hash)
	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	if err != nil {
		log.Println(err)
	}
	return
}
func QueryOneBTrf(db *sql.DB, seqId int64)(btrf bTrf, err error) {
	sqlStr := `select blockId, blockType, preHash, hash, 
       eventId, srcAddress, dstAddress, 
       messageHash from bTrf_table where eventId=?;`
	err = db.QueryRow(sqlStr, seqId).Scan(&btrf.blockId, &btrf.blockType,
		&btrf.preHash, &btrf.hash, &btrf.eventId, &btrf.srcAddress,
		&btrf.dstAddress, &btrf.messageHash)
	if err != nil {
		return
	}
	return
}
func QueryBTrfIDByHash(db *sql.DB, digest, clientAddr string)(eventId int64) {
	sqlStr := `select eventId from bTrf_table where messageHash=? and srcAddress=?;`
	err := db.QueryRow(sqlStr, digest, clientAddr).Scan(&eventId)
	if err != nil {
		log.Println(err)
		return 0
	}
	return
}
func QueryBTrfBySeq(db *sql.DB, eventId int64)(msgHash string) {
	sqlStr := `select messageHash from bTrf_table where eventId=?;`
	err := db.QueryRow(sqlStr, eventId).Scan(&msgHash)
	if err != nil {
		log.Println(err)
	}
	return
}
func NowBTrfHash(db *sql.DB, id int64)(hash string) {
	hash = ""
	sqlStr := `select hash from btrf_table where eventId in( 
    select MAX(eventId) from btrf_table where eventId <= ?);`
	err := db.QueryRow(sqlStr, id).Scan(&hash)
	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	if err != nil {
		log.Println(err)
	}
	return
}
func QueryBTrfHashByEventId(db *sql.DB, id int64)(hash string) {
	hash = ""
	sqlStr := `select hash from btrf_table where eventId=?;`
	err := db.QueryRow(sqlStr, id).Scan(&hash)
	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	if err != nil {
		log.Println(err)
	}
	return
}

func QueryOneClient(db *sql.DB, id int)(c clientBuffer, err error) {
	sqlStr := `select clientId, message, digest, timeStamp, operation, 
       clientAddress from client_request where clientId=?;`
	err = db.QueryRow(sqlStr, id).Scan(&c.bufferId, &c.message,
		&c.digest, &c.timeStamp, &c.operation, &c.clientAddress)
	if err != nil {
		return
	}
	return
}
func QueryOneCommit(db *sql.DB, nodeId int, eventId uint64)(c commit, err error) {
	sqlStr := `select nodeId, eventId, view, digest 
		from commit_table where nodeId=? and eventId=?;`
	err = db.QueryRow(sqlStr, nodeId, eventId).Scan(&c.nodeId,
		&c.eventId, &c.view, &c.digest)
	if err != nil {
		return
	}
	return
}
func QueryOneEventByID(db *sql.DB, eventId int64)(e event, err error){
	sqlStr := `select eventId, view, message, msgDigest, digest, timeStamp, operation, 
       clientAddress from event_table where eventId=?;`
	err = db.QueryRow(sqlStr, eventId).Scan(&e.eventId, &e.view, &e.message,
		&e.msgDigest, &e.digest, &e.timeStamp, &e.operation, &e.clientAddress)
	if err != nil {
		return
	}
	return
}
func QueryEventDigestByID(db *sql.DB, eventId int64, view int)(digest string){
	sqlStr := `select digest from event_table where eventId=? and view=?;`
	err := db.QueryRow(sqlStr, eventId, view).Scan(digest)
	if err != nil {
		return ""
	}
	return
}
func QueryOneEventByDigest(db *sql.DB, digest string)(e event, err error) {
	sqlStr := `select eventId, view, message, msgDigest, digest, timeStamp, operation, 
       clientAddress from event_table where digest=?;`
	err = db.QueryRow(sqlStr, digest).Scan(&e.eventId, &e.view, &e.message,
		&e.msgDigest, &e.digest, &e.timeStamp, &e.operation, &e.clientAddress)
	if err != nil {
		return
	}
	return
}
func QueryEventMaxSeq(db *sql.DB)(eId int64) {
	var seqRowNum,checkRowNum int
	sqlStr := `select COUNT(*) from event_table;`
	err := db.QueryRow(sqlStr).Scan(&seqRowNum)
	if err != nil {
		log.Println(err)
	}
	if seqRowNum > 0{
		sqlStr := `select MAX(eventId) from event_table;`
		err := db.QueryRow(sqlStr).Scan(&eId)
		if err != nil {
			log.Println(err)
		}
	}else{
		sqlStr := `select COUNT(*) from check_point;`
		err := db.QueryRow(sqlStr).Scan(&checkRowNum)
		if err != nil {
			log.Println(err)
		}
		if checkRowNum > 0{
			sqlStr := `select MAX(eventId) from check_point;`
			err := db.QueryRow(sqlStr).Scan(&eId)
			if err != nil {
				log.Println(err)
			}
		}else{
			eId = 0
		}
	}
	return
}
func QueryOnePrepare(db *sql.DB, nodeId int, eventId int64)(p prepare, err error) {
	sqlStr := `select nodeId, eventId, view, digest 
		from prepare_table where nodeId=? and eventId=?;`
	err = db.QueryRow(sqlStr, nodeId, eventId).Scan(&p.NodeId,
		&p.EventId, &p.View, &p.Digest)
	if err != nil {
		return
	}
	return
}
func QueryOnePrePrepare(db *sql.DB, eventId int64)(pp prePrepare, err error) {
	sqlStr := `select eventId, view, message, digest
		from prePrepare_table where eventId=?;`
	err = db.QueryRow(sqlStr, eventId).Scan(&pp.EventId,
		&pp.View, &pp.Message, &pp.Digest)
	if err != nil {
		return
	}
	return
}
func MaxPrePrepare(db *sql.DB)(maxSeq int64) {
	sqlStr := `select MAX(eventId) from prePrepare_table;`
	err := db.QueryRow(sqlStr).Scan(&maxSeq)
	if err != nil {
		log.Println(err)
	}
	return
}

func QueryOneReply(db *sql.DB, id int)(r reply, err error) {
	sqlStr := `select nodeId, view, response, timestamp 
				from reply_table where nodeId=?;`
	err = db.QueryRow(sqlStr, id).Scan(&r.nodeId, &r.view,
		&r.response, &r.timeStamp)
	if err != nil {
		return
	}
	return
}
func QueryCheckDigestByID(db *sql.DB, id int64) (digest string, err error){
	sqlStr := `select digest from check_point where eventId=?;`
	err = db.QueryRow(sqlStr, id).Scan(digest)
	if err != nil {
		return
	}
	return
}
func NowCheckPoint(db *sql.DB)(checkPointId int64){
	sqlStr := `select MAX(eventId) from check_point;`
	err := db.QueryRow(sqlStr).Scan(checkPoint{})
	if err != nil {
		return
	}
	return
}


//多行查询
func QueryRowsBid(db *sql.DB, n int) (rowNum int) {
	rowNum = 0
	sqlStr := "select * from bid_table where blockId>?;"
	rows, err := db.Query(sqlStr, n)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	defer rows.Close() //释放连接
	for rows.Next() {
		rowNum++
	}
	return
}
func QueryRowsBrTrf(db *sql.DB, n int) (rowNum int) {
	rowNum = 0
	sqlStr := "select * from brTrf_table where blockId>?;"
	rows, err := db.Query(sqlStr, n)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	defer rows.Close() //释放连接
	for rows.Next() {
		rowNum++
	}
	return
}
func QueryRowsBTrf(db *sql.DB, n int) (rowNum int) {
	rowNum = 0
	sqlStr := `select *from bTrf_table where blockId>?;`
	rows, err := db.Query(sqlStr, n)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	defer rows.Close() //释放连接
	for rows.Next() {
		rowNum++
	}
	return
}
func QueryRowsClient(db *sql.DB, n int) {
	sqlStr := `select clientId, message, digest, timeStamp, operation, 
       clientAddress from client_request where clientId>?;`
	rows, err := db.Query(sqlStr, n)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	defer rows.Close() //释放连接
	for rows.Next() {
		var bidMessage bid
		err = rows.Scan(&bidMessage.blockId, &bidMessage.eventId,
			&bidMessage.preHash, &bidMessage.hash, &bidMessage.nodeMac, &bidMessage.nodeIP, &bidMessage.nodePe)
		if err != nil {
			fmt.Printf("query scan failed, err:%v\n", err)
			return
		}
		fmt.Println("query:", bidMessage)
	}
	return
}
func QueryRowsCommit(db *sql.DB, digest string) (rowNum int) {
	rowNum = 0
	sqlStr := `select COUNT(*) from commit_table where digest = ?;`
	err := db.QueryRow(sqlStr, digest).Scan(&rowNum)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	return
}
func QueryRowsPrepare(db *sql.DB, eventId int64) (rowNum int) {
	rowNum = 0		//默认查询到0条数据
	sqlStr := `select COUNT(*) from prepare_table where eventId=?;`
	err := db.QueryRow(sqlStr, eventId).Scan(&rowNum)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	return
}
func QueryRowsPrepareByDigest(db *sql.DB, digest string) (rowNum int) {
	rowNum = 0
	sqlStr := `select COUNT(*) from prepare_table where digest=?;`
	err := db.QueryRow(sqlStr, digest).Scan(&rowNum)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	return
}
func QueryRowsPrePrepare(db *sql.DB, eventId int64) (rowNum int) {
	rowNum = 0
	sqlStr := `select * from prePrepare_table where eventId=?;`
	rows, err := db.Query(sqlStr, eventId)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	defer rows.Close() //释放连接
	for rows.Next() {
		rowNum++
	}
	return
}
func QueryRowsReply(db *sql.DB, n int) {
	sqlStr := `select nodeId, view, response, timestamp 
		from reply_table where nodeId>?;`
	rows, err := db.Query(sqlStr, n)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	defer rows.Close() //释放连接
	for rows.Next() {
		var bidMessage bid
		err = rows.Scan(&bidMessage.blockId, &bidMessage.eventId,
			&bidMessage.preHash, &bidMessage.hash, &bidMessage.nodeMac, &bidMessage.nodeIP, &bidMessage.nodePe)
		if err != nil {
			fmt.Printf("query scan failed, err:%v\n", err)
			return
		}
		fmt.Println("query:", bidMessage)
	}
	return
}
func QueryRowsCheckPoint(db *sql.DB, eventId int64, digest string) (rowNum int){
	sqlStr := `select COUNT(*) from check_point 
	where eventId=? and digest=?;`
	err := db.QueryRow(sqlStr, eventId, digest).Scan(&rowNum)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	return
}
func GetCheckPointString(db *sql.DB)(checkString string){
	sqlStr := `select eventId, nodeId, digest from check_point 
where eventId in(select MIN(eventId) from check_point);`
	rows, err := db.Query(sqlStr)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	defer rows.Close() //释放连接
	ckMap := make(map[int]string)
	i := 0
	for rows.Next() {
		if i > nodeCount * 2 / 3{
			break
		}
		checkMessage := new(checkPoint)
		err = rows.Scan(&checkMessage.EventId, &checkMessage.NodeId,
			&checkMessage.Digest)
		if err != nil {
			fmt.Printf("query scan failed, err:%v\n", err)
			return
		}
		cp, err := json.Marshal(checkMessage)
		if err != nil{
			fmt.Printf("json format failed, err:%v\n", err)
			return
		}
		ckMap[checkMessage.NodeId] = string(cp)
		i++
	}
	checkByte,err := json.Marshal(ckMap)
	if err != nil{
		fmt.Printf("map to json failed, err:%v\n", err)
		return
	}
	checkString = string(checkByte)
	return
}
func GetPrePrepareString(db *sql.DB)(prePreString string){
	sqlStr := `select eventId, view, digest from preprepare_table 
where prepared = "Yes";`
	rows, err := db.Query(sqlStr)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	defer rows.Close() //释放连接
	pPreMap := make(map[int64]string)
	for rows.Next() {
		prePreMessage := new(prePrepare)
		err = rows.Scan(&prePreMessage.EventId, &prePreMessage.View,
			 &prePreMessage.Digest)
		if err != nil {
			fmt.Printf("query scan failed, err:%v\n", err)
			return
		}
		cp, err := json.Marshal(prePreMessage)
		if err != nil{
			fmt.Printf("json format failed, err:%v\n", err)
			return
		}
		pPreMap[prePreMessage.EventId] = string(cp)
		//log.Println(string(cp))
	}
	pPreByte,err := json.Marshal(pPreMap)
	if err != nil{
		fmt.Printf("map to json failed, err:%v\n", err)
		return
	}
	prePreString = string(pPreByte)
	return
}

func GetPPreAndPreString(db *sql.DB)(prePreString string){
	sqlStr := `select eventId, view, message, digest from preprepare_table 
where prepared = "Yes";`
	rows, err := db.Query(sqlStr)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	defer rows.Close() //释放连接
	pPreMap := make(map[string]string)
	for rows.Next() {
		prePreMessage := new(prePrepare)
		err = rows.Scan(&prePreMessage.EventId, &prePreMessage.View,
			&prePreMessage.Message, &prePreMessage.Digest)
		if err != nil {
			fmt.Printf("query scan failed, err:%v\n", err)
			return
		}
		cp, err := json.Marshal(prePreMessage)
		if err != nil{
			fmt.Printf("json format failed, err:%v\n", err)
			return
		}
		pPreMap[string(cp)] = GetPrepareString(db, prePreMessage.EventId)
	}
	pPreByte,err := json.Marshal(pPreMap)
	if err != nil{
		fmt.Printf("map to json failed, err:%v\n", err)
		return
	}
	prePreString = string(pPreByte)
	return
}
func GetPrepareString(db *sql.DB, sequenceId int64)(preString string){
	sqlStr := `select eventId, nodeId, view, digest from prepare_table where eventId=?;`
	rows, err := db.Query(sqlStr,sequenceId)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	defer rows.Close() //释放连接
	preMap := make(map[int]string)
	i := 0
	for rows.Next() {
		if i > nodeCount * 2 / 3{
			break
		}
		preMessage := new(prepare)
		err = rows.Scan(&preMessage.EventId, &preMessage.NodeId,
			&preMessage.View, &preMessage.Digest)
		if err != nil {
			fmt.Printf("query scan failed, err:%v\n", err)
			return
		}
		cp, err := json.Marshal(preMessage)
		if err != nil{
			fmt.Printf("json format failed, err:%v\n", err)
			return
		}
		preMap[preMessage.NodeId] = string(cp)
		i++
	}
	preByte,err := json.Marshal(preMap)
	if err != nil{
		fmt.Printf("map to json failed, err:%v\n", err)
		return
	}
	preString = string(preByte)
	return
}

//、存在性查询
func ExistEventID(db *sql.DB, eventId int64, view int) (rowNum int){
	rowNum = 0
	sqlStr := `select *	from event_table where eventId=? and view=?;`
	rows, err := db.Query(sqlStr, eventId, view)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	defer rows.Close() //释放连接
	for rows.Next() {
		rowNum++
	}
	return
}
func ExistEventDigest(db *sql.DB, digest string) (rowNum int){
	rowNum = 0
	sqlStr := `select COUNT(*)	from event_table where digest=?;`
	err := db.QueryRow(sqlStr, digest).Scan(&rowNum)
	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	return
}
func ExistEventDigestAndView(db *sql.DB, digest,view string) (rowNum int){
	rowNum = 0
	sqlStr := `select COUNT(*) from event_table where digest=? and view=?;`
	err := db.QueryRow(sqlStr, digest, view).Scan(&rowNum)

	if err != nil {
		fmt.Printf("query  failed, err:%v\n", err)
		return
	}
	return
}
//插入
func InsertBid(db *sql.DB, eventId, preHash,
	hash, nodeMac, nodeIP, nodePe string) (insertID int64) {
	sqlStr := `insert into bid_table(eventId, preHash, 
                      hash, nodeMac, nodeIP, nodePe)values(?,?,?,?,?,?)`
	res, err := db.Exec(sqlStr, eventId, preHash,
		hash, nodeMac, nodeIP, nodePe)
	if err != nil {
		fmt.Println("exec failed;err:", err)
		return
	}
	id, err := res.LastInsertId() //新插入数据的id
	if err != nil {
		fmt.Println("result return failed;err:", err)
		return
	}
	insertID = id
	return
}
func InsertBrTrf(db *sql.DB, blockType, preHash, hash,
	eventId, srcAddress, dstAddress, messageHash string) (insertID int64){
	sqlStr := `insert into brTrf_table( blockType, preHash, hash, 
                      eventId, srcAddress, dstAddress, 
                      messageHash)values(?,?,?,?,?,?,?)`
	res, err := db.Exec(sqlStr, blockType, preHash, hash,
		eventId, srcAddress, dstAddress, messageHash)
	if err != nil {
		fmt.Println("exec failed;err:", err)
		return
	}
	id, err := res.LastInsertId() //新插入数据的id
	if err != nil {
		fmt.Println("result return failed;err:", err)
		return
	}
	insertID = id
	return
}
func InsertBTrf(db *sql.DB, blockType, preHash, hash,
	eventId, srcAddress, dstAddress, messageHash string) {
	sqlStr := `insert into bTrf_table( blockType, preHash, hash, 
                      eventId, srcAddress, dstAddress, 
                      messageHash)values(?,?,?,?,?,?,?)`
	res, err := db.Exec(sqlStr, blockType, preHash, hash,
		eventId, srcAddress, dstAddress, messageHash)
	if err != nil {
		fmt.Println("exec failed;err:", err)
		return
	}
	id, err := res.LastInsertId() //新插入数据的id
	if err != nil {
		fmt.Println("result return failed;err:", err)
		return
	}
	fmt.Println(id)
}
func InsertClient(db *sql.DB, bufferId, message, digest, timeStamp, operation,
	clientAddress string) (insertID int64){
	sqlStr := `insert into client_request(bufferId, message, digest, timeStamp, 
                           operation, clientAddress)values(?,?,?,?,?,?)`
	res, err := db.Exec(sqlStr, bufferId, message, digest,
		timeStamp, operation, clientAddress)
	if err != nil {
		fmt.Println("exec failed;err:", err)
		return
	}
	id, err := res.LastInsertId() //新插入数据的id
	if err != nil {
		fmt.Println("result return failed;err:", err)
		return
	}
	insertID = id
	return
}
func InsertCommit(db *sql.DB, nodeId, eventId, view, digest  string) (insertID int64) {
	sqlStr := `insert into commit_table(nodeId, eventId, view, 
                    digest )values(?,?,?,?)`
	res, err := db.Exec(sqlStr, nodeId, eventId, view, digest)
	if err != nil {
		fmt.Println("exec failed;err:", err)
		return
	}
	id, err := res.LastInsertId() //新插入数据的id
	if err != nil {
		fmt.Println("result return failed;err:", err)
		return
	}
	insertID = id
	return
}
func InsertEvent(db *sql.DB, eventId, view, message, msgDigest, digest, timeStamp, operation,
	clientAddress string) (insertID int64) {
	sqlStr := `insert into event_table(eventId, view, message, msgDigest,
                        digest, timeStamp, operation, 
                        clientAddress)values(?,?,?,?,?,?,?,?)`

	res, err := db.Exec(sqlStr, eventId, view, message, msgDigest, digest, timeStamp,
		operation, clientAddress)
	if err != nil {
		fmt.Println("exec failed;err:", err)
		return
	}
	id, err := res.LastInsertId() //新插入数据的id
	if err != nil {
		fmt.Println("result return failed;err:", err)
		return
	}
	insertID = id
	return
}
func InsertCheckPoint(db *sql.DB, eventId int64, nodeId int, digest string) {
	sqlStr := `insert into check_point(eventId, nodeId, digest
		)values(?,?,?)`
	_, err := db.Exec(sqlStr, eventId, nodeId, digest)
	if err != nil {
		fmt.Println("exec failed;err:", err)
	}
}

//goland:noinspection ALL
func InsertPrepare(db *sql.DB, nodeId, eventId, view, digest  string) (insertID int64) {
	sqlStr := `insert into prepare_table(nodeId, eventId, view, 
                       digest)values(?,?,?,?)`//SQL查询语句
	res, err := db.Exec(sqlStr, nodeId, eventId, view,digest)
	if err != nil {
		fmt.Println("exec failed;err:", err)
		return
	}
	id, err := res.LastInsertId() //新插入数据的id
	if err != nil {
		fmt.Println("result return failed;err:", err)
		return
	}
	insertID = id//返回序列id
	return
}
func InsertPrePrepare(db *sql.DB, eventId, view, digest, message string) (insertID int64) {
	sqlStr := `insert into prePrepare_table(eventId, view,
                     message, digest)values(?,?,?,?)`
	res, err := db.Exec(sqlStr, eventId, view, message, digest)
	if err != nil {
		fmt.Println("insert pre-prepare exec failed;err:", err)
		return
	}
	id, err := res.LastInsertId() //新插入数据的id
	if err != nil {
		fmt.Println("result return failed;err:", err)
		return
	}
	insertID = id
	return
}
func InsertReply(db *sql.DB, nodeId, view, response, timestamp string) (insertID int64) {
	sqlStr := `insert into reply_table(nodeId, view, response, 
                      timestamp)values(?,?,?,?)`
	res, err := db.Exec(sqlStr, nodeId, view, response, timestamp)
	if err != nil {
		fmt.Println("exec failed;err:", err)
		return
	}
	id, err := res.LastInsertId() //新插入数据的id
	if err != nil {
		fmt.Println("result return failed;err:", err)
		return
	}
	insertID = id
	return
}

//更新
func Prepared(db *sql.DB, sequenceId int64){
	sqlStr := `update preprepare_table set prepared="Yes" where eventId=?`
	err := db.QueryRow(sqlStr, sequenceId)
	if err != nil {
		fmt.Println("exec failed;err:", err)
	}

}


//func UpdateReply(db *sql.DB, timeStamp, eventId uint64) {
//	sqlStr := "update bid_table set eventId=? where timeStamp=? "
//	res, err := db.Exec(sqlStr, timeStamp, eventId)
//	if err != nil {
//		fmt.Println("exec  failed;err:", err)
//		return
//	}
//	n, err := res.RowsAffected() // 操作影响的行数
//	if err != nil {
//		fmt.Println("result  return failed;err:", err)
//		return
//	}
//	fmt.Println(n)
//}

//删除
func DeleteBid(db *sql.DB, id int) {
	sqlStr := "delete from bid_table where blockId=?"
	res, err := db.Exec(sqlStr, id)
	if err != nil {
		fmt.Println("exec  failed;err:", err)
		return
	}
	n, err := res.RowsAffected()
	if err != nil {
		fmt.Println("result return  failed;err:", err)
		return
	}
	fmt.Println(n)
}
func DeleteBrTrf(db *sql.DB, id int) {
	sqlStr := "delete from brTrf_table where blockId=?"
	res, err := db.Exec(sqlStr, id)
	if err != nil {
		fmt.Println("exec  failed;err:", err)
		return
	}
	n, err := res.RowsAffected()
	if err != nil {
		fmt.Println("result return  failed;err:", err)
		return
	}
	fmt.Println(n)
}
func DeleteBTrf(db *sql.DB, id int) {
	sqlStr := "delete from bTrf_table where blockId=?"
	res, err := db.Exec(sqlStr, id)
	if err != nil {
		fmt.Println("exec  failed;err:", err)
		return
	}
	n, err := res.RowsAffected()
	if err != nil {
		fmt.Println("result return  failed;err:", err)
		return
	}
	fmt.Println(n)
}
func DeleteClient(db *sql.DB, id int) {
	sqlStr := "delete from client_buffer where bufferId=?"
	res, err := db.Exec(sqlStr, id)
	if err != nil {
		fmt.Println("exec  failed;err:", err)
		return
	}
	n, err := res.RowsAffected()
	if err != nil {
		fmt.Println("result return  failed;err:", err)
		return
	}
	fmt.Println(n)
}
func DeleteCommit(db *sql.DB, eventId int64) {
	sqlStr := "delete from commit_table where eventId<=?"
	_, err := db.Exec(sqlStr, eventId)
	if err != nil {
		fmt.Println("exec  failed;err:", err)
		return
	}
}
func DeleteEvent(db *sql.DB, id int64) {
	sqlStr := "delete from event_table where eventId<=?"
	_, err := db.Exec(sqlStr, id)
	if err != nil {
		fmt.Println("exec  failed;err:", err)
		return
	}
}
func DeletePrepare(db *sql.DB, eventId int64) {
	sqlStr := "delete from prepare_table where eventId<=?"
	_, err := db.Exec(sqlStr, eventId)
	if err != nil {
		fmt.Println("exec  failed;err:", err)
		return
	}
}
func DeletePrePrepare(db *sql.DB, eventId int64) {
	sqlStr := "delete from preprepare_table where eventId<=?"
	_, err := db.Exec(sqlStr, eventId)
	if err != nil {
		fmt.Println("exec  failed;err:", err)
		return
	}

}
func DeleteCheckPoint(db *sql.DB, eventId int64) {
	sqlStr := "delete from preprepare_table where eventId<?"
	_, err := db.Exec(sqlStr, eventId)
	if err != nil {
		fmt.Println("exec  failed;err:", err)
		return
	}

}

func CleanPrePrepare(db *sql.DB) {
	sqlStr := "delete from preprepare_table where 1=1;"
	_, err := db.Exec(sqlStr)
	if err != nil {
		fmt.Println("exec  failed;err:", err)
		return
	}

}
func DeleteReply(db *sql.DB, id int64) {
	sqlStr := "delete from reply_table where eventId=?"
	res, err := db.Exec(sqlStr, id)
	if err != nil {
		fmt.Println("exec  failed;err:", err)
		return
	}
	n, err := res.RowsAffected()
	if err != nil {
		fmt.Println("result return  failed;err:", err)
		return
	}
	fmt.Println(n)
}