package workspace

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/storeerr"
)

const (
	PlacementStateActive   = "active"
	PlacementStateDraining = "draining"
	PlacementStateMoving   = "moving"
	PlacementStateDisabled = "disabled"
)

const (
	WorkspaceGroupStateActive   = "active"
	WorkspaceGroupStateDraining = "draining"
	WorkspaceGroupStateDisabled = "disabled"
)

const (
	PlacementMigrationStateRequested  = "requested"
	PlacementMigrationStatePreflight  = "preflight"
	PlacementMigrationStateDraining   = "draining"
	PlacementMigrationStateMoving     = "moving"
	PlacementMigrationStateVerifying  = "verifying"
	PlacementMigrationStateCompleted  = "completed"
	PlacementMigrationStateFailed     = "failed"
	PlacementMigrationStateRolledBack = "rolled_back"
)

// WorkspacePlacement is the stable workspace-to-group execution placement.
type WorkspacePlacement struct {
	WorkspaceID      string
	WorkspaceGroupID string
	State            string
	PlacementEpoch   int64
	CreatedAt        time.Time
	UpdatedAt        time.Time
}

// WorkspaceGroup stores stable operator intent for a workspace execution group.
type WorkspaceGroup struct {
	WorkspaceGroupID string
	DisplayName      string
	State            string
	ResourceProfile  string
	ServiceRef       string
	CreatedAt        time.Time
	UpdatedAt        time.Time
}

// WorkspacePlacementListFilter filters admin placement reads.
type WorkspacePlacementListFilter struct {
	WorkspaceID      string
	WorkspaceGroupID string
	State            string
	IncludeInactive  bool
}

// WorkspaceRouteTarget is the router-facing resolution of a workspace owner.
type WorkspaceRouteTarget struct {
	WorkspaceID      string
	WorkspaceGroupID string
	PlacementState   string
	PlacementEpoch   int64
	GroupState       string
	ServiceRef       string
}

// WorkspacePlacementMigration records one operator-triggered cross-group move.
type WorkspacePlacementMigration struct {
	MigrationID           string
	WorkspaceID           string
	SourceGroupID         string
	TargetGroupID         string
	State                 string
	PlacementEpochStart   int64
	PlacementEpochCurrent int64
	RequestedBy           string
	Reason                string
	IdempotencyKey        string
	LastError             string
	CreatedAt             time.Time
	UpdatedAt             time.Time
	CompletedAt           *time.Time
}

// CreateWorkspaceGroupRequest creates a stable workspace execution group.
type CreateWorkspaceGroupRequest struct {
	WorkspaceGroupID string
	DisplayName      string
	ResourceProfile  string
	ServiceRef       string
}

// StartWorkspacePlacementMigrationRequest starts a fenced cross-group migration.
type StartWorkspacePlacementMigrationRequest struct {
	WorkspaceID    string
	SourceGroupID  string
	TargetGroupID  string
	RequestedBy    string
	Reason         string
	IdempotencyKey string
}

// WorkspacePlacementVerification is the machine-readable Phase 1 cutover gate result.
type WorkspacePlacementVerification struct {
	WorkspaceGroupID         string `json:"workspace_group_id"`
	TotalWorkspaceCount      int64  `json:"total_workspace_count"`
	PlacementCount           int64  `json:"placement_count"`
	MissingPlacementCount    int64  `json:"missing_placement_count"`
	NonDefaultPlacementCount int64  `json:"non_default_placement_count"`
	InactivePlacementCount   int64  `json:"inactive_placement_count"`
	MalformedPlacementCount  int64  `json:"malformed_placement_count,omitempty"`
}

// WorkspacePlacementTopologyVerification is the multi-group admin preflight result.
type WorkspacePlacementTopologyVerification struct {
	TotalWorkspaceCount         int64 `json:"total_workspace_count"`
	WorkspaceGroupCount         int64 `json:"workspace_group_count"`
	ActiveWorkspaceGroupCount   int64 `json:"active_workspace_group_count"`
	PlacementCount              int64 `json:"placement_count"`
	MissingPlacementCount       int64 `json:"missing_placement_count"`
	UnknownGroupPlacementCount  int64 `json:"unknown_group_placement_count"`
	InactiveGroupPlacementCount int64 `json:"inactive_group_placement_count"`
	InactivePlacementCount      int64 `json:"inactive_placement_count"`
	MalformedPlacementCount     int64 `json:"malformed_placement_count"`
	ActiveMigrationCount        int64 `json:"active_migration_count"`
}

// EnsureWorkspacePlacementInTx writes the default Phase 1 placement for a new workspace.
func EnsureWorkspacePlacementInTx(ctx context.Context, tx coredb.Tx, workspaceID string, workspaceGroupID string, now time.Time) error {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedWorkspaceID == "" {
		return fmt.Errorf("workspace placement workspace id is required")
	}
	trimmedGroupID := strings.TrimSpace(workspaceGroupID)
	if trimmedGroupID == "" {
		return fmt.Errorf("workspace placement group id is required for workspace %s", trimmedWorkspaceID)
	}
	if tx == nil {
		return fmt.Errorf("workspace placement tx is not configured for workspace %s", trimmedWorkspaceID)
	}
	if now.IsZero() {
		now = time.Now().UTC()
	}
	if _, err := tx.Exec(ctx, `
INSERT INTO workspace_placements (
  workspace_id,
  workspace_group_id,
  state,
  placement_epoch,
  created_at,
  updated_at
) VALUES ($1, $2, $3, 1, $4, $4)
ON DUPLICATE KEY UPDATE
  updated_at = workspace_placements.updated_at
`, trimmedWorkspaceID, trimmedGroupID, PlacementStateActive, now); err != nil {
		return fmt.Errorf("ensure workspace placement for workspace %s in group %s: %w", trimmedWorkspaceID, trimmedGroupID, err)
	}
	return nil
}

// CreateWorkspaceGroup registers a stable workspace group for router/admin use.
func (store *Store) CreateWorkspaceGroup(ctx context.Context, request CreateWorkspaceGroupRequest) (WorkspaceGroup, error) {
	group := WorkspaceGroup{
		WorkspaceGroupID: strings.TrimSpace(request.WorkspaceGroupID),
		DisplayName:      strings.TrimSpace(request.DisplayName),
		State:            WorkspaceGroupStateActive,
		ResourceProfile:  strings.TrimSpace(request.ResourceProfile),
		ServiceRef:       strings.TrimSpace(request.ServiceRef),
	}
	if group.WorkspaceGroupID == "" {
		return WorkspaceGroup{}, badRequest("workspaceGroupId is required")
	}
	if group.DisplayName == "" {
		group.DisplayName = group.WorkspaceGroupID
	}
	if group.ServiceRef == "" {
		group.ServiceRef = group.WorkspaceGroupID
	}
	if store == nil || store.db == nil {
		return WorkspaceGroup{}, fmt.Errorf("workspace store database is not configured")
	}
	if _, err := store.db.Exec(ctx, `
INSERT INTO workspace_groups (
  workspace_group_id,
  display_name,
  state,
  resource_profile,
  service_ref,
  created_at,
  updated_at
) VALUES ($1, $2, $3, $4, $5, NOW(), NOW())
`, group.WorkspaceGroupID, group.DisplayName, group.State, group.ResourceProfile, group.ServiceRef); err != nil {
		if coredb.IsUniqueViolation(err) {
			return WorkspaceGroup{}, conflict(fmt.Sprintf("workspace group %s already exists", group.WorkspaceGroupID))
		}
		return WorkspaceGroup{}, fmt.Errorf("create workspace group %s: %w", group.WorkspaceGroupID, err)
	}
	created, err := store.GetWorkspaceGroup(ctx, group.WorkspaceGroupID)
	if err != nil {
		return WorkspaceGroup{}, fmt.Errorf("load created workspace group %s: %w", group.WorkspaceGroupID, err)
	}
	return created, nil
}

// GetWorkspaceGroup loads one workspace group by id.
func (store *Store) GetWorkspaceGroup(ctx context.Context, workspaceGroupID string) (WorkspaceGroup, error) {
	groupID := strings.TrimSpace(workspaceGroupID)
	if groupID == "" {
		return WorkspaceGroup{}, badRequest("workspaceGroupId is required")
	}
	if store == nil || store.db == nil {
		return WorkspaceGroup{}, fmt.Errorf("workspace store database is not configured")
	}
	var group WorkspaceGroup
	if err := store.db.QueryRow(ctx, `
SELECT workspace_group_id, display_name, state, resource_profile, service_ref, created_at, updated_at
FROM workspace_groups
WHERE workspace_group_id = $1
`, groupID).Scan(&group.WorkspaceGroupID, &group.DisplayName, &group.State, &group.ResourceProfile, &group.ServiceRef, &group.CreatedAt, &group.UpdatedAt); err != nil {
		if coredb.IsNotFound(err) {
			return WorkspaceGroup{}, storeerr.NotFound(fmt.Sprintf("workspace group %s not found", groupID))
		}
		return WorkspaceGroup{}, fmt.Errorf("get workspace group %s: %w", groupID, err)
	}
	return group, nil
}

// ListWorkspaceGroups returns all workspace groups for admin surfaces.
func (store *Store) ListWorkspaceGroups(ctx context.Context) ([]WorkspaceGroup, error) {
	if store == nil || store.db == nil {
		return nil, fmt.Errorf("workspace store database is not configured")
	}
	rows, err := store.db.Query(ctx, `
SELECT workspace_group_id, display_name, state, resource_profile, service_ref, created_at, updated_at
FROM workspace_groups
ORDER BY workspace_group_id
`)
	if err != nil {
		return nil, fmt.Errorf("list workspace groups: %w", err)
	}
	defer rows.Close()

	groups := make([]WorkspaceGroup, 0)
	for rows.Next() {
		var group WorkspaceGroup
		if err := rows.Scan(&group.WorkspaceGroupID, &group.DisplayName, &group.State, &group.ResourceProfile, &group.ServiceRef, &group.CreatedAt, &group.UpdatedAt); err != nil {
			return nil, fmt.Errorf("scan workspace group: %w", err)
		}
		groups = append(groups, group)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workspace groups: %w", err)
	}
	return groups, nil
}

// DisableWorkspaceGroup prevents future placements after safety checks pass.
func (store *Store) DisableWorkspaceGroup(ctx context.Context, workspaceGroupID string) (WorkspaceGroup, error) {
	groupID := strings.TrimSpace(workspaceGroupID)
	if groupID == "" {
		return WorkspaceGroup{}, badRequest("workspaceGroupId is required")
	}
	if store == nil || store.db == nil {
		return WorkspaceGroup{}, fmt.Errorf("workspace store database is not configured")
	}
	var activePlacements int64
	if err := store.db.QueryRow(ctx, `
SELECT COUNT(*)
FROM workspace_placements
WHERE workspace_group_id = $1
  AND state IN ($2, $3, $4)
`, groupID, PlacementStateActive, PlacementStateDraining, PlacementStateMoving).Scan(&activePlacements); err != nil {
		return WorkspaceGroup{}, fmt.Errorf("count active placements for workspace group %s: %w", groupID, err)
	}
	if activePlacements > 0 {
		return WorkspaceGroup{}, conflict(fmt.Sprintf("workspace group %s has %d active placements", groupID, activePlacements))
	}
	result, err := store.db.Exec(ctx, `
UPDATE workspace_groups
SET state = $2, updated_at = NOW()
WHERE workspace_group_id = $1
`, groupID, WorkspaceGroupStateDisabled)
	if err != nil {
		return WorkspaceGroup{}, fmt.Errorf("disable workspace group %s: %w", groupID, err)
	}
	if result.RowsAffected() == 0 {
		return WorkspaceGroup{}, storeerr.NotFound(fmt.Sprintf("workspace group %s not found", groupID))
	}
	return store.GetWorkspaceGroup(ctx, groupID)
}

// BackfillWorkspacePlacements creates placements for workspaces that do not have one yet.
func (store *Store) BackfillWorkspacePlacements(ctx context.Context, workspaceGroupID string) (int64, error) {
	trimmedGroupID := strings.TrimSpace(workspaceGroupID)
	if trimmedGroupID == "" {
		return 0, fmt.Errorf("workspace group id is required")
	}
	if store == nil || store.db == nil {
		return 0, fmt.Errorf("workspace store database is not configured")
	}
	result, err := store.db.Exec(ctx, `
INSERT INTO workspace_placements (
  workspace_id,
  workspace_group_id,
  state,
  placement_epoch,
  created_at,
  updated_at
)
SELECT
  w.id,
  $1,
  $2,
  1,
  NOW(),
  NOW()
FROM workspaces w
LEFT JOIN workspace_placements p
  ON p.workspace_id = w.id
WHERE p.workspace_id IS NULL
`, trimmedGroupID, PlacementStateActive)
	if err != nil {
		return 0, fmt.Errorf("backfill workspace placements for group %s: %w", trimmedGroupID, err)
	}
	return result.RowsAffected(), nil
}

// RepairMissingWorkspacePlacement creates one explicitly targeted placement.
func (store *Store) RepairMissingWorkspacePlacement(ctx context.Context, workspaceID string, workspaceGroupID string) (WorkspacePlacement, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedWorkspaceID == "" {
		return WorkspacePlacement{}, badRequest("workspaceId is required")
	}
	trimmedGroupID := strings.TrimSpace(workspaceGroupID)
	if trimmedGroupID == "" {
		return WorkspacePlacement{}, badRequest("workspaceGroupId is required")
	}
	if store == nil || store.db == nil {
		return WorkspacePlacement{}, fmt.Errorf("workspace store database is not configured")
	}

	placement := WorkspacePlacement{
		WorkspaceID:      trimmedWorkspaceID,
		WorkspaceGroupID: trimmedGroupID,
		State:            PlacementStateActive,
		PlacementEpoch:   1,
	}
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var lockedWorkspaceID string
		if err := tx.QueryRow(ctx, `
SELECT id
FROM workspaces
WHERE id = $1
FOR UPDATE
`, trimmedWorkspaceID).Scan(&lockedWorkspaceID); err != nil {
			if coredb.IsNotFound(err) {
				return storeerr.NotFound(fmt.Sprintf("workspace %s not found", trimmedWorkspaceID))
			}
			return fmt.Errorf("load workspace %s for placement repair: %w", trimmedWorkspaceID, err)
		}

		var groupState string
		if err := tx.QueryRow(ctx, `
SELECT state
FROM workspace_groups
WHERE workspace_group_id = $1
FOR UPDATE
`, trimmedGroupID).Scan(&groupState); err != nil {
			if coredb.IsNotFound(err) {
				return conflict(fmt.Sprintf("workspace group %s does not exist", trimmedGroupID))
			}
			return fmt.Errorf("load workspace group %s for placement repair: %w", trimmedGroupID, err)
		}
		if groupState != WorkspaceGroupStateActive {
			return conflict(fmt.Sprintf("workspace group %s is not active: state=%s", trimmedGroupID, groupState))
		}

		var placementCount int64
		if err := tx.QueryRow(ctx, `
SELECT COUNT(*)
FROM workspace_placements
WHERE workspace_id = $1
`, trimmedWorkspaceID).Scan(&placementCount); err != nil {
			return fmt.Errorf("check workspace %s placement before repair: %w", trimmedWorkspaceID, err)
		}
		if placementCount != 0 {
			return conflict(fmt.Sprintf("workspace %s already has a placement", trimmedWorkspaceID))
		}

		if _, err := tx.Exec(ctx, `
INSERT INTO workspace_placements (
  workspace_id,
  workspace_group_id,
  state,
  placement_epoch,
  created_at,
  updated_at
) VALUES ($1, $2, $3, 1, NOW(), NOW())
`, trimmedWorkspaceID, trimmedGroupID, PlacementStateActive); err != nil {
			if coredb.IsUniqueViolation(err) {
				return conflict(fmt.Sprintf("workspace %s already has a placement", trimmedWorkspaceID))
			}
			return fmt.Errorf("repair workspace %s placement in group %s: %w", trimmedWorkspaceID, trimmedGroupID, err)
		}
		return nil
	})
	if err != nil {
		return WorkspacePlacement{}, err
	}
	return placement, nil
}

// CountWorkspacesMissingPlacement returns the number of workspaces without placement.
func (store *Store) CountWorkspacesMissingPlacement(ctx context.Context) (int64, error) {
	if store == nil || store.db == nil {
		return 0, fmt.Errorf("workspace store database is not configured")
	}
	var count int64
	if err := store.db.QueryRow(ctx, `
SELECT COUNT(*)
FROM workspaces w
LEFT JOIN workspace_placements p ON p.workspace_id = w.id
WHERE p.workspace_id IS NULL
`).Scan(&count); err != nil {
		return 0, fmt.Errorf("count workspaces missing placement: %w", err)
	}
	return count, nil
}

// VerifyWorkspacePlacements reads only workspaces and workspace_placements for the cutover gate.
func (store *Store) VerifyWorkspacePlacements(ctx context.Context, workspaceGroupID string) (WorkspacePlacementVerification, error) {
	trimmedGroupID := strings.TrimSpace(workspaceGroupID)
	if trimmedGroupID == "" {
		return WorkspacePlacementVerification{}, fmt.Errorf("workspace group id is required")
	}
	if store == nil || store.db == nil {
		return WorkspacePlacementVerification{}, fmt.Errorf("workspace store database is not configured")
	}

	result := WorkspacePlacementVerification{WorkspaceGroupID: trimmedGroupID}
	if err := store.db.QueryRow(ctx, `
SELECT
  (SELECT COUNT(*) FROM workspaces) AS total_workspace_count,
  (SELECT COUNT(*) FROM workspace_placements) AS placement_count,
  (
    SELECT COUNT(*)
    FROM workspaces w
    LEFT JOIN workspace_placements p ON p.workspace_id = w.id
    WHERE p.workspace_id IS NULL
  ) AS missing_placement_count,
  (
    SELECT COUNT(*)
    FROM workspace_placements p
    WHERE p.workspace_group_id <> $1
  ) AS non_default_placement_count,
  (
    SELECT COUNT(*)
    FROM workspace_placements p
    WHERE p.state IN ($3, $4, $5)
  ) AS inactive_placement_count,
  (
    SELECT COUNT(*)
    FROM workspace_placements p
    WHERE p.state NOT IN ($2, $3, $4, $5)
  ) AS malformed_placement_count
`, trimmedGroupID, PlacementStateActive, PlacementStateDraining, PlacementStateMoving, PlacementStateDisabled).Scan(
		&result.TotalWorkspaceCount,
		&result.PlacementCount,
		&result.MissingPlacementCount,
		&result.NonDefaultPlacementCount,
		&result.InactivePlacementCount,
		&result.MalformedPlacementCount,
	); err != nil {
		return WorkspacePlacementVerification{}, fmt.Errorf("verify workspace placements for group %s: %w", trimmedGroupID, err)
	}
	return result, nil
}

// VerifyWorkspacePlacementTopology validates multi-group placement consistency.
func (store *Store) VerifyWorkspacePlacementTopology(ctx context.Context) (WorkspacePlacementTopologyVerification, error) {
	if store == nil || store.db == nil {
		return WorkspacePlacementTopologyVerification{}, fmt.Errorf("workspace store database is not configured")
	}
	var result WorkspacePlacementTopologyVerification
	if err := store.db.QueryRow(ctx, `
SELECT
  (SELECT COUNT(*) FROM workspaces) AS total_workspace_count,
  (SELECT COUNT(*) FROM workspace_groups) AS workspace_group_count,
  (SELECT COUNT(*) FROM workspace_groups WHERE state = $1) AS active_workspace_group_count,
  (SELECT COUNT(*) FROM workspace_placements) AS placement_count,
  (
    SELECT COUNT(*)
    FROM workspaces w
    LEFT JOIN workspace_placements p ON p.workspace_id = w.id
    WHERE p.workspace_id IS NULL
  ) AS missing_placement_count,
  (
    SELECT COUNT(*)
    FROM workspace_placements p
    LEFT JOIN workspace_groups g ON g.workspace_group_id = p.workspace_group_id
    WHERE g.workspace_group_id IS NULL
  ) AS unknown_group_placement_count,
  (
    SELECT COUNT(*)
    FROM workspace_placements p
    JOIN workspace_groups g ON g.workspace_group_id = p.workspace_group_id
    WHERE p.state IN ($2, $3, $4) AND g.state <> $1
  ) AS inactive_group_placement_count,
  (
    SELECT COUNT(*)
    FROM workspace_placements p
    WHERE p.state IN ($3, $4, $5)
  ) AS inactive_placement_count,
  (
    SELECT COUNT(*)
    FROM workspace_placements p
    WHERE p.state NOT IN ($2, $3, $4, $5)
  ) AS malformed_placement_count,
  (
    SELECT COUNT(*)
    FROM workspace_placement_migrations m
    WHERE m.state IN ($6, $7, $8, $9, $10)
  ) AS active_migration_count
`, WorkspaceGroupStateActive, PlacementStateActive, PlacementStateDraining, PlacementStateMoving, PlacementStateDisabled, PlacementMigrationStateRequested, PlacementMigrationStatePreflight, PlacementMigrationStateDraining, PlacementMigrationStateMoving, PlacementMigrationStateVerifying).Scan(
		&result.TotalWorkspaceCount,
		&result.WorkspaceGroupCount,
		&result.ActiveWorkspaceGroupCount,
		&result.PlacementCount,
		&result.MissingPlacementCount,
		&result.UnknownGroupPlacementCount,
		&result.InactiveGroupPlacementCount,
		&result.InactivePlacementCount,
		&result.MalformedPlacementCount,
		&result.ActiveMigrationCount,
	); err != nil {
		return WorkspacePlacementTopologyVerification{}, fmt.Errorf("verify workspace placement topology: %w", err)
	}
	return result, nil
}

// ListWorkspacePlacements returns placement rows for admin surfaces.
func (store *Store) ListWorkspacePlacements(ctx context.Context, filter WorkspacePlacementListFilter) ([]WorkspacePlacement, error) {
	if store == nil || store.db == nil {
		return nil, fmt.Errorf("workspace store database is not configured")
	}
	workspaceID := strings.TrimSpace(filter.WorkspaceID)
	groupID := strings.TrimSpace(filter.WorkspaceGroupID)
	state := strings.TrimSpace(filter.State)
	rows, err := store.db.Query(ctx, `
SELECT workspace_id, workspace_group_id, state, placement_epoch, created_at, updated_at
FROM workspace_placements
WHERE ($1 = '' OR workspace_id = $1)
  AND ($2 = '' OR workspace_group_id = $2)
  AND ($3 = '' OR state = $3)
  AND ($3 <> '' OR $4 OR state = $5)
ORDER BY workspace_id
`, workspaceID, groupID, state, filter.IncludeInactive, PlacementStateActive)
	if err != nil {
		return nil, fmt.Errorf("list workspace placements workspace_id=%s group_id=%s state=%s: %w", workspaceID, groupID, state, err)
	}
	defer rows.Close()

	placements := make([]WorkspacePlacement, 0)
	for rows.Next() {
		var placement WorkspacePlacement
		if err := rows.Scan(&placement.WorkspaceID, &placement.WorkspaceGroupID, &placement.State, &placement.PlacementEpoch, &placement.CreatedAt, &placement.UpdatedAt); err != nil {
			return nil, fmt.Errorf("scan workspace placement: %w", err)
		}
		placements = append(placements, placement)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workspace placements: %w", err)
	}
	return placements, nil
}

// ResolveWorkspaceRouteTarget returns the current group target for router/gateway forwarding.
func (store *Store) ResolveWorkspaceRouteTarget(ctx context.Context, workspaceID string) (WorkspaceRouteTarget, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedWorkspaceID == "" {
		return WorkspaceRouteTarget{}, badRequest("workspaceId is required")
	}
	if store == nil || store.db == nil {
		return WorkspaceRouteTarget{}, fmt.Errorf("workspace store database is not configured")
	}
	var target WorkspaceRouteTarget
	if err := store.db.QueryRow(ctx, `
SELECT
  p.workspace_id,
  p.workspace_group_id,
  p.state,
  p.placement_epoch,
  g.state,
  g.service_ref
FROM workspace_placements p
JOIN workspace_groups g ON g.workspace_group_id = p.workspace_group_id
WHERE p.workspace_id = $1
`, trimmedWorkspaceID).Scan(&target.WorkspaceID, &target.WorkspaceGroupID, &target.PlacementState, &target.PlacementEpoch, &target.GroupState, &target.ServiceRef); err != nil {
		if coredb.IsNotFound(err) {
			return WorkspaceRouteTarget{}, storeerr.NotFound(fmt.Sprintf("workspace placement for workspace %s not found", trimmedWorkspaceID))
		}
		return WorkspaceRouteTarget{}, fmt.Errorf("resolve workspace route target for workspace %s: %w", trimmedWorkspaceID, err)
	}
	if target.GroupState != WorkspaceGroupStateActive {
		return WorkspaceRouteTarget{}, conflict(fmt.Sprintf("workspace %s resolves to non-active group %s state=%s", target.WorkspaceID, target.WorkspaceGroupID, target.GroupState))
	}
	switch target.PlacementState {
	case PlacementStateActive, PlacementStateDraining, PlacementStateMoving:
		return target, nil
	default:
		return WorkspaceRouteTarget{}, conflict(fmt.Sprintf("workspace %s placement state %s cannot receive traffic", target.WorkspaceID, target.PlacementState))
	}
}

// IsWorkspaceRoutableToGroup checks the DB-authoritative traffic owner for one workspace.
func (store *Store) IsWorkspaceRoutableToGroup(ctx context.Context, workspaceID string, workspaceGroupID string) (bool, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedWorkspaceID == "" {
		return false, fmt.Errorf("workspace id is required")
	}
	trimmedGroupID := strings.TrimSpace(workspaceGroupID)
	if trimmedGroupID == "" {
		return false, fmt.Errorf("workspace group id is required")
	}
	if store == nil || store.db == nil {
		return false, fmt.Errorf("workspace store database is not configured")
	}
	var exists int
	if err := store.db.QueryRow(ctx, `
SELECT 1
FROM workspace_placements
WHERE workspace_id = $1
  AND workspace_group_id = $2
  AND state IN ($3, $4, $5)
LIMIT 1
`, trimmedWorkspaceID, trimmedGroupID, PlacementStateActive, PlacementStateDraining, PlacementStateMoving).Scan(&exists); err != nil {
		if coredb.IsNotFound(err) {
			return false, nil
		}
		return false, fmt.Errorf("check routable workspace placement workspace_id=%s group_id=%s: %w", trimmedWorkspaceID, trimmedGroupID, err)
	}
	return exists == 1, nil
}

// ListActiveWorkspaceIDsForGroup returns the active workspace snapshot for one workspace profile process.
func (store *Store) ListActiveWorkspaceIDsForGroup(ctx context.Context, workspaceGroupID string) ([]string, error) {
	return store.listWorkspaceIDsForGroup(ctx, workspaceGroupID, []string{PlacementStateActive}, "active")
}

// ListRoutableWorkspaceIDsForGroup returns the startup ownership snapshot for one workspace profile process.
func (store *Store) ListRoutableWorkspaceIDsForGroup(ctx context.Context, workspaceGroupID string) ([]string, error) {
	return store.listWorkspaceIDsForGroup(ctx, workspaceGroupID, []string{PlacementStateActive, PlacementStateDraining, PlacementStateMoving}, "routable")
}

func (store *Store) listWorkspaceIDsForGroup(ctx context.Context, workspaceGroupID string, states []string, label string) ([]string, error) {
	trimmedGroupID := strings.TrimSpace(workspaceGroupID)
	if trimmedGroupID == "" {
		return nil, fmt.Errorf("workspace group id is required")
	}
	if store == nil || store.db == nil {
		return nil, fmt.Errorf("workspace store database is not configured")
	}
	normalizedStates := make([]string, 0, len(states))
	for _, state := range states {
		trimmed := strings.TrimSpace(state)
		if trimmed != "" {
			normalizedStates = append(normalizedStates, trimmed)
		}
	}
	if len(normalizedStates) == 0 {
		return nil, fmt.Errorf("workspace placement states are required")
	}
	placeholders := make([]string, 0, len(normalizedStates))
	args := []any{trimmedGroupID}
	for i, state := range normalizedStates {
		placeholders = append(placeholders, fmt.Sprintf("$%d", i+2))
		args = append(args, state)
	}
	query := fmt.Sprintf(`
SELECT workspace_id
FROM workspace_placements
WHERE workspace_group_id = $1
  AND state IN (%s)
ORDER BY workspace_id
`, strings.Join(placeholders, ", "))
	rows, err := store.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list %s workspace placements for group %s: %w", label, trimmedGroupID, err)
	}
	defer rows.Close()

	workspaceIDs := make([]string, 0)
	for rows.Next() {
		var workspaceID string
		if err := rows.Scan(&workspaceID); err != nil {
			return nil, fmt.Errorf("scan %s workspace placement for group %s: %w", label, trimmedGroupID, err)
		}
		workspaceIDs = append(workspaceIDs, strings.TrimSpace(workspaceID))
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate %s workspace placements for group %s: %w", label, trimmedGroupID, err)
	}
	return workspaceIDs, nil
}

// StartWorkspacePlacementMigration marks a workspace as draining on its source group.
func (store *Store) StartWorkspacePlacementMigration(ctx context.Context, request StartWorkspacePlacementMigrationRequest) (WorkspacePlacementMigration, error) {
	normalized, err := normalizeStartWorkspacePlacementMigrationRequest(request)
	if err != nil {
		return WorkspacePlacementMigration{}, err
	}
	if store == nil || store.db == nil {
		return WorkspacePlacementMigration{}, fmt.Errorf("workspace store database is not configured")
	}
	existing, err := loadWorkspacePlacementMigrationByIdempotency(ctx, store.db, normalized.IdempotencyKey)
	if err == nil {
		return existing, nil
	}
	if !errors.Is(err, storeerr.ErrNotFound) {
		return WorkspacePlacementMigration{}, err
	}
	migrationID, err := identifiers.NewEntityID("wpm")
	if err != nil {
		return WorkspacePlacementMigration{}, fmt.Errorf("generate workspace placement migration id: %w", err)
	}

	var migration WorkspacePlacementMigration
	err = coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		current, err := loadWorkspacePlacementForUpdate(ctx, tx, normalized.WorkspaceID)
		if err != nil {
			return err
		}
		if current.State != PlacementStateActive {
			return conflict(fmt.Sprintf("workspace %s placement must be active before migration, got %s", normalized.WorkspaceID, current.State))
		}
		if normalized.SourceGroupID != "" && normalized.SourceGroupID != current.WorkspaceGroupID {
			return conflict(fmt.Sprintf("workspace %s source group mismatch: current=%s requested=%s", normalized.WorkspaceID, current.WorkspaceGroupID, normalized.SourceGroupID))
		}
		sourceGroupID := current.WorkspaceGroupID
		if sourceGroupID == normalized.TargetGroupID {
			return conflict(fmt.Sprintf("workspace %s is already in group %s", normalized.WorkspaceID, normalized.TargetGroupID))
		}
		if err := requireActiveWorkspaceGroup(ctx, tx, normalized.TargetGroupID); err != nil {
			return err
		}
		activeCount, err := countActiveWorkspacePlacementMigrations(ctx, tx, normalized.WorkspaceID)
		if err != nil {
			return err
		}
		if activeCount > 0 {
			return conflict(fmt.Sprintf("workspace %s already has an active placement migration", normalized.WorkspaceID))
		}
		nextEpoch := current.PlacementEpoch + 1
		if _, err := tx.Exec(ctx, `
UPDATE workspace_placements
SET state = $2, placement_epoch = $3, updated_at = NOW()
WHERE workspace_id = $1
`, normalized.WorkspaceID, PlacementStateDraining, nextEpoch); err != nil {
			return fmt.Errorf("mark workspace %s placement draining from group %s: %w", normalized.WorkspaceID, sourceGroupID, err)
		}
		if _, err := tx.Exec(ctx, `
INSERT INTO workspace_placement_migrations (
  migration_id,
  workspace_id,
  source_group_id,
  target_group_id,
  state,
  placement_epoch_start,
  placement_epoch_current,
  requested_by,
  reason,
  idempotency_key,
  created_at,
  updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, NOW(), NOW())
`, migrationID, normalized.WorkspaceID, sourceGroupID, normalized.TargetGroupID, PlacementMigrationStateDraining, current.PlacementEpoch, nextEpoch, normalized.RequestedBy, normalized.Reason, normalized.IdempotencyKey); err != nil {
			return fmt.Errorf("insert workspace placement migration for workspace %s from %s to %s: %w", normalized.WorkspaceID, sourceGroupID, normalized.TargetGroupID, err)
		}
		loaded, err := loadWorkspacePlacementMigration(ctx, tx, migrationID)
		if err != nil {
			return err
		}
		migration = loaded
		return nil
	})
	if err != nil {
		return WorkspacePlacementMigration{}, err
	}
	return migration, nil
}

// MoveWorkspacePlacementMigration switches new traffic to the target group.
func (store *Store) MoveWorkspacePlacementMigration(ctx context.Context, migrationID string) (WorkspacePlacementMigration, error) {
	return store.transitionWorkspacePlacementMigration(ctx, migrationID, PlacementMigrationStateMoving, func(ctx context.Context, tx coredb.Tx, migration WorkspacePlacementMigration, current WorkspacePlacement) (int64, error) {
		if migration.State != PlacementMigrationStateDraining {
			return 0, conflict(fmt.Sprintf("workspace migration %s must be draining before move, got %s", migration.MigrationID, migration.State))
		}
		if current.State != PlacementStateDraining || current.WorkspaceGroupID != migration.SourceGroupID {
			return 0, conflict(fmt.Sprintf("workspace migration %s placement mismatch before move: group=%s state=%s", migration.MigrationID, current.WorkspaceGroupID, current.State))
		}
		if err := requireActiveWorkspaceGroup(ctx, tx, migration.TargetGroupID); err != nil {
			return 0, err
		}
		nextEpoch := current.PlacementEpoch + 1
		if _, err := tx.Exec(ctx, `
UPDATE workspace_placements
SET workspace_group_id = $2, state = $3, placement_epoch = $4, updated_at = NOW()
WHERE workspace_id = $1
`, migration.WorkspaceID, migration.TargetGroupID, PlacementStateMoving, nextEpoch); err != nil {
			return 0, fmt.Errorf("move workspace %s placement to group %s: %w", migration.WorkspaceID, migration.TargetGroupID, err)
		}
		return nextEpoch, nil
	})
}

// CompleteWorkspacePlacementMigration marks target group ownership active.
func (store *Store) CompleteWorkspacePlacementMigration(ctx context.Context, migrationID string) (WorkspacePlacementMigration, error) {
	return store.transitionWorkspacePlacementMigration(ctx, migrationID, PlacementMigrationStateCompleted, func(ctx context.Context, tx coredb.Tx, migration WorkspacePlacementMigration, current WorkspacePlacement) (int64, error) {
		if migration.State != PlacementMigrationStateMoving && migration.State != PlacementMigrationStateVerifying {
			return 0, conflict(fmt.Sprintf("workspace migration %s must be moving or verifying before complete, got %s", migration.MigrationID, migration.State))
		}
		if current.State != PlacementStateMoving || current.WorkspaceGroupID != migration.TargetGroupID {
			return 0, conflict(fmt.Sprintf("workspace migration %s placement mismatch before complete: group=%s state=%s", migration.MigrationID, current.WorkspaceGroupID, current.State))
		}
		nextEpoch := current.PlacementEpoch + 1
		if _, err := tx.Exec(ctx, `
UPDATE workspace_placements
SET state = $2, placement_epoch = $3, updated_at = NOW()
WHERE workspace_id = $1
`, migration.WorkspaceID, PlacementStateActive, nextEpoch); err != nil {
			return 0, fmt.Errorf("complete workspace %s placement migration %s: %w", migration.WorkspaceID, migration.MigrationID, err)
		}
		return nextEpoch, nil
	})
}

// RollbackWorkspacePlacementMigration restores the source group when the migration is not completed.
func (store *Store) RollbackWorkspacePlacementMigration(ctx context.Context, migrationID string) (WorkspacePlacementMigration, error) {
	return store.transitionWorkspacePlacementMigration(ctx, migrationID, PlacementMigrationStateRolledBack, func(ctx context.Context, tx coredb.Tx, migration WorkspacePlacementMigration, current WorkspacePlacement) (int64, error) {
		if migration.State == PlacementMigrationStateCompleted || migration.State == PlacementMigrationStateRolledBack {
			return 0, conflict(fmt.Sprintf("workspace migration %s cannot rollback from state %s", migration.MigrationID, migration.State))
		}
		nextEpoch := current.PlacementEpoch + 1
		if _, err := tx.Exec(ctx, `
UPDATE workspace_placements
SET workspace_group_id = $2, state = $3, placement_epoch = $4, updated_at = NOW()
WHERE workspace_id = $1
`, migration.WorkspaceID, migration.SourceGroupID, PlacementStateActive, nextEpoch); err != nil {
			return 0, fmt.Errorf("rollback workspace %s placement migration %s to group %s: %w", migration.WorkspaceID, migration.MigrationID, migration.SourceGroupID, err)
		}
		return nextEpoch, nil
	})
}

// GetActiveWorkspacePlacementMigration loads the unfinished migration for one workspace.
func (store *Store) GetActiveWorkspacePlacementMigration(ctx context.Context, workspaceID string) (*WorkspacePlacementMigration, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedWorkspaceID == "" {
		return nil, badRequest("workspaceId is required")
	}
	if store == nil || store.db == nil {
		return nil, fmt.Errorf("workspace store database is not configured")
	}
	migration, err := loadActiveWorkspacePlacementMigration(ctx, store.db, trimmedWorkspaceID)
	if errors.Is(err, storeerr.ErrNotFound) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &migration, nil
}

// GetWorkspacePlacementMigration loads one migration record.
func (store *Store) GetWorkspacePlacementMigration(ctx context.Context, migrationID string) (WorkspacePlacementMigration, error) {
	trimmedMigrationID := strings.TrimSpace(migrationID)
	if trimmedMigrationID == "" {
		return WorkspacePlacementMigration{}, badRequest("migrationId is required")
	}
	if store == nil || store.db == nil {
		return WorkspacePlacementMigration{}, fmt.Errorf("workspace store database is not configured")
	}
	return loadWorkspacePlacementMigration(ctx, store.db, trimmedMigrationID)
}

type workspacePlacementMigrationTransition func(context.Context, coredb.Tx, WorkspacePlacementMigration, WorkspacePlacement) (int64, error)

func (store *Store) transitionWorkspacePlacementMigration(ctx context.Context, migrationID string, nextState string, apply workspacePlacementMigrationTransition) (WorkspacePlacementMigration, error) {
	trimmedMigrationID := strings.TrimSpace(migrationID)
	if trimmedMigrationID == "" {
		return WorkspacePlacementMigration{}, badRequest("migrationId is required")
	}
	if store == nil || store.db == nil {
		return WorkspacePlacementMigration{}, fmt.Errorf("workspace store database is not configured")
	}
	var result WorkspacePlacementMigration
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		migration, err := loadWorkspacePlacementMigrationForUpdate(ctx, tx, trimmedMigrationID)
		if err != nil {
			return err
		}
		current, err := loadWorkspacePlacementForUpdate(ctx, tx, migration.WorkspaceID)
		if err != nil {
			return err
		}
		nextEpoch, err := apply(ctx, tx, migration, current)
		if err != nil {
			return err
		}
		completedExpr := "completed_at"
		if nextState == PlacementMigrationStateCompleted || nextState == PlacementMigrationStateRolledBack || nextState == PlacementMigrationStateFailed {
			completedExpr = "COALESCE(completed_at, NOW())"
		}
		if _, err := tx.Exec(ctx, fmt.Sprintf(`
UPDATE workspace_placement_migrations
SET state = $2,
    placement_epoch_current = $3,
    completed_at = %s,
    updated_at = NOW()
WHERE migration_id = $1
`, completedExpr), migration.MigrationID, nextState, nextEpoch); err != nil {
			return fmt.Errorf("update workspace placement migration %s to state %s: %w", migration.MigrationID, nextState, err)
		}
		loaded, err := loadWorkspacePlacementMigration(ctx, tx, migration.MigrationID)
		if err != nil {
			return err
		}
		result = loaded
		return nil
	})
	if err != nil {
		return WorkspacePlacementMigration{}, err
	}
	return result, nil
}

func normalizeStartWorkspacePlacementMigrationRequest(request StartWorkspacePlacementMigrationRequest) (StartWorkspacePlacementMigrationRequest, error) {
	normalized := StartWorkspacePlacementMigrationRequest{
		WorkspaceID:    strings.TrimSpace(request.WorkspaceID),
		SourceGroupID:  strings.TrimSpace(request.SourceGroupID),
		TargetGroupID:  strings.TrimSpace(request.TargetGroupID),
		RequestedBy:    strings.TrimSpace(request.RequestedBy),
		Reason:         strings.TrimSpace(request.Reason),
		IdempotencyKey: strings.TrimSpace(request.IdempotencyKey),
	}
	if normalized.WorkspaceID == "" {
		return StartWorkspacePlacementMigrationRequest{}, badRequest("workspaceId is required")
	}
	if normalized.TargetGroupID == "" {
		return StartWorkspacePlacementMigrationRequest{}, badRequest("targetGroupId is required")
	}
	if normalized.RequestedBy == "" {
		return StartWorkspacePlacementMigrationRequest{}, badRequest("requestedBy is required")
	}
	if normalized.Reason == "" {
		return StartWorkspacePlacementMigrationRequest{}, badRequest("reason is required")
	}
	if normalized.IdempotencyKey == "" {
		return StartWorkspacePlacementMigrationRequest{}, badRequest("idempotencyKey is required")
	}
	return normalized, nil
}

func loadWorkspacePlacementForUpdate(ctx context.Context, tx coredb.Tx, workspaceID string) (WorkspacePlacement, error) {
	var placement WorkspacePlacement
	if err := tx.QueryRow(ctx, `
SELECT workspace_id, workspace_group_id, state, placement_epoch, created_at, updated_at
FROM workspace_placements
WHERE workspace_id = $1
FOR UPDATE
`, workspaceID).Scan(&placement.WorkspaceID, &placement.WorkspaceGroupID, &placement.State, &placement.PlacementEpoch, &placement.CreatedAt, &placement.UpdatedAt); err != nil {
		if coredb.IsNotFound(err) {
			return WorkspacePlacement{}, storeerr.NotFound(fmt.Sprintf("workspace placement for workspace %s not found", workspaceID))
		}
		return WorkspacePlacement{}, fmt.Errorf("load workspace placement for workspace %s: %w", workspaceID, err)
	}
	return placement, nil
}

func requireActiveWorkspaceGroup(ctx context.Context, tx coredb.Tx, workspaceGroupID string) error {
	var state string
	if err := tx.QueryRow(ctx, `
SELECT state
FROM workspace_groups
WHERE workspace_group_id = $1
FOR UPDATE
`, workspaceGroupID).Scan(&state); err != nil {
		if coredb.IsNotFound(err) {
			return storeerr.NotFound(fmt.Sprintf("workspace group %s not found", workspaceGroupID))
		}
		return fmt.Errorf("load workspace group %s: %w", workspaceGroupID, err)
	}
	if state != WorkspaceGroupStateActive {
		return conflict(fmt.Sprintf("workspace group %s is not active: state=%s", workspaceGroupID, state))
	}
	return nil
}

func countActiveWorkspacePlacementMigrations(ctx context.Context, tx coredb.Tx, workspaceID string) (int64, error) {
	var count int64
	if err := tx.QueryRow(ctx, `
SELECT COUNT(*)
FROM workspace_placement_migrations
WHERE workspace_id = $1
  AND state IN ($2, $3, $4, $5, $6)
`, workspaceID, PlacementMigrationStateRequested, PlacementMigrationStatePreflight, PlacementMigrationStateDraining, PlacementMigrationStateMoving, PlacementMigrationStateVerifying).Scan(&count); err != nil {
		return 0, fmt.Errorf("count active workspace placement migrations for workspace %s: %w", workspaceID, err)
	}
	return count, nil
}