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

package database

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"os"
	"path/filepath"
	"strings"
	"time"

	_ "modernc.org/sqlite"
)

// Open creates a Store for the requested driver. SQLite is fully supported;
// the driver switch is the extension point for PostgreSQL and MariaDB.
func Open(driver, dsn string) (Store, error) {
	switch strings.ToLower(strings.TrimSpace(driver)) {
	case "", "sqlite", "sqlite3":
		return openSQLite(dsn)
	default:
		return nil, fmt.Errorf("unsupported database driver %q (supported: sqlite)", driver)
	}
}

type sqliteStore struct {
	db *sql.DB
}

func openSQLite(dsn string) (*sqliteStore, error) {
	if err := ensureSQLiteDir(dsn); err != nil {
		return nil, err
	}
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("open sqlite: %w", err)
	}
	// SQLite allows a single writer; a small pool keeps behaviour predictable.
	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)
	db.SetConnMaxLifetime(0)

	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()
	if err := db.PingContext(ctx); err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("ping sqlite: %w", err)
	}

	store := &sqliteStore{db: db}
	if err := store.migrate(ctx); err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("migrate sqlite: %w", err)
	}
	return store, nil
}

func ensureSQLiteDir(dsn string) error {
	path := dsn
	if idx := strings.Index(path, "?"); idx >= 0 {
		path = path[:idx]
	}
	path = strings.TrimPrefix(path, "file:")
	if path == "" || path == ":memory:" || strings.HasPrefix(path, ":memory:") {
		return nil
	}
	dir := filepath.Dir(path)
	if dir == "." || dir == "" {
		return nil
	}
	if err := os.MkdirAll(dir, 0o755); err != nil {
		return fmt.Errorf("create database directory %s: %w", dir, err)
	}
	return nil
}

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

func (s *sqliteStore) Ping(ctx context.Context) error { return s.db.PingContext(ctx) }

func formatTime(t time.Time) string { return t.UTC().Format(time.RFC3339Nano) }

func parseTime(v string) time.Time {
	t, err := time.Parse(time.RFC3339Nano, v)
	if err != nil {
		return time.Time{}
	}
	return t.UTC()
}

func nullString(v *string) sql.NullString {
	if v == nil || *v == "" {
		return sql.NullString{}
	}
	return sql.NullString{String: *v, Valid: true}
}

func fromNullString(v sql.NullString) *string {
	if !v.Valid {
		return nil
	}
	s := v.String
	return &s
}

func boolToInt(b bool) int {
	if b {
		return 1
	}
	return 0
}

func (s *sqliteStore) CreateForm(ctx context.Context, form *Form) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	if err := insertForm(ctx, tx, form); err != nil {
		return err
	}
	for i := range form.Questions {
		if err := insertQuestion(ctx, tx, &form.Questions[i]); err != nil {
			return err
		}
	}
	return tx.Commit()
}

func (s *sqliteStore) UpdateForm(ctx context.Context, form *Form) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	res, err := tx.ExecContext(ctx, `UPDATE forms SET
		title = ?, description = ?, introduction = ?, is_active = ?,
		requires_access_code = ?, signature_required = ?,
		external_reference_required = ?, external_reference_label = ?,
		logo_url = ?, pdf_url = ?, company_name = ?, contact_email = ?,
		contact_phone = ?, contact_url = ?, updated_at = ?
		WHERE id = ?`,
		form.Title, nullString(form.Description), nullString(form.Introduction),
		boolToInt(form.IsActive), boolToInt(form.RequiresAccessCode),
		boolToInt(form.SignatureRequired), boolToInt(form.ExternalReferenceRequired),
		nullString(form.ExternalReferenceLabel), nullString(form.LogoURL),
		nullString(form.PDFURL), nullString(form.CompanyName),
		nullString(form.ContactEmail), nullString(form.ContactPhone),
		nullString(form.ContactURL), formatTime(form.UpdatedAt), form.ID,
	)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}

		if _, err := tx.ExecContext(ctx, `DELETE FROM questions WHERE form_id = ?`, form.ID); err != nil {
			return err
		}
		if _, err := tx.ExecContext(ctx, `DELETE FROM sections WHERE form_id = ?`, form.ID); err != nil {
			return err
		}
		if err := s.saveSections(ctx, tx, form.ID, form.Sections); err != nil {
			return err
		}
	for i := range form.Questions {
		form.Questions[i].FormID = form.ID
		if err := insertQuestion(ctx, tx, &form.Questions[i]); err != nil {
			return err
		}
	}
	return tx.Commit()
}

func (s *sqliteStore) saveSections(ctx context.Context, tx *sql.Tx, formID string, sections []Section) error {
	for i, sec := range sections {
		if sec.ID == "" {
			sec.ID = newID()
		}
		if _, err := tx.ExecContext(ctx, `INSERT INTO sections (id, form_id, position, identifier, title, description) VALUES (?, ?, ?, ?, ?, ?)`,
			sec.ID, formID, i, sec.Identifier, nullString(sec.Title), nullString(sec.Description),
		); err != nil {
			return err
		}
	}
	return nil
}

func insertForm(ctx context.Context, tx *sql.Tx, form *Form) error {
	_, err := tx.ExecContext(ctx, `INSERT INTO forms (
		id, form_group_id, version, title, description, introduction, is_active,
		requires_access_code, signature_required, external_reference_required,
		external_reference_label, logo_url, pdf_url, company_name, contact_email,
		contact_phone, contact_url, created_at, updated_at
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		form.ID, form.FormGroupID, form.Version, form.Title,
		nullString(form.Description), nullString(form.Introduction),
		boolToInt(form.IsActive), boolToInt(form.RequiresAccessCode),
		boolToInt(form.SignatureRequired), boolToInt(form.ExternalReferenceRequired),
		nullString(form.ExternalReferenceLabel), nullString(form.LogoURL),
		nullString(form.PDFURL), nullString(form.CompanyName),
		nullString(form.ContactEmail), nullString(form.ContactPhone),
		nullString(form.ContactURL), formatTime(form.CreatedAt), formatTime(form.UpdatedAt),
	)
	if err != nil {
		if strings.Contains(err.Error(), "UNIQUE") {
			return ErrConflict
		}
		return err
	}
	return nil
}

func insertQuestion(ctx context.Context, tx *sql.Tx, q *Question) error {
	options, err := json.Marshal(q.Options)
	if err != nil {
		return err
	}
	var minV, maxV sql.NullFloat64
	if q.MinValue != nil {
		minV = sql.NullFloat64{Float64: *q.MinValue, Valid: true}
	}
	if q.MaxValue != nil {
		maxV = sql.NullFloat64{Float64: *q.MaxValue, Valid: true}
	}
	var sectionID sql.NullString
	if q.SectionID != nil && *q.SectionID != "" {
		sectionID = sql.NullString{String: *q.SectionID, Valid: true}
	}
	_, err = tx.ExecContext(ctx, `INSERT INTO questions (
		id, form_id, position, question_key, label, description, type, required,
		options_json, min_value, max_value, placeholder, multiline, section_id
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		q.ID, q.FormID, q.Position, q.Key, q.Label, nullString(q.Description),
		q.Type, boolToInt(q.Required), string(options), minV, maxV,
		nullString(q.Placeholder), boolToInt(q.Multiline), sectionID,
	)
	if err != nil {
		if strings.Contains(err.Error(), "UNIQUE") {
			return ErrConflict
		}
		return err
	}
	return nil
}

const formColumns = `id, form_group_id, version, title, description, introduction,
	is_active, requires_access_code, signature_required, external_reference_required,
	external_reference_label, logo_url, pdf_url, company_name, contact_email,
	contact_phone, contact_url, created_at, updated_at`

func (s *sqliteStore) GetForm(ctx context.Context, id string) (*Form, error) {
	row := s.db.QueryRowContext(ctx, `SELECT `+formColumns+` FROM forms WHERE id = ?`, id)
	form, err := scanForm(row)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	questions, err := s.listQuestions(ctx, id)
	if err != nil {
		return nil, err
	}
	sections, err := s.listSections(ctx, id)
	if err != nil {
		return nil, err
	}
	form.Questions = questions
	form.Sections = sections
	return form, nil
}

func scanForm(row interface{ Scan(...any) error }) (*Form, error) {
	var (
		form                                  Form
		description, introduction, logoURL    sql.NullString
		pdfURL, externalReferenceLabel        sql.NullString
		companyName, email, phone, contactURL sql.NullString
		createdAt, updatedAt                  string
		isActive, requiresAccessCode          int
		signatureRequired, externalRefReq     int
	)
	err := row.Scan(
		&form.ID, &form.FormGroupID, &form.Version, &form.Title,
		&description, &introduction, &isActive, &requiresAccessCode,
		&signatureRequired, &externalRefReq, &externalReferenceLabel,
		&logoURL, &pdfURL, &companyName, &email, &phone, &contactURL,
		&createdAt, &updatedAt,
	)
	if err != nil {
		return nil, err
	}
	form.Description = fromNullString(description)
	form.Introduction = fromNullString(introduction)
	form.LogoURL = fromNullString(logoURL)
	form.PDFURL = fromNullString(pdfURL)
	form.ExternalReferenceLabel = fromNullString(externalReferenceLabel)
	form.CompanyName = fromNullString(companyName)
	form.ContactEmail = fromNullString(email)
	form.ContactPhone = fromNullString(phone)
	form.ContactURL = fromNullString(contactURL)
	form.IsActive = isActive == 1
	form.RequiresAccessCode = requiresAccessCode == 1
	form.SignatureRequired = signatureRequired == 1
	form.ExternalReferenceRequired = externalRefReq == 1
	form.CreatedAt = parseTime(createdAt)
	form.UpdatedAt = parseTime(updatedAt)
	return &form, nil
}

func (s *sqliteStore) listQuestions(ctx context.Context, formID string) ([]Question, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT
		id, form_id, position, question_key, label, description, type, required,
		options_json, min_value, max_value, placeholder, multiline, section_id
		FROM questions WHERE form_id = ? ORDER BY position`, formID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var questions []Question
	for rows.Next() {
		var (
			q                        Question
			description, placeholder sql.NullString
			sectionID                sql.NullString
			minV, maxV               sql.NullFloat64
			opts                     string
			required, multiline      int
		)
		if err := rows.Scan(&q.ID, &q.FormID, &q.Position, &q.Key, &q.Label,
			&description, &q.Type, &required, &opts, &minV, &maxV, &placeholder,
			&multiline, &sectionID); err != nil {
			return nil, err
		}
		if sectionID.Valid && sectionID.String != "" {
			sid := sectionID.String
			q.SectionID = &sid
		}
		q.Description = fromNullString(description)
		q.Placeholder = fromNullString(placeholder)
		q.Required = required == 1
		q.Multiline = multiline == 1
		if minV.Valid {
			v := minV.Float64
			q.MinValue = &v
		}
		if maxV.Valid {
			v := maxV.Float64
			q.MaxValue = &v
		}
		if opts != "" && opts != "null" {
			if err := json.Unmarshal([]byte(opts), &q.Options); err != nil {
				return nil, fmt.Errorf("decode options for question %s: %w", q.ID, err)
			}
		}
		questions = append(questions, q)
	}
	return questions, rows.Err()
}

func (s *sqliteStore) listSections(ctx context.Context, formID string) ([]Section, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id, form_id, position, identifier, title, description FROM sections WHERE form_id = ? ORDER BY position`, formID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var sections []Section
	for rows.Next() {
		var (
			sec                Section
			title, description sql.NullString
		)
		if err := rows.Scan(&sec.ID, &sec.FormID, &sec.Position, &sec.Identifier, &title, &description); err != nil {
			return nil, err
		}
		sec.Title = fromNullString(title)
		sec.Description = fromNullString(description)
		sections = append(sections, sec)
	}
	return sections, rows.Err()
}

func (s *sqliteStore) ListForms(ctx context.Context, includeInactive bool) ([]FormSummary, error) {
	query := `SELECT f.id, f.form_group_id, f.version, f.title, f.description, f.logo_url,
		f.pdf_url, f.external_reference_required, f.external_reference_label,
		f.is_active, f.created_at, f.updated_at,
		(SELECT COUNT(*) FROM questions q WHERE q.form_id = f.id) AS question_count
		FROM forms f`
	if !includeInactive {
		query += ` WHERE f.is_active = 1`
	}
	query += ` ORDER BY f.updated_at DESC`

	rows, err := s.db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	summaries := make([]FormSummary, 0)
	for rows.Next() {
		var (
			summary             FormSummary
			description         sql.NullString
			logoURL             sql.NullString
			pdfURL              sql.NullString
			externalRefLabel    sql.NullString
			createdAt           string
			updatedAt           string
			isActive            int
			externalRefRequired int
		)
		if err := rows.Scan(&summary.ID, &summary.FormGroupID, &summary.Version,
			&summary.Title, &description, &logoURL, &pdfURL,
			&externalRefRequired, &externalRefLabel, &isActive, &createdAt, &updatedAt,
			&summary.QuestionCount); err != nil {
			return nil, err
		}
		summary.Description = fromNullString(description)
		summary.LogoURL = fromNullString(logoURL)
		summary.PDFURL = fromNullString(pdfURL)
		summary.ExternalReferenceLabel = fromNullString(externalRefLabel)
		summary.ExternalReferenceRequired = externalRefRequired == 1
		summary.IsActive = isActive == 1
		summary.CreatedAt = parseTime(createdAt)
		summary.UpdatedAt = parseTime(updatedAt)
		summaries = append(summaries, summary)
	}
	return summaries, rows.Err()
}

func (s *sqliteStore) SetFormActive(ctx context.Context, id string, active bool) error {
	res, err := s.db.ExecContext(ctx,
		`UPDATE forms SET is_active = ?, updated_at = ? WHERE id = ?`,
		boolToInt(active), formatTime(time.Now()), id)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	return nil
}

func (s *sqliteStore) DeleteForm(ctx context.Context, id string) error {
	res, err := s.db.ExecContext(ctx, `DELETE FROM forms WHERE id = ?`, id)
	if err != nil {
		return err
	}
	if n, _ := res.RowsAffected(); n == 0 {
		return ErrNotFound
	}
	return nil
}

func (s *sqliteStore) CreateSubmission(ctx context.Context, sub *Submission, accessCode string) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	var signatureJSON sql.NullString
	if sub.Signature != nil {
		encoded, err := json.Marshal(sub.Signature)
		if err != nil {
			return err
		}
		signatureJSON = sql.NullString{String: string(encoded), Valid: true}
	}
	var offlinePDF any
	if len(sub.OfflinePDF) > 0 {
		offlinePDF = sub.OfflinePDF
	}

	_, err = tx.ExecContext(ctx, `INSERT INTO submissions (
		id, form_id, form_version, user_id, user_id_type, submitted_at,
		ip_address, user_agent, locale, timezone, place, referer,
		external_reference, signature_json, offline_pdf_name, offline_pdf
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		sub.ID, sub.FormID, sub.FormVersion, sub.UserID, sub.UserIDType,
		formatTime(sub.SubmittedAt), nullString(sub.IPAddress),
		nullString(sub.UserAgent), nullString(sub.Locale), nullString(sub.Timezone),
		nullString(sub.Place), nullString(sub.Referer),
		nullString(sub.ExternalReference), signatureJSON,
		nullString(sub.OfflinePDFName), offlinePDF,
	)
	if err != nil {
		return err
	}

	for i := range sub.Answers {
		if _, err := tx.ExecContext(ctx, `INSERT INTO answers (
			id, submission_id, question_id, question_key, value_json
		) VALUES (?, ?, ?, ?, ?)`,
			newID(), sub.ID, sub.Answers[i].QuestionID,
			sub.Answers[i].QuestionKey, string(sub.Answers[i].Value),
		); err != nil {
			return err
		}
	}

	if accessCode != "" {
		res, err := tx.ExecContext(ctx, `UPDATE access_codes
			SET used = 1, used_at = ?, submission_id = ?
			WHERE form_id = ? AND code = ? AND used = 0`,
			formatTime(time.Now()), sub.ID, sub.FormID, accessCode)
		if err != nil {
			return err
		}
		if n, _ := res.RowsAffected(); n == 0 {
			var used int
			err := tx.QueryRowContext(ctx,
				`SELECT used FROM access_codes WHERE form_id = ? AND code = ?`,
				sub.FormID, accessCode).Scan(&used)
			if errors.Is(err, sql.ErrNoRows) {
				return ErrAccessCode
			}
			if err != nil {
				return err
			}
			return ErrCodeAlreadyIn
		}
	}

	return tx.Commit()
}

func (s *sqliteStore) GetSubmission(ctx context.Context, id string) (*Submission, error) {
	var (
		sub                                    Submission
		submittedAt                            string
		ipAddress, userAgent, locale, timezone sql.NullString
		place, referer                         sql.NullString
		signatureJSON                          sql.NullString
		offlinePDFName                         sql.NullString
		externalReference                      sql.NullString
	)
	err := s.db.QueryRowContext(ctx, `SELECT id, form_id, form_version, user_id,
		user_id_type, submitted_at, ip_address, user_agent, locale, timezone,
		place, referer, external_reference, signature_json, offline_pdf_name
		FROM submissions WHERE id = ?`, id).Scan(
		&sub.ID, &sub.FormID, &sub.FormVersion, &sub.UserID, &sub.UserIDType,
		&submittedAt, &ipAddress, &userAgent, &locale, &timezone, &place, &referer,
		&externalReference, &signatureJSON, &offlinePDFName,
	)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, err
	}
	sub.SubmittedAt = parseTime(submittedAt)
	sub.IPAddress = fromNullString(ipAddress)
	sub.UserAgent = fromNullString(userAgent)
	sub.Locale = fromNullString(locale)
	sub.Timezone = fromNullString(timezone)
	sub.Place = fromNullString(place)
	sub.Referer = fromNullString(referer)
	sub.ExternalReference = fromNullString(externalReference)
	sub.OfflinePDFName = fromNullString(offlinePDFName)
	if signatureJSON.Valid && signatureJSON.String != "" {
		var signature Signature
		if err := json.Unmarshal([]byte(signatureJSON.String), &signature); err != nil {
			return nil, fmt.Errorf("decode signature for submission %s: %w", id, err)
		}
		sub.Signature = &signature
	}

	rows, err := s.db.QueryContext(ctx, `SELECT question_id, question_key, value_json
		FROM answers WHERE submission_id = ?`, id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var (
			a         Answer
			valueJSON string
		)
		if err := rows.Scan(&a.QuestionID, &a.QuestionKey, &valueJSON); err != nil {
			return nil, err
		}
		a.Value = json.RawMessage(valueJSON)
		sub.Answers = append(sub.Answers, a)
	}
	return &sub, rows.Err()
}

func (s *sqliteStore) GetSubmissionOfflinePDF(ctx context.Context, id string) ([]byte, string, error) {
	var (
		data     []byte
		filename sql.NullString
	)
	err := s.db.QueryRowContext(ctx,
		`SELECT offline_pdf, offline_pdf_name FROM submissions WHERE id = ?`, id).
		Scan(&data, &filename)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, "", ErrNotFound
	}
	if err != nil {
		return nil, "", err
	}
	if len(data) == 0 {
		return nil, "", ErrNotFound
	}
	name := "submission.pdf"
	if filename.Valid && strings.TrimSpace(filename.String) != "" {
		name = filepath.Base(filename.String)
	}
	return data, name, nil
}

func (s *sqliteStore) ListSubmissions(ctx context.Context, formID string) ([]SubmissionSummary, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id, form_id, form_version,
		user_id, user_id_type, external_reference, submitted_at FROM submissions
		WHERE form_id = ? ORDER BY submitted_at DESC`, formID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	summaries := make([]SubmissionSummary, 0)
	for rows.Next() {
		var (
			summary           SubmissionSummary
			submittedAt       string
			externalReference sql.NullString
		)
		if err := rows.Scan(&summary.ID, &summary.FormID, &summary.FormVersion,
			&summary.UserID, &summary.UserIDType, &externalReference, &submittedAt); err != nil {
			return nil, err
		}
		summary.ExternalReference = fromNullString(externalReference)
		summary.SubmittedAt = parseTime(submittedAt)
		summaries = append(summaries, summary)
	}
	return summaries, rows.Err()
}

func (s *sqliteStore) CreateAccessCodes(ctx context.Context, codes []AccessCode) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer func() { _ = tx.Rollback() }()

	for _, code := range codes {
		_, err := tx.ExecContext(ctx, `INSERT INTO access_codes
			(id, form_id, code, used, created_at) VALUES (?, ?, ?, ?, ?)`,
			code.ID, code.FormID, code.Code, boolToInt(code.Used), formatTime(code.CreatedAt))
		if err != nil {
			if strings.Contains(err.Error(), "UNIQUE") {
				return ErrConflict
			}
			return err
		}
	}
	return tx.Commit()
}

func (s *sqliteStore) ListAccessCodes(ctx context.Context, formID string) ([]AccessCode, error) {
	rows, err := s.db.QueryContext(ctx, `SELECT id, form_id, code, used,
		used_at, created_at FROM access_codes WHERE form_id = ? ORDER BY created_at`, formID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	codes := make([]AccessCode, 0)
	for rows.Next() {
		var (
			code      AccessCode
			usedAt    sql.NullString
			createdAt string
			used      int
		)
		if err := rows.Scan(&code.ID, &code.FormID, &code.Code, &used,
			&usedAt, &createdAt); err != nil {
			return nil, err
		}
		code.Used = used == 1
		code.CreatedAt = parseTime(createdAt)
		if usedAt.Valid {
			t := parseTime(usedAt.String)
			code.UsedAt = &t
		}
		codes = append(codes, code)
	}
	return codes, rows.Err()
}