// Package store reads and writes CS2-SimpleAdmin's MySQL tables (schema as of 1.8.2b, migrations
// 001-016). Times are stored by SimpleAdmin as wall-clock values in its configured Timezone
// (UTC unless changed), so every time here is read and written in that location.
package store

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"slices"
	"strconv"
	"strings"
	"time"
)

// Normalised penalty states. SimpleAdmin uses UNBANNED for bans and UNMUTED for mutes.
const (
	StatusActive  = "active"
	StatusExpired = "expired"
	StatusLifted  = "lifted"
)

var ErrNotFound = errors.New("not found")

type Store struct {
	db       *sql.DB
	loc      *time.Location
	serverID sql.NullInt64
	// TimeMode 0 counts mute time only while the player is online (sa_mutes.passed).
	timeMode int
}

func New(db *sql.DB, loc *time.Location, serverID int64, timeMode int) *Store {
	s := &Store{db: db, loc: loc, timeMode: timeMode}
	if serverID > 0 {
		s.serverID = sql.NullInt64{Int64: serverID, Valid: true}
	}
	return s
}

func (s *Store) DB() *sql.DB { return s.db }

// wall turns a time into the naive wall-clock form SimpleAdmin stores.
func (s *Store) wall(t time.Time) time.Time {
	t = t.In(s.loc)
	return time.Date(t.Year(), t.Month(), t.Day(), t.Hour(), t.Minute(), t.Second(), 0, time.UTC)
}

// fromWall reads a stored wall-clock value back as a real instant.
func (s *Store) fromWall(t time.Time) time.Time {
	return time.Date(t.Year(), t.Month(), t.Day(), t.Hour(), t.Minute(), t.Second(), 0, s.loc)
}

func (s *Store) nullWall(t sql.NullTime) *time.Time {
	if !t.Valid {
		return nil
	}
	v := s.fromWall(t.Time)
	return &v
}

func sid(n sql.NullInt64) string {
	if !n.Valid || n.Int64 == 0 {
		return ""
	}
	return strconv.FormatInt(n.Int64, 10)
}

func parseSID(steamid string) (int64, error) {
	return strconv.ParseInt(steamid, 10, 64)
}

// Server is a row of sa_servers.
type Server struct {
	ID           int64
	Hostname     string
	Address      string
	RconPassword string
}

// Servers lists sa_servers. SimpleAdmin fills it in, including each server's rcon_password.
func (s *Store) Servers(ctx context.Context) ([]Server, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id, COALESCE(hostname,''), address, COALESCE(rcon_password,'') FROM sa_servers ORDER BY id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Server
	for rows.Next() {
		var v Server
		if err := rows.Scan(&v.ID, &v.Hostname, &v.Address, &v.RconPassword); err != nil {
			return nil, err
		}
		out = append(out, v)
	}
	return out, rows.Err()
}

// ---------------------------------------------------------------- Penalties

// Penalty is a ban, gag/mute/silence or warning.
type Penalty struct {
	ID           int64      `json:"id"`
	Kind         string     `json:"kind"` // ban, comm, warn
	Type         string     `json:"type,omitempty"`
	Name         string     `json:"name"`
	SteamID      string     `json:"steamid"`
	IP           string     `json:"ip,omitempty"`
	AdminSteamID string     `json:"adminSteamid,omitempty"`
	AdminName    string     `json:"admin,omitempty"`
	Reason       string     `json:"reason"`
	Duration     int        `json:"duration"`
	Passed       *int       `json:"passed,omitempty"`
	Ends         *time.Time `json:"ends,omitempty"`
	Created      time.Time  `json:"created"`
	Status       string     `json:"status"`
	LiftedBy     string     `json:"liftedBy,omitempty"`
	LiftReason   string     `json:"liftReason,omitempty"`
}

// Remaining returns minutes left on an active timed penalty.
func (p Penalty) Remaining(now time.Time) float64 {
	if p.Duration == 0 {
		return 0
	}
	if p.Passed != nil && p.Ends == nil {
		return float64(p.Duration - *p.Passed)
	}
	if p.Ends == nil {
		return 0
	}
	return p.Ends.Sub(now).Minutes()
}

// status normalises SimpleAdmin's status, treating a timed penalty whose end has passed as expired
// even before SimpleAdmin's 61-second sweep marks it.
func (s *Store) status(raw string, duration int, ends *time.Time, passed *int) string {
	switch raw {
	case "UNBANNED", "UNMUTED":
		return StatusLifted
	case "EXPIRED", "":
		return StatusExpired
	}
	if duration == 0 {
		return StatusActive
	}
	if s.timeMode == 0 && passed != nil {
		if *passed >= duration {
			return StatusExpired
		}
		return StatusActive
	}
	if ends != nil && !ends.After(time.Now()) {
		return StatusExpired
	}
	return StatusActive
}

// Query filters a penalty list.
type Query struct {
	Status  string // active, expired, lifted, all
	Type    string // GAG, MUTE, SILENCE (comms only)
	Search  string
	SteamID string // exact player
	// Which fields Search may match. Admin names and IPs are only searchable by staff.
	SearchAdmin bool
	SearchIP    bool
	Limit       int
	Offset      int
}

// statusSQL builds the WHERE condition for a status. Only sa_mutes has "passed" (withPassed).
func (s *Store) statusSQL(status string, withPassed bool) (string, []any) {
	now := s.wall(time.Now())
	online := withPassed && s.timeMode == 0
	switch status {
	case StatusActive:
		if online {
			return `status = 'ACTIVE' AND (duration = 0 OR (passed IS NOT NULL AND passed < duration) OR (passed IS NULL AND ends > ?))`, []any{now}
		}
		return `status = 'ACTIVE' AND (duration = 0 OR ends > ?)`, []any{now}
	case StatusExpired:
		if online {
			return `(status IN ('EXPIRED','') OR (status = 'ACTIVE' AND duration > 0 AND ((passed IS NOT NULL AND passed >= duration) OR (passed IS NULL AND ends <= ?))))`, []any{now}
		}
		return `(status IN ('EXPIRED','') OR (status = 'ACTIVE' AND duration > 0 AND ends <= ?))`, []any{now}
	case StatusLifted:
		return `status IN ('UNBANNED','UNMUTED')`, nil
	}
	return "1=1", nil
}

func (s *Store) where(table string, q Query) (string, []any) {
	var conds []string
	var args []any
	if q.Status != "" {
		c, a := s.statusSQL(q.Status, table == "sa_mutes")
		conds, args = append(conds, c), append(args, a...)
	}
	if q.Type != "" {
		conds, args = append(conds, "type = ?"), append(args, q.Type)
	}
	if q.SteamID != "" {
		n, _ := parseSID(q.SteamID)
		conds, args = append(conds, "player_steamid = ?"), append(args, n)
	}
	if t := strings.TrimSpace(q.Search); t != "" {
		like := "%" + escapeLike(t) + "%"
		or := []string{"player_name LIKE ?", "reason LIKE ?"}
		or2 := []any{like, like}
		if n, err := strconv.ParseInt(t, 10, 64); err == nil {
			or, or2 = append(or, "player_steamid = ?"), append(or2, n)
		}
		if q.SearchAdmin {
			or, or2 = append(or, "admin_name LIKE ?"), append(or2, like)
		}
		if q.SearchIP && table == "sa_bans" {
			or, or2 = append(or, "player_ip = ?"), append(or2, t)
		}
		conds = append(conds, "("+strings.Join(or, " OR ")+")")
		args = append(args, or2...)
	}
	if len(conds) == 0 {
		return "", nil
	}
	return " WHERE " + strings.Join(conds, " AND "), args
}

func escapeLike(s string) string {
	return strings.NewReplacer(`\`, `\\`, `%`, `\%`, `_`, `\_`).Replace(s)
}

// Bans lists sa_bans, newest first, with the total matching count.
func (s *Store) Bans(ctx context.Context, q Query) ([]Penalty, int, error) {
	where, args := s.where("sa_bans", q)
	var total int
	if err := s.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM sa_bans"+where, args...).Scan(&total); err != nil {
		return nil, 0, err
	}
	sqlq := `SELECT b.id, COALESCE(b.player_name,''), b.player_steamid, COALESCE(b.player_ip,''), b.admin_steamid,
	               b.admin_name, b.reason, b.duration, b.ends, b.created, b.status,
	               COALESCE(ua.player_name,''), COALESCE(u.reason,'')
	        FROM (SELECT * FROM sa_bans` + where + ` ORDER BY id DESC LIMIT ? OFFSET ?) b
	        LEFT JOIN sa_unbans u ON u.id = b.unban_id
	        LEFT JOIN sa_admins ua ON ua.id = u.admin_id
	        ORDER BY b.id DESC`
	rows, err := s.db.QueryContext(ctx, sqlq, append(args, limit(q.Limit), q.Offset)...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	var out []Penalty
	for rows.Next() {
		var p Penalty
		var psid, asid sql.NullInt64
		var ends, created sql.NullTime
		var raw string
		if err := rows.Scan(&p.ID, &p.Name, &psid, &p.IP, &asid, &p.AdminName, &p.Reason, &p.Duration, &ends,
			&created, &raw, &p.LiftedBy, &p.LiftReason); err != nil {
			return nil, 0, err
		}
		p.Kind, p.SteamID, p.AdminSteamID = "ban", sid(psid), sid(asid)
		p.Ends, p.Created = s.nullWall(ends), s.fromWall(created.Time)
		p.Status = s.status(raw, p.Duration, p.Ends, nil)
		out = append(out, p)
	}
	return out, total, rows.Err()
}

// Comms lists sa_mutes (gags, mutes and silences), newest first.
func (s *Store) Comms(ctx context.Context, q Query) ([]Penalty, int, error) {
	where, args := s.where("sa_mutes", q)
	var total int
	if err := s.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM sa_mutes"+where, args...).Scan(&total); err != nil {
		return nil, 0, err
	}
	sqlq := `SELECT m.id, m.type, COALESCE(m.player_name,''), m.player_steamid, m.admin_steamid, m.admin_name,
	               m.reason, m.duration, m.passed, m.ends, m.created, m.status,
	               COALESCE(ua.player_name,''), COALESCE(u.reason,'')
	        FROM (SELECT * FROM sa_mutes` + where + ` ORDER BY id DESC LIMIT ? OFFSET ?) m
	        LEFT JOIN sa_unmutes u ON u.id = m.unmute_id
	        LEFT JOIN sa_admins ua ON ua.id = u.admin_id
	        ORDER BY m.id DESC`
	rows, err := s.db.QueryContext(ctx, sqlq, append(args, limit(q.Limit), q.Offset)...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	var out []Penalty
	for rows.Next() {
		var p Penalty
		var psid, asid sql.NullInt64
		var passed sql.NullInt64
		var ends, created sql.NullTime
		var raw string
		if err := rows.Scan(&p.ID, &p.Type, &p.Name, &psid, &asid, &p.AdminName, &p.Reason, &p.Duration, &passed,
			&ends, &created, &raw, &p.LiftedBy, &p.LiftReason); err != nil {
			return nil, 0, err
		}
		p.Kind, p.SteamID, p.AdminSteamID = "comm", sid(psid), sid(asid)
		if passed.Valid {
			v := int(passed.Int64)
			p.Passed = &v
		}
		p.Ends, p.Created = s.nullWall(ends), s.fromWall(created.Time)
		p.Status = s.status(raw, p.Duration, p.Ends, p.Passed)
		out = append(out, p)
	}
	return out, total, rows.Err()
}

// Warns lists sa_warns for one player, newest first.
func (s *Store) Warns(ctx context.Context, steamid string) ([]Penalty, error) {
	n, _ := parseSID(steamid)
	rows, err := s.db.QueryContext(ctx, `SELECT id, COALESCE(player_name,''), admin_steamid, admin_name, reason, duration, ends, created, status
		FROM sa_warns WHERE player_steamid = ? ORDER BY id DESC LIMIT 100`, n)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Penalty
	for rows.Next() {
		var p Penalty
		var asid sql.NullInt64
		var ends, created sql.NullTime
		var raw string
		if err := rows.Scan(&p.ID, &p.Name, &asid, &p.AdminName, &p.Reason, &p.Duration, &ends, &created, &raw); err != nil {
			return nil, err
		}
		p.Kind, p.SteamID, p.AdminSteamID = "warn", steamid, sid(asid)
		p.Ends, p.Created = s.nullWall(ends), s.fromWall(created.Time)
		p.Status = s.status(raw, p.Duration, p.Ends, nil)
		out = append(out, p)
	}
	return out, rows.Err()
}

// ActiveWarnCounts returns active warnings per player for the given SteamIDs.
func (s *Store) ActiveWarnCounts(ctx context.Context, steamids []string) (map[string]int, error) {
	out := map[string]int{}
	if len(steamids) == 0 {
		return out, nil
	}
	in, args := inList(steamids)
	args = append(args, s.wall(time.Now()))
	rows, err := s.db.QueryContext(ctx, `SELECT player_steamid, COUNT(*) FROM sa_warns
		WHERE player_steamid IN (`+in+`) AND status = 'ACTIVE' AND (duration = 0 OR ends > ?) GROUP BY player_steamid`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var id sql.NullInt64
		var n int
		if err := rows.Scan(&id, &n); err != nil {
			return nil, err
		}
		out[sid(id)] = n
	}
	return out, rows.Err()
}

// RecordCounts returns how many bans and comm blocks (any status) each player has.
func (s *Store) RecordCounts(ctx context.Context, steamids []string) (bans, comms map[string]int, err error) {
	bans, comms = map[string]int{}, map[string]int{}
	if len(steamids) == 0 {
		return
	}
	in, args := inList(steamids)
	for table, m := range map[string]map[string]int{"sa_bans": bans, "sa_mutes": comms} {
		rows, qerr := s.db.QueryContext(ctx, `SELECT player_steamid, COUNT(*) FROM `+table+` WHERE player_steamid IN (`+in+`) GROUP BY player_steamid`, args...)
		if qerr != nil {
			return nil, nil, qerr
		}
		for rows.Next() {
			var id sql.NullInt64
			var n int
			if err = rows.Scan(&id, &n); err != nil {
				rows.Close()
				return
			}
			m[sid(id)] = n
		}
		rows.Close()
	}
	return
}

func inList(steamids []string) (string, []any) {
	args := make([]any, 0, len(steamids))
	for _, v := range steamids {
		n, _ := parseSID(v)
		args = append(args, n)
	}
	return strings.TrimSuffix(strings.Repeat("?,", len(args)), ","), args
}

func limit(n int) int {
	if n <= 0 || n > 200 {
		return 50
	}
	return n
}

// Activity returns the most recent bans, comm blocks, warnings and unbans, newest first.
type Activity struct {
	Kind    string    `json:"kind"` // ban, comm, warn, unban, unmute
	Type    string    `json:"type,omitempty"`
	Name    string    `json:"name"`
	SteamID string    `json:"steamid"`
	Admin   string    `json:"admin"`
	Reason  string    `json:"reason"`
	Minutes int       `json:"duration"`
	At      time.Time `json:"at"`
}

func (s *Store) Activity(ctx context.Context, n int) ([]Activity, error) {
	q := `
	(SELECT 'ban' kind, '' type, COALESCE(player_name,'') name, player_steamid, admin_name, reason, duration, created FROM sa_bans ORDER BY id DESC LIMIT ?)
	UNION ALL
	(SELECT 'comm', type, COALESCE(player_name,''), player_steamid, admin_name, reason, duration, created FROM sa_mutes ORDER BY id DESC LIMIT ?)
	UNION ALL
	(SELECT 'warn', '', COALESCE(player_name,''), player_steamid, admin_name, reason, duration, created FROM sa_warns ORDER BY id DESC LIMIT ?)
	UNION ALL
	(SELECT 'unban', '', COALESCE(b.player_name,''), b.player_steamid, COALESCE(a.player_name,'Console'), u.reason, 0, u.date
	   FROM sa_unbans u JOIN sa_bans b ON b.id = u.ban_id LEFT JOIN sa_admins a ON a.id = u.admin_id ORDER BY u.id DESC LIMIT ?)
	UNION ALL
	(SELECT 'unmute', m.type, COALESCE(m.player_name,''), m.player_steamid, COALESCE(a.player_name,'Console'), u.reason, 0, u.date
	   FROM sa_unmutes u JOIN sa_mutes m ON m.id = u.mute_id LEFT JOIN sa_admins a ON a.id = u.admin_id ORDER BY u.id DESC LIMIT ?)
	ORDER BY 8 DESC LIMIT ?`
	rows, err := s.db.QueryContext(ctx, q, n, n, n, n, n, n)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Activity
	for rows.Next() {
		var a Activity
		var id sql.NullInt64
		var at sql.NullTime
		if err := rows.Scan(&a.Kind, &a.Type, &a.Name, &id, &a.Admin, &a.Reason, &a.Minutes, &at); err != nil {
			return nil, err
		}
		a.SteamID, a.At = sid(id), s.fromWall(at.Time)
		out = append(out, a)
	}
	return out, rows.Err()
}

// ---------------------------------------------------------------- Players

// Player is someone SimpleAdmin has seen connect (sa_players_ips).
type Player struct {
	SteamID  string    `json:"steamid"`
	Name     string    `json:"name"`
	LastSeen time.Time `json:"lastSeen"`
}

// Players lists known players, most recently seen first. Search matches name, SteamID64 or, when
// byIP is set, an exact IPv4 address.
func (s *Store) Players(ctx context.Context, search string, byIP bool, onlyIDs []string, lim, offset int) ([]Player, int, error) {
	var conds []string
	var args []any
	if t := strings.TrimSpace(search); t != "" {
		or := []string{"name LIKE ?"}
		or2 := []any{"%" + escapeLike(t) + "%"}
		if n, err := strconv.ParseInt(t, 10, 64); err == nil {
			or, or2 = append(or, "steamid = ?"), append(or2, n)
		}
		if byIP && strings.Count(t, ".") == 3 {
			or, or2 = append(or, "address = INET_ATON(?)"), append(or2, t)
		}
		conds = append(conds, "steamid IN (SELECT steamid FROM sa_players_ips WHERE "+strings.Join(or, " OR ")+")")
		args = append(args, or2...)
	}
	if onlyIDs != nil {
		if len(onlyIDs) == 0 {
			return nil, 0, nil
		}
		in, a := inList(onlyIDs)
		conds = append(conds, "steamid IN ("+in+")")
		args = append(args, a...)
	}
	where := ""
	if len(conds) > 0 {
		where = " WHERE " + strings.Join(conds, " AND ")
	}
	var total int
	if err := s.db.QueryRowContext(ctx, "SELECT COUNT(DISTINCT steamid) FROM sa_players_ips"+where, args...).Scan(&total); err != nil {
		return nil, 0, err
	}
	q := `SELECT steamid, MAX(used_at) AS seen,
	             SUBSTRING_INDEX(GROUP_CONCAT(COALESCE(name,'') ORDER BY used_at DESC SEPARATOR '\n'), '\n', 1)
	      FROM sa_players_ips` + where + ` GROUP BY steamid ORDER BY seen DESC LIMIT ? OFFSET ?`
	rows, err := s.db.QueryContext(ctx, q, append(args, limit(lim), offset)...)
	if err != nil {
		return nil, 0, err
	}
	defer rows.Close()
	var out []Player
	for rows.Next() {
		var p Player
		var id sql.NullInt64
		var seen sql.NullTime
		if err := rows.Scan(&id, &seen, &p.Name); err != nil {
			return nil, 0, err
		}
		p.SteamID, p.LastSeen = sid(id), s.fromWall(seen.Time)
		out = append(out, p)
	}
	return out, total, rows.Err()
}

// PlayerDetail is everything known about one SteamID.
type PlayerDetail struct {
	SteamID  string     `json:"steamid"`
	Name     string     `json:"name"`
	Names    []string   `json:"names"`
	IPs      []string   `json:"ips,omitempty"`
	LastSeen *time.Time `json:"lastSeen,omitempty"`
}

func (s *Store) Player(ctx context.Context, steamid string) (PlayerDetail, error) {
	n, err := parseSID(steamid)
	if err != nil {
		return PlayerDetail{}, ErrNotFound
	}
	d := PlayerDetail{SteamID: steamid}
	rows, err := s.db.QueryContext(ctx, `SELECT COALESCE(name,''), INET_NTOA(address), used_at FROM sa_players_ips WHERE steamid = ? ORDER BY used_at DESC`, n)
	if err != nil {
		return d, err
	}
	defer rows.Close()
	for rows.Next() {
		var name string
		var ip sql.NullString
		var at sql.NullTime
		if err := rows.Scan(&name, &ip, &at); err != nil {
			return d, err
		}
		if d.LastSeen == nil && at.Valid {
			t := s.fromWall(at.Time)
			d.LastSeen = &t
		}
		if name != "" && !slices.Contains(d.Names, name) {
			d.Names = append(d.Names, name)
		}
		if ip.Valid && !slices.Contains(d.IPs, ip.String) {
			d.IPs = append(d.IPs, ip.String)
		}
	}
	if err := rows.Err(); err != nil {
		return d, err
	}
	// Players who were banned offline may never have joined; fall back to names on their records.
	for _, table := range []string{"sa_bans", "sa_mutes", "sa_admins"} {
		var name sql.NullString
		err := s.db.QueryRowContext(ctx, `SELECT player_name FROM `+table+` WHERE player_steamid = ? AND player_name IS NOT NULL AND player_name <> '' ORDER BY id DESC LIMIT 1`, n).Scan(&name)
		if err == nil && name.Valid && !slices.Contains(d.Names, name.String) {
			d.Names = append(d.Names, name.String)
		}
	}
	if len(d.Names) == 0 {
		return d, ErrNotFound
	}
	d.Name = d.Names[0]
	return d, nil
}

// LatestName returns the last name SimpleAdmin saw for a SteamID, or "".
func (s *Store) LatestName(ctx context.Context, steamid string) string {
	d, err := s.Player(ctx, steamid)
	if err != nil {
		return ""
	}
	return d.Name
}

// ---------------------------------------------------------------- Admins and groups

// Group is a SimpleAdmin group ("rank"), e.g. #rank/mod.
type Group struct {
	ID       int64    `json:"id"`
	Name     string   `json:"name"`
	Immunity int      `json:"immunity"`
	Flags    []string `json:"flags"`
}

// Admin merges every applicable sa_admins row for one SteamID.
type Admin struct {
	RowIDs   []int64    `json:"-"`
	SteamID  string     `json:"steamid"`
	Name     string     `json:"name"`
	Immunity int        `json:"immunity"`
	Flags    []string   `json:"flags"`  // @css/... set directly on the admin
	Groups   []string   `json:"groups"` // #group names
	Ends     *time.Time `json:"ends,omitempty"`
	Global   bool       `json:"global"`
}

func (s *Store) serverFilter(col string) (string, []any) {
	if s.serverID.Valid {
		return "(" + col + " IS NULL OR " + col + " = ?)", []any{s.serverID.Int64}
	}
	return "1=1", nil
}

// Groups returns the groups that apply to this server, the same set SimpleAdmin loads.
func (s *Store) Groups(ctx context.Context) ([]Group, error) {
	cond, args := s.serverFilter("gs.server_id")
	rows, err := s.db.QueryContext(ctx, `SELECT g.id, g.name, g.immunity, COALESCE(f.flag,'')
		FROM sa_groups g
		JOIN sa_groups_servers gs ON gs.group_id = g.id AND `+cond+`
		LEFT JOIN sa_groups_flags f ON f.group_id = g.id
		ORDER BY g.immunity DESC, g.id`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Group
	idx := map[int64]int{}
	for rows.Next() {
		var g Group
		var flag string
		if err := rows.Scan(&g.ID, &g.Name, &g.Immunity, &flag); err != nil {
			return nil, err
		}
		i, ok := idx[g.ID]
		if !ok {
			g.Flags = []string{}
			out = append(out, g)
			i = len(out) - 1
			idx[g.ID] = i
		}
		if flag != "" && !slices.Contains(out[i].Flags, flag) {
			out[i].Flags = append(out[i].Flags, flag)
		}
	}
	return out, rows.Err()
}

// Admins returns every unexpired admin that applies to this server.
func (s *Store) Admins(ctx context.Context) ([]Admin, error) {
	cond, args := s.serverFilter("a.server_id")
	args = append([]any{s.wall(time.Now())}, args...)
	rows, err := s.db.QueryContext(ctx, `SELECT a.id, a.player_steamid, COALESCE(a.player_name,''), a.immunity, a.ends, a.server_id IS NULL, COALESCE(f.flag,'')
		FROM sa_admins a
		LEFT JOIN sa_admins_flags f ON f.admin_id = a.id
		WHERE a.player_steamid IS NOT NULL AND a.player_steamid <> 0
		  AND (a.ends IS NULL OR a.ends > ?) AND `+cond+`
		ORDER BY a.server_id IS NULL DESC, a.id`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Admin
	idx := map[string]int{}
	for rows.Next() {
		var rowID int64
		var id sql.NullInt64
		var name, flag string
		var imm int
		var ends sql.NullTime
		var global bool
		if err := rows.Scan(&rowID, &id, &name, &imm, &ends, &global, &flag); err != nil {
			return nil, err
		}
		key := sid(id)
		i, ok := idx[key]
		if !ok {
			out = append(out, Admin{SteamID: key, Name: name, Flags: []string{}, Groups: []string{}, Ends: s.nullWall(ends)})
			i = len(out) - 1
			idx[key] = i
		}
		a := &out[i]
		if !slices.Contains(a.RowIDs, rowID) {
			a.RowIDs = append(a.RowIDs, rowID)
		}
		a.Immunity = max(a.Immunity, imm)
		a.Global = a.Global || global
		switch {
		case strings.HasPrefix(flag, "#") && !slices.Contains(a.Groups, flag):
			a.Groups = append(a.Groups, flag)
		case strings.HasPrefix(flag, "@") && !slices.Contains(a.Flags, flag):
			a.Flags = append(a.Flags, flag)
		}
	}
	// SimpleAdmin ignores admin rows with no flags at all (its admin query joins the flags table).
	out = slices.DeleteFunc(out, func(a Admin) bool { return len(a.Flags) == 0 && len(a.Groups) == 0 })
	return out, rows.Err()
}

// AdminRowID returns the sa_admins.id to credit for unbans by this SteamID, or 0 (Console).
func (s *Store) AdminRowID(ctx context.Context, steamid string) int64 {
	n, _ := parseSID(steamid)
	var id int64
	_ = s.db.QueryRowContext(ctx, `SELECT id FROM sa_admins WHERE player_steamid = ? ORDER BY server_id IS NULL DESC, id LIMIT 1`, n).Scan(&id)
	return id
}

// CreateGroup adds a global group, like css_addgroup ... -g.
func (s *Store) CreateGroup(ctx context.Context, name string, immunity int, flags []string) (int64, error) {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return 0, err
	}
	defer tx.Rollback()
	var exists int
	_ = tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM sa_groups WHERE name = ?`, name).Scan(&exists)
	if exists > 0 {
		return 0, fmt.Errorf("A rank named %s already exists.", name)
	}
	res, err := tx.ExecContext(ctx, `INSERT INTO sa_groups (name, immunity) VALUES (?, ?)`, name, immunity)
	if err != nil {
		return 0, err
	}
	id, _ := res.LastInsertId()
	if err := insertGroupFlags(ctx, tx, id, flags); err != nil {
		return 0, err
	}
	if _, err := tx.ExecContext(ctx, `INSERT INTO sa_groups_servers (group_id, server_id) VALUES (?, NULL)`, id); err != nil {
		return 0, err
	}
	return id, tx.Commit()
}

func insertGroupFlags(ctx context.Context, tx *sql.Tx, id int64, flags []string) error {
	for _, f := range flags {
		if _, err := tx.ExecContext(ctx, `INSERT INTO sa_groups_flags (group_id, flag) VALUES (?, ?)`, id, f); err != nil {
			return err
		}
	}
	return nil
}

// UpdateGroup changes a group's name, immunity and flags. Renaming also updates every admin's
// "#name" reference so members keep the rank.
func (s *Store) UpdateGroup(ctx context.Context, id int64, name string, immunity int, flags []string) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	var old string
	if err := tx.QueryRowContext(ctx, `SELECT name FROM sa_groups WHERE id = ?`, id).Scan(&old); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return ErrNotFound
		}
		return err
	}
	if name != old {
		var exists int
		_ = tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM sa_groups WHERE name = ? AND id <> ?`, name, id).Scan(&exists)
		if exists > 0 {
			return fmt.Errorf("A rank named %s already exists.", name)
		}
		if _, err := tx.ExecContext(ctx, `UPDATE sa_admins_flags SET flag = ? WHERE flag = ?`, name, old); err != nil {
			return err
		}
	}
	if _, err := tx.ExecContext(ctx, `UPDATE sa_groups SET name = ?, immunity = ? WHERE id = ?`, name, immunity, id); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, `DELETE FROM sa_groups_flags WHERE group_id = ?`, id); err != nil {
		return err
	}
	if err := insertGroupFlags(ctx, tx, id, flags); err != nil {
		return err
	}
	return tx.Commit()
}

// DeleteGroup removes a group and every admin's reference to it. Admins left with no flags or
// groups are removed too, which is what makes them regular players again.
func (s *Store) DeleteGroup(ctx context.Context, id int64) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()
	var name string
	if err := tx.QueryRowContext(ctx, `SELECT name FROM sa_groups WHERE id = ?`, id).Scan(&name); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return ErrNotFound
		}
		return err
	}
	if _, err := tx.ExecContext(ctx, `DELETE FROM sa_admins_flags WHERE flag = ?`, name); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, `DELETE FROM sa_groups WHERE id = ?`, id); err != nil {
		return err
	}
	if err := deleteEmptyAdmins(ctx, tx); err != nil {
		return err
	}
	return tx.Commit()
}

func deleteEmptyAdmins(ctx context.Context, tx *sql.Tx) error {
	// id 0 is SimpleAdmin's "Console" row, which unbans reference.
	_, err := tx.ExecContext(ctx, `DELETE FROM sa_admins WHERE id <> 0 AND player_steamid <> 0
		AND NOT EXISTS (SELECT 1 FROM sa_admins_flags f WHERE f.admin_id = sa_admins.id)
		AND (flags IS NULL OR flags = '')`)
	return err
}

// SetAdminGroup gives a player exactly one rank (replacing any #group they had), creating a
// global admin row if they have none. immunity is stored on the admin row, as css_addadmin does.
// ends is nil for no expiry.
func (s *Store) SetAdminGroup(ctx context.Context, steamid, name, group string, immunity int, ends *time.Time) error {
	n, err := parseSID(steamid)
	if err != nil {
		return err
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer tx.Rollback()

	var rowID int64
	err = tx.QueryRowContext(ctx, `SELECT id FROM sa_admins WHERE player_steamid = ? ORDER BY server_id IS NULL DESC, id LIMIT 1`, n).Scan(&rowID)
	var endsArg any
	if ends != nil {
		endsArg = s.wall(*ends)
	}
	switch {
	case errors.Is(err, sql.ErrNoRows):
		res, err := tx.ExecContext(ctx, `INSERT INTO sa_admins (player_steamid, player_name, immunity, ends, created, server_id) VALUES (?, ?, ?, ?, ?, NULL)`,
			n, name, immunity, endsArg, s.wall(time.Now()))
		if err != nil {
			return err
		}
		rowID, _ = res.LastInsertId()
	case err != nil:
		return err
	default:
		if _, err := tx.ExecContext(ctx, `UPDATE sa_admins SET immunity = ?, ends = ?, player_name = COALESCE(NULLIF(?, ''), player_name) WHERE player_steamid = ?`,
			immunity, endsArg, name, n); err != nil {
			return err
		}
	}
	if _, err := tx.ExecContext(ctx, `DELETE f FROM sa_admins_flags f JOIN sa_admins a ON a.id = f.admin_id WHERE a.player_steamid = ? AND f.flag LIKE '#%'`, n); err != nil {
		return err
	}
	if _, err := tx.ExecContext(ctx, `INSERT INTO sa_admins_flags (admin_id, flag) VALUES (?, ?)`, rowID, group); err != nil {
		return err
	}
	return tx.Commit()
}

// RemoveAdmin deletes every sa_admins row for a SteamID, like css_deladmin ... -g.
func (s *Store) RemoveAdmin(ctx context.Context, steamid string) error {
	n, err := parseSID(steamid)
	if err != nil {
		return err
	}
	_, err = s.db.ExecContext(ctx, `DELETE FROM sa_admins WHERE player_steamid = ? AND id <> 0`, n)
	return err
}

// ---------------------------------------------------------------- Writes for penalties

// MaxID returns the highest id in a penalty table, to find rows a console command creates.
func (s *Store) MaxID(ctx context.Context, table string) int64 {
	var id sql.NullInt64
	_ = s.db.QueryRowContext(ctx, `SELECT MAX(id) FROM `+table).Scan(&id)
	return id.Int64
}

// ClaimConsoleRow finds a row created after afterID for the player by "Console" (admin_steamid 0)
// and credits it to the web admin instead. SimpleAdmin writes the row asynchronously, so this polls
// for a few seconds. It returns the row id, or 0 if none appeared.
func (s *Store) ClaimConsoleRow(ctx context.Context, table string, afterID int64, steamid, commType, adminSteamID, adminName string) int64 {
	n, _ := parseSID(steamid)
	a, _ := parseSID(adminSteamID)
	q := `SELECT id FROM ` + table + ` WHERE id > ? AND player_steamid = ? AND admin_steamid = 0`
	args := []any{afterID, n}
	if commType != "" {
		q += ` AND type = ?`
		args = append(args, commType)
	}
	q += ` ORDER BY id DESC LIMIT 1`
	for range 20 {
		var id int64
		if err := s.db.QueryRowContext(ctx, q, args...).Scan(&id); err == nil {
			_, _ = s.db.ExecContext(ctx, `UPDATE `+table+` SET admin_steamid = ?, admin_name = ? WHERE id = ?`, a, adminName, id)
			return id
		}
		select {
		case <-ctx.Done():
			return 0
		case <-time.After(250 * time.Millisecond):
		}
	}
	return 0
}

// ActivePenaltyIDs lists a player's active bans (table sa_bans) or active comm blocks of one type.
func (s *Store) ActivePenaltyIDs(ctx context.Context, table, steamid, commType string) []int64 {
	n, _ := parseSID(steamid)
	q := `SELECT id FROM ` + table + ` WHERE player_steamid = ? AND status = 'ACTIVE'`
	args := []any{n}
	if commType != "" {
		q += ` AND type = ?`
		args = append(args, commType)
	}
	rows, err := s.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil
	}
	defer rows.Close()
	var ids []int64
	for rows.Next() {
		var id int64
		if rows.Scan(&id) == nil {
			ids = append(ids, id)
		}
	}
	return ids
}

// ClaimLift credits the unban/unmute rows that a console command wrote for these penalties, which
// were active before the command. SimpleAdmin gives console lifts the id of its "Console" admin row,
// which isn't reliably 0, so the rows are found through the penalties instead.
func (s *Store) ClaimLift(ctx context.Context, table string, ids []int64, adminRowID int64) {
	if len(ids) == 0 || adminRowID == 0 {
		return
	}
	liftTable, col := "sa_unbans", "unban_id"
	if table == "sa_mutes" {
		liftTable, col = "sa_unmutes", "unmute_id"
	}
	in := strings.TrimSuffix(strings.Repeat("?,", len(ids)), ",")
	args := make([]any, 0, len(ids)+1)
	args = append(args, adminRowID)
	for _, id := range ids {
		args = append(args, id)
	}
	for range 20 {
		res, err := s.db.ExecContext(ctx, `UPDATE `+liftTable+` SET admin_id = ? WHERE id IN (SELECT `+col+` FROM `+table+` WHERE id IN (`+in+`) AND `+col+` IS NOT NULL)`, args...)
		if err == nil {
			if n, _ := res.RowsAffected(); n > 0 {
				return
			}
		}
		select {
		case <-ctx.Done():
			return
		case <-time.After(250 * time.Millisecond):
		}
	}
}

// NewPenalty is a ban or comm block written directly, used when the game server is unreachable.
type NewPenalty struct {
	SteamID      string
	Name         string
	Type         string // GAG/MUTE/SILENCE for comms
	AdminSteamID string
	AdminName    string
	Reason       string
	Duration     int
}

func (s *Store) serverArg() any {
	if s.serverID.Valid {
		return s.serverID.Int64
	}
	return nil
}

// InsertBan writes a ban the way SimpleAdmin's AddBanBySteamid does.
func (s *Store) InsertBan(ctx context.Context, p NewPenalty) error {
	n, _ := parseSID(p.SteamID)
	a, _ := parseSID(p.AdminSteamID)
	now := time.Now()
	var ends any
	if p.Duration > 0 {
		ends = s.wall(now.Add(time.Duration(p.Duration) * time.Minute))
	}
	_, err := s.db.ExecContext(ctx, `INSERT INTO sa_bans (player_steamid, player_name, admin_steamid, admin_name, reason, duration, ends, created, server_id)
		VALUES (?, NULLIF(?, ''), ?, ?, ?, ?, ?, ?, ?)`, n, p.Name, a, p.AdminName, p.Reason, p.Duration, ends, s.wall(now), s.serverArg())
	return err
}

// InsertComm writes a gag, mute or silence the way SimpleAdmin's AddMuteBySteamid does.
func (s *Store) InsertComm(ctx context.Context, p NewPenalty) error {
	n, _ := parseSID(p.SteamID)
	a, _ := parseSID(p.AdminSteamID)
	now := time.Now()
	var ends any
	if p.Duration > 0 {
		ends = s.wall(now.Add(time.Duration(p.Duration) * time.Minute))
	}
	_, err := s.db.ExecContext(ctx, `INSERT INTO sa_mutes (player_steamid, player_name, admin_steamid, admin_name, reason, duration, ends, created, type, server_id, passed)
		VALUES (?, NULLIF(?, ''), ?, ?, ?, ?, ?, ?, ?, ?, 0)`, n, p.Name, a, p.AdminName, p.Reason, p.Duration, ends, s.wall(now), p.Type, s.serverArg())
	return err
}

// LiftDirect marks a player's active bans (or comm blocks of one type) lifted and records who did
// it, the way SimpleAdmin's unban/unmute do. Used when the game server is unreachable.
func (s *Store) LiftDirect(ctx context.Context, table, steamid, commType string, adminRowID int64, reason string) (int, error) {
	ids := s.ActivePenaltyIDs(ctx, table, steamid, commType)
	liftTable, fk, col, status := "sa_unbans", "ban_id", "unban_id", "UNBANNED"
	if table == "sa_mutes" {
		liftTable, fk, col, status = "sa_unmutes", "mute_id", "unmute_id", "UNMUTED"
	}
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return 0, err
	}
	defer tx.Rollback()
	for _, id := range ids {
		res, err := tx.ExecContext(ctx, `INSERT INTO `+liftTable+` (`+fk+`, admin_id, reason) VALUES (?, ?, ?)`, id, adminRowID, reason)
		if err != nil {
			return 0, err
		}
		liftID, _ := res.LastInsertId()
		if _, err := tx.ExecContext(ctx, `UPDATE `+table+` SET status = ?, `+col+` = ? WHERE id = ?`, status, liftID, id); err != nil {
			return 0, err
		}
	}
	return len(ids), tx.Commit()
}

// PenaltyOwner returns the player SteamID and (for comms) type of a penalty row.
func (s *Store) PenaltyOwner(ctx context.Context, table string, id int64) (steamid, commType string, err error) {
	var n sql.NullInt64
	if table == "sa_mutes" {
		err = s.db.QueryRowContext(ctx, `SELECT player_steamid, type FROM sa_mutes WHERE id = ?`, id).Scan(&n, &commType)
	} else {
		err = s.db.QueryRowContext(ctx, `SELECT player_steamid FROM sa_bans WHERE id = ?`, id).Scan(&n)
	}
	if errors.Is(err, sql.ErrNoRows) {
		return "", "", ErrNotFound
	}
	return sid(n), commType, err
}

// Expiring returns active timed bans and comm blocks, soonest end first.
func (s *Store) Expiring(ctx context.Context, n int) ([]Penalty, error) {
	bans, _, err := s.Bans(ctx, Query{Status: StatusActive, Limit: 200})
	if err != nil {
		return nil, err
	}
	comms, _, err := s.Comms(ctx, Query{Status: StatusActive, Limit: 200})
	if err != nil {
		return nil, err
	}
	now := time.Now()
	all := slices.DeleteFunc(append(bans, comms...), func(p Penalty) bool { return p.Duration == 0 })
	slices.SortFunc(all, func(a, b Penalty) int {
		ra, rb := a.Remaining(now), b.Remaining(now)
		switch {
		case ra < rb:
			return -1
		case ra > rb:
			return 1
		}
		return 0
	})
	if len(all) > n {
		all = all[:n]
	}
	return all, nil
}