steam_analyzer/internal/repository/sqlite.go

// Package repository for repositorie's
package repository

import (
	"database/sql"
	"fmt"
	"log"
	"steam_analyzer/internal/domain"
	"time"

	"github.com/google/uuid"
)

type SQLLiteDatabase struct {
	db *sql.DB
}

func NewSQLLiteDatabase(db *sql.DB) *SQLLiteDatabase {
	return &SQLLiteDatabase{
		db: db,
	}
}

func ConnectOrCreateDatabase() *sql.DB {
	// Connect to (or create) the SQLite database
	db, err := sql.Open("sqlite3", "./steam_items.db")
	if err != nil {
		log.Fatal(err)
	}

	return db
}

func (sqldb SQLLiteDatabase) InitDatabaseTables() error {
	createTable := `
	CREATE TABLE IF NOT EXISTS item (
		id UUID PRIMARY KEY, 
		name TEXT,
		class_id TEXT UNIQUE NOT NULL,
		game_id INTEGER NOT NULL,
		type_id INTEGER
	);`
	_, err := sqldb.db.Exec(createTable)
	if err != nil {
		return err
	}

	createTable = `
	CREATE TABLE IF NOT EXISTS item_history (
		item_id UUID REFERENCES item(id), 
		price REAL,
		count INTEGER,
		date DATETIME NOT NULL,
		PRIMARY KEY (item_id, date)
	);`
	_, err = sqldb.db.Exec(createTable)
	if err != nil {
		return err
	}

	return nil
}

func (sqldb SQLLiteDatabase) AddItem(item domain.DBItem) error {
	query := `
		INSERT INTO item (id, name, class_id, game_id) 
			VALUES (?, ?, ?, ?)
	`

	_, err := sqldb.db.Exec(query,
		item.ID,
		item.Name,
		item.ClassID,
		item.GameID,
	)
	if err != nil {
		return err
	}

	return nil
}

func (sqldb SQLLiteDatabase) AddItemHistory(dbItemHistory domain.DBItemHistory) error {
	query := `
		INSERT INTO item_history (item_id, price, count, date) 
			VALUES (?, ?, ?, ?)
	`

	_, err := sqldb.db.Exec(query,
		dbItemHistory.ItemID,
		dbItemHistory.Price,
		dbItemHistory.Count,
		dbItemHistory.Date,
	)
	if err != nil {
		return err
	}

	return nil
}

func (sqldb SQLLiteDatabase) GetItemByClassID(classID string) (*domain.SteamItem, error) {
	query := `
		SELECT
			id,
			name,
			class_id
		FROM item
		WHERE class_id = ?
	`

	row := sqldb.db.QueryRow(query, classID)
	var steamItem domain.SteamItem
	err := row.Scan(
		&steamItem.ID,
		&steamItem.Name,
		&steamItem.ClassID,
	)
	if err != nil {
		return nil, err
	}

	return &steamItem, nil
}

func (sqldb SQLLiteDatabase) GetItemPriceHistoryByID(itemID uuid.UUID, limit int, offset int) ([]domain.ItemPriceHistory, error) {
	query := `
		SELECT
			price,
			count,
			date
		FROM item_history
		WHERE item_id = ?
		ORDER BY date DESC
		LIMIT ?
		OFFSET ?
	`

	var itemPriceHistory []domain.ItemPriceHistory
	rows, err := sqldb.db.Query(query,
		itemID,
		limit,
		offset,
	)
	if err != nil {
		return nil, err
	}

	for rows.Next() {
		var steamItemPriceHistory domain.ItemPriceHistory

		err := rows.Scan(
			&steamItemPriceHistory.Price,
			&steamItemPriceHistory.Count,
			&steamItemPriceHistory.Date,
		)
		if err != nil {
			fmt.Println("error while iterating rows", err)

			continue
		}
		itemPriceHistory = append(itemPriceHistory, steamItemPriceHistory)
	}

	return itemPriceHistory, nil
}

func (sqldb SQLLiteDatabase) GetItemsWithLastPriceByName(name string) ([]ItemWithLastPrice, error) {
	query := `
		SELECT
			item.name,
			price,
			last_date
		FROM item
		JOIN (
			SELECT
				item_id,
				price,
				max(date) as last_date
			FROM item_history
			GROUP BY item_id
		) as last_prices
		ON item.id = last_prices.item_id
		WHERE item.name like ?
		ORDER BY last_date DESC
	`

	rows, err := sqldb.db.Query(query,
		"%"+name+"%",
	)
	if err != nil {
		return nil, err
	}

	var itemsWithLastPrice []ItemWithLastPrice
	var itemWithLastPrice ItemWithLastPrice
	var lastDateStr string
	layout := "2006-01-02 15:04:05.999999999-07:00"
	for rows.Next() {
		err := rows.Scan(
			&itemWithLastPrice.Name,
			&itemWithLastPrice.Price,
			&lastDateStr,
		)
		if err != nil {
			fmt.Println("error while scaning row", err)
			continue
		}

		lastDate, err := time.Parse(layout, lastDateStr)
		if err != nil {
			fmt.Println("error while parsing date", err)
			continue
		}
		itemWithLastPrice.Date = lastDate

		itemsWithLastPrice = append(itemsWithLastPrice, itemWithLastPrice)
	}

	return itemsWithLastPrice, nil
}