package message

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

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

func (store PinStore) InsertActivePin(ctx context.Context, record ScopeMessagePinRecord) (ScopeMessagePinMutationResult, error) {
	if err := store.requireExecer(); err != nil {
		return ScopeMessagePinMutationResult{}, err
	}
	record = normalizeScopeMessagePinRecord(record)
	if record.ID == "" || record.WorkspaceID == "" || record.ScopeID == "" || record.MessageID == "" || record.PinnedByMemberID == "" {
		return ScopeMessagePinMutationResult{}, invalidArgument("message pin id, workspace, scope, message, and pinner are required")
	}
	if _, err := store.execer.Exec(ctx, `
INSERT INTO scope_message_pins (id, workspace_id, scope_id, message_id, pinned_by_member_id, pinned_at, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
`, record.ID, record.WorkspaceID, record.ScopeID, record.MessageID, record.PinnedByMemberID, record.PinnedAt, record.CreatedAt, record.UpdatedAt); err != nil {
		if uniqueViolationMatches(err, "scope_message_pins_scope_message_key") {
			existing, found, loadErr := store.LoadActivePin(ctx, record.WorkspaceID, record.ScopeID, record.MessageID)
			if loadErr != nil {
				return ScopeMessagePinMutationResult{}, loadErr
			}
			if found {
				return ScopeMessagePinMutationResult{Changed: false, Pin: existing}, nil
			}
		}
		return ScopeMessagePinMutationResult{}, fmt.Errorf("insert scope message pin: %w", err)
	}
	return ScopeMessagePinMutationResult{Changed: true, Pin: record}, nil
}

func (store PinStore) LoadActivePin(ctx context.Context, workspaceID, scopeID, messageID string) (ScopeMessagePinRecord, bool, error) {
	if err := store.requireExecer(); err != nil {
		return ScopeMessagePinRecord{}, false, err
	}
	return store.loadActivePinByWhere(ctx, `
WHERE workspace_id = $1
  AND scope_id = $2
  AND message_id = $3
`, strings.TrimSpace(workspaceID), strings.TrimSpace(scopeID), strings.TrimSpace(messageID))
}

func (store PinStore) LoadActivePinByID(ctx context.Context, workspaceID, scopeID, messageID, pinID string) (ScopeMessagePinRecord, bool, error) {
	if err := store.requireExecer(); err != nil {
		return ScopeMessagePinRecord{}, false, err
	}
	return store.loadActivePinByWhere(ctx, `
WHERE workspace_id = $1
  AND scope_id = $2
  AND message_id = $3
  AND id = $4
`, strings.TrimSpace(workspaceID), strings.TrimSpace(scopeID), strings.TrimSpace(messageID), strings.TrimSpace(pinID))
}

func (store PinStore) DeleteActivePin(ctx context.Context, workspaceID, scopeID, messageID string) (ScopeMessagePinMutationResult, error) {
	if err := store.requireExecer(); err != nil {
		return ScopeMessagePinMutationResult{}, err
	}
	existing, found, err := store.LoadActivePin(ctx, workspaceID, scopeID, messageID)
	if err != nil || !found {
		return ScopeMessagePinMutationResult{Changed: false}, err
	}
	result, err := store.execer.Exec(ctx, `
DELETE FROM scope_message_pins
WHERE workspace_id = $1
  AND scope_id = $2
  AND message_id = $3
`, strings.TrimSpace(workspaceID), strings.TrimSpace(scopeID), strings.TrimSpace(messageID))
	if err != nil {
		return ScopeMessagePinMutationResult{}, fmt.Errorf("delete scope message pin: %w", err)
	}
	return ScopeMessagePinMutationResult{Changed: result.RowsAffected() > 0, Pin: existing}, nil
}

func (store PinStore) ListActivePins(ctx context.Context, workspaceID, scopeID, viewerMemberID string, cursor MessagePinCursor, limit int) ([]ScopeMessagePinListRow, error) {
	if err := store.requireExecer(); err != nil {
		return nil, err
	}
	workspaceID = strings.TrimSpace(workspaceID)
	scopeID = strings.TrimSpace(scopeID)
	viewerMemberID = strings.TrimSpace(viewerMemberID)
	if workspaceID == "" || scopeID == "" || viewerMemberID == "" {
		return nil, invalidArgument("message pin list workspace, scope, and viewer are required")
	}
	cursor, err := normalizeMessagePinCursor(cursor)
	if err != nil {
		return nil, err
	}
	if limit <= 0 {
		limit = DefaultMessagePinListLimit
	}
	if limit > MaxMessagePinListLimit*4+1 {
		limit = MaxMessagePinListLimit*4 + 1
	}
	var cursorPinnedAt *time.Time
	if !cursor.PinnedAt.IsZero() {
		pinnedAt := cursor.PinnedAt
		cursorPinnedAt = &pinnedAt
	}
	rows, err := store.execer.Query(ctx, `
SELECT
    p.id, p.workspace_id, p.scope_id, p.message_id, p.pinned_by_member_id, p.pinned_at, p.created_at, p.updated_at,
    m.id, m.workspace_id, m.scope_id, m.seq, m.workspace_global_seq, m.author_member_id,
    m.author_type,
    COALESCE(NULLIF(m.notify_principal_member_id, ''), m.author_member_id),
    COALESCE(NULLIF(m.idempotency_subject_member_id, ''), m.author_member_id),
    COALESCE(m.client_message_id, ''), COALESCE(m.idempotency_payload_digest, ''),
    m.message_type, m.content, m.metadata, m.created_at, m.updated_at,
    m.edited_at, COALESCE(m.edited_by_member_id, ''), m.edit_revision
FROM scope_message_pins p
JOIN scope_messages m
  ON m.id = p.message_id
 AND m.workspace_id = p.workspace_id
WHERE p.workspace_id = $1
  AND p.scope_id = $2
  AND m.scope_id = p.scope_id
  AND ($3 IS NULL OR p.pinned_at < $3 OR (p.pinned_at = $3 AND p.id < $4))
ORDER BY p.pinned_at DESC, p.id DESC
LIMIT $5
`, workspaceID, scopeID, cursorPinnedAt, cursor.ID, limit)
	if err != nil {
		return nil, fmt.Errorf("list scope message pins: %w", err)
	}
	defer rows.Close()
	return scanScopeMessagePinListRows(rows)
}

func (store PinStore) ListPinStatesForMessages(ctx context.Context, workspaceID, viewerMemberID string, messageIDs []string) ([]ScopeMessagePinStateRow, error) {
	if err := store.requireExecer(); err != nil {
		return nil, err
	}
	ids := normalizeNonEmptyStrings(messageIDs)
	if len(ids) == 0 {
		return []ScopeMessagePinStateRow{}, nil
	}
	requestsCTE, args, _, err := coredb.BuildStringValuesCTE("requested_messages", "message_id", ids, 2)
	if err != nil {
		return nil, fmt.Errorf("build message pin state query: %w", err)
	}
	query := fmt.Sprintf(`
WITH %s
SELECT p.message_id, p.id, p.pinned_by_member_id, p.pinned_at
FROM requested_messages requested
JOIN scope_message_pins p
  ON p.workspace_id = $1
 AND p.message_id = requested.message_id
ORDER BY p.message_id ASC, p.pinned_at DESC, p.id DESC
`, requestsCTE)
	rows, err := store.execer.Query(ctx, query, append([]any{strings.TrimSpace(workspaceID)}, args...)...)
	if err != nil {
		return nil, fmt.Errorf("list scope message pin states: %w", err)
	}
	defer rows.Close()
	result := make([]ScopeMessagePinStateRow, 0, len(ids))
	for rows.Next() {
		var row ScopeMessagePinStateRow
		if err := rows.Scan(&row.MessageID, &row.PinID, &row.PinnedByMemberID, &row.PinnedAt); err != nil {
			return nil, fmt.Errorf("scan scope message pin state: %w", err)
		}
		result = append(result, row)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate scope message pin states: %w", err)
	}
	return result, nil
}

func AttachMessagePinStateForAppMessages(ctx context.Context, execer coredb.QueryExecer, workspaceID, viewerMemberID string, messages []AppMessage) error {
	if len(messages) == 0 {
		return nil
	}
	states, err := PinStore{scopeStoreBase: scopeStoreBase{execer: execer}}.ListPinStatesForMessages(ctx, workspaceID, viewerMemberID, CollectAppMessageIDs(messages))
	if err != nil {
		return err
	}
	byMessageID := make(map[string]ScopeMessagePinStateRow, len(states))
	for _, state := range states {
		messageID := strings.TrimSpace(state.MessageID)
		if messageID == "" {
			continue
		}
		if _, exists := byMessageID[messageID]; exists {
			continue
		}
		byMessageID[messageID] = state
	}
	for index := range messages {
		state, ok := byMessageID[strings.TrimSpace(messages[index].ID)]
		if !ok {
			continue
		}
		messages[index].PinState = &MessagePinState{
			IsPinned:         true,
			PinID:            state.PinID,
			PinnedAt:         state.PinnedAt,
			PinnedByMemberID: state.PinnedByMemberID,
		}
	}
	return nil
}

func (store PinStore) loadActivePinByWhere(ctx context.Context, where string, args ...any) (ScopeMessagePinRecord, bool, error) {
	var record ScopeMessagePinRecord
	err := store.execer.QueryRow(ctx, `
SELECT id, workspace_id, scope_id, message_id, pinned_by_member_id, pinned_at, created_at, updated_at
FROM scope_message_pins
`+where+`
LIMIT 1
`, args...).Scan(
		&record.ID,
		&record.WorkspaceID,
		&record.ScopeID,
		&record.MessageID,
		&record.PinnedByMemberID,
		&record.PinnedAt,
		&record.CreatedAt,
		&record.UpdatedAt,
	)
	if errorsIsNoRows(err) {
		return ScopeMessagePinRecord{}, false, nil
	}
	if err != nil {
		return ScopeMessagePinRecord{}, false, fmt.Errorf("load scope message pin: %w", err)
	}
	return record, true, nil
}

type messagePinTarget struct {
	WorkspaceID string
	ScopeID     string
	ScopeType   string
	MemberID    string
	UserID      string
	CanWrite    bool
	Contract    access.ResolvedScopeTarget
}