package risk

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 (
	OperationTransferInternal  = "transfer_internal"
	OperationTransferSEPA      = "transfer_sepa"
	OperationFXConversion      = "fx_conversion"
	OperationCardAuthorization = "card_authorization"

	DecisionAllow  = "allow"
	DecisionReview = "review"
	DecisionBlock  = "block"
)

type Repository struct {
	db *pgxpool.Pool
}

type LimitRule struct {
	ID                          string    `json:"id"`
	UserID                      string    `json:"user_id,omitempty"`
	Operation                   string    `json:"operation"`
	Currency                    string    `json:"currency,omitempty"`
	SingleTransactionLimitCents int64     `json:"single_transaction_limit_cents"`
	DailyLimitCents             int64     `json:"daily_limit_cents"`
	MonthlyLimitCents           int64     `json:"monthly_limit_cents"`
	DailyCountLimit             int       `json:"daily_count_limit"`
	MonthlyCountLimit           int       `json:"monthly_count_limit"`
	Active                      bool      `json:"active"`
	Description                 string    `json:"description"`
	CreatedByAdminUserID        string    `json:"created_by_admin_user_id,omitempty"`
	CreatedAt                   time.Time `json:"created_at"`
	UpdatedAt                   time.Time `json:"updated_at"`
}

type Event struct {
	ID           string          `json:"id"`
	UserID       string          `json:"user_id"`
	UserEmail    string          `json:"user_email,omitempty"`
	UserFullName string          `json:"user_full_name,omitempty"`
	Operation    string          `json:"operation"`
	SourceType   string          `json:"source_type"`
	SourceID     string          `json:"source_id,omitempty"`
	AccountID    string          `json:"account_id,omitempty"`
	Currency     string          `json:"currency"`
	AmountCents  int64           `json:"amount_cents"`
	Decision     string          `json:"decision"`
	Severity     string          `json:"severity"`
	Reason       string          `json:"reason"`
	LimitRuleID  string          `json:"limit_rule_id,omitempty"`
	Details      json.RawMessage `json:"details"`
	CreatedAt    time.Time       `json:"created_at"`
}

type Metrics struct {
	Allowed24h        int64 `json:"allowed_24h"`
	Review24h         int64 `json:"review_24h"`
	Blocked24h        int64 `json:"blocked_24h"`
	Critical24h       int64 `json:"critical_24h"`
	ActiveRules       int64 `json:"active_rules"`
	UserOverrides     int64 `json:"user_overrides"`
	CardBlocks24h     int64 `json:"card_blocks_24h"`
	TransferBlocks24h int64 `json:"transfer_blocks_24h"`
	FXBlocks24h       int64 `json:"fx_blocks_24h"`
}

type Dashboard struct {
	Metrics Metrics     `json:"metrics"`
	Rules   []LimitRule `json:"rules"`
	Events  []Event     `json:"events"`
}

type RuleParams struct {
	RuleID                      string
	AdminUserID                 string
	UserID                      string
	Operation                   string
	Currency                    string
	SingleTransactionLimitCents int64
	DailyLimitCents             int64
	MonthlyLimitCents           int64
	DailyCountLimit             int
	MonthlyCountLimit           int
	Active                      bool
	Description                 string
}

type CheckRequest struct {
	UserID      string
	AccountID   string
	Operation   string
	SourceType  string
	SourceID    string
	Currency    string
	AmountCents int64
	Metadata    map[string]any
}

type Evaluation struct {
	Decision           string     `json:"decision"`
	Severity           string     `json:"severity"`
	Reason             string     `json:"reason"`
	Rule               *LimitRule `json:"rule,omitempty"`
	DailyAmountCents   int64      `json:"daily_amount_cents"`
	DailyCount         int64      `json:"daily_count"`
	MonthlyAmountCents int64      `json:"monthly_amount_cents"`
	MonthlyCount       int64      `json:"monthly_count"`
}

type usage struct {
	DailyAmountCents   int64
	DailyCount         int64
	MonthlyAmountCents int64
	MonthlyCount       int64
}

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

func (r *Repository) Dashboard(ctx context.Context, decision, operation string, limit int) (Dashboard, error) {
	metrics, err := r.Metrics(ctx)
	if err != nil {
		return Dashboard{}, err
	}
	rules, err := r.ListRules(ctx, operation, "", true, limit)
	if err != nil {
		return Dashboard{}, err
	}
	events, err := r.ListEvents(ctx, decision, operation, limit)
	if err != nil {
		return Dashboard{}, err
	}
	return Dashboard{Metrics: metrics, Rules: rules, Events: events}, nil
}

func (r *Repository) Metrics(ctx context.Context) (Metrics, error) {
	var metrics Metrics
	err := r.db.QueryRow(ctx, `
		SELECT
			(SELECT COUNT(*) FROM risk_events WHERE decision = 'allow' AND created_at >= now() - interval '24 hours')::bigint,
			(SELECT COUNT(*) FROM risk_events WHERE decision = 'review' AND created_at >= now() - interval '24 hours')::bigint,
			(SELECT COUNT(*) FROM risk_events WHERE decision = 'block' AND created_at >= now() - interval '24 hours')::bigint,
			(SELECT COUNT(*) FROM risk_events WHERE severity = 'critical' AND created_at >= now() - interval '24 hours')::bigint,
			(SELECT COUNT(*) FROM payment_limit_rules WHERE active = true)::bigint,
			(SELECT COUNT(*) FROM payment_limit_rules WHERE active = true AND user_id IS NOT NULL)::bigint,
			(SELECT COUNT(*) FROM risk_events WHERE decision = 'block' AND operation = 'card_authorization' AND created_at >= now() - interval '24 hours')::bigint,
			(SELECT COUNT(*) FROM risk_events WHERE decision = 'block' AND operation IN ('transfer_internal', 'transfer_sepa') AND created_at >= now() - interval '24 hours')::bigint,
			(SELECT COUNT(*) FROM risk_events WHERE decision = 'block' AND operation = 'fx_conversion' AND created_at >= now() - interval '24 hours')::bigint
	`).Scan(
		&metrics.Allowed24h,
		&metrics.Review24h,
		&metrics.Blocked24h,
		&metrics.Critical24h,
		&metrics.ActiveRules,
		&metrics.UserOverrides,
		&metrics.CardBlocks24h,
		&metrics.TransferBlocks24h,
		&metrics.FXBlocks24h,
	)
	return metrics, err
}

func (r *Repository) ListRules(ctx context.Context, operation, userID string, activeOnly bool, limit int) ([]LimitRule, error) {
	limit = normalizeLimit(limit)
	operation = strings.ToLower(strings.TrimSpace(operation))
	userID = strings.TrimSpace(userID)
	if operation != "" && operation != "all" {
		if err := validateOperation(operation); err != nil {
			return nil, err
		}
	}
	if userID != "" {
		if err := domain.ValidateUUID("user_id", userID); err != nil {
			return nil, err
		}
	}

	rows, err := r.db.Query(ctx, `
		SELECT id::text, COALESCE(user_id::text, ''), operation, COALESCE(TRIM(currency)::text, ''),
			single_transaction_limit_cents, daily_limit_cents, monthly_limit_cents,
			daily_count_limit, monthly_count_limit, active, description,
			COALESCE(created_by_admin_user_id::text, ''), created_at, updated_at
		FROM payment_limit_rules
		WHERE ($1 = '' OR $1 = 'all' OR operation = $1)
			AND (NULLIF($2, '') IS NULL OR user_id = NULLIF($2, '')::uuid)
			AND ($3 = false OR active = true)
		ORDER BY active DESC, user_id NULLS FIRST, operation, currency NULLS FIRST, updated_at DESC
		LIMIT $4
	`, operation, userID, activeOnly, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	rules := []LimitRule{}
	for rows.Next() {
		rule, err := scanRule(rows)
		if err != nil {
			return nil, err
		}
		rules = append(rules, rule)
	}
	return rules, rows.Err()
}

func (r *Repository) ListEvents(ctx context.Context, decision, operation string, limit int) ([]Event, error) {
	limit = normalizeLimit(limit)
	decision = strings.ToLower(strings.TrimSpace(decision))
	if decision == "" {
		decision = DecisionBlock
	}
	if decision != "all" && decision != DecisionAllow && decision != DecisionReview && decision != DecisionBlock {
		return nil, fmt.Errorf("%w: invalid risk decision", domain.ErrValidation)
	}
	operation = strings.ToLower(strings.TrimSpace(operation))
	if operation != "" && operation != "all" {
		if err := validateOperation(operation); err != nil {
			return nil, err
		}
	}

	rows, err := r.db.Query(ctx, `
		SELECT re.id::text, re.user_id::text, u.email, u.full_name, re.operation, re.source_type,
			COALESCE(re.source_id, ''), COALESCE(re.account_id::text, ''), TRIM(re.currency)::text,
			re.amount_cents, re.decision, re.severity, re.reason, COALESCE(re.limit_rule_id::text, ''),
			re.details, re.created_at
		FROM risk_events re
		JOIN users u ON u.id = re.user_id
		WHERE ($1 = 'all' OR re.decision = $1)
			AND ($2 = '' OR $2 = 'all' OR re.operation = $2)
		ORDER BY
			CASE re.severity WHEN 'critical' THEN 1 WHEN 'high' THEN 2 WHEN 'medium' THEN 3 ELSE 4 END,
			re.created_at DESC
		LIMIT $3
	`, decision, operation, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	events := []Event{}
	for rows.Next() {
		event, err := scanEvent(rows)
		if err != nil {
			return nil, err
		}
		events = append(events, event)
	}
	return events, rows.Err()
}

func (r *Repository) CreateRule(ctx context.Context, params RuleParams) (LimitRule, error) {
	if err := normalizeRuleParams(&params); err != nil {
		return LimitRule{}, err
	}
	row := r.db.QueryRow(ctx, `
		INSERT INTO payment_limit_rules (
			user_id, operation, currency, single_transaction_limit_cents, daily_limit_cents,
			monthly_limit_cents, daily_count_limit, monthly_count_limit, active,
			description, created_by_admin_user_id
		)
		VALUES (
			NULLIF($1, '')::uuid, $2, NULLIF($3, '')::char(3), $4, $5,
			$6, $7, $8, $9, $10, NULLIF($11, '')::uuid
		)
		RETURNING id::text, COALESCE(user_id::text, ''), operation, COALESCE(TRIM(currency)::text, ''),
			single_transaction_limit_cents, daily_limit_cents, monthly_limit_cents,
			daily_count_limit, monthly_count_limit, active, description,
			COALESCE(created_by_admin_user_id::text, ''), created_at, updated_at
	`, params.UserID, params.Operation, params.Currency, params.SingleTransactionLimitCents,
		params.DailyLimitCents, params.MonthlyLimitCents, params.DailyCountLimit, params.MonthlyCountLimit,
		params.Active, params.Description, params.AdminUserID)

	rule, err := scanRule(row)
	if err != nil && isUniqueViolation(err) {
		return LimitRule{}, fmt.Errorf("%w: active limit rule already exists for this scope", domain.ErrConflict)
	}
	if err != nil && isForeignKeyViolation(err) {
		return LimitRule{}, fmt.Errorf("%w: user or currency does not exist", domain.ErrValidation)
	}
	return rule, err
}

func (r *Repository) UpdateRule(ctx context.Context, params RuleParams) (LimitRule, error) {
	if err := domain.ValidateUUID("id", strings.TrimSpace(params.RuleID)); err != nil {
		return LimitRule{}, err
	}
	params.RuleID = strings.TrimSpace(params.RuleID)
	if err := normalizeRuleParams(&params); err != nil {
		return LimitRule{}, err
	}
	row := r.db.QueryRow(ctx, `
		UPDATE payment_limit_rules
		SET user_id = NULLIF($2, '')::uuid,
			operation = $3,
			currency = NULLIF($4, '')::char(3),
			single_transaction_limit_cents = $5,
			daily_limit_cents = $6,
			monthly_limit_cents = $7,
			daily_count_limit = $8,
			monthly_count_limit = $9,
			active = $10,
			description = $11
		WHERE id = $1
		RETURNING id::text, COALESCE(user_id::text, ''), operation, COALESCE(TRIM(currency)::text, ''),
			single_transaction_limit_cents, daily_limit_cents, monthly_limit_cents,
			daily_count_limit, monthly_count_limit, active, description,
			COALESCE(created_by_admin_user_id::text, ''), created_at, updated_at
	`, params.RuleID, params.UserID, params.Operation, params.Currency, params.SingleTransactionLimitCents,
		params.DailyLimitCents, params.MonthlyLimitCents, params.DailyCountLimit, params.MonthlyCountLimit,
		params.Active, params.Description)

	rule, err := scanRule(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return LimitRule{}, domain.ErrNotFound
	}
	if err != nil && isUniqueViolation(err) {
		return LimitRule{}, fmt.Errorf("%w: active limit rule already exists for this scope", domain.ErrConflict)
	}
	if err != nil && isForeignKeyViolation(err) {
		return LimitRule{}, fmt.Errorf("%w: user or currency does not exist", domain.ErrValidation)
	}
	return rule, err
}

func (r *Repository) EvaluateAndRecord(ctx context.Context, tx pgx.Tx, req CheckRequest) (Evaluation, error) {
	if err := normalizeCheckRequest(&req); err != nil {
		return Evaluation{}, err
	}

	rule, err := r.findApplicableRule(ctx, tx, req)
	if err != nil {
		return Evaluation{}, err
	}
	currentUsage, err := r.operationUsage(ctx, tx, req)
	if err != nil {
		return Evaluation{}, err
	}

	evaluation := evaluate(rule, currentUsage, req.AmountCents)
	if rule != nil {
		evaluation.Rule = rule
	}
	if err := insertEvent(ctx, tx, req, evaluation); err != nil {
		return Evaluation{}, err
	}
	return evaluation, nil
}

func IsBlocked(evaluation Evaluation) bool {
	return evaluation.Decision == DecisionBlock
}

func (r *Repository) findApplicableRule(ctx context.Context, tx pgx.Tx, req CheckRequest) (*LimitRule, error) {
	row := tx.QueryRow(ctx, `
		SELECT id::text, COALESCE(user_id::text, ''), operation, COALESCE(TRIM(currency)::text, ''),
			single_transaction_limit_cents, daily_limit_cents, monthly_limit_cents,
			daily_count_limit, monthly_count_limit, active, description,
			COALESCE(created_by_admin_user_id::text, ''), created_at, updated_at
		FROM payment_limit_rules
		WHERE active = true
			AND operation = $1
			AND (currency IS NULL OR TRIM(currency)::text = $2)
			AND (user_id IS NULL OR user_id = $3::uuid)
		ORDER BY
			CASE WHEN user_id = $3::uuid THEN 0 ELSE 2 END,
			CASE WHEN currency IS NOT NULL AND TRIM(currency)::text = $2 THEN 0 ELSE 1 END,
			updated_at DESC
		LIMIT 1
	`, req.Operation, req.Currency, req.UserID)

	rule, err := scanRule(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &rule, nil
}

func (r *Repository) operationUsage(ctx context.Context, tx pgx.Tx, req CheckRequest) (usage, error) {
	var query string
	var args []any
	switch req.Operation {
	case OperationTransferInternal, OperationTransferSEPA:
		transferType := "internal"
		if req.Operation == OperationTransferSEPA {
			transferType = "sepa"
		}
		query = `
			SELECT
				COALESCE(SUM(amount_cents) FILTER (WHERE created_at >= now() - interval '24 hours'), 0)::bigint,
				COUNT(*) FILTER (WHERE created_at >= now() - interval '24 hours')::bigint,
				COALESCE(SUM(amount_cents) FILTER (WHERE created_at >= now() - interval '30 days'), 0)::bigint,
				COUNT(*) FILTER (WHERE created_at >= now() - interval '30 days')::bigint
			FROM transfers
			WHERE user_id = $1
				AND transfer_type = $2
				AND TRIM(currency)::text = $3
				AND status IN ('pending', 'processing', 'review_held', 'completed')
		`
		args = []any{req.UserID, transferType, req.Currency}
	case OperationFXConversion:
		query = `
			SELECT
				COALESCE(SUM(from_amount_cents + fee_cents) FILTER (WHERE created_at >= now() - interval '24 hours'), 0)::bigint,
				COUNT(*) FILTER (WHERE created_at >= now() - interval '24 hours')::bigint,
				COALESCE(SUM(from_amount_cents + fee_cents) FILTER (WHERE created_at >= now() - interval '30 days'), 0)::bigint,
				COUNT(*) FILTER (WHERE created_at >= now() - interval '30 days')::bigint
			FROM fx_conversions
			WHERE user_id = $1
				AND TRIM(from_currency)::text = $2
				AND status = 'completed'
		`
		args = []any{req.UserID, req.Currency}
	case OperationCardAuthorization:
		query = `
			SELECT
				COALESCE(SUM(ca.amount_cents) FILTER (WHERE ca.created_at >= now() - interval '24 hours'), 0)::bigint,
				COUNT(*) FILTER (WHERE ca.created_at >= now() - interval '24 hours')::bigint,
				COALESCE(SUM(ca.amount_cents) FILTER (WHERE ca.created_at >= now() - interval '30 days'), 0)::bigint,
				COUNT(*) FILTER (WHERE ca.created_at >= now() - interval '30 days')::bigint
			FROM card_authorizations ca
			JOIN virtual_cards vc ON vc.id = ca.virtual_card_id
			WHERE vc.user_id = $1
				AND TRIM(ca.currency)::text = $2
				AND ca.status IN ('approved', 'cleared')
		`
		args = []any{req.UserID, req.Currency}
	default:
		return usage{}, fmt.Errorf("%w: invalid risk operation", domain.ErrValidation)
	}

	var current usage
	err := tx.QueryRow(ctx, query, args...).Scan(
		&current.DailyAmountCents,
		&current.DailyCount,
		&current.MonthlyAmountCents,
		&current.MonthlyCount,
	)
	return current, err
}

func evaluate(rule *LimitRule, current usage, amountCents int64) Evaluation {
	evaluation := Evaluation{
		Decision:           DecisionAllow,
		Severity:           "low",
		Reason:             "within_limits",
		DailyAmountCents:   current.DailyAmountCents,
		DailyCount:         current.DailyCount,
		MonthlyAmountCents: current.MonthlyAmountCents,
		MonthlyCount:       current.MonthlyCount,
	}
	if rule == nil {
		evaluation.Reason = "no_limit_rule"
		return evaluation
	}

	if rule.SingleTransactionLimitCents > 0 && amountCents > rule.SingleTransactionLimitCents {
		return blockEvaluation(evaluation, "single_transaction_limit_exceeded", "high")
	}
	if rule.DailyLimitCents > 0 && current.DailyAmountCents+amountCents > rule.DailyLimitCents {
		return blockEvaluation(evaluation, "daily_amount_limit_exceeded", "critical")
	}
	if rule.MonthlyLimitCents > 0 && current.MonthlyAmountCents+amountCents > rule.MonthlyLimitCents {
		return blockEvaluation(evaluation, "monthly_amount_limit_exceeded", "critical")
	}
	if rule.DailyCountLimit > 0 && current.DailyCount+1 > int64(rule.DailyCountLimit) {
		return blockEvaluation(evaluation, "daily_count_limit_exceeded", "high")
	}
	if rule.MonthlyCountLimit > 0 && current.MonthlyCount+1 > int64(rule.MonthlyCountLimit) {
		return blockEvaluation(evaluation, "monthly_count_limit_exceeded", "high")
	}

	if nearAmountLimit(amountCents, rule.SingleTransactionLimitCents) ||
		nearAmountLimit(current.DailyAmountCents+amountCents, rule.DailyLimitCents) ||
		nearAmountLimit(current.MonthlyAmountCents+amountCents, rule.MonthlyLimitCents) ||
		nearCountLimit(current.DailyCount+1, rule.DailyCountLimit) ||
		nearCountLimit(current.MonthlyCount+1, rule.MonthlyCountLimit) {
		evaluation.Decision = DecisionReview
		evaluation.Severity = "medium"
		evaluation.Reason = "near_limit_threshold"
	}
	return evaluation
}

func blockEvaluation(evaluation Evaluation, reason, severity string) Evaluation {
	evaluation.Decision = DecisionBlock
	evaluation.Severity = severity
	evaluation.Reason = reason
	return evaluation
}

func nearAmountLimit(value, limit int64) bool {
	return limit > 0 && value*100 >= limit*90
}

func nearCountLimit(value int64, limit int) bool {
	return limit > 0 && value*100 >= int64(limit)*90
}

func insertEvent(ctx context.Context, tx pgx.Tx, req CheckRequest, evaluation Evaluation) error {
	details := map[string]any{
		"daily_amount_cents":   evaluation.DailyAmountCents,
		"daily_count":          evaluation.DailyCount,
		"monthly_amount_cents": evaluation.MonthlyAmountCents,
		"monthly_count":        evaluation.MonthlyCount,
	}
	if evaluation.Rule != nil {
		details["rule_id"] = evaluation.Rule.ID
		details["single_transaction_limit_cents"] = evaluation.Rule.SingleTransactionLimitCents
		details["daily_limit_cents"] = evaluation.Rule.DailyLimitCents
		details["monthly_limit_cents"] = evaluation.Rule.MonthlyLimitCents
		details["daily_count_limit"] = evaluation.Rule.DailyCountLimit
		details["monthly_count_limit"] = evaluation.Rule.MonthlyCountLimit
	}
	for key, value := range req.Metadata {
		details[key] = value
	}
	encoded, err := json.Marshal(details)
	if err != nil {
		return err
	}

	var accountID any
	if req.AccountID != "" {
		accountID = req.AccountID
	}
	var ruleID any
	if evaluation.Rule != nil {
		ruleID = evaluation.Rule.ID
	}
	_, err = tx.Exec(ctx, `
		INSERT INTO risk_events (
			user_id, operation, source_type, source_id, account_id, currency, amount_cents,
			decision, severity, reason, limit_rule_id, details
		)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
	`, req.UserID, req.Operation, req.SourceType, req.SourceID, accountID, req.Currency,
		req.AmountCents, evaluation.Decision, evaluation.Severity, evaluation.Reason, ruleID, encoded)
	return err
}

func normalizeRuleParams(params *RuleParams) error {
	params.UserID = strings.TrimSpace(params.UserID)
	params.Operation = strings.ToLower(strings.TrimSpace(params.Operation))
	params.Currency = domain.NormalizeCurrency(params.Currency)
	params.Description = strings.TrimSpace(params.Description)
	if params.UserID != "" {
		if err := domain.ValidateUUID("user_id", params.UserID); err != nil {
			return err
		}
	}
	if err := validateOperation(params.Operation); err != nil {
		return err
	}
	if params.Currency != "" {
		if err := domain.ValidateCurrency(params.Currency); err != nil {
			return err
		}
	}
	if params.SingleTransactionLimitCents < 0 || params.DailyLimitCents < 0 || params.MonthlyLimitCents < 0 ||
		params.DailyCountLimit < 0 || params.MonthlyCountLimit < 0 {
		return fmt.Errorf("%w: limit values cannot be negative", domain.ErrValidation)
	}
	if params.SingleTransactionLimitCents == 0 && params.DailyLimitCents == 0 && params.MonthlyLimitCents == 0 &&
		params.DailyCountLimit == 0 && params.MonthlyCountLimit == 0 {
		return fmt.Errorf("%w: at least one limit must be greater than zero", domain.ErrValidation)
	}
	if params.Description == "" {
		params.Description = "Manual payment limit"
	}
	if len(params.Description) > 200 {
		return fmt.Errorf("%w: description must be 200 characters or fewer", domain.ErrValidation)
	}
	return nil
}

func normalizeCheckRequest(req *CheckRequest) error {
	req.UserID = strings.TrimSpace(req.UserID)
	req.AccountID = strings.TrimSpace(req.AccountID)
	req.Operation = strings.ToLower(strings.TrimSpace(req.Operation))
	req.SourceType = strings.TrimSpace(req.SourceType)
	req.SourceID = strings.TrimSpace(req.SourceID)
	req.Currency = domain.NormalizeCurrency(req.Currency)
	if err := domain.ValidateUUID("user_id", req.UserID); err != nil {
		return err
	}
	if req.AccountID != "" {
		if err := domain.ValidateUUID("account_id", req.AccountID); err != nil {
			return err
		}
	}
	if err := validateOperation(req.Operation); err != nil {
		return err
	}
	if req.SourceType == "" {
		req.SourceType = req.Operation
	}
	if len(req.SourceType) > 80 {
		return fmt.Errorf("%w: source_type must be 80 characters or fewer", domain.ErrValidation)
	}
	if len(req.SourceID) > 160 {
		return fmt.Errorf("%w: source_id must be 160 characters or fewer", domain.ErrValidation)
	}
	if err := domain.ValidateCurrency(req.Currency); err != nil {
		return err
	}
	if err := domain.ValidateAmount(req.AmountCents); err != nil {
		return err
	}
	if req.Metadata == nil {
		req.Metadata = map[string]any{}
	}
	return nil
}

func validateOperation(operation string) error {
	switch operation {
	case OperationTransferInternal, OperationTransferSEPA, OperationFXConversion, OperationCardAuthorization:
		return nil
	default:
		return fmt.Errorf("%w: invalid risk operation", domain.ErrValidation)
	}
}

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

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

func scanRule(row scanner) (LimitRule, error) {
	var rule LimitRule
	err := row.Scan(
		&rule.ID,
		&rule.UserID,
		&rule.Operation,
		&rule.Currency,
		&rule.SingleTransactionLimitCents,
		&rule.DailyLimitCents,
		&rule.MonthlyLimitCents,
		&rule.DailyCountLimit,
		&rule.MonthlyCountLimit,
		&rule.Active,
		&rule.Description,
		&rule.CreatedByAdminUserID,
		&rule.CreatedAt,
		&rule.UpdatedAt,
	)
	return rule, err
}

func scanEvent(row scanner) (Event, error) {
	var event Event
	var accountID sql.NullString
	var limitRuleID sql.NullString
	err := row.Scan(
		&event.ID,
		&event.UserID,
		&event.UserEmail,
		&event.UserFullName,
		&event.Operation,
		&event.SourceType,
		&event.SourceID,
		&accountID,
		&event.Currency,
		&event.AmountCents,
		&event.Decision,
		&event.Severity,
		&event.Reason,
		&limitRuleID,
		&event.Details,
		&event.CreatedAt,
	)
	if accountID.Valid {
		event.AccountID = accountID.String
	}
	if limitRuleID.Valid {
		event.LimitRuleID = limitRuleID.String
	}
	return event, err
}

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

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