package serviceactor

import (
	"context"
	"database/sql"
	"fmt"

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

// InstallationSummary joins a service installation with its definition and
// workspace member projection for product-specific facades.
type InstallationSummary struct {
	Installation Installation
	Definition   Definition
	Member       Member
}

// ListInstallationSummaries lists service installations with their definition
// and member projections for one workspace and optional kind.
func (store *Store) ListInstallationSummaries(ctx context.Context, workspaceID string, serviceKind string) ([]InstallationSummary, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return nil, err
	}
	return listInstallationSummaries(ctx, queryer, workspaceID, serviceKind)
}

// LoadInstallationSummary loads one service installation summary by workspace
// and installation id.
func (store *Store) LoadInstallationSummary(ctx context.Context, workspaceID string, serviceInstallationID string) (InstallationSummary, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return InstallationSummary{}, err
	}
	return loadInstallationSummary(ctx, queryer, workspaceID, serviceInstallationID, "")
}

// LoadInstallationSummaryByKind loads one service installation summary by
// workspace, installation id, and kind.
func (store *Store) LoadInstallationSummaryByKind(ctx context.Context, workspaceID string, serviceInstallationID string, serviceKind string) (InstallationSummary, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return InstallationSummary{}, err
	}
	return loadInstallationSummary(ctx, queryer, workspaceID, serviceInstallationID, serviceKind)
}

// LoadInstallationSummaryByKind loads one summary through the caller's
// transaction. Direct create commands use this to build their response before
// commit so a failed response-shaping read cannot strand one-time credentials.
func (store *TxStore) LoadInstallationSummaryByKind(ctx context.Context, workspaceID string, serviceInstallationID string, serviceKind string) (InstallationSummary, error) {
	queryer, err := queryerFromTxStore(store)
	if err != nil {
		return InstallationSummary{}, err
	}
	return loadInstallationSummary(ctx, queryer, workspaceID, serviceInstallationID, serviceKind)
}

// LoadInstallationSummaryByKindForUpdate is the locking current-read variant
// for transactions that waited on other locks: the ordinary summary read
// can still resolve a pre-wait RR snapshot.
func (store *TxStore) LoadInstallationSummaryByKindForUpdate(ctx context.Context, workspaceID string, serviceInstallationID string, serviceKind string) (InstallationSummary, error) {
	queryer, err := queryerFromTxStore(store)
	if err != nil {
		return InstallationSummary{}, err
	}
	query := installationSummarySelectSQL() + `
WHERE si.workspace_id = $1
  AND si.id = $2
`
	args := []any{trim(workspaceID), trim(serviceInstallationID)}
	if trim(serviceKind) != "" {
		query += `  AND si.service_kind = $3
`
		args = append(args, trim(serviceKind))
	}
	query += `LIMIT 1
FOR UPDATE`
	row := queryer.QueryRow(ctx, query, args...)
	summary, err := scanInstallationSummary(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return InstallationSummary{}, notFound("service installation summary not found")
		}
		return InstallationSummary{}, fmt.Errorf("load service installation summary for update: %w", err)
	}
	return summary, nil
}

func listInstallationSummaries(ctx context.Context, queryer coredb.Queryer, workspaceID string, serviceKind string) ([]InstallationSummary, error) {
	if trim(workspaceID) == "" {
		return nil, badRequest("workspaceId is required")
	}
	query := installationSummarySelectSQL() + `
WHERE si.workspace_id = $1`
	args := []any{trim(workspaceID)}
	if trim(serviceKind) != "" {
		query += `
  AND si.service_kind = $2`
		args = append(args, trim(serviceKind))
	}
	query += `
ORDER BY si.created_at ASC, si.id ASC`
	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list service installation summaries: %w", err)
	}
	defer rows.Close()
	summaries := make([]InstallationSummary, 0)
	for rows.Next() {
		summary, err := scanInstallationSummary(rows)
		if err != nil {
			return nil, fmt.Errorf("scan service installation summary: %w", err)
		}
		summaries = append(summaries, summary)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate service installation summaries: %w", err)
	}
	return summaries, nil
}

func loadInstallationSummary(ctx context.Context, queryer coredb.QueryRower, workspaceID string, serviceInstallationID string, serviceKind string) (InstallationSummary, error) {
	query := installationSummarySelectSQL() + `
WHERE si.workspace_id = $1
  AND si.id = $2
`
	args := []any{trim(workspaceID), trim(serviceInstallationID)}
	if trim(serviceKind) != "" {
		query += `  AND si.service_kind = $3
`
		args = append(args, trim(serviceKind))
	}
	row := queryer.QueryRow(ctx, query, args...)
	summary, err := scanInstallationSummary(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return InstallationSummary{}, notFound("service installation summary not found")
		}
		return InstallationSummary{}, fmt.Errorf("load service installation summary: %w", err)
	}
	return summary, nil
}

func installationSummarySelectSQL() string {
	return `
SELECT
       si.id, si.workspace_id, si.service_definition_id, si.service_kind,
       si.instance_key, si.ownership_kind, COALESCE(si.owner_member_id, ''),
       si.status, si.installed_version, si.config, si.metadata,
       si.created_by_member_id, si.created_at, si.updated_at, si.disabled_at,
       sd.id, sd.service_kind, sd.slug, sd.name, sd.description, sd.avatar_url,
       sd.visibility, sd.developer_id,
       sd.latest_version, sd.manifest, sd.config, sd.status, sd.created_at,
       sd.updated_at,
       sm.id, sm.workspace_id, sm.service_definition_id,
       sm.service_installation_id, sm.member_id, sm.service_kind, sm.handle,
       sm.display_name, sm.avatar_url, sm.status, sm.created_at, sm.updated_at
FROM service_installations si
JOIN service_definitions sd ON sd.id = si.service_definition_id
JOIN service_members sm ON sm.service_installation_id = si.id
`
}

func scanInstallationSummary(row coredb.Row) (InstallationSummary, error) {
	var summary InstallationSummary
	var installationDisabledAt sql.NullTime
	if err := row.Scan(
		&summary.Installation.ID,
		&summary.Installation.WorkspaceID,
		&summary.Installation.ServiceDefinitionID,
		&summary.Installation.ServiceKind,
		&summary.Installation.InstanceKey,
		&summary.Installation.OwnershipKind,
		&summary.Installation.OwnerMemberID,
		&summary.Installation.Status,
		&summary.Installation.InstalledVersion,
		&summary.Installation.Config,
		&summary.Installation.Metadata,
		&summary.Installation.CreatedByMemberID,
		&summary.Installation.CreatedAt,
		&summary.Installation.UpdatedAt,
		&installationDisabledAt,
		&summary.Definition.ID,
		&summary.Definition.ServiceKind,
		&summary.Definition.Slug,
		&summary.Definition.Name,
		&summary.Definition.Description,
		&summary.Definition.AvatarURL,
		&summary.Definition.Visibility,
		&summary.Definition.DeveloperID,
		&summary.Definition.LatestVersion,
		&summary.Definition.Manifest,
		&summary.Definition.Config,
		&summary.Definition.Status,
		&summary.Definition.CreatedAt,
		&summary.Definition.UpdatedAt,
		&summary.Member.ID,
		&summary.Member.WorkspaceID,
		&summary.Member.ServiceDefinitionID,
		&summary.Member.ServiceInstallationID,
		&summary.Member.MemberID,
		&summary.Member.ServiceKind,
		&summary.Member.Handle,
		&summary.Member.DisplayName,
		&summary.Member.AvatarURL,
		&summary.Member.Status,
		&summary.Member.CreatedAt,
		&summary.Member.UpdatedAt,
	); err != nil {
		return InstallationSummary{}, err
	}
	summary.Installation.ID = trim(summary.Installation.ID)
	summary.Installation.WorkspaceID = trim(summary.Installation.WorkspaceID)
	summary.Installation.ServiceDefinitionID = trim(summary.Installation.ServiceDefinitionID)
	summary.Installation.ServiceKind = trim(summary.Installation.ServiceKind)
	summary.Installation.OwnershipKind = trim(summary.Installation.OwnershipKind)
	if summary.Installation.OwnershipKind == "" {
		summary.Installation.OwnershipKind = OwnershipKindWorkspace
	}
	summary.Installation.OwnerMemberID = trim(summary.Installation.OwnerMemberID)
	summary.Installation.Status = trim(summary.Installation.Status)
	summary.Installation.DisabledAt = ptrFromNullTime(installationDisabledAt)
	summary.Definition.ID = trim(summary.Definition.ID)
	summary.Definition.ServiceKind = trim(summary.Definition.ServiceKind)
	summary.Definition.Slug = trim(summary.Definition.Slug)
	summary.Definition.Name = trim(summary.Definition.Name)
	summary.Definition.Status = trim(summary.Definition.Status)
	summary.Member.ID = trim(summary.Member.ID)
	summary.Member.WorkspaceID = trim(summary.Member.WorkspaceID)
	summary.Member.ServiceDefinitionID = trim(summary.Member.ServiceDefinitionID)
	summary.Member.ServiceInstallationID = trim(summary.Member.ServiceInstallationID)
	summary.Member.MemberID = trim(summary.Member.MemberID)
	summary.Member.ServiceKind = trim(summary.Member.ServiceKind)
	summary.Member.Handle = trim(summary.Member.Handle)
	summary.Member.Status = trim(summary.Member.Status)
	return summary, nil
}