zoe-shaldon/server/internal/store/store.go

package store

import (
	"database/sql"
	"fmt"
	"path/filepath"
	"time"

	_ "modernc.org/sqlite"
)

type Store struct {
	db *sql.DB
}

const schema = `
CREATE TABLE IF NOT EXISTS rsvps (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	message TEXT,
	created_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS rsvp_guests (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	rsvp_id INTEGER NOT NULL REFERENCES rsvps(id),
	name TEXT NOT NULL,
	is_child INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS photo_uploads (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL,
	message TEXT,
	email TEXT,
	phone TEXT,
	created_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS photo_files (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	upload_id INTEGER NOT NULL REFERENCES photo_uploads(id),
	filename TEXT NOT NULL,
	path TEXT NOT NULL,
	url TEXT NOT NULL,
	created_at DATETIME NOT NULL
);
`

func Open(dataDir string) (*Store, error) {
	dbPath := filepath.Join(dataDir, "rsvp.db")
	db, err := sql.Open("sqlite", dbPath)
	if err != nil {
		return nil, fmt.Errorf("opening database: %w", err)
	}
	// SQLite only supports one writer at a time; a single connection avoids
	// SQLITE_BUSY errors under concurrent requests instead of retry logic.
	db.SetMaxOpenConns(1)

	if _, err := db.Exec(schema); err != nil {
		db.Close()
		return nil, fmt.Errorf("migrating schema: %w", err)
	}

	return &Store{db: db}, nil
}

func (s *Store) Close() error {
	return s.db.Close()
}

type Guest struct {
	Name    string
	IsChild bool
}

type RSVP struct {
	Guests  []Guest
	Message string
}

// ListGuests returns every RSVP'd guest, ordered by submission then entry order.
func (s *Store) ListGuests() ([]Guest, error) {
	rows, err := s.db.Query(
		`SELECT g.name, g.is_child
		 FROM rsvp_guests g
		 JOIN rsvps r ON r.id = g.rsvp_id
		 ORDER BY r.created_at ASC, g.id ASC`,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var guests []Guest
	for rows.Next() {
		var g Guest
		if err := rows.Scan(&g.Name, &g.IsChild); err != nil {
			return nil, err
		}
		guests = append(guests, g)
	}
	return guests, rows.Err()
}

func (s *Store) InsertRSVP(r RSVP) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	res, err := tx.Exec(
		`INSERT INTO rsvps (message, created_at) VALUES (?, ?)`,
		r.Message, time.Now().UTC(),
	)
	if err != nil {
		return err
	}
	rsvpID, err := res.LastInsertId()
	if err != nil {
		return err
	}

	for _, guest := range r.Guests {
		if _, err := tx.Exec(
			`INSERT INTO rsvp_guests (rsvp_id, name, is_child) VALUES (?, ?, ?)`,
			rsvpID, guest.Name, guest.IsChild,
		); err != nil {
			return err
		}
	}

	return tx.Commit()
}

type PhotoFile struct {
	Filename string
	Path     string
	URL      string
}

type PhotoUpload struct {
	Name    string
	Message string
	Email   string
	Phone   string
}

// CreatePhotoUpload inserts the upload record and returns its id, which the
// caller uses as the on-disk directory name before attaching files.
func (s *Store) CreatePhotoUpload(u PhotoUpload) (int64, error) {
	res, err := s.db.Exec(
		`INSERT INTO photo_uploads (name, message, email, phone, created_at) VALUES (?, ?, ?, ?, ?)`,
		u.Name, u.Message, u.Email, u.Phone, time.Now().UTC(),
	)
	if err != nil {
		return 0, err
	}
	return res.LastInsertId()
}

func (s *Store) AddPhotoFiles(uploadID int64, files []PhotoFile) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	now := time.Now().UTC()
	for _, f := range files {
		if _, err := tx.Exec(
			`INSERT INTO photo_files (upload_id, filename, path, url, created_at) VALUES (?, ?, ?, ?, ?)`,
			uploadID, f.Filename, f.Path, f.URL, now,
		); err != nil {
			return err
		}
	}
	return tx.Commit()
}