func Encode(p string) string {
	key := []byte(sysMagicNumber)
	plaintext := []byte(p)

	block, err := aes.NewCipher(key)
	if err != nil {
		log.Debug("%v", err)
		return ""
	}

	// The IV needs to be unique, but not secure. Therefore it's common to
	// include it at the beginning of the ciphertext.
	ciphertext := make([]byte, aes.BlockSize+len(plaintext))
	iv := ciphertext[:aes.BlockSize]
	if _, err := io.ReadFull(rand.Reader, iv); err != nil {
		log.Debug("%v", err)
		return ""
	}

	stream := cipher.NewCFBEncrypter(block, iv)
	stream.XORKeyStream(ciphertext[aes.BlockSize:], plaintext)

	// It's important to remember that ciphertexts must be authenticated
	// (i.e. by using crypto/hmac) as well as being encrypted in order to
	// be secure.

	return fmt.Sprintf("%x\n", ciphertext)
}

func dbFindCustomersByCond(cond string) ([]Customer, int) {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	sqlCond, limit, offset := buildSqlCond(cond)
	log.Debug("get customers for %s", cond)
	querySql := fmt.Sprintf("SELECT id, name, cover_photo, desc, phone, email FROM user WHERE type=1 AND %s LIMIT %d OFFSET %d ", sqlCond, limit, offset)

	stmt, err := dbHandler.Prepare(querySql)
	if err != nil {
		log.Debug("querySql: %s", querySql)
		log.Error("Prepare failed : %v", err)
		return nil, http.StatusInternalServerError
	}
	defer stmt.Close()
	rows, err := stmt.Query()
	if err != nil {
		log.Fatal("Query customers failed, something changed on db schema? : %v ", err)
		return nil, http.StatusNotFound
	}
	defer rows.Close()

	customers := make([]Customer, 0, limit)
	for rows.Next() {
		var customerId sql.NullInt64
		var name, coverPhoto, desc, phone, email sql.NullString
		rows.Scan(&customerId, &name, &coverPhoto, &desc, &phone, &email)
		customers = append(customers, Customer{strconv.FormatInt(customerId.Int64, 10), name.String, coverPhoto.String, "", phone.String, email.String, nil})
	}
	return customers, http.StatusOK
}

func dbSaveCustomer(c *Customer) int {
	log.Debug("try to save user %v", c)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	var userType = 1
	if len(c.Email) == 0 {
		userType = 2
	}
	stmt, err := dbHandler.Prepare("UPDATE user SET type=?, name=?, email=?, cover_photo=?, phone=?, desc=? WHERE id=?")
	if err != nil {
		log.Error("%v", err)
		return http.StatusInternalServerError
	}
	defer stmt.Close()

	_, err = stmt.Exec(userType, c.Name, c.Email, c.CoverPhoto, c.Phone, c.Desc, c.Id)
	if err != nil {
		log.Error("%v", err)
		return http.StatusBadRequest
	}
	return http.StatusOK
}

func dbSearchCustomers(t string, p int) ([]Customer, int) {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	offset := customerPageLimit * (p - 1)
	querySql := fmt.Sprintf("select id, name, cover_photo, phone, desc, email from user where type=1 and (name like '%%%s%%' or phone like '%%%s%%' or email like '%%%s%%') order by id limit %d offset %d", t, t, t, customerPageLimit, offset)

	stmt, err := dbHandler.Prepare(querySql)
	if err != nil {
		log.Debug("querySql: %s", querySql)
		log.Error("Prepare failed : %v", err)
		return nil, http.StatusInternalServerError
	}

	defer stmt.Close()
	rows, err := stmt.Query()
	if err != nil {
		log.Fatal("Query customers failed, something changed on db schema? : %v ", err)
		return nil, http.StatusNotFound
	}
	defer rows.Close()

	customers := make([]Customer, 0)
	for rows.Next() {
		var customerId sql.NullInt64
		var name, coverPhoto, phone, desc, email sql.NullString
		rows.Scan(&customerId, &name, &coverPhoto, &phone, &desc, &email)
		customers = append(customers, Customer{strconv.FormatInt(customerId.Int64, 10), name.String, coverPhoto.String, desc.String, phone.String, email.String, nil})
	}
	return customers, http.StatusOK

}

func dbCreateCustomer(c *Customer) int {
	log.Debug("try to create user %v", c)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	var userType = 1
	if len(c.Email) == 0 {
		userType = 2
	}
	stmt, err := dbHandler.Prepare("INSERT INTO user (type, name, email, cover_photo, phone, desc) VALUES (?,?,?,?,?,?)")
	if err != nil {
		log.Error("%v", err)
		return http.StatusInternalServerError
	}
	defer stmt.Close()

	_, err = stmt.Exec(userType, c.Name, c.Email, c.CoverPhoto, c.Phone, c.Desc)
	if err != nil {
		log.Error("%v", err)
		return http.StatusBadRequest
	}
	return http.StatusOK
}

func dbFindCustomer(c *Customer) int {
	log.Debug("get customer detail for %d", c.Id)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	querySql := "SELECT id, name, cover_photo, desc, phone, email FROM user WHERE type in (1,2) AND id=? "
	var id sql.NullInt64
	var name, coverPhoto, desc, phone, email sql.NullString
	err = dbHandler.QueryRow(querySql, c.Id).Scan(&id, &name, &coverPhoto, &desc, &phone, &email)
	if err != nil {
		if err == sql.ErrNoRows {
			log.Info("No customer found for %d", c.Id)
			return http.StatusNotFound
		} else {
			log.Debug("sql : %s", querySql)
			log.Error("DB query failed: %v", err)
			return http.StatusInternalServerError
		}
	}

	c.Name = name.String
	c.CoverPhoto = coverPhoto.String
	c.Desc = desc.String
	c.Phone = phone.String
	c.Email = email.String

	queryLogSql := "SELECT operation_type, operation_detail, operation_time FROM user_log WHERE user_id=? ORDER BY id DESC LIMIT 100"
	rows, err := dbHandler.Query(queryLogSql, c.Id)
	defer rows.Close()

	logs := make([]CustomerLog, 0, 100)
	for rows.Next() {
		var operation_type, operation_detail sql.NullString
		var operation_time time.Time
		rows.Scan(&operation_type, &operation_detail, &operation_time)
		logs = append(logs, CustomerLog{c.Id, operation_type.String, operation_detail.String, operation_time.Format(timeLayout)})
	}
	c.Logs = logs

	return http.StatusOK
}

func findCustomersByCond(req *restful.Request, resp *restful.Response) {
	log.Debug("try to find customers with cond : %s", req.PathParameter("cond"))
	cond := req.PathParameter("cond")
	customers, ret := dbFindCustomersByCond(cond)
	if ret == http.StatusOK {
		resp.WriteEntity(customers)
	} else {
		resp.WriteErrorString(ret, http.StatusText(ret))
	}
}

func sendRecover(req *restful.Request, resp *restful.Response) {
	var ru VerifyUser
	err := req.ReadEntity(&ru)
	if err != nil {
		log.Debug("read recover user info %s failed", ru.Email)
		resp.WriteErrorString(http.StatusBadRequest, http.StatusText(http.StatusBadRequest))
	} else {
		exist := dbEmailExist(ru.Email)
		if !exist {
			log.Debug("not a valid email %s", ru.Email)
			resp.WriteErrorString(http.StatusBadRequest, http.StatusText(http.StatusBadRequest))
		} else {
			magic := auth.GenMagic(ru.Email, time.Now().String())
			dbInsertRecoverInfo(ru.Email, magic)
			go mails.SendRecoverMail(ru.Email, magic)
			resp.WriteHeader(http.StatusOK)
		}
	}
}

func signoutUser(req *restful.Request, resp *restful.Response) {
	var id SignoutUser
	err := req.ReadEntity(&id)
	if err == nil {
		auth.DelCookie(req, resp, id.Id)
	} else {
		log.Debug("sign out id %s failed", id.Id)
		resp.WriteErrorString(http.StatusBadRequest, http.StatusText(http.StatusBadRequest))
	}
}

func AuthFilter(req *restful.Request, resp *restful.Response, chain *restful.FilterChain) {
	b, userid := AuthHandler(req.Request, resp.ResponseWriter)
	if !b {
		log.Debug("unauthorized request %s %s", req.Request.Method, req.Request.URL)
		resp.WriteErrorString(http.StatusUnauthorized, http.StatusText(http.StatusUnauthorized))
		return
	}
	req.SetAttribute("agsuserid", userid)
	chain.ProcessFilter(req, resp)
}

func updateReviewboardOwner(req *http.Request, id string) {
	log.Debug("Update Reviewboard Owner")
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	b, vid := AuthVisitorHandler(req, nil)
	if !b {
		log.Debug("Auth Visitor failed")
		return
	}
	var updateSql = "UPDATE reviewboard set customer_type=1, customer_id=? WHERE customer_type=2 AND customer_id=?"
	log.Error("Sql: %s", updateSql)
	_, err = dbHandler.Exec(updateSql, id, vid)
	if err != nil {
		log.Error("Sql: %s", updateSql)
		log.Error("Update reviewboard owner failed: %v", err)
	}
}

func verifyRecover(req *restful.Request, resp *restful.Response) {
	magic := req.PathParameter("recover_magic")
	log.Debug("magic is %s", magic)
	ret, id := dbVerifyRecover(magic)
	if ret == http.StatusOK {
		auth.AddCookie(req.Request, resp.ResponseWriter, strconv.FormatInt(id, 10))
		http.Redirect(resp.ResponseWriter, req.Request, "/#!/mypassword", http.StatusFound)

	} else {
		resp.WriteErrorString(ret, http.StatusText(ret))
	}
}

func Decode(c string) string {
	key := []byte(sysMagicNumber)
	ciphertext, _ := hex.DecodeString(c)

	block, err := aes.NewCipher(key)
	if err != nil {
		log.Debug("%v", err)
		return ""
	}

	// The IV needs to be unique, but not secure. Therefore it's common to
	// include it at the beginning of the ciphertext.
	if len(ciphertext) < aes.BlockSize {
		log.Debug("ciphertext too short")
		return ""
	}
	iv := ciphertext[:aes.BlockSize]
	ciphertext = ciphertext[aes.BlockSize:]

	stream := cipher.NewCFBDecrypter(block, iv)
	stream.XORKeyStream(ciphertext, ciphertext)

	return fmt.Sprintf("%s\n", ciphertext)
}

func dbSearchCustomersCount(t string) (int64, int) {
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	querySql := fmt.Sprintf("select count(id) from user where type=1 and (name like '%%%s%%' or phone like '%%%s%%' or email like '%%%s%%') ", t, t, t)
	var n sql.NullInt64
	err = dbHandler.QueryRow(querySql).Scan(&n)
	if err != nil {
		log.Debug("sql : %s", querySql)
		log.Error("DB query failed: %v", err)
		return 0, http.StatusInternalServerError
	}
	return n.Int64, http.StatusOK
}

func AuthHandler(r *http.Request, w http.ResponseWriter) (bool, string) {
	s, err := CookieStore.Get(r, "ags-session")
	if err != nil {
		log.Debug("Cannot get session: %v", err)
		return false, ""
	}

	if s.Values["id"] == nil || s.Values["time"] == nil || s.Values["magic"] == nil {
		return false, ""
	}

	b := Check(s.Values["id"].(string), s.Values["time"].(string), s.Values["magic"].(string))
	if b == true {
		return true, s.Values["id"].(string)
	} else {
		return false, ""
	}
}

func dbFindUser(user *User) int {
	log.Debug("try to find user with id : %v", user.Id)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	if len(user.Id) == 0 {
		return http.StatusNotFound
	}

	stmt, err := dbHandler.Prepare("SELECT type, name, email, phone, cover_photo FROM user WHERE id=? LIMIT 1")
	if err != nil {
		log.Error("%v", err)
		return http.StatusInternalServerError
	}
	defer stmt.Close()

	var name, email, phone, cover_photo sql.NullString
	var user_type sql.NullInt64
	err = stmt.QueryRow(user.Id).Scan(&user_type, &name, &email, &phone, &cover_photo)
	if err != nil {
		log.Error("%v", err)
		if err == sql.ErrNoRows {
			return http.StatusNotFound
		} else {
			return http.StatusInternalServerError
		}
	}

	if !name.Valid {
		return http.StatusNotFound
	} else {
		user.Type = user_type.Int64
		user.Email = email.String
		user.Name = name.String
		user.Phone = phone.String
		user.CoverPhoto = cover_photo.String
		return http.StatusOK
	}
}

func dbUpdateUser(user *User) int {
	log.Debug("try to update user %v", user)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	stmt, err := dbHandler.Prepare("UPDATE user SET name=?, phone=?, cover_photo=? WHERE id=? ")
	if err != nil {
		log.Error("%v", err)
		return http.StatusInternalServerError
	}
	defer stmt.Close()

	_, err = stmt.Exec(user.Name, user.Phone, user.CoverPhoto, user.Id)
	if err != nil {
		log.Error("%v", err)
		return http.StatusBadRequest
	}
	return http.StatusOK
}

func saveVisitorSesstionTime(id string, t string) int {
	log.Debug("try to update user %s session time", id)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	stmt, err := dbHandler.Prepare("UPDATE visitor SET session_time=? WHERE id=? ")
	if err != nil {
		log.Error("prepare update visitor session time failed: %v", err)
		return http.StatusInternalServerError
	}
	defer stmt.Close()

	_, err = stmt.Exec(t, id)
	if err != nil {
		log.Error("execute update visitor session time failed: %v", err)
		return http.StatusInternalServerError
	}
	return http.StatusOK
}

func dbCheckUser(user *User) int {
	log.Debug("try to find user with id : %v | %v", user.Email, user.Pass)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	stmt, err := dbHandler.Prepare("SELECT id, type, name, email, phone, cover_photo FROM user WHERE email=? AND pass=? LIMIT 1")
	if err != nil {
		log.Error("%v", err)
		return http.StatusInternalServerError
	}
	defer stmt.Close()

	var id, user_type sql.NullInt64
	var name, email, phone, cover_photo sql.NullString
	err = stmt.QueryRow(user.Email, user.Pass).Scan(&id, &user_type, &name, &email, &phone, &cover_photo)
	if err != nil {
		log.Error("%v", err)
		if err == sql.ErrNoRows {
			return http.StatusNotFound
		} else {
			return http.StatusInternalServerError
		}
	}

	if !id.Valid {
		return http.StatusNotFound
	} else {
		user.Id = strconv.FormatInt(id.Int64, 10)
		user.Type = user_type.Int64
		user.Name = name.String
		user.Email = email.String
		user.Phone = phone.String
		user.CoverPhoto = cover_photo.String
		return http.StatusOK
	}
}

func dbDeleteUser(id string) int {
	log.Debug("try to delete user id %v", id)
	dbHandler, err := sql.Open("sqlite3", config.GetValue("DbFile"))
	if err != nil {
		log.Fatal("%v", err)
	}
	defer dbHandler.Close()

	stmt, err := dbHandler.Prepare("DELETE FROM user WHERE id=?")
	if err != nil {
		log.Error("%v", err)
		return http.StatusInternalServerError
	}
	defer stmt.Close()

	_, err = stmt.Exec(id)
	if err != nil {
		log.Error("%v", err)
		return http.StatusBadRequest
	}
	return http.StatusOK
}