func (store *MysqlStore) ObservationsAutour(db *sql.DB, x int, y int, z int, dist int, trollId int, amis []int, withTresors bool) ([]*Observation, error) {
	sql := "select auteur, num, date, type, nom, x, y, z from observation where"
	sql += " x>" + strconv.Itoa(x-dist-1) + " and x<" + strconv.Itoa(x+dist+1)
	sql += " and y>" + strconv.Itoa(y-dist-1) + " and y<" + strconv.Itoa(y+dist+1)
	sql += " and z>" + strconv.Itoa(z-dist/2-1) + " and z<" + strconv.Itoa(z+dist/2+1)
	if !withTresors {
		sql += " and type<>'tresor'"
	}
	sql += " and auteur in (" + strconv.Itoa(trollId)
	for _, id := range amis {
		sql += "," + strconv.Itoa(id)
	}
	sql += ") order by type, num, date desc"
	rows, err := db.Query(sql)
	observations := make([]*Observation, 0, 20)
	for rows.Next() {
		r := new(Observation)
		err = rows.Scan(&r.Auteur, &r.Num, &r.Date, &r.Type, &r.Nom, &r.X, &r.Y, &r.Z)
		if err != nil {
			return nil, err
		}
		if len(observations) > 0 && r.Num == observations[len(observations)-1].Num { // dédoublonnage
			continue
		}
		observations = append(observations, r)

	}
	rows.Close()
	return observations, nil
}

func getApplicationDeployments(db *sql.DB, a *models.Application, limit int) ([]*models.Deployment, error) {
	deployments := []*models.Deployment{}

	rows, err := db.Query(applicationDeploymentsStmt, a.Name, limit)
	if err != nil {
		return deployments, err
	}
	defer rows.Close()

	for rows.Next() {
		var state string
		d := &models.Deployment{}

		err = rows.Scan(&d.Id, &d.UserId, &d.TargetName, &d.CommitSha, &d.Branch, &d.Comment, &state, &d.CreatedAt)
		if err != nil {
			return deployments, err
		}

		d.State = models.DeploymentState(state)

		deployments = append(deployments, d)
	}

	if err := rows.Err(); err != nil {
		return deployments, err
	}

	return deployments, nil
}

func dbUpdateFromV8(db *sql.DB) error {
	stmt := `
UPDATE certificates SET fingerprint = replace(fingerprint, " ", "");
INSERT INTO schema (version, updated_at) VALUES (?, strftime("%s"));`
	_, err := db.Exec(stmt, 9)
	return err
}

func GetHistoryPlayers(db *sql.DB, days int) ([]HistoryPlayers, error) {
	var data []HistoryPlayers
	var query string
	switch days {
	case 1:
		query = "SELECT history_slots_used, history_date FROM history WHERE history_date > NOW() - INTERVAL 1 DAY"
	case 7:
		query = "SELECT a.history_slots_used, a.history_date FROM (SELECT history_slots_used, history_date, DATE_FORMAT(history_date, '%p%d%m%y') AS g FROM history WHERE history_date > NOW() - INTERVAL 1 WEEK GROUP BY g) AS a"
	case 30:
		query = "SELECT a.history_slots_used, a.history_date FROM (SELECT history_slots_used, history_date, DATE_FORMAT(history_date, '%d%m%y') AS g FROM history WHERE history_date > NOW() - INTERVAL 1 MONTH GROUP BY g) AS a"
	case 365:
		query = "SELECT a.history_slots_used, a.history_date FROM (SELECT history_slots_used, history_date, DATE_FORMAT(history_date, '%u%y') AS g FROM history WHERE history_date > NOW() - INTERVAL 1 YEAR GROUP BY g) AS a"
	}
	rows, err := db.Query(query)
	if err != nil {
		return data, err
	}
	defer rows.Close()
	for rows.Next() {
		var tmp HistoryPlayers
		if err := rows.Scan(&tmp.Total, &tmp.Date); err != nil {
			return data, err
		}
		data = append(data, tmp)
	}
	return data, nil
}

func runExec(ctx context.Context, db *sql.DB, query string) error {
	done := make(chan struct{})
	var (
		errMsg error
	)
	go func() {
		for {
			if _, err := db.Exec(query); err != nil {
				errMsg = err
				time.Sleep(time.Second)
				continue
			} else {
				errMsg = nil
				done <- struct{}{}
				break
			}
		}
	}()
	select {
	case <-done:
		return errMsg
	case <-ctx.Done():
		return fmt.Errorf("runExec %s timed out with %v / %v", query, ctx.Err(), errMsg)
	}
}

func do(db *sql.DB, sql string) {
	_, err := db.Exec(sql)
	if err == nil {
		return
	}
	panic(fmt.Sprintf("Error %v running SQL: %s", err, sql))
}

func v1CompatUpdateDatabase(
	ctx *grader.Context,
	db *sql.DB,
	run *grader.RunInfo,
) {
	_, err := db.Exec(
		`UPDATE
			Runs
		SET
			status = 'ready', verdict = ?, runtime = ?, memory = ?, score = ?,
			contest_score = ?, judged_by = ?
		WHERE
			run_id = ?;`,
		run.Result.Verdict,
		run.Result.Time,
		run.Result.Memory,
		run.Result.Score,
		run.Result.ContestScore,
		run.Result.JudgedBy,
		run.ID,
	)
	if err != nil {
		ctx.Log.Error("Error updating the database", "err", err, "run", run)
	}
}

func AllAgents(db *sql.DB, simid []byte, proto string) (ags []AgentInfo, err error) {
	s := `SELECT AgentId,Kind,Spec,Prototype,ParentId,EnterTime,ExitTime,Lifetime FROM
				Agents
			WHERE Agents.SimId = ?`

	var rows *sql.Rows
	if proto != "" {
		s += ` AND Agents.Prototype = ?`
		rows, err = db.Query(s, simid, proto)
	} else {
		rows, err = db.Query(s, simid)
	}
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		ai := AgentInfo{}
		var exit sql.NullInt64
		if err := rows.Scan(&ai.Id, &ai.Kind, &ai.Impl, &ai.Proto, &ai.Parent, &ai.Enter, &exit, &ai.Lifetime); err != nil {
			return nil, err
		}
		if !exit.Valid {
			exit.Int64 = -1
		}
		ai.Exit = int(exit.Int64)
		ags = append(ags, ai)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return ags, nil
}

// NewLedgerClosePump starts a background proc that continually watches the
// history database provided.  The watch is stopped after the provided context
// is cancelled.
//
// Every second, the proc spawned by calling this func will check to see
// if a new ledger has been imported (by ruby-horizon as of 2015-04-30, but
// should eventually end up being in this project).  If a new ledger is seen
// the the channel returned by this function emits
func NewLedgerClosePump(ctx context.Context, db *sql.DB) <-chan time.Time {
	result := make(chan time.Time)

	go func() {
		var lastSeenLedger int32
		for {
			select {
			case <-time.After(1 * time.Second):
				var latestLedger int32
				row := db.QueryRow("SELECT MAX(sequence) FROM history_ledgers")
				err := row.Scan(&latestLedger)

				if err != nil {
					log.Warn(ctx, "Failed to check latest ledger", err)
					break
				}

				if latestLedger > lastSeenLedger {
					log.Debugf(ctx, "saw new ledger: %d, prev: %d", latestLedger, lastSeenLedger)
					lastSeenLedger = latestLedger
					result <- time.Now()
				}

			case <-ctx.Done():
				log.Info(ctx, "canceling ledger pump")
				return
			}
		}
	}()

	return result
}

func DeployCumulative(db *sql.DB, simid []byte, proto string) (xys []XY, err error) {
	sql := `SELECT Time, IFNULL(Count, 0) FROM 
			TimeList LEFT JOIN
			(SELECT ti.Time AS Timestep,COUNT(*) AS Count FROM
				TimeList AS ti 
				JOIN Agents AS ag ON (ti.Time >= ag.EnterTime) AND (ag.ExitTime >= ti.Time OR ag.ExitTime IS NULL)
				WHERE
				    ti.SimId = ag.SimId
					AND ag.SimId = ?
					AND ag.Prototype = ?
				GROUP BY ti.Time
				ORDER BY ti.Time) AS foo ON foo.Timestep = TimeList.Time;`
	rows, err := db.Query(sql, simid, proto)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		xy := XY{}
		if err := rows.Scan(&xy.X, &xy.Y); err != nil {
			return nil, err
		}
		xys = append(xys, xy)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return xys, nil
}

func InvSeries(db *sql.DB, simid []byte, agent int, iso int) (xys []XY, err error) {
	sql := `SELECT ti.Time,SUM(cmp.MassFrac * inv.Quantity) FROM (
				Compositions AS cmp
				INNER JOIN Inventories AS inv ON inv.QualId = cmp.QualId
				INNER JOIN TimeList AS ti ON (ti.Time >= inv.StartTime AND ti.Time < inv.EndTime)
			) WHERE (
				inv.SimId = ? AND inv.SimId = cmp.SimId AND ti.SimId = inv.SimId
				AND inv.AgentId = ? AND cmp.NucId = ?
			) GROUP BY ti.Time,cmp.NucId;`
	rows, err := db.Query(sql, simid, agent, iso)
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		xy := XY{}
		if err := rows.Scan(&xy.X, &xy.Y); err != nil {
			return nil, err
		}
		xys = append(xys, xy)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return xys, nil
}

func (store *MysqlStore) SearchObservations(db *sql.DB, tok string, trollId int, amis []int) ([]*Observation, error) {
	sql := "select auteur, num, date, type, nom, x, y, z from observation where"
	if num, _ := strconv.Atoi(tok); num != 0 {
		sql += " num=" + tok
	} else {
		sql += " nom like '%" + tok + "%'"
	}
	sql += " and auteur in (" + strconv.Itoa(trollId)
	for _, id := range amis {
		sql += "," + strconv.Itoa(id)
	}
	sql += ") order by num, date desc limit 100"
	rows, err := db.Query(sql)
	observations := make([]*Observation, 0, 20)
	for rows.Next() {
		r := new(Observation)
		err = rows.Scan(&r.Auteur, &r.Num, &r.Date, &r.Type, &r.Nom, &r.X, &r.Y, &r.Z)
		if err != nil {
			return nil, err
		}
		if len(observations) > 0 && r.Num == observations[len(observations)-1].Num { // dédoublonnage
			continue
		}
		observations = append(observations, r)

	}
	rows.Close()
	return observations, nil
}

// supprime la vue de trollID puis sauvegarde des observations reçues par SOAP de MH, observées juste maintenant par trollId
func (store *MysqlStore) CleanAndSaveSoapItems(db *sql.DB, trollId int, items []*SoapItem) error {
	seconds := time.Now()
	sql := "delete from observation where auteur=?"
	_, err := db.Exec(sql, trollId)
	if err != nil {
		return err
	}
	sql = "insert into observation"
	sql += "        (auteur, num, date, type, nom, x, y, z)"
	sql += " values (      ?,  ?,    ?,    ?,   ?, ?, ?, ?)"
	for _, i := range items {
		var t string
		if i.Type == "TROLL" {
			t = "troll"
		} else if i.Type == "MONSTRE" {
			t = "monstre"
		} else if i.Type == "LIEU" {
			t = "lieu"
		} else if i.Type == "TRESOR" {
			t = "tresor"
		} else {
			continue
		}
		_, err = db.Exec(sql, trollId, i.Numero, seconds, t, i.Nom, i.PositionX, i.PositionY, i.PositionN)
		if err != nil {
			return err
		}
	}
	return nil
}

// sauvegarde des observations reçues par SOAP de MH, observées juste maintenant par trollId
func (store *MysqlStore) SaveSoapItems(db *sql.DB, trollId int, items []SoapItem) error {
	seconds := time.Now()
	sql := "insert into observation"
	sql += "        (auteur, num, date, type, nom, x, y, z)"
	sql += " values (      ?,  ?,    ?,    ?,   ?, ?, ?, ?)"
	sql += " on duplicate key update date=?, nom=?, x=?, y=?, z=?"
	for _, i := range items {
		var t string
		if i.Type == "TROLL" {
			t = "troll"
		} else if i.Type == "MONSTRE" {
			t = "monstre"
		} else if i.Type == "TRESOR" {
			t = "tresor"
		} else if i.Type == "LIEU" {
			t = "lieu"
		} else {
			continue
		}
		_, err := db.Exec(sql, trollId, i.Numero, seconds, t, i.Nom, i.PositionX, i.PositionY, i.PositionN, seconds, i.Nom, i.PositionX, i.PositionY, i.PositionN)
		if err != nil {
			return err
		}
	}
	return nil
}

// WithTransaction begins a transaction on the given database connection, calls
// 'f' with the transaction as a parameter, then ensures the transaction is
// committed if 'f' completes successfully or rolled back in the event of an
// error. If an error occurs when committing or rolling back the transaction, a
// 'FailedCloseError' is returned.
func WithTransaction(db *sql.DB, f WithTransactionFunc) (err error) {
	// Begin transaction
	tx, err := db.Begin()
	if err != nil {
		return err
	}

	// Run the passed function
	err = f(tx)

	var closeErr error
	var what string

	// Commit or rollback the transaction
	if err != nil {
		closeErr = tx.Rollback()
		what = "roll back transaction"
	} else {
		closeErr = tx.Commit()
		what = "commit transaction"
	}

	// If committing/rolling back the transaction caused an error, return a
	// FailedCloseError
	if closeErr != nil {
		err = &FailedCloseError{what, closeErr, err}
	}

	return err
}

func truncate(db *sql.DB) {
	query := "TRUNCATE TABLE user"
	_, err := db.Exec(query)
	if err != nil {
		log.Fatal("truncate error: ", err)
	}
}

func getUsers(db *sql.DB) []User {
	q := `
		SELECT
			ID, Name, Phone, Created
		FROM
			users
		ORDER BY datetime(Created) DESC
	`

	rows, err := db.Query(q)
	if err != nil {
		log.Fatalln(err)
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		user := User{}
		err = rows.Scan(&user.ID, &user.Name, &user.Phone, &user.Created)
		if err != nil {
			log.Fatalln(err)
		}
		users = append(users, user)
	}
	return users
}

// postgres does not have REPLACE INTO (upsert), so we use that custom
// one for Set operations instead
func altSet(db *sql.DB, key, value string) error {
	r, err := db.Query("SELECT replaceinto($1, $2)", key, value)
	if err != nil {
		return err
	}
	return r.Close()
}

// as per http://www.mssqltips.com/sqlservertip/2563/understanding-the-sql-server-select-version-command/
func serverVersion(db *sql.DB) (sqlVersion, sqlPartNumber, osVersion string, err error) {
	var v string
	if err = db.QueryRow("select @@version").Scan(&v); err != nil {
		return "", "", "", err
	}
	a := strings.SplitN(v, "\n", -1)
	if len(a) < 4 {
		return "", "", "", errors.New("SQL Server version string must have at least 4 lines: " + v)
	}
	for i := range a {
		a[i] = strings.Trim(a[i], " \t")
	}
	l1 := strings.SplitN(a[0], "-", -1)
	if len(l1) != 2 {
		return "", "", "", errors.New("SQL Server version first line must have - in it: " + v)
	}
	i := strings.Index(a[3], " on ")
	if i < 0 {
		return "", "", "", errors.New("SQL Server version fourth line must have 'on' in it: " + v)
	}
	sqlVersion = l1[0] + a[3][:i]
	osVersion = a[3][i+4:]
	sqlPartNumber = strings.Trim(l1[1], " ")
	l12 := strings.SplitN(sqlPartNumber, " ", -1)
	if len(l12) < 2 {
		return "", "", "", errors.New("SQL Server version first line must have space after part number in it: " + v)
	}
	sqlPartNumber = l12[0]
	return sqlVersion, sqlPartNumber, osVersion, nil
}

func QueryRowSql(db *sql.DB, sqlStmt string, params ...interface{}) (row *sql.Row) {
	if db == nil {
		return
	}
	row = db.QueryRow(sqlStmt, params...)
	return
}