package extension

import (
	"context"
	"fmt"
)

// CatalogReconcileCandidate is one active (workspace, Agent) pair whose current
// Catalog may depend on a product-App lifecycle state.
type CatalogReconcileCandidate struct {
	WorkspaceID string
	AgentID     string
}

// ListCatalogReconcileCandidates returns active Agents that either already have
// a durable Catalog row or belong to a workspace with an effective App-hosted
// Extension candidate. The first arm keeps previously materialized Agents
// eligible after a selection/App/connection is disabled or removed, so
// reconciliation can advance them to an empty Catalog; the second arm discovers
// never-materialized default-allow Agents without hard-coding provider Apps.
func (store *Store) ListCatalogReconcileCandidates(ctx context.Context) ([]CatalogReconcileCandidate, error) {
	if store == nil || store.db == nil {
		return nil, fmt.Errorf("extension store is not configured")
	}
	rows, err := store.db.Query(ctx, `
SELECT DISTINCT a.workspace_id, a.id
FROM agents a
JOIN members m
  ON m.id = a.member_id
 AND m.workspace_id = a.workspace_id
 AND m.type = 'agent'
 AND m.status = 'active'
JOIN machines machine
  ON machine.id = a.machine_id
 AND machine.workspace_id = a.workspace_id
 AND machine.status = 'active'
WHERE a.status <> 'archived'
  AND (
    EXISTS (
      SELECT 1
      FROM agent_extension_catalogs c
      WHERE c.workspace_id = a.workspace_id AND c.agent_id = a.id
    )
    OR EXISTS (
      SELECT 1
      FROM service_installation_extensions selection
      JOIN service_installations si
        ON si.id = selection.service_installation_id
      JOIN service_connections sc
        ON sc.service_installation_id = si.id
       AND sc.status = 'active'
      WHERE selection.workspace_id = a.workspace_id
        AND selection.status = 'active'
        AND si.workspace_id = selection.workspace_id
        AND si.service_definition_id = selection.service_definition_id
        AND si.status = 'active'
        AND si.service_kind = 'app'
        AND sc.workspace_id = selection.workspace_id
        AND sc.service_definition_id = selection.service_definition_id
        AND sc.service_kind = 'app'
    )
  )
ORDER BY a.workspace_id, a.id`)
	if err != nil {
		return nil, fmt.Errorf("list catalog reconcile candidates: %w", err)
	}
	defer rows.Close()
	var result []CatalogReconcileCandidate
	for rows.Next() {
		var c CatalogReconcileCandidate
		if err := rows.Scan(&c.WorkspaceID, &c.AgentID); err != nil {
			return nil, fmt.Errorf("scan catalog reconcile candidate: %w", err)
		}
		// Preserve both identities raw. TiDB/MySQL PAD SPACE comparisons can
		// match a trailing-space durable identity; ApplyExtensionCatalogChange
		// performs the byte-exact ownership fence for each candidate.
		result = append(result, c)
	}
	return result, rows.Err()
}