eintopf.info@v0.13.16/service/event/store_sql.go

// Copyright (C) 2022 The Eintopf authors
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU Affero General Public License as
// published by the Free Software Foundation, either version 3 of the
// License, or (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Affero General Public License for more details.
//
// You should have received a copy of the GNU Affero General Public License
// along with this program.  If not, see <https://www.gnu.org/licenses/>.

package event

import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	"github.com/google/uuid"
	"github.com/jmoiron/sqlx"

	"eintopf.info/internal/crud"
	"eintopf.info/service/dbmigration"
)

// NewSqlStore returns a new sql db event store.
func NewSqlStore(db *sqlx.DB, migrationService dbmigration.Service) (*SqlStore, error) {
	store := &SqlStore{db: db, migrationService: migrationService}
	if err := store.runMigrations(context.Background()); err != nil {
		return nil, err
	}
	return store, nil
}

type SqlStore struct {
	db               *sqlx.DB
	migrationService dbmigration.Service
}

func (s *SqlStore) runMigrations(ctx context.Context) error {
	return s.migrationService.RunMigrations(ctx, []dbmigration.Migration{
		dbmigration.NewMigration("createEventsTable", s.createEventsTable, nil),
		dbmigration.NewMigration("createEventsOrganizerTable", s.createEventsOrganizerTable, nil),
		dbmigration.NewMigration("createInvolvedTable", s.createInvolvedTable, nil),
		dbmigration.NewMigration("createEventsTagsTable", s.createEventsTagsTable, nil),
		dbmigration.NewMigration("createEventsOwnedByTable", s.createEventsOwnedByTable, nil),
		dbmigration.NewMigration("moveOrganizerToNewTable", s.moveOrganizersToNewTable, nil),
		dbmigration.NewMigration("removeEmptyTags", s.removeEmptyTags, nil),
	})
}

func (s *SqlStore) createEventsTable(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS events (
            id varchar(32) NOT NULL PRIMARY KEY UNIQUE,
            published boolean DEFAULT FALSE,
            deactivated boolean DEFAULT FALSE,
            canceled boolean DEFAULT FALSE,
            parent varchar(64) DEFAULT "",
            parentListed boolean DEFAULT FALSE,
            name varchar(64) NOT NULL,
            location varchar(64),
            location2 varchar(64),
            description varchar(512),
            start timestamp,
            end timestamp NULL default NULL,
            image varchar(128)
        );
    `)
	return err
}

func (s *SqlStore) recreateEventsTable(ctx context.Context) error {
	events, total, err := s.Find(ctx, nil)
	if err != nil {
		return err
	}
	if len(events) != total {
		return fmt.Errorf("len(events) != total")
	}
	if _, err := s.db.Exec(`DROP TABLE events`); err != nil {
		return err
	}
	if err := s.createEventsTable(ctx); err != nil {
		return err
	}
	for _, event := range events {
		err := s.insertEvent(ctx, event)
		if err != nil {
			return err
		}
	}
	return nil
}

func (s *SqlStore) createEventsOrganizerTable(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS events_organizer (
            ID INTEGER PRIMARY KEY AUTOINCREMENT,
            event_id varchar(32),
            organizer varchar(64)
        );
    `)
	return err
}

func (s *SqlStore) createInvolvedTable(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS events_involved (
            ID INTEGER PRIMARY KEY AUTOINCREMENT,
            event_id varchar(32),
            name varchar(64),
            description varchar(512)
        );
    `)
	return err
}

func (s *SqlStore) createEventsTagsTable(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS events_tags (
            ID INTEGER PRIMARY KEY AUTOINCREMENT,
            event_id varchar(32),
            tag varchar(64)
        );
    `)
	return err
}

func (s *SqlStore) createEventsOwnedByTable(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS events_owned_by (
            ID INTEGER PRIMARY KEY AUTOINCREMENT,
            event_id varchar(32),
            user_id varchar(32)
        );
    `)
	if err != nil {
		return fmt.Errorf("create events_owned_by table: %s", err)
	}
	return nil
}

func (s *SqlStore) removeEmptyTags(ctx context.Context) error {
	events, _, err := s.Find(context.Background(), &crud.FindParams[FindFilters]{})
	if err != nil {
		return fmt.Errorf("remove empty tags: find events: %s", err)
	}
	for _, e := range events {
		needsUpdate := false
		for i, tag := range e.Tags {
			if tag == "" {
				needsUpdate = true
				e.Tags = append(e.Tags[:i], e.Tags[i+1:]...)
			}
		}
		if needsUpdate {
			_, err := s.Update(context.Background(), e)
			if err != nil {
				return fmt.Errorf("remove empty tags: update event(%s):%s", e.ID, err)
			}
		}
	}
	return nil
}

func (s *SqlStore) moveOrganizersToNewTable(ctx context.Context) error {
	rows, err := s.db.QueryxContext(ctx, `
        SELECT id, organizer, organizer2
        FROM events
    `, nil)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil
		}
		// When the organizer or organizer2 column does not exist, just ignore the migration
		if err.Error() == "no such column: organizer" || err.Error() == "no such column: organizer2" {
			return nil
		}
		return fmt.Errorf("select organizer and organizer2: %s", err)
	}
	defer rows.Close()

	type OldEvent struct {
		ID         string `db:"id"`
		Organizer  string `db:"organizer"`
		Organizer2 string `db:"organizer2"`
	}

	oldEvents := []OldEvent{}
	for rows.Next() {
		oldEvent := OldEvent{}
		err := rows.StructScan(&oldEvent)
		if err != nil {
			return err
		}

		oldEvents = append(oldEvents, oldEvent)
	}
	rows.Close()

	for _, oldEvent := range oldEvents {
		organizers := []string{}
		if oldEvent.Organizer != "" {
			organizers = append(organizers, oldEvent.Organizer)
		}
		if oldEvent.Organizer2 != "" {
			organizers = append(organizers, oldEvent.Organizer2)
		}
		err = s.insertOrganizersForEvent(ctx, &Event{
			ID:         oldEvent.ID,
			Organizers: organizers,
		})
		if err != nil {
			return fmt.Errorf("insert organizer and organizer2: %s", err)
		}
	}
	if err := s.recreateEventsTable(ctx); err != nil {
		return fmt.Errorf("recreateEventsTable: %s", err)
	}
	return nil
}

func (s *SqlStore) Create(ctx context.Context, newEvent *NewEvent) (*Event, error) {
	event := EventFromNewEvent(newEvent, uuid.New().String())
	err := s.insertEvent(ctx, event)
	if err != nil {
		return nil, err
	}

	err = s.insertOrganizersForEvent(ctx, event)
	if err != nil {
		return nil, err
	}
	err = s.insertInvolvedForEvent(ctx, event)
	if err != nil {
		return nil, err
	}
	err = s.insertTagsForEvent(ctx, event)
	if err != nil {
		return nil, err
	}
	err = s.insertOwnedByForEvent(ctx, event)
	if err != nil {
		return nil, err
	}

	return event, nil
}

func (s *SqlStore) insertEvent(ctx context.Context, event *Event) error {
	_, err := s.db.NamedExecContext(ctx, `
        INSERT INTO events (
            id,
            published,
            deactivated,
            canceled,
            parent,
            parentListed,
            name,
            location,
            location2,
            description,
            start,
            end,
            image
        ) VALUES (
            :id,
            :published,
            :deactivated,
            :canceled,
            :parent,
            :parentListed,
            :name,
            :location,
            :location2,
            :description,
            :start,
            :end,
            :image
        )
    `, event)
	return err
}

func (s *SqlStore) insertOrganizersForEvent(ctx context.Context, event *Event) error {
	for _, organizer := range event.Organizers {
		_, err := s.db.ExecContext(ctx, `
            INSERT INTO events_organizer (
                event_id,
                organizer
            ) VALUES (
                $1,
                $2
            )
        `, event.ID, organizer)
		if err != nil {
			return err
		}
	}
	return nil
}

func (s *SqlStore) insertInvolvedForEvent(ctx context.Context, event *Event) error {
	for _, involved := range event.Involved {
		_, err := s.db.ExecContext(ctx, `
            INSERT INTO events_involved (
                event_id,
                name,
                description
            ) VALUES (
                $1,
                $2,
                $3
            )
        `, event.ID, involved.Name, involved.Description)
		if err != nil {
			return err
		}
	}
	return nil
}

func (s *SqlStore) insertTagsForEvent(ctx context.Context, event *Event) error {
	for _, tag := range event.Tags {
		_, err := s.db.ExecContext(ctx, `
            INSERT INTO events_tags (
                event_id,
                tag
            ) VALUES (
                $1,
                $2
            )
        `, event.ID, tag)
		if err != nil {
			return err
		}
	}
	return nil
}

func (s *SqlStore) insertOwnedByForEvent(ctx context.Context, event *Event) error {
	for _, ownedBy := range event.OwnedBy {
		_, err := s.db.ExecContext(ctx, `
            INSERT INTO events_owned_by (
                event_id,
                user_id
            ) VALUES (
                $1,
                $2
            )
        `, event.ID, ownedBy)
		if err != nil {
			return err
		}
	}
	return nil
}

func (s *SqlStore) Update(ctx context.Context, event *Event) (*Event, error) {
	_, err := s.db.NamedExecContext(ctx, `
        UPDATE
            events
        SET
            published=:published,
            deactivated=:deactivated,
            canceled=:canceled,
            parent=:parent,
            parentListed=:parentListed,
            name=:name,
            location=:location,
            location2=:location2,
            description=:description,
            start=:start,
            end=:end,
            image=:image
        WHERE
            id=:id
    `, event)
	if err != nil {
		return nil, err
	}

	err = s.deleteOrganizersForEvent(ctx, event.ID)
	if err != nil {
		return nil, err
	}
	err = s.insertOrganizersForEvent(ctx, event)
	if err != nil {
		return nil, err
	}

	err = s.deleteInvolvedForEvent(ctx, event.ID)
	if err != nil {
		return nil, err
	}
	err = s.insertInvolvedForEvent(ctx, event)
	if err != nil {
		return nil, err
	}

	err = s.deleteTagsForEvent(ctx, event.ID)
	if err != nil {
		return nil, err
	}
	err = s.insertTagsForEvent(ctx, event)
	if err != nil {
		return nil, err
	}

	err = s.deleteOwnedByForEvent(ctx, event.ID)
	if err != nil {
		return nil, err
	}
	err = s.insertOwnedByForEvent(ctx, event)
	if err != nil {
		return nil, err
	}

	return event, err
}

func (s *SqlStore) Delete(ctx context.Context, id string) error {
	_, err := s.db.ExecContext(ctx, "DELETE FROM events WHERE id = $1", id)
	if err != nil {
		return err
	}

	err = s.deleteOrganizersForEvent(ctx, id)
	if err != nil {
		return err
	}
	err = s.deleteInvolvedForEvent(ctx, id)
	if err != nil {
		return err
	}
	err = s.deleteTagsForEvent(ctx, id)
	if err != nil {
		return err
	}
	err = s.deleteOwnedByForEvent(ctx, id)
	if err != nil {
		return err
	}

	return nil
}

func (s *SqlStore) deleteOrganizersForEvent(ctx context.Context, id string) error {
	_, err := s.db.ExecContext(ctx, "DELETE FROM events_organizer WHERE event_id = $1", id)
	if err != nil {
		return err
	}
	return nil
}

func (s *SqlStore) deleteInvolvedForEvent(ctx context.Context, id string) error {
	_, err := s.db.ExecContext(ctx, "DELETE FROM events_Involved WHERE event_id = $1", id)
	if err != nil {
		return err
	}
	return nil
}

func (s *SqlStore) deleteTagsForEvent(ctx context.Context, id string) error {
	_, err := s.db.ExecContext(ctx, "DELETE FROM events_tags WHERE event_id = $1", id)
	if err != nil {
		return err
	}
	return nil
}

func (s *SqlStore) deleteOwnedByForEvent(ctx context.Context, id string) error {
	_, err := s.db.ExecContext(ctx, "DELETE FROM events_owned_by WHERE event_id = $1", id)
	if err != nil {
		return err
	}
	return nil
}

func (s *SqlStore) FindByID(ctx context.Context, id string) (*Event, error) {
	event := &Event{}
	err := s.db.GetContext(ctx, event, `
        SELECT
            id,
            published,
            deactivated,
            canceled,
            parent,
            parentListed,
            name,
            location,
            location2,
            description,
            start,
            end,
            image
        FROM events
        WHERE events.id = $1
    `, id)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}

	s.findOrganizersForEvent(ctx, event)
	if err != nil {
		return nil, err
	}
	s.findInvolvedForEvent(ctx, event)
	if err != nil {
		return nil, err
	}
	s.findTagsForEvent(ctx, event)
	if err != nil {
		return nil, err
	}
	s.findOwnedByForEvent(ctx, event)
	if err != nil {
		return nil, err
	}

	return event, nil
}

func (s *SqlStore) findOrganizersForEvent(ctx context.Context, event *Event) error {
	organizers := []string{}
	err := s.db.SelectContext(ctx, &organizers, `
        SELECT organizer
        FROM events_organizer
        WHERE event_id = $1
    `, event.ID)
	if err == sql.ErrNoRows {
		return nil
	}
	if err != nil {
		return err
	}
	event.Organizers = organizers
	return nil
}

func (s *SqlStore) findInvolvedForEvent(ctx context.Context, event *Event) error {
	involved := []Involved{}
	err := s.db.SelectContext(ctx, &involved, `
        SELECT name, description
        FROM events_involved
        WHERE event_id = $1
    `, event.ID)
	if err == sql.ErrNoRows {
		return nil
	}
	if err != nil {
		return err
	}
	event.Involved = involved
	return nil
}

func (s *SqlStore) findTagsForEvent(ctx context.Context, event *Event) error {
	tags := []string{}
	err := s.db.SelectContext(ctx, &tags, `
        SELECT tag
        FROM events_tags
        WHERE event_id = $1
    `, event.ID)
	if err == sql.ErrNoRows {
		return nil
	}
	if err != nil {
		return err
	}
	event.Tags = tags
	return nil
}

func (s *SqlStore) findOwnedByForEvent(ctx context.Context, event *Event) error {
	ownedBy := []string{}
	err := s.db.SelectContext(ctx, &ownedBy, `
        SELECT user_id
        FROM events_owned_by
        WHERE event_id = $1
    `, event.ID)
	if err == sql.ErrNoRows {
		return nil
	}
	if err != nil {
		return err
	}
	event.OwnedBy = ownedBy
	return nil
}

var sortableFields = map[string]string{
	"id":           "id",
	"deactivated":  "deactivated",
	"published":    "published",
	"canceled":     "canceled",
	"parent":       "parent",
	"parentListed": "parentListed",
	"name":         "name",
	"location":     "location",
	"start":        "start",
	"end":          "end",
}

func (s *SqlStore) Find(ctx context.Context, params *crud.FindParams[FindFilters]) ([]*Event, int, error) {
	query := `
        SELECT
            events.id,
            events.published,
            events.deactivated,
            events.canceled,
            events.parent,
            events.parentListed,
            events.name,
            events.location,
            events.location2,
            events.description,
            events.start,
            events.end,
            events.image
        FROM
            events
    `
	joinQuery := ""
	whereStatements := []string{}
	sqlParams := make(map[string]interface{})
	if params != nil {
		if params.Filters != nil {
			if params.Filters.ID != nil {
				whereStatements = append(whereStatements, "events.id=:id")
				sqlParams["id"] = params.Filters.ID
			}
			if params.Filters.Deactivated != nil {
				whereStatements = append(whereStatements, "events.deactivated=:deactivated")
				sqlParams["deactivated"] = params.Filters.Deactivated
			}
			if params.Filters.Published != nil {
				whereStatements = append(whereStatements, "events.published=:published")
				sqlParams["published"] = params.Filters.Published
			}
			if params.Filters.Canceled != nil {
				whereStatements = append(whereStatements, "events.canceled=:canceled")
				sqlParams["canceled"] = params.Filters.Canceled
			}
			if params.Filters.Parent != nil {
				whereStatements = append(whereStatements, "events.parent=:parent")
				sqlParams["parent"] = params.Filters.Parent
			}
			if params.Filters.Name != nil {
				whereStatements = append(whereStatements, "events.name=:name")
				sqlParams["name"] = params.Filters.Name
			}
			if params.Filters.LikeName != nil {
				whereStatements = append(whereStatements, "events.name LIKE :likeName")
				sqlParams["likeName"] = fmt.Sprintf("%%%s%%", *params.Filters.LikeName)
			}
			if params.Filters.Organizers != nil {
				joinQuery += " LEFT JOIN events_organizer ON events.id = events_organizer.event_id"
				organizerStatemtes := make([]string, len(params.Filters.Organizers))
				for i, organizer := range params.Filters.Organizers {
					organizerRef := fmt.Sprintf("organizer%d", i)
					organizerStatemtes[i] = fmt.Sprintf("events_organizer.organizer=:%s", organizerRef)
					sqlParams[organizerRef] = organizer
				}
				whereStatements = append(whereStatements, fmt.Sprintf("(%s)", strings.Join(organizerStatemtes, " OR ")))
			}
			if params.Filters.Location != nil {
				whereStatements = append(whereStatements, "events.location=:location")
				sqlParams["Location"] = params.Filters.Location
			}
			if params.Filters.Location2 != nil {
				whereStatements = append(whereStatements, "events.location2=:location2")
				sqlParams["Location2"] = params.Filters.Location2
			}
			if params.Filters.Description != nil {
				whereStatements = append(whereStatements, "events.description=:description")
				sqlParams["Description"] = params.Filters.Description
			}
			if params.Filters.Start != nil {
				whereStatements = append(whereStatements, "events.start=:start")
				sqlParams["start"] = params.Filters.Start
			}
			if params.Filters.StartBefore != nil {
				whereStatements = append(whereStatements, "events.start<:startBefore")
				sqlParams["startBefore"] = params.Filters.StartBefore
			}
			if params.Filters.StartAfter != nil {
				whereStatements = append(whereStatements, "events.start>:startAfter")
				sqlParams["startAfter"] = params.Filters.StartAfter
			}
			if params.Filters.End != nil {
				whereStatements = append(whereStatements, "events.end=:end")
				sqlParams["end"] = params.Filters.End
			}
			if params.Filters.EndBefore != nil {
				whereStatements = append(whereStatements, "events.end<:endBefore")
				sqlParams["endBefore"] = params.Filters.EndBefore
			}
			if params.Filters.EndAfter != nil {
				whereStatements = append(whereStatements, "events.end>:endAfter")
				sqlParams["endAfter"] = params.Filters.EndAfter
			}
			if params.Filters.Tags != nil {
				joinQuery += " JOIN events_tags ON events.id = events_tags.event_id"
				tagStatemtes := make([]string, len(params.Filters.Tags))
				for i, tag := range params.Filters.Tags {
					tagRef := fmt.Sprintf("tag%d", i)
					tagStatemtes[i] = fmt.Sprintf("events_tags.tag=:%s", tagRef)
					sqlParams[tagRef] = tag
				}
				whereStatements = append(whereStatements, fmt.Sprintf("(%s)", strings.Join(tagStatemtes, " OR ")))
			}
			if params.Filters.OwnedBy != nil {
				joinQuery += " LEFT JOIN events_owned_by ON events.id = events_owned_by.event_id"
				ownedByStatements := []string{}
				for i, ownedBy := range params.Filters.OwnedBy {
					ownedByRef := fmt.Sprintf("ownedBy%d", i)
					ownedByStatements = append(ownedByStatements, fmt.Sprintf("events_owned_by.user_id=:%s", ownedByRef))
					sqlParams[ownedByRef] = ownedBy
				}
				if params.Filters.OwnedByOrOrganizers != nil {
					joinQuery += " LEFT JOIN events_organizer ON events.id = events_organizer.event_id"
					for i, ownedByGroup := range params.Filters.OwnedByOrOrganizers {
						ownedByRef := fmt.Sprintf("ownedByOrganizer%d", i)
						ownedByStatements = append(ownedByStatements, fmt.Sprintf("events_organizer.organizer=:%s", ownedByRef))
						sqlParams[ownedByRef] = ownedByGroup
					}
				}
				whereStatements = append(whereStatements, fmt.Sprintf("(%s)", strings.Join(ownedByStatements, " OR ")))
			}

			if joinQuery != "" {
				query += " " + joinQuery
			}
			if len(whereStatements) > 0 {
				query += " WHERE " + strings.Join(whereStatements, " AND ")
			}
			if joinQuery != "" {
				query += " GROUP BY events.id"
			}
		}

		if params.Sort != "" {
			sort, ok := sortableFields[params.Sort]
			if !ok {
				return nil, 0, fmt.Errorf("find events: invalid sort field: %s", params.Sort)
			}
			order := "ASC"
			if params.Order == "DESC" {
				order = "DESC"
			}
			query += fmt.Sprintf(" ORDER BY events.%s %s", sort, order)
		}
		if params.Limit > 0 {
			query += fmt.Sprintf(" LIMIT %d", params.Limit)
		}
		if params.Offset > 0 {
			query += fmt.Sprintf(" OFFSET %d", params.Offset)
		}
	}
	rows, err := s.db.NamedQueryContext(ctx, query, sqlParams)
	if err != nil && err != sql.ErrNoRows {
		return nil, 0, fmt.Errorf("find events: %s", err)
	}
	defer rows.Close()
	events := make([]*Event, 0)
	for rows.Next() {
		event := Event{}
		err := rows.StructScan(&event)
		if err != nil {
			return nil, 0, err
		}

		events = append(events, &event)
	}

	for i, event := range events {
		err := s.findOrganizersForEvent(ctx, event)
		if err != nil {
			return nil, 0, err
		}
		err = s.findInvolvedForEvent(ctx, event)
		if err != nil {
			return nil, 0, err
		}
		err = s.findTagsForEvent(ctx, event)
		if err != nil {
			return nil, 0, err
		}
		err = s.findOwnedByForEvent(ctx, event)
		if err != nil {
			return nil, 0, err
		}
		events[i] = event
	}

	totalQuery := `
        SELECT COUNT(*) as total
        FROM (
            SELECT events.id
            FROM events
    `
	if len(whereStatements) > 0 {
		if joinQuery != "" {
			totalQuery += " " + joinQuery
		}
		totalQuery += " WHERE " + strings.Join(whereStatements, " AND ")
		if joinQuery != "" {
			totalQuery += " GROUP BY events.id"
		}
	}
	totalQuery += ")"

	totalEvents := struct {
		Total int `db:"total"`
	}{}
	rows2, err := s.db.NamedQueryContext(ctx, totalQuery, sqlParams)
	if err != nil && err != sql.ErrNoRows {
		return nil, 0, fmt.Errorf("find events: total: %s", err)
	}
	defer rows2.Close()
	if rows2.Next() {
		rows2.StructScan(&totalEvents)
	}

	return events, totalEvents.Total, nil
}