package codeforcesbot

import (
	"database/sql"
	"fmt"
	"strings"

	_ "github.com/lib/pq"
)

var db *sql.DB

func SqlOpen() {
	var err error
	db, err = sql.Open("postgres", "port=5432 user=postgres password=postgres dbname=codeforcesbotdb sslmode=disable")
	//port是数据库的端口号，默认是5432，如果改了，这里一定要自定义；
	//user就是你数据库的登录帐号;
	//dbname就是你在数据库里面建立的数据库的名字;
	//sslmode就是安全验证模式;

	//还可以是这种方式打开
	//db, err := sql.Open("postgres", "postgres://pqgotest:password@localhost/pqgotest?sslmode=verify-full")
	CheckErr(err)
}
func SqlInsertCFid(qqid int64, cfid string) string{
	//插入数据
	stmt, err := db.Prepare("INSERT INTO codeforcesId(qqid, cfid) VALUES($1,$2)")
	CheckErr(err)

	res, err := stmt.Exec(qqid, cfid)

	if err != nil {
		return err.Error()
	}

	affect, err := res.RowsAffected()
	CheckErr(err)

	return fmt.Sprintf("OK rows affect:", affect)
}

func SqlQueryHandles(qqid int64) (ret []string){
	rows, err := db.Query(fmt.Sprintf("SELECT cfid FROM codeforcesid WHERE qqid = %v", qqid))
	CheckErr(err)
	for rows.Next() {
		var handle string
		CheckErr(rows.Scan(&handle))
		ret = append(ret, strings.TrimSpace(handle))
	}
	return ret
}

func SqlGetNotifiedGroup() (groups []int64) {
	rows, err := db.Query("SELECT groupid FROM codeforcesnotify")
	CheckErr(err)
	for rows.Next() {
		var groupid int64
		CheckErr(rows.Scan(&groupid))
		groups = append(groups, groupid)
	}
	return groups
}
/*
func sqlSelect() {
	//查询数据
	rows, err := db.Query("SELECT * FROM userinfo")
	CheckErr(err)

	println("-----------")
	for rows.Next() {
		var uid int
		var username string
		var department string
		var created string
		err = rows.Scan(&uid, &username, &department, &created)
		CheckErr(err)
		fmt.Println("uid = ", uid, "\nname = ", username, "\ndep = ", department, "\ncreated = ", created, "\n-----------")
	}
}


func sqlUpdate() {
	//更新数据
	stmt, err := db.Prepare("update userinfo set username=$1 where uid=$2")
	CheckErr(err)

	res, err := stmt.Exec("ficow", 1)
	CheckErr(err)

	affect, err := res.RowsAffected()
	CheckErr(err)

	fmt.Println("rows affect:", affect)
}
*/
func sqlClose() {
	db.Close()
}
func CheckErr(err error) {
	if err != nil {
		panic(err)
	}
}