package db

import (
	"context"
	"database/sql"
	"errors"
	"github.com/huandu/go-sqlbuilder"
	"log/slog"
	"xoxo-webserver/internal/domain/model"
)

const quoteTableName = "QUOTE"

var quoteStruct = sqlbuilder.NewStruct(new(model.Quote)).For(dbFlavor)

func GetQuote() (*model.Quote, error) {
	sb := quoteStruct.SelectFrom(quoteTableName)
	q, args := sb.Build()
	row := db.QueryRow(q, args...)
	var quote model.Quote
	if err := row.Scan(quoteStruct.Addr(&quote)...); err != nil && !errors.Is(err, sql.ErrNoRows) {
		slog.Error("Failed to get latest QUOTE", "error", err, "query", q, "args", args)
		return nil, err
	}
	return &quote, nil
}

func SetNewQuote(quote *model.Quote) error {
	txOptions := sql.TxOptions{Isolation: sql.LevelReadCommitted}
	tx, err := db.BeginTx(context.Background(), &txOptions)
	if err != nil {
		slog.Error("Failed to create db transaction", "error", err)
		return err
	}
	sb := quoteStruct.DeleteFrom(quoteTableName)
	q, args := sb.Build()
	_, err = tx.Exec(q, args...)
	if err != nil {
		slog.Error("Could not delete db entries of QUOTE table", "error", err)
	}

	ib := quoteStruct.InsertInto(quoteTableName, quote)
	q, args = ib.Build()
	_, err = tx.Exec(q, args...)
	if err != nil {
		slog.Error("Failed to create quoteTableName db entry", "error", err, "query", q, "args", args)
		return err
	}
	_ = tx.Commit()

	return nil
}

func createQuoteTableIfNotExists() {
	s := sqlbuilder.CreateTable(quoteTableName).IfNotExists().
		Define("QUOTE", "TEXT", "PRIMARY KEY").
		Define("SONG", "TEXT").
		Define("ALBUM", "TEXT").
		String()
	_, err := db.Exec(s)
	if err != nil {
		slog.Error("Could not create table", "quoteTableName", quoteTableName, "error", err)
	}
}
