package attachment

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

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

type PersonalSticker struct {
	ID          string
	Filename    string
	ContentType string
	SizeBytes   int64
	CreatedAt   time.Time
}

func (txs TxStore) ListPersonalStickersForUser(ctx context.Context, userID string, workspaceID string) ([]PersonalSticker, error) {
	memberID, err := txs.loadHumanMemberID(ctx, txs.tx, strings.TrimSpace(userID), strings.TrimSpace(workspaceID))
	if err != nil {
		return nil, err
	}

	rows, err := txs.tx.Query(ctx, `
SELECT id, filename, content_type, size_bytes, created_at
FROM personal_stickers
WHERE workspace_id = $1
  AND member_id = $2
ORDER BY created_at DESC, id DESC
`, workspaceID, memberID)
	if err != nil {
		return nil, fmt.Errorf("list personal stickers: %w", err)
	}
	defer rows.Close()

	stickers := make([]PersonalSticker, 0)
	for rows.Next() {
		var sticker PersonalSticker
		if err := rows.Scan(
			&sticker.ID,
			&sticker.Filename,
			&sticker.ContentType,
			&sticker.SizeBytes,
			&sticker.CreatedAt,
		); err != nil {
			return nil, fmt.Errorf("scan personal sticker: %w", err)
		}
		stickers = append(stickers, sticker)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate personal stickers: %w", err)
	}

	return stickers, nil
}

func (txs TxStore) UploadPersonalStickersForUser(ctx context.Context, userID string, workspaceID string, stickers []CreateAttachmentParams) ([]PersonalSticker, error) {
	if len(stickers) == 0 {
		return nil, badRequest("files are required")
	}

	memberID, err := txs.loadHumanMemberID(ctx, txs.tx, strings.TrimSpace(userID), strings.TrimSpace(workspaceID))
	if err != nil {
		return nil, err
	}

	created := make([]PersonalSticker, 0, len(stickers))
	cleanup := func(err error) ([]PersonalSticker, error) {
		txs.cleanupUploadedMediaObjects(ctx)
		return nil, err
	}
	now := time.Now().UTC()
	insertValues := make([]string, 0, len(stickers))
	insertArgs := make([]any, 0, len(stickers)*10)

	for _, input := range stickers {
		filename := strings.TrimSpace(input.Filename)
		if filename == "" {
			return cleanup(badRequest("filename is required"))
		}
		contentType := strings.TrimSpace(input.ContentType)
		if contentType == "" {
			contentType = "application/octet-stream"
		}
		if len(input.Blob) == 0 {
			return cleanup(badRequest("file is empty"))
		}

		stickerID, err := identifiers.NewEntityID("stk")
		if err != nil {
			return cleanup(fmt.Errorf("generate personal sticker id: %w", err))
		}

		storageDriver := ""
		objectKey := ""
		payload := input.Blob
		if txs.mediaStorageEnabled() {
			storageDriver, objectKey, err = txs.putMediaObject(ctx, mediaObjectTypeSticker, workspaceID, stickerID, contentType, input.Blob)
			if err != nil {
				return cleanup(err)
			}
			payload = nil
			txs.trackUploadedMediaObject(storageDriver, objectKey)
		}

		created = append(created, PersonalSticker{
			ID:          stickerID,
			Filename:    filename,
			ContentType: contentType,
			SizeBytes:   int64(len(input.Blob)),
			CreatedAt:   now,
		})
		base := len(insertArgs) + 1
		insertValues = append(insertValues, fmt.Sprintf("($%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d)", base, base+1, base+2, base+3, base+4, base+5, base+6, base+7, base+8, base+9))
		insertArgs = append(insertArgs, stickerID, workspaceID, memberID, filename, contentType, int64(len(input.Blob)), storageDriver, nullableString(objectKey), payload, now)
	}

	if _, err := txs.tx.Exec(ctx, fmt.Sprintf(`
INSERT INTO personal_stickers (id, workspace_id, member_id, filename, content_type, size_bytes, storage_driver, object_key, payload, created_at)
VALUES %s
`, strings.Join(insertValues, ",\n")), insertArgs...); err != nil {
		return cleanup(fmt.Errorf("insert personal stickers: %w", err))
	}

	return created, nil
}

func (txs TxStore) DeletePersonalStickerForUser(ctx context.Context, userID string, workspaceID string, stickerID string) error {
	memberID, err := txs.loadHumanMemberID(ctx, txs.tx, strings.TrimSpace(userID), strings.TrimSpace(workspaceID))
	if err != nil {
		return err
	}

	trimmedStickerID := strings.TrimSpace(stickerID)
	if trimmedStickerID == "" {
		return badRequest("stickerId is required")
	}

	row := txs.tx.QueryRow(ctx, `
SELECT COALESCE(storage_driver, ''), COALESCE(object_key, '')
FROM personal_stickers
WHERE id = $1
  AND workspace_id = $2
  AND member_id = $3
`, trimmedStickerID, workspaceID, memberID)
	var storageDriver string
	var objectKey string
	if err := row.Scan(&storageDriver, &objectKey); err != nil {
		if coredb.IsNotFound(err) {
			return ErrNotFound
		}
		return fmt.Errorf("load personal sticker delete target: %w", err)
	}

	commandTag, err := txs.tx.Exec(ctx, `
DELETE FROM personal_stickers
WHERE id = $1
  AND workspace_id = $2
  AND member_id = $3
`, trimmedStickerID, workspaceID, memberID)
	if err != nil {
		return fmt.Errorf("delete personal sticker: %w", err)
	}
	if commandTag.RowsAffected() == 0 {
		return ErrNotFound
	}

	txs.deleteMediaObjectBestEffort(ctx, storageDriver, objectKey)
	return nil
}

func (txs TxStore) LoadPersonalStickerForUser(ctx context.Context, userID string, workspaceID string, stickerID string) (AttachmentDownload, error) {
	trimmedUserID := strings.TrimSpace(userID)
	if trimmedUserID == "" {
		return AttachmentDownload{}, badRequest("userId is required")
	}

	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedWorkspaceID == "" {
		return AttachmentDownload{}, badRequest("workspaceId is required")
	}

	trimmedStickerID := strings.TrimSpace(stickerID)
	if trimmedStickerID == "" {
		return AttachmentDownload{}, badRequest("stickerId is required")
	}

	row := txs.tx.QueryRow(ctx, `
SELECT ps.filename, ps.content_type, COALESCE(ps.storage_driver, ''), COALESCE(ps.object_key, ''), ps.payload
FROM personal_stickers ps
JOIN members m ON m.id = ps.member_id
WHERE ps.id = $1
  AND ps.workspace_id = $2
  AND m.user_id = $3
  AND m.type = 'human'
`, trimmedStickerID, trimmedWorkspaceID, trimmedUserID)

	var download AttachmentDownload
	var storageDriver string
	var objectKey string
	if err := row.Scan(&download.Filename, &download.ContentType, &storageDriver, &objectKey, &download.Blob); err != nil {
		if coredb.IsNotFound(err) {
			return AttachmentDownload{}, ErrNotFound
		}
		return AttachmentDownload{}, fmt.Errorf("load personal sticker: %w", err)
	}

	// Personal stickers are fetched by the web composer via fetch(...).blob()
	// before the message send flow re-uploads them as sticker attachments.
	// Keeping this route same-origin avoids cross-origin CDN fetch/CORS failures.
	blob, err := txs.loadMediaObject(ctx, storageDriver, objectKey, download.Blob)
	if err != nil {
		return AttachmentDownload{}, err
	}
	download.Blob = blob
	return download, nil
}