package serviceactor

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

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

// WebhookEndpoint records a verified callback endpoint for a service actor.
type WebhookEndpoint struct {
	ID                      string
	WorkspaceID             string
	ServiceDefinitionID     string
	ServiceInstallationID   string
	ServiceConnectionID     string
	ServiceKind             string
	EndpointKind            string
	ExternalSubjectRef      string
	CallbackPath            string
	SigningSecretCiphertext string
	SecretVersion           int64
	Config                  []byte
	Status                  string
	CreatedByMemberID       string
	CreatedAt               time.Time
	UpdatedAt               time.Time
	DisabledAt              *time.Time
	ActiveEndpointKey       string
}

// WebhookEndpointUpsert describes a webhook endpoint write.
type WebhookEndpointUpsert struct {
	ID                      string
	WorkspaceID             string
	ServiceDefinitionID     string
	ServiceInstallationID   string
	ServiceConnectionID     string
	ServiceKind             string
	EndpointKind            string
	ExternalSubjectRef      string
	CallbackPath            string
	SigningSecretCiphertext string
	SecretVersion           int64
	Config                  []byte
	Status                  string
	CreatedByMemberID       string
	Now                     time.Time
	DisabledAt              *time.Time
	ActiveEndpointKey       string
}

// UpsertWebhookEndpoint creates or updates a service webhook endpoint.
func (store *Store) UpsertWebhookEndpoint(ctx context.Context, params WebhookEndpointUpsert) (WebhookEndpoint, error) {
	execer, err := execerFromStore(store)
	if err != nil {
		return WebhookEndpoint{}, err
	}
	return upsertWebhookEndpoint(ctx, execer, params)
}

// UpsertWebhookEndpoint creates or updates a service webhook endpoint in the transaction.
func (store *TxStore) UpsertWebhookEndpoint(ctx context.Context, params WebhookEndpointUpsert) (WebhookEndpoint, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return WebhookEndpoint{}, err
	}
	return upsertWebhookEndpoint(ctx, execer, params)
}

// LoadWebhookEndpointForConnection loads the active endpoint for a connection and kind.
func (store *Store) LoadWebhookEndpointForConnection(ctx context.Context, serviceConnectionID string, endpointKind string) (WebhookEndpoint, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return WebhookEndpoint{}, err
	}
	return loadWebhookEndpointForConnection(ctx, queryer, serviceConnectionID, endpointKind)
}

// DisableWebhookEndpointsForConnection disables active endpoints for a
// connection and endpoint kind. Empty kind disables every endpoint on the
// connection.
func (store *TxStore) DisableWebhookEndpointsForConnection(ctx context.Context, serviceConnectionID string, endpointKind string, now time.Time) error {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return err
	}
	return disableWebhookEndpointsForConnection(ctx, execer, serviceConnectionID, endpointKind, now)
}

// DisableWebhookEndpointsForInstallation disables active endpoints for one
// service installation.
func (store *TxStore) DisableWebhookEndpointsForInstallation(ctx context.Context, serviceInstallationID string, now time.Time) error {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return err
	}
	return disableWebhookEndpointsForInstallation(ctx, execer, serviceInstallationID, now)
}

func upsertWebhookEndpoint(ctx context.Context, execer coredb.QueryExecer, params WebhookEndpointUpsert) (WebhookEndpoint, error) {
	endpointID := trim(params.ID)
	if endpointID == "" {
		var err error
		endpointID, err = identifiers.NewEntityID("svcwh")
		if err != nil {
			return WebhookEndpoint{}, fmt.Errorf("generate service webhook endpoint id: %w", err)
		}
	}
	workspaceID := trim(params.WorkspaceID)
	definitionID := trim(params.ServiceDefinitionID)
	installationID := trim(params.ServiceInstallationID)
	serviceKind := trim(params.ServiceKind)
	endpointKind := trim(params.EndpointKind)
	createdByMemberID := trim(params.CreatedByMemberID)
	if workspaceID == "" || definitionID == "" || installationID == "" || serviceKind == "" || endpointKind == "" || createdByMemberID == "" {
		return WebhookEndpoint{}, badRequest("workspaceId, service identity, endpointKind, and createdByMemberId are required")
	}
	status := trim(params.Status)
	if status == "" {
		status = StatusActive
	}
	secretVersion := params.SecretVersion
	if secretVersion == 0 {
		secretVersion = 1
	}
	now := params.Now.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	disabledAt := params.DisabledAt
	if status == StatusDisabled && disabledAt == nil {
		disabledAt = &now
	}
	if status == StatusActive {
		disabledAt = nil
	}
	if _, err := execer.Exec(ctx, `
INSERT INTO service_webhook_endpoints (
    id, workspace_id, service_definition_id, service_installation_id,
    service_connection_id, service_kind, endpoint_kind, external_subject_ref,
    callback_path, signing_secret_ciphertext, secret_version, config,
    status, created_by_member_id, created_at, updated_at, disabled_at,
    active_endpoint_key
) VALUES (
    $1, $2, $3, $4,
    $5, $6, $7, $8,
    $9, $10, $11, $12,
    $13, $14, $15, $15, $16,
    $17
)
ON DUPLICATE KEY UPDATE
    service_connection_id = VALUES(service_connection_id),
    external_subject_ref = VALUES(external_subject_ref),
    callback_path = VALUES(callback_path),
    signing_secret_ciphertext = VALUES(signing_secret_ciphertext),
    secret_version = VALUES(secret_version),
    config = VALUES(config),
    status = VALUES(status),
    created_by_member_id = VALUES(created_by_member_id),
    updated_at = VALUES(updated_at),
    disabled_at = VALUES(disabled_at),
    active_endpoint_key = VALUES(active_endpoint_key)
`, endpointID, workspaceID, definitionID, installationID, nullableString(trim(params.ServiceConnectionID)), serviceKind, endpointKind, trim(params.ExternalSubjectRef), trim(params.CallbackPath), trim(params.SigningSecretCiphertext), secretVersion, params.Config, status, createdByMemberID, now, nullTimePtr(disabledAt), nullableString(trim(params.ActiveEndpointKey))); err != nil {
		return WebhookEndpoint{}, fmt.Errorf("upsert service webhook endpoint: %w", mapWebhookEndpointWriteError(err))
	}
	if trim(params.ActiveEndpointKey) != "" {
		endpoint, found, err := loadWebhookEndpointByActiveKey(ctx, execer, params.ActiveEndpointKey)
		if err != nil {
			return WebhookEndpoint{}, err
		}
		if found {
			return endpoint, nil
		}
	}
	return loadWebhookEndpoint(ctx, execer, endpointID)
}

func loadWebhookEndpoint(ctx context.Context, queryer coredb.QueryRower, endpointID string) (WebhookEndpoint, error) {
	row := queryer.QueryRow(ctx, webhookEndpointSelectSQL()+` WHERE id = $1`, trim(endpointID))
	endpoint, err := scanWebhookEndpoint(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return WebhookEndpoint{}, notFound("service webhook endpoint not found")
		}
		return WebhookEndpoint{}, fmt.Errorf("load service webhook endpoint: %w", err)
	}
	return endpoint, nil
}

func loadWebhookEndpointForConnection(ctx context.Context, queryer coredb.QueryRower, serviceConnectionID string, endpointKind string) (WebhookEndpoint, error) {
	row := queryer.QueryRow(ctx, webhookEndpointSelectSQL()+`
WHERE service_connection_id = $1
  AND endpoint_kind = $2
  AND status = 'active'
ORDER BY updated_at DESC, id ASC
LIMIT 1
`, trim(serviceConnectionID), trim(endpointKind))
	endpoint, err := scanWebhookEndpoint(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return WebhookEndpoint{}, notFound("service webhook endpoint not found")
		}
		return WebhookEndpoint{}, fmt.Errorf("load service webhook endpoint for connection: %w", err)
	}
	return endpoint, nil
}

func loadWebhookEndpointByActiveKey(ctx context.Context, queryer coredb.QueryRower, activeEndpointKey string) (WebhookEndpoint, bool, error) {
	row := queryer.QueryRow(ctx, webhookEndpointSelectSQL()+`
WHERE active_endpoint_key = $1
LIMIT 1
`, trim(activeEndpointKey))
	endpoint, err := scanWebhookEndpoint(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return WebhookEndpoint{}, false, nil
		}
		return WebhookEndpoint{}, false, fmt.Errorf("load service webhook endpoint by active key: %w", err)
	}
	return endpoint, true, nil
}

func disableWebhookEndpointsForConnection(ctx context.Context, execer coredb.QueryExecer, serviceConnectionID string, endpointKind string, now time.Time) error {
	now = now.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	query := `
UPDATE service_webhook_endpoints
SET status = 'disabled',
    disabled_at = $2,
    updated_at = $2,
    active_endpoint_key = NULL
WHERE service_connection_id = $1
  AND status = 'active'
`
	args := []any{trim(serviceConnectionID), now}
	if trim(endpointKind) != "" {
		query += `  AND endpoint_kind = $3
`
		args = append(args, trim(endpointKind))
	}
	if _, err := execer.Exec(ctx, query, args...); err != nil {
		return fmt.Errorf("disable service webhook endpoints for connection: %w", err)
	}
	return nil
}

func disableWebhookEndpointsForInstallation(ctx context.Context, execer coredb.QueryExecer, serviceInstallationID string, now time.Time) error {
	now = now.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if _, err := execer.Exec(ctx, `
UPDATE service_webhook_endpoints
SET status = 'disabled',
    disabled_at = $2,
    updated_at = $2,
    active_endpoint_key = NULL
WHERE service_installation_id = $1
  AND status = 'active'
`, trim(serviceInstallationID), now); err != nil {
		return fmt.Errorf("disable service webhook endpoints for installation: %w", err)
	}
	return nil
}

func webhookEndpointSelectSQL() string {
	return `
SELECT id, workspace_id, service_definition_id, service_installation_id,
       service_connection_id, service_kind, endpoint_kind, external_subject_ref,
       callback_path, signing_secret_ciphertext, secret_version, config,
       status, created_by_member_id, created_at, updated_at, disabled_at,
       active_endpoint_key
FROM service_webhook_endpoints`
}

func scanWebhookEndpoint(row coredb.Row) (WebhookEndpoint, error) {
	var endpoint WebhookEndpoint
	var serviceConnectionID sql.NullString
	var disabledAt sql.NullTime
	var activeKey sql.NullString
	if err := row.Scan(
		&endpoint.ID,
		&endpoint.WorkspaceID,
		&endpoint.ServiceDefinitionID,
		&endpoint.ServiceInstallationID,
		&serviceConnectionID,
		&endpoint.ServiceKind,
		&endpoint.EndpointKind,
		&endpoint.ExternalSubjectRef,
		&endpoint.CallbackPath,
		&endpoint.SigningSecretCiphertext,
		&endpoint.SecretVersion,
		&endpoint.Config,
		&endpoint.Status,
		&endpoint.CreatedByMemberID,
		&endpoint.CreatedAt,
		&endpoint.UpdatedAt,
		&disabledAt,
		&activeKey,
	); err != nil {
		return WebhookEndpoint{}, err
	}
	endpoint.ID = trim(endpoint.ID)
	endpoint.WorkspaceID = trim(endpoint.WorkspaceID)
	endpoint.ServiceDefinitionID = trim(endpoint.ServiceDefinitionID)
	endpoint.ServiceInstallationID = trim(endpoint.ServiceInstallationID)
	if serviceConnectionID.Valid {
		endpoint.ServiceConnectionID = trim(serviceConnectionID.String)
	}
	endpoint.ServiceKind = trim(endpoint.ServiceKind)
	endpoint.EndpointKind = trim(endpoint.EndpointKind)
	endpoint.ExternalSubjectRef = trim(endpoint.ExternalSubjectRef)
	endpoint.Status = trim(endpoint.Status)
	endpoint.DisabledAt = ptrFromNullTime(disabledAt)
	if activeKey.Valid {
		endpoint.ActiveEndpointKey = trim(activeKey.String)
	}
	return endpoint, nil
}

func nullableString(value string) any {
	if trim(value) == "" {
		return nil
	}
	return trim(value)
}

func mapWebhookEndpointWriteError(err error) error {
	if constraint, ok := coredb.UniqueViolationConstraint(err); ok {
		switch constraint {
		case "service_webhook_endpoints_active_key":
			return conflict("Service webhook endpoint already exists.")
		}
	}
	return err
}