package providers

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"

	"github.com/niels/banking-app/backend/internal/domain"
)

const (
	IntegrationStatusDraft      = "draft"
	IntegrationStatusConfigured = "configured"
	IntegrationStatusApproved   = "approved"
	IntegrationStatusDisabled   = "disabled"
	IntegrationStatusFailed     = "failed"

	SandboxStatusPending = "pending"
	SandboxStatusPassed  = "passed"
	SandboxStatusFailed  = "failed"
	SandboxStatusWaived  = "waived"
)

type Repository struct {
	db *pgxpool.Pool
}

type Integration struct {
	ID                      string          `json:"id"`
	ProviderType            string          `json:"provider_type"`
	ProviderName            string          `json:"provider_name"`
	Mode                    string          `json:"mode"`
	Status                  string          `json:"status"`
	Environment             string          `json:"environment"`
	OwnerTeam               string          `json:"owner_team"`
	ContractReference       string          `json:"contract_reference,omitempty"`
	CredentialsReference    string          `json:"credentials_reference,omitempty"`
	WebhookSecretReference  string          `json:"webhook_secret_reference,omitempty"`
	APIBaseURL              string          `json:"api_base_url,omitempty"`
	TimeoutMS               int             `json:"timeout_ms"`
	RetryMaxAttempts        int             `json:"retry_max_attempts"`
	CircuitFailureThreshold int             `json:"circuit_failure_threshold"`
	CircuitCooldownSeconds  int             `json:"circuit_cooldown_seconds"`
	DegradedMode            string          `json:"degraded_mode"`
	SandboxEnabled          bool            `json:"sandbox_enabled"`
	ProductionEnabled       bool            `json:"production_enabled"`
	LastVerifiedAt          *time.Time      `json:"last_verified_at,omitempty"`
	NextReviewAt            *time.Time      `json:"next_review_at,omitempty"`
	EvidenceReference       string          `json:"evidence_reference,omitempty"`
	Metadata                json.RawMessage `json:"metadata"`
	CreatedByAdminUserID    string          `json:"created_by_admin_user_id,omitempty"`
	ApprovedByAdminUserID   string          `json:"approved_by_admin_user_id,omitempty"`
	CreatedAt               time.Time       `json:"created_at"`
	UpdatedAt               time.Time       `json:"updated_at"`
}

type IntegrationParams struct {
	ProviderType            string
	ProviderName            string
	Mode                    string
	Status                  string
	Environment             string
	OwnerTeam               string
	ContractReference       string
	CredentialsReference    string
	WebhookSecretReference  string
	APIBaseURL              string
	TimeoutMS               int
	RetryMaxAttempts        int
	CircuitFailureThreshold int
	CircuitCooldownSeconds  int
	DegradedMode            string
	SandboxEnabled          bool
	ProductionEnabled       bool
	LastVerifiedAt          *time.Time
	NextReviewAt            *time.Time
	EvidenceReference       string
	Metadata                json.RawMessage
	AdminUserID             string
}

type OutboundCall struct {
	ID             string          `json:"id"`
	ProviderType   string          `json:"provider_type"`
	ProviderName   string          `json:"provider_name"`
	Operation      string          `json:"operation"`
	IdempotencyKey string          `json:"idempotency_key"`
	Status         string          `json:"status"`
	Attempts       int             `json:"attempts"`
	DurationMS     int64           `json:"duration_ms"`
	RequestHash    string          `json:"request_hash"`
	Request        json.RawMessage `json:"redacted_request"`
	Response       json.RawMessage `json:"redacted_response"`
	ErrorCode      string          `json:"error_code,omitempty"`
	ErrorMessage   string          `json:"error_message,omitempty"`
	DegradedMode   string          `json:"degraded_mode"`
	CircuitState   string          `json:"circuit_state"`
	StartedAt      time.Time       `json:"started_at"`
	FinishedAt     time.Time       `json:"finished_at"`
	CreatedAt      time.Time       `json:"created_at"`
}

type WebhookEvent struct {
	ID               string          `json:"id"`
	ProviderType     string          `json:"provider_type"`
	ProviderName     string          `json:"provider_name"`
	ExternalEventID  string          `json:"external_event_id"`
	EventType        string          `json:"event_type"`
	SequenceNumber   *int64          `json:"sequence_number,omitempty"`
	SignatureStatus  string          `json:"signature_status"`
	ReplayStatus     string          `json:"replay_status"`
	OrderingStatus   string          `json:"ordering_status"`
	ProcessingStatus string          `json:"processing_status"`
	PayloadHash      string          `json:"payload_hash"`
	Payload          json.RawMessage `json:"redacted_payload"`
	ReceivedAt       time.Time       `json:"received_at"`
	CreatedAt        time.Time       `json:"created_at"`
}

type SandboxTestRun struct {
	ID                   string          `json:"id"`
	ProviderType         string          `json:"provider_type"`
	ProviderName         string          `json:"provider_name"`
	SuiteName            string          `json:"suite_name"`
	Status               string          `json:"status"`
	PassedChecks         int             `json:"passed_checks"`
	FailedChecks         int             `json:"failed_checks"`
	SkippedChecks        int             `json:"skipped_checks"`
	EvidenceReference    string          `json:"evidence_reference,omitempty"`
	Summary              string          `json:"summary"`
	Metadata             json.RawMessage `json:"metadata"`
	StartedAt            time.Time       `json:"started_at"`
	CompletedAt          *time.Time      `json:"completed_at,omitempty"`
	CreatedByAdminUserID string          `json:"created_by_admin_user_id,omitempty"`
	CreatedAt            time.Time       `json:"created_at"`
}

type SandboxRunParams struct {
	ProviderType      string
	ProviderName      string
	SuiteName         string
	Status            string
	PassedChecks      int
	FailedChecks      int
	SkippedChecks     int
	EvidenceReference string
	Summary           string
	Metadata          json.RawMessage
	StartedAt         time.Time
	CompletedAt       *time.Time
	AdminUserID       string
}

type Metrics struct {
	IntegrationsTotal       int64 `json:"integrations_total"`
	ProductionGatesOpen     int64 `json:"production_gates_open"`
	OutboundCalls24h        int64 `json:"outbound_calls_24h"`
	OutboundErrors24h       int64 `json:"outbound_errors_24h"`
	WebhookRejected24h      int64 `json:"webhook_rejected_24h"`
	SandboxRunsOpenOrFailed int64 `json:"sandbox_runs_open_or_failed"`
}

type Dashboard struct {
	Metrics         Metrics          `json:"metrics"`
	Integrations    []Integration    `json:"integrations"`
	RecentCalls     []OutboundCall   `json:"recent_outbound_calls"`
	RecentWebhooks  []WebhookEvent   `json:"recent_webhook_events"`
	SandboxTestRuns []SandboxTestRun `json:"sandbox_test_runs"`
	ProductionRisks []Integration    `json:"production_risks"`
	GeneratedAt     time.Time        `json:"generated_at"`
}

func NewRepository(db *pgxpool.Pool) *Repository {
	return &Repository{db: db}
}

func (r *Repository) Dashboard(ctx context.Context, limit int) (Dashboard, error) {
	metrics, err := r.Metrics(ctx)
	if err != nil {
		return Dashboard{}, err
	}
	integrations, err := r.ListIntegrations(ctx, "", "", "", limit)
	if err != nil {
		return Dashboard{}, err
	}
	calls, err := r.ListOutboundCalls(ctx, "", "", "", limit)
	if err != nil {
		return Dashboard{}, err
	}
	webhooks, err := r.ListWebhookEvents(ctx, "", "", "", limit)
	if err != nil {
		return Dashboard{}, err
	}
	runs, err := r.ListSandboxTestRuns(ctx, "", "", limit)
	if err != nil {
		return Dashboard{}, err
	}
	risks := []Integration{}
	for _, integration := range integrations {
		if productionRisk(integration) {
			risks = append(risks, integration)
		}
	}
	return Dashboard{
		Metrics:         metrics,
		Integrations:    integrations,
		RecentCalls:     calls,
		RecentWebhooks:  webhooks,
		SandboxTestRuns: runs,
		ProductionRisks: risks,
		GeneratedAt:     time.Now().UTC(),
	}, nil
}

func (r *Repository) Metrics(ctx context.Context) (Metrics, error) {
	var metrics Metrics
	err := r.db.QueryRow(ctx, `
		SELECT
			(SELECT COUNT(*) FROM provider_integrations)::bigint,
			(SELECT COUNT(*) FROM provider_integrations
				WHERE production_enabled = true
					AND (status <> 'approved' OR mode <> 'contracted' OR evidence_reference = '' OR credentials_reference = ''))::bigint,
			(SELECT COUNT(*) FROM provider_outbound_calls WHERE created_at >= now() - interval '24 hours')::bigint,
			(SELECT COUNT(*) FROM provider_outbound_calls WHERE created_at >= now() - interval '24 hours' AND status <> 'success')::bigint,
			(SELECT COUNT(*) FROM provider_webhook_events WHERE created_at >= now() - interval '24 hours'
				AND (signature_status <> 'verified' OR replay_status = 'duplicate' OR ordering_status IN ('gap', 'regression')))::bigint,
			(SELECT COUNT(*) FROM provider_sandbox_test_runs WHERE status IN ('pending', 'failed'))::bigint
	`).Scan(
		&metrics.IntegrationsTotal,
		&metrics.ProductionGatesOpen,
		&metrics.OutboundCalls24h,
		&metrics.OutboundErrors24h,
		&metrics.WebhookRejected24h,
		&metrics.SandboxRunsOpenOrFailed,
	)
	return metrics, err
}

func (r *Repository) ListIntegrations(ctx context.Context, providerType, mode, status string, limit int) ([]Integration, error) {
	limit = normalizeLimit(limit)
	providerType = strings.ToLower(strings.TrimSpace(providerType))
	mode = strings.ToLower(strings.TrimSpace(mode))
	status = strings.ToLower(strings.TrimSpace(status))
	rows, err := r.db.Query(ctx, integrationSelect+`
		WHERE ($1 = '' OR $1 = 'all' OR provider_type = $1)
			AND ($2 = '' OR $2 = 'all' OR mode = $2)
			AND ($3 = '' OR $3 = 'all' OR status = $3)
		ORDER BY production_enabled DESC,
			CASE status WHEN 'failed' THEN 0 WHEN 'draft' THEN 1 WHEN 'configured' THEN 2 WHEN 'approved' THEN 3 ELSE 4 END,
			updated_at DESC
		LIMIT $4
	`, providerType, mode, status, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []Integration{}
	for rows.Next() {
		item, err := scanIntegration(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) UpsertIntegration(ctx context.Context, params IntegrationParams) (Integration, error) {
	if err := normalizeIntegrationParams(&params); err != nil {
		return Integration{}, err
	}
	row := r.db.QueryRow(ctx, `
		INSERT INTO provider_integrations (
			provider_type, provider_name, mode, status, environment, owner_team,
			contract_reference, credentials_reference, webhook_secret_reference, api_base_url,
			timeout_ms, retry_max_attempts, circuit_failure_threshold, circuit_cooldown_seconds,
			degraded_mode, sandbox_enabled, production_enabled, last_verified_at, next_review_at,
			evidence_reference, metadata, created_by_admin_user_id,
			approved_by_admin_user_id
		)
		VALUES (
			$1, $2, $3, $4, $5, $6, $7, $8, $9, $10,
			$11, $12, $13, $14, $15, $16, $17, $18, $19,
			$20, $21, NULLIF($22, '')::uuid,
			CASE WHEN $4 = 'approved' THEN NULLIF($22, '')::uuid ELSE NULL END
		)
		ON CONFLICT (provider_type, provider_name, environment) DO UPDATE
		SET mode = EXCLUDED.mode,
			status = EXCLUDED.status,
			owner_team = EXCLUDED.owner_team,
			contract_reference = EXCLUDED.contract_reference,
			credentials_reference = EXCLUDED.credentials_reference,
			webhook_secret_reference = EXCLUDED.webhook_secret_reference,
			api_base_url = EXCLUDED.api_base_url,
			timeout_ms = EXCLUDED.timeout_ms,
			retry_max_attempts = EXCLUDED.retry_max_attempts,
			circuit_failure_threshold = EXCLUDED.circuit_failure_threshold,
			circuit_cooldown_seconds = EXCLUDED.circuit_cooldown_seconds,
			degraded_mode = EXCLUDED.degraded_mode,
			sandbox_enabled = EXCLUDED.sandbox_enabled,
			production_enabled = EXCLUDED.production_enabled,
			last_verified_at = EXCLUDED.last_verified_at,
			next_review_at = EXCLUDED.next_review_at,
			evidence_reference = EXCLUDED.evidence_reference,
			metadata = EXCLUDED.metadata,
			approved_by_admin_user_id = CASE
				WHEN EXCLUDED.status = 'approved' THEN EXCLUDED.approved_by_admin_user_id
				ELSE provider_integrations.approved_by_admin_user_id
			END
		RETURNING `+integrationColumns,
		params.ProviderType,
		params.ProviderName,
		params.Mode,
		params.Status,
		params.Environment,
		params.OwnerTeam,
		params.ContractReference,
		params.CredentialsReference,
		params.WebhookSecretReference,
		params.APIBaseURL,
		params.TimeoutMS,
		params.RetryMaxAttempts,
		params.CircuitFailureThreshold,
		params.CircuitCooldownSeconds,
		params.DegradedMode,
		params.SandboxEnabled,
		params.ProductionEnabled,
		params.LastVerifiedAt,
		params.NextReviewAt,
		params.EvidenceReference,
		params.Metadata,
		params.AdminUserID,
	)
	item, err := scanIntegration(row)
	if err != nil && isForeignKeyViolation(err) {
		return Integration{}, fmt.Errorf("%w: admin user does not exist", domain.ErrValidation)
	}
	return item, err
}

func (r *Repository) RecordOutboundCall(ctx context.Context, record OutboundCallRecord) error {
	if r == nil || r.db == nil {
		return nil
	}
	if record.StartedAt.IsZero() {
		record.StartedAt = time.Now().UTC()
	}
	if record.FinishedAt.IsZero() {
		record.FinishedAt = time.Now().UTC()
	}
	if len(record.Request) == 0 {
		record.Request = json.RawMessage(`{}`)
	}
	if len(record.Response) == 0 {
		record.Response = json.RawMessage(`{}`)
	}
	_, err := r.db.Exec(ctx, `
		INSERT INTO provider_outbound_calls (
			provider_type, provider_name, operation, idempotency_key, status, attempts,
			duration_ms, request_hash, redacted_request, redacted_response, error_code, error_message,
			degraded_mode, circuit_state, started_at, finished_at
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16)
	`, record.ProviderType, record.ProviderName, record.Operation, record.IdempotencyKey, record.Status,
		record.Attempts, record.Duration.Milliseconds(), record.RequestHash, record.Request, record.Response,
		record.ErrorCode, trimLen(record.ErrorMessage, 1000), record.DegradedMode, record.CircuitState,
		record.StartedAt, record.FinishedAt)
	return err
}

func (r *Repository) ListOutboundCalls(ctx context.Context, providerType, providerName, status string, limit int) ([]OutboundCall, error) {
	limit = normalizeLimit(limit)
	providerType = strings.ToLower(strings.TrimSpace(providerType))
	providerName = strings.TrimSpace(providerName)
	status = strings.ToLower(strings.TrimSpace(status))
	rows, err := r.db.Query(ctx, `
		SELECT id::text, provider_type, provider_name, operation, idempotency_key, status, attempts,
			duration_ms, request_hash, redacted_request, redacted_response, error_code, error_message,
			degraded_mode, circuit_state, started_at, finished_at, created_at
		FROM provider_outbound_calls
		WHERE ($1 = '' OR $1 = 'all' OR provider_type = $1)
			AND ($2 = '' OR $2 = 'all' OR provider_name = $2)
			AND ($3 = '' OR $3 = 'all' OR status = $3)
		ORDER BY created_at DESC
		LIMIT $4
	`, providerType, providerName, status, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []OutboundCall{}
	for rows.Next() {
		item, err := scanOutboundCall(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) RecordWebhookEvent(ctx context.Context, event WebhookEventRecord) (WebhookEventDecision, error) {
	if r == nil || r.db == nil {
		return WebhookEventDecision{Inserted: true, ReplayStatus: ReplayAccepted, OrderingStatus: OrderingNoSequence}, nil
	}
	if err := normalizeWebhookEvent(&event); err != nil {
		return WebhookEventDecision{}, err
	}
	payloadHash := fingerprint(event.Payload)
	payload := RedactJSON(event.Payload)

	tx, err := r.db.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})
	if err != nil {
		return WebhookEventDecision{}, err
	}
	defer tx.Rollback(ctx)

	var existingID string
	err = tx.QueryRow(ctx, `
		SELECT id::text
		FROM provider_webhook_events
		WHERE provider_type = $1 AND provider_name = $2 AND external_event_id = $3
	`, event.ProviderType, event.ProviderName, event.ExternalEventID).Scan(&existingID)
	if err == nil {
		return WebhookEventDecision{Inserted: false, ReplayStatus: ReplayDuplicate, OrderingStatus: OrderingNoSequence}, tx.Commit(ctx)
	}
	if !errors.Is(err, pgx.ErrNoRows) {
		return WebhookEventDecision{}, err
	}

	ordering := OrderingNoSequence
	if event.SequenceNumber != nil {
		ordering = OrderingInOrder
		var lastSequence sql.NullInt64
		err = tx.QueryRow(ctx, `
			SELECT sequence_number
			FROM provider_webhook_events
			WHERE provider_type = $1 AND provider_name = $2 AND event_type = $3 AND sequence_number IS NOT NULL
			ORDER BY sequence_number DESC
			LIMIT 1
		`, event.ProviderType, event.ProviderName, event.EventType).Scan(&lastSequence)
		if err != nil && !errors.Is(err, pgx.ErrNoRows) {
			return WebhookEventDecision{}, err
		}
		if lastSequence.Valid {
			switch {
			case *event.SequenceNumber <= lastSequence.Int64:
				ordering = OrderingRegression
			case *event.SequenceNumber > lastSequence.Int64+1:
				ordering = OrderingGap
			}
		}
	}

	_, err = tx.Exec(ctx, `
		INSERT INTO provider_webhook_events (
			provider_type, provider_name, external_event_id, event_type, sequence_number,
			signature_status, replay_status, ordering_status, processing_status,
			payload_hash, redacted_payload, received_at
		)
		VALUES ($1, $2, $3, $4, $5, $6, 'accepted', $7, $8, $9, $10, $11)
	`, event.ProviderType, event.ProviderName, event.ExternalEventID, event.EventType, event.SequenceNumber,
		event.SignatureStatus, ordering, event.ProcessingStatus, payloadHash, payload, event.ReceivedAt)
	if err != nil && isUniqueViolation(err) {
		return WebhookEventDecision{Inserted: false, ReplayStatus: ReplayDuplicate, OrderingStatus: ordering}, tx.Commit(ctx)
	}
	if err != nil {
		return WebhookEventDecision{}, err
	}
	return WebhookEventDecision{Inserted: true, ReplayStatus: ReplayAccepted, OrderingStatus: ordering}, tx.Commit(ctx)
}

func (r *Repository) ListWebhookEvents(ctx context.Context, providerType, providerName, status string, limit int) ([]WebhookEvent, error) {
	limit = normalizeLimit(limit)
	providerType = strings.ToLower(strings.TrimSpace(providerType))
	providerName = strings.TrimSpace(providerName)
	status = strings.ToLower(strings.TrimSpace(status))
	rows, err := r.db.Query(ctx, `
		SELECT id::text, provider_type, provider_name, external_event_id, event_type, sequence_number,
			signature_status, replay_status, ordering_status, processing_status, payload_hash,
			redacted_payload, received_at, created_at
		FROM provider_webhook_events
		WHERE ($1 = '' OR $1 = 'all' OR provider_type = $1)
			AND ($2 = '' OR $2 = 'all' OR provider_name = $2)
			AND ($3 = '' OR $3 = 'all' OR processing_status = $3)
		ORDER BY received_at DESC, created_at DESC
		LIMIT $4
	`, providerType, providerName, status, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []WebhookEvent{}
	for rows.Next() {
		item, err := scanWebhookEvent(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

func (r *Repository) CreateSandboxTestRun(ctx context.Context, params SandboxRunParams) (SandboxTestRun, error) {
	if err := normalizeSandboxRunParams(&params); err != nil {
		return SandboxTestRun{}, err
	}
	row := r.db.QueryRow(ctx, `
		INSERT INTO provider_sandbox_test_runs (
			provider_type, provider_name, suite_name, status, passed_checks, failed_checks,
			skipped_checks, evidence_reference, summary, metadata, started_at, completed_at,
			created_by_admin_user_id
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, NULLIF($13, '')::uuid)
		RETURNING id::text, provider_type, provider_name, suite_name, status, passed_checks, failed_checks,
			skipped_checks, evidence_reference, summary, metadata, started_at, completed_at,
			COALESCE(created_by_admin_user_id::text, ''), created_at
	`, params.ProviderType, params.ProviderName, params.SuiteName, params.Status, params.PassedChecks,
		params.FailedChecks, params.SkippedChecks, params.EvidenceReference, params.Summary, params.Metadata,
		params.StartedAt, params.CompletedAt, params.AdminUserID)
	item, err := scanSandboxTestRun(row)
	if err != nil && isForeignKeyViolation(err) {
		return SandboxTestRun{}, fmt.Errorf("%w: admin user does not exist", domain.ErrValidation)
	}
	return item, err
}

func (r *Repository) ListSandboxTestRuns(ctx context.Context, providerType, providerName string, limit int) ([]SandboxTestRun, error) {
	limit = normalizeLimit(limit)
	providerType = strings.ToLower(strings.TrimSpace(providerType))
	providerName = strings.TrimSpace(providerName)
	rows, err := r.db.Query(ctx, `
		SELECT id::text, provider_type, provider_name, suite_name, status, passed_checks, failed_checks,
			skipped_checks, evidence_reference, summary, metadata, started_at, completed_at,
			COALESCE(created_by_admin_user_id::text, ''), created_at
		FROM provider_sandbox_test_runs
		WHERE ($1 = '' OR $1 = 'all' OR provider_type = $1)
			AND ($2 = '' OR $2 = 'all' OR provider_name = $2)
		ORDER BY created_at DESC
		LIMIT $3
	`, providerType, providerName, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := []SandboxTestRun{}
	for rows.Next() {
		item, err := scanSandboxTestRun(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

const integrationColumns = `id::text, provider_type, provider_name, mode, status, environment, owner_team,
	contract_reference, credentials_reference, webhook_secret_reference, api_base_url,
	timeout_ms, retry_max_attempts, circuit_failure_threshold, circuit_cooldown_seconds,
	degraded_mode, sandbox_enabled, production_enabled, last_verified_at, next_review_at,
	evidence_reference, metadata, COALESCE(created_by_admin_user_id::text, ''),
	COALESCE(approved_by_admin_user_id::text, ''), created_at, updated_at`

const integrationSelect = `SELECT ` + integrationColumns + ` FROM provider_integrations`

type scanner interface {
	Scan(dest ...any) error
}

func scanIntegration(row scanner) (Integration, error) {
	var item Integration
	var lastVerifiedAt, nextReviewAt sql.NullTime
	err := row.Scan(
		&item.ID,
		&item.ProviderType,
		&item.ProviderName,
		&item.Mode,
		&item.Status,
		&item.Environment,
		&item.OwnerTeam,
		&item.ContractReference,
		&item.CredentialsReference,
		&item.WebhookSecretReference,
		&item.APIBaseURL,
		&item.TimeoutMS,
		&item.RetryMaxAttempts,
		&item.CircuitFailureThreshold,
		&item.CircuitCooldownSeconds,
		&item.DegradedMode,
		&item.SandboxEnabled,
		&item.ProductionEnabled,
		&lastVerifiedAt,
		&nextReviewAt,
		&item.EvidenceReference,
		&item.Metadata,
		&item.CreatedByAdminUserID,
		&item.ApprovedByAdminUserID,
		&item.CreatedAt,
		&item.UpdatedAt,
	)
	if lastVerifiedAt.Valid {
		item.LastVerifiedAt = &lastVerifiedAt.Time
	}
	if nextReviewAt.Valid {
		item.NextReviewAt = &nextReviewAt.Time
	}
	if len(item.Metadata) == 0 {
		item.Metadata = json.RawMessage(`{}`)
	}
	return item, err
}

func scanOutboundCall(row scanner) (OutboundCall, error) {
	var item OutboundCall
	err := row.Scan(
		&item.ID,
		&item.ProviderType,
		&item.ProviderName,
		&item.Operation,
		&item.IdempotencyKey,
		&item.Status,
		&item.Attempts,
		&item.DurationMS,
		&item.RequestHash,
		&item.Request,
		&item.Response,
		&item.ErrorCode,
		&item.ErrorMessage,
		&item.DegradedMode,
		&item.CircuitState,
		&item.StartedAt,
		&item.FinishedAt,
		&item.CreatedAt,
	)
	return item, err
}

func scanWebhookEvent(row scanner) (WebhookEvent, error) {
	var item WebhookEvent
	var sequence sql.NullInt64
	err := row.Scan(
		&item.ID,
		&item.ProviderType,
		&item.ProviderName,
		&item.ExternalEventID,
		&item.EventType,
		&sequence,
		&item.SignatureStatus,
		&item.ReplayStatus,
		&item.OrderingStatus,
		&item.ProcessingStatus,
		&item.PayloadHash,
		&item.Payload,
		&item.ReceivedAt,
		&item.CreatedAt,
	)
	if sequence.Valid {
		item.SequenceNumber = &sequence.Int64
	}
	return item, err
}

func scanSandboxTestRun(row scanner) (SandboxTestRun, error) {
	var item SandboxTestRun
	var completedAt sql.NullTime
	err := row.Scan(
		&item.ID,
		&item.ProviderType,
		&item.ProviderName,
		&item.SuiteName,
		&item.Status,
		&item.PassedChecks,
		&item.FailedChecks,
		&item.SkippedChecks,
		&item.EvidenceReference,
		&item.Summary,
		&item.Metadata,
		&item.StartedAt,
		&completedAt,
		&item.CreatedByAdminUserID,
		&item.CreatedAt,
	)
	if completedAt.Valid {
		item.CompletedAt = &completedAt.Time
	}
	if len(item.Metadata) == 0 {
		item.Metadata = json.RawMessage(`{}`)
	}
	return item, err
}

func normalizeIntegrationParams(params *IntegrationParams) error {
	params.ProviderType = strings.ToLower(strings.TrimSpace(params.ProviderType))
	params.ProviderName = strings.TrimSpace(params.ProviderName)
	params.Mode = strings.ToLower(strings.TrimSpace(params.Mode))
	params.Status = strings.ToLower(strings.TrimSpace(params.Status))
	params.Environment = strings.ToLower(strings.TrimSpace(params.Environment))
	params.OwnerTeam = strings.ToLower(strings.TrimSpace(params.OwnerTeam))
	params.ContractReference = strings.TrimSpace(params.ContractReference)
	params.CredentialsReference = strings.TrimSpace(params.CredentialsReference)
	params.WebhookSecretReference = strings.TrimSpace(params.WebhookSecretReference)
	params.APIBaseURL = strings.TrimSpace(params.APIBaseURL)
	params.EvidenceReference = strings.TrimSpace(params.EvidenceReference)
	if err := validateProviderType(params.ProviderType); err != nil {
		return err
	}
	if params.ProviderName == "" {
		return fmt.Errorf("%w: provider_name is required", domain.ErrValidation)
	}
	if params.Mode == "" {
		params.Mode = string(ModeSandbox)
	}
	if err := validateProviderMode(params.Mode, params.ProviderType); err != nil {
		return err
	}
	if params.Status == "" {
		params.Status = IntegrationStatusDraft
	}
	if err := validateIntegrationStatus(params.Status); err != nil {
		return err
	}
	if params.Environment == "" {
		params.Environment = "staging"
	}
	if err := validateEnvironment(params.Environment); err != nil {
		return err
	}
	if params.OwnerTeam == "" {
		params.OwnerTeam = "operations"
	}
	if !validOwnerTeam(params.OwnerTeam) {
		return fmt.Errorf("%w: invalid owner_team", domain.ErrValidation)
	}
	if params.TimeoutMS <= 0 {
		params.TimeoutMS = 3000
	}
	if params.RetryMaxAttempts <= 0 {
		params.RetryMaxAttempts = 3
	}
	if params.CircuitFailureThreshold <= 0 {
		params.CircuitFailureThreshold = 5
	}
	if params.CircuitCooldownSeconds <= 0 {
		params.CircuitCooldownSeconds = 60
	}
	if params.DegradedMode == "" {
		params.DegradedMode = "fail_closed"
	}
	if err := validateProviderBudgets(params.TimeoutMS, params.RetryMaxAttempts, params.CircuitFailureThreshold, params.CircuitCooldownSeconds); err != nil {
		return err
	}
	if params.ProductionEnabled && (params.Mode != string(ModeContracted) || params.Status != IntegrationStatusApproved) {
		return fmt.Errorf("%w: production_enabled providers must be contracted and approved", domain.ErrValidation)
	}
	if params.Status == IntegrationStatusApproved {
		if params.EvidenceReference == "" {
			return fmt.Errorf("%w: approved providers require evidence_reference", domain.ErrValidation)
		}
		if params.Mode == string(ModeContracted) && params.CredentialsReference == "" {
			return fmt.Errorf("%w: contracted providers require credentials_reference", domain.ErrValidation)
		}
	}
	if params.Metadata == nil || len(params.Metadata) == 0 {
		params.Metadata = json.RawMessage(`{}`)
	}
	if !json.Valid(params.Metadata) {
		return fmt.Errorf("%w: metadata must be valid JSON", domain.ErrValidation)
	}
	return nil
}

func normalizeWebhookEvent(event *WebhookEventRecord) error {
	event.ProviderType = strings.ToLower(strings.TrimSpace(event.ProviderType))
	event.ProviderName = strings.TrimSpace(event.ProviderName)
	event.ExternalEventID = strings.TrimSpace(event.ExternalEventID)
	event.EventType = strings.TrimSpace(event.EventType)
	event.SignatureStatus = strings.ToLower(strings.TrimSpace(event.SignatureStatus))
	event.ProcessingStatus = strings.ToLower(strings.TrimSpace(event.ProcessingStatus))
	if err := validateProviderType(event.ProviderType); err != nil {
		return err
	}
	if event.ProviderName == "" || event.ExternalEventID == "" || event.EventType == "" {
		return fmt.Errorf("%w: provider_name, external_event_id and event_type are required", domain.ErrValidation)
	}
	if event.SignatureStatus == "" {
		event.SignatureStatus = SignatureVerified
	}
	if event.SignatureStatus != SignatureVerified && event.SignatureStatus != SignatureInvalid && event.SignatureStatus != SignatureMissing {
		return fmt.Errorf("%w: invalid signature_status", domain.ErrValidation)
	}
	if event.ProcessingStatus == "" {
		event.ProcessingStatus = "received"
	}
	if event.ReceivedAt.IsZero() {
		event.ReceivedAt = time.Now().UTC()
	}
	return nil
}

func normalizeSandboxRunParams(params *SandboxRunParams) error {
	params.ProviderType = strings.ToLower(strings.TrimSpace(params.ProviderType))
	params.ProviderName = strings.TrimSpace(params.ProviderName)
	params.SuiteName = strings.TrimSpace(params.SuiteName)
	params.Status = strings.ToLower(strings.TrimSpace(params.Status))
	params.EvidenceReference = strings.TrimSpace(params.EvidenceReference)
	params.Summary = strings.TrimSpace(params.Summary)
	if err := validateProviderType(params.ProviderType); err != nil {
		return err
	}
	if params.ProviderName == "" || params.SuiteName == "" {
		return fmt.Errorf("%w: provider_name and suite_name are required", domain.ErrValidation)
	}
	if params.Status == "" {
		params.Status = SandboxStatusPending
	}
	switch params.Status {
	case SandboxStatusPending, SandboxStatusPassed, SandboxStatusFailed, SandboxStatusWaived:
	default:
		return fmt.Errorf("%w: invalid sandbox test status", domain.ErrValidation)
	}
	if params.StartedAt.IsZero() {
		params.StartedAt = time.Now().UTC()
	}
	if params.Status == SandboxStatusPassed && params.EvidenceReference == "" {
		return fmt.Errorf("%w: passed sandbox runs require evidence_reference", domain.ErrValidation)
	}
	if params.Metadata == nil || len(params.Metadata) == 0 {
		params.Metadata = json.RawMessage(`{}`)
	}
	if !json.Valid(params.Metadata) {
		return fmt.Errorf("%w: metadata must be valid JSON", domain.ErrValidation)
	}
	return nil
}

func validateProviderType(value string) error {
	switch value {
	case "bank_ledger", "card_issuer", "identity", "sanctions", "custody", "blockchain_analytics", "market_data", "payment_rail":
		return nil
	default:
		return fmt.Errorf("%w: invalid provider_type", domain.ErrValidation)
	}
}

func validateProviderMode(value, providerType string) error {
	switch value {
	case string(ModeLocal), string(ModeSandbox), string(ModeContracted):
		return nil
	case string(ModeDisabled):
		if providerType == "custody" || providerType == "market_data" || providerType == "blockchain_analytics" {
			return nil
		}
	}
	return fmt.Errorf("%w: invalid provider mode", domain.ErrValidation)
}

func validateIntegrationStatus(value string) error {
	switch value {
	case IntegrationStatusDraft, IntegrationStatusConfigured, IntegrationStatusApproved, IntegrationStatusDisabled, IntegrationStatusFailed:
		return nil
	default:
		return fmt.Errorf("%w: invalid provider status", domain.ErrValidation)
	}
}

func validateEnvironment(value string) error {
	switch value {
	case "development", "test", "ci", "staging", "preprod", "production":
		return nil
	default:
		return fmt.Errorf("%w: invalid environment", domain.ErrValidation)
	}
}

func validateProviderBudgets(timeoutMS, attempts, failures, cooldownSeconds int) error {
	if timeoutMS < 100 || timeoutMS > 30000 {
		return fmt.Errorf("%w: timeout_ms must be between 100 and 30000", domain.ErrValidation)
	}
	if attempts < 1 || attempts > 5 {
		return fmt.Errorf("%w: retry_max_attempts must be between 1 and 5", domain.ErrValidation)
	}
	if failures < 1 || failures > 100 {
		return fmt.Errorf("%w: circuit_failure_threshold must be between 1 and 100", domain.ErrValidation)
	}
	if cooldownSeconds < 1 || cooldownSeconds > 3600 {
		return fmt.Errorf("%w: circuit_cooldown_seconds must be between 1 and 3600", domain.ErrValidation)
	}
	return nil
}

func validOwnerTeam(value string) bool {
	switch value {
	case "operations", "finance", "compliance", "risk", "support", "security", "engineering":
		return true
	default:
		return false
	}
}

func productionRisk(integration Integration) bool {
	return integration.ProductionEnabled &&
		(integration.Mode != string(ModeContracted) ||
			integration.Status != IntegrationStatusApproved ||
			integration.EvidenceReference == "" ||
			integration.CredentialsReference == "")
}

func normalizeLimit(limit int) int {
	if limit <= 0 || limit > 100 {
		return 50
	}
	return limit
}

func trimLen(value string, max int) string {
	if len(value) <= max {
		return value
	}
	return value[:max]
}

func isForeignKeyViolation(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && pgErr.Code == "23503"
}

func isUniqueViolation(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) && pgErr.Code == "23505"
}