package app

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"net/http"
	"strings"

	"sub2api-cn-relay-manager/internal/store/sqlite"
)

type CreateLogicalGroupRequest struct {
	LogicalGroupID         string `json:"logical_group_id"`
	DisplayName            string `json:"display_name"`
	Status                 string `json:"status"`
	Description            string `json:"description,omitempty"`
	UsageScenario          string `json:"usage_scenario,omitempty"`
	Recommendation         string `json:"recommendation,omitempty"`
	NextStepHint           string `json:"next_step_hint,omitempty"`
	VisibilityScope        string `json:"visibility_scope,omitempty"`
	PackageTier            string `json:"package_tier,omitempty"`
	PurchaseCTALabel       string `json:"purchase_cta_label,omitempty"`
	PurchaseCTAURL         string `json:"purchase_cta_url,omitempty"`
	RoutePolicy            string `json:"route_policy,omitempty"`
	StickyMode             string `json:"sticky_mode,omitempty"`
	ConversationTTLSeconds int    `json:"conversation_ttl_seconds,omitempty"`
	UserModelTTLSeconds    int    `json:"user_model_ttl_seconds,omitempty"`
	FailoverThreshold      int    `json:"failover_threshold,omitempty"`
	CooldownSeconds        int    `json:"cooldown_seconds,omitempty"`
}

type UpdateLogicalGroupRequest struct {
	LogicalGroupID         string `json:"-"`
	DisplayName            string `json:"display_name"`
	Status                 string `json:"status"`
	Description            string `json:"description,omitempty"`
	UsageScenario          string `json:"usage_scenario,omitempty"`
	Recommendation         string `json:"recommendation,omitempty"`
	NextStepHint           string `json:"next_step_hint,omitempty"`
	VisibilityScope        string `json:"visibility_scope,omitempty"`
	PackageTier            string `json:"package_tier,omitempty"`
	PurchaseCTALabel       string `json:"purchase_cta_label,omitempty"`
	PurchaseCTAURL         string `json:"purchase_cta_url,omitempty"`
	RoutePolicy            string `json:"route_policy,omitempty"`
	StickyMode             string `json:"sticky_mode,omitempty"`
	ConversationTTLSeconds int    `json:"conversation_ttl_seconds,omitempty"`
	UserModelTTLSeconds    int    `json:"user_model_ttl_seconds,omitempty"`
	FailoverThreshold      int    `json:"failover_threshold,omitempty"`
	CooldownSeconds        int    `json:"cooldown_seconds,omitempty"`
}

type LogicalGroupInfo struct {
	LogicalGroupID         string                  `json:"logical_group_id"`
	DisplayName            string                  `json:"display_name"`
	Status                 string                  `json:"status"`
	Description            string                  `json:"description,omitempty"`
	UsageScenario          string                  `json:"usage_scenario,omitempty"`
	Recommendation         string                  `json:"recommendation,omitempty"`
	NextStepHint           string                  `json:"next_step_hint,omitempty"`
	VisibilityScope        string                  `json:"visibility_scope,omitempty"`
	PackageTier            string                  `json:"package_tier,omitempty"`
	PurchaseCTALabel       string                  `json:"purchase_cta_label,omitempty"`
	PurchaseCTAURL         string                  `json:"purchase_cta_url,omitempty"`
	RoutePolicy            string                  `json:"route_policy,omitempty"`
	StickyMode             string                  `json:"sticky_mode,omitempty"`
	ConversationTTLSeconds int                     `json:"conversation_ttl_seconds,omitempty"`
	UserModelTTLSeconds    int                     `json:"user_model_ttl_seconds,omitempty"`
	FailoverThreshold      int                     `json:"failover_threshold,omitempty"`
	CooldownSeconds        int                     `json:"cooldown_seconds,omitempty"`
	Models                 []LogicalGroupModelInfo `json:"models,omitempty"`
	Routes                 []LogicalGroupRouteInfo `json:"routes,omitempty"`
	CreatedAt              string                  `json:"created_at,omitempty"`
	UpdatedAt              string                  `json:"updated_at,omitempty"`
}

type CreateLogicalGroupModelRequest struct {
	LogicalGroupID string `json:"-"`
	PublicModel    string `json:"public_model"`
	Status         string `json:"status,omitempty"`
}

type DeleteLogicalGroupModelRequest struct {
	LogicalGroupID string
	PublicModel    string
}

type LogicalGroupModelInfo struct {
	PublicModel string `json:"public_model"`
	Status      string `json:"status,omitempty"`
	CreatedAt   string `json:"created_at,omitempty"`
	UpdatedAt   string `json:"updated_at,omitempty"`
}

type CreateLogicalGroupRouteRequest struct {
	LogicalGroupID      string `json:"-"`
	RouteID             string `json:"route_id"`
	Name                string `json:"name"`
	Status              string `json:"status"`
	Priority            int    `json:"priority"`
	Weight              int    `json:"weight,omitempty"`
	ShadowGroupID       string `json:"shadow_group_id"`
	ShadowHostID        string `json:"shadow_host_id"`
	UpstreamBaseURLHint string `json:"upstream_base_url_hint,omitempty"`
	CooldownUntil       string `json:"cooldown_until,omitempty"`
}

type UpdateLogicalGroupRouteRequest struct {
	LogicalGroupID      string `json:"-"`
	RouteID             string `json:"-"`
	Name                string `json:"name"`
	Status              string `json:"status"`
	Priority            int    `json:"priority"`
	Weight              int    `json:"weight,omitempty"`
	ShadowGroupID       string `json:"shadow_group_id"`
	ShadowHostID        string `json:"shadow_host_id"`
	UpstreamBaseURLHint string `json:"upstream_base_url_hint,omitempty"`
	CooldownUntil       string `json:"cooldown_until,omitempty"`
}

type DeleteLogicalGroupRouteRequest struct {
	LogicalGroupID string
	RouteID        string
}

type LogicalGroupRouteInfo struct {
	RouteID             string                       `json:"route_id"`
	LogicalGroupID      string                       `json:"logical_group_id"`
	Name                string                       `json:"name"`
	Status              string                       `json:"status"`
	Priority            int                          `json:"priority"`
	Weight              int                          `json:"weight,omitempty"`
	ShadowGroupID       string                       `json:"shadow_group_id"`
	ShadowHostID        string                       `json:"shadow_host_id"`
	UpstreamBaseURLHint string                       `json:"upstream_base_url_hint,omitempty"`
	CooldownUntil       string                       `json:"cooldown_until,omitempty"`
	Models              []LogicalGroupRouteModelInfo `json:"models,omitempty"`
	CreatedAt           string                       `json:"created_at,omitempty"`
	UpdatedAt           string                       `json:"updated_at,omitempty"`
}

type CreateLogicalGroupRouteModelRequest struct {
	LogicalGroupID string `json:"-"`
	RouteID        string `json:"-"`
	PublicModel    string `json:"public_model"`
	ShadowModel    string `json:"shadow_model,omitempty"`
	Status         string `json:"status,omitempty"`
}

type ListLogicalGroupRouteModelsRequest struct {
	LogicalGroupID string
	RouteID        string
}

type LogicalGroupRouteModelInfo struct {
	PublicModel string `json:"public_model"`
	ShadowModel string `json:"shadow_model,omitempty"`
	Status      string `json:"status,omitempty"`
	CreatedAt   string `json:"created_at,omitempty"`
	UpdatedAt   string `json:"updated_at,omitempty"`
}

func handleCreateLogicalGroup(w http.ResponseWriter, r *http.Request, fn func(context.Context, CreateLogicalGroupRequest) (LogicalGroupInfo, error)) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "create-logical-group action is not configured"})
		return
	}
	var req CreateLogicalGroupRequest
	if err := decodeJSON(r, &req); err != nil {
		writeHTTPError(w, err)
		return
	}
	group, err := fn(r.Context(), req)
	if err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	writeJSON(w, http.StatusCreated, map[string]any{"logical_group": group})
}

func handleListLogicalGroups(w http.ResponseWriter, r *http.Request, fn func(context.Context) ([]LogicalGroupInfo, error)) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "list-logical-groups action is not configured"})
		return
	}
	groups, err := fn(r.Context())
	if err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	writeJSON(w, http.StatusOK, map[string]any{"logical_groups": groups})
}

func handleGetLogicalGroup(w http.ResponseWriter, r *http.Request, fn func(context.Context, string) (LogicalGroupInfo, error)) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "get-logical-group action is not configured"})
		return
	}
	groupID := strings.TrimSpace(r.PathValue("groupID"))
	group, err := fn(r.Context(), groupID)
	if err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	writeJSON(w, http.StatusOK, map[string]any{"logical_group": group})
}

func handleUpdateLogicalGroup(w http.ResponseWriter, r *http.Request, fn func(context.Context, UpdateLogicalGroupRequest) (LogicalGroupInfo, error)) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "update-logical-group action is not configured"})
		return
	}
	var req UpdateLogicalGroupRequest
	req.LogicalGroupID = strings.TrimSpace(r.PathValue("groupID"))
	if err := decodeJSON(r, &req); err != nil {
		writeHTTPError(w, err)
		return
	}
	group, err := fn(r.Context(), req)
	if err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	writeJSON(w, http.StatusOK, map[string]any{"logical_group": group})
}

func handleDeleteLogicalGroup(w http.ResponseWriter, r *http.Request, fn func(context.Context, string) error) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "delete-logical-group action is not configured"})
		return
	}
	if err := fn(r.Context(), strings.TrimSpace(r.PathValue("groupID"))); err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	w.WriteHeader(http.StatusNoContent)
}

func handleCreateLogicalGroupModel(w http.ResponseWriter, r *http.Request, fn func(context.Context, CreateLogicalGroupModelRequest) (LogicalGroupModelInfo, error)) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "create-logical-group-model action is not configured"})
		return
	}
	var req CreateLogicalGroupModelRequest
	req.LogicalGroupID = strings.TrimSpace(r.PathValue("groupID"))
	if err := decodeJSON(r, &req); err != nil {
		writeHTTPError(w, err)
		return
	}
	model, err := fn(r.Context(), req)
	if err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	writeJSON(w, http.StatusCreated, map[string]any{"logical_group_model": model})
}

func handleListLogicalGroupModels(w http.ResponseWriter, r *http.Request, fn func(context.Context, string) ([]LogicalGroupModelInfo, error)) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "list-logical-group-models action is not configured"})
		return
	}
	models, err := fn(r.Context(), strings.TrimSpace(r.PathValue("groupID")))
	if err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	writeJSON(w, http.StatusOK, map[string]any{"models": models})
}

func handleDeleteLogicalGroupModel(w http.ResponseWriter, r *http.Request, fn func(context.Context, DeleteLogicalGroupModelRequest) error) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "delete-logical-group-model action is not configured"})
		return
	}
	if err := fn(r.Context(), DeleteLogicalGroupModelRequest{
		LogicalGroupID: strings.TrimSpace(r.PathValue("groupID")),
		PublicModel:    strings.TrimSpace(r.PathValue("model")),
	}); err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	w.WriteHeader(http.StatusNoContent)
}

func handleCreateLogicalGroupRoute(w http.ResponseWriter, r *http.Request, fn func(context.Context, CreateLogicalGroupRouteRequest) (LogicalGroupRouteInfo, error)) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "create-logical-group-route action is not configured"})
		return
	}
	var req CreateLogicalGroupRouteRequest
	req.LogicalGroupID = strings.TrimSpace(r.PathValue("groupID"))
	if err := decodeJSON(r, &req); err != nil {
		writeHTTPError(w, err)
		return
	}
	route, err := fn(r.Context(), req)
	if err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	writeJSON(w, http.StatusCreated, map[string]any{"route": route})
}

func handleListLogicalGroupRoutes(w http.ResponseWriter, r *http.Request, fn func(context.Context, string) ([]LogicalGroupRouteInfo, error)) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "list-logical-group-routes action is not configured"})
		return
	}
	routes, err := fn(r.Context(), strings.TrimSpace(r.PathValue("groupID")))
	if err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	writeJSON(w, http.StatusOK, map[string]any{"routes": routes})
}

func handleUpdateLogicalGroupRoute(w http.ResponseWriter, r *http.Request, fn func(context.Context, UpdateLogicalGroupRouteRequest) (LogicalGroupRouteInfo, error)) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "update-logical-group-route action is not configured"})
		return
	}
	var req UpdateLogicalGroupRouteRequest
	req.LogicalGroupID = strings.TrimSpace(r.PathValue("groupID"))
	req.RouteID = strings.TrimSpace(r.PathValue("routeID"))
	if err := decodeJSON(r, &req); err != nil {
		writeHTTPError(w, err)
		return
	}
	route, err := fn(r.Context(), req)
	if err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	writeJSON(w, http.StatusOK, map[string]any{"route": route})
}

func handleDeleteLogicalGroupRoute(w http.ResponseWriter, r *http.Request, fn func(context.Context, DeleteLogicalGroupRouteRequest) error) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "delete-logical-group-route action is not configured"})
		return
	}
	if err := fn(r.Context(), DeleteLogicalGroupRouteRequest{
		LogicalGroupID: strings.TrimSpace(r.PathValue("groupID")),
		RouteID:        strings.TrimSpace(r.PathValue("routeID")),
	}); err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	w.WriteHeader(http.StatusNoContent)
}

func handleCreateLogicalGroupRouteModel(w http.ResponseWriter, r *http.Request, fn func(context.Context, CreateLogicalGroupRouteModelRequest) (LogicalGroupRouteModelInfo, error)) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "create-logical-group-route-model action is not configured"})
		return
	}
	var req CreateLogicalGroupRouteModelRequest
	req.LogicalGroupID = strings.TrimSpace(r.PathValue("groupID"))
	req.RouteID = strings.TrimSpace(r.PathValue("routeID"))
	if err := decodeJSON(r, &req); err != nil {
		writeHTTPError(w, err)
		return
	}
	model, err := fn(r.Context(), req)
	if err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	writeJSON(w, http.StatusCreated, map[string]any{"route_model": model})
}

func handleListLogicalGroupRouteModels(w http.ResponseWriter, r *http.Request, fn func(context.Context, ListLogicalGroupRouteModelsRequest) ([]LogicalGroupRouteModelInfo, error)) {
	if fn == nil {
		writeHTTPError(w, &httpError{StatusCode: http.StatusInternalServerError, Code: "server_misconfigured", Message: "list-logical-group-route-models action is not configured"})
		return
	}
	models, err := fn(r.Context(), ListLogicalGroupRouteModelsRequest{
		LogicalGroupID: strings.TrimSpace(r.PathValue("groupID")),
		RouteID:        strings.TrimSpace(r.PathValue("routeID")),
	})
	if err != nil {
		writeHTTPError(w, classifyError(err))
		return
	}
	writeJSON(w, http.StatusOK, map[string]any{"route_models": models})
}

func buildCreateLogicalGroupAction(sqliteDSN string) func(context.Context, CreateLogicalGroupRequest) (LogicalGroupInfo, error) {
	return func(ctx context.Context, req CreateLogicalGroupRequest) (LogicalGroupInfo, error) {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return LogicalGroupInfo{}, err
		}
		defer store.Close()

		row := logicalGroupRequestToRow(req)
		if _, err := store.LogicalGroups().Create(ctx, row); err != nil {
			return LogicalGroupInfo{}, err
		}
		return loadLogicalGroupInfo(ctx, store, row.LogicalGroupID)
	}
}

func buildListLogicalGroupsAction(sqliteDSN string) func(context.Context) ([]LogicalGroupInfo, error) {
	return func(ctx context.Context) ([]LogicalGroupInfo, error) {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return nil, err
		}
		defer store.Close()

		rows, err := store.LogicalGroups().List(ctx)
		if err != nil {
			return nil, err
		}
		result := make([]LogicalGroupInfo, 0, len(rows))
		for _, row := range rows {
			info, err := loadLogicalGroupInfo(ctx, store, row.LogicalGroupID)
			if err != nil {
				return nil, err
			}
			result = append(result, info)
		}
		return result, nil
	}
}

func buildGetLogicalGroupAction(sqliteDSN string) func(context.Context, string) (LogicalGroupInfo, error) {
	return func(ctx context.Context, logicalGroupID string) (LogicalGroupInfo, error) {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return LogicalGroupInfo{}, err
		}
		defer store.Close()
		return loadLogicalGroupInfo(ctx, store, logicalGroupID)
	}
}

func buildUpdateLogicalGroupAction(sqliteDSN string) func(context.Context, UpdateLogicalGroupRequest) (LogicalGroupInfo, error) {
	return func(ctx context.Context, req UpdateLogicalGroupRequest) (LogicalGroupInfo, error) {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return LogicalGroupInfo{}, err
		}
		defer store.Close()

		if _, err := getLogicalGroupRow(ctx, store, req.LogicalGroupID); err != nil {
			return LogicalGroupInfo{}, err
		}
		if err := store.LogicalGroups().UpdateByLogicalGroupID(ctx, logicalGroupRequestToRow(CreateLogicalGroupRequest{
			LogicalGroupID:         req.LogicalGroupID,
			DisplayName:            req.DisplayName,
			Status:                 req.Status,
			Description:            req.Description,
			UsageScenario:          req.UsageScenario,
			Recommendation:         req.Recommendation,
			NextStepHint:           req.NextStepHint,
			VisibilityScope:        req.VisibilityScope,
			PackageTier:            req.PackageTier,
			PurchaseCTALabel:       req.PurchaseCTALabel,
			PurchaseCTAURL:         req.PurchaseCTAURL,
			RoutePolicy:            req.RoutePolicy,
			StickyMode:             req.StickyMode,
			ConversationTTLSeconds: req.ConversationTTLSeconds,
			UserModelTTLSeconds:    req.UserModelTTLSeconds,
			FailoverThreshold:      req.FailoverThreshold,
			CooldownSeconds:        req.CooldownSeconds,
		})); err != nil {
			return LogicalGroupInfo{}, err
		}
		return loadLogicalGroupInfo(ctx, store, req.LogicalGroupID)
	}
}

func buildDeleteLogicalGroupAction(sqliteDSN string) func(context.Context, string) error {
	return func(ctx context.Context, logicalGroupID string) error {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return err
		}
		defer store.Close()
		return store.LogicalGroups().DeleteByLogicalGroupID(ctx, strings.TrimSpace(logicalGroupID))
	}
}

func buildCreateLogicalGroupModelAction(sqliteDSN string) func(context.Context, CreateLogicalGroupModelRequest) (LogicalGroupModelInfo, error) {
	return func(ctx context.Context, req CreateLogicalGroupModelRequest) (LogicalGroupModelInfo, error) {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return LogicalGroupModelInfo{}, err
		}
		defer store.Close()

		if _, err := getLogicalGroupRow(ctx, store, req.LogicalGroupID); err != nil {
			return LogicalGroupModelInfo{}, err
		}
		row := sqlite.LogicalGroupModel{
			LogicalGroupID: strings.TrimSpace(req.LogicalGroupID),
			PublicModel:    strings.TrimSpace(req.PublicModel),
			Status:         strings.TrimSpace(req.Status),
		}
		if _, err := store.LogicalGroupModels().Create(ctx, row); err != nil {
			return LogicalGroupModelInfo{}, err
		}
		models, err := store.LogicalGroupModels().ListByLogicalGroupID(ctx, row.LogicalGroupID)
		if err != nil {
			return LogicalGroupModelInfo{}, err
		}
		for _, model := range models {
			if model.PublicModel == row.PublicModel {
				return logicalGroupModelRowToInfo(model), nil
			}
		}
		return LogicalGroupModelInfo{}, fmt.Errorf("logical group model %q/%q not found", row.LogicalGroupID, row.PublicModel)
	}
}

func buildListLogicalGroupModelsAction(sqliteDSN string) func(context.Context, string) ([]LogicalGroupModelInfo, error) {
	return func(ctx context.Context, logicalGroupID string) ([]LogicalGroupModelInfo, error) {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return nil, err
		}
		defer store.Close()

		if _, err := getLogicalGroupRow(ctx, store, logicalGroupID); err != nil {
			return nil, err
		}
		rows, err := store.LogicalGroupModels().ListByLogicalGroupID(ctx, logicalGroupID)
		if err != nil {
			return nil, err
		}
		return logicalGroupModelRowsToInfo(rows), nil
	}
}

func buildDeleteLogicalGroupModelAction(sqliteDSN string) func(context.Context, DeleteLogicalGroupModelRequest) error {
	return func(ctx context.Context, req DeleteLogicalGroupModelRequest) error {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return err
		}
		defer store.Close()

		if _, err := getLogicalGroupRow(ctx, store, req.LogicalGroupID); err != nil {
			return err
		}
		return store.LogicalGroupModels().DeleteByLogicalGroupIDAndModel(ctx, strings.TrimSpace(req.LogicalGroupID), strings.TrimSpace(req.PublicModel))
	}
}

func buildCreateLogicalGroupRouteAction(sqliteDSN string) func(context.Context, CreateLogicalGroupRouteRequest) (LogicalGroupRouteInfo, error) {
	return func(ctx context.Context, req CreateLogicalGroupRouteRequest) (LogicalGroupRouteInfo, error) {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return LogicalGroupRouteInfo{}, err
		}
		defer store.Close()

		if _, err := getLogicalGroupRow(ctx, store, req.LogicalGroupID); err != nil {
			return LogicalGroupRouteInfo{}, err
		}
		row := logicalGroupRouteRequestToRow(req)
		if _, err := store.LogicalGroupRoutes().Create(ctx, row); err != nil {
			return LogicalGroupRouteInfo{}, err
		}
		return loadLogicalGroupRouteInfo(ctx, store, row.RouteID)
	}
}

func buildListLogicalGroupRoutesAction(sqliteDSN string) func(context.Context, string) ([]LogicalGroupRouteInfo, error) {
	return func(ctx context.Context, logicalGroupID string) ([]LogicalGroupRouteInfo, error) {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return nil, err
		}
		defer store.Close()

		if _, err := getLogicalGroupRow(ctx, store, logicalGroupID); err != nil {
			return nil, err
		}
		rows, err := store.LogicalGroupRoutes().ListByLogicalGroupID(ctx, logicalGroupID)
		if err != nil {
			return nil, err
		}
		return logicalGroupRouteRowsToInfo(ctx, store, rows)
	}
}

func buildUpdateLogicalGroupRouteAction(sqliteDSN string) func(context.Context, UpdateLogicalGroupRouteRequest) (LogicalGroupRouteInfo, error) {
	return func(ctx context.Context, req UpdateLogicalGroupRouteRequest) (LogicalGroupRouteInfo, error) {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return LogicalGroupRouteInfo{}, err
		}
		defer store.Close()

		if _, err := getLogicalGroupRow(ctx, store, req.LogicalGroupID); err != nil {
			return LogicalGroupRouteInfo{}, err
		}
		existing, err := getLogicalGroupRouteRow(ctx, store, req.RouteID)
		if err != nil {
			return LogicalGroupRouteInfo{}, err
		}
		if existing.LogicalGroupID != strings.TrimSpace(req.LogicalGroupID) {
			return LogicalGroupRouteInfo{}, fmt.Errorf("logical group route %q not found under logical group %q", req.RouteID, req.LogicalGroupID)
		}
		row := logicalGroupRouteRequestToRow(CreateLogicalGroupRouteRequest{
			LogicalGroupID:      req.LogicalGroupID,
			RouteID:             req.RouteID,
			Name:                req.Name,
			Status:              req.Status,
			Priority:            req.Priority,
			Weight:              req.Weight,
			ShadowGroupID:       req.ShadowGroupID,
			ShadowHostID:        req.ShadowHostID,
			UpstreamBaseURLHint: req.UpstreamBaseURLHint,
			CooldownUntil:       req.CooldownUntil,
		})
		if err := store.LogicalGroupRoutes().UpdateByRouteID(ctx, row); err != nil {
			return LogicalGroupRouteInfo{}, err
		}
		return loadLogicalGroupRouteInfo(ctx, store, req.RouteID)
	}
}

func buildDeleteLogicalGroupRouteAction(sqliteDSN string) func(context.Context, DeleteLogicalGroupRouteRequest) error {
	return func(ctx context.Context, req DeleteLogicalGroupRouteRequest) error {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return err
		}
		defer store.Close()

		existing, err := getLogicalGroupRouteRow(ctx, store, req.RouteID)
		if err != nil {
			return err
		}
		if existing.LogicalGroupID != strings.TrimSpace(req.LogicalGroupID) {
			return fmt.Errorf("logical group route %q not found under logical group %q", req.RouteID, req.LogicalGroupID)
		}
		return store.LogicalGroupRoutes().DeleteByRouteID(ctx, strings.TrimSpace(req.RouteID))
	}
}

func buildCreateLogicalGroupRouteModelAction(sqliteDSN string) func(context.Context, CreateLogicalGroupRouteModelRequest) (LogicalGroupRouteModelInfo, error) {
	return func(ctx context.Context, req CreateLogicalGroupRouteModelRequest) (LogicalGroupRouteModelInfo, error) {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return LogicalGroupRouteModelInfo{}, err
		}
		defer store.Close()

		route, err := getLogicalGroupRouteRow(ctx, store, req.RouteID)
		if err != nil {
			return LogicalGroupRouteModelInfo{}, err
		}
		if route.LogicalGroupID != strings.TrimSpace(req.LogicalGroupID) {
			return LogicalGroupRouteModelInfo{}, fmt.Errorf("logical group route %q not found under logical group %q", req.RouteID, req.LogicalGroupID)
		}
		row := sqlite.LogicalGroupRouteModel{
			RouteID:     strings.TrimSpace(req.RouteID),
			PublicModel: strings.TrimSpace(req.PublicModel),
			ShadowModel: strings.TrimSpace(req.ShadowModel),
			Status:      strings.TrimSpace(req.Status),
		}
		if _, err := store.LogicalGroupRouteModels().Create(ctx, row); err != nil {
			return LogicalGroupRouteModelInfo{}, err
		}
		models, err := store.LogicalGroupRouteModels().ListByRouteID(ctx, row.RouteID)
		if err != nil {
			return LogicalGroupRouteModelInfo{}, err
		}
		for _, model := range models {
			if model.PublicModel == row.PublicModel {
				return logicalGroupRouteModelRowToInfo(model), nil
			}
		}
		return LogicalGroupRouteModelInfo{}, fmt.Errorf("logical group route model %q/%q not found", row.RouteID, row.PublicModel)
	}
}

func buildListLogicalGroupRouteModelsAction(sqliteDSN string) func(context.Context, ListLogicalGroupRouteModelsRequest) ([]LogicalGroupRouteModelInfo, error) {
	return func(ctx context.Context, req ListLogicalGroupRouteModelsRequest) ([]LogicalGroupRouteModelInfo, error) {
		store, err := sqlite.Open(ctx, sqliteDSN)
		if err != nil {
			return nil, err
		}
		defer store.Close()

		route, err := getLogicalGroupRouteRow(ctx, store, req.RouteID)
		if err != nil {
			return nil, err
		}
		if route.LogicalGroupID != strings.TrimSpace(req.LogicalGroupID) {
			return nil, fmt.Errorf("logical group route %q not found under logical group %q", req.RouteID, req.LogicalGroupID)
		}
		rows, err := store.LogicalGroupRouteModels().ListByRouteID(ctx, req.RouteID)
		if err != nil {
			return nil, err
		}
		return logicalGroupRouteModelRowsToInfo(rows), nil
	}
}

func logicalGroupRequestToRow(req CreateLogicalGroupRequest) sqlite.LogicalGroup {
	return sqlite.LogicalGroup{
		LogicalGroupID:         strings.TrimSpace(req.LogicalGroupID),
		DisplayName:            strings.TrimSpace(req.DisplayName),
		Status:                 strings.TrimSpace(req.Status),
		Description:            strings.TrimSpace(req.Description),
		UsageScenario:          strings.TrimSpace(req.UsageScenario),
		Recommendation:         strings.TrimSpace(req.Recommendation),
		NextStepHint:           strings.TrimSpace(req.NextStepHint),
		VisibilityScope:        strings.TrimSpace(req.VisibilityScope),
		PackageTier:            strings.TrimSpace(req.PackageTier),
		PurchaseCTALabel:       strings.TrimSpace(req.PurchaseCTALabel),
		PurchaseCTAURL:         strings.TrimSpace(req.PurchaseCTAURL),
		RoutePolicy:            strings.TrimSpace(req.RoutePolicy),
		StickyMode:             strings.TrimSpace(req.StickyMode),
		ConversationTTLSeconds: req.ConversationTTLSeconds,
		UserModelTTLSeconds:    req.UserModelTTLSeconds,
		FailoverThreshold:      req.FailoverThreshold,
		CooldownSeconds:        req.CooldownSeconds,
	}
}

func logicalGroupRouteRequestToRow(req CreateLogicalGroupRouteRequest) sqlite.LogicalGroupRoute {
	return sqlite.LogicalGroupRoute{
		RouteID:             strings.TrimSpace(req.RouteID),
		LogicalGroupID:      strings.TrimSpace(req.LogicalGroupID),
		Name:                strings.TrimSpace(req.Name),
		Status:              strings.TrimSpace(req.Status),
		Priority:            req.Priority,
		Weight:              req.Weight,
		ShadowGroupID:       strings.TrimSpace(req.ShadowGroupID),
		ShadowHostID:        strings.TrimSpace(req.ShadowHostID),
		UpstreamBaseURLHint: strings.TrimSpace(req.UpstreamBaseURLHint),
		CooldownUntil:       strings.TrimSpace(req.CooldownUntil),
	}
}

func loadLogicalGroupInfo(ctx context.Context, store *sqlite.DB, logicalGroupID string) (LogicalGroupInfo, error) {
	group, err := getLogicalGroupRow(ctx, store, logicalGroupID)
	if err != nil {
		return LogicalGroupInfo{}, err
	}
	models, err := store.LogicalGroupModels().ListByLogicalGroupID(ctx, group.LogicalGroupID)
	if err != nil {
		return LogicalGroupInfo{}, err
	}
	routes, err := store.LogicalGroupRoutes().ListByLogicalGroupID(ctx, group.LogicalGroupID)
	if err != nil {
		return LogicalGroupInfo{}, err
	}
	routeInfos, err := logicalGroupRouteRowsToInfo(ctx, store, routes)
	if err != nil {
		return LogicalGroupInfo{}, err
	}
	return logicalGroupRowToInfo(group, logicalGroupModelRowsToInfo(models), routeInfos), nil
}

func loadLogicalGroupRouteInfo(ctx context.Context, store *sqlite.DB, routeID string) (LogicalGroupRouteInfo, error) {
	route, err := getLogicalGroupRouteRow(ctx, store, routeID)
	if err != nil {
		return LogicalGroupRouteInfo{}, err
	}
	models, err := store.LogicalGroupRouteModels().ListByRouteID(ctx, route.RouteID)
	if err != nil {
		return LogicalGroupRouteInfo{}, err
	}
	return logicalGroupRouteRowToInfo(route, logicalGroupRouteModelRowsToInfo(models)), nil
}

func logicalGroupRowToInfo(group sqlite.LogicalGroup, models []LogicalGroupModelInfo, routes []LogicalGroupRouteInfo) LogicalGroupInfo {
	return LogicalGroupInfo{
		LogicalGroupID:         group.LogicalGroupID,
		DisplayName:            group.DisplayName,
		Status:                 group.Status,
		Description:            group.Description,
		UsageScenario:          group.UsageScenario,
		Recommendation:         group.Recommendation,
		NextStepHint:           group.NextStepHint,
		VisibilityScope:        group.VisibilityScope,
		PackageTier:            group.PackageTier,
		PurchaseCTALabel:       group.PurchaseCTALabel,
		PurchaseCTAURL:         group.PurchaseCTAURL,
		RoutePolicy:            group.RoutePolicy,
		StickyMode:             group.StickyMode,
		ConversationTTLSeconds: group.ConversationTTLSeconds,
		UserModelTTLSeconds:    group.UserModelTTLSeconds,
		FailoverThreshold:      group.FailoverThreshold,
		CooldownSeconds:        group.CooldownSeconds,
		Models:                 models,
		Routes:                 routes,
		CreatedAt:              group.CreatedAt,
		UpdatedAt:              group.UpdatedAt,
	}
}

func logicalGroupModelRowsToInfo(rows []sqlite.LogicalGroupModel) []LogicalGroupModelInfo {
	result := make([]LogicalGroupModelInfo, 0, len(rows))
	for _, row := range rows {
		result = append(result, logicalGroupModelRowToInfo(row))
	}
	return result
}

func logicalGroupModelRowToInfo(row sqlite.LogicalGroupModel) LogicalGroupModelInfo {
	return LogicalGroupModelInfo{
		PublicModel: row.PublicModel,
		Status:      row.Status,
		CreatedAt:   row.CreatedAt,
		UpdatedAt:   row.UpdatedAt,
	}
}

func logicalGroupRouteRowsToInfo(ctx context.Context, store *sqlite.DB, rows []sqlite.LogicalGroupRoute) ([]LogicalGroupRouteInfo, error) {
	result := make([]LogicalGroupRouteInfo, 0, len(rows))
	for _, row := range rows {
		models, err := store.LogicalGroupRouteModels().ListByRouteID(ctx, row.RouteID)
		if err != nil {
			return nil, err
		}
		result = append(result, logicalGroupRouteRowToInfo(row, logicalGroupRouteModelRowsToInfo(models)))
	}
	return result, nil
}

func logicalGroupRouteRowToInfo(row sqlite.LogicalGroupRoute, models []LogicalGroupRouteModelInfo) LogicalGroupRouteInfo {
	return LogicalGroupRouteInfo{
		RouteID:             row.RouteID,
		LogicalGroupID:      row.LogicalGroupID,
		Name:                row.Name,
		Status:              row.Status,
		Priority:            row.Priority,
		Weight:              row.Weight,
		ShadowGroupID:       row.ShadowGroupID,
		ShadowHostID:        row.ShadowHostID,
		UpstreamBaseURLHint: row.UpstreamBaseURLHint,
		CooldownUntil:       row.CooldownUntil,
		Models:              models,
		CreatedAt:           row.CreatedAt,
		UpdatedAt:           row.UpdatedAt,
	}
}

func logicalGroupRouteModelRowsToInfo(rows []sqlite.LogicalGroupRouteModel) []LogicalGroupRouteModelInfo {
	result := make([]LogicalGroupRouteModelInfo, 0, len(rows))
	for _, row := range rows {
		result = append(result, logicalGroupRouteModelRowToInfo(row))
	}
	return result
}

func logicalGroupRouteModelRowToInfo(row sqlite.LogicalGroupRouteModel) LogicalGroupRouteModelInfo {
	return LogicalGroupRouteModelInfo{
		PublicModel: row.PublicModel,
		ShadowModel: row.ShadowModel,
		Status:      row.Status,
		CreatedAt:   row.CreatedAt,
		UpdatedAt:   row.UpdatedAt,
	}
}

func getLogicalGroupRow(ctx context.Context, store *sqlite.DB, logicalGroupID string) (sqlite.LogicalGroup, error) {
	row, err := store.LogicalGroups().GetByLogicalGroupID(ctx, strings.TrimSpace(logicalGroupID))
	if errors.Is(err, sql.ErrNoRows) {
		return sqlite.LogicalGroup{}, fmt.Errorf("logical group %q not found", strings.TrimSpace(logicalGroupID))
	}
	return row, err
}

func getLogicalGroupRouteRow(ctx context.Context, store *sqlite.DB, routeID string) (sqlite.LogicalGroupRoute, error) {
	row, err := store.LogicalGroupRoutes().GetByRouteID(ctx, strings.TrimSpace(routeID))
	if errors.Is(err, sql.ErrNoRows) {
		return sqlite.LogicalGroupRoute{}, fmt.Errorf("logical group route %q not found", strings.TrimSpace(routeID))
	}
	return row, err
}
