bookhoard/internal/sync/annotations.go

package sync

import (
	"bookhoard/internal/database"
	"context"
	"crypto/sha1"
	"encoding/hex"
	"encoding/json"
	"errors"
	"fmt"
	"log"
	"math"
	"strings"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgtype"
)

const TombstoneTTL = 30 * 24 * time.Hour

type SaveOutcome string

const (
	SaveOutcomeCreated SaveOutcome = "created"
	SaveOutcomeUpdated SaveOutcome = "updated"
	SaveOutcomeSkipped SaveOutcome = "skipped"
	SaveOutcomeDeleted SaveOutcome = "deleted"
)

type AnnotationService struct {
	db      *database.Queries
	connMgr *ConnectionManager
}

func NewAnnotationService(db *database.Queries, connMgr *ConnectionManager) *AnnotationService {
	return &AnnotationService{db: db, connMgr: connMgr}
}

type SaveHighlightRequest struct {
	MediaItemID      pgtype.UUID
	UserID           pgtype.UUID
	SelectionText    string
	StartPosition    string
	EndPosition      string
	Color            string
	NoteText         string
	PercentageStart  float64
	PercentageEnd    float64
	EpubcfiStart     string
	EpubcfiEnd       string
	ChapterReference int32
	Source           string
	ModifiedAt       time.Time
	DeviceSyncData   json.RawMessage
}

type SaveHighlightResult struct {
	Highlight database.MediaHighlights
	Outcome   SaveOutcome
	Conflict  bool
}

func (s *AnnotationService) SaveHighlight(ctx context.Context, req SaveHighlightRequest) (*SaveHighlightResult, error) {
	dedupKey := ComputeDedupKey(req.SelectionText, req.EpubcfiStart, req.StartPosition)

	existing, err := s.db.GetMediaHighlightByDedupKey(ctx, database.GetMediaHighlightByDedupKeyParams{
		UserID:      req.UserID,
		MediaItemID: req.MediaItemID,
		DedupKey:    pgtype.Text{String: dedupKey, Valid: true},
	})
	if err != nil && !errors.Is(err, pgx.ErrNoRows) {
		return nil, fmt.Errorf("query existing highlight: %w", err)
	}

	if errors.Is(err, pgx.ErrNoRows) {
		return s.createHighlight(ctx, req, dedupKey)
	}

	if existing.Deleted.Bool {
		if existing.DeletedAt.Valid && time.Since(existing.DeletedAt.Time) < TombstoneTTL {
			return &SaveHighlightResult{Highlight: existing, Outcome: SaveOutcomeDeleted}, nil
		}
		return s.createHighlight(ctx, req, dedupKey)
	}

	return s.applyLWW(ctx, req, existing, dedupKey)
}

func (s *AnnotationService) createHighlight(
	ctx context.Context,
	req SaveHighlightRequest,
	dedupKey string,
) (*SaveHighlightResult, error) {
	modifiedAt := req.ModifiedAt
	if modifiedAt.IsZero() {
		modifiedAt = time.Now()
	}

	deviceData := mergeDeviceSyncData(nil, req.Source, req.DeviceSyncData)

	highlight, err := s.db.CreateMediaHighlightFull(ctx, database.CreateMediaHighlightFullParams{
		MediaItemID:      req.MediaItemID,
		UserID:           req.UserID,
		SelectionText:    req.SelectionText,
		StartPosition:    pgText(req.StartPosition),
		EndPosition:      pgText(req.EndPosition),
		Color:            pgText(req.Color),
		NoteText:         pgText(req.NoteText),
		PercentageStart:  pgFloat8(req.PercentageStart),
		PercentageEnd:    pgFloat8(req.PercentageEnd),
		EpubcfiStart:     pgText(req.EpubcfiStart),
		EpubcfiEnd:       pgText(req.EpubcfiEnd),
		ChapterReference: pgInt4(req.ChapterReference),
		DedupKey:         pgtype.Text{String: dedupKey, Valid: true},
		LastModifiedAt:   pgtype.Timestamptz{Time: modifiedAt, Valid: true},
		LastModifiedSource: pgtype.Text{String: req.Source, Valid: req.Source != ""},
		DeviceSyncData:   deviceData,
	})
	if err != nil {
		return nil, fmt.Errorf("create highlight: %w", err)
	}

	s.broadcast(highlight.ID, req.UserID, req.MediaItemID, "highlight", req.Source)
	return &SaveHighlightResult{Highlight: highlight, Outcome: SaveOutcomeCreated}, nil
}

func (s *AnnotationService) applyLWW(
	ctx context.Context,
	req SaveHighlightRequest,
	existing database.MediaHighlights,
	dedupKey string,
) (*SaveHighlightResult, error) {
	incomingNewer, contentChanged := s.compareIncoming(req, existing)

	if !incomingNewer && !contentChanged {
		conflict := isCrossSource(req.Source, existing.LastModifiedSource)
		if conflict {
			s.recordConflict(ctx, req.UserID, req.MediaItemID, "annotation_highlight",
				existing.DedupKey.String, req.Source, existing.LastModifiedSource.String,
				req, existing, "existing")
		}
		return &SaveHighlightResult{
			Highlight: existing,
			Outcome:   SaveOutcomeSkipped,
			Conflict:  conflict,
		}, nil
	}

	modifiedAt := req.ModifiedAt
	if modifiedAt.IsZero() {
		modifiedAt = time.Now()
	}

	deviceData := mergeDeviceSyncData(existing.DeviceSyncData, req.Source, req.DeviceSyncData)

	highlight, err := s.db.UpdateMediaHighlightForSync(ctx, database.UpdateMediaHighlightForSyncParams{
		ID:               existing.ID,
		SelectionText:    req.SelectionText,
		StartPosition:    pgText(req.StartPosition),
		EndPosition:      pgText(req.EndPosition),
		Color:            pgText(req.Color),
		NoteText:         pgText(req.NoteText),
		PercentageStart:  pgFloat8(req.PercentageStart),
		PercentageEnd:    pgFloat8(req.PercentageEnd),
		EpubcfiStart:     pgText(req.EpubcfiStart),
		EpubcfiEnd:       pgText(req.EpubcfiEnd),
		ChapterReference: pgInt4(req.ChapterReference),
		LastModifiedAt:   pgtype.Timestamptz{Time: modifiedAt, Valid: true},
		LastModifiedSource: pgtype.Text{String: req.Source, Valid: req.Source != ""},
		DeviceSyncData:   deviceData,
	})
	if err != nil {
		return nil, fmt.Errorf("update highlight: %w", err)
	}

	conflict := isCrossSource(req.Source, existing.LastModifiedSource)
	if conflict {
		s.recordConflict(ctx, req.UserID, req.MediaItemID, "annotation_highlight",
			existing.DedupKey.String, req.Source, existing.LastModifiedSource.String,
			req, existing, "incoming")
	}
	s.broadcast(highlight.ID, req.UserID, req.MediaItemID, "highlight", req.Source)
	return &SaveHighlightResult{Highlight: highlight, Outcome: SaveOutcomeUpdated, Conflict: conflict}, nil
}

func (s *AnnotationService) compareIncoming(req SaveHighlightRequest, existing database.MediaHighlights) (incomingNewer bool, contentChanged bool) {
	if req.ModifiedAt.IsZero() {
		contentSame := strings.EqualFold(req.SelectionText, existing.SelectionText) &&
			textEq(req.Color, existing.Color) &&
			textEq(req.NoteText, existing.NoteText) &&
			floatEq(req.PercentageStart, existing.PercentageStart) &&
			floatEq(req.PercentageEnd, existing.PercentageEnd)
		return !contentSame, !contentSame
	}

	existingMod := existing.LastModifiedAt
	if !existingMod.Valid {
		existingMod = existing.UpdatedAt
	}
	return req.ModifiedAt.After(existingMod.Time), true
}

func (s *AnnotationService) TombstoneHighlight(
	ctx context.Context,
	userID, mediaItemID pgtype.UUID,
	dedupKey string,
	source string,
) error {
	err := s.db.TombstoneMediaHighlightByDedupKey(ctx, database.TombstoneMediaHighlightByDedupKeyParams{
		UserID:      userID,
		MediaItemID: mediaItemID,
		DedupKey:    pgtype.Text{String: dedupKey, Valid: true},
	})
	if err != nil {
		return fmt.Errorf("tombstone highlight: %w", err)
	}
	s.broadcast(pgtype.UUID{}, userID, mediaItemID, "highlight_delete", source)
	return nil
}

func (s *AnnotationService) TombstoneHighlightByID(
	ctx context.Context,
	highlightID pgtype.UUID,
	source string,
) error {
	h, err := s.db.GetMediaHighlight(ctx, highlightID)
	if err != nil {
		return fmt.Errorf("get highlight for tombstone: %w", err)
	}
	err = s.db.TombstoneMediaHighlightByID(ctx, highlightID)
	if err != nil {
		return fmt.Errorf("tombstone highlight by ID: %w", err)
	}
	s.broadcast(pgtype.UUID{}, h.UserID, h.MediaItemID, "highlight_delete", source)
	return nil
}

func (s *AnnotationService) PurgeExpiredTombstones(ctx context.Context) error {
	cutoff := pgtype.Timestamptz{Time: time.Now().Add(-TombstoneTTL), Valid: true}
	if err := s.db.PurgeExpiredHighlightTombstones(ctx, cutoff); err != nil {
		return fmt.Errorf("purge highlight tombstones: %w", err)
	}
	if err := s.db.PurgeExpiredNoteTombstones(ctx, cutoff); err != nil {
		return fmt.Errorf("purge note tombstones: %w", err)
	}
	if err := s.db.PurgeExpiredBookmarkTombstones(ctx, cutoff); err != nil {
		return fmt.Errorf("purge bookmark tombstones: %w", err)
	}
	return nil
}

func (s *AnnotationService) StartTombstonePurger() context.CancelFunc {
	ticker := time.NewTicker(24 * time.Hour)
	ctx, cancel := context.WithCancel(context.Background())

	go func() {
		for {
			select {
			case <-ctx.Done():
				ticker.Stop()
				return
			case <-ticker.C:
				if err := s.PurgeExpiredTombstones(ctx); err != nil {
					log.Printf("AnnotationService: tombstone purge failed: %v", err)
				}
			}
		}
	}()

	return cancel
}

type SaveNoteRequest struct {
	MediaItemID        pgtype.UUID
	UserID             pgtype.UUID
	Content            string
	Position           string
	PercentageLocation float64
	CharacterStart     int32
	CharacterEnd       int32
	EpubcfiLocation    string
	ChapterReference   int32
	ParagraphReference int32
	Source             string
	ModifiedAt         time.Time
	DeviceSyncData     []byte
}

type SaveNoteResult struct {
	Note     database.MediaNotes
	Outcome  SaveOutcome
	Conflict bool
}

func (s *AnnotationService) SaveNote(ctx context.Context, req SaveNoteRequest) (*SaveNoteResult, error) {
	if !req.UserID.Valid || !req.MediaItemID.Valid {
		return nil, errors.New("invalid user_id or media_item_id")
	}

	dedupKey := ComputeDedupKey(req.Content, req.EpubcfiLocation, req.Position)

	existing, err := s.db.GetMediaNoteByDedupKey(ctx, database.GetMediaNoteByDedupKeyParams{
		UserID:      req.UserID,
		MediaItemID: req.MediaItemID,
		DedupKey:    pgtype.Text{String: dedupKey, Valid: true},
	})
	if err != nil {
		if !errors.Is(err, pgx.ErrNoRows) {
			return nil, fmt.Errorf("get note by dedup key: %w", err)
		}
		return s.createNote(ctx, req, dedupKey)
	}

	if existing.Deleted.Valid && existing.Deleted.Bool {
		return &SaveNoteResult{Note: existing, Outcome: SaveOutcomeDeleted}, nil
	}

	return s.applyNoteLWW(ctx, req, existing, dedupKey)
}

func (s *AnnotationService) createNote(ctx context.Context, req SaveNoteRequest, dedupKey string) (*SaveNoteResult, error) {
	modifiedAt := req.ModifiedAt
	if modifiedAt.IsZero() {
		modifiedAt = time.Now()
	}

	note, err := s.db.CreateMediaNoteFull(ctx, database.CreateMediaNoteFullParams{
		MediaItemID:        req.MediaItemID,
		UserID:             req.UserID,
		Content:            req.Content,
		Position:           pgText(req.Position),
		PercentageLocation: pgFloat8(req.PercentageLocation),
		CharacterStart:     pgInt4(req.CharacterStart),
		CharacterEnd:       pgInt4(req.CharacterEnd),
		EpubcfiLocation:    pgText(req.EpubcfiLocation),
		ChapterReference:   pgInt4(req.ChapterReference),
		ParagraphReference: pgInt4(req.ParagraphReference),
		DedupKey:           pgtype.Text{String: dedupKey, Valid: true},
		LastModifiedAt:     pgtype.Timestamptz{Time: modifiedAt, Valid: true},
		LastModifiedSource: pgtype.Text{String: req.Source, Valid: req.Source != ""},
		DeviceSyncData:     req.DeviceSyncData,
	})
	if err != nil {
		return nil, fmt.Errorf("create note: %w", err)
	}
	s.broadcast(note.ID, req.UserID, req.MediaItemID, "note", req.Source)
	return &SaveNoteResult{Note: note, Outcome: SaveOutcomeCreated}, nil
}

func (s *AnnotationService) applyNoteLWW(ctx context.Context, req SaveNoteRequest, existing database.MediaNotes, dedupKey string) (*SaveNoteResult, error) {
	incomingNewer, contentChanged := s.compareIncomingNote(req, existing)

	if !incomingNewer && !contentChanged {
		conflict := isCrossSource(req.Source, existing.LastModifiedSource)
		if conflict {
			s.recordConflict(ctx, req.UserID, req.MediaItemID, "annotation_note",
				existing.DedupKey.String, req.Source, existing.LastModifiedSource.String,
				req, existing, "existing")
		}
		return &SaveNoteResult{Note: existing, Outcome: SaveOutcomeSkipped, Conflict: conflict}, nil
	}

	modifiedAt := req.ModifiedAt
	if modifiedAt.IsZero() {
		modifiedAt = time.Now()
	}

	deviceData := mergeDeviceSyncData(existing.DeviceSyncData, req.Source, req.DeviceSyncData)

	note, err := s.db.UpdateMediaNoteForSync(ctx, database.UpdateMediaNoteForSyncParams{
		ID:                 existing.ID,
		Content:            req.Content,
		Position:           pgText(req.Position),
		PercentageLocation: pgFloat8(req.PercentageLocation),
		CharacterStart:     pgInt4(req.CharacterStart),
		CharacterEnd:       pgInt4(req.CharacterEnd),
		EpubcfiLocation:    pgText(req.EpubcfiLocation),
		ChapterReference:   pgInt4(req.ChapterReference),
		ParagraphReference: pgInt4(req.ParagraphReference),
		LastModifiedAt:     pgtype.Timestamptz{Time: modifiedAt, Valid: true},
		LastModifiedSource: pgtype.Text{String: req.Source, Valid: req.Source != ""},
		DeviceSyncData:     deviceData,
	})
	if err != nil {
		return nil, fmt.Errorf("update note: %w", err)
	}

	conflict := isCrossSource(req.Source, existing.LastModifiedSource)
	if conflict {
		s.recordConflict(ctx, req.UserID, req.MediaItemID, "annotation_note",
			existing.DedupKey.String, req.Source, existing.LastModifiedSource.String,
			req, existing, "incoming")
	}
	s.broadcast(note.ID, req.UserID, req.MediaItemID, "note", req.Source)
	return &SaveNoteResult{Note: note, Outcome: SaveOutcomeUpdated, Conflict: conflict}, nil
}

func (s *AnnotationService) compareIncomingNote(req SaveNoteRequest, existing database.MediaNotes) (incomingNewer bool, contentChanged bool) {
	if req.ModifiedAt.IsZero() {
		contentSame := strings.EqualFold(req.Content, existing.Content) &&
			textEq(req.Position, existing.Position)
		return !contentSame, !contentSame
	}
	existingMod := existing.LastModifiedAt
	if !existingMod.Valid {
		existingMod = existing.UpdatedAt
	}
	if !existingMod.Valid {
		return true, true
	}
	return req.ModifiedAt.After(existingMod.Time), true
}

func (s *AnnotationService) TombstoneNoteByID(ctx context.Context, id pgtype.UUID) error {
	return s.db.TombstoneMediaNoteByID(ctx, id)
}

type SaveBookmarkRequest struct {
	MediaItemID      pgtype.UUID
	UserID           pgtype.UUID
	Title            string
	Position         string
	Notes            string
	PageNumber       int32
	ChapterNumber    int32
	CFIPosition      string
	PercentageLoc    float64
	EpubcfiLocation  string
	ChapterReference int32
	Source           string
	ModifiedAt       time.Time
	DeviceSyncData   json.RawMessage
}

type SaveBookmarkResult struct {
	Bookmark database.MediaBookmarks
	Outcome  SaveOutcome
	Conflict bool
}

func (s *AnnotationService) SaveBookmark(ctx context.Context, req SaveBookmarkRequest) (*SaveBookmarkResult, error) {
	dedupKey := ComputeDedupKey(req.Title, req.EpubcfiLocation, req.Position)

	existing, err := s.db.GetMediaBookmarkByDedupKey(ctx, database.GetMediaBookmarkByDedupKeyParams{
		UserID:      req.UserID,
		MediaItemID: req.MediaItemID,
		DedupKey:    pgtype.Text{String: dedupKey, Valid: true},
	})
	if err != nil && !errors.Is(err, pgx.ErrNoRows) {
		return nil, fmt.Errorf("query existing bookmark: %w", err)
	}

	if errors.Is(err, pgx.ErrNoRows) {
		return s.createBookmark(ctx, req, dedupKey)
	}

	if existing.Deleted.Bool {
		if existing.DeletedAt.Valid && time.Since(existing.DeletedAt.Time) < TombstoneTTL {
			return &SaveBookmarkResult{Bookmark: existing, Outcome: SaveOutcomeDeleted}, nil
		}
		return s.createBookmark(ctx, req, dedupKey)
	}

	return s.applyBookmarkLWW(ctx, req, existing, dedupKey)
}

func (s *AnnotationService) createBookmark(ctx context.Context, req SaveBookmarkRequest, dedupKey string) (*SaveBookmarkResult, error) {
	modifiedAt := req.ModifiedAt
	if modifiedAt.IsZero() {
		modifiedAt = time.Now()
	}
	deviceData := mergeDeviceSyncData(nil, req.Source, req.DeviceSyncData)

	bm, err := s.db.CreateMediaBookmarkFull(ctx, database.CreateMediaBookmarkFullParams{
		MediaItemID:      req.MediaItemID,
		UserID:           req.UserID,
		PageNumber:       pgInt4(req.PageNumber),
		ChapterNumber:    pgInt4(req.ChapterNumber),
		CfiPosition:      pgText(req.CFIPosition),
		Title:            req.Title,
		Position:         pgText(req.Position),
		Notes:            pgText(req.Notes),
		PercentageLocation: pgFloat8(req.PercentageLoc),
		EpubcfiLocation:  pgText(req.EpubcfiLocation),
		ChapterReference: pgInt4(req.ChapterReference),
		DedupKey:         pgtype.Text{String: dedupKey, Valid: true},
		LastModifiedAt:   pgtype.Timestamptz{Time: modifiedAt, Valid: true},
		LastModifiedSource: pgtype.Text{String: req.Source, Valid: req.Source != ""},
		DeviceSyncData:   deviceData,
	})
	if err != nil {
		return nil, fmt.Errorf("create bookmark: %w", err)
	}
	s.broadcast(bm.ID, req.UserID, req.MediaItemID, "bookmark", req.Source)
	return &SaveBookmarkResult{Bookmark: bm, Outcome: SaveOutcomeCreated}, nil
}

func (s *AnnotationService) applyBookmarkLWW(ctx context.Context, req SaveBookmarkRequest, existing database.MediaBookmarks, dedupKey string) (*SaveBookmarkResult, error) {
	incomingNewer, contentChanged := s.compareIncomingBookmark(req, existing)

	if !incomingNewer && !contentChanged {
		conflict := isCrossSource(req.Source, existing.LastModifiedSource)
		if conflict {
			s.recordConflict(ctx, req.UserID, req.MediaItemID, "annotation_bookmark",
				existing.DedupKey.String, req.Source, existing.LastModifiedSource.String,
				req, existing, "existing")
		}
		return &SaveBookmarkResult{Bookmark: existing, Outcome: SaveOutcomeSkipped, Conflict: conflict}, nil
	}

	modifiedAt := req.ModifiedAt
	if modifiedAt.IsZero() {
		modifiedAt = time.Now()
	}
	deviceData := mergeDeviceSyncData(existing.DeviceSyncData, req.Source, req.DeviceSyncData)

	bm, err := s.db.UpdateMediaBookmarkForSync(ctx, database.UpdateMediaBookmarkForSyncParams{
		ID:               existing.ID,
		PageNumber:       pgInt4(req.PageNumber),
		ChapterNumber:    pgInt4(req.ChapterNumber),
		CfiPosition:      pgText(req.CFIPosition),
		Title:            req.Title,
		Position:         pgText(req.Position),
		Notes:            pgText(req.Notes),
		PercentageLocation: pgFloat8(req.PercentageLoc),
		EpubcfiLocation:  pgText(req.EpubcfiLocation),
		ChapterReference: pgInt4(req.ChapterReference),
		LastModifiedAt:   pgtype.Timestamptz{Time: modifiedAt, Valid: true},
		LastModifiedSource: pgtype.Text{String: req.Source, Valid: req.Source != ""},
		DeviceSyncData:   deviceData,
	})
	if err != nil {
		return nil, fmt.Errorf("update bookmark: %w", err)
	}
	conflict := isCrossSource(req.Source, existing.LastModifiedSource)
	if conflict {
		s.recordConflict(ctx, req.UserID, req.MediaItemID, "annotation_bookmark",
			existing.DedupKey.String, req.Source, existing.LastModifiedSource.String,
			req, existing, "incoming")
	}
	s.broadcast(bm.ID, req.UserID, req.MediaItemID, "bookmark", req.Source)
	return &SaveBookmarkResult{Bookmark: bm, Outcome: SaveOutcomeUpdated, Conflict: conflict}, nil
}

func (s *AnnotationService) compareIncomingBookmark(req SaveBookmarkRequest, existing database.MediaBookmarks) (incomingNewer bool, contentChanged bool) {
	if req.ModifiedAt.IsZero() {
		contentSame := strings.EqualFold(req.Title, existing.Title) &&
			textEq(req.Notes, existing.Notes)
		return !contentSame, !contentSame
	}
	existingMod := existing.LastModifiedAt
	if !existingMod.Valid {
		existingMod = existing.CreatedAt
	}
	return req.ModifiedAt.After(existingMod.Time), true
}

func (s *AnnotationService) TombstoneBookmarkByID(ctx context.Context, bookmarkID pgtype.UUID, source string) error {
	bm, err := s.db.GetMediaBookmark(ctx, bookmarkID)
	if err != nil {
		return fmt.Errorf("get bookmark for tombstone: %w", err)
	}
	err = s.db.TombstoneMediaBookmarkByID(ctx, bookmarkID)
	if err != nil {
		return fmt.Errorf("tombstone bookmark by ID: %w", err)
	}
	s.broadcast(pgtype.UUID{}, bm.UserID, bm.MediaItemID, "bookmark_delete", source)
	return nil
}

func (s *AnnotationService) recordConflict(
	ctx context.Context,
	userID, mediaItemID pgtype.UUID,
	conflictType, dedupKey string,
	incomingSource, existingSource string,
	incoming any,
	existing any,
	winner string,
) {
	if s.connMgr == nil || !userID.Valid || !mediaItemID.Valid {
		return
	}

	incomingJSON, _ := json.Marshal(incoming)
	existingJSON, _ := json.Marshal(existing)

	var incomingMap, existingMap map[string]interface{}
	json.Unmarshal(incomingJSON, &incomingMap)
	json.Unmarshal(existingJSON, &existingMap)
	if incomingMap == nil {
		incomingMap = map[string]interface{}{}
	}
	if existingMap == nil {
		existingMap = map[string]interface{}{}
	}
	incomingMap["dedup_key"] = dedupKey
	existingMap["dedup_key"] = dedupKey

	conflictData, _ := json.Marshal(map[string]interface{}{
		"incoming": map[string]interface{}{
			"source": incomingSource,
			"data":   incomingMap,
		},
		"existing": map[string]interface{}{
			"source": existingSource,
			"data":   existingMap,
		},
	})

	resolutionData, _ := json.Marshal(map[string]interface{}{
		"winner": winner,
		"reason": "last_modified_at_wins",
	})

	conflict, err := s.db.CreateAutoResolvedSyncConflict(ctx, database.CreateAutoResolvedSyncConflictParams{
		MediaItemID:    mediaItemID,
		UserID:         userID,
		ConflictType:   conflictType,
		ConflictData:   conflictData,
		ResolutionData: resolutionData,
	})
	if err != nil {
		log.Printf("AnnotationService: failed to record conflict: %v", err)
		return
	}

	var conflictIDStr string
	if conflict.ID.Valid {
		conflictIDStr = uuid.UUID(conflict.ID.Bytes).String()
	}
	s.connMgr.BroadcastConflictNotification(
		uuid.UUID(mediaItemID.Bytes),
		"annotation_conflict",
		conflictIDStr,
	)
}

func (s *AnnotationService) broadcast(
	highlightID, userID, mediaItemID pgtype.UUID,
	annotationType string,
	source string,
) {
	if s.connMgr == nil || !userID.Valid || !mediaItemID.Valid {
		return
	}
	src := SourceDevice{Type: source}
	s.connMgr.BroadcastAnnotationUpdate(
		uuid.UUID(mediaItemID.Bytes),
		annotationType,
		map[string]interface{}{
			"highlight_id": uuid.UUID(highlightID.Bytes),
		},
		src,
	)
}

func ComputeDedupKey(selectionText, epubcfiStart, startPosition string) string {
	normalized := normalizeText(selectionText)
	posBucket := bucketPosition(epubcfiStart)
	if posBucket == "" {
		posBucket = bucketPosition(startPosition)
	}

	h := sha1.New()
	h.Write([]byte(normalized))
	h.Write([]byte{0})
	h.Write([]byte(posBucket))
	return hex.EncodeToString(h.Sum(nil))
}

func normalizeText(s string) string {
	fields := strings.Fields(strings.ToLower(s))
	return strings.Join(fields, " ")
}

func bucketPosition(pos string) string {
	if pos == "" {
		return ""
	}
	if strings.HasPrefix(pos, "epubcfi(") {
		if idx := strings.LastIndex(pos, ":"); idx > 0 {
			return pos[:idx]
		}
	}
	if len(pos) > 50 {
		return pos[:50]
	}
	return pos
}

func mergeDeviceSyncData(existing []byte, source string, data json.RawMessage) []byte {
	if source == "" && len(data) == 0 {
		return existing
	}
	m := make(map[string]interface{})
	if len(existing) > 0 {
		_ = json.Unmarshal(existing, &m)
	}
	if source != "" {
		if len(data) > 0 {
			var val interface{}
			_ = json.Unmarshal(data, &val)
			m[source] = val
		} else {
			m[source] = map[string]interface{}{"synced_at": time.Now().UTC().Format(time.RFC3339)}
		}
	}
	result, _ := json.Marshal(m)
	return result
}

func isCrossSource(incoming string, existing pgtype.Text) bool {
	if incoming == "" || !existing.Valid {
		return false
	}
	return incoming != existing.String
}

func pgText(s string) pgtype.Text {
	if s == "" {
		return pgtype.Text{Valid: false}
	}
	return pgtype.Text{String: s, Valid: true}
}

func pgFloat8(f float64) pgtype.Float8 {
	if f == 0 {
		return pgtype.Float8{Valid: false}
	}
	return pgtype.Float8{Float64: f, Valid: true}
}

func pgInt4(i int32) pgtype.Int4 {
	if i == 0 {
		return pgtype.Int4{Valid: false}
	}
	return pgtype.Int4{Int32: i, Valid: true}
}

func textEq(a string, b pgtype.Text) bool {
	if !b.Valid {
		return a == ""
	}
	return a == b.String
}

func floatEq(a float64, b pgtype.Float8) bool {
	if !b.Valid {
		return a == 0
	}
	return math.Abs(a-b.Float64) < 0.001
}