FlexDXCluster/database.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"strconv"
	"time"

	log "github.com/sirupsen/logrus"
)

type Contact struct {
	Callsign        string
	Band            string
	Mode            string
	DXCC            string
	StationCallsign string
	Country         string
}

type Spotter struct {
	Spotter       string
	NumberofSpots string
}

type Log4OMContactsRepository struct {
	db  *sql.DB
	Log *log.Logger
}

type FlexDXClusterRepository struct {
	db  *sql.DB
	Log *log.Logger
}

func NewLog4OMContactsRepository(filePath string, log *log.Logger) *Log4OMContactsRepository {
	db, err := sql.Open("sqlite3", filePath)
	if err != nil {
		fmt.Println("Cannot open db", err)
	}
	return &Log4OMContactsRepository{
		db:  db,
		Log: log}
}

func NewFlexDXDatabase(filePath string, log *log.Logger) *FlexDXClusterRepository {

	db, err := sql.Open("sqlite3", filePath)
	if err != nil {
		fmt.Println("Cannot open db", err)
	}

	log.Info("Opening SQLite database")

	_, err = db.ExecContext(
		context.Background(),
		`CREATE TABLE IF NOT EXISTS "spots" (
	"id"	INTEGER NOT NULL UNIQUE,
	"commandNumber"	INTEGER NOT NULL UNIQUE,
	"flexSpotNumber" INTEGER,
	"dx"	TEXT NOT NULL,
	"freqMhz"	TEXT,
	"freqHz"	TEXT,
	"band"		TEXT,
	"mode"	TEXT,
	"spotter"	INTEGER,
	"flexMode"	TEXT,
	"source"	TEXT,
	"time"		TEXT,
	"timestamp"	INTEGER,
	"lifeTime"	TEXT,
	"priority"	TEXT,
	"comment"	TEXT,
	"color"	TEXT,
	"backgroundColor"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT)
)`,
	)

	if err != nil {
		log.Warn("Cannot create table", err)
	}

	return &FlexDXClusterRepository{
		db:  db,
		Log: log,
	}
}

func (r *Log4OMContactsRepository) ListByCountry(countryID string) ([]*Contact, error) {
	rows, err := r.db.Query("SELECT callsign, band, mode, dxcc, stationcallsign, country FROM log WHERE dxcc = ?", countryID)
	if err != nil {
		log.Error("could not query database", err)
		return nil, err
	}

	defer rows.Close()

	contacts := []*Contact{}
	for rows.Next() {
		c := Contact{}
		if err := rows.Scan(&c.Callsign, &c.Band, &c.Mode, &c.DXCC, &c.StationCallsign, &c.Country); err != nil {
			log.Error("could not query database", err)
			return nil, err
		}
		contacts = append(contacts, &c)
	}
	return contacts, nil
}

func (r *Log4OMContactsRepository) ListByCountryMode(countryID string, mode string) ([]*Contact, error) {

	if mode == "USB" || mode == "LSB" {

		rows, err := r.db.Query("SELECT callsign, band, mode, dxcc, stationcallsign, country FROM log WHERE dxcc = ? AND (mode = ? OR mode = ?)", countryID, "USB", "LSB")
		if err != nil {
			log.Error("could not query database", err)
			return nil, err
		}

		defer rows.Close()

		contacts := []*Contact{}
		for rows.Next() {
			c := Contact{}
			if err := rows.Scan(&c.Callsign, &c.Band, &c.Mode, &c.DXCC, &c.StationCallsign, &c.Country); err != nil {
				log.Error("could not query database", err)
				return nil, err
			}
			contacts = append(contacts, &c)
		}
		return contacts, nil

	} else {

		rows, err := r.db.Query("SELECT callsign, band, mode, dxcc, stationcallsign, country FROM log WHERE dxcc = ? AND mode = ?", countryID, mode)
		if err != nil {
			log.Error("could not query the database", err)
			return nil, err
		}

		defer rows.Close()

		contacts := []*Contact{}
		for rows.Next() {
			c := Contact{}
			if err := rows.Scan(&c.Callsign, &c.Band, &c.Mode, &c.DXCC, &c.StationCallsign, &c.Country); err != nil {
				fmt.Println(err)
				return nil, err
			}
			contacts = append(contacts, &c)
		}
		return contacts, nil

	}
}

func (r *Log4OMContactsRepository) ListByCountryBand(countryID string, band string) ([]*Contact, error) {
	rows, err := r.db.Query("SELECT callsign, band, mode, dxcc, stationcallsign, country FROM log WHERE dxcc = ? AND band = ?", countryID, band)
	if err != nil {
		fmt.Println(err)
		return nil, err
	}

	defer rows.Close()

	contacts := []*Contact{}
	for rows.Next() {
		c := Contact{}
		if err := rows.Scan(&c.Callsign, &c.Band, &c.Mode, &c.DXCC, &c.StationCallsign, &c.Country); err != nil {
			fmt.Println(err)
			return nil, err
		}
		contacts = append(contacts, &c)
	}
	return contacts, nil
}

func (r *Log4OMContactsRepository) ListByCallSign(callSign string, band string, mode string) ([]*Contact, error) {
	rows, err := r.db.Query("SELECT callsign, band, mode, dxcc, stationcallsign, country FROM log WHERE callsign = ? AND band = ? AND mode = ?", callSign, band, mode)
	if err != nil {
		fmt.Println(err)
		return nil, err
	}

	defer rows.Close()

	contacts := []*Contact{}
	for rows.Next() {
		c := Contact{}
		if err := rows.Scan(&c.Callsign, &c.Band, &c.Mode, &c.DXCC, &c.StationCallsign, &c.Country); err != nil {
			fmt.Println(err)
			return nil, err
		}
		contacts = append(contacts, &c)
	}
	return contacts, nil
}

func (r *FlexDXClusterRepository) GetAllSpots(limit string) []FlexSpot {

	Spots := []FlexSpot{}

	var query string

	if limit == "0" {
		query = "SELECT * from spots ORDER BY id DESC"
	} else {
		query = fmt.Sprintf("SELECT * from spots ORDER BY id DESC LIMIT %s", limit)
	}

	rows, err := r.db.Query(query)

	if err != nil {
		r.Log.Error(err)
		return nil
	}

	defer rows.Close()

	s := FlexSpot{}
	for rows.Next() {
		if err := rows.Scan(&s.ID, &s.CommandNumber, &s.FlexSpotNumber, &s.DX, &s.FrequencyMhz, &s.FrequencyHz, &s.Band, &s.Mode, &s.SpotterCallsign, &s.FlexMode, &s.Source, &s.UTCTime, &s.TimeStamp, &s.LifeTime, &s.Priority,
			&s.Comment, &s.Color, &s.BackgroundColor); err != nil {
			fmt.Println(err)
			return nil
		}

		Spots = append(Spots, s)
	}

	return Spots
}

func (r *FlexDXClusterRepository) GetSpotters() []Spotter {

	sList := []Spotter{}

	rows, err := r.db.Query("select spotter, count(*) as occurences from spots group by spotter order by occurences desc, spotter limit 5")

	if err != nil {
		r.Log.Error(err)
		return nil
	}

	defer rows.Close()

	s := Spotter{}
	for rows.Next() {
		if err := rows.Scan(&s.Spotter, &s.NumberofSpots); err != nil {
			fmt.Println(err)
			return nil
		}

		sList = append(sList, s)
	}

	return sList
}

func (r *FlexDXClusterRepository) FindDXSameBand(spot FlexSpot) (*FlexSpot, error) {
	rows, err := r.db.Query("SELECT * from spots WHERE dx = ? AND band = ?", spot.DX, spot.Band)
	if err != nil {
		r.Log.Error(err)
		return nil, err
	}

	defer rows.Close()

	s := FlexSpot{}
	for rows.Next() {
		if err := rows.Scan(&s.ID, &s.CommandNumber, &s.FlexSpotNumber, &s.DX, &s.FrequencyMhz, &s.FrequencyHz, &s.Band, &s.Mode, &s.SpotterCallsign, &s.FlexMode, &s.Source, &s.UTCTime, &s.TimeStamp, &s.LifeTime, &s.Priority,
			&s.Comment, &s.Color, &s.BackgroundColor); err != nil {
			fmt.Println(err)
			return nil, err
		}
	}
	return &s, nil
}

func (r *FlexDXClusterRepository) CreateSpot(spot FlexSpot) {
	query := "INSERT INTO `spots` (`commandNumber`, `flexSpotNumber`, `dx`, `freqMhz`, `freqHz`, `band`, `mode`, `spotter`, `flexMode`, `source`, `time`, `timestamp`, `lifeTime`, `priority`, `comment`, `color`, `backgroundColor`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
	insertResult, err := r.db.ExecContext(context.Background(), query, spot.CommandNumber, spot.CommandNumber, spot.DX, spot.FrequencyMhz, spot.FrequencyHz, spot.Band, spot.Mode, spot.SpotterCallsign, spot.FlexMode, spot.Source, spot.UTCTime, time.Now().Unix(), spot.LifeTime, spot.Priority, spot.Comment, spot.Color, spot.BackgroundColor)
	if err != nil {
		log.Errorf("cannot insert spot in database: %s", err)
	}

	_, err = insertResult.LastInsertId()
	if err != nil {
		log.Errorf("impossible to retrieve last inserted id: %s", err)
	}

}

func (r *FlexDXClusterRepository) UpdateSpotSameBand(spot FlexSpot) error {
	_, err := r.db.Exec(`UPDATE spots SET commandNumber = ?, DX = ?, freqMhz = ?, freqHz = ?, band = ?, mode = ?, spotter = ?, flexMode = ?, source = ?, time = ?, timestamp = ?, lifeTime = ?, priority = ?, comment = ?, color = ?, backgroundColor = ? WHERE DX = ? AND band = ?`,
		spot.CommandNumber, spot.DX, spot.FrequencyMhz, spot.FrequencyHz, spot.Band, spot.Mode, spot.SpotterCallsign, spot.FlexMode, spot.Source, spot.UTCTime, spot.TimeStamp, spot.LifeTime, spot.Priority, spot.Comment, spot.Color, spot.BackgroundColor, spot.DX, spot.Band)
	if err != nil {
		r.Log.Errorf("could not update database: %s", err)
		return err
	}
	return nil
}

func (r *FlexDXClusterRepository) FindSpotByCommandNumber(commandNumber string) (*FlexSpot, error) {
	rows, err := r.db.Query("SELECT * from spots WHERE commandNumber = ?", commandNumber)
	if err != nil {
		fmt.Println(err)
		return nil, err
	}

	defer rows.Close()

	s := FlexSpot{}
	for rows.Next() {
		if err := rows.Scan(&s.ID, &s.CommandNumber, &s.FlexSpotNumber, &s.DX, &s.FrequencyMhz, &s.FrequencyHz, &s.Band, &s.Mode, &s.SpotterCallsign, &s.FlexMode, &s.Source, &s.UTCTime, &s.TimeStamp, &s.LifeTime, &s.Priority,
			&s.Comment, &s.Color, &s.BackgroundColor); err != nil {
			fmt.Println(err)
			return nil, err
		}
	}
	return &s, nil
}

func (r *FlexDXClusterRepository) FindSpotByFlexSpotNumber(spotNumber string) (*FlexSpot, error) {
	rows, err := r.db.Query("SELECT * from spots WHERE flexSpotNumber = ?", spotNumber)
	if err != nil {
		fmt.Println(err)
		return nil, err
	}

	defer rows.Close()

	s := FlexSpot{}
	for rows.Next() {
		if err := rows.Scan(&s.ID, &s.CommandNumber, &s.FlexSpotNumber, &s.DX, &s.FrequencyMhz, &s.FrequencyHz, &s.Band, &s.Mode, &s.SpotterCallsign, &s.FlexMode, &s.Source, &s.UTCTime, &s.TimeStamp, &s.LifeTime, &s.Priority,
			&s.Comment, &s.Color, &s.BackgroundColor); err != nil {
			fmt.Println(err)
			return nil, err
		}
	}
	return &s, nil
}

func (r *FlexDXClusterRepository) UpdateFlexSpotNumberByID(flexSpotNumber string, spot FlexSpot) (*FlexSpot, error) {
	flexSpotNumberInt, _ := strconv.Atoi(flexSpotNumber)
	rows, err := r.db.Query(`UPDATE spots SET flexSpotNumber = ? WHERE id = ? RETURNING *`, flexSpotNumberInt, spot.ID)
	if err != nil {
		r.Log.Errorf("could not update database: %s", err)
	}

	defer rows.Close()

	s := FlexSpot{}
	for rows.Next() {
		if err := rows.Scan(&s.ID, &s.CommandNumber, &s.FlexSpotNumber, &s.DX, &s.FrequencyMhz, &s.FrequencyHz, &s.Band, &s.Mode, &s.SpotterCallsign, &s.FlexMode, &s.Source, &s.UTCTime, &s.TimeStamp, &s.LifeTime, &s.Priority,
			&s.Comment, &s.Color, &s.BackgroundColor); err != nil {
			fmt.Println(err)
			return nil, err
		}
	}

	return &s, nil
}

func (r *FlexDXClusterRepository) DeleteSpotByFlexSpotNumber(flexSpotNumber string) {
	flexSpotNumberInt, _ := strconv.Atoi(flexSpotNumber)
	query := "DELETE from spots WHERE flexSpotNumber = ?"
	_, err := r.db.Exec(query, flexSpotNumberInt)
	if err != nil {
		r.Log.Errorf("could not delete spot %v from database", flexSpotNumberInt)
	}
}

func DeleteDatabase(filePath string, log *log.Logger) {
	_, err := os.Stat(filePath)
	if !os.IsNotExist(err) {
		err := os.Remove(filePath)
		if err != nil {
			log.Error("could not delete existing database")
		}
		log.Info("deleting existing database")
	}
}