package organization

import (
	"context"
	"errors"
	"fmt"
	"time"

	"github.com/tidbcloud/AgentTeam/internal/core/domain"
	"github.com/tidbcloud/AgentTeam/internal/core/identifiers"
	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

type organizationWorkspaceMemberProjectionRecord struct {
	ID                    string
	MemberID              string
	MemberCreatedByOrg    bool
	BaselineWorkspaceRole string
	HasBaselineRole       bool
	AppliedWorkspaceRole  string
	AppliedSource         string
}

type humanWorkspaceMemberRecord struct {
	ID            string
	WorkspaceRole string
	Status        string
}

type organizationMemberProjectionTarget struct {
	MemberID                 string
	UserID                   string
	DefaultWorkspaceRole     string
	MemberStatus             string
	OrganizationMemberStatus string
}

type organizationWorkspaceAccessCandidate struct {
	Role   string
	Source string
}

func expectedProjectionMaterializedRole(projection organizationWorkspaceMemberProjectionRecord) string {
	if projection.HasBaselineRole {
		return higherWorkspaceRole(projection.BaselineWorkspaceRole, projection.AppliedWorkspaceRole)
	}
	return projection.AppliedWorkspaceRole
}

func reconcileProjectionBaselineDrift(
	projection organizationWorkspaceMemberProjectionRecord,
	hasCurrentMember bool,
	currentMember humanWorkspaceMemberRecord,
) organizationWorkspaceMemberProjectionRecord {
	if !hasCurrentMember || currentMember.Status != "active" {
		return projection
	}
	if organizationWorkspaceRoleRank(currentMember.WorkspaceRole) == 0 {
		return projection
	}
	if currentMember.WorkspaceRole == expectedProjectionMaterializedRole(projection) {
		return projection
	}

	projection.MemberCreatedByOrg = false
	projection.BaselineWorkspaceRole = currentMember.WorkspaceRole
	projection.HasBaselineRole = true
	return projection
}

func organizationWorkspaceTeamGrantSource(teamSource string) string {
	switch teamSource {
	case "unit_backed":
		return "unit_backed_team"
	case "synced":
		return "sync_rule"
	default:
		return "team"
	}
}

func (txs TxStore) ListOrganizationWorkspaceTeamGrantsForUser(ctx context.Context, userID string, organizationID string, workspaceID string) ([]OrganizationWorkspaceTeamGrantView, error) {
	if _, err := txs.requireActiveOrganizationMembership(ctx, txs.db, userID, organizationID); err != nil {
		return nil, err
	}
	if err := txs.requireOrganizationWorkspaceBinding(ctx, txs.db, organizationID, workspaceID); err != nil {
		return nil, err
	}

	rows, err := txs.db.Query(ctx, `
SELECT twg.workspace_id,
       t.id,
       t.name,
       twg.workspace_role,
       t.source,
       twg.status,
       COUNT(DISTINCT CASE
         WHEN tm.status = 'active' AND om.status = 'active'
         THEN tm.organization_member_id
         ELSE NULL
       END)
FROM organization_workspace_team_grants twg
JOIN organization_teams t ON t.id = twg.organization_team_id
LEFT JOIN organization_member_team_memberships tm
  ON tm.organization_id = twg.organization_id
 AND tm.organization_team_id = twg.organization_team_id
LEFT JOIN organization_members om ON om.id = tm.organization_member_id
WHERE twg.organization_id = $1
  AND twg.workspace_id = $2
  AND twg.status = 'active'
GROUP BY twg.workspace_id, t.id, t.name, t.source, twg.workspace_role, twg.status, twg.created_at
ORDER BY twg.created_at ASC, t.name ASC, t.id ASC
`, organizationID, workspaceID)
	if err != nil {
		return nil, fmt.Errorf("list organization workspace team grants: %w", err)
	}
	defer rows.Close()

	grants := make([]OrganizationWorkspaceTeamGrantView, 0)
	for rows.Next() {
		var grant OrganizationWorkspaceTeamGrantView
		var teamSource string
		if err := rows.Scan(
			&grant.WorkspaceID,
			&grant.TeamID,
			&grant.TeamName,
			&grant.Role,
			&teamSource,
			&grant.Status,
			&grant.MemberCount,
		); err != nil {
			return nil, fmt.Errorf("scan organization workspace team grant: %w", err)
		}
		grant.Source = organizationWorkspaceTeamGrantSource(teamSource)
		grants = append(grants, grant)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization workspace team grants: %w", err)
	}
	return grants, nil
}

func (txs TxStore) GrantOrganizationTeamWorkspaceAccessForUser(ctx context.Context, userID string, organizationID string, workspaceID string, teamID string, role string) (OrganizationWorkspaceTeamGrantView, error) {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return OrganizationWorkspaceTeamGrantView{}, err
	}
	if err := txs.core.RequireWorkspaceMemberManagementForUser(ctx, userID, workspaceID); err != nil {
		return OrganizationWorkspaceTeamGrantView{}, err
	}

	return withTxValue(ctx, txs.db, func(tx coredb.Tx) (OrganizationWorkspaceTeamGrantView, error) {
		if err := txs.requireOrganizationWorkspaceBinding(ctx, tx, organizationID, workspaceID); err != nil {
			return OrganizationWorkspaceTeamGrantView{}, err
		}
		team, err := txs.loadOrganizationTeam(ctx, tx, organizationID, teamID)
		if err != nil {
			return OrganizationWorkspaceTeamGrantView{}, err
		}
		if team.Status != organizationTeamStatusActive {
			return OrganizationWorkspaceTeamGrantView{}, badRequest("team is not active")
		}
		resolvedWorkspaceRole, err := resolveOrganizationWorkspaceRole(role, string(domain.WorkspaceRoleMember))
		if err != nil {
			return OrganizationWorkspaceTeamGrantView{}, err
		}

		grantID, err := identifiers.NewEntityID("ogtg")
		if err != nil {
			return OrganizationWorkspaceTeamGrantView{}, fmt.Errorf("generate organization team grant id: %w", err)
		}
		now := time.Now().UTC()
		if err := execPortableUpdateThenInsert(
			ctx,
			tx,
			`
SELECT 1
FROM organization_workspace_team_grants
WHERE workspace_id = $1
  AND organization_team_id = $2
`,
			[]any{workspaceID, teamID},
			`
UPDATE organization_workspace_team_grants
SET workspace_role = $3,
    status = 'active',
    source = 'manual',
    granted_by_user_id = $4,
    updated_at = $5
WHERE workspace_id = $1
  AND organization_team_id = $2
`,
			[]any{workspaceID, teamID, resolvedWorkspaceRole, userID, now},
			`
INSERT INTO organization_workspace_team_grants (
    id,
    organization_id,
    workspace_id,
    organization_team_id,
    workspace_role,
    status,
    source,
    granted_by_user_id,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, $5, 'active', 'manual', $6, $7, $7)
`,
			[]any{grantID, organizationID, workspaceID, teamID, resolvedWorkspaceRole, userID, now},
		); err != nil {
			return OrganizationWorkspaceTeamGrantView{}, fmt.Errorf("upsert organization workspace team grant: %w", err)
		}

		accountIDs, err := txs.listActiveOrganizationMemberIDsForTeam(ctx, tx, organizationID, teamID)
		if err != nil {
			return OrganizationWorkspaceTeamGrantView{}, err
		}
		for _, accountID := range accountIDs {
			if _, _, _, err := txs.reconcileOrganizationMemberWorkspaceProjection(ctx, tx, organizationID, workspaceID, accountID); err != nil {
				return OrganizationWorkspaceTeamGrantView{}, err
			}
		}

		grant, err := txs.loadOrganizationWorkspaceTeamGrantView(ctx, tx, organizationID, workspaceID, teamID)
		if err != nil {
			return OrganizationWorkspaceTeamGrantView{}, err
		}

		return grant, nil
	})
}

func (txs TxStore) RevokeOrganizationTeamWorkspaceAccessForUser(ctx context.Context, userID string, organizationID string, workspaceID string, teamID string) error {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return err
	}
	if err := txs.core.RequireWorkspaceMemberManagementForUser(ctx, userID, workspaceID); err != nil {
		return err
	}

	return withTx(ctx, txs.db, func(tx coredb.Tx) error {
		if err := txs.requireOrganizationWorkspaceBinding(ctx, tx, organizationID, workspaceID); err != nil {
			return err
		}
		workspace, err := txs.loadOrganizationWorkspace(ctx, tx, organizationID, workspaceID)
		if err != nil {
			return err
		}
		team, err := txs.loadOrganizationTeam(ctx, tx, organizationID, teamID)
		if err != nil {
			return err
		}
		if workspace.IsDefault && team.IsDefault {
			return badRequest("default workspace requires the default team grant")
		}

		commandTag, err := tx.Exec(ctx, `
UPDATE organization_workspace_team_grants
SET status = 'inactive', updated_at = $4
WHERE organization_id = $1 AND workspace_id = $2 AND organization_team_id = $3
`, organizationID, workspaceID, teamID, time.Now().UTC())
		if err != nil {
			return fmt.Errorf("revoke organization workspace team grant: %w", err)
		}
		if commandTag.RowsAffected() == 0 {
			return ErrNotFound
		}

		accountIDs, err := txs.listActiveOrganizationMemberIDsForTeam(ctx, tx, organizationID, teamID)
		if err != nil {
			return err
		}
		for _, accountID := range accountIDs {
			if _, _, _, err := txs.reconcileOrganizationMemberWorkspaceProjection(ctx, tx, organizationID, workspaceID, accountID); err != nil {
				return err
			}
		}

		return nil
	})
}

func (txs TxStore) RevokeOrganizationMemberWorkspaceAccessForUser(ctx context.Context, userID string, organizationID string, workspaceID string, accountID string) error {
	if _, err := txs.requireActiveOrganizationAdmin(ctx, txs.db, userID, organizationID); err != nil {
		return err
	}
	if err := txs.core.RequireWorkspaceMemberManagementForUser(ctx, userID, workspaceID); err != nil {
		return err
	}

	return withTx(ctx, txs.db, func(tx coredb.Tx) error {
		if err := txs.requireOrganizationWorkspaceBinding(ctx, tx, organizationID, workspaceID); err != nil {
			return err
		}
		if _, err := txs.loadOrganizationMemberProjectionTarget(ctx, tx, organizationID, accountID); err != nil {
			return err
		}

		commandTag, err := tx.Exec(ctx, `
UPDATE organization_workspace_member_grants
SET status = 'inactive', updated_at = $4
WHERE organization_id = $1 AND workspace_id = $2 AND organization_member_id = $3
`, organizationID, workspaceID, accountID, time.Now().UTC())
		if err != nil {
			return fmt.Errorf("revoke organization workspace member grant: %w", err)
		}
		if commandTag.RowsAffected() == 0 {
			return ErrNotFound
		}

		if _, _, _, err := txs.reconcileOrganizationMemberWorkspaceProjection(ctx, tx, organizationID, workspaceID, accountID); err != nil {
			return err
		}

		return nil
	})
}

func (txs TxStore) reconcileOrganizationMemberWorkspaceProjection(ctx context.Context, tx coredb.Tx, organizationID string, workspaceID string, accountID string) (string, string, bool, error) {
	account, err := txs.loadOrganizationMemberProjectionTarget(ctx, tx, organizationID, accountID)
	if err != nil {
		return "", "", false, err
	}

	// An inactive or suspended organization member must not receive
	// org-derived workspace access. Missing historical members remain compatible
	// until the workspace/org membership backfill has run and runtime reads move
	// to the final hard constraint.
	// Treat it as if all effective access has been revoked and reconcile accordingly.
	if account.MemberStatus != organizationMemberStatusActive ||
		(account.OrganizationMemberStatus != "" && account.OrganizationMemberStatus != organizationMemberStatusActive) {
		projection, hasProjection, err := txs.loadOrganizationWorkspaceMemberProjection(ctx, tx, organizationID, workspaceID, accountID)
		if err != nil {
			return "", "", false, err
		}
		if !hasProjection {
			return "", "", false, nil
		}
		memberID := projection.MemberID
		if projection.MemberCreatedByOrg {
			memberID, err = txs.core.DeactivateHumanWorkspaceMemberInTx(ctx, tx, workspaceID, account.UserID)
			if err != nil && !errors.Is(err, ErrNotFound) {
				return "", "", false, err
			}
		} else if projection.HasBaselineRole {
			memberID, err = txs.core.RestoreHumanWorkspaceMemberBaselineInTx(ctx, tx, workspaceID, account.UserID, projection.BaselineWorkspaceRole)
			if err != nil {
				return "", "", false, err
			}
		}
		if memberID == "" {
			memberID = projection.MemberID
		}
		if err := txs.upsertOrganizationWorkspaceMemberProjection(
			ctx,
			tx,
			organizationID,
			workspaceID,
			accountID,
			memberID,
			projection.MemberCreatedByOrg,
			projection.BaselineWorkspaceRole,
			projection.HasBaselineRole,
			projection.AppliedWorkspaceRole,
			projection.AppliedSource,
		); err != nil {
			return "", "", false, err
		}
		return memberID, "", false, nil
	}

	projection, hasProjection, err := txs.loadOrganizationWorkspaceMemberProjection(ctx, tx, organizationID, workspaceID, accountID)
	if err != nil {
		return "", "", false, err
	}

	effectiveAccess, hasAccess, err := txs.loadEffectiveOrganizationWorkspaceAccess(ctx, tx, organizationID, workspaceID, accountID)
	if err != nil {
		return "", "", false, err
	}

	currentMember, hasCurrentMember, err := txs.loadHumanWorkspaceMemberForUser(ctx, tx, workspaceID, account.UserID)
	if err != nil {
		return "", "", false, err
	}
	if hasProjection {
		projection = reconcileProjectionBaselineDrift(projection, hasCurrentMember, currentMember)
	}

	if !hasAccess {
		if !hasProjection {
			return "", "", false, nil
		}
		memberID := projection.MemberID
		if projection.MemberCreatedByOrg {
			memberID, err = txs.core.DeactivateHumanWorkspaceMemberInTx(ctx, tx, workspaceID, account.UserID)
			if err != nil && !errors.Is(err, ErrNotFound) {
				return "", "", false, err
			}
		} else if projection.HasBaselineRole {
			memberID, err = txs.core.RestoreHumanWorkspaceMemberBaselineInTx(ctx, tx, workspaceID, account.UserID, projection.BaselineWorkspaceRole)
			if err != nil {
				return "", "", false, err
			}
		}
		if memberID == "" {
			memberID = projection.MemberID
		}
		if err := txs.upsertOrganizationWorkspaceMemberProjection(
			ctx,
			tx,
			organizationID,
			workspaceID,
			accountID,
			memberID,
			projection.MemberCreatedByOrg,
			projection.BaselineWorkspaceRole,
			projection.HasBaselineRole,
			projection.AppliedWorkspaceRole,
			projection.AppliedSource,
		); err != nil {
			return "", "", false, err
		}
		return memberID, "", false, nil
	}

	memberCreatedByOrg := false
	baselineRole := ""
	hasBaselineRole := false

	if hasProjection {
		memberCreatedByOrg = projection.MemberCreatedByOrg
		baselineRole = projection.BaselineWorkspaceRole
		hasBaselineRole = projection.HasBaselineRole
	} else {
		if hasCurrentMember && currentMember.Status == "active" {
			baselineRole = currentMember.WorkspaceRole
			hasBaselineRole = true
		} else {
			memberCreatedByOrg = true
		}
	}

	materializedRole := effectiveAccess.Role
	if hasBaselineRole {
		materializedRole = higherWorkspaceRole(baselineRole, effectiveAccess.Role)
	}

	memberID, _, err := txs.core.EnsureHumanWorkspaceMemberInTx(ctx, tx, workspaceID, account.UserID, materializedRole)
	if err != nil {
		return "", "", false, err
	}
	if err := txs.upsertOrganizationWorkspaceMemberProjection(ctx, tx, organizationID, workspaceID, accountID, memberID, memberCreatedByOrg, baselineRole, hasBaselineRole, effectiveAccess.Role, effectiveAccess.Source); err != nil {
		return "", "", false, err
	}
	return memberID, effectiveAccess.Role, true, nil
}

func (txs TxStore) loadEffectiveOrganizationWorkspaceAccess(ctx context.Context, queryer queryer, organizationID string, workspaceID string, accountID string) (organizationWorkspaceAccessCandidate, bool, error) {
	candidates := make([]organizationWorkspaceAccessCandidate, 0, 4)

	directAccess, hasDirectAccess, err := txs.loadDirectOrganizationWorkspaceAccess(ctx, queryer, organizationID, workspaceID, accountID)
	if err != nil {
		return organizationWorkspaceAccessCandidate{}, false, err
	}
	if hasDirectAccess {
		candidates = append(candidates, directAccess)
	}

	teamAccesses, err := txs.listTeamDerivedOrganizationWorkspaceAccesses(ctx, queryer, organizationID, workspaceID, accountID)
	if err != nil {
		return organizationWorkspaceAccessCandidate{}, false, err
	}
	candidates = append(candidates, teamAccesses...)

	if len(candidates) == 0 {
		return organizationWorkspaceAccessCandidate{}, false, nil
	}

	effective := candidates[0]
	for _, candidate := range candidates[1:] {
		if organizationWorkspaceRoleRank(candidate.Role) > organizationWorkspaceRoleRank(effective.Role) {
			effective = candidate
		}
	}
	return effective, true, nil
}

func (txs TxStore) loadEffectiveOrganizationWorkspaceRole(ctx context.Context, queryer queryer, organizationID string, workspaceID string, accountID string) (string, bool, error) {
	access, hasAccess, err := txs.loadEffectiveOrganizationWorkspaceAccess(ctx, queryer, organizationID, workspaceID, accountID)
	if err != nil || !hasAccess {
		return "", hasAccess, err
	}
	return access.Role, true, nil
}

func (txs TxStore) loadDirectOrganizationWorkspaceAccess(ctx context.Context, queryer queryRower, organizationID string, workspaceID string, accountID string) (organizationWorkspaceAccessCandidate, bool, error) {
	row := queryer.QueryRow(ctx, `
SELECT owag.workspace_role
FROM organization_workspace_member_grants owag
JOIN workspaces w
  ON w.id = owag.workspace_id
 AND w.organization_id = owag.organization_id
JOIN organization_settings os
  ON os.organization_id = owag.organization_id
 AND os.status = 'active'
JOIN organization_members om
  ON om.id = owag.organization_member_id
 AND om.status = 'active'
WHERE owag.organization_id = $1
  AND owag.workspace_id = $2
  AND owag.organization_member_id = $3
  AND owag.status = 'active'
`, organizationID, workspaceID, accountID)

	var role string
	if err := row.Scan(&role); err != nil {
		if coredb.IsNotFound(err) {
			return organizationWorkspaceAccessCandidate{}, false, nil
		}
		return organizationWorkspaceAccessCandidate{}, false, fmt.Errorf("load direct organization workspace role: %w", err)
	}
	return organizationWorkspaceAccessCandidate{
		Role:   role,
		Source: "manual_admin_grant",
	}, true, nil
}

func (txs TxStore) listTeamDerivedOrganizationWorkspaceAccesses(ctx context.Context, queryer queryer, organizationID string, workspaceID string, accountID string) ([]organizationWorkspaceAccessCandidate, error) {
	rows, err := queryer.Query(ctx, `
SELECT twg.workspace_role, t.source
FROM organization_workspace_team_grants twg
JOIN workspaces w
  ON w.id = twg.workspace_id
 AND w.organization_id = twg.organization_id
JOIN organization_settings os
  ON os.organization_id = twg.organization_id
 AND os.status = 'active'
JOIN organization_teams t
  ON t.id = twg.organization_team_id
 AND t.status = 'active'
JOIN organization_member_team_memberships tm
  ON tm.organization_id = twg.organization_id
 AND tm.organization_team_id = twg.organization_team_id
 AND tm.organization_member_id = $3
 AND tm.status = 'active'
JOIN organization_members om
  ON om.id = tm.organization_member_id
 AND om.status = 'active'
WHERE twg.organization_id = $1
  AND twg.workspace_id = $2
  AND twg.status = 'active'
ORDER BY twg.created_at ASC, twg.organization_team_id ASC
`, organizationID, workspaceID, accountID)
	if err != nil {
		return nil, fmt.Errorf("list team-derived organization workspace roles: %w", err)
	}
	defer rows.Close()

	accesses := make([]organizationWorkspaceAccessCandidate, 0)
	for rows.Next() {
		var role string
		var teamSource string
		if err := rows.Scan(&role, &teamSource); err != nil {
			return nil, fmt.Errorf("scan team-derived organization workspace role: %w", err)
		}
		accesses = append(accesses, organizationWorkspaceAccessCandidate{
			Role:   role,
			Source: organizationWorkspaceTeamGrantSource(teamSource),
		})
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate team-derived organization workspace roles: %w", err)
	}
	return accesses, nil
}

func (txs TxStore) loadOrganizationWorkspaceMemberProjection(ctx context.Context, queryer queryRower, organizationID string, workspaceID string, accountID string) (organizationWorkspaceMemberProjectionRecord, bool, error) {
	row := queryer.QueryRow(ctx, `
SELECT id,
       member_id,
       member_created_by_org,
       COALESCE(baseline_workspace_role, ''),
       baseline_workspace_role IS NOT NULL,
       applied_workspace_role,
       applied_source
FROM organization_workspace_member_projections
WHERE organization_id = $1 AND workspace_id = $2 AND organization_member_id = $3
`, organizationID, workspaceID, accountID)

	var projection organizationWorkspaceMemberProjectionRecord
	if err := row.Scan(
		&projection.ID,
		&projection.MemberID,
		&projection.MemberCreatedByOrg,
		&projection.BaselineWorkspaceRole,
		&projection.HasBaselineRole,
		&projection.AppliedWorkspaceRole,
		&projection.AppliedSource,
	); err != nil {
		if coredb.IsNotFound(err) {
			return organizationWorkspaceMemberProjectionRecord{}, false, nil
		}
		return organizationWorkspaceMemberProjectionRecord{}, false, fmt.Errorf("load organization workspace member projection: %w", err)
	}
	return projection, true, nil
}

func (txs TxStore) loadHumanWorkspaceMemberForUser(ctx context.Context, queryer queryRower, workspaceID string, userID string) (humanWorkspaceMemberRecord, bool, error) {
	row := queryer.QueryRow(ctx, `
SELECT id, workspace_role, status
FROM members
WHERE workspace_id = $1 AND user_id = $2 AND type = 'human'
`, workspaceID, userID)

	var member humanWorkspaceMemberRecord
	if err := row.Scan(&member.ID, &member.WorkspaceRole, &member.Status); err != nil {
		if coredb.IsNotFound(err) {
			return humanWorkspaceMemberRecord{}, false, nil
		}
		return humanWorkspaceMemberRecord{}, false, fmt.Errorf("load human workspace member for organization projection: %w", err)
	}
	return member, true, nil
}

func (txs TxStore) upsertOrganizationWorkspaceMemberProjection(ctx context.Context, tx coredb.Tx, organizationID string, workspaceID string, accountID string, memberID string, memberCreatedByOrg bool, baselineRole string, hasBaselineRole bool, appliedRole string, appliedSource string) error {
	projectionID, err := identifiers.NewEntityID("ogmp")
	if err != nil {
		return fmt.Errorf("generate organization workspace member projection id: %w", err)
	}

	var baselineValue any
	if hasBaselineRole {
		baselineValue = baselineRole
	} else {
		baselineValue = nil
	}

	now := time.Now().UTC()
	if err := execPortableUpdateThenInsert(
		ctx,
		tx,
		`
SELECT 1
FROM organization_workspace_member_projections
WHERE workspace_id = $1
  AND organization_member_id = $2
`,
		[]any{workspaceID, accountID},
		`
UPDATE organization_workspace_member_projections
SET member_id = $3,
    member_created_by_org = $4,
    baseline_workspace_role = $5,
    applied_workspace_role = $6,
    applied_source = $7,
    updated_at = $8
WHERE workspace_id = $1
  AND organization_member_id = $2
`,
		[]any{workspaceID, accountID, memberID, memberCreatedByOrg, baselineValue, appliedRole, appliedSource, now},
		`
INSERT INTO organization_workspace_member_projections (
    id,
    organization_id,
    workspace_id,
    organization_member_id,
    member_id,
    member_created_by_org,
    baseline_workspace_role,
    applied_workspace_role,
    applied_source,
    created_at,
    updated_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $10)
`,
		[]any{projectionID, organizationID, workspaceID, accountID, memberID, memberCreatedByOrg, baselineValue, appliedRole, appliedSource, now},
	); err != nil {
		return fmt.Errorf("upsert organization workspace member projection: %w", err)
	}
	return nil
}

func (txs TxStore) listActiveOrganizationMemberIDsForTeam(ctx context.Context, queryer queryer, organizationID string, teamID string) ([]string, error) {
	rows, err := queryer.Query(ctx, `
SELECT tm.organization_member_id
FROM organization_member_team_memberships tm
JOIN organization_members om ON om.id = tm.organization_member_id
WHERE tm.organization_id = $1
  AND tm.organization_team_id = $2
  AND tm.status = 'active'
  AND om.status = 'active'
ORDER BY tm.organization_member_id ASC
`, organizationID, teamID)
	if err != nil {
		return nil, fmt.Errorf("list active organization members for team: %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 organization member for team: %w", err)
		}
		accountIDs = append(accountIDs, accountID)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate active organization members for team: %w", err)
	}
	return accountIDs, nil
}

func (txs TxStore) listOrganizationWorkspaceAffectedMemberIDs(ctx context.Context, queryer queryer, organizationID string, workspaceID string) ([]string, error) {
	rows, err := queryer.Query(ctx, `
SELECT organization_member_id
FROM (
    SELECT organization_member_id
    FROM organization_workspace_member_projections
    WHERE organization_id = $1 AND workspace_id = $2

    UNION

    SELECT organization_member_id
    FROM organization_workspace_member_grants
    WHERE organization_id = $1 AND workspace_id = $2 AND status = 'active'

    UNION

    SELECT tm.organization_member_id
    FROM organization_workspace_team_grants twg
    JOIN organization_member_team_memberships tm
      ON tm.organization_id = twg.organization_id
     AND tm.organization_team_id = twg.organization_team_id
     AND tm.status = 'active'
    WHERE twg.organization_id = $1
      AND twg.workspace_id = $2
      AND twg.status = 'active'
) affected
ORDER BY organization_member_id ASC
`, organizationID, workspaceID)
	if err != nil {
		return nil, fmt.Errorf("list organization workspace affected members: %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 organization workspace affected member: %w", err)
		}
		accountIDs = append(accountIDs, accountID)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization workspace affected members: %w", err)
	}
	return accountIDs, nil
}

func (txs TxStore) loadOrganizationWorkspaceTeamGrantView(ctx context.Context, queryer queryRower, organizationID string, workspaceID string, teamID string) (OrganizationWorkspaceTeamGrantView, error) {
	row := queryer.QueryRow(ctx, `
SELECT twg.workspace_id,
       t.id,
       t.name,
       twg.workspace_role,
       t.source,
       twg.status,
       (
         SELECT COUNT(*)
         FROM organization_member_team_memberships tm
         JOIN organization_members om ON om.id = tm.organization_member_id
         WHERE tm.organization_id = twg.organization_id
           AND tm.organization_team_id = twg.organization_team_id
           AND tm.status = 'active'
           AND om.status = 'active'
       )
FROM organization_workspace_team_grants twg
JOIN organization_teams t ON t.id = twg.organization_team_id
WHERE twg.organization_id = $1
  AND twg.workspace_id = $2
  AND twg.organization_team_id = $3
`, organizationID, workspaceID, teamID)

	var grant OrganizationWorkspaceTeamGrantView
	var teamSource string
	if err := row.Scan(
		&grant.WorkspaceID,
		&grant.TeamID,
		&grant.TeamName,
		&grant.Role,
		&teamSource,
		&grant.Status,
		&grant.MemberCount,
	); err != nil {
		if coredb.IsNotFound(err) {
			return OrganizationWorkspaceTeamGrantView{}, ErrNotFound
		}
		return OrganizationWorkspaceTeamGrantView{}, fmt.Errorf("load organization workspace team grant: %w", err)
	}
	grant.Source = organizationWorkspaceTeamGrantSource(teamSource)
	return grant, nil
}

func (txs TxStore) loadOrganizationMemberProjectionTarget(ctx context.Context, queryer queryRower, organizationID string, accountID string) (organizationMemberProjectionTarget, error) {
	row := queryer.QueryRow(ctx, `
SELECT id,
       user_id,
       default_workspace_role,
       status,
       status
FROM organization_members
WHERE organization_id = $1
  AND id = $2
`, organizationID, accountID)

	var account organizationMemberProjectionTarget
	if err := row.Scan(&account.MemberID, &account.UserID, &account.DefaultWorkspaceRole, &account.MemberStatus, &account.OrganizationMemberStatus); err != nil {
		if coredb.IsNotFound(err) {
			return organizationMemberProjectionTarget{}, ErrNotFound
		}
		return organizationMemberProjectionTarget{}, fmt.Errorf("load organization member projection target: %w", err)
	}
	return account, nil
}

func higherWorkspaceRole(left string, right string) string {
	if organizationWorkspaceRoleRank(left) >= organizationWorkspaceRoleRank(right) {
		return left
	}
	return right
}

func organizationWorkspaceRoleRank(role string) int {
	switch domain.WorkspaceRole(role) {
	case domain.WorkspaceRoleOwner:
		return 3
	case domain.WorkspaceRoleAdmin:
		return 2
	case domain.WorkspaceRoleMember:
		return 1
	default:
		return 0
	}
}