package app

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

	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
	"github.com/tidbcloud/AgentTeam/internal/store/provider"
)

// serviceEventDeliveryCandidate is the bounded DB projection that bridges a
// normalized provider webhook event to one active subscription plus its delivery
// binding.
type serviceEventDeliveryCandidate struct {
	Subscription           ServiceEventSubscriptionRecord
	Binding                serviceEventDeliveryTargetBinding
	ServiceEventDeliveryID string
}

type serviceEventDeliveryTargetBinding struct {
	ID                       string
	WorkspaceID              string
	TargetKind               string
	TargetRef                string
	DeliveryScopeID          string
	PermissionCheckScopeID   string
	AuthoritySubjectMemberID string
	DeliveryContract         []byte
	ServiceDefinitionID      string
	ServiceInstallationID    string
	// ServiceMemberID is the canonical service_members row id. The visible
	// workspace member projection is kept separately so delivery code never
	// has to guess which identity shape it received.
	ServiceMemberID        string
	ServiceVisibleMemberID string
	Status                 string
	DisabledReason         string
	CreatedByMemberID      string
	CreatedByActorType     string
	CreatedByActorID       string
	CreatedVia             string
	ScopeSnapshot          []byte
	Metadata               []byte
	CreatedAt              time.Time
	UpdatedAt              time.Time
}

const serviceEventDeliveryCandidateSelectSQL = `
SELECT
    subscription.id, subscription.workspace_id,
    subscription.service_installation_id, subscription.service_member_id,
    JSON_OBJECT(
      'app_id', subscription.service_definition_id,
      'app_installation_id', subscription.service_installation_id,
      'app_member_id', service_member.member_id
    ),
    COALESCE(owner_member.user_id, ''),
    subscription.service_target_binding_id,
    CASE subscription.service_definition_id
      WHEN 'app_github' THEN 'github.pull_request'
      WHEN 'app_gitea' THEN 'gitea.pull_request'
      ELSE subscription.subscription_kind
    END,
    subscription.external_resource_key,
    subscription.service_connection_ref,
    JSON_OBJECT(
      'provider', CASE subscription.service_definition_id
        WHEN 'app_github' THEN 'github'
        WHEN 'app_gitea' THEN 'gitea'
        ELSE ''
      END,
      'resource_kind', subscription.external_resource_type,
      'resource_ref', subscription.external_resource_ref,
      'resource_key', subscription.external_resource_key,
      'provider_connection_ref', subscription.service_connection_ref,
      'event_filters', 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.target_kind, subscription.target_ref,
    subscription.delivery_scope_id,
    binding.id, binding.workspace_id, binding.target_kind, binding.target_ref,
    binding.delivery_scope_id, binding.permission_check_scope_id,
    binding.authority_subject_member_id, binding.delivery_contract,
    binding.service_definition_id, binding.service_installation_id,
    binding.service_member_id, service_member.member_id,
    binding.status, binding.disabled_reason,
    binding.created_by_member_id, binding.created_by_actor_type,
    binding.created_by_actor_id, binding.created_via, binding.scope_snapshot,
    binding.metadata, binding.created_at, binding.updated_at
FROM service_event_subscriptions subscription
JOIN service_target_bindings binding
  ON binding.id = subscription.service_target_binding_id
JOIN service_installations installation
  ON installation.id = subscription.service_installation_id
JOIN service_members service_member
  ON service_member.id = subscription.service_member_id
LEFT JOIN members owner_member
  ON owner_member.id = subscription.owner_member_id
 AND owner_member.type = 'human'
JOIN service_connections connection
  ON connection.service_installation_id = subscription.service_installation_id
 AND connection.external_subject_ref = subscription.service_connection_ref
 AND connection.status = 'active'
WHERE subscription.workspace_id = $1
  AND subscription.service_installation_id = $2
  AND subscription.service_definition_id = $3
  AND subscription.subscription_kind = 'pull_request'
  AND subscription.external_resource_type = 'pull_request'
`

const serviceEventDeliveryCandidateActiveSQL = `
  AND subscription.status = 'active'
  AND subscription.removed_at IS NULL
  AND binding.status = 'active'
  AND binding.removed_at IS NULL
  AND installation.status = 'active'
ORDER BY subscription.created_at ASC, subscription.id ASC
`

func loadServiceEventDeliveryCandidates(ctx context.Context, queryer coredb.Queryer, workspaceID string, installationID string, providerName string, resourceKey string) ([]serviceEventDeliveryCandidate, bool, error) {
	query, args := buildLoadServiceEventDeliveryCandidatesQuery(workspaceID, installationID, providerName, resourceKey)
	return queryServiceEventDeliveryCandidates(ctx, queryer, query, args, "service event webhook candidates")
}

func loadServiceEventDeliveryCandidatesForBaseBranchPush(ctx context.Context, queryer coredb.Queryer, workspaceID string, installationID string, providerName string, connectionRef string, repositoryFullName string, pushedBranch string, defaultBranch string) ([]serviceEventDeliveryCandidate, bool, error) {
	normalizedRepository := strings.ToLower(strings.TrimSpace(repositoryFullName))
	normalizedBranch := strings.TrimSpace(pushedBranch)
	if normalizedRepository == "" || normalizedBranch == "" {
		return nil, false, nil
	}
	query, args := buildLoadServiceEventDeliveryCandidatesForRepositoryQuery(workspaceID, installationID, providerName, connectionRef, normalizedRepository, normalizedBranch, defaultBranch)
	return queryServiceEventDeliveryCandidates(ctx, queryer, query, args, "service event repository webhook candidates")
}

func queryServiceEventDeliveryCandidates(ctx context.Context, queryer coredb.Queryer, query string, args []any, label string) ([]serviceEventDeliveryCandidate, bool, error) {
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, false, fmt.Errorf("load %s: %w", label, err)
	}
	defer rows.Close()

	candidates := make([]serviceEventDeliveryCandidate, 0, serviceEventDeliveryCandidateMax+1)
	for rows.Next() {
		candidate, err := scanServiceEventDeliveryCandidate(rows)
		if err != nil {
			return nil, false, fmt.Errorf("scan %s: %w", label, err)
		}
		candidates = append(candidates, candidate)
	}
	if err := rows.Err(); err != nil {
		return nil, false, fmt.Errorf("iterate %s: %w", label, err)
	}
	if len(candidates) > serviceEventDeliveryCandidateMax {
		return candidates[:serviceEventDeliveryCandidateMax], true, nil
	}
	return candidates, false, nil
}

func loadServiceEventDeliveryCandidatesForCommitStatus(ctx context.Context, queryer coredb.Queryer, workspaceID string, installationID string, providerName string, connectionRef string, repositoryFullName string, sha string) ([]serviceEventDeliveryCandidate, bool, error) {
	normalizedRepository := strings.ToLower(strings.TrimSpace(repositoryFullName))
	trimmedSHA := strings.TrimSpace(sha)
	if normalizedRepository == "" || trimmedSHA == "" {
		return nil, false, nil
	}
	query, args := buildLoadServiceEventDeliveryCandidatesForCommitStatusQuery(workspaceID, installationID, providerName, connectionRef, normalizedRepository, trimmedSHA)
	return queryServiceEventDeliveryCandidates(ctx, queryer, query, args, "service event commit status candidates")
}

func buildLoadServiceEventDeliveryCandidatesQuery(workspaceID string, installationID string, providerName string, resourceKey string) (string, []any) {
	args := []any{
		strings.TrimSpace(workspaceID),
		strings.TrimSpace(installationID),
		pullRequestSubscriptionServiceDefinitionID(providerName),
		strings.TrimSpace(resourceKey),
		serviceEventDeliveryCandidateMax + 1,
	}
	return serviceEventDeliveryCandidateSelectSQL + `
  AND subscription.external_resource_key = $4
` + serviceEventDeliveryCandidateActiveSQL + `
LIMIT $5
`, args
}

func buildLoadServiceEventDeliveryCandidatesForRepositoryQuery(workspaceID string, installationID string, providerName string, connectionRef string, repositoryFullName string, pushedBranch string, defaultBranch string) (string, []any) {
	repositoryKey := serviceEventSubscriptionExternalRepositoryKey(providerName, repositoryFullName)
	args := []any{
		strings.TrimSpace(workspaceID),
		strings.TrimSpace(installationID),
		pullRequestSubscriptionServiceDefinitionID(providerName),
		strings.TrimSpace(connectionRef),
		repositoryKey,
		strings.ToLower(strings.TrimSpace(pushedBranch)),
		strings.ToLower(strings.TrimSpace(defaultBranch)),
		serviceEventDeliveryCandidateMax + 1,
	}
	return serviceEventDeliveryCandidateSelectSQL + `
  AND subscription.service_connection_ref = $4
  AND subscription.external_repository_key = $5
  AND (
        subscription.base_branch_key = $6
        OR subscription.target_branch_key = $6
        OR (
             $7 <> ''
             AND $7 = $6
             AND subscription.base_branch_key = ''
             AND subscription.target_branch_key = ''
           )
      )
` + serviceEventDeliveryCandidateActiveSQL + `
LIMIT $8
`, args
}

func buildLoadServiceEventDeliveryCandidatesForCommitStatusQuery(workspaceID string, installationID string, providerName string, connectionRef string, repositoryFullName string, sha string) (string, []any) {
	repositoryKey := serviceEventSubscriptionExternalRepositoryKey(providerName, repositoryFullName)
	args := []any{
		strings.TrimSpace(workspaceID),
		strings.TrimSpace(installationID),
		pullRequestSubscriptionServiceDefinitionID(providerName),
		strings.TrimSpace(connectionRef),
		repositoryKey,
		strings.ToLower(strings.TrimSpace(sha)),
		serviceEventDeliveryCandidateMax + 1,
	}
	return serviceEventDeliveryCandidateSelectSQL + `
  AND subscription.service_connection_ref = $4
  AND subscription.external_repository_key = $5
  AND subscription.head_sha_key = $6
` + serviceEventDeliveryCandidateActiveSQL + `
LIMIT $7
`, args
}

func updateServiceEventDeliveryBranchMetadataForCandidates(ctx context.Context, execer coredb.QueryExecer, candidates []serviceEventDeliveryCandidate, event provider.ProviderEvent, now time.Time) error {
	for _, candidate := range candidates {
		if err := updateServiceEventDeliveryBranchMetadataFromEvent(ctx, execer, candidate, event, now); err != nil {
			return err
		}
	}
	return nil
}

func updateServiceEventDeliveryBranchMetadataFromEvent(ctx context.Context, execer coredb.QueryExecer, candidate serviceEventDeliveryCandidate, event provider.ProviderEvent, now time.Time) error {
	baseBranch := strings.TrimSpace(event.Extra["base_branch"])
	if baseBranch == "" {
		return nil
	}
	metadata := decodeServiceEventSubscriptionJSONMap(candidate.Subscription.Metadata)
	changed := false
	if strings.TrimSpace(stringFromServiceEventMetadata(metadata["base_branch"])) != baseBranch {
		metadata["base_branch"] = baseBranch
		metadata["target_branch"] = baseBranch
		changed = true
	}
	for metadataKey, eventKey := range map[string]string{
		"head_branch": "head_branch",
		"base_sha":    "base_sha",
		"head_sha":    "head_sha",
	} {
		if value := strings.TrimSpace(event.Extra[eventKey]); value != "" && strings.TrimSpace(stringFromServiceEventMetadata(metadata[metadataKey])) != value {
			metadata[metadataKey] = value
			changed = true
		}
	}
	if !changed {
		return nil
	}
	encoded, err := marshalServiceEventSubscriptionJSONMap(metadata)
	if err != nil {
		return err
	}
	matchKeys := serviceEventSubscriptionMatchKeysFromMetadata(metadata)
	if _, err := execer.Exec(ctx, `
UPDATE service_event_subscriptions
SET metadata = $3,
    updated_at = $4,
    base_branch_key = $5,
    target_branch_key = $6,
    head_sha_key = $7,
    base_sha_key = $8
WHERE id = $1
  AND workspace_id = $2
`, candidate.Subscription.ID, candidate.Subscription.WorkspaceID, encoded, now, matchKeys.BaseBranchKey, matchKeys.TargetBranchKey, matchKeys.HeadSHAKey, matchKeys.BaseSHAKey); err != nil {
		return fmt.Errorf("update service event subscription branch metadata: %w", err)
	}
	return nil
}

func scanServiceEventDeliveryCandidate(row coredb.Row) (serviceEventDeliveryCandidate, error) {
	var candidate serviceEventDeliveryCandidate
	var ownerRef string
	var ownerMemberID string
	var ownerSnapshotPayload []byte
	var ownerUserID string
	var sourceType string
	var sourceConfigPayload []byte
	var pausedAt sql.NullTime
	var removedAt sql.NullTime
	if err := row.Scan(
		&candidate.Subscription.ID,
		&candidate.Subscription.WorkspaceID,
		&ownerRef,
		&ownerMemberID,
		&ownerSnapshotPayload,
		&ownerUserID,
		&candidate.Subscription.AppBindingID,
		&sourceType,
		&candidate.Subscription.ResourceKey,
		&candidate.Subscription.ProviderConnectionRef,
		&sourceConfigPayload,
		&candidate.Subscription.Status,
		&candidate.Subscription.PausedReason,
		&candidate.Subscription.CreatedByMemberID,
		&candidate.Subscription.CreatedByActorType,
		&candidate.Subscription.CreatedByActorID,
		&candidate.Subscription.AuthorizerUserID,
		&candidate.Subscription.DeliveryPolicy,
		&candidate.Subscription.NotifyPrincipal,
		&candidate.Subscription.CreatedVia,
		&candidate.Subscription.CorrelationID,
		&candidate.Subscription.Metadata,
		&candidate.Subscription.CreatedAt,
		&candidate.Subscription.UpdatedAt,
		&pausedAt,
		&removedAt,
		&candidate.Subscription.TargetKind,
		&candidate.Subscription.TargetRef,
		&candidate.Subscription.DeliveryScopeID,
		&candidate.Binding.ID,
		&candidate.Binding.WorkspaceID,
		&candidate.Binding.TargetKind,
		&candidate.Binding.TargetRef,
		&candidate.Binding.DeliveryScopeID,
		&candidate.Binding.PermissionCheckScopeID,
		&candidate.Binding.AuthoritySubjectMemberID,
		&candidate.Binding.DeliveryContract,
		&candidate.Binding.ServiceDefinitionID,
		&candidate.Binding.ServiceInstallationID,
		&candidate.Binding.ServiceMemberID,
		&candidate.Binding.ServiceVisibleMemberID,
		&candidate.Binding.Status,
		&candidate.Binding.DisabledReason,
		&candidate.Binding.CreatedByMemberID,
		&candidate.Binding.CreatedByActorType,
		&candidate.Binding.CreatedByActorID,
		&candidate.Binding.CreatedVia,
		&candidate.Binding.ScopeSnapshot,
		&candidate.Binding.Metadata,
		&candidate.Binding.CreatedAt,
		&candidate.Binding.UpdatedAt,
	); err != nil {
		return serviceEventDeliveryCandidate{}, err
	}
	candidate.Subscription.TriggerID = candidate.Subscription.ID
	candidate.Subscription.OwnerUserID = strings.TrimSpace(ownerUserID)
	candidate.Subscription.AppInstallationID = strings.TrimSpace(ownerRef)
	if strings.TrimSpace(candidate.Subscription.DeliveryPolicy) == "" {
		candidate.Subscription.DeliveryPolicy = pullRequestSubscriptionDeliveryPolicy
	}
	config := decodeServiceEventProviderSourceConfig(sourceConfigPayload)
	candidate.Subscription.Provider = firstNonEmpty(config.Provider, pullRequestSubscriptionProviderFromSourceType(sourceType))
	candidate.Subscription.ResourceKind = firstNonEmpty(config.ResourceKind, PullRequestSubscriptionResourcePullRequest)
	candidate.Subscription.ResourceRef = config.ResourceRef
	if candidate.Subscription.ResourceKey == "" {
		candidate.Subscription.ResourceKey = config.ResourceKey
	}
	if candidate.Subscription.ProviderConnectionRef == "" {
		candidate.Subscription.ProviderConnectionRef = config.ProviderConnectionRef
	}
	if decodedFilters, err := model.MarshalJSONObject("provider event filters", config.EventFilters); err == nil && len(config.EventFilters) > 0 {
		candidate.Subscription.EventFilters = decodedFilters
	} else {
		candidate.Subscription.EventFilters = []byte(`{}`)
	}
	var ownerSnapshot serviceEventDeliveryOwnerSnapshot
	if len(ownerSnapshotPayload) > 0 {
		_ = model.UnmarshalJSONObject("service event delivery owner snapshot", ownerSnapshotPayload, &ownerSnapshot)
	}
	candidate.Subscription.AppID = strings.TrimSpace(ownerSnapshot.AppID)
	if candidate.Subscription.AppInstallationID == "" {
		candidate.Subscription.AppInstallationID = strings.TrimSpace(ownerSnapshot.AppInstallationID)
	}
	_ = ownerMemberID
	if pausedAt.Valid {
		candidate.Subscription.PausedAt = &pausedAt.Time
	}
	if removedAt.Valid {
		candidate.Subscription.RemovedAt = &removedAt.Time
	}
	return candidate, nil
}

type serviceEventDeliveryOwnerSnapshot struct {
	AppID             string `json:"app_id,omitempty"`
	AppInstallationID string `json:"app_installation_id,omitempty"`
	AppMemberID       string `json:"app_member_id,omitempty"`
}

func serviceEventDeliveryCandidatesMatchingEvent(candidates []serviceEventDeliveryCandidate, event provider.ProviderEvent) []serviceEventDeliveryCandidate {
	matched := make([]serviceEventDeliveryCandidate, 0, len(candidates))
	for _, candidate := range candidates {
		if serviceEventProviderEventFiltersMatch(candidate.Subscription.EventFilters, event) {
			matched = append(matched, candidate)
		}
	}
	return matched
}