package message

import (
	"context"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

func OwnerQuoteReadFloor() time.Time {
	return time.Unix(1, 0).UTC()
}

func WriteThreadReadStateByGlobalSeq(ctx context.Context, execer CommandTagExecer, memberID string, scopeID string, lastReadSeq int64, lastReadGlobalSeq int64, updatedAt time.Time) error {
	_, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = CASE
        WHEN GREATEST(last_read_seq, $3) <> last_read_seq OR GREATEST(last_read_global_seq, $4) <> last_read_global_seq THEN unread_generation + 1
        ELSE unread_generation
    END,
    last_read_seq = GREATEST(last_read_seq, $3),
    last_read_global_seq = GREATEST(last_read_global_seq, $4),
    updated_at = $5
WHERE member_id = $1
  AND scope_id = $2
  AND removed_at IS NULL
  AND effective_to_seq IS NULL
  AND (
      last_read_seq < $3
      OR last_read_global_seq < $4
  )
`, strings.TrimSpace(memberID), strings.TrimSpace(scopeID), lastReadSeq, lastReadGlobalSeq, updatedAt.UTC())
	if err != nil {
		return fmt.Errorf("update scope read state: %w", err)
	}
	return nil
}

func WriteActivityReadStateByGlobalSeq(ctx context.Context, execer CommandTagExecer, memberID string, scopeID string, lastActivityGlobalSeq int64, updatedAt time.Time) error {
	_, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = CASE
        WHEN GREATEST(last_activity_global_seq, $3) <> last_activity_global_seq THEN unread_generation + 1
        ELSE unread_generation
    END,
    last_activity_global_seq = GREATEST(last_activity_global_seq, $3),
    updated_at = $4
WHERE member_id = $1
  AND scope_id = $2
  AND removed_at IS NULL
  AND effective_to_seq IS NULL
  AND last_activity_global_seq < $3
`, strings.TrimSpace(memberID), strings.TrimSpace(scopeID), lastActivityGlobalSeq, updatedAt.UTC())
	if err != nil {
		return fmt.Errorf("update scope activity read state: %w", err)
	}
	return nil
}

func WriteThreadReadStateExact(ctx context.Context, execer CommandTagExecer, memberID string, scopeID string, lastReadSeq int64, lastReadGlobalSeq int64, updatedAt time.Time) error {
	trimmedMemberID := strings.TrimSpace(memberID)
	trimmedScopeID := strings.TrimSpace(scopeID)
	normalizedUpdatedAt := updatedAt.UTC()

	_, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    last_read_seq = $3,
    last_read_global_seq = $4,
    updated_at = $5
WHERE member_id = $1
  AND scope_id = $2
  AND removed_at IS NULL
  AND effective_to_seq IS NULL
  AND (last_read_seq <> $3 OR last_read_global_seq <> $4)
`, trimmedMemberID, trimmedScopeID, lastReadSeq, lastReadGlobalSeq, normalizedUpdatedAt)
	if err != nil {
		return fmt.Errorf("update exact scope read state: %w", err)
	}
	return nil
}

func WriteAuthorThreadReadState(ctx context.Context, execer CommandTagExecer, memberID string, scopeID string, lastReadSeq int64, lastReadGlobalSeq int64) error {
	_, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = CASE
        WHEN last_read_seq <> $3 OR last_read_global_seq <> $4 THEN unread_generation + 1
        ELSE unread_generation
    END,
    last_read_seq = $3,
    last_read_global_seq = $4,
    updated_at = NOW()
WHERE member_id = $1
  AND scope_id = $2
  AND removed_at IS NULL
  AND effective_to_seq IS NULL
`, strings.TrimSpace(memberID), strings.TrimSpace(scopeID), lastReadSeq, lastReadGlobalSeq)
	if err != nil {
		return fmt.Errorf("update author scope read state: %w", err)
	}
	return nil
}

func WriteThreadOwnerContextDismissState(ctx context.Context, execer CommandTagExecer, memberID string, scopeID string, ownerContextDismissedThroughGlobalSeq int64) error {
	_, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET owner_context_dismissed_through_global_seq = GREATEST(owner_context_dismissed_through_global_seq, $3),
    updated_at = NOW()
WHERE member_id = $1
  AND scope_id = $2
  AND removed_at IS NULL
  AND effective_to_seq IS NULL
`, strings.TrimSpace(memberID), strings.TrimSpace(scopeID), ownerContextDismissedThroughGlobalSeq)
	if err != nil {
		return fmt.Errorf("update scope owner context dismiss state: %w", err)
	}
	return nil
}

func LoadThreadOwnerContextDismissedThroughGlobalSeq(ctx context.Context, queryer coredb.QueryRower, memberID string, scopeID string) (int64, error) {
	row := queryer.QueryRow(ctx, `
SELECT owner_context_dismissed_through_global_seq
FROM scope_memberships
WHERE member_id = $1
  AND scope_id = $2
  AND removed_at IS NULL
  AND effective_to_seq IS NULL
`, strings.TrimSpace(memberID), strings.TrimSpace(scopeID))
	var ownerContextDismissedThroughGlobalSeq int64
	if err := row.Scan(&ownerContextDismissedThroughGlobalSeq); err != nil {
		if coredb.IsNotFound(err) {
			return 0, nil
		}
		return 0, fmt.Errorf("load owner context dismiss state: %w", err)
	}
	if ownerContextDismissedThroughGlobalSeq < 0 {
		return 0, fmt.Errorf("invalid owner context dismiss state")
	}
	return ownerContextDismissedThroughGlobalSeq, nil
}

func UpsertThreadReadState(
	ctx context.Context,
	execer CommandTagExecer,
	memberID string,
	scopeID string,
	lastReadSeq int64,
	ownerQuoteReadAt *time.Time,
) error {
	updatedAt := OwnerQuoteReadFloor()
	shouldUpdateUpdatedAt := false
	if ownerQuoteReadAt != nil {
		updatedAt = ownerQuoteReadAt.UTC()
		shouldUpdateUpdatedAt = true
	}

	_, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = CASE
        WHEN GREATEST(last_read_seq, $3) <> last_read_seq THEN unread_generation + 1
        ELSE unread_generation
    END,
    last_read_seq = GREATEST(last_read_seq, $3),
    updated_at = CASE
        WHEN $5 THEN GREATEST(updated_at, $4)
        ELSE updated_at
    END
WHERE member_id = $1
  AND scope_id = $2
  AND removed_at IS NULL
  AND effective_to_seq IS NULL
  AND (
      last_read_seq < $3
      OR ($5 AND updated_at < $4)
  )
`, strings.TrimSpace(memberID), strings.TrimSpace(scopeID), lastReadSeq, updatedAt, shouldUpdateUpdatedAt)
	if err != nil {
		return fmt.Errorf("upsert scope read state: %w", err)
	}
	return nil
}