215 lines
6.4 KiB
Go
215 lines
6.4 KiB
Go
package db
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"fmt"
|
|
"time"
|
|
|
|
"github.com/user/flexdxcluster2/internal/spot"
|
|
)
|
|
|
|
type SpotsStore struct {
|
|
db *DB
|
|
}
|
|
|
|
func NewSpotsStore(db *DB) *SpotsStore {
|
|
return &SpotsStore{db: db}
|
|
}
|
|
|
|
// Create insère un nouveau spot et retourne son ID
|
|
func (s *SpotsStore) Create(ctx context.Context, sp spot.Spot) (int64, error) {
|
|
res, err := s.db.conn.ExecContext(ctx, `
|
|
INSERT INTO spots (
|
|
dx, spotter, frequency_khz, frequency_mhz, band, mode,
|
|
comment, original_comment, time, timestamp,
|
|
dxcc, country_name,
|
|
new_dxcc, new_band, new_mode, new_slot, callsign_worked, in_watchlist,
|
|
pota_ref, sota_ref, park_name, summit_name,
|
|
flex_spot_number, command_number,
|
|
color, background_color, priority, life_time,
|
|
cluster_name, source
|
|
) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
|
|
sp.DX, sp.Spotter, sp.FrequencyKHz, sp.FrequencyMHz, sp.Band, sp.Mode,
|
|
sp.Comment, sp.OriginalComment, sp.Time, sp.Timestamp,
|
|
sp.DXCC, sp.CountryName,
|
|
boolToInt(sp.NewDXCC), boolToInt(sp.NewBand), boolToInt(sp.NewMode),
|
|
boolToInt(sp.NewSlot), boolToInt(sp.CallsignWorked), boolToInt(sp.InWatchlist),
|
|
sp.POTARef, sp.SOTARef, sp.ParkName, sp.SummitName,
|
|
sp.FlexSpotNumber, sp.CommandNumber,
|
|
sp.Color, sp.BackgroundColor, sp.Priority, sp.LifeTime,
|
|
sp.ClusterName, int(sp.Source),
|
|
)
|
|
if err != nil {
|
|
return 0, fmt.Errorf("create spot: %w", err)
|
|
}
|
|
return res.LastInsertId()
|
|
}
|
|
|
|
// GetAll retourne tous les spots, optionnellement filtrés par bande
|
|
func (s *SpotsStore) GetAll(ctx context.Context, band string) ([]spot.Spot, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
|
|
if band == "" || band == "0" || band == "ALL" {
|
|
rows, err = s.db.conn.QueryContext(ctx,
|
|
`SELECT * FROM spots ORDER BY timestamp DESC`)
|
|
} else {
|
|
rows, err = s.db.conn.QueryContext(ctx,
|
|
`SELECT * FROM spots WHERE band = ? ORDER BY timestamp DESC`, band)
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
return scanSpots(rows)
|
|
}
|
|
|
|
// FindByDXAndBand cherche un spot existant pour le même DX sur la même bande
|
|
func (s *SpotsStore) FindByDXAndBand(ctx context.Context, dx, band string) (*spot.Spot, error) {
|
|
row := s.db.conn.QueryRowContext(ctx,
|
|
`SELECT * FROM spots WHERE dx = ? AND band = ? ORDER BY timestamp DESC LIMIT 1`,
|
|
dx, band)
|
|
sp, err := scanSpot(row)
|
|
if err == sql.ErrNoRows {
|
|
return nil, nil
|
|
}
|
|
return sp, err
|
|
}
|
|
|
|
// FindByCommandNumber cherche un spot par son numéro de commande Flex
|
|
func (s *SpotsStore) FindByCommandNumber(ctx context.Context, cmdNum int) (*spot.Spot, error) {
|
|
row := s.db.conn.QueryRowContext(ctx,
|
|
`SELECT * FROM spots WHERE command_number = ? LIMIT 1`, cmdNum)
|
|
sp, err := scanSpot(row)
|
|
if err == sql.ErrNoRows {
|
|
return nil, nil
|
|
}
|
|
return sp, err
|
|
}
|
|
|
|
// FindByFlexSpotNumber cherche un spot par son numéro de spot Flex
|
|
func (s *SpotsStore) FindByFlexSpotNumber(ctx context.Context, flexNum int) (*spot.Spot, error) {
|
|
row := s.db.conn.QueryRowContext(ctx,
|
|
`SELECT * FROM spots WHERE flex_spot_number = ? LIMIT 1`, flexNum)
|
|
sp, err := scanSpot(row)
|
|
if err == sql.ErrNoRows {
|
|
return nil, nil
|
|
}
|
|
return sp, err
|
|
}
|
|
|
|
// UpdateFlexSpotNumber met à jour le numéro de spot Flex après confirmation du Flex
|
|
func (s *SpotsStore) UpdateFlexSpotNumber(ctx context.Context, cmdNum, flexNum int) error {
|
|
_, err := s.db.conn.ExecContext(ctx,
|
|
`UPDATE spots SET flex_spot_number = ? WHERE command_number = ?`,
|
|
flexNum, cmdNum)
|
|
return err
|
|
}
|
|
|
|
// DeleteByFlexSpotNumber supprime un spot par son numéro Flex
|
|
func (s *SpotsStore) DeleteByFlexSpotNumber(ctx context.Context, flexNum int) error {
|
|
_, err := s.db.conn.ExecContext(ctx,
|
|
`DELETE FROM spots WHERE flex_spot_number = ?`, flexNum)
|
|
return err
|
|
}
|
|
|
|
// DeleteByID supprime un spot par son ID
|
|
func (s *SpotsStore) DeleteByID(ctx context.Context, id int64) error {
|
|
_, err := s.db.conn.ExecContext(ctx,
|
|
`DELETE FROM spots WHERE id = ?`, id)
|
|
return err
|
|
}
|
|
|
|
// DeleteExpired supprime les spots expirés selon leur lifetime
|
|
func (s *SpotsStore) DeleteExpired(ctx context.Context, lifetimeSeconds int64) ([]spot.Spot, error) {
|
|
cutoff := time.Now().Unix() - lifetimeSeconds
|
|
rows, err := s.db.conn.QueryContext(ctx,
|
|
`SELECT * FROM spots WHERE timestamp < ?`, cutoff)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
expired, err := scanSpots(rows)
|
|
rows.Close()
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
_, err = s.db.conn.ExecContext(ctx,
|
|
`DELETE FROM spots WHERE timestamp < ?`, cutoff)
|
|
return expired, err
|
|
}
|
|
|
|
// --- Helpers ---
|
|
|
|
func boolToInt(b bool) int {
|
|
if b {
|
|
return 1
|
|
}
|
|
return 0
|
|
}
|
|
|
|
func scanSpots(rows *sql.Rows) ([]spot.Spot, error) {
|
|
var spots []spot.Spot
|
|
for rows.Next() {
|
|
sp, err := scanSpotFromRows(rows)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
spots = append(spots, *sp)
|
|
}
|
|
return spots, rows.Err()
|
|
}
|
|
|
|
func scanSpot(row *sql.Row) (*spot.Spot, error) {
|
|
var sp spot.Spot
|
|
var newDXCC, newBand, newMode, newSlot, worked, inWatchlist, source int
|
|
err := row.Scan(
|
|
&sp.ID, &sp.DX, &sp.Spotter, &sp.FrequencyKHz, &sp.FrequencyMHz,
|
|
&sp.Band, &sp.Mode, &sp.Comment, &sp.OriginalComment, &sp.Time, &sp.Timestamp,
|
|
&sp.DXCC, &sp.CountryName,
|
|
&newDXCC, &newBand, &newMode, &newSlot, &worked, &inWatchlist,
|
|
&sp.POTARef, &sp.SOTARef, &sp.ParkName, &sp.SummitName,
|
|
&sp.FlexSpotNumber, &sp.CommandNumber,
|
|
&sp.Color, &sp.BackgroundColor, &sp.Priority, &sp.LifeTime,
|
|
&sp.ClusterName, &source,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
sp.NewDXCC = newDXCC == 1
|
|
sp.NewBand = newBand == 1
|
|
sp.NewMode = newMode == 1
|
|
sp.NewSlot = newSlot == 1
|
|
sp.CallsignWorked = worked == 1
|
|
sp.InWatchlist = inWatchlist == 1
|
|
sp.Source = spot.SpotSource(source)
|
|
return &sp, nil
|
|
}
|
|
|
|
func scanSpotFromRows(rows *sql.Rows) (*spot.Spot, error) {
|
|
var sp spot.Spot
|
|
var newDXCC, newBand, newMode, newSlot, worked, inWatchlist, source int
|
|
err := rows.Scan(
|
|
&sp.ID, &sp.DX, &sp.Spotter, &sp.FrequencyKHz, &sp.FrequencyMHz,
|
|
&sp.Band, &sp.Mode, &sp.Comment, &sp.OriginalComment, &sp.Time, &sp.Timestamp,
|
|
&sp.DXCC, &sp.CountryName,
|
|
&newDXCC, &newBand, &newMode, &newSlot, &worked, &inWatchlist,
|
|
&sp.POTARef, &sp.SOTARef, &sp.ParkName, &sp.SummitName,
|
|
&sp.FlexSpotNumber, &sp.CommandNumber,
|
|
&sp.Color, &sp.BackgroundColor, &sp.Priority, &sp.LifeTime,
|
|
&sp.ClusterName, &source,
|
|
)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
sp.NewDXCC = newDXCC == 1
|
|
sp.NewBand = newBand == 1
|
|
sp.NewMode = newMode == 1
|
|
sp.NewSlot = newSlot == 1
|
|
sp.CallsignWorked = worked == 1
|
|
sp.InWatchlist = inWatchlist == 1
|
|
sp.Source = spot.SpotSource(source)
|
|
return &sp, nil
|
|
}
|