package organization

import (
	"context"
	"crypto/sha256"
	"encoding/hex"
	"fmt"
	"sort"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/apr/pricing"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

const organizationLLMStatsBucketGranularity = 5 * time.Minute

type organizationLLMStatsWindow struct {
	key      string
	duration time.Duration
	interval time.Duration
}

type organizationLLMErrorTypeInfo struct {
	Type        string
	Label       string
	Color       string
	Description string
}

type organizationLLMSummaryRecord struct {
	SubjectType          string
	SubjectID            string
	SubjectName          string
	Source               string
	Count1h              uint64
	Count6h              uint64
	Count1d              uint64
	Count3d              uint64
	Count1w              uint64
	InputTokens          uint64
	CachedInputTokens    uint64
	OutputTokens         uint64
	DurationMS           uint64
	CurrentDailySpentUSD float64
	DailyQuotaUSD        float64
}

type organizationLLMDetailRecord struct {
	SubjectType       string
	SubjectID         string
	SubjectName       string
	Source            string
	ProviderID        string
	ProviderName      string
	Model             string
	Count1h           uint64
	Count6h           uint64
	Count1d           uint64
	Count3d           uint64
	Count1w           uint64
	InputTokens       uint64
	CachedInputTokens uint64
	OutputTokens      uint64
	DurationMS        uint64
}

type organizationLLMErrorAggregate struct {
	LastErrorTime   int64
	Total1h         uint64
	Total6h         uint64
	Total12h        uint64
	Total24h        uint64
	Total1w         uint64
	RequestCount1h  uint64
	RequestCount6h  uint64
	RequestCount12h uint64
	RequestCount24h uint64
	RequestCount1w  uint64
}

var organizationLLMDefaultCosts = pricing.DefaultCosts()

func (txs TxStore) CheckOrganizationLLMQuota(ctx context.Context, access OrganizationProviderProxyAccess, model string) (OrganizationLLMQuotaDecision, error) {
	subjectType, subjectID, ok := organizationLLMSubjectFromAccess(access)
	if !ok {
		return OrganizationLLMQuotaDecision{Allowed: true}, nil
	}
	organizationID := strings.TrimSpace(access.OrganizationID)
	if organizationID == "" {
		return OrganizationLLMQuotaDecision{Allowed: true}, nil
	}

	policy, found, err := txs.loadOrganizationLLMQuotaPolicy(ctx, organizationID, subjectType, subjectID)
	if err != nil {
		return OrganizationLLMQuotaDecision{}, err
	}
	if !found || strings.TrimSpace(policy.Status) != organizationLLMQuotaPolicyStatusActive || policy.DailyQuotaUSD <= 0 {
		return OrganizationLLMQuotaDecision{Allowed: true, SubjectType: subjectType, SubjectID: subjectID}, nil
	}

	if !organizationLLMModelHasPrice(model) {
		return OrganizationLLMQuotaDecision{
			Allowed:       true,
			SubjectType:   subjectType,
			SubjectID:     subjectID,
			DailyQuotaUSD: policy.DailyQuotaUSD,
		}, nil
	}

	spent, err := txs.loadOrganizationLLMDailySpentUSD(ctx, organizationID, subjectType, subjectID, time.Now().UTC())
	if err != nil {
		return OrganizationLLMQuotaDecision{}, err
	}
	decision := OrganizationLLMQuotaDecision{
		Allowed:         spent < policy.DailyQuotaUSD,
		SubjectType:     subjectType,
		SubjectID:       subjectID,
		DailyQuotaUSD:   policy.DailyQuotaUSD,
		CurrentSpentUSD: spent,
		RemainingUSD:    policy.DailyQuotaUSD - spent,
	}
	if decision.RemainingUSD < 0 {
		decision.RemainingUSD = 0
	}
	return decision, nil
}

func (txs TxStore) RecordOrganizationLLMUsage(ctx context.Context, record OrganizationLLMUsageRecord) error {
	if txs.db == nil {
		return nil
	}

	organizationID := strings.TrimSpace(record.Authorization.OrganizationID)
	if organizationID == "" {
		return nil
	}
	subjectType, subjectID, hasSubject := organizationLLMSubjectFromAccess(record.Authorization)
	providerID := strings.TrimSpace(record.ProviderID)
	model := strings.TrimSpace(record.Model)
	if providerID == "" {
		providerID = strings.TrimSpace(record.Authorization.OrganizationProviderID)
	}
	if providerID == "" {
		providerID = "unknown"
	}
	if model == "" {
		model = "unknown"
	}
	requestCount := record.RequestCount
	if requestCount == 0 {
		requestCount = 1
	}
	occurredAt := record.OccurredAt.UTC()
	if occurredAt.IsZero() {
		occurredAt = time.Now().UTC()
	}
	bucketStart := organizationLLMTruncateBucket(occurredAt)
	usageBucketKey := organizationLLMUsageBucketKey(organizationID, record.Authorization.WorkspaceID, record.Authorization.KeyID, record.Authorization.Source, providerID, model, bucketStart)
	durationMS := uint64(0)
	if record.Duration > 0 {
		durationMS = uint64(record.Duration / time.Millisecond)
	}
	queueWaitMS := uint64(0)
	if record.QueueWait > 0 {
		queueWaitMS = uint64(record.QueueWait / time.Millisecond)
	}

	tx := txs.tx

	if _, err := tx.Exec(ctx, `
INSERT INTO organization_llm_usage_buckets (
    bucket_key,
    organization_id,
    workspace_id,
    proxy_key_id,
    source,
    organization_provider_id,
    model,
    bucket_start,
    request_count,
    input_tokens,
    cached_input_tokens,
    output_tokens,
    duration_ms,
    queue_wait_ms,
    created_at,
    updated_at
) VALUES (
    $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16
)
ON DUPLICATE KEY UPDATE
    request_count = request_count + VALUES(request_count),
    input_tokens = input_tokens + VALUES(input_tokens),
    cached_input_tokens = cached_input_tokens + VALUES(cached_input_tokens),
    output_tokens = output_tokens + VALUES(output_tokens),
    duration_ms = duration_ms + VALUES(duration_ms),
    queue_wait_ms = queue_wait_ms + VALUES(queue_wait_ms),
    updated_at = VALUES(updated_at)
`, usageBucketKey, organizationID, nullableID(record.Authorization.WorkspaceID), nullableID(record.Authorization.KeyID), strings.TrimSpace(record.Authorization.Source), providerID, model, bucketStart, requestCount, record.InputTokens, record.CachedInputTokens, record.OutputTokens, durationMS, queueWaitMS, bucketStart, occurredAt); err != nil {
		return fmt.Errorf("upsert llm usage bucket: %w", err)
	}

	if trimmedErrorType := normalizeOrganizationLLMErrorType(record.ErrorType); trimmedErrorType != "" {
		if _, err := tx.Exec(ctx, `
INSERT INTO organization_llm_error_buckets (
    bucket_key,
    organization_id,
    workspace_id,
    proxy_key_id,
    source,
    organization_provider_id,
    model,
    error_type,
    bucket_start,
    error_count,
    created_at,
    updated_at
) VALUES (
    $1, $2, $3, $4, $5, $6, $7, $8, $9, 1, $10, $11
)
ON DUPLICATE KEY UPDATE
    error_count = error_count + 1,
    updated_at = VALUES(updated_at)
`, organizationLLMErrorBucketKey(organizationID, record.Authorization.WorkspaceID, record.Authorization.KeyID, record.Authorization.Source, providerID, model, trimmedErrorType, bucketStart), organizationID, nullableID(record.Authorization.WorkspaceID), nullableID(record.Authorization.KeyID), strings.TrimSpace(record.Authorization.Source), providerID, model, trimmedErrorType, bucketStart, bucketStart, occurredAt); err != nil {
			return fmt.Errorf("upsert llm error bucket: %w", err)
		}
	}

	if hasSubject {
		spentUSD := organizationLLMCalculateCostUSD(model, record.InputTokens, record.OutputTokens, record.CachedInputTokens)
		if _, err := tx.Exec(ctx, `
INSERT INTO organization_llm_daily_usage (
    organization_id,
    subject_type,
    subject_id,
    usage_date,
    spent_usd,
    request_count,
    input_tokens,
    cached_input_tokens,
    output_tokens,
    created_at,
    updated_at
) VALUES (
    $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11
)
ON DUPLICATE KEY UPDATE
    spent_usd = spent_usd + VALUES(spent_usd),
    request_count = request_count + VALUES(request_count),
    input_tokens = input_tokens + VALUES(input_tokens),
    cached_input_tokens = cached_input_tokens + VALUES(cached_input_tokens),
    output_tokens = output_tokens + VALUES(output_tokens),
    updated_at = VALUES(updated_at)
`, organizationID, subjectType, subjectID, occurredAt.Format("2006-01-02"), spentUSD, requestCount, record.InputTokens, record.CachedInputTokens, record.OutputTokens, bucketStart, occurredAt); err != nil {
			return fmt.Errorf("upsert llm daily usage: %w", err)
		}
	}

	return nil
}

func (txs TxStore) ListOrganizationLLMStatsSummaryForUser(ctx context.Context, userID string, organizationID string) ([]OrganizationLLMStatsSummaryView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return nil, err
	}

	now := time.Now().UTC()
	weekCutoff := now.Add(-7 * 24 * time.Hour)
	rows, err := txs.db.Query(ctx, `
SELECT
    subject_type,
    subject_id,
    subject_name,
    source,
    SUM(CASE WHEN bucket_start >= $2 THEN request_count ELSE 0 END) AS count_1h,
    SUM(CASE WHEN bucket_start >= $3 THEN request_count ELSE 0 END) AS count_6h,
    SUM(CASE WHEN bucket_start >= $4 THEN request_count ELSE 0 END) AS count_1d,
    SUM(CASE WHEN bucket_start >= $5 THEN request_count ELSE 0 END) AS count_3d,
    SUM(request_count) AS count_1w,
    SUM(input_tokens) AS input_tokens,
    SUM(cached_input_tokens) AS cached_input_tokens,
    SUM(output_tokens) AS output_tokens,
    SUM(duration_ms) AS duration_ms,
    MAX(current_daily_spent_usd) AS current_daily_spent_usd,
    MAX(daily_quota_usd) AS daily_quota_usd
FROM (
    SELECT
        CASE
            WHEN ub.proxy_key_id IS NOT NULL AND ub.proxy_key_id <> '' THEN 'proxy_key'
            ELSE 'workspace'
        END AS subject_type,
        CASE
            WHEN ub.proxy_key_id IS NOT NULL AND ub.proxy_key_id <> '' THEN ub.proxy_key_id
            ELSE ub.workspace_id
        END AS subject_id,
        CASE
            WHEN ub.proxy_key_id IS NOT NULL AND ub.proxy_key_id <> '' THEN COALESCE(NULLIF(ok.label, ''), NULLIF(ok.token_hint, ''), ub.proxy_key_id)
            ELSE COALESCE(NULLIF(w.name, ''), ub.workspace_id)
        END AS subject_name,
        ub.source,
        ub.bucket_start,
        ub.request_count,
        ub.input_tokens,
        ub.cached_input_tokens,
        ub.output_tokens,
        ub.duration_ms,
        COALESCE(du.spent_usd, 0) AS current_daily_spent_usd,
        COALESCE(qp.daily_quota_usd, 0) AS daily_quota_usd
    FROM organization_llm_usage_buckets ub
    LEFT JOIN organization_llm_proxy_keys ok
      ON ok.organization_id = ub.organization_id
     AND ok.id = ub.proxy_key_id
    LEFT JOIN workspaces w
      ON w.id = ub.workspace_id
    LEFT JOIN organization_llm_daily_usage du
      ON du.organization_id = ub.organization_id
     AND du.subject_type = CASE
         WHEN ub.proxy_key_id IS NOT NULL AND ub.proxy_key_id <> '' THEN 'proxy_key'
         ELSE 'workspace'
     END
     AND du.subject_id = CASE
         WHEN ub.proxy_key_id IS NOT NULL AND ub.proxy_key_id <> '' THEN ub.proxy_key_id
         ELSE ub.workspace_id
     END
     AND du.usage_date = $6
    LEFT JOIN organization_llm_quota_policies qp
      ON qp.organization_id = ub.organization_id
     AND qp.subject_type = CASE
         WHEN ub.proxy_key_id IS NOT NULL AND ub.proxy_key_id <> '' THEN 'proxy_key'
         ELSE 'workspace'
     END
     AND qp.subject_id = CASE
         WHEN ub.proxy_key_id IS NOT NULL AND ub.proxy_key_id <> '' THEN ub.proxy_key_id
         ELSE ub.workspace_id
     END
     AND qp.status = 'active'
    WHERE ub.organization_id = $1
      AND ub.bucket_start >= $7
) summary
GROUP BY subject_type, subject_id, subject_name, source
ORDER BY count_1h DESC, count_6h DESC, count_1d DESC, subject_name ASC, subject_id ASC
`, organizationID, now.Add(-1*time.Hour), now.Add(-6*time.Hour), now.Add(-24*time.Hour), now.Add(-72*time.Hour), now.Format("2006-01-02"), weekCutoff)
	if err != nil {
		return nil, fmt.Errorf("list organization llm stats summary: %w", err)
	}
	defer rows.Close()

	items := make([]OrganizationLLMStatsSummaryView, 0)
	for rows.Next() {
		var item organizationLLMSummaryRecord
		if err := rows.Scan(
			&item.SubjectType,
			&item.SubjectID,
			&item.SubjectName,
			&item.Source,
			&item.Count1h,
			&item.Count6h,
			&item.Count1d,
			&item.Count3d,
			&item.Count1w,
			&item.InputTokens,
			&item.CachedInputTokens,
			&item.OutputTokens,
			&item.DurationMS,
			&item.CurrentDailySpentUSD,
			&item.DailyQuotaUSD,
		); err != nil {
			return nil, fmt.Errorf("scan organization llm stats summary: %w", err)
		}
		items = append(items, OrganizationLLMStatsSummaryView{
			SubjectType:          strings.TrimSpace(item.SubjectType),
			SubjectID:            strings.TrimSpace(item.SubjectID),
			SubjectName:          strings.TrimSpace(item.SubjectName),
			Source:               strings.TrimSpace(item.Source),
			Count1h:              item.Count1h,
			Count6h:              item.Count6h,
			Count1d:              item.Count1d,
			Count3d:              item.Count3d,
			Count1w:              item.Count1w,
			InputTokens:          item.InputTokens,
			CachedInputTokens:    item.CachedInputTokens,
			OutputTokens:         item.OutputTokens,
			TokenSpeed:           organizationLLMTokenSpeed(item.OutputTokens, item.DurationMS),
			CurrentDailySpentUSD: item.CurrentDailySpentUSD,
			DailyQuotaUSD:        item.DailyQuotaUSD,
		})
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization llm stats summary: %w", err)
	}
	return items, nil
}

func (txs TxStore) ListOrganizationLLMStatsDetailForUser(ctx context.Context, userID string, organizationID string, query OrganizationLLMStatsQuery) ([]OrganizationLLMStatsDetailView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return nil, err
	}
	if err := validateOrganizationLLMStatsSubjectFilter(query.SubjectType, query.SubjectID); err != nil {
		return nil, err
	}

	now := time.Now().UTC()
	weekCutoff := now.Add(-7 * 24 * time.Hour)
	args := []any{organizationID, weekCutoff, now.Add(-1 * time.Hour), now.Add(-6 * time.Hour), now.Add(-24 * time.Hour), now.Add(-72 * time.Hour)}
	filters, args := organizationLLMStatsUsageFilters(query, args)
	rows, err := txs.db.Query(ctx, `
SELECT
    CASE
        WHEN ub.proxy_key_id IS NOT NULL AND ub.proxy_key_id <> '' THEN 'proxy_key'
        ELSE 'workspace'
    END AS subject_type,
    CASE
        WHEN ub.proxy_key_id IS NOT NULL AND ub.proxy_key_id <> '' THEN ub.proxy_key_id
        ELSE ub.workspace_id
    END AS subject_id,
    CASE
        WHEN ub.proxy_key_id IS NOT NULL AND ub.proxy_key_id <> '' THEN COALESCE(NULLIF(ok.label, ''), NULLIF(ok.token_hint, ''), ub.proxy_key_id)
        ELSE COALESCE(NULLIF(w.name, ''), ub.workspace_id)
    END AS subject_name,
    ub.source,
    ub.organization_provider_id,
    COALESCE(NULLIF(op.name, ''), ub.organization_provider_id) AS provider_name,
    ub.model,
    SUM(CASE WHEN ub.bucket_start >= $3 THEN ub.request_count ELSE 0 END) AS count_1h,
    SUM(CASE WHEN ub.bucket_start >= $4 THEN ub.request_count ELSE 0 END) AS count_6h,
    SUM(CASE WHEN ub.bucket_start >= $5 THEN ub.request_count ELSE 0 END) AS count_1d,
    SUM(CASE WHEN ub.bucket_start >= $6 THEN ub.request_count ELSE 0 END) AS count_3d,
    SUM(ub.request_count) AS count_1w,
    SUM(ub.input_tokens) AS input_tokens,
    SUM(ub.cached_input_tokens) AS cached_input_tokens,
    SUM(ub.output_tokens) AS output_tokens,
    SUM(ub.duration_ms) AS duration_ms
FROM organization_llm_usage_buckets ub
LEFT JOIN organization_llm_proxy_keys ok
  ON ok.organization_id = ub.organization_id
 AND ok.id = ub.proxy_key_id
LEFT JOIN workspaces w
  ON w.id = ub.workspace_id
LEFT JOIN organization_providers op
  ON op.organization_id = ub.organization_id
 AND op.id = ub.organization_provider_id
WHERE ub.organization_id = $1
  AND ub.bucket_start >= $2`+filters+`
GROUP BY subject_type, subject_id, subject_name, ub.source, ub.organization_provider_id, provider_name, ub.model
ORDER BY subject_name ASC, subject_id ASC, provider_name ASC, ub.model ASC
`, args...)
	if err != nil {
		return nil, fmt.Errorf("list organization llm stats detail: %w", err)
	}
	defer rows.Close()

	grouped := make(map[string]*OrganizationLLMStatsDetailView)
	order := make([]string, 0)
	for rows.Next() {
		var row organizationLLMDetailRecord
		if err := rows.Scan(
			&row.SubjectType,
			&row.SubjectID,
			&row.SubjectName,
			&row.Source,
			&row.ProviderID,
			&row.ProviderName,
			&row.Model,
			&row.Count1h,
			&row.Count6h,
			&row.Count1d,
			&row.Count3d,
			&row.Count1w,
			&row.InputTokens,
			&row.CachedInputTokens,
			&row.OutputTokens,
			&row.DurationMS,
		); err != nil {
			return nil, fmt.Errorf("scan organization llm stats detail: %w", err)
		}
		groupKey := row.SubjectType + ":" + row.SubjectID
		if _, ok := grouped[groupKey]; !ok {
			grouped[groupKey] = &OrganizationLLMStatsDetailView{
				SubjectType: strings.TrimSpace(row.SubjectType),
				SubjectID:   strings.TrimSpace(row.SubjectID),
				SubjectName: strings.TrimSpace(row.SubjectName),
				Source:      strings.TrimSpace(row.Source),
				Models:      []OrganizationLLMModelStatView{},
			}
			order = append(order, groupKey)
		}
		grouped[groupKey].Models = append(grouped[groupKey].Models, OrganizationLLMModelStatView{
			Model:             strings.TrimSpace(row.Model),
			ModelName:         organizationLLMModelName(row.Model),
			ProviderID:        strings.TrimSpace(row.ProviderID),
			Provider:          strings.TrimSpace(row.ProviderName),
			Count1h:           row.Count1h,
			Count6h:           row.Count6h,
			Count1d:           row.Count1d,
			Count3d:           row.Count3d,
			Count1w:           row.Count1w,
			InputTokens:       row.InputTokens,
			CachedInputTokens: row.CachedInputTokens,
			OutputTokens:      row.OutputTokens,
			TokenSpeed:        organizationLLMTokenSpeed(row.OutputTokens, row.DurationMS),
		})
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization llm stats detail: %w", err)
	}

	items := make([]OrganizationLLMStatsDetailView, 0, len(order))
	for _, key := range order {
		items = append(items, *grouped[key])
	}
	return items, nil
}

func (txs TxStore) GetOrganizationLLMStatsTimeSeriesForUser(ctx context.Context, userID string, organizationID string, query OrganizationLLMStatsQuery) (OrganizationLLMTimeSeriesView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationLLMTimeSeriesView{}, err
	}
	window, err := parseOrganizationLLMStatsWindow(query.Window, "12h")
	if err != nil {
		return OrganizationLLMTimeSeriesView{}, err
	}
	if err := validateOrganizationLLMStatsSubjectFilter(query.SubjectType, query.SubjectID); err != nil {
		return OrganizationLLMTimeSeriesView{}, err
	}

	now := time.Now().UTC()
	cutoff := now.Add(-window.duration)
	args := []any{organizationID, cutoff, int64(window.interval / time.Second), int64(window.interval / time.Second)}
	filters, args := organizationLLMStatsUsageFilters(query, args)
	rows, err := txs.db.Query(ctx, `
SELECT
    FLOOR(UNIX_TIMESTAMP(ub.bucket_start) / $3) * $4 AS bucket_unix,
    SUM(ub.request_count) AS request_count,
    SUM(ub.input_tokens) AS input_tokens,
    SUM(ub.cached_input_tokens) AS cached_input_tokens,
    SUM(ub.output_tokens) AS output_tokens
FROM organization_llm_usage_buckets ub
WHERE ub.organization_id = $1
  AND ub.bucket_start >= $2`+filters+`
GROUP BY bucket_unix
ORDER BY bucket_unix ASC
`, args...)
	if err != nil {
		return OrganizationLLMTimeSeriesView{}, fmt.Errorf("query organization llm timeseries: %w", err)
	}
	defer rows.Close()

	result := OrganizationLLMTimeSeriesView{
		Interval: int(window.interval / time.Second),
		Data:     []OrganizationLLMTimeSeriesPoint{},
	}
	for rows.Next() {
		var point OrganizationLLMTimeSeriesPoint
		if err := rows.Scan(&point.Timestamp, &point.Count, &point.Input, &point.Cached, &point.Output); err != nil {
			return OrganizationLLMTimeSeriesView{}, fmt.Errorf("scan organization llm timeseries: %w", err)
		}
		result.Data = append(result.Data, point)
	}
	if err := rows.Err(); err != nil {
		return OrganizationLLMTimeSeriesView{}, fmt.Errorf("iterate organization llm timeseries: %w", err)
	}
	return result, nil
}

func (txs TxStore) GetOrganizationLLMErrorSummaryForUser(ctx context.Context, userID string, organizationID string, query OrganizationLLMStatsQuery) (OrganizationLLMErrorSummaryView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationLLMErrorSummaryView{}, err
	}
	if err := validateOrganizationLLMStatsSubjectFilter(query.SubjectType, query.SubjectID); err != nil {
		return OrganizationLLMErrorSummaryView{}, err
	}

	now := time.Now().UTC()
	args := []any{organizationID, now.Add(-1 * time.Hour), now.Add(-6 * time.Hour), now.Add(-12 * time.Hour), now.Add(-24 * time.Hour), now.Add(-7 * 24 * time.Hour)}
	filters, args := organizationLLMStatsErrorFilters(query, args)
	row := txs.db.QueryRow(ctx, `
SELECT
    COALESCE(UNIX_TIMESTAMP(MAX(eb.bucket_start)), 0) AS last_error_time,
    COALESCE(SUM(CASE WHEN eb.bucket_start >= $2 THEN eb.error_count ELSE 0 END), 0) AS total_1h,
    COALESCE(SUM(CASE WHEN eb.bucket_start >= $3 THEN eb.error_count ELSE 0 END), 0) AS total_6h,
    COALESCE(SUM(CASE WHEN eb.bucket_start >= $4 THEN eb.error_count ELSE 0 END), 0) AS total_12h,
    COALESCE(SUM(CASE WHEN eb.bucket_start >= $5 THEN eb.error_count ELSE 0 END), 0) AS total_24h,
    COALESCE(SUM(CASE WHEN eb.bucket_start >= $6 THEN eb.error_count ELSE 0 END), 0) AS total_1w
FROM organization_llm_error_buckets eb
WHERE eb.organization_id = $1`+filters, args...)

	var aggregate organizationLLMErrorAggregate
	if err := row.Scan(&aggregate.LastErrorTime, &aggregate.Total1h, &aggregate.Total6h, &aggregate.Total12h, &aggregate.Total24h, &aggregate.Total1w); err != nil {
		return OrganizationLLMErrorSummaryView{}, fmt.Errorf("scan organization llm error summary: %w", err)
	}

	requestCounts, err := txs.loadOrganizationLLMRequestCountsForErrorSummary(ctx, organizationID, query, now)
	if err != nil {
		return OrganizationLLMErrorSummaryView{}, err
	}
	typeCounts, err := txs.loadOrganizationLLMErrorTypeCounts(ctx, organizationID, query, now)
	if err != nil {
		return OrganizationLLMErrorSummaryView{}, err
	}

	items := make([]OrganizationLLMErrorTypeStatsView, 0, len(organizationLLMErrorTypeCatalog()))
	for _, typeInfo := range organizationLLMErrorTypeCatalog() {
		counts := typeCounts[typeInfo.Type]
		items = append(items, OrganizationLLMErrorTypeStatsView{
			Type:        typeInfo.Type,
			Label:       typeInfo.Label,
			Color:       typeInfo.Color,
			Description: typeInfo.Description,
			Count1h:     counts[0],
			Count6h:     counts[1],
			Count12h:    counts[2],
			Count24h:    counts[3],
			Count1w:     counts[4],
		})
	}

	return OrganizationLLMErrorSummaryView{
		LastErrorTime:   aggregate.LastErrorTime,
		Total1h:         aggregate.Total1h,
		Total6h:         aggregate.Total6h,
		Total12h:        aggregate.Total12h,
		Total24h:        aggregate.Total24h,
		Total1w:         aggregate.Total1w,
		RequestCount1h:  requestCounts[0],
		RequestCount6h:  requestCounts[1],
		RequestCount12h: requestCounts[2],
		RequestCount24h: requestCounts[3],
		RequestCount1w:  requestCounts[4],
		ErrorTypes:      items,
	}, nil
}

func (txs TxStore) GetOrganizationLLMErrorTimeSeriesForUser(ctx context.Context, userID string, organizationID string, query OrganizationLLMStatsQuery) (OrganizationLLMErrorTimeSeriesView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationLLMErrorTimeSeriesView{}, err
	}
	window, err := parseOrganizationLLMStatsWindow(query.Window, "1d")
	if err != nil {
		return OrganizationLLMErrorTimeSeriesView{}, err
	}
	if err := validateOrganizationLLMStatsSubjectFilter(query.SubjectType, query.SubjectID); err != nil {
		return OrganizationLLMErrorTimeSeriesView{}, err
	}

	now := time.Now().UTC()
	cutoff := now.Add(-window.duration)
	args := []any{organizationID, cutoff, int64(window.interval / time.Second), int64(window.interval / time.Second)}
	filters, args := organizationLLMStatsErrorFilters(query, args)
	rows, err := txs.db.Query(ctx, `
SELECT
    FLOOR(UNIX_TIMESTAMP(eb.bucket_start) / $3) * $4 AS bucket_unix,
    eb.error_type,
    SUM(eb.error_count) AS error_count
FROM organization_llm_error_buckets eb
WHERE eb.organization_id = $1
  AND eb.bucket_start >= $2`+filters+`
GROUP BY bucket_unix, eb.error_type
ORDER BY bucket_unix ASC, eb.error_type ASC
`, args...)
	if err != nil {
		return OrganizationLLMErrorTimeSeriesView{}, fmt.Errorf("query organization llm error timeseries: %w", err)
	}
	defer rows.Close()

	type bucketAccumulator struct {
		Errors map[string]uint64
		Total  uint64
	}
	accumulators := make(map[int64]*bucketAccumulator)
	order := make([]int64, 0)
	for rows.Next() {
		var bucketUnix int64
		var errorType string
		var count uint64
		if err := rows.Scan(&bucketUnix, &errorType, &count); err != nil {
			return OrganizationLLMErrorTimeSeriesView{}, fmt.Errorf("scan organization llm error timeseries: %w", err)
		}
		if _, ok := accumulators[bucketUnix]; !ok {
			accumulators[bucketUnix] = &bucketAccumulator{Errors: make(map[string]uint64)}
			order = append(order, bucketUnix)
		}
		accumulators[bucketUnix].Errors[strings.TrimSpace(errorType)] += count
		accumulators[bucketUnix].Total += count
	}
	if err := rows.Err(); err != nil {
		return OrganizationLLMErrorTimeSeriesView{}, fmt.Errorf("iterate organization llm error timeseries: %w", err)
	}
	sort.Slice(order, func(i, j int) bool { return order[i] < order[j] })
	result := OrganizationLLMErrorTimeSeriesView{
		Interval: int(window.interval / time.Second),
		Data:     make([]OrganizationLLMErrorTimeSeriesPoint, 0, len(order)),
	}
	for _, bucketUnix := range order {
		accumulator := accumulators[bucketUnix]
		result.Data = append(result.Data, OrganizationLLMErrorTimeSeriesPoint{
			Timestamp: bucketUnix,
			Errors:    accumulator.Errors,
			Total:     accumulator.Total,
		})
	}
	return result, nil
}

func (txs TxStore) ListOrganizationLLMQuotaPoliciesForUser(ctx context.Context, userID string, organizationID string) ([]OrganizationLLMQuotaPolicyView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return nil, err
	}
	rows, err := txs.db.Query(ctx, `
SELECT
    qp.subject_type,
    qp.subject_id,
    CASE
        WHEN qp.subject_type = 'proxy_key' THEN COALESCE(NULLIF(ok.label, ''), NULLIF(ok.token_hint, ''), qp.subject_id)
        ELSE COALESCE(NULLIF(w.name, ''), qp.subject_id)
    END AS subject_name,
    qp.status,
    qp.daily_quota_usd,
    COALESCE(du.spent_usd, 0) AS current_spent_usd,
    COALESCE(du.request_count, 0) AS request_count,
    COALESCE(du.input_tokens, 0) AS input_tokens,
    COALESCE(du.cached_input_tokens, 0) AS cached_input_tokens,
    COALESCE(du.output_tokens, 0) AS output_tokens,
    qp.updated_at
FROM organization_llm_quota_policies qp
LEFT JOIN organization_llm_proxy_keys ok
  ON ok.organization_id = qp.organization_id
 AND ok.id = qp.subject_id
 AND qp.subject_type = 'proxy_key'
LEFT JOIN workspaces w
  ON w.id = qp.subject_id
 AND qp.subject_type = 'workspace'
LEFT JOIN organization_llm_daily_usage du
  ON du.organization_id = qp.organization_id
 AND du.subject_type = qp.subject_type
 AND du.subject_id = qp.subject_id
 AND du.usage_date = $2
WHERE qp.organization_id = $1
ORDER BY qp.subject_type ASC, subject_name ASC, qp.subject_id ASC
`, organizationID, time.Now().UTC().Format("2006-01-02"))
	if err != nil {
		return nil, fmt.Errorf("list organization llm quota policies: %w", err)
	}
	defer rows.Close()

	items := make([]OrganizationLLMQuotaPolicyView, 0)
	for rows.Next() {
		var item OrganizationLLMQuotaPolicyView
		if err := rows.Scan(
			&item.SubjectType,
			&item.SubjectID,
			&item.SubjectName,
			&item.Status,
			&item.DailyQuotaUSD,
			&item.CurrentSpentUSD,
			&item.RequestCount,
			&item.InputTokens,
			&item.CachedInputTokens,
			&item.OutputTokens,
			&item.UpdatedAt,
		); err != nil {
			return nil, fmt.Errorf("scan organization llm quota policy: %w", err)
		}
		item.Source = organizationLLMSourceForSubjectType(item.SubjectType)
		item.RemainingUSD = item.DailyQuotaUSD - item.CurrentSpentUSD
		if item.RemainingUSD < 0 {
			item.RemainingUSD = 0
		}
		items = append(items, item)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization llm quota policies: %w", err)
	}
	return items, nil
}

func (txs TxStore) UpsertOrganizationLLMQuotaPolicyForUser(ctx context.Context, userID string, organizationID string, params UpsertOrganizationLLMQuotaPolicyParams) (OrganizationLLMQuotaPolicyView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationLLMQuotaPolicyView{}, err
	}
	subjectType := normalizeOrganizationLLMSubjectType(params.SubjectType)
	subjectID := strings.TrimSpace(params.SubjectID)
	if subjectType == "" {
		return OrganizationLLMQuotaPolicyView{}, badRequest("subjectType is required")
	}
	if subjectID == "" {
		return OrganizationLLMQuotaPolicyView{}, badRequest("subjectId is required")
	}
	if params.DailyQuotaUSD <= 0 {
		return OrganizationLLMQuotaPolicyView{}, badRequest("dailyQuotaUsd must be positive")
	}
	if err := txs.ensureOrganizationLLMQuotaSubject(ctx, organizationID, subjectType, subjectID); err != nil {
		return OrganizationLLMQuotaPolicyView{}, err
	}

	now := time.Now().UTC()
	if _, err := txs.db.Exec(ctx, `
INSERT INTO organization_llm_quota_policies (
    organization_id,
    subject_type,
    subject_id,
    status,
    daily_quota_usd,
    created_at,
    updated_at
) VALUES (
    $1, $2, $3, $4, $5, $6, $6
)
ON DUPLICATE KEY UPDATE
    status = VALUES(status),
    daily_quota_usd = VALUES(daily_quota_usd),
    updated_at = VALUES(updated_at)
`, organizationID, subjectType, subjectID, organizationLLMQuotaPolicyStatusActive, params.DailyQuotaUSD, now); err != nil {
		return OrganizationLLMQuotaPolicyView{}, fmt.Errorf("upsert organization llm quota policy: %w", err)
	}

	return txs.loadOrganizationLLMQuotaPolicyViewForUser(ctx, userID, organizationID, subjectType, subjectID)
}

func (txs TxStore) DeleteOrganizationLLMQuotaPolicyForUser(ctx context.Context, userID string, organizationID string, subjectType string, subjectID string) error {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return err
	}
	normalizedType := normalizeOrganizationLLMSubjectType(subjectType)
	trimmedID := strings.TrimSpace(subjectID)
	if normalizedType == "" || trimmedID == "" {
		return badRequest("invalid llm quota subject")
	}
	result, err := txs.db.Exec(ctx, `
DELETE FROM organization_llm_quota_policies
WHERE organization_id = $1
  AND subject_type = $2
  AND subject_id = $3
`, organizationID, normalizedType, trimmedID)
	if err != nil {
		return fmt.Errorf("delete organization llm quota policy: %w", err)
	}
	if result.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

func (txs TxStore) loadOrganizationLLMQuotaPolicy(ctx context.Context, organizationID string, subjectType string, subjectID string) (OrganizationLLMQuotaPolicyView, bool, error) {
	row := txs.db.QueryRow(ctx, `
SELECT status, daily_quota_usd, updated_at
FROM organization_llm_quota_policies
WHERE organization_id = $1
  AND subject_type = $2
  AND subject_id = $3
`, organizationID, subjectType, subjectID)
	var item OrganizationLLMQuotaPolicyView
	item.SubjectType = subjectType
	item.SubjectID = subjectID
	if err := row.Scan(&item.Status, &item.DailyQuotaUSD, &item.UpdatedAt); err != nil {
		if coredb.IsNotFound(err) {
			return OrganizationLLMQuotaPolicyView{}, false, nil
		}
		return OrganizationLLMQuotaPolicyView{}, false, fmt.Errorf("load organization llm quota policy: %w", err)
	}
	return item, true, nil
}

func (txs TxStore) loadOrganizationLLMDailySpentUSD(ctx context.Context, organizationID string, subjectType string, subjectID string, now time.Time) (float64, error) {
	row := txs.db.QueryRow(ctx, `
SELECT spent_usd
FROM organization_llm_daily_usage
WHERE organization_id = $1
  AND subject_type = $2
  AND subject_id = $3
  AND usage_date = $4
`, organizationID, subjectType, subjectID, now.UTC().Format("2006-01-02"))
	var spent float64
	if err := row.Scan(&spent); err != nil {
		if coredb.IsNotFound(err) {
			return 0, nil
		}
		return 0, fmt.Errorf("load organization llm daily spent: %w", err)
	}
	return spent, nil
}

func (txs TxStore) loadOrganizationLLMQuotaPolicyViewForUser(ctx context.Context, userID string, organizationID string, subjectType string, subjectID string) (OrganizationLLMQuotaPolicyView, error) {
	items, err := txs.ListOrganizationLLMQuotaPoliciesForUser(ctx, userID, organizationID)
	if err != nil {
		return OrganizationLLMQuotaPolicyView{}, err
	}
	for _, item := range items {
		if item.SubjectType == subjectType && item.SubjectID == subjectID {
			return item, nil
		}
	}
	return OrganizationLLMQuotaPolicyView{}, ErrNotFound
}

func (txs TxStore) ensureOrganizationLLMQuotaSubject(ctx context.Context, organizationID string, subjectType string, subjectID string) error {
	switch normalizeOrganizationLLMSubjectType(subjectType) {
	case OrganizationLLMSubjectTypeProxyKey:
		if _, err := txs.loadOrganizationLLMProxyKey(ctx, txs.db, organizationID, subjectID); err != nil {
			return err
		}
		return nil
	case OrganizationLLMSubjectTypeWorkspace:
		return txs.requireOrganizationWorkspaceBinding(ctx, txs.db, organizationID, subjectID)
	default:
		return badRequest("unsupported llm quota subject type")
	}
}

func (txs TxStore) loadOrganizationLLMRequestCountsForErrorSummary(ctx context.Context, organizationID string, query OrganizationLLMStatsQuery, now time.Time) ([5]uint64, error) {
	args := []any{organizationID, now.Add(-1 * time.Hour), now.Add(-6 * time.Hour), now.Add(-12 * time.Hour), now.Add(-24 * time.Hour), now.Add(-7 * 24 * time.Hour)}
	filters, args := organizationLLMStatsUsageFilters(query, args)
	row := txs.db.QueryRow(ctx, `
SELECT
    COALESCE(SUM(CASE WHEN ub.bucket_start >= $2 THEN ub.request_count ELSE 0 END), 0) AS count_1h,
    COALESCE(SUM(CASE WHEN ub.bucket_start >= $3 THEN ub.request_count ELSE 0 END), 0) AS count_6h,
    COALESCE(SUM(CASE WHEN ub.bucket_start >= $4 THEN ub.request_count ELSE 0 END), 0) AS count_12h,
    COALESCE(SUM(CASE WHEN ub.bucket_start >= $5 THEN ub.request_count ELSE 0 END), 0) AS count_24h,
    COALESCE(SUM(CASE WHEN ub.bucket_start >= $6 THEN ub.request_count ELSE 0 END), 0) AS count_1w
FROM organization_llm_usage_buckets ub
WHERE ub.organization_id = $1`+filters, args...)
	var counts [5]uint64
	if err := row.Scan(&counts[0], &counts[1], &counts[2], &counts[3], &counts[4]); err != nil {
		return counts, fmt.Errorf("scan organization llm request counts: %w", err)
	}
	return counts, nil
}

func (txs TxStore) loadOrganizationLLMErrorTypeCounts(ctx context.Context, organizationID string, query OrganizationLLMStatsQuery, now time.Time) (map[string][5]uint64, error) {
	args := []any{organizationID, now.Add(-1 * time.Hour), now.Add(-6 * time.Hour), now.Add(-12 * time.Hour), now.Add(-24 * time.Hour), now.Add(-7 * 24 * time.Hour)}
	filters, args := organizationLLMStatsErrorFilters(query, args)
	rows, err := txs.db.Query(ctx, `
SELECT
    eb.error_type,
    COALESCE(SUM(CASE WHEN eb.bucket_start >= $2 THEN eb.error_count ELSE 0 END), 0) AS count_1h,
    COALESCE(SUM(CASE WHEN eb.bucket_start >= $3 THEN eb.error_count ELSE 0 END), 0) AS count_6h,
    COALESCE(SUM(CASE WHEN eb.bucket_start >= $4 THEN eb.error_count ELSE 0 END), 0) AS count_12h,
    COALESCE(SUM(CASE WHEN eb.bucket_start >= $5 THEN eb.error_count ELSE 0 END), 0) AS count_24h,
    COALESCE(SUM(CASE WHEN eb.bucket_start >= $6 THEN eb.error_count ELSE 0 END), 0) AS count_1w
FROM organization_llm_error_buckets eb
WHERE eb.organization_id = $1`+filters+`
GROUP BY eb.error_type
`, args...)
	if err != nil {
		return nil, fmt.Errorf("query organization llm error type counts: %w", err)
	}
	defer rows.Close()

	counts := make(map[string][5]uint64)
	for rows.Next() {
		var errorType string
		var bucketCounts [5]uint64
		if err := rows.Scan(&errorType, &bucketCounts[0], &bucketCounts[1], &bucketCounts[2], &bucketCounts[3], &bucketCounts[4]); err != nil {
			return nil, fmt.Errorf("scan organization llm error type counts: %w", err)
		}
		counts[strings.TrimSpace(errorType)] = bucketCounts
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization llm error type counts: %w", err)
	}
	return counts, nil
}

func organizationLLMStatsUsageFilters(query OrganizationLLMStatsQuery, args []any) (string, []any) {
	var filters strings.Builder
	subjectType := normalizeOrganizationLLMSubjectType(query.SubjectType)
	if subjectType != "" && strings.TrimSpace(query.SubjectID) != "" {
		args = append(args, subjectType, strings.TrimSpace(query.SubjectID))
		fmt.Fprintf(&filters, `
  AND CASE
        WHEN ub.proxy_key_id IS NOT NULL AND ub.proxy_key_id <> '' THEN 'proxy_key'
        ELSE 'workspace'
      END = $%d
  AND CASE
        WHEN ub.proxy_key_id IS NOT NULL AND ub.proxy_key_id <> '' THEN ub.proxy_key_id
        ELSE ub.workspace_id
      END = $%d`, len(args)-1, len(args))
	}
	if trimmedProviderID := strings.TrimSpace(query.ProviderID); trimmedProviderID != "" {
		args = append(args, trimmedProviderID)
		fmt.Fprintf(&filters, `
  AND ub.organization_provider_id = $%d`, len(args))
	}
	if trimmedModel := strings.TrimSpace(query.Model); trimmedModel != "" {
		args = append(args, trimmedModel)
		fmt.Fprintf(&filters, `
  AND ub.model = $%d`, len(args))
	}
	return filters.String(), args
}

func organizationLLMStatsErrorFilters(query OrganizationLLMStatsQuery, args []any) (string, []any) {
	var filters strings.Builder
	subjectType := normalizeOrganizationLLMSubjectType(query.SubjectType)
	if subjectType != "" && strings.TrimSpace(query.SubjectID) != "" {
		args = append(args, subjectType, strings.TrimSpace(query.SubjectID))
		fmt.Fprintf(&filters, `
  AND CASE
        WHEN eb.proxy_key_id IS NOT NULL AND eb.proxy_key_id <> '' THEN 'proxy_key'
        ELSE 'workspace'
      END = $%d
  AND CASE
        WHEN eb.proxy_key_id IS NOT NULL AND eb.proxy_key_id <> '' THEN eb.proxy_key_id
        ELSE eb.workspace_id
      END = $%d`, len(args)-1, len(args))
	}
	if trimmedProviderID := strings.TrimSpace(query.ProviderID); trimmedProviderID != "" {
		args = append(args, trimmedProviderID)
		fmt.Fprintf(&filters, `
  AND eb.organization_provider_id = $%d`, len(args))
	}
	if trimmedModel := strings.TrimSpace(query.Model); trimmedModel != "" {
		args = append(args, trimmedModel)
		fmt.Fprintf(&filters, `
  AND eb.model = $%d`, len(args))
	}
	return filters.String(), args
}

func validateOrganizationLLMStatsSubjectFilter(subjectType string, subjectID string) error {
	normalizedType := normalizeOrganizationLLMSubjectType(subjectType)
	trimmedID := strings.TrimSpace(subjectID)
	if normalizedType == "" && trimmedID == "" {
		return nil
	}
	if normalizedType == "" || trimmedID == "" {
		return badRequest("subjectType and subjectId must be provided together")
	}
	return nil
}

func parseOrganizationLLMStatsWindow(raw string, fallback string) (organizationLLMStatsWindow, error) {
	key := strings.TrimSpace(raw)
	if key == "" {
		key = fallback
	}
	switch key {
	case "1h":
		return organizationLLMStatsWindow{key: key, duration: 1 * time.Hour, interval: 5 * time.Minute}, nil
	case "6h":
		return organizationLLMStatsWindow{key: key, duration: 6 * time.Hour, interval: 5 * time.Minute}, nil
	case "12h":
		return organizationLLMStatsWindow{key: key, duration: 12 * time.Hour, interval: 5 * time.Minute}, nil
	case "1d":
		return organizationLLMStatsWindow{key: key, duration: 24 * time.Hour, interval: 5 * time.Minute}, nil
	case "3d":
		return organizationLLMStatsWindow{key: key, duration: 72 * time.Hour, interval: 15 * time.Minute}, nil
	case "1w":
		return organizationLLMStatsWindow{key: key, duration: 7 * 24 * time.Hour, interval: 30 * time.Minute}, nil
	default:
		return organizationLLMStatsWindow{}, badRequest("invalid time window, must be one of: 1h, 6h, 12h, 1d, 3d, 1w")
	}
}

func organizationLLMTruncateBucket(now time.Time) time.Time {
	return now.UTC().Truncate(organizationLLMStatsBucketGranularity)
}

func organizationLLMSubjectFromAccess(access OrganizationProviderProxyAccess) (string, string, bool) {
	if strings.TrimSpace(access.KeyID) != "" && strings.TrimSpace(access.Source) == organizationLLMSourcePersistentOrganization {
		return OrganizationLLMSubjectTypeProxyKey, strings.TrimSpace(access.KeyID), true
	}
	if strings.TrimSpace(access.WorkspaceID) != "" {
		return OrganizationLLMSubjectTypeWorkspace, strings.TrimSpace(access.WorkspaceID), true
	}
	return "", "", false
}

func normalizeOrganizationLLMSubjectType(value string) string {
	switch strings.TrimSpace(value) {
	case OrganizationLLMSubjectTypeProxyKey:
		return OrganizationLLMSubjectTypeProxyKey
	case OrganizationLLMSubjectTypeWorkspace:
		return OrganizationLLMSubjectTypeWorkspace
	default:
		return ""
	}
}

func organizationLLMSourceForSubjectType(subjectType string) string {
	switch normalizeOrganizationLLMSubjectType(subjectType) {
	case OrganizationLLMSubjectTypeProxyKey:
		return organizationLLMSourcePersistentOrganization
	case OrganizationLLMSubjectTypeWorkspace:
		return organizationLLMSourceSignedWorkspace
	default:
		return ""
	}
}

func organizationLLMModelHasPrice(model string) bool {
	_, ok := organizationLLMPriceForModel(model)
	return ok
}

func organizationLLMCalculateCostUSD(model string, inputTokens uint64, outputTokens uint64, cachedTokens uint64) float64 {
	price, ok := organizationLLMPriceForModel(model)
	if !ok {
		return 0
	}
	const tokensPerMillion = 1_000_000
	result := 0.0
	if inputTokens > 0 {
		result += float64(inputTokens) / tokensPerMillion * price.Input
	}
	if outputTokens > 0 {
		result += float64(outputTokens) / tokensPerMillion * price.Output
	}
	if cachedTokens > 0 {
		result += float64(cachedTokens) / tokensPerMillion * price.CacheRead
	}
	return result
}

type organizationLLMPrice struct {
	Input     float64
	Output    float64
	CacheRead float64
}

func organizationLLMPriceForModel(model string) (organizationLLMPrice, bool) {
	trimmedModel := strings.TrimSpace(model)
	if trimmedModel == "" {
		return organizationLLMPrice{}, false
	}
	for _, cost := range organizationLLMDefaultCosts {
		if !organizationLLMMatchAnyPattern(cost.Models, trimmedModel) {
			continue
		}
		price := organizationLLMPrice{
			Input:     cost.InputPrice,
			Output:    cost.OutputPrice,
			CacheRead: cost.CacheReadPrice,
		}
		if price.Input == 0 && price.Output == 0 && price.CacheRead == 0 {
			return organizationLLMPrice{}, false
		}
		return price, true
	}
	return organizationLLMPrice{}, false
}

func organizationLLMMatchAnyPattern(patterns []string, model string) bool {
	for _, pattern := range patterns {
		if organizationLLMModelMatchesPattern(pattern, model) {
			return true
		}
	}
	return false
}

func organizationLLMModelMatchesPattern(pattern string, model string) bool {
	trimmedPattern := strings.TrimSpace(pattern)
	trimmedModel := strings.TrimSpace(model)
	if trimmedPattern == "" || trimmedModel == "" {
		return false
	}
	if trimmedPattern == "*" || trimmedPattern == trimmedModel {
		return true
	}
	if organizationLLMGlobMatch(trimmedPattern, trimmedModel) {
		return true
	}
	if strings.Contains(trimmedModel, "/") {
		return false
	}
	slashIndex := strings.LastIndex(trimmedPattern, "/")
	if slashIndex < 0 || slashIndex >= len(trimmedPattern)-1 {
		return false
	}
	suffixPattern := trimmedPattern[slashIndex+1:]
	return suffixPattern == trimmedModel || organizationLLMGlobMatch(suffixPattern, trimmedModel)
}

func organizationLLMGlobMatch(pattern string, value string) bool {
	patternIndex := 0
	valueIndex := 0
	starIndex := -1
	matchIndex := 0
	for valueIndex < len(value) {
		if patternIndex < len(pattern) && pattern[patternIndex] == '*' {
			starIndex = patternIndex
			matchIndex = valueIndex
			patternIndex++
			continue
		}
		if patternIndex < len(pattern) && (pattern[patternIndex] == value[valueIndex] || pattern[patternIndex] == '?') {
			patternIndex++
			valueIndex++
			continue
		}
		if starIndex == -1 {
			return false
		}
		patternIndex = starIndex + 1
		matchIndex++
		valueIndex = matchIndex
	}
	for patternIndex < len(pattern) && pattern[patternIndex] == '*' {
		patternIndex++
	}
	return patternIndex == len(pattern)
}

func organizationLLMErrorTypeCatalog() []organizationLLMErrorTypeInfo {
	return []organizationLLMErrorTypeInfo{
		{Type: "auth", Label: "Authentication Error", Color: "#ef4444", Description: "API key invalid or access denied"},
		{Type: "rate_limit", Label: "Rate Limit", Color: "#f97316", Description: "Too many requests"},
		{Type: "network", Label: "Network Error", Color: "#8b5cf6", Description: "Connection failed"},
		{Type: "provider", Label: "Provider Error", Color: "#ec4899", Description: "Upstream provider error"},
		{Type: "bad_request", Label: "Bad Request", Color: "#eab308", Description: "Invalid request format"},
		{Type: "model_not_found", Label: "Model Not Found", Color: "#6366f1", Description: "Model does not exist"},
		{Type: "internal", Label: "Internal Error", Color: "#dc2626", Description: "Internal server error"},
		{Type: "timeout", Label: "Timeout", Color: "#f59e0b", Description: "Request timed out"},
	}
}

func normalizeOrganizationLLMErrorType(value string) string {
	trimmed := strings.TrimSpace(value)
	for _, item := range organizationLLMErrorTypeCatalog() {
		if item.Type == trimmed {
			return trimmed
		}
	}
	return ""
}

func organizationLLMModelName(model string) string {
	parts := strings.SplitN(strings.TrimSpace(model), "/", 2)
	if len(parts) == 2 && strings.TrimSpace(parts[1]) != "" {
		return strings.TrimSpace(parts[1])
	}
	return strings.TrimSpace(model)
}

func organizationLLMTokenSpeed(outputTokens uint64, durationMS uint64) float64 {
	if durationMS == 0 {
		return 0
	}
	return float64(outputTokens) / (float64(durationMS) / 1000.0)
}

func nullableID(value string) any {
	trimmed := strings.TrimSpace(value)
	if trimmed == "" {
		return nil
	}
	return trimmed
}

func organizationLLMUsageBucketKey(organizationID string, workspaceID string, proxyKeyID string, source string, providerID string, model string, bucketStart time.Time) string {
	return organizationLLMHashKey("usage", organizationID, workspaceID, proxyKeyID, source, providerID, model, bucketStart.UTC().Format(time.RFC3339))
}

func organizationLLMErrorBucketKey(organizationID string, workspaceID string, proxyKeyID string, source string, providerID string, model string, errorType string, bucketStart time.Time) string {
	return organizationLLMHashKey("error", organizationID, workspaceID, proxyKeyID, source, providerID, model, normalizeOrganizationLLMErrorType(errorType), bucketStart.UTC().Format(time.RFC3339))
}

func organizationLLMHashKey(parts ...string) string {
	hash := sha256.Sum256([]byte(strings.Join(parts, "\x1f")))
	return hex.EncodeToString(hash[:])
}