package app

import (
	"context"
	"fmt"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/workspace"
)

type workspaceUsageRow struct {
	MachineCount int64 `db:"machine_count"`
	AgentCount   int64 `db:"agent_count"`
	ChannelCount int64 `db:"channel_count"`
}

func (store *Store) LoadWorkspaceUsageForUser(ctx context.Context, userID string, workspaceID string) (workspace.WorkspaceUsage, error) {
	if _, err := store.GetWorkspaceForUser(ctx, userID, workspaceID); err != nil {
		return workspace.WorkspaceUsage{}, err
	}

	var row workspaceUsageRow
	if err := coredb.Get(ctx, store.db, &row, `
SELECT
    (SELECT COUNT(*) FROM machines WHERE workspace_id = $1 AND status = 'active') AS machine_count,
    (SELECT COUNT(*) FROM agents WHERE workspace_id = $1 AND status <> 'archived') AS agent_count,
    (
        SELECT COUNT(*)
        FROM scopes s
        JOIN scope_channel_profiles p ON p.scope_id = s.id
        WHERE s.workspace_id = $1
          AND s.type = 'channel'
          AND s.status = 'active'
          AND s.archived_at IS NULL
          AND p.is_all_channel = FALSE
          AND p.subsystem = 'im'
    ) AS channel_count
`, workspaceID); err != nil {
		return workspace.WorkspaceUsage{}, fmt.Errorf("load workspace usage: %w", err)
	}
	return workspace.WorkspaceUsage{
		MachineCount: row.MachineCount,
		AgentCount:   row.AgentCount,
		ChannelCount: row.ChannelCount,
	}, nil
}