Minedall/userdb.go

package main

import (
	"database/sql"
	"fmt"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

// Inital func to ensure database is properly setup
func dbSetup() {
	database, _ := sql.Open("sqlite3", config.DbFile)
	defer database.Close()
	statement, _ := database.Prepare("CREATE TABLE IF NOT EXISTS users (email TEXT PRIMARY KEY, discordID TEXT, mcUser TEXT, lastSeen TEXT, lastReported TEXT)")
	statement.Exec()
	statement, _ = database.Prepare("CREATE TABLE IF NOT EXISTS reports (email TEXT PRIMARY KEY, reportedBy TEXT, reportMsg TEXT, reportTime TEXT)")
	statement.Exec()
}

// Write a user to database, also update last seen to time.Now()
func (u User) Write() error {
	database, _ := sql.Open("sqlite3", config.DbFile)
	defer database.Close()
	if u.Email == "" || u.DiscordID == "" || u.McUser == "" {
		log.LogDebug(fmt.Sprintf("Attempted to insert invalid user. DENIED.\n%+v", u))
		return nil
	}
	statement, err := database.Prepare("INSERT INTO users (email, discordID, mcUser, lastSeen, lastReported) VALUES (?, ?, ?, ?, ?)")
	if err != nil {
		return err
	}
	log.LogDebug(fmt.Sprintf("Running insert on %+v", u))
	statement.Exec(u.Email, u.DiscordID, u.McUser, time.Now(), u.LastReported)
	return nil
}

// Delete a user from the database.
func (u User) Delete() error {
	database, err := sql.Open("sqlite3", config.DbFile)
	if err != nil {
		return err
	}
	defer database.Close()
	statement, err := database.Prepare("DELETE FROM users WHERE email=? mcUser=? discordID=?")
	if err != nil {
		return err
	}
	_, err = statement.Exec(u.Email, u.McUser, u.DiscordID)
	return err
}

// Get a user by a field (deprecated?)
func (u User) GetUserBy(userfield string) {
	database, _ := sql.Open("sqlite3", config.DbFile)
	defer database.Close()
	rows, _ := database.Query("SELECT * FROM users WHERE ? = ?", userfield, userGetField(&u, userfield))
	for rows.Next() {
		rows.Scan(&u.Email, &u.DiscordID, &u.McUser, &u.LastSeen, &u.LastReported)
	}
}

// Retrieve a user and fill in missing values
func (u *User) Retrieve() error {
	database, _ := sql.Open("sqlite3", config.DbFile)
	defer database.Close()
	var parms []interface{}
	statement := "SELECT * FROM users WHERE"
	first := true
	if u.DiscordID != "" {
		parms = append(parms, u.DiscordID)
		statement += " discordID = ? "
		first = false
	}
	if u.Email != "" {
		parms = append(parms, u.Email)
		if !first {
			statement += " AND "
		} else {
			first = false
		}
		statement += " email = ?"
	}
	if u.McUser != "" {
		parms = append(parms, u.McUser)
		if !first {
			statement += " AND "
		} else {
			first = false
		}
		statement += " mcUser = ?"
	}
	log.LogDebug(fmt.Sprintf("Attempting to query using the following statement: %+v\nParams: %+v", statement, parms))
	rows, err := database.Query(statement, parms...)
	if err != nil {
		return err
	}
	for rows.Next() {
		rows.Scan(&u.Email, &u.DiscordID, &u.McUser, &u.LastSeen, &u.LastReported)
	}
	go u.Write()
	log.LogDebug(fmt.Sprintf("DB Returned: %+v", u))
	return nil

}

// Get a slice of all users within the database.
func GetAllUsers() []User {
	database, _ := sql.Open("sqlite3", config.DbFile)
	defer database.Close()
	rows, _ := database.Query("SELECT * FROM users")
	var users []User
	for rows.Next() {
		var u User // Don't need anything else for this
		rows.Scan(&u.Email, &u.DiscordID, &u.McUser, &u.LastSeen, &u.LastReported)
		users = append(users, u) // You wanna get all users, not just the last one right?
	}

	return users
}

// Get Reports per user via SITNET
func (u User) GetReports() []Report {
	database, _ := sql.Open("sqlite3", config.DbFile)
	defer database.Close()
	rows, _ := database.Query("SELECT * FROM reports WHERE email = ?", u.Email)
	var reports []Report
	for rows.Next() {
		var r Report
		rows.Scan(&r.Abuser, &r.Reporter, &r.Msg, &r.Time)
		reports = append(reports, r)
	}
	return reports
}

// Write a report to the database
func (r Report) Write() error {
	//(email TEXT PRIMARY KEY, reportedBy TEXT, reportMsg TEXT, reportTime TEXT)
	log.LogDebug(fmt.Sprintf("Attempting Adding report of %+v", r.Abuser))
	database, _ := sql.Open("sqlite3", config.DbFile)
	defer database.Close()
	statement, err := database.Prepare("INSERT INTO reports (email, reportedBy, reportMsg, reportTime) VALUES (?, ?, ?, ?)")
	if err != nil {
		log.LogDebug(fmt.Sprintf("Adding report of %+v failed with error %+v", r.Abuser, err))
		return err
	}
	_, err = statement.Exec(r.Abuser.Email, r.Reporter.Email, r.Msg, r.Time)
	if err != nil {
		log.LogDebug(fmt.Sprintf("Execution failed with error %+v", err))
		return err
	}
	return nil
}