package attachment

import (
	"context"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/objectstorage"
	"github.com/tidbcloud/AgentTeam/internal/store/testfixture"
)

func mustExec(t *testing.T, queryer coredb.QueryExecer, query string, args ...any) {
	t.Helper()

	_, err := queryer.Exec(context.Background(), query, args...)
	require.NoError(t, err)
}

func TestDeleteUnclaimedAttachmentsBeforeDeletesOnlyStaleUnclaimedRows(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startAttachmentTestPool(t, ctx)

	fakeMedia := &fakeMediaStorage{driver: objectstorage.DriverOSS}
	store := New(db, nil, fakeMedia, 30, Options{})
	now := time.Now().UTC()
	cutoff := now.Add(-24 * time.Hour)
	staleCreatedAt := now.Add(-48 * time.Hour)
	recentCreatedAt := now.Add(-time.Hour)
	payload := []byte{0x00, 0x01, 0x02, 0x03}

	const (
		workspaceID         = "wsp_attachment_gc"
		channelID           = "chn_attachment_gc"
		memberID            = "mem_attachment_gc"
		claimedMessageID    = "msg_attachment_gc"
		staleAttachmentID   = "att_stale_unclaimed"
		recentAttachmentID  = "att_recent_unclaimed"
		claimedAttachmentID = "att_claimed"
		staleObjectKey      = "attachments/wsp_attachment_gc/att_stale_unclaimed"
		recentObjectKey     = "attachments/wsp_attachment_gc/att_recent_unclaimed"
		claimedObjectKey    = "attachments/wsp_attachment_gc/att_claimed"
		staleDerivativeKey  = "attachment-derivatives/wsp_attachment_gc/att_stale_unclaimed/thumb-v1.jpg"
	)

	mustExec(t, db, `
INSERT INTO workspaces (id, organization_id, slug, name, latest_message_seq, created_at, updated_at)
VALUES ($1, $2, 'attachment-gc', 'attachment-gc', 1, $3, $3)
`, workspaceID, testfixture.SeedWorkspaceOrganization(t, ctx, db, workspaceID, now), now)
	mustExec(t, db, `
INSERT INTO members (id, workspace_id, type, display_name, created_at, updated_at)
VALUES ($1, $2, 'agent', 'Agent', $3, $3)
`, memberID, workspaceID, now)
	mustExec(t, db, `
INSERT INTO scopes (
  id, workspace_id, type, identity_key, latest_seq, latest_global_seq,
  latest_message_at, status, created_by_member_id, created_at, updated_at
) VALUES ($1, $2, 'channel', $3, 1, 1, $4, 'active', $5, $4, $4)
`, channelID, workspaceID, "channel:attachment-gc", now, memberID)
	mustExec(t, db, `
INSERT INTO scope_messages (
    id, workspace_id, scope_id, seq, workspace_global_seq, author_member_id,
    author_type, message_type, content, metadata, created_at, updated_at
)
VALUES ($1, $2, $3, 1, 1, $4, 'agent', '', 'hello', NULL, $5, $5)
`, claimedMessageID, workspaceID, channelID, memberID, now)
	mustExec(t, db, `
INSERT INTO attachments (id, workspace_id, scope_id, uploaded_by_member_id, filename, content_type, size_bytes, storage_driver, object_key, payload, created_at)
VALUES ($1, $2, $3, $4, 'stale.png', 'image/png', 4, $5, $6, $7, $8)
`, staleAttachmentID, workspaceID, channelID, memberID, objectstorage.DriverOSS, staleObjectKey, payload, staleCreatedAt)
	mustExec(t, db, `
INSERT INTO attachments (id, workspace_id, scope_id, uploaded_by_member_id, filename, content_type, size_bytes, storage_driver, object_key, payload, created_at)
VALUES ($1, $2, $3, $4, 'recent.png', 'image/png', 4, $5, $6, $7, $8)
`, recentAttachmentID, workspaceID, channelID, memberID, objectstorage.DriverOSS, recentObjectKey, payload, recentCreatedAt)
	mustExec(t, db, `
INSERT INTO attachments (id, workspace_id, scope_id, uploaded_by_member_id, message_id, filename, content_type, size_bytes, storage_driver, object_key, payload, created_at)
VALUES ($1, $2, $3, $4, $5, 'claimed.png', 'image/png', 4, $6, $7, $8, $9)
`, claimedAttachmentID, workspaceID, channelID, memberID, claimedMessageID, objectstorage.DriverOSS, claimedObjectKey, payload, staleCreatedAt)

	deletedCount, err := store.DeleteUnclaimedAttachmentsBefore(ctx, cutoff, 10)
	require.NoError(t, err)
	require.EqualValues(t, 1, deletedCount)

	var staleExists bool
	require.NoError(t, db.QueryRow(ctx, `SELECT EXISTS (SELECT 1 FROM attachments WHERE id = $1)`, staleAttachmentID).Scan(&staleExists))
	require.False(t, staleExists)

	var recentExists bool
	require.NoError(t, db.QueryRow(ctx, `SELECT EXISTS (SELECT 1 FROM attachments WHERE id = $1)`, recentAttachmentID).Scan(&recentExists))
	require.True(t, recentExists)

	var claimedExists bool
	require.NoError(t, db.QueryRow(ctx, `SELECT EXISTS (SELECT 1 FROM attachments WHERE id = $1)`, claimedAttachmentID).Scan(&claimedExists))
	require.True(t, claimedExists)
	// GC sweeps every supported derivative version: the active thumb-v2 and
	// the superseded thumb-v1 objects still lingering in storage.
	require.Equal(t, []string{
		staleObjectKey,
		"attachment-derivatives/wsp_attachment_gc/att_stale_unclaimed/thumb-v2.jpg",
		staleDerivativeKey,
	}, fakeMedia.deleted)
}

func TestDeleteUnclaimedAttachmentsBeforeSkipsAttachmentsBeingClaimed(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startAttachmentTestPool(t, ctx)

	fakeMedia := &fakeMediaStorage{driver: objectstorage.DriverOSS}
	store := New(db, nil, fakeMedia, 30, Options{})
	now := time.Now().UTC()
	cutoff := now.Add(-24 * time.Hour)
	staleCreatedAt := now.Add(-48 * time.Hour)
	payload := []byte{0x00, 0x01, 0x02, 0x03}

	const (
		workspaceID       = "wsp_attachment_gc_claim"
		channelID         = "chn_attachment_gc_claim"
		memberID          = "mem_attachment_gc_claim"
		claimedMessageID  = "msg_attachment_gc_claim"
		staleAttachmentID = "att_stale_claiming"
		staleObjectKey    = "attachments/wsp_attachment_gc_claim/att_stale_claiming"
	)

	mustExec(t, db, `
INSERT INTO workspaces (id, organization_id, slug, name, latest_message_seq, created_at, updated_at)
VALUES ($1, $2, 'attachment-gc-claim', 'attachment-gc-claim', 1, $3, $3)
`, workspaceID, testfixture.SeedWorkspaceOrganization(t, ctx, db, workspaceID, now), now)
	mustExec(t, db, `
INSERT INTO members (id, workspace_id, type, display_name, created_at, updated_at)
VALUES ($1, $2, 'agent', 'Agent', $3, $3)
`, memberID, workspaceID, now)
	mustExec(t, db, `
INSERT INTO scopes (
  id, workspace_id, type, identity_key, latest_seq, latest_global_seq,
  latest_message_at, status, created_by_member_id, created_at, updated_at
) VALUES ($1, $2, 'channel', $3, 1, 1, $4, 'active', $5, $4, $4)
`, channelID, workspaceID, "channel:attachment-gc-claim", now, memberID)
	mustExec(t, db, `
INSERT INTO scope_messages (
    id, workspace_id, scope_id, seq, workspace_global_seq, author_member_id,
    author_type, message_type, content, metadata, created_at, updated_at
)
VALUES ($1, $2, $3, 1, 1, $4, 'agent', '', 'hello', NULL, $5, $5)
`, claimedMessageID, workspaceID, channelID, memberID, now)
	mustExec(t, db, `
INSERT INTO attachments (id, workspace_id, scope_id, uploaded_by_member_id, filename, content_type, size_bytes, storage_driver, object_key, payload, created_at)
VALUES ($1, $2, $3, $4, 'stale.png', 'image/png', 4, $5, $6, $7, $8)
`, staleAttachmentID, workspaceID, channelID, memberID, objectstorage.DriverOSS, staleObjectKey, payload, staleCreatedAt)

	claimTx, err := db.BeginTx(ctx, coredb.TxOptions{})
	require.NoError(t, err)
	t.Cleanup(func() {
		_ = claimTx.Rollback(ctx)
	})
	_, err = claimTx.Exec(ctx, `
UPDATE attachments
SET message_id = $2
WHERE id = $1
`, staleAttachmentID, claimedMessageID)
	require.NoError(t, err)

	deletedCount, err := store.DeleteUnclaimedAttachmentsBefore(ctx, cutoff, 10)
	require.NoError(t, err)
	require.Zero(t, deletedCount)

	require.NoError(t, claimTx.Commit(ctx))

	var messageID *string
	require.NoError(t, db.QueryRow(ctx, `SELECT message_id FROM attachments WHERE id = $1`, staleAttachmentID).Scan(&messageID))
	require.NotNil(t, messageID)
	require.Equal(t, claimedMessageID, *messageID)
	require.Empty(t, fakeMedia.deleted)
}

func TestScopeAttachmentRowsKeepMessageTaskAndUnclaimedRefs(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	db := startAttachmentTestPool(t, ctx)
	now := time.Now().UTC()
	payload := []byte{0x00, 0x01, 0x02, 0x03}

	const (
		workspaceID         = "wsp_attachment_scope_refs"
		channelID           = "chn_attachment_scope_refs"
		memberID            = "mem_attachment_scope_refs"
		messageID           = "msg_attachment_scope_refs"
		boardID             = "tb_attachment_scope_refs"
		taskID              = "tsk_attachment_scope_refs"
		taskDiscussionID    = "msg_attachment_scope_refs_task"
		messageAttachmentID = "att_scope_message_ref"
		taskAttachmentID    = "att_scope_task_ref"
		unclaimedID         = "att_scope_unclaimed_ref"
	)

	mustExec(t, db, `
INSERT INTO workspaces (id, organization_id, slug, name, latest_message_seq, created_at, updated_at)
VALUES ($1, $2, 'attachment-scope-refs', 'attachment-scope-refs', 1, $3, $3)
`, workspaceID, testfixture.SeedWorkspaceOrganization(t, ctx, db, workspaceID, now), now)
	mustExec(t, db, `
INSERT INTO members (id, workspace_id, type, display_name, created_at, updated_at)
VALUES ($1, $2, 'agent', 'Agent', $3, $3)
`, memberID, workspaceID, now)
	mustExec(t, db, `
INSERT INTO scopes (
  id, workspace_id, type, identity_key, latest_seq, latest_global_seq,
  latest_message_at, status, created_by_member_id, created_at, updated_at
) VALUES ($1, $2, 'channel', $3, 1, 1, $4, 'active', $5, $4, $4)
`, channelID, workspaceID, "channel:attachment-scope-refs", now, memberID)
	mustExec(t, db, `
INSERT INTO scope_messages (
    id, workspace_id, scope_id, seq, workspace_global_seq, author_member_id,
    author_type, message_type, content, metadata, created_at, updated_at
)
VALUES ($1, $2, $3, 1, 1, $4, 'agent', '', 'hello', NULL, $5, $5)
`, messageID, workspaceID, channelID, memberID, now)
	mustExec(t, db, `
INSERT INTO task_boards (channel_id, workspace_id, next_task_number, created_at, updated_at, id)
VALUES ($1, $2, 2, $3, $3, $4)
`, channelID, workspaceID, now, boardID)
	mustExec(t, db, `
INSERT INTO scopes (id, workspace_id, type, identity_key, status, created_by_member_id, created_at, updated_at)
VALUES ($1, $2, 'task', CONCAT('scope/task/', $1), 'active', $3, $4, $4)
`, taskID, workspaceID, memberID, now)
	mustExec(t, db, `
INSERT INTO tasks (id, task_scope_id, board_id, channel_id, task_number, title, status, created_by_member_id, created_by_ref, created_at, updated_at)
VALUES ($1, $1, $2, $3, 1, 'attachment task', 'open', $4, $4, $5, $5)
`, taskID, boardID, channelID, memberID, now)
	mustExec(t, db, `
INSERT INTO scope_messages (
    id, workspace_id, scope_id, seq, workspace_global_seq, author_member_id,
    author_type, message_type, content, metadata, created_at, updated_at
)
VALUES ($1, $2, $3, 1, 2, $4, 'agent', 'task_discussion', 'attachment discussion', NULL, $5, $5)
`, taskDiscussionID, workspaceID, taskID, memberID, now)
	mustExec(t, db, `
INSERT INTO attachments (id, workspace_id, scope_id, uploaded_by_member_id, message_id, filename, content_type, size_bytes, payload, created_at)
VALUES ($1, $2, $3, $4, $5, 'message.png', 'image/png', 4, $6, $7)
`, messageAttachmentID, workspaceID, channelID, memberID, messageID, payload, now)
	mustExec(t, db, `
INSERT INTO attachments (id, workspace_id, scope_id, uploaded_by_member_id, message_id, filename, content_type, size_bytes, payload, created_at)
VALUES ($1, $2, $3, $4, $5, 'task.png', 'image/png', 4, $6, $7)
`, taskAttachmentID, workspaceID, taskID, memberID, taskDiscussionID, payload, now)
	mustExec(t, db, `
INSERT INTO attachments (id, workspace_id, scope_id, uploaded_by_member_id, filename, content_type, size_bytes, payload, created_at)
VALUES ($1, $2, $3, $4, 'unclaimed.png', 'image/png', 4, $5, $6)
`, unclaimedID, workspaceID, channelID, memberID, payload, now)

	requireSingleAttachmentCount(t, ctx, db, 1, `
SELECT COUNT(*)
FROM attachments a
JOIN scope_messages sm
  ON sm.id = a.message_id
 AND sm.scope_id = a.scope_id
WHERE a.id = $1
`, messageAttachmentID)
	requireSingleAttachmentCount(t, ctx, db, 1, `
SELECT COUNT(*)
FROM attachments a
JOIN scope_messages sm
  ON sm.id = a.message_id
 AND sm.scope_id = a.scope_id
WHERE a.id = $1
  AND a.scope_id = $2
  AND sm.message_type = 'task_discussion'
`, taskAttachmentID, taskID)
	requireSingleAttachmentCount(t, ctx, db, 1, `
SELECT COUNT(*)
FROM attachments
WHERE id = $1
  AND message_id IS NULL
  AND scope_id = $2
`, unclaimedID, channelID)

	store := New(db, nil, nil, 30, Options{})
	claimed, err := store.ClaimAttachmentsForMessage(ctx, workspaceID, channelID, memberID, messageID, []string{unclaimedID})
	require.NoError(t, err)
	require.Len(t, claimed, 1)
	require.Equal(t, unclaimedID, claimed[0].ID)
	requireSingleAttachmentCount(t, ctx, db, 1, `
SELECT COUNT(*)
FROM attachments
WHERE id = $1
  AND message_id = $2
`, unclaimedID, messageID)

	_, err = store.ClaimAttachmentsForMessage(ctx, workspaceID, channelID, memberID, messageID, []string{taskAttachmentID})
	require.ErrorIs(t, err, ErrNotFound)
	requireSingleAttachmentCount(t, ctx, db, 1, `
SELECT COUNT(*)
FROM attachments
WHERE id = $1
  AND message_id = $2
  AND scope_id = $3
`, taskAttachmentID, taskDiscussionID, taskID)
}

func requireSingleAttachmentCount(t *testing.T, ctx context.Context, db coredb.Queryer, want int64, query string, args ...any) {
	t.Helper()

	var got int64
	require.NoError(t, db.QueryRow(ctx, query, args...).Scan(&got))
	require.Equal(t, want, got)
}