package seatbilling

import (
	"context"
	"errors"
	"fmt"
	"strings"
	"time"

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

const pendingOrderKey = "pending"

var errSeatOrderUnsafe = errors.New("seat order is unsafe to apply")

type CreateSeatOrderParams struct {
	OrganizationID    string
	CreatedByUserID   string
	OrderType         string
	ToPlanKey         string
	BillingPeriod     string
	SeatQuantityAfter int
	IdempotencyKey    string
	Now               time.Time
}

type SettleSeatPaymentParams struct {
	Provider        string
	ProviderOrderID string
	AmountCents     int64
	Currency        string
	PaidAt          *time.Time
	ConfirmedAt     time.Time
	RawPayloadJSON  string
}

type CancelSeatOrderParams struct {
	OrganizationID    string
	OrderID           string
	CancelledByUserID string
	Now               time.Time
	RawPayloadJSON    string
}

type ListSeatOrdersParams struct {
	OrganizationID string
	Limit          int
	Statuses       []string
}

type ListSeatPaymentHistoryParams struct {
	OrganizationID string
	MonthStart     time.Time
	MonthEnd       time.Time
	Limit          int
}

type orderEntitlementSnapshot struct {
	PlanKey              string
	BillingPeriod        string
	ManagedAgentsPerSeat int64
	MaxHumanSeats        int
	UnitPriceCents       int64
	Currency             string
}

func (store *Store) CreateSeatOrder(ctx context.Context, params CreateSeatOrderParams) (SeatOrderWithPayment, error) {
	db, err := store.requireDB()
	if err != nil {
		return SeatOrderWithPayment{}, err
	}
	params, now, err := normalizeCreateSeatOrderParams(params)
	if err != nil {
		return SeatOrderWithPayment{}, err
	}

	orderID, err := identifiers.NewEntityID("oso")
	if err != nil {
		return SeatOrderWithPayment{}, fmt.Errorf("generate seat order id: %w", err)
	}
	paymentID, err := identifiers.NewEntityID("ospay")
	if err != nil {
		return SeatOrderWithPayment{}, fmt.Errorf("generate seat payment order id: %w", err)
	}

	var result SeatOrderWithPayment
	err = coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		existing, found, err := loadSeatOrderByIdempotencyKey(ctx, tx, params.OrganizationID, params.IdempotencyKey, false)
		if err != nil {
			return err
		}
		if found {
			result = existing
			return nil
		}

		if err := expirePendingSeatOrders(ctx, tx, params.OrganizationID, now); err != nil {
			return err
		}
		hasPending, err := hasPendingSeatOrder(ctx, tx, params.OrganizationID, true)
		if err != nil {
			return err
		}
		if hasPending {
			return storeerr.Conflict("organization already has a pending seat order")
		}

		order, err := buildSeatOrder(ctx, tx, params, now)
		if err != nil {
			return err
		}
		order.ID = orderID
		order.CreatedAt = now
		order.UpdatedAt = now
		order.ExpiresAt = now.Add(SeatOrderPaymentTTL)

		payment := SeatPaymentOrder{
			ID:              paymentID,
			OrganizationID:  params.OrganizationID,
			SeatOrderID:     order.ID,
			Provider:        PaymentProviderAlipay,
			ProviderOrderID: paymentID,
			AmountCents:     order.AmountCents,
			Currency:        order.Currency,
			Status:          PaymentStatusPending,
			RawPayloadJSON:  "{}",
			CreatedAt:       now,
			UpdatedAt:       now,
		}

		if err := insertSeatOrder(ctx, tx, order); err != nil {
			if existing, ok, loadErr := loadExistingSeatOrderAfterCreateConflict(ctx, tx, params, err); loadErr != nil {
				return loadErr
			} else if ok {
				result = existing
				return nil
			}
			return err
		}
		if err := insertSeatPaymentOrder(ctx, tx, payment); err != nil {
			return err
		}
		result = SeatOrderWithPayment{Order: order, Payment: payment}
		return nil
	})
	if err != nil {
		return SeatOrderWithPayment{}, err
	}
	return result, nil
}

func (store *Store) SettleSeatPaymentPaid(ctx context.Context, params SettleSeatPaymentParams) (SeatOrderWithPayment, error) {
	db, err := store.requireDB()
	if err != nil {
		return SeatOrderWithPayment{}, err
	}
	params, paidAt, confirmedAt, err := normalizeSettleSeatPaymentParams(params)
	if err != nil {
		return SeatOrderWithPayment{}, err
	}

	var result SeatOrderWithPayment
	err = coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		current, found, err := loadSeatOrderByProviderOrderID(ctx, tx, params.Provider, params.ProviderOrderID, true)
		if err != nil {
			return err
		}
		if !found {
			return storeerr.NotFound("seat payment order not found")
		}
		if current.Order.AmountCents != params.AmountCents {
			return storeerr.Conflict("seat payment amount does not match order")
		}
		if !strings.EqualFold(current.Order.Currency, params.Currency) || !strings.EqualFold(current.Payment.Currency, params.Currency) {
			return storeerr.Conflict("seat payment currency does not match order")
		}
		if current.Order.Status == OrderStatusPaid {
			result = current
			return nil
		}
		if current.Payment.PaidAt != nil {
			paidAt = current.Payment.PaidAt.UTC()
		}

		applicable, err := canApplyPaidSeatOrder(ctx, tx, current.Order, paidAt)
		if err != nil {
			return err
		}
		if applicable {
			if err := applyPaidSeatOrder(ctx, tx, current.Order, paidAt, confirmedAt); err != nil {
				if !errors.Is(err, errSeatOrderUnsafe) {
					return err
				}
				applicable = false
			}
		}
		if !applicable {
			if err := markSeatOrderPaidUnapplied(ctx, tx, current.Order, params, paidAt, confirmedAt); err != nil {
				return err
			}
		} else if err := markSeatOrderPaidApplied(ctx, tx, current.Order, params, paidAt, confirmedAt); err != nil {
			return err
		}

		loaded, found, err := loadSeatOrderByProviderOrderID(ctx, tx, params.Provider, params.ProviderOrderID, true)
		if err != nil {
			return err
		}
		if !found {
			return storeerr.NotFound("settled seat payment order not found")
		}
		result = loaded
		return nil
	})
	if err != nil {
		return SeatOrderWithPayment{}, err
	}
	return result, nil
}

func (store *Store) CancelSeatOrder(ctx context.Context, params CancelSeatOrderParams) (SeatOrderWithPayment, error) {
	db, err := store.requireDB()
	if err != nil {
		return SeatOrderWithPayment{}, err
	}
	params, now, err := normalizeCancelSeatOrderParams(params)
	if err != nil {
		return SeatOrderWithPayment{}, err
	}

	var result SeatOrderWithPayment
	err = coredb.WithTx(ctx, db, func(tx coredb.Tx) error {
		current, found, err := loadSeatOrderByID(ctx, tx, params.OrganizationID, params.OrderID, true)
		if err != nil {
			return err
		}
		if !found {
			return storeerr.NotFound("seat order not found")
		}
		if current.Order.Status != OrderStatusPending || current.Payment.Status == PaymentStatusPaid {
			return storeerr.Conflict("seat order is not cancellable")
		}

		_, err = tx.Exec(ctx, `
UPDATE organization_seat_payment_orders
SET status = $1,
    closed_at = $2,
    raw_payload_json = CAST($3 AS JSON),
    updated_at = $2
WHERE id = $4
`, PaymentStatusClosed, now, params.RawPayloadJSON, current.Payment.ID)
		if err != nil {
			return fmt.Errorf("close seat payment order: %w", err)
		}
		_, err = tx.Exec(ctx, `
UPDATE organization_seat_orders
SET status = $1,
    pending_order_key = NULL,
    cancelled_by_user_id = $2,
    updated_at = $3
WHERE id = $4
`, OrderStatusCancelled, params.CancelledByUserID, now, current.Order.ID)
		if err != nil {
			return fmt.Errorf("cancel seat order: %w", err)
		}

		loaded, found, err := loadSeatOrderByID(ctx, tx, params.OrganizationID, params.OrderID, true)
		if err != nil {
			return err
		}
		if !found {
			return storeerr.NotFound("cancelled seat order not found")
		}
		result = loaded
		return nil
	})
	if err != nil {
		return SeatOrderWithPayment{}, err
	}
	return result, nil
}

func (store *Store) LoadSeatOrder(ctx context.Context, organizationID string, orderID string) (SeatOrderWithPayment, bool, error) {
	db, err := store.requireDB()
	if err != nil {
		return SeatOrderWithPayment{}, false, err
	}
	trimmedOrganizationID := strings.TrimSpace(organizationID)
	trimmedOrderID := strings.TrimSpace(orderID)
	if trimmedOrganizationID == "" {
		return SeatOrderWithPayment{}, false, storeerr.BadRequest("organizationId is required")
	}
	if trimmedOrderID == "" {
		return SeatOrderWithPayment{}, false, storeerr.BadRequest("orderId is required")
	}
	return loadSeatOrderByID(ctx, db, trimmedOrganizationID, trimmedOrderID, false)
}

func (store *Store) LoadSeatOrderByProviderOrderID(ctx context.Context, provider string, providerOrderID string) (SeatOrderWithPayment, bool, error) {
	db, err := store.requireDB()
	if err != nil {
		return SeatOrderWithPayment{}, false, err
	}
	trimmedProvider := strings.TrimSpace(provider)
	trimmedProviderOrderID := strings.TrimSpace(providerOrderID)
	if trimmedProvider == "" {
		return SeatOrderWithPayment{}, false, storeerr.BadRequest("provider is required")
	}
	if trimmedProviderOrderID == "" {
		return SeatOrderWithPayment{}, false, storeerr.BadRequest("providerOrderId is required")
	}
	return loadSeatOrderByProviderOrderID(ctx, db, trimmedProvider, trimmedProviderOrderID, false)
}

func (store *Store) ListSeatOrders(ctx context.Context, params ListSeatOrdersParams) ([]SeatOrderWithPayment, error) {
	db, err := store.requireDB()
	if err != nil {
		return nil, err
	}
	organizationID := strings.TrimSpace(params.OrganizationID)
	if organizationID == "" {
		return nil, storeerr.BadRequest("organizationId is required")
	}
	limit := normalizeListLimit(params.Limit, 50, 100)
	statuses := normalizeOrderStatuses(params.Statuses)

	query := 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
`, prefixedSeatOrderColumns("o"), prefixedSeatPaymentOrderColumns("p"))
	args := []any{organizationID}
	if len(statuses) > 0 {
		placeholders := make([]string, 0, len(statuses))
		for _, status := range statuses {
			args = append(args, status)
			placeholders = append(placeholders, fmt.Sprintf("$%d", len(args)))
		}
		query += "  AND o.status IN (" + strings.Join(placeholders, ", ") + ")\n"
	}
	args = append(args, limit)
	query += fmt.Sprintf("ORDER BY o.created_at DESC, o.id DESC\nLIMIT $%d", len(args))

	rows, err := db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list seat orders: %w", err)
	}
	defer rows.Close()

	orders := make([]SeatOrderWithPayment, 0)
	for rows.Next() {
		order, err := scanSeatOrderWithPayment(rows)
		if err != nil {
			return nil, err
		}
		orders = append(orders, order)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate seat orders: %w", err)
	}
	return orders, nil
}

func (store *Store) ListSeatPaymentHistory(ctx context.Context, params ListSeatPaymentHistoryParams) ([]SeatPaymentHistoryRow, error) {
	db, err := store.requireDB()
	if err != nil {
		return nil, err
	}
	organizationID := strings.TrimSpace(params.OrganizationID)
	if organizationID == "" {
		return nil, storeerr.BadRequest("organizationId is required")
	}
	if params.MonthStart.IsZero() || params.MonthEnd.IsZero() || !params.MonthEnd.After(params.MonthStart) {
		return nil, storeerr.BadRequest("valid month range is required")
	}
	limit := normalizeListLimit(params.Limit, 100, 100)

	rows, err := db.Query(ctx, 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.organization_id = $1
  AND (
    (p.paid_at IS NOT NULL AND p.paid_at >= $2 AND p.paid_at < $3)
    OR (p.paid_at IS NULL AND p.created_at >= $2 AND p.created_at < $3)
  )
ORDER BY COALESCE(p.paid_at, p.created_at) DESC, p.created_at DESC, p.id DESC
LIMIT $4
`, prefixedSeatOrderColumns("o"), prefixedSeatPaymentOrderColumns("p")), organizationID, params.MonthStart.UTC(), params.MonthEnd.UTC(), limit)
	if err != nil {
		return nil, fmt.Errorf("list seat payment history: %w", err)
	}
	defer rows.Close()

	history := make([]SeatPaymentHistoryRow, 0)
	for rows.Next() {
		order, err := scanSeatOrderWithPayment(rows)
		if err != nil {
			return nil, err
		}
		history = append(history, SeatPaymentHistoryRow{Order: order.Order, Payment: order.Payment})
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate seat payment history: %w", err)
	}
	return history, nil
}

func normalizeCreateSeatOrderParams(params CreateSeatOrderParams) (CreateSeatOrderParams, time.Time, error) {
	params.OrganizationID = strings.TrimSpace(params.OrganizationID)
	params.CreatedByUserID = strings.TrimSpace(params.CreatedByUserID)
	params.OrderType = strings.TrimSpace(params.OrderType)
	params.ToPlanKey = strings.TrimSpace(params.ToPlanKey)
	params.BillingPeriod = strings.TrimSpace(params.BillingPeriod)
	params.IdempotencyKey = strings.TrimSpace(params.IdempotencyKey)
	if params.OrganizationID == "" {
		return CreateSeatOrderParams{}, time.Time{}, storeerr.BadRequest("organizationId is required")
	}
	if params.CreatedByUserID == "" {
		return CreateSeatOrderParams{}, time.Time{}, storeerr.BadRequest("createdByUserId is required")
	}
	if params.OrderType == "" {
		return CreateSeatOrderParams{}, time.Time{}, storeerr.BadRequest("orderType is required")
	}
	if params.IdempotencyKey == "" {
		return CreateSeatOrderParams{}, time.Time{}, storeerr.BadRequest("idempotencyKey is required")
	}
	now := params.Now
	if now.IsZero() {
		now = time.Now().UTC()
	}
	return params, now.UTC(), nil
}

func normalizeSettleSeatPaymentParams(params SettleSeatPaymentParams) (SettleSeatPaymentParams, time.Time, time.Time, error) {
	params.Provider = strings.TrimSpace(params.Provider)
	params.ProviderOrderID = strings.TrimSpace(params.ProviderOrderID)
	params.Currency = strings.TrimSpace(params.Currency)
	rawPayloadJSON, err := normalizePayloadJSON(params.RawPayloadJSON)
	if err != nil {
		return SettleSeatPaymentParams{}, time.Time{}, time.Time{}, err
	}
	params.RawPayloadJSON = rawPayloadJSON
	if params.Provider == "" {
		return SettleSeatPaymentParams{}, time.Time{}, time.Time{}, storeerr.BadRequest("provider is required")
	}
	if params.ProviderOrderID == "" {
		return SettleSeatPaymentParams{}, time.Time{}, time.Time{}, storeerr.BadRequest("providerOrderId is required")
	}
	if params.AmountCents <= 0 {
		return SettleSeatPaymentParams{}, time.Time{}, time.Time{}, storeerr.BadRequest("amountCents must be positive")
	}
	if params.Currency == "" {
		return SettleSeatPaymentParams{}, time.Time{}, time.Time{}, storeerr.BadRequest("currency is required")
	}
	confirmedAt := params.ConfirmedAt
	if confirmedAt.IsZero() {
		confirmedAt = time.Now().UTC()
	}
	paidAt := confirmedAt
	if params.PaidAt != nil && !params.PaidAt.IsZero() {
		paidAt = params.PaidAt.UTC()
	}
	return params, paidAt.UTC(), confirmedAt.UTC(), nil
}

func normalizeCancelSeatOrderParams(params CancelSeatOrderParams) (CancelSeatOrderParams, time.Time, error) {
	params.OrganizationID = strings.TrimSpace(params.OrganizationID)
	params.OrderID = strings.TrimSpace(params.OrderID)
	params.CancelledByUserID = strings.TrimSpace(params.CancelledByUserID)
	rawPayloadJSON, err := normalizePayloadJSON(params.RawPayloadJSON)
	if err != nil {
		return CancelSeatOrderParams{}, time.Time{}, err
	}
	params.RawPayloadJSON = rawPayloadJSON
	if params.OrganizationID == "" {
		return CancelSeatOrderParams{}, time.Time{}, storeerr.BadRequest("organizationId is required")
	}
	if params.OrderID == "" {
		return CancelSeatOrderParams{}, time.Time{}, storeerr.BadRequest("orderId is required")
	}
	if params.CancelledByUserID == "" {
		return CancelSeatOrderParams{}, time.Time{}, storeerr.BadRequest("cancelledByUserId is required")
	}
	now := params.Now
	if now.IsZero() {
		now = time.Now().UTC()
	}
	return params, now.UTC(), nil
}

func buildSeatOrder(ctx context.Context, queryer coredb.QueryRower, params CreateSeatOrderParams, now time.Time) (SeatOrder, error) {
	switch params.OrderType {
	case OrderTypeNewSubscription:
		return buildNewSubscriptionOrder(ctx, queryer, params, now)
	case OrderTypeAddSeats:
		return buildAddSeatsOrder(ctx, queryer, params, now)
	case OrderTypeUpgradeTier:
		return buildUpgradeTierOrder(ctx, queryer, params, now)
	case OrderTypeRenewSubscription:
		return buildRenewSubscriptionOrder(ctx, queryer, params, now)
	default:
		return SeatOrder{}, storeerr.BadRequest("unsupported seat order type")
	}
}

func buildNewSubscriptionOrder(ctx context.Context, queryer coredb.QueryRower, params CreateSeatOrderParams, now time.Time) (SeatOrder, error) {
	if params.ToPlanKey != PlanKeyTeam && params.ToPlanKey != PlanKeyEnterprise {
		return SeatOrder{}, storeerr.BadRequest("toPlanKey must be team or enterprise")
	}
	if params.BillingPeriod == "" {
		return SeatOrder{}, storeerr.BadRequest("billingPeriod is required")
	}
	plan, err := loadSelfServicePlan(ctx, queryer, params.ToPlanKey, params.BillingPeriod)
	if err != nil {
		return SeatOrder{}, err
	}
	if err := validateSeatQuantity(params.SeatQuantityAfter, plan.MinSeatQuantity, plan.MaxSeatQuantity); err != nil {
		return SeatOrder{}, err
	}
	if subscription, found, err := loadSubscription(ctx, queryer, params.OrganizationID, true); err != nil {
		return SeatOrder{}, err
	} else if found && subscriptionIsEffective(subscription, now) {
		return SeatOrder{}, storeerr.Conflict("organization already has an active seat subscription")
	}

	amount := int64(params.SeatQuantityAfter) * plan.UnitPriceCents
	if amount <= 0 {
		return SeatOrder{}, storeerr.New(storeerr.ErrUnprocessable, "seat order amount is not payable")
	}
	prorationJSON, err := marshalSnapshot(map[string]any{
		"prorated":          false,
		"base_amount_cents": amount,
		"amount_cents":      amount,
	})
	if err != nil {
		return SeatOrder{}, err
	}
	entitlementJSON, err := planEntitlementSnapshotJSON(plan)
	if err != nil {
		return SeatOrder{}, err
	}

	toPlanKey := plan.PlanKey
	return SeatOrder{
		OrganizationID:               params.OrganizationID,
		OrderType:                    OrderTypeNewSubscription,
		Status:                       OrderStatusPending,
		ToPlanKey:                    &toPlanKey,
		BillingPeriod:                plan.BillingPeriod,
		SeatQuantityBefore:           0,
		SeatQuantityAfter:            params.SeatQuantityAfter,
		SeatDelta:                    params.SeatQuantityAfter,
		UnitPriceCentsSnapshot:       plan.UnitPriceCents,
		TargetUnitPriceCentsSnapshot: plan.UnitPriceCents,
		BaseAmountCents:              amount,
		ProrationSnapshotJSON:        prorationJSON,
		AmountCents:                  amount,
		Currency:                     plan.Currency,
		EntitlementSnapshotJSON:      entitlementJSON,
		IdempotencyKey:               params.IdempotencyKey,
		CreatedByUserID:              params.CreatedByUserID,
	}, nil
}

func buildAddSeatsOrder(ctx context.Context, queryer coredb.QueryRower, params CreateSeatOrderParams, now time.Time) (SeatOrder, error) {
	subscription, found, err := loadEffectiveSubscription(ctx, queryer, params.OrganizationID, now, true)
	if err != nil {
		return SeatOrder{}, err
	}
	if !found {
		return SeatOrder{}, storeerr.Conflict("active seat subscription is required")
	}
	if params.SeatQuantityAfter <= subscription.SeatQuantity {
		return SeatOrder{}, storeerr.BadRequest("seatQuantityAfter must be greater than current seat quantity")
	}
	if seatQuantityExceedsMax(params.SeatQuantityAfter, subscription.MaxHumanSeatsSnapshot) {
		return SeatOrder{}, storeerr.Conflict("seat quantity exceeds current plan limit")
	}

	seatDelta := params.SeatQuantityAfter - subscription.SeatQuantity
	baseAmount := int64(seatDelta) * subscription.UnitPriceCentsSnapshot
	proration, amount, err := prorateSeatOrderAmount(ctx, queryer, params.OrganizationID, now, subscription.CurrentPeriodStart, subscription.CurrentPeriodEnd, baseAmount)
	if err != nil {
		return SeatOrder{}, err
	}
	if amount <= 0 {
		return SeatOrder{}, storeerr.New(storeerr.ErrUnprocessable, "prorated seat order amount is not payable")
	}
	entitlementJSON, err := subscriptionEntitlementSnapshotJSON(subscription)
	if err != nil {
		return SeatOrder{}, err
	}

	fromPlanKey := subscription.PlanKey
	toPlanKey := subscription.PlanKey
	start := subscription.CurrentPeriodStart
	end := subscription.CurrentPeriodEnd
	return SeatOrder{
		OrganizationID:               params.OrganizationID,
		OrderType:                    OrderTypeAddSeats,
		Status:                       OrderStatusPending,
		FromPlanKey:                  &fromPlanKey,
		ToPlanKey:                    &toPlanKey,
		BillingPeriod:                subscription.BillingPeriod,
		SeatQuantityBefore:           subscription.SeatQuantity,
		SeatQuantityAfter:            params.SeatQuantityAfter,
		SeatDelta:                    seatDelta,
		CurrentPeriodStartSnapshot:   &start,
		CurrentPeriodEndSnapshot:     &end,
		UnitPriceCentsSnapshot:       subscription.UnitPriceCentsSnapshot,
		TargetUnitPriceCentsSnapshot: subscription.UnitPriceCentsSnapshot,
		BaseAmountCents:              baseAmount,
		ProrationSnapshotJSON:        proration,
		AmountCents:                  amount,
		Currency:                     subscription.Currency,
		EntitlementSnapshotJSON:      entitlementJSON,
		IdempotencyKey:               params.IdempotencyKey,
		CreatedByUserID:              params.CreatedByUserID,
	}, nil
}

func buildUpgradeTierOrder(ctx context.Context, queryer coredb.QueryRower, params CreateSeatOrderParams, now time.Time) (SeatOrder, error) {
	subscription, found, err := loadEffectiveSubscription(ctx, queryer, params.OrganizationID, now, true)
	if err != nil {
		return SeatOrder{}, err
	}
	if !found {
		return SeatOrder{}, storeerr.Conflict("active seat subscription is required")
	}
	if subscription.PlanKey != PlanKeyTeam || params.ToPlanKey != PlanKeyEnterprise {
		return SeatOrder{}, storeerr.Conflict("only active Team subscriptions can upgrade to Enterprise")
	}
	plan, err := loadSelfServicePlan(ctx, queryer, PlanKeyEnterprise, subscription.BillingPeriod)
	if err != nil {
		return SeatOrder{}, err
	}
	if seatQuantityExceedsMax(subscription.SeatQuantity, plan.MaxSeatQuantity) {
		return SeatOrder{}, storeerr.Conflict("current seat quantity exceeds target plan limit")
	}
	baseAmount := int64(subscription.SeatQuantity) * (plan.UnitPriceCents - subscription.UnitPriceCentsSnapshot)
	if baseAmount <= 0 {
		return SeatOrder{}, storeerr.New(storeerr.ErrUnprocessable, "upgrade amount is not payable")
	}
	proration, amount, err := prorateSeatOrderAmount(ctx, queryer, params.OrganizationID, now, subscription.CurrentPeriodStart, subscription.CurrentPeriodEnd, baseAmount)
	if err != nil {
		return SeatOrder{}, err
	}
	if amount <= 0 {
		return SeatOrder{}, storeerr.New(storeerr.ErrUnprocessable, "prorated upgrade amount is not payable")
	}
	entitlementJSON, err := planEntitlementSnapshotJSON(plan)
	if err != nil {
		return SeatOrder{}, err
	}

	fromPlanKey := subscription.PlanKey
	toPlanKey := plan.PlanKey
	start := subscription.CurrentPeriodStart
	end := subscription.CurrentPeriodEnd
	return SeatOrder{
		OrganizationID:               params.OrganizationID,
		OrderType:                    OrderTypeUpgradeTier,
		Status:                       OrderStatusPending,
		FromPlanKey:                  &fromPlanKey,
		ToPlanKey:                    &toPlanKey,
		BillingPeriod:                subscription.BillingPeriod,
		SeatQuantityBefore:           subscription.SeatQuantity,
		SeatQuantityAfter:            subscription.SeatQuantity,
		SeatDelta:                    0,
		CurrentPeriodStartSnapshot:   &start,
		CurrentPeriodEndSnapshot:     &end,
		UnitPriceCentsSnapshot:       subscription.UnitPriceCentsSnapshot,
		TargetUnitPriceCentsSnapshot: plan.UnitPriceCents,
		BaseAmountCents:              baseAmount,
		ProrationSnapshotJSON:        proration,
		AmountCents:                  amount,
		Currency:                     plan.Currency,
		EntitlementSnapshotJSON:      entitlementJSON,
		IdempotencyKey:               params.IdempotencyKey,
		CreatedByUserID:              params.CreatedByUserID,
	}, nil
}

func buildRenewSubscriptionOrder(ctx context.Context, queryer coredb.QueryRower, params CreateSeatOrderParams, now time.Time) (SeatOrder, error) {
	subscription, found, err := loadSubscription(ctx, queryer, params.OrganizationID, true)
	if err != nil {
		return SeatOrder{}, err
	}
	if !found {
		return SeatOrder{}, storeerr.Conflict("seat subscription is required")
	}
	if subscription.Status != SubscriptionStatusActive && subscription.Status != SubscriptionStatusExpired {
		return SeatOrder{}, storeerr.Conflict("seat subscription is not renewable")
	}
	plan, err := loadSelfServicePlan(ctx, queryer, subscription.PlanKey, subscription.BillingPeriod)
	if err != nil {
		return SeatOrder{}, err
	}
	if !seatQuantityWithinPlanRange(subscription.SeatQuantity, plan.MinSeatQuantity, plan.MaxSeatQuantity) {
		return SeatOrder{}, storeerr.Conflict("current seat quantity is outside current plan limit")
	}

	amount := int64(subscription.SeatQuantity) * plan.UnitPriceCents
	if amount <= 0 {
		return SeatOrder{}, storeerr.New(storeerr.ErrUnprocessable, "renewal amount is not payable")
	}
	prorationJSON, err := marshalSnapshot(map[string]any{
		"prorated":          false,
		"base_amount_cents": amount,
		"amount_cents":      amount,
	})
	if err != nil {
		return SeatOrder{}, err
	}
	entitlementJSON, err := planEntitlementSnapshotJSON(plan)
	if err != nil {
		return SeatOrder{}, err
	}

	fromPlanKey := subscription.PlanKey
	toPlanKey := subscription.PlanKey
	start := subscription.CurrentPeriodStart
	end := subscription.CurrentPeriodEnd
	return SeatOrder{
		OrganizationID:               params.OrganizationID,
		OrderType:                    OrderTypeRenewSubscription,
		Status:                       OrderStatusPending,
		FromPlanKey:                  &fromPlanKey,
		ToPlanKey:                    &toPlanKey,
		BillingPeriod:                subscription.BillingPeriod,
		SeatQuantityBefore:           subscription.SeatQuantity,
		SeatQuantityAfter:            subscription.SeatQuantity,
		SeatDelta:                    0,
		CurrentPeriodStartSnapshot:   &start,
		CurrentPeriodEndSnapshot:     &end,
		UnitPriceCentsSnapshot:       subscription.UnitPriceCentsSnapshot,
		TargetUnitPriceCentsSnapshot: plan.UnitPriceCents,
		BaseAmountCents:              amount,
		ProrationSnapshotJSON:        prorationJSON,
		AmountCents:                  amount,
		Currency:                     plan.Currency,
		EntitlementSnapshotJSON:      entitlementJSON,
		IdempotencyKey:               params.IdempotencyKey,
		CreatedByUserID:              params.CreatedByUserID,
	}, nil
}

func validateSeatQuantity(quantity int, minQuantity int, maxQuantity int) error {
	if !seatQuantityWithinPlanRange(quantity, minQuantity, maxQuantity) {
		return storeerr.BadRequest("seat quantity is outside plan range")
	}
	return nil
}

func seatQuantityWithinPlanRange(quantity int, minQuantity int, maxQuantity int) bool {
	return quantity >= minQuantity && !seatQuantityExceedsMax(quantity, maxQuantity)
}

func seatQuantityExceedsMax(quantity int, maxQuantity int) bool {
	return maxQuantity > 0 && quantity > maxQuantity
}

func prorateSeatOrderAmount(ctx context.Context, queryer coredb.QueryRower, organizationID string, now time.Time, periodStart time.Time, periodEnd time.Time, baseAmount int64) (string, int64, error) {
	if baseAmount <= 0 {
		return "", 0, storeerr.New(storeerr.ErrUnprocessable, "base amount is not payable")
	}
	location, timezoneName, err := loadOrganizationLocation(ctx, queryer, organizationID)
	if err != nil {
		return "", 0, err
	}
	today := localDateStart(now, location)
	startDate := localDateStart(periodStart, location)
	endDate := localDateStart(periodEnd, location)
	remainingDays := daysBetween(today, endDate)
	periodDays := daysBetween(startDate, endDate)
	if remainingDays <= 0 || periodDays <= 0 {
		return "", 0, storeerr.New(storeerr.ErrUnprocessable, "prorated seat order amount is not payable")
	}
	amount := ceilDivide(baseAmount*int64(remainingDays), int64(periodDays))
	prorationJSON, err := marshalSnapshot(map[string]any{
		"prorated":                  true,
		"timezone":                  timezoneName,
		"current_period_start":      periodStart.UTC().Format(time.RFC3339),
		"current_period_end":        periodEnd.UTC().Format(time.RFC3339),
		"today":                     today.Format("2006-01-02"),
		"current_period_start_date": startDate.Format("2006-01-02"),
		"current_period_end_date":   endDate.Format("2006-01-02"),
		"remaining_days":            remainingDays,
		"period_days":               periodDays,
		"base_delta_amount_cents":   baseAmount,
		"prorated_amount_cents":     amount,
	})
	if err != nil {
		return "", 0, err
	}
	return prorationJSON, amount, nil
}

func loadOrganizationLocation(ctx context.Context, queryer coredb.QueryRower, organizationID string) (*time.Location, string, error) {
	var timezone string
	err := queryer.QueryRow(ctx, `
SELECT timezone
FROM organization_settings
WHERE organization_id = $1
`, organizationID).Scan(&timezone)
	if err != nil {
		if coredb.IsNotFound(err) {
			return time.UTC, "UTC", nil
		}
		return nil, "", fmt.Errorf("load organization timezone: %w", err)
	}
	timezone = strings.TrimSpace(timezone)
	if timezone == "" {
		return time.UTC, "UTC", nil
	}
	location, err := time.LoadLocation(timezone)
	if err != nil {
		return nil, "", fmt.Errorf("load organization timezone %q: %w", timezone, err)
	}
	return location, timezone, nil
}

func localDateStart(value time.Time, location *time.Location) time.Time {
	year, month, day := value.In(location).Date()
	return time.Date(year, month, day, 0, 0, 0, 0, location)
}

func daysBetween(start time.Time, end time.Time) int {
	startYear, startMonth, startDay := start.Date()
	endYear, endMonth, endDay := end.Date()
	startDate := time.Date(startYear, startMonth, startDay, 0, 0, 0, 0, time.UTC)
	endDate := time.Date(endYear, endMonth, endDay, 0, 0, 0, 0, time.UTC)
	if !endDate.After(startDate) {
		return 0
	}
	return int(endDate.Sub(startDate) / (24 * time.Hour))
}

func ceilDivide(numerator int64, denominator int64) int64 {
	if denominator <= 0 {
		return 0
	}
	return (numerator + denominator - 1) / denominator
}

func planEntitlementSnapshotJSON(plan SeatPlan) (string, error) {
	return marshalSnapshot(map[string]any{
		"plan_key":                plan.PlanKey,
		"billing_period":          plan.BillingPeriod,
		"managed_agents_per_seat": plan.ManagedAgentsPerSeat,
		"max_human_seats":         plan.MaxSeatQuantity,
		"unit_price_cents":        plan.UnitPriceCents,
		"currency":                plan.Currency,
	})
}

func subscriptionEntitlementSnapshotJSON(subscription SeatSubscription) (string, error) {
	return marshalSnapshot(map[string]any{
		"plan_key":                subscription.PlanKey,
		"billing_period":          subscription.BillingPeriod,
		"managed_agents_per_seat": subscription.ManagedAgentsPerSeatSnapshot,
		"max_human_seats":         subscription.MaxHumanSeatsSnapshot,
		"unit_price_cents":        subscription.UnitPriceCentsSnapshot,
		"currency":                subscription.Currency,
	})
}

func marshalSnapshot(snapshot map[string]any) (string, error) {
	payload, err := model.MarshalJSONString("seat order snapshot", snapshot)
	if err != nil {
		return "", fmt.Errorf("marshal seat order snapshot: %w", err)
	}
	return payload, nil
}

func parseOrderEntitlementSnapshot(order SeatOrder) (orderEntitlementSnapshot, error) {
	var values map[string]any
	if err := model.UnmarshalJSONString("seat order entitlement snapshot", order.EntitlementSnapshotJSON, &values); err != nil {
		return orderEntitlementSnapshot{}, fmt.Errorf("parse seat order entitlement snapshot: %w", err)
	}
	snapshot := orderEntitlementSnapshot{
		PlanKey:       stringSnapshotValue(values, "plan_key"),
		BillingPeriod: stringSnapshotValue(values, "billing_period"),
		Currency:      stringSnapshotValue(values, "currency"),
	}
	var ok bool
	snapshot.ManagedAgentsPerSeat, ok = int64SnapshotValue(values, "managed_agents_per_seat")
	if !ok {
		return orderEntitlementSnapshot{}, fmt.Errorf("seat order entitlement snapshot missing managed_agents_per_seat")
	}
	maxHumanSeats, ok := int64SnapshotValue(values, "max_human_seats")
	if !ok {
		return orderEntitlementSnapshot{}, fmt.Errorf("seat order entitlement snapshot missing max_human_seats")
	}
	snapshot.MaxHumanSeats = int(maxHumanSeats)
	snapshot.UnitPriceCents, ok = int64SnapshotValue(values, "unit_price_cents")
	if !ok {
		return orderEntitlementSnapshot{}, fmt.Errorf("seat order entitlement snapshot missing unit_price_cents")
	}
	return snapshot, nil
}

func stringSnapshotValue(values map[string]any, key string) string {
	value, ok := values[key].(string)
	if !ok {
		return ""
	}
	return value
}

func int64SnapshotValue(values map[string]any, key string) (int64, bool) {
	switch value := values[key].(type) {
	case float64:
		return int64(value), true
	case int64:
		return value, true
	case int:
		return int64(value), true
	default:
		return 0, false
	}
}