package organization

import (
	"context"
	"fmt"
	"strings"

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

const (
	workspaceAccessSourceOrgDirect       = "org-direct"
	workspaceAccessSourceOrgTeam         = "org-team"
	workspaceAccessSourceWorkspaceDirect = "workspace-direct"
)

func workspaceAccessSources(hasDirect bool, hasTeam bool, hasWorkspaceDirect bool) []string {
	sources := make([]string, 0, 3)
	if hasDirect {
		sources = append(sources, workspaceAccessSourceOrgDirect)
	}
	if hasTeam {
		sources = append(sources, workspaceAccessSourceOrgTeam)
	}
	if hasWorkspaceDirect {
		sources = append(sources, workspaceAccessSourceWorkspaceDirect)
	}
	return sources
}

func (txs TxStore) listOrganizationMemberWorkspaceAccessesForMemberIDs(ctx context.Context, organizationID string, memberIDs []string) (map[string][]OrganizationMemberWorkspaceAccessView, error) {
	normalizedMemberIDs := normalizeOrganizationWorkspaceAccessMemberIDs(memberIDs)
	if len(normalizedMemberIDs) == 0 {
		return map[string][]OrganizationMemberWorkspaceAccessView{}, nil
	}

	requestedMemberIDsCTE, requestedMemberIDArgs, _, err := coredb.BuildStringValuesCTE("requested_member_ids", "member_id", normalizedMemberIDs, 2)
	if err != nil {
		return nil, fmt.Errorf("build requested organization member workspace access ids: %w", err)
	}
	args := append([]any{organizationID}, requestedMemberIDArgs...)

	rows, err := txs.db.Query(ctx, `WITH `+requestedMemberIDsCTE+`
SELECT om.id,
       w.id,
       w.name,
       w.slug,
       COALESCE(m.workspace_role, ''),
       EXISTS (
         SELECT 1
         FROM organization_workspace_member_grants owag
         WHERE owag.organization_id = w.organization_id
           AND owag.workspace_id = m.workspace_id
           AND owag.organization_member_id = om.id
           AND owag.status = 'active'
       ) AS has_direct,
       EXISTS (
         SELECT 1
         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.organization_member_id = om.id
          AND tm.status = 'active'
         WHERE twg.organization_id = w.organization_id
           AND twg.workspace_id = m.workspace_id
           AND twg.status = 'active'
       ) AS has_team,
       CASE
         WHEN owmp.id IS NULL THEN TRUE
         WHEN owmp.member_created_by_org = FALSE THEN TRUE
         ELSE FALSE
       END AS has_external
FROM requested_member_ids requested
JOIN organization_members om
  ON om.id = requested.member_id
 AND om.organization_id = $1
 AND om.status = 'active'
JOIN members m
  ON m.user_id = om.user_id
 AND m.type = 'human'
 AND m.status = 'active'
JOIN workspaces w
  ON w.id = m.workspace_id
 AND w.organization_id = om.organization_id
LEFT JOIN organization_workspace_member_projections owmp
  ON owmp.organization_id = w.organization_id
 AND owmp.workspace_id = m.workspace_id
 AND owmp.organization_member_id = om.id
ORDER BY w.created_at ASC, w.name ASC, w.id ASC, om.id ASC
`, args...)
	if err != nil {
		return nil, fmt.Errorf("list organization member workspace accesses: %w", err)
	}
	defer rows.Close()

	accessesByMemberID := make(map[string][]OrganizationMemberWorkspaceAccessView)
	for rows.Next() {
		var memberID string
		var access OrganizationMemberWorkspaceAccessView
		var hasDirect bool
		var hasTeam bool
		var hasWorkspaceDirect bool
		if err := rows.Scan(
			&memberID,
			&access.WorkspaceID,
			&access.WorkspaceName,
			&access.WorkspaceSlug,
			&access.Role,
			&hasDirect,
			&hasTeam,
			&hasWorkspaceDirect,
		); err != nil {
			return nil, fmt.Errorf("scan organization member workspace access: %w", err)
		}
		access.Sources = workspaceAccessSources(hasDirect, hasTeam, hasWorkspaceDirect)
		accessesByMemberID[memberID] = append(accessesByMemberID[memberID], access)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate organization member workspace accesses: %w", err)
	}
	return accessesByMemberID, nil
}

func (txs TxStore) ListWorkspaceMemberAccessSources(ctx context.Context, workspaceID string) ([]WorkspaceMemberAccessSourceView, error) {
	rows, err := txs.db.Query(ctx, `
WITH member_accounts AS (
    SELECT m.user_id,
           m.created_at,
           w.organization_id,
           om.id AS organization_member_id,
           owmp.id AS workspace_member_projection_id,
           owmp.member_created_by_org
    FROM members m
    JOIN workspaces w ON w.id = m.workspace_id
    LEFT JOIN organization_members om
      ON om.organization_id = w.organization_id
     AND om.user_id = m.user_id
     AND om.status = 'active'
    LEFT JOIN organization_workspace_member_projections owmp
      ON owmp.organization_id = w.organization_id
     AND owmp.workspace_id = m.workspace_id
     AND owmp.organization_member_id = om.id
    WHERE m.workspace_id = $1
      AND m.type = 'human'
      AND m.status = 'active'
),
direct_grants AS (
    SELECT DISTINCT owag.organization_member_id
    FROM organization_workspace_member_grants owag
    JOIN member_accounts ma
      ON ma.organization_member_id = owag.organization_member_id
     AND ma.organization_id = owag.organization_id
    WHERE owag.workspace_id = $1
      AND owag.status = 'active'
),
team_grants AS (
    SELECT DISTINCT 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'
    JOIN member_accounts ma
      ON ma.organization_member_id = tm.organization_member_id
     AND ma.organization_id = twg.organization_id
    WHERE twg.workspace_id = $1
      AND twg.status = 'active'
)
SELECT ma.user_id,
       ma.organization_member_id IS NOT NULL AND direct_grants.organization_member_id IS NOT NULL AS has_direct,
       ma.organization_member_id IS NOT NULL AND team_grants.organization_member_id IS NOT NULL AS has_team,
       CASE
         WHEN ma.organization_member_id IS NULL THEN TRUE
         WHEN ma.workspace_member_projection_id IS NULL THEN TRUE
         WHEN ma.member_created_by_org = FALSE THEN TRUE
         ELSE FALSE
       END AS has_external
FROM member_accounts ma
LEFT JOIN direct_grants ON direct_grants.organization_member_id = ma.organization_member_id
LEFT JOIN team_grants ON team_grants.organization_member_id = ma.organization_member_id
ORDER BY ma.created_at ASC, ma.user_id ASC
`, workspaceID)
	if err != nil {
		return nil, fmt.Errorf("list workspace member access sources: %w", err)
	}
	defer rows.Close()

	views := make([]WorkspaceMemberAccessSourceView, 0)
	for rows.Next() {
		var view WorkspaceMemberAccessSourceView
		var hasDirect bool
		var hasTeam bool
		var hasWorkspaceDirect bool
		if err := rows.Scan(&view.UserID, &hasDirect, &hasTeam, &hasWorkspaceDirect); err != nil {
			return nil, fmt.Errorf("scan workspace member access sources: %w", err)
		}
		view.Sources = workspaceAccessSources(hasDirect, hasTeam, hasWorkspaceDirect)
		views = append(views, view)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workspace member access sources: %w", err)
	}
	return views, nil
}

func (txs TxStore) ListWorkspaceMemberAccessSourcesForUsers(ctx context.Context, workspaceID string, userIDs []string) ([]WorkspaceMemberAccessSourceView, error) {
	normalizedUserIDs := normalizeWorkspaceAccessSourceUserIDs(userIDs)
	if len(normalizedUserIDs) == 0 {
		return []WorkspaceMemberAccessSourceView{}, nil
	}

	requestedUserIDsCTE, requestedUserIDArgs, _, err := coredb.BuildStringValuesCTE("requested_user_ids", "user_id", normalizedUserIDs, 2)
	if err != nil {
		return nil, fmt.Errorf("build requested workspace member access source user ids: %w", err)
	}
	args := append([]any{workspaceID}, requestedUserIDArgs...)

	rows, err := txs.db.Query(ctx, `WITH `+requestedUserIDsCTE+`,
member_accounts AS (
    SELECT m.user_id,
           m.created_at,
           w.organization_id,
           om.id AS organization_member_id,
           owmp.id AS workspace_member_projection_id,
           owmp.member_created_by_org
    FROM requested_user_ids r
    JOIN members m
      ON m.user_id = r.user_id
     AND m.workspace_id = $1
     AND m.type = 'human'
     AND m.status = 'active'
    JOIN workspaces w ON w.id = m.workspace_id
    LEFT JOIN organization_members om
      ON om.organization_id = w.organization_id
     AND om.user_id = m.user_id
     AND om.status = 'active'
    LEFT JOIN organization_workspace_member_projections owmp
      ON owmp.organization_id = w.organization_id
     AND owmp.workspace_id = m.workspace_id
     AND owmp.organization_member_id = om.id
),
direct_grants AS (
    SELECT DISTINCT owag.organization_member_id
    FROM organization_workspace_member_grants owag
    JOIN member_accounts ma
      ON ma.organization_member_id = owag.organization_member_id
     AND ma.organization_id = owag.organization_id
    WHERE owag.workspace_id = $1
      AND owag.status = 'active'
),
team_grants AS (
    SELECT DISTINCT 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'
    JOIN member_accounts ma
      ON ma.organization_member_id = tm.organization_member_id
     AND ma.organization_id = twg.organization_id
    WHERE twg.workspace_id = $1
      AND twg.status = 'active'
)
SELECT ma.user_id,
       ma.organization_member_id IS NOT NULL AND direct_grants.organization_member_id IS NOT NULL AS has_direct,
       ma.organization_member_id IS NOT NULL AND team_grants.organization_member_id IS NOT NULL AS has_team,
       CASE
         WHEN ma.organization_member_id IS NULL THEN TRUE
         WHEN ma.workspace_member_projection_id IS NULL THEN TRUE
         WHEN ma.member_created_by_org = FALSE THEN TRUE
         ELSE FALSE
       END AS has_external
FROM member_accounts ma
LEFT JOIN direct_grants ON direct_grants.organization_member_id = ma.organization_member_id
LEFT JOIN team_grants ON team_grants.organization_member_id = ma.organization_member_id
ORDER BY ma.created_at ASC, ma.user_id ASC
`, args...)
	if err != nil {
		return nil, fmt.Errorf("list workspace member access sources for users: %w", err)
	}
	defer rows.Close()

	views := make([]WorkspaceMemberAccessSourceView, 0, len(normalizedUserIDs))
	for rows.Next() {
		var view WorkspaceMemberAccessSourceView
		var hasDirect bool
		var hasTeam bool
		var hasWorkspaceDirect bool
		if err := rows.Scan(&view.UserID, &hasDirect, &hasTeam, &hasWorkspaceDirect); err != nil {
			return nil, fmt.Errorf("scan workspace member access sources for users: %w", err)
		}
		view.Sources = workspaceAccessSources(hasDirect, hasTeam, hasWorkspaceDirect)
		views = append(views, view)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workspace member access sources for users: %w", err)
	}
	return views, nil
}

func normalizeWorkspaceAccessSourceUserIDs(userIDs []string) []string {
	normalized := make([]string, 0, len(userIDs))
	seen := make(map[string]struct{}, len(userIDs))
	for _, userID := range userIDs {
		trimmed := strings.TrimSpace(userID)
		if trimmed == "" {
			continue
		}
		if _, ok := seen[trimmed]; ok {
			continue
		}
		seen[trimmed] = struct{}{}
		normalized = append(normalized, trimmed)
	}
	return normalized
}

func normalizeOrganizationWorkspaceAccessMemberIDs(accountIDs []string) []string {
	normalized := make([]string, 0, len(accountIDs))
	seen := make(map[string]struct{}, len(accountIDs))
	for _, accountID := range accountIDs {
		trimmed := strings.TrimSpace(accountID)
		if trimmed == "" {
			continue
		}
		if _, ok := seen[trimmed]; ok {
			continue
		}
		seen[trimmed] = struct{}{}
		normalized = append(normalized, trimmed)
	}
	return normalized
}