Eric Bower
·
2026-08-17
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
1084func TestAddFeatureUser(t *testing.T) {
1085 cleanupTestData(t)
1086
1087 user, _ := testDB.RegisterUser("featureuserowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI featureuserowner", "comment", "")
1088
1089 err := testDB.AddFeatureUser("featureuserowner", "pgs")
1090 if err != nil {
1091 t.Fatalf("AddFeatureUser failed: %v", err)
1092 }
1093
1094 ff, err := testDB.FindFeature(user.ID, "pgs")
1095 if err != nil {
1096 t.Fatalf("FindFeature failed: %v", err)
1097 }
1098 if ff.Name != "pgs" {
1099 t.Errorf("expected feature name pgs, got %s", ff.Name)
1100 }
1101 if !ff.IsValid() {
1102 t.Errorf("expected feature to be valid")
1103 }
1104}
1105
1106// ============ Feed Items Tests ============
1107
1108func TestInsertFeedItems(t *testing.T) {
1109 cleanupTestData(t)
1110
1111 user, _ := testDB.RegisterUser("feeditemsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI feeditemsowner", "comment", "")
1112 post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "feed.txt", Slug: "feed", Title: "Feed", Space: "feeds"})
1113
1114 items := []*db.FeedItem{
1115 {PostID: post.ID, GUID: "guid-1", Data: db.FeedItemData{Title: "Item 1", Link: "http://example.com/1"}},
1116 {PostID: post.ID, GUID: "guid-2", Data: db.FeedItemData{Title: "Item 2", Link: "http://example.com/2"}},
1117 }
1118
1119 err := testDB.InsertFeedItems(post.ID, items)
1120 if err != nil {
1121 t.Fatalf("InsertFeedItems failed: %v", err)
1122 }
1123}
1124
1125func TestFindFeedItemsByPostID(t *testing.T) {
1126 cleanupTestData(t)
1127
1128 user, _ := testDB.RegisterUser("findfeeditemsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findfeeditemsowner", "comment", "")
1129 post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "findfeed.txt", Slug: "findfeed", Title: "Find Feed", Space: "feeds"})
1130
1131 items := []*db.FeedItem{
1132 {PostID: post.ID, GUID: "find-guid-1", Data: db.FeedItemData{Title: "Find Item 1"}},
1133 }
1134 _ = testDB.InsertFeedItems(post.ID, items)
1135
1136 found, err := testDB.FindFeedItemsByPostID(post.ID)
1137 if err != nil {
1138 t.Fatalf("FindFeedItemsByPostID failed: %v", err)
1139 }
1140 if len(found) != 1 {
1141 t.Errorf("expected 1 feed item, got %d", len(found))
1142 }
1143}
1144
1145// ============ Projects Tests ============
1146
1147func TestUpsertProject(t *testing.T) {
1148 cleanupTestData(t)
1149
1150 user, _ := testDB.RegisterUser("projectowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI projectowner", "comment", "")
1151
1152 project, err := testDB.UpsertProject(user.ID, "my-project", "my-project")
1153 if err != nil {
1154 t.Fatalf("UpsertProject failed: %v", err)
1155 }
1156 if project.Name != "my-project" {
1157 t.Errorf("expected project name 'my-project', got '%s'", project.Name)
1158 }
1159
1160 project2, err := testDB.UpsertProject(user.ID, "my-project", "my-project")
1161 if err != nil {
1162 t.Fatalf("UpsertProject (update) failed: %v", err)
1163 }
1164 if project2.ID != project.ID {
1165 t.Error("expected same project ID on upsert")
1166 }
1167}
1168
1169func TestFindProjectByName(t *testing.T) {
1170 cleanupTestData(t)
1171
1172 user, _ := testDB.RegisterUser("findprojectowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findprojectowner", "comment", "")
1173 _, _ = testDB.UpsertProject(user.ID, "findme-project", "findme-project")
1174
1175 project, err := testDB.FindProjectByName(user.ID, "findme-project")
1176 if err != nil {
1177 t.Fatalf("FindProjectByName failed: %v", err)
1178 }
1179 if project.Name != "findme-project" {
1180 t.Errorf("expected project name 'findme-project', got '%s'", project.Name)
1181 }
1182}
1183
1184// ============ User Stats Tests ============
1185
1186func TestFindUserStats(t *testing.T) {
1187 cleanupTestData(t)
1188
1189 user, _ := testDB.RegisterUser("statsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI statsowner", "comment", "")
1190 _ = mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "stat.md", Slug: "stat", Title: "Stat", Space: "prose"})
1191 _, _ = testDB.UpsertProject(user.ID, "stat-project", "stat-project")
1192
1193 stats, err := testDB.FindUserStats(user.ID)
1194 if err != nil {
1195 t.Fatalf("FindUserStats failed: %v", err)
1196 }
1197 if stats.Prose.Num != 1 {
1198 t.Errorf("expected 1 prose post, got %d", stats.Prose.Num)
1199 }
1200 if stats.Pages.Num != 1 {
1201 t.Errorf("expected 1 project, got %d", stats.Pages.Num)
1202 }
1203}
1204
1205// ============ Tuns Event Logs Tests ============
1206
1207func TestInsertTunsEventLog(t *testing.T) {
1208 cleanupTestData(t)
1209
1210 user, _ := testDB.RegisterUser("tunsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tunsowner", "comment", "")
1211
1212 log := &db.TunsEventLog{
1213 UserId: user.ID,
1214 ServerID: "server-1",
1215 RemoteAddr: "192.168.1.1:1234",
1216 EventType: "connect",
1217 TunnelType: "http",
1218 ConnectionType: "tcp",
1219 TunnelID: "tunnel-123",
1220 }
1221
1222 err := testDB.InsertTunsEventLog(log)
1223 if err != nil {
1224 t.Fatalf("InsertTunsEventLog failed: %v", err)
1225 }
1226}
1227
1228func TestFindTunsEventLogs(t *testing.T) {
1229 cleanupTestData(t)
1230
1231 user, _ := testDB.RegisterUser("findtunsowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findtunsowner", "comment", "")
1232 _ = testDB.InsertTunsEventLog(&db.TunsEventLog{
1233 UserId: user.ID,
1234 ServerID: "server-1",
1235 RemoteAddr: "192.168.1.1:1234",
1236 EventType: "connect",
1237 TunnelType: "http",
1238 ConnectionType: "tcp",
1239 TunnelID: "tunnel-456",
1240 })
1241
1242 logs, err := testDB.FindTunsEventLogs(user.ID)
1243 if err != nil {
1244 t.Fatalf("FindTunsEventLogs failed: %v", err)
1245 }
1246 if len(logs) != 1 {
1247 t.Errorf("expected 1 log, got %d", len(logs))
1248 }
1249}
1250
1251func TestFindTunsEventLogsByAddr(t *testing.T) {
1252 cleanupTestData(t)
1253
1254 user, _ := testDB.RegisterUser("tunsaddrowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI tunsaddrowner", "comment", "")
1255 _ = testDB.InsertTunsEventLog(&db.TunsEventLog{
1256 UserId: user.ID,
1257 ServerID: "server-1",
1258 RemoteAddr: "192.168.1.1:1234",
1259 EventType: "connect",
1260 TunnelType: "http",
1261 ConnectionType: "tcp",
1262 TunnelID: "tunnel-789",
1263 })
1264
1265 logs, err := testDB.FindTunsEventLogsByAddr(user.ID, "tunnel-789")
1266 if err != nil {
1267 t.Fatalf("FindTunsEventLogsByAddr failed: %v", err)
1268 }
1269 if len(logs) != 1 {
1270 t.Errorf("expected 1 log, got %d", len(logs))
1271 }
1272}
1273
1274// ============ Access Logs Tests ============
1275
1276func TestInsertAccessLog(t *testing.T) {
1277 cleanupTestData(t)
1278
1279 user, _ := testDB.RegisterUser("accesslogowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI accesslogowner", "comment", "")
1280
1281 log := &db.AccessLog{
1282 UserID: user.ID,
1283 Service: "pgs",
1284 Pubkey: "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI accesslogowner",
1285 Identity: "accesslogowner",
1286 }
1287
1288 err := testDB.InsertAccessLog(log)
1289 if err != nil {
1290 t.Fatalf("InsertAccessLog failed: %v", err)
1291 }
1292}
1293
1294func TestFindAccessLogs(t *testing.T) {
1295 cleanupTestData(t)
1296
1297 user, _ := testDB.RegisterUser("findaccesslogowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findaccesslogowner", "comment", "")
1298 _ = testDB.InsertAccessLog(&db.AccessLog{
1299 UserID: user.ID,
1300 Service: "pgs",
1301 Pubkey: "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI findaccesslogowner",
1302 Identity: "findaccesslogowner",
1303 })
1304
1305 fromDate := time.Now().Add(-24 * time.Hour)
1306 logs, err := testDB.FindAccessLogs(user.ID, &fromDate)
1307 if err != nil {
1308 t.Fatalf("FindAccessLogs failed: %v", err)
1309 }
1310 if len(logs) != 1 {
1311 t.Errorf("expected 1 log, got %d", len(logs))
1312 }
1313}
1314
1315func TestFindPubkeysInAccessLogs(t *testing.T) {
1316 cleanupTestData(t)
1317
1318 user, _ := testDB.RegisterUser("pubkeylogowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pubkeylogowner", "comment", "")
1319 _ = testDB.InsertAccessLog(&db.AccessLog{
1320 UserID: user.ID,
1321 Service: "pgs",
1322 Pubkey: "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pubkeylogowner",
1323 Identity: "pubkeylogowner",
1324 })
1325
1326 pubkeys, err := testDB.FindPubkeysInAccessLogs(user.ID)
1327 if err != nil {
1328 t.Fatalf("FindPubkeysInAccessLogs failed: %v", err)
1329 }
1330 if len(pubkeys) != 1 {
1331 t.Errorf("expected 1 pubkey, got %d", len(pubkeys))
1332 }
1333}
1334
1335func TestFindAccessLogsByPubkey(t *testing.T) {
1336 cleanupTestData(t)
1337
1338 user, _ := testDB.RegisterUser("accessbypubkeyowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI accessbypubkeyowner", "comment", "")
1339 pubkey := "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI accessbypubkeyowner"
1340 _ = testDB.InsertAccessLog(&db.AccessLog{
1341 UserID: user.ID,
1342 Service: "pgs",
1343 Pubkey: pubkey,
1344 Identity: "accessbypubkeyowner",
1345 })
1346
1347 fromDate := time.Now().Add(-24 * time.Hour)
1348 logs, err := testDB.FindAccessLogsByPubkey(pubkey, &fromDate)
1349 if err != nil {
1350 t.Fatalf("FindAccessLogsByPubkey failed: %v", err)
1351 }
1352 if len(logs) != 1 {
1353 t.Errorf("expected 1 log, got %d", len(logs))
1354 }
1355}
1356
1357// ============ JSONB Roundtrip Tests ============
1358
1359func TestPostData_JSONBRoundtrip(t *testing.T) {
1360 cleanupTestData(t)
1361
1362 user, _ := testDB.RegisterUser("postdataowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI postdataowner", "comment", "")
1363
1364 now := time.Now().Truncate(time.Second)
1365 postData := db.PostData{
1366 ImgPath: "/images/test.png",
1367 LastDigest: &now,
1368 Attempts: 5,
1369 }
1370
1371 post := mustInsertPost(t, &db.Post{
1372 UserID: user.ID,
1373 Filename: "jsonb.md",
1374 Slug: "jsonb",
1375 Title: "JSONB Test",
1376 Space: "prose",
1377 Data: postData,
1378 })
1379
1380 found, err := testDB.FindPost(post.ID)
1381 if err != nil {
1382 t.Fatalf("FindPost failed: %v", err)
1383 }
1384 if found.Data.ImgPath != "/images/test.png" {
1385 t.Errorf("expected ImgPath '/images/test.png', got '%s'", found.Data.ImgPath)
1386 }
1387 if found.Data.Attempts != 5 {
1388 t.Errorf("expected Attempts 5, got %d", found.Data.Attempts)
1389 }
1390}
1391
1392func TestProjectAcl_JSONBRoundtrip(t *testing.T) {
1393 cleanupTestData(t)
1394
1395 user, _ := testDB.RegisterUser("aclowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI aclowner", "comment", "")
1396
1397 project, err := testDB.UpsertProject(user.ID, "acl-project", "acl-project")
1398 if err != nil {
1399 t.Fatalf("UpsertProject failed: %v", err)
1400 }
1401
1402 found, err := testDB.FindProjectByName(user.ID, "acl-project")
1403 if err != nil {
1404 t.Fatalf("FindProjectByName failed: %v", err)
1405 }
1406 if found.Acl.Type != "public" {
1407 t.Errorf("expected Acl.Type 'public', got '%s'", found.Acl.Type)
1408 }
1409 _ = project
1410}
1411
1412func TestFeedItemData_JSONBRoundtrip(t *testing.T) {
1413 cleanupTestData(t)
1414
1415 user, _ := testDB.RegisterUser("feedjsonbowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI feedjsonbowner", "comment", "")
1416 post := mustInsertPost(t, &db.Post{UserID: user.ID, Filename: "feedjsonb.txt", Slug: "feedjsonb", Title: "Feed JSONB", Space: "feeds"})
1417
1418 now := time.Now().Truncate(time.Second)
1419 items := []*db.FeedItem{
1420 {
1421 PostID: post.ID,
1422 GUID: "jsonb-guid",
1423 Data: db.FeedItemData{
1424 Title: "JSONB Item",
1425 Description: "Description",
1426 Content: "Content",
1427 Link: "http://example.com",
1428 PublishedAt: &now,
1429 },
1430 },
1431 }
1432 _ = testDB.InsertFeedItems(post.ID, items)
1433
1434 found, err := testDB.FindFeedItemsByPostID(post.ID)
1435 if err != nil {
1436 t.Fatalf("FindFeedItemsByPostID failed: %v", err)
1437 }
1438 if len(found) != 1 {
1439 t.Fatalf("expected 1 item, got %d", len(found))
1440 }
1441 if found[0].Data.Title != "JSONB Item" {
1442 t.Errorf("expected title 'JSONB Item', got '%s'", found[0].Data.Title)
1443 }
1444}
1445
1446func TestFeatureFlagData_JSONBRoundtrip(t *testing.T) {
1447 cleanupTestData(t)
1448
1449 user, _ := testDB.RegisterUser("featurejsonbowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI featurejsonbowner", "comment", "")
1450
1451 err := testDB.AddPicoPlusUser("featurejsonbowner", "test@example.com", "stripe", "tx456")
1452 if err != nil {
1453 t.Fatalf("AddPicoPlusUser failed: %v", err)
1454 }
1455
1456 feature, err := testDB.FindFeature(user.ID, "plus")
1457 if err != nil {
1458 t.Fatalf("FindFeature failed: %v", err)
1459 }
1460 if feature.Data.StorageMax != 10000000000 {
1461 t.Errorf("expected StorageMax 10000000000, got %d", feature.Data.StorageMax)
1462 }
1463 if feature.Data.FileMax != 100000000 {
1464 t.Errorf("expected FileMax 100000000, got %d", feature.Data.FileMax)
1465 }
1466}
1467
1468func TestPaymentHistoryData_JSONBRoundtrip(t *testing.T) {
1469 cleanupTestData(t)
1470
1471 user, _ := testDB.RegisterUser("paymentjsonbowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI paymentjsonbowner", "comment", "")
1472
1473 err := testDB.AddPicoPlusUser("paymentjsonbowner", "payment@example.com", "stripe", "tx789")
1474 if err != nil {
1475 t.Fatalf("AddPicoPlusUser failed: %v", err)
1476 }
1477
1478 var txId string
1479 err = testDB.Db.QueryRow("SELECT data->>'tx_id' FROM payment_history WHERE user_id = $1", user.ID).Scan(&txId)
1480 if err != nil {
1481 t.Fatalf("failed to query payment history: %v", err)
1482 }
1483 if txId != "tx789" {
1484 t.Errorf("expected tx_id 'tx789', got '%s'", txId)
1485 }
1486}
1487
1488// ============ Pipe Monitor Tests ============
1489
1490func TestUpsertPipeMonitor(t *testing.T) {
1491 cleanupTestData(t)
1492
1493 user, _ := testDB.RegisterUser("pipemonitorowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pipemonitorowner", "comment", "")
1494
1495 winEnd := time.Now().Add(time.Hour)
1496 err := testDB.UpsertPipeMonitor(user.ID, "test-topic", 5*time.Minute, &winEnd)
1497 if err != nil {
1498 t.Fatalf("UpsertPipeMonitor failed: %v", err)
1499 }
1500
1501 monitor, err := testDB.FindPipeMonitorByTopic(user.ID, "test-topic")
1502 if err != nil {
1503 t.Fatalf("FindPipeMonitorByTopic failed: %v", err)
1504 }
1505 if monitor.Topic != "test-topic" {
1506 t.Errorf("expected topic 'test-topic', got '%s'", monitor.Topic)
1507 }
1508 if monitor.WindowDur != 5*time.Minute {
1509 t.Errorf("expected window_dur 5m, got %v", monitor.WindowDur)
1510 }
1511}
1512
1513func TestUpsertPipeMonitor_Update(t *testing.T) {
1514 cleanupTestData(t)
1515
1516 user, _ := testDB.RegisterUser("pipeupdateowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pipeupdateowner", "comment", "")
1517
1518 winEnd1 := time.Now().Add(time.Hour)
1519 err := testDB.UpsertPipeMonitor(user.ID, "update-topic", 5*time.Minute, &winEnd1)
1520 if err != nil {
1521 t.Fatalf("first UpsertPipeMonitor failed: %v", err)
1522 }
1523
1524 winEnd2 := time.Now().Add(2 * time.Hour)
1525 err = testDB.UpsertPipeMonitor(user.ID, "update-topic", 10*time.Minute, &winEnd2)
1526 if err != nil {
1527 t.Fatalf("second UpsertPipeMonitor failed: %v", err)
1528 }
1529
1530 monitor, err := testDB.FindPipeMonitorByTopic(user.ID, "update-topic")
1531 if err != nil {
1532 t.Fatalf("FindPipeMonitorByTopic failed: %v", err)
1533 }
1534 if monitor.WindowDur != 10*time.Minute {
1535 t.Errorf("expected window_dur 10m after update, got %v", monitor.WindowDur)
1536 }
1537}
1538
1539func TestUpdatePipeMonitorLastPing(t *testing.T) {
1540 cleanupTestData(t)
1541
1542 user, _ := testDB.RegisterUser("pipepingowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pipepingowner", "comment", "")
1543
1544 winEnd := time.Now().Add(time.Hour)
1545 err := testDB.UpsertPipeMonitor(user.ID, "ping-topic", 5*time.Minute, &winEnd)
1546 if err != nil {
1547 t.Fatalf("UpsertPipeMonitor failed: %v", err)
1548 }
1549
1550 lastPing := time.Now()
1551 err = testDB.UpdatePipeMonitorLastPing(user.ID, "ping-topic", &lastPing)
1552 if err != nil {
1553 t.Fatalf("UpdatePipeMonitorLastPing failed: %v", err)
1554 }
1555
1556 monitor, err := testDB.FindPipeMonitorByTopic(user.ID, "ping-topic")
1557 if err != nil {
1558 t.Fatalf("FindPipeMonitorByTopic failed: %v", err)
1559 }
1560 if monitor.LastPing == nil {
1561 t.Error("expected last_ping to be set, got nil")
1562 }
1563}
1564
1565func TestRemovePipeMonitor(t *testing.T) {
1566 cleanupTestData(t)
1567
1568 user, _ := testDB.RegisterUser("piperemoveowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI piperemoveowner", "comment", "")
1569
1570 winEnd := time.Now().Add(time.Hour)
1571 err := testDB.UpsertPipeMonitor(user.ID, "remove-topic", 5*time.Minute, &winEnd)
1572 if err != nil {
1573 t.Fatalf("UpsertPipeMonitor failed: %v", err)
1574 }
1575
1576 err = testDB.RemovePipeMonitor(user.ID, "remove-topic")
1577 if err != nil {
1578 t.Fatalf("RemovePipeMonitor failed: %v", err)
1579 }
1580
1581 _, err = testDB.FindPipeMonitorByTopic(user.ID, "remove-topic")
1582 if err == nil {
1583 t.Error("expected error finding removed monitor, got nil")
1584 }
1585}
1586
1587func TestFindPipeMonitorByTopic_NotFound(t *testing.T) {
1588 cleanupTestData(t)
1589
1590 user, _ := testDB.RegisterUser("pipenotfoundowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pipenotfoundowner", "comment", "")
1591
1592 _, err := testDB.FindPipeMonitorByTopic(user.ID, "nonexistent-topic")
1593 if err == nil {
1594 t.Error("expected error for nonexistent monitor, got nil")
1595 }
1596}
1597
1598func TestFindPipeMonitorsByUser(t *testing.T) {
1599 cleanupTestData(t)
1600
1601 user, _ := testDB.RegisterUser("pipemonlistowner", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pipemonlistowner", "comment", "")
1602
1603 winEnd := time.Now().Add(time.Hour)
1604 _ = testDB.UpsertPipeMonitor(user.ID, "service-a", 5*time.Minute, &winEnd)
1605 _ = testDB.UpsertPipeMonitor(user.ID, "service-b", 10*time.Minute, &winEnd)
1606 _ = testDB.UpsertPipeMonitor(user.ID, "service-c", 1*time.Hour, &winEnd)
1607
1608 monitors, err := testDB.FindPipeMonitorsByUser(user.ID)
1609 if err != nil {
1610 t.Fatalf("FindPipeMonitorsByUser failed: %v", err)
1611 }
1612
1613 if len(monitors) != 3 {
1614 t.Errorf("expected 3 monitors, got %d", len(monitors))
1615 }
1616
1617 // Should be ordered by topic
1618 if monitors[0].Topic != "service-a" {
1619 t.Errorf("expected first topic 'service-a', got %s", monitors[0].Topic)
1620 }
1621 if monitors[1].Topic != "service-b" {
1622 t.Errorf("expected second topic 'service-b', got %s", monitors[1].Topic)
1623 }
1624 if monitors[2].Topic != "service-c" {
1625 t.Errorf("expected third topic 'service-c', got %s", monitors[2].Topic)
1626 }
1627}
1628
1629func TestFindPipeMonitorsByUser_Empty(t *testing.T) {
1630 cleanupTestData(t)
1631
1632 user, _ := testDB.RegisterUser("pipenomonitors", "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAI pipenomonitors", "comment", "")
1633
1634 monitors, err := testDB.FindPipeMonitorsByUser(user.ID)
1635 if err != nil {
1636 t.Fatalf("FindPipeMonitorsByUser failed: %v", err)
1637 }
1638
1639 if len(monitors) != 0 {
1640 t.Errorf("expected 0 monitors for user with none, got %d", len(monitors))
1641 }
1642}