package serviceaccount

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

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

// UpsertManager activates one Human manager assignment. The natural key makes
// repeated adds idempotent, including retries issued by another manager after
// the assignment has already become active.
func (store *TxStore) UpsertManager(ctx context.Context, params ManagerUpsert) (ManagerAssignment, error) {
	execer, err := txExecer(store)
	if err != nil {
		return ManagerAssignment{}, err
	}
	params.ID = trim(params.ID)
	params.WorkspaceID = trim(params.WorkspaceID)
	params.ServiceAccountID = trim(params.ServiceAccountID)
	params.MemberID = trim(params.MemberID)
	params.GrantedByMemberID = trim(params.GrantedByMemberID)
	params.GrantedByActorType = trim(params.GrantedByActorType)
	params.GrantedByActorID = trim(params.GrantedByActorID)
	if params.ID == "" || params.WorkspaceID == "" || params.ServiceAccountID == "" || params.MemberID == "" || params.GrantedByActorType == "" || params.GrantedByActorID == "" {
		return ManagerAssignment{}, badRequest("service account manager identity, account, member, and grant actor are required")
	}
	if params.GrantedByActorType == "human" && (params.GrantedByMemberID == "" || params.GrantedByActorID != params.GrantedByMemberID) {
		return ManagerAssignment{}, badRequest("Human manager grants require matching grantor member and actor ids")
	}
	if err := lockWorkspaceAccountForManagerMutation(ctx, execer, params.WorkspaceID, params.ServiceAccountID); err != nil {
		return ManagerAssignment{}, err
	}
	if err := validateManagerSubstrate(ctx, execer, params); err != nil {
		return ManagerAssignment{}, err
	}
	if err := validateManagerIDReuse(ctx, execer, params); err != nil {
		return ManagerAssignment{}, err
	}
	existing, found, err := loadManagerByNaturalKeyForUpdate(ctx, execer, params.WorkspaceID, params.ServiceAccountID, params.MemberID)
	if err != nil {
		return ManagerAssignment{}, err
	}
	if found && existing.Status == ManagerStatusActive && existing.EndedAt == nil {
		return existing, nil
	}
	now := utcOrNow(params.Now)
	if _, err := execer.Exec(ctx, `
INSERT INTO service_account_managers (
    id, workspace_id, service_account_id, member_id, status, version,
    granted_by_member_id, granted_by_actor_type, granted_by_actor_id,
    created_at, updated_at, ended_at
) VALUES ($1, $2, $3, $4, 'active', 1, $5, $6, $7, $8, $8, 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),
    granted_by_actor_type = VALUES(granted_by_actor_type),
    granted_by_actor_id = VALUES(granted_by_actor_id),
    updated_at = VALUES(updated_at),
    ended_at = NULL
`, params.ID, params.WorkspaceID, params.ServiceAccountID, params.MemberID,
		nullableString(params.GrantedByMemberID), params.GrantedByActorType, params.GrantedByActorID, now); err != nil {
		return ManagerAssignment{}, fmt.Errorf("upsert service account manager: %w", err)
	}
	assignment, err := loadManagerByNaturalKey(ctx, execer, params.WorkspaceID, params.ServiceAccountID, params.MemberID)
	if errors.Is(err, ErrNotFound) {
		return ManagerAssignment{}, conflict("service account manager id is already assigned to another member")
	}
	return assignment, err
}

func validateManagerSubstrate(ctx context.Context, queryer coredb.QueryRower, params ManagerUpsert) error {
	var matches bool
	if err := queryer.QueryRow(ctx, `
SELECT EXISTS (
    SELECT 1
    FROM members manager_member
    WHERE manager_member.id = $2
      AND manager_member.workspace_id = $1
      AND manager_member.type = 'human'
      AND manager_member.status = 'active'
      AND (
          NULLIF($3, '') IS NULL
          OR EXISTS (
              SELECT 1
              FROM members grantor
              WHERE grantor.id = $3
                AND grantor.workspace_id = $1
                AND grantor.type = 'human'
                AND grantor.status = 'active'
          )
      )
)
`, params.WorkspaceID, params.MemberID, params.GrantedByMemberID).Scan(&matches); err != nil {
		return fmt.Errorf("validate service account manager substrate: %w", err)
	}
	if !matches {
		return conflict("service account manager, grantor, or account belongs to a different workspace")
	}
	return nil
}

func lockWorkspaceAccountForManagerMutation(ctx context.Context, queryer coredb.QueryRower, workspaceID string, accountID string) error {
	var managementKind string
	if err := queryer.QueryRow(ctx, `
SELECT management_kind
FROM service_accounts
WHERE workspace_id = $1 AND id = $2
FOR UPDATE
`, trim(workspaceID), trim(accountID)).Scan(&managementKind); err != nil {
		if coredb.IsNotFound(err) {
			return notFound("service account not found")
		}
		return fmt.Errorf("lock service account for manager mutation: %w", err)
	}
	if trim(managementKind) != ManagementKindWorkspace {
		return conflict("platform-managed service accounts cannot have workspace managers")
	}
	return nil
}

func validateManagerIDReuse(ctx context.Context, queryer coredb.QueryRower, params ManagerUpsert) error {
	var existingWorkspaceID, existingAccountID, existingMemberID string
	err := queryer.QueryRow(ctx, `
SELECT workspace_id, service_account_id, member_id
FROM service_account_managers
WHERE id = $1
`, params.ID).Scan(&existingWorkspaceID, &existingAccountID, &existingMemberID)
	if err != nil {
		if coredb.IsNotFound(err) {
			return nil
		}
		return fmt.Errorf("validate service account manager id reuse: %w", err)
	}
	if existingWorkspaceID != params.WorkspaceID || existingAccountID != params.ServiceAccountID || existingMemberID != params.MemberID {
		return conflict("service account manager id is already assigned to another member")
	}
	return nil
}

func (store *Store) ListManagers(ctx context.Context, workspaceID string, accountID string, activeOnly bool) ([]ManagerAssignment, error) {
	q, err := queryer(store)
	if err != nil {
		return nil, err
	}
	query := managerSelectSQL() + ` WHERE workspace_id = $1 AND service_account_id = $2`
	if activeOnly {
		query += ` AND status = 'active' AND ended_at IS NULL`
	}
	query += ` ORDER BY member_id ASC, id ASC`
	rows, err := q.Query(ctx, query, trim(workspaceID), trim(accountID))
	if err != nil {
		return nil, fmt.Errorf("list service account managers: %w", err)
	}
	defer rows.Close()
	managers := make([]ManagerAssignment, 0)
	for rows.Next() {
		manager, err := scanManager(rows)
		if err != nil {
			return nil, fmt.Errorf("scan service account manager: %w", err)
		}
		managers = append(managers, manager)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate service account managers: %w", err)
	}
	return managers, nil
}

func (store *Store) HasActiveManager(ctx context.Context, workspaceID string, accountID string, memberID string) (bool, error) {
	q, err := queryer(store)
	if err != nil {
		return false, err
	}
	return hasActiveManager(ctx, q, workspaceID, accountID, memberID)
}

// ListActiveManagerAccountIDs returns the workspace Service Accounts for which
// one Human currently holds the indivisible management/publishing authority.
// It is intentionally identity-only so caller-specific API projections can
// compute capabilities without exposing the manager directory itself.
func (store *Store) ListActiveManagerAccountIDs(ctx context.Context, workspaceID string, memberID string) ([]string, error) {
	q, err := queryer(store)
	if err != nil {
		return nil, err
	}
	workspaceID = trim(workspaceID)
	memberID = trim(memberID)
	if workspaceID == "" || memberID == "" {
		return nil, badRequest("workspaceId and memberId are required")
	}
	rows, err := q.Query(ctx, `
SELECT service_account_id
FROM service_account_managers
WHERE workspace_id = $1
  AND member_id = $2
  AND status = 'active'
  AND ended_at IS NULL
ORDER BY service_account_id ASC
`, workspaceID, memberID)
	if err != nil {
		return nil, fmt.Errorf("list active managed service accounts: %w", err)
	}
	defer rows.Close()
	accountIDs := make([]string, 0)
	for rows.Next() {
		var accountID string
		if err := rows.Scan(&accountID); err != nil {
			return nil, fmt.Errorf("scan active managed service account: %w", err)
		}
		accountIDs = append(accountIDs, accountID)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active managed service accounts: %w", err)
	}
	return accountIDs, nil
}

func (store *TxStore) HasActiveManager(ctx context.Context, workspaceID string, accountID string, memberID string) (bool, error) {
	q, err := txExecer(store)
	if err != nil {
		return false, err
	}
	return hasActiveManager(ctx, q, workspaceID, accountID, memberID)
}

// HasActiveManagerForUpdate resolves manager authority from the current
// mutation transaction and locks the matching assignment. Callers already
// hold the account row lock, so manager grants/removals serialize in the same
// account -> manager order used by the manager write paths.
func (store *TxStore) HasActiveManagerForUpdate(ctx context.Context, workspaceID string, accountID string, memberID string) (bool, error) {
	q, err := txExecer(store)
	if err != nil {
		return false, err
	}
	workspaceID = trim(workspaceID)
	accountID = trim(accountID)
	memberID = trim(memberID)
	if workspaceID == "" || accountID == "" || memberID == "" {
		return false, badRequest("workspaceId, serviceAccountId, and memberId are required")
	}
	var id string
	err = q.QueryRow(ctx, `
SELECT id
FROM service_account_managers
WHERE workspace_id = $1
  AND service_account_id = $2
  AND member_id = $3
  AND status = 'active'
  AND ended_at IS NULL
LIMIT 1
FOR UPDATE
`, workspaceID, accountID, memberID).Scan(&id)
	if err == nil {
		return trim(id) != "", nil
	}
	if coredb.IsNotFound(err) {
		return false, nil
	}
	return false, fmt.Errorf("lock active service account manager: %w", err)
}

func hasActiveManager(ctx context.Context, q coredb.QueryRower, workspaceID string, accountID string, memberID string) (bool, error) {
	workspaceID = trim(workspaceID)
	accountID = trim(accountID)
	memberID = trim(memberID)
	if workspaceID == "" || accountID == "" || memberID == "" {
		return false, badRequest("workspaceId, serviceAccountId, and memberId are required")
	}
	var exists bool
	if err := q.QueryRow(ctx, `
SELECT EXISTS (
    SELECT 1
    FROM service_account_managers
    WHERE workspace_id = $1
      AND service_account_id = $2
      AND member_id = $3
      AND status = 'active'
      AND ended_at IS NULL
)
`, workspaceID, accountID, memberID).Scan(&exists); err != nil {
		return false, fmt.Errorf("check active service account manager: %w", err)
	}
	return exists, nil
}

func loadManagerByID(ctx context.Context, q coredb.QueryRower, managerID string) (ManagerAssignment, error) {
	manager, err := scanManager(q.QueryRow(ctx, managerSelectSQL()+` WHERE id = $1`, trim(managerID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return ManagerAssignment{}, notFound("service account manager not found")
		}
		return ManagerAssignment{}, fmt.Errorf("load service account manager: %w", err)
	}
	return manager, nil
}

func loadManagerByNaturalKey(ctx context.Context, q coredb.QueryRower, workspaceID string, accountID string, memberID string) (ManagerAssignment, error) {
	loaded, err := scanManager(q.QueryRow(ctx, managerSelectSQL()+`
WHERE workspace_id = $1 AND service_account_id = $2 AND member_id = $3
`, trim(workspaceID), trim(accountID), trim(memberID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return ManagerAssignment{}, notFound("service account manager not found")
		}
		return ManagerAssignment{}, fmt.Errorf("load service account manager by natural key: %w", err)
	}
	return loaded, nil
}

func loadManagerByNaturalKeyForUpdate(ctx context.Context, q coredb.QueryRower, workspaceID string, accountID string, memberID string) (ManagerAssignment, bool, error) {
	loaded, err := scanManager(q.QueryRow(ctx, managerSelectSQL()+`
WHERE workspace_id = $1 AND service_account_id = $2 AND member_id = $3
FOR UPDATE
`, trim(workspaceID), trim(accountID), trim(memberID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return ManagerAssignment{}, false, nil
		}
		return ManagerAssignment{}, false, fmt.Errorf("lock service account manager by natural key: %w", err)
	}
	return loaded, true, nil
}

func managerSelectSQL() string {
	return `
SELECT id, workspace_id, service_account_id, member_id, status, version,
       COALESCE(granted_by_member_id, ''), granted_by_actor_type,
       granted_by_actor_id, created_at, updated_at, ended_at
FROM service_account_managers`
}

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

// EndManager ends one active manager assignment with an optimistic version
// fence while preserving the at-least-one-manager invariant.
func (store *TxStore) EndManager(ctx context.Context, workspaceID string, accountID string, managerID string, expectedVersion int64, now time.Time) (ManagerAssignment, error) {
	execer, err := txExecer(store)
	if err != nil {
		return ManagerAssignment{}, err
	}
	workspaceID = trim(workspaceID)
	accountID = trim(accountID)
	managerID = trim(managerID)
	if workspaceID == "" || accountID == "" || managerID == "" || expectedVersion <= 0 {
		return ManagerAssignment{}, badRequest("workspaceId, serviceAccountId, managerId, and a positive expectedVersion are required")
	}
	target, err := loadManagerByID(ctx, execer, managerID)
	if err != nil {
		return ManagerAssignment{}, err
	}
	if target.WorkspaceID != workspaceID || target.ServiceAccountID != accountID {
		return ManagerAssignment{}, notFound("service account manager not found")
	}
	if target.Status == ManagerStatusEnded && target.EndedAt != nil {
		if target.Version == expectedVersion+1 {
			return target, nil
		}
		return ManagerAssignment{}, conflict("service account manager version changed")
	}
	if err := lockWorkspaceAccountForManagerMutation(ctx, execer, workspaceID, accountID); err != nil {
		return ManagerAssignment{}, err
	}
	activeManagers, err := lockActiveAccountManagers(ctx, execer, workspaceID, accountID)
	if err != nil {
		return ManagerAssignment{}, err
	}
	activeTarget := false
	for _, manager := range activeManagers {
		if manager.ID == target.ID && manager.Version == expectedVersion {
			activeTarget = true
			break
		}
	}
	if !activeTarget {
		return ManagerAssignment{}, conflict("service account manager version changed")
	}
	if len(activeManagers) <= 1 {
		return ManagerAssignment{}, conflict("workspace service accounts require at least one manager")
	}
	now = utcOrNow(now)
	result, err := execer.Exec(ctx, `
UPDATE service_account_managers
SET status = 'ended', version = version + 1, updated_at = $4, ended_at = $4
WHERE workspace_id = $1 AND id = $2 AND version = $3 AND status = 'active' AND ended_at IS NULL
`, workspaceID, managerID, expectedVersion, now)
	if err != nil {
		return ManagerAssignment{}, fmt.Errorf("end service account manager: %w", err)
	}
	if result.RowsAffected() == 0 {
		return ManagerAssignment{}, conflict("service account manager version changed")
	}
	return loadManagerByID(ctx, execer, managerID)
}

func lockActiveAccountManagers(ctx context.Context, queryer coredb.Queryer, workspaceID string, accountID string) ([]ManagerAssignment, error) {
	rows, err := queryer.Query(ctx, managerSelectSQL()+`
WHERE workspace_id = $1
  AND service_account_id = $2
  AND status = 'active'
  AND ended_at IS NULL
ORDER BY id ASC
FOR UPDATE
`, trim(workspaceID), trim(accountID))
	if err != nil {
		return nil, fmt.Errorf("lock active service account managers: %w", err)
	}
	defer rows.Close()
	managers := make([]ManagerAssignment, 0)
	for rows.Next() {
		manager, err := scanManager(rows)
		if err != nil {
			return nil, fmt.Errorf("scan locked service account manager: %w", err)
		}
		managers = append(managers, manager)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate locked service account managers: %w", err)
	}
	return managers, nil
}