Eric Bower
·
2026-01-08
storage_test.go
1package postgres
2
3import (
4 "context"
5 "fmt"
6 "log/slog"
7 "os"
8 "os/exec"
9 "path/filepath"
10 "runtime"
11 "sort"
12 "testing"
13 "time"
14
15 "github.com/jmoiron/sqlx"
16 _ "github.com/lib/pq"
17 "github.com/picosh/pico/pkg/db"
18 "github.com/testcontainers/testcontainers-go"
19 "github.com/testcontainers/testcontainers-go/modules/postgres"
20 "github.com/testcontainers/testcontainers-go/wait"
21)
22
23var testDB *PsqlDB
24var testLogger *slog.Logger
25var skipTests bool
26
27func setupContainerRuntime() bool {
28 // Check if DATABASE_URL is set for external postgres (CI/CD or manual testing)
29 if os.Getenv("TEST_DATABASE_URL") != "" {
30 return true
31 }
32
33 // Try podman first
34 if cmd := exec.Command("podman", "info"); cmd.Run() == nil {
35 // For podman, we need to ensure the socket is running
36 // User should run: systemctl --user start podman.socket
37 _ = os.Setenv("TESTCONTAINERS_RYUK_DISABLED", "true")
38
39 // Check if socket exists and is accessible
40 xdgRuntime := os.Getenv("XDG_RUNTIME_DIR")
41 if xdgRuntime != "" {
42 socketPath := xdgRuntime + "/podman/podman.sock"
43 if _, err := os.Stat(socketPath); err == nil {
44 _ = os.Setenv("DOCKER_HOST", "unix://"+socketPath)
45 return true
46 }
47 }
48 // Socket not available, need to start it
49 fmt.Println("Podman detected but socket not running. Run: systemctl --user start podman.socket")
50 return false
51 }
52
53 // Try docker
54 if cmd := exec.Command("docker", "info"); cmd.Run() == nil {
55 return true
56 }
57
58 return false
59}
60
61func TestMain(m *testing.M) {
62 ctx := context.Background()
63 testLogger = slog.New(slog.NewTextHandler(os.Stdout, &slog.HandlerOptions{Level: slog.LevelError}))
64
65 // Check for external database URL first (for CI/CD or manual testing)
66 if dbURL := os.Getenv("TEST_DATABASE_URL"); dbURL != "" {
67 testDB = NewDB(dbURL, testLogger)
68 if err := setupTestSchema(testDB.Db); err != nil {
69 panic(fmt.Sprintf("failed to setup schema: %s", err))
70 }
71 code := m.Run()
72 _ = testDB.Close()
73 os.Exit(code)
74 }
75
76 if !setupContainerRuntime() {
77 fmt.Println("Container runtime not available, skipping integration tests")
78 fmt.Println("To run tests, either:")
79 fmt.Println(" - Set TEST_DATABASE_URL to a postgres connection string")
80 fmt.Println(" - Start podman socket: systemctl --user start podman.socket")
81 fmt.Println(" - Start docker daemon")
82 skipTests = true
83 os.Exit(0)
84 }
85
86 pgContainer, err := postgres.Run(ctx,
87 "postgres:14",
88 postgres.WithDatabase("pico_test"),
89 postgres.WithUsername("postgres"),
90 postgres.WithPassword("postgres"),
91 testcontainers.WithWaitStrategy(
92 wait.ForLog("database system is ready to accept connections").
93 WithOccurrence(2).
94 WithStartupTimeout(30*time.Second)),
95 )
96 if err != nil {
97 fmt.Printf("Failed to start postgres container (Docker may not be running): %s\n", err)
98 skipTests = true
99 os.Exit(0)
100 }
101
102 connStr, err := pgContainer.ConnectionString(ctx, "sslmode=disable")
103 if err != nil {
104 panic(fmt.Sprintf("failed to get connection string: %s", err))
105 }
106
107 testDB = NewDB(connStr, testLogger)
108
109 if err := setupTestSchema(testDB.Db); err != nil {
110 panic(fmt.Sprintf("failed to setup schema: %s", err))
111 }
112
113 code := m.Run()
114
115 _ = testDB.Close()
116 _ = pgContainer.Terminate(ctx)
117
118 os.Exit(code)
119}
120
121func getProjectRoot() string {
122 _, filename, _, ok := runtime.Caller(0)
123 if !ok {
124 panic("failed to get current file path")
125 }
126 // storage_test.go is in pkg/db/postgres/, so go up 4 levels to get project root
127 return filepath.Join(filepath.Dir(filename), "..", "..", "..")
128}
129
130func setupTestSchema(db *sqlx.DB) error {
131 projectRoot := getProjectRoot()
132 migrationsDir := filepath.Join(projectRoot, "sql", "migrations")
133
134 // Read all migration files
135 entries, err := os.ReadDir(migrationsDir)
136 if err != nil {
137 return fmt.Errorf("failed to read migrations directory: %w", err)
138 }
139
140 // Sort by filename (they're date-prefixed, so alphabetical order is correct)
141 var migrationFiles []string
142 for _, entry := range entries {
143 if !entry.IsDir() && filepath.Ext(entry.Name()) == ".sql" {
144 migrationFiles = append(migrationFiles, entry.Name())
145 }
146 }
147 sort.Strings(migrationFiles)
148
149 // Execute each migration in order
150 for _, filename := range migrationFiles {
151 migrationPath := filepath.Join(migrationsDir, filename)
152 content, err := os.ReadFile(migrationPath)
153 if err != nil {
154 return fmt.Errorf("failed to read migration %s: %w", filename, err)
155 }
156
157 _, err = db.Exec(string(content))
158 if err != nil {
159 return fmt.Errorf("failed to execute migration %s: %w", filename, err)
160 }
161 }
162
163 return nil
164}
165
166func cleanupTestData(t *testing.T) {
167 t.Helper()
168 tables := []string{
169 "access_logs", "tuns_event_logs", "analytics_visits",
170 "feed_items", "post_aliases", "post_tags", "posts",
171 "projects", "feature_flags", "payment_history", "tokens",
172 "public_keys", "pipe_monitors", "app_users",
173 }
174 for _, table := range tables {
175 _, err := testDB.Db.Exec(fmt.Sprintf("DELETE FROM %s", table))
176 if err != nil {
177 t.Fatalf("failed to clean up %s: %v", table, err)
178 }
179 }
180}
181
182func mustInsertPost(t *testing.T, post *db.Post) *db.Post {
183 t.Helper()
184 now := time.Now()
185 if post.UpdatedAt == nil {
186 post.UpdatedAt = &now
187 }
188 if post.PublishAt == nil {
189 post.PublishAt = &now
190 }
191 created, err := testDB.InsertPost(post)
192 if err != nil {
193 t.Fatalf("InsertPost failed: %v", err)
194 }
195 return created
196}
197
198// ============ User Management Tests ============
199
200func TestRegisterUser_Success(t *testing.T) {
201 cleanupTestData(t)
202
203 user, err := testDB.RegisterUser("testuser", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI testkey", "test comment")
204 if err != nil {
205 t.Fatalf("RegisterUser failed: %v", err)
206 return
207 }
208 if user == nil {
209 t.Fatal("expected user, got nil")
210 return
211 }
212 if user.Name != "testuser" {
213 t.Errorf("expected name 'testuser', got '%s'", user.Name)
214 }
215 if user.PublicKey == nil {
216 t.Fatal("expected public key, got nil")
217 }
218}
219
220func TestRegisterUser_DuplicateName(t *testing.T) {
221 cleanupTestData(t)
222
223 _, err := testDB.RegisterUser("duplicateuser", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI key1", "comment1")
224 if err != nil {
225 t.Fatalf("first RegisterUser failed: %v", err)
226 }
227
228 _, err = testDB.RegisterUser("duplicateuser", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI key2", "comment2")
229 if err == nil {
230 t.Error("expected error for duplicate name, got nil")
231 }
232}
233
234func TestRegisterUser_DeniedName(t *testing.T) {
235 cleanupTestData(t)
236
237 _, err := testDB.RegisterUser("admin", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI adminkey", "comment")
238 if err == nil {
239 t.Error("expected error for denied name 'admin', got nil")
240 }
241}
242
243func TestRegisterUser_InvalidName(t *testing.T) {
244 cleanupTestData(t)
245
246 _, err := testDB.RegisterUser("user@invalid", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI invalidkey", "comment")
247 if err == nil {
248 t.Error("expected error for invalid name, got nil")
249 }
250}
251
252func TestFindUser(t *testing.T) {
253 cleanupTestData(t)
254
255 created, err := testDB.RegisterUser("findme", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findmekey", "comment")
256 if err != nil {
257 t.Fatalf("RegisterUser failed: %v", err)
258 }
259
260 found, err := testDB.FindUser(created.ID)
261 if err != nil {
262 t.Fatalf("FindUser failed: %v", err)
263 }
264 if found.Name != "findme" {
265 t.Errorf("expected name 'findme', got '%s'", found.Name)
266 }
267}
268
269func TestFindUserByName(t *testing.T) {
270 cleanupTestData(t)
271
272 _, err := testDB.RegisterUser("nameduser", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI namedkey", "comment")
273 if err != nil {
274 t.Fatalf("RegisterUser failed: %v", err)
275 }
276
277 found, err := testDB.FindUserByName("nameduser")
278 if err != nil {
279 t.Fatalf("FindUserByName failed: %v", err)
280 }
281 if found.Name != "nameduser" {
282 t.Errorf("expected name 'nameduser', got '%s'", found.Name)
283 }
284}
285
286func TestFindUserByPubkey(t *testing.T) {
287 cleanupTestData(t)
288
289 pubkey := "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pubkeyuser"
290 _, err := testDB.RegisterUser("pubkeyuser", pubkey, "comment")
291 if err != nil {
292 t.Fatalf("RegisterUser failed: %v", err)
293 }
294
295 found, err := testDB.FindUserByPubkey(pubkey)
296 if err != nil {
297 t.Fatalf("FindUserByPubkey failed: %v", err)
298 }
299 if found.Name != "pubkeyuser" {
300 t.Errorf("expected name 'pubkeyuser', got '%s'", found.Name)
301 }
302}
303
304func TestFindUserByKey(t *testing.T) {
305 cleanupTestData(t)
306
307 pubkey := "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI forkeyfind"
308 _, err := testDB.RegisterUser("forkeyfind", pubkey, "comment")
309 if err != nil {
310 t.Fatalf("RegisterUser failed: %v", err)
311 }
312
313 found, err := testDB.FindUserByKey("forkeyfind", pubkey)
314 if err != nil {
315 t.Fatalf("FindUserByKey failed: %v", err)
316 }
317 if found.Name != "forkeyfind" {
318 t.Errorf("expected name 'forkeyfind', got '%s'", found.Name)
319 }
320}
321
322func TestFindUsers(t *testing.T) {
323 cleanupTestData(t)
324
325 _, _ = testDB.RegisterUser("user1", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI user1key", "comment")
326 _, _ = testDB.RegisterUser("user2", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI user2key", "comment")
327
328 users, err := testDB.FindUsers()
329 if err != nil {
330 t.Fatalf("FindUsers failed: %v", err)
331 }
332 if len(users) != 2 {
333 t.Errorf("expected 2 users, got %d", len(users))
334 }
335}
336
337// ============ Public Key Management Tests ============
338
339func TestInsertPublicKey_Success(t *testing.T) {
340 cleanupTestData(t)
341
342 user, err := testDB.RegisterUser("keyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI keyowner1", "comment")
343 if err != nil {
344 t.Fatalf("RegisterUser failed: %v", err)
345 }
346
347 err = testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI secondkey", "second key")
348 if err != nil {
349 t.Fatalf("InsertPublicKey failed: %v", err)
350 }
351
352 keys, err := testDB.FindKeysByUser(user)
353 if err != nil {
354 t.Fatalf("FindKeysByUser failed: %v", err)
355 }
356 if len(keys) != 2 {
357 t.Errorf("expected 2 keys, got %d", len(keys))
358 }
359}
360
361func TestInsertPublicKey_Duplicate(t *testing.T) {
362 cleanupTestData(t)
363
364 user, _ := testDB.RegisterUser("dupkeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI dupkey", "comment")
365
366 err := testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI dupkey", "same key")
367 if err == nil {
368 t.Error("expected error for duplicate key, got nil")
369 }
370}
371
372func TestUpdatePublicKey(t *testing.T) {
373 cleanupTestData(t)
374
375 user, _ := testDB.RegisterUser("updatekeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI updatekeyowner", "original")
376
377 updated, err := testDB.UpdatePublicKey(user.PublicKey.ID, "new-name")
378 if err != nil {
379 t.Fatalf("UpdatePublicKey failed: %v", err)
380 }
381 if updated.Name != "new-name" {
382 t.Errorf("expected name 'new-name', got '%s'", updated.Name)
383 }
384}
385
386func TestFindKeysByUser(t *testing.T) {
387 cleanupTestData(t)
388
389 user, _ := testDB.RegisterUser("multikeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI multikeyowner1", "key1")
390 _ = testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI multikeyowner2", "key2")
391
392 keys, err := testDB.FindKeysByUser(user)
393 if err != nil {
394 t.Fatalf("FindKeysByUser failed: %v", err)
395 }
396 if len(keys) != 2 {
397 t.Errorf("expected 2 keys, got %d", len(keys))
398 }
399}
400
401func TestRemoveKeys(t *testing.T) {
402 cleanupTestData(t)
403
404 user, _ := testDB.RegisterUser("removekeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI removekeyowner", "key1")
405 _ = testDB.InsertPublicKey(user.ID, "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI removekeyowner2", "key2")
406
407 keys, _ := testDB.FindKeysByUser(user)
408 if len(keys) != 2 {
409 t.Fatalf("expected 2 keys before removal, got %d", len(keys))
410 }
411
412 err := testDB.RemoveKeys([]string{keys[1].ID})
413 if err != nil {
414 t.Fatalf("RemoveKeys failed: %v", err)
415 }
416
417 keys, _ = testDB.FindKeysByUser(user)
418 if len(keys) != 1 {
419 t.Errorf("expected 1 key after removal, got %d", len(keys))
420 }
421}
422
423// ============ Token Management Tests ============
424
425func TestInsertToken(t *testing.T) {
426 cleanupTestData(t)
427
428 user, _ := testDB.RegisterUser("tokenowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tokenowner", "comment")
429
430 token, err := testDB.InsertToken(user.ID, "my-token")
431 if err != nil {
432 t.Fatalf("InsertToken failed: %v", err)
433 }
434 if token == "" {
435 t.Error("expected token string, got empty")
436 }
437}
438
439func TestUpsertToken(t *testing.T) {
440 cleanupTestData(t)
441
442 user, _ := testDB.RegisterUser("upserttokenowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI upserttokenowner", "comment")
443
444 token1, err := testDB.UpsertToken(user.ID, "upsert-token")
445 if err != nil {
446 t.Fatalf("first UpsertToken failed: %v", err)
447 }
448
449 token2, err := testDB.UpsertToken(user.ID, "upsert-token")
450 if err != nil {
451 t.Fatalf("second UpsertToken failed: %v", err)
452 }
453
454 if token1 != token2 {
455 t.Errorf("expected same token, got different: %s vs %s", token1, token2)
456 }
457}
458
459func TestFindTokensByUser(t *testing.T) {
460 cleanupTestData(t)
461
462 user, _ := testDB.RegisterUser("tokensowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tokensowner", "comment")
463 _, _ = testDB.InsertToken(user.ID, "token1")
464 _, _ = testDB.InsertToken(user.ID, "token2")
465
466 tokens, err := testDB.FindTokensByUser(user.ID)
467 if err != nil {
468 t.Fatalf("FindTokensByUser failed: %v", err)
469 }
470 if len(tokens) != 2 {
471 t.Errorf("expected 2 tokens, got %d", len(tokens))
472 }
473}
474
475func TestFindUserByToken_Valid(t *testing.T) {
476 cleanupTestData(t)
477
478 user, _ := testDB.RegisterUser("validtokenowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI validtokenowner", "comment")
479 token, _ := testDB.InsertToken(user.ID, "valid-token")
480
481 found, err := testDB.FindUserByToken(token)
482 if err != nil {
483 t.Fatalf("FindUserByToken failed: %v", err)
484 }
485 if found.Name != "validtokenowner" {
486 t.Errorf("expected name 'validtokenowner', got '%s'", found.Name)
487 }
488}
489
490func TestFindUserByToken_Expired(t *testing.T) {
491 cleanupTestData(t)
492
493 user, _ := testDB.RegisterUser("expiredtokenowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI expiredtokenowner", "comment")
494 token, _ := testDB.InsertToken(user.ID, "expired-token")
495
496 _, err := testDB.Db.Exec("UPDATE tokens SET expires_at = NOW() - INTERVAL '1 day' WHERE token = $1", token)
497 if err != nil {
498 t.Fatalf("failed to expire token: %v", err)
499 }
500
501 _, err = testDB.FindUserByToken(token)
502 if err == nil {
503 t.Error("expected error for expired token, got nil")
504 }
505}
506
507func TestRemoveToken(t *testing.T) {
508 cleanupTestData(t)
509
510 user, _ := testDB.RegisterUser("removetokenowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI removetokenowner", "comment")
511 _, _ = testDB.InsertToken(user.ID, "remove-token")
512
513 tokens, _ := testDB.FindTokensByUser(user.ID)
514 if len(tokens) != 1 {
515 t.Fatalf("expected 1 token, got %d", len(tokens))
516 }
517
518 err := testDB.RemoveToken(tokens[0].ID)
519 if err != nil {
520 t.Fatalf("RemoveToken failed: %v", err)
521 }
522
523 tokens, _ = testDB.FindTokensByUser(user.ID)
524 if len(tokens) != 0 {
525 t.Errorf("expected 0 tokens after removal, got %d", len(tokens))
526 }
527}
528
529// ============ Post CRUD Tests ============
530
531func TestInsertPost(t *testing.T) {
532 cleanupTestData(t)
533
534 user, _ := testDB.RegisterUser("postowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI postowner", "comment")
535
536 now := time.Now()
537 post := &db.Post{
538 UserID: user.ID,
539 Filename: "test.md",
540 Slug: "test-post",
541 Title: "Test Post",
542 Text: "Post content",
543 Description: "A test post",
544 PublishAt: &now,
545 UpdatedAt: &now,
546 Space: "prose",
547 MimeType: "text/markdown",
548 }
549
550 created, err := testDB.InsertPost(post)
551 if err != nil {
552 t.Fatalf("InsertPost failed: %v", err)
553 }
554 if created.ID == "" {
555 t.Error("expected post ID, got empty")
556 }
557 if created.Title != "Test Post" {
558 t.Errorf("expected title 'Test Post', got '%s'", created.Title)
559 }
560}
561
562func TestUpdatePost(t *testing.T) {
563 cleanupTestData(t)
564
565 user, _ := testDB.RegisterUser("updatepostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI updatepostowner", "comment")
566
567 now := time.Now()
568 post := mustInsertPost(t, &db.Post{
569 UserID: user.ID,
570 Filename: "update.md",
571 Slug: "update-post",
572 Title: "Original Title",
573 Text: "Original content",
574 Space: "prose",
575 })
576
577 post.Title = "Updated Title"
578 post.Text = "Updated content"
579 post.UpdatedAt = &now
580
581 updated, err := testDB.UpdatePost(post)
582 if err != nil {
583 t.Fatalf("UpdatePost failed: %v", err)
584 }
585 if updated.Title != "Updated Title" {
586 t.Errorf("expected title 'Updated Title', got '%s'", updated.Title)
587 }
588}
589
590func TestRemovePosts(t *testing.T) {
591 cleanupTestData(t)
592
593 user, _ := testDB.RegisterUser("removepostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI removepostowner", "comment")
594
595 post := mustInsertPost(t, &db.Post{
596 UserID: user.ID,
597 Filename: "remove.md",
598 Slug: "remove-post",
599 Title: "To Remove",
600 Space: "prose",
601 })
602
603 err := testDB.RemovePosts([]string{post.ID})
604 if err != nil {
605 t.Fatalf("RemovePosts failed: %v", err)
606 }
607
608 _, err = testDB.FindPost(post.ID)
609 if err == nil {
610 t.Error("expected error finding removed post, got nil")
611 }
612}
613
614func TestFindPost(t *testing.T) {
615 cleanupTestData(t)
616
617 user, _ := testDB.RegisterUser("findpostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findpostowner", "comment")
618
619 created := mustInsertPost(t, &db.Post{
620 UserID: user.ID,
621 Filename: "find.md",
622 Slug: "find-post",
623 Title: "Find Me",
624 Space: "prose",
625 })
626
627 found, err := testDB.FindPost(created.ID)
628 if err != nil {
629 t.Fatalf("FindPost failed: %v", err)
630 }
631 if found.Title != "Find Me" {
632 t.Errorf("expected title 'Find Me', got '%s'", found.Title)
633 }
634}
635
636func TestFindPostWithFilename(t *testing.T) {
637 cleanupTestData(t)
638
639 user, _ := testDB.RegisterUser("filenamepostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI filenamepostowner", "comment")
640
641 _ = mustInsertPost(t, &db.Post{
642 UserID: user.ID,
643 Filename: "byfilename.md",
644 Slug: "byfilename-post",
645 Title: "By Filename",
646 Space: "prose",
647 })
648
649 found, err := testDB.FindPostWithFilename("byfilename.md", user.ID, "prose")
650 if err != nil {
651 t.Fatalf("FindPostWithFilename failed: %v", err)
652 }
653 if found.Title != "By Filename" {
654 t.Errorf("expected title 'By Filename', got '%s'", found.Title)
655 }
656}
657
658func TestFindPostWithSlug(t *testing.T) {
659 cleanupTestData(t)
660
661 user, _ := testDB.RegisterUser("slugpostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI slugpostowner", "comment")
662
663 _ = mustInsertPost(t, &db.Post{
664 UserID: user.ID,
665 Filename: "byslug.md",
666 Slug: "byslug-post",
667 Title: "By Slug",
668 Space: "prose",
669 })
670
671 found, err := testDB.FindPostWithSlug("byslug-post", user.ID, "prose")
672 if err != nil {
673 t.Fatalf("FindPostWithSlug failed: %v", err)
674 }
675 if found.Title != "By Slug" {
676 t.Errorf("expected title 'By Slug', got '%s'", found.Title)
677 }
678}
679
680func TestFindPosts(t *testing.T) {
681 cleanupTestData(t)
682
683 user, _ := testDB.RegisterUser("postsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI postsowner", "comment")
684
685 _ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "post1.md", Slug: "post1", Title: "Post 1", Space: "prose"})
686 _ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "post2.md", Slug: "post2", Title: "Post 2", Space: "prose"})
687
688 posts, err := testDB.FindPosts()
689 if err != nil {
690 t.Fatalf("FindPosts failed: %v", err)
691 }
692 if len(posts) != 2 {
693 t.Errorf("expected 2 posts, got %d", len(posts))
694 }
695}
696
697func TestFindPostsByUser(t *testing.T) {
698 cleanupTestData(t)
699
700 user, _ := testDB.RegisterUser("userpostsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI userpostsowner", "comment")
701 now := time.Now()
702
703 _ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "userpost1.md", Slug: "userpost1", Title: "User Post 1", Space: "prose", PublishAt: &now})
704 _ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "userpost2.md", Slug: "userpost2", Title: "User Post 2", Space: "prose", PublishAt: &now})
705
706 pager := &db.Pager{Num: 10, Page: 0}
707 result, err := testDB.FindPostsByUser(pager, user.ID, "prose")
708 if err != nil {
709 t.Fatalf("FindPostsByUser failed: %v", err)
710 }
711 if len(result.Data) != 2 {
712 t.Errorf("expected 2 posts, got %d", len(result.Data))
713 }
714}
715
716func TestFindAllPostsByUser(t *testing.T) {
717 cleanupTestData(t)
718
719 user, _ := testDB.RegisterUser("allpostsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI allpostsowner", "comment")
720
721 _ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "allpost1.md", Slug: "allpost1", Title: "All Post 1", Space: "prose"})
722 _ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "allpost2.md", Slug: "allpost2", Title: "All Post 2", Space: "prose"})
723
724 posts, err := testDB.FindAllPostsByUser(user.ID, "prose")
725 if err != nil {
726 t.Fatalf("FindAllPostsByUser failed: %v", err)
727 }
728 if len(posts) != 2 {
729 t.Errorf("expected 2 posts, got %d", len(posts))
730 }
731}
732
733func TestFindUsersWithPost(t *testing.T) {
734 cleanupTestData(t)
735
736 user, _ := testDB.RegisterUser("feedspostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI feedspostowner", "comment")
737
738 _ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "feed.txt", Slug: "feed", Title: "Feed", Space: "feeds"})
739
740 users, err := testDB.FindUsersWithPost("feeds")
741 if err != nil {
742 t.Fatalf("FindUsersWithPost failed: %v", err)
743 }
744 if len(users) != 1 {
745 t.Errorf("expected 1 user, got %d", len(users))
746 }
747}
748
749func TestFindExpiredPosts(t *testing.T) {
750 cleanupTestData(t)
751
752 user, _ := testDB.RegisterUser("expiredpostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI expiredpostowner", "comment")
753
754 expired := time.Now().Add(-24 * time.Hour)
755 _ = mustInsertPost(t, &db.Post{
756 UserID: user.ID,
757 Filename: "expired.txt",
758 Slug: "expired",
759 Title: "Expired",
760 Space: "pastes",
761 ExpiresAt: &expired,
762 })
763
764 posts, err := testDB.FindExpiredPosts("pastes")
765 if err != nil {
766 t.Fatalf("FindExpiredPosts failed: %v", err)
767 }
768 if len(posts) != 1 {
769 t.Errorf("expected 1 expired post, got %d", len(posts))
770 }
771}
772
773func TestFindPostsByFeed(t *testing.T) {
774 cleanupTestData(t)
775
776 user, _ := testDB.RegisterUser("feedowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI feedowner", "comment")
777
778 now := time.Now()
779 _ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "feedpost.md", Slug: "feedpost", Title: "Feed Post", Space: "prose", PublishAt: &now})
780
781 pager := &db.Pager{Num: 10, Page: 0}
782 result, err := testDB.FindPostsByFeed(pager, "prose")
783 if err != nil {
784 t.Fatalf("FindPostsByFeed failed: %v", err)
785 }
786 if len(result.Data) < 1 {
787 t.Errorf("expected at least 1 post in feed, got %d", len(result.Data))
788 }
789}
790
791// ============ Tags Tests ============
792
793func TestReplaceTagsByPost(t *testing.T) {
794 cleanupTestData(t)
795
796 user, _ := testDB.RegisterUser("tagspostowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tagspostowner", "comment")
797 post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "tagged.md", Slug: "tagged", Title: "Tagged", Space: "prose"})
798
799 err := testDB.ReplaceTagsByPost([]string{"tag1", "tag2"}, post.ID)
800 if err != nil {
801 t.Fatalf("ReplaceTagsByPost failed: %v", err)
802 }
803
804 found, _ := testDB.FindPostWithFilename("tagged.md", user.ID, "prose")
805 if len(found.Tags) != 2 {
806 t.Errorf("expected 2 tags, got %d", len(found.Tags))
807 }
808}
809
810func TestFindUserPostsByTag(t *testing.T) {
811 cleanupTestData(t)
812
813 user, _ := testDB.RegisterUser("usertagowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI usertagowner", "comment")
814 now := time.Now()
815 post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "usertag.md", Slug: "usertag", Title: "User Tag", Space: "prose", PublishAt: &now})
816 _ = testDB.ReplaceTagsByPost([]string{"mytag"}, post.ID)
817
818 pager := &db.Pager{Num: 10, Page: 0}
819 result, err := testDB.FindUserPostsByTag(pager, "mytag", user.ID, "prose")
820 if err != nil {
821 t.Fatalf("FindUserPostsByTag failed: %v", err)
822 }
823 if len(result.Data) != 1 {
824 t.Errorf("expected 1 post with tag, got %d", len(result.Data))
825 }
826}
827
828func TestFindPostsByTag(t *testing.T) {
829 cleanupTestData(t)
830
831 user, _ := testDB.RegisterUser("tagsearchowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tagsearchowner", "comment")
832 now := time.Now()
833 post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "tagsearch.md", Slug: "tagsearch", Title: "Tag Search", Space: "prose", PublishAt: &now})
834 _ = testDB.ReplaceTagsByPost([]string{"searchtag"}, post.ID)
835
836 pager := &db.Pager{Num: 10, Page: 0}
837 result, err := testDB.FindPostsByTag(pager, "searchtag", "prose")
838 if err != nil {
839 t.Fatalf("FindPostsByTag failed: %v", err)
840 }
841 if len(result.Data) != 1 {
842 t.Errorf("expected 1 post with tag, got %d", len(result.Data))
843 }
844}
845
846func TestFindPopularTags(t *testing.T) {
847 cleanupTestData(t)
848
849 user, _ := testDB.RegisterUser("populartagowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI populartagowner", "comment")
850 post1 := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "pop1.md", Slug: "pop1", Title: "Pop 1", Space: "prose"})
851 post2 := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "pop2.md", Slug: "pop2", Title: "Pop 2", Space: "prose"})
852 _ = testDB.ReplaceTagsByPost([]string{"popular"}, post1.ID)
853 _ = testDB.ReplaceTagsByPost([]string{"popular"}, post2.ID)
854
855 tags, err := testDB.FindPopularTags("prose")
856 if err != nil {
857 t.Fatalf("FindPopularTags failed: %v", err)
858 }
859 if len(tags) < 1 {
860 t.Errorf("expected at least 1 popular tag, got %d", len(tags))
861 }
862}
863
864// ============ Aliases Tests ============
865
866func TestReplaceAliasesByPost(t *testing.T) {
867 cleanupTestData(t)
868
869 user, _ := testDB.RegisterUser("aliasowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI aliasowner", "comment")
870 post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "aliased.md", Slug: "aliased", Title: "Aliased", Space: "prose"})
871
872 err := testDB.ReplaceAliasesByPost([]string{"alias1", "alias2"}, post.ID)
873 if err != nil {
874 t.Fatalf("ReplaceAliasesByPost failed: %v", err)
875 }
876}
877
878func TestFindPostWithSlug_Alias(t *testing.T) {
879 cleanupTestData(t)
880
881 user, _ := testDB.RegisterUser("aliassearchowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI aliassearchowner", "comment")
882 post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "original.md", Slug: "original", Title: "Original", Space: "prose"})
883 _ = testDB.ReplaceAliasesByPost([]string{"my-alias"}, post.ID)
884
885 found, err := testDB.FindPostWithSlug("my-alias", user.ID, "prose")
886 if err != nil {
887 t.Fatalf("FindPostWithSlug for alias failed: %v", err)
888 }
889 if found.Title != "Original" {
890 t.Errorf("expected title 'Original', got '%s'", found.Title)
891 }
892}
893
894// ============ Analytics Tests ============
895
896func TestInsertVisit(t *testing.T) {
897 cleanupTestData(t)
898
899 user, _ := testDB.RegisterUser("visitowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI visitowner", "comment")
900
901 visit := &db.AnalyticsVisits{
902 UserID: user.ID,
903 Host: "example.com",
904 Path: "/test",
905 IpAddress: "192.168.1.1",
906 UserAgent: "TestAgent/1.0",
907 Referer: "https://referrer.com",
908 Status: 200,
909 }
910
911 err := testDB.InsertVisit(visit)
912 if err != nil {
913 t.Fatalf("InsertVisit failed: %v", err)
914 }
915}
916
917func TestVisitSummary(t *testing.T) {
918 cleanupTestData(t)
919
920 user, _ := testDB.RegisterUser("summaryowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI summaryowner", "comment")
921
922 visit := &db.AnalyticsVisits{
923 UserID: user.ID,
924 Host: "summary.com",
925 Path: "/page",
926 IpAddress: "192.168.1.2",
927 Status: 200,
928 }
929 _ = testDB.InsertVisit(visit)
930
931 opts := &db.SummaryOpts{
932 Interval: "day",
933 Origin: time.Now().Add(-24 * time.Hour),
934 Host: "summary.com",
935 UserID: user.ID,
936 }
937
938 summary, err := testDB.VisitSummary(opts)
939 if err != nil {
940 t.Fatalf("VisitSummary failed: %v", err)
941 }
942 if summary == nil {
943 t.Error("expected summary, got nil")
944 }
945}
946
947func TestFindVisitSiteList(t *testing.T) {
948 cleanupTestData(t)
949
950 user, _ := testDB.RegisterUser("sitelistowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI sitelistowner", "comment")
951
952 _ = testDB.InsertVisit(&db.AnalyticsVisits{
953 UserID: user.ID,
954 Host: "site1.com",
955 Path: "/",
956 IpAddress: "192.168.1.3",
957 Status: 200,
958 })
959
960 opts := &db.SummaryOpts{UserID: user.ID}
961 sites, err := testDB.FindVisitSiteList(opts)
962 if err != nil {
963 t.Fatalf("FindVisitSiteList failed: %v", err)
964 }
965 if len(sites) < 1 {
966 t.Errorf("expected at least 1 site, got %d", len(sites))
967 }
968}
969
970func TestVisitUrlNotFound(t *testing.T) {
971 cleanupTestData(t)
972
973 user, _ := testDB.RegisterUser("notfoundowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI notfoundowner", "comment")
974
975 _ = testDB.InsertVisit(&db.AnalyticsVisits{
976 UserID: user.ID,
977 Host: "notfound.com",
978 Path: "/missing",
979 IpAddress: "192.168.1.4",
980 Status: 404,
981 })
982
983 opts := &db.SummaryOpts{
984 Origin: time.Now().Add(-24 * time.Hour),
985 Host: "notfound.com",
986 UserID: user.ID,
987 }
988 notFound, err := testDB.VisitUrlNotFound(opts)
989 if err != nil {
990 t.Fatalf("VisitUrlNotFound failed: %v", err)
991 }
992 if len(notFound) < 1 {
993 t.Errorf("expected at least 1 not found URL, got %d", len(notFound))
994 }
995}
996
997// ============ Features Tests ============
998
999func TestInsertFeature(t *testing.T) {
1000 cleanupTestData(t)
1001
1002 user, _ := testDB.RegisterUser("featureowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI featureowner", "comment")
1003
1004 expiresAt := time.Now().Add(365 * 24 * time.Hour)
1005 feature, err := testDB.InsertFeature(user.ID, "plus", expiresAt)
1006 if err != nil {
1007 t.Fatalf("InsertFeature failed: %v", err)
1008 }
1009 if feature.Name != "plus" {
1010 t.Errorf("expected feature name 'plus', got '%s'", feature.Name)
1011 }
1012}
1013
1014func TestFindFeature(t *testing.T) {
1015 cleanupTestData(t)
1016
1017 user, _ := testDB.RegisterUser("findfeatureowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findfeatureowner", "comment")
1018 expiresAt := time.Now().Add(365 * 24 * time.Hour)
1019 _, _ = testDB.InsertFeature(user.ID, "plus", expiresAt)
1020
1021 feature, err := testDB.FindFeature(user.ID, "plus")
1022 if err != nil {
1023 t.Fatalf("FindFeature failed: %v", err)
1024 }
1025 if feature.Name != "plus" {
1026 t.Errorf("expected feature name 'plus', got '%s'", feature.Name)
1027 }
1028}
1029
1030func TestFindFeaturesByUser(t *testing.T) {
1031 cleanupTestData(t)
1032
1033 user, _ := testDB.RegisterUser("featuresowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI featuresowner", "comment")
1034 expiresAt := time.Now().Add(365 * 24 * time.Hour)
1035 _, _ = testDB.InsertFeature(user.ID, "plus", expiresAt)
1036 _, _ = testDB.InsertFeature(user.ID, "pro", expiresAt)
1037
1038 features, err := testDB.FindFeaturesByUser(user.ID)
1039 if err != nil {
1040 t.Fatalf("FindFeaturesByUser failed: %v", err)
1041 }
1042 if len(features) != 2 {
1043 t.Errorf("expected 2 features, got %d", len(features))
1044 }
1045}
1046
1047func TestHasFeatureByUser(t *testing.T) {
1048 cleanupTestData(t)
1049
1050 user, _ := testDB.RegisterUser("hasfeatureowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI hasfeatureowner", "comment")
1051 expiresAt := time.Now().Add(365 * 24 * time.Hour)
1052 _, _ = testDB.InsertFeature(user.ID, "plus", expiresAt)
1053
1054 has := testDB.HasFeatureByUser(user.ID, "plus")
1055 if !has {
1056 t.Error("expected HasFeatureByUser to return true")
1057 }
1058
1059 hasNot := testDB.HasFeatureByUser(user.ID, "nonexistent")
1060 if hasNot {
1061 t.Error("expected HasFeatureByUser to return false for nonexistent feature")
1062 }
1063}
1064
1065func TestRemoveFeature(t *testing.T) {
1066 cleanupTestData(t)
1067
1068 user, _ := testDB.RegisterUser("removefeatureowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI removefeatureowner", "comment")
1069 expiresAt := time.Now().Add(365 * 24 * time.Hour)
1070 _, _ = testDB.InsertFeature(user.ID, "plus", expiresAt)
1071
1072 err := testDB.RemoveFeature(user.ID, "plus")
1073 if err != nil {
1074 t.Fatalf("RemoveFeature failed: %v", err)
1075 }
1076
1077 _, err = testDB.FindFeature(user.ID, "plus")
1078 if err == nil {
1079 t.Error("expected error finding removed feature, got nil")
1080 }
1081}
1082
1083func TestAddPicoPlusUser(t *testing.T) {
1084 cleanupTestData(t)
1085
1086 _, _ = testDB.RegisterUser("picoplusowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI picoplusowner", "comment")
1087
1088 err := testDB.AddPicoPlusUser("picoplusowner", "test@example.com", "stripe", "tx123")
1089 if err != nil {
1090 t.Fatalf("AddPicoPlusUser failed: %v", err)
1091 }
1092}
1093
1094// ============ Feed Items Tests ============
1095
1096func TestInsertFeedItems(t *testing.T) {
1097 cleanupTestData(t)
1098
1099 user, _ := testDB.RegisterUser("feeditemsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI feeditemsowner", "comment")
1100 post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "feed.txt", Slug: "feed", Title: "Feed", Space: "feeds"})
1101
1102 items := []*db.FeedItem{
1103 {PostID: post.ID, GUID: "guid-1", Data: db.FeedItemData{Title: "Item 1", Link: "http://example.com/1"}},
1104 {PostID: post.ID, GUID: "guid-2", Data: db.FeedItemData{Title: "Item 2", Link: "http://example.com/2"}},
1105 }
1106
1107 err := testDB.InsertFeedItems(post.ID, items)
1108 if err != nil {
1109 t.Fatalf("InsertFeedItems failed: %v", err)
1110 }
1111}
1112
1113func TestFindFeedItemsByPostID(t *testing.T) {
1114 cleanupTestData(t)
1115
1116 user, _ := testDB.RegisterUser("findfeeditemsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findfeeditemsowner", "comment")
1117 post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "findfeed.txt", Slug: "findfeed", Title: "Find Feed", Space: "feeds"})
1118
1119 items := []*db.FeedItem{
1120 {PostID: post.ID, GUID: "find-guid-1", Data: db.FeedItemData{Title: "Find Item 1"}},
1121 }
1122 _ = testDB.InsertFeedItems(post.ID, items)
1123
1124 found, err := testDB.FindFeedItemsByPostID(post.ID)
1125 if err != nil {
1126 t.Fatalf("FindFeedItemsByPostID failed: %v", err)
1127 }
1128 if len(found) != 1 {
1129 t.Errorf("expected 1 feed item, got %d", len(found))
1130 }
1131}
1132
1133// ============ Projects Tests ============
1134
1135func TestUpsertProject(t *testing.T) {
1136 cleanupTestData(t)
1137
1138 user, _ := testDB.RegisterUser("projectowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI projectowner", "comment")
1139
1140 project, err := testDB.UpsertProject(user.ID, "my-project", "my-project")
1141 if err != nil {
1142 t.Fatalf("UpsertProject failed: %v", err)
1143 }
1144 if project.Name != "my-project" {
1145 t.Errorf("expected project name 'my-project', got '%s'", project.Name)
1146 }
1147
1148 project2, err := testDB.UpsertProject(user.ID, "my-project", "my-project")
1149 if err != nil {
1150 t.Fatalf("UpsertProject (update) failed: %v", err)
1151 }
1152 if project2.ID != project.ID {
1153 t.Error("expected same project ID on upsert")
1154 }
1155}
1156
1157func TestFindProjectByName(t *testing.T) {
1158 cleanupTestData(t)
1159
1160 user, _ := testDB.RegisterUser("findprojectowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findprojectowner", "comment")
1161 _, _ = testDB.UpsertProject(user.ID, "findme-project", "findme-project")
1162
1163 project, err := testDB.FindProjectByName(user.ID, "findme-project")
1164 if err != nil {
1165 t.Fatalf("FindProjectByName failed: %v", err)
1166 }
1167 if project.Name != "findme-project" {
1168 t.Errorf("expected project name 'findme-project', got '%s'", project.Name)
1169 }
1170}
1171
1172// ============ User Stats Tests ============
1173
1174func TestFindUserStats(t *testing.T) {
1175 cleanupTestData(t)
1176
1177 user, _ := testDB.RegisterUser("statsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI statsowner", "comment")
1178 _ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "stat.md", Slug: "stat", Title: "Stat", Space: "prose"})
1179 _, _ = testDB.UpsertProject(user.ID, "stat-project", "stat-project")
1180
1181 stats, err := testDB.FindUserStats(user.ID)
1182 if err != nil {
1183 t.Fatalf("FindUserStats failed: %v", err)
1184 }
1185 if stats.Prose.Num != 1 {
1186 t.Errorf("expected 1 prose post, got %d", stats.Prose.Num)
1187 }
1188 if stats.Pages.Num != 1 {
1189 t.Errorf("expected 1 project, got %d", stats.Pages.Num)
1190 }
1191}
1192
1193// ============ Tuns Event Logs Tests ============
1194
1195func TestInsertTunsEventLog(t *testing.T) {
1196 cleanupTestData(t)
1197
1198 user, _ := testDB.RegisterUser("tunsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tunsowner", "comment")
1199
1200 log := &db.TunsEventLog{
1201 UserId: user.ID,
1202 ServerID: "server-1",
1203 RemoteAddr: "192.168.1.1:1234",
1204 EventType: "connect",
1205 TunnelType: "http",
1206 ConnectionType: "tcp",
1207 TunnelID: "tunnel-123",
1208 }
1209
1210 err := testDB.InsertTunsEventLog(log)
1211 if err != nil {
1212 t.Fatalf("InsertTunsEventLog failed: %v", err)
1213 }
1214}
1215
1216func TestFindTunsEventLogs(t *testing.T) {
1217 cleanupTestData(t)
1218
1219 user, _ := testDB.RegisterUser("findtunsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findtunsowner", "comment")
1220 _ = testDB.InsertTunsEventLog(&db.TunsEventLog{
1221 UserId: user.ID,
1222 ServerID: "server-1",
1223 RemoteAddr: "192.168.1.1:1234",
1224 EventType: "connect",
1225 TunnelType: "http",
1226 ConnectionType: "tcp",
1227 TunnelID: "tunnel-456",
1228 })
1229
1230 logs, err := testDB.FindTunsEventLogs(user.ID)
1231 if err != nil {
1232 t.Fatalf("FindTunsEventLogs failed: %v", err)
1233 }
1234 if len(logs) != 1 {
1235 t.Errorf("expected 1 log, got %d", len(logs))
1236 }
1237}
1238
1239func TestFindTunsEventLogsByAddr(t *testing.T) {
1240 cleanupTestData(t)
1241
1242 user, _ := testDB.RegisterUser("tunsaddrowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tunsaddrowner", "comment")
1243 _ = testDB.InsertTunsEventLog(&db.TunsEventLog{
1244 UserId: user.ID,
1245 ServerID: "server-1",
1246 RemoteAddr: "192.168.1.1:1234",
1247 EventType: "connect",
1248 TunnelType: "http",
1249 ConnectionType: "tcp",
1250 TunnelID: "tunnel-789",
1251 })
1252
1253 logs, err := testDB.FindTunsEventLogsByAddr(user.ID, "tunnel-789")
1254 if err != nil {
1255 t.Fatalf("FindTunsEventLogsByAddr failed: %v", err)
1256 }
1257 if len(logs) != 1 {
1258 t.Errorf("expected 1 log, got %d", len(logs))
1259 }
1260}
1261
1262// ============ Access Logs Tests ============
1263
1264func TestInsertAccessLog(t *testing.T) {
1265 cleanupTestData(t)
1266
1267 user, _ := testDB.RegisterUser("accesslogowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI accesslogowner", "comment")
1268
1269 log := &db.AccessLog{
1270 UserID: user.ID,
1271 Service: "pgs",
1272 Pubkey: "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI accesslogowner",
1273 Identity: "accesslogowner",
1274 }
1275
1276 err := testDB.InsertAccessLog(log)
1277 if err != nil {
1278 t.Fatalf("InsertAccessLog failed: %v", err)
1279 }
1280}
1281
1282func TestFindAccessLogs(t *testing.T) {
1283 cleanupTestData(t)
1284
1285 user, _ := testDB.RegisterUser("findaccesslogowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findaccesslogowner", "comment")
1286 _ = testDB.InsertAccessLog(&db.AccessLog{
1287 UserID: user.ID,
1288 Service: "pgs",
1289 Pubkey: "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findaccesslogowner",
1290 Identity: "findaccesslogowner",
1291 })
1292
1293 fromDate := time.Now().Add(-24 * time.Hour)
1294 logs, err := testDB.FindAccessLogs(user.ID, &fromDate)
1295 if err != nil {
1296 t.Fatalf("FindAccessLogs failed: %v", err)
1297 }
1298 if len(logs) != 1 {
1299 t.Errorf("expected 1 log, got %d", len(logs))
1300 }
1301}
1302
1303func TestFindPubkeysInAccessLogs(t *testing.T) {
1304 cleanupTestData(t)
1305
1306 user, _ := testDB.RegisterUser("pubkeylogowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pubkeylogowner", "comment")
1307 _ = testDB.InsertAccessLog(&db.AccessLog{
1308 UserID: user.ID,
1309 Service: "pgs",
1310 Pubkey: "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pubkeylogowner",
1311 Identity: "pubkeylogowner",
1312 })
1313
1314 pubkeys, err := testDB.FindPubkeysInAccessLogs(user.ID)
1315 if err != nil {
1316 t.Fatalf("FindPubkeysInAccessLogs failed: %v", err)
1317 }
1318 if len(pubkeys) != 1 {
1319 t.Errorf("expected 1 pubkey, got %d", len(pubkeys))
1320 }
1321}
1322
1323func TestFindAccessLogsByPubkey(t *testing.T) {
1324 cleanupTestData(t)
1325
1326 user, _ := testDB.RegisterUser("accessbypubkeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI accessbypubkeyowner", "comment")
1327 pubkey := "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI accessbypubkeyowner"
1328 _ = testDB.InsertAccessLog(&db.AccessLog{
1329 UserID: user.ID,
1330 Service: "pgs",
1331 Pubkey: pubkey,
1332 Identity: "accessbypubkeyowner",
1333 })
1334
1335 fromDate := time.Now().Add(-24 * time.Hour)
1336 logs, err := testDB.FindAccessLogsByPubkey(pubkey, &fromDate)
1337 if err != nil {
1338 t.Fatalf("FindAccessLogsByPubkey failed: %v", err)
1339 }
1340 if len(logs) != 1 {
1341 t.Errorf("expected 1 log, got %d", len(logs))
1342 }
1343}
1344
1345// ============ JSONB Roundtrip Tests ============
1346
1347func TestPostData_JSONBRoundtrip(t *testing.T) {
1348 cleanupTestData(t)
1349
1350 user, _ := testDB.RegisterUser("postdataowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI postdataowner", "comment")
1351
1352 now := time.Now().Truncate(time.Second)
1353 postData := db.PostData{
1354 ImgPath: "/images/test.png",
1355 LastDigest: &now,
1356 Attempts: 5,
1357 }
1358
1359 post := mustInsertPost(t, &db.Post{
1360 UserID: user.ID,
1361 Filename: "jsonb.md",
1362 Slug: "jsonb",
1363 Title: "JSONB Test",
1364 Space: "prose",
1365 Data: postData,
1366 })
1367
1368 found, err := testDB.FindPost(post.ID)
1369 if err != nil {
1370 t.Fatalf("FindPost failed: %v", err)
1371 }
1372 if found.Data.ImgPath != "/images/test.png" {
1373 t.Errorf("expected ImgPath '/images/test.png', got '%s'", found.Data.ImgPath)
1374 }
1375 if found.Data.Attempts != 5 {
1376 t.Errorf("expected Attempts 5, got %d", found.Data.Attempts)
1377 }
1378}
1379
1380func TestProjectAcl_JSONBRoundtrip(t *testing.T) {
1381 cleanupTestData(t)
1382
1383 user, _ := testDB.RegisterUser("aclowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI aclowner", "comment")
1384
1385 project, err := testDB.UpsertProject(user.ID, "acl-project", "acl-project")
1386 if err != nil {
1387 t.Fatalf("UpsertProject failed: %v", err)
1388 }
1389
1390 found, err := testDB.FindProjectByName(user.ID, "acl-project")
1391 if err != nil {
1392 t.Fatalf("FindProjectByName failed: %v", err)
1393 }
1394 if found.Acl.Type != "public" {
1395 t.Errorf("expected Acl.Type 'public', got '%s'", found.Acl.Type)
1396 }
1397 _ = project
1398}
1399
1400func TestFeedItemData_JSONBRoundtrip(t *testing.T) {
1401 cleanupTestData(t)
1402
1403 user, _ := testDB.RegisterUser("feedjsonbowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI feedjsonbowner", "comment")
1404 post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "feedjsonb.txt", Slug: "feedjsonb", Title: "Feed JSONB", Space: "feeds"})
1405
1406 now := time.Now().Truncate(time.Second)
1407 items := []*db.FeedItem{
1408 {
1409 PostID: post.ID,
1410 GUID: "jsonb-guid",
1411 Data: db.FeedItemData{
1412 Title: "JSONB Item",
1413 Description: "Description",
1414 Content: "Content",
1415 Link: "http://example.com",
1416 PublishedAt: &now,
1417 },
1418 },
1419 }
1420 _ = testDB.InsertFeedItems(post.ID, items)
1421
1422 found, err := testDB.FindFeedItemsByPostID(post.ID)
1423 if err != nil {
1424 t.Fatalf("FindFeedItemsByPostID failed: %v", err)
1425 }
1426 if len(found) != 1 {
1427 t.Fatalf("expected 1 item, got %d", len(found))
1428 }
1429 if found[0].Data.Title != "JSONB Item" {
1430 t.Errorf("expected title 'JSONB Item', got '%s'", found[0].Data.Title)
1431 }
1432}
1433
1434func TestFeatureFlagData_JSONBRoundtrip(t *testing.T) {
1435 cleanupTestData(t)
1436
1437 user, _ := testDB.RegisterUser("featurejsonbowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI featurejsonbowner", "comment")
1438
1439 err := testDB.AddPicoPlusUser("featurejsonbowner", "test@example.com", "stripe", "tx456")
1440 if err != nil {
1441 t.Fatalf("AddPicoPlusUser failed: %v", err)
1442 }
1443
1444 feature, err := testDB.FindFeature(user.ID, "plus")
1445 if err != nil {
1446 t.Fatalf("FindFeature failed: %v", err)
1447 }
1448 if feature.Data.StorageMax != 10000000000 {
1449 t.Errorf("expected StorageMax 10000000000, got %d", feature.Data.StorageMax)
1450 }
1451 if feature.Data.FileMax != 50000000 {
1452 t.Errorf("expected FileMax 50000000, got %d", feature.Data.FileMax)
1453 }
1454}
1455
1456func TestPaymentHistoryData_JSONBRoundtrip(t *testing.T) {
1457 cleanupTestData(t)
1458
1459 user, _ := testDB.RegisterUser("paymentjsonbowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI paymentjsonbowner", "comment")
1460
1461 err := testDB.AddPicoPlusUser("paymentjsonbowner", "payment@example.com", "stripe", "tx789")
1462 if err != nil {
1463 t.Fatalf("AddPicoPlusUser failed: %v", err)
1464 }
1465
1466 var txId string
1467 err = testDB.Db.QueryRow("SELECT data->>'tx_id' FROM payment_history WHERE user_id = $1", user.ID).Scan(&txId)
1468 if err != nil {
1469 t.Fatalf("failed to query payment history: %v", err)
1470 }
1471 if txId != "tx789" {
1472 t.Errorf("expected tx_id 'tx789', got '%s'", txId)
1473 }
1474}
1475
1476// ============ Pipe Monitor Tests ============
1477
1478func TestUpsertPipeMonitor(t *testing.T) {
1479 cleanupTestData(t)
1480
1481 user, _ := testDB.RegisterUser("pipemonitorowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pipemonitorowner", "comment")
1482
1483 winEnd := time.Now().Add(time.Hour)
1484 err := testDB.UpsertPipeMonitor(user.ID, "test-topic", 5*time.Minute, &winEnd)
1485 if err != nil {
1486 t.Fatalf("UpsertPipeMonitor failed: %v", err)
1487 }
1488
1489 monitor, err := testDB.FindPipeMonitorByTopic(user.ID, "test-topic")
1490 if err != nil {
1491 t.Fatalf("FindPipeMonitorByTopic failed: %v", err)
1492 }
1493 if monitor.Topic != "test-topic" {
1494 t.Errorf("expected topic 'test-topic', got '%s'", monitor.Topic)
1495 }
1496 if monitor.WindowDur != 5*time.Minute {
1497 t.Errorf("expected window_dur 5m, got %v", monitor.WindowDur)
1498 }
1499}
1500
1501func TestUpsertPipeMonitor_Update(t *testing.T) {
1502 cleanupTestData(t)
1503
1504 user, _ := testDB.RegisterUser("pipeupdateowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pipeupdateowner", "comment")
1505
1506 winEnd1 := time.Now().Add(time.Hour)
1507 err := testDB.UpsertPipeMonitor(user.ID, "update-topic", 5*time.Minute, &winEnd1)
1508 if err != nil {
1509 t.Fatalf("first UpsertPipeMonitor failed: %v", err)
1510 }
1511
1512 winEnd2 := time.Now().Add(2 * time.Hour)
1513 err = testDB.UpsertPipeMonitor(user.ID, "update-topic", 10*time.Minute, &winEnd2)
1514 if err != nil {
1515 t.Fatalf("second UpsertPipeMonitor failed: %v", err)
1516 }
1517
1518 monitor, err := testDB.FindPipeMonitorByTopic(user.ID, "update-topic")
1519 if err != nil {
1520 t.Fatalf("FindPipeMonitorByTopic failed: %v", err)
1521 }
1522 if monitor.WindowDur != 10*time.Minute {
1523 t.Errorf("expected window_dur 10m after update, got %v", monitor.WindowDur)
1524 }
1525}
1526
1527func TestUpdatePipeMonitorLastPing(t *testing.T) {
1528 cleanupTestData(t)
1529
1530 user, _ := testDB.RegisterUser("pipepingowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pipepingowner", "comment")
1531
1532 winEnd := time.Now().Add(time.Hour)
1533 err := testDB.UpsertPipeMonitor(user.ID, "ping-topic", 5*time.Minute, &winEnd)
1534 if err != nil {
1535 t.Fatalf("UpsertPipeMonitor failed: %v", err)
1536 }
1537
1538 lastPing := time.Now()
1539 err = testDB.UpdatePipeMonitorLastPing(user.ID, "ping-topic", &lastPing)
1540 if err != nil {
1541 t.Fatalf("UpdatePipeMonitorLastPing failed: %v", err)
1542 }
1543
1544 monitor, err := testDB.FindPipeMonitorByTopic(user.ID, "ping-topic")
1545 if err != nil {
1546 t.Fatalf("FindPipeMonitorByTopic failed: %v", err)
1547 }
1548 if monitor.LastPing == nil {
1549 t.Error("expected last_ping to be set, got nil")
1550 }
1551}
1552
1553func TestRemovePipeMonitor(t *testing.T) {
1554 cleanupTestData(t)
1555
1556 user, _ := testDB.RegisterUser("piperemoveowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI piperemoveowner", "comment")
1557
1558 winEnd := time.Now().Add(time.Hour)
1559 err := testDB.UpsertPipeMonitor(user.ID, "remove-topic", 5*time.Minute, &winEnd)
1560 if err != nil {
1561 t.Fatalf("UpsertPipeMonitor failed: %v", err)
1562 }
1563
1564 err = testDB.RemovePipeMonitor(user.ID, "remove-topic")
1565 if err != nil {
1566 t.Fatalf("RemovePipeMonitor failed: %v", err)
1567 }
1568
1569 _, err = testDB.FindPipeMonitorByTopic(user.ID, "remove-topic")
1570 if err == nil {
1571 t.Error("expected error finding removed monitor, got nil")
1572 }
1573}
1574
1575func TestFindPipeMonitorByTopic_NotFound(t *testing.T) {
1576 cleanupTestData(t)
1577
1578 user, _ := testDB.RegisterUser("pipenotfoundowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pipenotfoundowner", "comment")
1579
1580 _, err := testDB.FindPipeMonitorByTopic(user.ID, "nonexistent-topic")
1581 if err == nil {
1582 t.Error("expected error for nonexistent monitor, got nil")
1583 }
1584}
1585
1586func TestFindPipeMonitorsByUser(t *testing.T) {
1587 cleanupTestData(t)
1588
1589 user, _ := testDB.RegisterUser("pipemonlistowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pipemonlistowner", "comment")
1590
1591 winEnd := time.Now().Add(time.Hour)
1592 _ = testDB.UpsertPipeMonitor(user.ID, "service-a", 5*time.Minute, &winEnd)
1593 _ = testDB.UpsertPipeMonitor(user.ID, "service-b", 10*time.Minute, &winEnd)
1594 _ = testDB.UpsertPipeMonitor(user.ID, "service-c", 1*time.Hour, &winEnd)
1595
1596 monitors, err := testDB.FindPipeMonitorsByUser(user.ID)
1597 if err != nil {
1598 t.Fatalf("FindPipeMonitorsByUser failed: %v", err)
1599 }
1600
1601 if len(monitors) != 3 {
1602 t.Errorf("expected 3 monitors, got %d", len(monitors))
1603 }
1604
1605 // Should be ordered by topic
1606 if monitors[0].Topic != "service-a" {
1607 t.Errorf("expected first topic 'service-a', got %s", monitors[0].Topic)
1608 }
1609 if monitors[1].Topic != "service-b" {
1610 t.Errorf("expected second topic 'service-b', got %s", monitors[1].Topic)
1611 }
1612 if monitors[2].Topic != "service-c" {
1613 t.Errorf("expected third topic 'service-c', got %s", monitors[2].Topic)
1614 }
1615}
1616
1617func TestFindPipeMonitorsByUser_Empty(t *testing.T) {
1618 cleanupTestData(t)
1619
1620 user, _ := testDB.RegisterUser("pipenomonitors", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pipenomonitors", "comment")
1621
1622 monitors, err := testDB.FindPipeMonitorsByUser(user.ID)
1623 if err != nil {
1624 t.Fatalf("FindPipeMonitorsByUser failed: %v", err)
1625 }
1626
1627 if len(monitors) != 0 {
1628 t.Errorf("expected 0 monitors for user with none, got %d", len(monitors))
1629 }
1630}