package seatbilling

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/model"
	"github.com/tidbcloud/AgentTeam/internal/store/storeerr"
)

func canApplyPaidSeatOrder(ctx context.Context, queryer coredb.QueryRower, order SeatOrder, paidAt time.Time) (bool, error) {
	switch order.Status {
	case OrderStatusPending:
		if paidAt.After(order.ExpiresAt) {
			hasNewer, err := hasNewerSeatOrder(ctx, queryer, order)
			if err != nil {
				return false, err
			}
			return !hasNewer, nil
		}
		return true, nil
	case OrderStatusExpired:
		hasNewer, err := hasNewerSeatOrder(ctx, queryer, order)
		if err != nil {
			return false, err
		}
		return !hasNewer, nil
	case OrderStatusPaidUnapplied:
		hasNewer, err := hasNewerSeatOrder(ctx, queryer, order)
		if err != nil {
			return false, err
		}
		return !hasNewer, nil
	default:
		return false, nil
	}
}

func hasNewerSeatOrder(ctx context.Context, queryer coredb.QueryRower, order SeatOrder) (bool, error) {
	var count int64
	err := queryer.QueryRow(ctx, `
SELECT COUNT(*)
FROM organization_seat_orders
WHERE organization_id = $1
  AND id <> $2
  AND (created_at > $3 OR (created_at = $3 AND id > $2))
`, order.OrganizationID, order.ID, order.CreatedAt).Scan(&count)
	if err != nil {
		return false, fmt.Errorf("check newer seat order: %w", err)
	}
	return count > 0, nil
}

func applyPaidSeatOrder(ctx context.Context, tx coredb.Tx, order SeatOrder, paidAt time.Time, now time.Time) error {
	switch order.OrderType {
	case OrderTypeNewSubscription:
		return applyNewSubscriptionOrder(ctx, tx, order, paidAt, now)
	case OrderTypeAddSeats:
		return applyAddSeatsOrder(ctx, tx, order, paidAt, now)
	case OrderTypeUpgradeTier:
		return applyUpgradeTierOrder(ctx, tx, order, paidAt, now)
	case OrderTypeRenewSubscription:
		return applyRenewSubscriptionOrder(ctx, tx, order, paidAt, now)
	default:
		return storeerr.BadRequest("unsupported seat order type")
	}
}

func applyNewSubscriptionOrder(ctx context.Context, tx coredb.Tx, order SeatOrder, paidAt time.Time, now time.Time) error {
	if subscription, found, err := loadSubscription(ctx, tx, order.OrganizationID, true); err != nil {
		return err
	} else if found && subscriptionIsEffective(subscription, paidAt) {
		return errSeatOrderUnsafe
	}
	snapshot, err := parseOrderEntitlementSnapshot(order)
	if err != nil {
		return err
	}
	if order.ToPlanKey == nil || *order.ToPlanKey != snapshot.PlanKey || order.BillingPeriod != snapshot.BillingPeriod {
		return errSeatOrderUnsafe
	}
	periodEnd, err := addBillingPeriod(paidAt, order.BillingPeriod)
	if err != nil {
		return err
	}
	return upsertSeatSubscription(ctx, tx, SeatSubscription{
		OrganizationID:               order.OrganizationID,
		Status:                       SubscriptionStatusActive,
		PlanKey:                      snapshot.PlanKey,
		BillingPeriod:                order.BillingPeriod,
		SeatQuantity:                 order.SeatQuantityAfter,
		ManagedAgentsPerSeatSnapshot: snapshot.ManagedAgentsPerSeat,
		MaxHumanSeatsSnapshot:        snapshot.MaxHumanSeats,
		UnitPriceCentsSnapshot:       order.TargetUnitPriceCentsSnapshot,
		Currency:                     order.Currency,
		CurrentPeriodStart:           paidAt,
		CurrentPeriodEnd:             periodEnd,
		CreatedAt:                    now,
		UpdatedAt:                    now,
	})
}

func applyAddSeatsOrder(ctx context.Context, tx coredb.Tx, order SeatOrder, paidAt time.Time, now time.Time) error {
	subscription, found, err := loadEffectiveSubscription(ctx, tx, order.OrganizationID, paidAt, true)
	if err != nil {
		return err
	}
	if !found || order.FromPlanKey == nil || subscription.PlanKey != *order.FromPlanKey || subscription.BillingPeriod != order.BillingPeriod || subscription.SeatQuantity != order.SeatQuantityBefore {
		return errSeatOrderUnsafe
	}
	if order.SeatQuantityAfter <= subscription.SeatQuantity || seatQuantityExceedsMax(order.SeatQuantityAfter, subscription.MaxHumanSeatsSnapshot) {
		return errSeatOrderUnsafe
	}
	subscription.SeatQuantity = order.SeatQuantityAfter
	subscription.UpdatedAt = now
	return updateSeatSubscription(ctx, tx, subscription)
}

func applyUpgradeTierOrder(ctx context.Context, tx coredb.Tx, order SeatOrder, paidAt time.Time, now time.Time) error {
	subscription, found, err := loadEffectiveSubscription(ctx, tx, order.OrganizationID, paidAt, true)
	if err != nil {
		return err
	}
	if !found || order.FromPlanKey == nil || order.ToPlanKey == nil || subscription.PlanKey != *order.FromPlanKey || subscription.BillingPeriod != order.BillingPeriod || subscription.SeatQuantity != order.SeatQuantityBefore {
		return errSeatOrderUnsafe
	}
	snapshot, err := parseOrderEntitlementSnapshot(order)
	if err != nil {
		return err
	}
	if *order.ToPlanKey != snapshot.PlanKey || seatQuantityExceedsMax(order.SeatQuantityAfter, snapshot.MaxHumanSeats) {
		return errSeatOrderUnsafe
	}
	subscription.Status = SubscriptionStatusActive
	subscription.PlanKey = snapshot.PlanKey
	subscription.SeatQuantity = order.SeatQuantityAfter
	subscription.ManagedAgentsPerSeatSnapshot = snapshot.ManagedAgentsPerSeat
	subscription.MaxHumanSeatsSnapshot = snapshot.MaxHumanSeats
	subscription.UnitPriceCentsSnapshot = order.TargetUnitPriceCentsSnapshot
	subscription.Currency = order.Currency
	subscription.UpdatedAt = now
	return updateSeatSubscription(ctx, tx, subscription)
}

func applyRenewSubscriptionOrder(ctx context.Context, tx coredb.Tx, order SeatOrder, paidAt time.Time, now time.Time) error {
	subscription, found, err := loadSubscription(ctx, tx, order.OrganizationID, true)
	if err != nil {
		return err
	}
	if !found || order.FromPlanKey == nil || subscription.PlanKey != *order.FromPlanKey || subscription.BillingPeriod != order.BillingPeriod || subscription.SeatQuantity != order.SeatQuantityBefore {
		return errSeatOrderUnsafe
	}
	if subscription.Status != SubscriptionStatusActive && subscription.Status != SubscriptionStatusExpired {
		return errSeatOrderUnsafe
	}
	snapshot, err := parseOrderEntitlementSnapshot(order)
	if err != nil {
		return err
	}
	start := paidAt
	if subscriptionIsEffective(subscription, paidAt) {
		start = subscription.CurrentPeriodStart
		paidAt = subscription.CurrentPeriodEnd
	}
	periodEnd, err := addBillingPeriod(paidAt, order.BillingPeriod)
	if err != nil {
		return err
	}
	subscription.Status = SubscriptionStatusActive
	subscription.PlanKey = snapshot.PlanKey
	subscription.BillingPeriod = order.BillingPeriod
	subscription.SeatQuantity = order.SeatQuantityAfter
	subscription.ManagedAgentsPerSeatSnapshot = snapshot.ManagedAgentsPerSeat
	subscription.MaxHumanSeatsSnapshot = snapshot.MaxHumanSeats
	subscription.UnitPriceCentsSnapshot = order.TargetUnitPriceCentsSnapshot
	subscription.Currency = order.Currency
	subscription.CurrentPeriodStart = start
	subscription.CurrentPeriodEnd = periodEnd
	subscription.UpdatedAt = now
	return updateSeatSubscription(ctx, tx, subscription)
}

func addBillingPeriod(start time.Time, billingPeriod string) (time.Time, error) {
	switch billingPeriod {
	case BillingPeriodMonth:
		return start.UTC().AddDate(0, 1, 0), nil
	case BillingPeriodQuarter:
		return start.UTC().AddDate(0, 3, 0), nil
	case BillingPeriodYear:
		return start.UTC().AddDate(1, 0, 0), nil
	default:
		return time.Time{}, storeerr.BadRequest("unsupported billing period")
	}
}

func subscriptionIsEffective(subscription SeatSubscription, now time.Time) bool {
	return subscription.Status == SubscriptionStatusActive &&
		!subscription.CurrentPeriodStart.After(now) &&
		now.Before(subscription.CurrentPeriodEnd)
}

func upsertSeatSubscription(ctx context.Context, queryer coredb.QueryExecer, subscription SeatSubscription) error {
	_, err := queryer.Exec(ctx, `
INSERT INTO organization_seat_subscriptions (
  organization_id,
  status,
  plan_key,
  billing_period,
  seat_quantity,
  managed_agents_per_seat_snapshot,
  max_human_seats_snapshot,
  unit_price_cents_snapshot,
  currency,
  current_period_start,
  current_period_end,
  created_at,
  updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
ON DUPLICATE KEY UPDATE
  status = VALUES(status),
  plan_key = VALUES(plan_key),
  billing_period = VALUES(billing_period),
  seat_quantity = VALUES(seat_quantity),
  managed_agents_per_seat_snapshot = VALUES(managed_agents_per_seat_snapshot),
  max_human_seats_snapshot = VALUES(max_human_seats_snapshot),
  unit_price_cents_snapshot = VALUES(unit_price_cents_snapshot),
  currency = VALUES(currency),
  current_period_start = VALUES(current_period_start),
  current_period_end = VALUES(current_period_end),
  updated_at = VALUES(updated_at)
`, subscription.OrganizationID, subscription.Status, subscription.PlanKey, subscription.BillingPeriod, subscription.SeatQuantity, subscription.ManagedAgentsPerSeatSnapshot, subscription.MaxHumanSeatsSnapshot, subscription.UnitPriceCentsSnapshot, subscription.Currency, subscription.CurrentPeriodStart.UTC(), subscription.CurrentPeriodEnd.UTC(), subscription.CreatedAt.UTC(), subscription.UpdatedAt.UTC())
	if err != nil {
		return fmt.Errorf("upsert seat subscription: %w", err)
	}
	return nil
}

func updateSeatSubscription(ctx context.Context, queryer coredb.QueryExecer, subscription SeatSubscription) error {
	result, err := queryer.Exec(ctx, `
UPDATE organization_seat_subscriptions
SET status = $2,
    plan_key = $3,
    billing_period = $4,
    seat_quantity = $5,
    managed_agents_per_seat_snapshot = $6,
    max_human_seats_snapshot = $7,
    unit_price_cents_snapshot = $8,
    currency = $9,
    current_period_start = $10,
    current_period_end = $11,
    updated_at = $12
WHERE organization_id = $1
`, subscription.OrganizationID, subscription.Status, subscription.PlanKey, subscription.BillingPeriod, subscription.SeatQuantity, subscription.ManagedAgentsPerSeatSnapshot, subscription.MaxHumanSeatsSnapshot, subscription.UnitPriceCentsSnapshot, subscription.Currency, subscription.CurrentPeriodStart.UTC(), subscription.CurrentPeriodEnd.UTC(), subscription.UpdatedAt.UTC())
	if err != nil {
		return fmt.Errorf("update seat subscription: %w", err)
	}
	if result.RowsAffected() == 0 {
		return errSeatOrderUnsafe
	}
	return nil
}

func markSeatOrderPaidApplied(ctx context.Context, queryer coredb.QueryExecer, order SeatOrder, params SettleSeatPaymentParams, paidAt time.Time, now time.Time) error {
	if err := markSeatPaymentPaid(ctx, queryer, order, params, paidAt, now); err != nil {
		return err
	}
	_, err := queryer.Exec(ctx, `
UPDATE organization_seat_orders
SET status = $1,
    pending_order_key = NULL,
    settled_at = $2,
    applied_at = $3,
    updated_at = $3
WHERE id = $4
`, OrderStatusPaid, paidAt.UTC(), now.UTC(), order.ID)
	if err != nil {
		return fmt.Errorf("mark seat order paid: %w", err)
	}
	return nil
}

func markSeatOrderPaidUnapplied(ctx context.Context, queryer coredb.QueryExecer, order SeatOrder, params SettleSeatPaymentParams, paidAt time.Time, now time.Time) error {
	if err := markSeatPaymentPaid(ctx, queryer, order, params, paidAt, now); err != nil {
		return err
	}
	_, err := queryer.Exec(ctx, `
UPDATE organization_seat_orders
SET status = $1,
    pending_order_key = NULL,
    settled_at = $2,
    applied_at = NULL,
    updated_at = $3
WHERE id = $4
`, OrderStatusPaidUnapplied, paidAt.UTC(), now.UTC(), order.ID)
	if err != nil {
		return fmt.Errorf("mark seat order paid unapplied: %w", err)
	}
	return nil
}

func markSeatPaymentPaid(ctx context.Context, queryer coredb.QueryExecer, order SeatOrder, params SettleSeatPaymentParams, paidAt time.Time, now time.Time) error {
	_, err := queryer.Exec(ctx, `
UPDATE organization_seat_payment_orders
SET status = $1,
    paid_at = $2,
    raw_payload_json = CAST($3 AS JSON),
    updated_at = $4
WHERE seat_order_id = $5
`, PaymentStatusPaid, paidAt.UTC(), params.RawPayloadJSON, now.UTC(), order.ID)
	if err != nil {
		return fmt.Errorf("mark seat payment paid: %w", err)
	}
	return nil
}

func insertSeatOrder(ctx context.Context, queryer coredb.QueryExecer, order SeatOrder) error {
	_, err := queryer.Exec(ctx, `
INSERT INTO organization_seat_orders (
  id,
  organization_id,
  order_type,
  status,
  from_plan_key,
  to_plan_key,
  billing_period,
  seat_quantity_before,
  seat_quantity_after,
  seat_delta,
  current_period_start_snapshot,
  current_period_end_snapshot,
  unit_price_cents_snapshot,
  target_unit_price_cents_snapshot,
  base_amount_cents,
  proration_snapshot_json,
  amount_cents,
  currency,
  entitlement_snapshot_json,
  idempotency_key,
  pending_order_key,
  expires_at,
  created_by_user_id,
  cancelled_by_user_id,
  settled_at,
  applied_at,
  created_at,
  updated_at
) VALUES (
  $1, $2, $3, $4, $5, $6, $7, $8, $9, $10,
  $11, $12, $13, $14, $15, CAST($16 AS JSON), $17, $18,
  CAST($19 AS JSON), $20, $21, $22, $23, $24, $25, $26, $27, $28
)
`, order.ID, order.OrganizationID, order.OrderType, order.Status, nullableStringArg(order.FromPlanKey), nullableStringArg(order.ToPlanKey), order.BillingPeriod, order.SeatQuantityBefore, order.SeatQuantityAfter, order.SeatDelta, nullableTimeArg(order.CurrentPeriodStartSnapshot), nullableTimeArg(order.CurrentPeriodEndSnapshot), order.UnitPriceCentsSnapshot, order.TargetUnitPriceCentsSnapshot, order.BaseAmountCents, order.ProrationSnapshotJSON, order.AmountCents, order.Currency, order.EntitlementSnapshotJSON, order.IdempotencyKey, pendingOrderKey, order.ExpiresAt.UTC(), order.CreatedByUserID, nullableStringArg(order.CancelledByUserID), nullableTimeArg(order.SettledAt), nullableTimeArg(order.AppliedAt), order.CreatedAt.UTC(), order.UpdatedAt.UTC())
	if err != nil {
		return fmt.Errorf("insert seat order: %w", err)
	}
	return nil
}

func insertSeatPaymentOrder(ctx context.Context, queryer coredb.QueryExecer, payment SeatPaymentOrder) error {
	_, err := queryer.Exec(ctx, `
INSERT INTO organization_seat_payment_orders (
  id,
  organization_id,
  seat_order_id,
  provider,
  provider_order_id,
  amount_cents,
  currency,
  status,
  paid_at,
  closed_at,
  raw_payload_json,
  created_at,
  updated_at
) VALUES (
  $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, CAST($11 AS JSON), $12, $13
)
`, payment.ID, payment.OrganizationID, payment.SeatOrderID, payment.Provider, payment.ProviderOrderID, payment.AmountCents, payment.Currency, payment.Status, nullableTimeArg(payment.PaidAt), nullableTimeArg(payment.ClosedAt), payment.RawPayloadJSON, payment.CreatedAt.UTC(), payment.UpdatedAt.UTC())
	if err != nil {
		return fmt.Errorf("insert seat payment order: %w", err)
	}
	return nil
}

func loadExistingSeatOrderAfterCreateConflict(ctx context.Context, queryer coredb.QueryRower, params CreateSeatOrderParams, insertErr error) (SeatOrderWithPayment, bool, error) {
	if !coredb.IsUniqueViolation(insertErr) {
		return SeatOrderWithPayment{}, false, nil
	}
	constraint, _ := coredb.UniqueViolationConstraint(insertErr)
	switch constraint {
	case "organization_seat_orders_idempotency_key":
		existing, found, err := loadSeatOrderByIdempotencyKey(ctx, queryer, params.OrganizationID, params.IdempotencyKey, false)
		return existing, found, err
	case "organization_seat_orders_pending_key":
		return SeatOrderWithPayment{}, false, storeerr.Conflict("organization already has a pending seat order")
	default:
		return SeatOrderWithPayment{}, false, nil
	}
}

func expirePendingSeatOrders(ctx context.Context, queryer coredb.QueryExecer, organizationID string, now time.Time) error {
	_, err := queryer.Exec(ctx, `
UPDATE organization_seat_orders
SET status = $1,
    pending_order_key = NULL,
    updated_at = $2
WHERE organization_id = $3
  AND status = $4
  AND expires_at <= $2
`, OrderStatusExpired, now.UTC(), organizationID, OrderStatusPending)
	if err != nil {
		return fmt.Errorf("expire pending seat orders: %w", err)
	}
	return nil
}

func hasPendingSeatOrder(ctx context.Context, queryer coredb.QueryRower, organizationID string, lock bool) (bool, error) {
	lockClause := ""
	if lock {
		lockClause = " FOR UPDATE"
	}
	var id string
	err := queryer.QueryRow(ctx, `
SELECT id
FROM organization_seat_orders
WHERE organization_id = $1
  AND pending_order_key = $2
LIMIT 1
`+lockClause, organizationID, pendingOrderKey).Scan(&id)
	if err != nil {
		if coredb.IsNotFound(err) {
			return false, nil
		}
		return false, fmt.Errorf("load pending seat order: %w", err)
	}
	return true, nil
}

func loadSeatOrderByID(ctx context.Context, queryer coredb.QueryRower, organizationID string, orderID string, lock bool) (SeatOrderWithPayment, bool, error) {
	return loadSeatOrderWithPayment(ctx, queryer, fmt.Sprintf(`
SELECT %s, %s
FROM organization_seat_orders o
JOIN organization_seat_payment_orders p ON p.seat_order_id = o.id
WHERE o.organization_id = $1
  AND o.id = $2
`, prefixedSeatOrderColumns("o"), prefixedSeatPaymentOrderColumns("p")), lock, organizationID, orderID)
}

func loadSeatOrderByIdempotencyKey(ctx context.Context, queryer coredb.QueryRower, organizationID string, idempotencyKey string, lock bool) (SeatOrderWithPayment, bool, error) {
	return loadSeatOrderWithPayment(ctx, queryer, fmt.Sprintf(`
SELECT %s, %s
FROM organization_seat_orders o
JOIN organization_seat_payment_orders p ON p.seat_order_id = o.id
WHERE o.organization_id = $1
  AND o.idempotency_key = $2
`, prefixedSeatOrderColumns("o"), prefixedSeatPaymentOrderColumns("p")), lock, organizationID, idempotencyKey)
}

func loadSeatOrderByProviderOrderID(ctx context.Context, queryer coredb.QueryRower, provider string, providerOrderID string, lock bool) (SeatOrderWithPayment, bool, error) {
	return loadSeatOrderWithPayment(ctx, queryer, fmt.Sprintf(`
SELECT %s, %s
FROM organization_seat_payment_orders p
JOIN organization_seat_orders o ON o.id = p.seat_order_id
WHERE p.provider = $1
  AND p.provider_order_id = $2
`, prefixedSeatOrderColumns("o"), prefixedSeatPaymentOrderColumns("p")), lock, provider, providerOrderID)
}

func loadSeatOrderWithPayment(ctx context.Context, queryer coredb.QueryRower, baseQuery string, lock bool, args ...any) (SeatOrderWithPayment, bool, error) {
	if lock {
		baseQuery += " FOR UPDATE"
	}
	order, err := scanSeatOrderWithPayment(queryer.QueryRow(ctx, baseQuery, args...))
	if err != nil {
		if coredb.IsNotFound(err) {
			return SeatOrderWithPayment{}, false, nil
		}
		return SeatOrderWithPayment{}, false, err
	}
	return order, true, nil
}

func prefixedSeatOrderColumns(alias string) string {
	prefix := alias + "."
	return strings.Join([]string{
		prefix + "id",
		prefix + "organization_id",
		prefix + "order_type",
		prefix + "status",
		prefix + "from_plan_key",
		prefix + "to_plan_key",
		prefix + "billing_period",
		prefix + "seat_quantity_before",
		prefix + "seat_quantity_after",
		prefix + "seat_delta",
		prefix + "current_period_start_snapshot",
		prefix + "current_period_end_snapshot",
		prefix + "unit_price_cents_snapshot",
		prefix + "target_unit_price_cents_snapshot",
		prefix + "base_amount_cents",
		"COALESCE(CAST(" + prefix + "proration_snapshot_json AS CHAR), '')",
		prefix + "amount_cents",
		prefix + "currency",
		"COALESCE(CAST(" + prefix + "entitlement_snapshot_json AS CHAR), '')",
		prefix + "idempotency_key",
		prefix + "expires_at",
		prefix + "created_by_user_id",
		prefix + "cancelled_by_user_id",
		prefix + "settled_at",
		prefix + "applied_at",
		prefix + "created_at",
		prefix + "updated_at",
	}, ", ")
}

func prefixedSeatPaymentOrderColumns(alias string) string {
	prefix := alias + "."
	return strings.Join([]string{
		prefix + "id",
		prefix + "organization_id",
		prefix + "seat_order_id",
		prefix + "provider",
		prefix + "provider_order_id",
		prefix + "amount_cents",
		prefix + "currency",
		prefix + "status",
		prefix + "paid_at",
		prefix + "closed_at",
		"COALESCE(CAST(" + prefix + "raw_payload_json AS CHAR), '')",
		prefix + "created_at",
		prefix + "updated_at",
	}, ", ")
}

type nullableSeatOrderScan struct {
	order                      SeatOrder
	fromPlanKey                sql.NullString
	toPlanKey                  sql.NullString
	currentPeriodStartSnapshot sql.NullTime
	currentPeriodEndSnapshot   sql.NullTime
	cancelledByUserID          sql.NullString
	settledAt                  sql.NullTime
	appliedAt                  sql.NullTime
}

func (scan *nullableSeatOrderScan) targets() []any {
	return []any{
		&scan.order.ID,
		&scan.order.OrganizationID,
		&scan.order.OrderType,
		&scan.order.Status,
		&scan.fromPlanKey,
		&scan.toPlanKey,
		&scan.order.BillingPeriod,
		&scan.order.SeatQuantityBefore,
		&scan.order.SeatQuantityAfter,
		&scan.order.SeatDelta,
		&scan.currentPeriodStartSnapshot,
		&scan.currentPeriodEndSnapshot,
		&scan.order.UnitPriceCentsSnapshot,
		&scan.order.TargetUnitPriceCentsSnapshot,
		&scan.order.BaseAmountCents,
		&scan.order.ProrationSnapshotJSON,
		&scan.order.AmountCents,
		&scan.order.Currency,
		&scan.order.EntitlementSnapshotJSON,
		&scan.order.IdempotencyKey,
		&scan.order.ExpiresAt,
		&scan.order.CreatedByUserID,
		&scan.cancelledByUserID,
		&scan.settledAt,
		&scan.appliedAt,
		&scan.order.CreatedAt,
		&scan.order.UpdatedAt,
	}
}

func (scan *nullableSeatOrderScan) finish() SeatOrder {
	scan.order.FromPlanKey = nullStringPtr(scan.fromPlanKey)
	scan.order.ToPlanKey = nullStringPtr(scan.toPlanKey)
	scan.order.CurrentPeriodStartSnapshot = nullTimePtr(scan.currentPeriodStartSnapshot)
	scan.order.CurrentPeriodEndSnapshot = nullTimePtr(scan.currentPeriodEndSnapshot)
	scan.order.CancelledByUserID = nullStringPtr(scan.cancelledByUserID)
	scan.order.SettledAt = nullTimePtr(scan.settledAt)
	scan.order.AppliedAt = nullTimePtr(scan.appliedAt)
	return scan.order
}

type nullableSeatPaymentOrderScan struct {
	payment  SeatPaymentOrder
	paidAt   sql.NullTime
	closedAt sql.NullTime
}

func (scan *nullableSeatPaymentOrderScan) targets() []any {
	return []any{
		&scan.payment.ID,
		&scan.payment.OrganizationID,
		&scan.payment.SeatOrderID,
		&scan.payment.Provider,
		&scan.payment.ProviderOrderID,
		&scan.payment.AmountCents,
		&scan.payment.Currency,
		&scan.payment.Status,
		&scan.paidAt,
		&scan.closedAt,
		&scan.payment.RawPayloadJSON,
		&scan.payment.CreatedAt,
		&scan.payment.UpdatedAt,
	}
}

func (scan *nullableSeatPaymentOrderScan) finish() SeatPaymentOrder {
	scan.payment.PaidAt = nullTimePtr(scan.paidAt)
	scan.payment.ClosedAt = nullTimePtr(scan.closedAt)
	return scan.payment
}

type seatOrderPaymentScanner interface {
	Scan(...any) error
}

func scanSeatOrderWithPayment(scanner seatOrderPaymentScanner) (SeatOrderWithPayment, error) {
	orderScan := nullableSeatOrderScan{}
	paymentScan := nullableSeatPaymentOrderScan{}
	targets := append(orderScan.targets(), paymentScan.targets()...)
	if err := scanner.Scan(targets...); err != nil {
		return SeatOrderWithPayment{}, fmt.Errorf("scan seat order with payment: %w", err)
	}
	return SeatOrderWithPayment{
		Order:   orderScan.finish(),
		Payment: paymentScan.finish(),
	}, nil
}

func normalizePayloadJSON(value string) (string, error) {
	trimmed := strings.TrimSpace(value)
	if trimmed == "" {
		return "{}", nil
	}
	if !model.IsValidJSON([]byte(trimmed)) {
		return "", storeerr.BadRequest("raw payload json is invalid")
	}
	return trimmed, nil
}

func normalizeListLimit(limit int, defaultLimit int, maxLimit int) int {
	if limit <= 0 {
		return defaultLimit
	}
	if limit > maxLimit {
		return maxLimit
	}
	return limit
}

func normalizeOrderStatuses(statuses []string) []string {
	if len(statuses) == 0 {
		return nil
	}
	normalized := make([]string, 0, len(statuses))
	seen := make(map[string]struct{}, len(statuses))
	for _, status := range statuses {
		trimmed := strings.TrimSpace(status)
		if trimmed == "" {
			continue
		}
		if _, ok := seen[trimmed]; ok {
			continue
		}
		seen[trimmed] = struct{}{}
		normalized = append(normalized, trimmed)
	}
	return normalized
}

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

func nullableTimeArg(value *time.Time) any {
	if value == nil {
		return nil
	}
	return value.UTC()
}

func nullStringPtr(value sql.NullString) *string {
	if !value.Valid {
		return nil
	}
	trimmed := value.String
	return &trimmed
}

func nullTimePtr(value sql.NullTime) *time.Time {
	if !value.Valid {
		return nil
	}
	timestamp := value.Time
	return &timestamp
}