package app

import (
	"context"
	"database/sql"
	"fmt"
	"strconv"
	"strings"

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

const (
	serviceEventSubscriptionListDefaultLimit = 20
	serviceEventSubscriptionListMaxLimit     = 100
)

func pullRequestSubscriptionServiceDefinitionID(provider string) string {
	appID, _ := pullRequestSubscriptionProviderAppID(provider)
	return appID
}

func pullRequestSubscriptionProviderServiceDefinitionIDs() []string {
	return []string{
		pullRequestSubscriptionServiceDefinitionID(PullRequestSubscriptionProviderGitHub),
		pullRequestSubscriptionServiceDefinitionID(PullRequestSubscriptionProviderGitea),
	}
}

func pullRequestSubscriptionProviderFromServiceDefinitionID(serviceDefinitionID string) string {
	switch strings.TrimSpace(serviceDefinitionID) {
	case "app_github":
		return PullRequestSubscriptionProviderGitHub
	case "app_gitea":
		return PullRequestSubscriptionProviderGitea
	default:
		return ""
	}
}

func pullRequestSubscriptionServiceDefinitionForInstallation(ctx context.Context, queryer coredb.QueryRower, workspaceID string, installationID string) (string, error) {
	row := queryer.QueryRow(ctx, `
SELECT service_definition_id
FROM service_installations
WHERE workspace_id = $1
  AND id = $2
  AND service_kind = $3
LIMIT 1
`, strings.TrimSpace(workspaceID), strings.TrimSpace(installationID), serviceactor.KindApp)
	var serviceDefinitionID string
	if err := row.Scan(&serviceDefinitionID); err != nil {
		if coredb.IsNotFound(err) {
			return "", storeerr.NotFound("app_not_installed")
		}
		return "", fmt.Errorf("load pull request subscription service definition: %w", err)
	}
	if pullRequestSubscriptionProviderFromServiceDefinitionID(serviceDefinitionID) == "" {
		return "", storeerr.BadRequest("app_does_not_support_pull_request_subscription")
	}
	return strings.TrimSpace(serviceDefinitionID), nil
}

func normalizeServiceEventSubscriptionListLimit(limit int) int {
	if limit <= 0 {
		return serviceEventSubscriptionListDefaultLimit
	}
	if limit > serviceEventSubscriptionListMaxLimit {
		return serviceEventSubscriptionListMaxLimit
	}
	return limit
}

func normalizeServiceEventSubscriptionListSQLLimit(limit int, page bool) int {
	if !page {
		return normalizeServiceEventSubscriptionListLimit(limit)
	}
	if limit <= 0 {
		return serviceEventSubscriptionListDefaultLimit + 1
	}
	if limit > serviceEventSubscriptionListMaxLimit+1 {
		return serviceEventSubscriptionListMaxLimit + 1
	}
	return limit
}

func normalizedServiceEventSubscriptionListOffset(offset int) int {
	if offset < 0 {
		return 0
	}
	return offset
}

func parseServiceEventSubscriptionListCursor(raw string) (int, error) {
	trimmed := strings.TrimSpace(raw)
	if trimmed == "" {
		return 0, nil
	}
	offset, err := strconv.Atoi(trimmed)
	if err != nil || offset < 0 {
		return 0, badRequest("invalid cursor")
	}
	return offset, nil
}

type listServiceEventSubscriptionsQueryParams struct {
	WorkspaceID           string
	ServiceDefinitionIDs  []string
	ServiceInstallationID string
	SubscriptionKind      string
	TargetKind            string
	TargetRef             string
	Status                string
	ResourceKey           string
	OwnerMemberID         string
	OwnerUserID           string
	WorkspaceWide         bool
	RequestedLimit        int
	Offset                int
	Page                  bool
}

func buildListServiceEventSubscriptionsQuery(params listServiceEventSubscriptionsQueryParams) (string, []any) {
	conditions := []string{"subscription.workspace_id = $1", "subscription.removed_at IS NULL"}
	args := []any{strings.TrimSpace(params.WorkspaceID)}
	conditions = append(conditions, serviceEventSubscriptionSQLInCondition("subscription.service_definition_id", params.ServiceDefinitionIDs, &args))
	if strings.TrimSpace(params.ServiceInstallationID) != "" {
		args = append(args, strings.TrimSpace(params.ServiceInstallationID))
		conditions = append(conditions, fmt.Sprintf("subscription.service_installation_id = $%d", len(args)))
	}
	if strings.TrimSpace(params.SubscriptionKind) != "" {
		args = append(args, strings.TrimSpace(params.SubscriptionKind))
		conditions = append(conditions, fmt.Sprintf("subscription.subscription_kind = $%d", len(args)))
	}
	if strings.TrimSpace(params.TargetKind) != "" {
		args = append(args, strings.TrimSpace(params.TargetKind))
		conditions = append(conditions, fmt.Sprintf("subscription.target_kind = $%d", len(args)))
	}
	if strings.TrimSpace(params.TargetRef) != "" {
		args = append(args, strings.TrimSpace(params.TargetRef))
		conditions = append(conditions, fmt.Sprintf("subscription.target_ref = $%d", len(args)))
	}
	if strings.TrimSpace(params.Status) != "" {
		args = append(args, strings.TrimSpace(params.Status))
		conditions = append(conditions, fmt.Sprintf("subscription.status = $%d", len(args)))
	}
	if strings.TrimSpace(params.ResourceKey) != "" {
		args = append(args, strings.TrimSpace(params.ResourceKey))
		conditions = append(conditions, fmt.Sprintf("subscription.external_resource_key = $%d", len(args)))
	}
	if params.WorkspaceWide {
		// Workspace owners/admins intentionally have a workspace-wide management
		// view. Do not add a creator predicate for this case.
	} else if strings.TrimSpace(params.OwnerMemberID) == "" {
		conditions = append(conditions, "1 = 0")
	} else {
		args = append(args, strings.TrimSpace(params.OwnerMemberID))
		ownerMemberPlaceholder := fmt.Sprintf("$%d", len(args))
		if strings.TrimSpace(params.OwnerUserID) == "" {
			conditions = append(conditions, "subscription.owner_member_id = "+ownerMemberPlaceholder)
		} else {
			args = append(args, strings.TrimSpace(params.OwnerUserID))
			ownerUserPlaceholder := fmt.Sprintf("$%d", len(args))
			args = append(args, serviceEventSubscriptionOwnerMemberIDSourceAgentMachineV1)
			canonicalAgentOwnerMarkerPlaceholder := fmt.Sprintf("$%d", len(args))
			// A live creator agent is always scoped through its backing machine.
			// Physical agent deletion removes that relation, so only rows carrying
			// the server-owned ownership marker may fall back to owner_member_id.
			// Unmarked legacy rows fail closed.
			conditions = append(conditions, fmt.Sprintf(`(
    (
      subscription.created_by_actor_type <> 'agent'
      AND subscription.owner_member_id = %s
    )
    OR (
      subscription.created_by_actor_type = 'agent'
      AND (
        EXISTS (
          SELECT 1
          FROM agents creator_agent
          JOIN machines creator_machine
            ON creator_machine.workspace_id = creator_agent.workspace_id
           AND creator_machine.id = creator_agent.machine_id
          WHERE creator_agent.workspace_id = subscription.workspace_id
            AND creator_agent.id = subscription.created_by_actor_id
            AND creator_machine.created_by_user_id = %s
        )
        OR (
          NOT EXISTS (
            SELECT 1
            FROM agents existing_creator_agent
            WHERE existing_creator_agent.workspace_id = subscription.workspace_id
              AND existing_creator_agent.id = subscription.created_by_actor_id
          )
          AND COALESCE(
            JSON_UNQUOTE(JSON_EXTRACT(
              CASE WHEN JSON_VALID(subscription.metadata) THEN subscription.metadata ELSE JSON_OBJECT() END,
              '$.%s'
            )),
            ''
          ) = %s
          AND subscription.owner_member_id = %s
        )
      )
    )
  )`, ownerMemberPlaceholder, ownerUserPlaceholder, serviceEventSubscriptionOwnerMemberIDSourceMetadataKey, canonicalAgentOwnerMarkerPlaceholder, ownerMemberPlaceholder))
		}
	}
	args = append(args, normalizeServiceEventSubscriptionListSQLLimit(params.RequestedLimit, params.Page))
	limitPlaceholder := fmt.Sprintf("$%d", len(args))
	args = append(args, normalizedServiceEventSubscriptionListOffset(params.Offset))
	offsetPlaceholder := fmt.Sprintf("$%d", len(args))
	return serviceEventSubscriptionRecordSelectSQL() + `
WHERE ` + strings.Join(conditions, " AND ") + `
ORDER BY subscription.updated_at DESC, subscription.id ASC
LIMIT ` + limitPlaceholder + ` OFFSET ` + offsetPlaceholder, args
}

func serviceEventSubscriptionSQLInCondition(column string, values []string, args *[]any) string {
	placeholders := make([]string, 0, len(values))
	for _, value := range values {
		trimmed := strings.TrimSpace(value)
		if trimmed == "" {
			continue
		}
		*args = append(*args, trimmed)
		placeholders = append(placeholders, fmt.Sprintf("$%d", len(*args)))
	}
	if len(placeholders) == 0 {
		return "1 = 0"
	}
	return fmt.Sprintf("%s IN (%s)", column, strings.Join(placeholders, ", "))
}

func serviceEventSubscriptionRecordSelectSQL() string {
	return `
SELECT subscription.id, subscription.workspace_id, COALESCE(owner_member.user_id, ''),
       subscription.service_definition_id, subscription.service_installation_id,
       subscription.service_target_binding_id, subscription.service_connection_ref,
       subscription.target_kind, subscription.target_ref,
       subscription.delivery_scope_id, subscription.permission_check_scope_id,
       subscription.external_resource_type, subscription.external_resource_ref,
       subscription.external_resource_key, subscription.event_filters,
       subscription.status, subscription.paused_reason, subscription.owner_member_id,
       subscription.created_by_actor_type, subscription.created_by_actor_id,
       subscription.authorizer_member_id, subscription.delivery_policy,
       subscription.notify_principal, subscription.created_via, subscription.metadata,
       subscription.created_at, subscription.updated_at, subscription.paused_at,
       subscription.removed_at, subscription.last_delivery_at, subscription.last_failure_at,
       COALESCE(subscription.last_failure_code, ''), COALESCE(subscription.last_failure_detail, ''),
       COALESCE(binding.authority_subject_member_id, ''), binding.delivery_contract,
       binding.scope_snapshot
FROM service_event_subscriptions subscription
LEFT JOIN service_target_bindings binding
  ON binding.id = subscription.service_target_binding_id
LEFT JOIN members owner_member
  ON owner_member.id = subscription.owner_member_id
 AND owner_member.type = 'human'`
}

func scanServiceEventSubscriptionRecord(row coredb.Row) (ServiceEventSubscriptionRecord, error) {
	var subscription ServiceEventSubscriptionRecord
	var pausedAt, removedAt, lastEventAt, lastFailureAt sql.NullTime
	if err := row.Scan(
		&subscription.ID,
		&subscription.WorkspaceID,
		&subscription.OwnerUserID,
		&subscription.AppID,
		&subscription.AppInstallationID,
		&subscription.AppBindingID,
		&subscription.ProviderConnectionRef,
		&subscription.TargetKind,
		&subscription.TargetRef,
		&subscription.DeliveryScopeID,
		&subscription.PermissionCheckScopeID,
		&subscription.ResourceKind,
		&subscription.ResourceRef,
		&subscription.ResourceKey,
		&subscription.EventFilters,
		&subscription.Status,
		&subscription.PausedReason,
		&subscription.CreatedByMemberID,
		&subscription.CreatedByActorType,
		&subscription.CreatedByActorID,
		&subscription.AuthorizerUserID,
		&subscription.DeliveryPolicy,
		&subscription.NotifyPrincipal,
		&subscription.CreatedVia,
		&subscription.Metadata,
		&subscription.CreatedAt,
		&subscription.UpdatedAt,
		&pausedAt,
		&removedAt,
		&lastEventAt,
		&lastFailureAt,
		&subscription.LastFailureCode,
		&subscription.LastFailureMessage,
		&subscription.AuthoritySubjectMemberID,
		&subscription.DeliveryContract,
		&subscription.ScopeSnapshot,
	); err != nil {
		return ServiceEventSubscriptionRecord{}, err
	}
	subscription.TriggerID = strings.TrimSpace(subscription.ID)
	subscription.Provider = pullRequestSubscriptionProviderFromServiceDefinitionID(subscription.AppID)
	subscription.EventFilters = defaultPullRequestSubscriptionFiltersPayload(subscription.EventFilters)
	if strings.TrimSpace(subscription.ResourceKind) == "" {
		subscription.ResourceKind = PullRequestSubscriptionResourcePullRequest
	}
	if strings.TrimSpace(subscription.DeliveryPolicy) == "" {
		subscription.DeliveryPolicy = pullRequestSubscriptionDeliveryPolicy
	}
	if pausedAt.Valid {
		subscription.PausedAt = &pausedAt.Time
	}
	if removedAt.Valid {
		subscription.RemovedAt = &removedAt.Time
	}
	if lastEventAt.Valid {
		subscription.LastEventAt = &lastEventAt.Time
	}
	if lastFailureAt.Valid {
		subscription.LastFailureAt = &lastFailureAt.Time
	}
	return subscription, nil
}

func defaultPullRequestSubscriptionFiltersPayload(payload []byte) []byte {
	if len(payload) == 0 {
		return []byte(`{}`)
	}
	return payload
}

func loadActiveServiceEventSubscriptionByKeyForUpdate(ctx context.Context, queryer coredb.Queryer, workspaceID string, activeSubscriptionKey string) (ServiceEventSubscriptionRecord, bool, error) {
	row := queryer.QueryRow(ctx, serviceEventSubscriptionRecordSelectSQL()+`
WHERE subscription.workspace_id = $1
  AND subscription.active_subscription_key = $2
  AND subscription.removed_at IS NULL
  AND subscription.subscription_kind = $3
  AND subscription.service_definition_id IN ($4, $5)
LIMIT 1
FOR UPDATE
`, strings.TrimSpace(workspaceID), strings.TrimSpace(activeSubscriptionKey), serviceactor.SubscriptionKindPullRequest, pullRequestSubscriptionServiceDefinitionID(PullRequestSubscriptionProviderGitHub), pullRequestSubscriptionServiceDefinitionID(PullRequestSubscriptionProviderGitea))
	subscription, err := scanServiceEventSubscriptionRecord(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return ServiceEventSubscriptionRecord{}, false, nil
		}
		return ServiceEventSubscriptionRecord{}, false, fmt.Errorf("load active service event subscription by key: %w", err)
	}
	return subscription, true, nil
}

func loadServiceEventSubscriptionByID(ctx context.Context, queryer coredb.QueryRower, workspaceID string, subscriptionID string) (ServiceEventSubscriptionRecord, error) {
	row := queryer.QueryRow(ctx, serviceEventSubscriptionRecordSelectSQL()+`
WHERE subscription.workspace_id = $1
  AND subscription.id = $2
  AND subscription.service_definition_id IN ($3, $4)
  AND subscription.subscription_kind = $5
`, workspaceID, subscriptionID, pullRequestSubscriptionServiceDefinitionID(PullRequestSubscriptionProviderGitHub), pullRequestSubscriptionServiceDefinitionID(PullRequestSubscriptionProviderGitea), serviceactor.SubscriptionKindPullRequest)
	return scanServiceEventSubscriptionRecord(row)
}

func loadServiceEventSubscriptionForUpdate(ctx context.Context, queryer coredb.Queryer, workspaceID string, subscriptionID string) (ServiceEventSubscriptionRecord, error) {
	row := queryer.QueryRow(ctx, serviceEventSubscriptionRecordSelectSQL()+`
WHERE subscription.workspace_id = $1
  AND subscription.id = $2
  AND subscription.service_definition_id IN ($3, $4)
  AND subscription.subscription_kind = $5
  AND subscription.removed_at IS NULL
FOR UPDATE
`, workspaceID, subscriptionID, pullRequestSubscriptionServiceDefinitionID(PullRequestSubscriptionProviderGitHub), pullRequestSubscriptionServiceDefinitionID(PullRequestSubscriptionProviderGitea), serviceactor.SubscriptionKindPullRequest)
	subscription, err := scanServiceEventSubscriptionRecord(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return ServiceEventSubscriptionRecord{}, storeerr.ErrNotFound
		}
		return ServiceEventSubscriptionRecord{}, err
	}
	return subscription, nil
}