package admin

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

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

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

func (r *Repository) ListRoutingCodeRequirements(ctx context.Context, provider, country, network, ruleSource, approvalStatus string, limit int) ([]routingcodes.Requirement, error) {
	limit = normalizeLimit(limit)
	provider = strings.ToLower(strings.TrimSpace(provider))
	country = strings.ToUpper(strings.TrimSpace(country))
	network = strings.ToLower(strings.TrimSpace(network))
	ruleSource = strings.ToLower(strings.TrimSpace(ruleSource))
	approvalStatus = strings.ToLower(strings.TrimSpace(approvalStatus))

	rows, err := r.db.Query(ctx, routingCodeRequirementSelect+`
		WHERE (NULLIF($1, '') IS NULL OR provider = $1)
			AND (NULLIF($2, '') IS NULL OR country = NULLIF($2, '')::char(2))
			AND (NULLIF($3, '') IS NULL OR network = $3)
			AND (NULLIF($4, '') IS NULL OR rule_source = $4)
			AND (NULLIF($5, '') IS NULL OR approval_status = $5)
		ORDER BY
			CASE approval_status WHEN 'draft' THEN 0 WHEN 'approved' THEN 1 ELSE 2 END,
			provider, country, network, currency, priority, code_type
		LIMIT $6
	`, provider, country, network, ruleSource, approvalStatus, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	requirements := []routingcodes.Requirement{}
	for rows.Next() {
		requirement, err := scanRoutingCodeRequirement(rows)
		if err != nil {
			return nil, err
		}
		requirements = append(requirements, requirement)
	}
	return requirements, rows.Err()
}

func (r *Repository) CreateRoutingCodeRequirement(ctx context.Context, adminUserID string, params routingcodes.RequirementParams) (routingcodes.Requirement, error) {
	normalized, err := routingcodes.NormalizeRequirementParams(params)
	if err != nil {
		return routingcodes.Requirement{}, err
	}

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

	if err := requireLiveAdmin(ctx, tx, adminUserID); err != nil {
		return routingcodes.Requirement{}, err
	}

	var approvedBy any
	var approvedAt any
	var retiredAt any
	if normalized.ApprovalStatus == routingcodes.ApprovalStatusApproved {
		approvedBy = adminUserID
		approvedAt = time.Now().UTC()
	}
	if normalized.ApprovalStatus == routingcodes.ApprovalStatusRetired {
		retiredAt = time.Now().UTC()
	}

	var requirementID string
	err = tx.QueryRow(ctx, `
		INSERT INTO routing_code_requirements (
			provider, country, network, currency, code_type, required, active, priority,
			description, rule_source, contract_reference, contract_version, contract_signed_at,
			effective_from, effective_to, approval_status, approved_by_admin_user_id,
			approved_at, retired_at, metadata
		)
		VALUES (
			$1, $2, $3, $4, $5, $6, $7, $8,
			$9, $10, NULLIF($11, ''), NULLIF($12, ''), NULLIF($13, '')::date,
			NULLIF($14, '')::date, NULLIF($15, '')::date, $16, $17,
			$18, $19, $20::jsonb
		)
		RETURNING id::text
	`, normalized.Provider, normalized.Country, normalized.Network, normalized.Currency, normalized.CodeType,
		normalized.Required, normalized.Active, normalized.Priority, normalized.Description, normalized.RuleSource,
		normalized.ContractReference, normalized.ContractVersion, normalized.ContractSignedAt,
		normalized.EffectiveFrom, normalized.EffectiveTo, normalized.ApprovalStatus,
		approvedBy, approvedAt, retiredAt, normalized.Metadata).Scan(&requirementID)
	if err != nil {
		if isRoutingRequirementUniqueViolation(err) {
			return routingcodes.Requirement{}, fmt.Errorf("%w: routing-code requirement contract rule already exists", domain.ErrConflict)
		}
		return routingcodes.Requirement{}, err
	}

	requirement, err := findRoutingCodeRequirement(ctx, tx, requirementID)
	if err != nil {
		return routingcodes.Requirement{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return routingcodes.Requirement{}, err
	}
	return requirement, nil
}

func (r *Repository) UpdateRoutingCodeRequirement(ctx context.Context, requirementID, adminUserID string, params routingcodes.RequirementParams) (routingcodes.Requirement, error) {
	if err := domain.ValidateUUID("id", strings.TrimSpace(requirementID)); err != nil {
		return routingcodes.Requirement{}, err
	}
	normalized, err := routingcodes.NormalizeRequirementParams(params)
	if err != nil {
		if isRoutingRequirementUniqueViolation(err) {
			return routingcodes.Requirement{}, fmt.Errorf("%w: routing-code requirement contract rule already exists", domain.ErrConflict)
		}
		return routingcodes.Requirement{}, err
	}

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

	if err := requireLiveAdmin(ctx, tx, adminUserID); err != nil {
		return routingcodes.Requirement{}, err
	}

	var approvedBy any
	var approvedAt any
	var retiredAt any
	if normalized.ApprovalStatus == routingcodes.ApprovalStatusApproved {
		approvedBy = adminUserID
		approvedAt = time.Now().UTC()
	}
	if normalized.ApprovalStatus == routingcodes.ApprovalStatusRetired {
		retiredAt = time.Now().UTC()
	}

	command, err := tx.Exec(ctx, `
		UPDATE routing_code_requirements
		SET provider = $2,
			country = $3,
			network = $4,
			currency = $5,
			code_type = $6,
			required = $7,
			active = $8,
			priority = $9,
			description = $10,
			rule_source = $11,
			contract_reference = NULLIF($12, ''),
			contract_version = NULLIF($13, ''),
			contract_signed_at = NULLIF($14, '')::date,
			effective_from = NULLIF($15, '')::date,
			effective_to = NULLIF($16, '')::date,
			approval_status = $17,
			approved_by_admin_user_id = CASE WHEN $17 = 'approved' THEN $18 ELSE approved_by_admin_user_id END,
			approved_at = CASE WHEN $17 = 'approved' THEN $19 ELSE approved_at END,
			retired_at = CASE WHEN $17 = 'retired' THEN $20 ELSE NULL END,
			metadata = $21::jsonb
		WHERE id = $1
	`, requirementID, normalized.Provider, normalized.Country, normalized.Network, normalized.Currency,
		normalized.CodeType, normalized.Required, normalized.Active, normalized.Priority, normalized.Description,
		normalized.RuleSource, normalized.ContractReference, normalized.ContractVersion, normalized.ContractSignedAt,
		normalized.EffectiveFrom, normalized.EffectiveTo, normalized.ApprovalStatus, approvedBy, approvedAt,
		retiredAt, normalized.Metadata)
	if err != nil {
		return routingcodes.Requirement{}, err
	}
	if command.RowsAffected() == 0 {
		return routingcodes.Requirement{}, domain.ErrNotFound
	}

	requirement, err := findRoutingCodeRequirement(ctx, tx, requirementID)
	if err != nil {
		return routingcodes.Requirement{}, err
	}
	if err := tx.Commit(ctx); err != nil {
		return routingcodes.Requirement{}, err
	}
	return requirement, nil
}

const routingCodeRequirementSelect = `
	SELECT id::text, provider, country::text, network, currency, code_type, required, active, priority,
		description, rule_source, COALESCE(contract_reference, ''), COALESCE(contract_version, ''),
		COALESCE(contract_signed_at::text, ''), COALESCE(effective_from::text, ''),
		COALESCE(effective_to::text, ''), approval_status, COALESCE(approved_by_admin_user_id::text, ''),
		approved_at, retired_at, metadata::text, created_at, updated_at
	FROM routing_code_requirements
`

func findRoutingCodeRequirement(ctx context.Context, tx pgx.Tx, requirementID string) (routingcodes.Requirement, error) {
	requirement, err := scanRoutingCodeRequirement(tx.QueryRow(ctx, routingCodeRequirementSelect+` WHERE id = $1`, requirementID))
	if errors.Is(err, pgx.ErrNoRows) {
		return routingcodes.Requirement{}, domain.ErrNotFound
	}
	return requirement, err
}

func scanRoutingCodeRequirement(row scanner) (routingcodes.Requirement, error) {
	var requirement routingcodes.Requirement
	var approvedAt sql.NullTime
	var retiredAt sql.NullTime
	var metadata string
	err := row.Scan(
		&requirement.ID,
		&requirement.Provider,
		&requirement.Country,
		&requirement.Network,
		&requirement.Currency,
		&requirement.CodeType,
		&requirement.Required,
		&requirement.Active,
		&requirement.Priority,
		&requirement.Description,
		&requirement.RuleSource,
		&requirement.ContractReference,
		&requirement.ContractVersion,
		&requirement.ContractSignedAt,
		&requirement.EffectiveFrom,
		&requirement.EffectiveTo,
		&requirement.ApprovalStatus,
		&requirement.ApprovedByAdminUserID,
		&approvedAt,
		&retiredAt,
		&metadata,
		&requirement.CreatedAt,
		&requirement.UpdatedAt,
	)
	if err != nil {
		return requirement, err
	}
	if approvedAt.Valid {
		requirement.ApprovedAt = &approvedAt.Time
	}
	if retiredAt.Valid {
		requirement.RetiredAt = &retiredAt.Time
	}
	if strings.TrimSpace(metadata) == "" {
		metadata = "{}"
	}
	requirement.Metadata = json.RawMessage(metadata)
	return requirement, nil
}

func metadataBytes(value map[string]any) ([]byte, error) {
	if value == nil {
		return []byte("{}"), nil
	}
	raw, err := json.Marshal(value)
	if err != nil {
		return nil, fmt.Errorf("%w: metadata must be JSON serializable", domain.ErrValidation)
	}
	return raw, nil
}

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