package serviceactor

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

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

// Member links a service installation to its visible workspace member.
type Member struct {
	ID                    string
	WorkspaceID           string
	ServiceDefinitionID   string
	ServiceInstallationID string
	MemberID              string
	ServiceKind           string
	Handle                string
	DisplayName           string
	AvatarURL             string
	Status                string
	CreatedAt             time.Time
	UpdatedAt             time.Time
}

// MemberUpsert describes a service member write.
type MemberUpsert struct {
	ID                    string
	WorkspaceID           string
	ServiceDefinitionID   string
	ServiceInstallationID string
	MemberID              string
	ServiceKind           string
	Handle                string
	DisplayName           string
	AvatarURL             string
	Status                string
	Now                   time.Time
}

// UpsertMember creates or updates a service member row.
func (store *Store) UpsertMember(ctx context.Context, params MemberUpsert) (Member, error) {
	if _, err := execerFromStore(store); err != nil {
		return Member{}, err
	}
	var member Member
	err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var err error
		member, err = upsertMember(ctx, tx, params)
		return err
	})
	return member, err
}

// UpsertMember creates or updates a service member row in the transaction.
func (store *TxStore) UpsertMember(ctx context.Context, params MemberUpsert) (Member, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return Member{}, err
	}
	return upsertMember(ctx, execer, params)
}

// LoadMemberByInstallation loads the service member for one installation.
func (store *Store) LoadMemberByInstallation(ctx context.Context, serviceInstallationID string) (Member, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return Member{}, err
	}
	return loadMemberByInstallation(ctx, queryer, serviceInstallationID)
}

// LoadMemberByInstallation loads the service member for one installation in the transaction.
func (store *TxStore) LoadMemberByInstallation(ctx context.Context, serviceInstallationID string) (Member, error) {
	execer, err := execerFromTxStore(store)
	if err != nil {
		return Member{}, err
	}
	return loadMemberByInstallation(ctx, execer, serviceInstallationID)
}

// LoadMemberByWorkspaceHandle loads a service member by workspace, kind, and handle.
func (store *Store) LoadMemberByWorkspaceHandle(ctx context.Context, workspaceID string, serviceKind string, handle string) (Member, error) {
	queryer, err := queryerFromStore(store)
	if err != nil {
		return Member{}, err
	}
	return loadMemberByWorkspaceHandle(ctx, queryer, workspaceID, serviceKind, handle)
}

func upsertMember(ctx context.Context, execer coredb.QueryExecer, params MemberUpsert) (Member, error) {
	memberRecordID := trim(params.ID)
	workspaceID := trim(params.WorkspaceID)
	definitionID := trim(params.ServiceDefinitionID)
	installationID := trim(params.ServiceInstallationID)
	memberID := trim(params.MemberID)
	serviceKind := trim(params.ServiceKind)
	handle := trim(params.Handle)
	if memberRecordID == "" || workspaceID == "" || definitionID == "" || installationID == "" || memberID == "" || serviceKind == "" || handle == "" {
		return Member{}, badRequest("service member id, workspaceId, serviceDefinitionId, serviceInstallationId, memberId, kind, and handle are required")
	}
	status := trim(params.Status)
	if status == "" {
		status = StatusActive
	}
	now := params.Now.UTC()
	if now.IsZero() {
		now = time.Now().UTC()
	}

	installation, found, err := findInstallationByIDForUpdate(ctx, execer, installationID)
	if err != nil {
		return Member{}, err
	}
	if !found || !installationIdentityMatches(installation, workspaceID, definitionID, serviceKind, installation.InstanceKey) {
		return Member{}, conflict("Service member installation must belong to the same workspace, definition, and kind.")
	}
	if err := validateInstallationCreatorSubstrate(ctx, execer, workspaceID, installation.CreatedByMemberID, installation.CreatedByActorType, installation.CreatedByActorID); err != nil {
		return Member{}, err
	}
	if err := validateServiceMemberSubstrate(ctx, execer, workspaceID, memberID); err != nil {
		return Member{}, err
	}

	existingByID, foundByID, err := findMemberForUpdate(ctx, execer, `id = $1`, memberRecordID)
	if err != nil {
		return Member{}, err
	}
	existingByInstallation, foundByInstallation, err := findMemberForUpdate(ctx, execer, `service_installation_id = $1`, installationID)
	if err != nil {
		return Member{}, err
	}
	existingByMember, foundByMember, err := findMemberForUpdate(ctx, execer, `member_id = $1`, memberID)
	if err != nil {
		return Member{}, err
	}
	var existingByHandle Member
	var foundByHandle bool
	if serviceKind == KindServiceAccount {
		handleKey := memberHandleNaturalKey(serviceKind, handle)
		existingByHandle, foundByHandle, err = findMemberForUpdate(ctx, execer, `workspace_id = $1 AND service_kind = $2 AND normalized_handle = $3`, workspaceID, serviceKind, handleKey)
		if err != nil {
			return Member{}, err
		}
	}
	// A repeated canonical App upsert may supply a fresh service-member row id
	// while synchronizing the same installation. Preserve the already-bound row
	// id only when the requested id is otherwise unused and the locked
	// installation row has exactly the same immutable substrate.
	if serviceKind == KindApp && !foundByID && foundByInstallation && memberSubstrateIdentityMatches(existingByInstallation, workspaceID, definitionID, installationID, memberID, serviceKind) {
		memberRecordID = existingByInstallation.ID
	}
	for _, candidate := range []struct {
		identity string
		member   Member
		found    bool
	}{
		{identity: "id", member: existingByID, found: foundByID},
		{identity: "installation", member: existingByInstallation, found: foundByInstallation},
		{identity: "member", member: existingByMember, found: foundByMember},
		{identity: "handle", member: existingByHandle, found: foundByHandle},
	} {
		if !candidate.found {
			continue
		}
		if candidate.member.ID != memberRecordID {
			return Member{}, conflict(fmt.Sprintf("Service member %s identity is already bound to another id.", candidate.identity))
		}
		if !memberSubstrateIdentityMatches(candidate.member, workspaceID, definitionID, installationID, memberID, serviceKind) {
			return Member{}, conflict("Service member id is already bound to another substrate identity.")
		}
	}

	if foundByID || foundByInstallation || foundByMember || foundByHandle {
		if _, err := execer.Exec(ctx, `
UPDATE service_members
SET handle = $7,
    display_name = $8,
    avatar_url = $9,
    status = $10,
    updated_at = $11
WHERE id = $1
  AND workspace_id = $2
  AND service_definition_id = $3
  AND service_installation_id = $4
  AND member_id = $5
  AND service_kind = $6
`, memberRecordID, workspaceID, definitionID, installationID, memberID, serviceKind, handle, trim(params.DisplayName), trim(params.AvatarURL), status, now); err != nil {
			return Member{}, fmt.Errorf("update service member: %w", mapMemberWriteError(err))
		}
		return loadMemberByID(ctx, execer, memberRecordID)
	}

	if _, err := execer.Exec(ctx, `
INSERT INTO service_members (
    id, workspace_id, service_definition_id, service_installation_id,
    member_id, service_kind, handle, display_name, avatar_url, status,
    created_at, updated_at
) VALUES (
    $1, $2, $3, $4,
    $5, $6, $7, $8, $9, $10,
    $11, $11
)
`, memberRecordID, workspaceID, definitionID, installationID, memberID, serviceKind, handle, trim(params.DisplayName), trim(params.AvatarURL), status, now); err != nil {
		return Member{}, fmt.Errorf("insert service member: %w", mapMemberWriteError(err))
	}
	return loadMemberByID(ctx, execer, memberRecordID)
}

func loadMemberByID(ctx context.Context, queryer coredb.QueryRower, memberRecordID string) (Member, error) {
	row := queryer.QueryRow(ctx, memberSelectSQL()+` WHERE id = $1`, trim(memberRecordID))
	member, err := scanMember(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return Member{}, notFound("service member not found")
		}
		return Member{}, fmt.Errorf("load service member: %w", err)
	}
	return member, nil
}

func loadMemberByInstallation(ctx context.Context, queryer coredb.QueryRower, serviceInstallationID string) (Member, error) {
	member, found, err := findMemberByInstallation(ctx, queryer, serviceInstallationID)
	if err != nil {
		return Member{}, err
	}
	if !found {
		return Member{}, notFound("service member not found")
	}
	return member, nil
}

func findMemberByInstallation(ctx context.Context, queryer coredb.QueryRower, serviceInstallationID string) (Member, bool, error) {
	row := queryer.QueryRow(ctx, memberSelectSQL()+` WHERE service_installation_id = $1`, trim(serviceInstallationID))
	member, err := scanMember(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return Member{}, false, nil
		}
		return Member{}, false, fmt.Errorf("load service member by installation: %w", err)
	}
	return member, true, nil
}

func findMemberForUpdate(ctx context.Context, queryer coredb.QueryRower, predicate string, args ...any) (Member, bool, error) {
	row := queryer.QueryRow(ctx, memberSelectSQL()+` WHERE `+predicate+` FOR UPDATE`, args...)
	member, err := scanMember(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return Member{}, false, nil
		}
		return Member{}, false, fmt.Errorf("lock service member: %w", err)
	}
	return member, true, nil
}

func loadMemberByWorkspaceHandle(ctx context.Context, queryer coredb.QueryRower, workspaceID string, serviceKind string, handle string) (Member, error) {
	serviceKind = trim(serviceKind)
	handleKey := memberHandleNaturalKey(serviceKind, handle)
	row := queryer.QueryRow(ctx, memberSelectSQL()+` WHERE workspace_id = $1 AND service_kind = $2 AND normalized_handle = $3`, trim(workspaceID), serviceKind, handleKey)
	member, err := scanMember(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return Member{}, notFound("service member not found")
		}
		return Member{}, fmt.Errorf("load service member by handle: %w", err)
	}
	return member, nil
}

func memberHandleNaturalKey(serviceKind string, handle string) string {
	if trim(serviceKind) == KindServiceAccount {
		return strings.ToLower(trim(handle))
	}
	return trim(handle)
}

func memberSubstrateIdentityMatches(member Member, workspaceID string, definitionID string, installationID string, memberID string, serviceKind string) bool {
	return member.WorkspaceID == workspaceID &&
		member.ServiceDefinitionID == definitionID &&
		member.ServiceInstallationID == installationID &&
		member.MemberID == memberID &&
		member.ServiceKind == serviceKind
}

func memberSelectSQL() string {
	return `
SELECT id, workspace_id, service_definition_id, service_installation_id,
       member_id, service_kind, handle, display_name, avatar_url, status,
       created_at, updated_at
FROM service_members`
}

func scanMember(row coredb.Row) (Member, error) {
	var member Member
	if err := row.Scan(
		&member.ID,
		&member.WorkspaceID,
		&member.ServiceDefinitionID,
		&member.ServiceInstallationID,
		&member.MemberID,
		&member.ServiceKind,
		&member.Handle,
		&member.DisplayName,
		&member.AvatarURL,
		&member.Status,
		&member.CreatedAt,
		&member.UpdatedAt,
	); err != nil {
		return Member{}, err
	}
	member.ID = trim(member.ID)
	member.WorkspaceID = trim(member.WorkspaceID)
	member.ServiceDefinitionID = trim(member.ServiceDefinitionID)
	member.ServiceInstallationID = trim(member.ServiceInstallationID)
	member.MemberID = trim(member.MemberID)
	member.ServiceKind = trim(member.ServiceKind)
	member.Handle = trim(member.Handle)
	member.Status = trim(member.Status)
	return member, nil
}

func mapMemberWriteError(err error) error {
	if constraint, ok := coredb.UniqueViolationConstraint(err); ok {
		switch constraint {
		case "PRIMARY":
			return conflict("Service member already exists.")
		case "service_members_installation_key":
			return conflict("Service installation already has a member.")
		case "service_members_member_key":
			return conflict("Workspace member is already attached to a service.")
		case "service_members_workspace_kind_handle_key", "service_members_workspace_kind_normalized_handle_key":
			return conflict("Service handle already exists in this workspace.")
		}
	}
	return err
}