package serviceaccount

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

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

// NormalizeName applies the single user-facing Service Account name rule.
// Names preserve case and Unicode for display while normalized_name provides
// case-insensitive uniqueness at the persistence boundary.
func NormalizeName(value string) (string, error) {
	name := trim(value)
	if name == "" || utf8.RuneCountInString(name) > 80 {
		return "", badRequest("service account name must be between 1 and 80 characters")
	}
	for _, r := range name {
		if unicode.IsControl(r) {
			return "", badRequest("service account name cannot contain control characters")
		}
	}
	return name, nil
}

// LockNameNamespaceAt is the typed variant used by domain services.
func (store *TxStore) LockNameNamespaceAt(ctx context.Context, name string, now time.Time) (string, error) {
	execer, err := txExecer(store)
	if err != nil {
		return "", err
	}
	name, err = NormalizeName(name)
	if err != nil {
		return "", err
	}
	normalized := normalizedName(name)
	now = utcOrNow(now)
	if _, err := execer.Exec(ctx, `
INSERT INTO service_account_name_namespaces (
    normalized_name, platform_service_account_id, version, created_at, updated_at
) VALUES ($1, NULL, 1, $2, $2)
ON DUPLICATE KEY UPDATE updated_at = updated_at
`, normalized, now); err != nil {
		return "", fmt.Errorf("ensure service account name namespace: %w", err)
	}
	return lockNameNamespace(ctx, execer, normalized)
}

func lockNameNamespace(ctx context.Context, queryer coredb.QueryRower, normalized string) (string, error) {
	var platformID sql.NullString
	if err := queryer.QueryRow(ctx, `
SELECT platform_service_account_id
FROM service_account_name_namespaces
WHERE normalized_name = $1
FOR UPDATE
`, normalized).Scan(&platformID); err != nil {
		if coredb.IsNotFound(err) {
			return "", notFound("service account name namespace not found")
		}
		return "", fmt.Errorf("lock service account name namespace: %w", err)
	}
	return trim(platformID.String), nil
}

// InsertAccount persists a fully materialized local account. Identity substrate
// rows must already exist in the same transaction.
func (store *TxStore) InsertAccount(ctx context.Context, params AccountInsert) (Account, error) {
	execer, err := txExecer(store)
	if err != nil {
		return Account{}, err
	}
	account := params.Account
	account.ID = trim(account.ID)
	account.PrincipalID = trim(account.PrincipalID)
	account.WorkspaceID = trim(account.WorkspaceID)
	account.PlatformServiceAccountID = trim(account.PlatformServiceAccountID)
	account.ServiceInstallationID = trim(account.ServiceInstallationID)
	account.ServiceMemberID = trim(account.ServiceMemberID)
	account.AuthorMemberID = trim(account.AuthorMemberID)
	account.MessageScopeID = trim(account.MessageScopeID)
	account.Name, err = NormalizeName(account.Name)
	if err != nil {
		return Account{}, err
	}
	if account.ID == "" || account.PrincipalID == "" || account.WorkspaceID == "" || account.ServiceInstallationID == "" || account.ServiceMemberID == "" || account.AuthorMemberID == "" || account.MessageScopeID == "" || account.Name == "" {
		return Account{}, badRequest("service account identity, workspace, service actor, scope, and name are required")
	}
	managementKind, err := normalizeManagementKind(account.ManagementKind)
	if err != nil {
		return Account{}, err
	}
	if managementKind == ManagementKindPlatform && account.PlatformServiceAccountID == "" {
		return Account{}, badRequest("platform service account id is required for platform-managed accounts")
	}
	if managementKind == ManagementKindWorkspace && account.PlatformServiceAccountID != "" {
		return Account{}, badRequest("workspace-managed accounts cannot reference a platform account")
	}
	if managementKind == ManagementKindWorkspace && account.PrincipalID != account.ID {
		return Account{}, badRequest("workspace-managed service account principal must equal its local account id")
	}
	if managementKind == ManagementKindWorkspace && account.CreatedByMemberID == "" {
		return Account{}, badRequest("workspace-managed service account creator member is required")
	}
	if managementKind == ManagementKindWorkspace && account.CreatedByOperatorUserID != "" {
		return Account{}, badRequest("workspace-managed service accounts cannot carry a platform operator creator")
	}
	if managementKind == ManagementKindPlatform && account.PrincipalID != account.PlatformServiceAccountID {
		return Account{}, badRequest("platform-managed service account principal must equal its platform account id")
	}
	if managementKind == ManagementKindPlatform && account.CreatedByOperatorUserID == "" {
		return Account{}, badRequest("platform-managed service account operator is required")
	}
	if managementKind == ManagementKindPlatform && account.CreatedByMemberID != "" {
		return Account{}, badRequest("platform-managed service accounts cannot carry a workspace member creator")
	}
	if account.AudiencePolicyVersion <= 0 {
		account.AudiencePolicyVersion = 1
	}
	if account.Version <= 0 {
		account.Version = 1
	}
	if account.AudienceState == "" {
		account.AudienceState = AudienceStatePending
	}
	account.CreatedAt = utcOrNow(account.CreatedAt)
	account.UpdatedAt = utcOrNow(account.UpdatedAt)
	if err := validateAccountSubstrate(ctx, execer, account, managementKind); err != nil {
		return Account{}, err
	}
	if _, err := execer.Exec(ctx, `
INSERT INTO service_accounts (
    id, principal_id, workspace_id, management_kind, platform_service_account_id,
    service_installation_id, service_member_id, author_member_id, message_scope_id,
    name, description, avatar_asset_id,
    applied_platform_profile_version, audience_policy_version,
    effective_audience_version, audience_state, version,
    created_by_member_id, created_by_operator_user_id, created_at, updated_at
) VALUES (
    $1, $2, $3, $4, $5,
    $6, $7, $8, $9,
    $10, $11, $12,
    $13, $14,
    $15, $16, $17,
    $18, $19, $20, $21
)
`, account.ID, account.PrincipalID, account.WorkspaceID, managementKind, nullableString(account.PlatformServiceAccountID),
		account.ServiceInstallationID, account.ServiceMemberID, account.AuthorMemberID, account.MessageScopeID,
		account.Name, account.Description, trim(account.AvatarAssetID),
		account.AppliedPlatformProfileVersion, account.AudiencePolicyVersion,
		account.EffectiveAudienceVersion, account.AudienceState, account.Version,
		nullableString(account.CreatedByMemberID), nullableString(account.CreatedByOperatorUserID), account.CreatedAt, account.UpdatedAt); err != nil {
		return Account{}, fmt.Errorf("insert service account: %w", mapAccountWriteError(err))
	}
	return loadAccount(ctx, execer, account.WorkspaceID, account.ID, "")
}

// InsertWorkspaceNameClaim binds one normalized name to one local account.
func (store *TxStore) InsertWorkspaceNameClaim(ctx context.Context, claimID string, workspaceID string, name string, accountID string, now time.Time) error {
	execer, err := txExecer(store)
	if err != nil {
		return err
	}
	name, err = NormalizeName(name)
	if err != nil {
		return err
	}
	normalized := normalizedName(name)
	claimID = trim(claimID)
	workspaceID = trim(workspaceID)
	accountID = trim(accountID)
	if claimID == "" || workspaceID == "" || accountID == "" {
		return badRequest("service account name claim identity is required")
	}
	platformOwnerID, err := lockNameNamespace(ctx, execer, normalized)
	if err != nil {
		return err
	}
	if platformOwnerID != "" {
		return conflict("workspace service account name is owned by a platform account")
	}
	var substrateMatches bool
	if err := execer.QueryRow(ctx, `
SELECT EXISTS (
    SELECT 1
    FROM service_accounts
    JOIN service_account_name_namespaces namespace
      ON namespace.normalized_name = service_accounts.normalized_name
     AND namespace.platform_service_account_id IS NULL
    WHERE service_accounts.id = $1
      AND service_accounts.workspace_id = $2
      AND service_accounts.normalized_name = $3
      AND service_accounts.management_kind = 'workspace'
)
`, accountID, workspaceID, normalized).Scan(&substrateMatches); err != nil {
		return fmt.Errorf("validate service account name claim: %w", err)
	}
	if !substrateMatches {
		return conflict("service account name claim does not match its workspace account")
	}
	now = utcOrNow(now)
	if _, err := execer.Exec(ctx, `
INSERT INTO service_account_name_claims (
    id, workspace_id, normalized_name, service_account_id, created_at, updated_at
) VALUES ($1, $2, $3, $4, $5, $5)
`, claimID, workspaceID, normalized, accountID, now); err != nil {
		return fmt.Errorf("insert service account name claim: %w", mapAccountWriteError(err))
	}
	return nil
}

func validateAccountSubstrate(ctx context.Context, queryer coredb.QueryRower, account Account, managementKind string) error {
	var matches bool
	if err := queryer.QueryRow(ctx, `
SELECT EXISTS (
    SELECT 1
    FROM service_installations installation
    JOIN service_definitions definition
      ON definition.id = installation.service_definition_id
     AND definition.service_kind = 'service_account'
     AND definition.installation_mode = 'named_multi_instance'
     AND definition.status = 'active'
    JOIN service_members service_member
      ON service_member.id = $3
     AND service_member.workspace_id = installation.workspace_id
     AND service_member.service_definition_id = installation.service_definition_id
     AND service_member.service_installation_id = installation.id
     AND service_member.member_id = $4
     AND service_member.service_kind = 'service_account'
     AND service_member.status = 'active'
     AND LOWER(TRIM(service_member.handle)) = $6
    JOIN members author_member
      ON author_member.id = service_member.member_id
     AND author_member.workspace_id = installation.workspace_id
     AND author_member.type = 'service'
     AND author_member.status = 'active'
    JOIN scopes message_scope
      ON message_scope.id = $5
     AND message_scope.workspace_id = installation.workspace_id
     AND message_scope.type = 'service_account'
     AND message_scope.status = 'active'
     AND message_scope.identity_key = $10
    WHERE installation.id = $2
      AND installation.workspace_id = $1
      AND installation.service_definition_id = $7
      AND installation.service_kind = 'service_account'
      AND installation.status = 'active'
      AND installation.instance_key = $9
      AND (
          NULLIF($8, '') IS NULL
          OR EXISTS (
              SELECT 1
              FROM members creator
              WHERE creator.id = $8
                AND creator.workspace_id = $1
                AND creator.type = 'human'
                AND creator.status = 'active'
          )
      )
)
	`, account.WorkspaceID, account.ServiceInstallationID, account.ServiceMemberID,
		account.AuthorMemberID, account.MessageScopeID, normalizedName(account.Name),
		ServiceDefinitionID, account.CreatedByMemberID, account.PrincipalID,
		fmt.Sprintf("service_account:%s:%s", account.WorkspaceID, account.PrincipalID)).Scan(&matches); err != nil {
		return fmt.Errorf("validate service account identity substrate: %w", err)
	}
	if !matches {
		return conflict("service account identity substrate does not belong to the requested workspace or service account")
	}
	var creatorMatches bool
	if managementKind == ManagementKindWorkspace {
		if err := queryer.QueryRow(ctx, `
SELECT EXISTS (
    SELECT 1
    FROM service_installations
    WHERE id = $1
      AND workspace_id = $2
      AND created_by_member_id = $3
      AND created_by_actor_type = 'human'
      AND created_by_actor_id = $3
)
`, account.ServiceInstallationID, account.WorkspaceID, account.CreatedByMemberID).Scan(&creatorMatches); err != nil {
			return fmt.Errorf("validate workspace service account creator shape: %w", err)
		}
	} else {
		if err := queryer.QueryRow(ctx, `
SELECT EXISTS (
    SELECT 1
    FROM service_installations
    WHERE id = $1
      AND workspace_id = $2
      AND created_by_member_id IS NULL
      AND created_by_actor_type = 'platform_operator'
      AND created_by_actor_id = $3
)
`, account.ServiceInstallationID, account.WorkspaceID, account.CreatedByOperatorUserID).Scan(&creatorMatches); err != nil {
			return fmt.Errorf("validate platform service account creator shape: %w", err)
		}
	}
	if !creatorMatches {
		return conflict("service account installation creator shape does not match its management kind")
	}
	if managementKind == ManagementKindPlatform {
		var platformMatches bool
		if err := queryer.QueryRow(ctx, `
SELECT EXISTS (
    SELECT 1
    FROM platform_service_accounts
    WHERE id = $1
      AND normalized_name = $2
      AND EXISTS (SELECT 1 FROM users WHERE id = $3)
)
`, account.PlatformServiceAccountID, normalizedName(account.Name), account.CreatedByOperatorUserID).Scan(&platformMatches); err != nil {
			return fmt.Errorf("validate platform service account principal: %w", err)
		}
		if !platformMatches {
			return conflict("platform service account principal or name does not match the local account")
		}
	}
	return nil
}

func (store *Store) LoadAccount(ctx context.Context, workspaceID string, accountID string) (Account, error) {
	q, err := queryer(store)
	if err != nil {
		return Account{}, err
	}
	return loadAccount(ctx, q, workspaceID, accountID, "")
}

func (store *Store) LoadAccountByScope(ctx context.Context, workspaceID string, scopeID string) (Account, error) {
	q, err := queryer(store)
	if err != nil {
		return Account{}, err
	}
	return loadAccountByScope(ctx, q, workspaceID, scopeID, "")
}

func (store *TxStore) LoadAccountForUpdate(ctx context.Context, workspaceID string, accountID string) (Account, error) {
	q, err := txExecer(store)
	if err != nil {
		return Account{}, err
	}
	return loadAccount(ctx, q, workspaceID, accountID, "FOR UPDATE")
}

func loadAccount(ctx context.Context, q coredb.QueryRower, workspaceID string, accountID string, suffix string) (Account, error) {
	workspaceID = trim(workspaceID)
	accountID = trim(accountID)
	if workspaceID == "" || accountID == "" {
		return Account{}, badRequest("workspaceId and serviceAccountId are required")
	}
	row := q.QueryRow(ctx, accountSelectSQL()+` WHERE workspace_id = $1 AND id = $2 `+suffix, workspaceID, accountID)
	account, err := scanAccount(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return Account{}, notFound("service account not found")
		}
		return Account{}, fmt.Errorf("load service account: %w", err)
	}
	return account, nil
}

func loadAccountByScope(ctx context.Context, q coredb.QueryRower, workspaceID string, scopeID string, suffix string) (Account, error) {
	workspaceID = trim(workspaceID)
	scopeID = trim(scopeID)
	if workspaceID == "" || scopeID == "" {
		return Account{}, badRequest("workspaceId and scopeId are required")
	}
	row := q.QueryRow(ctx, accountSelectSQL()+` WHERE workspace_id = $1 AND message_scope_id = $2 `+suffix, workspaceID, scopeID)
	account, err := scanAccount(row)
	if err != nil {
		if coredb.IsNotFound(err) {
			return Account{}, notFound("service account not found")
		}
		return Account{}, fmt.Errorf("load service account by scope: %w", err)
	}
	return account, nil
}

// UpdateWorkspaceAccountAvatar applies one managed avatar asset to a
// workspace-owned account under the caller's existing row lock.
func (store *TxStore) UpdateWorkspaceAccountAvatar(ctx context.Context, workspaceID string, accountID string, expectedVersion int64, avatarAssetID string, now time.Time) (Account, error) {
	execer, err := txExecer(store)
	if err != nil {
		return Account{}, err
	}
	now = utcOrNow(now)
	result, err := execer.Exec(ctx, `
UPDATE service_accounts
SET avatar_asset_id = $4,
    version = version + 1,
    updated_at = $5
WHERE workspace_id = $1
  AND id = $2
  AND version = $3
  AND management_kind = 'workspace'
`, trim(workspaceID), trim(accountID), expectedVersion, trim(avatarAssetID), now)
	if err != nil {
		return Account{}, fmt.Errorf("update service account avatar: %w", err)
	}
	if result.RowsAffected() == 0 {
		return Account{}, conflict("service account profile changed")
	}
	return loadAccount(ctx, execer, workspaceID, accountID, "")
}

func (store *Store) ListAccounts(ctx context.Context, workspaceID string) ([]Account, error) {
	q, err := queryer(store)
	if err != nil {
		return nil, err
	}
	query := accountSelectSQL() + ` WHERE workspace_id = $1 ORDER BY name ASC, id ASC`
	rows, err := q.Query(ctx, query, trim(workspaceID))
	if err != nil {
		return nil, fmt.Errorf("list service accounts: %w", err)
	}
	defer rows.Close()
	accounts := make([]Account, 0)
	for rows.Next() {
		account, err := scanAccount(rows)
		if err != nil {
			return nil, fmt.Errorf("scan service account: %w", err)
		}
		accounts = append(accounts, account)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate service accounts: %w", err)
	}
	return accounts, nil
}

// ListAccountPage returns one bounded, id-ordered directory page. Search is
// evaluated before the cursor so paging remains stable for one query.
func (store *Store) ListAccountPage(ctx context.Context, workspaceID string, afterID string, query string, limit int) ([]Account, error) {
	q, err := queryer(store)
	if err != nil {
		return nil, err
	}
	if limit <= 0 || limit > 100 {
		return nil, badRequest("limit must be between 1 and 100")
	}
	searchPattern := strings.ToLower(trim(query))
	if searchPattern != "" {
		searchPattern = "%" + searchPattern + "%"
	}
	statement := accountSelectSQL() + `
WHERE workspace_id = $1
  AND id > $2
  AND ($3 = '' OR LOWER(CONCAT_WS(' ', id, principal_id, message_scope_id, name, description, management_kind)) LIKE $3)`
	statement += ` ORDER BY id ASC LIMIT $4`
	rows, err := q.Query(ctx, statement, trim(workspaceID), trim(afterID), searchPattern, limit)
	if err != nil {
		return nil, fmt.Errorf("list service account page: %w", err)
	}
	defer rows.Close()
	accounts := make([]Account, 0, limit)
	for rows.Next() {
		account, scanErr := scanAccount(rows)
		if scanErr != nil {
			return nil, fmt.Errorf("scan service account page: %w", scanErr)
		}
		accounts = append(accounts, account)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate service account page: %w", err)
	}
	return accounts, nil
}

// MarkAudienceReady advances the effective audience version after membership
// reconciliation has committed in the same transaction.
func (store *TxStore) MarkAudienceReady(ctx context.Context, workspaceID string, accountID string, expectedVersion int64, effectiveAudienceVersion int64, now time.Time) (Account, error) {
	execer, err := txExecer(store)
	if err != nil {
		return Account{}, err
	}
	now = utcOrNow(now)
	result, err := execer.Exec(ctx, `
UPDATE service_accounts
SET effective_audience_version = $4,
    audience_state = 'ready',
    version = version + 1,
    updated_at = $5
WHERE workspace_id = $1
  AND id = $2
  AND version = $3
  AND audience_policy_version = $4
`, trim(workspaceID), trim(accountID), expectedVersion, effectiveAudienceVersion, now)
	if err != nil {
		return Account{}, fmt.Errorf("mark service account audience ready: %w", err)
	}
	if result.RowsAffected() == 0 {
		return Account{}, conflict("service account audience version changed")
	}
	return loadAccount(ctx, execer, workspaceID, accountID, "")
}

func accountSelectSQL() string {
	return `
SELECT id, principal_id, workspace_id, management_kind,
       COALESCE(platform_service_account_id, ''), service_installation_id,
       service_member_id, author_member_id, message_scope_id, name,
       description, avatar_asset_id,
       COALESCE((
         SELECT service_member.avatar_url
         FROM service_members service_member
         WHERE service_member.id = service_accounts.service_member_id
       ), ''),
       applied_platform_profile_version, audience_policy_version,
       effective_audience_version, audience_state, version,
       COALESCE(created_by_member_id, ''), COALESCE(created_by_operator_user_id, ''),
       created_at, updated_at
FROM service_accounts`
}

func scanAccount(scanner interface{ Scan(...any) error }) (Account, error) {
	var account Account
	if err := scanner.Scan(
		&account.ID,
		&account.PrincipalID,
		&account.WorkspaceID,
		&account.ManagementKind,
		&account.PlatformServiceAccountID,
		&account.ServiceInstallationID,
		&account.ServiceMemberID,
		&account.AuthorMemberID,
		&account.MessageScopeID,
		&account.Name,
		&account.Description,
		&account.AvatarAssetID,
		&account.AvatarURL,
		&account.AppliedPlatformProfileVersion,
		&account.AudiencePolicyVersion,
		&account.EffectiveAudienceVersion,
		&account.AudienceState,
		&account.Version,
		&account.CreatedByMemberID,
		&account.CreatedByOperatorUserID,
		&account.CreatedAt,
		&account.UpdatedAt,
	); err != nil {
		return Account{}, err
	}
	return account, nil
}

func mapAccountWriteError(err error) error {
	if err == nil {
		return nil
	}
	if constraint, ok := coredb.UniqueViolationConstraint(err); ok {
		switch constraint {
		case "platform_service_accounts_name_key":
			return conflict("platform service account name already exists in this deployment")
		case "service_accounts_workspace_principal_key",
			"service_accounts_workspace_name_key",
			"service_account_name_claims_workspace_name_key":
			return conflict("service account name or principal already exists in this workspace")
		case "service_accounts_scope_key",
			"service_accounts_installation_key",
			"service_accounts_service_member_key",
			"service_accounts_author_member_key",
			"service_account_name_claims_account_key",
			"platform_sa_workspace_instances_platform_workspace_key",
			"platform_sa_workspace_instances_local_account_key":
			return conflict("service account identity substrate is already assigned")
		}
	}
	if errors.Is(err, sql.ErrNoRows) {
		return notFound("service account not found")
	}
	return err
}