Backend/database/novels.go

package database

import (
	"backend/utils"
	"fmt"
	"strings"
	"time"
)

type Novel struct {
	ID            int32       `json:"id"`
	OriginalName  string      `db:"original_name" json:"original_name"`
	LocalizedName string      `db:"localized_name" json:"localized_name"`
	Description   string      `json:"description"`
	Image         string      `json:"image"`
	Rating        float32     `json:"rating"`
	ReleaseDate   time.Time   `json:"release_date" db:"release_date"`
	Duration      uint64      `json:"duration"`
	ExitStatus    string      `json:"exit_status" db:"exit_status"`
	IsDemo        bool        `json:"is_demo" db:"is_demo"`
	Platforms     string      `json:"platforms"`
	Genres        []Genre     `json:"genres" ignore:"1"`
	Links         []NovelLink `json:"links" ignore:"1"`
	Walkthrough   string      `json:"walkthrough"`
	RatingAll     []RatingAll `json:"rating_all" ignore:"1"`
	StatusAll     []StatusAll `json:"status_all" ignore:"1"`
	IsNSFW        bool        `json:"is_nsfw" db:"is_nsfw"`
}

type ShortNovel struct {
	ID            int32       `json:"id"`
	OriginalName  string      `db:"original_name" json:"original_name"`
	LocalizedName string      `db:"localized_name" json:"localized_name"`
	Description   string      `json:"-"`
	Image         string      `json:"image"`
	Rating        float32     `json:"rating"`
	ReleaseDate   time.Time   `json:"release_date" db:"release_date"`
	Duration      uint64      `json:"-"`
	ExitStatus    string      `json:"exit_status" db:"exit_status"`
	IsDemo        bool        `json:"is_demo" db:"is_demo"`
	Platforms     string      `json:"-"`
	Genres        []Genre     `json:"genres" ignore:"1"`
	Links         []NovelLink `json:"-" ignore:"1"`
	Walkthrough   string      `json:"-"`
	RatingAll     []RatingAll `json:"rating_all" ignore:"1"`
	IsNSFW        bool        `json:"is_nsfw" db:"is_nsfw"`
}

type CommentNovel struct {
	ID      int32  `json:"id" db:"id"`
	UserID  int64  `json:"user_id" db:"user_id" sql:"REFERENCES users(id)"`
	NovelID int16  `json:"novel_id" db:"novel_id" sql:"REFERENCES novels(id)"`
	Text    string `json:"text" db:"text"`
}

type NovelLink struct {
	NovelID int32  `json:"novel_id" db:"novel_id"`
	Name    string `json:"name"`
	Link    string `json:"link"`
	Type    string `json:"type"`
}

type RatingAll struct {
	Mark  int `json:"mark"`
	Count int `json:"count"`
}

type StatusAll struct {
	Status string `json:"status"`
	Count  int    `json:"count"`
}

func FetchNovels(orderBy string) ([]*ShortNovel, error) {
	var novels = make([]*ShortNovel, 0)
	err := Database.Select(&novels, fmt.Sprintf("select id, original_name, localized_name, image, rating, release_date from novels order by %v desc;", orderBy))
	if err != nil {
		return nil, err
	}

	tx := Database.MustBegin()
	for _, novel := range novels {
		novel.Genres = make([]Genre, 0)
		err = tx.Select(&novel.Genres, "select g.* from novel_genre join genres g on g.id = novel_genre.genre_id where novel_id=$1;", novel.ID)
		if err != nil {
			return nil, err
		}
	}
	err = tx.Commit()
	return novels, err
}

func FetchNovel(id int) (*Novel, error) {
	novel := new(Novel)
	err := Database.Get(novel, `select * from novels where id = $1;`, id)
	if err != nil {
		return novel, err
	}

	novel.Genres = make([]Genre, 0)
	err = Database.Select(&novel.Genres, `select genres.* from novel_genre join genres on (novel_genre.genre_id = genres.id) where novel_id = $1`, id)
	if err != nil {
		return novel, err
	}

	novel.RatingAll = make([]RatingAll, 0)
	err = Database.Select(&novel.RatingAll, `select mark, count(mark) from user_novel where novel_id=$1 and mark > 0 group by mark;`, id)
	if err != nil {
		return novel, err
	}

	novel.StatusAll = make([]StatusAll, 0)
	err = Database.Select(&novel.StatusAll, `select status, count(status) from user_novel where novel_id=$1 group by status;`, id)
	if err != nil {
		return novel, err
	}

	novel.Links = make([]NovelLink, 0)
	err = Database.Select(&novel.Links, "select * from novel_link where novel_id=$1;", novel.ID)
	return novel, err
}
func UpdateNovel(novel *Novel) (*Novel, error) {
	var newNovel = new(Novel)
	setPart := ""
	setPartList := make([]string, 0)
	for _, i := range utils.GetStructAndNamedForDatabase(newNovel) {
		setPartList = append(setPartList, i[0]+"="+i[1])
	}
	setPart = strings.Join(setPartList, ",")
	rows, err := Database.NamedQuery(fmt.Sprintf(`update novels set %s where id=:id returning *;`, setPart), novel)
	if err != nil {
		return newNovel, err
	}
	err = UpdateNovelGenres(int(novel.ID), novel.Genres)
	if err != nil {
		return newNovel, err
	}
	rows.Next()
	err = rows.StructScan(&newNovel)
	if err != nil {
		return newNovel, err
	}
	genres, err := FetchNovelGenres(int(novel.ID))
	newNovel.Genres = genres

	return newNovel, err
}
func UpdateNovelGenres(novelId int, genres []Genre) error {
	_, err := Database.Exec("delete from novel_genre where novel_id=$1;", novelId)
	if err != nil {
		return err
	}

	tx, err := Database.Begin()
	if err != nil {
		return err
	}
	for _, genre := range genres {
		_, err = tx.Exec("insert into novel_genre values ($1, $2);", genre.ID, novelId)
		if err != nil {
			return err
		}
	}
	err = tx.Commit()
	if err != nil {
		return err
	}
	return nil
}
func UpdateNovelCover(novelId int, url string) error {
	_, err := Database.Exec("update novels set image=$1 where id=$2;", url, novelId)
	return err
}

func CreateNovel(novelData *Novel) (Novel, error) {
	var novel Novel
	rows, err := Database.NamedQuery(`insert into novels (original_name, localized_name, image, description, is_demo) values (:original_name, :localized_name, :image, :description, :is_demo) returning *;`, novelData)
	if rows != nil && rows.Next() {
		_ = rows.StructScan(&novel)
	}
	return novel, err
}
func DeleteNovel(id int) error {
	_, err := Database.Exec("delete from novels where id = $1;", id)
	return err
}
func SearchNovels(query string) ([]ShortNovel, error) {
	novels := make([]ShortNovel, 0)
	err := Database.Select(&novels, "select * from search_novel($1) order by rating desc;", query)
	if err != nil {
		return novels, err
	}
	tx := Database.MustBegin()
	for _, novel := range novels {
		novel.Genres = make([]Genre, 0)
		err = tx.Select(&novel.Genres, "select g.* from novel_genre join genres g on g.id = novel_genre.genre_id where novel_id=$1;", novel.ID)
		if err != nil {
			return nil, err
		}
	}
	err = tx.Commit()
	return novels, err
}

func FetchNovelComments(id int) ([]CommentNovel, error) {
	var comments = make([]CommentNovel, 0)
	err := Database.Select(&comments, `select * from novel_comment where novel_id = $1;`, id)
	return comments, err
}
func AddNovelComment(commentData CommentNovel) (CommentNovel, error) {
	var comment CommentNovel
	rows, err := Database.NamedQuery(`insert into novel_comment(user_id, novel_id, text) values (:user_id, :novel_id, :text) returning *;`, commentData)
	if rows != nil && rows.Next() {
		_ = rows.StructScan(&comment)
	}
	return comment, err
}

func FetchNovelCharacters(id int) ([]Character, error) {
	var characters = make([]Character, 0)
	err := Database.Select(&characters, `select characters.*, character_novel.main from character_novel join characters on (characters.id=character_novel.character_id) where novel_id=$1;`, id)
	return characters, err
}

func FetchNovelGenres(id int) ([]Genre, error) {
	var genres = make([]Genre, 0)
	err := Database.Select(&genres, `select genres.* from novel_genre join genres on (novel_genre.genre_id = genres.id) where novel_id = $1`, id)
	return genres, err
}