package seatbilling

import (
	"context"
	"fmt"
	"strings"
	"time"

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

func (store *Store) LoadSeatSummary(ctx context.Context, organizationID string, now time.Time) (SeatSummary, error) {
	db, err := store.requireDB()
	if err != nil {
		return SeatSummary{}, err
	}
	trimmedOrganizationID := strings.TrimSpace(organizationID)
	if trimmedOrganizationID == "" {
		return SeatSummary{}, storeerr.BadRequest("organizationId is required")
	}
	if now.IsZero() {
		now = time.Now().UTC()
	}

	entitlement, err := LoadEffectiveEntitlement(ctx, db, trimmedOrganizationID, now.UTC(), false)
	if err != nil {
		return SeatSummary{}, err
	}
	agentGrants, err := loadActiveAgentOwnerGrantSummary(ctx, db, trimmedOrganizationID, now.UTC())
	if err != nil {
		return SeatSummary{}, err
	}
	entitlement.OrganizationAgentLimit += agentGrants
	humanSeats, agents, err := loadSeatBillingUsage(ctx, db, trimmedOrganizationID)
	if err != nil {
		return SeatSummary{}, err
	}
	return SeatSummary{
		Entitlement:         entitlement,
		CurrentHumanSeats:   humanSeats,
		CurrentAgents:       agents,
		HumanSeatsOverLimit: humanSeats > entitlement.HumanSeatLimit,
		AgentsOverLimit:     agents > entitlement.OrganizationAgentLimit,
	}, nil
}

func loadSeatBillingUsage(ctx context.Context, queryer coredb.QueryRower, organizationID string) (int64, int64, error) {
	var humanSeats int64
	if err := queryer.QueryRow(ctx, `
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
`, organizationID).Scan(&humanSeats); err != nil {
		return 0, 0, fmt.Errorf("load seat billing human usage: %w", err)
	}

	agentQuery := `
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
`
	var agents int64
	if err := queryer.QueryRow(ctx, agentQuery, organizationID).Scan(&agents); err != nil {
		return 0, 0, fmt.Errorf("load seat billing agent usage: %w", err)
	}
	return humanSeats, agents, nil
}