kereru-forms/backend/internal/database/sqlite_test.go

package database

import (
	"context"
	"encoding/json"
	"errors"
	"path/filepath"
	"testing"
	"time"
)

func testStore(t *testing.T) (*sqliteStore, string) {
	t.Helper()
	path := filepath.Join(t.TempDir(), "kereru-test.db")
	store, err := Open("sqlite", "file:"+path+"?_pragma=busy_timeout(5000)&_pragma=foreign_keys(1)")
	if err != nil {
		t.Fatalf("Open: %v", err)
	}
	t.Cleanup(func() { _ = store.Close() })
	return store.(*sqliteStore), path
}

func sampleForm() *Form {
	now := time.Now().UTC()
	return &Form{
		ID:                 "11111111-1111-1111-1111-111111111111",
		FormGroupID:        "22222222-2222-2222-2222-222222222222",
		Version:            1,
		Title:              "Customer feedback",
		Description:        strPtr("Tell us how we did"),
		IsActive:           true,
		RequiresAccessCode: false,
		CreatedAt:          now,
		UpdatedAt:          now,
		Questions: []Question{
			{
				ID:       "33333333-3333-3333-3333-333333333333",
				FormID:   "11111111-1111-1111-1111-111111111111",
				Position: 0,
				Key:      "happy",
				Label:    "Are you happy?",
				Type:     "boolean",
				Required: true,
			},
			{
				ID:       "44444444-4444-4444-4444-444444444444",
				FormID:   "11111111-1111-1111-1111-111111111111",
				Position: 1,
				Key:      "score",
				Label:    "Score",
				Type:     "number",
			},
		},
	}
}

func strPtr(s string) *string { return &s }

func TestMigrateIsIdempotent(t *testing.T) {
	_, path := testStore(t)
	second, err := Open("sqlite", "file:"+path)
	if err != nil {
		t.Fatalf("re-open: %v", err)
	}
	_ = second.Close()
}

func TestFormCRUD(t *testing.T) {
	store, _ := testStore(t)
	ctx := context.Background()

	form := sampleForm()
	if err := store.CreateForm(ctx, form); err != nil {
		t.Fatalf("CreateForm: %v", err)
	}

	got, err := store.GetForm(ctx, form.ID)
	if err != nil {
		t.Fatalf("GetForm: %v", err)
	}
	if got.Title != form.Title || len(got.Questions) != 2 {
		t.Fatalf("unexpected form: %+v", got)
	}
	if got.Questions[0].Key != "happy" || got.Questions[1].Key != "score" {
		t.Fatalf("questions out of order: %+v", got.Questions)
	}

	forms, err := store.ListForms(ctx, false)
	if err != nil {
		t.Fatalf("ListForms: %v", err)
	}
	if len(forms) != 1 || forms[0].QuestionCount != 2 {
		t.Fatalf("ListForms = %+v", forms)
	}

	got.Title = "Updated title"
	got.Questions = got.Questions[:1]
	got.UpdatedAt = time.Now().UTC()
	if err := store.UpdateForm(ctx, got); err != nil {
		t.Fatalf("UpdateForm: %v", err)
	}
	updated, err := store.GetForm(ctx, form.ID)
	if err != nil {
		t.Fatalf("GetForm after update: %v", err)
	}
	if updated.Title != "Updated title" || len(updated.Questions) != 1 {
		t.Fatalf("update not applied: %+v", updated)
	}

	if err := store.SetFormActive(ctx, form.ID, false); err != nil {
		t.Fatalf("SetFormActive: %v", err)
	}
	active, err := store.ListForms(ctx, false)
	if err != nil {
		t.Fatalf("ListForms active: %v", err)
	}
	if len(active) != 0 {
		t.Fatalf("expected no active forms, got %+v", active)
	}
	all, err := store.ListForms(ctx, true)
	if err != nil {
		t.Fatalf("ListForms all: %v", err)
	}
	if len(all) != 1 || all[0].IsActive {
		t.Fatalf("expected one inactive form, got %+v", all)
	}

	if err := store.DeleteForm(ctx, form.ID); err != nil {
		t.Fatalf("DeleteForm: %v", err)
	}
	if _, err := store.GetForm(ctx, form.ID); !errors.Is(err, ErrNotFound) {
		t.Fatalf("expected ErrNotFound, got %v", err)
	}
}

func TestSubmissionsAndAccessCodes(t *testing.T) {
	store, _ := testStore(t)
	ctx := context.Background()

	form := sampleForm()
	form.RequiresAccessCode = true
	if err := store.CreateForm(ctx, form); err != nil {
		t.Fatalf("CreateForm: %v", err)
	}

	now := time.Now().UTC()
	code := AccessCode{
		ID:        "55555555-5555-5555-5555-555555555555",
		FormID:    form.ID,
		Code:      "TEST-ABCD-EFGH",
		CreatedAt: now,
	}
	if err := store.CreateAccessCodes(ctx, []AccessCode{code}); err != nil {
		t.Fatalf("CreateAccessCodes: %v", err)
	}

	codes, err := store.ListAccessCodes(ctx, form.ID)
	if err != nil {
		t.Fatalf("ListAccessCodes: %v", err)
	}
	if len(codes) != 1 || codes[0].Used {
		t.Fatalf("unexpected codes: %+v", codes)
	}

	value, _ := json.Marshal(true)
	sub := &Submission{
		ID:          "66666666-6666-6666-6666-666666666666",
		FormID:      form.ID,
		FormVersion: form.Version,
		UserID:      "person@example.com",
		UserIDType:  "email",
		SubmittedAt: now,
		Answers: []Answer{
			{QuestionID: form.Questions[0].ID, QuestionKey: "happy", Value: value},
		},
	}

	if err := store.CreateSubmission(ctx, sub, "WRONG"); !errors.Is(err, ErrAccessCode) {
		t.Fatalf("expected ErrAccessCode, got %v", err)
	}
	if err := store.CreateSubmission(ctx, sub, code.Code); err != nil {
		t.Fatalf("CreateSubmission: %v", err)
	}

	got, err := store.GetSubmission(ctx, sub.ID)
	if err != nil {
		t.Fatalf("GetSubmission: %v", err)
	}
	if len(got.Answers) != 1 || got.Answers[0].QuestionKey != "happy" {
		t.Fatalf("unexpected submission: %+v", got)
	}

	codes, _ = store.ListAccessCodes(ctx, form.ID)
	if !codes[0].Used || codes[0].UsedAt == nil {
		t.Fatalf("access code should be marked used: %+v", codes[0])
	}

	reuse := *sub
	reuse.ID = "77777777-7777-7777-7777-777777777777"
	if err := store.CreateSubmission(ctx, &reuse, code.Code); !errors.Is(err, ErrCodeAlreadyIn) {
		t.Fatalf("expected ErrCodeAlreadyIn, got %v", err)
	}

	subs, err := store.ListSubmissions(ctx, form.ID)
	if err != nil {
		t.Fatalf("ListSubmissions: %v", err)
	}
	if len(subs) != 1 {
		t.Fatalf("expected 1 submission, got %d", len(subs))
	}

	if err := store.DeleteForm(ctx, form.ID); err != nil {
		t.Fatalf("DeleteForm with submissions: %v", err)
	}
	if _, err := store.GetSubmission(ctx, sub.ID); !errors.Is(err, ErrNotFound) {
		t.Fatalf("submission should cascade delete, got %v", err)
	}
	if codes, err := store.ListAccessCodes(ctx, form.ID); err != nil || len(codes) != 0 {
		t.Fatalf("access codes should cascade delete, got %v err=%v", codes, err)
	}
}

func TestUnsupportedDriver(t *testing.T) {
	if _, err := Open("oracle", "dsn"); err == nil {
		t.Fatal("expected error for unsupported driver")
	}
}