func CheckBan(ip, name, filename, checksum string) (*BanInfo, error) {
	ban := new(BanInfo)
	ipban, err1 := checkIPBan(ip)
	err1NoRows := (err1 == sql.ErrNoRows)
	_, err2 := checkFileBan(checksum)
	err2NoRows := (err2 == sql.ErrNoRows)
	_, err3 := checkFilenameBan(filename)
	err3NoRows := (err3 == sql.ErrNoRows)
	_, err4 := checkUsernameBan(name)
	err4NoRows := (err4 == sql.ErrNoRows)
	if err1NoRows && err2NoRows && err3NoRows && err4NoRows {
		return nil, sql.ErrNoRows
	}
	if err1NoRows {
		return nil, err1
	}
	if err2NoRows {
		return nil, err2
	}
	if err3NoRows {
		return nil, err3
	}
	if err4NoRows {
		return nil, err4
	}
	if ipban != nil {
		ban.ID = 0
		ban.IP = string(ipban.IP)
		staff, _ := getStaffByID(ipban.StaffID)
		ban.Staff = staff.Username
		ban.Timestamp = ipban.IssuedAt
		ban.Expires = ipban.ExpiresAt
		ban.Permaban = ipban.Permanent
		ban.Reason = ipban.Message
		ban.StaffNote = ipban.StaffNote
		ban.AppealAt = ipban.AppealAt
		ban.CanAppeal = ipban.CanAppeal
		return ban, nil
	}
	return nil, gcutil.ErrNotImplemented
}
CheckBan returns banentry if a ban was found or a sql.ErrNoRows if not banned
name, filename and checksum may be empty strings and will be treated as not requested if done so
Deprecated: This method was created to support old functionality during the database refactor of april 2020
The code should be changed to reflect the new database design
package gcsql
import (
	"database/sql"
	"errors"
	"fmt"
	"html/template"
	"os"
	"path"
	"strings"
	"time"
	"github.com/gochan-org/gochan/pkg/config"
	"github.com/gochan-org/gochan/pkg/gcutil"
)
const GochanVersionKeyConstant = "gochan"
var (
	ErrNilBoard = errors.New("Board is nil")
)
func GetAllNondeletedMessageRaw() ([]MessagePostContainer, error) {
	const sql = `select posts.id, posts.message, posts.message_raw from DBPREFIXposts as posts
	WHERE posts.is_deleted = FALSE`
	rows, err := QuerySQL(sql)
	if err != nil {
		return nil, err
	}
	var messages []MessagePostContainer
	for rows.Next() {
		var message MessagePostContainer
		var formattedHTML template.HTML
		if err = rows.Scan(&message.ID, &formattedHTML, &message.MessageRaw); err != nil {
			return nil, err
		}
		message.Message = template.HTML(formattedHTML)
		messages = append(messages, message)
	}
	return messages, nil
}
func SetFormattedInDatabase(messages []MessagePostContainer) error {
	const sql = `UPDATE DBPREFIXposts
	SET message = ?
	WHERE id = ?`
	stmt, err := PrepareSQL(sql)
	if err != nil {
		return err
	}
	defer stmt.Close()
	for _, message := range messages {
		if _, err = stmt.Exec(string(message.Message), message.ID); err != nil {
			return err
		}
	}
	return err
}
func GetReplyCount(postID int) (int, error) {
	const sql = `SELECT COUNT(posts.id) FROM DBPREFIXposts as posts
	JOIN (
		SELECT threads.id FROM DBPREFIXthreads as threads
		JOIN DBPREFIXposts as posts
		ON posts.thread_id = threads.id
		WHERE posts.id = ?
	) as thread
	ON posts.thread_id = thread.id
	WHERE posts.is_deleted = FALSE`
	var count int
	err := QueryRowSQL(sql, interfaceSlice(postID), interfaceSlice(&count))
	return count, err
}
func GetReplyFileCount(postID int) (int, error) {
	const sql = `SELECT COUNT(files.id) from DBPREFIXfiles as files
	JOIN (SELECT posts.id FROM DBPREFIXposts as posts
		JOIN (
			SELECT threads.id FROM DBPREFIXthreads as threads
			JOIN DBPREFIXposts as posts
			ON posts.thread_id = threads.id
			WHERE posts.id = ?
		) as thread
		ON posts.thread_id = thread.id
		WHERE posts.is_deleted = FALSE) as posts
	ON posts.id = files.post_id`
	var count int
	err := QueryRowSQL(sql, interfaceSlice(postID), interfaceSlice(&count))
	return count, err
}
func GetStaffName(session string) (string, error) {
	const sql = `SELECT staff.username from DBPREFIXstaff as staff
	JOIN DBPREFIXsessions as sessions
	ON sessions.staff_id = staff.id
	WHERE sessions.data = ?`
	var username string
	err := QueryRowSQL(sql, interfaceSlice(session), interfaceSlice(&username))
	return username, err
}
func GetStaffBySession(session string) (*Staff, error) {
	const sql = `SELECT 
		staff.id, 
		staff.username, 
		staff.password_checksum, 
		staff.global_rank,
		staff.added_on,
		staff.last_login 
	FROM DBPREFIXstaff as staff
	JOIN DBPREFIXsessions as sessions
	ON sessions.staff_id = staff.id
	WHERE sessions.data = ?`
	staff := new(Staff)
	err := QueryRowSQL(sql, interfaceSlice(session), interfaceSlice(&staff.ID, &staff.Username, &staff.PasswordChecksum, &staff.Rank, &staff.AddedOn, &staff.LastActive))
	return staff, err
}
func GetStaffByName(name string) (*Staff, error) {
	const sql = `SELECT 
		staff.id, 
		staff.username, 
		staff.password_checksum, 
		staff.global_rank,
		staff.added_on,
		staff.last_login 
	FROM DBPREFIXstaff as staff
	WHERE staff.username = ?`
	staff := new(Staff)
	err := QueryRowSQL(sql, interfaceSlice(name), interfaceSlice(&staff.ID, &staff.Username, &staff.PasswordChecksum, &staff.Rank, &staff.AddedOn, &staff.LastActive))
	return staff, err
}
func getStaffByID(id int) (*Staff, error) {
	const sql = `SELECT 
		staff.id, 
		staff.username, 
		staff.password_checksum, 
		staff.global_rank,
		staff.added_on,
		staff.last_login 
	FROM DBPREFIXstaff as staff
	WHERE staff.id = ?`
	staff := new(Staff)
	err := QueryRowSQL(sql, interfaceSlice(id), interfaceSlice(&staff.ID, &staff.Username, &staff.PasswordChecksum, &staff.Rank, &staff.AddedOn, &staff.LastActive))
	return staff, err
}
func NewStaff(username, password string, rank int) error {
	const sql = `INSERT INTO DBPREFIXstaff (username, password_checksum, global_rank)
	VALUES (?, ?, ?)`
	_, err := ExecSQL(sql, username, gcutil.BcryptSum(password), rank)
	return err
}
func DeleteStaff(username string) error {
	const sql = `UPDATE DBPREFIXstaff SET username = ?, is_active = FALSE WHERE username = ?`
	_, err := ExecSQL(sql, gcutil.RandomString(45), username)
	return err
}
func getStaffID(username string) (int, error) {
	staff, err := GetStaffByName(username)
	if err != nil {
		return -1, err
	}
	return staff.ID, nil
}
func CreateSession(key, username string) error {
	const sql1 = `INSERT INTO DBPREFIXsessions (staff_id,data,expires) VALUES(?,?,?)`
	const sql2 = `UPDATE DBPREFIXstaff SET last_login = CURRENT_TIMESTAMP WHERE id = ?`
	staffID, err := getStaffID(username)
	if err != nil {
		return err
	}
	_, err = ExecSQL(sql1, staffID, key, time.Now().Add(time.Duration(time.Hour*730)))  
	if err != nil {
		return err
	}
	_, err = ExecSQL(sql2, staffID)
	return err
}
func PermanentlyRemoveDeletedPosts() error {
	const sql1 = `DELETE FROM DBPREFIXposts WHERE is_deleted`
	const sql2 = `DELETE FROM DBPREFIXthreads WHERE is_deleted`
	_, err := ExecSQL(sql1)
	if err != nil {
		return err
	}
	_, err = ExecSQL(sql2)
	return err
}
func OptimizeDatabase() error {
	tableRows, tablesErr := QuerySQL("SHOW TABLES")
	if tablesErr != nil {
		return tablesErr
	}
	for tableRows.Next() {
		var table string
		tableRows.Scan(&table)
		if _, err := ExecSQL("OPTIMIZE TABLE " + table); err != nil {
			return err
		}
	}
	return nil
}
func getBoardIDFromURIOrNil(URI string) *int {
	ID, err := getBoardIDFromURI(URI)
	if err != nil {
		return nil
	}
	return &ID
}
func CreateFileBan(fileChecksum, staffName string, permaban bool, staffNote, boardURI string) error {
	const sql = `INSERT INTO DBPREFIXfile_ban (board_id, staff_id, staff_note, checksum) VALUES board_id = ?, staff_id = ?, staff_note = ?, checksum = ?`
	staffID, err := getStaffID(staffName)
	if err != nil {
		return err
	}
	boardID := getBoardIDFromURIOrNil(boardURI)
	_, err = ExecSQL(sql, boardID, staffID, staffNote, fileChecksum)
	return err
}
func CreateFileNameBan(fileName string, isRegex bool, staffName string, permaban bool, staffNote, boardURI string) error {
	const sql = `INSERT INTO DBPREFIXfilename_ban (board_id, staff_id, staff_note, filename, is_regex) VALUES board_id = ?, staff_id = ?, staff_note = ?, filename = ?, is_regex = ?`
	staffID, err := getStaffID(staffName)
	if err != nil {
		return err
	}
	boardID := getBoardIDFromURIOrNil(boardURI)
	_, err = ExecSQL(sql, boardID, staffID, staffNote, fileName, isRegex)
	return err
}
func CreateUserNameBan(userName string, isRegex bool, staffName string, permaban bool, staffNote, boardURI string) error {
	const sql = `INSERT INTO DBPREFIXusername_ban (board_id, staff_id, staff_note, username, is_regex) VALUES board_id = ?, staff_id = ?, staff_note = ?, username = ?, is_regex = ?`
	staffID, err := getStaffID(staffName)
	if err != nil {
		return err
	}
	boardID := getBoardIDFromURIOrNil(boardURI)
	_, err = ExecSQL(sql, boardID, staffID, staffNote, userName, isRegex)
	return err
}
func CreateUserBan(IP string, threadBan bool, staffName, boardURI string, expires time.Time, permaban bool,
	staffNote, message string, canAppeal bool, appealAt time.Time) error {
	const sql = `INSERT INTO DBPREFIXip_ban (board_id, staff_id, staff_note, is_thread_ban, ip, appeal_at, expires_at, permanent, message, can_appeal, issued_at, copy_posted_text, is_active)
	VALUES (?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP,'OLD SYSTEM BAN, NO TEXT AVAILABLE',TRUE)`
	staffID, err := getStaffID(staffName)
	if err != nil {
		return err
	}
	boardID := getBoardIDFromURIOrNil(boardURI)
	_, err = ExecSQL(sql, boardID, staffID, staffNote, threadBan, IP, appealAt, expires, permaban, message, canAppeal)
	return err
}
func GetAllAccouncements() ([]Announcement, error) {
	const sql = `SELECT s.username, a.timestamp, a.subject, a.message FROM DBPREFIXannouncements AS a
	JOIN DBPREFIXstaff AS s
	ON a.staff_id = s.id
	ORDER BY a.id DESC`
	rows, err := QuerySQL(sql)
	if err != nil {
		return nil, err
	}
	var announcements []Announcement
	for rows.Next() {
		var announcement Announcement
		err = rows.Scan(&announcement.Poster, &announcement.Timestamp, &announcement.Subject, &announcement.Message)
		if err != nil {
			return nil, err
		}
		announcements = append(announcements, announcement)
	}
	return announcements, nil
}
func CreateBoard(values *Board) error {
	const maxThreads = 300
	const sqlINSERT = `INSERT INTO DBPREFIXboards (navbar_position, dir, uri, title, subtitle, description, max_file_size, max_threads, default_style, locked, anonymous_name, force_anonymous, autosage_after, no_images_after, max_message_length, min_message_length, allow_embeds, redirect_to_thread, require_file, enable_catalog, section_id)
	VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
	const sqlSELECT = "SELECT id FROM DBPREFIXboards WHERE dir = ?"
	if values == nil {
		return ErrNilBoard
	}
	_, err := ExecSQL(sqlINSERT, values.ListOrder, values.Dir, values.Dir, values.Title, values.Subtitle, values.Description, values.MaxFilesize, maxThreads, values.DefaultStyle, values.Locked, values.Anonymous, values.ForcedAnon, values.AutosageAfter, values.NoImagesAfter, values.MaxMessageLength, 1, values.EmbedsAllowed, values.RedirectToThread, values.RequireFile, values.EnableCatalog, values.Section)
	if err != nil {
		return err
	}
	return QueryRowSQL(sqlSELECT, interfaceSlice(values.Dir), interfaceSlice(&values.ID))
}
func GetBoardUris() (URIS []string, err error) {
	const sql = `SELECT uri FROM DBPREFIXboards`
	rows, err := QuerySQL(sql)
	if err != nil {
		return nil, err
	}
	var uris []string
	for rows.Next() {
		var uri string
		if err = rows.Scan(&uri); err != nil {
			return nil, err
		}
		uris = append(uris, uri)
	}
	return uris, nil
}
func GetAllSections() ([]BoardSection, error) {
	const sql = `SELECT id, name, abbreviation, position, hidden FROM DBPREFIXsections ORDER BY position ASC, name ASC`
	rows, err := QuerySQL(sql)
	if err != nil {
		return nil, err
	}
	var sections []BoardSection
	for rows.Next() {
		var section BoardSection
		err = rows.Scan(&section.ID, &section.Name, &section.Abbreviation, &section.ListOrder, &section.Hidden)
		if err != nil {
			return nil, err
		}
		sections = append(sections, section)
	}
	return sections, nil
}
func GetAllSectionsOrCreateDefault() ([]BoardSection, error) {
	_, err := GetOrCreateDefaultSectionID()
	if err != nil {
		return nil, err
	}
	return GetAllSections()
}
func getNextSectionListOrder() (int, error) {
	const sql = `SELECT COALESCE(MAX(position) + 1, 0) FROM DBPREFIXsections`
	var ID int
	err := QueryRowSQL(sql, interfaceSlice(), interfaceSlice(&ID))
	return ID, err
}
func GetOrCreateDefaultSectionID() (sectionID int, err error) {
	const SQL = `SELECT id FROM DBPREFIXsections WHERE name = 'Main'`
	var ID int
	err = QueryRowSQL(SQL, interfaceSlice(), interfaceSlice(&ID))
	if err == sql.ErrNoRows {
		ID, err := getNextSectionListOrder()
		if err != nil {
			return 0, err
		}
		board := BoardSection{Name: "Main", Abbreviation: "Main", Hidden: false, ListOrder: ID}
		err = CreateSection(&board)
		return board.ID, err
	}
	if err != nil {
		return 0, err  
	}
	return ID, nil
}
func CreateSection(section *BoardSection) error {
	const sqlINSERT = `INSERT INTO DBPREFIXsections (name, abbreviation, hidden, position) VALUES (?,?,?,?)`
	const sqlSELECT = `SELECT id FROM DBPREFIXsections WHERE position = ?`
	_, err := ExecSQL(sqlINSERT, section.Name, section.Abbreviation, section.Hidden, section.ListOrder)
	if err != nil {
		return err
	}
	return QueryRowSQL(
		sqlSELECT,
		interfaceSlice(section.ListOrder),
		interfaceSlice(&section.ID))
}
func GetAllStaffNopass() ([]Staff, error) {
	const sql = `SELECT id, username, global_rank, added_on, last_login FROM DBPREFIXstaff`
	rows, err := QuerySQL(sql)
	if err != nil {
		return nil, err
	}
	var staffs []Staff
	for rows.Next() {
		var staff Staff
		err = rows.Scan(&staff.ID, &staff.Username, &staff.Rank, &staff.AddedOn, &staff.LastActive)
		if err != nil {
			return nil, err
		}
		staffs = append(staffs, staff)
	}
	return staffs, nil
}
func GetAllBans() ([]BanInfo, error) {
	const sql = `SELECT 
	ban.id, 
	ban.ip, 
	COALESCE(board.title, '') as boardname,
	staff.username as staff,
	ban.issued_at,
	ban.expires_at,
	ban.permanent,
	ban.message,
	ban.staff_note,
	ban.appeal_at,
	ban.can_appeal
FROM DBPREFIXip_ban as ban
JOIN DBPREFIXstaff as staff
ON ban.staff_id = staff.id
JOIN DBPREFIXboards as board
ON ban.board_id = board.id`
	rows, err := QuerySQL(sql)
	if err != nil {
		return nil, err
	}
	var bans []BanInfo
	for rows.Next() {
		var ban BanInfo
		err = rows.Scan(&ban.ID, &ban.IP, &ban.Boards, &ban.Staff, &ban.Timestamp, &ban.Expires, &ban.Permaban, &ban.Reason, &ban.StaffNote, &ban.AppealAt, &ban.CanAppeal)
		if err != nil {
			return nil, err
		}
		bans = append(bans, ban)
	}
	return bans, nil
}