sandrodach
5cf6b59377
MindSift CI & Docker Release / Build, Test in Container & Push (push) Successful in 59s
5461 lines
173 KiB
Go
5461 lines
173 KiB
Go
package storage
|
||
|
||
import (
|
||
"context"
|
||
"database/sql"
|
||
"encoding/json"
|
||
"errors"
|
||
"fmt"
|
||
"os"
|
||
"path/filepath"
|
||
"regexp"
|
||
"sort"
|
||
"strings"
|
||
"sync"
|
||
"time"
|
||
|
||
"github.com/sandrodach/mindsift/internal/auth"
|
||
"github.com/sandrodach/mindsift/internal/domain"
|
||
_ "modernc.org/sqlite"
|
||
)
|
||
|
||
type Store struct {
|
||
db *sql.DB
|
||
initMu sync.Mutex
|
||
}
|
||
|
||
func NewStore(dbPath string) (*Store, error) {
|
||
if dbPath != ":memory:" && !strings.HasPrefix(dbPath, "file:") {
|
||
dir := filepath.Dir(dbPath)
|
||
if err := os.MkdirAll(dir, 0755); err != nil {
|
||
return nil, fmt.Errorf("failed to create data directory %s: %w", dir, err)
|
||
}
|
||
}
|
||
|
||
dsn := dbPath
|
||
if !strings.Contains(dsn, "?") {
|
||
dsn += "?_pragma=busy_timeout(10000)&_pragma=journal_mode(WAL)&_pragma=synchronous(NORMAL)&_pragma=foreign_keys(1)"
|
||
}
|
||
|
||
db, err := sql.Open("sqlite", dsn)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("failed to open sqlite db: %w", err)
|
||
}
|
||
|
||
db.SetMaxOpenConns(8)
|
||
db.SetMaxIdleConns(4)
|
||
|
||
store := &Store{db: db}
|
||
if err := store.migrate(); err != nil {
|
||
return nil, fmt.Errorf("failed to run migrations: %w", err)
|
||
}
|
||
|
||
return store, nil
|
||
}
|
||
|
||
func (s *Store) Close() error {
|
||
return s.db.Close()
|
||
}
|
||
|
||
func (s *Store) migrate() error {
|
||
schema := `
|
||
PRAGMA journal_mode = WAL;
|
||
PRAGMA synchronous = NORMAL;
|
||
PRAGMA foreign_keys = ON;
|
||
PRAGMA busy_timeout = 5000;
|
||
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id TEXT PRIMARY KEY,
|
||
email TEXT UNIQUE NOT NULL,
|
||
username TEXT NOT NULL DEFAULT '',
|
||
password_hash TEXT NOT NULL,
|
||
display_name TEXT NOT NULL,
|
||
role TEXT NOT NULL DEFAULT 'member',
|
||
is_active INTEGER NOT NULL DEFAULT 1,
|
||
token_version INTEGER NOT NULL DEFAULT 1,
|
||
alarm_channels TEXT NOT NULL DEFAULT 'discord',
|
||
alarm_email TEXT NOT NULL DEFAULT '',
|
||
discord_webhook_url TEXT NOT NULL DEFAULT '',
|
||
custom_webhook_url TEXT NOT NULL DEFAULT '',
|
||
notify_before_minutes INTEGER NOT NULL DEFAULT 30,
|
||
avatar_url TEXT NOT NULL DEFAULT '',
|
||
created_at TEXT NOT NULL
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS user_invites (
|
||
id TEXT PRIMARY KEY,
|
||
code TEXT UNIQUE NOT NULL,
|
||
email TEXT NOT NULL DEFAULT '',
|
||
role TEXT NOT NULL DEFAULT 'member',
|
||
team_id TEXT NOT NULL DEFAULT '',
|
||
created_by TEXT NOT NULL,
|
||
expires_at TEXT NOT NULL,
|
||
used_by TEXT NOT NULL DEFAULT '',
|
||
created_at TEXT NOT NULL
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS thoughts (
|
||
id TEXT PRIMARY KEY,
|
||
user_id TEXT NOT NULL,
|
||
content TEXT NOT NULL,
|
||
tags TEXT NOT NULL DEFAULT '[]',
|
||
priority TEXT NOT NULL DEFAULT 'normal',
|
||
source TEXT NOT NULL DEFAULT 'manual',
|
||
status TEXT NOT NULL DEFAULT 'inbox',
|
||
is_completed INTEGER NOT NULL DEFAULT 0,
|
||
converted_type TEXT DEFAULT '',
|
||
converted_ref_id TEXT DEFAULT '',
|
||
due_date TEXT DEFAULT '',
|
||
task_list_id TEXT NOT NULL DEFAULT '',
|
||
kanban_status TEXT NOT NULL DEFAULT 'todo',
|
||
notes TEXT NOT NULL DEFAULT '',
|
||
alarm_sent INTEGER NOT NULL DEFAULT 0,
|
||
subtasks TEXT NOT NULL DEFAULT '[]',
|
||
created_at TEXT NOT NULL,
|
||
triaged_at TEXT DEFAULT '',
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_thoughts_user_status ON thoughts(user_id, status, created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_thoughts_user_due ON thoughts(user_id, due_date);
|
||
CREATE INDEX IF NOT EXISTS idx_thoughts_task_list ON thoughts(task_list_id);
|
||
|
||
CREATE TABLE IF NOT EXISTS task_lists (
|
||
id TEXT PRIMARY KEY,
|
||
user_id TEXT NOT NULL,
|
||
space_id TEXT DEFAULT '',
|
||
team_id TEXT DEFAULT '',
|
||
title TEXT NOT NULL,
|
||
icon TEXT NOT NULL DEFAULT '📋',
|
||
color TEXT NOT NULL DEFAULT '#2563eb',
|
||
columns TEXT NOT NULL DEFAULT '[]',
|
||
created_at TEXT NOT NULL,
|
||
updated_at TEXT NOT NULL,
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_task_lists_user ON task_lists(user_id, updated_at DESC);
|
||
|
||
CREATE TABLE IF NOT EXISTS organizations (
|
||
id TEXT PRIMARY KEY,
|
||
name TEXT NOT NULL,
|
||
owner_id TEXT NOT NULL,
|
||
created_at TEXT NOT NULL,
|
||
FOREIGN KEY(owner_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS teams (
|
||
id TEXT PRIMARY KEY,
|
||
org_id TEXT NOT NULL DEFAULT 'default',
|
||
name TEXT NOT NULL,
|
||
description TEXT NOT NULL DEFAULT '',
|
||
created_at TEXT NOT NULL
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS team_members (
|
||
team_id TEXT NOT NULL,
|
||
user_id TEXT NOT NULL,
|
||
role TEXT NOT NULL DEFAULT 'member',
|
||
joined_at TEXT NOT NULL,
|
||
PRIMARY KEY (team_id, user_id),
|
||
FOREIGN KEY(team_id) REFERENCES teams(id) ON DELETE CASCADE,
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS documents (
|
||
id TEXT PRIMARY KEY,
|
||
user_id TEXT NOT NULL DEFAULT '',
|
||
space_id TEXT DEFAULT '',
|
||
title TEXT NOT NULL DEFAULT '',
|
||
folder_id TEXT NOT NULL DEFAULT '',
|
||
markdown_cache TEXT NOT NULL DEFAULT '',
|
||
icon TEXT NOT NULL DEFAULT '📄',
|
||
content TEXT NOT NULL DEFAULT '',
|
||
blocks_json TEXT NOT NULL DEFAULT '[]',
|
||
tags TEXT NOT NULL DEFAULT '[]',
|
||
folder_path TEXT NOT NULL DEFAULT '',
|
||
is_pinned INTEGER NOT NULL DEFAULT 0,
|
||
is_archived INTEGER NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_documents_user ON documents(user_id, is_archived, updated_at DESC);
|
||
|
||
CREATE TABLE IF NOT EXISTS blocks (
|
||
id TEXT PRIMARY KEY,
|
||
document_id TEXT NOT NULL,
|
||
content TEXT NOT NULL DEFAULT '',
|
||
level INT NOT NULL DEFAULT 0,
|
||
position REAL NOT NULL DEFAULT 0,
|
||
type TEXT NOT NULL DEFAULT 'bullet',
|
||
checked INTEGER NOT NULL DEFAULT 0,
|
||
collapsed INTEGER NOT NULL DEFAULT 0,
|
||
lang TEXT NOT NULL DEFAULT '',
|
||
FOREIGN KEY(document_id) REFERENCES documents(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_blocks_doc_pos ON blocks(document_id, position ASC);
|
||
|
||
CREATE TABLE IF NOT EXISTS block_tags (
|
||
tag TEXT NOT NULL,
|
||
document_id TEXT NOT NULL,
|
||
block_id TEXT NOT NULL,
|
||
PRIMARY KEY(tag, block_id),
|
||
FOREIGN KEY(document_id) REFERENCES documents(id) ON DELETE CASCADE,
|
||
FOREIGN KEY(block_id) REFERENCES blocks(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_block_tags_tag ON block_tags(tag);
|
||
CREATE INDEX IF NOT EXISTS idx_block_tags_doc ON block_tags(document_id);
|
||
|
||
CREATE TABLE IF NOT EXISTS doc_shares (
|
||
id TEXT PRIMARY KEY,
|
||
doc_id TEXT NOT NULL,
|
||
user_id TEXT NOT NULL,
|
||
permission TEXT NOT NULL CHECK(permission IN ('read', 'write')),
|
||
created_at TEXT NOT NULL,
|
||
created_by TEXT NOT NULL,
|
||
UNIQUE(doc_id, user_id),
|
||
FOREIGN KEY(doc_id) REFERENCES documents(id) ON DELETE CASCADE,
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_doc_shares_user ON doc_shares(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_doc_shares_doc ON doc_shares(doc_id);
|
||
|
||
CREATE TABLE IF NOT EXISTS doc_pins (
|
||
user_id TEXT NOT NULL,
|
||
doc_id TEXT NOT NULL,
|
||
created_at TEXT NOT NULL,
|
||
PRIMARY KEY(user_id, doc_id),
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
|
||
FOREIGN KEY(doc_id) REFERENCES documents(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_doc_pins_user ON doc_pins(user_id);
|
||
|
||
INSERT OR IGNORE INTO doc_pins (user_id, doc_id, created_at)
|
||
SELECT user_id, id, updated_at FROM documents WHERE is_pinned = 1;
|
||
|
||
CREATE TABLE IF NOT EXISTS tag_pages (
|
||
user_id TEXT NOT NULL,
|
||
tag TEXT NOT NULL,
|
||
content TEXT NOT NULL DEFAULT '',
|
||
updated_at TEXT NOT NULL,
|
||
PRIMARY KEY(user_id, tag)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS lists (
|
||
id TEXT PRIMARY KEY,
|
||
user_id TEXT NOT NULL,
|
||
title TEXT NOT NULL,
|
||
icon TEXT NOT NULL DEFAULT '🛒',
|
||
list_type TEXT NOT NULL DEFAULT 'shopping',
|
||
updated_at TEXT NOT NULL,
|
||
created_at TEXT NOT NULL,
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_lists_user ON lists(user_id, updated_at DESC);
|
||
|
||
CREATE TABLE IF NOT EXISTS list_shares (
|
||
id TEXT PRIMARY KEY,
|
||
list_id TEXT NOT NULL,
|
||
target_type TEXT NOT NULL CHECK(target_type IN ('team', 'user')),
|
||
target_id TEXT NOT NULL,
|
||
role TEXT NOT NULL DEFAULT 'editor' CHECK(role IN ('editor', 'viewer')),
|
||
created_by TEXT NOT NULL,
|
||
created_at TEXT NOT NULL,
|
||
UNIQUE(list_id, target_type, target_id),
|
||
FOREIGN KEY(list_id) REFERENCES lists(id) ON DELETE CASCADE,
|
||
FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_list_shares_target ON list_shares(target_type, target_id);
|
||
CREATE INDEX IF NOT EXISTS idx_list_shares_list ON list_shares(list_id);
|
||
|
||
CREATE TABLE IF NOT EXISTS list_items (
|
||
id TEXT PRIMARY KEY,
|
||
list_id TEXT NOT NULL,
|
||
content TEXT NOT NULL,
|
||
category TEXT NOT NULL DEFAULT '',
|
||
quantity TEXT NOT NULL DEFAULT '',
|
||
is_completed INTEGER NOT NULL DEFAULT 0,
|
||
completed_by TEXT NOT NULL DEFAULT '',
|
||
completed_by_name TEXT NOT NULL DEFAULT '',
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
created_at TEXT NOT NULL,
|
||
completed_at TEXT NOT NULL DEFAULT '',
|
||
FOREIGN KEY(list_id) REFERENCES lists(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_list_items_sync ON list_items(list_id, is_completed, sort_order ASC);
|
||
|
||
CREATE TABLE IF NOT EXISTS api_keys (
|
||
id TEXT PRIMARY KEY,
|
||
user_id TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
key_prefix TEXT NOT NULL,
|
||
key_hash TEXT UNIQUE NOT NULL,
|
||
scopes TEXT NOT NULL DEFAULT 'ingest,read',
|
||
last_used_at TEXT NOT NULL DEFAULT '',
|
||
created_at TEXT NOT NULL,
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS system_settings (
|
||
key TEXT PRIMARY KEY,
|
||
value TEXT NOT NULL
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS revoked_tokens (
|
||
token_hash TEXT PRIMARY KEY,
|
||
user_id TEXT NOT NULL,
|
||
expires_at TEXT NOT NULL,
|
||
revoked_at TEXT NOT NULL
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_revoked_tokens_expires ON revoked_tokens(expires_at);
|
||
CREATE INDEX IF NOT EXISTS idx_revoked_tokens_user ON revoked_tokens(user_id);
|
||
`
|
||
if _, err := s.db.Exec(schema); err != nil {
|
||
return err
|
||
}
|
||
|
||
// Idempotent column migrations for existing databases
|
||
columnMigrations := []string{
|
||
"ALTER TABLE users ADD COLUMN username TEXT NOT NULL DEFAULT '';",
|
||
"CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);",
|
||
"ALTER TABLE users ADD COLUMN role TEXT NOT NULL DEFAULT 'member';",
|
||
"ALTER TABLE users ADD COLUMN is_active INTEGER NOT NULL DEFAULT 1;",
|
||
"ALTER TABLE users ADD COLUMN token_version INTEGER NOT NULL DEFAULT 1;",
|
||
"ALTER TABLE users ADD COLUMN alarm_channels TEXT NOT NULL DEFAULT 'discord';",
|
||
"ALTER TABLE users ADD COLUMN alarm_email TEXT NOT NULL DEFAULT '';",
|
||
"ALTER TABLE users ADD COLUMN discord_webhook_url TEXT NOT NULL DEFAULT '';",
|
||
"ALTER TABLE users ADD COLUMN custom_webhook_url TEXT NOT NULL DEFAULT '';",
|
||
"ALTER TABLE users ADD COLUMN notify_before_minutes INTEGER NOT NULL DEFAULT 30;",
|
||
"ALTER TABLE users ADD COLUMN avatar_url TEXT NOT NULL DEFAULT '';",
|
||
|
||
"ALTER TABLE thoughts ADD COLUMN task_list_id TEXT NOT NULL DEFAULT '';",
|
||
"ALTER TABLE thoughts ADD COLUMN kanban_status TEXT NOT NULL DEFAULT 'todo';",
|
||
"ALTER TABLE thoughts ADD COLUMN notes TEXT NOT NULL DEFAULT '';",
|
||
"ALTER TABLE thoughts ADD COLUMN alarm_sent INTEGER NOT NULL DEFAULT 0;",
|
||
"ALTER TABLE thoughts ADD COLUMN subtasks TEXT NOT NULL DEFAULT '[]';",
|
||
"ALTER TABLE thoughts ADD COLUMN sort_order INTEGER NOT NULL DEFAULT 0;",
|
||
"CREATE INDEX IF NOT EXISTS idx_thoughts_list_sort ON thoughts(task_list_id, sort_order ASC, created_at DESC);",
|
||
|
||
"ALTER TABLE documents ADD COLUMN folder_path TEXT NOT NULL DEFAULT '';",
|
||
|
||
"CREATE TABLE IF NOT EXISTS doc_shares (id TEXT PRIMARY KEY, doc_id TEXT NOT NULL, user_id TEXT NOT NULL, permission TEXT NOT NULL CHECK(permission IN ('read', 'write')), created_at TEXT NOT NULL, created_by TEXT NOT NULL, UNIQUE(doc_id, user_id));",
|
||
"CREATE INDEX IF NOT EXISTS idx_doc_shares_user ON doc_shares(user_id);",
|
||
"CREATE INDEX IF NOT EXISTS idx_doc_shares_doc ON doc_shares(doc_id);",
|
||
|
||
"ALTER TABLE task_lists ADD COLUMN team_id TEXT NOT NULL DEFAULT '';",
|
||
"ALTER TABLE task_lists ADD COLUMN columns TEXT NOT NULL DEFAULT '[]';",
|
||
"CREATE TABLE IF NOT EXISTS task_list_shares (id TEXT PRIMARY KEY, task_list_id TEXT NOT NULL, target_type TEXT NOT NULL CHECK(target_type IN ('team', 'user')), target_id TEXT NOT NULL, created_at TEXT NOT NULL, created_by TEXT NOT NULL, UNIQUE(task_list_id, target_type, target_id));",
|
||
"CREATE INDEX IF NOT EXISTS idx_task_list_shares_target ON task_list_shares(target_type, target_id);",
|
||
"CREATE INDEX IF NOT EXISTS idx_task_list_shares_list ON task_list_shares(task_list_id);",
|
||
|
||
"CREATE TABLE IF NOT EXISTS doc_team_shares (id TEXT PRIMARY KEY, doc_id TEXT NOT NULL, team_id TEXT NOT NULL, permission TEXT NOT NULL CHECK(permission IN ('read', 'write')), created_at TEXT NOT NULL, created_by TEXT NOT NULL, UNIQUE(doc_id, team_id));",
|
||
"CREATE INDEX IF NOT EXISTS idx_doc_team_shares_team ON doc_team_shares(team_id);",
|
||
"CREATE INDEX IF NOT EXISTS idx_doc_team_shares_doc ON doc_team_shares(doc_id);",
|
||
|
||
"ALTER TABLE lists ADD COLUMN user_id TEXT NOT NULL DEFAULT '';",
|
||
"CREATE TABLE IF NOT EXISTS list_shares (id TEXT PRIMARY KEY, list_id TEXT NOT NULL, target_type TEXT NOT NULL CHECK(target_type IN ('team', 'user')), target_id TEXT NOT NULL, role TEXT NOT NULL DEFAULT 'editor' CHECK(role IN ('editor', 'viewer')), created_at TEXT NOT NULL, created_by TEXT NOT NULL, UNIQUE(list_id, target_type, target_id));",
|
||
"CREATE INDEX IF NOT EXISTS idx_list_shares_target ON list_shares(target_type, target_id);",
|
||
"CREATE INDEX IF NOT EXISTS idx_list_shares_list ON list_shares(list_id);",
|
||
|
||
// Workspace Shared Folders & User Folder Placements
|
||
"ALTER TABLE documents ADD COLUMN shared_folder_id TEXT NOT NULL DEFAULT '';",
|
||
"ALTER TABLE documents ADD COLUMN sub_folder_path TEXT NOT NULL DEFAULT '';",
|
||
"CREATE INDEX IF NOT EXISTS idx_documents_shared_folder ON documents(shared_folder_id);",
|
||
|
||
"CREATE TABLE IF NOT EXISTS workspace_shared_folders (id TEXT PRIMARY KEY, owner_id TEXT NOT NULL, name TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);",
|
||
"CREATE INDEX IF NOT EXISTS idx_workspace_shared_folders_owner ON workspace_shared_folders(owner_id);",
|
||
|
||
"CREATE TABLE IF NOT EXISTS workspace_folder_shares (id TEXT PRIMARY KEY, folder_id TEXT NOT NULL, target_type TEXT NOT NULL CHECK(target_type IN ('team', 'user')), target_id TEXT NOT NULL, permission TEXT NOT NULL CHECK(permission IN ('read', 'write')), created_by TEXT NOT NULL, created_at TEXT NOT NULL, UNIQUE(folder_id, target_type, target_id));",
|
||
"CREATE INDEX IF NOT EXISTS idx_workspace_folder_shares_folder ON workspace_folder_shares(folder_id);",
|
||
"CREATE INDEX IF NOT EXISTS idx_workspace_folder_shares_target ON workspace_folder_shares(target_type, target_id);",
|
||
|
||
"CREATE TABLE IF NOT EXISTS workspace_folder_mounts (user_id TEXT NOT NULL, folder_id TEXT NOT NULL, mount_path TEXT NOT NULL, created_at TEXT NOT NULL, updated_at TEXT NOT NULL, PRIMARY KEY(user_id, folder_id));",
|
||
"CREATE INDEX IF NOT EXISTS idx_workspace_folder_mounts_user ON workspace_folder_mounts(user_id);",
|
||
|
||
"CREATE TABLE IF NOT EXISTS doc_user_folders (user_id TEXT NOT NULL, doc_id TEXT NOT NULL, folder_path TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL, updated_at TEXT NOT NULL, PRIMARY KEY(user_id, doc_id));",
|
||
"CREATE INDEX IF NOT EXISTS idx_doc_user_folders_user ON doc_user_folders(user_id);",
|
||
|
||
"CREATE TABLE IF NOT EXISTS workspace_folders (id TEXT PRIMARY KEY, user_id TEXT NOT NULL, shared_folder_id TEXT NOT NULL DEFAULT '', path TEXT NOT NULL, sub_path TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL);",
|
||
"CREATE INDEX IF NOT EXISTS idx_workspace_folders_user ON workspace_folders(user_id);",
|
||
"CREATE INDEX IF NOT EXISTS idx_workspace_folders_sf ON workspace_folders(shared_folder_id);",
|
||
|
||
"ALTER TABLE documents ADD COLUMN folder_id TEXT NOT NULL DEFAULT '';",
|
||
"ALTER TABLE documents ADD COLUMN markdown_cache TEXT NOT NULL DEFAULT '';",
|
||
|
||
"CREATE TABLE IF NOT EXISTS blocks (id TEXT PRIMARY KEY, document_id TEXT NOT NULL, content TEXT NOT NULL DEFAULT '', level INT NOT NULL DEFAULT 0, position REAL NOT NULL DEFAULT 0, type TEXT NOT NULL DEFAULT 'bullet', checked INTEGER NOT NULL DEFAULT 0, collapsed INTEGER NOT NULL DEFAULT 0, lang TEXT NOT NULL DEFAULT '', FOREIGN KEY(document_id) REFERENCES documents(id) ON DELETE CASCADE);",
|
||
"CREATE INDEX IF NOT EXISTS idx_blocks_doc_pos ON blocks(document_id, position ASC);",
|
||
|
||
"CREATE TABLE IF NOT EXISTS block_tags (tag TEXT NOT NULL, document_id TEXT NOT NULL, block_id TEXT NOT NULL, PRIMARY KEY(tag, block_id), FOREIGN KEY(document_id) REFERENCES documents(id) ON DELETE CASCADE, FOREIGN KEY(block_id) REFERENCES blocks(id) ON DELETE CASCADE);",
|
||
"CREATE INDEX IF NOT EXISTS idx_block_tags_tag ON block_tags(tag);",
|
||
"CREATE INDEX IF NOT EXISTS idx_block_tags_doc ON block_tags(document_id);",
|
||
}
|
||
for _, q := range columnMigrations {
|
||
_, _ = s.db.Exec(q)
|
||
}
|
||
|
||
// Clean up legacy space and old document block/crdt tables
|
||
_, _ = s.db.Exec(`DROP TABLE IF EXISTS spaces`)
|
||
_, _ = s.db.Exec(`DROP TABLE IF EXISTS space_members`)
|
||
_, _ = s.db.Exec(`DROP TABLE IF EXISTS space_team_shares`)
|
||
_, _ = s.db.Exec(`DROP TABLE IF EXISTS document_crdt_state`)
|
||
_, _ = s.db.Exec(`DROP TABLE IF EXISTS document_crdt_updates`)
|
||
_, _ = s.db.Exec(`DROP TABLE IF EXISTS document_blocks`)
|
||
_, _ = s.db.Exec(`DROP TABLE IF EXISTS doc_block_tags`)
|
||
|
||
// Backfill username for existing accounts where username is empty
|
||
_, _ = s.db.Exec(`UPDATE users SET username = LOWER(SUBSTR(email, 1, INSTR(email, '@') - 1)) WHERE (username IS NULL OR username = '') AND INSTR(email, '@') > 0`)
|
||
|
||
// Ensure all existing document & folder shares are strictly read-only
|
||
_, _ = s.db.Exec(`UPDATE doc_shares SET permission = 'read' WHERE permission != 'read'`)
|
||
_, _ = s.db.Exec(`UPDATE doc_team_shares SET permission = 'read' WHERE permission != 'read'`)
|
||
_, _ = s.db.Exec(`UPDATE workspace_folder_shares SET permission = 'read' WHERE permission != 'read'`)
|
||
|
||
// Default system settings (public registration disabled by default; invite or admin creation required)
|
||
_, _ = s.db.Exec(`INSERT OR IGNORE INTO system_settings (key, value) VALUES
|
||
('sharing_mode', 'teams_only'),
|
||
('allow_registration', 'false'),
|
||
('instance_name', 'MindSift'),
|
||
('smtp_host', ''),
|
||
('smtp_port', '587'),
|
||
('smtp_user', ''),
|
||
('smtp_pass', ''),
|
||
('smtp_from', ''),
|
||
('smtp_tls', 'true')`)
|
||
|
||
// Migrate existing teams table if it still has a legacy FOREIGN KEY to organizations
|
||
var teamsTableSQL string
|
||
_ = s.db.QueryRow(`SELECT sql FROM sqlite_master WHERE type='table' AND name='teams'`).Scan(&teamsTableSQL)
|
||
if strings.Contains(strings.ToUpper(teamsTableSQL), "ORGANIZATIONS") {
|
||
if conn, err := s.db.Conn(context.Background()); err == nil {
|
||
_, _ = conn.ExecContext(context.Background(), `PRAGMA foreign_keys = OFF;`)
|
||
_, _ = conn.ExecContext(context.Background(), `
|
||
BEGIN TRANSACTION;
|
||
CREATE TABLE IF NOT EXISTS teams_migrated (
|
||
id TEXT PRIMARY KEY,
|
||
org_id TEXT NOT NULL DEFAULT 'default',
|
||
name TEXT NOT NULL,
|
||
description TEXT NOT NULL DEFAULT '',
|
||
created_at TEXT NOT NULL
|
||
);
|
||
INSERT OR IGNORE INTO teams_migrated (id, org_id, name, description, created_at)
|
||
SELECT id, COALESCE(NULLIF(org_id, ''), 'default'), name, COALESCE(description, ''), created_at FROM teams;
|
||
DROP TABLE teams;
|
||
ALTER TABLE teams_migrated RENAME TO teams;
|
||
COMMIT;
|
||
`)
|
||
_, _ = conn.ExecContext(context.Background(), `PRAGMA foreign_keys = ON;`)
|
||
_ = conn.Close()
|
||
}
|
||
}
|
||
|
||
// Clean up legacy roles in team_members and users
|
||
_, _ = s.db.Exec(`UPDATE team_members SET role = 'admin' WHERE role IN ('team_admin', 'owner')`)
|
||
_, _ = s.db.Exec(`UPDATE team_members SET role = 'member' WHERE role NOT IN ('admin', 'member')`)
|
||
_, _ = s.db.Exec(`UPDATE users SET role = 'member' WHERE role = 'team_admin'`)
|
||
|
||
// Migrate workspace documents, blocks, folders, and strip legacy folder:* tags into pure DB tables
|
||
s.migrateWorkspaceToPureDB()
|
||
|
||
// Reindex block tags if table is newly added or empty
|
||
var tagCount int
|
||
_ = s.db.QueryRow(`SELECT COUNT(*) FROM block_tags`).Scan(&tagCount)
|
||
if tagCount == 0 {
|
||
_ = s.reindexAllDocBlockTags()
|
||
}
|
||
|
||
return nil
|
||
}
|
||
|
||
// SeedStarterContent populates realistic initial content for a newly created user if requested
|
||
func (s *Store) SeedStarterContent(user *domain.User) error {
|
||
now := domain.NowISO()
|
||
|
||
starterThoughts := []struct {
|
||
content string
|
||
priority string
|
||
source string
|
||
dueDate string
|
||
}{
|
||
{
|
||
content: "Zündkerzen und Luftfilter für den Rasenmäher bestellen #garage #wartung",
|
||
priority: "normal",
|
||
source: "manual",
|
||
},
|
||
{
|
||
content: "Urlaubsidee: Wanderhütte in Südtirol für Oktober buchen #urlaub #familie",
|
||
priority: "high",
|
||
source: "voice",
|
||
},
|
||
{
|
||
content: "Kellerfeuchtigkeit über 68% gestiegen – Entfeuchter prüfen #smarthome #alarm",
|
||
priority: "urgent",
|
||
source: "webhook",
|
||
},
|
||
}
|
||
|
||
for _, st := range starterThoughts {
|
||
_, _ = s.CreateThought(user.ID, st.content, st.priority, st.source, st.dueDate)
|
||
}
|
||
|
||
keptTask, err := s.CreateThought(user.ID, "Gitea CI/CD Pipeline für MindSift Container testen #devops #docker", "high", "manual", now[:10])
|
||
if err == nil && keptTask != nil {
|
||
_, _ = s.UpdateThoughtTriage(user.ID, keptTask.ID, "keep", "task", "", now[:10], "")
|
||
}
|
||
|
||
shopList, err := s.CreateList(user.ID, "Wocheneinkauf Supermarkt", "🛒", "shopping")
|
||
if err == nil && shopList != nil {
|
||
_, _ = s.CreateListItem(shopList.ID, "Hafermilch (Barista Edition)", "Kühlregal", "3x", 0)
|
||
_, _ = s.CreateListItem(shopList.ID, "Espressobohnen (100% Arabica)", "Kaffee & Tee", "1 kg", 1)
|
||
}
|
||
|
||
starterBlocks := []domain.DocumentBlock{
|
||
{ID: "blk_1", Type: "callout", Content: "Willkommen in deinem MindSift Workspace! Hier wachsen flüchtige Gedanken aus deiner Inbox zu strukturierten Projekten heran."},
|
||
{ID: "blk_2", Type: "h2", Content: "Projekt-Checkliste"},
|
||
{ID: "blk_3", Type: "todo", Content: "MindSift als Docker-Container auf eigenem Server starten", Checked: true},
|
||
}
|
||
|
||
_, _ = s.CreateDocument(user.ID, "", "MindSift Architektur & Notizen", "🚀", "", starterBlocks, []string{"mindsift", "workflow"}, true)
|
||
return nil
|
||
}
|
||
|
||
// EnsureDefaultUser is retained strictly for unit test convenience
|
||
func (s *Store) EnsureDefaultUser() (*domain.User, error) {
|
||
s.initMu.Lock()
|
||
defer s.initMu.Unlock()
|
||
|
||
count, err := s.CountUsers()
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if count > 0 {
|
||
return s.GetFirstUser()
|
||
}
|
||
|
||
hash, err := auth.HashPassword("mindsift")
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
user, err := s.CreateUser("test@mindsift.local", hash, "Admin", "admin")
|
||
if err != nil {
|
||
return s.GetFirstUser()
|
||
}
|
||
return user, nil
|
||
}
|
||
|
||
// --- USERS ---
|
||
|
||
const userSelectCols = `id, email, username, password_hash, display_name, role, is_active, token_version, alarm_channels, alarm_email, discord_webhook_url, custom_webhook_url, notify_before_minutes, avatar_url, created_at`
|
||
|
||
func parseAlarmChannels(raw string) []string {
|
||
parts := strings.Split(raw, ",")
|
||
res := make([]string, 0, len(parts))
|
||
seen := make(map[string]bool)
|
||
for _, p := range parts {
|
||
p = strings.ToLower(strings.TrimSpace(p))
|
||
if (p == "email" || p == "discord" || p == "webhook") && !seen[p] {
|
||
seen[p] = true
|
||
res = append(res, p)
|
||
}
|
||
}
|
||
return res
|
||
}
|
||
|
||
func scanUser(row interface{ Scan(dest ...any) error }) (*domain.User, error) {
|
||
u := &domain.User{}
|
||
var isActiveInt int
|
||
var channelsRaw string
|
||
err := row.Scan(
|
||
&u.ID, &u.Email, &u.Username, &u.PasswordHash, &u.DisplayName,
|
||
&u.Role, &isActiveInt, &u.TokenVersion, &channelsRaw, &u.AlarmEmail,
|
||
&u.DiscordWebhookURL, &u.CustomWebhookURL, &u.NotifyBeforeMinutes, &u.AvatarURL, &u.CreatedAt,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
u.IsActive = isActiveInt != 0
|
||
if u.TokenVersion <= 0 {
|
||
u.TokenVersion = 1
|
||
}
|
||
if u.Role == "" {
|
||
u.Role = "member"
|
||
}
|
||
u.AlarmChannels = parseAlarmChannels(channelsRaw)
|
||
if u.AlarmEmail == "" {
|
||
u.AlarmEmail = u.Email
|
||
}
|
||
return u, nil
|
||
}
|
||
|
||
func (s *Store) CountUsers() (int, error) {
|
||
var count int
|
||
err := s.db.QueryRow(`SELECT COUNT(*) FROM users`).Scan(&count)
|
||
return count, err
|
||
}
|
||
|
||
func (s *Store) CreateUser(email, passwordHash, displayName, role string) (*domain.User, error) {
|
||
cleanEmail := strings.ToLower(strings.TrimSpace(email))
|
||
username := strings.Split(cleanEmail, "@")[0]
|
||
return s.CreateUserWithUsername(cleanEmail, username, passwordHash, displayName, role)
|
||
}
|
||
|
||
func (s *Store) CreateUserWithUsername(email, username, passwordHash, displayName, role string) (*domain.User, error) {
|
||
if role != "admin" && role != "member" {
|
||
role = "member"
|
||
}
|
||
cleanEmail := strings.ToLower(strings.TrimSpace(email))
|
||
cleanUsername := strings.ToLower(strings.TrimSpace(username))
|
||
if cleanUsername == "" {
|
||
cleanUsername = strings.Split(cleanEmail, "@")[0]
|
||
}
|
||
u := &domain.User{
|
||
ID: domain.NewID("usr"),
|
||
Email: cleanEmail,
|
||
Username: cleanUsername,
|
||
PasswordHash: passwordHash,
|
||
DisplayName: strings.TrimSpace(displayName),
|
||
Role: role,
|
||
IsActive: true,
|
||
TokenVersion: 1,
|
||
AlarmChannels: []string{},
|
||
AlarmEmail: cleanEmail,
|
||
DiscordWebhookURL: "",
|
||
CustomWebhookURL: "",
|
||
NotifyBeforeMinutes: 30,
|
||
AvatarURL: "",
|
||
CreatedAt: domain.NowISO(),
|
||
}
|
||
_, err := s.db.Exec(
|
||
`INSERT INTO users (id, email, username, password_hash, display_name, role, is_active, token_version, alarm_channels, alarm_email, discord_webhook_url, custom_webhook_url, notify_before_minutes, avatar_url, created_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, 1, 1, '', ?, '', '', 30, '', ?)`,
|
||
u.ID, u.Email, u.Username, u.PasswordHash, u.DisplayName, u.Role, u.AlarmEmail, u.CreatedAt,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
return u, nil
|
||
}
|
||
|
||
func (s *Store) GetUserByEmail(email string) (*domain.User, error) {
|
||
return s.GetUserByLogin(email)
|
||
}
|
||
|
||
func (s *Store) GetUserByLogin(login string) (*domain.User, error) {
|
||
cleanLogin := strings.ToLower(strings.TrimSpace(login))
|
||
if cleanLogin == "" {
|
||
return nil, errors.New("benutzername oder e-mail darf nicht leer sein")
|
||
}
|
||
row := s.db.QueryRow(
|
||
`SELECT `+userSelectCols+` FROM users
|
||
WHERE LOWER(email) = ?
|
||
OR (username != '' AND LOWER(username) = ?)
|
||
OR LOWER(display_name) = ?
|
||
OR (INSTR(email, '@') > 0 AND LOWER(SUBSTR(email, 1, INSTR(email, '@') - 1)) = ?)
|
||
LIMIT 1`,
|
||
cleanLogin, cleanLogin, cleanLogin, cleanLogin,
|
||
)
|
||
return scanUser(row)
|
||
}
|
||
|
||
// EnsureDemoAccounts seeds admin/admin and user/user accounts for demo evaluation
|
||
func (s *Store) EnsureDemoAccounts() error {
|
||
s.initMu.Lock()
|
||
defer s.initMu.Unlock()
|
||
|
||
adminHash, err := auth.HashPassword("admin")
|
||
if err != nil {
|
||
return err
|
||
}
|
||
userHash, err := auth.HashPassword("user")
|
||
if err != nil {
|
||
return err
|
||
}
|
||
|
||
// 1. Admin user (admin / admin)
|
||
adminUser, err := s.GetUserByLogin("admin")
|
||
if err != nil || adminUser == nil {
|
||
adminUser, err = s.CreateUserWithUsername("admin@mindsift.local", "admin", adminHash, "Admin", "admin")
|
||
if err != nil {
|
||
return fmt.Errorf("failed to create demo admin: %w", err)
|
||
}
|
||
_ = s.SeedStarterContent(adminUser)
|
||
} else {
|
||
_ = s.UpdateUserPassword(adminUser.ID, adminHash)
|
||
_, _ = s.db.Exec(`UPDATE users SET is_active = 1, role = 'admin' WHERE id = ?`, adminUser.ID)
|
||
}
|
||
|
||
// 2. Regular user (user / user)
|
||
normalUser, err := s.GetUserByLogin("user")
|
||
if err != nil || normalUser == nil {
|
||
_, err = s.CreateUserWithUsername("user@mindsift.local", "user", userHash, "User", "member")
|
||
if err != nil {
|
||
return fmt.Errorf("failed to create demo user: %w", err)
|
||
}
|
||
} else {
|
||
_ = s.UpdateUserPassword(normalUser.ID, userHash)
|
||
_, _ = s.db.Exec(`UPDATE users SET is_active = 1 WHERE id = ?`, normalUser.ID)
|
||
}
|
||
|
||
return nil
|
||
}
|
||
|
||
func (s *Store) GetUserByID(id string) (*domain.User, error) {
|
||
row := s.db.QueryRow(
|
||
`SELECT `+userSelectCols+` FROM users WHERE id = ?`,
|
||
id,
|
||
)
|
||
return scanUser(row)
|
||
}
|
||
|
||
func (s *Store) GetFirstUser() (*domain.User, error) {
|
||
row := s.db.QueryRow(
|
||
`SELECT ` + userSelectCols + ` FROM users ORDER BY created_at ASC LIMIT 1`,
|
||
)
|
||
return scanUser(row)
|
||
}
|
||
|
||
func (s *Store) GetAllUsers() ([]domain.User, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT ` + userSelectCols + ` FROM users ORDER BY created_at ASC`,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
users := make([]domain.User, 0)
|
||
for rows.Next() {
|
||
if u, err := scanUser(rows); err == nil {
|
||
users = append(users, *u)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
return users, nil
|
||
}
|
||
|
||
func (s *Store) GetDiscoverableUsers(viewerID string) ([]domain.User, error) {
|
||
viewer, err := s.GetUserByID(viewerID)
|
||
if err != nil {
|
||
return []domain.User{}, nil
|
||
}
|
||
sys, _ := s.GetSystemSettings()
|
||
mode := "teams_only"
|
||
if sys != nil && sys.SharingMode != "" {
|
||
mode = sys.SharingMode
|
||
}
|
||
|
||
if viewer.Role == "admin" || mode == "open" || mode == "teams_only" {
|
||
return s.GetAllUsers()
|
||
}
|
||
if mode == "strict_private" {
|
||
return []domain.User{*viewer}, nil
|
||
}
|
||
|
||
// users sharing at least one team, organization or space with viewer
|
||
rows, err := s.db.Query(
|
||
`SELECT DISTINCT `+userSelectCols+` FROM users WHERE id = ? OR id IN (
|
||
SELECT tm2.user_id FROM team_members tm1
|
||
JOIN team_members tm2 ON tm1.team_id = tm2.team_id
|
||
WHERE tm1.user_id = ?
|
||
)`,
|
||
viewerID, viewerID,
|
||
)
|
||
if err != nil {
|
||
return []domain.User{*viewer}, nil
|
||
}
|
||
users := make([]domain.User, 0)
|
||
for rows.Next() {
|
||
if u, err := scanUser(rows); err == nil {
|
||
users = append(users, *u)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
return users, nil
|
||
}
|
||
|
||
func (s *Store) UpdateUser(
|
||
userID string,
|
||
displayName, email, role *string,
|
||
isActive *bool,
|
||
discordWebhookURL *string,
|
||
notifyBeforeMinutes *int,
|
||
) (*domain.User, error) {
|
||
return s.UpdateUserFull(userID, displayName, email, role, isActive, nil, nil, discordWebhookURL, nil, notifyBeforeMinutes, nil)
|
||
}
|
||
|
||
func (s *Store) UpdateUserFull(
|
||
userID string,
|
||
displayName, email, role *string,
|
||
isActive *bool,
|
||
alarmChannels *[]string,
|
||
alarmEmail, discordWebhookURL, customWebhookURL *string,
|
||
notifyBeforeMinutes *int,
|
||
avatarURL *string,
|
||
) (*domain.User, error) {
|
||
u, err := s.GetUserByID(userID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if displayName != nil {
|
||
u.DisplayName = strings.TrimSpace(*displayName)
|
||
}
|
||
if email != nil {
|
||
u.Email = strings.ToLower(strings.TrimSpace(*email))
|
||
}
|
||
if role != nil && (*role == "admin" || *role == "member") {
|
||
u.Role = *role
|
||
}
|
||
if isActive != nil {
|
||
u.IsActive = *isActive
|
||
}
|
||
if alarmChannels != nil {
|
||
u.AlarmChannels = parseAlarmChannels(strings.Join(*alarmChannels, ","))
|
||
}
|
||
if alarmEmail != nil {
|
||
u.AlarmEmail = strings.TrimSpace(*alarmEmail)
|
||
}
|
||
if discordWebhookURL != nil {
|
||
u.DiscordWebhookURL = strings.TrimSpace(*discordWebhookURL)
|
||
}
|
||
if customWebhookURL != nil {
|
||
u.CustomWebhookURL = strings.TrimSpace(*customWebhookURL)
|
||
}
|
||
if notifyBeforeMinutes != nil {
|
||
u.NotifyBeforeMinutes = *notifyBeforeMinutes
|
||
}
|
||
if avatarURL != nil {
|
||
u.AvatarURL = strings.TrimSpace(*avatarURL)
|
||
}
|
||
if (role != nil && *role != u.Role) || (isActive != nil && !*isActive && u.IsActive) {
|
||
u.TokenVersion++
|
||
}
|
||
activeInt := 0
|
||
if u.IsActive {
|
||
activeInt = 1
|
||
}
|
||
channelsStr := strings.Join(u.AlarmChannels, ",")
|
||
_, err = s.db.Exec(
|
||
`UPDATE users SET display_name = ?, email = ?, role = ?, is_active = ?, token_version = ?, alarm_channels = ?, alarm_email = ?, discord_webhook_url = ?, custom_webhook_url = ?, notify_before_minutes = ?, avatar_url = ? WHERE id = ?`,
|
||
u.DisplayName, u.Email, u.Role, activeInt, u.TokenVersion, channelsStr, u.AlarmEmail, u.DiscordWebhookURL, u.CustomWebhookURL, u.NotifyBeforeMinutes, u.AvatarURL, userID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
return u, nil
|
||
}
|
||
|
||
func (s *Store) UpdateUserPassword(userID, passwordHash string) error {
|
||
_, err := s.db.Exec(`UPDATE users SET password_hash = ?, token_version = token_version + 1 WHERE id = ?`, passwordHash, userID)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) DeleteUser(userID string) error {
|
||
_, _ = s.db.Exec(`DELETE FROM revoked_tokens WHERE user_id = ?`, userID)
|
||
_, err := s.db.Exec(`DELETE FROM users WHERE id = ?`, userID)
|
||
return err
|
||
}
|
||
|
||
// RevokeToken adds an individual JWT to the blacklist until its expiration date
|
||
func (s *Store) RevokeToken(tokenStr, userID string, expiresAt time.Time) error {
|
||
hash := auth.HashAPIKey(tokenStr)
|
||
_, err := s.db.Exec(
|
||
`INSERT OR REPLACE INTO revoked_tokens (token_hash, user_id, expires_at, revoked_at) VALUES (?, ?, ?, ?)`,
|
||
hash, userID, expiresAt.UTC().Format(time.RFC3339), time.Now().UTC().Format(time.RFC3339),
|
||
)
|
||
// Idempotently prune old expired tokens
|
||
_, _ = s.db.Exec(`DELETE FROM revoked_tokens WHERE expires_at < ?`, time.Now().UTC().Format(time.RFC3339))
|
||
return err
|
||
}
|
||
|
||
// IsTokenRevoked checks if a specific JWT token has been explicitly blacklisted
|
||
func (s *Store) IsTokenRevoked(tokenStr string) bool {
|
||
hash := auth.HashAPIKey(tokenStr)
|
||
var count int
|
||
err := s.db.QueryRow(
|
||
`SELECT COUNT(1) FROM revoked_tokens WHERE token_hash = ? AND expires_at >= ?`,
|
||
hash, time.Now().UTC().Format(time.RFC3339),
|
||
).Scan(&count)
|
||
return err == nil && count > 0
|
||
}
|
||
|
||
// RevokeAllUserTokens increments the user's token_version, instantly invalidating all existing JWTs for that user
|
||
func (s *Store) RevokeAllUserTokens(userID string) error {
|
||
_, err := s.db.Exec(`UPDATE users SET token_version = token_version + 1 WHERE id = ?`, userID)
|
||
return err
|
||
}
|
||
|
||
// --- THOUGHTS & TRIAGE ---
|
||
|
||
func (s *Store) CreateThought(userID, content, priority, source, dueDate string) (*domain.Thought, error) {
|
||
return s.CreateThoughtWithDetails(userID, content, priority, source, dueDate, "", "todo", "")
|
||
}
|
||
|
||
func (s *Store) CreateThoughtWithDetails(userID, content, priority, source, dueDate, taskListID, kanbanStatus, notes string) (*domain.Thought, error) {
|
||
content = strings.TrimSpace(content)
|
||
if content == "" {
|
||
return nil, errors.New("content cannot be empty")
|
||
}
|
||
if priority == "" {
|
||
priority = "normal"
|
||
}
|
||
if source == "" {
|
||
source = "manual"
|
||
}
|
||
if kanbanStatus == "" {
|
||
kanbanStatus = "todo"
|
||
}
|
||
|
||
tags := domain.ExtractTags(content)
|
||
if strings.EqualFold(source, "voice") {
|
||
hasDiktat := false
|
||
for _, tag := range tags {
|
||
if strings.EqualFold(tag, "diktat") {
|
||
hasDiktat = true
|
||
break
|
||
}
|
||
}
|
||
if !hasDiktat {
|
||
tags = append(tags, "diktat")
|
||
}
|
||
}
|
||
if strings.EqualFold(source, "webhook") {
|
||
hasWebhook := false
|
||
for _, tag := range tags {
|
||
if strings.EqualFold(tag, "webhook") {
|
||
hasWebhook = true
|
||
break
|
||
}
|
||
}
|
||
if !hasWebhook {
|
||
tags = append(tags, "webhook")
|
||
}
|
||
}
|
||
tagsJSON, _ := json.Marshal(tags)
|
||
|
||
t := &domain.Thought{
|
||
ID: domain.NewID("th"),
|
||
UserID: userID,
|
||
Content: content,
|
||
Tags: tags,
|
||
Priority: priority,
|
||
Source: source,
|
||
Status: domain.StatusInbox,
|
||
DueDate: dueDate,
|
||
TaskListID: taskListID,
|
||
KanbanStatus: kanbanStatus,
|
||
Notes: notes,
|
||
CreatedAt: domain.NowISO(),
|
||
}
|
||
|
||
_, err := s.db.Exec(
|
||
`INSERT INTO thoughts (id, user_id, content, tags, priority, source, status, is_completed, due_date, task_list_id, kanban_status, notes, alarm_sent, created_at, subtasks)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, 0, ?, ?, ?, ?, 0, ?, '[]')`,
|
||
t.ID, t.UserID, t.Content, string(tagsJSON), t.Priority, t.Source, string(t.Status), t.DueDate, t.TaskListID, t.KanbanStatus, t.Notes, t.CreatedAt,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
t.Subtasks = []domain.SubTask{}
|
||
return t, nil
|
||
}
|
||
|
||
func scanThought(rows interface {
|
||
Scan(dest ...any) error
|
||
}) (*domain.Thought, error) {
|
||
t := &domain.Thought{}
|
||
var tagsStr, subtasksStr, statusStr string
|
||
var isCompleted, alarmSent, sortOrder int
|
||
err := rows.Scan(
|
||
&t.ID, &t.UserID, &t.Content, &tagsStr, &t.Priority, &t.Source,
|
||
&statusStr, &isCompleted, &t.ConvertedType, &t.ConvertedRefID,
|
||
&t.DueDate, &t.TaskListID, &t.KanbanStatus, &t.Notes, &alarmSent,
|
||
&t.CreatedAt, &t.TriagedAt, &subtasksStr, &sortOrder,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
t.SortOrder = sortOrder
|
||
t.Status = domain.ThoughtStatus(statusStr)
|
||
t.IsCompleted = isCompleted != 0
|
||
t.AlarmSent = alarmSent != 0
|
||
if t.KanbanStatus == "" {
|
||
if t.IsCompleted {
|
||
t.KanbanStatus = "done"
|
||
} else {
|
||
t.KanbanStatus = "todo"
|
||
}
|
||
}
|
||
t.Tags = []string{}
|
||
_ = json.Unmarshal([]byte(tagsStr), &t.Tags)
|
||
if t.ConvertedType == "document_block" && strings.Contains(t.ConvertedRefID, ":") {
|
||
parts := strings.SplitN(t.ConvertedRefID, ":", 2)
|
||
t.DocID = parts[0]
|
||
t.BlockID = parts[1]
|
||
}
|
||
t.Subtasks = []domain.SubTask{}
|
||
if subtasksStr != "" && subtasksStr != "null" {
|
||
_ = json.Unmarshal([]byte(subtasksStr), &t.Subtasks)
|
||
}
|
||
return t, nil
|
||
}
|
||
|
||
func (s *Store) accessibleTaskListIDsQuery() string {
|
||
return `SELECT id FROM task_lists
|
||
WHERE user_id = ?
|
||
OR (team_id != '' AND team_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
OR id IN (
|
||
SELECT task_list_id FROM task_list_shares
|
||
WHERE (target_type = 'user' AND target_id = ?)
|
||
OR (target_type = 'team' AND target_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
)`
|
||
}
|
||
|
||
func (s *Store) GetThoughtByID(userID, thoughtID string) (*domain.Thought, error) {
|
||
row := s.db.QueryRow(
|
||
`SELECT id, user_id, content, tags, priority, source, status, is_completed, converted_type, converted_ref_id, due_date, task_list_id, kanban_status, notes, alarm_sent, created_at, triaged_at, subtasks, sort_order
|
||
FROM thoughts WHERE id = ? AND (user_id = ? OR task_list_id IN (`+s.accessibleTaskListIDsQuery()+`))`,
|
||
thoughtID, userID, userID, userID, userID, userID,
|
||
)
|
||
return scanThought(row)
|
||
}
|
||
|
||
func (s *Store) GetThoughts(userID, statusFilter string, limit int) ([]domain.Thought, error) {
|
||
if limit <= 0 || limit > 500 {
|
||
limit = 200
|
||
}
|
||
var rows *sql.Rows
|
||
var err error
|
||
|
||
if statusFilter != "" && statusFilter != "all" {
|
||
rows, err = s.db.Query(
|
||
`SELECT id, user_id, content, tags, priority, source, status, is_completed, converted_type, converted_ref_id, due_date, task_list_id, kanban_status, notes, alarm_sent, created_at, triaged_at, subtasks, sort_order
|
||
FROM thoughts WHERE user_id = ? AND status = ?
|
||
ORDER BY CASE priority WHEN 'urgent' THEN 1 WHEN 'high' THEN 2 WHEN 'normal' THEN 3 ELSE 4 END, sort_order ASC, created_at DESC LIMIT ?`,
|
||
userID, statusFilter, limit,
|
||
)
|
||
} else {
|
||
rows, err = s.db.Query(
|
||
`SELECT id, user_id, content, tags, priority, source, status, is_completed, converted_type, converted_ref_id, due_date, task_list_id, kanban_status, notes, alarm_sent, created_at, triaged_at, subtasks, sort_order
|
||
FROM thoughts WHERE user_id = ?
|
||
ORDER BY sort_order ASC, created_at DESC LIMIT ?`,
|
||
userID, limit,
|
||
)
|
||
}
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
result := make([]domain.Thought, 0)
|
||
for rows.Next() {
|
||
t, err := scanThought(rows)
|
||
if err == nil {
|
||
result = append(result, *t)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
return result, nil
|
||
}
|
||
|
||
func (s *Store) GetTasks(userID string, taskListID string) ([]domain.Thought, error) {
|
||
var rows *sql.Rows
|
||
var err error
|
||
if taskListID != "" {
|
||
rows, err = s.db.Query(
|
||
`SELECT id, user_id, content, tags, priority, source, status, is_completed, converted_type, converted_ref_id, due_date, task_list_id, kanban_status, notes, alarm_sent, created_at, triaged_at, subtasks, sort_order
|
||
FROM thoughts
|
||
WHERE (user_id = ? OR task_list_id IN (`+s.accessibleTaskListIDsQuery()+`))
|
||
AND task_list_id = ?
|
||
AND (status = 'kept' OR (status = 'converted' AND converted_type = 'task'))
|
||
ORDER BY is_completed ASC, sort_order ASC, created_at DESC`,
|
||
userID, userID, userID, userID, userID, taskListID,
|
||
)
|
||
} else {
|
||
rows, err = s.db.Query(
|
||
`SELECT id, user_id, content, tags, priority, source, status, is_completed, converted_type, converted_ref_id, due_date, task_list_id, kanban_status, notes, alarm_sent, created_at, triaged_at, subtasks, sort_order
|
||
FROM thoughts
|
||
WHERE (user_id = ? OR task_list_id IN (`+s.accessibleTaskListIDsQuery()+`))
|
||
AND (status = 'kept' OR (status = 'converted' AND converted_type = 'task'))
|
||
ORDER BY is_completed ASC, sort_order ASC, created_at DESC`,
|
||
userID, userID, userID, userID, userID,
|
||
)
|
||
}
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
result := make([]domain.Thought, 0)
|
||
for rows.Next() {
|
||
t, err := scanThought(rows)
|
||
if err == nil {
|
||
result = append(result, *t)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
return result, nil
|
||
}
|
||
|
||
func (s *Store) ReorderTasks(taskListID string, items []domain.ThoughtReorderItem) error {
|
||
tx, err := s.db.Begin()
|
||
if err != nil {
|
||
return err
|
||
}
|
||
defer tx.Rollback()
|
||
|
||
for _, it := range items {
|
||
if it.KanbanStatus != nil && *it.KanbanStatus != "" {
|
||
isCompleted := 0
|
||
if *it.KanbanStatus == "done" {
|
||
isCompleted = 1
|
||
}
|
||
_, err = tx.Exec(
|
||
`UPDATE thoughts SET sort_order = ?, kanban_status = ?, is_completed = ? WHERE id = ?`,
|
||
it.SortOrder, *it.KanbanStatus, isCompleted, it.ID,
|
||
)
|
||
} else {
|
||
_, err = tx.Exec(
|
||
`UPDATE thoughts SET sort_order = ? WHERE id = ?`,
|
||
it.SortOrder, it.ID,
|
||
)
|
||
}
|
||
if err != nil {
|
||
return err
|
||
}
|
||
}
|
||
|
||
return tx.Commit()
|
||
}
|
||
|
||
// UpdateThoughtTriage handles swipe left (discard), swipe right (keep), restore, and conversion to task/document/list_item
|
||
func (s *Store) UpdateThoughtTriage(userID, thoughtID, action, convertTo, targetListID, dueDate, taskListID string) (*domain.Thought, error) {
|
||
thought, err := s.GetThoughtByID(userID, thoughtID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
now := domain.NowISO()
|
||
newStatus := domain.StatusKept
|
||
convertedType := thought.ConvertedType
|
||
convertedRefID := thought.ConvertedRefID
|
||
if dueDate == "" {
|
||
dueDate = thought.DueDate
|
||
}
|
||
if taskListID == "" {
|
||
taskListID = thought.TaskListID
|
||
}
|
||
|
||
switch action {
|
||
case "discard":
|
||
newStatus = domain.StatusDiscarded
|
||
case "restore":
|
||
newStatus = domain.StatusInbox
|
||
case "keep":
|
||
newStatus = domain.StatusKept
|
||
if convertTo == "task" {
|
||
newStatus = domain.StatusConverted
|
||
convertedType = "task"
|
||
} else if convertTo == "document" {
|
||
newStatus = domain.StatusConverted
|
||
convertedType = "document"
|
||
title := thought.Content
|
||
if len(title) > 72 {
|
||
title = title[:72] + "…"
|
||
}
|
||
blocks := make([]domain.DocumentBlock, 0)
|
||
|
||
// 17. Wenn ich eine Aufgabe zu einer Notiz mache, muss die erste Zeile das "To-Do" noch mal als "To-Do" enthalten, auch wenn es schon in der Überschrift steht. Wenn es vorher schon abgehakt war, dann den Zustand natürlich übernehmen.
|
||
blocks = append(blocks, domain.DocumentBlock{
|
||
ID: domain.NewID("blk"),
|
||
Type: "todo",
|
||
Content: thought.Content,
|
||
Checked: thought.IsCompleted,
|
||
Level: 0,
|
||
})
|
||
|
||
// 17a. Die anderen Details aus der Aufgabe sollen natürlich ebenfalls übernommen werden (Notizen, Subtasks, Fälligkeit/Priorität).
|
||
if strings.TrimSpace(thought.Notes) != "" {
|
||
blocks = append(blocks, domain.DocumentBlock{
|
||
ID: domain.NewID("blk"),
|
||
Type: "bullet",
|
||
Content: strings.TrimSpace(thought.Notes),
|
||
Level: 1,
|
||
})
|
||
}
|
||
if len(thought.Subtasks) > 0 {
|
||
for _, st := range thought.Subtasks {
|
||
blocks = append(blocks, domain.DocumentBlock{
|
||
ID: domain.NewID("blk"),
|
||
Type: "todo",
|
||
Content: st.Title,
|
||
Checked: st.IsCompleted,
|
||
Level: 1,
|
||
})
|
||
}
|
||
}
|
||
var metaItems []string
|
||
if thought.DueDate != "" {
|
||
metaItems = append(metaItems, "Fällig: "+thought.DueDate)
|
||
}
|
||
if thought.Priority != "" && thought.Priority != "normal" {
|
||
metaItems = append(metaItems, "Priorität: "+thought.Priority)
|
||
}
|
||
if len(metaItems) > 0 {
|
||
blocks = append(blocks, domain.DocumentBlock{
|
||
ID: domain.NewID("blk"),
|
||
Type: "bullet",
|
||
Content: strings.Join(metaItems, " • "),
|
||
Level: 1,
|
||
})
|
||
}
|
||
if len(blocks) == 1 {
|
||
blocks = append(blocks, domain.DocumentBlock{
|
||
ID: domain.NewID("blk"),
|
||
Type: "bullet",
|
||
Content: "",
|
||
Level: 0,
|
||
})
|
||
}
|
||
doc, err := s.CreateDocument(userID, "", title, "📄", "", blocks, thought.Tags, false)
|
||
if err == nil && doc != nil {
|
||
convertedRefID = doc.ID
|
||
}
|
||
} else if convertTo == "list_item" && targetListID != "" {
|
||
newStatus = domain.StatusConverted
|
||
convertedType = "list_item"
|
||
item, err := s.CreateListItem(targetListID, thought.Content, "", "", 0)
|
||
if err == nil && item != nil {
|
||
convertedRefID = item.ID
|
||
}
|
||
}
|
||
default:
|
||
return nil, fmt.Errorf("invalid triage action: %s", action)
|
||
}
|
||
|
||
_, err = s.db.Exec(
|
||
`UPDATE thoughts
|
||
SET status = ?, converted_type = ?, converted_ref_id = ?, due_date = ?, task_list_id = ?, triaged_at = ?
|
||
WHERE id = ? AND user_id = ?`,
|
||
string(newStatus), convertedType, convertedRefID, dueDate, taskListID, now, thoughtID, userID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
return s.GetThoughtByID(userID, thoughtID)
|
||
}
|
||
|
||
func (s *Store) UpdateThoughtDetails(userID, thoughtID string, content *string, isCompleted *bool, dueDate *string, priority *string, taskListID *string, kanbanStatus *string, notes *string, subtasks *[]domain.SubTask, tags *[]string) (*domain.Thought, error) {
|
||
thought, err := s.GetThoughtByID(userID, thoughtID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
if content != nil {
|
||
thought.Content = strings.TrimSpace(*content)
|
||
thought.Tags = domain.ExtractTags(thought.Content)
|
||
}
|
||
if tags != nil {
|
||
thought.Tags = *tags
|
||
}
|
||
if isCompleted != nil {
|
||
thought.IsCompleted = *isCompleted
|
||
if *isCompleted {
|
||
thought.KanbanStatus = "done"
|
||
} else if thought.KanbanStatus == "done" {
|
||
thought.KanbanStatus = "todo"
|
||
}
|
||
}
|
||
if dueDate != nil {
|
||
thought.DueDate = *dueDate
|
||
}
|
||
if priority != nil {
|
||
p := strings.ToLower(strings.TrimSpace(*priority))
|
||
if p == "" || p == "none" {
|
||
p = "normal"
|
||
}
|
||
thought.Priority = p
|
||
}
|
||
if taskListID != nil {
|
||
thought.TaskListID = *taskListID
|
||
}
|
||
if kanbanStatus != nil && *kanbanStatus != "" {
|
||
thought.KanbanStatus = *kanbanStatus
|
||
if isCompleted == nil {
|
||
if *kanbanStatus == "done" {
|
||
thought.IsCompleted = true
|
||
} else {
|
||
thought.IsCompleted = false
|
||
}
|
||
}
|
||
}
|
||
if notes != nil {
|
||
thought.Notes = *notes
|
||
}
|
||
if subtasks != nil {
|
||
thought.Subtasks = *subtasks
|
||
}
|
||
|
||
tagsJSON, _ := json.Marshal(thought.Tags)
|
||
subtasksJSON, _ := json.Marshal(thought.Subtasks)
|
||
completedInt := 0
|
||
if thought.IsCompleted {
|
||
completedInt = 1
|
||
}
|
||
|
||
_, err = s.db.Exec(
|
||
`UPDATE thoughts SET content = ?, tags = ?, is_completed = ?, due_date = ?, priority = ?, task_list_id = ?, kanban_status = ?, notes = ?, subtasks = ? WHERE id = ?`,
|
||
thought.Content, string(tagsJSON), completedInt, thought.DueDate, thought.Priority, thought.TaskListID, thought.KanbanStatus, thought.Notes, string(subtasksJSON), thoughtID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
return thought, nil
|
||
}
|
||
|
||
func (s *Store) DeleteThought(userID, thoughtID string) error {
|
||
thought, err := s.GetThoughtByID(userID, thoughtID)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
_, err = s.db.Exec(`DELETE FROM thoughts WHERE id = ?`, thought.ID)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) PurgeDiscardedThoughts(userID string) (int64, error) {
|
||
res, err := s.db.Exec(`DELETE FROM thoughts WHERE user_id = ? AND status = 'discarded'`, userID)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
return res.RowsAffected()
|
||
}
|
||
|
||
// --- DOCUMENTS (WORKSPACE - PURE DATABASE ARCHITECTURE) ---
|
||
|
||
func stripFolderTags(tags []string) []string {
|
||
if len(tags) == 0 {
|
||
return []string{}
|
||
}
|
||
cleaned := make([]string, 0, len(tags))
|
||
for _, t := range tags {
|
||
if !strings.HasPrefix(strings.TrimSpace(t), "folder:") {
|
||
cleaned = append(cleaned, t)
|
||
}
|
||
}
|
||
return cleaned
|
||
}
|
||
|
||
func (s *Store) migrateWorkspaceToPureDB() {
|
||
now := domain.NowISO()
|
||
|
||
// 1. Migrate documents.blocks_json into document_blocks for any doc without rows in document_blocks
|
||
rows, err := s.db.Query(`
|
||
SELECT d.id, d.user_id, d.blocks_json, d.tags, d.folder_path, d.updated_at
|
||
FROM documents d
|
||
`)
|
||
if err == nil {
|
||
type migDoc struct {
|
||
id, userID, blocksJSON, tagsJSON, folderPath, updatedAt string
|
||
}
|
||
var docsToMigrate []migDoc
|
||
for rows.Next() {
|
||
var md migDoc
|
||
if err := rows.Scan(&md.id, &md.userID, &md.blocksJSON, &md.tagsJSON, &md.folderPath, &md.updatedAt); err == nil {
|
||
docsToMigrate = append(docsToMigrate, md)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
|
||
for _, md := range docsToMigrate {
|
||
var count int
|
||
_ = s.db.QueryRow(`SELECT COUNT(*) FROM blocks WHERE document_id = ?`, md.id).Scan(&count)
|
||
if count == 0 && md.blocksJSON != "" && md.blocksJSON != "[]" {
|
||
var blocks []domain.DocumentBlock
|
||
if err := json.Unmarshal([]byte(md.blocksJSON), &blocks); err == nil && len(blocks) > 0 {
|
||
ts := md.updatedAt
|
||
if ts == "" {
|
||
ts = now
|
||
}
|
||
s.replaceDocBlocksInDB(md.id, md.userID, blocks, ts)
|
||
}
|
||
}
|
||
|
||
// Strip legacy folder:* tags from documents.tags
|
||
if strings.Contains(md.tagsJSON, "folder:") {
|
||
var tags []string
|
||
if err := json.Unmarshal([]byte(md.tagsJSON), &tags); err == nil {
|
||
cleaned := stripFolderTags(tags)
|
||
cleanedBytes, _ := json.Marshal(cleaned)
|
||
_, _ = s.db.Exec(`UPDATE documents SET tags = ? WHERE id = ?`, string(cleanedBytes), md.id)
|
||
}
|
||
}
|
||
|
||
// Ensure personal folder exists in workspace_folders
|
||
if md.folderPath != "" {
|
||
var existsID string
|
||
err := s.db.QueryRow(`SELECT id FROM workspace_folders WHERE user_id = ? AND path = ?`, md.userID, md.folderPath).Scan(&existsID)
|
||
if err == sql.ErrNoRows {
|
||
_, _ = s.db.Exec(`INSERT INTO workspace_folders (id, user_id, shared_folder_id, path, sub_path, created_at) VALUES (?, ?, '', ?, '', ?)`,
|
||
domain.NewID("wfld"), md.userID, md.folderPath, now)
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
// 2. Sync all existing shared folders so all docs/subfolders in their owner's mount path are linked
|
||
sfRows, err := s.db.Query(`
|
||
SELECT f.id, f.owner_id, COALESCE(m.mount_path, f.name)
|
||
FROM workspace_shared_folders f
|
||
LEFT JOIN workspace_folder_mounts m ON m.folder_id = f.id AND m.user_id = f.owner_id
|
||
`)
|
||
if err == nil {
|
||
type sfInfo struct{ id, ownerID, mountPath string }
|
||
var sfs []sfInfo
|
||
for sfRows.Next() {
|
||
var info sfInfo
|
||
if err := sfRows.Scan(&info.id, &info.ownerID, &info.mountPath); err == nil {
|
||
sfs = append(sfs, info)
|
||
}
|
||
}
|
||
_ = sfRows.Close()
|
||
for _, info := range sfs {
|
||
s.syncSharedFolderContents(info.ownerID, info.id, info.mountPath)
|
||
}
|
||
}
|
||
}
|
||
|
||
func (s *Store) loadDocBlocks(docID string, fallbackJSON string) []domain.DocumentBlock {
|
||
rows, err := s.db.Query(`
|
||
SELECT id, document_id, type, content, level, position, checked, collapsed, lang
|
||
FROM blocks
|
||
WHERE document_id = ?
|
||
ORDER BY position ASC, rowid ASC
|
||
`, docID)
|
||
if err == nil {
|
||
blocks := make([]domain.DocumentBlock, 0)
|
||
for rows.Next() {
|
||
var b domain.DocumentBlock
|
||
var chk, col int
|
||
if scanErr := rows.Scan(&b.ID, &b.DocumentID, &b.Type, &b.Content, &b.Level, &b.Position, &chk, &col, &b.Lang); scanErr == nil {
|
||
b.Checked = chk != 0
|
||
b.Collapsed = col != 0
|
||
blocks = append(blocks, b)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
if len(blocks) > 0 {
|
||
return blocks
|
||
}
|
||
}
|
||
|
||
blocks := make([]domain.DocumentBlock, 0)
|
||
if fallbackJSON != "" && fallbackJSON != "[]" {
|
||
_ = json.Unmarshal([]byte(fallbackJSON), &blocks)
|
||
if len(blocks) > 0 {
|
||
s.replaceDocBlocksInDB(docID, "", blocks, domain.NowISO())
|
||
}
|
||
}
|
||
return blocks
|
||
}
|
||
|
||
func (s *Store) replaceDocBlocksInDB(docID, _ string, blocks []domain.DocumentBlock, now string) {
|
||
_, _ = s.db.Exec(`DELETE FROM blocks WHERE document_id = ?`, docID)
|
||
if len(blocks) > 0 {
|
||
stmt, err := s.db.Prepare(`
|
||
INSERT INTO blocks (id, document_id, content, level, position, type, checked, collapsed, lang)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
ON CONFLICT(id) DO UPDATE SET
|
||
document_id = excluded.document_id,
|
||
content = excluded.content,
|
||
level = excluded.level,
|
||
position = excluded.position,
|
||
type = excluded.type,
|
||
checked = excluded.checked,
|
||
collapsed = excluded.collapsed,
|
||
lang = excluded.lang
|
||
`)
|
||
if err == nil {
|
||
defer stmt.Close()
|
||
for i, b := range blocks {
|
||
bID := strings.TrimSpace(b.ID)
|
||
if bID == "" {
|
||
bID = domain.NewID("blk")
|
||
}
|
||
bType := b.Type
|
||
if bType == "" {
|
||
bType = "bullet"
|
||
}
|
||
chk := 0
|
||
if b.Checked {
|
||
chk = 1
|
||
}
|
||
col := 0
|
||
if b.Collapsed {
|
||
col = 1
|
||
}
|
||
pos := b.Position
|
||
if pos <= 0 {
|
||
pos = float64((i + 1) * 1000)
|
||
}
|
||
_, _ = stmt.Exec(bID, docID, b.Content, b.Level, pos, bType, chk, col, b.Lang)
|
||
}
|
||
}
|
||
}
|
||
|
||
// Update markdown_cache on documents
|
||
mdCache := domain.CompileBlocksToMarkdown(blocks)
|
||
_, _ = s.db.Exec(`UPDATE documents SET markdown_cache = ?, updated_at = ? WHERE id = ?`, mdCache, now, docID)
|
||
}
|
||
|
||
func (s *Store) LoadDocumentState(docID string) (string, []domain.DocumentBlock, error) {
|
||
var title, blocksJSON string
|
||
err := s.db.QueryRow(`SELECT title, blocks_json FROM documents WHERE id = ?`, docID).Scan(&title, &blocksJSON)
|
||
if err != nil {
|
||
return "", nil, err
|
||
}
|
||
blocks := s.loadDocBlocks(docID, blocksJSON)
|
||
return title, blocks, nil
|
||
}
|
||
|
||
// FlushDocumentState persists the in-memory blocks into `blocks`, extracts all #tags into `block_tags`,
|
||
// and compiles the hierarchical Markdown into `documents.markdown_cache`.
|
||
func (s *Store) FlushDocumentState(docID string, title string, blocks []domain.DocumentBlock) error {
|
||
now := domain.NowISO()
|
||
|
||
// Normalize positions sequentially
|
||
normalized := make([]domain.DocumentBlock, len(blocks))
|
||
var plainBuilder strings.Builder
|
||
for i, b := range blocks {
|
||
if strings.TrimSpace(b.ID) == "" {
|
||
b.ID = domain.NewID("blk")
|
||
}
|
||
if b.Type == "" {
|
||
b.Type = "bullet"
|
||
}
|
||
b.DocumentID = docID
|
||
b.Position = float64((i + 1) * 1000)
|
||
normalized[i] = b
|
||
if b.Content != "" {
|
||
plainBuilder.WriteString(b.Content)
|
||
plainBuilder.WriteString("\n")
|
||
}
|
||
}
|
||
|
||
s.replaceDocBlocksInDB(docID, "", normalized, now)
|
||
_ = s.indexDocBlockTags(docID, "", normalized)
|
||
|
||
mdCache := domain.CompileBlocksToMarkdown(normalized)
|
||
plainContent := plainBuilder.String()
|
||
blocksJSON, _ := json.Marshal(normalized)
|
||
|
||
if strings.TrimSpace(title) != "" {
|
||
_, err := s.db.Exec(`
|
||
UPDATE documents
|
||
SET title = ?, markdown_cache = ?, content = ?, blocks_json = ?, updated_at = ?
|
||
WHERE id = ?
|
||
`, title, mdCache, plainContent, string(blocksJSON), now, docID)
|
||
return err
|
||
}
|
||
|
||
_, err := s.db.Exec(`
|
||
UPDATE documents
|
||
SET markdown_cache = ?, content = ?, blocks_json = ?, updated_at = ?
|
||
WHERE id = ?
|
||
`, mdCache, plainContent, string(blocksJSON), now, docID)
|
||
return err
|
||
}
|
||
|
||
// PatchDocumentBlocks applies atomic block-level upserts, deletions, and order reconciliation without overwriting concurrent edits from other sessions.
|
||
func (s *Store) PatchDocumentBlocks(userID, docID string, req domain.DocBlocksPatchRequest) (*domain.Document, []string, error) {
|
||
doc, err := s.GetDocumentByID(userID, docID)
|
||
if err != nil {
|
||
return nil, nil, err
|
||
}
|
||
if !doc.IsOwner {
|
||
return nil, nil, fmt.Errorf("keine Schreibberechtigung für dieses Dokument (nur Lesezugriff)")
|
||
}
|
||
|
||
now := domain.NowISO()
|
||
|
||
// Ensure blocks table is seeded if previously only in blocks_json
|
||
var existingCount int
|
||
_ = s.db.QueryRow(`SELECT COUNT(*) FROM blocks WHERE document_id = ?`, docID).Scan(&existingCount)
|
||
if existingCount == 0 && len(doc.Blocks) > 0 {
|
||
s.replaceDocBlocksInDB(docID, doc.UserID, doc.Blocks, doc.UpdatedAt)
|
||
}
|
||
|
||
// Read current ordered block IDs from DB before patch
|
||
var existingOrder []string
|
||
ordRows, err := s.db.Query(`SELECT id FROM blocks WHERE document_id = ? ORDER BY position ASC, rowid ASC`, docID)
|
||
if err == nil {
|
||
for ordRows.Next() {
|
||
var bid string
|
||
if err := ordRows.Scan(&bid); err == nil {
|
||
existingOrder = append(existingOrder, bid)
|
||
}
|
||
}
|
||
_ = ordRows.Close()
|
||
}
|
||
|
||
// 1. Delete explicitly deleted block IDs
|
||
deletedSet := make(map[string]bool, len(req.DeletedIDs))
|
||
for _, delID := range req.DeletedIDs {
|
||
delID = strings.TrimSpace(delID)
|
||
if delID == "" {
|
||
continue
|
||
}
|
||
deletedSet[delID] = true
|
||
_, _ = s.db.Exec(`DELETE FROM blocks WHERE document_id = ? AND id = ?`, docID, delID)
|
||
}
|
||
|
||
// Filter existingOrder to exclude deleted blocks
|
||
survivingExisting := make([]string, 0, len(existingOrder))
|
||
survivingSet := make(map[string]bool, len(existingOrder))
|
||
for _, bid := range existingOrder {
|
||
if !deletedSet[bid] {
|
||
survivingExisting = append(survivingExisting, bid)
|
||
survivingSet[bid] = true
|
||
}
|
||
}
|
||
|
||
// 2. Upsert modified or newly created blocks
|
||
upsertedIDs := make([]string, 0, len(req.Upserted))
|
||
for idx, b := range req.Upserted {
|
||
bID := strings.TrimSpace(b.ID)
|
||
if bID == "" || deletedSet[bID] {
|
||
continue
|
||
}
|
||
bType := b.Type
|
||
if bType == "" {
|
||
bType = "bullet"
|
||
}
|
||
chk := 0
|
||
if b.Checked {
|
||
chk = 1
|
||
}
|
||
col := 0
|
||
if b.Collapsed {
|
||
col = 1
|
||
}
|
||
defaultSort := float64((len(survivingExisting) + idx + 1) * 1000)
|
||
_, _ = s.db.Exec(`
|
||
INSERT INTO blocks (id, document_id, content, level, position, type, checked, collapsed, lang)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
ON CONFLICT(id) DO UPDATE SET
|
||
type = excluded.type,
|
||
content = excluded.content,
|
||
level = excluded.level,
|
||
checked = excluded.checked,
|
||
collapsed = excluded.collapsed,
|
||
lang = excluded.lang
|
||
`, bID, docID, b.Content, b.Level, defaultSort, bType, chk, col, b.Lang)
|
||
upsertedIDs = append(upsertedIDs, bID)
|
||
survivingSet[bID] = true
|
||
}
|
||
|
||
// 3. Reconcile block ordering without dropping blocks concurrently created by other users
|
||
var mergedOrder []string
|
||
inMerged := make(map[string]bool)
|
||
|
||
if len(req.BlockOrder) > 0 {
|
||
for _, bid := range req.BlockOrder {
|
||
if !deletedSet[bid] && survivingSet[bid] && !inMerged[bid] {
|
||
mergedOrder = append(mergedOrder, bid)
|
||
inMerged[bid] = true
|
||
}
|
||
}
|
||
for i, bid := range survivingExisting {
|
||
if inMerged[bid] {
|
||
continue
|
||
}
|
||
insertPos := 0
|
||
for j := i - 1; j >= 0; j-- {
|
||
predID := survivingExisting[j]
|
||
if inMerged[predID] {
|
||
for k, mID := range mergedOrder {
|
||
if mID == predID {
|
||
insertPos = k + 1
|
||
break
|
||
}
|
||
}
|
||
break
|
||
}
|
||
}
|
||
mergedOrder = append(mergedOrder[:insertPos], append([]string{bid}, mergedOrder[insertPos:]...)...)
|
||
inMerged[bid] = true
|
||
}
|
||
for _, bid := range upsertedIDs {
|
||
if !inMerged[bid] {
|
||
mergedOrder = append(mergedOrder, bid)
|
||
inMerged[bid] = true
|
||
}
|
||
}
|
||
} else {
|
||
for _, bid := range survivingExisting {
|
||
if !inMerged[bid] {
|
||
mergedOrder = append(mergedOrder, bid)
|
||
inMerged[bid] = true
|
||
}
|
||
}
|
||
for _, bid := range upsertedIDs {
|
||
if !inMerged[bid] {
|
||
mergedOrder = append(mergedOrder, bid)
|
||
inMerged[bid] = true
|
||
}
|
||
}
|
||
}
|
||
|
||
// Update position in blocks
|
||
for idx, bid := range mergedOrder {
|
||
_, _ = s.db.Exec(`UPDATE blocks SET position = ? WHERE document_id = ? AND id = ?`, float64((idx+1)*1000), docID, bid)
|
||
}
|
||
|
||
// Reload authoritative block list and update document snapshot
|
||
finalBlocks := s.loadDocBlocks(docID, "")
|
||
var plainBuilder strings.Builder
|
||
for _, b := range finalBlocks {
|
||
if b.Content != "" {
|
||
plainBuilder.WriteString(b.Content)
|
||
plainBuilder.WriteString("\n")
|
||
}
|
||
}
|
||
plainContent := plainBuilder.String()
|
||
mdCache := domain.CompileBlocksToMarkdown(finalBlocks)
|
||
blocksJSON, _ := json.Marshal(finalBlocks)
|
||
|
||
_, _ = s.db.Exec(`UPDATE documents SET markdown_cache = ?, content = ?, blocks_json = ?, updated_at = ? WHERE id = ?`,
|
||
mdCache, plainContent, string(blocksJSON), now, docID)
|
||
|
||
doc.Blocks = finalBlocks
|
||
doc.MarkdownCache = mdCache
|
||
doc.Content = plainContent
|
||
doc.UpdatedAt = now
|
||
_ = s.indexDocBlockTags(doc.ID, doc.UserID, finalBlocks)
|
||
|
||
return doc, mergedOrder, nil
|
||
}
|
||
|
||
// GetAuthorizedDocUsers returns all user IDs who have read/write/owner access to docID.
|
||
func (s *Store) GetAuthorizedDocUsers(docID string) ([]string, error) {
|
||
userSet := make(map[string]bool)
|
||
|
||
var ownerID, sharedFolderID string
|
||
err := s.db.QueryRow(`SELECT user_id, shared_folder_id FROM documents WHERE id = ?`, docID).Scan(&ownerID, &sharedFolderID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if ownerID != "" {
|
||
userSet[ownerID] = true
|
||
}
|
||
|
||
// Direct user shares
|
||
uRows, err := s.db.Query(`SELECT user_id FROM doc_shares WHERE doc_id = ?`, docID)
|
||
if err == nil {
|
||
for uRows.Next() {
|
||
var uid string
|
||
if err := uRows.Scan(&uid); err == nil && uid != "" {
|
||
userSet[uid] = true
|
||
}
|
||
}
|
||
_ = uRows.Close()
|
||
}
|
||
|
||
// Team shares
|
||
tRows, err := s.db.Query(`
|
||
SELECT tm.user_id
|
||
FROM doc_team_shares dts
|
||
JOIN team_members tm ON tm.team_id = dts.team_id
|
||
WHERE dts.doc_id = ?
|
||
`, docID)
|
||
if err == nil {
|
||
for tRows.Next() {
|
||
var uid string
|
||
if err := tRows.Scan(&uid); err == nil && uid != "" {
|
||
userSet[uid] = true
|
||
}
|
||
}
|
||
_ = tRows.Close()
|
||
}
|
||
|
||
// Shared folder access
|
||
if sharedFolderID != "" {
|
||
sfUsers, err := s.GetAuthorizedSharedFolderUsers(sharedFolderID)
|
||
if err == nil {
|
||
for _, uid := range sfUsers {
|
||
userSet[uid] = true
|
||
}
|
||
}
|
||
}
|
||
|
||
users := make([]string, 0, len(userSet))
|
||
for uid := range userSet {
|
||
users = append(users, uid)
|
||
}
|
||
return users, nil
|
||
}
|
||
|
||
// GetAuthorizedSharedFolderUsers returns all user IDs who have access to a shared folder.
|
||
func (s *Store) GetAuthorizedSharedFolderUsers(folderID string) ([]string, error) {
|
||
userSet := make(map[string]bool)
|
||
|
||
var ownerID string
|
||
if err := s.db.QueryRow(`SELECT owner_id FROM workspace_shared_folders WHERE id = ?`, folderID).Scan(&ownerID); err == nil && ownerID != "" {
|
||
userSet[ownerID] = true
|
||
}
|
||
|
||
rows, err := s.db.Query(`
|
||
SELECT target_type, target_id FROM workspace_folder_shares WHERE folder_id = ?
|
||
`, folderID)
|
||
if err == nil {
|
||
type shareTarget struct{ tType, tID string }
|
||
var targets []shareTarget
|
||
for rows.Next() {
|
||
var st shareTarget
|
||
if err := rows.Scan(&st.tType, &st.tID); err == nil {
|
||
targets = append(targets, st)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
|
||
for _, st := range targets {
|
||
if st.tType == "user" && st.tID != "" {
|
||
userSet[st.tID] = true
|
||
} else if st.tType == "team" && st.tID != "" {
|
||
mRows, err := s.db.Query(`SELECT user_id FROM team_members WHERE team_id = ?`, st.tID)
|
||
if err == nil {
|
||
for mRows.Next() {
|
||
var uid string
|
||
if err := mRows.Scan(&uid); err == nil && uid != "" {
|
||
userSet[uid] = true
|
||
}
|
||
}
|
||
_ = mRows.Close()
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
users := make([]string, 0, len(userSet))
|
||
for uid := range userSet {
|
||
users = append(users, uid)
|
||
}
|
||
return users, nil
|
||
}
|
||
|
||
func (s *Store) CreateDocument(userID, spaceID, title, icon, folderPath string, blocks []domain.DocumentBlock, tags []string, isPinned bool) (*domain.Document, error) {
|
||
if strings.TrimSpace(title) == "" {
|
||
title = "Unbenannte Notiz"
|
||
}
|
||
if icon == "" {
|
||
icon = "📄"
|
||
}
|
||
if blocks == nil {
|
||
blocks = []domain.DocumentBlock{
|
||
{ID: domain.NewID("blk"), Type: "paragraph", Content: ""},
|
||
}
|
||
}
|
||
tags = stripFolderTags(tags)
|
||
|
||
var plainBuilder strings.Builder
|
||
for _, b := range blocks {
|
||
if b.Content != "" {
|
||
plainBuilder.WriteString(b.Content)
|
||
plainBuilder.WriteString("\n")
|
||
}
|
||
}
|
||
|
||
blocksJSON, _ := json.Marshal(blocks)
|
||
tagsJSON, _ := json.Marshal(tags)
|
||
now := domain.NowISO()
|
||
pinnedInt := 0
|
||
if isPinned {
|
||
pinnedInt = 1
|
||
}
|
||
|
||
cleanFolder := filepath.ToSlash(filepath.Clean(strings.TrimSpace(folderPath)))
|
||
if cleanFolder == "." {
|
||
cleanFolder = ""
|
||
}
|
||
|
||
mounts, _, _ := s.getUserFolderMounts(userID)
|
||
var sharedFolderID, subFolderPath string
|
||
if fID, sub, ok := matchMountPath(cleanFolder, mounts); ok {
|
||
sharedFolderID = fID
|
||
subFolderPath = sub
|
||
}
|
||
|
||
doc := &domain.Document{
|
||
ID: domain.NewID("doc"),
|
||
UserID: userID,
|
||
SpaceID: spaceID,
|
||
Title: strings.TrimSpace(title),
|
||
Icon: icon,
|
||
Content: plainBuilder.String(),
|
||
Blocks: blocks,
|
||
Tags: tags,
|
||
FolderPath: cleanFolder,
|
||
IsPinned: isPinned,
|
||
SharedFolderID: sharedFolderID,
|
||
SubFolderPath: subFolderPath,
|
||
IsSharedFolder: sharedFolderID != "",
|
||
Permission: "owner",
|
||
IsOwner: true,
|
||
CreatedAt: now,
|
||
UpdatedAt: now,
|
||
}
|
||
|
||
_, err := s.db.Exec(
|
||
`INSERT INTO documents (id, user_id, space_id, title, icon, content, blocks_json, tags, folder_path, is_pinned, is_archived, created_at, updated_at, shared_folder_id, sub_folder_path)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?, ?, ?)`,
|
||
doc.ID, doc.UserID, doc.SpaceID, doc.Title, doc.Icon, doc.Content, string(blocksJSON), string(tagsJSON), doc.FolderPath, pinnedInt, doc.CreatedAt, doc.UpdatedAt, doc.SharedFolderID, doc.SubFolderPath,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if isPinned {
|
||
_, _ = s.db.Exec(`INSERT OR IGNORE INTO doc_pins (user_id, doc_id, created_at) VALUES (?, ?, ?)`, userID, doc.ID, doc.CreatedAt)
|
||
}
|
||
s.replaceDocBlocksInDB(doc.ID, userID, doc.Blocks, now)
|
||
_ = s.indexDocBlockTags(doc.ID, doc.UserID, doc.Blocks)
|
||
return doc, nil
|
||
}
|
||
|
||
// getUserFolderMounts returns map[folder_id]mount_path and map[folder_id]folder_name for all shared folders accessible to userID
|
||
func (s *Store) getUserFolderMounts(userID string) (map[string]string, map[string]string, error) {
|
||
mounts := make(map[string]string)
|
||
names := make(map[string]string)
|
||
|
||
rows, err := s.db.Query(`
|
||
SELECT f.id, f.name, COALESCE(m.mount_path, f.name) as mount_path
|
||
FROM workspace_shared_folders f
|
||
LEFT JOIN workspace_folder_mounts m ON m.folder_id = f.id AND m.user_id = ?
|
||
LEFT JOIN workspace_folder_shares s ON s.folder_id = f.id AND (
|
||
(s.target_type = 'user' AND s.target_id = ?) OR
|
||
(s.target_type = 'team' AND s.target_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
)
|
||
WHERE f.owner_id = ? OR s.id IS NOT NULL
|
||
GROUP BY f.id
|
||
`, userID, userID, userID, userID)
|
||
if err != nil {
|
||
return mounts, names, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
for rows.Next() {
|
||
var fID, fName, mPath string
|
||
if err := rows.Scan(&fID, &fName, &mPath); err == nil {
|
||
names[fID] = fName
|
||
if mPath == "" {
|
||
mPath = fName
|
||
}
|
||
mounts[fID] = mPath
|
||
}
|
||
}
|
||
return mounts, names, nil
|
||
}
|
||
|
||
// matchMountPath checks if folderPath falls within any mounted shared folder.
|
||
// Returns (sharedFolderID, relativeSubPath, isMatch).
|
||
func matchMountPath(folderPath string, mounts map[string]string) (string, string, bool) {
|
||
clean := filepath.ToSlash(filepath.Clean(strings.TrimSpace(folderPath)))
|
||
if clean == "." {
|
||
clean = ""
|
||
}
|
||
if clean == "" {
|
||
return "", "", false
|
||
}
|
||
|
||
var bestFolderID string
|
||
var bestSubPath string
|
||
var longestMountLen int = -1
|
||
|
||
for fID, mPath := range mounts {
|
||
cleanMount := filepath.ToSlash(filepath.Clean(strings.TrimSpace(mPath)))
|
||
if cleanMount == "." {
|
||
cleanMount = ""
|
||
}
|
||
if cleanMount == "" {
|
||
continue
|
||
}
|
||
|
||
if clean == cleanMount {
|
||
if len(cleanMount) > longestMountLen {
|
||
longestMountLen = len(cleanMount)
|
||
bestFolderID = fID
|
||
bestSubPath = ""
|
||
}
|
||
} else if strings.HasPrefix(clean, cleanMount+"/") {
|
||
sub := strings.TrimPrefix(clean, cleanMount+"/")
|
||
if len(cleanMount) > longestMountLen {
|
||
longestMountLen = len(cleanMount)
|
||
bestFolderID = fID
|
||
bestSubPath = sub
|
||
}
|
||
}
|
||
}
|
||
|
||
if longestMountLen >= 0 {
|
||
return bestFolderID, bestSubPath, true
|
||
}
|
||
return "", "", false
|
||
}
|
||
|
||
func resolveDocumentPath(userID string, doc *domain.Document, mounts map[string]string, names map[string]string, userFolders map[string]string) {
|
||
if doc.SharedFolderID != "" {
|
||
mountPath, ok := mounts[doc.SharedFolderID]
|
||
if !ok || mountPath == "" {
|
||
mountPath = names[doc.SharedFolderID]
|
||
if mountPath == "" {
|
||
mountPath = "Geteilter Ordner"
|
||
}
|
||
}
|
||
doc.IsSharedFolder = true
|
||
if doc.SubFolderPath != "" {
|
||
doc.FolderPath = mountPath + "/" + doc.SubFolderPath
|
||
} else {
|
||
doc.FolderPath = mountPath
|
||
}
|
||
} else {
|
||
if uf, ok := userFolders[doc.ID]; ok {
|
||
doc.FolderPath = uf
|
||
} else if !doc.IsOwner {
|
||
doc.FolderPath = ""
|
||
}
|
||
}
|
||
}
|
||
|
||
func scanDocument(rows interface {
|
||
Scan(dest ...any) error
|
||
}) (*domain.Document, error) {
|
||
d := &domain.Document{}
|
||
var blocksStr, tagsStr string
|
||
var isPinned, isArchived int
|
||
err := rows.Scan(
|
||
&d.ID, &d.UserID, &d.SpaceID, &d.Title, &d.Icon, &d.Content,
|
||
&blocksStr, &tagsStr, &d.FolderPath, &isPinned, &isArchived, &d.CreatedAt, &d.UpdatedAt,
|
||
&d.SharedFolderID, &d.SubFolderPath,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
d.IsPinned = isPinned != 0
|
||
d.IsArchived = isArchived != 0
|
||
d.Blocks = []domain.DocumentBlock{}
|
||
d.Tags = []string{}
|
||
_ = json.Unmarshal([]byte(blocksStr), &d.Blocks)
|
||
_ = json.Unmarshal([]byte(tagsStr), &d.Tags)
|
||
d.Tags = stripFolderTags(d.Tags)
|
||
return d, nil
|
||
}
|
||
|
||
func (s *Store) scanDocumentWithBlocks(rows interface {
|
||
Scan(dest ...any) error
|
||
}) (*domain.Document, error) {
|
||
d := &domain.Document{}
|
||
var blocksStr, tagsStr string
|
||
var isPinned, isArchived int
|
||
err := rows.Scan(
|
||
&d.ID, &d.UserID, &d.SpaceID, &d.Title, &d.Icon, &d.Content,
|
||
&blocksStr, &tagsStr, &d.FolderPath, &isPinned, &isArchived, &d.CreatedAt, &d.UpdatedAt,
|
||
&d.SharedFolderID, &d.SubFolderPath,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
d.IsPinned = isPinned != 0
|
||
d.IsArchived = isArchived != 0
|
||
d.Tags = []string{}
|
||
_ = json.Unmarshal([]byte(tagsStr), &d.Tags)
|
||
d.Tags = stripFolderTags(d.Tags)
|
||
d.Blocks = s.loadDocBlocks(d.ID, blocksStr)
|
||
d.MarkdownCache = domain.CompileBlocksToMarkdown(d.Blocks)
|
||
d.FolderID = d.FolderPath
|
||
return d, nil
|
||
}
|
||
|
||
func (s *Store) GetDocuments(userID string) ([]domain.Document, error) {
|
||
mounts, names, _ := s.getUserFolderMounts(userID)
|
||
|
||
userFolders := make(map[string]string)
|
||
ufRows, err := s.db.Query(`SELECT doc_id, folder_path FROM doc_user_folders WHERE user_id = ?`, userID)
|
||
if err == nil {
|
||
for ufRows.Next() {
|
||
var did, fp string
|
||
if err := ufRows.Scan(&did, &fp); err == nil {
|
||
userFolders[did] = fp
|
||
}
|
||
}
|
||
_ = ufRows.Close()
|
||
}
|
||
|
||
// 1. Fetch user's own documents (with per-user is_pinned from doc_pins)
|
||
rows, err := s.db.Query(
|
||
`SELECT d.id, d.user_id, d.space_id, d.title, d.icon, d.content, d.blocks_json, d.tags, d.folder_path,
|
||
CASE WHEN p.doc_id IS NOT NULL THEN 1 ELSE 0 END AS is_pinned,
|
||
d.is_archived, d.created_at, d.updated_at,
|
||
d.shared_folder_id, d.sub_folder_path
|
||
FROM documents d
|
||
LEFT JOIN doc_pins p ON p.doc_id = d.id AND p.user_id = ?
|
||
WHERE d.user_id = ? AND d.is_archived = 0
|
||
ORDER BY is_pinned DESC, d.updated_at DESC`,
|
||
userID, userID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
type rawDocRow struct {
|
||
doc domain.Document
|
||
blocksStr string
|
||
}
|
||
var ownRaw []rawDocRow
|
||
for rows.Next() {
|
||
d := domain.Document{}
|
||
var blocksStr, tagsStr string
|
||
var isPinned, isArchived int
|
||
if err := rows.Scan(
|
||
&d.ID, &d.UserID, &d.SpaceID, &d.Title, &d.Icon, &d.Content,
|
||
&blocksStr, &tagsStr, &d.FolderPath, &isPinned, &isArchived, &d.CreatedAt, &d.UpdatedAt,
|
||
&d.SharedFolderID, &d.SubFolderPath,
|
||
); err == nil {
|
||
d.IsPinned = isPinned != 0
|
||
d.IsArchived = isArchived != 0
|
||
d.Tags = []string{}
|
||
_ = json.Unmarshal([]byte(tagsStr), &d.Tags)
|
||
d.Tags = stripFolderTags(d.Tags)
|
||
d.Permission = "owner"
|
||
d.IsOwner = true
|
||
resolveDocumentPath(userID, &d, mounts, names, userFolders)
|
||
ownRaw = append(ownRaw, rawDocRow{doc: d, blocksStr: blocksStr})
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
|
||
docs := make([]domain.Document, 0, len(ownRaw))
|
||
for _, r := range ownRaw {
|
||
r.doc.Blocks = s.loadDocBlocks(r.doc.ID, r.blocksStr)
|
||
docs = append(docs, r.doc)
|
||
}
|
||
|
||
// 2. Fetch documents shared with the user directly, via teams, or via shared folders
|
||
sharedRows, err := s.db.Query(
|
||
`SELECT d.id, d.user_id, d.space_id, d.title, d.icon, d.content, d.blocks_json, d.tags, d.folder_path,
|
||
CASE WHEN p.doc_id IS NOT NULL THEN 1 ELSE 0 END AS is_pinned,
|
||
d.is_archived, d.created_at, d.updated_at,
|
||
d.shared_folder_id, d.sub_folder_path,
|
||
CASE
|
||
WHEN wsf.owner_id = ? THEN 'owner'
|
||
WHEN COALESCE(s.permission, '') = 'write' OR COALESCE(dts.permission, '') = 'write' OR COALESCE(wfs.permission, '') = 'write' THEN 'write'
|
||
ELSE 'read'
|
||
END AS permission,
|
||
u.display_name
|
||
FROM documents d
|
||
JOIN users u ON d.user_id = u.id
|
||
LEFT JOIN doc_shares s ON d.id = s.doc_id AND s.user_id = ?
|
||
LEFT JOIN (
|
||
SELECT dts2.doc_id, dts2.permission
|
||
FROM doc_team_shares dts2
|
||
JOIN team_members tm ON tm.team_id = dts2.team_id
|
||
WHERE tm.user_id = ?
|
||
) dts ON d.id = dts.doc_id
|
||
LEFT JOIN workspace_shared_folders wsf ON d.shared_folder_id = wsf.id
|
||
LEFT JOIN (
|
||
SELECT wfs2.folder_id, wfs2.permission
|
||
FROM workspace_folder_shares wfs2
|
||
WHERE (wfs2.target_type = 'user' AND wfs2.target_id = ?)
|
||
OR (wfs2.target_type = 'team' AND wfs2.target_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
) wfs ON d.shared_folder_id = wfs.folder_id
|
||
LEFT JOIN doc_pins p ON p.doc_id = d.id AND p.user_id = ?
|
||
WHERE d.is_archived = 0 AND d.user_id != ? AND (
|
||
s.user_id IS NOT NULL OR dts.doc_id IS NOT NULL OR wsf.owner_id = ? OR wfs.folder_id IS NOT NULL
|
||
)
|
||
GROUP BY d.id
|
||
ORDER BY is_pinned DESC, d.updated_at DESC`,
|
||
userID, userID, userID, userID, userID, userID, userID, userID,
|
||
)
|
||
if err == nil {
|
||
var sharedRaw []rawDocRow
|
||
for sharedRows.Next() {
|
||
d := domain.Document{}
|
||
var blocksStr, tagsStr string
|
||
var isPinned, isArchived int
|
||
var perm, ownerName string
|
||
if scanErr := sharedRows.Scan(
|
||
&d.ID, &d.UserID, &d.SpaceID, &d.Title, &d.Icon, &d.Content,
|
||
&blocksStr, &tagsStr, &d.FolderPath, &isPinned, &isArchived, &d.CreatedAt, &d.UpdatedAt,
|
||
&d.SharedFolderID, &d.SubFolderPath,
|
||
&perm, &ownerName,
|
||
); scanErr == nil {
|
||
d.IsPinned = isPinned != 0
|
||
d.IsArchived = isArchived != 0
|
||
d.Tags = []string{}
|
||
_ = json.Unmarshal([]byte(tagsStr), &d.Tags)
|
||
d.Tags = stripFolderTags(d.Tags)
|
||
d.Permission = perm
|
||
d.IsOwner = (perm == "owner")
|
||
d.OwnerName = ownerName
|
||
resolveDocumentPath(userID, &d, mounts, names, userFolders)
|
||
sharedRaw = append(sharedRaw, rawDocRow{doc: d, blocksStr: blocksStr})
|
||
}
|
||
}
|
||
_ = sharedRows.Close()
|
||
|
||
for _, r := range sharedRaw {
|
||
r.doc.Blocks = s.loadDocBlocks(r.doc.ID, r.blocksStr)
|
||
docs = append(docs, r.doc)
|
||
}
|
||
}
|
||
|
||
// 3. Attach shares to owned documents
|
||
for i := range docs {
|
||
if docs[i].IsOwner {
|
||
if shares, err := s.GetDocShares(docs[i].ID); err == nil {
|
||
docs[i].SharedWith = shares
|
||
}
|
||
}
|
||
}
|
||
|
||
return docs, nil
|
||
}
|
||
|
||
func (s *Store) GetDocumentByID(userID, docID string) (*domain.Document, error) {
|
||
mounts, names, _ := s.getUserFolderMounts(userID)
|
||
userFolders := make(map[string]string)
|
||
var personalFp string
|
||
if err := s.db.QueryRow(`SELECT folder_path FROM doc_user_folders WHERE user_id = ? AND doc_id = ?`, userID, docID).Scan(&personalFp); err == nil {
|
||
userFolders[docID] = personalFp
|
||
}
|
||
|
||
// Try owned document first (with per-user is_pinned)
|
||
row := s.db.QueryRow(
|
||
`SELECT d.id, d.user_id, d.space_id, d.title, d.icon, d.content, d.blocks_json, d.tags, d.folder_path,
|
||
CASE WHEN p.doc_id IS NOT NULL THEN 1 ELSE 0 END AS is_pinned,
|
||
d.is_archived, d.created_at, d.updated_at,
|
||
d.shared_folder_id, d.sub_folder_path
|
||
FROM documents d
|
||
LEFT JOIN doc_pins p ON p.doc_id = d.id AND p.user_id = ?
|
||
WHERE d.id = ? AND d.user_id = ?`,
|
||
userID, docID, userID,
|
||
)
|
||
doc, err := s.scanDocumentWithBlocks(row)
|
||
if err == nil {
|
||
doc.Permission = "owner"
|
||
doc.IsOwner = true
|
||
resolveDocumentPath(userID, doc, mounts, names, userFolders)
|
||
if shares, err := s.GetDocShares(doc.ID); err == nil {
|
||
doc.SharedWith = shares
|
||
}
|
||
return doc, nil
|
||
}
|
||
|
||
// Try shared document (direct, via team, or via shared folder)
|
||
sharedRow := s.db.QueryRow(
|
||
`SELECT d.id, d.user_id, d.space_id, d.title, d.icon, d.content, d.blocks_json, d.tags, d.folder_path,
|
||
CASE WHEN p.doc_id IS NOT NULL THEN 1 ELSE 0 END AS is_pinned,
|
||
d.is_archived, d.created_at, d.updated_at,
|
||
d.shared_folder_id, d.sub_folder_path,
|
||
CASE
|
||
WHEN wsf.owner_id = ? THEN 'owner'
|
||
WHEN COALESCE(s.permission, '') = 'write' OR COALESCE(dts.permission, '') = 'write' OR COALESCE(wfs.permission, '') = 'write' THEN 'write'
|
||
ELSE 'read'
|
||
END AS permission,
|
||
u.display_name
|
||
FROM documents d
|
||
JOIN users u ON d.user_id = u.id
|
||
LEFT JOIN doc_shares s ON d.id = s.doc_id AND s.user_id = ?
|
||
LEFT JOIN (
|
||
SELECT dts2.doc_id, dts2.permission
|
||
FROM doc_team_shares dts2
|
||
JOIN team_members tm ON tm.team_id = dts2.team_id
|
||
WHERE tm.user_id = ?
|
||
) dts ON d.id = dts.doc_id
|
||
LEFT JOIN workspace_shared_folders wsf ON d.shared_folder_id = wsf.id
|
||
LEFT JOIN (
|
||
SELECT wfs2.folder_id, wfs2.permission
|
||
FROM workspace_folder_shares wfs2
|
||
WHERE (wfs2.target_type = 'user' AND wfs2.target_id = ?)
|
||
OR (wfs2.target_type = 'team' AND wfs2.target_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
) wfs ON d.shared_folder_id = wfs.folder_id
|
||
LEFT JOIN doc_pins p ON p.doc_id = d.id AND p.user_id = ?
|
||
WHERE d.id = ? AND (s.user_id = ? OR dts.doc_id IS NOT NULL OR wsf.owner_id = ? OR wfs.folder_id IS NOT NULL)
|
||
LIMIT 1`,
|
||
userID, userID, userID, userID, userID, userID, docID, userID, userID,
|
||
)
|
||
d := &domain.Document{}
|
||
var blocksStr, tagsStr string
|
||
var isPinned, isArchived int
|
||
var perm, ownerName string
|
||
err = sharedRow.Scan(
|
||
&d.ID, &d.UserID, &d.SpaceID, &d.Title, &d.Icon, &d.Content,
|
||
&blocksStr, &tagsStr, &d.FolderPath, &isPinned, &isArchived, &d.CreatedAt, &d.UpdatedAt,
|
||
&d.SharedFolderID, &d.SubFolderPath,
|
||
&perm, &ownerName,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
d.IsPinned = isPinned != 0
|
||
d.IsArchived = isArchived != 0
|
||
d.Tags = []string{}
|
||
_ = json.Unmarshal([]byte(tagsStr), &d.Tags)
|
||
d.Tags = stripFolderTags(d.Tags)
|
||
d.Blocks = s.loadDocBlocks(d.ID, blocksStr)
|
||
d.Permission = perm
|
||
d.IsOwner = (perm == "owner")
|
||
d.OwnerName = ownerName
|
||
resolveDocumentPath(userID, d, mounts, names, userFolders)
|
||
if shares, err := s.GetDocShares(d.ID); err == nil {
|
||
d.SharedWith = shares
|
||
}
|
||
return d, nil
|
||
}
|
||
|
||
func (s *Store) UpdateDocument(userID, docID string, title, icon, folderPath *string, blocks *[]domain.DocumentBlock, tags *[]string, isPinned *bool) (*domain.Document, error) {
|
||
doc, err := s.GetDocumentByID(userID, docID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
isOnlyPinChange := isPinned != nil && title == nil && icon == nil && folderPath == nil && blocks == nil && tags == nil
|
||
isOnlyFolderMove := folderPath != nil && title == nil && icon == nil && blocks == nil && tags == nil && isPinned == nil
|
||
|
||
if !doc.IsOwner && !isOnlyPinChange && !isOnlyFolderMove {
|
||
return nil, fmt.Errorf("keine Schreibberechtigung für dieses Dokument (nur Lesezugriff)")
|
||
}
|
||
|
||
if isPinned != nil {
|
||
doc.IsPinned = *isPinned
|
||
if *isPinned {
|
||
_, _ = s.db.Exec(`INSERT OR IGNORE INTO doc_pins (user_id, doc_id, created_at) VALUES (?, ?, ?)`, userID, docID, domain.NowISO())
|
||
if doc.IsOwner {
|
||
_, _ = s.db.Exec(`UPDATE documents SET is_pinned = 1 WHERE id = ?`, docID)
|
||
}
|
||
} else {
|
||
_, _ = s.db.Exec(`DELETE FROM doc_pins WHERE user_id = ? AND doc_id = ?`, userID, docID)
|
||
if doc.IsOwner {
|
||
_, _ = s.db.Exec(`UPDATE documents SET is_pinned = 0 WHERE id = ?`, docID)
|
||
}
|
||
}
|
||
}
|
||
|
||
if isOnlyPinChange {
|
||
return doc, nil
|
||
}
|
||
|
||
if folderPath != nil {
|
||
movedDoc, moveErr := s.MoveDocument(userID, docID, *folderPath)
|
||
if moveErr != nil {
|
||
return nil, moveErr
|
||
}
|
||
doc.FolderPath = movedDoc.FolderPath
|
||
doc.SharedFolderID = movedDoc.SharedFolderID
|
||
doc.SubFolderPath = movedDoc.SubFolderPath
|
||
doc.IsSharedFolder = movedDoc.IsSharedFolder
|
||
}
|
||
|
||
if isOnlyFolderMove {
|
||
return doc, nil
|
||
}
|
||
|
||
if title != nil {
|
||
doc.Title = strings.TrimSpace(*title)
|
||
}
|
||
if icon != nil {
|
||
doc.Icon = *icon
|
||
}
|
||
if tags != nil {
|
||
doc.Tags = stripFolderTags(*tags)
|
||
}
|
||
|
||
doc.UpdatedAt = domain.NowISO()
|
||
if blocks != nil {
|
||
doc.Blocks = *blocks
|
||
s.replaceDocBlocksInDB(docID, userID, doc.Blocks, doc.UpdatedAt)
|
||
var plainBuilder strings.Builder
|
||
for _, b := range doc.Blocks {
|
||
if b.Content != "" {
|
||
plainBuilder.WriteString(b.Content)
|
||
plainBuilder.WriteString("\n")
|
||
}
|
||
}
|
||
doc.Content = plainBuilder.String()
|
||
}
|
||
|
||
blocksJSON, _ := json.Marshal(doc.Blocks)
|
||
tagsJSON, _ := json.Marshal(doc.Tags)
|
||
|
||
// IMPORTANT: Never overwrite documents.folder_path here! Folder placement is managed exclusively by MoveDocument.
|
||
_, err = s.db.Exec(
|
||
`UPDATE documents SET title = ?, icon = ?, content = ?, blocks_json = ?, tags = ?, updated_at = ?
|
||
WHERE id = ?`,
|
||
doc.Title, doc.Icon, doc.Content, string(blocksJSON), string(tagsJSON), doc.UpdatedAt, docID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if blocks != nil {
|
||
_ = s.indexDocBlockTags(doc.ID, doc.UserID, doc.Blocks)
|
||
for _, b := range doc.Blocks {
|
||
if b.TaskID != "" {
|
||
s.SyncTaskFromBlockCheck(doc.UserID, b.TaskID, b.Checked)
|
||
}
|
||
}
|
||
|
||
// When a block is deleted from the note, unlink the corresponding task in thoughts without deleting the task
|
||
remainingBlockIDs := make(map[string]bool)
|
||
for _, b := range doc.Blocks {
|
||
remainingBlockIDs[b.ID] = true
|
||
}
|
||
rows, err := s.db.Query(`SELECT id, converted_ref_id, block_id FROM thoughts WHERE (doc_id = ? OR converted_ref_id LIKE ?) AND converted_type = 'document_block'`, docID, docID+":%")
|
||
if err == nil {
|
||
for rows.Next() {
|
||
var tid, refID, bID string
|
||
if err := rows.Scan(&tid, &refID, &bID); err == nil {
|
||
bid := bID
|
||
if bid == "" && strings.Contains(refID, ":") {
|
||
parts := strings.SplitN(refID, ":", 2)
|
||
bid = parts[1]
|
||
}
|
||
if !remainingBlockIDs[bid] {
|
||
_, _ = s.db.Exec(`UPDATE thoughts SET converted_type = '', converted_ref_id = '', doc_id = '', block_id = '' WHERE id = ?`, tid)
|
||
}
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
}
|
||
}
|
||
return doc, nil
|
||
}
|
||
|
||
func (s *Store) DeleteDocument(userID, docID string) error {
|
||
doc, err := s.GetDocumentByID(userID, docID)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if !doc.IsOwner {
|
||
// Collaborator removing their shared access
|
||
_, _ = s.db.Exec(`DELETE FROM doc_user_folders WHERE user_id = ? AND doc_id = ?`, userID, docID)
|
||
return s.DeleteDocShare(userID, docID, userID)
|
||
}
|
||
_, _ = s.db.Exec(`DELETE FROM blocks WHERE document_id = ?`, docID)
|
||
_, _ = s.db.Exec(`DELETE FROM block_tags WHERE document_id = ?`, docID)
|
||
_, _ = s.db.Exec(`DELETE FROM doc_user_folders WHERE doc_id = ?`, docID)
|
||
_, err = s.db.Exec(`DELETE FROM documents WHERE id = ?`, docID)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) reindexAllDocBlockTags() error {
|
||
rows, err := s.db.Query(`SELECT id, user_id, blocks_json FROM documents WHERE is_archived = 0`)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
defer rows.Close()
|
||
|
||
type docInfo struct {
|
||
id, userID, blocksJSON string
|
||
}
|
||
var docs []docInfo
|
||
for rows.Next() {
|
||
var d docInfo
|
||
if err := rows.Scan(&d.id, &d.userID, &d.blocksJSON); err == nil {
|
||
docs = append(docs, d)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
|
||
for _, d := range docs {
|
||
blocks := s.loadDocBlocks(d.id, d.blocksJSON)
|
||
if len(blocks) > 0 {
|
||
_ = s.indexDocBlockTags(d.id, d.userID, blocks)
|
||
}
|
||
}
|
||
return nil
|
||
}
|
||
|
||
func (s *Store) indexDocBlockTags(docID, _ string, blocks []domain.DocumentBlock) error {
|
||
_, _ = s.db.Exec(`DELETE FROM block_tags WHERE document_id = ?`, docID)
|
||
if len(blocks) == 0 {
|
||
return nil
|
||
}
|
||
|
||
stmt, err := s.db.Prepare(`INSERT OR IGNORE INTO block_tags (tag, document_id, block_id) VALUES (?, ?, ?)`)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
defer stmt.Close()
|
||
|
||
for _, b := range blocks {
|
||
if b.Content == "" {
|
||
continue
|
||
}
|
||
tags := domain.ExtractTags(b.Content)
|
||
for _, tag := range tags {
|
||
_, _ = stmt.Exec(tag, docID, b.ID)
|
||
}
|
||
}
|
||
return nil
|
||
}
|
||
|
||
func (s *Store) UpdateTagPageNote(userID, tag, content string) (string, error) {
|
||
cleanTag := strings.ToLower(strings.TrimPrefix(strings.TrimSpace(tag), "#"))
|
||
if cleanTag == "" {
|
||
return "", errors.New("tag name required")
|
||
}
|
||
now := domain.NowISO()
|
||
_, err := s.db.Exec(
|
||
`INSERT INTO tag_pages (user_id, tag, content, updated_at)
|
||
VALUES (?, ?, ?, ?)
|
||
ON CONFLICT(user_id, tag) DO UPDATE SET content = excluded.content, updated_at = excluded.updated_at`,
|
||
userID, cleanTag, content, now,
|
||
)
|
||
if err != nil {
|
||
return "", err
|
||
}
|
||
return now, nil
|
||
}
|
||
|
||
func (s *Store) GetWorkspaceTags(userID string) ([]domain.TagSummary, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT t.tag, COUNT(DISTINCT t.document_id || ':' || t.block_id) as count
|
||
FROM block_tags t
|
||
JOIN documents d ON t.document_id = d.id
|
||
LEFT JOIN doc_shares s ON s.doc_id = d.id AND s.user_id = ?
|
||
LEFT JOIN (
|
||
SELECT dts2.doc_id
|
||
FROM doc_team_shares dts2
|
||
JOIN team_members tm ON tm.team_id = dts2.team_id
|
||
WHERE tm.user_id = ?
|
||
) dts ON d.id = dts.doc_id
|
||
LEFT JOIN workspace_shared_folders wsf ON d.shared_folder_id = wsf.id
|
||
LEFT JOIN (
|
||
SELECT wfs2.folder_id
|
||
FROM workspace_folder_shares wfs2
|
||
WHERE (wfs2.target_type = 'user' AND wfs2.target_id = ?)
|
||
OR (wfs2.target_type = 'team' AND wfs2.target_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
) wfs ON d.shared_folder_id = wfs.folder_id
|
||
WHERE (d.user_id = ? OR s.user_id = ? OR dts.doc_id IS NOT NULL OR (wsf.id IS NOT NULL AND (wsf.owner_id = ? OR wfs.folder_id IS NOT NULL)))
|
||
AND d.is_archived = 0
|
||
GROUP BY t.tag
|
||
ORDER BY count DESC, t.tag ASC`,
|
||
userID, userID, userID, userID, userID, userID, userID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
results := make([]domain.TagSummary, 0)
|
||
seen := make(map[string]bool)
|
||
for rows.Next() {
|
||
var ts domain.TagSummary
|
||
if err := rows.Scan(&ts.Tag, &ts.Count); err == nil {
|
||
results = append(results, ts)
|
||
seen[ts.Tag] = true
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
|
||
// Also include tags that have a non-empty tag_pages note even if not currently used in a block
|
||
pageRows, err := s.db.Query(
|
||
`SELECT tag FROM tag_pages WHERE user_id = ? AND TRIM(content) != '' ORDER BY tag ASC`,
|
||
userID,
|
||
)
|
||
if err == nil {
|
||
for pageRows.Next() {
|
||
var t string
|
||
if err := pageRows.Scan(&t); err == nil && t != "" && !seen[t] {
|
||
results = append(results, domain.TagSummary{Tag: t, Count: 0})
|
||
seen[t] = true
|
||
}
|
||
}
|
||
_ = pageRows.Close()
|
||
}
|
||
|
||
return results, nil
|
||
}
|
||
|
||
func (s *Store) GetTagDetails(userID, tag string) (*domain.TagDetails, error) {
|
||
cleanTag := strings.ToLower(strings.TrimPrefix(strings.TrimSpace(tag), "#"))
|
||
if cleanTag == "" {
|
||
return &domain.TagDetails{Tag: "", Documents: []domain.TagDocumentGroup{}, TotalMatches: 0}, nil
|
||
}
|
||
|
||
var noteContent, noteUpdatedAt string
|
||
_ = s.db.QueryRow(
|
||
`SELECT content, updated_at FROM tag_pages WHERE user_id = ? AND tag = ?`,
|
||
userID, cleanTag,
|
||
).Scan(¬eContent, ¬eUpdatedAt)
|
||
|
||
rows, err := s.db.Query(
|
||
`SELECT t.block_id, d.id, d.title, d.icon, d.folder_path, d.blocks_json, d.updated_at, d.user_id,
|
||
COALESCE(u.display_name, '') as owner_name,
|
||
COALESCE(s.permission, dts.permission, wfs.permission, '') as shared_permission
|
||
FROM block_tags t
|
||
JOIN documents d ON t.document_id = d.id
|
||
LEFT JOIN users u ON d.user_id = u.id
|
||
LEFT JOIN doc_shares s ON s.doc_id = d.id AND s.user_id = ?
|
||
LEFT JOIN (
|
||
SELECT dts2.doc_id, dts2.permission
|
||
FROM doc_team_shares dts2
|
||
JOIN team_members tm ON tm.team_id = dts2.team_id
|
||
WHERE tm.user_id = ?
|
||
) dts ON d.id = dts.doc_id
|
||
LEFT JOIN workspace_shared_folders wsf ON d.shared_folder_id = wsf.id
|
||
LEFT JOIN (
|
||
SELECT wfs2.folder_id, wfs2.permission
|
||
FROM workspace_folder_shares wfs2
|
||
WHERE (wfs2.target_type = 'user' AND wfs2.target_id = ?)
|
||
OR (wfs2.target_type = 'team' AND wfs2.target_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
) wfs ON d.shared_folder_id = wfs.folder_id
|
||
WHERE (d.user_id = ? OR s.user_id = ? OR dts.doc_id IS NOT NULL OR (wsf.id IS NOT NULL AND (wsf.owner_id = ? OR wfs.folder_id IS NOT NULL)))
|
||
AND t.tag = ?
|
||
AND d.is_archived = 0
|
||
ORDER BY d.updated_at DESC`,
|
||
userID, userID, userID, userID, userID, userID, userID, cleanTag,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
type rawMatch struct {
|
||
blockID string
|
||
docID string
|
||
title string
|
||
icon string
|
||
folderPath string
|
||
blocksJSON string
|
||
updatedAt string
|
||
docUserID string
|
||
ownerName string
|
||
sharedPermission string
|
||
}
|
||
|
||
var rawMatches []rawMatch
|
||
for rows.Next() {
|
||
var rm rawMatch
|
||
if err := rows.Scan(&rm.blockID, &rm.docID, &rm.title, &rm.icon, &rm.folderPath, &rm.blocksJSON, &rm.updatedAt, &rm.docUserID, &rm.ownerName, &rm.sharedPermission); err == nil {
|
||
rawMatches = append(rawMatches, rm)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
|
||
// Group rawMatches by docID
|
||
docMap := make(map[string][]rawMatch)
|
||
var docOrder []string
|
||
for _, rm := range rawMatches {
|
||
if _, exists := docMap[rm.docID]; !exists {
|
||
docOrder = append(docOrder, rm.docID)
|
||
}
|
||
docMap[rm.docID] = append(docMap[rm.docID], rm)
|
||
}
|
||
|
||
totalMatches := 0
|
||
docGroups := make([]domain.TagDocumentGroup, 0, len(docOrder))
|
||
|
||
for _, docID := range docOrder {
|
||
matches := docMap[docID]
|
||
first := matches[0]
|
||
|
||
blocks := s.loadDocBlocks(first.docID, first.blocksJSON)
|
||
|
||
blockIdxMap := make(map[string]int, len(blocks))
|
||
for i, b := range blocks {
|
||
blockIdxMap[b.ID] = i
|
||
}
|
||
|
||
matchedBlocks := make([]domain.MatchedTagBlock, 0)
|
||
processedBlockIDs := make(map[string]bool)
|
||
|
||
for _, m := range matches {
|
||
if processedBlockIDs[m.blockID] {
|
||
continue
|
||
}
|
||
idx, ok := blockIdxMap[m.blockID]
|
||
if !ok {
|
||
continue
|
||
}
|
||
rootBlock := blocks[idx]
|
||
processedBlockIDs[rootBlock.ID] = true
|
||
|
||
children := make([]domain.DocumentBlock, 0)
|
||
rootLevel := rootBlock.Level
|
||
for i := idx + 1; i < len(blocks); i++ {
|
||
if blocks[i].Level > rootLevel {
|
||
children = append(children, blocks[i])
|
||
processedBlockIDs[blocks[i].ID] = true
|
||
} else {
|
||
break
|
||
}
|
||
}
|
||
|
||
matchedBlocks = append(matchedBlocks, domain.MatchedTagBlock{
|
||
RootBlock: rootBlock,
|
||
Children: children,
|
||
})
|
||
totalMatches++
|
||
}
|
||
|
||
if len(matchedBlocks) == 0 {
|
||
continue
|
||
}
|
||
|
||
isOwner := first.docUserID == userID
|
||
permission := "read"
|
||
if isOwner {
|
||
permission = "owner"
|
||
} else if first.sharedPermission != "" {
|
||
permission = first.sharedPermission
|
||
}
|
||
|
||
docGroups = append(docGroups, domain.TagDocumentGroup{
|
||
DocID: first.docID,
|
||
Title: first.title,
|
||
Icon: first.icon,
|
||
FolderPath: first.folderPath,
|
||
UpdatedAt: first.updatedAt,
|
||
IsOwner: isOwner,
|
||
Permission: permission,
|
||
OwnerName: first.ownerName,
|
||
MatchedBlocks: matchedBlocks,
|
||
})
|
||
}
|
||
|
||
return &domain.TagDetails{
|
||
Tag: cleanTag,
|
||
NoteContent: noteContent,
|
||
NoteUpdatedAt: noteUpdatedAt,
|
||
Documents: docGroups,
|
||
TotalMatches: totalMatches,
|
||
}, nil
|
||
}
|
||
|
||
func (s *Store) GetFolders(userID string) ([]string, error) {
|
||
folderSet := make(map[string]bool)
|
||
|
||
// 1. Collect from resolved documents for this user
|
||
docs, err := s.GetDocuments(userID)
|
||
if err == nil {
|
||
for _, doc := range docs {
|
||
if doc.FolderPath != "" {
|
||
addFolderSegments(doc.FolderPath, folderSet)
|
||
}
|
||
}
|
||
}
|
||
|
||
// 2. Add mounted shared folders (so even empty shared folders appear in the tree)
|
||
mounts, _, err := s.getUserFolderMounts(userID)
|
||
if err == nil {
|
||
for sfID, mPath := range mounts {
|
||
if mPath != "" {
|
||
addFolderSegments(mPath, folderSet)
|
||
|
||
// 2b. Add all subfolders created in this shared folder
|
||
sRows, err := s.db.Query(`SELECT sub_path FROM workspace_folders WHERE shared_folder_id = ?`, sfID)
|
||
if err == nil {
|
||
for sRows.Next() {
|
||
var sp string
|
||
if err := sRows.Scan(&sp); err == nil && sp != "" {
|
||
fullP := mPath + "/" + sp
|
||
addFolderSegments(fullP, folderSet)
|
||
}
|
||
}
|
||
_ = sRows.Close()
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
// 3. Add personal empty folders created by this user
|
||
pRows, err := s.db.Query(`SELECT path FROM workspace_folders WHERE user_id = ? AND (shared_folder_id = '' OR shared_folder_id IS NULL)`, userID)
|
||
if err == nil {
|
||
for pRows.Next() {
|
||
var p string
|
||
if err := pRows.Scan(&p); err == nil && p != "" {
|
||
addFolderSegments(p, folderSet)
|
||
}
|
||
}
|
||
_ = pRows.Close()
|
||
}
|
||
|
||
result := make([]string, 0, len(folderSet))
|
||
for f := range folderSet {
|
||
result = append(result, f)
|
||
}
|
||
sort.Strings(result)
|
||
return result, nil
|
||
}
|
||
|
||
func addFolderSegments(fp string, set map[string]bool) {
|
||
clean := filepath.ToSlash(filepath.Clean(strings.TrimSpace(fp)))
|
||
if clean == "." || clean == "" {
|
||
return
|
||
}
|
||
parts := strings.Split(clean, "/")
|
||
var cur string
|
||
for _, p := range parts {
|
||
if p != "" && p != "." {
|
||
if cur == "" {
|
||
cur = p
|
||
} else {
|
||
cur = cur + "/" + p
|
||
}
|
||
set[cur] = true
|
||
}
|
||
}
|
||
}
|
||
|
||
func (s *Store) CreateFolder(userID, folderPath string) (string, error) {
|
||
clean := filepath.ToSlash(filepath.Clean(strings.TrimSpace(folderPath)))
|
||
if clean == "" || clean == "." || strings.HasPrefix(clean, "/") || strings.Contains(clean, "..") {
|
||
return "", fmt.Errorf("ungültiger Ordnerpfad")
|
||
}
|
||
|
||
mounts, _, err := s.getUserFolderMounts(userID)
|
||
var sfID, subPath string
|
||
if err == nil {
|
||
sfID, subPath, _ = matchMountPath(clean, mounts)
|
||
}
|
||
|
||
now := time.Now().UTC().Format(time.RFC3339)
|
||
id := domain.NewID("wfld")
|
||
|
||
if sfID != "" {
|
||
var existsID string
|
||
err := s.db.QueryRow(`SELECT id FROM workspace_folders WHERE shared_folder_id = ? AND sub_path = ?`, sfID, subPath).Scan(&existsID)
|
||
if err == sql.ErrNoRows {
|
||
_, _ = s.db.Exec(`INSERT INTO workspace_folders (id, user_id, shared_folder_id, path, sub_path, created_at) VALUES (?, ?, ?, ?, ?, ?)`,
|
||
id, userID, sfID, clean, subPath, now)
|
||
}
|
||
} else {
|
||
var existsID string
|
||
err := s.db.QueryRow(`SELECT id FROM workspace_folders WHERE user_id = ? AND path = ? AND (shared_folder_id = '' OR shared_folder_id IS NULL)`, userID, clean).Scan(&existsID)
|
||
if err == sql.ErrNoRows {
|
||
_, _ = s.db.Exec(`INSERT INTO workspace_folders (id, user_id, shared_folder_id, path, sub_path, created_at) VALUES (?, ?, '', ?, '', ?)`,
|
||
id, userID, clean, now)
|
||
}
|
||
}
|
||
|
||
return clean, nil
|
||
}
|
||
|
||
func (s *Store) DeleteFolder(userID, folderPath string) ([]string, error) {
|
||
clean := filepath.ToSlash(filepath.Clean(strings.TrimSpace(folderPath)))
|
||
if clean == "" || clean == "." || strings.HasPrefix(clean, "/") || strings.Contains(clean, "..") {
|
||
return nil, fmt.Errorf("ungültiger Ordnerpfad")
|
||
}
|
||
|
||
var deletedDocIDs []string
|
||
seenDocID := make(map[string]bool)
|
||
|
||
mounts, _, err := s.getUserFolderMounts(userID)
|
||
if err == nil {
|
||
sfID, subPath, isMatch := matchMountPath(clean, mounts)
|
||
if isMatch && sfID != "" {
|
||
if subPath == "" {
|
||
// Root of a shared folder
|
||
shFolder, sfErr := s.GetSharedFolderByID(userID, sfID)
|
||
if sfErr == nil && !shFolder.IsOwner {
|
||
// Collaborator removing mount/share
|
||
_ = s.DeleteSharedFolder(userID, sfID)
|
||
return []string{}, nil
|
||
}
|
||
rows, qErr := s.db.Query(`SELECT id FROM documents WHERE shared_folder_id = ?`, sfID)
|
||
if qErr == nil {
|
||
for rows.Next() {
|
||
var id string
|
||
if err := rows.Scan(&id); err == nil && !seenDocID[id] {
|
||
seenDocID[id] = true
|
||
deletedDocIDs = append(deletedDocIDs, id)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
}
|
||
_, _ = s.db.Exec(`DELETE FROM workspace_folders WHERE shared_folder_id = ?`, sfID)
|
||
} else {
|
||
// Subfolder inside a shared folder
|
||
rows, qErr := s.db.Query(
|
||
`SELECT id FROM documents WHERE shared_folder_id = ? AND (sub_folder_path = ? OR sub_folder_path LIKE ?)`,
|
||
sfID, subPath, subPath+"/%",
|
||
)
|
||
if qErr == nil {
|
||
for rows.Next() {
|
||
var id string
|
||
if err := rows.Scan(&id); err == nil && !seenDocID[id] {
|
||
seenDocID[id] = true
|
||
deletedDocIDs = append(deletedDocIDs, id)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
}
|
||
_, _ = s.db.Exec(`DELETE FROM workspace_folders WHERE shared_folder_id = ? AND (sub_path = ? OR sub_path LIKE ?)`,
|
||
sfID, subPath, subPath+"/%")
|
||
}
|
||
}
|
||
}
|
||
|
||
// Find all personal documents owned by this user in this folder and subfolders
|
||
rows, err := s.db.Query(
|
||
`SELECT id FROM documents
|
||
WHERE user_id = ? AND (shared_folder_id = '' OR shared_folder_id IS NULL) AND (folder_path = ? OR folder_path LIKE ?)`,
|
||
userID, clean, clean+"/%",
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
for rows.Next() {
|
||
var id string
|
||
if err := rows.Scan(&id); err == nil && !seenDocID[id] {
|
||
seenDocID[id] = true
|
||
deletedDocIDs = append(deletedDocIDs, id)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
|
||
// Delete documents from SQLite, blocks, pins, shares, tags, user_folders
|
||
for _, id := range deletedDocIDs {
|
||
_, _ = s.db.Exec(`DELETE FROM blocks WHERE document_id = ?`, id)
|
||
_, _ = s.db.Exec(`DELETE FROM block_tags WHERE document_id = ?`, id)
|
||
_, _ = s.db.Exec(`DELETE FROM doc_pins WHERE doc_id = ?`, id)
|
||
_, _ = s.db.Exec(`DELETE FROM doc_shares WHERE doc_id = ?`, id)
|
||
_, _ = s.db.Exec(`DELETE FROM doc_team_shares WHERE doc_id = ?`, id)
|
||
_, _ = s.db.Exec(`DELETE FROM doc_user_folders WHERE doc_id = ?`, id)
|
||
_, _ = s.db.Exec(`DELETE FROM documents WHERE id = ?`, id)
|
||
}
|
||
|
||
// Also clear any personal folder placements for shared notes in this folder
|
||
_, _ = s.db.Exec(`UPDATE doc_user_folders SET folder_path = '' WHERE user_id = ? AND (folder_path = ? OR folder_path LIKE ?)`, userID, clean, clean+"/%")
|
||
_, _ = s.db.Exec(`DELETE FROM workspace_folders WHERE user_id = ? AND (path = ? OR path LIKE ?)`, userID, clean, clean+"/%")
|
||
|
||
return deletedDocIDs, nil
|
||
}
|
||
|
||
func (s *Store) MoveDocument(userID, docID, targetFolderPath string) (*domain.Document, error) {
|
||
doc, err := s.GetDocumentByID(userID, docID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
cleanTarget := filepath.ToSlash(filepath.Clean(strings.TrimSpace(targetFolderPath)))
|
||
if cleanTarget == "." {
|
||
cleanTarget = ""
|
||
}
|
||
|
||
mounts, _, err := s.getUserFolderMounts(userID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
now := domain.NowISO()
|
||
|
||
// Check if target folder falls within any mounted shared folder
|
||
targetFolderID, subPath, isTargetShared := matchMountPath(cleanTarget, mounts)
|
||
|
||
if isTargetShared {
|
||
// Moving into / within a shared folder
|
||
shFolder, err := s.GetSharedFolderByID(userID, targetFolderID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if !shFolder.IsOwner {
|
||
return nil, fmt.Errorf("nur der Eigentümer dieses geteilten Ordners kann Dateien dorthin verschieben")
|
||
}
|
||
|
||
// Update document to belong to targetSharedFolderID
|
||
_, err = s.db.Exec(`UPDATE documents SET shared_folder_id = ?, sub_folder_path = ?, updated_at = ? WHERE id = ?`,
|
||
targetFolderID, subPath, now, docID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
// Remove personal override if any
|
||
_, _ = s.db.Exec(`DELETE FROM doc_user_folders WHERE user_id = ? AND doc_id = ?`, userID, docID)
|
||
|
||
doc.SharedFolderID = targetFolderID
|
||
doc.SubFolderPath = subPath
|
||
doc.IsSharedFolder = true
|
||
doc.FolderPath = cleanTarget
|
||
return doc, nil
|
||
}
|
||
|
||
// Target is NOT inside a shared folder (personal / standalone)
|
||
if doc.SharedFolderID != "" {
|
||
// Moving OUT of a shared folder into a personal folder
|
||
if !doc.IsOwner {
|
||
return nil, fmt.Errorf("keine Berechtigung, die Notiz aus dem geteilten Ordner zu verschieben")
|
||
}
|
||
_, err = s.db.Exec(`UPDATE documents SET shared_folder_id = '', sub_folder_path = '', folder_path = ?, updated_at = ? WHERE id = ?`,
|
||
cleanTarget, now, docID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
doc.SharedFolderID = ""
|
||
doc.SubFolderPath = ""
|
||
doc.IsSharedFolder = false
|
||
doc.FolderPath = cleanTarget
|
||
return doc, nil
|
||
}
|
||
|
||
// Standalone note
|
||
if doc.IsOwner {
|
||
// Owner moves their own document -> updates documents.folder_path
|
||
_, err = s.db.Exec(`UPDATE documents SET folder_path = ?, updated_at = ? WHERE id = ?`, cleanTarget, now, docID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
doc.FolderPath = cleanTarget
|
||
return doc, nil
|
||
}
|
||
|
||
// Recipient moves an individually shared note -> updates doc_user_folders ONLY!
|
||
_, err = s.db.Exec(`
|
||
INSERT INTO doc_user_folders (user_id, doc_id, folder_path, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
ON CONFLICT(user_id, doc_id) DO UPDATE SET folder_path = excluded.folder_path, updated_at = excluded.updated_at
|
||
`, userID, docID, cleanTarget, now, now)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
doc.FolderPath = cleanTarget
|
||
return doc, nil
|
||
}
|
||
|
||
// MoveFolder moves a folder (and all its documents/subfolders) to a new target parent path.
|
||
func (s *Store) MoveFolder(userID, sourcePath, targetParentPath string) (string, []string, error) {
|
||
cleanSource := filepath.ToSlash(filepath.Clean(strings.TrimSpace(sourcePath)))
|
||
if cleanSource == "" || cleanSource == "." || strings.HasPrefix(cleanSource, "/") || strings.Contains(cleanSource, "..") {
|
||
return "", nil, fmt.Errorf("ungültiger Quell-Ordnerpfad")
|
||
}
|
||
cleanTarget := filepath.ToSlash(filepath.Clean(strings.TrimSpace(targetParentPath)))
|
||
if cleanTarget == "." {
|
||
cleanTarget = ""
|
||
}
|
||
if cleanTarget != "" && (cleanTarget == cleanSource || strings.HasPrefix(cleanTarget, cleanSource+"/")) {
|
||
return "", nil, fmt.Errorf("ein Ordner kann nicht in sich selbst verschoben werden")
|
||
}
|
||
|
||
folderName := filepath.Base(cleanSource)
|
||
var newFolderPath string
|
||
if cleanTarget == "" {
|
||
newFolderPath = folderName
|
||
} else {
|
||
newFolderPath = cleanTarget + "/" + folderName
|
||
}
|
||
|
||
if newFolderPath == cleanSource {
|
||
return cleanSource, []string{}, nil
|
||
}
|
||
|
||
// 1. Check if sourcePath is the root mount of a shared folder
|
||
mounts, _, err := s.getUserFolderMounts(userID)
|
||
if err == nil {
|
||
for sfID, mPath := range mounts {
|
||
if mPath == cleanSource {
|
||
err := s.UpdateFolderMount(userID, sfID, newFolderPath)
|
||
if err != nil {
|
||
return "", nil, err
|
||
}
|
||
return newFolderPath, []string{}, nil
|
||
}
|
||
}
|
||
}
|
||
|
||
// 2. Move all affected documents
|
||
docs, err := s.GetDocuments(userID)
|
||
if err != nil {
|
||
return "", nil, err
|
||
}
|
||
|
||
var movedDocIDs []string
|
||
for _, doc := range docs {
|
||
if doc.FolderPath == cleanSource || strings.HasPrefix(doc.FolderPath, cleanSource+"/") {
|
||
var nextDocFolder string
|
||
if doc.FolderPath == cleanSource {
|
||
nextDocFolder = newFolderPath
|
||
} else {
|
||
suffix := strings.TrimPrefix(doc.FolderPath, cleanSource)
|
||
nextDocFolder = newFolderPath + suffix
|
||
}
|
||
_, moveErr := s.MoveDocument(userID, doc.ID, nextDocFolder)
|
||
if moveErr == nil {
|
||
movedDocIDs = append(movedDocIDs, doc.ID)
|
||
}
|
||
}
|
||
}
|
||
|
||
// 3. Re-map workspace_folders entries
|
||
targetSFID, targetSub, isTargetSF := matchMountPath(newFolderPath, mounts)
|
||
sourceSFID, sourceSub, isSourceSF := matchMountPath(cleanSource, mounts)
|
||
|
||
wfRows, qErr := s.db.Query(`SELECT id, user_id, shared_folder_id, path, sub_path FROM workspace_folders`)
|
||
if qErr == nil {
|
||
type wfRecord struct {
|
||
id, uid, sfID, path, subPath string
|
||
}
|
||
var records []wfRecord
|
||
for wfRows.Next() {
|
||
var r wfRecord
|
||
if err := wfRows.Scan(&r.id, &r.uid, &r.sfID, &r.path, &r.subPath); err == nil {
|
||
records = append(records, r)
|
||
}
|
||
}
|
||
_ = wfRows.Close()
|
||
|
||
for _, r := range records {
|
||
if isSourceSF && r.sfID == sourceSFID {
|
||
matches := false
|
||
var suffix string
|
||
if sourceSub == "" {
|
||
matches = true
|
||
suffix = "/" + r.subPath
|
||
} else if r.subPath == sourceSub {
|
||
matches = true
|
||
suffix = ""
|
||
} else if strings.HasPrefix(r.subPath, sourceSub+"/") {
|
||
matches = true
|
||
suffix = strings.TrimPrefix(r.subPath, sourceSub)
|
||
}
|
||
if matches {
|
||
var newSub string
|
||
if targetSub == "" {
|
||
newSub = strings.TrimPrefix(folderName+suffix, "/")
|
||
} else {
|
||
newSub = targetSub + "/" + folderName + suffix
|
||
}
|
||
if isTargetSF {
|
||
_, _ = s.db.Exec(`UPDATE workspace_folders SET shared_folder_id = ?, sub_path = ? WHERE id = ?`, targetSFID, newSub, r.id)
|
||
} else {
|
||
newP := newFolderPath + suffix
|
||
_, _ = s.db.Exec(`UPDATE workspace_folders SET shared_folder_id = '', sub_path = '', path = ? WHERE id = ?`, newP, r.id)
|
||
}
|
||
}
|
||
} else if r.path == cleanSource || strings.HasPrefix(r.path, cleanSource+"/") {
|
||
suffix := strings.TrimPrefix(r.path, cleanSource)
|
||
newP := newFolderPath + suffix
|
||
if isTargetSF {
|
||
var newSub string
|
||
if targetSub == "" {
|
||
newSub = strings.TrimPrefix(folderName+suffix, "/")
|
||
} else {
|
||
newSub = targetSub + "/" + folderName + suffix
|
||
}
|
||
_, _ = s.db.Exec(`UPDATE workspace_folders SET shared_folder_id = ?, sub_path = ?, path = ? WHERE id = ?`, targetSFID, newSub, newP, r.id)
|
||
} else {
|
||
_, _ = s.db.Exec(`UPDATE workspace_folders SET path = ? WHERE id = ?`, newP, r.id)
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
_, _ = s.CreateFolder(userID, newFolderPath)
|
||
|
||
return newFolderPath, movedDocIDs, nil
|
||
}
|
||
|
||
// syncSharedFolderContents ensures all documents and subfolders under cleanMount owned by ownerID are linked to folderID.
|
||
func (s *Store) syncSharedFolderContents(ownerID, folderID, cleanMount string) {
|
||
if ownerID == "" || folderID == "" || cleanMount == "" {
|
||
return
|
||
}
|
||
|
||
// 1. Link all documents owned by ownerID in cleanMount to this shared folder
|
||
rows, err := s.db.Query(`SELECT id, folder_path FROM documents WHERE user_id = ? AND is_archived = 0 AND (folder_path = ? OR folder_path LIKE ?)`,
|
||
ownerID, cleanMount, cleanMount+"/%")
|
||
if err == nil {
|
||
type docEntry struct{ id, fp string }
|
||
var entries []docEntry
|
||
for rows.Next() {
|
||
var de docEntry
|
||
if err := rows.Scan(&de.id, &de.fp); err == nil {
|
||
entries = append(entries, de)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
|
||
for _, de := range entries {
|
||
var subPath string
|
||
if de.fp == cleanMount {
|
||
subPath = ""
|
||
} else if strings.HasPrefix(de.fp, cleanMount+"/") {
|
||
subPath = strings.TrimPrefix(de.fp, cleanMount+"/")
|
||
}
|
||
_, _ = s.db.Exec(`UPDATE documents SET shared_folder_id = ?, sub_folder_path = ? WHERE id = ?`,
|
||
folderID, subPath, de.id)
|
||
}
|
||
}
|
||
|
||
// 2. Link any workspace_folders created by ownerID in cleanMount
|
||
fRows, err := s.db.Query(`SELECT id, path FROM workspace_folders WHERE user_id = ? AND (path = ? OR path LIKE ?)`,
|
||
ownerID, cleanMount, cleanMount+"/%")
|
||
if err == nil {
|
||
type wfEntry struct{ id, path string }
|
||
var wfEntries []wfEntry
|
||
for fRows.Next() {
|
||
var wfe wfEntry
|
||
if err := fRows.Scan(&wfe.id, &wfe.path); err == nil {
|
||
wfEntries = append(wfEntries, wfe)
|
||
}
|
||
}
|
||
_ = fRows.Close()
|
||
|
||
for _, wfe := range wfEntries {
|
||
subP := ""
|
||
if strings.HasPrefix(wfe.path, cleanMount+"/") {
|
||
subP = strings.TrimPrefix(wfe.path, cleanMount+"/")
|
||
}
|
||
_, _ = s.db.Exec(`UPDATE workspace_folders SET shared_folder_id = ?, sub_path = ? WHERE id = ?`,
|
||
folderID, subP, wfe.id)
|
||
}
|
||
}
|
||
}
|
||
|
||
func (s *Store) CreateSharedFolder(userID, folderName, initialMountPath string) (*domain.SharedFolder, error) {
|
||
cleanName := strings.TrimSpace(folderName)
|
||
if cleanName == "" {
|
||
return nil, fmt.Errorf("ordnername darf nicht leer sein")
|
||
}
|
||
cleanMount := filepath.ToSlash(filepath.Clean(strings.TrimSpace(initialMountPath)))
|
||
if cleanMount == "." || cleanMount == "" {
|
||
cleanMount = cleanName
|
||
}
|
||
|
||
// Check if already shared by this user at this mount path
|
||
var existingID string
|
||
err := s.db.QueryRow(`
|
||
SELECT f.id FROM workspace_shared_folders f
|
||
JOIN workspace_folder_mounts m ON m.folder_id = f.id AND m.user_id = ?
|
||
WHERE f.owner_id = ? AND (m.mount_path = ? OR f.name = ?)
|
||
`, userID, userID, cleanMount, cleanName).Scan(&existingID)
|
||
if err == nil && existingID != "" {
|
||
s.syncSharedFolderContents(userID, existingID, cleanMount)
|
||
return s.GetSharedFolderByID(userID, existingID)
|
||
}
|
||
|
||
now := domain.NowISO()
|
||
folderID := domain.NewID("fld")
|
||
|
||
_, err = s.db.Exec(`
|
||
INSERT INTO workspace_shared_folders (id, owner_id, name, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
`, folderID, userID, cleanName, now, now)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
_, err = s.db.Exec(`
|
||
INSERT INTO workspace_folder_mounts (user_id, folder_id, mount_path, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
`, userID, folderID, cleanMount, now, now)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
s.syncSharedFolderContents(userID, folderID, cleanMount)
|
||
|
||
var ownerName string
|
||
_ = s.db.QueryRow(`SELECT display_name FROM users WHERE id = ?`, userID).Scan(&ownerName)
|
||
|
||
return &domain.SharedFolder{
|
||
ID: folderID,
|
||
OwnerID: userID,
|
||
OwnerName: ownerName,
|
||
Name: cleanName,
|
||
MountPath: cleanMount,
|
||
IsOwner: true,
|
||
Permission: "owner",
|
||
Shares: []domain.FolderShare{},
|
||
CreatedAt: now,
|
||
UpdatedAt: now,
|
||
}, nil
|
||
}
|
||
|
||
func (s *Store) GetSharedFolders(userID string) ([]domain.SharedFolder, error) {
|
||
rows, err := s.db.Query(`
|
||
SELECT f.id, f.owner_id, u.display_name, f.name,
|
||
COALESCE(m.mount_path, f.name) as mount_path,
|
||
CASE
|
||
WHEN f.owner_id = ? THEN 1
|
||
ELSE 0
|
||
END as is_owner,
|
||
CASE
|
||
WHEN f.owner_id = ? THEN 'owner'
|
||
WHEN MAX(CASE WHEN s.permission = 'write' THEN 1 ELSE 0 END) = 1 THEN 'write'
|
||
ELSE 'read'
|
||
END as permission,
|
||
f.created_at, f.updated_at
|
||
FROM workspace_shared_folders f
|
||
JOIN users u ON f.owner_id = u.id
|
||
LEFT JOIN workspace_folder_mounts m ON m.folder_id = f.id AND m.user_id = ?
|
||
LEFT JOIN workspace_folder_shares s ON s.folder_id = f.id AND (
|
||
(s.target_type = 'user' AND s.target_id = ?) OR
|
||
(s.target_type = 'team' AND s.target_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
)
|
||
WHERE f.owner_id = ? OR s.id IS NOT NULL
|
||
GROUP BY f.id
|
||
ORDER BY f.created_at ASC
|
||
`, userID, userID, userID, userID, userID, userID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
folders := make([]domain.SharedFolder, 0)
|
||
for rows.Next() {
|
||
var f domain.SharedFolder
|
||
var isOwner int
|
||
if err := rows.Scan(&f.ID, &f.OwnerID, &f.OwnerName, &f.Name, &f.MountPath, &isOwner, &f.Permission, &f.CreatedAt, &f.UpdatedAt); err == nil {
|
||
f.IsOwner = isOwner != 0
|
||
if f.IsOwner {
|
||
if shares, err := s.GetFolderShares(f.ID); err == nil {
|
||
f.Shares = shares
|
||
}
|
||
}
|
||
folders = append(folders, f)
|
||
}
|
||
}
|
||
return folders, nil
|
||
}
|
||
|
||
func (s *Store) GetSharedFolderByID(userID, folderID string) (*domain.SharedFolder, error) {
|
||
row := s.db.QueryRow(`
|
||
SELECT f.id, f.owner_id, u.display_name, f.name,
|
||
COALESCE(m.mount_path, f.name) as mount_path,
|
||
CASE
|
||
WHEN f.owner_id = ? THEN 1
|
||
ELSE 0
|
||
END as is_owner,
|
||
CASE
|
||
WHEN f.owner_id = ? THEN 'owner'
|
||
WHEN MAX(CASE WHEN s.permission = 'write' THEN 1 ELSE 0 END) = 1 THEN 'write'
|
||
ELSE 'read'
|
||
END as permission,
|
||
f.created_at, f.updated_at
|
||
FROM workspace_shared_folders f
|
||
JOIN users u ON f.owner_id = u.id
|
||
LEFT JOIN workspace_folder_mounts m ON m.folder_id = f.id AND m.user_id = ?
|
||
LEFT JOIN workspace_folder_shares s ON s.folder_id = f.id AND (
|
||
(s.target_type = 'user' AND s.target_id = ?) OR
|
||
(s.target_type = 'team' AND s.target_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
)
|
||
WHERE f.id = ? AND (f.owner_id = ? OR s.id IS NOT NULL)
|
||
GROUP BY f.id
|
||
`, userID, userID, userID, userID, userID, folderID, userID)
|
||
|
||
var f domain.SharedFolder
|
||
var isOwner int
|
||
if err := row.Scan(&f.ID, &f.OwnerID, &f.OwnerName, &f.Name, &f.MountPath, &isOwner, &f.Permission, &f.CreatedAt, &f.UpdatedAt); err != nil {
|
||
return nil, fmt.Errorf("geteilter Ordner nicht gefunden")
|
||
}
|
||
f.IsOwner = isOwner != 0
|
||
if f.IsOwner {
|
||
if shares, err := s.GetFolderShares(f.ID); err == nil {
|
||
f.Shares = shares
|
||
}
|
||
}
|
||
return &f, nil
|
||
}
|
||
|
||
func (s *Store) UpdateFolderMount(userID, folderID, mountPath string) error {
|
||
cleanMount := filepath.ToSlash(filepath.Clean(strings.TrimSpace(mountPath)))
|
||
if cleanMount == "." || cleanMount == "" {
|
||
return fmt.Errorf("ungültiger Mount-Pfad")
|
||
}
|
||
now := domain.NowISO()
|
||
_, err := s.db.Exec(`
|
||
INSERT INTO workspace_folder_mounts (user_id, folder_id, mount_path, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
ON CONFLICT(user_id, folder_id) DO UPDATE SET mount_path = excluded.mount_path, updated_at = excluded.updated_at
|
||
`, userID, folderID, cleanMount, now, now)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) GetFolderShares(folderID string) ([]domain.FolderShare, error) {
|
||
shares := make([]domain.FolderShare, 0)
|
||
rows, err := s.db.Query(`
|
||
SELECT s.id, s.folder_id, s.target_type, s.target_id,
|
||
CASE
|
||
WHEN s.target_type = 'user' THEN u.display_name
|
||
WHEN s.target_type = 'team' THEN t.name
|
||
ELSE ''
|
||
END as target_name,
|
||
COALESCE(u.email, '') as email,
|
||
COALESCE(u.avatar_url, '') as avatar_url,
|
||
s.permission, s.created_by, s.created_at
|
||
FROM workspace_folder_shares s
|
||
LEFT JOIN users u ON s.target_type = 'user' AND s.target_id = u.id
|
||
LEFT JOIN teams t ON s.target_type = 'team' AND s.target_id = t.id
|
||
WHERE s.folder_id = ?
|
||
ORDER BY s.created_at ASC
|
||
`, folderID)
|
||
if err != nil {
|
||
return shares, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
for rows.Next() {
|
||
var sh domain.FolderShare
|
||
if err := rows.Scan(&sh.ID, &sh.FolderID, &sh.TargetType, &sh.TargetID, &sh.TargetName, &sh.Email, &sh.AvatarURL, &sh.Permission, &sh.CreatedBy, &sh.CreatedAt); err == nil {
|
||
shares = append(shares, sh)
|
||
}
|
||
}
|
||
return shares, nil
|
||
}
|
||
|
||
func (s *Store) CreateFolderShare(currentUserID, folderID, targetType, targetIdentifier, permission string) (*domain.FolderShare, error) {
|
||
f, err := s.GetSharedFolderByID(currentUserID, folderID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if !f.IsOwner {
|
||
return nil, fmt.Errorf("nur der Eigentümer kann Ordner-Freigaben verwalten")
|
||
}
|
||
|
||
cleanType := strings.TrimSpace(targetType)
|
||
cleanTarget := strings.TrimSpace(targetIdentifier)
|
||
if cleanType == "" {
|
||
if strings.HasPrefix(cleanTarget, "team_") {
|
||
cleanType = "team"
|
||
} else {
|
||
cleanType = "user"
|
||
}
|
||
}
|
||
|
||
// Geteilte Ordner können ausschließlich mit Leserberechtigung geteilt werden (wie Dokumente)
|
||
permission = "read"
|
||
|
||
var targetID, targetName, email, avatarURL string
|
||
if cleanType == "team" {
|
||
err = s.db.QueryRow(`SELECT id, name FROM teams WHERE id = ?`, cleanTarget).Scan(&targetID, &targetName)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("gruppe nicht gefunden")
|
||
}
|
||
} else {
|
||
var u *domain.User
|
||
if strings.HasPrefix(cleanTarget, "usr_") {
|
||
u, _ = s.GetUserByID(cleanTarget)
|
||
}
|
||
if u == nil {
|
||
u, _ = s.GetUserByLogin(cleanTarget)
|
||
}
|
||
if u == nil {
|
||
u, err = s.GetUserByID(cleanTarget)
|
||
}
|
||
if err != nil || u == nil {
|
||
if s.db.QueryRow(`SELECT id, name FROM teams WHERE id = ?`, cleanTarget).Scan(&targetID, &targetName) == nil {
|
||
cleanType = "team"
|
||
} else {
|
||
return nil, fmt.Errorf("benutzer nicht gefunden")
|
||
}
|
||
} else {
|
||
if u.ID == f.OwnerID {
|
||
return nil, fmt.Errorf("ordner gehört bereits diesem Benutzer")
|
||
}
|
||
targetID = u.ID
|
||
targetName = u.DisplayName
|
||
email = u.Email
|
||
avatarURL = u.AvatarURL
|
||
}
|
||
}
|
||
|
||
now := domain.NowISO()
|
||
shareID := domain.NewID("fsh")
|
||
|
||
_, err = s.db.Exec(`
|
||
INSERT INTO workspace_folder_shares (id, folder_id, target_type, target_id, permission, created_by, created_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)
|
||
ON CONFLICT(folder_id, target_type, target_id) DO UPDATE SET permission = excluded.permission
|
||
`, shareID, folderID, cleanType, targetID, permission, currentUserID, now)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
var ownerMount string
|
||
_ = s.db.QueryRow(`SELECT COALESCE(m.mount_path, f.name) FROM workspace_shared_folders f LEFT JOIN workspace_folder_mounts m ON m.folder_id = f.id AND m.user_id = f.owner_id WHERE f.id = ?`, folderID).Scan(&ownerMount)
|
||
if ownerMount == "" {
|
||
ownerMount = f.Name
|
||
}
|
||
s.syncSharedFolderContents(f.OwnerID, folderID, ownerMount)
|
||
|
||
if cleanType == "user" {
|
||
_, _ = s.db.Exec(`
|
||
INSERT OR IGNORE INTO workspace_folder_mounts (user_id, folder_id, mount_path, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
`, targetID, folderID, f.Name, now, now)
|
||
} else if cleanType == "team" {
|
||
mRows, err := s.db.Query(`SELECT user_id FROM team_members WHERE team_id = ?`, targetID)
|
||
if err == nil {
|
||
var memberIDs []string
|
||
for mRows.Next() {
|
||
var uid string
|
||
if err := mRows.Scan(&uid); err == nil && uid != "" {
|
||
memberIDs = append(memberIDs, uid)
|
||
}
|
||
}
|
||
_ = mRows.Close()
|
||
for _, uid := range memberIDs {
|
||
_, _ = s.db.Exec(`
|
||
INSERT OR IGNORE INTO workspace_folder_mounts (user_id, folder_id, mount_path, created_at, updated_at)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
`, uid, folderID, f.Name, now, now)
|
||
}
|
||
}
|
||
}
|
||
|
||
return &domain.FolderShare{
|
||
ID: shareID,
|
||
FolderID: folderID,
|
||
TargetType: cleanType,
|
||
TargetID: targetID,
|
||
TargetName: targetName,
|
||
Email: email,
|
||
AvatarURL: avatarURL,
|
||
Permission: permission,
|
||
CreatedBy: currentUserID,
|
||
CreatedAt: now,
|
||
}, nil
|
||
}
|
||
|
||
func (s *Store) DeleteFolderShare(currentUserID, folderID, shareID string) error {
|
||
f, err := s.GetSharedFolderByID(currentUserID, folderID)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
cleanID := strings.TrimSpace(shareID)
|
||
|
||
if !f.IsOwner && f.Permission != "write" {
|
||
_, err = s.db.Exec(`DELETE FROM workspace_folder_shares WHERE folder_id = ? AND target_type = 'user' AND target_id = ? AND (id = ? OR target_id = ?)`,
|
||
folderID, currentUserID, cleanID, cleanID)
|
||
_, _ = s.db.Exec(`DELETE FROM workspace_folder_mounts WHERE folder_id = ? AND user_id = ?`, folderID, currentUserID)
|
||
return err
|
||
}
|
||
|
||
var targetType, targetID string
|
||
_ = s.db.QueryRow(`SELECT target_type, target_id FROM workspace_folder_shares WHERE folder_id = ? AND (id = ? OR target_id = ?)`,
|
||
folderID, cleanID, cleanID).Scan(&targetType, &targetID)
|
||
|
||
_, err = s.db.Exec(`DELETE FROM workspace_folder_shares WHERE folder_id = ? AND (id = ? OR target_id = ?)`, folderID, cleanID, cleanID)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if targetType == "user" && targetID != "" {
|
||
_, _ = s.db.Exec(`DELETE FROM workspace_folder_mounts WHERE folder_id = ? AND user_id = ?`, folderID, targetID)
|
||
}
|
||
return nil
|
||
}
|
||
|
||
func (s *Store) DeleteSharedFolder(currentUserID, folderID string) error {
|
||
f, err := s.GetSharedFolderByID(currentUserID, folderID)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if !f.IsOwner {
|
||
return s.DeleteFolderShare(currentUserID, folderID, currentUserID)
|
||
}
|
||
|
||
_, _ = s.db.Exec(`UPDATE documents SET shared_folder_id = '', sub_folder_path = '' WHERE shared_folder_id = ?`, folderID)
|
||
_, _ = s.db.Exec(`DELETE FROM workspace_folder_mounts WHERE folder_id = ?`, folderID)
|
||
_, _ = s.db.Exec(`DELETE FROM workspace_folder_shares WHERE folder_id = ?`, folderID)
|
||
_, _ = s.db.Exec(`DELETE FROM workspace_folders WHERE shared_folder_id = ?`, folderID)
|
||
_, err = s.db.Exec(`DELETE FROM workspace_shared_folders WHERE id = ?`, folderID)
|
||
return err
|
||
}
|
||
|
||
// GetSharedFolderDocIDs returns all non-archived document IDs belonging to a shared folder.
|
||
func (s *Store) GetSharedFolderDocIDs(folderID string) ([]string, error) {
|
||
rows, err := s.db.Query(`SELECT id FROM documents WHERE shared_folder_id = ? AND is_archived = 0`, folderID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
var docIDs []string
|
||
for rows.Next() {
|
||
var id string
|
||
if err := rows.Scan(&id); err == nil && id != "" {
|
||
docIDs = append(docIDs, id)
|
||
}
|
||
}
|
||
return docIDs, nil
|
||
}
|
||
|
||
func (s *Store) GetDocShares(docID string) ([]domain.DocShare, error) {
|
||
shares := make([]domain.DocShare, 0)
|
||
|
||
// 1. Direct user shares
|
||
uRows, err := s.db.Query(
|
||
`SELECT s.id, s.doc_id, s.user_id, u.email, u.display_name, s.permission, s.created_by, s.created_at
|
||
FROM doc_shares s
|
||
JOIN users u ON s.user_id = u.id
|
||
WHERE s.doc_id = ?
|
||
ORDER BY s.created_at ASC`,
|
||
docID,
|
||
)
|
||
if err == nil {
|
||
for uRows.Next() {
|
||
var sh domain.DocShare
|
||
if err := uRows.Scan(&sh.ID, &sh.DocID, &sh.UserID, &sh.UserEmail, &sh.DisplayName, &sh.Permission, &sh.CreatedBy, &sh.CreatedAt); err == nil {
|
||
sh.TargetType = "user"
|
||
sh.TargetID = sh.UserID
|
||
sh.TargetName = sh.DisplayName
|
||
shares = append(shares, sh)
|
||
}
|
||
}
|
||
_ = uRows.Close()
|
||
}
|
||
|
||
// 2. Team shares
|
||
tRows, err := s.db.Query(
|
||
`SELECT s.id, s.doc_id, s.team_id, t.name, s.permission, s.created_by, s.created_at
|
||
FROM doc_team_shares s
|
||
JOIN teams t ON s.team_id = t.id
|
||
WHERE s.doc_id = ?
|
||
ORDER BY s.created_at ASC`,
|
||
docID,
|
||
)
|
||
if err == nil {
|
||
for tRows.Next() {
|
||
var sh domain.DocShare
|
||
var teamID, teamName string
|
||
if err := tRows.Scan(&sh.ID, &sh.DocID, &teamID, &teamName, &sh.Permission, &sh.CreatedBy, &sh.CreatedAt); err == nil {
|
||
sh.TargetType = "team"
|
||
sh.TargetID = teamID
|
||
sh.TargetName = teamName
|
||
sh.DisplayName = teamName
|
||
shares = append(shares, sh)
|
||
}
|
||
}
|
||
_ = tRows.Close()
|
||
}
|
||
|
||
return shares, nil
|
||
}
|
||
|
||
func (s *Store) CreateDocTeamShare(currentUserID, docID, teamID, permission string) (*domain.DocShare, error) {
|
||
doc, err := s.GetDocumentByID(currentUserID, docID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if !doc.IsOwner {
|
||
return nil, fmt.Errorf("nur der Eigentümer kann Freigaben verwalten")
|
||
}
|
||
|
||
cleanTeamID := strings.TrimSpace(teamID)
|
||
var teamName string
|
||
err = s.db.QueryRow(`SELECT name FROM teams WHERE id = ?`, cleanTeamID).Scan(&teamName)
|
||
if err != nil {
|
||
return nil, fmt.Errorf("team nicht gefunden")
|
||
}
|
||
|
||
// Document sharing is strictly read-only
|
||
permission = "read"
|
||
|
||
now := domain.NowISO()
|
||
shareID := domain.NewID("share")
|
||
_, err = s.db.Exec(
|
||
`INSERT INTO doc_team_shares (id, doc_id, team_id, permission, created_at, created_by)
|
||
VALUES (?, ?, ?, ?, ?, ?)
|
||
ON CONFLICT(doc_id, team_id) DO UPDATE SET permission = excluded.permission`,
|
||
shareID, docID, cleanTeamID, permission, now, currentUserID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
return &domain.DocShare{
|
||
ID: shareID,
|
||
DocID: docID,
|
||
TargetType: "team",
|
||
TargetID: cleanTeamID,
|
||
TargetName: teamName,
|
||
DisplayName: teamName,
|
||
Permission: permission,
|
||
CreatedBy: currentUserID,
|
||
CreatedAt: now,
|
||
}, nil
|
||
}
|
||
|
||
func (s *Store) CreateDocShare(currentUserID, docID, targetUserIdentifier, permission string) (*domain.DocShare, error) {
|
||
doc, err := s.GetDocumentByID(currentUserID, docID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if !doc.IsOwner {
|
||
return nil, fmt.Errorf("nur der Eigentümer kann Freigaben verwalten")
|
||
}
|
||
|
||
cleanTarget := strings.TrimSpace(targetUserIdentifier)
|
||
|
||
// If identifier looks like a team or is prefixed with team_, route to CreateDocTeamShare
|
||
if strings.HasPrefix(cleanTarget, "team_") {
|
||
return s.CreateDocTeamShare(currentUserID, docID, cleanTarget, "read")
|
||
}
|
||
|
||
// Document sharing is strictly read-only
|
||
permission = "read"
|
||
|
||
// Resolve target user by ID, username, or Email
|
||
var targetUser *domain.User
|
||
if strings.HasPrefix(cleanTarget, "usr_") {
|
||
targetUser, _ = s.GetUserByID(cleanTarget)
|
||
}
|
||
if targetUser == nil {
|
||
targetUser, _ = s.GetUserByLogin(cleanTarget)
|
||
}
|
||
if targetUser == nil {
|
||
targetUser, err = s.GetUserByID(cleanTarget)
|
||
}
|
||
if err != nil || targetUser == nil {
|
||
// Check if it's a team ID that doesn't start with team_
|
||
var tName string
|
||
if s.db.QueryRow(`SELECT name FROM teams WHERE id = ?`, cleanTarget).Scan(&tName) == nil {
|
||
return s.CreateDocTeamShare(currentUserID, docID, cleanTarget, permission)
|
||
}
|
||
return nil, fmt.Errorf("benutzer nicht gefunden")
|
||
}
|
||
|
||
if targetUser.ID == doc.UserID {
|
||
return nil, fmt.Errorf("dokument gehört bereits diesem Benutzer")
|
||
}
|
||
|
||
now := domain.NowISO()
|
||
shareID := domain.NewID("share")
|
||
_, err = s.db.Exec(
|
||
`INSERT INTO doc_shares (id, doc_id, user_id, permission, created_at, created_by)
|
||
VALUES (?, ?, ?, ?, ?, ?)
|
||
ON CONFLICT(doc_id, user_id) DO UPDATE SET permission = excluded.permission`,
|
||
shareID, docID, targetUser.ID, permission, now, currentUserID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
return &domain.DocShare{
|
||
ID: shareID,
|
||
DocID: docID,
|
||
TargetType: "user",
|
||
TargetID: targetUser.ID,
|
||
TargetName: targetUser.DisplayName,
|
||
UserID: targetUser.ID,
|
||
UserEmail: targetUser.Email,
|
||
DisplayName: targetUser.DisplayName,
|
||
Permission: permission,
|
||
CreatedBy: currentUserID,
|
||
CreatedAt: now,
|
||
}, nil
|
||
}
|
||
|
||
func (s *Store) DeleteDocShare(currentUserID, docID, targetID string) error {
|
||
doc, err := s.GetDocumentByID(currentUserID, docID)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if !doc.IsOwner && doc.Permission != "write" && currentUserID != targetID {
|
||
return fmt.Errorf("keine Berechtigung zum Entfernen der Freigabe")
|
||
}
|
||
|
||
cleanTarget := strings.TrimSpace(targetID)
|
||
// Try deleting team share first
|
||
res, err := s.db.Exec(`DELETE FROM doc_team_shares WHERE doc_id = ? AND (id = ? OR team_id = ?)`, docID, cleanTarget, cleanTarget)
|
||
if err == nil {
|
||
if rows, _ := res.RowsAffected(); rows > 0 {
|
||
return nil
|
||
}
|
||
}
|
||
|
||
// Fallback to user share
|
||
_, err = s.db.Exec(`DELETE FROM doc_shares WHERE doc_id = ? AND (id = ? OR user_id = ?)`, docID, cleanTarget, cleanTarget)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) GetCollaborators(currentUserID string) ([]domain.User, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT id, email, display_name, role, is_active, avatar_url, created_at FROM users WHERE id != ? AND is_active = 1 ORDER BY display_name ASC`,
|
||
currentUserID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
users := make([]domain.User, 0)
|
||
for rows.Next() {
|
||
var u domain.User
|
||
var activeInt int
|
||
if err := rows.Scan(&u.ID, &u.Email, &u.DisplayName, &u.Role, &activeInt, &u.AvatarURL, &u.CreatedAt); err == nil {
|
||
u.IsActive = activeInt == 1
|
||
users = append(users, u)
|
||
}
|
||
}
|
||
return users, nil
|
||
}
|
||
|
||
// --- STANDALONE REALTIME LISTS & SHARING ---
|
||
|
||
func (s *Store) CreateList(userID, title, icon, listType string) (*domain.SharedList, error) {
|
||
if strings.TrimSpace(title) == "" {
|
||
return nil, errors.New("list title required")
|
||
}
|
||
if icon == "" {
|
||
icon = "🛒"
|
||
}
|
||
if listType == "" {
|
||
listType = "shopping"
|
||
}
|
||
now := domain.NowISO()
|
||
lst := &domain.SharedList{
|
||
ID: domain.NewID("lst"),
|
||
UserID: userID,
|
||
IsOwner: true,
|
||
Role: "editor",
|
||
Title: strings.TrimSpace(title),
|
||
Icon: icon,
|
||
ListType: listType,
|
||
Items: []domain.ListItem{},
|
||
Shares: []domain.ListShare{},
|
||
UpdatedAt: now,
|
||
CreatedAt: now,
|
||
}
|
||
|
||
_, err := s.db.Exec(
|
||
`INSERT INTO lists (id, user_id, title, icon, list_type, updated_at, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)`,
|
||
lst.ID, lst.UserID, lst.Title, lst.Icon, lst.ListType, lst.UpdatedAt, lst.CreatedAt,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
_ = s.db.QueryRow(`SELECT display_name FROM users WHERE id = ?`, userID).Scan(&lst.OwnerName)
|
||
return lst, nil
|
||
}
|
||
|
||
func (s *Store) AddListShare(currentUserID, listID, targetType, targetID, role string) (*domain.ListShare, error) {
|
||
if targetType != "team" && targetType != "user" {
|
||
return nil, errors.New("ungültiger Freigabetyp")
|
||
}
|
||
targetID = strings.TrimSpace(targetID)
|
||
if targetID == "" {
|
||
return nil, errors.New("Ziel-ID erforderlich")
|
||
}
|
||
if role != "viewer" && role != "editor" {
|
||
role = "editor"
|
||
}
|
||
|
||
if targetType == "user" {
|
||
var targetUser *domain.User
|
||
if strings.HasPrefix(targetID, "usr_") {
|
||
targetUser, _ = s.GetUserByID(targetID)
|
||
}
|
||
if targetUser == nil {
|
||
targetUser, _ = s.GetUserByLogin(targetID)
|
||
}
|
||
if targetUser == nil {
|
||
targetUser, _ = s.GetUserByEmail(targetID)
|
||
}
|
||
if targetUser == nil {
|
||
return nil, fmt.Errorf("benutzer nicht gefunden: %s", targetID)
|
||
}
|
||
targetID = targetUser.ID
|
||
}
|
||
|
||
// Verify current user is owner or editor of the list
|
||
var ownerID string
|
||
err := s.db.QueryRow(`SELECT user_id FROM lists WHERE id = ?`, listID).Scan(&ownerID)
|
||
if err != nil {
|
||
return nil, errors.New("liste nicht gefunden")
|
||
}
|
||
if ownerID != currentUserID {
|
||
var myRole string
|
||
err = s.db.QueryRow(`SELECT role FROM list_shares WHERE list_id = ? AND target_type = 'user' AND target_id = ?`, listID, currentUserID).Scan(&myRole)
|
||
if err != nil || myRole != "editor" {
|
||
return nil, errors.New("keine Berechtigung zum Teilen dieser Liste")
|
||
}
|
||
}
|
||
|
||
now := domain.NowISO()
|
||
share := &domain.ListShare{
|
||
ID: domain.NewID("lsh"),
|
||
ListID: listID,
|
||
TargetType: targetType,
|
||
TargetID: targetID,
|
||
Role: role,
|
||
CreatedBy: currentUserID,
|
||
CreatedAt: now,
|
||
}
|
||
|
||
_, err = s.db.Exec(
|
||
`INSERT INTO list_shares (id, list_id, target_type, target_id, role, created_by, created_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)
|
||
ON CONFLICT(list_id, target_type, target_id) DO UPDATE SET role = excluded.role`,
|
||
share.ID, share.ListID, share.TargetType, share.TargetID, share.Role, share.CreatedBy, share.CreatedAt,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
_ = s.db.QueryRow(`SELECT id, created_at FROM list_shares WHERE list_id = ? AND target_type = ? AND target_id = ?`,
|
||
listID, targetType, targetID).Scan(&share.ID, &share.CreatedAt)
|
||
|
||
if targetType == "team" {
|
||
_ = s.db.QueryRow(`SELECT name FROM teams WHERE id = ?`, targetID).Scan(&share.TargetName)
|
||
} else {
|
||
_ = s.db.QueryRow(`SELECT display_name, email FROM users WHERE id = ?`, targetID).Scan(&share.TargetName, &share.Email)
|
||
}
|
||
return share, nil
|
||
}
|
||
|
||
func (s *Store) RemoveListShare(currentUserID, listID, shareIDOrTargetID string) error {
|
||
var ownerID string
|
||
err := s.db.QueryRow(`SELECT user_id FROM lists WHERE id = ?`, listID).Scan(&ownerID)
|
||
if err != nil {
|
||
return errors.New("liste nicht gefunden")
|
||
}
|
||
if ownerID != currentUserID {
|
||
return errors.New("nur der Eigentümer kann Freigaben entfernen")
|
||
}
|
||
|
||
cleanTarget := strings.TrimSpace(shareIDOrTargetID)
|
||
_, err = s.db.Exec(
|
||
`DELETE FROM list_shares WHERE list_id = ? AND (id = ? OR target_id = ?)`,
|
||
listID, cleanTarget, cleanTarget,
|
||
)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) GetListShares(listID string) ([]domain.ListShare, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT id, list_id, target_type, target_id, role, created_by, created_at
|
||
FROM list_shares WHERE list_id = ? ORDER BY created_at ASC`,
|
||
listID,
|
||
)
|
||
if err != nil {
|
||
return []domain.ListShare{}, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
shares := make([]domain.ListShare, 0)
|
||
for rows.Next() {
|
||
var sh domain.ListShare
|
||
if err := rows.Scan(&sh.ID, &sh.ListID, &sh.TargetType, &sh.TargetID, &sh.Role, &sh.CreatedBy, &sh.CreatedAt); err == nil {
|
||
if sh.TargetType == "team" {
|
||
_ = s.db.QueryRow(`SELECT name FROM teams WHERE id = ?`, sh.TargetID).Scan(&sh.TargetName)
|
||
} else {
|
||
_ = s.db.QueryRow(`SELECT display_name, email FROM users WHERE id = ?`, sh.TargetID).Scan(&sh.TargetName, &sh.Email)
|
||
}
|
||
shares = append(shares, sh)
|
||
}
|
||
}
|
||
return shares, nil
|
||
}
|
||
|
||
func (s *Store) GetAllListsForUser(userID string) ([]domain.SharedList, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT DISTINCT l.id, l.user_id, l.title, l.icon, l.list_type, l.updated_at, l.created_at,
|
||
u.display_name,
|
||
CASE
|
||
WHEN l.user_id = ? THEN 'owner'
|
||
WHEN ls.role IS NOT NULL THEN ls.role
|
||
ELSE 'editor'
|
||
END as effective_role
|
||
FROM lists l
|
||
LEFT JOIN users u ON u.id = l.user_id
|
||
LEFT JOIN list_shares ls ON ls.list_id = l.id AND (
|
||
(ls.target_type = 'user' AND ls.target_id = ?)
|
||
OR (ls.target_type = 'team' AND ls.target_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
)
|
||
WHERE l.user_id = ?
|
||
OR ls.id IS NOT NULL
|
||
ORDER BY l.updated_at DESC`,
|
||
userID, userID, userID, userID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
lists := make([]domain.SharedList, 0)
|
||
for rows.Next() {
|
||
var l domain.SharedList
|
||
var ownerName sql.NullString
|
||
var effectiveRole string
|
||
if err := rows.Scan(&l.ID, &l.UserID, &l.Title, &l.Icon, &l.ListType, &l.UpdatedAt, &l.CreatedAt, &ownerName, &effectiveRole); err == nil {
|
||
l.OwnerName = ownerName.String
|
||
l.IsOwner = (l.UserID == userID)
|
||
l.Role = effectiveRole
|
||
lists = append(lists, l)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
|
||
for i := range lists {
|
||
lists[i].Items, _ = s.GetListItems(lists[i].ID)
|
||
lists[i].Shares, _ = s.GetListShares(lists[i].ID)
|
||
}
|
||
return lists, nil
|
||
}
|
||
|
||
func (s *Store) GetListByID(userID, listID string) (*domain.SharedList, error) {
|
||
var l domain.SharedList
|
||
var ownerName sql.NullString
|
||
var effectiveRole string
|
||
|
||
var query string
|
||
var args []interface{}
|
||
if userID == "" {
|
||
query = `SELECT l.id, l.user_id, l.title, l.icon, l.list_type, l.updated_at, l.created_at,
|
||
u.display_name, 'owner' as effective_role
|
||
FROM lists l
|
||
LEFT JOIN users u ON u.id = l.user_id
|
||
WHERE l.id = ?`
|
||
args = []interface{}{listID}
|
||
} else {
|
||
query = `SELECT l.id, l.user_id, l.title, l.icon, l.list_type, l.updated_at, l.created_at,
|
||
u.display_name,
|
||
CASE
|
||
WHEN l.user_id = ? THEN 'owner'
|
||
WHEN ls.role IS NOT NULL THEN ls.role
|
||
ELSE 'editor'
|
||
END as effective_role
|
||
FROM lists l
|
||
LEFT JOIN users u ON u.id = l.user_id
|
||
LEFT JOIN list_shares ls ON ls.list_id = l.id AND (
|
||
(ls.target_type = 'user' AND ls.target_id = ?)
|
||
OR (ls.target_type = 'team' AND ls.target_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
)
|
||
WHERE l.id = ? AND (
|
||
l.user_id = ?
|
||
OR ls.id IS NOT NULL
|
||
)`
|
||
args = []interface{}{userID, userID, userID, listID, userID}
|
||
}
|
||
|
||
err := s.db.QueryRow(query, args...).Scan(&l.ID, &l.UserID, &l.Title, &l.Icon, &l.ListType, &l.UpdatedAt, &l.CreatedAt, &ownerName, &effectiveRole)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
l.OwnerName = ownerName.String
|
||
l.IsOwner = (userID == "" || l.UserID == userID)
|
||
l.Role = effectiveRole
|
||
l.Items, _ = s.GetListItems(l.ID)
|
||
l.Shares, _ = s.GetListShares(l.ID)
|
||
return &l, nil
|
||
}
|
||
|
||
func (s *Store) DeleteList(userID, listID string) error {
|
||
var ownerID string
|
||
err := s.db.QueryRow(`SELECT user_id FROM lists WHERE id = ?`, listID).Scan(&ownerID)
|
||
if err != nil {
|
||
return errors.New("liste nicht gefunden")
|
||
}
|
||
if ownerID != userID {
|
||
return errors.New("nur der Eigentümer kann die Liste löschen")
|
||
}
|
||
_, err = s.db.Exec(`DELETE FROM lists WHERE id = ?`, listID)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) GetListItems(listID string) ([]domain.ListItem, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT id, list_id, content, category, quantity, is_completed, completed_by, completed_by_name, sort_order, created_at, completed_at
|
||
FROM list_items WHERE list_id = ?
|
||
ORDER BY is_completed ASC, sort_order ASC, created_at DESC`,
|
||
listID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
items := make([]domain.ListItem, 0)
|
||
for rows.Next() {
|
||
var it domain.ListItem
|
||
var doneInt int
|
||
if err := rows.Scan(&it.ID, &it.ListID, &it.Content, &it.Category, &it.Quantity, &doneInt, &it.CompletedBy, &it.CompletedByName, &it.SortOrder, &it.CreatedAt, &it.CompletedAt); err == nil {
|
||
it.IsCompleted = doneInt != 0
|
||
items = append(items, it)
|
||
}
|
||
}
|
||
return items, nil
|
||
}
|
||
|
||
func (s *Store) CreateListItem(listID, content, category, quantity string, sortOrder int) (*domain.ListItem, error) {
|
||
content = strings.TrimSpace(content)
|
||
if content == "" {
|
||
return nil, errors.New("item content cannot be empty")
|
||
}
|
||
|
||
category = strings.TrimSpace(category)
|
||
// Auto-extract hashtag if category is empty
|
||
if category == "" && strings.Contains(content, "#") {
|
||
re := regexp.MustCompile(`#([a-zA-Z0-9äöüÄÖÜß_-]+)`)
|
||
if m := re.FindStringSubmatch(content); len(m) > 1 {
|
||
category = m[1]
|
||
cleaned := strings.TrimSpace(re.ReplaceAllString(content, ""))
|
||
if cleaned != "" {
|
||
content = cleaned
|
||
}
|
||
}
|
||
} else if category != "" {
|
||
category = strings.TrimPrefix(category, "#")
|
||
}
|
||
|
||
now := domain.NowISO()
|
||
it := &domain.ListItem{
|
||
ID: domain.NewID("itm"),
|
||
ListID: listID,
|
||
Content: content,
|
||
Category: category,
|
||
Quantity: strings.TrimSpace(quantity),
|
||
IsCompleted: false,
|
||
SortOrder: sortOrder,
|
||
CreatedAt: now,
|
||
}
|
||
|
||
_, err := s.db.Exec(
|
||
`INSERT INTO list_items (id, list_id, content, category, quantity, is_completed, sort_order, created_at)
|
||
VALUES (?, ?, ?, ?, ?, 0, ?, ?)`,
|
||
it.ID, it.ListID, it.Content, it.Category, it.Quantity, it.SortOrder, it.CreatedAt,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
_, _ = s.db.Exec(`UPDATE lists SET updated_at = ? WHERE id = ?`, now, listID)
|
||
return it, nil
|
||
}
|
||
|
||
func (s *Store) ToggleListItem(itemID string, isCompleted bool, userID, userName string) (*domain.ListItem, error) {
|
||
now := domain.NowISO()
|
||
doneInt := 0
|
||
completedAt := ""
|
||
completedBy := ""
|
||
completedByName := ""
|
||
if isCompleted {
|
||
doneInt = 1
|
||
completedAt = now
|
||
completedBy = userID
|
||
completedByName = userName
|
||
}
|
||
|
||
_, err := s.db.Exec(
|
||
`UPDATE list_items SET is_completed = ?, completed_by = ?, completed_by_name = ?, completed_at = ? WHERE id = ?`,
|
||
doneInt, completedBy, completedByName, completedAt, itemID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
var it domain.ListItem
|
||
var dInt int
|
||
err = s.db.QueryRow(
|
||
`SELECT id, list_id, content, category, quantity, is_completed, completed_by, completed_by_name, sort_order, created_at, completed_at
|
||
FROM list_items WHERE id = ?`,
|
||
itemID,
|
||
).Scan(&it.ID, &it.ListID, &it.Content, &it.Category, &it.Quantity, &dInt, &it.CompletedBy, &it.CompletedByName, &it.SortOrder, &it.CreatedAt, &it.CompletedAt)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
it.IsCompleted = dInt != 0
|
||
_, _ = s.db.Exec(`UPDATE lists SET updated_at = ? WHERE id = ?`, now, it.ListID)
|
||
return &it, nil
|
||
}
|
||
|
||
func (s *Store) UpdateListItem(itemID string, content *string, category *string, quantity *string) (*domain.ListItem, error) {
|
||
now := domain.NowISO()
|
||
var it domain.ListItem
|
||
var dInt int
|
||
err := s.db.QueryRow(
|
||
`SELECT id, list_id, content, category, quantity, is_completed, completed_by, completed_by_name, sort_order, created_at, completed_at
|
||
FROM list_items WHERE id = ?`,
|
||
itemID,
|
||
).Scan(&it.ID, &it.ListID, &it.Content, &it.Category, &it.Quantity, &dInt, &it.CompletedBy, &it.CompletedByName, &it.SortOrder, &it.CreatedAt, &it.CompletedAt)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
it.IsCompleted = dInt != 0
|
||
|
||
if content != nil {
|
||
it.Content = *content
|
||
}
|
||
if category != nil {
|
||
it.Category = *category
|
||
}
|
||
if quantity != nil {
|
||
it.Quantity = *quantity
|
||
}
|
||
|
||
_, err = s.db.Exec(`UPDATE list_items SET content = ?, category = ?, quantity = ? WHERE id = ?`, it.Content, it.Category, it.Quantity, itemID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
_, _ = s.db.Exec(`UPDATE lists SET updated_at = ? WHERE id = ?`, now, it.ListID)
|
||
return &it, nil
|
||
}
|
||
|
||
func (s *Store) CreateTaskFromDocBlock(userID, docID, blockID, content string, checked bool) (*domain.Thought, error) {
|
||
doc, err := s.GetDocumentByID(userID, docID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
t, err := s.CreateThoughtWithDetails(userID, content, "normal", "manual", "", "", "todo", "")
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
t.ConvertedType = "document_block"
|
||
t.ConvertedRefID = docID + ":" + blockID
|
||
t.DocID = docID
|
||
t.BlockID = blockID
|
||
t.IsCompleted = checked
|
||
t.Status = domain.StatusKept
|
||
doneInt := 0
|
||
if checked {
|
||
doneInt = 1
|
||
t.KanbanStatus = "done"
|
||
}
|
||
_, _ = s.db.Exec(`UPDATE thoughts SET converted_type = 'document_block', converted_ref_id = ?, is_completed = ?, kanban_status = ?, status = 'kept' WHERE id = ?`,
|
||
t.ConvertedRefID, doneInt, t.KanbanStatus, t.ID)
|
||
|
||
var updatedBlocks []domain.DocumentBlock
|
||
found := false
|
||
for _, b := range doc.Blocks {
|
||
if b.ID == blockID {
|
||
b.TaskID = t.ID
|
||
b.Checked = checked
|
||
found = true
|
||
}
|
||
updatedBlocks = append(updatedBlocks, b)
|
||
}
|
||
if found {
|
||
_, _ = s.UpdateDocument(userID, docID, nil, nil, nil, &updatedBlocks, nil, nil)
|
||
}
|
||
return t, nil
|
||
}
|
||
|
||
func (s *Store) SyncDocBlockAndTask(userID, taskID string, isCompleted bool) {
|
||
t, err := s.GetThoughtByID(userID, taskID)
|
||
if err != nil || t == nil || t.ConvertedType != "document_block" || !strings.Contains(t.ConvertedRefID, ":") {
|
||
return
|
||
}
|
||
parts := strings.SplitN(t.ConvertedRefID, ":", 2)
|
||
docID, blockID := parts[0], parts[1]
|
||
doc, err := s.GetDocumentByID(userID, docID)
|
||
if err != nil || doc == nil {
|
||
return
|
||
}
|
||
var updatedBlocks []domain.DocumentBlock
|
||
found := false
|
||
for _, b := range doc.Blocks {
|
||
if b.ID == blockID {
|
||
if b.Checked != isCompleted {
|
||
b.Checked = isCompleted
|
||
found = true
|
||
}
|
||
}
|
||
updatedBlocks = append(updatedBlocks, b)
|
||
}
|
||
if found {
|
||
_, _ = s.UpdateDocument(userID, docID, nil, nil, nil, &updatedBlocks, nil, nil)
|
||
}
|
||
}
|
||
|
||
func (s *Store) UnlinkTaskFromDocBlock(userID, taskID string) {
|
||
t, err := s.GetThoughtByID(userID, taskID)
|
||
if err != nil || t == nil {
|
||
return
|
||
}
|
||
docID := t.DocID
|
||
blockID := t.BlockID
|
||
if (docID == "" || blockID == "") && strings.Contains(t.ConvertedRefID, ":") {
|
||
parts := strings.SplitN(t.ConvertedRefID, ":", 2)
|
||
docID, blockID = parts[0], parts[1]
|
||
}
|
||
if docID != "" {
|
||
if doc, err := s.GetDocumentByID(userID, docID); err == nil && doc != nil {
|
||
var updatedBlocks []domain.DocumentBlock
|
||
found := false
|
||
for _, b := range doc.Blocks {
|
||
if b.ID == blockID || b.TaskID == taskID {
|
||
b.TaskID = ""
|
||
found = true
|
||
}
|
||
updatedBlocks = append(updatedBlocks, b)
|
||
}
|
||
if found {
|
||
_, _ = s.UpdateDocument(userID, docID, nil, nil, nil, &updatedBlocks, nil, nil)
|
||
}
|
||
}
|
||
}
|
||
_, _ = s.db.Exec(`UPDATE thoughts SET converted_type = '', converted_ref_id = '', doc_id = '', block_id = '' WHERE id = ?`, taskID)
|
||
}
|
||
|
||
func (s *Store) SyncTaskFromBlockCheck(userID, taskID string, checked bool) {
|
||
if taskID == "" {
|
||
return
|
||
}
|
||
doneInt := 0
|
||
kanban := "todo"
|
||
if checked {
|
||
doneInt = 1
|
||
kanban = "done"
|
||
}
|
||
_, _ = s.db.Exec(`UPDATE thoughts SET is_completed = ?, kanban_status = ? WHERE id = ? AND user_id = ?`, doneInt, kanban, taskID, userID)
|
||
}
|
||
|
||
func (s *Store) DeleteListItem(itemID string) (string, error) {
|
||
var listID string
|
||
_ = s.db.QueryRow(`SELECT list_id FROM list_items WHERE id = ?`, itemID).Scan(&listID)
|
||
_, err := s.db.Exec(`DELETE FROM list_items WHERE id = ?`, itemID)
|
||
if listID != "" {
|
||
_, _ = s.db.Exec(`UPDATE lists SET updated_at = ? WHERE id = ?`, domain.NowISO(), listID)
|
||
}
|
||
return listID, err
|
||
}
|
||
|
||
func (s *Store) ClearCompletedListItems(listID string) (int64, error) {
|
||
res, err := s.db.Exec(`DELETE FROM list_items WHERE list_id = ? AND is_completed = 1`, listID)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
_, _ = s.db.Exec(`UPDATE lists SET updated_at = ? WHERE id = ?`, domain.NowISO(), listID)
|
||
return res.RowsAffected()
|
||
}
|
||
|
||
func (s *Store) ReorderListItems(listID string, items []domain.ListItemReorderItem) error {
|
||
tx, err := s.db.Begin()
|
||
if err != nil {
|
||
return err
|
||
}
|
||
defer tx.Rollback()
|
||
|
||
for _, it := range items {
|
||
if it.Category != nil && it.Content != nil {
|
||
_, err = tx.Exec(
|
||
`UPDATE list_items SET sort_order = ?, category = ?, content = ? WHERE id = ? AND list_id = ?`,
|
||
it.SortOrder, *it.Category, *it.Content, it.ID, listID,
|
||
)
|
||
} else if it.Category != nil {
|
||
_, err = tx.Exec(
|
||
`UPDATE list_items SET sort_order = ?, category = ? WHERE id = ? AND list_id = ?`,
|
||
it.SortOrder, *it.Category, it.ID, listID,
|
||
)
|
||
} else if it.Content != nil {
|
||
_, err = tx.Exec(
|
||
`UPDATE list_items SET sort_order = ?, content = ? WHERE id = ? AND list_id = ?`,
|
||
it.SortOrder, *it.Content, it.ID, listID,
|
||
)
|
||
} else {
|
||
_, err = tx.Exec(
|
||
`UPDATE list_items SET sort_order = ? WHERE id = ? AND list_id = ?`,
|
||
it.SortOrder, it.ID, listID,
|
||
)
|
||
}
|
||
if err != nil {
|
||
return err
|
||
}
|
||
}
|
||
|
||
_, _ = tx.Exec(`UPDATE lists SET updated_at = ? WHERE id = ?`, domain.NowISO(), listID)
|
||
return tx.Commit()
|
||
}
|
||
|
||
// --- API KEYS & WEBHOOKS ---
|
||
|
||
func (s *Store) CreateAPIKey(userID, name string) (*domain.APIKey, error) {
|
||
if strings.TrimSpace(name) == "" {
|
||
name = "Smarthome / Webhook Key"
|
||
}
|
||
rawKey, prefix, hash := auth.GenerateAPIKey()
|
||
k := &domain.APIKey{
|
||
ID: domain.NewID("key"),
|
||
UserID: userID,
|
||
Name: strings.TrimSpace(name),
|
||
KeyPrefix: prefix,
|
||
KeyHash: hash,
|
||
RawKey: rawKey,
|
||
Scopes: "ingest,read",
|
||
CreatedAt: domain.NowISO(),
|
||
}
|
||
|
||
_, err := s.db.Exec(
|
||
`INSERT INTO api_keys (id, user_id, name, key_prefix, key_hash, scopes, created_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)`,
|
||
k.ID, k.UserID, k.Name, k.KeyPrefix, k.KeyHash, k.Scopes, k.CreatedAt,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
return k, nil
|
||
}
|
||
|
||
func (s *Store) GetAPIKeys(userID string) ([]domain.APIKey, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT id, user_id, name, key_prefix, scopes, last_used_at, created_at
|
||
FROM api_keys WHERE user_id = ? ORDER BY created_at DESC`,
|
||
userID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
keys := make([]domain.APIKey, 0)
|
||
for rows.Next() {
|
||
var k domain.APIKey
|
||
if err := rows.Scan(&k.ID, &k.UserID, &k.Name, &k.KeyPrefix, &k.Scopes, &k.LastUsedAt, &k.CreatedAt); err == nil {
|
||
keys = append(keys, k)
|
||
}
|
||
}
|
||
return keys, nil
|
||
}
|
||
|
||
func (s *Store) DeleteAPIKey(userID, keyID string) error {
|
||
_, err := s.db.Exec(`DELETE FROM api_keys WHERE id = ? AND user_id = ?`, keyID, userID)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) ValidateAPIKeyAndGetUser(rawKey string) (*domain.User, error) {
|
||
hash := auth.HashAPIKey(strings.TrimSpace(rawKey))
|
||
var keyID, userID string
|
||
err := s.db.QueryRow(`SELECT id, user_id FROM api_keys WHERE key_hash = ?`, hash).Scan(&keyID, &userID)
|
||
if err != nil {
|
||
return nil, errors.New("invalid API key")
|
||
}
|
||
_, _ = s.db.Exec(`UPDATE api_keys SET last_used_at = ? WHERE id = ?`, domain.NowISO(), keyID)
|
||
return s.GetUserByID(userID)
|
||
}
|
||
|
||
// --- STATS, SEARCH & EXPORT ---
|
||
|
||
func (s *Store) GetDashboardStats(userID string) (*domain.DashboardStats, error) {
|
||
st := &domain.DashboardStats{}
|
||
_ = s.db.QueryRow(`SELECT COUNT(*) FROM thoughts WHERE user_id = ? AND status = 'inbox'`, userID).Scan(&st.InboxCount)
|
||
_ = s.db.QueryRow(`SELECT COUNT(*) FROM thoughts WHERE user_id = ? AND (status = 'kept' OR (status = 'converted' AND converted_type = 'task')) AND is_completed = 0`, userID).Scan(&st.TasksOpenCount)
|
||
_ = s.db.QueryRow(`SELECT COUNT(*) FROM thoughts WHERE user_id = ? AND (status = 'kept' OR (status = 'converted' AND converted_type = 'task')) AND is_completed = 1`, userID).Scan(&st.TasksDoneCount)
|
||
_ = s.db.QueryRow(`SELECT COUNT(*) FROM documents WHERE user_id = ? AND is_archived = 0`, userID).Scan(&st.DocsCount)
|
||
_ = s.db.QueryRow(`SELECT COUNT(*) FROM thoughts WHERE user_id = ? AND status = 'discarded'`, userID).Scan(&st.DiscardedCount)
|
||
_ = s.db.QueryRow(`
|
||
SELECT COUNT(DISTINCT l.id)
|
||
FROM lists l
|
||
LEFT JOIN list_shares ls ON ls.list_id = l.id AND (
|
||
(ls.target_type = 'user' AND ls.target_id = ?)
|
||
OR (ls.target_type = 'team' AND ls.target_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
)
|
||
WHERE l.user_id = ? OR ls.id IS NOT NULL`,
|
||
userID, userID, userID).Scan(&st.ListsCount)
|
||
|
||
// Count open (uncompleted) To-Dos across accessible workspace documents (owned or shared)
|
||
docRows, err := s.db.Query(`
|
||
SELECT blocks_json FROM documents
|
||
WHERE (user_id = ? OR id IN (SELECT doc_id FROM doc_shares WHERE user_id = ?))
|
||
AND is_archived = 0`,
|
||
userID, userID,
|
||
)
|
||
if err == nil {
|
||
for docRows.Next() {
|
||
var bj string
|
||
if err := docRows.Scan(&bj); err == nil && bj != "" {
|
||
var blocks []domain.DocumentBlock
|
||
if err := json.Unmarshal([]byte(bj), &blocks); err == nil {
|
||
for _, b := range blocks {
|
||
if b.Type == "todo" && !b.Checked {
|
||
st.DocsTodosOpenCount++
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
_ = docRows.Close()
|
||
}
|
||
|
||
return st, nil
|
||
}
|
||
|
||
func (s *Store) SearchAll(userID, query string) ([]domain.Thought, []domain.Document, error) {
|
||
q := "%" + strings.ToLower(strings.TrimSpace(query)) + "%"
|
||
|
||
tRows, err := s.db.Query(
|
||
`SELECT id, user_id, content, tags, priority, source, status, is_completed, converted_type, converted_ref_id, due_date, task_list_id, kanban_status, notes, alarm_sent, created_at, triaged_at, subtasks, sort_order
|
||
FROM thoughts WHERE user_id = ? AND LOWER(content) LIKE ? ORDER BY created_at DESC LIMIT 30`,
|
||
userID, q,
|
||
)
|
||
thoughts := make([]domain.Thought, 0)
|
||
if err == nil {
|
||
for tRows.Next() {
|
||
if t, err := scanThought(tRows); err == nil {
|
||
thoughts = append(thoughts, *t)
|
||
}
|
||
}
|
||
_ = tRows.Close()
|
||
}
|
||
|
||
dRows, err := s.db.Query(
|
||
`SELECT d.id, d.user_id, d.space_id, d.title, d.icon, d.content, d.blocks_json, d.tags, d.folder_path,
|
||
CASE WHEN p.doc_id IS NOT NULL THEN 1 ELSE 0 END AS is_pinned,
|
||
d.is_archived, d.created_at, d.updated_at,
|
||
d.shared_folder_id, d.sub_folder_path
|
||
FROM documents d
|
||
LEFT JOIN doc_pins p ON p.doc_id = d.id AND p.user_id = ?
|
||
WHERE d.user_id = ? AND (LOWER(d.title) LIKE ? OR LOWER(d.content) LIKE ?) ORDER BY is_pinned DESC, d.updated_at DESC LIMIT 20`,
|
||
userID, userID, q, q,
|
||
)
|
||
docs := make([]domain.Document, 0)
|
||
if err == nil {
|
||
for dRows.Next() {
|
||
if d, err := scanDocument(dRows); err == nil {
|
||
docs = append(docs, *d)
|
||
}
|
||
}
|
||
_ = dRows.Close()
|
||
}
|
||
|
||
return thoughts, docs, nil
|
||
}
|
||
|
||
func (s *Store) ExportUserData(userID string) (*domain.FullExport, error) {
|
||
user, err := s.GetUserByID(userID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
thoughts, _ := s.GetThoughts(userID, "all", 5000)
|
||
docs, _ := s.GetDocuments(userID)
|
||
lists, _ := s.GetAllListsForUser(userID)
|
||
taskLists, _ := s.GetTaskLists(userID)
|
||
|
||
return &domain.FullExport{
|
||
ExportedAt: domain.NowISO(),
|
||
Version: "1.0.0",
|
||
User: *user,
|
||
Thoughts: thoughts,
|
||
Documents: docs,
|
||
Lists: lists,
|
||
TaskLists: taskLists,
|
||
}, nil
|
||
}
|
||
|
||
// --- TASK LISTS ---
|
||
|
||
func (s *Store) CreateTaskList(userID, spaceID, teamID, title, icon, color string, columns ...[]domain.KanbanColumn) (*domain.TaskList, error) {
|
||
if strings.TrimSpace(title) == "" {
|
||
title = "Meine Aufgaben"
|
||
}
|
||
if icon == "" {
|
||
icon = "📋"
|
||
}
|
||
if color == "" {
|
||
color = "#2563eb"
|
||
}
|
||
cols := []domain.KanbanColumn{
|
||
{ID: "todo", Name: "Zu erledigen"},
|
||
{ID: "in_progress", Name: "In Bearbeitung", Color: "accent"},
|
||
{ID: "waiting", Name: "Wartet auf andere", Color: "amber"},
|
||
{ID: "done", Name: "Erledigt", IsDone: true, Color: "emerald"},
|
||
}
|
||
if len(columns) > 0 && len(columns[0]) > 0 {
|
||
cols = columns[0]
|
||
}
|
||
colsJSON, _ := json.Marshal(cols)
|
||
now := domain.NowISO()
|
||
tl := &domain.TaskList{
|
||
ID: domain.NewID("tkl"),
|
||
UserID: userID,
|
||
SpaceID: spaceID,
|
||
TeamID: teamID,
|
||
Title: strings.TrimSpace(title),
|
||
Icon: icon,
|
||
Color: color,
|
||
Columns: cols,
|
||
CreatedAt: now,
|
||
UpdatedAt: now,
|
||
IsOwner: true,
|
||
}
|
||
_, err := s.db.Exec(
|
||
`INSERT INTO task_lists (id, user_id, space_id, team_id, title, icon, color, columns, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
||
tl.ID, tl.UserID, tl.SpaceID, tl.TeamID, tl.Title, tl.Icon, tl.Color, string(colsJSON), tl.CreatedAt, tl.UpdatedAt,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if teamID != "" {
|
||
_, _ = s.AddTaskListShare(tl.ID, "team", teamID, userID)
|
||
}
|
||
return s.GetTaskListByID(userID, tl.ID)
|
||
}
|
||
|
||
func (s *Store) AddTaskListShare(taskListID, targetType, targetID, createdBy string) (*domain.TaskListShare, error) {
|
||
if targetType != "team" && targetType != "user" {
|
||
return nil, errors.New("ungültiger Freigabetyp")
|
||
}
|
||
targetID = strings.TrimSpace(targetID)
|
||
if targetID == "" {
|
||
return nil, errors.New("Ziel-ID erforderlich")
|
||
}
|
||
now := domain.NowISO()
|
||
share := &domain.TaskListShare{
|
||
ID: domain.NewID("tls"),
|
||
TaskListID: taskListID,
|
||
TargetType: targetType,
|
||
TargetID: targetID,
|
||
CreatedAt: now,
|
||
}
|
||
_, err := s.db.Exec(
|
||
`INSERT INTO task_list_shares (id, task_list_id, target_type, target_id, created_at, created_by)
|
||
VALUES (?, ?, ?, ?, ?, ?)
|
||
ON CONFLICT(task_list_id, target_type, target_id) DO UPDATE SET created_at = excluded.created_at`,
|
||
share.ID, share.TaskListID, share.TargetType, share.TargetID, share.CreatedAt, createdBy,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
_ = s.db.QueryRow(`SELECT id, created_at FROM task_list_shares WHERE task_list_id = ? AND target_type = ? AND target_id = ?`,
|
||
taskListID, targetType, targetID).Scan(&share.ID, &share.CreatedAt)
|
||
|
||
if targetType == "team" {
|
||
_, _ = s.db.Exec(`UPDATE task_lists SET team_id = ? WHERE id = ? AND (team_id = '' OR team_id IS NULL)`, targetID, taskListID)
|
||
_ = s.db.QueryRow(`SELECT name FROM teams WHERE id = ?`, targetID).Scan(&share.TargetName)
|
||
} else {
|
||
_ = s.db.QueryRow(`SELECT display_name, email FROM users WHERE id = ?`, targetID).Scan(&share.TargetName, &share.Email)
|
||
}
|
||
return share, nil
|
||
}
|
||
|
||
func (s *Store) RemoveTaskListShare(taskListID, shareIDOrTargetID string) error {
|
||
var targetType, targetID string
|
||
_ = s.db.QueryRow(`SELECT target_type, target_id FROM task_list_shares WHERE task_list_id = ? AND (id = ? OR target_id = ?)`, taskListID, shareIDOrTargetID, shareIDOrTargetID).Scan(&targetType, &targetID)
|
||
|
||
_, err := s.db.Exec(
|
||
`DELETE FROM task_list_shares WHERE task_list_id = ? AND (id = ? OR target_id = ?)`,
|
||
taskListID, shareIDOrTargetID, shareIDOrTargetID,
|
||
)
|
||
if targetType == "team" {
|
||
_, _ = s.db.Exec(`UPDATE task_lists SET team_id = '' WHERE id = ? AND team_id = ?`, taskListID, targetID)
|
||
}
|
||
return err
|
||
}
|
||
|
||
func (s *Store) GetTaskListShares(taskListID string) ([]domain.TaskListShare, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT id, task_list_id, target_type, target_id, created_at
|
||
FROM task_list_shares WHERE task_list_id = ? ORDER BY created_at ASC`,
|
||
taskListID,
|
||
)
|
||
if err != nil {
|
||
return []domain.TaskListShare{}, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
shares := make([]domain.TaskListShare, 0)
|
||
for rows.Next() {
|
||
var sh domain.TaskListShare
|
||
if err := rows.Scan(&sh.ID, &sh.TaskListID, &sh.TargetType, &sh.TargetID, &sh.CreatedAt); err == nil {
|
||
shares = append(shares, sh)
|
||
}
|
||
}
|
||
for i := range shares {
|
||
if shares[i].TargetType == "team" {
|
||
_ = s.db.QueryRow(`SELECT name FROM teams WHERE id = ?`, shares[i].TargetID).Scan(&shares[i].TargetName)
|
||
} else {
|
||
_ = s.db.QueryRow(`SELECT display_name, email, avatar_url FROM users WHERE id = ?`, shares[i].TargetID).Scan(&shares[i].TargetName, &shares[i].Email, &shares[i].AvatarURL)
|
||
}
|
||
}
|
||
return shares, nil
|
||
}
|
||
|
||
func (s *Store) getMembersForTaskList(tl *domain.TaskList) []domain.TaskListMember {
|
||
memberMap := make(map[string]domain.TaskListMember)
|
||
|
||
// 1. Owner
|
||
var ownerName, ownerEmail, ownerAvatar string
|
||
_ = s.db.QueryRow(`SELECT display_name, email, avatar_url FROM users WHERE id = ?`, tl.UserID).Scan(&ownerName, &ownerEmail, &ownerAvatar)
|
||
memberMap[tl.UserID] = domain.TaskListMember{
|
||
UserID: tl.UserID,
|
||
DisplayName: ownerName,
|
||
Email: ownerEmail,
|
||
AvatarURL: ownerAvatar,
|
||
Role: "owner",
|
||
}
|
||
|
||
// 2. Team members if tl.TeamID != ""
|
||
if tl.TeamID != "" {
|
||
rows, err := s.db.Query(
|
||
`SELECT u.id, u.display_name, u.email, u.avatar_url FROM team_members tm JOIN users u ON u.id = tm.user_id WHERE tm.team_id = ?`,
|
||
tl.TeamID,
|
||
)
|
||
if err == nil {
|
||
for rows.Next() {
|
||
var m domain.TaskListMember
|
||
if err := rows.Scan(&m.UserID, &m.DisplayName, &m.Email, &m.AvatarURL); err == nil {
|
||
m.Role = "member"
|
||
memberMap[m.UserID] = m
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
}
|
||
}
|
||
|
||
// 3. From task_list_shares
|
||
for _, sh := range tl.Shares {
|
||
if sh.TargetType == "team" {
|
||
rows, err := s.db.Query(
|
||
`SELECT u.id, u.display_name, u.email, u.avatar_url FROM team_members tm JOIN users u ON u.id = tm.user_id WHERE tm.team_id = ?`,
|
||
sh.TargetID,
|
||
)
|
||
if err == nil {
|
||
for rows.Next() {
|
||
var m domain.TaskListMember
|
||
if err := rows.Scan(&m.UserID, &m.DisplayName, &m.Email, &m.AvatarURL); err == nil {
|
||
m.Role = "member"
|
||
memberMap[m.UserID] = m
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
}
|
||
} else if sh.TargetType == "user" {
|
||
var m domain.TaskListMember
|
||
if err := s.db.QueryRow(`SELECT id, display_name, email, avatar_url FROM users WHERE id = ?`, sh.TargetID).Scan(&m.UserID, &m.DisplayName, &m.Email, &m.AvatarURL); err == nil {
|
||
m.Role = "member"
|
||
memberMap[m.UserID] = m
|
||
}
|
||
}
|
||
}
|
||
|
||
result := make([]domain.TaskListMember, 0, len(memberMap))
|
||
for _, m := range memberMap {
|
||
result = append(result, m)
|
||
}
|
||
return result
|
||
}
|
||
|
||
func (s *Store) GetTaskLists(userID string) ([]domain.TaskList, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT id, user_id, space_id, COALESCE(team_id, ''), title, icon, color, COALESCE(columns, '[]'), created_at, updated_at
|
||
FROM task_lists
|
||
WHERE user_id = ?
|
||
OR (team_id != '' AND team_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
OR id IN (
|
||
SELECT task_list_id FROM task_list_shares
|
||
WHERE (target_type = 'user' AND target_id = ?)
|
||
OR (target_type = 'team' AND target_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
)
|
||
ORDER BY updated_at DESC`,
|
||
userID, userID, userID, userID,
|
||
)
|
||
if err != nil {
|
||
return []domain.TaskList{}, err
|
||
}
|
||
|
||
defaultCols := []domain.KanbanColumn{
|
||
{ID: "todo", Name: "Zu erledigen"},
|
||
{ID: "in_progress", Name: "In Bearbeitung", Color: "accent"},
|
||
{ID: "waiting", Name: "Wartet auf andere", Color: "amber"},
|
||
{ID: "done", Name: "Erledigt", IsDone: true, Color: "emerald"},
|
||
}
|
||
|
||
list := make([]domain.TaskList, 0)
|
||
for rows.Next() {
|
||
var tl domain.TaskList
|
||
var colsJSON string
|
||
if err := rows.Scan(&tl.ID, &tl.UserID, &tl.SpaceID, &tl.TeamID, &tl.Title, &tl.Icon, &tl.Color, &colsJSON, &tl.CreatedAt, &tl.UpdatedAt); err == nil {
|
||
tl.IsOwner = (tl.UserID == userID)
|
||
_ = json.Unmarshal([]byte(colsJSON), &tl.Columns)
|
||
if len(tl.Columns) == 0 {
|
||
tl.Columns = defaultCols
|
||
}
|
||
list = append(list, tl)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
|
||
for i := range list {
|
||
list[i].Members = make([]domain.TaskListMember, 0)
|
||
list[i].Shares = make([]domain.TaskListShare, 0)
|
||
|
||
_ = s.db.QueryRow(
|
||
`SELECT COUNT(*) FROM thoughts WHERE task_list_id = ? AND is_completed = 0 AND (status = 'kept' OR (status = 'converted' AND converted_type = 'task'))`,
|
||
list[i].ID,
|
||
).Scan(&list[i].TasksCount)
|
||
_ = s.db.QueryRow(`SELECT display_name FROM users WHERE id = ?`, list[i].UserID).Scan(&list[i].OwnerName)
|
||
|
||
if list[i].TeamID != "" {
|
||
_ = s.db.QueryRow(`SELECT name FROM teams WHERE id = ?`, list[i].TeamID).Scan(&list[i].TeamName)
|
||
}
|
||
|
||
shares, _ := s.GetTaskListShares(list[i].ID)
|
||
list[i].Shares = shares
|
||
list[i].Members = s.getMembersForTaskList(&list[i])
|
||
}
|
||
return list, nil
|
||
}
|
||
|
||
func (s *Store) GetTaskListByID(userID, listID string) (*domain.TaskList, error) {
|
||
var tl domain.TaskList
|
||
var colsJSON string
|
||
err := s.db.QueryRow(
|
||
`SELECT id, user_id, space_id, COALESCE(team_id, ''), title, icon, color, COALESCE(columns, '[]'), created_at, updated_at
|
||
FROM task_lists
|
||
WHERE id = ? AND (
|
||
user_id = ?
|
||
OR (team_id != '' AND team_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
OR id IN (
|
||
SELECT task_list_id FROM task_list_shares
|
||
WHERE (target_type = 'user' AND target_id = ?)
|
||
OR (target_type = 'team' AND target_id IN (SELECT team_id FROM team_members WHERE user_id = ?))
|
||
)
|
||
)`,
|
||
listID, userID, userID, userID, userID,
|
||
).Scan(&tl.ID, &tl.UserID, &tl.SpaceID, &tl.TeamID, &tl.Title, &tl.Icon, &tl.Color, &colsJSON, &tl.CreatedAt, &tl.UpdatedAt)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
tl.IsOwner = (tl.UserID == userID)
|
||
_ = json.Unmarshal([]byte(colsJSON), &tl.Columns)
|
||
if len(tl.Columns) == 0 {
|
||
tl.Columns = []domain.KanbanColumn{
|
||
{ID: "todo", Name: "Zu erledigen"},
|
||
{ID: "in_progress", Name: "In Bearbeitung", Color: "accent"},
|
||
{ID: "waiting", Name: "Wartet auf andere", Color: "amber"},
|
||
{ID: "done", Name: "Erledigt", IsDone: true, Color: "emerald"},
|
||
}
|
||
}
|
||
_ = s.db.QueryRow(`SELECT COUNT(*) FROM thoughts WHERE task_list_id = ? AND is_completed = 0 AND (status = 'kept' OR (status = 'converted' AND converted_type = 'task'))`, tl.ID).Scan(&tl.TasksCount)
|
||
_ = s.db.QueryRow(`SELECT display_name FROM users WHERE id = ?`, tl.UserID).Scan(&tl.OwnerName)
|
||
if tl.TeamID != "" {
|
||
_ = s.db.QueryRow(`SELECT name FROM teams WHERE id = ?`, tl.TeamID).Scan(&tl.TeamName)
|
||
}
|
||
shares, _ := s.GetTaskListShares(tl.ID)
|
||
tl.Shares = shares
|
||
tl.Members = s.getMembersForTaskList(&tl)
|
||
return &tl, nil
|
||
}
|
||
|
||
func (s *Store) UpdateTaskList(userID, listID string, title, icon, color, teamID, spaceID *string, columns *[]domain.KanbanColumn) (*domain.TaskList, error) {
|
||
tl, err := s.GetTaskListByID(userID, listID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if title != nil {
|
||
tl.Title = strings.TrimSpace(*title)
|
||
}
|
||
if icon != nil && *icon != "" {
|
||
tl.Icon = *icon
|
||
}
|
||
if color != nil && *color != "" {
|
||
tl.Color = *color
|
||
}
|
||
if spaceID != nil {
|
||
tl.SpaceID = *spaceID
|
||
}
|
||
if columns != nil {
|
||
tl.Columns = *columns
|
||
}
|
||
if teamID != nil {
|
||
oldTeamID := tl.TeamID
|
||
tl.TeamID = *teamID
|
||
if oldTeamID != "" && oldTeamID != *teamID {
|
||
_ = s.RemoveTaskListShare(listID, oldTeamID)
|
||
}
|
||
if *teamID != "" {
|
||
_, _ = s.AddTaskListShare(listID, "team", *teamID, userID)
|
||
}
|
||
}
|
||
tl.UpdatedAt = domain.NowISO()
|
||
colsJSON, _ := json.Marshal(tl.Columns)
|
||
_, err = s.db.Exec(
|
||
`UPDATE task_lists SET title = ?, icon = ?, color = ?, space_id = ?, team_id = ?, columns = ?, updated_at = ? WHERE id = ?`,
|
||
tl.Title, tl.Icon, tl.Color, tl.SpaceID, tl.TeamID, string(colsJSON), tl.UpdatedAt, listID,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
return s.GetTaskListByID(userID, listID)
|
||
}
|
||
|
||
func (s *Store) DeleteTaskList(userID, listID string) error {
|
||
_, _ = s.db.Exec(`UPDATE thoughts SET task_list_id = '' WHERE task_list_id = ?`, listID)
|
||
_, _ = s.db.Exec(`DELETE FROM task_list_shares WHERE task_list_id = ?`, listID)
|
||
_, err := s.db.Exec(`DELETE FROM task_lists WHERE id = ? AND user_id = ?`, listID, userID)
|
||
return err
|
||
}
|
||
|
||
// --- ORGANIZATIONS & TEAMS ---
|
||
|
||
func (s *Store) CreateOrganization(ownerID, name string) (*domain.Organization, error) {
|
||
name = strings.TrimSpace(name)
|
||
if name == "" {
|
||
return nil, errors.New("organization name required")
|
||
}
|
||
org := &domain.Organization{
|
||
ID: domain.NewID("org"),
|
||
Name: name,
|
||
OwnerID: ownerID,
|
||
Teams: []domain.Team{},
|
||
CreatedAt: domain.NowISO(),
|
||
}
|
||
_, err := s.db.Exec(
|
||
`INSERT INTO organizations (id, name, owner_id, created_at) VALUES (?, ?, ?, ?)`,
|
||
org.ID, org.Name, org.OwnerID, org.CreatedAt,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
return org, nil
|
||
}
|
||
|
||
func (s *Store) DeleteOrganization(orgID string) error {
|
||
_, err := s.db.Exec(`DELETE FROM organizations WHERE id = ?`, orgID)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) GetOrganizationsForUser(userID string) ([]domain.Organization, error) {
|
||
viewer, _ := s.GetUserByID(userID)
|
||
var rows *sql.Rows
|
||
var err error
|
||
|
||
if viewer != nil && viewer.Role == "admin" {
|
||
rows, err = s.db.Query(
|
||
`SELECT id, name, owner_id, created_at FROM organizations ORDER BY created_at ASC`,
|
||
)
|
||
} else {
|
||
rows, err = s.db.Query(
|
||
`SELECT DISTINCT o.id, o.name, o.owner_id, o.created_at
|
||
FROM organizations o
|
||
LEFT JOIN teams t ON t.org_id = o.id
|
||
LEFT JOIN team_members tm ON tm.team_id = t.id
|
||
WHERE o.owner_id = ? OR tm.user_id = ?
|
||
ORDER BY o.created_at ASC`,
|
||
userID, userID,
|
||
)
|
||
}
|
||
if err != nil {
|
||
return []domain.Organization{}, err
|
||
}
|
||
|
||
orgs := make([]domain.Organization, 0)
|
||
for rows.Next() {
|
||
var o domain.Organization
|
||
if err := rows.Scan(&o.ID, &o.Name, &o.OwnerID, &o.CreatedAt); err == nil {
|
||
o.Teams = []domain.Team{}
|
||
orgs = append(orgs, o)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
|
||
for i := range orgs {
|
||
teams, _ := s.GetTeamsForOrg(orgs[i].ID)
|
||
orgs[i].Teams = teams
|
||
}
|
||
return orgs, nil
|
||
}
|
||
|
||
func (s *Store) CreateTeam(orgID, name, description string) (*domain.Team, error) {
|
||
name = strings.TrimSpace(name)
|
||
if name == "" {
|
||
return nil, errors.New("team name required")
|
||
}
|
||
orgID = strings.TrimSpace(orgID)
|
||
if orgID == "" {
|
||
orgID = "default"
|
||
}
|
||
|
||
var orgExists int
|
||
_ = s.db.QueryRow(`SELECT COUNT(*) FROM organizations WHERE id = ?`, orgID).Scan(&orgExists)
|
||
if orgExists == 0 {
|
||
var ownerID string
|
||
_ = s.db.QueryRow(`SELECT id FROM users ORDER BY created_at ASC LIMIT 1`).Scan(&ownerID)
|
||
if ownerID != "" {
|
||
_, _ = s.db.Exec(
|
||
`INSERT OR IGNORE INTO organizations (id, name, owner_id, created_at) VALUES (?, ?, ?, ?)`,
|
||
orgID, "Standard", ownerID, domain.NowISO(),
|
||
)
|
||
}
|
||
}
|
||
|
||
team := &domain.Team{
|
||
ID: domain.NewID("team"),
|
||
OrgID: orgID,
|
||
Name: name,
|
||
Description: strings.TrimSpace(description),
|
||
Members: []domain.TeamMember{},
|
||
CreatedAt: domain.NowISO(),
|
||
}
|
||
_, err := s.db.Exec(
|
||
`INSERT INTO teams (id, org_id, name, description, created_at) VALUES (?, ?, ?, ?, ?)`,
|
||
team.ID, team.OrgID, team.Name, team.Description, team.CreatedAt,
|
||
)
|
||
if err != nil && strings.Contains(strings.ToUpper(err.Error()), "FOREIGN KEY") {
|
||
if conn, connErr := s.db.Conn(context.Background()); connErr == nil {
|
||
_, _ = conn.ExecContext(context.Background(), `PRAGMA foreign_keys = OFF;`)
|
||
_, err = conn.ExecContext(
|
||
context.Background(),
|
||
`INSERT INTO teams (id, org_id, name, description, created_at) VALUES (?, ?, ?, ?, ?)`,
|
||
team.ID, team.OrgID, team.Name, team.Description, team.CreatedAt,
|
||
)
|
||
_, _ = conn.ExecContext(context.Background(), `PRAGMA foreign_keys = ON;`)
|
||
_ = conn.Close()
|
||
}
|
||
}
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
return team, nil
|
||
}
|
||
|
||
func (s *Store) DeleteTeam(teamID string) error {
|
||
_, err := s.db.Exec(`DELETE FROM teams WHERE id = ?`, teamID)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) GetAllTeams() ([]domain.Team, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT id, org_id, name, description, created_at FROM teams ORDER BY name COLLATE NOCASE ASC`,
|
||
)
|
||
if err != nil {
|
||
return []domain.Team{}, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
teams := make([]domain.Team, 0)
|
||
for rows.Next() {
|
||
var t domain.Team
|
||
if err := rows.Scan(&t.ID, &t.OrgID, &t.Name, &t.Description, &t.CreatedAt); err == nil {
|
||
t.Members = []domain.TeamMember{}
|
||
teams = append(teams, t)
|
||
}
|
||
}
|
||
for i := range teams {
|
||
members, _ := s.GetTeamMembers(teams[i].ID)
|
||
teams[i].Members = members
|
||
}
|
||
return teams, nil
|
||
}
|
||
|
||
func (s *Store) GetTeamsForOrg(orgID string) ([]domain.Team, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT id, org_id, name, description, created_at FROM teams WHERE org_id = ? ORDER BY created_at ASC`,
|
||
orgID,
|
||
)
|
||
if err != nil {
|
||
return []domain.Team{}, err
|
||
}
|
||
|
||
teams := make([]domain.Team, 0)
|
||
for rows.Next() {
|
||
var t domain.Team
|
||
if err := rows.Scan(&t.ID, &t.OrgID, &t.Name, &t.Description, &t.CreatedAt); err == nil {
|
||
t.Members = []domain.TeamMember{}
|
||
teams = append(teams, t)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
|
||
for i := range teams {
|
||
members, _ := s.GetTeamMembers(teams[i].ID)
|
||
teams[i].Members = members
|
||
}
|
||
return teams, nil
|
||
}
|
||
|
||
func (s *Store) GetTeamByID(teamID string) (*domain.Team, error) {
|
||
var t domain.Team
|
||
err := s.db.QueryRow(
|
||
`SELECT id, org_id, name, description, created_at FROM teams WHERE id = ?`,
|
||
teamID,
|
||
).Scan(&t.ID, &t.OrgID, &t.Name, &t.Description, &t.CreatedAt)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
t.Members, _ = s.GetTeamMembers(t.ID)
|
||
return &t, nil
|
||
}
|
||
|
||
func (s *Store) GetTeamsForUser(userID string) ([]domain.Team, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT DISTINCT t.id, t.org_id, t.name, t.description, t.created_at
|
||
FROM teams t
|
||
JOIN team_members tm ON tm.team_id = t.id
|
||
WHERE tm.user_id = ?
|
||
ORDER BY t.created_at ASC`,
|
||
userID,
|
||
)
|
||
if err != nil {
|
||
return []domain.Team{}, err
|
||
}
|
||
|
||
teams := make([]domain.Team, 0)
|
||
for rows.Next() {
|
||
var t domain.Team
|
||
if err := rows.Scan(&t.ID, &t.OrgID, &t.Name, &t.Description, &t.CreatedAt); err == nil {
|
||
t.Members = []domain.TeamMember{}
|
||
teams = append(teams, t)
|
||
}
|
||
}
|
||
_ = rows.Close()
|
||
|
||
for i := range teams {
|
||
members, _ := s.GetTeamMembers(teams[i].ID)
|
||
teams[i].Members = members
|
||
}
|
||
return teams, nil
|
||
}
|
||
|
||
func (s *Store) UpdateTeam(teamID, name, description string) (*domain.Team, error) {
|
||
name = strings.TrimSpace(name)
|
||
if name == "" {
|
||
return nil, errors.New("Name für die Gruppe ist erforderlich")
|
||
}
|
||
_, err := s.db.Exec(`UPDATE teams SET name = ?, description = ? WHERE id = ?`, name, strings.TrimSpace(description), teamID)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
return s.GetTeamByID(teamID)
|
||
}
|
||
|
||
func (s *Store) GetTeamMemberRole(teamID, userID string) (string, error) {
|
||
var role string
|
||
err := s.db.QueryRow(`SELECT role FROM team_members WHERE team_id = ? AND user_id = ?`, teamID, userID).Scan(&role)
|
||
if err != nil {
|
||
if errors.Is(err, sql.ErrNoRows) {
|
||
return "", nil
|
||
}
|
||
return "", err
|
||
}
|
||
return role, nil
|
||
}
|
||
|
||
func (s *Store) CountTeamAdmins(teamID string) (int, error) {
|
||
var count int
|
||
err := s.db.QueryRow(`SELECT COUNT(*) FROM team_members WHERE team_id = ? AND role = 'admin'`, teamID).Scan(&count)
|
||
return count, err
|
||
}
|
||
|
||
func (s *Store) AddTeamMember(teamID, userID, role string) error {
|
||
role = strings.ToLower(strings.TrimSpace(role))
|
||
if role != "admin" && role != "member" {
|
||
role = "member"
|
||
}
|
||
_, err := s.db.Exec(
|
||
`INSERT INTO team_members (team_id, user_id, role, joined_at) VALUES (?, ?, ?, ?)
|
||
ON CONFLICT(team_id, user_id) DO UPDATE SET role = excluded.role`,
|
||
teamID, userID, role, domain.NowISO(),
|
||
)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) UpdateTeamMemberRole(teamID, userID, role string) error {
|
||
role = strings.ToLower(strings.TrimSpace(role))
|
||
if role != "admin" && role != "member" {
|
||
return errors.New("Ungültige Rolle (erlaubt sind: admin, member)")
|
||
}
|
||
currentRole, err := s.GetTeamMemberRole(teamID, userID)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if currentRole == "" {
|
||
return errors.New("Benutzer ist kein Mitglied dieser Gruppe")
|
||
}
|
||
if currentRole == "admin" && role != "admin" {
|
||
adminCount, err := s.CountTeamAdmins(teamID)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if adminCount <= 1 {
|
||
return errors.New("Die Gruppe muss mindestens einen Gruppen-Admin behalten")
|
||
}
|
||
}
|
||
_, err = s.db.Exec(`UPDATE team_members SET role = ? WHERE team_id = ? AND user_id = ?`, role, teamID, userID)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) RemoveTeamMember(teamID, userID string) error {
|
||
currentRole, err := s.GetTeamMemberRole(teamID, userID)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if currentRole == "admin" {
|
||
adminCount, err := s.CountTeamAdmins(teamID)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if adminCount <= 1 {
|
||
return errors.New("Du bist der letzte Gruppen-Admin. Bitte ernenne zuerst einen anderen Admin oder lösche die Gruppe.")
|
||
}
|
||
}
|
||
_, err = s.db.Exec(`DELETE FROM team_members WHERE team_id = ? AND user_id = ?`, teamID, userID)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) GetTeamMembers(teamID string) ([]domain.TeamMember, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT tm.team_id, tm.user_id, u.display_name, u.email, u.avatar_url, tm.role, tm.joined_at
|
||
FROM team_members tm
|
||
JOIN users u ON u.id = tm.user_id
|
||
WHERE tm.team_id = ?
|
||
ORDER BY tm.joined_at ASC`,
|
||
teamID,
|
||
)
|
||
if err != nil {
|
||
return []domain.TeamMember{}, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
members := make([]domain.TeamMember, 0)
|
||
for rows.Next() {
|
||
var m domain.TeamMember
|
||
if err := rows.Scan(&m.TeamID, &m.UserID, &m.DisplayName, &m.Email, &m.AvatarURL, &m.Role, &m.JoinedAt); err == nil {
|
||
members = append(members, m)
|
||
}
|
||
}
|
||
return members, nil
|
||
}
|
||
|
||
// --- USER INVITES ---
|
||
|
||
func (s *Store) CreateUserInvite(createdBy, email, role, teamID string, validDays int) (*domain.UserInvite, error) {
|
||
if role != "admin" && role != "member" {
|
||
role = "member"
|
||
}
|
||
if validDays <= 0 || validDays > 365 {
|
||
validDays = 7
|
||
}
|
||
code := "INV-" + strings.ToUpper(domain.NewID("c")[2:])
|
||
now := time.Now().UTC()
|
||
inv := &domain.UserInvite{
|
||
ID: domain.NewID("inv"),
|
||
Code: code,
|
||
Email: strings.ToLower(strings.TrimSpace(email)),
|
||
Role: role,
|
||
TeamID: strings.TrimSpace(teamID),
|
||
CreatedBy: createdBy,
|
||
ExpiresAt: now.AddDate(0, 0, validDays).Format(time.RFC3339),
|
||
CreatedAt: now.Format(time.RFC3339),
|
||
}
|
||
_, err := s.db.Exec(
|
||
`INSERT INTO user_invites (id, code, email, role, team_id, created_by, expires_at, used_by, created_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, '', ?)`,
|
||
inv.ID, inv.Code, inv.Email, inv.Role, inv.TeamID, inv.CreatedBy, inv.ExpiresAt, inv.CreatedAt,
|
||
)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
return inv, nil
|
||
}
|
||
|
||
func (s *Store) GetUserInvites() ([]domain.UserInvite, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT i.id, i.code, i.email, i.role, i.team_id, COALESCE(t.name, ''), i.created_by, i.expires_at, i.used_by, i.created_at
|
||
FROM user_invites i
|
||
LEFT JOIN teams t ON t.id = i.team_id
|
||
ORDER BY i.created_at DESC`,
|
||
)
|
||
if err != nil {
|
||
return []domain.UserInvite{}, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
invites := make([]domain.UserInvite, 0)
|
||
for rows.Next() {
|
||
var inv domain.UserInvite
|
||
if err := rows.Scan(&inv.ID, &inv.Code, &inv.Email, &inv.Role, &inv.TeamID, &inv.TeamName, &inv.CreatedBy, &inv.ExpiresAt, &inv.UsedBy, &inv.CreatedAt); err == nil {
|
||
invites = append(invites, inv)
|
||
}
|
||
}
|
||
return invites, nil
|
||
}
|
||
|
||
func (s *Store) GetUserInviteByCode(code string) (*domain.UserInvite, error) {
|
||
code = strings.ToUpper(strings.TrimSpace(code))
|
||
var inv domain.UserInvite
|
||
err := s.db.QueryRow(
|
||
`SELECT id, code, email, role, team_id, created_by, expires_at, used_by, created_at
|
||
FROM user_invites WHERE UPPER(code) = ?`,
|
||
code,
|
||
).Scan(&inv.ID, &inv.Code, &inv.Email, &inv.Role, &inv.TeamID, &inv.CreatedBy, &inv.ExpiresAt, &inv.UsedBy, &inv.CreatedAt)
|
||
if err != nil {
|
||
return nil, errors.New("Ungültiger Einladungscode")
|
||
}
|
||
if inv.UsedBy != "" {
|
||
return nil, errors.New("Dieser Einladungscode wurde bereits eingelöst")
|
||
}
|
||
if exp, err := time.Parse(time.RFC3339, inv.ExpiresAt); err == nil && time.Now().UTC().After(exp) {
|
||
return nil, errors.New("Dieser Einladungscode ist abgelaufen")
|
||
}
|
||
return &inv, nil
|
||
}
|
||
|
||
func (s *Store) MarkUserInviteUsed(inviteID, usedByUserID string) error {
|
||
_, err := s.db.Exec(`UPDATE user_invites SET used_by = ? WHERE id = ?`, usedByUserID, inviteID)
|
||
return err
|
||
}
|
||
|
||
func (s *Store) DeleteUserInvite(inviteID string) error {
|
||
_, err := s.db.Exec(`DELETE FROM user_invites WHERE id = ?`, inviteID)
|
||
return err
|
||
}
|
||
|
||
// --- SYSTEM SETTINGS ---
|
||
|
||
func (s *Store) SeedSMTPDefaultsFromEnv(host, port, user, pass, from string, tls bool) {
|
||
cur, err := s.GetSystemSettings()
|
||
if err != nil || cur == nil {
|
||
return
|
||
}
|
||
// Only populate DB from env if DB has no SMTP host configured yet and env provides one
|
||
if cur.SMTPHost == "" && strings.TrimSpace(host) != "" {
|
||
cur.SMTPHost = strings.TrimSpace(host)
|
||
if strings.TrimSpace(port) != "" {
|
||
cur.SMTPPort = strings.TrimSpace(port)
|
||
}
|
||
cur.SMTPUser = strings.TrimSpace(user)
|
||
cur.SMTPPass = pass
|
||
cur.SMTPFrom = strings.TrimSpace(from)
|
||
cur.SMTPTLS = tls
|
||
_ = s.UpdateSystemSettings(cur)
|
||
}
|
||
}
|
||
|
||
func (s *Store) GetSystemSettings() (*domain.SystemSettings, error) {
|
||
settings := &domain.SystemSettings{
|
||
SharingMode: "teams_only",
|
||
AllowRegistration: false,
|
||
InstanceName: "MindSift",
|
||
SMTPPort: "587",
|
||
SMTPTLS: true,
|
||
}
|
||
|
||
rows, err := s.db.Query(`SELECT key, value FROM system_settings`)
|
||
if err != nil {
|
||
return settings, nil
|
||
}
|
||
defer rows.Close()
|
||
|
||
for rows.Next() {
|
||
var k, v string
|
||
if err := rows.Scan(&k, &v); err == nil {
|
||
switch k {
|
||
case "sharing_mode":
|
||
settings.SharingMode = v
|
||
case "allow_registration":
|
||
settings.AllowRegistration = (v == "true" || v == "1")
|
||
case "instance_name":
|
||
settings.InstanceName = v
|
||
case "smtp_host":
|
||
settings.SMTPHost = v
|
||
case "smtp_port":
|
||
if v != "" {
|
||
settings.SMTPPort = v
|
||
}
|
||
case "smtp_user":
|
||
settings.SMTPUser = v
|
||
case "smtp_pass":
|
||
settings.SMTPPass = v
|
||
case "smtp_from":
|
||
settings.SMTPFrom = v
|
||
case "smtp_tls":
|
||
settings.SMTPTLS = (v != "false" && v != "0")
|
||
}
|
||
}
|
||
}
|
||
return settings, nil
|
||
}
|
||
|
||
func (s *Store) UpdateSystemSettings(settings *domain.SystemSettings) error {
|
||
regStr := "false"
|
||
if settings.AllowRegistration {
|
||
regStr = "true"
|
||
}
|
||
tlsStr := "false"
|
||
if settings.SMTPTLS {
|
||
tlsStr = "true"
|
||
}
|
||
if settings.SMTPPort == "" {
|
||
settings.SMTPPort = "587"
|
||
}
|
||
if settings.SharingMode == "" {
|
||
settings.SharingMode = "teams_only"
|
||
}
|
||
if settings.InstanceName == "" {
|
||
settings.InstanceName = "MindSift"
|
||
}
|
||
_, err := s.db.Exec(`INSERT INTO system_settings (key, value) VALUES
|
||
('sharing_mode', ?),
|
||
('allow_registration', ?),
|
||
('instance_name', ?),
|
||
('smtp_host', ?),
|
||
('smtp_port', ?),
|
||
('smtp_user', ?),
|
||
('smtp_pass', ?),
|
||
('smtp_from', ?),
|
||
('smtp_tls', ?)
|
||
ON CONFLICT(key) DO UPDATE SET value = excluded.value`,
|
||
settings.SharingMode,
|
||
regStr,
|
||
settings.InstanceName,
|
||
strings.TrimSpace(settings.SMTPHost),
|
||
strings.TrimSpace(settings.SMTPPort),
|
||
strings.TrimSpace(settings.SMTPUser),
|
||
settings.SMTPPass,
|
||
strings.TrimSpace(settings.SMTPFrom),
|
||
tlsStr,
|
||
)
|
||
return err
|
||
}
|
||
|
||
// --- ALARMS ---
|
||
|
||
type DueTaskAlarm struct {
|
||
TaskID string
|
||
UserID string
|
||
Title string
|
||
Notes string
|
||
DueDate string
|
||
Priority string
|
||
UserEmail string
|
||
AlarmChannels []string
|
||
AlarmEmail string
|
||
DiscordWebhookURL string
|
||
CustomWebhookURL string
|
||
NotifyBeforeMinutes int
|
||
}
|
||
|
||
func (s *Store) GetDueTasksForAlarm() ([]DueTaskAlarm, error) {
|
||
rows, err := s.db.Query(
|
||
`SELECT t.id, t.user_id, t.content, t.notes, t.due_date, t.priority,
|
||
u.email, u.alarm_channels, u.alarm_email, u.discord_webhook_url, u.custom_webhook_url, u.notify_before_minutes
|
||
FROM thoughts t
|
||
JOIN users u ON u.id = t.user_id
|
||
WHERE (t.status = 'kept' OR (t.status = 'converted' AND t.converted_type = 'task'))
|
||
AND t.is_completed = 0
|
||
AND t.alarm_sent = 0
|
||
AND t.due_date != ''
|
||
AND u.is_active = 1`,
|
||
)
|
||
if err != nil {
|
||
return []DueTaskAlarm{}, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
alarms := make([]DueTaskAlarm, 0)
|
||
for rows.Next() {
|
||
var a DueTaskAlarm
|
||
var channelsRaw string
|
||
if err := rows.Scan(
|
||
&a.TaskID, &a.UserID, &a.Title, &a.Notes, &a.DueDate, &a.Priority,
|
||
&a.UserEmail, &channelsRaw, &a.AlarmEmail, &a.DiscordWebhookURL, &a.CustomWebhookURL, &a.NotifyBeforeMinutes,
|
||
); err == nil {
|
||
a.AlarmChannels = parseAlarmChannels(channelsRaw)
|
||
if a.AlarmEmail == "" {
|
||
a.AlarmEmail = a.UserEmail
|
||
}
|
||
alarms = append(alarms, a)
|
||
}
|
||
}
|
||
return alarms, nil
|
||
}
|
||
|
||
func (s *Store) MarkTaskAlarmSent(taskID string) error {
|
||
_, err := s.db.Exec(`UPDATE thoughts SET alarm_sent = 1 WHERE id = ?`, taskID)
|
||
return err
|
||
}
|
||
|