// Package qso models a radio contact (QSO) and provides its repository.
package qso

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"reflect"
	"regexp"
	"sort"
	"strings"
	"time"

	"hamlog/internal/db"
)

// MergeNonZero copies every non-zero field of src onto dst. Zero-value src
// fields (empty string, nil pointer, zero time, zero number) are skipped so
// existing data is preserved. Maps (Extras) are merged key-by-key rather than
// replaced. Because an imported QSO has ID==0 and CreatedAt zero, dst's
// identity is naturally preserved. Used by the importer's "update duplicates"
// mode so re-importing an ADIF refreshes QSL/confirmation statuses without
// clobbering fields the source file doesn't carry.
func MergeNonZero(dst *QSO, src QSO) {
	dv := reflect.ValueOf(dst).Elem()
	sv := reflect.ValueOf(src)
	for i := 0; i < dv.NumField(); i++ {
		df, sf := dv.Field(i), sv.Field(i)
		if !df.CanSet() || sf.IsZero() {
			continue
		}
		if sf.Kind() == reflect.Map {
			if df.IsNil() {
				df.Set(reflect.MakeMap(sf.Type()))
			}
			for _, k := range sf.MapKeys() {
				df.SetMapIndex(k, sf.MapIndex(k))
			}
			continue
		}
		df.Set(sf)
	}
}

// QSO represents a contact. Fields are aligned on ADIF naming for
// import/export. Pointers are used to distinguish "absent" from "zero".
// Anything in ADIF that is not a promoted column lands in Extras.
type QSO struct {
	ID int64 `json:"id"`
	// Number is the contact's position in the log, oldest = 1.
	//
	// NOT a column and NOT the ID: the primary key follows insertion order, so
	// importing an old ADIF gives the oldest contacts the highest ids. This is a
	// rank over qso_date, computed by the App layer and stamped on the way out —
	// which is why it is `json:"-"`-adjacent in spirit: nothing reads or writes
	// it in SQL.
	Number     int       `json:"number,omitempty"`
	Callsign   string    `json:"callsign"`
	QSODate    time.Time `json:"qso_date"`               // start, UTC
	QSODateOff time.Time `json:"qso_date_off,omitempty"` // end, UTC
	Band       string    `json:"band"`
	BandRX     string    `json:"band_rx,omitempty"`
	Mode       string    `json:"mode"`
	Submode    string    `json:"submode,omitempty"`
	FreqHz     *int64    `json:"freq_hz,omitempty"`
	FreqRXHz   *int64    `json:"freq_rx_hz,omitempty"`

	RSTSent string `json:"rst_sent,omitempty"`
	RSTRcvd string `json:"rst_rcvd,omitempty"`

	// --- Contacted station ---
	Name      string   `json:"name,omitempty"`
	QTH       string   `json:"qth,omitempty"`
	Address   string   `json:"address,omitempty"`
	Email     string   `json:"email,omitempty"`
	Web       string   `json:"web,omitempty"`
	Grid      string   `json:"grid,omitempty"`
	GridExt   string   `json:"gridsquare_ext,omitempty"`
	VUCCGrids string   `json:"vucc_grids,omitempty"`
	Country   string   `json:"country,omitempty"`
	State     string   `json:"state,omitempty"`
	County    string   `json:"cnty,omitempty"`
	DXCC      *int     `json:"dxcc,omitempty"`
	Continent string   `json:"cont,omitempty"`
	CQZ       *int     `json:"cqz,omitempty"`
	ITUZ      *int     `json:"ituz,omitempty"`
	IOTA      string   `json:"iota,omitempty"`
	SOTARef   string   `json:"sota_ref,omitempty"`
	POTARef   string   `json:"pota_ref,omitempty"`
	Age       *int     `json:"age,omitempty"`
	Lat       *float64 `json:"lat,omitempty"`
	Lon       *float64 `json:"lon,omitempty"`
	Rig       string   `json:"rig,omitempty"`
	Ant       string   `json:"ant,omitempty"`

	// --- QSL / LoTW / eQSL / Clublog / HRDLog ---
	QSLSent     string `json:"qsl_sent,omitempty"`
	QSLRcvd     string `json:"qsl_rcvd,omitempty"`
	QSLSentDate string `json:"qsl_sent_date,omitempty"`
	QSLRcvdDate string `json:"qsl_rcvd_date,omitempty"`
	QSLVia      string `json:"qsl_via,omitempty"`      // ADIF QSL_VIA — the QSL manager
	QSLSentVia  string `json:"qsl_sent_via,omitempty"` // ADIF enumeration B/D/E — how the card was sent
	QSLRcvdVia  string `json:"qsl_rcvd_via,omitempty"` // same enumeration, for the card received
	QSLMsg      string `json:"qsl_msg,omitempty"`
	QSLMsgRcvd  string `json:"qslmsg_rcvd,omitempty"`

	LOTWSent     string `json:"lotw_sent,omitempty"`
	LOTWRcvd     string `json:"lotw_rcvd,omitempty"`
	LOTWSentDate string `json:"lotw_sent_date,omitempty"`
	LOTWRcvdDate string `json:"lotw_rcvd_date,omitempty"`

	EQSLSent     string `json:"eqsl_sent,omitempty"`
	EQSLRcvd     string `json:"eqsl_rcvd,omitempty"`
	EQSLSentDate string `json:"eqsl_sent_date,omitempty"`
	EQSLRcvdDate string `json:"eqsl_rcvd_date,omitempty"`

	ClublogUploadDate    string `json:"clublog_qso_upload_date,omitempty"`
	ClublogUploadStatus  string `json:"clublog_qso_upload_status,omitempty"`
	HRDLogUploadDate     string `json:"hrdlog_qso_upload_date,omitempty"`
	HRDLogUploadStatus   string `json:"hrdlog_qso_upload_status,omitempty"`
	QRZComUploadDate     string `json:"qrzcom_qso_upload_date,omitempty"`
	QRZComUploadStatus   string `json:"qrzcom_qso_upload_status,omitempty"`
	QRZComDownloadDate   string `json:"qrzcom_qso_download_date,omitempty"`
	QRZComDownloadStatus string `json:"qrzcom_qso_download_status,omitempty"`
	// Club Log match download (getmatches.php) — app-defined, no standard ADIF
	// field exists; exported/imported as CLUBLOG_QSO_DOWNLOAD_*.
	ClublogDownloadDate   string `json:"clublog_qso_download_date,omitempty"`
	ClublogDownloadStatus string `json:"clublog_qso_download_status,omitempty"`

	// --- Contest ---
	ContestID  string `json:"contest_id,omitempty"`
	SRX        *int   `json:"srx,omitempty"`
	STX        *int   `json:"stx,omitempty"`
	SRXString  string `json:"srx_string,omitempty"`
	STXString  string `json:"stx_string,omitempty"`
	Check      string `json:"check,omitempty"`
	Precedence string `json:"precedence,omitempty"`
	ARRLSect   string `json:"arrl_sect,omitempty"`

	// --- Satellite / propagation ---
	PropMode string   `json:"prop_mode,omitempty"`
	SatName  string   `json:"sat_name,omitempty"`
	SatMode  string   `json:"sat_mode,omitempty"`
	AntAz    *float64 `json:"ant_az,omitempty"`
	AntEl    *float64 `json:"ant_el,omitempty"`
	AntPath  string   `json:"ant_path,omitempty"`

	// --- My station / operator ---
	StationCallsign string   `json:"station_callsign,omitempty"`
	Operator        string   `json:"operator,omitempty"`
	MyGrid          string   `json:"my_grid,omitempty"`
	MyGridExt       string   `json:"my_gridsquare_ext,omitempty"`
	MyCountry       string   `json:"my_country,omitempty"`
	MyState         string   `json:"my_state,omitempty"`
	MyCounty        string   `json:"my_cnty,omitempty"`
	MyIOTA          string   `json:"my_iota,omitempty"`
	MySOTARef       string   `json:"my_sota_ref,omitempty"`
	MyPOTARef       string   `json:"my_pota_ref,omitempty"`
	MyDXCC          *int     `json:"my_dxcc,omitempty"`
	MyCQZone        *int     `json:"my_cq_zone,omitempty"`
	MyITUZone       *int     `json:"my_itu_zone,omitempty"`
	MyLat           *float64 `json:"my_lat,omitempty"`
	MyLon           *float64 `json:"my_lon,omitempty"`
	MyStreet        string   `json:"my_street,omitempty"`
	MyCity          string   `json:"my_city,omitempty"`
	MyPostalCode    string   `json:"my_postal_code,omitempty"`
	MyRig           string   `json:"my_rig,omitempty"`
	MyAntenna       string   `json:"my_antenna,omitempty"`

	// --- Misc ---
	TXPower *float64 `json:"tx_pwr,omitempty"`
	Comment string   `json:"comment,omitempty"`
	Notes   string   `json:"notes,omitempty"`

	// --- ADIF 3.1.7 additional promoted fields ---
	// Kept in one block so columnList / args() / scanQSO stay trivially in
	// sync (they are appended at the end, before extras_json).
	SIG             string   `json:"sig,omitempty"`
	SIGInfo         string   `json:"sig_info,omitempty"`
	MySIG           string   `json:"my_sig,omitempty"`
	MySIGInfo       string   `json:"my_sig_info,omitempty"`
	WWFFRef         string   `json:"wwff_ref,omitempty"`
	MyWWFFRef       string   `json:"my_wwff_ref,omitempty"`
	Distance        *float64 `json:"distance,omitempty"`
	RXPower         *float64 `json:"rx_pwr,omitempty"`
	AIndex          *float64 `json:"a_index,omitempty"`
	KIndex          *float64 `json:"k_index,omitempty"`
	SFI             *float64 `json:"sfi,omitempty"`
	SKCC            string   `json:"skcc,omitempty"`
	FISTS           string   `json:"fists,omitempty"`
	TenTen          string   `json:"ten_ten,omitempty"`
	ContactedOp     string   `json:"contacted_op,omitempty"`
	EqCall          string   `json:"eq_call,omitempty"`
	PFX             string   `json:"pfx,omitempty"`
	MyName          string   `json:"my_name,omitempty"`
	Class           string   `json:"class,omitempty"`
	DarcDOK         string   `json:"darc_dok,omitempty"`
	MyDarcDOK       string   `json:"my_darc_dok,omitempty"`
	Region          string   `json:"region,omitempty"`
	SilentKey       string   `json:"silent_key,omitempty"`
	SWL             string   `json:"swl,omitempty"`
	QSOComplete     string   `json:"qso_complete,omitempty"`
	QSORandom       string   `json:"qso_random,omitempty"`
	CreditGranted   string   `json:"credit_granted,omitempty"`
	CreditSubmitted string   `json:"credit_submitted,omitempty"`
	MyARRLSect      string   `json:"my_arrl_sect,omitempty"`
	MyVUCCGrids     string   `json:"my_vucc_grids,omitempty"`

	// Extras holds ADIF fields not promoted to columns. Keys are uppercase
	// ADIF field names (e.g. "MS_SHOWER"); values are the raw string content.
	Extras map[string]string `json:"extras,omitempty"`

	// AwardRefs is the materialised award-reference JSON for this QSO — a compact
	// object keyed by award code, e.g. {"DDFM":"74","WAJA":"12"}. Derived (the app
	// computes it on log/edit and bulk-recomputes it when awards change) and stored
	// so the grid's award columns read straight from the row. Written ONLY via
	// SetAwardRefs, never through the normal insert/update column list, so an edit
	// that doesn't know about it can't clobber it.
	AwardRefs string `json:"award_refs,omitempty"`

	// SyncUID is this contact's stable identity for folder synchronisation —
	// what lets another machine name the same QSO when it edits or deletes it.
	// Like AwardRefs it is read here but NOT in columnList, so an ordinary edit
	// can never clobber it; it is written only by SetSyncUID / BackfillSyncUIDs.
	SyncUID string `json:"sync_uid,omitempty"`

	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

// ListFilter filters a search in the logbook.
// Any zero/nil field is ignored.
type ListFilter struct {
	Callsign        string `json:"callsign,omitempty"`
	Band            string `json:"band,omitempty"`
	Mode            string `json:"mode,omitempty"`
	StationCallsign string `json:"station_callsign,omitempty"`
	Limit           int    `json:"limit,omitempty"`
	Offset          int    `json:"offset,omitempty"`
}

// Repo accesses the qso table.
type Repo struct {
	db *sql.DB
}

// NewRepo builds a QSO repo on the given connection.
func NewRepo(db *sql.DB) *Repo { return &Repo{db: db} }

const isoMillis = "2006-01-02T15:04:05.000Z"

// columnList is the canonical list of columns in declaration order. It is
// used by both read and write queries so they stay in sync.
const columnList = `callsign, qso_date, qso_date_off, band, band_rx, mode, submode, freq_hz, freq_rx_hz,
    rst_sent, rst_rcvd,
    name, qth, address, email, web,
    grid, gridsquare_ext, vucc_grids,
    country, state, cnty, dxcc, cont, cqz, ituz, iota, sota_ref, pota_ref,
    age, lat, lon, rig, ant,
    qsl_sent, qsl_rcvd, qsl_sent_date, qsl_rcvd_date, qsl_via, qsl_sent_via, qsl_rcvd_via, qsl_msg, qslmsg_rcvd,
    lotw_sent, lotw_rcvd, lotw_sent_date, lotw_rcvd_date,
    eqsl_sent, eqsl_rcvd, eqsl_sent_date, eqsl_rcvd_date,
    clublog_qso_upload_date, clublog_qso_upload_status,
    hrdlog_qso_upload_date, hrdlog_qso_upload_status,
    qrzcom_qso_upload_date, qrzcom_qso_upload_status,
    qrzcom_qso_download_date, qrzcom_qso_download_status,
    clublog_qso_download_date, clublog_qso_download_status,
    contest_id, srx, stx, srx_string, stx_string, check_field, precedence, arrl_sect,
    prop_mode, sat_name, sat_mode, ant_az, ant_el, ant_path,
    station_callsign, operator, my_grid, my_gridsquare_ext, my_country, my_state, my_cnty, my_iota,
    my_sota_ref, my_pota_ref, my_dxcc, my_cq_zone, my_itu_zone, my_lat, my_lon,
    my_street, my_city, my_postal_code, my_rig, my_antenna,
    tx_pwr, comment, notes,
    sig, sig_info, my_sig, my_sig_info, wwff_ref, my_wwff_ref,
    distance, rx_pwr, a_index, k_index, sfi,
    skcc, fists, ten_ten, contacted_op, eq_call, pfx, my_name, class,
    darc_dok, my_darc_dok, region, silent_key, swl, qso_complete, qso_random,
    credit_granted, credit_submitted, my_arrl_sect, my_vucc_grids,
    extras_json`

// award_refs is read here but is NOT part of columnList (the insert/update
// write path) — it is a derived cache written only via SetAwardRefs, so a
// normal QSO write can never clobber it.
const selectCols = `id, ` + columnList + `, award_refs, sync_uid, created_at, updated_at`

// columnCount is derived from columnList at init so they can never drift.
var columnCount = countColumns(columnList)

// insertPlaceholders returns "?,?,?,..." matching columnCount.
var insertPlaceholders = buildInsertPlaceholders()

// insertCols/insertVals append created_at + updated_at so they're set from Go
// (NowISO) on every backend — MySQL has no strftime default, and binding the
// timestamp keeps a single backend-agnostic INSERT. insertArgs pairs with them.
const insertCols = columnList + `, created_at, updated_at`

var insertVals = insertPlaceholders + ",?,?"

// insertArgs returns the column values plus the two timestamps for an INSERT.
func (q *QSO) insertArgs() []any {
	now := db.NowISO()
	return append(q.args(), now, now)
}

func countColumns(s string) int {
	n := 1
	for i := 0; i < len(s); i++ {
		if s[i] == ',' {
			n++
		}
	}
	return n
}

func buildInsertPlaceholders() string {
	out := make([]byte, 0, 2*columnCount)
	for i := 0; i < columnCount; i++ {
		if i > 0 {
			out = append(out, ',')
		}
		out = append(out, '?')
	}
	return string(out)
}

// args returns the QSO field values in the same order as columnList.
func (q *QSO) args() []any {
	extras := encodeExtras(q.Extras)
	return []any{
		q.Callsign, q.QSODate.UTC().Format(isoMillis), nullDateTime(q.QSODateOff), q.Band, q.BandRX, q.Mode, q.Submode, q.FreqHz, q.FreqRXHz,
		q.RSTSent, q.RSTRcvd,
		q.Name, q.QTH, q.Address, q.Email, q.Web,
		q.Grid, q.GridExt, q.VUCCGrids,
		q.Country, q.State, q.County, q.DXCC, q.Continent, q.CQZ, q.ITUZ, q.IOTA, q.SOTARef, q.POTARef,
		q.Age, q.Lat, q.Lon, q.Rig, q.Ant,
		q.QSLSent, q.QSLRcvd, q.QSLSentDate, q.QSLRcvdDate, q.QSLVia, q.QSLSentVia, q.QSLRcvdVia, q.QSLMsg, q.QSLMsgRcvd,
		q.LOTWSent, q.LOTWRcvd, q.LOTWSentDate, q.LOTWRcvdDate,
		q.EQSLSent, q.EQSLRcvd, q.EQSLSentDate, q.EQSLRcvdDate,
		q.ClublogUploadDate, q.ClublogUploadStatus,
		q.HRDLogUploadDate, q.HRDLogUploadStatus,
		q.QRZComUploadDate, q.QRZComUploadStatus,
		q.QRZComDownloadDate, q.QRZComDownloadStatus,
		q.ClublogDownloadDate, q.ClublogDownloadStatus,
		q.ContestID, q.SRX, q.STX, q.SRXString, q.STXString, q.Check, q.Precedence, q.ARRLSect,
		q.PropMode, q.SatName, q.SatMode, q.AntAz, q.AntEl, q.AntPath,
		q.StationCallsign, q.Operator, q.MyGrid, q.MyGridExt, q.MyCountry, q.MyState, q.MyCounty, q.MyIOTA,
		q.MySOTARef, q.MyPOTARef, q.MyDXCC, q.MyCQZone, q.MyITUZone, q.MyLat, q.MyLon,
		q.MyStreet, q.MyCity, q.MyPostalCode, q.MyRig, q.MyAntenna,
		q.TXPower, q.Comment, q.Notes,
		q.SIG, q.SIGInfo, q.MySIG, q.MySIGInfo, q.WWFFRef, q.MyWWFFRef,
		q.Distance, q.RXPower, q.AIndex, q.KIndex, q.SFI,
		q.SKCC, q.FISTS, q.TenTen, q.ContactedOp, q.EqCall, q.PFX, q.MyName, q.Class,
		q.DarcDOK, q.MyDarcDOK, q.Region, q.SilentKey, q.SWL, q.QSOComplete, q.QSORandom,
		q.CreditGranted, q.CreditSubmitted, q.MyARRLSect, q.MyVUCCGrids,
		extras,
	}
}

// nullDateTime renders a time.Time as ISO UTC or nil if zero.
func nullDateTime(t time.Time) any {
	if t.IsZero() {
		return nil
	}
	return t.UTC().Format(isoMillis)
}

// encodeExtras returns a JSON-encoded string or nil if empty.
func encodeExtras(m map[string]string) any {
	if len(m) == 0 {
		return nil
	}
	b, err := json.Marshal(m)
	if err != nil {
		return nil
	}
	return string(b)
}

func decodeExtras(s string) map[string]string {
	if s == "" {
		return nil
	}
	var m map[string]string
	if err := json.Unmarshal([]byte(s), &m); err != nil {
		return nil
	}
	return m
}

// DistinctExtraKeys returns the set of keys present across stored QSO extras
// (the non-promoted ADIF tags kept verbatim), scanning the most recent `sample`
// rows that have any extras. Capped so it stays fast on a large remote logbook —
// the extras vocabulary is small and stable, so a recent sample finds it all.
func (r *Repo) DistinctExtraKeys(ctx context.Context, sample int) ([]string, error) {
	if sample <= 0 {
		sample = 5000
	}
	rows, err := r.db.QueryContext(ctx,
		`SELECT extras_json FROM qso
		 WHERE extras_json IS NOT NULL AND extras_json <> '' AND extras_json <> '{}'
		 ORDER BY id DESC LIMIT ?`, sample)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	seen := map[string]bool{}
	for rows.Next() {
		var s sql.NullString
		if err := rows.Scan(&s); err != nil {
			return nil, err
		}
		for k := range decodeExtras(s.String) {
			seen[k] = true
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	out := make([]string, 0, len(seen))
	for k := range seen {
		out = append(out, k)
	}
	return out, nil
}

// Add inserts a QSO and returns its ID.
func (r *Repo) Add(ctx context.Context, q QSO) (int64, error) {
	q.Callsign = strings.ToUpper(strings.TrimSpace(q.Callsign))
	if q.Callsign == "" {
		return 0, fmt.Errorf("empty callsign")
	}
	if q.QSODate.IsZero() {
		q.QSODate = time.Now().UTC()
	}
	res, err := r.db.ExecContext(ctx,
		`INSERT INTO qso (`+insertCols+`) VALUES (`+insertVals+`)`,
		q.insertArgs()...)
	if err != nil {
		return 0, fmt.Errorf("insert qso: %w", err)
	}
	return res.LastInsertId()
}

// batchInsertRows is how many QSOs go into one multi-row INSERT on MySQL. Each
// row carries ~135 columns, so 200 rows ≈ 27k bound parameters — well under
// MySQL's 65535-placeholder limit and a modest packet — while cutting network
// round-trips ~200× versus one INSERT per row (critical for a remote server).
const batchInsertRows = 200

// AddBatch inserts many QSOs inside a single transaction. Empty-callsign records
// are skipped. On MySQL it uses chunked multi-row INSERTs so a 27k-record import
// over a remote link takes seconds, not many minutes; on local SQLite a prepared
// statement per row is already fast.
func (r *Repo) AddBatch(ctx context.Context, qsos []QSO) (int64, error) {
	if len(qsos) == 0 {
		return 0, nil
	}
	// Normalise and drop empty-callsign records up front.
	rows := make([]QSO, 0, len(qsos))
	for _, q := range qsos {
		q.Callsign = strings.ToUpper(strings.TrimSpace(q.Callsign))
		if q.Callsign == "" {
			continue
		}
		if q.QSODate.IsZero() {
			q.QSODate = time.Now().UTC()
		}
		rows = append(rows, q)
	}
	if len(rows) == 0 {
		return 0, nil
	}

	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return 0, fmt.Errorf("begin tx: %w", err)
	}
	defer tx.Rollback()

	var inserted int64
	if db.IsMySQL() {
		rowPlaceholder := "(" + insertVals + ")"
		for start := 0; start < len(rows); start += batchInsertRows {
			end := start + batchInsertRows
			if end > len(rows) {
				end = len(rows)
			}
			chunk := rows[start:end]
			var sb strings.Builder
			sb.WriteString("INSERT INTO qso (")
			sb.WriteString(insertCols)
			sb.WriteString(") VALUES ")
			args := make([]any, 0, len(chunk)*(columnCount+2))
			for j := range chunk {
				if j > 0 {
					sb.WriteByte(',')
				}
				sb.WriteString(rowPlaceholder)
				args = append(args, chunk[j].insertArgs()...)
			}
			if _, err := tx.ExecContext(ctx, sb.String(), args...); err != nil {
				return inserted, fmt.Errorf("batch insert: %w", err)
			}
			inserted += int64(len(chunk))
		}
	} else {
		stmt, err := tx.PrepareContext(ctx,
			`INSERT INTO qso (`+insertCols+`) VALUES (`+insertVals+`)`)
		if err != nil {
			return 0, fmt.Errorf("prepare batch insert: %w", err)
		}
		defer stmt.Close()
		for i := range rows {
			if _, err := stmt.ExecContext(ctx, rows[i].insertArgs()...); err != nil {
				return inserted, fmt.Errorf("insert qso %q: %w", rows[i].Callsign, err)
			}
			inserted++
		}
	}
	if err := tx.Commit(); err != nil {
		return 0, fmt.Errorf("commit batch: %w", err)
	}
	return inserted, nil
}

// Revision returns a cheap fingerprint of the logbook — row count and the
// highest id — so a client can poll for changes made by OTHER instances on a
// shared MySQL logbook and refresh when it differs. Inserts bump the max id;
// deletes change the count.
func (r *Repo) Revision(ctx context.Context) (string, error) {
	var count, maxID int64
	if err := r.db.QueryRowContext(ctx,
		`SELECT COUNT(*), COALESCE(MAX(id), 0) FROM qso`).Scan(&count, &maxID); err != nil {
		return "", err
	}
	return fmt.Sprintf("%d:%d", count, maxID), nil
}

// GetByID fetches a single QSO by primary key.
func (r *Repo) GetByID(ctx context.Context, id int64) (QSO, error) {
	row := r.db.QueryRowContext(ctx,
		`SELECT `+selectCols+` FROM qso WHERE id = ?`, id)
	return scanQSO(row)
}

// UploadRow is a lightweight QSO projection for the QSL Manager grid.
type UploadRow struct {
	ID       int64  `json:"id"`
	QSODate  string `json:"qso_date"` // ISO UTC; the UI formats it
	Callsign string `json:"callsign"`
	Band     string `json:"band"`
	Mode     string `json:"mode"`
	Country  string `json:"country"`
	Status   string `json:"status"` // the matched per-service sent status
}

// uploadStatusCols whitelists the per-service sent-status columns the QSL
// Manager may filter on (guards the dynamic column name in the query).
var uploadStatusCols = map[string]bool{
	"lotw_sent":                 true,
	"eqsl_sent":                 true,
	"qrzcom_qso_upload_status":  true,
	"clublog_qso_upload_status": true,
	"hrdlog_qso_upload_status":  true,
}

// ListForUpload returns QSOs whose per-service sent-status column equals
// value ("" matches blank/NULL). Used by the QSL Manager's "Select required".
// ListForUploadFull is like ListForUpload but returns FULL QSO rows so the UI
// can show the same rich, column-pickable table as Recent QSOs. column is an
// upload-status column (validated); value is the status to match ("" = not yet
// uploaded).
func (r *Repo) ListForUploadFull(ctx context.Context, column, value string) ([]QSO, error) {
	if !uploadStatusCols[column] {
		return nil, fmt.Errorf("invalid upload column %q", column)
	}
	rows, err := r.db.QueryContext(ctx,
		`SELECT `+selectCols+` FROM qso WHERE COALESCE(`+column+`,'') = ? ORDER BY qso_date DESC, id DESC`, value)
	if err != nil {
		return nil, fmt.Errorf("list for upload: %w", err)
	}
	defer rows.Close()
	out := make([]QSO, 0, 64)
	for rows.Next() {
		q, err := scanQSO(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, q)
	}
	return out, rows.Err()
}

func (r *Repo) ListForUpload(ctx context.Context, column, value string) ([]UploadRow, error) {
	if !uploadStatusCols[column] {
		return nil, fmt.Errorf("invalid upload column %q", column)
	}
	rows, err := r.db.QueryContext(ctx,
		`SELECT id, qso_date, callsign, COALESCE(band,''), COALESCE(mode,''),
		        COALESCE(country,''), COALESCE(`+column+`,'')
		   FROM qso WHERE COALESCE(`+column+`,'') = ?
		  ORDER BY qso_date DESC`, value)
	if err != nil {
		return nil, fmt.Errorf("list for upload: %w", err)
	}
	defer rows.Close()
	var out []UploadRow
	for rows.Next() {
		var u UploadRow
		if err := rows.Scan(&u.ID, &u.QSODate, &u.Callsign, &u.Band, &u.Mode, &u.Country, &u.Status); err != nil {
			return nil, err
		}
		out = append(out, u)
	}
	return out, rows.Err()
}

// UploadCandidate is a QSO eligible for an on-close upload: its id plus its
// STATION_CALLSIGN, so the caller can keep only the rows that belong to the
// active logbook's callsign (a mixed-call DB — F4BPO, F4BPO/P, TM2Q — must not
// all be signed under one cert).
type UploadCandidate struct {
	ID              int64
	StationCallsign string
}

// ListUploadCandidates returns QSOs eligible for an on-close upload to a
// service, scanning the whole logbook. For LoTW (column "lotw_sent"), statuses
// is the set of sent-status values to treat as "to send" (e.g. N, R); rows
// already "Y" are excluded. For QRZ/Club Log, statuses is ignored and anything
// whose upload status isn't yet "Y" qualifies.
func (r *Repo) ListUploadCandidates(ctx context.Context, column string, statuses []string) ([]UploadCandidate, error) {
	if !uploadStatusCols[column] {
		return nil, fmt.Errorf("invalid upload column %q", column)
	}
	var where string
	var args []any
	if column == "lotw_sent" {
		if len(statuses) == 0 {
			return nil, nil
		}
		ph := make([]string, len(statuses))
		for i, s := range statuses {
			ph[i] = "?"
			args = append(args, strings.ToUpper(strings.TrimSpace(s)))
		}
		where = "UPPER(COALESCE(lotw_sent,'')) IN (" + strings.Join(ph, ",") + ")"
	} else {
		where = "UPPER(COALESCE(" + column + ",'')) <> 'Y'"
	}
	rows, err := r.db.QueryContext(ctx,
		`SELECT id, COALESCE(station_callsign,'') FROM qso WHERE `+where+` ORDER BY qso_date`, args...)
	if err != nil {
		return nil, fmt.Errorf("list upload candidates: %w", err)
	}
	defer rows.Close()
	var out []UploadCandidate
	for rows.Next() {
		var c UploadCandidate
		if err := rows.Scan(&c.ID, &c.StationCallsign); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

// MarkQRZUploaded stamps QRZCOM_QSO_UPLOAD_STATUS=Y and the upload date on
// a QSO after a successful push to the QRZ.com logbook. date is an ADIF
// YYYYMMDD string. Only the two QRZ columns are touched — no full-row
// rewrite — so it's safe to call from the async upload path.
func (r *Repo) MarkQRZUploaded(ctx context.Context, id int64, date string) error {
	_, err := r.db.ExecContext(ctx,
		`UPDATE qso SET qrzcom_qso_upload_status = 'Y', qrzcom_qso_upload_date = ?,
		   updated_at = ? WHERE id = ?`,
		date, db.NowISO(), id)
	if err != nil {
		return fmt.Errorf("mark qrz uploaded %d: %w", id, err)
	}
	return nil
}

// MarkClublogUploaded stamps CLUBLOG_QSO_UPLOAD_STATUS=Y and the upload
// date after a successful Club Log push. date is an ADIF YYYYMMDD string.
func (r *Repo) MarkClublogUploaded(ctx context.Context, id int64, date string) error {
	_, err := r.db.ExecContext(ctx,
		`UPDATE qso SET clublog_qso_upload_status = 'Y', clublog_qso_upload_date = ?,
		   updated_at = ? WHERE id = ?`,
		date, db.NowISO(), id)
	if err != nil {
		return fmt.Errorf("mark clublog uploaded %d: %w", id, err)
	}
	return nil
}

// MarkHRDLogUploaded stamps HRDLOG_QSO_UPLOAD_STATUS=Y and the upload date
// after a successful HRDLog.net push. date is an ADIF YYYYMMDD string.
func (r *Repo) MarkHRDLogUploaded(ctx context.Context, id int64, date string) error {
	_, err := r.db.ExecContext(ctx,
		`UPDATE qso SET hrdlog_qso_upload_status = 'Y', hrdlog_qso_upload_date = ?,
		   updated_at = ? WHERE id = ?`,
		date, db.NowISO(), id)
	if err != nil {
		return fmt.Errorf("mark hrdlog uploaded %d: %w", id, err)
	}
	return nil
}

// MarkLoTWUploaded stamps LOTW_QSL_SENT=Y and the sent date after a
// successful TQSL upload. date is an ADIF YYYYMMDD string.
func (r *Repo) MarkLoTWUploaded(ctx context.Context, id int64, date string) error {
	_, err := r.db.ExecContext(ctx,
		`UPDATE qso SET lotw_sent = 'Y', lotw_sent_date = ?,
		   updated_at = ? WHERE id = ?`,
		date, db.NowISO(), id)
	if err != nil {
		return fmt.Errorf("mark lotw uploaded %d: %w", id, err)
	}
	return nil
}

// MarkUploadedBatch sets <statusCol>='Y' and <dateCol>=date on EVERY id in one
// UPDATE — used by bulk upload (Club Log / HRDLog) so a 25k-QSO run isn't one
// round-trip per QSO on a remote MySQL. statusCol/dateCol come from a fixed
// whitelist (not user input), so the column interpolation is safe.
func (r *Repo) MarkUploadedBatch(ctx context.Context, statusCol, dateCol, date string, ids []int64) error {
	if len(ids) == 0 {
		return nil
	}
	now := db.NowISO()
	_, err := bulkByIDChunks(ctx, ids, func(ph string, idArgs []any) (int64, error) {
		args := append([]any{date, now}, idArgs...)
		_, err := r.db.ExecContext(ctx,
			`UPDATE qso SET `+statusCol+` = 'Y', `+dateCol+` = ?, updated_at = ? WHERE id IN (`+ph+`)`,
			args...)
		return 0, err
	})
	if err != nil {
		return fmt.Errorf("mark uploaded batch (%d): %w", len(ids), err)
	}
	return nil
}

// MarkEQSLSent stamps EQSL_QSL_SENT=Y and the sent date after a successful
// eQSL e-mail. date is an ADIF YYYYMMDD string.
func (r *Repo) MarkEQSLSent(ctx context.Context, id int64, date string) error {
	_, err := r.db.ExecContext(ctx,
		`UPDATE qso SET eqsl_sent = 'Y', eqsl_sent_date = ?,
		   updated_at = ? WHERE id = ?`,
		date, db.NowISO(), id)
	if err != nil {
		return fmt.Errorf("mark eqsl sent %d: %w", id, err)
	}
	return nil
}

// bulkEditableCols whitelists the columns BulkSetField may write.
//
// Mostly TEXT fields where one value across many QSOs is meaningful: the
// per-service QSL/upload status fields, plus "my station"/operator fields
// naturally constant across a run (grid, antenna, rig, address, …).
//
// Mode, submode and RST are here too, which the original rule excluded as
// "per-QSO". That rule confused two different things. Bulk edit is not for
// describing QSOs, it is for REPAIRING a batch — an import that mapped every
// contact to SSB, an ADIF with no MODE at all — and refusing to fix a hundred
// rows because a hundred rows should not normally share a value leaves the
// operator editing them one at a time.
//
// Band is NOT here, and frequency is not either: both go through
// BulkSetFrequency, which writes the pair together. A band that contradicts its
// own frequency is invalid ADIF, and every export would carry the contradiction.
//
// Still excluded, and this part of the rule stands: callsign and date, which
// identify the contact rather than describe it, and the numeric columns (power,
// zones, lat/lon) that are meaningless shared.
var bulkEditableCols = map[string]bool{
	// QSL / upload status
	"lotw_sent":                   true,
	"lotw_rcvd":                   true,
	"eqsl_sent":                   true,
	"eqsl_rcvd":                   true,
	"qsl_sent":                    true,
	"qsl_rcvd":                    true,
	"qsl_via":                     true,
	"qsl_sent_via":                true,
	"qsl_rcvd_via":                true,
	"qrzcom_qso_upload_status":    true,
	"qrzcom_qso_download_status":  true,
	"clublog_qso_upload_status":   true,
	"clublog_qso_download_status": true,
	"hrdlog_qso_upload_status":    true,
	// Confirmation DATES. ADIF YYYYMMDD strings, so plain TEXT like the rest.
	"qsl_sent_date":             true,
	"qsl_rcvd_date":             true,
	"lotw_sent_date":            true,
	"lotw_rcvd_date":            true,
	"eqsl_sent_date":            true,
	"eqsl_rcvd_date":            true,
	"qrzcom_qso_upload_date":    true,
	"qrzcom_qso_download_date":  true,
	"clublog_qso_upload_date":   true,
	"clublog_qso_download_date": true,
	"hrdlog_qso_upload_date":    true,
	// My station / operator
	"station_callsign": true,
	"operator":         true,
	"my_grid":          true,
	"my_country":       true,
	"my_state":         true,
	"my_cnty":          true,
	"my_iota":          true,
	"my_sota_ref":      true,
	"my_pota_ref":      true,
	"my_wwff_ref":      true,
	"my_street":        true,
	"my_city":          true,
	"my_postal_code":   true,
	"my_rig":           true,
	"my_antenna":       true,
	"my_sig":           true,
	"my_sig_info":      true,
	"my_name":          true,
	"my_arrl_sect":     true,
	"my_darc_dok":      true,
	"my_vucc_grids":    true,
	// Contacted station: the LOCATION fields only. State and county are the ones
	// an import loses and a whole run shares (a park activation, a county line
	// operation), and the dialog has always offered them — but they were missing
	// here, so choosing one failed at Apply. The rest of the contacted station
	// (callsign, name, RST…) stays deliberately out: bulk-setting those would
	// corrupt the log.
	// grid: the locator an import loses wholesale. Correcting it one QSO at a
	// time is what the operator is trying to avoid, and unlike the callsign it
	// carries no risk of confusing two stations — a wrong value is simply
	// overwritten again.
	"grid":  true,
	"state": true,
	"cnty":  true,
	// Contest — the exchange/label fields that are constant across a run.
	// (srx/stx serial numbers stay excluded: they are per-QSO.)
	"contest_id": true,
	"srx_string": true,
	"stx_string": true,
	"arrl_sect":  true,
	"precedence": true,
	"class":      true,
	// Propagation / satellite — usually identical across a session.
	"prop_mode": true,
	"sat_name":  true,
	"sat_mode":  true,
	// Contacted station activation refs / SIG — meaningful to set across a
	// selected batch (e.g. a park/summit worked repeatedly).
	"pota_ref": true,
	"sota_ref": true,
	"wwff_ref": true,
	"iota":     true,
	"sig":      true,
	"sig_info": true,
	// The contact itself. Repair fields: an import that mapped everything to SSB,
	// or an ADIF that carried no MODE. Setting mode CLEARS submode (see
	// BulkSetField) — a submode left over from the old mode contradicts the new
	// one, and "FT8 / USB" is not a thing.
	"mode":     true,
	"submode":  true,
	"rst_sent": true,
	"rst_rcvd": true,
	// Misc text
	"comment": true,
	"notes":   true,
	"rig":     true,
	"ant":     true,
}

// BulkSetField sets one whitelisted column to value on every listed QSO in a
// single statement. value "" clears the field. Returns rows affected.
// bulkEditableIntCols are the NUMERIC columns the bulk editor may touch. Their
// own path, not the text one: the columns are nullable integers, and while
// SQLite would coerce "14" quietly, a shared MySQL logbook would not — and an
// empty string is NULL here, never "".
// bulkByIDChunks runs one UPDATE per slice of ids, small enough for SQLite's
// bound-variable cap: the single IN (…) with one placeholder per id worked at
// 10 000 QSOs and failed at 168 000 with "too many SQL variables". Each call
// gets the placeholder string and the id arguments for its slice; affected
// rows are summed. 500 per statement keeps every backend far from any limit
// while costing a few hundred statements on the largest logs.
func bulkByIDChunks(ctx context.Context, ids []int64, run func(ph string, idArgs []any) (int64, error)) (int64, error) {
	const chunk = 500
	var total int64
	for start := 0; start < len(ids); start += chunk {
		end := start + chunk
		if end > len(ids) {
			end = len(ids)
		}
		part := ids[start:end]
		ph := strings.Repeat("?,", len(part)-1) + "?"
		args := make([]any, len(part))
		for i, id := range part {
			args[i] = id
		}
		n, err := run(ph, args)
		if err != nil {
			return total, err
		}
		total += n
	}
	return total, nil
}

var bulkEditableIntCols = map[string]bool{
	"my_dxcc":     true,
	"my_cq_zone":  true,
	"my_itu_zone": true,
}

// BulkSetIntField sets one integer column across the ids; v nil clears it.
func (r *Repo) BulkSetIntField(ctx context.Context, ids []int64, column string, v *int) (int64, error) {
	if !bulkEditableIntCols[column] {
		return 0, fmt.Errorf("field %q is not bulk-editable", column)
	}
	if len(ids) == 0 {
		return 0, nil
	}
	var val any
	if v != nil {
		val = *v
	}
	now := db.NowISO()
	n, err := bulkByIDChunks(ctx, ids, func(ph string, idArgs []any) (int64, error) {
		args := append([]any{val, now}, idArgs...)
		res, err := r.db.ExecContext(ctx,
			"UPDATE qso SET "+column+" = ?, updated_at = ? WHERE id IN ("+ph+")", args...)
		if err != nil {
			return 0, err
		}
		return res.RowsAffected()
	})
	if err != nil {
		return n, fmt.Errorf("bulk set %s: %w", column, err)
	}
	return n, nil
}

func (r *Repo) BulkSetField(ctx context.Context, ids []int64, column, value string) (int64, error) {
	if !bulkEditableCols[column] {
		return 0, fmt.Errorf("field %q is not bulk-editable", column)
	}
	if len(ids) == 0 {
		return 0, nil
	}
	set := column + " = ?, updated_at = ?"
	if column == "mode" {
		// A submode belongs to the mode it was recorded under. Left behind, it
		// contradicts the new one — "FT8" with a submode of "USB" is not a thing,
		// and it is the submode that most ADIF readers believe. Clearing it is the
		// only outcome that leaves the row meaning what the operator asked for.
		set += ", submode = ''"
	}
	now := db.NowISO()
	n, err := bulkByIDChunks(ctx, ids, func(ph string, idArgs []any) (int64, error) {
		args := append([]any{value, now}, idArgs...)
		res, err := r.db.ExecContext(ctx,
			`UPDATE qso SET `+set+` WHERE id IN (`+ph+`)`, args...)
		if err != nil {
			return 0, err
		}
		return res.RowsAffected()
	})
	if err != nil {
		return n, fmt.Errorf("bulk set %s: %w", column, err)
	}
	return n, nil
}

// bulkEditableExtras whitelists ADIF fields that are bulk-editable but live in
// extras_json rather than in a promoted column. Key = the frontend's field id,
// value = the uppercase ADIF key inside the JSON object.
//
// OWNER_CALLSIGN is the case that prompted this: it was already filterable (see
// filterableExtras) but could not be bulk-edited, because BulkSetField writes a
// column and there is no owner_callsign column.
var bulkEditableExtras = map[string]string{
	"owner_callsign": "OWNER_CALLSIGN",
	// HAMLOG.online has no promoted column — the ADIF standard defines a field
	// for hamlog.EU and none for hamlog.ONLINE. Bulk-editable because a log
	// uploaded to their site BY HAND has to be markable afterwards: without it
	// OpsLog would offer to send every one of those contacts again.
	//
	// A STATUS and a DATE in each direction, like every other service: one field
	// holding a date and standing in for the flag looked clever and read as a
	// riddle — "which of these two is the status?" was the first thing an
	// operator asked of it.
	"hamlog_sent":      "APP_OPSLOG_HAMLOG_SENT",
	"hamlog_sent_date": "APP_OPSLOG_HAMLOG_SENT_DATE",
	"hamlog_rcvd":      "APP_HAMLOG_QSO_CFM",
	"hamlog_rcvd_date": "APP_OPSLOG_HAMLOG_QSL_DATE",
	// HamQTH, same story and SENT only — the site publishes no confirmations,
	// so there is no received side to edit. Bulk-editable for the one case that
	// matters: a log uploaded to HamQTH by hand, which OpsLog would otherwise
	// offer to send all over again.
	"hamqth_sent":      "APP_OPSLOG_HAMQTH_SENT",
	"hamqth_sent_date": "APP_OPSLOG_HAMQTH_SENT_DATE",
}

// BulkExtraKey maps a frontend field id to its ADIF key in extras_json, or "".
func BulkExtraKey(field string) string { return bulkEditableExtras[field] }

// BulkEditable reports whether a COLUMN may be bulk-written. Exported so the
// app layer can check its own field mapping against this whitelist: the two
// lists are separate, valid on their own, and a field present in one and absent
// from the other fails only when an operator tries to use it.
func BulkEditable(column string) bool { return bulkEditableCols[column] }

// BulkEditableColumns lists every bulk-writable column, for the same check from
// the other side: a column nothing maps to looks supported and cannot be used.
func BulkEditableColumns() []string {
	out := make([]string, 0, len(bulkEditableCols))
	for c := range bulkEditableCols {
		out = append(out, c)
	}
	sort.Strings(out)
	return out
}

// BulkSetExtra sets one whitelisted extras_json field on every listed QSO,
// leaving the other extras untouched. An empty value REMOVES the key rather than
// storing a blank — an empty extra would otherwise be carried into every export.
//
// json_set / json_remove exist under those names in both SQLite and MySQL and
// take the same '$.KEY' path syntax, so one statement serves both backends.
func (r *Repo) BulkSetExtra(ctx context.Context, ids []int64, adifKey, value string) (int64, error) {
	if adifKey == "" {
		return 0, fmt.Errorf("empty extras key")
	}
	if len(ids) == 0 {
		return 0, nil
	}
	head := []any{}
	expr := `json_set(COALESCE(extras_json, '{}'), '$.` + adifKey + `', ?)`
	if value == "" {
		expr = `json_remove(COALESCE(extras_json, '{}'), '$.` + adifKey + `')`
	} else {
		head = append(head, value)
	}
	head = append(head, db.NowISO())
	n, err := bulkByIDChunks(ctx, ids, func(ph string, idArgs []any) (int64, error) {
		args := append(append([]any{}, head...), idArgs...)
		res, err := r.db.ExecContext(ctx,
			`UPDATE qso SET extras_json = `+expr+`, updated_at = ? WHERE id IN (`+ph+`)`, args...)
		if err != nil {
			return 0, err
		}
		return res.RowsAffected()
	})
	if err != nil {
		return n, fmt.Errorf("bulk set extra %s: %w", adifKey, err)
	}
	return n, nil
}

// BulkSetFrequency sets freq_hz AND band together on every listed QSO. Kept
// separate from BulkSetField because frequency is numeric and must keep the band
// consistent — the main use is fixing a batch that was logged on a stale/default
// frequency after CAT dropped. freqHz "" is not allowed (caller validates).
func (r *Repo) BulkSetFrequency(ctx context.Context, ids []int64, freqHz int64, band string) (int64, error) {
	if len(ids) == 0 {
		return 0, nil
	}
	now := db.NowISO()
	n, err := bulkByIDChunks(ctx, ids, func(ph string, idArgs []any) (int64, error) {
		args := append([]any{freqHz, band, now}, idArgs...)
		res, err := r.db.ExecContext(ctx,
			`UPDATE qso SET freq_hz = ?, band = ?, updated_at = ? WHERE id IN (`+ph+`)`, args...)
		if err != nil {
			return 0, err
		}
		return res.RowsAffected()
	})
	if err != nil {
		return n, fmt.Errorf("bulk set frequency: %w", err)
	}
	return n, nil
}

// SetExtra merges ONE key into a QSO's extras JSON without rewriting the rest of
// the row. A slow caller that only wants to stamp its own app field (e-mailing a
// QSL card, saving a recording) must not do a full-row Update: the row it read may
// be seconds stale, and writing it all back silently reverts any column another
// action changed meanwhile — e.g. an auto-upload flipping clublog_qso_upload_status
// from R to Y. Touching only `extras` makes that impossible. Empty value deletes
// the key.
func (r *Repo) SetExtra(ctx context.Context, id int64, key, value string) error {
	if id == 0 || strings.TrimSpace(key) == "" {
		return fmt.Errorf("missing id or key")
	}
	var extrasJSON sql.NullString
	if err := r.db.QueryRowContext(ctx, `SELECT extras_json FROM qso WHERE id = ?`, id).Scan(&extrasJSON); err != nil {
		return fmt.Errorf("load extras: %w", err)
	}
	m := decodeExtras(extrasJSON.String)
	if m == nil {
		m = map[string]string{}
	}
	if value == "" {
		delete(m, key)
	} else {
		m[key] = value
	}
	if _, err := r.db.ExecContext(ctx,
		`UPDATE qso SET extras_json = ?, updated_at = ? WHERE id = ?`,
		encodeExtras(m), db.NowISO(), id); err != nil {
		return fmt.Errorf("set extra %s: %w", key, err)
	}
	return nil
}

// SetAwardRefs stores the materialised award-reference JSON for one QSO. Like
// SetExtra it is a targeted single-column UPDATE — never a full-row write — so it
// cannot clobber a field another action changed meanwhile. updated_at is left
// UNTOUCHED on purpose: award_refs is a derived cache, and bumping updated_at
// would masquerade as a real edit (re-triggering uploads / sync that watch it).
func (r *Repo) SetAwardRefs(ctx context.Context, id int64, jsonStr string) error {
	if id == 0 {
		return fmt.Errorf("missing id")
	}
	if _, err := r.db.ExecContext(ctx,
		`UPDATE qso SET award_refs = ? WHERE id = ?`, jsonStr, id); err != nil {
		return fmt.Errorf("set award_refs: %w", err)
	}
	return nil
}

// SetAwardRefsBatch stores materialised award refs for many QSOs in a single
// transaction — used by the bulk recompute so a large logbook on a remote MySQL
// is one round-trip's worth of work, not N. Like SetAwardRefs it touches only the
// award_refs column and leaves updated_at alone (derived cache). A nil/empty map
// is a no-op.
func (r *Repo) SetAwardRefsBatch(ctx context.Context, byID map[int64]string) error {
	if len(byID) == 0 {
		return nil
	}
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin tx: %w", err)
	}
	defer tx.Rollback()
	stmt, err := tx.PrepareContext(ctx, `UPDATE qso SET award_refs = ? WHERE id = ?`)
	if err != nil {
		return fmt.Errorf("prepare: %w", err)
	}
	defer stmt.Close()
	for id, js := range byID {
		if _, err := stmt.ExecContext(ctx, js, id); err != nil {
			return fmt.Errorf("set award_refs %d: %w", id, err)
		}
	}
	return tx.Commit()
}

// Update overwrites all editable fields of an existing QSO. updated_at is bumped.
func (r *Repo) Update(ctx context.Context, q QSO) error {
	if q.ID == 0 {
		return fmt.Errorf("missing id")
	}
	q.Callsign = strings.ToUpper(strings.TrimSpace(q.Callsign))
	if q.Callsign == "" {
		return fmt.Errorf("empty callsign")
	}
	if q.QSODate.IsZero() {
		q.QSODate = time.Now().UTC()
	}
	setClause := buildUpdateSetClause()
	args := append(q.args(), db.NowISO(), q.ID)
	res, err := r.db.ExecContext(ctx,
		`UPDATE qso SET `+setClause+`, updated_at = ? WHERE id = ?`,
		args...)
	if err != nil {
		return fmt.Errorf("update qso %d: %w", q.ID, err)
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return sql.ErrNoRows
	}
	return nil
}

// buildUpdateSetClause turns "col1, col2, …" into "col1 = ?, col2 = ?, …".
func buildUpdateSetClause() string {
	// Quick split — columnList has no escaped commas.
	var out []byte
	col := []byte{}
	flush := func() {
		if len(col) == 0 {
			return
		}
		if len(out) > 0 {
			out = append(out, ',', ' ')
		}
		// trim leading whitespace
		i := 0
		for i < len(col) && (col[i] == ' ' || col[i] == '\n' || col[i] == '\t') {
			i++
		}
		out = append(out, col[i:]...)
		out = append(out, ' ', '=', ' ', '?')
		col = col[:0]
	}
	for i := 0; i < len(columnList); i++ {
		c := columnList[i]
		if c == ',' {
			flush()
			continue
		}
		col = append(col, c)
	}
	flush()
	return string(out)
}

// DeleteAll wipes every QSO from the logbook. Returns the number of rows
// removed. Use with care — there is no recovery short of a DB backup.
// Runs in a transaction and VACUUMs afterwards so the file actually shrinks.
func (r *Repo) DeleteAll(ctx context.Context) (int64, error) {
	res, err := r.db.ExecContext(ctx, `DELETE FROM qso`)
	if err != nil {
		return 0, fmt.Errorf("delete all qso: %w", err)
	}
	n, _ := res.RowsAffected()
	// VACUUM is run outside any transaction; reclaims pages so the .db file
	// stops being a 200 MB ghost after a 25k-row wipe.
	if _, err := r.db.ExecContext(ctx, `VACUUM`); err != nil {
		// Non-fatal: rows are gone, just file isn't shrunk. Surface the err.
		return n, fmt.Errorf("delete ok (%d rows) but vacuum failed: %w", n, err)
	}
	return n, nil
}

// DeleteMany removes several QSOs in one statement. Returns the number deleted.
func (r *Repo) DeleteMany(ctx context.Context, ids []int64) (int64, error) {
	if len(ids) == 0 {
		return 0, nil
	}
	n, err := bulkByIDChunks(ctx, ids, func(ph string, idArgs []any) (int64, error) {
		res, err := r.db.ExecContext(ctx, `DELETE FROM qso WHERE id IN (`+ph+`)`, idArgs...)
		if err != nil {
			return 0, err
		}
		return res.RowsAffected()
	})
	if err != nil {
		return n, fmt.Errorf("delete qsos: %w", err)
	}
	return n, nil
}

// Delete removes a QSO by id.
func (r *Repo) Delete(ctx context.Context, id int64) error {
	res, err := r.db.ExecContext(ctx, `DELETE FROM qso WHERE id = ?`, id)
	if err != nil {
		return fmt.Errorf("delete qso %d: %w", id, err)
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return sql.ErrNoRows
	}
	return nil
}

// List returns QSOs matching the filter, sorted by date desc.
func (r *Repo) List(ctx context.Context, f ListFilter) ([]QSO, error) {
	q := `SELECT ` + selectCols + ` FROM qso WHERE 1=1`
	args := []any{}
	if f.Callsign != "" {
		// Contains-match so a search for "XYZ" finds F4XYZ, F4XYZ/P, etc.
		q += " AND callsign LIKE ?"
		args = append(args, "%"+f.Callsign+"%")
	}
	if f.Band != "" {
		q += " AND band = ?"
		args = append(args, f.Band)
	}
	if f.Mode != "" {
		q += " AND mode = ?"
		args = append(args, f.Mode)
	}
	if f.StationCallsign != "" {
		q += " AND station_callsign = ?"
		args = append(args, f.StationCallsign)
	}
	q += " ORDER BY qso_date DESC, id DESC"
	if f.Limit <= 0 {
		f.Limit = 500
	}
	// Hard upper bound: 1M is enough to fit any realistic personal log.
	if f.Limit > 1_000_000 {
		f.Limit = 1_000_000
	}
	q += " LIMIT ? OFFSET ?"
	args = append(args, f.Limit, f.Offset)

	rows, err := r.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, fmt.Errorf("query qso: %w", err)
	}
	defer rows.Close()

	out := make([]QSO, 0, 64)
	for rows.Next() {
		q, err := scanQSO(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, q)
	}
	return out, rows.Err()
}

// ── Advanced filter builder ──────────────────────────────────────────────
//
// QueryFilter powers the UI's filter builder: a list of field/operator/value
// conditions joined by AND or OR, plus an always-ANDed quick callsign search.
// Every field is validated against filterableColumns so user input can never
// reach the SQL string — only parameterised values do.

// Condition is one "field OP value" clause.
type Condition struct {
	Field string `json:"field"` // db column name (validated against whitelist)
	Op    string `json:"op"`    // eq|ne|gt|lt|ge|le|contains|startswith|endswith|empty|notempty
	Value string `json:"value"`
}

// QueryFilter is a full filter expression.
type QueryFilter struct {
	QuickCallsign string      `json:"quick_callsign,omitempty"` // always-ANDed contains-match
	Conditions    []Condition `json:"conditions,omitempty"`
	Match         string      `json:"match,omitempty"` // "AND" (default) | "OR"
	Limit         int         `json:"limit,omitempty"`
	Offset        int         `json:"offset,omitempty"`
}

// filterableColumns whitelists the columns the filter builder may reference.
// Keep field names identical to DB columns so the frontend can send them
// directly; anything not in this set is rejected.
var filterableColumns = map[string]bool{
	"callsign": true, "qso_date": true, "qso_date_off": true, "band": true, "band_rx": true,
	"mode": true, "submode": true, "freq_hz": true, "freq_rx_hz": true,
	"rst_sent": true, "rst_rcvd": true,
	"name": true, "qth": true, "address": true, "email": true,
	"grid": true, "country": true, "state": true, "cnty": true,
	"dxcc": true, "cont": true, "cqz": true, "ituz": true,
	"iota": true, "sota_ref": true, "pota_ref": true, "wwff_ref": true, "rig": true, "ant": true,
	"qsl_sent": true, "qsl_rcvd": true, "qsl_via": true, "qsl_sent_via": true, "qsl_rcvd_via": true,
	"lotw_sent": true, "lotw_rcvd": true, "eqsl_sent": true, "eqsl_rcvd": true,
	"qrzcom_qso_upload_status": true, "qrzcom_qso_download_status": true,
	"clublog_qso_upload_status": true, "clublog_qso_download_status": true,
	"hrdlog_qso_upload_status": true,
	// Confirmation DATES. ADIF YYYYMMDD strings, so a plain string comparison is
	// also chronological — "before 20240101" works with no date parsing.
	"qsl_sent_date": true, "qsl_rcvd_date": true,
	"lotw_sent_date": true, "lotw_rcvd_date": true,
	"eqsl_sent_date": true, "eqsl_rcvd_date": true,
	"qrzcom_qso_upload_date": true, "qrzcom_qso_download_date": true,
	"clublog_qso_upload_date": true, "clublog_qso_download_date": true,
	"hrdlog_qso_upload_date": true,
	"contest_id":             true, "srx": true, "stx": true,
	"prop_mode": true, "sat_name": true,
	"station_callsign": true, "operator": true, "my_grid": true, "my_country": true,
	"my_state": true, "my_cnty": true, "my_iota": true, "my_sota_ref": true, "my_pota_ref": true,
	"my_wwff_ref": true, "my_street": true, "my_city": true, "my_postal_code": true,
	"my_rig": true, "my_antenna": true, "my_sig": true, "my_sig_info": true,
	"tx_pwr": true, "comment": true, "notes": true,
	// When the record entered THIS logbook, as opposed to when the contact was
	// made. The question it answers is "what did that import actually add?" —
	// unanswerable otherwise, since an import of old contacts carries old QSO
	// dates and hides inside the log.
	"created_at": true,
}

// dateColumns are stored as full ISO timestamps; a filter on a bare YYYY-MM-DD
// value compares on the date part (see conditionSQL) so day filters are exact.
var dateColumns = map[string]bool{"qso_date": true, "qso_date_off": true, "created_at": true}

// numericColumns are the filterable columns holding numbers rather than text.
//
// "Empty" means something different for them: NULL *or* zero. It has to be said
// explicitly because the two backends disagree — SQLite compares 0 against ”
// as false, MySQL calls it true — so one expression would quietly answer two
// different questions depending on where the logbook lives.
var numericColumns = map[string]bool{
	"freq_hz": true, "freq_rx_hz": true, "dxcc": true, "cqz": true, "ituz": true,
	"srx": true, "stx": true, "tx_pwr": true,
}

// bareDateRe matches a plain calendar date with no time component.
var bareDateRe = regexp.MustCompile(`^\d{4}-\d{2}-\d{2}$`)

// filterableExtras whitelists virtual filter fields stored inside extras_json
// (valid ADIF fields we don't promote to columns). The value is the uppercase
// ADIF/Extras key; the SQL expression uses json_extract.
var filterableExtras = map[string]string{
	"owner_callsign": "OWNER_CALLSIGN",
	// HAMLOG.online, in both directions and with its dates. Filterable for the
	// same reason it is bulk-editable: without it there is no way to ask "which
	// contacts have never gone there", which is the question an operator asks
	// before uploading a backlog. The received key is THEIR field name — see
	// award.HamlogQSLKey — so an imported log filters the same as a stamped one.
	"hamlog_sent":      "APP_OPSLOG_HAMLOG_SENT",
	"hamlog_sent_date": "APP_OPSLOG_HAMLOG_SENT_DATE",
	"hamlog_rcvd":      "APP_HAMLOG_QSO_CFM",
	"hamlog_rcvd_date": "APP_OPSLOG_HAMLOG_QSL_DATE",
	// HamQTH — sent only, and filterable for the question that precedes every
	// backlog upload: "which contacts have never gone there".
	"hamqth_sent":      "APP_OPSLOG_HAMQTH_SENT",
	"hamqth_sent_date": "APP_OPSLOG_HAMQTH_SENT_DATE",
}

// FilterableFields returns the whitelist (for the frontend to build its field
// dropdown and stay in sync with the backend).
func FilterableFields() []string {
	out := make([]string, 0, len(filterableColumns)+len(filterableExtras))
	for c := range filterableColumns {
		out = append(out, c)
	}
	for c := range filterableExtras {
		out = append(out, c)
	}
	sort.Strings(out)
	return out
}

// columnExpr resolves a filter field to a safe SQL expression — either a
// OfflineQueueKey is the ADIF extras key stamped on a QSO that was parked in the
// offline safety file. It survives the ADIF round-trip and makes the replay
// IDEMPOTENT: if the app dies between "inserted into the DB" and "removed from
// the file", the next replay sees the id already in the log and skips it instead
// of creating a duplicate.
const OfflineQueueKey = "APP_OPSLOG_QUEUEID"

// ExistsByQueueID reports whether a QSO carrying this offline-queue id is already
// in the logbook — the guard that makes replaying the safety file safe to repeat.
func (r *Repo) ExistsByQueueID(ctx context.Context, qid string) (bool, error) {
	qid = strings.TrimSpace(qid)
	if qid == "" {
		return false, nil
	}
	expr := "json_extract(extras_json, '$." + OfflineQueueKey + "')"
	if db.IsMySQL() {
		expr = "JSON_UNQUOTE(JSON_EXTRACT(NULLIF(extras_json,''), '$." + OfflineQueueKey + "'))"
	}
	var n int
	if err := r.db.QueryRowContext(ctx,
		`SELECT COUNT(*) FROM qso WHERE `+expr+` = ?`, qid).Scan(&n); err != nil {
		return false, err
	}
	return n > 0, nil
}

// whitelisted column name or a json_extract over extras_json.
func columnExpr(field string) (string, bool) {
	f := strings.ToLower(strings.TrimSpace(field))
	if filterableColumns[f] {
		return f, true
	}
	if key, ok := filterableExtras[f]; ok {
		if db.IsMySQL() {
			// JSON_EXTRACT errors on an invalid/empty document, so guard with
			// NULLIF; JSON_UNQUOTE strips the quotes MySQL adds around strings.
			return "JSON_UNQUOTE(JSON_EXTRACT(NULLIF(extras_json,''), '$." + key + "'))", true
		}
		return "json_extract(extras_json, '$." + key + "')", true
	}
	return "", false
}

// conditionSQL turns one condition into a parameterised predicate.
// splitList parses the comma-separated value of an "in" / "not in" condition.
//
// Blanks are dropped, so a trailing comma or a stray space while typing does not
// silently add an empty value that matches nothing. Case is left alone: the
// columns this is used on (band, mode, country) are stored in the case the log
// was written in, and forcing it here would break the ones that are not.
func splitList(v string) []string {
	parts := strings.Split(v, ",")
	out := make([]string, 0, len(parts))
	for _, p := range parts {
		if p = strings.TrimSpace(p); p != "" {
			out = append(out, p)
		}
	}
	return out
}

func conditionSQL(c Condition) (string, []any, error) {
	col, ok := columnExpr(c.Field)
	if !ok {
		return "", nil, fmt.Errorf("unknown filter field %q", c.Field)
	}
	v := c.Value
	// Date columns hold full ISO timestamps ("2020-01-01T12:34:56.000Z"). When
	// the user filters on a bare calendar date, compare only the date part so
	// "= 2020-01-01" matches that whole day and "<= 2020-12-31" includes it
	// (a raw string compare would otherwise drop times on the boundary day).
	if dateColumns[strings.ToLower(strings.TrimSpace(c.Field))] && bareDateRe.MatchString(strings.TrimSpace(v)) {
		col = "substr(" + col + ",1,10)"
		v = strings.TrimSpace(v)
	}
	// "equals nothing" and "is empty" are the same question, and SQL answers the
	// first with nothing at all: a NULL never equals '', so a filter written that
	// way returns zero rows and looks broken rather than wrong. Asking it in
	// plain words is not a mistake worth punishing.
	if strings.TrimSpace(v) == "" {
		switch c.Op {
		case "eq":
			c.Op = "empty"
		case "ne":
			c.Op = "notempty"
		}
	}
	switch c.Op {
	case "eq":
		return col + " = ?", []any{v}, nil
	case "ne":
		return col + " <> ?", []any{v}, nil
	case "gt":
		return col + " > ?", []any{v}, nil
	case "lt":
		return col + " < ?", []any{v}, nil
	case "ge":
		return col + " >= ?", []any{v}, nil
	case "le":
		return col + " <= ?", []any{v}, nil
	case "contains":
		return col + " LIKE ?", []any{"%" + v + "%"}, nil
	case "startswith":
		return col + " LIKE ?", []any{v + "%"}, nil
	case "endswith":
		return col + " LIKE ?", []any{"%" + v}, nil
	case "in", "notin":
		// Several values for ONE field, comma-separated.
		//
		// This is what makes a real question expressible. The filter joins every
		// condition with a single AND or OR, so "2 m or 70 cm, in FT8, since
		// January" could not be asked: AND killed the two bands, OR let every FT8
		// QSO through. Rather than grow the model nested groups — a tree in the UI
		// to answer a question that is nearly always "this field, any of these
		// values" — the OR lives INSIDE one condition and everything else keeps
		// ANDing.
		vals := splitList(v)
		if len(vals) == 0 {
			// An empty list matches nothing, which is the honest reading. Silently
			// dropping the condition would widen the result set instead.
			if c.Op == "in" {
				return "1=0", nil, nil
			}
			return "1=1", nil, nil
		}
		ph := strings.TrimSuffix(strings.Repeat("?,", len(vals)), ",")
		args := make([]any, 0, len(vals))
		for _, s := range vals {
			args = append(args, s)
		}
		if c.Op == "notin" {
			// IFNULL, or a NULL column would fail "NOT IN" and vanish from the
			// result — the row is not one of the listed values, so it belongs.
			return "IFNULL(" + col + ",'') NOT IN (" + ph + ")", args, nil
		}
		return col + " IN (" + ph + ")", args, nil
	case "empty":
		// A numeric column is empty when it is NULL *or* zero, and that has to be
		// said explicitly: SQLite compares 0 against '' as false while MySQL calls
		// it true, so IFNULL(col,'')='' quietly means different things on the two
		// backends OpsLog supports.
		if numericColumns[strings.ToLower(strings.TrimSpace(c.Field))] {
			return "(" + col + " IS NULL OR " + col + " = 0)", nil, nil
		}
		return "IFNULL(" + col + ",'') = ''", nil, nil
	case "notempty":
		if numericColumns[strings.ToLower(strings.TrimSpace(c.Field))] {
			return "(" + col + " IS NOT NULL AND " + col + " <> 0)", nil, nil
		}
		return "IFNULL(" + col + ",'') <> ''", nil, nil
	default:
		return "", nil, fmt.Errorf("unknown operator %q", c.Op)
	}
}

// quickCallsignLike turns the Recent-QSOs search box into a SQL LIKE pattern.
//
// The rule, which is the one an operator already has in their head from the
// alert filters:
//
//	4S     → starts with 4S      (4S%)
//	*4S    → ends with 4S        (%4S)
//	*4S*   → contains 4S         (%4S%)
//	4S?    → 4S and one more     (4S_)
//
// So a plain word is a PREFIX — the common case, and what makes typing a call
// feel like a call sign lookup rather than a text search. The moment a wildcard
// appears the pattern is taken literally end to end, which is the only way
// "*4S" can mean "ends with" while "4S" means "starts with".
//
// This used to be an unconditional contains-match, so there was no way to ask
// for a prefix at all: searching 4S returned every call with 4S buried in it.
//
// % and _ typed by the operator are escaped to literals — otherwise a search
// for "_" would quietly match everything.
func quickCallsignLike(pattern string) string {
	p := strings.TrimSpace(pattern)
	var b strings.Builder
	esc := func(r rune) {
		switch r {
		case '%', '_', '!':
			b.WriteByte('!')
		}
		b.WriteRune(r)
	}
	if !strings.ContainsAny(p, "*?") {
		for _, r := range p {
			esc(r)
		}
		b.WriteByte('%') // plain text = prefix
		return b.String()
	}
	for _, r := range p {
		switch r {
		case '*':
			b.WriteByte('%')
		case '?':
			b.WriteByte('_')
		default:
			esc(r)
		}
	}
	return b.String()
}

// buildWhere assembles the predicate (everything after WHERE) + args.
func buildWhere(f QueryFilter) (string, []any, error) {
	pred := "1=1"
	var args []any
	if qc := strings.TrimSpace(f.QuickCallsign); qc != "" {
		// ESCAPE '!' rather than the usual backslash: the clause is a literal in
		// the SQL text, and '\\' is one character to MySQL but two to SQLite,
		// which rejects it. '!' is one character to both and appears in no
		// callsign.
		pred += " AND callsign LIKE ? ESCAPE '!'"
		args = append(args, quickCallsignLike(qc))
	}
	if len(f.Conditions) > 0 {
		joiner := " AND "
		if strings.EqualFold(strings.TrimSpace(f.Match), "OR") {
			joiner = " OR "
		}
		parts := make([]string, 0, len(f.Conditions))
		for _, c := range f.Conditions {
			if strings.TrimSpace(c.Field) == "" {
				continue
			}
			p, a, err := conditionSQL(c)
			if err != nil {
				return "", nil, err
			}
			parts = append(parts, p)
			args = append(args, a...)
		}
		if len(parts) > 0 {
			pred += " AND (" + strings.Join(parts, joiner) + ")"
		}
	}
	return pred, args, nil
}

// ListFiltered returns QSOs matching a QueryFilter, newest first, limited.
func (r *Repo) ListFiltered(ctx context.Context, f QueryFilter) ([]QSO, error) {
	pred, args, err := buildWhere(f)
	if err != nil {
		return nil, err
	}
	q := `SELECT ` + selectCols + ` FROM qso WHERE ` + pred + ` ORDER BY qso_date DESC, id DESC`
	limit := f.Limit
	if limit <= 0 {
		limit = 500
	}
	if limit > 1_000_000 {
		limit = 1_000_000
	}
	q += " LIMIT ? OFFSET ?"
	args = append(args, limit, f.Offset)

	rows, err := r.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, fmt.Errorf("query qso: %w", err)
	}
	defer rows.Close()
	out := make([]QSO, 0, 64)
	for rows.Next() {
		qrow, err := scanQSO(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, qrow)
	}
	return out, rows.Err()
}

// CountFiltered returns how many QSOs match a filter (ignoring limit/offset).
func (r *Repo) CountFiltered(ctx context.Context, f QueryFilter) (int64, error) {
	pred, args, err := buildWhere(f)
	if err != nil {
		return 0, err
	}
	var n int64
	err = r.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM qso WHERE `+pred, args...).Scan(&n)
	return n, err
}

// IterateFiltered streams all QSOs matching a filter (no limit), chronological,
// for an ADIF export of "the current filtered view, no row limit".
func (r *Repo) IterateFiltered(ctx context.Context, f QueryFilter, fn func(QSO) error) error {
	pred, args, err := buildWhere(f)
	if err != nil {
		return err
	}
	rows, err := r.db.QueryContext(ctx,
		`SELECT `+selectCols+` FROM qso WHERE `+pred+` ORDER BY qso_date ASC, id ASC`, args...)
	if err != nil {
		return fmt.Errorf("query qso: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		q, err := scanQSO(rows)
		if err != nil {
			return err
		}
		if err := fn(q); err != nil {
			return err
		}
	}
	return rows.Err()
}

// IterateByIDs streams the QSOs with the given ids, chronological — for
// "export the rows I selected with the mouse".
func (r *Repo) IterateByIDs(ctx context.Context, ids []int64, fn func(QSO) error) error {
	if len(ids) == 0 {
		return nil
	}
	// Chunked like every other by-ids statement (the one-placeholder-per-id IN
	// died at 168k with "too many SQL variables") — and because each chunk is
	// only locally ordered, the rows are collected and sorted once at the end
	// so the chronological contract holds across chunks.
	var all []QSO
	_, err := bulkByIDChunks(ctx, ids, func(ph string, idArgs []any) (int64, error) {
		rows, err := r.db.QueryContext(ctx,
			`SELECT `+selectCols+` FROM qso WHERE id IN (`+ph+`)`, idArgs...)
		if err != nil {
			return 0, err
		}
		defer rows.Close()
		for rows.Next() {
			q, err := scanQSO(rows)
			if err != nil {
				return 0, err
			}
			all = append(all, q)
		}
		return 0, rows.Err()
	})
	if err != nil {
		return fmt.Errorf("query qso: %w", err)
	}
	sort.Slice(all, func(i, j int) bool {
		if !all[i].QSODate.Equal(all[j].QSODate) {
			return all[i].QSODate.Before(all[j].QSODate)
		}
		return all[i].ID < all[j].ID
	})
	for _, q := range all {
		if err := fn(q); err != nil {
			return err
		}
	}
	return nil
}

// GridKey builds the lookup key for the worked-grid index.
//
// band is "" for a "mix bands" scope, which is why the index stores BOTH forms
// for every contact: asking "worked anywhere" must not mean walking the map.
func GridKey(grid, band, class string) string {
	return grid + "|" + strings.ToLower(band) + "|" + class
}

// GridWorkedIndex maps every worked square to whether it is CONFIRMED, under
// every band-and-mode scope the operator can choose between.
//
// One QSO lands in several buckets, because the scopes overlap: an FT8 contact
// counts for "this exact mode", for "any FTx mode" and for "any digital mode",
// and each of those under both its own band and the any-band form. classesOf is
// supplied by the caller — the meaning of DIGI and FTx belongs upstairs with the
// rest of the mode vocabulary, not here.
//
// The value is "confirmed", not merely "present", so the same index answers both
// hunting modes: chase what has never been worked, or chase what is not yet
// confirmed. Confirmed means LoTW, a card or eQSL — the three the award engine
// counts, so a square cannot be confirmed here and unconfirmed there.
func (r *Repo) GridWorkedIndex(ctx context.Context, classesOf func(mode string) []string) (map[string]bool, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT COALESCE(grid,''), UPPER(COALESCE(mode,'')), LOWER(COALESCE(band,'')),
		       COALESCE(lotw_rcvd,''), COALESCE(qsl_rcvd,''), COALESCE(eqsl_rcvd,'')
		FROM qso
		WHERE grid IS NOT NULL AND grid != ''`)
	if err != nil {
		return nil, fmt.Errorf("query worked grids: %w", err)
	}
	defer rows.Close()
	out := make(map[string]bool, 8192)
	for rows.Next() {
		var grid, mode, band, lotw, card, eqsl string
		if err := rows.Scan(&grid, &mode, &band, &lotw, &card, &eqsl); err != nil {
			return nil, err
		}
		g := strings.ToUpper(strings.TrimSpace(grid))
		if len(g) < 4 {
			continue
		}
		g = g[:4]
		confirmed := lotw == "Y" || card == "Y" || eqsl == "Y"
		for _, cls := range classesOf(mode) {
			if cls == "" {
				continue
			}
			// || is never a downgrade: one confirmed contact confirms the square
			// for that scope, whatever the others say.
			for _, k := range []string{GridKey(g, band, cls), GridKey(g, "", cls)} {
				out[k] = out[k] || confirmed
			}
		}
	}
	return out, rows.Err()
}

// GridSquare is one 4-character Maidenhead square in the log.
type GridSquare struct {
	Grid      string `json:"grid"`
	Count     int    `json:"count"`
	Confirmed bool   `json:"confirmed"`
	// Band and Mode of the most recent contact in the square, for the tooltip.
	// The square is the subject here, not the QSO, so one example is enough —
	// listing every band a square was worked on turns a map into a table.
	Band string `json:"band,omitempty"`
	Mode string `json:"mode,omitempty"`
}

// GridSquares aggregates the log into 4-character squares, newest contact
// deciding the example band/mode.
//
// modeOf filters and is supplied by the caller (it owns what "digital" means):
// return false to drop a QSO. Aggregation to 4 characters happens HERE rather
// than in SQL — the column holds 4, 6 and 8-character grids, and lower(substr)
// in SQL would differ between SQLite and MySQL for no gain.
func (r *Repo) GridSquares(ctx context.Context, keep func(mode string) bool) ([]GridSquare, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT COALESCE(grid,''), UPPER(COALESCE(mode,'')), LOWER(COALESCE(band,'')),
		       COALESCE(lotw_rcvd,''), COALESCE(qsl_rcvd,''), COALESCE(eqsl_rcvd,'')
		FROM qso
		WHERE grid IS NOT NULL AND grid != ''
		ORDER BY qso_date ASC, id ASC`)
	if err != nil {
		return nil, fmt.Errorf("query grid squares: %w", err)
	}
	defer rows.Close()
	out := map[string]*GridSquare{}
	for rows.Next() {
		var grid, mode, band, lotw, card, eqsl string
		if err := rows.Scan(&grid, &mode, &band, &lotw, &card, &eqsl); err != nil {
			return nil, err
		}
		if keep != nil && !keep(mode) {
			continue
		}
		g := strings.ToUpper(strings.TrimSpace(grid))
		if len(g) < 4 {
			continue
		}
		g = g[:4]
		// Guard the shape: a malformed grid would draw a rectangle somewhere
		// arbitrary, and a map with one square in the sea is a map nobody trusts.
		if g[0] < 'A' || g[0] > 'R' || g[1] < 'A' || g[1] > 'R' ||
			g[2] < '0' || g[2] > '9' || g[3] < '0' || g[3] > '9' {
			continue
		}
		sq := out[g]
		if sq == nil {
			sq = &GridSquare{Grid: g}
			out[g] = sq
		}
		sq.Count++
		// Ascending order, so the last write wins = the most recent contact.
		sq.Band, sq.Mode = band, mode
		if lotw == "Y" || card == "Y" || eqsl == "Y" {
			sq.Confirmed = true
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	list := make([]GridSquare, 0, len(out))
	for _, sq := range out {
		list = append(list, *sq)
	}
	sort.Slice(list, func(i, j int) bool { return list[i].Grid < list[j].Grid })
	return list, nil
}

// BandSlotQSOs returns every contact on one band that belongs to a slot of the
// entry matrix: the exact callsign, or any callsign in the same DXCC entity.
// Mode is NOT filtered here — the class (phone / CW / digital) is a derived
// notion the caller owns, so it filters the (small) band-scoped result itself.
//
// The DXCC arm matches the stored dxcc column only. A QSO logged before the
// entity was resolved has a NULL there and is genuinely unattributed; guessing
// it back from the callsign here would disagree with the matrix above, which is
// built from the same column.
func (r *Repo) BandSlotQSOs(ctx context.Context, callsign string, dxccNum int, band string) ([]QSO, error) {
	call := upperTrim(callsign)
	b := strings.TrimSpace(band)
	if b == "" || (call == "" && dxccNum <= 0) {
		return []QSO{}, nil
	}
	where := "band = ?"
	args := []any{b}
	switch {
	case call != "" && dxccNum > 0:
		where += " AND (callsign = ? OR dxcc = ?)"
		args = append(args, call, dxccNum)
	case call != "":
		where += " AND callsign = ?"
		args = append(args, call)
	default:
		where += " AND dxcc = ?"
		args = append(args, dxccNum)
	}
	rows, err := r.db.QueryContext(ctx,
		`SELECT `+selectCols+` FROM qso WHERE `+where+` ORDER BY qso_date DESC, id DESC LIMIT 500`, args...)
	if err != nil {
		return nil, fmt.Errorf("query band slot: %w", err)
	}
	defer rows.Close()
	out := []QSO{}
	for rows.Next() {
		q, err := scanQSO(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, q)
	}
	return out, rows.Err()
}

// WorkedBefore summarises prior contacts at two granularities:
//   - by exact callsign  → shown as "this call worked N×"
//   - by DXCC entity     → drives NEW ONE / NEW BAND / NEW MODE / NEW SLOT
//
// In ham radio the meaningful "new one" is a new DXCC entity, not a brand-new
// callsign — most QSOs to known countries aren't worth flagging.
type WorkedBefore struct {
	Callsign string `json:"callsign"`

	// --- Per-callsign ---
	Count     int        `json:"count"`           // total prior QSOs with this call
	First     time.Time  `json:"first,omitempty"` // oldest call QSO date
	Last      time.Time  `json:"last,omitempty"`  // most recent call QSO date
	Bands     []string   `json:"bands"`           // distinct bands for this call
	Modes     []string   `json:"modes"`           // distinct modes for this call
	BandModes []BandMode `json:"band_modes"`      // distinct (band, mode) pairs
	Entries   []QSO      `json:"entries"`         // up to maxWorkedEntries most recent (full records)

	// --- Per-DXCC entity (populated when DXCC is known) ---
	DXCC          int        `json:"dxcc,omitempty"`
	DXCCName      string     `json:"dxcc_name,omitempty"` // human country name (best effort)
	DXCCCount     int        `json:"dxcc_count"`
	DXCCFirst     time.Time  `json:"dxcc_first,omitempty"`
	DXCCLast      time.Time  `json:"dxcc_last,omitempty"`
	DXCCBands     []string   `json:"dxcc_bands"`
	DXCCModes     []string   `json:"dxcc_modes"`
	DXCCBandModes []BandMode `json:"dxcc_band_modes"`

	// MWRank is the ClubLog "Most Wanted" rank for this entity (1 = most wanted),
	// 0 when unknown or the feature is off. Populated by the app layer, not here.
	MWRank int `json:"mw_rank,omitempty"`

	// Status grid driving the band×class matrix in the UI. One entry per
	// (band, class) where ANY QSO exists in this DXCC. Only the highest
	// status for that cell is kept (call_c > dxcc_c > call_w > dxcc_w —
	// confirmed outranks worked).
	BandStatus []BandStatus `json:"band_status"`
}

// BandStatus is one cell in the worked-before grid.
//
// Status is the single highest thing true of the cell, which is what colours
// it. Call is the SAME cell's answer to a different question — "have I worked
// THIS callsign here" — kept separately because the two are asked at the same
// moment and one was hiding the other.
//
// Chasing an expedition, an operator needs both: whether the slot is still
// missing for the entity (does this fill a DXCC hole) and whether this
// expedition has already been worked on it (would this be a dupe). A confirmed
// entity outranks a worked callsign in Status — correctly, for awards — so a
// slot worked with the DX yesterday can read "entity confirmed" and say nothing
// at all about yesterday.
type BandStatus struct {
	Band   string `json:"band"`   // ADIF lowercase band, e.g. "20m"
	Class  string `json:"class"`  // "PH" | "CW" | "DIG", or a raw digital mode ("FT8", "RTTY"…)
	Status string `json:"status"` // "call_c" | "call_w" | "dxcc_c" | "dxcc_w"
	// Call is "", "w" (worked with this callsign) or "c" (confirmed with it).
	Call string `json:"call,omitempty"`
}

// Band-status codes, lowest first. The ORDER is the rule: a cell shows the
// highest that applies.
const (
	stDxccW = iota // the entity was worked on this slot
	stCallW        // …and this callsign was one of them
	stDxccC        // the entity is CONFIRMED here
	stCallC        // …and by this callsign
)

// bandStatusNames maps those codes to what the UI colours by.
var bandStatusNames = [...]string{"dxcc_w", "call_w", "dxcc_c", "call_c"}

// bandStatusCode picks the status of one cell of the band × mode matrix.
//
// CONFIRMED OUTRANKS WORKED, and that is the whole of it. The ladder used to
// run call_c > call_w > dxcc_c > dxcc_w, so a callsign worked and not confirmed
// beat an entity confirmed on the same slot: an operator with YB confirmed on
// 20m digital saw that cell as "worked, not confirmed" because he had also
// worked one unconfirmed YB station there. The grid answers "what do I still
// need", and a confirmed entity needs nothing, whoever was worked afterwards.
//
// Taken as a MAXIMUM rather than as a run of assignments, which is how the
// later test came to overwrite the earlier one in the first place.
func bandStatusCode(callWorked, callConfirmed, entityConfirmed bool) int {
	code := stDxccW // there is a row at all ⇒ the entity was worked here
	raise := func(c int) {
		if c > code {
			code = c
		}
	}
	if callWorked {
		raise(stCallW)
	}
	if entityConfirmed {
		raise(stDxccC)
	}
	if callConfirmed {
		raise(stCallC)
	}
	return code
}

// modeClass collapses ADIF modes into the three buckets DXers care about.
// Anything not voice and not CW is treated as digital.
// ModeClass is modeClass for callers outside the package — the watchlist
// judges slots at the same grain the cluster does.
func ModeClass(mode string) string { return modeClass(mode) }

// SlotRow is one (callsign, band, mode) triple from SlotsSince.
type SlotRow struct {
	Callsign string
	Band     string
	Mode     string
}

// SlotsSince lists the slots of every contact made since the given instant —
// the contest watchlist's "worked today", where the day boundary lives in this
// query's bound and nowhere else.
func (r *Repo) SlotsSince(ctx context.Context, since time.Time) ([]SlotRow, error) {
	rows, err := r.db.QueryContext(ctx,
		"SELECT callsign, band, mode FROM qso WHERE qso_date >= ?",
		since.UTC().Format(isoMillis))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []SlotRow
	for rows.Next() {
		var sr SlotRow
		if err := rows.Scan(&sr.Callsign, &sr.Band, &sr.Mode); err != nil {
			return nil, err
		}
		out = append(out, sr)
	}
	return out, rows.Err()
}

func modeClass(mode string) string {
	switch strings.ToUpper(mode) {
	case "SSB", "USB", "LSB", "AM", "FM", "DIGITALVOICE", "PHONE":
		return "PH"
	case "CW":
		return "CW"
	default:
		return "DIG"
	}
}

// GroupDigitalMode collapses every digital mode into the single "DIG" bucket
// (FT8, FT4, RTTY, PSK… all become one mode) while leaving phone modes and CW
// untouched. Used as the optional slot normaliser when the operator prefers
// DXCC-style mode classes over per-mode slots (Settings → General).
func GroupDigitalMode(mode string) string {
	if modeClass(mode) == "DIG" {
		return "DIG"
	}
	return strings.ToUpper(mode)
}

// BandMode is a (band, mode) pair used for the NEW SLOT check.
type BandMode struct {
	Band string `json:"band"`
	Mode string `json:"mode"`
}

// WorkedEntry is one prior contact row, lean enough to ship to the UI for
// rendering a recent-contacts mini-list.
const maxWorkedEntries = 50

// operatorSuffixes are the appendages that qualify a callsign without changing
// whose it is. A bare digit (RK3DWA/3) counts too, handled separately.
var operatorSuffixes = map[string]bool{
	"P": true, "M": true, "MM": true, "AM": true, "QRP": true,
	"A": true, "B": true, "J": true, "LH": true, "R": true, "T": true,
}

// baseCall extracts the operator's OWN callsign from a portable form.
//
// The slash carries the qualifier on either side, and which side depends on
// what it is: RK3DWA/3 and RK3DWA/P append to the call, while ZA/OE8NDR and
// F/DL1ABC put a country prefix in front of it. Taking the part before the
// slash — which is what this used to do — reads ZA/OE8NDR as the station "ZA".
//
// So: drop the known qualifiers, and of what is left take the longest part. A
// prefix is short by nature (ZA, F, KH6, VP2E) and a callsign is not.
func baseCall(call string) string {
	if !strings.Contains(call, "/") {
		return call
	}
	best := ""
	for _, p := range strings.Split(call, "/") {
		if p == "" || operatorSuffixes[p] {
			continue
		}
		if len(p) == 1 && p[0] >= '0' && p[0] <= '9' {
			continue // the call-area digit
		}
		if len(p) > len(best) {
			best = p
		}
	}
	if best == "" {
		return call
	}
	return best
}

// callMatch builds the WHERE fragment that selects one station's QSOs.
//
// Exact by default. With variants on, an operator's portable forms count as the
// same station: RK3DWA, RK3DWA/3, RK3DWA/P and RK3DWA/QRP are one person, and
// someone asking "have I worked RK3DWA?" means the person, not the string.
// Typing 21 QSOs' worth of history only when you happen to add "/3" is the
// behaviour this replaces.
//
// PREFIXED FORMS MATCH TOO, and getting that wrong is what prompted this.
// Matching on the part before the slash turned ZA/OE8NDR into the station "ZA",
// so the worked-before list for one Austrian operating from Albania showed
// every OTHER visitor to Albania — two Italians and himself — and counted them
// as four contacts "with this call".
//
// Deliberately NOT a bare "starts with": LIKE 'RK3DWA%' would also drag in
// RK3DWAB, which is a different station. The '/' is what makes it the same one.
// And the variant match is only used on a base of three characters or more —
// below that a prefix could match half the log.
func callMatch(call string, variants bool) (string, []any) {
	if !variants {
		return "callsign = ?", []any{call}
	}
	base := baseCall(call)
	if len(base) < 3 {
		return "callsign = ?", []any{call}
	}
	return "(callsign = ? OR callsign LIKE ? OR callsign LIKE ? OR callsign LIKE ?)",
		[]any{
			base,               // OE8NDR
			base + "/%",        // OE8NDR/P
			"%/" + base,        // ZA/OE8NDR
			"%/" + base + "/%", // ZA/OE8NDR/P
		}
}

// WorkedBefore returns aggregated history at both callsign and DXCC level.
// dxccHint lets the caller pass a known DXCC number (e.g. from a fresh QRZ
// lookup) when the call has never been worked. If 0, the DXCC is inferred
// from the most recent prior QSO with the same callsign.
//
// matchVariants folds the portable forms of the call together — see callMatch.
func (r *Repo) WorkedBefore(ctx context.Context, callsign string, dxccHint int, matchVariants bool) (WorkedBefore, error) {
	wb := WorkedBefore{
		Callsign:      upperTrim(callsign),
		Bands:         []string{},
		Modes:         []string{},
		BandModes:     []BandMode{},
		Entries:       []QSO{},
		DXCCBands:     []string{},
		DXCCModes:     []string{},
		DXCCBandModes: []BandMode{},
	}
	if wb.Callsign == "" {
		return wb, nil
	}

	// ---- Per-callsign stats ----
	pred, predArgs := callMatch(wb.Callsign, matchVariants)
	if err := r.db.QueryRowContext(ctx,
		`SELECT COUNT(*) FROM qso WHERE `+pred, predArgs...).Scan(&wb.Count); err != nil {
		return wb, fmt.Errorf("count worked: %w", err)
	}
	if wb.Count > 0 {
		// Pull the full QSO records (same columns as the Recent QSOs list) so
		// the Worked-before grid can offer the same rich column picker.
		rows, err := r.db.QueryContext(ctx, `SELECT `+selectCols+`
            FROM qso WHERE `+pred+`
            ORDER BY qso_date DESC, id DESC
            LIMIT ?`, append(append([]any{}, predArgs...), maxWorkedEntries)...)
		if err != nil {
			return wb, fmt.Errorf("query worked: %w", err)
		}
		bandsSet := map[string]struct{}{}
		modesSet := map[string]struct{}{}
		bmSet := map[string]BandMode{}
		for rows.Next() {
			q, err := scanQSO(rows)
			if err != nil {
				rows.Close()
				return wb, fmt.Errorf("scan worked: %w", err)
			}
			wb.Entries = append(wb.Entries, q)
			if q.Band != "" {
				bandsSet[q.Band] = struct{}{}
			}
			if q.Mode != "" {
				modesSet[q.Mode] = struct{}{}
			}
			if q.Band != "" && q.Mode != "" {
				bmSet[q.Band+"|"+q.Mode] = BandMode{Band: q.Band, Mode: q.Mode}
			}
			if wb.Last.IsZero() {
				wb.Last = q.QSODate
			}
			wb.First = q.QSODate
		}
		rows.Close()

		if wb.Count > maxWorkedEntries {
			var firstStr sql.NullString
			_ = r.db.QueryRowContext(ctx,
				`SELECT MIN(qso_date) FROM qso WHERE `+pred, predArgs...).Scan(&firstStr)
			if firstStr.Valid {
				wb.First = parseTimeLoose(firstStr.String)
			}
		}
		for b := range bandsSet {
			wb.Bands = append(wb.Bands, b)
		}
		for m := range modesSet {
			wb.Modes = append(wb.Modes, m)
		}
		for _, bm := range bmSet {
			wb.BandModes = append(wb.BandModes, bm)
		}
		sortStrings(wb.Bands)
		sortStrings(wb.Modes)
	}

	// ---- Resolve DXCC ----
	dxcc := dxccHint
	if dxcc == 0 && wb.Count > 0 {
		// Take the most recent non-null DXCC from past QSOs with this call.
		var d sql.NullInt64
		_ = r.db.QueryRowContext(ctx, `
            SELECT dxcc FROM qso
            WHERE `+pred+` AND dxcc IS NOT NULL
            ORDER BY qso_date DESC LIMIT 1`, predArgs...).Scan(&d)
		if d.Valid {
			dxcc = int(d.Int64)
		}
	}
	if dxcc <= 0 {
		return wb, nil
	}
	wb.DXCC = dxcc

	// Best-effort country name: latest non-null country for this DXCC.
	var name sql.NullString
	_ = r.db.QueryRowContext(ctx, `
        SELECT country FROM qso
        WHERE dxcc = ? AND country IS NOT NULL AND country != ''
        ORDER BY qso_date DESC LIMIT 1`, dxcc).Scan(&name)
	wb.DXCCName = name.String

	// ---- Per-DXCC stats ----
	if err := r.db.QueryRowContext(ctx, `
        SELECT COUNT(*), MIN(qso_date), MAX(qso_date)
        FROM qso WHERE dxcc = ?`, dxcc).Scan(
		&wb.DXCCCount,
		&nullableTimeScan{&wb.DXCCFirst},
		&nullableTimeScan{&wb.DXCCLast},
	); err != nil {
		return wb, fmt.Errorf("count dxcc: %w", err)
	}
	if wb.DXCCCount == 0 {
		return wb, nil
	}

	if err := r.collectDistinct(ctx, &wb.DXCCBands,
		`SELECT DISTINCT band FROM qso WHERE dxcc = ? AND band IS NOT NULL AND band != ''`, dxcc); err != nil {
		return wb, err
	}
	if err := r.collectDistinct(ctx, &wb.DXCCModes,
		`SELECT DISTINCT mode FROM qso WHERE dxcc = ? AND mode IS NOT NULL AND mode != ''`, dxcc); err != nil {
		return wb, err
	}
	bmRows, err := r.db.QueryContext(ctx,
		`SELECT DISTINCT band, mode FROM qso WHERE dxcc = ?
		  AND band IS NOT NULL AND band != ''
		  AND mode IS NOT NULL AND mode != ''`, dxcc)
	if err != nil {
		return wb, fmt.Errorf("dxcc band_modes: %w", err)
	}
	for bmRows.Next() {
		var b, m string
		if err := bmRows.Scan(&b, &m); err != nil {
			bmRows.Close()
			return wb, err
		}
		wb.DXCCBandModes = append(wb.DXCCBandModes, BandMode{Band: b, Mode: m})
	}
	bmRows.Close()
	sortStrings(wb.DXCCBands)
	sortStrings(wb.DXCCModes)

	// ---- Per-(band, class) status grid ----
	// One pass over every distinct (band, mode) in the DXCC, aggregating
	// "did this call work it?" and "was anything confirmed?" via MAX.
	// Status precedence: CONFIRMED OUTRANKS WORKED — call_c > dxcc_c > call_w >
	// dxcc_w.
	//
	// It used to run call_c > call_w > dxcc_c > dxcc_w, which made a call worked
	// and not confirmed outrank an entity confirmed on the same slot. Reported
	// from a real log: YB confirmed on 20m digital showed BLUE, because that
	// operator had also worked one unconfirmed YB station there. The cell said
	// "not confirmed" about a slot that is confirmed.
	//
	// The grid answers "what do I still need on this band and mode", and for
	// that question confirmation is the axis that matters: a confirmed entity
	// needs nothing, whoever else was worked afterwards.
	// The two per-callsign columns use the SAME predicate as the callsign count
	// above, portable variants included: a cell that counts RI1FJL/1 in "worked
	// with this call" and a header that does not would be two answers to one
	// question.
	// Filter NULL/empty band+mode rows — they'd create a NULL group key
	// that Scan into *string can't handle and would error out the whole
	// WorkedBefore call, blanking the matrix in the UI.
	statusRows, err := r.db.QueryContext(ctx, `
        SELECT band, mode,
               MAX(CASE WHEN `+pred+` THEN 1 ELSE 0 END),
               MAX(CASE WHEN `+pred+`
                         AND (lotw_rcvd IN `+ConfirmedValues+` OR qsl_rcvd IN `+ConfirmedValues+` OR eqsl_rcvd IN `+ConfirmedValues+`)
                        THEN 1 ELSE 0 END),
               MAX(CASE WHEN lotw_rcvd IN `+ConfirmedValues+` OR qsl_rcvd IN `+ConfirmedValues+` OR eqsl_rcvd IN `+ConfirmedValues+`
                        THEN 1 ELSE 0 END)
        FROM qso
        WHERE dxcc = ?
          AND band IS NOT NULL AND band != ''
          AND mode IS NOT NULL AND mode != ''
        GROUP BY band, mode`, append(append(append([]any{}, predArgs...), predArgs...), dxcc)...)
	if err != nil {
		return wb, fmt.Errorf("band status: %w", err)
	}
	type cellKey struct{ band, class string }
	best := map[cellKey]int{}
	// The call's own answer per cell, independent of the ladder above.
	callByCell := map[cellKey]string{}
	for statusRows.Next() {
		var band, mode string
		var callW, callC, dxccConfirmed int
		if err := statusRows.Scan(&band, &mode, &callW, &callC, &dxccConfirmed); err != nil {
			statusRows.Close()
			return wb, fmt.Errorf("scan band status: %w", err)
		}
		code := bandStatusCode(callW == 1, callC == 1, dxccConfirmed == 1)
		keys := []cellKey{{band: band, class: modeClass(mode)}}
		// The DIGITAL row can be cycled through the individual modes in the UI —
		// FT8, then FT4, then RTTY — so the same cell is also published under the
		// raw mode name. The query already grouped by mode; only the collapse to
		// a class threw that away, and re-asking the database for it would be a
		// second scan to learn what we had just read.
		//
		// Digital only: PH and CW have nothing to cycle through, and publishing
		// "SSB" beside "PH" would just double the payload.
		if um := strings.ToUpper(mode); modeClass(mode) == "DIG" && um != "" {
			keys = append(keys, cellKey{band: band, class: um})
		}
		for _, k := range keys {
			if cur, ok := best[k]; !ok || code > cur {
				best[k] = code
			}
		}
		// Confirmed beats worked here too, and neither is ever erased by the
		// entity: this is only ever about the callsign.
		for _, k := range keys {
			switch {
			case callC == 1:
				callByCell[k] = "c"
			case callW == 1 && callByCell[k] == "":
				callByCell[k] = "w"
			}
		}
	}
	statusRows.Close()
	codeStr := bandStatusNames
	for k, code := range best {
		wb.BandStatus = append(wb.BandStatus, BandStatus{
			Band: k.band, Class: k.class, Status: codeStr[code],
			Call: callByCell[k],
		})
	}
	return wb, nil
}

// collectDistinct fills *out with the first column of every row.
func (r *Repo) collectDistinct(ctx context.Context, out *[]string, query string, args ...any) error {
	rows, err := r.db.QueryContext(ctx, query, args...)
	if err != nil {
		return fmt.Errorf("distinct: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var s string
		if err := rows.Scan(&s); err != nil {
			return err
		}
		*out = append(*out, s)
	}
	return rows.Err()
}

// nullableTimeScan adapts a *time.Time to sql.Scanner for fields that may
// be NULL in aggregate queries (MIN/MAX over an empty subset returns NULL).
type nullableTimeScan struct {
	dst *time.Time
}

func (n *nullableTimeScan) Scan(src any) error {
	if src == nil {
		return nil
	}
	switch v := src.(type) {
	case string:
		*n.dst = parseTimeLoose(v)
	case []byte:
		*n.dst = parseTimeLoose(string(v))
	}
	return nil
}

func upperTrim(s string) string {
	out := []byte(s)
	// strings.TrimSpace + ToUpper without importing strings for this micro-helper.
	start, end := 0, len(out)
	for start < end && out[start] <= ' ' {
		start++
	}
	for end > start && out[end-1] <= ' ' {
		end--
	}
	for i := start; i < end; i++ {
		if out[i] >= 'a' && out[i] <= 'z' {
			out[i] -= 32
		}
	}
	return string(out[start:end])
}

func sortStrings(s []string) {
	// Tiny insertion sort: typical worked-before slices are < 20 items.
	for i := 1; i < len(s); i++ {
		for j := i; j > 0 && s[j-1] > s[j]; j-- {
			s[j-1], s[j] = s[j], s[j-1]
		}
	}
}

// IterateAll streams every QSO in the database through fn, ordered by
// qso_date ascending so an ADIF export is chronological. Constant memory
// regardless of table size — the alternative (loading all 25k+ rows into
// a slice) wastes ~20MB for no good reason.
func (r *Repo) IterateAll(ctx context.Context, fn func(QSO) error) error {
	rows, err := r.db.QueryContext(ctx,
		`SELECT `+selectCols+` FROM qso ORDER BY qso_date ASC, id ASC`)
	if err != nil {
		return fmt.Errorf("query qso: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		q, err := scanQSO(rows)
		if err != nil {
			return err
		}
		if err := fn(q); err != nil {
			return err
		}
	}
	return rows.Err()
}

// awardCols is the column set the award engine can read — every field reachable
// from refField/inScope/confirmed in internal/award plus enrichQSOForAwards:
// the reference fields (dxcc, zones, continent, country, state, grid/vucc, iota,
// sota, pota, wwff via extras_json, and the free-text fields name/qth/address/
// comment/notes a custom award may key on), band/mode/qso_date for scoping,
// freq_hz for band recovery, and the qsl/lotw/eqsl rcvd confirmation flags.
// This drops ~125 unused columns so award computation over a remote MySQL
// backend ships a fraction of each row instead of the whole record.
//
// IMPORTANT: when a new award references a QSO field not listed here, add the
// column to this list AND populate it in scanAwardQSO below, or that award will
// silently see an empty value during stats/computation.
const awardCols = `id, callsign, qso_date, band, freq_hz, mode, ` +
	`grid, vucc_grids, country, state, cnty, cont, cqz, ituz, dxcc, iota, sota_ref, pota_ref, ` +
	`name, qth, address, comment, notes, ` +
	`qsl_rcvd, lotw_rcvd, eqsl_rcvd, extras_json, award_refs`

// IterateForAwards streams a lightweight projection of every QSO — only the
// fields award computation reads (see awardCols). All other QSO fields are left
// zero. Use IterateAll when the full record is needed. Ordered by date/id like
// IterateAll for deterministic results.
func (r *Repo) IterateForAwards(ctx context.Context, fn func(QSO) error) error {
	rows, err := r.db.QueryContext(ctx,
		`SELECT `+awardCols+` FROM qso ORDER BY qso_date ASC, id ASC`)
	if err != nil {
		return fmt.Errorf("query qso (awards): %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		q, err := scanAwardQSO(rows)
		if err != nil {
			return err
		}
		if err := fn(q); err != nil {
			return err
		}
	}
	return rows.Err()
}

// IterateForAwardsWithRef streams only the QSOs whose stored award_refs already
// mention one award code.
//
// For a change that RELABELS an award rather than re-matching it — the grid
// column switching from the reference to its description — the rows that can
// possibly change are exactly these. On a 30 000-QSO log with 2 488 French
// departments in it, that is the difference between reading the whole logbook
// and reading a twelfth of it.
//
// Matched with LIKE on the JSON key, which is exact enough: award_refs is a
// compact object written by us, so a code appears as "DDFM": and nowhere else.
func (r *Repo) IterateForAwardsWithRef(ctx context.Context, code string, fn func(QSO) error) error {
	like := "%\"" + strings.ToUpper(strings.TrimSpace(code)) + "\":%"
	rows, err := r.db.QueryContext(ctx,
		`SELECT `+awardCols+` FROM qso WHERE award_refs LIKE ? ORDER BY qso_date ASC, id ASC`, like)
	if err != nil {
		return fmt.Errorf("query qso (awards, one code): %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		q, err := scanAwardQSO(rows)
		if err != nil {
			return err
		}
		if err := fn(q); err != nil {
			return err
		}
	}
	return rows.Err()
}

// scanAwardQSO reads one row produced by awardCols into a QSO, populating only
// the award-relevant fields. Column order MUST match awardCols.
func scanAwardQSO(s scanner) (QSO, error) {
	var q QSO
	var (
		qsoDateStr                  string
		freqHz                      sql.NullInt64
		grid, vucc, country, state  sql.NullString
		cnty                        sql.NullString
		cont, iotaRef, sota, pota   sql.NullString
		dxcc, cqz, ituz             sql.NullInt64
		name, qth, address          sql.NullString
		comment, notes              sql.NullString
		qslRcvd, lotwRcvd, eqslRcvd sql.NullString
		extrasJSON                  sql.NullString
		awardRefs                   sql.NullString
	)
	if err := s.Scan(
		&q.ID, &q.Callsign, &qsoDateStr, &q.Band, &freqHz, &q.Mode,
		&grid, &vucc, &country, &state, &cnty, &cont, &cqz, &ituz, &dxcc, &iotaRef, &sota, &pota,
		&name, &qth, &address, &comment, &notes,
		&qslRcvd, &lotwRcvd, &eqslRcvd, &extrasJSON, &awardRefs,
	); err != nil {
		return QSO{}, fmt.Errorf("scan qso (awards): %w", err)
	}
	q.QSODate = parseTimeLoose(qsoDateStr)
	// award_refs rides along so a recompute can compare what it produced against
	// what is stored WITHOUT falling back to the full-record scan — the whole
	// point of this projection.
	q.AwardRefs = awardRefs.String
	if freqHz.Valid {
		v := freqHz.Int64
		q.FreqHz = &v
	}
	q.Grid = grid.String
	q.VUCCGrids = vucc.String
	q.Country = country.String
	q.State = state.String
	q.County = cnty.String
	q.Continent = cont.String
	if cqz.Valid {
		v := int(cqz.Int64)
		q.CQZ = &v
	}
	if ituz.Valid {
		v := int(ituz.Int64)
		q.ITUZ = &v
	}
	if dxcc.Valid {
		v := int(dxcc.Int64)
		q.DXCC = &v
	}
	q.IOTA = iotaRef.String
	q.SOTARef = sota.String
	q.POTARef = pota.String
	q.Name = name.String
	q.QTH = qth.String
	q.Address = address.String
	q.Comment = comment.String
	q.Notes = notes.String
	q.QSLRcvd = qslRcvd.String
	q.LOTWRcvd = lotwRcvd.String
	q.EQSLRcvd = eqslRcvd.String
	q.Extras = decodeExtras(extrasJSON.String)
	return q, nil
}

// EntitySlot bundles every (band, mode) tuple ever worked for a given
// DXCC entity name. Used by the cluster spot colouring code to decide
// NEW / NEW SLOT / WORKED in constant time after one batched query.
type EntitySlot struct {
	Country string
	Bands   map[string]struct{}            // bands worked, any mode
	Modes   map[string]struct{}            // modes worked, any band
	Slots   map[string]map[string]struct{} // band → modes worked
}

// EntitySlotMap returns slot data for every QSO, grouped by DXCC entity NUMBER.
//
// keyFor maps a QSO (its callsign + stored DXCC + stored country) to a DXCC
// entity number. Keying by NUMBER — not name — is what makes the cluster
// "new / new-band / new-slot" check robust: QRZ's "Turkey" and cty.dat's
// "Asiatic Turkey" are the same entity (390), and a logged Lord Howe Island
// QSO (stored DXCC 147) matches a VJ2L spot even though cty.dat resolves the
// logged callsign "VK2/SP9FIH" to Australia by prefix. The caller decides the
// precedence (stored DXCC → stored country → cty.dat prefix). keyFor returning
// 0 (unresolvable) skips the QSO.
//
// One DB scan regardless of input size. Cheap to call per cluster batch.
//
// normMode (nil = identity) maps each QSO's mode before it is stored as a
// Modes/Slots key — pass GroupDigitalMode to collapse all digital modes into
// one bucket. Callers must normalise their lookup mode the same way.
func (r *Repo) EntitySlotMap(ctx context.Context, keyFor func(call string, storedDXCC int, country string) int, normMode func(string) string) (map[int]*EntitySlot, error) {
	return r.EntitySlotMapPred(ctx, keyFor, normMode, "")
}

// EntitySlotMapPred is EntitySlotMap over only the QSOs matching pred — the
// confirmed-only ledger the "chase unconfirmed too" mode judges against.
func (r *Repo) EntitySlotMapPred(ctx context.Context, keyFor func(call string, storedDXCC int, country string) int, normMode func(string) string, pred string) (map[int]*EntitySlot, error) {
	where := ""
	if pred != "" {
		where = " AND " + pred
	}
	rows, err := r.db.QueryContext(ctx,
		`SELECT callsign, coalesce(dxcc,0), lower(coalesce(country,'')), lower(band), upper(mode) FROM qso
		 WHERE band IS NOT NULL AND band != ''
		   AND mode IS NOT NULL AND mode != ''`+where)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[int]*EntitySlot, 256)
	for rows.Next() {
		var call, country, band, mode string
		var storedDXCC int
		if err := rows.Scan(&call, &storedDXCC, &country, &band, &mode); err != nil {
			return nil, err
		}
		key := 0
		if keyFor != nil {
			key = keyFor(call, storedDXCC, country)
		} else {
			key = storedDXCC
		}
		if key == 0 {
			continue
		}
		if normMode != nil {
			mode = normMode(mode)
		}
		e, ok := out[key]
		if !ok {
			e = &EntitySlot{
				Country: country,
				Bands:   make(map[string]struct{}),
				Modes:   make(map[string]struct{}),
				Slots:   make(map[string]map[string]struct{}),
			}
			out[key] = e
		}
		e.Bands[band] = struct{}{}
		e.Modes[mode] = struct{}{}
		bandSlots, ok := e.Slots[band]
		if !ok {
			bandSlots = make(map[string]struct{})
			e.Slots[band] = bandSlots
		}
		bandSlots[mode] = struct{}{}
	}
	return out, rows.Err()
}

// WorkedCallsigns returns the set of every callsign ever logged (uppercased).
// One pass, used by the cluster spot colouring to flag "already worked this
// exact call" regardless of band/mode — Log4OM/RUMlogNG-style call highlight.
func (r *Repo) WorkedCallsigns(ctx context.Context) (map[string]struct{}, error) {
	return r.WorkedCallsignsPred(ctx, "")
}

// WorkedCallsignsPred restricts the callsign ledger to QSOs matching pred —
// the confirmed-prefix (WPX) chase reads its prefixes off this set.
func (r *Repo) WorkedCallsignsPred(ctx context.Context, pred string) (map[string]struct{}, error) {
	where := ""
	if pred != "" {
		where = " AND " + pred
	}
	rows, err := r.db.QueryContext(ctx,
		`SELECT DISTINCT upper(callsign) FROM qso WHERE callsign != ''`+where)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]struct{}, 1024)
	for rows.Next() {
		var c string
		if err := rows.Scan(&c); err != nil {
			return nil, err
		}
		out[c] = struct{}{}
	}
	return out, rows.Err()
}

// WorkedCountyKeys returns the set of counties already worked, keyed by the
// caller-supplied normaliser (award.USCountyKey — passed in to avoid importing
// the award package here). Only US-entity QSOs (DXCC 291/110/6) with a county
// are considered. Empty keys (unresolvable state/county) are skipped.
func (r *Repo) WorkedCountyKeys(ctx context.Context, keyFn func(state, cnty string) string) (map[string]struct{}, error) {
	return r.WorkedCountyKeysPred(ctx, keyFn, "")
}

// WorkedCountyKeysPred restricts the county ledger to QSOs matching pred.
func (r *Repo) WorkedCountyKeysPred(ctx context.Context, keyFn func(state, cnty string) string, pred string) (map[string]struct{}, error) {
	where := ""
	if pred != "" {
		where = " AND " + pred
	}
	rows, err := r.db.QueryContext(ctx,
		`SELECT DISTINCT COALESCE(state,''), COALESCE(cnty,'') FROM qso
		 WHERE dxcc IN (291,110,6) AND cnty IS NOT NULL AND cnty != ''`+where)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]struct{}, 1024)
	for rows.Next() {
		var state, cnty string
		if err := rows.Scan(&state, &cnty); err != nil {
			return nil, err
		}
		if k := keyFn(state, cnty); k != "" {
			out[k] = struct{}{}
		}
	}
	return out, rows.Err()
}

// WorkedStateKeys returns the set of US states already worked, uppercased —
// the WAS ledger the cluster and decode panels judge NEW STATE against.
func (r *Repo) WorkedStateKeys(ctx context.Context) (map[string]struct{}, error) {
	return r.WorkedStateKeysPred(ctx, "")
}

// WorkedStateKeysPred restricts the state ledger to QSOs matching pred.
func (r *Repo) WorkedStateKeysPred(ctx context.Context, pred string) (map[string]struct{}, error) {
	where := ""
	if pred != "" {
		where = " AND " + pred
	}
	rows, err := r.db.QueryContext(ctx,
		`SELECT DISTINCT UPPER(COALESCE(state,'')) FROM qso
		 WHERE dxcc IN (291,110,6) AND state IS NOT NULL AND state != ''`+where)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]struct{}, 64)
	for rows.Next() {
		var st string
		if err := rows.Scan(&st); err != nil {
			return nil, err
		}
		if st != "" {
			out[st] = struct{}{}
		}
	}
	return out, rows.Err()
}

// CountQSLViaRouting counts the QSOs whose qsl_via holds a routing method
// instead of a manager, per isRouting.
//
// The test is applied in Go rather than in SQL because it has to hold the same
// vocabulary as the import and the QSL panel — one list of accepted spellings,
// in internal/adif, not a LIKE pattern drifting apart from it here. Only the
// distinct values are examined, so the log is scanned once whatever its size.
func (r *Repo) CountQSLViaRouting(ctx context.Context, isRouting func(string) bool) (int, error) {
	rows, err := r.db.QueryContext(ctx,
		`SELECT qsl_via, COUNT(*) FROM qso
		 WHERE qsl_via IS NOT NULL AND qsl_via != ''
		   AND (qsl_sent_via IS NULL OR qsl_sent_via = '')
		 GROUP BY qsl_via`)
	if err != nil {
		return 0, err
	}
	defer rows.Close()
	n := 0
	for rows.Next() {
		var via string
		var c int
		if err := rows.Scan(&via, &c); err != nil {
			return 0, err
		}
		if isRouting(via) {
			n += c
		}
	}
	return n, rows.Err()
}

// RepairQSLViaRouting moves routing words out of qsl_via into qsl_sent_via,
// returning how many QSOs were changed.
//
// One UPDATE per distinct spelling, not per QSO: a log holds a handful of them
// ("Bureau", "E", "Direct"…), and a remote MySQL logbook must not be made to
// carry one round trip per contact for a tidy-up. Rows that already have a
// sent-via are left alone by the same condition the count uses, so running this
// twice cannot undo a later import.
func (r *Repo) RepairQSLViaRouting(ctx context.Context, normalise func(string) string) (int, error) {
	rows, err := r.db.QueryContext(ctx,
		`SELECT DISTINCT qsl_via FROM qso
		 WHERE qsl_via IS NOT NULL AND qsl_via != ''
		   AND (qsl_sent_via IS NULL OR qsl_sent_via = '')`)
	if err != nil {
		return 0, err
	}
	type move struct{ from, to string }
	var moves []move
	for rows.Next() {
		var via string
		if err := rows.Scan(&via); err != nil {
			rows.Close()
			return 0, err
		}
		if to := normalise(via); to != "" {
			moves = append(moves, move{from: via, to: to})
		}
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return 0, err
	}

	total := 0
	for _, m := range moves {
		res, err := r.db.ExecContext(ctx,
			`UPDATE qso SET qsl_sent_via = ?, qsl_via = ''
			 WHERE qsl_via = ? AND (qsl_sent_via IS NULL OR qsl_sent_via = '')`,
			m.to, m.from)
		if err != nil {
			return total, err
		}
		if n, err := res.RowsAffected(); err == nil {
			total += int(n)
		}
	}
	return total, nil
}

// CallCounties returns callsign → "STATE,County" for every US station already
// logged with a county, newest QSO winning.
//
// It exists so the cluster shows the SAME county the entry panel does. The
// entry panel gets its county from a callbook, which knows where the licensee
// actually lives; a spot carries only a callsign, so the cluster falls back to
// deriving one from the FCC licence address, which is right about 98% of the
// time. Where the log already holds a callbook's answer for that station, the
// two panels disagreeing is pure noise — this is what lets the better answer
// win in both.
//
// One DISTINCT scan when the cluster snapshot is rebuilt, then map lookups per
// spot, like the worked-county and worked-grid sets beside it.
func (r *Repo) CallCounties(ctx context.Context) (map[string]string, error) {
	rows, err := r.db.QueryContext(ctx,
		`SELECT callsign, COALESCE(state,''), COALESCE(cnty,'') FROM qso
		 WHERE dxcc IN (291,110,6) AND cnty IS NOT NULL AND cnty != ''
		 ORDER BY qso_date, time_on`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]string, 1024)
	for rows.Next() {
		var call, state, cnty string
		if err := rows.Scan(&call, &state, &cnty); err != nil {
			return nil, err
		}
		call = strings.ToUpper(strings.TrimSpace(call))
		cnty = strings.TrimSpace(cnty)
		if call == "" || cnty == "" {
			continue
		}
		// Store the ADIF "STATE,County" shape whatever the log holds, so the
		// caller has both halves without a second column.
		if !strings.Contains(cnty, ",") {
			if state = strings.TrimSpace(state); state == "" {
				continue
			}
			cnty = state + "," + cnty
		}
		out[call] = cnty
	}
	return out, rows.Err()
}

// CountyWorked reports whether one US county has already been worked.
//
// It exists so the entry panel can flag a new county without loading the whole
// worked-county set on every keystroke — the county arrives from a QRZ lookup,
// which is exactly when the operator is deciding whether the contact is worth
// chasing. The comparison goes through keyFn on both sides (rather than a SQL
// equality) because logged county spellings vary — "Los Angeles" against
// "LOS ANGELES, CA" — and the key function is what already resolves that
// everywhere else.
func (r *Repo) CountyWorked(ctx context.Context, state, cnty string, keyFn func(state, cnty string) string) (bool, error) {
	want := keyFn(state, cnty)
	if want == "" {
		return false, nil
	}
	// Narrowed to the one state: a few dozen rows, not the whole logbook.
	rows, err := r.db.QueryContext(ctx,
		`SELECT DISTINCT COALESCE(state,''), COALESCE(cnty,'') FROM qso
		 WHERE dxcc IN (291,110,6) AND cnty IS NOT NULL AND cnty != ''
		   AND upper(COALESCE(state,'')) = upper(?)`, strings.TrimSpace(state))
	if err != nil {
		return false, err
	}
	defer rows.Close()
	for rows.Next() {
		var st, cn string
		if err := rows.Scan(&st, &cn); err != nil {
			return false, err
		}
		if keyFn(st, cn) == want {
			return true, nil
		}
	}
	return false, rows.Err()
}

// WorkedCallBandModeKeys returns the set of every worked "CALL|BAND|MODE" key
// (all upper-cased), loaded in one pass. It backs the in-memory worked-index the
// alert engine checks per cluster spot — a DB query per spot cannot keep up with
// an FT8 skimmer firehose (and hammers a remote MySQL).
func (r *Repo) WorkedCallBandModeKeys(ctx context.Context) (map[string]struct{}, error) {
	rows, err := r.db.QueryContext(ctx,
		`SELECT upper(callsign), upper(band), upper(mode) FROM qso WHERE callsign != ''`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]struct{}, 4096)
	for rows.Next() {
		var c, b, m string
		if err := rows.Scan(&c, &b, &m); err != nil {
			return nil, err
		}
		out[c+"|"+b+"|"+m] = struct{}{}
	}
	return out, rows.Err()
}

// WorkedCallSlotKeys backs the cluster "already worked only on the same slot"
// option. It returns each worked slot keyed BOTH as "CALL|band" (mode-agnostic,
// for a spot whose mode couldn't be inferred) and "CALL|band|MODE" with the mode
// run through normMode — so when digital grouping is on, FT8/FT4/RTTY fold into
// one "DIG" and a call worked on 20m FT8 also matches a 20m FT4 spot. Call is
// upper-cased and band lower-cased to match the spot keys ClusterSpotStatuses
// builds. normMode may be nil (no grouping).
func (r *Repo) WorkedCallSlotKeys(ctx context.Context, normMode func(string) string) (map[string]struct{}, error) {
	rows, err := r.db.QueryContext(ctx,
		`SELECT upper(callsign), lower(band), upper(mode) FROM qso
		 WHERE callsign != '' AND band IS NOT NULL AND band != '' AND mode IS NOT NULL AND mode != ''`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]struct{}, 4096)
	for rows.Next() {
		var c, b, m string
		if err := rows.Scan(&c, &b, &m); err != nil {
			return nil, err
		}
		if normMode != nil {
			m = normMode(m)
		}
		out[c+"|"+b] = struct{}{}
		out[c+"|"+b+"|"+m] = struct{}{}
	}
	return out, rows.Err()
}

// WorkedPOTARefs returns the set of POTA park references already worked
// (upper-cased). A QSO's pota_ref may hold several comma-separated parks
// (an n-fer); each is added separately.
func (r *Repo) WorkedPOTARefs(ctx context.Context) (map[string]struct{}, error) {
	rows, err := r.db.QueryContext(ctx,
		`SELECT DISTINCT pota_ref FROM qso WHERE pota_ref IS NOT NULL AND pota_ref != ''`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]struct{}, 256)
	for rows.Next() {
		var ref string
		if err := rows.Scan(&ref); err != nil {
			return nil, err
		}
		for _, p := range strings.Split(ref, ",") {
			if p = strings.ToUpper(strings.TrimSpace(p)); p != "" {
				out[p] = struct{}{}
			}
		}
	}
	return out, rows.Err()
}

// Count returns the total number of QSOs in the database.
func (r *Repo) Count(ctx context.Context) (int64, error) {
	var n int64
	err := r.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM qso`).Scan(&n)
	return n, err
}

// LastQSOTime returns the start time of the most recently LOGGED QSO for an
// operator (highest id wins) — used to seed the live "on air" state at launch so an
// operator who just worked someone before (re)starting OpsLog shows online right
// away instead of waiting for their next QSO. Empty operator matches every QSO.
func (r *Repo) LastQSOTime(ctx context.Context, operator string) (time.Time, bool) {
	// ONE indexed row, not 400 filtered in Go: this runs every ~5 s (the ON-AIR
	// badge) and ~15 s (live-status publish), so pulling 400 rows each time over a
	// remote MySQL hammered the connection pool and, on a busy multi-op event, was a
	// big part of "after a while nothing updates". Match the operator the same
	// (case/space-insensitive) way, newest first.
	opFilter := strings.ToUpper(strings.TrimSpace(operator))
	var dateStr sql.NullString
	err := r.db.QueryRowContext(ctx,
		`SELECT qso_date FROM qso WHERE UPPER(TRIM(operator)) = ? AND qso_date IS NOT NULL AND qso_date <> '' ORDER BY id DESC LIMIT 1`,
		opFilter).Scan(&dateStr)
	if err != nil {
		return time.Time{}, false // ErrNoRows or a real error — no known last QSO
	}
	if t := parseTimeLoose(dateStr.String).UTC(); !t.IsZero() {
		return t, true
	}
	return time.Time{}, false
}

// RecentRateBreakdown counts, in ONE pass over the most recent rows, QSOs whose
// start time falls within each trailing window from `now` — for a specific operator
// (their own rate, `op`) AND for ALL operators combined (the team/station rate,
// `all`). The header rate meter shows both. It scans only recently inserted rows
// (ORDER BY id DESC LIMIT), since any QSO in the last hour was inserted recently, so
// it stays cheap on a large log. qso_date is parsed with parseTimeLoose (backend-
// format agnostic).
func (r *Repo) RecentRateBreakdown(ctx context.Context, now time.Time, operator string, windows ...time.Duration) (op []int, all []int, err error) {
	op = make([]int, len(windows))
	all = make([]int, len(windows))
	// 2000 rows covers a full hour even in a busy multi-op run.
	rows, err := r.db.QueryContext(ctx, `SELECT operator, qso_date FROM qso ORDER BY id DESC LIMIT 2000`)
	if err != nil {
		return op, all, err
	}
	defer rows.Close()
	now = now.UTC()
	opFilter := strings.ToUpper(strings.TrimSpace(operator))
	for rows.Next() {
		var oper, dateStr sql.NullString
		if err := rows.Scan(&oper, &dateStr); err != nil {
			return op, all, err
		}
		t := parseTimeLoose(dateStr.String).UTC()
		if t.IsZero() || t.After(now) {
			continue
		}
		mine := strings.ToUpper(strings.TrimSpace(oper.String)) == opFilter
		age := now.Sub(t)
		for i, w := range windows {
			if age <= w {
				all[i]++
				if mine {
					op[i]++
				}
			}
		}
	}
	return op, all, rows.Err()
}

// ExistingDedupeKeys returns a set of every QSO key currently in the DB,
// used by the ADIF importer to skip records that would re-create the
// same contact. The key is callsign|YYYY-MM-DDTHH:MM|band|mode — minute
// precision so two loggers that wrote a few seconds apart still match.
//
// On a 25k-row table this returns ~25k strings (~2MB RAM) in one pass —
// far cheaper than N exists-queries during the import loop.
func (r *Repo) ExistingDedupeKeys(ctx context.Context) (map[string]struct{}, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT callsign, substr(qso_date, 1, 16), band, mode
		FROM qso`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]struct{}, 1024)
	for rows.Next() {
		var call, when, band, mode string
		if err := rows.Scan(&call, &when, &band, &mode); err != nil {
			return nil, err
		}
		out[DedupeKey(call, when, band, mode)] = struct{}{}
	}
	return out, rows.Err()
}

// DedupeKey is the canonical dedupe identity for a QSO. Exposed so the
// importer can compute the key from in-flight records and check against
// the same map ExistingDedupeKeys returns.
func DedupeKey(callsign, qsoDateMinute, band, mode string) string {
	return strings.ToUpper(callsign) + "|" + qsoDateMinute + "|" + strings.ToLower(band) + "|" + strings.ToUpper(mode)
}

// StationCallsigns lists the distinct station callsigns the logbook was worked
// under, upper-cased and without the blanks.
//
// Used to decide whether a downloaded confirmation belongs to THIS log at all:
// one LoTW account can hold several stations (a home call, a portable, an
// expedition), and a confirmation for a station this logbook has never used is
// somebody else's log — here, another profile's.
func (r *Repo) StationCallsigns(ctx context.Context) (map[string]bool, error) {
	rows, err := r.db.QueryContext(ctx,
		`SELECT DISTINCT COALESCE(station_callsign,'') FROM qso`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := map[string]bool{}
	for rows.Next() {
		var c string
		if err := rows.Scan(&c); err != nil {
			return nil, err
		}
		if c = strings.ToUpper(strings.TrimSpace(c)); c != "" {
			out[c] = true
		}
	}
	return out, rows.Err()
}

// DedupeKeyIDs returns a map of dedupe key → QSO id, for matching downloaded
// confirmations back to local QSOs.
func (r *Repo) DedupeKeyIDs(ctx context.Context) (map[string]int64, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT id, callsign, substr(qso_date, 1, 16), band, mode
		FROM qso`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]int64, 1024)
	for rows.Next() {
		var id int64
		var call, when, band, mode string
		if err := rows.Scan(&id, &call, &when, &band, &mode); err != nil {
			return nil, err
		}
		out[DedupeKey(call, when, band, mode)] = id
	}
	return out, rows.Err()
}

// DedupeClassKey is DedupeKey with the mode collapsed to its CLASS (Phone / CW /
// Digital).
//
// For matching a downloaded confirmation whose mode is not the one you logged.
// LoTW does not hand back the submode it was given: a contact uploaded as FT4,
// FT8 or anything else digital comes back as the mode GROUP, "DATA". Matched on
// the exact string that is simply never equal, and the operator is told their
// own QSO is not in their log — which is how a confirmed contact goes unrecorded.
func DedupeClassKey(callsign, qsoDateMinute, band, mode string) string {
	return strings.ToUpper(callsign) + "|" + qsoDateMinute + "|" + strings.ToLower(band) + "|" + modeClass(mode)
}

// DedupeClassKeyIDs is DedupeKeyIDs at mode-CLASS granularity, for the second
// pass when the exact key misses.
//
// A key shared by more than one QSO maps to 0 — AMBIGUOUS, not "pick one". Two
// contacts with the same station, on the same band, in the same minute, in two
// digital modes is barely physical; but if it ever happens, stamping the
// confirmation on whichever row the map happened to keep would be a silent
// error in someone's award credit. Better to report it unmatched.
func (r *Repo) DedupeClassKeyIDs(ctx context.Context) (map[string]int64, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT id, callsign, substr(qso_date, 1, 16), band, mode
		FROM qso`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]int64, 1024)
	for rows.Next() {
		var id int64
		var call, when, band, mode string
		if err := rows.Scan(&id, &call, &when, &band, &mode); err != nil {
			return nil, err
		}
		k := DedupeClassKey(call, when, band, mode)
		if prev, seen := out[k]; seen && prev != id {
			out[k] = 0
			continue
		}
		out[k] = id
	}
	return out, rows.Err()
}

// matchRef is one local QSO's time + id, for time-window confirmation matching.
type matchRef struct {
	when time.Time
	id   int64
}

// MatchIndex matches a downloaded confirmation to a local QSO within a TIME
// WINDOW (not an exact minute): QSL confirmations carry the OTHER station's
// logged time, which drifts a minute or two from ours. The MODE must match, with
// ONE equivalence: the phone modes SSB/USB/LSB are interchangeable (a station may
// confirm USB where we logged SSB). Every other mode is exact (FT8 only matches
// FT8, FT4 only FT4, CW only CW…). Built in one table scan.
type MatchIndex struct {
	byMode map[string][]matchRef // call|band|canonMode → refs
	byBand map[string][]matchRef // call|band → refs (mode-blind fallback)
}

// canonMode folds the phone sidebands into a single "SSB" bucket; every other
// mode is returned uppercased and unchanged (exact-match).
func canonMode(mode string) string {
	m := strings.ToUpper(strings.TrimSpace(mode))
	switch m {
	case "SSB", "USB", "LSB":
		return "SSB"
	}
	return m
}

func matchKeyMode(call, band, mode string) string {
	return strings.ToUpper(call) + "|" + strings.ToLower(band) + "|" + canonMode(mode)
}

// parseQSODate reads the stored qso_date (ISO "2006-01-02T15:04[:05…]") to minute
// precision as UTC. Zero time on failure (never matched).
func parseQSODate(s string) time.Time {
	if len(s) >= 16 {
		if t, err := time.Parse("2006-01-02T15:04", s[:16]); err == nil {
			return t
		}
	}
	return time.Time{}
}

// BuildMatchIndex scans the log once and indexes every QSO by call+band(+mode)
// with its time, for windowed confirmation matching. When ownerCall is non-empty
// the index is SCOPED to QSOs whose station_callsign is that call (or blank —
// legacy QSOs that predate station-callsign stamping), so a confirmation download
// for one of the operator's calls (e.g. F4BPO) never touches QSOs logged under
// another (e.g. TM2Q).
func (r *Repo) BuildMatchIndex(ctx context.Context, ownerCall string) (*MatchIndex, error) {
	idx := &MatchIndex{byMode: map[string][]matchRef{}, byBand: map[string][]matchRef{}}
	query := `SELECT id, callsign, qso_date, band, mode FROM qso`
	var args []any
	if oc := strings.ToUpper(strings.TrimSpace(ownerCall)); oc != "" {
		query += ` WHERE UPPER(TRIM(COALESCE(station_callsign,''))) IN ('', ?)`
		args = append(args, oc)
	}
	rows, err := r.db.QueryContext(ctx, query, args...)
	if err != nil {
		return idx, err
	}
	defer rows.Close()
	for rows.Next() {
		var id int64
		var call, when, band, mode string
		if err := rows.Scan(&id, &call, &when, &band, &mode); err != nil {
			return idx, err
		}
		idx.add(call, band, mode, parseQSODate(when), id)
	}
	return idx, rows.Err()
}

// add inserts a QSO into the index (also used to register a freshly-inserted
// "add not-found" QSO so later confirmations in the same batch can match it).
func (idx *MatchIndex) add(call, band, mode string, when time.Time, id int64) {
	mk := matchKeyMode(call, band, mode)
	idx.byMode[mk] = append(idx.byMode[mk], matchRef{when: when, id: id})
	if idx.byBand == nil { // tests build the index literally, without byBand
		idx.byBand = map[string][]matchRef{}
	}
	bk := strings.ToUpper(call) + "|" + strings.ToLower(band)
	idx.byBand[bk] = append(idx.byBand[bk], matchRef{when: when, id: id})
}

// Add registers a QSO in the index (exported wrapper for callers that inserted a
// not-found confirmation).
func (idx *MatchIndex) Add(call, band, mode string, when time.Time, id int64) {
	idx.add(call, band, mode, when, id)
}

// Match returns the id of the local QSO closest in time to `when` (within
// `window`) for the given call/band/mode. The mode must match (phone sidebands
// folded together by canonMode). Ok is false when nothing is within the window.
func (idx *MatchIndex) Match(call, band, mode string, when time.Time, window time.Duration) (int64, bool) {
	return closestRef(idx.byMode[matchKeyMode(call, band, mode)], when, window)
}

// MatchBand matches ignoring the mode — for confirmations whose source doesn't
// carry one (Club Log reports "false" for modes it can't infer).
func (idx *MatchIndex) MatchBand(call, band string, when time.Time, window time.Duration) (int64, bool) {
	return closestRef(idx.byBand[strings.ToUpper(call)+"|"+strings.ToLower(band)], when, window)
}

func closestRef(refs []matchRef, when time.Time, window time.Duration) (int64, bool) {
	var best int64
	bestD := window + time.Second
	found := false
	for _, rf := range refs {
		if rf.when.IsZero() {
			continue
		}
		d := when.Sub(rf.when)
		if d < 0 {
			d = -d
		}
		if d <= window && d < bestD {
			bestD, best, found = d, rf.id, true
		}
	}
	return best, found
}

// ConfirmedSets captures which DXCC / band / slot combinations are already
// confirmed (by any QSL system), so a freshly-downloaded confirmation can be
// flagged as a NEW DXCC / NEW BAND / NEW SLOT.
// ConfirmedSets captures confirmed combinations for the QSL Manager's NEW flags.
// Modes are grouped into CLASSES (Phone/CW/Digital) — a digital confirmation is a
// "new mode/slot" only if no digital mode was confirmed there before (RTTY and FT8
// are the same DIGI class). Raw-mode granularity lives only in the cluster/matrix.
type ConfirmedSets struct {
	DXCC map[int]bool    // dxcc entity confirmed
	Band map[string]bool // "dxcc|band"
	Mode map[string]bool // "dxcc|class"
	Slot map[string]bool // "dxcc|band|class"
}

// Key builders for ConfirmedSets. Band is mode-agnostic; Mode/Slot use the class.
func BandKey(dxcc int, band string) string { return fmt.Sprintf("%d|%s", dxcc, strings.ToLower(band)) }
func ModeClassKey(dxcc int, mode string) string {
	return fmt.Sprintf("%d|%s", dxcc, modeClass(mode))
}
func SlotClassKey(dxcc int, band, mode string) string {
	return fmt.Sprintf("%d|%s|%s", dxcc, strings.ToLower(band), modeClass(mode))
}

// SlotKey is the RAW-mode slot key, kept for the cluster/matrix new-slot flag.
func SlotKey(dxcc int, band, mode string) string {
	return fmt.Sprintf("%d|%s|%s", dxcc, strings.ToLower(band), strings.ToUpper(mode))
}

// SlotStats is the worked/confirmed tally for the QSL Manager, counting slots by
// mode CLASS (Phone / CW / Digital) — NOT raw mode. Raw-mode granularity (RTTY ≠
// FT8) is kept only for the cluster/matrix "new slot" flag; the totals here match
// how slots are conventionally counted (a band in a class, not in each digital
// sub-mode).
type SlotStats struct {
	SlotsWorked    int `json:"slots_worked"`    // distinct DXCC × band × class, all QSOs
	SlotsConfirmed int `json:"slots_confirmed"` // distinct DXCC × band × class, confirmed
	DXCCWorked     int `json:"dxcc_worked"`     // distinct DXCC entities worked
	DXCCConfirmed  int `json:"dxcc_confirmed"`  // distinct DXCC entities confirmed
	// Per-class slot breakdown (Phone / CW / Digital) so the numbers are checkable.
	PHWorked     int `json:"ph_worked"`
	PHConfirmed  int `json:"ph_confirmed"`
	CWWorked     int `json:"cw_worked"`
	CWConfirmed  int `json:"cw_confirmed"`
	DIGWorked    int `json:"dig_worked"`
	DIGConfirmed int `json:"dig_confirmed"`
}

// ConfirmedValues is what a QSL-received field holds when the contact IS
// confirmed, as an SQL list.
//
// Y AND V. ADIF's QSL_Rcvd enumeration has both: Y is "received", V is
// "verified" — and that is what a LoTW download writes for a confirmation the
// ARRL has validated. Testing only = 'Y' therefore misses exactly the
// confirmations an operator cares most about.
//
// This was visible on screen: the Awards panel showed Morocco validated on five
// bands while the band/mode matrix beside it showed the entity as merely worked,
// because the award engine's isYes accepts "Y" or "V" and every SQL query here
// compared against 'Y' alone. One definition of confirmed, in one place, is the
// only way those two agree.
const ConfirmedValues = "('Y','V')"

// ConfirmSourcesPredicate builds the SQL that says "this QSO is confirmed",
// from the operator's chosen sources — the same choice the award engine's
// fixed three (LoTW, card, eQSL) used to hard-code. QRZ.com's confirmation is
// its download status; HRDLog has no confirmation field in ADIF at all, which
// is why it cannot be offered. Empty input falls back to the classic three.
func ConfirmSourcesPredicate(sources []string) string {
	cols := map[string]string{
		"lotw": "lotw_rcvd",
		"card": "qsl_rcvd",
		"eqsl": "eqsl_rcvd",
		"qrz":  "qrzcom_qso_download_status",
	}
	var parts []string
	for _, src := range sources {
		if col, ok := cols[strings.ToLower(strings.TrimSpace(src))]; ok {
			parts = append(parts, col+" IN "+ConfirmedValues)
		}
	}
	if len(parts) == 0 {
		return "(lotw_rcvd IN " + ConfirmedValues + " OR qsl_rcvd IN " + ConfirmedValues + " OR eqsl_rcvd IN " + ConfirmedValues + ")"
	}
	return "(" + strings.Join(parts, " OR ") + ")"
}

// GetSlotStats computes the worked/confirmed slot and DXCC tallies in one pass.
// "Confirmed" = LoTW or paper QSL received (the award-valid sources).
func (r *Repo) GetSlotStats(ctx context.Context) (SlotStats, error) {
	rows, err := r.db.QueryContext(ctx, `
		SELECT COALESCE(dxcc,0), LOWER(COALESCE(band,'')), UPPER(COALESCE(mode,'')),
		       CASE WHEN lotw_rcvd IN `+ConfirmedValues+` OR qsl_rcvd IN `+ConfirmedValues+` THEN 1 ELSE 0 END
		FROM qso`)
	if err != nil {
		return SlotStats{}, err
	}
	defer rows.Close()
	dxccW, slotW := map[int]bool{}, map[string]bool{}
	dxccC, slotC := map[int]bool{}, map[string]bool{}
	// Per-class distinct slots (worked "w" / confirmed "c").
	clsW := map[string]map[string]bool{"PH": {}, "CW": {}, "DIG": {}}
	clsC := map[string]map[string]bool{"PH": {}, "CW": {}, "DIG": {}}
	for rows.Next() {
		var dxcc, conf int
		var band, mode string
		if err := rows.Scan(&dxcc, &band, &mode, &conf); err != nil {
			return SlotStats{}, err
		}
		if dxcc == 0 {
			continue
		}
		dxccW[dxcc] = true
		if conf == 1 {
			dxccC[dxcc] = true
		}
		if band == "" {
			continue // no band → counts for DXCC but not for a slot
		}
		key := SlotClassKey(dxcc, band, mode)
		cl := modeClass(mode)
		slotW[key] = true
		clsW[cl][key] = true
		if conf == 1 {
			slotC[key] = true
			clsC[cl][key] = true
		}
	}
	return SlotStats{
		SlotsWorked: len(slotW), SlotsConfirmed: len(slotC),
		DXCCWorked: len(dxccW), DXCCConfirmed: len(dxccC),
		PHWorked: len(clsW["PH"]), PHConfirmed: len(clsC["PH"]),
		CWWorked: len(clsW["CW"]), CWConfirmed: len(clsC["CW"]),
		DIGWorked: len(clsW["DIG"]), DIGConfirmed: len(clsC["DIG"]),
	}, rows.Err()
}

// confirmedCols whitelists the received-status columns ConfirmedSlots may
// OR together (guards the dynamic SQL).
var confirmedCols = map[string]bool{
	"lotw_rcvd":                   true,
	"qsl_rcvd":                    true,
	"eqsl_rcvd":                   true,
	"qrzcom_qso_download_status":  true,
	"clublog_qso_download_status": true,
}

// ConfirmedSlots returns the set of confirmed DXCC/band/slot combos, counting
// only the given received-status columns as "confirmed". This lets the caller
// scope award-relevant confirmations per service — e.g. LoTW download uses
// {lotw_rcvd, qsl_rcvd} (the award-valid sources), QRZ uses
// {qrzcom_qso_download_status}.
func (r *Repo) ConfirmedSlots(ctx context.Context, cols []string) (ConfirmedSets, error) {
	sets := ConfirmedSets{DXCC: map[int]bool{}, Band: map[string]bool{}, Mode: map[string]bool{}, Slot: map[string]bool{}}
	var conds []string
	for _, c := range cols {
		if confirmedCols[c] {
			conds = append(conds, c+" = 'Y'")
		}
	}
	if len(conds) == 0 {
		return sets, nil
	}
	rows, err := r.db.QueryContext(ctx, `
		SELECT COALESCE(dxcc,0), LOWER(COALESCE(band,'')), UPPER(COALESCE(mode,''))
		FROM qso
		WHERE `+strings.Join(conds, " OR "))
	if err != nil {
		return sets, err
	}
	defer rows.Close()
	for rows.Next() {
		var dxcc int
		var band, mode string
		if err := rows.Scan(&dxcc, &band, &mode); err != nil {
			return sets, err
		}
		if dxcc == 0 {
			continue
		}
		sets.DXCC[dxcc] = true
		sets.Band[BandKey(dxcc, band)] = true
		sets.Mode[ModeClassKey(dxcc, mode)] = true
		sets.Slot[SlotClassKey(dxcc, band, mode)] = true
	}
	return sets, rows.Err()
}

// MarkQRZConfirmed stamps QRZCOM_QSO_DOWNLOAD_STATUS=Y and the date on a QSO
// confirmed via a QRZ.com download. date is an ADIF YYYYMMDD string.
func (r *Repo) MarkQRZConfirmed(ctx context.Context, id int64, date string) error {
	_, err := r.db.ExecContext(ctx,
		`UPDATE qso SET qrzcom_qso_download_status = 'Y', qrzcom_qso_download_date = ?,
		   updated_at = ? WHERE id = ?`,
		date, db.NowISO(), id)
	if err != nil {
		return fmt.Errorf("mark qrz confirmed %d: %w", id, err)
	}
	return nil
}

// MarkClublogConfirmed stamps CLUBLOG_QSO_DOWNLOAD_STATUS=Y and the date on a
// QSO Club Log reports as matched. date is an ADIF YYYYMMDD string.
func (r *Repo) MarkClublogConfirmed(ctx context.Context, id int64, date string) error {
	_, err := r.db.ExecContext(ctx,
		`UPDATE qso SET clublog_qso_download_status = 'Y', clublog_qso_download_date = ?,
		   updated_at = ? WHERE id = ?`,
		date, db.NowISO(), id)
	if err != nil {
		return fmt.Errorf("mark clublog confirmed %d: %w", id, err)
	}
	return nil
}

// ClearQRZConfirmed takes back a QRZ confirmation.
//
// Needed because OpsLog set some wrongly: it read qrzcom_qso_download_status,
// which QRZ puts on everything it hands back, as QRZ's confirmation. Those
// QSOs count towards award slots, so leaving them green until the operator
// notices is not an option — and nothing else in the app would ever undo them.
func (r *Repo) ClearQRZConfirmed(ctx context.Context, id int64) error {
	_, err := r.db.ExecContext(ctx,
		`UPDATE qso SET qrzcom_qso_download_status = 'N', qrzcom_qso_download_date = NULL,
		   updated_at = ? WHERE id = ?`,
		db.NowISO(), id)
	if err != nil {
		return fmt.Errorf("clear qrz confirmed %d: %w", id, err)
	}
	return nil
}

// MarkEQSLConfirmed stamps EQSL_QSL_RCVD=Y and the received date on a QSO after
// an eQSL Inbox download. date is an ADIF YYYYMMDD string.
func (r *Repo) MarkEQSLConfirmed(ctx context.Context, id int64, date string) error {
	_, err := r.db.ExecContext(ctx,
		`UPDATE qso SET eqsl_rcvd = 'Y', eqsl_rcvd_date = ?,
		   updated_at = ? WHERE id = ?`,
		date, db.NowISO(), id)
	if err != nil {
		return fmt.Errorf("mark eqsl confirmed %d: %w", id, err)
	}
	return nil
}

// MarkLoTWConfirmed stamps LOTW_QSL_RCVD=Y and the received date on a QSO
// after a LoTW confirmation download. date is an ADIF YYYYMMDD string.
func (r *Repo) MarkLoTWConfirmed(ctx context.Context, id int64, date string) error {
	_, err := r.db.ExecContext(ctx,
		`UPDATE qso SET lotw_rcvd = 'Y', lotw_rcvd_date = ?,
		   updated_at = ? WHERE id = ?`,
		date, db.NowISO(), id)
	if err != nil {
		return fmt.Errorf("mark lotw confirmed %d: %w", id, err)
	}
	return nil
}

// scanner is what both *sql.Row and *sql.Rows satisfy for our needs.
type scanner interface {
	Scan(dest ...any) error
}

// scanQSO reads one row produced by selectCols into a QSO.
// Long but flat — kept in field declaration order to make audits easy.
func scanQSO(s scanner) (QSO, error) {
	var q QSO
	var (
		qsoDateStr                             string
		qsoDateOffStr                          sql.NullString
		bandRx, submode                        sql.NullString
		freqHz, freqRX                         sql.NullInt64
		rstS, rstR                             sql.NullString
		name, qth, addr, email, web            sql.NullString
		grid, gridExt, vucc                    sql.NullString
		country, state, cnty                   sql.NullString
		dxcc, cqz, ituz                        sql.NullInt64
		cont, iota, sota, pota                 sql.NullString
		age                                    sql.NullInt64
		lat, lon                               sql.NullFloat64
		rig, ant                               sql.NullString
		qslSent, qslRcvd                       sql.NullString
		qslSentDate, qslRcvdDate               sql.NullString
		qslVia, qslSentVia, qslRcvdVia         sql.NullString
		qslMsg, qslMsgRcvd                     sql.NullString
		lotwSent, lotwRcvd                     sql.NullString
		lotwSentDate, lotwRcvdDate             sql.NullString
		eqslSent, eqslRcvd                     sql.NullString
		eqslSentDate, eqslRcvdDate             sql.NullString
		clublogDate, clublogStatus             sql.NullString
		hrdlogDate, hrdlogStatus               sql.NullString
		qrzcomDate, qrzcomStatus               sql.NullString
		qrzcomDlDate, qrzcomDlStatus           sql.NullString
		clublogDlDate, clublogDlStatus         sql.NullString
		contestID                              sql.NullString
		srx, stx                               sql.NullInt64
		srxStr, stxStr                         sql.NullString
		checkField, precedence, arrlSect       sql.NullString
		propMode, satName, satMode             sql.NullString
		antAz, antEl                           sql.NullFloat64
		antPath                                sql.NullString
		stCall, op, myGrid, myGridExt          sql.NullString
		myCountry, myState, myCnty, myIOTA     sql.NullString
		mySOTA, myPOTA                         sql.NullString
		myDXCC, myCQZ, myITUZ                  sql.NullInt64
		myLat, myLon                           sql.NullFloat64
		myStreet, myCity, myPostal             sql.NullString
		myRig, myAntenna                       sql.NullString
		txp                                    sql.NullFloat64
		comment, notes                         sql.NullString
		sig, sigInfo, mySig, mySigInfo         sql.NullString
		wwffRef, myWWFFRef                     sql.NullString
		distance, rxPwr, aIndex, kIndex, sfi   sql.NullFloat64
		skcc, fists, tenTen                    sql.NullString
		contactedOp, eqCall, pfx, myName       sql.NullString
		class, darcDOK, myDarcDOK, region      sql.NullString
		silentKey, swl, qsoComplete, qsoRandom sql.NullString
		creditGranted, creditSubmitted         sql.NullString
		myARRLSect, myVUCCGrids                sql.NullString
		extrasJSON                             sql.NullString
		awardRefs                              sql.NullString
		syncUID                                sql.NullString
		createdStr, updatedStr                 string
	)
	if err := s.Scan(
		&q.ID, &q.Callsign, &qsoDateStr, &qsoDateOffStr, &q.Band, &bandRx, &q.Mode, &submode, &freqHz, &freqRX,
		&rstS, &rstR,
		&name, &qth, &addr, &email, &web,
		&grid, &gridExt, &vucc,
		&country, &state, &cnty, &dxcc, &cont, &cqz, &ituz, &iota, &sota, &pota,
		&age, &lat, &lon, &rig, &ant,
		&qslSent, &qslRcvd, &qslSentDate, &qslRcvdDate, &qslVia, &qslSentVia, &qslRcvdVia, &qslMsg, &qslMsgRcvd,
		&lotwSent, &lotwRcvd, &lotwSentDate, &lotwRcvdDate,
		&eqslSent, &eqslRcvd, &eqslSentDate, &eqslRcvdDate,
		&clublogDate, &clublogStatus,
		&hrdlogDate, &hrdlogStatus,
		&qrzcomDate, &qrzcomStatus,
		&qrzcomDlDate, &qrzcomDlStatus,
		&clublogDlDate, &clublogDlStatus,
		&contestID, &srx, &stx, &srxStr, &stxStr, &checkField, &precedence, &arrlSect,
		&propMode, &satName, &satMode, &antAz, &antEl, &antPath,
		&stCall, &op, &myGrid, &myGridExt, &myCountry, &myState, &myCnty, &myIOTA,
		&mySOTA, &myPOTA, &myDXCC, &myCQZ, &myITUZ, &myLat, &myLon,
		&myStreet, &myCity, &myPostal, &myRig, &myAntenna,
		&txp, &comment, &notes,
		&sig, &sigInfo, &mySig, &mySigInfo, &wwffRef, &myWWFFRef,
		&distance, &rxPwr, &aIndex, &kIndex, &sfi,
		&skcc, &fists, &tenTen, &contactedOp, &eqCall, &pfx, &myName, &class,
		&darcDOK, &myDarcDOK, &region, &silentKey, &swl, &qsoComplete, &qsoRandom,
		&creditGranted, &creditSubmitted, &myARRLSect, &myVUCCGrids,
		&extrasJSON, &awardRefs, &syncUID, &createdStr, &updatedStr,
	); err != nil {
		return QSO{}, fmt.Errorf("scan qso: %w", err)
	}
	q.QSODate = parseTimeLoose(qsoDateStr)
	q.QSODateOff = parseTimeLoose(qsoDateOffStr.String)
	q.CreatedAt = parseTimeLoose(createdStr)
	q.UpdatedAt = parseTimeLoose(updatedStr)
	q.BandRX = bandRx.String
	q.Submode = submode.String
	if freqHz.Valid {
		v := freqHz.Int64
		q.FreqHz = &v
	}
	if freqRX.Valid {
		v := freqRX.Int64
		q.FreqRXHz = &v
	}
	q.RSTSent = rstS.String
	q.RSTRcvd = rstR.String
	q.Name = name.String
	q.QTH = qth.String
	q.Address = addr.String
	q.Email = email.String
	q.Web = web.String
	q.Grid = grid.String
	q.GridExt = gridExt.String
	q.VUCCGrids = vucc.String
	q.Country = country.String
	q.State = state.String
	q.County = cnty.String
	if dxcc.Valid {
		v := int(dxcc.Int64)
		q.DXCC = &v
	}
	q.Continent = cont.String
	if cqz.Valid {
		v := int(cqz.Int64)
		q.CQZ = &v
	}
	if ituz.Valid {
		v := int(ituz.Int64)
		q.ITUZ = &v
	}
	q.IOTA = iota.String
	q.SOTARef = sota.String
	q.POTARef = pota.String
	if age.Valid {
		v := int(age.Int64)
		q.Age = &v
	}
	if lat.Valid {
		v := lat.Float64
		q.Lat = &v
	}
	if lon.Valid {
		v := lon.Float64
		q.Lon = &v
	}
	q.Rig = rig.String
	q.Ant = ant.String
	q.QSLSent = qslSent.String
	q.QSLRcvd = qslRcvd.String
	q.QSLSentDate = qslSentDate.String
	q.QSLRcvdDate = qslRcvdDate.String
	q.QSLVia = qslVia.String
	q.QSLSentVia = qslSentVia.String
	q.QSLRcvdVia = qslRcvdVia.String
	q.QSLMsg = qslMsg.String
	q.QSLMsgRcvd = qslMsgRcvd.String
	q.LOTWSent = lotwSent.String
	q.LOTWRcvd = lotwRcvd.String
	q.LOTWSentDate = lotwSentDate.String
	q.LOTWRcvdDate = lotwRcvdDate.String
	q.EQSLSent = eqslSent.String
	q.EQSLRcvd = eqslRcvd.String
	q.EQSLSentDate = eqslSentDate.String
	q.EQSLRcvdDate = eqslRcvdDate.String
	q.ClublogUploadDate = clublogDate.String
	q.ClublogUploadStatus = clublogStatus.String
	q.HRDLogUploadDate = hrdlogDate.String
	q.HRDLogUploadStatus = hrdlogStatus.String
	q.QRZComUploadDate = qrzcomDate.String
	q.QRZComUploadStatus = qrzcomStatus.String
	q.QRZComDownloadDate = qrzcomDlDate.String
	q.QRZComDownloadStatus = qrzcomDlStatus.String
	q.ClublogDownloadDate = clublogDlDate.String
	q.ClublogDownloadStatus = clublogDlStatus.String
	q.ContestID = contestID.String
	if srx.Valid {
		v := int(srx.Int64)
		q.SRX = &v
	}
	if stx.Valid {
		v := int(stx.Int64)
		q.STX = &v
	}
	q.SRXString = srxStr.String
	q.STXString = stxStr.String
	q.Check = checkField.String
	q.Precedence = precedence.String
	q.ARRLSect = arrlSect.String
	q.PropMode = propMode.String
	q.SatName = satName.String
	q.SatMode = satMode.String
	if antAz.Valid {
		v := antAz.Float64
		q.AntAz = &v
	}
	if antEl.Valid {
		v := antEl.Float64
		q.AntEl = &v
	}
	q.AntPath = antPath.String
	q.StationCallsign = stCall.String
	q.Operator = op.String
	q.MyGrid = myGrid.String
	q.MyGridExt = myGridExt.String
	q.MyCountry = myCountry.String
	q.MyState = myState.String
	q.MyCounty = myCnty.String
	q.MyIOTA = myIOTA.String
	q.MySOTARef = mySOTA.String
	q.MyPOTARef = myPOTA.String
	if myDXCC.Valid {
		v := int(myDXCC.Int64)
		q.MyDXCC = &v
	}
	if myCQZ.Valid {
		v := int(myCQZ.Int64)
		q.MyCQZone = &v
	}
	if myITUZ.Valid {
		v := int(myITUZ.Int64)
		q.MyITUZone = &v
	}
	if myLat.Valid {
		v := myLat.Float64
		q.MyLat = &v
	}
	if myLon.Valid {
		v := myLon.Float64
		q.MyLon = &v
	}
	q.MyStreet = myStreet.String
	q.MyCity = myCity.String
	q.MyPostalCode = myPostal.String
	q.MyRig = myRig.String
	q.MyAntenna = myAntenna.String
	if txp.Valid {
		v := txp.Float64
		q.TXPower = &v
	}
	q.Comment = comment.String
	q.Notes = notes.String
	q.SIG = sig.String
	q.SIGInfo = sigInfo.String
	q.MySIG = mySig.String
	q.MySIGInfo = mySigInfo.String
	q.WWFFRef = wwffRef.String
	q.MyWWFFRef = myWWFFRef.String
	if distance.Valid {
		v := distance.Float64
		q.Distance = &v
	}
	if rxPwr.Valid {
		v := rxPwr.Float64
		q.RXPower = &v
	}
	if aIndex.Valid {
		v := aIndex.Float64
		q.AIndex = &v
	}
	if kIndex.Valid {
		v := kIndex.Float64
		q.KIndex = &v
	}
	if sfi.Valid {
		v := sfi.Float64
		q.SFI = &v
	}
	q.SKCC = skcc.String
	q.FISTS = fists.String
	q.TenTen = tenTen.String
	q.ContactedOp = contactedOp.String
	q.EqCall = eqCall.String
	q.PFX = pfx.String
	q.MyName = myName.String
	q.Class = class.String
	q.DarcDOK = darcDOK.String
	q.MyDarcDOK = myDarcDOK.String
	q.Region = region.String
	q.SilentKey = silentKey.String
	q.SWL = swl.String
	q.QSOComplete = qsoComplete.String
	q.QSORandom = qsoRandom.String
	q.CreditGranted = creditGranted.String
	q.CreditSubmitted = creditSubmitted.String
	q.MyARRLSect = myARRLSect.String
	q.MyVUCCGrids = myVUCCGrids.String
	q.Extras = decodeExtras(extrasJSON.String)
	q.AwardRefs = awardRefs.String
	q.SyncUID = syncUID.String
	return q, nil
}

// parseTimeLoose tries several common ISO layouts returned by SQLite.
func parseTimeLoose(s string) time.Time {
	if s == "" {
		return time.Time{}
	}
	for _, layout := range []string{isoMillis, "2006-01-02T15:04:05Z", "2006-01-02 15:04:05"} {
		if t, err := time.Parse(layout, s); err == nil {
			return t.UTC()
		}
	}
	return time.Time{}
}

// IsBulkEditable reports whether a column may be written by a bulk edit. Used by
// the app layer's own test to prove that every field the UI offers is actually
// accepted here — the two lists live in different packages and drifted apart
// once already: the new confirmation columns were added to the QSL Manager's
// filter whitelist instead of this one, so the UI offered fields that the
// repository then refused with "field … is not bulk-editable".
func IsBulkEditable(column string) bool { return bulkEditableCols[column] }

// IsFilterable reports whether a column may appear in a query filter. Same
// reason as IsBulkEditable.
func IsFilterable(column string) bool { return filterableColumns[column] }

// IsBulkEditableExtra / IsFilterableExtra cover the ADIF fields that have no
// column of their own and live in extras_json.
func IsBulkEditableExtra(field string) bool { _, ok := bulkEditableExtras[field]; return ok }
func IsFilterableExtra(field string) bool   { _, ok := filterableExtras[field]; return ok }

// WorkedGridKeys returns the set of "GRID|MODE" keys already in the log, where
// GRID is the 4-character field+square and MODE has been through normMode.
//
// The mode is part of the key, and normMode is what makes the DX-cluster
// "group digital modes" option apply to grids for free: with grouping on, FT8
// and FT4 both normalise to the same token, so a grid worked on FT8 is not new
// on FT4; with it off they stay separate keys and it is.
//
// Truncated to four characters on the way in. A log holds a mix of JN36 and
// JN36QU depending on where each QSO came from, and grid chasing is a
// field+square game — without the truncation the same square counts as new
// forever, once per subsquare.
func (r *Repo) WorkedGridKeys(ctx context.Context, normMode func(string) string) (map[string]struct{}, error) {
	rows, err := r.db.QueryContext(ctx,
		// The column is "grid", not "gridsquare" — that is the ADIF field name, and
		// gridsquare_ext is a different (six-plus character) column entirely.
		`SELECT DISTINCT COALESCE(grid,''), COALESCE(mode,'') FROM qso
		 WHERE grid IS NOT NULL AND grid != ''`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := make(map[string]struct{}, 4096)
	for rows.Next() {
		var grid, mode string
		if err := rows.Scan(&grid, &mode); err != nil {
			return nil, err
		}
		grid = strings.ToUpper(strings.TrimSpace(grid))
		if len(grid) < 4 {
			continue
		}
		grid = grid[:4]
		mode = strings.ToUpper(strings.TrimSpace(mode))
		if normMode != nil && mode != "" {
			mode = normMode(mode)
		}
		out[grid+"|"+mode] = struct{}{}
	}
	return out, rows.Err()
}

// OrderedIDs returns every QSO id in chronological order, oldest first.
//
// The mirror of ListFiltered's "qso_date DESC, id DESC", so the tie-break is the
// same one the grid displays and a contact cannot change number depending on
// which way it is read.
//
// One query, ids only: the caller turns it into a rank once and keeps it in
// memory. Asking the database for a row's rank per row would be a correlated
// count over the whole log for each of thirty thousand rows.
func (r *Repo) OrderedIDs(ctx context.Context) ([]int64, time.Time, error) {
	rows, err := r.db.QueryContext(ctx, `SELECT id, qso_date FROM qso ORDER BY qso_date ASC, id ASC`)
	if err != nil {
		return nil, time.Time{}, err
	}
	defer rows.Close()
	out := make([]int64, 0, 4096)
	// qso_date is stored as a formatted STRING, not a driver date. Scanning it
	// into a time.Time silently fails on SQLite, which is how this returned an
	// error and left the whole column empty.
	var lastDate string
	for rows.Next() {
		var id int64
		var at string
		if err := rows.Scan(&id, &at); err != nil {
			return nil, time.Time{}, err
		}
		out = append(out, id)
		lastDate = at // ascending, so the last row read is the newest
	}
	if err := rows.Err(); err != nil {
		return nil, time.Time{}, err
	}
	// Parsed once, for the newest row only — the ordering came from SQL.
	return out, parseTimeLoose(lastDate), nil
}

// --- Folder synchronisation identity -----------------------------------------

// SetSyncUID stamps a contact's sync identity. Targeted UPDATE: it must never
// go through the normal write path, or an ordinary edit would clobber it.
func (r *Repo) SetSyncUID(ctx context.Context, id int64, uid string) error {
	_, err := r.db.ExecContext(ctx, `UPDATE qso SET sync_uid = ? WHERE id = ?`, uid, id)
	return err
}

// IDBySyncUID resolves an incoming change to a local row. Indexed, so this is
// the one lookup a sync performs per record and it stays a key hit rather than
// a scan — which is the whole reason sync_uid is a column and not a JSON key.
func (r *Repo) IDBySyncUID(ctx context.Context, uid string) (int64, bool, error) {
	if uid == "" {
		return 0, false, nil
	}
	var id int64
	err := r.db.QueryRowContext(ctx, `SELECT id FROM qso WHERE sync_uid = ? LIMIT 1`, uid).Scan(&id)
	if err == sql.ErrNoRows {
		return 0, false, nil
	}
	if err != nil {
		return 0, false, err
	}
	return id, true, nil
}

// IDByDedupeKey finds the local row a change refers to when its identity is
// unknown here.
//
// The change log starts EMPTY, so contacts logged before synchronisation was
// switched on are never exchanged — nothing has to be copied across, and
// identities are stamped lazily on the contacts that are actually touched.
//
// One case still needs this. Two machines can already hold the SAME old contact
// — the second was seeded by copying the database or importing an ADIF — with
// different row ids and no identity on either. The day one of them edits that
// contact, it stamps an identity and sends a change naming it; the other has
// never seen that identity and would insert a duplicate. Matching on the
// contact itself (callsign + minute + band + mode, the importer's own dedupe
// key) recognises it instead.
//
// Looked up only when an identity is unknown, which is rare, and it goes
// through idx_qso_callsign rather than scanning — a full map of every contact,
// rebuilt each pass, was the wrong shape for something this occasional.
func (r *Repo) IDByDedupeKey(ctx context.Context, callsign, qsoDateMinute, band, mode string) (id int64, uid string, found bool, err error) {
	if callsign == "" || qsoDateMinute == "" {
		return 0, "", false, nil
	}
	var u sql.NullString
	err = r.db.QueryRowContext(ctx, `
		SELECT id, sync_uid FROM qso
		 WHERE callsign = ? AND substr(qso_date, 1, 16) = ? AND band = ? AND mode = ?
		 LIMIT 1`,
		strings.ToUpper(callsign), qsoDateMinute, band, mode).Scan(&id, &u)
	if err == sql.ErrNoRows {
		return 0, "", false, nil
	}
	if err != nil {
		return 0, "", false, err
	}
	return id, u.String, true, nil
}

// ListMissingExtra returns the QSOs that do NOT carry an ADIF extras key.
//
// For a service with no status column of its own — HAMLOG.online, whose field
// the ADIF standard does not define — this is the backlog: everything never
// sent. Matched with LIKE on the extras JSON, which is a full scan; that is
// acceptable here because it answers a button the operator presses, not a hot
// path, and the alternative is a promoted column for a field no other program
// would ever read.
func (r *Repo) ListMissingExtra(ctx context.Context, key string) ([]QSO, error) {
	key = strings.ToUpper(strings.TrimSpace(key))
	if key == "" {
		return nil, fmt.Errorf("missing extras key")
	}
	// The JSON is written by encoding/json, so a present key always appears as
	// "KEY": — quoted, followed by a colon. A value containing that text cannot
	// produce a false positive: it would be escaped inside its own quotes.
	like := "%\"" + key + "\":%"
	rows, err := r.db.QueryContext(ctx,
		`SELECT `+selectCols+` FROM qso
		 WHERE COALESCE(extras_json,'') NOT LIKE ?
		 ORDER BY qso_date DESC, id DESC`, like)
	if err != nil {
		return nil, fmt.Errorf("list missing extra: %w", err)
	}
	defer rows.Close()
	out := make([]QSO, 0, 64)
	for rows.Next() {
		q, err := scanQSO(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, q)
	}
	return out, rows.Err()
}
