package compliance

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

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

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

type Repository struct {
	db  *pgxpool.Pool
	aml *aml.Repository
}

type DashboardMetrics struct {
	OpenAMLCases       int64 `json:"open_aml_cases"`
	OverdueAMLCases    int64 `json:"overdue_aml_cases"`
	HighRiskCustomers  int64 `json:"high_risk_customers"`
	OpenEDDReviews     int64 `json:"open_edd_reviews"`
	RegulatoryDrafts   int64 `json:"regulatory_drafts"`
	ActiveTuningRules  int64 `json:"active_tuning_rules"`
	FalsePositive30Day int64 `json:"false_positive_30d"`
}

type Dashboard struct {
	Metrics           DashboardMetrics   `json:"metrics"`
	CustomerRisk      []CustomerRisk     `json:"customer_risk"`
	EDDReviews        []EDDReview        `json:"edd_reviews"`
	RegulatoryReports []RegulatoryReport `json:"regulatory_reports"`
	TuningRules       []TuningRule       `json:"tuning_rules"`
}

type CustomerRisk struct {
	ID           string          `json:"id"`
	UserID       string          `json:"user_id"`
	UserEmail    string          `json:"user_email,omitempty"`
	UserFullName string          `json:"user_full_name,omitempty"`
	RiskScore    int             `json:"risk_score"`
	RiskTier     string          `json:"risk_tier"`
	Factors      json.RawMessage `json:"factors"`
	Status       string          `json:"status"`
	LastReviewAt time.Time       `json:"last_review_at"`
	NextReviewAt time.Time       `json:"next_review_at"`
	CreatedAt    time.Time       `json:"created_at"`
	UpdatedAt    time.Time       `json:"updated_at"`
}

type EDDReview struct {
	ID                     string          `json:"id"`
	UserID                 string          `json:"user_id"`
	AMLCaseID              string          `json:"aml_case_id,omitempty"`
	Status                 string          `json:"status"`
	Reason                 string          `json:"reason"`
	RequiredEvidence       json.RawMessage `json:"required_evidence"`
	DecisionNote           string          `json:"decision_note,omitempty"`
	RequestedByAdminUserID string          `json:"requested_by_admin_user_id,omitempty"`
	DecidedByAdminUserID   string          `json:"decided_by_admin_user_id,omitempty"`
	DueAt                  *time.Time      `json:"due_at,omitempty"`
	DecidedAt              *time.Time      `json:"decided_at,omitempty"`
	CreatedAt              time.Time       `json:"created_at"`
	UpdatedAt              time.Time       `json:"updated_at"`
}

type EDDReviewParams struct {
	UserID                 string
	AMLCaseID              string
	Reason                 string
	RequiredEvidence       []string
	RequestedByAdminUserID string
	DueAt                  *time.Time
}

type MonitoringRun struct {
	ID                     string     `json:"id"`
	RunType                string     `json:"run_type"`
	Provider               string     `json:"provider"`
	Status                 string     `json:"status"`
	RequestedByAdminUserID string     `json:"requested_by_admin_user_id,omitempty"`
	UserID                 string     `json:"user_id,omitempty"`
	TotalCount             int        `json:"total_count"`
	ScreenedCount          int        `json:"screened_count"`
	ReviewCount            int        `json:"review_count"`
	HitCount               int        `json:"hit_count"`
	ErrorCount             int        `json:"error_count"`
	StartedAt              time.Time  `json:"started_at"`
	FinishedAt             *time.Time `json:"finished_at,omitempty"`
	CreatedAt              time.Time  `json:"created_at"`
}

type RegulatoryReport struct {
	ID                     string     `json:"id"`
	AMLCaseID              string     `json:"aml_case_id"`
	UserID                 string     `json:"user_id"`
	ReportType             string     `json:"report_type"`
	Status                 string     `json:"status"`
	Jurisdiction           string     `json:"jurisdiction,omitempty"`
	FilingReference        string     `json:"filing_reference,omitempty"`
	Narrative              string     `json:"narrative,omitempty"`
	CreatedByAdminUserID   string     `json:"created_by_admin_user_id,omitempty"`
	SubmittedByAdminUserID string     `json:"submitted_by_admin_user_id,omitempty"`
	SubmittedAt            *time.Time `json:"submitted_at,omitempty"`
	CreatedAt              time.Time  `json:"created_at"`
	UpdatedAt              time.Time  `json:"updated_at"`
}

type RegulatoryReportParams struct {
	AMLCaseID    string
	ReportType   string
	Jurisdiction string
	Narrative    string
	AdminUserID  string
}

type TuningRule struct {
	ID                   string    `json:"id"`
	Signal               string    `json:"signal"`
	Description          string    `json:"description"`
	Weight               int       `json:"weight"`
	ReviewThreshold      int       `json:"review_threshold"`
	BlockThreshold       int       `json:"block_threshold"`
	Enabled              bool      `json:"enabled"`
	FalsePositiveCount   int       `json:"false_positive_count"`
	TruePositiveCount    int       `json:"true_positive_count"`
	UpdatedByAdminUserID string    `json:"updated_by_admin_user_id,omitempty"`
	CreatedAt            time.Time `json:"created_at"`
	UpdatedAt            time.Time `json:"updated_at"`
}

type TuningRuleParams struct {
	ID              string
	Signal          string
	Description     string
	Weight          int
	ReviewThreshold int
	BlockThreshold  int
	Enabled         bool
	AdminUserID     string
}

type monitoringProfile struct {
	UserID      string
	ProfileID   string
	LegalName   string
	DateOfBirth string
	Country     string
}

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

func (r *Repository) Dashboard(ctx context.Context, limit int) (Dashboard, error) {
	metrics, err := r.Metrics(ctx)
	if err != nil {
		return Dashboard{}, err
	}
	risks, err := r.ListCustomerRisk(ctx, "", limit)
	if err != nil {
		return Dashboard{}, err
	}
	edd, err := r.ListEDDReviews(ctx, "open", limit)
	if err != nil {
		return Dashboard{}, err
	}
	reports, err := r.ListRegulatoryReports(ctx, "", limit)
	if err != nil {
		return Dashboard{}, err
	}
	rules, err := r.ListTuningRules(ctx, limit)
	if err != nil {
		return Dashboard{}, err
	}
	return Dashboard{Metrics: metrics, CustomerRisk: risks, EDDReviews: edd, RegulatoryReports: reports, TuningRules: rules}, nil
}

func (r *Repository) Metrics(ctx context.Context) (DashboardMetrics, error) {
	var metrics DashboardMetrics
	err := r.db.QueryRow(ctx, `
		SELECT
			(SELECT COUNT(*) FROM aml_cases WHERE status IN ('open', 'reviewing', 'escalated'))::bigint,
			(SELECT COUNT(*) FROM aml_cases WHERE status IN ('open', 'reviewing', 'escalated') AND sla_due_at < now())::bigint,
			(SELECT COUNT(*) FROM customer_risk_profiles WHERE status = 'active' AND risk_tier IN ('high', 'prohibited'))::bigint,
			(SELECT COUNT(*) FROM enhanced_due_diligence_reviews WHERE status IN ('requested', 'in_progress'))::bigint,
			(SELECT COUNT(*) FROM regulatory_reports WHERE status IN ('draft', 'in_review'))::bigint,
			(SELECT COUNT(*) FROM compliance_tuning_rules WHERE enabled = true)::bigint,
			(SELECT COUNT(*) FROM aml_false_positive_feedback WHERE outcome = 'confirmed_false_positive' AND created_at >= now() - interval '30 days')::bigint
	`).Scan(
		&metrics.OpenAMLCases,
		&metrics.OverdueAMLCases,
		&metrics.HighRiskCustomers,
		&metrics.OpenEDDReviews,
		&metrics.RegulatoryDrafts,
		&metrics.ActiveTuningRules,
		&metrics.FalsePositive30Day,
	)
	return metrics, err
}

func (r *Repository) RecalculateCustomerRisk(ctx context.Context, userID string) (CustomerRisk, error) {
	facts, err := r.customerRiskFacts(ctx, userID)
	if err != nil {
		return CustomerRisk{}, err
	}
	score := 10
	factors := map[string]any{
		"kyc_status":             facts.KYCStatus,
		"country":                facts.Country,
		"latest_screening":       facts.LatestScreeningStatus,
		"latest_screening_score": facts.LatestScreeningScore,
		"open_aml_cases":         facts.OpenCases,
		"escalated_aml_cases":    facts.EscalatedCases,
		"risk_reviews_30d":       facts.RiskReviews30d,
		"risk_blocks_30d":        facts.RiskBlocks30d,
	}
	switch facts.KYCStatus {
	case "manual_review":
		score += 25
	case "rejected":
		score += 40
	case "pending", "not_started":
		score += 10
	}
	if highRiskCountry(facts.Country) {
		score += 30
		factors["high_risk_country"] = true
	}
	if facts.LatestScreeningScore > 0 {
		score += facts.LatestScreeningScore / 2
	}
	score += facts.OpenCases * 15
	score += facts.EscalatedCases * 20
	score += facts.RiskReviews30d * 3
	score += facts.RiskBlocks30d * 8
	if score > 100 {
		score = 100
	}
	tier := riskTier(score)
	nextReviewAt := nextReview(tier)
	raw, err := json.Marshal(factors)
	if err != nil {
		return CustomerRisk{}, err
	}

	row := r.db.QueryRow(ctx, `
		INSERT INTO customer_risk_profiles (user_id, risk_score, risk_tier, factors, next_review_at)
		VALUES ($1, $2, $3, $4::jsonb, $5)
		ON CONFLICT (user_id) DO UPDATE
		SET risk_score = EXCLUDED.risk_score,
			risk_tier = EXCLUDED.risk_tier,
			factors = EXCLUDED.factors,
			last_review_at = now(),
			next_review_at = EXCLUDED.next_review_at,
			status = 'active'
		RETURNING id::text, user_id::text, ''::text, ''::text, risk_score, risk_tier, factors, status,
			last_review_at, next_review_at, created_at, updated_at
	`, userID, score, tier, string(raw), nextReviewAt)
	return scanCustomerRisk(row)
}

func (r *Repository) ListCustomerRisk(ctx context.Context, tier string, limit int) ([]CustomerRisk, error) {
	limit = normalizeLimit(limit)
	rows, err := r.db.Query(ctx, `
		SELECT crp.id::text, crp.user_id::text, u.email, u.full_name, crp.risk_score, crp.risk_tier,
			crp.factors, crp.status, crp.last_review_at, crp.next_review_at, crp.created_at, crp.updated_at
		FROM customer_risk_profiles crp
		JOIN users u ON u.id = crp.user_id
		WHERE NULLIF($1, '') IS NULL OR crp.risk_tier = $1
		ORDER BY crp.risk_score DESC, crp.next_review_at ASC
		LIMIT $2
	`, tier, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	profiles := []CustomerRisk{}
	for rows.Next() {
		profile, err := scanCustomerRisk(rows)
		if err != nil {
			return nil, err
		}
		profiles = append(profiles, profile)
	}
	return profiles, rows.Err()
}

func (r *Repository) RunOngoingMonitoring(ctx context.Context, userID, adminUserID string, provider sanctions.Provider) (MonitoringRun, error) {
	if provider == nil {
		return MonitoringRun{}, fmt.Errorf("%w: sanctions provider is not configured", domain.ErrValidation)
	}
	profiles, err := r.monitoringProfiles(ctx, userID)
	if err != nil {
		return MonitoringRun{}, err
	}
	runType := "batch"
	if userID != "" {
		runType = "user"
	}
	runID, err := r.createMonitoringRun(ctx, runType, "local_sanctions", userID, adminUserID, len(profiles))
	if err != nil {
		return MonitoringRun{}, err
	}
	screened, reviews, hits, errorsCount := 0, 0, 0, 0
	for _, profile := range profiles {
		result, err := provider.ScreenPerson(ctx, sanctions.ScreenPersonParams{
			UserID:      profile.UserID,
			LegalName:   profile.LegalName,
			DateOfBirth: profile.DateOfBirth,
			Country:     profile.Country,
		})
		if err != nil {
			errorsCount++
			continue
		}
		screened++
		screening, err := r.aml.CreateScreening(ctx, aml.CreateScreeningParams{
			UserID:        profile.UserID,
			KYCProfileID:  profile.ProfileID,
			ScreeningType: "ongoing_monitoring",
			Result:        result,
		})
		if err != nil {
			errorsCount++
			continue
		}
		switch result.Status {
		case "hit":
			hits++
		case "review":
			reviews++
		}
		if result.Matched || result.RiskScore >= 70 {
			if openCase, err := r.aml.HasOpenCase(ctx, profile.UserID); err != nil {
				errorsCount++
			} else if !openCase {
				severity := "medium"
				if result.RiskScore >= 90 {
					severity = "critical"
				} else if result.RiskScore >= 80 {
					severity = "high"
				}
				if _, err := r.aml.CreateCase(ctx, screening, "ongoing_monitoring", severity, result.Status); err != nil {
					errorsCount++
				}
			}
		}
		if _, err := r.RecalculateCustomerRisk(ctx, profile.UserID); err != nil {
			errorsCount++
		}
	}
	status := "completed"
	if errorsCount > 0 {
		status = "failed"
	}
	return r.finishMonitoringRun(ctx, runID, status, screened, reviews, hits, errorsCount)
}

func (r *Repository) CreateEDDReview(ctx context.Context, params EDDReviewParams) (EDDReview, error) {
	required, err := json.Marshal(params.RequiredEvidence)
	if err != nil {
		return EDDReview{}, err
	}
	if string(required) == "null" {
		required = []byte("[]")
	}
	row := r.db.QueryRow(ctx, `
		INSERT INTO enhanced_due_diligence_reviews (
			user_id, aml_case_id, reason, required_evidence, requested_by_admin_user_id, due_at
		)
		VALUES ($1, NULLIF($2, '')::uuid, $3, $4::jsonb, NULLIF($5, '')::uuid, $6)
		RETURNING id::text, user_id::text, COALESCE(aml_case_id::text, ''), status, reason, required_evidence,
			decision_note, COALESCE(requested_by_admin_user_id::text, ''), COALESCE(decided_by_admin_user_id::text, ''),
			due_at, decided_at, created_at, updated_at
	`, params.UserID, params.AMLCaseID, params.Reason, string(required), params.RequestedByAdminUserID, params.DueAt)
	return scanEDDReview(row)
}

func (r *Repository) ListEDDReviews(ctx context.Context, status string, limit int) ([]EDDReview, error) {
	limit = normalizeLimit(limit)
	status = normalizeOpenStatus(status)
	rows, err := r.db.Query(ctx, `
		SELECT id::text, user_id::text, COALESCE(aml_case_id::text, ''), status, reason, required_evidence,
			decision_note, COALESCE(requested_by_admin_user_id::text, ''), COALESCE(decided_by_admin_user_id::text, ''),
			due_at, decided_at, created_at, updated_at
		FROM enhanced_due_diligence_reviews
		WHERE NULLIF($1, '') IS NULL
			OR ($1 = 'open' AND status IN ('requested', 'in_progress'))
			OR status = $1
		ORDER BY updated_at DESC
		LIMIT $2
	`, status, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	reviews := []EDDReview{}
	for rows.Next() {
		review, err := scanEDDReview(rows)
		if err != nil {
			return nil, err
		}
		reviews = append(reviews, review)
	}
	return reviews, rows.Err()
}

func (r *Repository) DecideEDDReview(ctx context.Context, reviewID, status, decisionNote, adminUserID string) (EDDReview, error) {
	row := r.db.QueryRow(ctx, `
		UPDATE enhanced_due_diligence_reviews
		SET status = $2,
			decision_note = $3,
			decided_by_admin_user_id = NULLIF($4, '')::uuid,
			decided_at = CASE WHEN $2 IN ('approved', 'rejected', 'closed') THEN now() ELSE decided_at END
		WHERE id = $1
		RETURNING id::text, user_id::text, COALESCE(aml_case_id::text, ''), status, reason, required_evidence,
			decision_note, COALESCE(requested_by_admin_user_id::text, ''), COALESCE(decided_by_admin_user_id::text, ''),
			due_at, decided_at, created_at, updated_at
	`, reviewID, status, decisionNote, adminUserID)
	review, err := scanEDDReview(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return EDDReview{}, domain.ErrNotFound
	}
	return review, err
}

func (r *Repository) CreateRegulatoryReport(ctx context.Context, params RegulatoryReportParams) (RegulatoryReport, error) {
	var userID string
	err := r.db.QueryRow(ctx, `SELECT user_id::text FROM aml_cases WHERE id = $1`, params.AMLCaseID).Scan(&userID)
	if errors.Is(err, pgx.ErrNoRows) {
		return RegulatoryReport{}, domain.ErrNotFound
	}
	if err != nil {
		return RegulatoryReport{}, err
	}
	tx, err := r.db.BeginTx(ctx, pgx.TxOptions{})
	if err != nil {
		return RegulatoryReport{}, err
	}
	defer tx.Rollback(ctx)
	row := tx.QueryRow(ctx, `
		INSERT INTO regulatory_reports (
			aml_case_id, user_id, report_type, jurisdiction, narrative, created_by_admin_user_id
		)
		VALUES ($1, $2, $3, $4, $5, NULLIF($6, '')::uuid)
		RETURNING id::text, aml_case_id::text, user_id::text, report_type, status, jurisdiction,
			filing_reference, narrative, COALESCE(created_by_admin_user_id::text, ''),
			COALESCE(submitted_by_admin_user_id::text, ''), submitted_at, created_at, updated_at
	`, params.AMLCaseID, userID, params.ReportType, params.Jurisdiction, params.Narrative, params.AdminUserID)
	report, err := scanRegulatoryReport(row)
	if err != nil {
		return RegulatoryReport{}, err
	}
	if _, err := tx.Exec(ctx, `UPDATE aml_cases SET regulatory_report_required = true WHERE id = $1`, params.AMLCaseID); err != nil {
		return RegulatoryReport{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return RegulatoryReport{}, err
	}
	return report, nil
}

func (r *Repository) ListRegulatoryReports(ctx context.Context, status string, limit int) ([]RegulatoryReport, error) {
	limit = normalizeLimit(limit)
	rows, err := r.db.Query(ctx, `
		SELECT id::text, aml_case_id::text, user_id::text, report_type, status, jurisdiction,
			filing_reference, narrative, COALESCE(created_by_admin_user_id::text, ''),
			COALESCE(submitted_by_admin_user_id::text, ''), submitted_at, created_at, updated_at
		FROM regulatory_reports
		WHERE NULLIF($1, '') IS NULL OR status = $1
		ORDER BY updated_at DESC
		LIMIT $2
	`, status, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	reports := []RegulatoryReport{}
	for rows.Next() {
		report, err := scanRegulatoryReport(rows)
		if err != nil {
			return nil, err
		}
		reports = append(reports, report)
	}
	return reports, rows.Err()
}

func (r *Repository) UpdateRegulatoryReport(ctx context.Context, reportID, status, filingReference, narrative, adminUserID string) (RegulatoryReport, error) {
	row := r.db.QueryRow(ctx, `
		UPDATE regulatory_reports
		SET status = CASE WHEN NULLIF($2, '') IS NULL THEN status ELSE $2 END,
			filing_reference = CASE WHEN $3::text IS NULL THEN filing_reference ELSE $3 END,
			narrative = CASE WHEN $4::text IS NULL THEN narrative ELSE $4 END,
			submitted_by_admin_user_id = CASE WHEN NULLIF($2, '') = 'submitted' THEN NULLIF($5, '')::uuid ELSE submitted_by_admin_user_id END,
			submitted_at = CASE WHEN NULLIF($2, '') = 'submitted' THEN now() ELSE submitted_at END
		WHERE id = $1
		RETURNING id::text, aml_case_id::text, user_id::text, report_type, status, jurisdiction,
			filing_reference, narrative, COALESCE(created_by_admin_user_id::text, ''),
			COALESCE(submitted_by_admin_user_id::text, ''), submitted_at, created_at, updated_at
	`, reportID, status, emptyStringToNil(filingReference), emptyStringToNil(narrative), adminUserID)
	report, err := scanRegulatoryReport(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return RegulatoryReport{}, domain.ErrNotFound
	}
	return report, err
}

func (r *Repository) ListTuningRules(ctx context.Context, limit int) ([]TuningRule, error) {
	limit = normalizeLimit(limit)
	rows, err := r.db.Query(ctx, `
		SELECT id::text, signal, description, weight, review_threshold, block_threshold, enabled,
			false_positive_count, true_positive_count, COALESCE(updated_by_admin_user_id::text, ''), created_at, updated_at
		FROM compliance_tuning_rules
		ORDER BY signal ASC
		LIMIT $1
	`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	rules := []TuningRule{}
	for rows.Next() {
		rule, err := scanTuningRule(rows)
		if err != nil {
			return nil, err
		}
		rules = append(rules, rule)
	}
	return rules, rows.Err()
}

func (r *Repository) UpsertTuningRule(ctx context.Context, params TuningRuleParams) (TuningRule, error) {
	if params.ID != "" {
		row := r.db.QueryRow(ctx, `
			UPDATE compliance_tuning_rules
			SET signal = $2,
				description = $3,
				weight = $4,
				review_threshold = $5,
				block_threshold = $6,
				enabled = $7,
				updated_by_admin_user_id = NULLIF($8, '')::uuid
			WHERE id = $1
			RETURNING id::text, signal, description, weight, review_threshold, block_threshold, enabled,
				false_positive_count, true_positive_count, COALESCE(updated_by_admin_user_id::text, ''), created_at, updated_at
		`, params.ID, params.Signal, params.Description, params.Weight, params.ReviewThreshold, params.BlockThreshold, params.Enabled, params.AdminUserID)
		rule, err := scanTuningRule(row)
		if errors.Is(err, pgx.ErrNoRows) {
			return TuningRule{}, domain.ErrNotFound
		}
		return rule, err
	}
	row := r.db.QueryRow(ctx, `
		INSERT INTO compliance_tuning_rules (
			signal, description, weight, review_threshold, block_threshold, enabled, updated_by_admin_user_id
		)
		VALUES ($1, $2, $3, $4, $5, $6, NULLIF($7, '')::uuid)
		ON CONFLICT (signal) DO UPDATE
		SET description = EXCLUDED.description,
			weight = EXCLUDED.weight,
			review_threshold = EXCLUDED.review_threshold,
			block_threshold = EXCLUDED.block_threshold,
			enabled = EXCLUDED.enabled,
			updated_by_admin_user_id = EXCLUDED.updated_by_admin_user_id
		RETURNING id::text, signal, description, weight, review_threshold, block_threshold, enabled,
			false_positive_count, true_positive_count, COALESCE(updated_by_admin_user_id::text, ''), created_at, updated_at
	`, params.Signal, params.Description, params.Weight, params.ReviewThreshold, params.BlockThreshold, params.Enabled, params.AdminUserID)
	return scanTuningRule(row)
}

type riskFacts struct {
	KYCStatus             string
	Country               string
	LatestScreeningStatus string
	LatestScreeningScore  int
	OpenCases             int
	EscalatedCases        int
	RiskReviews30d        int
	RiskBlocks30d         int
}

func (r *Repository) customerRiskFacts(ctx context.Context, userID string) (riskFacts, error) {
	var facts riskFacts
	err := r.db.QueryRow(ctx, `
		SELECT
			COALESCE(kp.status, 'not_started'),
			COALESCE(kp.country::text, ''),
			COALESCE((SELECT s.status FROM aml_screenings s WHERE s.user_id = u.id ORDER BY s.created_at DESC LIMIT 1), ''),
			COALESCE((SELECT s.risk_score FROM aml_screenings s WHERE s.user_id = u.id ORDER BY s.created_at DESC LIMIT 1), 0),
			(SELECT COUNT(*) FROM aml_cases c WHERE c.user_id = u.id AND c.status IN ('open', 'reviewing', 'escalated'))::int,
			(SELECT COUNT(*) FROM aml_cases c WHERE c.user_id = u.id AND c.status = 'escalated')::int,
			(SELECT COUNT(*) FROM risk_events e WHERE e.user_id = u.id AND e.decision = 'review' AND e.created_at >= now() - interval '30 days')::int,
			(SELECT COUNT(*) FROM risk_events e WHERE e.user_id = u.id AND e.decision = 'block' AND e.created_at >= now() - interval '30 days')::int
		FROM users u
		LEFT JOIN kyc_profiles kp ON kp.user_id = u.id
		WHERE u.id = $1
	`, userID).Scan(
		&facts.KYCStatus,
		&facts.Country,
		&facts.LatestScreeningStatus,
		&facts.LatestScreeningScore,
		&facts.OpenCases,
		&facts.EscalatedCases,
		&facts.RiskReviews30d,
		&facts.RiskBlocks30d,
	)
	if errors.Is(err, pgx.ErrNoRows) {
		return riskFacts{}, domain.ErrNotFound
	}
	return facts, err
}

func (r *Repository) monitoringProfiles(ctx context.Context, userID string) ([]monitoringProfile, error) {
	rows, err := r.db.Query(ctx, `
		SELECT user_id::text, id::text, legal_name, date_of_birth::text, country::text
		FROM kyc_profiles
		WHERE status IN ('pending', 'verified', 'manual_review')
			AND (NULLIF($1, '') IS NULL OR user_id = $1::uuid)
		ORDER BY updated_at ASC
		LIMIT 100
	`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	profiles := []monitoringProfile{}
	for rows.Next() {
		var profile monitoringProfile
		if err := rows.Scan(&profile.UserID, &profile.ProfileID, &profile.LegalName, &profile.DateOfBirth, &profile.Country); err != nil {
			return nil, err
		}
		profiles = append(profiles, profile)
	}
	return profiles, rows.Err()
}

func (r *Repository) createMonitoringRun(ctx context.Context, runType, provider, userID, adminUserID string, total int) (string, error) {
	var id string
	err := r.db.QueryRow(ctx, `
		INSERT INTO compliance_monitoring_runs (
			run_type, provider, requested_by_admin_user_id, user_id, total_count
		)
		VALUES ($1, $2, NULLIF($3, '')::uuid, NULLIF($4, '')::uuid, $5)
		RETURNING id::text
	`, runType, provider, adminUserID, userID, total).Scan(&id)
	return id, err
}

func (r *Repository) finishMonitoringRun(ctx context.Context, runID, status string, screened, reviews, hits, errorsCount int) (MonitoringRun, error) {
	row := r.db.QueryRow(ctx, `
		UPDATE compliance_monitoring_runs
		SET status = $2,
			screened_count = $3,
			review_count = $4,
			hit_count = $5,
			error_count = $6,
			finished_at = now()
		WHERE id = $1
		RETURNING id::text, run_type, provider, status, COALESCE(requested_by_admin_user_id::text, ''),
			COALESCE(user_id::text, ''), total_count, screened_count, review_count, hit_count,
			error_count, started_at, finished_at, created_at
	`, runID, status, screened, reviews, hits, errorsCount)
	return scanMonitoringRun(row)
}

func scanCustomerRisk(row interface{ Scan(dest ...any) error }) (CustomerRisk, error) {
	var profile CustomerRisk
	err := row.Scan(
		&profile.ID,
		&profile.UserID,
		&profile.UserEmail,
		&profile.UserFullName,
		&profile.RiskScore,
		&profile.RiskTier,
		&profile.Factors,
		&profile.Status,
		&profile.LastReviewAt,
		&profile.NextReviewAt,
		&profile.CreatedAt,
		&profile.UpdatedAt,
	)
	return profile, err
}

func scanEDDReview(row interface{ Scan(dest ...any) error }) (EDDReview, error) {
	var review EDDReview
	var dueAt, decidedAt sql.NullTime
	err := row.Scan(
		&review.ID,
		&review.UserID,
		&review.AMLCaseID,
		&review.Status,
		&review.Reason,
		&review.RequiredEvidence,
		&review.DecisionNote,
		&review.RequestedByAdminUserID,
		&review.DecidedByAdminUserID,
		&dueAt,
		&decidedAt,
		&review.CreatedAt,
		&review.UpdatedAt,
	)
	if dueAt.Valid {
		review.DueAt = &dueAt.Time
	}
	if decidedAt.Valid {
		review.DecidedAt = &decidedAt.Time
	}
	return review, err
}

func scanMonitoringRun(row interface{ Scan(dest ...any) error }) (MonitoringRun, error) {
	var run MonitoringRun
	var finishedAt sql.NullTime
	err := row.Scan(
		&run.ID,
		&run.RunType,
		&run.Provider,
		&run.Status,
		&run.RequestedByAdminUserID,
		&run.UserID,
		&run.TotalCount,
		&run.ScreenedCount,
		&run.ReviewCount,
		&run.HitCount,
		&run.ErrorCount,
		&run.StartedAt,
		&finishedAt,
		&run.CreatedAt,
	)
	if finishedAt.Valid {
		run.FinishedAt = &finishedAt.Time
	}
	return run, err
}

func scanRegulatoryReport(row interface{ Scan(dest ...any) error }) (RegulatoryReport, error) {
	var report RegulatoryReport
	var submittedAt sql.NullTime
	err := row.Scan(
		&report.ID,
		&report.AMLCaseID,
		&report.UserID,
		&report.ReportType,
		&report.Status,
		&report.Jurisdiction,
		&report.FilingReference,
		&report.Narrative,
		&report.CreatedByAdminUserID,
		&report.SubmittedByAdminUserID,
		&submittedAt,
		&report.CreatedAt,
		&report.UpdatedAt,
	)
	if submittedAt.Valid {
		report.SubmittedAt = &submittedAt.Time
	}
	return report, err
}

func scanTuningRule(row interface{ Scan(dest ...any) error }) (TuningRule, error) {
	var rule TuningRule
	err := row.Scan(
		&rule.ID,
		&rule.Signal,
		&rule.Description,
		&rule.Weight,
		&rule.ReviewThreshold,
		&rule.BlockThreshold,
		&rule.Enabled,
		&rule.FalsePositiveCount,
		&rule.TruePositiveCount,
		&rule.UpdatedByAdminUserID,
		&rule.CreatedAt,
		&rule.UpdatedAt,
	)
	return rule, err
}

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

func normalizeOpenStatus(status string) string {
	status = strings.ToLower(strings.TrimSpace(status))
	if status == "all" {
		return ""
	}
	return status
}

func highRiskCountry(country string) bool {
	switch strings.ToUpper(strings.TrimSpace(country)) {
	case "IR", "KP", "SY", "RU", "BY":
		return true
	default:
		return false
	}
}

func riskTier(score int) string {
	switch {
	case score >= 90:
		return "prohibited"
	case score >= 70:
		return "high"
	case score >= 35:
		return "standard"
	default:
		return "low"
	}
}

func nextReview(tier string) time.Time {
	now := time.Now().UTC()
	switch tier {
	case "prohibited":
		return now.Add(30 * 24 * time.Hour)
	case "high":
		return now.Add(90 * 24 * time.Hour)
	case "standard":
		return now.Add(180 * 24 * time.Hour)
	default:
		return now.Add(365 * 24 * time.Hour)
	}
}

func emptyStringToNil(value string) any {
	if strings.TrimSpace(value) == "" {
		return nil
	}
	return strings.TrimSpace(value)
}