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