package installation

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

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

// LoadActiveGrantScopesByInstallation returns active grant scopes keyed by app installation ID.
func (store *Store) LoadActiveGrantScopesByInstallation(ctx context.Context, installationIDs []string) (map[string][]string, error) {
	return LoadActiveGrantScopesByInstallation(ctx, store.db, installationIDs)
}

// LoadActiveGrantScopesByInstallation returns active grant scopes keyed by app installation ID.
func LoadActiveGrantScopesByInstallation(ctx context.Context, queryer coredb.Queryer, installationIDs []string) (map[string][]string, error) {
	normalizedInstallationIDs := UniqueTrimmedStrings(installationIDs)
	if len(normalizedInstallationIDs) == 0 {
		return map[string][]string{}, nil
	}

	cte, installationArgs, _, err := coredb.BuildStringValuesCTE("requested_installations", "installation_id", normalizedInstallationIDs, 1)
	if err != nil {
		return nil, fmt.Errorf("load service grants: %w", err)
	}

	rows, err := queryer.Query(ctx, fmt.Sprintf(`
WITH %s
SELECT sg.service_installation_id, sg.scope
FROM service_grants sg
WHERE sg.revoked_at IS NULL
  AND (sg.expires_at IS NULL OR sg.expires_at > CURRENT_TIMESTAMP)
  AND EXISTS (
      SELECT 1
      FROM requested_installations
      WHERE requested_installations.installation_id = sg.service_installation_id
  )
ORDER BY sg.service_installation_id ASC, sg.scope ASC
`, cte), installationArgs...)
	if err != nil {
		return nil, fmt.Errorf("load service grants: %w", err)
	}
	defer rows.Close()

	grantsByInstallation := make(map[string][]string, len(normalizedInstallationIDs))
	for _, installationID := range normalizedInstallationIDs {
		grantsByInstallation[installationID] = []string{}
	}

	for rows.Next() {
		var installationID string
		var scope string
		if err := rows.Scan(&installationID, &scope); err != nil {
			return nil, fmt.Errorf("scan service grant: %w", err)
		}
		installationID = strings.TrimSpace(installationID)
		scope = strings.TrimSpace(scope)
		if installationID == "" || scope == "" {
			continue
		}
		grantsByInstallation[installationID] = append(grantsByInstallation[installationID], scope)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate service grants: %w", err)
	}
	for installationID, scopes := range grantsByInstallation {
		grantsByInstallation[installationID] = SortedUniqueScopes(scopes)
	}
	return grantsByInstallation, nil
}

// SyncGrantScopes reconciles the active grant scopes for an installation against
// the canonical service grant ledger.
func SyncGrantScopes(ctx context.Context, tx coredb.Tx, installationID string, grantedByUserID string, scopes []string, now time.Time) error {
	installationID = strings.TrimSpace(installationID)
	grantedByUserID = strings.TrimSpace(grantedByUserID)
	desiredScopes := SortedUniqueScopes(scopes)
	desiredSet := make(map[string]struct{}, len(desiredScopes))
	for _, scope := range desiredScopes {
		desiredSet[scope] = struct{}{}
	}

	var installationRow struct {
		workspaceID       string
		serviceDefinition string
		serviceKind       string
		grantedByMemberID string
	}
	err := tx.QueryRow(ctx, `
SELECT si.workspace_id, si.service_definition_id, si.service_kind,
       COALESCE(si.created_by_member_id, '')
FROM service_installations si
WHERE si.id = $1
LIMIT 1
FOR UPDATE
`, installationID).Scan(
		&installationRow.workspaceID,
		&installationRow.serviceDefinition,
		&installationRow.serviceKind,
		&installationRow.grantedByMemberID,
	)
	if err != nil {
		return fmt.Errorf("load service installation for grant sync: %w", err)
	}
	grantedByMemberID := strings.TrimSpace(grantedByUserID)
	if grantedByMemberID == "" {
		grantedByMemberID = strings.TrimSpace(installationRow.grantedByMemberID)
	}

	rows, err := tx.Query(ctx, `
SELECT scope
FROM service_grants
WHERE service_installation_id = $1
  AND revoked_at IS NULL
FOR UPDATE
`, installationID)
	if err != nil {
		return fmt.Errorf("list active service grants: %w", err)
	}
	defer rows.Close()

	activeScopes := make([]string, 0)
	for rows.Next() {
		var scope string
		if err := rows.Scan(&scope); err != nil {
			return fmt.Errorf("scan active service grant: %w", err)
		}
		activeScopes = append(activeScopes, strings.TrimSpace(scope))
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate active service grants: %w", err)
	}

	revokeScopes := make([]string, 0)
	for _, scope := range activeScopes {
		if scope == "" {
			continue
		}
		if _, keep := desiredSet[scope]; keep {
			delete(desiredSet, scope)
			continue
		}
		revokeScopes = append(revokeScopes, scope)
	}
	if len(revokeScopes) > 0 {
		revokeArgs := []any{installationID, now}
		revokePlaceholders := make([]string, 0, len(revokeScopes))
		for _, scope := range revokeScopes {
			revokeArgs = append(revokeArgs, scope)
			revokePlaceholders = append(revokePlaceholders, fmt.Sprintf("$%d", len(revokeArgs)))
		}
		if _, err := tx.Exec(ctx, fmt.Sprintf(`
UPDATE service_grants
SET revoked_at = $2,
    updated_at = $2
WHERE service_installation_id = $1
  AND revoked_at IS NULL
  AND scope IN (%s)
`, strings.Join(revokePlaceholders, ", ")), revokeArgs...); err != nil {
			return fmt.Errorf("revoke service grants: %w", err)
		}
	}

	insertValues := make([]string, 0)
	insertArgs := make([]any, 0)
	for _, scope := range desiredScopes {
		if _, missing := desiredSet[scope]; !missing {
			continue
		}
		grantID, err := identifiers.NewEntityID("appgr")
		if err != nil {
			return fmt.Errorf("generate service grant id: %w", err)
		}
		base := len(insertArgs) + 1
		insertValues = append(insertValues, fmt.Sprintf("($%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d, $%d)", base, base+1, base+2, base+3, base+4, base+5, base+6, base+7, base+8, base+9, base+10, base+11, base+12, base+13))
		insertArgs = append(insertArgs, grantID, installationRow.workspaceID, installationRow.serviceDefinition, installationID, installationRow.serviceKind, scope, []byte(`{}`), int64(1), "", grantedByMemberID, now, nil, now, now)
	}
	if len(insertValues) > 0 {
		if _, err := tx.Exec(ctx, fmt.Sprintf(`
INSERT INTO service_grants (
    id, workspace_id, service_definition_id, service_installation_id,
    service_kind, scope, config, policy_version, policy_digest,
    granted_by_member_id, granted_at, expires_at, created_at, updated_at
)
VALUES %s
`, strings.Join(insertValues, ",\n")), insertArgs...); err != nil {
			return fmt.Errorf("insert service grants: %w", err)
		}
	}

	return nil
}