package aml

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"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"
	"github.com/niels/banking-app/backend/internal/integrations/sanctions"
)

type Repository struct {
	db *pgxpool.Pool
}

type CreateScreeningParams struct {
	UserID        string
	KYCProfileID  string
	ScreeningType string
	Result        sanctions.ScreeningResult
}

type UpdateCaseParams struct {
	CaseID                   string
	ActorAdminUserID         string
	Status                   string
	ResolutionNote           *string
	AssignedToAdminUserID    *string
	SLADueAt                 *time.Time
	RegulatoryReportRequired *bool
	FalsePositive            *bool
	HistoryNote              string
}

type CaseNote struct {
	ID                string    `json:"id"`
	AMLCaseID         string    `json:"aml_case_id"`
	AuthorAdminUserID string    `json:"author_admin_user_id,omitempty"`
	NoteType          string    `json:"note_type"`
	Body              string    `json:"body"`
	CreatedAt         time.Time `json:"created_at"`
}

type CaseAttachment struct {
	ID                    string    `json:"id"`
	AMLCaseID             string    `json:"aml_case_id"`
	UploadedByAdminUserID string    `json:"uploaded_by_admin_user_id,omitempty"`
	FileName              string    `json:"file_name"`
	ContentType           string    `json:"content_type"`
	StorageURI            string    `json:"storage_uri,omitempty"`
	SHA256Hash            string    `json:"sha256_hash,omitempty"`
	Description           string    `json:"description,omitempty"`
	CreatedAt             time.Time `json:"created_at"`
}

type CaseStatusHistory struct {
	ID               string          `json:"id"`
	AMLCaseID        string          `json:"aml_case_id"`
	ActorAdminUserID string          `json:"actor_admin_user_id,omitempty"`
	FromStatus       string          `json:"from_status,omitempty"`
	ToStatus         string          `json:"to_status"`
	Note             string          `json:"note,omitempty"`
	Metadata         json.RawMessage `json:"metadata"`
	CreatedAt        time.Time       `json:"created_at"`
}

type CaseAttachmentParams struct {
	CaseID                string
	UploadedByAdminUserID string
	FileName              string
	ContentType           string
	StorageURI            string
	SHA256Hash            string
	Description           string
}

type FalsePositiveFeedback struct {
	ID                    string    `json:"id"`
	AMLCaseID             string    `json:"aml_case_id"`
	AMLScreeningID        string    `json:"aml_screening_id,omitempty"`
	TuningRuleID          string    `json:"tuning_rule_id,omitempty"`
	ReviewedByAdminUserID string    `json:"reviewed_by_admin_user_id,omitempty"`
	Outcome               string    `json:"outcome"`
	Reason                string    `json:"reason,omitempty"`
	CreatedAt             time.Time `json:"created_at"`
}

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

func (r *Repository) CreateScreening(ctx context.Context, params CreateScreeningParams) (domain.AMLScreening, error) {
	details, err := json.Marshal(params.Result.MatchDetails)
	if err != nil {
		return domain.AMLScreening{}, err
	}

	row := r.db.QueryRow(ctx, `
		INSERT INTO aml_screenings (
			user_id, kyc_profile_id, screening_type, provider, status, risk_score,
			matched, match_details, external_screening_id
		)
		VALUES ($1, NULLIF($2, '')::uuid, $3, $4, $5, $6, $7, $8::jsonb, $9)
		RETURNING id::text, user_id::text, COALESCE(kyc_profile_id::text, ''), screening_type, provider,
			status, risk_score, matched, match_details, COALESCE(external_screening_id, ''), created_at
	`, params.UserID, params.KYCProfileID, params.ScreeningType, params.Result.Provider, params.Result.Status,
		params.Result.RiskScore, params.Result.Matched, string(details), params.Result.ExternalScreeningID)
	return scanScreening(row)
}

func (r *Repository) CreateCase(ctx context.Context, screening domain.AMLScreening, caseType, severity, reason string) (domain.AMLCase, error) {
	tx, err := r.db.BeginTx(ctx, pgx.TxOptions{})
	if err != nil {
		return domain.AMLCase{}, err
	}
	defer tx.Rollback(ctx)

	row := tx.QueryRow(ctx, `
		INSERT INTO aml_cases (user_id, aml_screening_id, case_type, severity, reason, sla_due_at)
		VALUES ($1, $2, $3, $4, $5, $6)
		RETURNING id::text, user_id::text, COALESCE(aml_screening_id::text, ''), case_type, status, severity,
			reason, COALESCE(resolution_note, ''), COALESCE(assigned_to_admin_user_id::text, ''),
			sla_due_at, escalated_at, closed_at, regulatory_report_required, false_positive, created_at, updated_at
	`, screening.UserID, screening.ID, caseType, severity, reason, defaultSLADue(severity))
	amlCase, err := scanCase(row)
	if err != nil {
		return domain.AMLCase{}, err
	}
	if err := insertCaseHistory(ctx, tx, amlCase.ID, "", "", amlCase.Status, "case opened", map[string]any{
		"case_type":        caseType,
		"severity":         severity,
		"aml_screening_id": screening.ID,
	}); err != nil {
		return domain.AMLCase{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return domain.AMLCase{}, err
	}
	return amlCase, nil
}

func (r *Repository) HasOpenCase(ctx context.Context, userID string) (bool, error) {
	var exists bool
	err := r.db.QueryRow(ctx, `
		SELECT EXISTS (
			SELECT 1 FROM aml_cases
			WHERE user_id = $1 AND status IN ('open', 'reviewing', 'escalated')
		)
	`, userID).Scan(&exists)
	return exists, err
}

func (r *Repository) ListScreenings(ctx context.Context, userID string, limit int) ([]domain.AMLScreening, error) {
	if limit <= 0 || limit > 100 {
		limit = 50
	}

	rows, err := r.db.Query(ctx, `
		SELECT id::text, user_id::text, COALESCE(kyc_profile_id::text, ''), screening_type, provider,
			status, risk_score, matched, match_details, COALESCE(external_screening_id, ''), created_at
		FROM aml_screenings
		WHERE NULLIF($1, '') IS NULL OR user_id = $1
		ORDER BY created_at DESC
		LIMIT $2
	`, userID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	screenings := []domain.AMLScreening{}
	for rows.Next() {
		screening, err := scanScreening(rows)
		if err != nil {
			return nil, err
		}
		screenings = append(screenings, screening)
	}
	return screenings, rows.Err()
}

func (r *Repository) ListCases(ctx context.Context, status string, limit int) ([]domain.AMLCase, error) {
	if limit <= 0 || limit > 100 {
		limit = 50
	}

	rows, err := r.db.Query(ctx, `
		SELECT id::text, user_id::text, COALESCE(aml_screening_id::text, ''), case_type, status, severity,
			reason, COALESCE(resolution_note, ''), COALESCE(assigned_to_admin_user_id::text, ''),
			sla_due_at, escalated_at, closed_at, regulatory_report_required, false_positive, created_at, updated_at
		FROM aml_cases
		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()

	cases := []domain.AMLCase{}
	for rows.Next() {
		amlCase, err := scanCase(rows)
		if err != nil {
			return nil, err
		}
		cases = append(cases, amlCase)
	}
	return cases, rows.Err()
}

func (r *Repository) UpdateCaseStatus(ctx context.Context, caseID, status, resolutionNote string) (domain.AMLCase, error) {
	note := resolutionNote
	return r.UpdateCase(ctx, UpdateCaseParams{
		CaseID:         caseID,
		Status:         status,
		ResolutionNote: &note,
		HistoryNote:    note,
	})
}

func (r *Repository) UpdateCase(ctx context.Context, params UpdateCaseParams) (domain.AMLCase, error) {
	tx, err := r.db.BeginTx(ctx, pgx.TxOptions{})
	if err != nil {
		return domain.AMLCase{}, err
	}
	defer tx.Rollback(ctx)

	var oldStatus string
	err = tx.QueryRow(ctx, `SELECT status FROM aml_cases WHERE id = $1`, params.CaseID).Scan(&oldStatus)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.AMLCase{}, domain.ErrNotFound
	}
	if err != nil {
		return domain.AMLCase{}, err
	}

	row := tx.QueryRow(ctx, `
		UPDATE aml_cases
		SET status = CASE WHEN NULLIF($2, '') IS NULL THEN status ELSE $2 END,
			resolution_note = CASE WHEN $3::text IS NULL THEN resolution_note ELSE NULLIF($3, '') END,
			assigned_to_admin_user_id = CASE WHEN $4::text IS NULL THEN assigned_to_admin_user_id ELSE NULLIF($4, '')::uuid END,
			sla_due_at = COALESCE($5, sla_due_at),
			regulatory_report_required = COALESCE($6, regulatory_report_required),
			false_positive = COALESCE($7, false_positive),
			escalated_at = CASE
				WHEN NULLIF($2, '') = 'escalated' AND status <> 'escalated' THEN now()
				ELSE escalated_at
			END,
			closed_at = CASE
				WHEN NULLIF($2, '') = 'closed' AND status <> 'closed' THEN now()
				WHEN NULLIF($2, '') IS NOT NULL AND NULLIF($2, '') <> 'closed' THEN NULL
				ELSE closed_at
			END
		WHERE id = $1
		RETURNING id::text, user_id::text, COALESCE(aml_screening_id::text, ''), case_type, status, severity,
			reason, COALESCE(resolution_note, ''), COALESCE(assigned_to_admin_user_id::text, ''),
			sla_due_at, escalated_at, closed_at, regulatory_report_required, false_positive, created_at, updated_at
	`, params.CaseID, params.Status, stringPtrValue(params.ResolutionNote), stringPtrValue(params.AssignedToAdminUserID),
		params.SLADueAt, params.RegulatoryReportRequired, params.FalsePositive)

	amlCase, err := scanCase(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.AMLCase{}, domain.ErrNotFound
	}
	if err != nil {
		return domain.AMLCase{}, err
	}
	if err := insertCaseHistory(ctx, tx, params.CaseID, params.ActorAdminUserID, oldStatus, amlCase.Status, params.HistoryNote, map[string]any{
		"assigned_to_admin_user_id":    stringPtrValue(params.AssignedToAdminUserID),
		"sla_due_at":                   params.SLADueAt,
		"regulatory_report_required":   params.RegulatoryReportRequired,
		"false_positive":               params.FalsePositive,
		"resolution_note_was_provided": params.ResolutionNote != nil,
	}); err != nil {
		return domain.AMLCase{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return domain.AMLCase{}, err
	}
	return amlCase, nil
}

func (r *Repository) AddCaseNote(ctx context.Context, caseID, authorAdminUserID, noteType, body string) (CaseNote, error) {
	row := r.db.QueryRow(ctx, `
		INSERT INTO aml_case_notes (aml_case_id, author_admin_user_id, note_type, body)
		VALUES ($1, NULLIF($2, '')::uuid, $3, $4)
		RETURNING id::text, aml_case_id::text, COALESCE(author_admin_user_id::text, ''), note_type, body, created_at
	`, caseID, authorAdminUserID, noteType, body)
	return scanCaseNote(row)
}

func (r *Repository) ListCaseNotes(ctx context.Context, caseID string, limit int) ([]CaseNote, error) {
	if limit <= 0 || limit > 100 {
		limit = 50
	}
	rows, err := r.db.Query(ctx, `
		SELECT id::text, aml_case_id::text, COALESCE(author_admin_user_id::text, ''), note_type, body, created_at
		FROM aml_case_notes
		WHERE aml_case_id = $1
		ORDER BY created_at DESC
		LIMIT $2
	`, caseID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	notes := []CaseNote{}
	for rows.Next() {
		note, err := scanCaseNote(rows)
		if err != nil {
			return nil, err
		}
		notes = append(notes, note)
	}
	return notes, rows.Err()
}

func (r *Repository) AddCaseAttachment(ctx context.Context, params CaseAttachmentParams) (CaseAttachment, error) {
	row := r.db.QueryRow(ctx, `
		INSERT INTO aml_case_attachments (
			aml_case_id, uploaded_by_admin_user_id, file_name, content_type, storage_uri, sha256_hash, description
		)
		VALUES ($1, NULLIF($2, '')::uuid, $3, $4, $5, $6, $7)
		RETURNING id::text, aml_case_id::text, COALESCE(uploaded_by_admin_user_id::text, ''), file_name,
			content_type, storage_uri, sha256_hash, description, created_at
	`, params.CaseID, params.UploadedByAdminUserID, params.FileName, params.ContentType, params.StorageURI, params.SHA256Hash, params.Description)
	return scanCaseAttachment(row)
}

func (r *Repository) ListCaseAttachments(ctx context.Context, caseID string, limit int) ([]CaseAttachment, error) {
	if limit <= 0 || limit > 100 {
		limit = 50
	}
	rows, err := r.db.Query(ctx, `
		SELECT id::text, aml_case_id::text, COALESCE(uploaded_by_admin_user_id::text, ''), file_name,
			content_type, storage_uri, sha256_hash, description, created_at
		FROM aml_case_attachments
		WHERE aml_case_id = $1
		ORDER BY created_at DESC
		LIMIT $2
	`, caseID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	attachments := []CaseAttachment{}
	for rows.Next() {
		attachment, err := scanCaseAttachment(rows)
		if err != nil {
			return nil, err
		}
		attachments = append(attachments, attachment)
	}
	return attachments, rows.Err()
}

func (r *Repository) ListCaseHistory(ctx context.Context, caseID string, limit int) ([]CaseStatusHistory, error) {
	if limit <= 0 || limit > 100 {
		limit = 50
	}
	rows, err := r.db.Query(ctx, `
		SELECT id::text, aml_case_id::text, COALESCE(actor_admin_user_id::text, ''), from_status,
			to_status, note, metadata, created_at
		FROM aml_case_status_history
		WHERE aml_case_id = $1
		ORDER BY created_at DESC
		LIMIT $2
	`, caseID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	history := []CaseStatusHistory{}
	for rows.Next() {
		item, err := scanCaseStatusHistory(rows)
		if err != nil {
			return nil, err
		}
		history = append(history, item)
	}
	return history, rows.Err()
}

func (r *Repository) RecordFalsePositiveFeedback(ctx context.Context, caseID, screeningID, tuningRuleID, adminUserID, outcome, reason string) (FalsePositiveFeedback, error) {
	tx, err := r.db.BeginTx(ctx, pgx.TxOptions{})
	if err != nil {
		return FalsePositiveFeedback{}, err
	}
	defer tx.Rollback(ctx)

	row := tx.QueryRow(ctx, `
		INSERT INTO aml_false_positive_feedback (
			aml_case_id, aml_screening_id, tuning_rule_id, reviewed_by_admin_user_id, outcome, reason
		)
		VALUES ($1, NULLIF($2, '')::uuid, NULLIF($3, '')::uuid, NULLIF($4, '')::uuid, $5, $6)
		RETURNING id::text, aml_case_id::text, COALESCE(aml_screening_id::text, ''),
			COALESCE(tuning_rule_id::text, ''), COALESCE(reviewed_by_admin_user_id::text, ''), outcome, reason, created_at
	`, caseID, screeningID, tuningRuleID, adminUserID, outcome, reason)
	feedback, err := scanFalsePositiveFeedback(row)
	if err != nil {
		return FalsePositiveFeedback{}, err
	}

	if outcome == "confirmed_false_positive" {
		if _, err := tx.Exec(ctx, `UPDATE aml_cases SET false_positive = true WHERE id = $1`, caseID); err != nil {
			return FalsePositiveFeedback{}, err
		}
		if tuningRuleID != "" {
			if _, err := tx.Exec(ctx, `UPDATE compliance_tuning_rules SET false_positive_count = false_positive_count + 1 WHERE id = $1`, tuningRuleID); err != nil {
				return FalsePositiveFeedback{}, err
			}
		}
	} else if outcome == "true_positive" && tuningRuleID != "" {
		if _, err := tx.Exec(ctx, `UPDATE compliance_tuning_rules SET true_positive_count = true_positive_count + 1 WHERE id = $1`, tuningRuleID); err != nil {
			return FalsePositiveFeedback{}, err
		}
	}

	if err := insertCaseHistory(ctx, tx, caseID, adminUserID, "", "", "false positive feedback recorded", map[string]any{
		"outcome":        outcome,
		"tuning_rule_id": tuningRuleID,
	}); err != nil {
		return FalsePositiveFeedback{}, err
	}

	if err := tx.Commit(ctx); err != nil {
		return FalsePositiveFeedback{}, err
	}
	return feedback, nil
}

func scanScreening(row interface{ Scan(dest ...any) error }) (domain.AMLScreening, error) {
	var screening domain.AMLScreening
	err := row.Scan(
		&screening.ID,
		&screening.UserID,
		&screening.KYCProfileID,
		&screening.ScreeningType,
		&screening.Provider,
		&screening.Status,
		&screening.RiskScore,
		&screening.Matched,
		&screening.MatchDetails,
		&screening.ExternalScreeningID,
		&screening.CreatedAt,
	)
	return screening, err
}

func scanCase(row interface{ Scan(dest ...any) error }) (domain.AMLCase, error) {
	var amlCase domain.AMLCase
	var slaDueAt, escalatedAt, closedAt sql.NullTime
	err := row.Scan(
		&amlCase.ID,
		&amlCase.UserID,
		&amlCase.AMLScreeningID,
		&amlCase.CaseType,
		&amlCase.Status,
		&amlCase.Severity,
		&amlCase.Reason,
		&amlCase.ResolutionNote,
		&amlCase.AssignedToAdminUserID,
		&slaDueAt,
		&escalatedAt,
		&closedAt,
		&amlCase.RegulatoryReportRequired,
		&amlCase.FalsePositive,
		&amlCase.CreatedAt,
		&amlCase.UpdatedAt,
	)
	if slaDueAt.Valid {
		amlCase.SLADueAt = &slaDueAt.Time
	}
	if escalatedAt.Valid {
		amlCase.EscalatedAt = &escalatedAt.Time
	}
	if closedAt.Valid {
		amlCase.ClosedAt = &closedAt.Time
	}
	return amlCase, err
}

type txer interface {
	Exec(ctx context.Context, sql string, arguments ...any) (pgconn.CommandTag, error)
}

func insertCaseHistory(ctx context.Context, q txer, caseID, actorID, fromStatus, toStatus, note string, metadata map[string]any) error {
	if toStatus == "" {
		toStatus = fromStatus
	}
	raw, err := json.Marshal(metadata)
	if err != nil {
		return err
	}
	_, err = q.Exec(ctx, `
		INSERT INTO aml_case_status_history (
			aml_case_id, actor_admin_user_id, from_status, to_status, note, metadata
		)
		VALUES ($1, NULLIF($2, '')::uuid, $3, $4, $5, $6::jsonb)
	`, caseID, actorID, fromStatus, toStatus, note, string(raw))
	return err
}

func scanCaseNote(row interface{ Scan(dest ...any) error }) (CaseNote, error) {
	var note CaseNote
	err := row.Scan(&note.ID, &note.AMLCaseID, &note.AuthorAdminUserID, &note.NoteType, &note.Body, &note.CreatedAt)
	return note, err
}

func scanCaseAttachment(row interface{ Scan(dest ...any) error }) (CaseAttachment, error) {
	var attachment CaseAttachment
	err := row.Scan(
		&attachment.ID,
		&attachment.AMLCaseID,
		&attachment.UploadedByAdminUserID,
		&attachment.FileName,
		&attachment.ContentType,
		&attachment.StorageURI,
		&attachment.SHA256Hash,
		&attachment.Description,
		&attachment.CreatedAt,
	)
	return attachment, err
}

func scanCaseStatusHistory(row interface{ Scan(dest ...any) error }) (CaseStatusHistory, error) {
	var history CaseStatusHistory
	err := row.Scan(
		&history.ID,
		&history.AMLCaseID,
		&history.ActorAdminUserID,
		&history.FromStatus,
		&history.ToStatus,
		&history.Note,
		&history.Metadata,
		&history.CreatedAt,
	)
	return history, err
}

func scanFalsePositiveFeedback(row interface{ Scan(dest ...any) error }) (FalsePositiveFeedback, error) {
	var feedback FalsePositiveFeedback
	err := row.Scan(
		&feedback.ID,
		&feedback.AMLCaseID,
		&feedback.AMLScreeningID,
		&feedback.TuningRuleID,
		&feedback.ReviewedByAdminUserID,
		&feedback.Outcome,
		&feedback.Reason,
		&feedback.CreatedAt,
	)
	return feedback, err
}

func stringPtrValue(value *string) any {
	if value == nil {
		return nil
	}
	return *value
}

func defaultSLADue(severity string) time.Time {
	switch severity {
	case "critical":
		return time.Now().UTC().Add(4 * time.Hour)
	case "high":
		return time.Now().UTC().Add(24 * time.Hour)
	case "medium":
		return time.Now().UTC().Add(72 * time.Hour)
	default:
		return time.Now().UTC().Add(7 * 24 * time.Hour)
	}
}

func ValidateCaseNote(noteType, body string) error {
	switch noteType {
	case "internal", "customer_contact", "provider_contact", "regulatory", "decision":
	default:
		return fmt.Errorf("%w: note_type must be internal, customer_contact, provider_contact, regulatory or decision", domain.ErrValidation)
	}
	if body == "" {
		return fmt.Errorf("%w: body is required", domain.ErrValidation)
	}
	return nil
}