package privateagent

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

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/message"
)

func (store *Store) ensureScopeProjectionForTarget(ctx context.Context, tx coredb.Tx, target message.ThreadAccess) (string, error) {
	if target.ReplyThread != nil {
		return store.ensureReplyThreadScopeProjection(ctx, tx, target.ChannelID)
	}
	if target.ThreadType == domain.ThreadTypeDM {
		return store.ensureDMScopeProjection(ctx, tx, target.DMThreadID)
	}
	return store.ensureChannelScopeProjection(ctx, tx, target.ChannelID)
}

func (store *Store) ensureChannelScopeProjection(ctx context.Context, execer coredb.QueryExecer, channelID string) (string, error) {
	trimmedChannelID := strings.TrimSpace(channelID)
	if trimmedChannelID == "" {
		return "", badRequest("channelId is required")
	}

	if _, err := message.EnsureScopeProjectionForThreadAccess(ctx, execer, message.ThreadAccess{
		ThreadType: domain.ThreadTypeChannel,
		ChannelID:  trimmedChannelID,
	}, "", time.Now().UTC()); err != nil {
		return "", err
	}
	return trimmedChannelID, nil
}

func (store *Store) ensureDMScopeProjection(ctx context.Context, execer coredb.QueryExecer, dmThreadID string) (string, error) {
	trimmedDMThreadID := strings.TrimSpace(dmThreadID)
	if trimmedDMThreadID == "" {
		return "", badRequest("dmThreadId is required")
	}

	if _, err := message.EnsureScopeProjectionForThreadAccess(ctx, execer, message.ThreadAccess{
		ThreadType: domain.ThreadTypeDM,
		DMThreadID: trimmedDMThreadID,
	}, "", time.Now().UTC()); err != nil {
		return "", err
	}
	return trimmedDMThreadID, nil
}

func (store *Store) ensureReplyThreadScopeProjection(ctx context.Context, execer coredb.QueryExecer, threadChannelID string) (string, error) {
	trimmedThreadChannelID := strings.TrimSpace(threadChannelID)
	if trimmedThreadChannelID == "" {
		return "", badRequest("threadChannelId is required")
	}

	if _, err := message.EnsureScopeProjectionForThreadAccess(ctx, execer, message.ThreadAccess{
		ThreadType:  domain.ThreadTypeChannel,
		ChannelID:   trimmedThreadChannelID,
		ReplyThread: &message.ReplyThreadTarget{},
	}, "", time.Now().UTC()); err != nil {
		return "", err
	}
	return trimmedThreadChannelID, nil
}

func (store *Store) ensureScopeParticipantProjection(ctx context.Context, execer coredb.QueryExecer, scopeID string, memberID string, agentState string, createdAt time.Time) error {
	trimmedScopeID := strings.TrimSpace(scopeID)
	trimmedMemberID := strings.TrimSpace(memberID)
	if trimmedScopeID == "" || trimmedMemberID == "" {
		return nil
	}
	now := createdAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	var latestSeq, latestGlobalSeq int64
	if err := execer.QueryRow(ctx, `
SELECT latest_seq, latest_global_seq
FROM scopes
WHERE id = $1
`, trimmedScopeID).Scan(&latestSeq, &latestGlobalSeq); err != nil {
		if coredb.IsNotFound(err) {
			return ErrNotFound
		}
		return fmt.Errorf("load scope bounds for membership projection: %w", err)
	}
	if err := message.EnsureScopeMembership(ctx, execer, trimmedScopeID, trimmedMemberID, "public", "", strings.TrimSpace(agentState), latestSeq, latestGlobalSeq, now); err != nil {
		return fmt.Errorf("insert scope membership projection: %w", err)
	}
	return nil
}

func (store *Store) ensureChannelScopeParticipantProjection(ctx context.Context, execer coredb.QueryExecer, channelID string, memberID string, agentState string, createdAt time.Time) error {
	if _, err := store.ensureChannelScopeProjection(ctx, execer, channelID); err != nil {
		return err
	}
	return store.ensureScopeParticipantProjection(ctx, execer, channelID, memberID, agentState, createdAt)
}

func (store *Store) setScopeParticipantArchivedState(ctx context.Context, execer coredb.QueryExecer, scopeID string, memberID string, archivedAt *time.Time) error {
	trimmedScopeID := strings.TrimSpace(scopeID)
	trimmedMemberID := strings.TrimSpace(memberID)
	if trimmedScopeID == "" || trimmedMemberID == "" {
		return nil
	}
	now := time.Now().UTC()
	if archivedAt != nil {
		now = archivedAt.UTC()
		if now.IsZero() {
			now = time.Now().UTC()
		}
		if _, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    archived_at = COALESCE(archived_at, $3),
    updated_at = $3
WHERE scope_id = $1
  AND member_id = $2
  AND visibility = 'public'
  AND removed_at IS NULL
`, trimmedScopeID, trimmedMemberID, now); err != nil {
			return fmt.Errorf("archive scope membership projection: %w", err)
		}
		return nil
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    archived_at = NULL,
    updated_at = $3
WHERE scope_id = $1
  AND member_id = $2
  AND visibility = 'public'
  AND archived_at IS NOT NULL
`, trimmedScopeID, trimmedMemberID, now); err != nil {
		return fmt.Errorf("unarchive scope membership projection: %w", err)
	}
	return nil
}

func (store *Store) ensurePublicChannelAgentEntryProjection(ctx context.Context, execer coredb.QueryExecer, workspaceID string, channelID string, agentMemberID string, createdAt time.Time) error {
	trimmedChannelID := strings.TrimSpace(channelID)
	trimmedAgentMemberID := strings.TrimSpace(agentMemberID)
	if trimmedChannelID == "" || trimmedAgentMemberID == "" {
		return nil
	}
	if _, err := store.ensureChannelScopeProjection(ctx, execer, trimmedChannelID); err != nil {
		return err
	}
	now := createdAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if err := message.EnsureScopeMembership(ctx, execer, trimmedChannelID, trimmedAgentMemberID, channelAgentEntryVisibilityPublic, "", "", 0, 0, now); err != nil {
		return fmt.Errorf("insert public channel agent scope membership: %w", err)
	}
	_ = workspaceID
	return nil
}

func (store *Store) ensureAllChannelScopeParticipantProjection(ctx context.Context, tx coredb.Tx, workspaceID string, memberID string, agentState string, createdAt time.Time) error {
	allChannelID, err := store.lookupAllChannelID(ctx, tx, workspaceID)
	if err != nil {
		return err
	}
	if err := store.ensureChannelScopeParticipantProjection(ctx, tx, allChannelID, memberID, agentState, createdAt); err != nil {
		return err
	}
	return nil
}

func (store *Store) markMemberScopeProjectionsRemoved(ctx context.Context, execer coredb.QueryExecer, memberID string, removedAt time.Time) error {
	trimmedMemberID := strings.TrimSpace(memberID)
	if trimmedMemberID == "" {
		return nil
	}
	now := removedAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if _, err := execer.Exec(ctx, `
DELETE FROM scope_notification_settings
WHERE member_id = $1
`, trimmedMemberID); err != nil {
		return fmt.Errorf("delete member scope notification settings: %w", err)
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    removed_at = $2,
    updated_at = $2
WHERE member_id = $1 AND removed_at IS NULL
`, trimmedMemberID, now); err != nil {
		return fmt.Errorf("mark member scope memberships removed: %w", err)
	}
	return nil
}

func (store *Store) archiveScopeProjection(ctx context.Context, execer coredb.QueryExecer, scopeID string, archivedAt time.Time) error {
	trimmedScopeID := strings.TrimSpace(scopeID)
	if trimmedScopeID == "" {
		return nil
	}
	now := archivedAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if _, err := execer.Exec(ctx, `
DELETE FROM scope_notification_settings
WHERE scope_id = $1
`, trimmedScopeID); err != nil {
		return fmt.Errorf("delete archived scope notification settings: %w", err)
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    removed_at = $2,
    archived_at = COALESCE(archived_at, $2),
    updated_at = $2
WHERE scope_id = $1
  AND removed_at IS NULL
`, trimmedScopeID, now); err != nil {
		return fmt.Errorf("archive scope memberships: %w", err)
	}
	if _, err := execer.Exec(ctx, `
UPDATE scopes
SET status = 'archived',
    archived_at = COALESCE(archived_at, $2),
    updated_at = $2
WHERE id = $1
`, trimmedScopeID, now); err != nil {
		return fmt.Errorf("archive scope projection: %w", err)
	}
	return nil
}

func (store *Store) markScopeParticipantProjectionRemoved(ctx context.Context, execer coredb.QueryExecer, scopeID string, memberID string, removedAt time.Time) error {
	trimmedScopeID := strings.TrimSpace(scopeID)
	trimmedMemberID := strings.TrimSpace(memberID)
	if trimmedScopeID == "" || trimmedMemberID == "" {
		return nil
	}
	now := removedAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if _, err := execer.Exec(ctx, `
DELETE FROM scope_notification_settings
WHERE scope_id = $1 AND member_id = $2
`, trimmedScopeID, trimmedMemberID); err != nil {
		return fmt.Errorf("delete scope notification setting: %w", err)
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_memberships
SET unread_generation = unread_generation + 1,
    removed_at = $3,
    updated_at = $3
WHERE scope_id = $1
  AND member_id = $2
  AND visibility = 'public'
  AND removed_at IS NULL
`, trimmedScopeID, trimmedMemberID, now); err != nil {
		return fmt.Errorf("mark public scope membership removed: %w", err)
	}
	return nil
}

func (store *Store) markPublicChannelAgentEntryProjectionRemoved(ctx context.Context, execer coredb.QueryExecer, channelID string, agentMemberID string, removedAt time.Time) error {
	trimmedChannelID := strings.TrimSpace(channelID)
	trimmedAgentMemberID := strings.TrimSpace(agentMemberID)
	if trimmedChannelID == "" || trimmedAgentMemberID == "" {
		return nil
	}
	now := removedAt.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if _, err := execer.Exec(ctx, `
DELETE sns
FROM scope_notification_settings sns
JOIN scopes scope
  ON scope.id = sns.scope_id
WHERE sns.member_id = $2
  AND (scope.id = $1 OR scope.parent_scope_id = $1)
`, trimmedChannelID, trimmedAgentMemberID); err != nil {
		return fmt.Errorf("delete public channel agent scope notification: %w", err)
	}
	if _, err := execer.Exec(ctx, `
UPDATE scope_memberships sm
JOIN scopes scope
  ON scope.id = sm.scope_id
SET sm.removed_at = $3,
    sm.updated_at = $3,
    sm.unread_generation = sm.unread_generation + 1
WHERE sm.member_id = $2
  AND sm.visibility = 'public'
  AND sm.removed_at IS NULL
  AND (scope.id = $1 OR scope.parent_scope_id = $1)
`, trimmedChannelID, trimmedAgentMemberID, now); err != nil {
		return fmt.Errorf("mark public channel agent scope membership removed: %w", err)
	}
	return nil
}

func (store *Store) lookupAllChannelID(ctx context.Context, queryer interface {
	QueryRow(context.Context, string, ...any) coredb.Row
}, workspaceID string) (string, error) {
	row := queryer.QueryRow(ctx, `
SELECT p.scope_id
FROM scope_channel_profiles p
JOIN scopes s ON s.id = p.scope_id
WHERE p.workspace_id = $1
  AND p.is_all_channel = TRUE
  AND s.type = 'channel'
  AND s.status = 'active'
  AND s.archived_at IS NULL
LIMIT 1
`, strings.TrimSpace(workspaceID))
	var channelID string
	if err := row.Scan(&channelID); err != nil {
		if coredb.IsNotFound(err) {
			return "", ErrNotFound
		}
		return "", fmt.Errorf("load #all channel: %w", err)
	}
	return channelID, nil
}