package app

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/access"
	"github.com/tidbcloud/AgentTeam/internal/store/app/installation"
	"github.com/tidbcloud/AgentTeam/internal/store/attachment"
	"github.com/tidbcloud/AgentTeam/internal/store/serviceactor"
)

const (
	AppManagerStatusActive = "active"
	AppManagerStatusEnded  = "ended"
)

// AppManagerAssignment is one versioned Human administrator assignment for a
// workspace-created App. Workspace owners/admins retain implicit recovery
// authority and therefore do not need a materialized assignment.
type AppManagerAssignment struct {
	ID                string
	WorkspaceID       string
	AppID             string
	MemberID          string
	Status            string
	Version           int64
	GrantedByMemberID string
	CreatedAt         time.Time
	UpdatedAt         time.Time
	EndedAt           *time.Time
}

// ListAppManagersForUser returns active App administrators only to callers who
// can manage the App. The administrator directory is itself an authorization
// fact and is not exposed to ordinary workspace members.
func (store *Store) ListAppManagersForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	installationID string,
) ([]AppManagerAssignment, error) {
	workspaceMember, summary, err := store.loadManagedWorkspaceApp(ctx, userID, workspaceID, installationID)
	if err != nil {
		return nil, err
	}
	if err := store.authorizeAppInstallationManagement(
		ctx, store.db, workspaceID, workspaceMember, summary.Definition.ID,
	); err != nil {
		return nil, err
	}
	return listActiveAppManagers(ctx, store.db, workspaceID, summary.Definition.ID)
}

// AddAppManagerForUser grants the App's complete management authority to one
// active Human in the same workspace. Repeated adds are idempotent; re-adding
// an ended assignment reactivates it and advances its version.
func (store *Store) AddAppManagerForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	installationID string,
	memberID string,
	now time.Time,
) (AppManagerAssignment, error) {
	workspaceMember, summary, err := store.loadManagedWorkspaceApp(ctx, userID, workspaceID, installationID)
	if err != nil {
		return AppManagerAssignment{}, err
	}
	if err := store.authorizeAppInstallationManagement(
		ctx, store.db, workspaceID, workspaceMember, summary.Definition.ID,
	); err != nil {
		return AppManagerAssignment{}, err
	}
	managerID, err := identifiers.NewEntityID("appmanager")
	if err != nil {
		return AppManagerAssignment{}, fmt.Errorf("generate App manager id: %w", err)
	}
	now = normalizedAppManagementTime(now)
	memberID = strings.TrimSpace(memberID)
	if memberID == "" {
		return AppManagerAssignment{}, badRequest("memberId is required")
	}

	var assignment AppManagerAssignment
	err = coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, summary.Definition.ID); err != nil {
			return err
		}
		currentMember, err := store.revalidateAppInstallationManagementForUpdate(
			ctx,
			tx,
			userID,
			workspaceID,
			workspaceMember.Member.ID,
			summary.Definition.ID,
		)
		if err != nil {
			return err
		}
		if err := installation.LockCurrentWorkspaceAppInstallation(
			ctx, tx, workspaceID, installationID, summary.Definition.ID,
		); err != nil {
			return err
		}
		assignment, err = upsertAppManager(ctx, tx, appManagerUpsert{
			ID:                managerID,
			WorkspaceID:       workspaceID,
			AppID:             summary.Definition.ID,
			MemberID:          memberID,
			GrantedByMemberID: currentMember.Member.ID,
			Now:               now,
		})
		return err
	})
	return assignment, err
}

// RemoveAppManagerForUser ends one administrator assignment using optimistic
// concurrency. The shared App-definition lock serializes removals, and the App
// must retain at least one active Human administrator.
func (store *Store) RemoveAppManagerForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	installationID string,
	managerID string,
	expectedVersion int64,
	now time.Time,
) (AppManagerAssignment, error) {
	workspaceMember, summary, err := store.loadManagedWorkspaceApp(ctx, userID, workspaceID, installationID)
	if err != nil {
		return AppManagerAssignment{}, err
	}
	if err := store.authorizeAppInstallationManagement(
		ctx, store.db, workspaceID, workspaceMember, summary.Definition.ID,
	); err != nil {
		return AppManagerAssignment{}, err
	}
	managerID = strings.TrimSpace(managerID)
	if managerID == "" || expectedVersion <= 0 {
		return AppManagerAssignment{}, badRequest("managerId and a positive expectedVersion are required")
	}
	now = normalizedAppManagementTime(now)

	var assignment AppManagerAssignment
	err = coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, summary.Definition.ID); err != nil {
			return err
		}
		if _, err := store.revalidateAppInstallationManagementForUpdate(
			ctx,
			tx,
			userID,
			workspaceID,
			workspaceMember.Member.ID,
			summary.Definition.ID,
		); err != nil {
			return err
		}
		if err := installation.LockCurrentWorkspaceAppInstallation(
			ctx, tx, workspaceID, installationID, summary.Definition.ID,
		); err != nil {
			return err
		}
		assignment, err = endAppManager(
			ctx,
			tx,
			workspaceID,
			summary.Definition.ID,
			managerID,
			expectedVersion,
			now,
		)
		return err
	})
	return assignment, err
}

// UpdateAppAvatarForUser applies an immutable, App-owned avatar asset to the
// canonical App definition and service member in one transaction.
func (store *Store) UpdateAppAvatarForUser(
	ctx context.Context,
	userID string,
	workspaceID string,
	installationID string,
	avatarAssetID string,
	now time.Time,
) (AppInstallationView, error) {
	workspaceMember, summary, err := store.loadManagedWorkspaceApp(ctx, userID, workspaceID, installationID)
	if err != nil {
		return AppInstallationView{}, err
	}
	if err := store.authorizeAppInstallationManagement(
		ctx, store.db, workspaceID, workspaceMember, summary.Definition.ID,
	); err != nil {
		return AppInstallationView{}, err
	}
	avatarAssetID = strings.TrimSpace(avatarAssetID)
	avatarURL := attachment.AvatarAssetURL(avatarAssetID)
	now = normalizedAppManagementTime(now)

	if err := coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		if err := installation.LockAppInstallationMutationScope(ctx, tx, summary.Definition.ID); err != nil {
			return err
		}
		if _, err := store.revalidateAppInstallationManagementForUpdate(
			ctx,
			tx,
			userID,
			workspaceID,
			workspaceMember.Member.ID,
			summary.Definition.ID,
		); err != nil {
			return err
		}
		if err := installation.LockCurrentWorkspaceAppInstallation(
			ctx, tx, workspaceID, installationID, summary.Definition.ID,
		); err != nil {
			return err
		}
		if err := requireAppAvatarAsset(
			ctx, tx, avatarAssetID, summary.Definition.ID, workspaceID,
		); err != nil {
			return err
		}
		_, err := tx.Exec(ctx, `
UPDATE service_definitions
SET config = JSON_SET(COALESCE(config, JSON_OBJECT()), '$.avatarAssetId', $3),
    avatar_url = $4,
    updated_at = $5
WHERE id = $2
  AND service_kind = 'app'
  AND JSON_UNQUOTE(JSON_EXTRACT(COALESCE(config, JSON_OBJECT()), '$.appKind')) = 'third_party'
  AND EXISTS (
      SELECT 1
      FROM extension_catalog_attributions attribution
      WHERE attribution.workspace_id = $1
        AND attribution.service_definition_id = service_definitions.id
  )
`, strings.TrimSpace(workspaceID), strings.TrimSpace(summary.Definition.ID), avatarAssetID, avatarURL, now)
		if err != nil {
			return fmt.Errorf("update App avatar: %w", err)
		}
		if _, err := store.syncAppServiceDefinition(ctx, tx, summary.Definition.ID, now); err != nil {
			return err
		}
		txActors := store.serviceActorStore().InTx(tx)
		member, err := txActors.LoadMemberByInstallation(ctx, strings.TrimSpace(installationID))
		if err != nil {
			return err
		}
		_, err = txActors.UpsertMember(ctx, serviceactor.MemberUpsert{
			ID:                    member.ID,
			WorkspaceID:           member.WorkspaceID,
			ServiceDefinitionID:   member.ServiceDefinitionID,
			ServiceInstallationID: member.ServiceInstallationID,
			MemberID:              member.MemberID,
			ServiceKind:           member.ServiceKind,
			Handle:                member.Handle,
			DisplayName:           member.DisplayName,
			AvatarURL:             avatarURL,
			Status:                member.Status,
			Now:                   now,
		})
		return err
	}); err != nil {
		return AppInstallationView{}, err
	}
	return store.appInstallationViewByID(ctx, workspaceID, strings.TrimSpace(installationID))
}

func (store *Store) loadManagedWorkspaceApp(
	ctx context.Context,
	userID string,
	workspaceID string,
	installationID string,
) (access.HumanWorkspaceMember, serviceactor.InstallationSummary, error) {
	workspaceMember, err := store.requireWorkspaceAccess(ctx, userID, workspaceID)
	if err != nil {
		return access.HumanWorkspaceMember{}, serviceactor.InstallationSummary{}, err
	}
	installationID = strings.TrimSpace(installationID)
	if installationID == "" {
		return access.HumanWorkspaceMember{}, serviceactor.InstallationSummary{}, badRequest("installationId is required")
	}
	summary, err := store.serviceActorStore().LoadInstallationSummaryByKind(
		ctx,
		strings.TrimSpace(workspaceID),
		installationID,
		serviceactor.KindApp,
	)
	if err != nil {
		return access.HumanWorkspaceMember{}, serviceactor.InstallationSummary{}, err
	}
	view, err := installationViewFromServiceSummary(summary)
	if err != nil {
		return access.HumanWorkspaceMember{}, serviceactor.InstallationSummary{}, err
	}
	if !view.Enabled || !isWorkspaceAppInstallation(view) {
		return access.HumanWorkspaceMember{}, serviceactor.InstallationSummary{}, ErrNotFound
	}
	attributed, err := appDefinitionAssociatedWithWorkspace(
		ctx, store.db, workspaceID, summary.Definition.ID,
	)
	if err != nil {
		return access.HumanWorkspaceMember{}, serviceactor.InstallationSummary{}, err
	}
	if !attributed {
		return access.HumanWorkspaceMember{}, serviceactor.InstallationSummary{}, ErrNotFound
	}
	return workspaceMember, summary, nil
}

type appManagerUpsert struct {
	ID                string
	WorkspaceID       string
	AppID             string
	MemberID          string
	GrantedByMemberID string
	Now               time.Time
}

func upsertAppManager(ctx context.Context, tx coredb.Tx, params appManagerUpsert) (AppManagerAssignment, error) {
	params.ID = strings.TrimSpace(params.ID)
	params.WorkspaceID = strings.TrimSpace(params.WorkspaceID)
	params.AppID = strings.TrimSpace(params.AppID)
	params.MemberID = strings.TrimSpace(params.MemberID)
	params.GrantedByMemberID = strings.TrimSpace(params.GrantedByMemberID)
	if params.ID == "" || params.WorkspaceID == "" || params.AppID == "" || params.MemberID == "" || params.GrantedByMemberID == "" {
		return AppManagerAssignment{}, badRequest("App manager identity, App, member, and grantor are required")
	}
	if err := validateAppManagerHumans(ctx, tx, params.WorkspaceID, params.MemberID, params.GrantedByMemberID); err != nil {
		return AppManagerAssignment{}, err
	}
	if err := validateAppManagerIDReuse(ctx, tx, params); err != nil {
		return AppManagerAssignment{}, err
	}
	existing, found, err := loadAppManagerByNaturalKeyForUpdate(
		ctx, tx, params.WorkspaceID, params.AppID, params.MemberID,
	)
	if err != nil {
		return AppManagerAssignment{}, err
	}
	if found && existing.Status == AppManagerStatusActive && existing.EndedAt == nil {
		return existing, nil
	}
	now := normalizedAppManagementTime(params.Now)
	if _, err := tx.Exec(ctx, `
INSERT INTO extension_definition_managers (
    id, workspace_id, service_definition_id, member_id, status, version,
    granted_by_member_id, created_at, updated_at, ended_at
) VALUES ($1, $2, $3, $4, 'active', 1, $5, $6, $6, NULL)
ON DUPLICATE KEY UPDATE
    version = CASE WHEN status = 'active' THEN version ELSE version + 1 END,
    status = 'active',
    granted_by_member_id = VALUES(granted_by_member_id),
    updated_at = VALUES(updated_at),
    ended_at = NULL
`, params.ID, params.WorkspaceID, params.AppID, params.MemberID, params.GrantedByMemberID, now); err != nil {
		return AppManagerAssignment{}, fmt.Errorf("upsert App manager: %w", err)
	}
	assignment, err := loadAppManagerByNaturalKey(ctx, tx, params.WorkspaceID, params.AppID, params.MemberID)
	if errors.Is(err, ErrNotFound) {
		return AppManagerAssignment{}, conflict("App manager id is already assigned to another member")
	}
	return assignment, err
}

func endAppManager(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID string,
	appID string,
	managerID string,
	expectedVersion int64,
	now time.Time,
) (AppManagerAssignment, error) {
	manager, err := loadAppManagerByID(ctx, tx, managerID)
	if err != nil {
		return AppManagerAssignment{}, err
	}
	if strings.TrimSpace(manager.WorkspaceID) != strings.TrimSpace(workspaceID) ||
		strings.TrimSpace(manager.AppID) != strings.TrimSpace(appID) {
		return AppManagerAssignment{}, ErrNotFound
	}
	if manager.Status == AppManagerStatusEnded && manager.EndedAt != nil {
		if manager.Version == expectedVersion+1 {
			return manager, nil
		}
		return AppManagerAssignment{}, conflict("App manager changed. Reload and try again.")
	}
	activeManagers, err := lockActiveAppManagers(ctx, tx, workspaceID, appID)
	if err != nil {
		return AppManagerAssignment{}, err
	}
	activeTarget := false
	for _, activeManager := range activeManagers {
		if activeManager.ID == manager.ID && activeManager.Version == expectedVersion {
			activeTarget = true
			break
		}
	}
	if !activeTarget {
		return AppManagerAssignment{}, conflict("App manager changed. Reload and try again.")
	}
	if len(activeManagers) <= 1 {
		return AppManagerAssignment{}, conflict("A App must keep at least one manager")
	}
	now = normalizedAppManagementTime(now)
	result, err := tx.Exec(ctx, `
UPDATE extension_definition_managers
SET status = 'ended',
    version = version + 1,
    updated_at = $4,
    ended_at = $4
WHERE id = $3
  AND workspace_id = $1
  AND service_definition_id = $2
  AND status = 'active'
  AND ended_at IS NULL
  AND version = $5
`, strings.TrimSpace(workspaceID), strings.TrimSpace(appID), strings.TrimSpace(managerID), now, expectedVersion)
	if err != nil {
		return AppManagerAssignment{}, fmt.Errorf("end App manager: %w", err)
	}
	if result.RowsAffected() != 1 {
		return AppManagerAssignment{}, conflict("App manager changed. Reload and try again.")
	}
	return loadAppManagerByID(ctx, tx, managerID)
}

// lockActiveAppManagers locks only the current App's active assignments in a
// stable order. Besides preserving the last-manager invariant, scoping the
// locking read prevents an invalid manager id from acquiring a row lock that
// belongs to another App.
func lockActiveAppManagers(
	ctx context.Context,
	queryer coredb.Queryer,
	workspaceID string,
	appID string,
) ([]AppManagerAssignment, error) {
	rows, err := queryer.Query(ctx, appManagerSelectSQL()+`
WHERE workspace_id = $1
  AND service_definition_id = $2
  AND status = 'active'
  AND ended_at IS NULL
ORDER BY id ASC
FOR UPDATE
`, strings.TrimSpace(workspaceID), strings.TrimSpace(appID))
	if err != nil {
		return nil, fmt.Errorf("lock active App managers: %w", err)
	}
	defer rows.Close()
	managers := make([]AppManagerAssignment, 0)
	for rows.Next() {
		manager, err := scanAppManager(rows)
		if err != nil {
			return nil, fmt.Errorf("scan locked App manager: %w", err)
		}
		managers = append(managers, manager)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate locked App managers: %w", err)
	}
	return managers, nil
}

func listActiveAppManagers(
	ctx context.Context,
	queryer coredb.Queryer,
	workspaceID string,
	appID string,
) ([]AppManagerAssignment, error) {
	rows, err := queryer.Query(ctx, appManagerSelectSQL()+`
WHERE workspace_id = $1
  AND service_definition_id = $2
  AND status = 'active'
  AND ended_at IS NULL
ORDER BY member_id ASC, id ASC
`, strings.TrimSpace(workspaceID), strings.TrimSpace(appID))
	if err != nil {
		return nil, fmt.Errorf("list App managers: %w", err)
	}
	defer rows.Close()
	managers := make([]AppManagerAssignment, 0)
	for rows.Next() {
		manager, err := scanAppManager(rows)
		if err != nil {
			return nil, fmt.Errorf("scan App manager: %w", err)
		}
		managers = append(managers, manager)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate App managers: %w", err)
	}
	return managers, nil
}

func hasActiveAppManager(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
	appID string,
	memberID string,
	forUpdate bool,
) (bool, error) {
	query := `
SELECT id
FROM extension_definition_managers
WHERE workspace_id = $1
  AND service_definition_id = $2
  AND member_id = $3
  AND status = 'active'
  AND ended_at IS NULL
LIMIT 1
`
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	var id string
	err := queryer.QueryRow(
		ctx,
		query,
		strings.TrimSpace(workspaceID),
		strings.TrimSpace(appID),
		strings.TrimSpace(memberID),
	).Scan(&id)
	if err == nil {
		return strings.TrimSpace(id) != "", nil
	}
	if coredb.IsNotFound(err) {
		return false, nil
	}
	return false, fmt.Errorf("check active App manager: %w", err)
}

func validateAppManagerHumans(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
	memberID string,
	grantorMemberID string,
) error {
	var valid bool
	if err := queryer.QueryRow(ctx, `
SELECT EXISTS (
    SELECT 1
    FROM members manager_member
    JOIN members grantor
      ON grantor.id = $3
     AND grantor.workspace_id = manager_member.workspace_id
     AND grantor.type = 'human'
     AND grantor.status = 'active'
    WHERE manager_member.id = $2
      AND manager_member.workspace_id = $1
      AND manager_member.type = 'human'
      AND manager_member.status = 'active'
)
`, strings.TrimSpace(workspaceID), strings.TrimSpace(memberID), strings.TrimSpace(grantorMemberID)).Scan(&valid); err != nil {
		return fmt.Errorf("validate App manager workspace members: %w", err)
	}
	if !valid {
		return conflict("App manager and grantor must be active Human members of this workspace")
	}
	return nil
}

func validateAppManagerIDReuse(ctx context.Context, queryer coredb.QueryRower, params appManagerUpsert) error {
	var workspaceID, appID, memberID string
	err := queryer.QueryRow(ctx, `
SELECT workspace_id, service_definition_id, member_id
FROM extension_definition_managers
WHERE id = $1
`, params.ID).Scan(&workspaceID, &appID, &memberID)
	if coredb.IsNotFound(err) {
		return nil
	}
	if err != nil {
		return fmt.Errorf("validate App manager id reuse: %w", err)
	}
	if strings.TrimSpace(workspaceID) != params.WorkspaceID ||
		strings.TrimSpace(appID) != params.AppID ||
		strings.TrimSpace(memberID) != params.MemberID {
		return conflict("App manager id is already assigned to another member")
	}
	return nil
}

func loadAppManagerByNaturalKeyForUpdate(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
	appID string,
	memberID string,
) (AppManagerAssignment, bool, error) {
	manager, err := scanAppManager(queryer.QueryRow(ctx, appManagerSelectSQL()+`
WHERE workspace_id = $1 AND service_definition_id = $2 AND member_id = $3
FOR UPDATE
`, strings.TrimSpace(workspaceID), strings.TrimSpace(appID), strings.TrimSpace(memberID)))
	if coredb.IsNotFound(err) {
		return AppManagerAssignment{}, false, nil
	}
	if err != nil {
		return AppManagerAssignment{}, false, fmt.Errorf("lock App manager by member: %w", err)
	}
	return manager, true, nil
}

func loadAppManagerByNaturalKey(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
	appID string,
	memberID string,
) (AppManagerAssignment, error) {
	manager, err := scanAppManager(queryer.QueryRow(ctx, appManagerSelectSQL()+`
WHERE workspace_id = $1 AND service_definition_id = $2 AND member_id = $3
`, strings.TrimSpace(workspaceID), strings.TrimSpace(appID), strings.TrimSpace(memberID)))
	if coredb.IsNotFound(err) {
		return AppManagerAssignment{}, ErrNotFound
	}
	if err != nil {
		return AppManagerAssignment{}, fmt.Errorf("load App manager by member: %w", err)
	}
	return manager, nil
}

func loadAppManagerByID(
	ctx context.Context,
	queryer coredb.QueryRower,
	managerID string,
) (AppManagerAssignment, error) {
	manager, err := scanAppManager(queryer.QueryRow(
		ctx, appManagerSelectSQL()+` WHERE id = $1`, strings.TrimSpace(managerID),
	))
	if coredb.IsNotFound(err) {
		return AppManagerAssignment{}, ErrNotFound
	}
	if err != nil {
		return AppManagerAssignment{}, fmt.Errorf("load App manager: %w", err)
	}
	return manager, nil
}

func appManagerSelectSQL() string {
	return `
SELECT id, workspace_id, service_definition_id, member_id, status, version,
       granted_by_member_id, created_at, updated_at, ended_at
FROM extension_definition_managers`
}

func scanAppManager(scanner interface{ Scan(...any) error }) (AppManagerAssignment, error) {
	var manager AppManagerAssignment
	var endedAt sql.NullTime
	if err := scanner.Scan(
		&manager.ID,
		&manager.WorkspaceID,
		&manager.AppID,
		&manager.MemberID,
		&manager.Status,
		&manager.Version,
		&manager.GrantedByMemberID,
		&manager.CreatedAt,
		&manager.UpdatedAt,
		&endedAt,
	); err != nil {
		return AppManagerAssignment{}, err
	}
	if endedAt.Valid {
		value := endedAt.Time.UTC()
		manager.EndedAt = &value
	}
	manager.CreatedAt = manager.CreatedAt.UTC()
	manager.UpdatedAt = manager.UpdatedAt.UTC()
	return manager, nil
}

func requireAppAvatarAsset(
	ctx context.Context,
	queryer coredb.QueryRower,
	avatarAssetID string,
	appID string,
	workspaceID string,
) error {
	if strings.TrimSpace(avatarAssetID) == "" {
		return nil
	}
	var count int64
	if err := queryer.QueryRow(ctx, `
SELECT COUNT(*)
FROM avatars
WHERE id = $1
  AND owner_kind = 'app'
  AND owner_id = $2
  AND workspace_id = $3
`, strings.TrimSpace(avatarAssetID), strings.TrimSpace(appID), strings.TrimSpace(workspaceID)).Scan(&count); err != nil {
		return fmt.Errorf("validate App avatar asset: %w", err)
	}
	if count != 1 {
		return badRequest("avatar asset does not belong to App")
	}
	return nil
}

func normalizedAppManagementTime(now time.Time) time.Time {
	if now.IsZero() {
		return time.Now().UTC()
	}
	return now.UTC()
}