package plan

import (
	"context"
	"fmt"

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

func loadOrganizationUsage(ctx context.Context, queryer coredb.QueryRower, organizationID string, entitlement seatbilling.SeatEntitlement) (map[MetricKey]int64, error) {
	usage := make(map[MetricKey]int64, len(quotaMetrics))
	for _, metric := range quotaMetrics {
		value, err := loadOrganizationMetricUsageForEntitlement(ctx, queryer, organizationID, metric, entitlement)
		if err != nil {
			return nil, err
		}
		usage[metric] = value
	}
	return usage, nil
}

func loadOrganizationMetricUsageForEntitlement(ctx context.Context, queryer coredb.QueryRower, organizationID string, metric MetricKey, entitlement seatbilling.SeatEntitlement) (int64, error) {
	if metric == MetricAgents {
		return loadOrganizationMaxOwnedAgentUsage(ctx, queryer, organizationID)
	}
	return loadOrganizationMetricUsage(ctx, queryer, organizationID, metric)
}

func loadAgentQuotaUsageForOwner(ctx context.Context, queryer coredb.QueryRower, organizationID string, ownerUserID string) (int64, error) {
	return loadOrganizationOwnedAgentUsage(ctx, queryer, organizationID, ownerUserID)
}

func loadOrganizationMetricUsage(ctx context.Context, queryer coredb.QueryRower, organizationID string, metric MetricKey) (int64, error) {
	var query string
	switch metric {
	case MetricWorkspaces:
		query = `
SELECT COUNT(*)
FROM workspaces
WHERE organization_id = $1
`
	case MetricSeats:
		query = `
SELECT COUNT(DISTINCT user_id)
FROM (
  SELECT om.user_id AS user_id
  FROM organization_members om
  WHERE om.organization_id = $1
    AND om.status = 'active'
    AND om.user_id IS NOT NULL
    AND om.user_id <> ''

  UNION

  SELECT m.user_id AS user_id
  FROM members m
  JOIN workspaces w ON w.id = m.workspace_id
  WHERE w.organization_id = $1
    AND m.type = 'human'
    AND m.status = 'active'
    AND m.user_id IS NOT NULL
    AND m.user_id <> ''
) seats
`
	case MetricMachines:
		query = `
SELECT COUNT(*)
FROM machines m
JOIN workspaces w ON w.id = m.workspace_id
WHERE w.organization_id = $1
  AND m.status = 'active'
`
	case MetricAgents:
		query = `
SELECT COUNT(*)
FROM agents a
JOIN workspaces w ON w.id = a.workspace_id
WHERE w.organization_id = $1
  AND a.status <> 'archived'
`
	case MetricChannels:
		query = `
SELECT COUNT(*)
FROM scopes s
JOIN scope_channel_profiles p ON p.scope_id = s.id
JOIN workspaces w ON w.id = s.workspace_id
WHERE w.organization_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'
`
	default:
		return 0, fmt.Errorf("unknown quota metric %s", metric)
	}

	var value int64
	if err := queryer.QueryRow(ctx, query, organizationID).Scan(&value); err != nil {
		return 0, fmt.Errorf("load organization %s usage: %w", metric, err)
	}
	return value, nil
}

func loadOrganizationOwnedAgentUsage(ctx context.Context, queryer coredb.QueryRower, organizationID string, ownerUserID string) (int64, error) {
	var value int64
	if err := queryer.QueryRow(ctx, `
SELECT COUNT(*)
FROM agents a
JOIN workspaces w ON w.id = a.workspace_id
JOIN machines mac
  ON mac.workspace_id = a.workspace_id
 AND mac.id = a.machine_id
WHERE w.organization_id = $1
  AND mac.created_by_user_id = $2
  AND a.status <> 'archived'
`, organizationID, ownerUserID).Scan(&value); err != nil {
		return 0, fmt.Errorf("load organization owner agent usage: %w", err)
	}
	return value, nil
}

func loadOrganizationMaxOwnedAgentUsage(ctx context.Context, queryer coredb.QueryRower, organizationID string) (int64, error) {
	var value int64
	if err := queryer.QueryRow(ctx, `
SELECT COALESCE(MAX(owner_agents.agent_count), 0)
FROM (
  SELECT mac.created_by_user_id, COUNT(*) AS agent_count
  FROM agents a
  JOIN workspaces w ON w.id = a.workspace_id
  JOIN machines mac
    ON mac.workspace_id = a.workspace_id
   AND mac.id = a.machine_id
  WHERE w.organization_id = $1
    AND a.status <> 'archived'
    AND mac.created_by_user_id IS NOT NULL
    AND mac.created_by_user_id <> ''
  GROUP BY mac.created_by_user_id
) owner_agents
`, organizationID).Scan(&value); err != nil {
		return 0, fmt.Errorf("load organization max owned agent usage: %w", err)
	}
	return value, nil
}

func loadWorkspaceUsage(ctx context.Context, queryer coredb.QueryRower, workspaceID string) (WorkspaceUsage, error) {
	var usage WorkspaceUsage
	if err := queryer.QueryRow(ctx, `
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).Scan(&usage.MachineCount, &usage.AgentCount, &usage.ChannelCount); err != nil {
		return WorkspaceUsage{}, fmt.Errorf("load workspace usage: %w", err)
	}
	return usage, nil
}