package cards

import (
	"context"
	"database/sql"
	"errors"

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

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

type Repository struct {
	db     *pgxpool.Pool
	risk   *risk.Repository
	ledger *ledger.Repository
}

type CreateParams struct {
	UserID             string
	AccountID          string
	Nickname           string
	CardholderName     string
	Network            string
	CardType           string
	ExternalCardID     string
	CardToken          string
	PANFingerprint     string
	Last4              string
	ExpMonth           int
	ExpYear            int
	SpendingLimitCents int64
}

func NewRepository(db *pgxpool.Pool, ledgers ...*ledger.Repository) *Repository {
	ledgerRepo := ledger.NewRepository(db)
	if len(ledgers) > 0 && ledgers[0] != nil {
		ledgerRepo = ledgers[0]
	}
	return &Repository{db: db, ledger: ledgerRepo}
}

func (r *Repository) WithRisk(riskRepo *risk.Repository) *Repository {
	r.risk = riskRepo
	return r
}

func (r *Repository) Create(ctx context.Context, params CreateParams) (domain.VirtualCard, error) {
	cardToken := params.CardToken
	if cardToken == "" {
		cardToken = params.ExternalCardID
	}
	row := r.db.QueryRow(ctx, `
		INSERT INTO virtual_cards (
			user_id, account_id, external_card_id, nickname, cardholder_name, network, card_type,
			pan_fingerprint, last4, exp_month, exp_year, spending_limit_cents, limit_sync_status, limit_synced_at,
			provider_config_id, tokenization_policy_id, card_token, tokenization_status, token_key_reference, pci_scope_classification
		)
		SELECT
			$1, a.id, $3, NULLIF($4, ''), $5, $6, $7, $8, $9, $10, $11, $12, 'synced', now(),
			(SELECT id FROM card_processor_configs WHERE provider = 'local_card_issuer'),
			(SELECT id FROM card_tokenization_policies WHERE policy_name = 'local-issuer-token-policy'),
			$13, 'provider_tokenized', 'BANKING_CARD_SECRET', 'metadata_only'
		FROM accounts a
		WHERE a.id = $2 AND a.user_id = $1 AND a.status = 'active'
		RETURNING id::text, user_id::text, account_id::text, external_card_id, COALESCE(nickname, ''), cardholder_name,
			network, card_type, last4, exp_month::int, exp_year::int, spending_limit_cents,
			limit_sync_status, limit_synced_at, COALESCE(limit_sync_error, ''),
			card_token, tokenization_status, token_key_reference, pci_scope_classification,
			status, created_at, updated_at
	`, params.UserID, params.AccountID, params.ExternalCardID, params.Nickname, params.CardholderName, params.Network, params.CardType,
		params.PANFingerprint, params.Last4, params.ExpMonth, params.ExpYear, params.SpendingLimitCents, cardToken)

	card, err := scanVirtualCard(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.VirtualCard{}, domain.ErrNotFound
	}
	if err != nil {
		if isUniqueViolation(err) {
			return domain.VirtualCard{}, domain.ErrConflict
		}
		return domain.VirtualCard{}, err
	}

	return card, nil
}

func (r *Repository) ListByUser(ctx context.Context, userID string) ([]domain.VirtualCard, error) {
	rows, err := r.db.Query(ctx, `
		SELECT id::text, user_id::text, account_id::text, external_card_id, COALESCE(nickname, ''), cardholder_name,
			network, card_type, last4, exp_month::int, exp_year::int, spending_limit_cents,
			limit_sync_status, limit_synced_at, COALESCE(limit_sync_error, ''),
			card_token, tokenization_status, token_key_reference, pci_scope_classification,
			status, created_at, updated_at
		FROM virtual_cards
		WHERE user_id = $1
		ORDER BY created_at DESC
	`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	cards := []domain.VirtualCard{}
	for rows.Next() {
		card, err := scanVirtualCard(rows)
		if err != nil {
			return nil, err
		}
		cards = append(cards, card)
	}

	return cards, rows.Err()
}

func (r *Repository) FindOwned(ctx context.Context, userID, cardID string) (domain.VirtualCard, error) {
	row := r.db.QueryRow(ctx, `
		SELECT id::text, user_id::text, account_id::text, external_card_id, COALESCE(nickname, ''), cardholder_name,
			network, card_type, last4, exp_month::int, exp_year::int, spending_limit_cents,
			limit_sync_status, limit_synced_at, COALESCE(limit_sync_error, ''),
			card_token, tokenization_status, token_key_reference, pci_scope_classification,
			status, created_at, updated_at
		FROM virtual_cards
		WHERE id = $1 AND user_id = $2
	`, cardID, userID)

	card, err := scanVirtualCard(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.VirtualCard{}, domain.ErrNotFound
	}
	return card, err
}

func (r *Repository) UpdateStatus(ctx context.Context, userID, cardID, status string) (domain.VirtualCard, error) {
	row := r.db.QueryRow(ctx, `
		UPDATE virtual_cards
		SET status = $3
		WHERE id = $1 AND user_id = $2
		RETURNING id::text, user_id::text, account_id::text, external_card_id, COALESCE(nickname, ''), cardholder_name,
			network, card_type, last4, exp_month::int, exp_year::int, spending_limit_cents,
			limit_sync_status, limit_synced_at, COALESCE(limit_sync_error, ''),
			card_token, tokenization_status, token_key_reference, pci_scope_classification,
			status, created_at, updated_at
	`, cardID, userID, status)

	card, err := scanVirtualCard(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.VirtualCard{}, domain.ErrNotFound
	}
	return card, err
}

func (r *Repository) UpdateSpendingLimit(ctx context.Context, userID, cardID string, spendingLimitCents int64, syncStatus, syncError string) (domain.VirtualCard, error) {
	row := r.db.QueryRow(ctx, `
		UPDATE virtual_cards
		SET spending_limit_cents = $3,
			limit_sync_status = $4,
			limit_synced_at = CASE WHEN $4::text = 'synced' THEN now() ELSE limit_synced_at END,
			limit_sync_error = NULLIF($5, '')
		WHERE id = $1 AND user_id = $2
		RETURNING id::text, user_id::text, account_id::text, external_card_id, COALESCE(nickname, ''), cardholder_name,
			network, card_type, last4, exp_month::int, exp_year::int, spending_limit_cents,
			limit_sync_status, limit_synced_at, COALESCE(limit_sync_error, ''),
			card_token, tokenization_status, token_key_reference, pci_scope_classification,
			status, created_at, updated_at
	`, cardID, userID, spendingLimitCents, syncStatus, syncError)

	card, err := scanVirtualCard(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.VirtualCard{}, domain.ErrNotFound
	}
	return card, err
}

func (r *Repository) UpdateSpendingLimitByExternalID(ctx context.Context, externalCardID string, spendingLimitCents int64) (domain.VirtualCard, error) {
	row := r.db.QueryRow(ctx, `
		UPDATE virtual_cards
		SET spending_limit_cents = $2,
			limit_sync_status = 'synced',
			limit_synced_at = now(),
			limit_sync_error = NULL
		WHERE external_card_id = $1
		RETURNING id::text, user_id::text, account_id::text, external_card_id, COALESCE(nickname, ''), cardholder_name,
			network, card_type, last4, exp_month::int, exp_year::int, spending_limit_cents,
			limit_sync_status, limit_synced_at, COALESCE(limit_sync_error, ''),
			card_token, tokenization_status, token_key_reference, pci_scope_classification,
			status, created_at, updated_at
	`, externalCardID, spendingLimitCents)

	card, err := scanVirtualCard(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.VirtualCard{}, domain.ErrNotFound
	}
	return card, err
}

func (r *Repository) UpdateStatusByExternalID(ctx context.Context, externalCardID, status string) (domain.VirtualCard, error) {
	row := r.db.QueryRow(ctx, `
		UPDATE virtual_cards
		SET status = $2
		WHERE external_card_id = $1
		RETURNING id::text, user_id::text, account_id::text, external_card_id, COALESCE(nickname, ''), cardholder_name,
			network, card_type, last4, exp_month::int, exp_year::int, spending_limit_cents,
			limit_sync_status, limit_synced_at, COALESCE(limit_sync_error, ''),
			card_token, tokenization_status, token_key_reference, pci_scope_classification,
			status, created_at, updated_at
	`, externalCardID, status)

	card, err := scanVirtualCard(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return domain.VirtualCard{}, domain.ErrNotFound
	}
	return card, err
}

type AuthorizationParams struct {
	ExternalAuthorizationID string
	ExternalCardID          string
	AmountCents             int64
	Currency                string
	MerchantName            string
	MerchantCategoryCode    string
	MerchantCountry         string
	RawEvent                []byte
}

type AuthorizationDecision struct {
	Authorization domain.CardAuthorization `json:"authorization,omitempty"`
	Decision      string                   `json:"decision"`
	Reason        string                   `json:"reason,omitempty"`
}

func (r *Repository) Authorize(ctx context.Context, params AuthorizationParams) (AuthorizationDecision, error) {
	if params.AmountCents <= 0 {
		return AuthorizationDecision{Decision: "declined", Reason: "invalid_amount"}, nil
	}

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

	existing, err := findAuthorization(ctx, tx, params.ExternalAuthorizationID)
	if err == nil {
		return decisionFromAuthorization(existing), tx.Commit(ctx)
	}
	if !errors.Is(err, pgx.ErrNoRows) {
		return AuthorizationDecision{}, err
	}

	card, account, err := lockCardForAuthorization(ctx, tx, params.ExternalCardID)
	if errors.Is(err, pgx.ErrNoRows) {
		return AuthorizationDecision{Decision: "declined", Reason: "unknown_card"}, tx.Commit(ctx)
	}
	if err != nil {
		return AuthorizationDecision{}, err
	}

	status := "approved"
	reason := ""
	if card.KYCStatus != "verified" {
		status = "declined"
		reason = "kyc_required"
	} else if card.HasOpenAMLCase {
		status = "declined"
		reason = "aml_review_required"
	} else if card.Status != "active" {
		status = "declined"
		reason = "card_inactive"
	} else if account.Status != "active" {
		status = "declined"
		reason = "account_inactive"
	} else if account.Currency != params.Currency {
		status = "declined"
		reason = "currency_mismatch"
	} else if account.BalanceCents < params.AmountCents {
		status = "declined"
		reason = "insufficient_funds"
	}

	if status == "approved" && card.SpendingLimitCents > 0 {
		approvedAmount, err := approvedAuthorizationAmount(ctx, tx, card.ID)
		if err != nil {
			return AuthorizationDecision{}, err
		}
		if approvedAmount+params.AmountCents > card.SpendingLimitCents {
			status = "declined"
			reason = "spending_limit_exceeded"
		}
	}
	if status == "approved" && r.risk != nil {
		evaluation, err := r.risk.EvaluateAndRecord(ctx, tx, risk.CheckRequest{
			UserID:      card.UserID,
			AccountID:   account.ID,
			Operation:   risk.OperationCardAuthorization,
			SourceType:  "card_authorization",
			SourceID:    params.ExternalAuthorizationID,
			Currency:    params.Currency,
			AmountCents: params.AmountCents,
			Metadata: map[string]any{
				"virtual_card_id":        card.ID,
				"external_card_id":       params.ExternalCardID,
				"merchant_name":          params.MerchantName,
				"merchant_category_code": params.MerchantCategoryCode,
				"merchant_country":       params.MerchantCountry,
			},
		})
		if err != nil {
			return AuthorizationDecision{}, err
		}
		if risk.IsBlocked(evaluation) {
			status = "declined"
			reason = evaluation.Reason
		}
	}

	auth, err := insertAuthorization(ctx, tx, card.ID, params, status, reason)
	if err != nil {
		return AuthorizationDecision{}, err
	}
	if status == "approved" {
		auth, err = r.postAuthorizationHold(ctx, tx, auth, card, account)
		if err != nil {
			return AuthorizationDecision{}, err
		}
	} else {
		auth, err = updateAuthorizationLedgerStatus(ctx, tx, auth.ID, "not_required")
		if err != nil {
			return AuthorizationDecision{}, err
		}
	}
	if err := insertCardNotification(ctx, tx, card.UserID, card.ID, auth.ID, "", notificationTypeForAuthorization(auth), cardNotificationTitle(auth), cardNotificationBody(auth)); err != nil {
		return AuthorizationDecision{}, err
	}

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

	return decisionFromAuthorization(auth), nil
}

func (r *Repository) ReverseAuthorization(ctx context.Context, externalAuthorizationID string, rawEvent []byte) (domain.CardAuthorization, error) {
	return r.reverseAuthorization(ctx, externalAuthorizationID, rawEvent)
}

func (r *Repository) RecordWebhookEvent(ctx context.Context, externalEventID, eventType string, payload []byte) (bool, error) {
	var id string
	safePayload := string(redactRawEvent(payload))
	err := r.db.QueryRow(ctx, `
		INSERT INTO card_issuer_webhook_events (external_event_id, event_type, payload)
		VALUES ($1, $2, $3)
		ON CONFLICT (external_event_id) DO NOTHING
		RETURNING id::text
	`, externalEventID, eventType, safePayload).Scan(&id)
	if errors.Is(err, pgx.ErrNoRows) {
		return false, nil
	}
	return err == nil, err
}

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

	rows, err := r.db.Query(ctx, `
		SELECT ca.id::text, ca.virtual_card_id::text, ca.external_authorization_id, ca.amount_cents, ca.currency,
			COALESCE(ca.merchant_name, ''), COALESCE(ca.merchant_category_code, ''), COALESCE(ca.merchant_country, ''),
			ca.status, COALESCE(ca.decline_reason, ''), ca.ledger_status,
			ca.expires_at, ca.cleared_at, ca.reversed_at, ca.disputed_at,
			ca.raw_event, ca.created_at, ca.updated_at
		FROM card_authorizations ca
		JOIN virtual_cards vc ON vc.id = ca.virtual_card_id
		WHERE vc.id = $1 AND vc.user_id = $2
		ORDER BY ca.created_at DESC
		LIMIT $3
	`, cardID, userID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	authorizations := []domain.CardAuthorization{}
	for rows.Next() {
		auth, err := scanAuthorization(rows)
		if err != nil {
			return nil, err
		}
		authorizations = append(authorizations, auth)
	}

	return authorizations, rows.Err()
}

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

func scanVirtualCard(row scanner) (domain.VirtualCard, error) {
	var card domain.VirtualCard
	var syncedAt sql.NullTime
	err := row.Scan(
		&card.ID,
		&card.UserID,
		&card.AccountID,
		&card.ExternalCardID,
		&card.Nickname,
		&card.CardholderName,
		&card.Network,
		&card.CardType,
		&card.Last4,
		&card.ExpMonth,
		&card.ExpYear,
		&card.SpendingLimitCents,
		&card.LimitSyncStatus,
		&syncedAt,
		&card.LimitSyncError,
		&card.CardToken,
		&card.TokenizationStatus,
		&card.TokenKeyReference,
		&card.PCIScopeClass,
		&card.Status,
		&card.CreatedAt,
		&card.UpdatedAt,
	)
	if syncedAt.Valid {
		card.LimitSyncedAt = &syncedAt.Time
	}
	return card, err
}

type authCard struct {
	ID                 string
	UserID             string
	Status             string
	SpendingLimitCents int64
	KYCStatus          string
	HasOpenAMLCase     bool
}

type authAccount struct {
	ID           string
	BalanceCents int64
	Currency     string
	Status       string
}

func lockCardForAuthorization(ctx context.Context, tx pgx.Tx, externalCardID string) (authCard, authAccount, error) {
	row := tx.QueryRow(ctx, `
		SELECT vc.id::text, vc.user_id::text, vc.status, vc.spending_limit_cents,
			COALESCE(kp.status, ''), EXISTS (
				SELECT 1 FROM aml_cases ac
				WHERE ac.user_id = vc.user_id AND ac.status IN ('open', 'reviewing', 'escalated')
			),
			a.id::text, a.balance_cents, a.currency, a.status
		FROM virtual_cards vc
		JOIN accounts a ON a.id = vc.account_id
		LEFT JOIN kyc_profiles kp ON kp.user_id = vc.user_id
		WHERE vc.external_card_id = $1
		FOR UPDATE OF vc, a
	`, externalCardID)

	var card authCard
	var account authAccount
	err := row.Scan(
		&card.ID,
		&card.UserID,
		&card.Status,
		&card.SpendingLimitCents,
		&card.KYCStatus,
		&card.HasOpenAMLCase,
		&account.ID,
		&account.BalanceCents,
		&account.Currency,
		&account.Status,
	)
	return card, account, err
}

func approvedAuthorizationAmount(ctx context.Context, tx pgx.Tx, cardID string) (int64, error) {
	var amount int64
	err := tx.QueryRow(ctx, `
		SELECT COALESCE(SUM(amount_cents), 0)
		FROM card_authorizations
		WHERE virtual_card_id = $1 AND status IN ('approved', 'cleared')
	`, cardID).Scan(&amount)
	return amount, err
}

func (r *Repository) ClearAuthorization(ctx context.Context, externalAuthorizationID string, amountCents int64, rawEvent []byte) (domain.CardAuthorization, error) {
	return r.clearAuthorization(ctx, externalAuthorizationID, amountCents, rawEvent)
}

func insertAuthorization(ctx context.Context, tx pgx.Tx, cardID string, params AuthorizationParams, status, declineReason string) (domain.CardAuthorization, error) {
	rawJSON := string(redactRawEvent(params.RawEvent))
	row := tx.QueryRow(ctx, `
		INSERT INTO card_authorizations (
			virtual_card_id, external_authorization_id, amount_cents, currency,
			merchant_name, merchant_category_code, merchant_country, status, decline_reason, raw_event,
			expires_at, ledger_status
		)
		VALUES (
			$1, $2, $3, $4, NULLIF($5, ''), NULLIF($6, ''), NULLIF($7, ''), $8, NULLIF($9, ''), $10,
			CASE WHEN $8 = 'approved' THEN now() + interval '7 days' ELSE NULL END,
			CASE WHEN $8 = 'declined' THEN 'not_required' ELSE 'pending' END
		)
		RETURNING id::text, virtual_card_id::text, external_authorization_id, amount_cents, currency,
			COALESCE(merchant_name, ''), COALESCE(merchant_category_code, ''), COALESCE(merchant_country, ''),
			status, COALESCE(decline_reason, ''), ledger_status,
			expires_at, cleared_at, reversed_at, disputed_at,
			raw_event, created_at, updated_at
	`, cardID, params.ExternalAuthorizationID, params.AmountCents, params.Currency, params.MerchantName,
		params.MerchantCategoryCode, params.MerchantCountry, status, declineReason, rawJSON)
	return scanAuthorization(row)
}

func findAuthorization(ctx context.Context, tx pgx.Tx, externalAuthorizationID string) (domain.CardAuthorization, error) {
	row := tx.QueryRow(ctx, `
		SELECT id::text, virtual_card_id::text, external_authorization_id, amount_cents, currency,
			COALESCE(merchant_name, ''), COALESCE(merchant_category_code, ''), COALESCE(merchant_country, ''),
			status, COALESCE(decline_reason, ''), ledger_status,
			expires_at, cleared_at, reversed_at, disputed_at,
			raw_event, created_at, updated_at
		FROM card_authorizations
		WHERE external_authorization_id = $1
	`, externalAuthorizationID)
	return scanAuthorization(row)
}

func scanAuthorization(row scanner) (domain.CardAuthorization, error) {
	var auth domain.CardAuthorization
	var expiresAt sql.NullTime
	var clearedAt sql.NullTime
	var reversedAt sql.NullTime
	var disputedAt sql.NullTime
	err := row.Scan(
		&auth.ID,
		&auth.VirtualCardID,
		&auth.ExternalAuthorizationID,
		&auth.AmountCents,
		&auth.Currency,
		&auth.MerchantName,
		&auth.MerchantCategoryCode,
		&auth.MerchantCountry,
		&auth.Status,
		&auth.DeclineReason,
		&auth.LedgerStatus,
		&expiresAt,
		&clearedAt,
		&reversedAt,
		&disputedAt,
		&auth.RawEvent,
		&auth.CreatedAt,
		&auth.UpdatedAt,
	)
	if expiresAt.Valid {
		auth.ExpiresAt = &expiresAt.Time
	}
	if clearedAt.Valid {
		auth.ClearedAt = &clearedAt.Time
	}
	if reversedAt.Valid {
		auth.ReversedAt = &reversedAt.Time
	}
	if disputedAt.Valid {
		auth.DisputedAt = &disputedAt.Time
	}
	return auth, err
}

func decisionFromAuthorization(auth domain.CardAuthorization) AuthorizationDecision {
	decision := "approved"
	if auth.Status != "approved" && auth.Status != "cleared" {
		decision = "declined"
	}
	return AuthorizationDecision{
		Authorization: auth,
		Decision:      decision,
		Reason:        auth.DeclineReason,
	}
}

func rawOrEmpty(raw []byte) []byte {
	if len(raw) == 0 {
		return []byte(`{}`)
	}
	return raw
}

func redactRawEvent(raw []byte) []byte {
	return carddata.RedactJSON(rawOrEmpty(raw))
}

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