package app

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

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

type appVisibilityChannelCleanup struct {
	WorkspaceID    string
	MemberID       string
	InstallationID string
	DefinitionID   string
	Now            time.Time
}

func executeAppVisibilityChannelCleanup(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	actorMemberID string,
	installation serviceactor.Installation,
	memberID string,
	now time.Time,
) ([]string, error) {
	cleanup, err := prepareAppVisibilityChannelCleanup(
		ctx, tx, workspaceID, actorMemberID, installation, memberID, now,
	)
	if err != nil {
		return nil, err
	}
	channelIDs, err := listAppVisibilityCleanupChannelIDs(ctx, tx, cleanup)
	if err != nil {
		return nil, err
	}
	// Do not return early when the parent membership is already absent. Binding
	// and subscription provenance is authoritative for repairing orphaned
	// channel-derived thread/task runtime on private -> private retries.
	if err := removeAppVisibilityChannelSubscriptions(ctx, tx, cleanup); err != nil {
		return nil, err
	}
	if err := disableAppVisibilityChannelBindings(ctx, tx, cleanup); err != nil {
		return nil, err
	}
	if err := deleteAppVisibilityChannelNotifications(ctx, tx, cleanup); err != nil {
		return nil, err
	}
	if err := removeAppVisibilityChannelMemberships(ctx, tx, cleanup); err != nil {
		return nil, err
	}
	return channelIDs, nil
}

func prepareAppVisibilityChannelCleanup(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	actorMemberID string,
	installation serviceactor.Installation,
	memberID string,
	now time.Time,
) (appVisibilityChannelCleanup, error) {
	workspaceID = strings.TrimSpace(workspaceID)
	actorMemberID = strings.TrimSpace(actorMemberID)
	memberID = strings.TrimSpace(memberID)
	installationID := strings.TrimSpace(installation.ID)
	definitionID := strings.TrimSpace(installation.ServiceDefinitionID)
	if workspaceID == "" || actorMemberID == "" || memberID == "" || installationID == "" || definitionID == "" {
		return appVisibilityChannelCleanup{}, badRequest("workspaceId, actor member, installation, and App member are required")
	}
	if strings.TrimSpace(installation.WorkspaceID) != workspaceID ||
		strings.TrimSpace(installation.ServiceKind) != serviceactor.KindApp ||
		strings.TrimSpace(installation.Status) != serviceactor.StatusActive {
		return appVisibilityChannelCleanup{}, ErrNotFound
	}
	current, _, found, isApp, err := loadActiveAppServiceInstallationByServiceMemberID(
		ctx, tx, workspaceID, memberID, false,
	)
	if err != nil {
		return appVisibilityChannelCleanup{}, err
	}
	if !found || !isApp ||
		strings.TrimSpace(current.ID) != installationID ||
		strings.TrimSpace(current.ServiceDefinitionID) != definitionID {
		return appVisibilityChannelCleanup{}, ErrNotFound
	}
	if now.IsZero() {
		now = time.Now().UTC()
	} else {
		now = now.UTC()
	}
	return appVisibilityChannelCleanup{
		WorkspaceID:    workspaceID,
		MemberID:       memberID,
		InstallationID: installationID,
		DefinitionID:   definitionID,
		Now:            now,
	}, nil
}

func listAppVisibilityCleanupChannelIDs(
	ctx context.Context,
	tx coredb.Tx,
	cleanup appVisibilityChannelCleanup,
) ([]string, error) {
	rows, err := tx.Query(ctx, `
SELECT channel.id
FROM scope_memberships membership
JOIN scopes channel
  ON channel.id = membership.scope_id
 AND channel.workspace_id = $1
 AND channel.type = 'channel'
WHERE membership.member_id = $2
  AND membership.visibility = 'public'
  AND membership.removed_at IS NULL
  AND membership.effective_to_seq IS NULL
ORDER BY channel.id ASC
`, cleanup.WorkspaceID, cleanup.MemberID)
	if err != nil {
		return nil, fmt.Errorf("list public App channel memberships for visibility cleanup: %w", err)
	}
	defer rows.Close()
	channelIDs := make([]string, 0)
	for rows.Next() {
		var channelID string
		if err := rows.Scan(&channelID); err != nil {
			return nil, fmt.Errorf("scan public App channel membership for visibility cleanup: %w", err)
		}
		channelIDs = append(channelIDs, channelID)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate public App channel memberships for visibility cleanup: %w", err)
	}
	channelIDs = normalizeNonEmptyStrings(channelIDs)
	sort.Strings(channelIDs)
	return channelIDs, nil
}

func removeAppVisibilityChannelSubscriptions(
	ctx context.Context,
	tx coredb.Tx,
	cleanup appVisibilityChannelCleanup,
) error {
	if _, err := tx.Exec(ctx, `
UPDATE service_event_subscriptions subscription
SET subscription.active_subscription_key = NULL,
    subscription.removed_at = $4,
    subscription.updated_at = $4
WHERE subscription.workspace_id = $1
  AND subscription.service_installation_id = $2
  AND subscription.service_definition_id = $3
  AND subscription.service_kind = 'app'
  AND subscription.removed_at IS NULL
  AND EXISTS (
      SELECT 1
      FROM scopes permission_scope
      WHERE permission_scope.id = subscription.permission_check_scope_id
        AND permission_scope.workspace_id = $1
        AND permission_scope.type = 'channel'
  )
`, cleanup.WorkspaceID, cleanup.InstallationID, cleanup.DefinitionID, cleanup.Now); err != nil {
		return fmt.Errorf("remove public App channel subscriptions for visibility cleanup: %w", err)
	}
	return nil
}

func disableAppVisibilityChannelBindings(
	ctx context.Context,
	tx coredb.Tx,
	cleanup appVisibilityChannelCleanup,
) error {
	if _, err := tx.Exec(ctx, `
UPDATE service_target_bindings binding
SET binding.status = 'disabled',
    binding.disabled_reason = 'catalog_visibility_private',
    binding.active_binding_key = NULL,
    binding.updated_at = $4
WHERE binding.workspace_id = $1
  AND binding.service_installation_id = $2
  AND binding.service_definition_id = $3
  AND binding.service_kind = 'app'
  AND binding.removed_at IS NULL
  AND EXISTS (
      SELECT 1
      FROM scopes permission_scope
      WHERE permission_scope.id = binding.permission_check_scope_id
        AND permission_scope.workspace_id = $1
        AND permission_scope.type = 'channel'
  )
`, cleanup.WorkspaceID, cleanup.InstallationID, cleanup.DefinitionID, cleanup.Now); err != nil {
		return fmt.Errorf("disable public App channel bindings for visibility cleanup: %w", err)
	}
	return nil
}

func deleteAppVisibilityChannelNotifications(
	ctx context.Context,
	tx coredb.Tx,
	cleanup appVisibilityChannelCleanup,
) error {
	if _, err := tx.Exec(ctx, `
DELETE notification
FROM scope_notification_settings notification
WHERE notification.member_id = $2
  AND (
      EXISTS (
          SELECT 1
          FROM scopes channel
          WHERE channel.id = notification.scope_id
            AND channel.workspace_id = $1
            AND channel.type = 'channel'
      )
      OR EXISTS (
          SELECT 1
          FROM service_target_bindings binding
          JOIN scopes permission_scope
            ON permission_scope.id = binding.permission_check_scope_id
           AND permission_scope.workspace_id = binding.workspace_id
           AND permission_scope.type = 'channel'
          WHERE binding.workspace_id = $1
            AND binding.service_installation_id = $3
            AND binding.service_definition_id = $4
            AND binding.service_kind = 'app'
            AND binding.delivery_scope_id = notification.scope_id
      )
  )
`, cleanup.WorkspaceID, cleanup.MemberID, cleanup.InstallationID, cleanup.DefinitionID); err != nil {
		return fmt.Errorf("delete public App channel notification settings for visibility cleanup: %w", err)
	}
	return nil
}

func removeAppVisibilityChannelMemberships(
	ctx context.Context,
	tx coredb.Tx,
	cleanup appVisibilityChannelCleanup,
) error {
	if _, err := tx.Exec(ctx, `
UPDATE scope_memberships membership
JOIN scopes membership_scope
  ON membership_scope.id = membership.scope_id
 AND membership_scope.workspace_id = $1
SET membership.unread_generation = membership.unread_generation + 1,
    membership.removed_at = $3,
    membership.updated_at = $3
WHERE membership.member_id = $2
  AND membership.visibility = 'public'
  AND membership.removed_at IS NULL
  AND membership.effective_to_seq IS NULL
  AND (
      membership_scope.type = 'channel'
      OR EXISTS (
          SELECT 1
          FROM service_target_bindings binding
          JOIN scopes permission_scope
            ON permission_scope.id = binding.permission_check_scope_id
           AND permission_scope.workspace_id = binding.workspace_id
           AND permission_scope.type = 'channel'
          WHERE binding.workspace_id = $1
            AND binding.service_installation_id = $4
            AND binding.service_definition_id = $5
            AND binding.service_kind = 'app'
            AND binding.delivery_scope_id = membership.scope_id
      )
  )
`, cleanup.WorkspaceID, cleanup.MemberID, cleanup.Now, cleanup.InstallationID, cleanup.DefinitionID); err != nil {
		return fmt.Errorf("remove public App channel-derived memberships for visibility cleanup: %w", err)
	}
	return nil
}