package app

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"strings"

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

type ServiceAuthorProfile struct {
	InstallationID string
	MemberID       string
	AuthorProfile  message.AuthorProfile
}
type ServiceAuthorProfileQueryer interface {
	Query(context.Context, string, ...any) (coredb.Rows, error)
}

func loadServiceAuthorProfilesByMemberID(ctx context.Context, queryer ServiceAuthorProfileQueryer, workspaceID string, memberIDs []string) (map[string]ServiceAuthorProfile, error) {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedWorkspaceID == "" {
		return nil, fmt.Errorf("workspace id is required")
	}

	normalizedMemberIDs := normalizeNonEmptyStrings(memberIDs)
	if len(normalizedMemberIDs) == 0 {
		return map[string]ServiceAuthorProfile{}, nil
	}

	requestedMemberIDsCTE, requestedMemberIDArgs, _, err := coredb.BuildStringValuesCTE("requested_member_ids", "member_id", normalizedMemberIDs, 2)
	if err != nil {
		return nil, fmt.Errorf("build requested member ids: %w", err)
	}

	query := fmt.Sprintf(`
WITH %s
SELECT
    m.id,
    si.id,
    si.metadata,
    sm.service_kind,
    sm.handle,
    m.display_name,
    sd.name,
    sd.slug,
    COALESCE(NULLIF(sm.avatar_url, ''), sd.avatar_url, ''),
    COALESCE(sa.principal_id, ''),
    COALESCE(sa.id, ''),
    COALESCE(sa.name, '')
FROM members m
LEFT JOIN service_members sm
  ON sm.workspace_id = m.workspace_id
 AND sm.member_id = m.id
 AND sm.service_kind IN ('app', 'service_account')
LEFT JOIN service_installations si
  ON si.id = sm.service_installation_id
 AND si.service_kind = sm.service_kind
LEFT JOIN service_definitions sd
  ON sd.id = sm.service_definition_id
 AND sd.service_kind = sm.service_kind
LEFT JOIN service_accounts sa
  ON sa.workspace_id = m.workspace_id
 AND sa.author_member_id = m.id
 AND sm.service_kind = 'service_account'
WHERE m.workspace_id = $1
  AND m.type = 'service'
  AND EXISTS (
      SELECT 1
      FROM requested_member_ids
      WHERE requested_member_ids.member_id = m.id
  )
`, requestedMemberIDsCTE)
	args := append([]any{trimmedWorkspaceID}, requestedMemberIDArgs...)

	rows, err := queryer.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("load service author profiles by member id: %w", err)
	}
	defer rows.Close()

	profiles := make(map[string]ServiceAuthorProfile, len(normalizedMemberIDs))
	for rows.Next() {
		profile, err := scanServiceAuthorProfile(rows)
		if err != nil {
			return nil, err
		}
		if profile.MemberID == "" {
			continue
		}
		profiles[profile.MemberID] = profile
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate service author profiles: %w", err)
	}
	return profiles, nil
}

func scanServiceAuthorProfile(scanner interface{ Scan(...any) error }) (ServiceAuthorProfile, error) {
	var profile ServiceAuthorProfile
	var installationID, serviceKind, handle sql.NullString
	var installationMetadata []byte
	var memberDisplayName, appName, serviceSlug, avatarURL sql.NullString
	var serviceAccountPrincipalID, serviceAccountID, serviceAccountName sql.NullString
	if err := scanner.Scan(
		&profile.MemberID,
		&installationID,
		&installationMetadata,
		&serviceKind,
		&handle,
		&memberDisplayName,
		&appName,
		&serviceSlug,
		&avatarURL,
		&serviceAccountPrincipalID,
		&serviceAccountID,
		&serviceAccountName,
	); err != nil {
		return ServiceAuthorProfile{}, fmt.Errorf("scan service author profile: %w", err)
	}
	profile.MemberID = strings.TrimSpace(profile.MemberID)
	profile.InstallationID = trimmedNullString(installationID)
	serviceKindValue := trimmedNullString(serviceKind)
	handleValue := preferredDisplayName(trimmedNullString(handle), profile.MemberID)
	targetID := handleValue
	displayName := preferredDisplayName(trimmedNullString(memberDisplayName), trimmedNullString(appName), targetID, profile.MemberID)
	serviceSlugValue := trimmedNullString(serviceSlug)
	principalID := preferredDisplayName(profile.InstallationID, profile.MemberID)
	if serviceKindValue == message.AuthorServiceKindServiceAccount {
		principalID = preferredDisplayName(trimmedNullString(serviceAccountPrincipalID), profile.InstallationID, profile.MemberID)
		accountID := preferredDisplayName(trimmedNullString(serviceAccountID), principalID, profile.MemberID)
		targetID = "service-account:" + accountID
		displayName = preferredDisplayName(trimmedNullString(serviceAccountName), trimmedNullString(memberDisplayName), trimmedNullString(appName), profile.MemberID)
		serviceSlugValue = accountID
	} else if serviceAuthorUsesHandlePresentation(serviceSlugValue) {
		displayName = preferredDisplayName(targetID, displayName, profile.MemberID)
	}
	if profile.InstallationID == "" {
		serviceKindValue = ""
	}
	profile.AuthorProfile = message.AuthorProfile{
		ActorType:      "service",
		PrincipalID:    principalID,
		MemberID:       profile.MemberID,
		TargetID:       targetID,
		DisplayName:    displayName,
		AvatarURL:      trimmedNullString(avatarURL),
		ServiceKind:    serviceKindValue,
		ServiceSlug:    serviceSlugValue,
		ServiceBuiltin: serviceInstallationIsBuiltin(installationMetadata),
	}
	return profile, nil
}

func serviceInstallationIsBuiltin(metadata []byte) bool {
	if len(metadata) == 0 {
		return false
	}
	var value struct {
		Builtin bool `json:"builtin"`
	}
	return json.Unmarshal(metadata, &value) == nil && value.Builtin
}

func LoadServiceAuthorProfilesByMemberID(ctx context.Context, queryer interface {
	Query(context.Context, string, ...any) (coredb.Rows, error)
}, workspaceID string, memberIDs []string) (map[string]ServiceAuthorProfile, error) {
	return loadServiceAuthorProfilesByMemberID(ctx, queryer, workspaceID, memberIDs)
}

func attachServiceAuthorProfilePresentationWithQueryer(ctx context.Context, queryer interface {
	Query(context.Context, string, ...any) (coredb.Rows, error)
}, workspaceID string, messages []message.AppMessage) error {
	if len(messages) == 0 {
		return nil
	}

	memberIDs := make([]string, 0, len(messages))
	for _, appMessage := range messages {
		if strings.TrimSpace(appMessage.AuthorActorType()) != message.AuthorActorTypeService {
			continue
		}
		memberID := strings.TrimSpace(appMessage.AuthorMemberID())
		if memberID == "" {
			continue
		}
		memberIDs = append(memberIDs, memberID)
	}
	if len(memberIDs) == 0 {
		return nil
	}

	profiles, err := loadServiceAuthorProfilesByMemberID(ctx, queryer, workspaceID, memberIDs)
	if err != nil {
		return err
	}
	for i := range messages {
		if strings.TrimSpace(messages[i].AuthorActorType()) != message.AuthorActorTypeService {
			continue
		}
		memberID := strings.TrimSpace(messages[i].AuthorMemberID())
		profile, ok := profiles[memberID]
		if !ok {
			messages[i].ApplyAuthorProfile(message.AuthorProfile{
				ActorType:   "service",
				PrincipalID: strings.TrimSpace(messages[i].AuthorPrincipalID()),
				MemberID:    memberID,
				TargetID:    memberID,
				DisplayName: preferredDisplayName(messages[i].AuthorDisplayName(), memberID),
			})
			continue
		}
		messages[i].ApplyAuthorProfile(profile.AuthorProfile)
	}
	return nil
}

func (store *Store) AttachServiceAuthorProfilePresentation(ctx context.Context, workspaceID string, messages []message.AppMessage) error {
	return attachServiceAuthorProfilePresentationWithQueryer(ctx, store.db, workspaceID, messages)
}

func serviceAuthorUsesHandlePresentation(serviceSlug string) bool {
	switch strings.ToLower(strings.TrimSpace(serviceSlug)) {
	case "github", "gitea":
		return true
	default:
		return false
	}
}