package serviceaccount

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

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

// InsertPlatformAccount persists a global platform principal after its name
// namespace has been locked and verified by the caller.
func (store *TxStore) InsertPlatformAccount(ctx context.Context, account PlatformAccount) (PlatformAccount, error) {
	execer, err := txExecer(store)
	if err != nil {
		return PlatformAccount{}, err
	}
	account.ID = trim(account.ID)
	account.Name, err = NormalizeName(account.Name)
	if err != nil {
		return PlatformAccount{}, err
	}
	account.CreatedByOperatorUserID = trim(account.CreatedByOperatorUserID)
	if account.ID == "" || account.Name == "" || account.CreatedByOperatorUserID == "" {
		return PlatformAccount{}, badRequest("platform service account identity, name, and operator are required")
	}
	if account.ProfileVersion <= 0 {
		account.ProfileVersion = 1
	}
	if account.Version <= 0 {
		account.Version = 1
	}
	if account.ProvisioningMode == "" {
		account.ProvisioningMode = "all_workspaces"
	}
	if account.ProvisioningState == "" {
		account.ProvisioningState = "pending"
	}
	if account.AudiencePolicyVersion <= 0 {
		account.AudiencePolicyVersion = 1
	}
	account.CreatedAt = utcOrNow(account.CreatedAt)
	account.UpdatedAt = utcOrNow(account.UpdatedAt)
	if _, err := execer.Exec(ctx, `
INSERT INTO platform_service_accounts (
    id, name, description, avatar_asset_id, profile_version,
    version, provisioning_mode, provisioning_state,
    applied_provisioning_generation, audience_policy_version,
    latest_broadcast_seq, created_by_operator_user_id, created_at, updated_at
) VALUES (
    $1, $2, $3, $4, $5,
    $6, $7, $8,
    $9, $10,
    $11, $12, $13, $14
)
`, account.ID, account.Name, account.Description, trim(account.AvatarAssetID), account.ProfileVersion,
		account.Version, account.ProvisioningMode, account.ProvisioningState,
		account.AppliedProvisioningGeneration, account.AudiencePolicyVersion,
		account.LatestBroadcastSeq, account.CreatedByOperatorUserID, account.CreatedAt, account.UpdatedAt); err != nil {
		return PlatformAccount{}, fmt.Errorf("insert platform service account: %w", mapAccountWriteError(err))
	}
	normalized := normalizedName(account.Name)
	var workspaceClaims int
	if err := execer.QueryRow(ctx, `
SELECT COUNT(*) FROM service_account_name_claims WHERE normalized_name = $1
`, normalized).Scan(&workspaceClaims); err != nil {
		return PlatformAccount{}, fmt.Errorf("check workspace service account name claims: %w", err)
	}
	if workspaceClaims != 0 {
		return PlatformAccount{}, conflict("platform service account name is already used by workspace accounts")
	}
	result, err := execer.Exec(ctx, `
UPDATE service_account_name_namespaces
SET platform_service_account_id = $2, version = version + 1, updated_at = $3
WHERE normalized_name = $1 AND platform_service_account_id IS NULL
`, normalized, account.ID, account.UpdatedAt)
	if err != nil {
		return PlatformAccount{}, fmt.Errorf("claim platform service account name namespace: %w", err)
	}
	if result.RowsAffected() == 0 {
		return PlatformAccount{}, conflict("platform service account name is already owned")
	}
	return loadPlatformAccount(ctx, execer, account.ID, "")
}

func (store *Store) LoadPlatformAccount(ctx context.Context, accountID string) (PlatformAccount, error) {
	q, err := queryer(store)
	if err != nil {
		return PlatformAccount{}, err
	}
	return loadPlatformAccount(ctx, q, accountID, "")
}

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

// UpdatePlatformAccountAvatar advances the global profile version under an
// optimistic root-version fence.
func (store *TxStore) UpdatePlatformAccountAvatar(ctx context.Context, accountID string, expectedVersion int64, avatarAssetID string, now time.Time) (PlatformAccount, error) {
	execer, err := txExecer(store)
	if err != nil {
		return PlatformAccount{}, err
	}
	now = utcOrNow(now)
	result, err := execer.Exec(ctx, `
UPDATE platform_service_accounts
SET avatar_asset_id = $3,
    profile_version = profile_version + 1,
    version = version + 1,
    updated_at = $4
WHERE id = $1
  AND version = $2
`, trim(accountID), expectedVersion, trim(avatarAssetID), now)
	if err != nil {
		return PlatformAccount{}, fmt.Errorf("update platform service account avatar: %w", err)
	}
	if result.RowsAffected() == 0 {
		return PlatformAccount{}, conflict("platform service account profile changed")
	}
	return loadPlatformAccount(ctx, execer, accountID, "")
}

// ListPlatformAccounts returns a stable id-ordered, optionally filtered page
// of global platform principals. The caller owns any opaque cursor wrapping at
// the API boundary.
func (store *Store) ListPlatformAccounts(ctx context.Context, afterID string, query string, limit int) ([]PlatformAccount, 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 + "%"
	}
	rows, err := q.Query(ctx, platformAccountSelectSQL()+`
WHERE id > $1
  AND ($2 = '' OR LOWER(CONCAT_WS(' ', id, name, description)) LIKE $2)
ORDER BY id ASC
LIMIT $3
`, trim(afterID), searchPattern, limit)
	if err != nil {
		return nil, fmt.Errorf("list platform service accounts: %w", err)
	}
	defer rows.Close()
	accounts := make([]PlatformAccount, 0, limit)
	for rows.Next() {
		account, scanErr := scanPlatformAccount(rows)
		if scanErr != nil {
			return nil, fmt.Errorf("scan platform service account: %w", scanErr)
		}
		accounts = append(accounts, account)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate platform service accounts: %w", err)
	}
	return accounts, nil
}

func loadPlatformAccount(ctx context.Context, q coredb.QueryRower, accountID string, suffix string) (PlatformAccount, error) {
	account, err := scanPlatformAccount(q.QueryRow(ctx, platformAccountSelectSQL()+` WHERE id = $1 `+suffix, trim(accountID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return PlatformAccount{}, notFound("platform service account not found")
		}
		return PlatformAccount{}, fmt.Errorf("load platform service account: %w", err)
	}
	return account, nil
}

// InsertPlatformWorkspaceInstance writes one durable global-to-local binding.
func (store *TxStore) InsertPlatformWorkspaceInstance(ctx context.Context, instance PlatformWorkspaceInstance) (PlatformWorkspaceInstance, error) {
	execer, err := txExecer(store)
	if err != nil {
		return PlatformWorkspaceInstance{}, err
	}
	instance.ID = trim(instance.ID)
	instance.PlatformServiceAccountID = trim(instance.PlatformServiceAccountID)
	instance.WorkspaceID = trim(instance.WorkspaceID)
	instance.LocalServiceAccountID = trim(instance.LocalServiceAccountID)
	if instance.ID == "" || instance.PlatformServiceAccountID == "" || instance.WorkspaceID == "" || instance.LocalServiceAccountID == "" {
		return PlatformWorkspaceInstance{}, badRequest("platform service account workspace binding identity is required")
	}
	var localMatches bool
	if err := execer.QueryRow(ctx, `
SELECT EXISTS (
    SELECT 1
    FROM service_accounts
    WHERE id = $1
      AND workspace_id = $2
      AND management_kind = 'platform'
      AND platform_service_account_id = $3
      AND principal_id = $3
)
`, instance.LocalServiceAccountID, instance.WorkspaceID, instance.PlatformServiceAccountID).Scan(&localMatches); err != nil {
		return PlatformWorkspaceInstance{}, fmt.Errorf("validate platform service account workspace instance: %w", err)
	}
	if !localMatches {
		return PlatformWorkspaceInstance{}, conflict("platform workspace instance does not match its local service account")
	}
	if instance.ProvisioningState == "" {
		instance.ProvisioningState = "pending"
	}
	if instance.Version <= 0 {
		instance.Version = 1
	}
	instance.CreatedAt = utcOrNow(instance.CreatedAt)
	instance.UpdatedAt = utcOrNow(instance.UpdatedAt)
	if _, err := execer.Exec(ctx, `
INSERT INTO platform_service_account_workspace_instances (
    id, platform_service_account_id, workspace_id, local_service_account_id,
    opened_provisioning_generation, effective_from_broadcast_seq,
    effective_to_broadcast_seq, last_applied_broadcast_seq,
    applied_profile_version, provisioning_state, version, created_at, updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
`, instance.ID, instance.PlatformServiceAccountID, instance.WorkspaceID, instance.LocalServiceAccountID,
		instance.OpenedProvisioningGeneration, instance.EffectiveFromBroadcastSeq,
		instance.EffectiveToBroadcastSeq, instance.LastAppliedBroadcastSeq,
		instance.AppliedProfileVersion, instance.ProvisioningState, instance.Version,
		instance.CreatedAt, instance.UpdatedAt); err != nil {
		return PlatformWorkspaceInstance{}, fmt.Errorf("insert platform service account workspace instance: %w", mapAccountWriteError(err))
	}
	return loadPlatformWorkspaceInstance(ctx, execer, instance.PlatformServiceAccountID, instance.WorkspaceID)
}

func loadPlatformWorkspaceInstance(ctx context.Context, q coredb.QueryRower, platformID string, workspaceID string) (PlatformWorkspaceInstance, error) {
	instance, err := scanPlatformWorkspaceInstance(q.QueryRow(ctx, platformWorkspaceInstanceSelectSQL()+`
WHERE platform_service_account_id = $1 AND workspace_id = $2
`, trim(platformID), trim(workspaceID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return PlatformWorkspaceInstance{}, notFound("platform service account workspace instance not found")
		}
		return PlatformWorkspaceInstance{}, fmt.Errorf("load platform service account workspace instance: %w", err)
	}
	return instance, nil
}

// LoadPlatformWorkspaceInstance resolves one durable global-to-local binding.
func (store *Store) LoadPlatformWorkspaceInstance(ctx context.Context, platformID string, workspaceID string) (PlatformWorkspaceInstance, error) {
	q, err := queryer(store)
	if err != nil {
		return PlatformWorkspaceInstance{}, err
	}
	return loadPlatformWorkspaceInstance(ctx, q, platformID, workspaceID)
}

// LoadPlatformWorkspaceInstanceForUpdate locks one durable binding.
func (store *TxStore) LoadPlatformWorkspaceInstanceForUpdate(ctx context.Context, platformID string, workspaceID string) (PlatformWorkspaceInstance, error) {
	q, err := txExecer(store)
	if err != nil {
		return PlatformWorkspaceInstance{}, err
	}
	instance, err := scanPlatformWorkspaceInstance(q.QueryRow(ctx, platformWorkspaceInstanceSelectSQL()+`
WHERE platform_service_account_id = $1 AND workspace_id = $2
FOR UPDATE
`, trim(platformID), trim(workspaceID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return PlatformWorkspaceInstance{}, notFound("platform service account workspace instance not found")
		}
		return PlatformWorkspaceInstance{}, fmt.Errorf("lock platform service account workspace instance: %w", err)
	}
	return instance, nil
}

// ListPlatformWorkspaceInstances returns a stable workspace-ordered page.
func (store *Store) ListPlatformWorkspaceInstances(ctx context.Context, platformID string, afterWorkspaceID string, limit int) ([]PlatformWorkspaceInstance, error) {
	q, err := queryer(store)
	if err != nil {
		return nil, err
	}
	if limit <= 0 || limit > 500 {
		return nil, badRequest("limit must be between 1 and 500")
	}
	rows, err := q.Query(ctx, platformWorkspaceInstanceSelectSQL()+`
WHERE platform_service_account_id = $1
  AND workspace_id > $2
ORDER BY workspace_id ASC
LIMIT $3
`, trim(platformID), trim(afterWorkspaceID), limit)
	if err != nil {
		return nil, fmt.Errorf("list platform service account workspace instances: %w", err)
	}
	defer rows.Close()
	instances := make([]PlatformWorkspaceInstance, 0, limit)
	for rows.Next() {
		instance, scanErr := scanPlatformWorkspaceInstance(rows)
		if scanErr != nil {
			return nil, fmt.Errorf("scan platform service account workspace instance: %w", scanErr)
		}
		instances = append(instances, instance)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate platform service account workspace instances: %w", err)
	}
	return instances, nil
}

func platformAccountSelectSQL() string {
	return `
SELECT id, name, description, avatar_asset_id, profile_version,
       version, provisioning_mode, provisioning_state,
       applied_provisioning_generation, audience_policy_version,
       latest_broadcast_seq, created_by_operator_user_id, created_at, updated_at
FROM platform_service_accounts`
}

func scanPlatformAccount(scanner interface{ Scan(...any) error }) (PlatformAccount, error) {
	var account PlatformAccount
	if err := scanner.Scan(
		&account.ID,
		&account.Name,
		&account.Description,
		&account.AvatarAssetID,
		&account.ProfileVersion,
		&account.Version,
		&account.ProvisioningMode,
		&account.ProvisioningState,
		&account.AppliedProvisioningGeneration,
		&account.AudiencePolicyVersion,
		&account.LatestBroadcastSeq,
		&account.CreatedByOperatorUserID,
		&account.CreatedAt,
		&account.UpdatedAt,
	); err != nil {
		return PlatformAccount{}, err
	}
	return account, nil
}

func platformWorkspaceInstanceSelectSQL() string {
	return `
SELECT id, platform_service_account_id, workspace_id, local_service_account_id,
       opened_provisioning_generation, effective_from_broadcast_seq,
       effective_to_broadcast_seq, last_applied_broadcast_seq,
       applied_profile_version, provisioning_state, version, created_at, updated_at
FROM platform_service_account_workspace_instances`
}

func scanPlatformWorkspaceInstance(scanner interface{ Scan(...any) error }) (PlatformWorkspaceInstance, error) {
	var instance PlatformWorkspaceInstance
	var effectiveTo sql.NullInt64
	if err := scanner.Scan(
		&instance.ID,
		&instance.PlatformServiceAccountID,
		&instance.WorkspaceID,
		&instance.LocalServiceAccountID,
		&instance.OpenedProvisioningGeneration,
		&instance.EffectiveFromBroadcastSeq,
		&effectiveTo,
		&instance.LastAppliedBroadcastSeq,
		&instance.AppliedProfileVersion,
		&instance.ProvisioningState,
		&instance.Version,
		&instance.CreatedAt,
		&instance.UpdatedAt,
	); err != nil {
		return PlatformWorkspaceInstance{}, err
	}
	if effectiveTo.Valid {
		value := effectiveTo.Int64
		instance.EffectiveToBroadcastSeq = &value
	}
	return instance, nil
}