package app

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

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/app/installation"
	"github.com/tidbcloud/AgentTeam/internal/store/app/registry"
	"github.com/tidbcloud/AgentTeam/internal/store/app/runtime"
)

type UpsertAppConnectionParams struct {
	InstallationID     string
	ExternalSubjectRef string
	DisplayLabel       string
	Credentials        map[string]any
	Config             map[string]any
	CreatedByUserID    string
}

type webhookAppConnection struct {
	webhookAppInstallation
	ConnectionID string
}

func (store *Store) UpsertAppConnection(ctx context.Context, params UpsertAppConnectionParams) (runtime.ConnectionView, error) {
	installationID := strings.TrimSpace(params.InstallationID)
	if installationID == "" {
		return runtime.ConnectionView{}, badRequest("installationId is required")
	}
	externalSubjectRef := strings.TrimSpace(params.ExternalSubjectRef)
	if externalSubjectRef == "" {
		return runtime.ConnectionView{}, badRequest("externalSubjectRef is required")
	}
	installation, err := store.serviceActorStore().
		LoadInstallation(ctx, installationID)
	if err != nil {
		return runtime.ConnectionView{}, err
	}
	if err := requireGenericAppSetupAllowed(
		ctx,
		store.db,
		installation.ServiceDefinitionID,
	); err != nil {
		return runtime.ConnectionView{}, err
	}

	var connection runtime.ConnectionView
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var txErr error
		connection, txErr = store.upsertAppConnectionInTx(ctx, tx, params, "", time.Now().UTC())
		return txErr
	}); err != nil {
		return runtime.ConnectionView{}, err
	}
	return connection, nil
}

// upsertAppConnectionInTx applies the canonical connection row and webhook
// endpoint in one caller-owned transaction. It is used by approval settlement
// so an approved request cannot be committed without its local connection
// state.
func (store *Store) upsertAppConnectionInTx(ctx context.Context, tx coredb.Tx, params UpsertAppConnectionParams, connectionID string, now time.Time) (runtime.ConnectionView, error) {
	installationID := strings.TrimSpace(params.InstallationID)
	externalSubjectRef := strings.TrimSpace(params.ExternalSubjectRef)
	if installationID == "" {
		return runtime.ConnectionView{}, badRequest("installationId is required")
	}
	if externalSubjectRef == "" {
		return runtime.ConnectionView{}, badRequest("externalSubjectRef is required")
	}
	if strings.TrimSpace(connectionID) == "" {
		var err error
		connectionID, err = identifiers.NewEntityID("appconn")
		if err != nil {
			return runtime.ConnectionView{}, fmt.Errorf("generate app connection id: %w", err)
		}
	}
	credentialsJSON, err := store.marshalAppCredentialsObject(registry.JSONObjectOrEmpty(params.Credentials))
	if err != nil {
		return runtime.ConnectionView{}, err
	}
	configJSON, err := installation.MarshalJSONObject(
		registry.JSONObjectOrEmpty(params.Config),
		"config",
	)
	if err != nil {
		return runtime.ConnectionView{}, err
	}
	serviceConnection, err := store.upsertAppServiceConnection(ctx, tx, installationID, connectionID, externalSubjectRef, strings.TrimSpace(params.DisplayLabel), credentialsJSON, configJSON, params.CreatedByUserID, now)
	if err != nil {
		return runtime.ConnectionView{}, err
	}
	if err := store.syncAppServiceWebhookEndpoint(ctx, tx, serviceConnection, credentialsJSON, registry.JSONObjectOrEmpty(params.Credentials), now); err != nil {
		return runtime.ConnectionView{}, err
	}
	if err := store.RunWorkspaceAppCatalogReconcileHook(ctx, tx, serviceConnection.WorkspaceID, now); err != nil {
		return runtime.ConnectionView{}, err
	}
	return serviceConnectionToAppView(serviceConnection), nil
}

func (store *Store) ListAppConnections(ctx context.Context, installationID string) ([]runtime.ConnectionView, error) {
	connections, err := store.serviceActorStore().ListActiveConnections(ctx, installationID)
	if err != nil {
		return nil, err
	}
	return serviceConnectionsToAppViews(connections), nil
}

func (store *Store) DisableAppConnection(ctx context.Context, installationID string, connectionID string) error {
	trimmedInstallationID := strings.TrimSpace(installationID)
	trimmedConnectionID := strings.TrimSpace(connectionID)
	if trimmedInstallationID == "" || trimmedConnectionID == "" {
		return badRequest("installationId and connectionId are required")
	}
	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		return store.disableAppConnectionInTx(ctx, tx, trimmedInstallationID, trimmedConnectionID, time.Now().UTC())
	}); err != nil {
		return err
	}
	return nil
}

func (store *Store) disableAppConnectionInTx(ctx context.Context, tx coredb.Tx, installationID string, connectionID string, now time.Time) error {
	var changed int64
	var workspaceID string
	if err := tx.QueryRow(ctx, `
SELECT workspace_id
FROM service_connections
WHERE id = $1
  AND service_installation_id = $2
LIMIT 1
FOR UPDATE
`, strings.TrimSpace(connectionID), strings.TrimSpace(installationID)).Scan(&workspaceID); err != nil {
		if coredb.IsNotFound(err) {
			return ErrNotFound
		}
		return fmt.Errorf("load service connection Workspace for disable: %w", err)
	}
	serviceResult, err := tx.Exec(ctx, `
UPDATE service_connections
SET status = 'disabled',
    disabled_at = $3,
    updated_at = $3
WHERE id = $1
  AND service_installation_id = $2
  AND status = 'active'
`, strings.TrimSpace(connectionID), strings.TrimSpace(installationID), now)
	if err != nil {
		return fmt.Errorf("disable service connection: %w", err)
	}
	changed += serviceResult.RowsAffected()
	if changed == 0 {
		return ErrNotFound
	}
	if err := store.serviceActorStore().InTx(tx).DisableWebhookEndpointsForConnection(ctx, strings.TrimSpace(connectionID), "", now); err != nil {
		return err
	}
	return store.RunWorkspaceAppCatalogReconcileHook(ctx, tx, workspaceID, now)
}

func (store *Store) loadEnabledConnectionsByExternalRef(ctx context.Context, appID string, externalSubjectRef string) ([]webhookAppConnection, error) {
	rows, err := store.db.Query(ctx, `
SELECT
    connection.id AS connection_id,
    connection.external_subject_ref,
    connection.credentials,
    installation.id AS installation_id,
    definition.id AS app_id,
    definition.slug,
    definition.name,
    service_member.member_id,
    service_member.handle,
    installation.workspace_id
FROM service_connections connection
JOIN service_installations installation
  ON installation.id = connection.service_installation_id
JOIN service_definitions definition
  ON definition.id = connection.service_definition_id
JOIN service_members service_member
  ON service_member.service_installation_id = installation.id
WHERE definition.id = $1
  AND definition.service_kind = 'app'
  AND connection.service_definition_id = $1
  AND connection.external_subject_ref = $2
  AND connection.status = 'active'
  AND installation.status = 'active'
ORDER BY connection.created_at ASC, connection.id ASC
`, strings.TrimSpace(appID), strings.TrimSpace(externalSubjectRef))
	if err != nil {
		return nil, fmt.Errorf("load enabled app connections: %w", err)
	}
	defer rows.Close()

	connections := make([]webhookAppConnection, 0)
	for rows.Next() {
		var connection webhookAppConnection
		if err := rows.Scan(
			&connection.ConnectionID,
			&connection.ExternalSubjectRef,
			&connection.Credentials,
			&connection.InstallationID,
			&connection.AppID,
			&connection.AppSlug,
			&connection.AppName,
			&connection.MemberID,
			&connection.Handle,
			&connection.WorkspaceID,
		); err != nil {
			return nil, fmt.Errorf("scan enabled app connection: %w", err)
		}

		decryptedCredentials, err := store.decryptStoredAppCredentialsJSON(connection.Credentials)
		if err != nil {
			return nil, err
		}
		connection.ConnectionID = strings.TrimSpace(connection.ConnectionID)
		connection.ExternalSubjectRef = strings.TrimSpace(connection.ExternalSubjectRef)
		connection.InstallationID = strings.TrimSpace(connection.InstallationID)
		connection.AppID = strings.TrimSpace(connection.AppID)
		connection.AppSlug = strings.TrimSpace(connection.AppSlug)
		connection.AppName = strings.TrimSpace(connection.AppName)
		connection.MemberID = strings.TrimSpace(connection.MemberID)
		connection.Handle = strings.TrimSpace(connection.Handle)
		connection.WorkspaceID = strings.TrimSpace(connection.WorkspaceID)
		connection.Credentials = decryptedCredentials
		connections = append(connections, connection)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate enabled app connections: %w", err)
	}
	return connections, nil
}