package serviceaccount

import (
	"context"
	"fmt"
)

// ListPlatformLocalAccountsForUpdate locks every workspace-local projection
// owned by one platform principal. The control-plane delete transaction uses
// the returned substrate refs to remove each projection before deleting the
// global principal.
func (store *TxStore) ListPlatformLocalAccountsForUpdate(ctx context.Context, platformAccountID string) ([]Account, error) {
	q, err := txExecer(store)
	if err != nil {
		return nil, err
	}
	rows, err := q.Query(ctx, accountSelectSQL()+`
WHERE platform_service_account_id = $1
  AND management_kind = 'platform'
ORDER BY workspace_id ASC, id ASC
FOR UPDATE
`, trim(platformAccountID))
	if err != nil {
		return nil, fmt.Errorf("lock platform local service accounts: %w", err)
	}
	defer rows.Close()
	accounts := make([]Account, 0)
	for rows.Next() {
		account, scanErr := scanAccount(rows)
		if scanErr != nil {
			return nil, fmt.Errorf("scan platform local service account: %w", scanErr)
		}
		accounts = append(accounts, account)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate platform local service accounts: %w", err)
	}
	return accounts, nil
}

// DeletePlatformWorkspaceInstances removes the global-to-local ownership rows
// before their local account and platform-principal foreign keys disappear.
func (store *TxStore) DeletePlatformWorkspaceInstances(ctx context.Context, platformAccountID string) error {
	execer, err := txExecer(store)
	if err != nil {
		return err
	}
	if _, err := execer.Exec(ctx, `
DELETE FROM platform_service_account_workspace_instances
WHERE platform_service_account_id = $1
`, trim(platformAccountID)); err != nil {
		return fmt.Errorf("delete platform service account workspace instances: %w", err)
	}
	return nil
}

// DeleteLocalAccountSubstrate permanently removes one local account and the
// identity, message scope, Inbox projections, audience/read state, and Service
// Actor rows that it exclusively owns. Callers must lock the account first.
// Workspace creation requests are removed with their deleted result so no
// fulfilled row points at an object that no longer exists.
func (store *TxStore) DeleteLocalAccountSubstrate(ctx context.Context, account Account) error {
	execer, err := txExecer(store)
	if err != nil {
		return err
	}
	if trim(account.ID) == "" || trim(account.WorkspaceID) == "" || account.Version <= 0 {
		return badRequest("service account identity, workspace, and version are required for deletion")
	}
	if _, err := execer.Exec(ctx, `
DELETE FROM service_account_create_requests
WHERE created_service_account_id = $1
`, account.ID); err != nil {
		return fmt.Errorf("delete service account create request: %w", err)
	}
	if _, err := execer.Exec(ctx, `
DELETE FROM im_conversation_index
WHERE workspace_id = $1 AND scope_id = $2
`, account.WorkspaceID, account.MessageScopeID); err != nil {
		return fmt.Errorf("delete service account conversation index: %w", err)
	}
	result, err := execer.Exec(ctx, `
DELETE FROM service_accounts
WHERE id = $1 AND workspace_id = $2 AND version = $3
`, account.ID, account.WorkspaceID, account.Version)
	if err != nil {
		return fmt.Errorf("delete service account: %w", err)
	}
	if result.RowsAffected() == 0 {
		return conflict("service account version changed")
	}
	if _, err := execer.Exec(ctx, `DELETE FROM service_installations WHERE id = $1`, account.ServiceInstallationID); err != nil {
		return fmt.Errorf("delete service account installation: %w", err)
	}
	if _, err := execer.Exec(ctx, `DELETE FROM scopes WHERE id = $1 AND workspace_id = $2`, account.MessageScopeID, account.WorkspaceID); err != nil {
		return fmt.Errorf("delete service account message scope: %w", err)
	}
	if _, err := execer.Exec(ctx, `DELETE FROM members WHERE id = $1 AND workspace_id = $2 AND type = 'service'`, account.AuthorMemberID, account.WorkspaceID); err != nil {
		return fmt.Errorf("delete service account author member: %w", err)
	}
	if _, err := execer.Exec(ctx, `
DELETE FROM avatars
WHERE owner_kind = 'service_account' AND owner_id = $1
`, account.ID); err != nil {
		return fmt.Errorf("delete service account avatar assets: %w", err)
	}
	return nil
}

// DeletePlatformAccount deletes the global principal after every local
// projection and namespace ownership row has been removed in the same
// transaction.
func (store *TxStore) DeletePlatformAccount(ctx context.Context, account PlatformAccount) error {
	execer, err := txExecer(store)
	if err != nil {
		return err
	}
	if trim(account.ID) == "" || account.Version <= 0 {
		return badRequest("platform service account identity and version are required for deletion")
	}
	if _, err := execer.Exec(ctx, `
DELETE FROM service_account_name_namespaces
WHERE platform_service_account_id = $1
`, account.ID); err != nil {
		return fmt.Errorf("release platform service account name: %w", err)
	}
	result, err := execer.Exec(ctx, `
DELETE FROM platform_service_accounts
WHERE id = $1 AND version = $2
`, account.ID, account.Version)
	if err != nil {
		return fmt.Errorf("delete platform service account: %w", err)
	}
	if result.RowsAffected() == 0 {
		return conflict("platform service account version changed")
	}
	if _, err := execer.Exec(ctx, `
DELETE FROM avatars
WHERE owner_kind = 'service_account' AND owner_id = $1
`, account.ID); err != nil {
		return fmt.Errorf("delete platform service account avatar assets: %w", err)
	}
	return nil
}