Function findOrphanedNotes

pkg/cli/cmd/sync/sync.go:821–863

findOrphanedNotes returns a list of all orphaned notes

(db *database.DB)

// findOrphanedNotes returns a list of all orphaned notes
func findOrphanedNotes(db *database.DB) (int, []struct{ noteUUID, bookUUID string }, error) {
	var orphanCount int
	err := db.QueryRow(`
		SELECT COUNT(*) FROM notes n
		WHERE NOT EXISTS (
			SELECT 1 FROM books b
			WHERE b.uuid = n.book_uuid
			AND NOT b.deleted
		)
	`).Scan(&orphanCount)
	if err != nil {
		return 0, nil, err
	}

	if orphanCount == 0 {
		return 0, nil, nil
	}

	rows, err := db.Query(`
		SELECT n.uuid, n.book_uuid
		FROM notes n
		WHERE NOT EXISTS (
			SELECT 1 FROM books b
			WHERE b.uuid = n.book_uuid
			AND NOT b.deleted
		)
	`)
	if err != nil {
		return orphanCount, nil, err
	}
	defer rows.Close()

	var orphans []struct{ noteUUID, bookUUID string }
	for rows.Next() {
		var noteUUID, bookUUID string
		if err := rows.Scan(&noteUUID, &bookUUID); err != nil {
			continue
		}
		orphans = append(orphans, struct{ noteUUID, bookUUID string }{noteUUID, bookUUID})
	}

	return orphanCount, orphans, nil
}

func warnOrphanedNotes(tx *database.DB) {
	count, orphans, err := findOrphanedNotes(tx)