package orgmembership

import (
	"context"
	"fmt"
	"strings"

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

// WorkspaceBackfillGapOptions filters historical workspace members whose owning
// organization membership is missing or inactive.
type WorkspaceBackfillGapOptions struct {
	OrganizationID string
	WorkspaceID    string
	Limit          int
}

// WorkspaceBackfillGap describes one active workspace member that needs an
// owning organization membership repair.
type WorkspaceBackfillGap struct {
	OrganizationID       string
	WorkspaceID          string
	WorkspaceMemberID    string
	UserID               string
	AuditUserID          string
	OrganizationMemberID string
	MemberStatus         string
}

// CountWorkspaceBackfillGaps counts historical workspace membership rows that
// still need organization membership repair.
func CountWorkspaceBackfillGaps(ctx context.Context, db coredb.QueryRower, options WorkspaceBackfillGapOptions) (int64, error) {
	where, args := workspaceBackfillGapWhere(options)
	query := `
SELECT COUNT(*)
FROM members m
JOIN workspaces w ON w.id = m.workspace_id
JOIN organizations o ON o.id = w.organization_id
LEFT JOIN organization_members om
  ON om.organization_id = w.organization_id
 AND om.user_id = m.user_id
WHERE ` + strings.Join(where, "\n  AND ")

	var count int64
	if err := db.QueryRow(ctx, query, args...).Scan(&count); err != nil {
		return 0, fmt.Errorf("count workspace org membership gaps: %w", err)
	}
	return count, nil
}

// ListWorkspaceBackfillGaps lists historical workspace membership rows that
// still need organization membership repair.
func ListWorkspaceBackfillGaps(ctx context.Context, db coredb.Queryer, options WorkspaceBackfillGapOptions) ([]WorkspaceBackfillGap, error) {
	where, args := workspaceBackfillGapWhere(options)
	limit := options.Limit
	if limit <= 0 {
		limit = 100
	}
	limitPlaceholder := fmt.Sprintf("$%d", len(args)+1)
	args = append(args, limit)
	query := `
SELECT w.organization_id,
       w.id,
       m.id,
       m.user_id,
       o.created_by_user_id,
       COALESCE(om.id, ''),
       COALESCE(om.status, '')
FROM members m
JOIN workspaces w ON w.id = m.workspace_id
JOIN organizations o ON o.id = w.organization_id
LEFT JOIN organization_members om
  ON om.organization_id = w.organization_id
 AND om.user_id = m.user_id
WHERE ` + strings.Join(where, "\n  AND ") + `
ORDER BY w.organization_id ASC, w.id ASC, m.created_at ASC, m.id ASC
LIMIT ` + limitPlaceholder + `
`
	rows, err := db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list workspace org membership gaps: %w", err)
	}
	defer rows.Close()

	gaps := make([]WorkspaceBackfillGap, 0, limit)
	for rows.Next() {
		var gap WorkspaceBackfillGap
		if err := rows.Scan(
			&gap.OrganizationID,
			&gap.WorkspaceID,
			&gap.WorkspaceMemberID,
			&gap.UserID,
			&gap.AuditUserID,
			&gap.OrganizationMemberID,
			&gap.MemberStatus,
		); err != nil {
			return nil, fmt.Errorf("scan workspace org membership gap: %w", err)
		}
		gaps = append(gaps, gap)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workspace org membership gaps: %w", err)
	}
	return gaps, nil
}

func workspaceBackfillGapWhere(options WorkspaceBackfillGapOptions) ([]string, []any) {
	where := []string{
		"m.type = 'human'",
		"m.status = 'active'",
		"m.user_id IS NOT NULL",
		"m.user_id <> ''",
		"w.organization_id IS NOT NULL",
		"w.organization_id <> ''",
		"(om.id IS NULL OR om.status <> 'active')",
	}
	args := make([]any, 0, 2)
	organizationID := strings.TrimSpace(options.OrganizationID)
	if organizationID != "" {
		args = append(args, organizationID)
		where = append(where, fmt.Sprintf("w.organization_id = $%d", len(args)))
	}
	workspaceID := strings.TrimSpace(options.WorkspaceID)
	if workspaceID != "" {
		args = append(args, workspaceID)
		where = append(where, fmt.Sprintf("w.id = $%d", len(args)))
	}
	return where, args
}