package postgres

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"time"

	projectdom "github.com/Paca-AI/api/internal/domain/project"
	"github.com/google/uuid"
	"gorm.io/gorm"
)

// --- GORM models ------------------------------------------------------------

type projectRecord struct {
	ID           string `gorm:"primarykey;type:uuid"`
	Name         string `gorm:"not null"`
	Description  string
	TaskIDPrefix string  `gorm:"column:task_id_prefix;not null;default:''"`
	IsPublic     bool    `gorm:"column:is_public;not null;default:false"`
	Settings     []byte  `gorm:"type:jsonb;not null"`
	CreatedBy    *string `gorm:"type:uuid"`
	CreatedAt    time.Time
}

func (projectRecord) TableName() string { return "projects" }

type projectRoleRecord struct {
	ID          string  `gorm:"primarykey;type:uuid"`
	ProjectID   *string `gorm:"type:uuid;column:project_id"`
	RoleName    string  `gorm:"column:role_name;not null"`
	Permissions []byte  `gorm:"type:jsonb;not null"`
	CreatedAt   time.Time
	UpdatedAt   time.Time
}

func (projectRoleRecord) TableName() string { return "project_roles" }

type projectMemberRecord struct {
	ID            string  `gorm:"primarykey;type:uuid"`
	ProjectID     string  `gorm:"type:uuid;not null;column:project_id"`
	UserID        *string `gorm:"type:uuid;column:user_id"`
	ProjectRoleID string  `gorm:"type:uuid;not null;column:project_role_id"`
	MemberType    string  `gorm:"column:member_type"`
	AgentID       *string `gorm:"type:uuid;column:agent_id"`
	CreatedAt     time.Time
	DeletedAt     gorm.DeletedAt `gorm:"index"`
}

func (projectMemberRecord) TableName() string { return "project_members" }

// projectMemberReadRow is the result of the SELECT … JOIN query.
type projectMemberReadRow struct {
	ID            string
	ProjectID     string
	UserID        *string
	ProjectRoleID string
	MemberType    string
	AgentID       *string
	Username      string
	FullName      string
	RoleName      string
	AgentName     string
	AgentHandle   string
	CreatedAt     time.Time
	DeletedAt     gorm.DeletedAt
}

// --- Repository -------------------------------------------------------------

// ProjectRepository is the GORM implementation of projectdom.Repository.
type ProjectRepository struct {
	db *gorm.DB
}

// NewProjectRepository returns a new ProjectRepository.
func NewProjectRepository(db *gorm.DB) *ProjectRepository {
	return &ProjectRepository{db: db}
}

// --- Projects ---------------------------------------------------------------

// List returns a page of projects and the total count.
func (r *ProjectRepository) List(ctx context.Context, offset, limit int) ([]*projectdom.Project, int64, error) {
	var total int64
	if err := r.db.WithContext(ctx).Table("projects").Count(&total).Error; err != nil {
		return nil, 0, fmt.Errorf("project repo: list count: %w", err)
	}

	var records []projectRecord
	if err := r.db.WithContext(ctx).
		Order("created_at ASC").
		Offset(offset).
		Limit(limit).
		Find(&records).Error; err != nil {
		return nil, 0, fmt.Errorf("project repo: list: %w", err)
	}

	projects := make([]*projectdom.Project, 0, len(records))
	for i := range records {
		p, err := toProjectEntity(&records[i])
		if err != nil {
			return nil, 0, err
		}
		projects = append(projects, p)
	}
	return projects, total, nil
}

// ListAccessible returns the projects that the given user is an active member of.
func (r *ProjectRepository) ListAccessible(ctx context.Context, userID uuid.UUID, offset, limit int) ([]*projectdom.Project, int64, error) {
	var total int64
	if err := r.db.WithContext(ctx).
		Table("projects").
		Joins("JOIN project_members ON project_members.project_id = projects.id").
		Where("project_members.user_id = ? AND project_members.deleted_at IS NULL", userID.String()).
		Count(&total).Error; err != nil {
		return nil, 0, fmt.Errorf("project repo: list accessible count: %w", err)
	}

	var records []projectRecord
	if err := r.db.WithContext(ctx).
		Joins("JOIN project_members ON project_members.project_id = projects.id").
		Where("project_members.user_id = ? AND project_members.deleted_at IS NULL", userID.String()).
		Order("projects.created_at ASC").
		Offset(offset).
		Limit(limit).
		Find(&records).Error; err != nil {
		return nil, 0, fmt.Errorf("project repo: list accessible: %w", err)
	}

	projects := make([]*projectdom.Project, 0, len(records))
	for i := range records {
		p, err := toProjectEntity(&records[i])
		if err != nil {
			return nil, 0, err
		}
		projects = append(projects, p)
	}
	return projects, total, nil
}

// FindByID returns a project by its primary key.
func (r *ProjectRepository) FindByID(ctx context.Context, id uuid.UUID) (*projectdom.Project, error) {
	var record projectRecord
	result := r.db.WithContext(ctx).First(&record, "id = ?", id.String())
	if errors.Is(result.Error, gorm.ErrRecordNotFound) {
		return nil, projectdom.ErrNotFound
	}
	if result.Error != nil {
		return nil, fmt.Errorf("project repo: find by id: %w", result.Error)
	}
	return toProjectEntity(&record)
}

// FindByTaskIDPrefix returns the first project whose task_id_prefix matches
// the given prefix (case-insensitive).  Returns projectdom.ErrNotFound when
// no match exists.
func (r *ProjectRepository) FindByTaskIDPrefix(ctx context.Context, prefix string) (*projectdom.Project, error) {
	var record projectRecord
	err := r.db.WithContext(ctx).
		Where("upper(task_id_prefix) = upper(?)", prefix).
		First(&record).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return nil, projectdom.ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("project repo: find by task id prefix: %w", err)
	}
	return toProjectEntity(&record)
}

// Create persists a new project.
func (r *ProjectRepository) Create(ctx context.Context, p *projectdom.Project) error {
	rec, err := fromProjectEntity(p)
	if err != nil {
		return err
	}
	if err := r.db.WithContext(ctx).Create(rec).Error; err != nil {
		if isUniqueViolation(err) {
			return projectdom.ErrNameTaken
		}
		return fmt.Errorf("project repo: create: %w", err)
	}
	return nil
}

// Update saves changes to a project.
func (r *ProjectRepository) Update(ctx context.Context, p *projectdom.Project) error {
	settings, err := json.Marshal(p.Settings)
	if err != nil {
		return fmt.Errorf("project repo: marshal settings: %w", err)
	}

	var createdBy *string
	if p.CreatedBy != nil {
		s := p.CreatedBy.String()
		createdBy = &s
	}

	result := r.db.WithContext(ctx).
		Model(&projectRecord{}).
		Where("id = ?", p.ID.String()).
		Updates(map[string]any{
			"name":           p.Name,
			"description":    p.Description,
			"task_id_prefix": p.TaskIDPrefix,
			"is_public":      p.IsPublic,
			"settings":       settings,
			"created_by":     createdBy,
		})
	if result.Error != nil {
		if isUniqueViolation(result.Error) {
			return projectdom.ErrNameTaken
		}
		return fmt.Errorf("project repo: update: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return projectdom.ErrNotFound
	}
	return nil
}

// Delete removes a project by ID. The DB schema cascades deletes to child tables.
func (r *ProjectRepository) Delete(ctx context.Context, id uuid.UUID) error {
	result := r.db.WithContext(ctx).Delete(&projectRecord{}, "id = ?", id.String())
	if result.Error != nil {
		return fmt.Errorf("project repo: delete: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return projectdom.ErrNotFound
	}
	return nil
}

// --- Project Roles ----------------------------------------------------------

// ListRoles returns project-scoped roles for a given project.
func (r *ProjectRepository) ListRoles(ctx context.Context, projectID uuid.UUID) ([]*projectdom.ProjectRole, error) {
	var records []projectRoleRecord
	if err := r.db.WithContext(ctx).
		Where("project_id = ?", projectID.String()).
		Order("role_name ASC").
		Find(&records).Error; err != nil {
		return nil, fmt.Errorf("project repo: list roles: %w", err)
	}

	roles := make([]*projectdom.ProjectRole, 0, len(records))
	for i := range records {
		role, err := toProjectRoleEntity(&records[i])
		if err != nil {
			return nil, err
		}
		roles = append(roles, role)
	}
	return roles, nil
}

// FindRoleByID returns a project role by its primary key.
func (r *ProjectRepository) FindRoleByID(ctx context.Context, id uuid.UUID) (*projectdom.ProjectRole, error) {
	var record projectRoleRecord
	result := r.db.WithContext(ctx).First(&record, "id = ?", id.String())
	if errors.Is(result.Error, gorm.ErrRecordNotFound) {
		return nil, projectdom.ErrRoleNotFound
	}
	if result.Error != nil {
		return nil, fmt.Errorf("project repo: find role by id: %w", result.Error)
	}
	return toProjectRoleEntity(&record)
}

// FindRoleByName returns a role by name within the given project scope.
func (r *ProjectRepository) FindRoleByName(ctx context.Context, projectID uuid.UUID, name string) (*projectdom.ProjectRole, error) {
	var record projectRoleRecord
	result := r.db.WithContext(ctx).
		Where("project_id = ? AND role_name = ?", projectID.String(), name).
		First(&record)
	if errors.Is(result.Error, gorm.ErrRecordNotFound) {
		return nil, projectdom.ErrRoleNotFound
	}
	if result.Error != nil {
		return nil, fmt.Errorf("project repo: find role by name: %w", result.Error)
	}
	return toProjectRoleEntity(&record)
}

// CreateRole persists a new project role.
func (r *ProjectRepository) CreateRole(ctx context.Context, role *projectdom.ProjectRole) error {
	rec, err := fromProjectRoleEntity(role)
	if err != nil {
		return err
	}
	if err := r.db.WithContext(ctx).Create(rec).Error; err != nil {
		if isUniqueViolation(err) {
			return projectdom.ErrRoleNameTaken
		}
		return fmt.Errorf("project repo: create role: %w", err)
	}
	return nil
}

// UpdateRole saves changes to a project role.
func (r *ProjectRepository) UpdateRole(ctx context.Context, role *projectdom.ProjectRole) error {
	perms, err := json.Marshal(role.Permissions)
	if err != nil {
		return fmt.Errorf("project repo: marshal role permissions: %w", err)
	}
	result := r.db.WithContext(ctx).
		Model(&projectRoleRecord{}).
		Where("id = ?", role.ID.String()).
		Updates(map[string]any{
			"role_name":   role.RoleName,
			"permissions": perms,
			"updated_at":  role.UpdatedAt,
		})
	if result.Error != nil {
		if isUniqueViolation(result.Error) {
			return projectdom.ErrRoleNameTaken
		}
		return fmt.Errorf("project repo: update role: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return projectdom.ErrRoleNotFound
	}
	return nil
}

// DeleteRole removes a project role.
func (r *ProjectRepository) DeleteRole(ctx context.Context, id uuid.UUID) error {
	result := r.db.WithContext(ctx).Delete(&projectRoleRecord{}, "id = ?", id.String())
	if result.Error != nil {
		return fmt.Errorf("project repo: delete role: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return projectdom.ErrRoleNotFound
	}
	return nil
}

// CountMembersWithRole returns the number of active project members assigned to the role.
func (r *ProjectRepository) CountMembersWithRole(ctx context.Context, roleID uuid.UUID) (int64, error) {
	var count int64
	if err := r.db.WithContext(ctx).
		Model(&projectMemberRecord{}).
		Where("project_role_id = ?", roleID.String()).
		Count(&count).Error; err != nil {
		return 0, fmt.Errorf("project repo: count members with role: %w", err)
	}
	return count, nil
}

// --- Project Members --------------------------------------------------------

const projectMemberCols = `
	pm.id, pm.project_id, pm.user_id, pm.project_role_id, pm.member_type, pm.agent_id, pm.created_at,
	COALESCE(u.username, '') AS username, COALESCE(u.full_name, '') AS full_name, pr.role_name,
	COALESCE(a.name, '') AS agent_name, COALESCE(a.handle, '') AS agent_handle`

// ListMembers returns all active (non-deleted) members of a project enriched with user and role info.
func (r *ProjectRepository) ListMembers(ctx context.Context, projectID uuid.UUID) ([]*projectdom.ProjectMember, error) {
	var rows []projectMemberReadRow
	if err := r.db.WithContext(ctx).
		Table("project_members pm").
		Select(projectMemberCols).
		Joins("LEFT JOIN users u ON u.id = pm.user_id AND u.deleted_at IS NULL").
		Joins("JOIN project_roles pr ON pr.id = pm.project_role_id").
		Joins("LEFT JOIN agents a ON a.id = pm.agent_id AND a.deleted_at IS NULL").
		Where("pm.project_id = ? AND pm.deleted_at IS NULL", projectID.String()).
		Order("COALESCE(u.username, a.handle) ASC").
		Scan(&rows).Error; err != nil {
		return nil, fmt.Errorf("project repo: list members: %w", err)
	}

	members := make([]*projectdom.ProjectMember, 0, len(rows))
	for i := range rows {
		members = append(members, toMemberEntity(&rows[i]))
	}
	return members, nil
}

// FindMember returns a single active member record for the given project + user combo.
func (r *ProjectRepository) FindMember(ctx context.Context, projectID, userID uuid.UUID) (*projectdom.ProjectMember, error) {
	var row projectMemberReadRow
	result := r.db.WithContext(ctx).
		Table("project_members pm").
		Select(projectMemberCols).
		Joins("LEFT JOIN users u ON u.id = pm.user_id AND u.deleted_at IS NULL").
		Joins("JOIN project_roles pr ON pr.id = pm.project_role_id").
		Joins("LEFT JOIN agents a ON a.id = pm.agent_id AND a.deleted_at IS NULL").
		Where("pm.project_id = ? AND pm.user_id = ? AND pm.deleted_at IS NULL", projectID.String(), userID.String()).
		Scan(&row)
	if result.Error != nil {
		return nil, fmt.Errorf("project repo: find member: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return nil, projectdom.ErrMemberNotFound
	}
	return toMemberEntity(&row), nil
}

// FindMemberByAgent returns a single active member record for the given project + agent combo.
func (r *ProjectRepository) FindMemberByAgent(ctx context.Context, projectID, agentID uuid.UUID) (*projectdom.ProjectMember, error) {
	var row projectMemberReadRow
	result := r.db.WithContext(ctx).
		Table("project_members pm").
		Select(projectMemberCols).
		Joins("LEFT JOIN users u ON u.id = pm.user_id AND u.deleted_at IS NULL").
		Joins("JOIN project_roles pr ON pr.id = pm.project_role_id").
		Joins("LEFT JOIN agents a ON a.id = pm.agent_id AND a.deleted_at IS NULL").
		Where("pm.project_id = ? AND pm.agent_id = ? AND pm.member_type = 'agent' AND pm.deleted_at IS NULL", projectID.String(), agentID.String()).
		Scan(&row)
	if result.Error != nil {
		return nil, fmt.Errorf("project repo: find member by agent: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return nil, projectdom.ErrMemberNotFound
	}
	return toMemberEntity(&row), nil
}

// FindMemberByUserProject returns the active member record for a (user_id, project_id)
// pair. It is used by the activity consumer to resolve a user UUID to a member UUID.
func (r *ProjectRepository) FindMemberByUserProject(ctx context.Context, userID, projectID uuid.UUID) (*projectdom.ProjectMember, error) {
	return r.FindMember(ctx, projectID, userID)
}

// FindMemberByActor resolves an actor to a project member.
// When agentID is non-nil the agent's member record is returned via
// FindMemberByAgent; otherwise the user's member record is returned via
// FindMemberByUserProject. This is the single canonical actor-resolution method
// used by activity services and stream consumers.
func (r *ProjectRepository) FindMemberByActor(ctx context.Context, projectID, actorID uuid.UUID, agentID *uuid.UUID) (*projectdom.ProjectMember, error) {
	if agentID != nil {
		return r.FindMemberByAgent(ctx, projectID, *agentID)
	}
	return r.FindMemberByUserProject(ctx, actorID, projectID)
}

// FindMemberByID returns the active member record for the given project_members.id.
// Used by the notification service to resolve an assignee member ID to a user ID.
func (r *ProjectRepository) FindMemberByID(ctx context.Context, memberID uuid.UUID) (*projectdom.ProjectMember, error) {
	var row projectMemberReadRow
	result := r.db.WithContext(ctx).
		Table("project_members pm").
		Select(projectMemberCols).
		Joins("LEFT JOIN users u ON u.id = pm.user_id AND u.deleted_at IS NULL").
		Joins("JOIN project_roles pr ON pr.id = pm.project_role_id").
		Joins("LEFT JOIN agents a ON a.id = pm.agent_id AND a.deleted_at IS NULL").
		Where("pm.id = ? AND pm.deleted_at IS NULL", memberID.String()).
		Scan(&row)
	if result.Error != nil {
		return nil, fmt.Errorf("project repo: find member by id: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return nil, projectdom.ErrMemberNotFound
	}
	return toMemberEntity(&row), nil
}

// AddMember inserts a project_members row, or restores a previously soft-deleted one.
func (r *ProjectRepository) AddMember(ctx context.Context, m *projectdom.ProjectMember) error {
	// First try to restore a previously soft-deleted membership for this
	// project+user pair, updating only the role (preserving original created_at).
	restore := r.db.WithContext(ctx).Exec(`
		UPDATE project_members
		SET project_role_id = ?, deleted_at = NULL
		WHERE project_id = ? AND user_id = ? AND deleted_at IS NOT NULL`,
		m.ProjectRoleID.String(), m.ProjectID.String(), m.UserID.String(),
	)
	if restore.Error != nil {
		return fmt.Errorf("project repo: restore member: %w", restore.Error)
	}
	if restore.RowsAffected > 0 {
		return nil
	}

	// No soft-deleted row to restore; insert a fresh membership.
	result := r.db.WithContext(ctx).Exec(`
		INSERT INTO project_members (id, project_id, user_id, project_role_id, member_type, created_at, deleted_at)
		VALUES (?, ?, ?, ?, 'human', NOW(), NULL)
		ON CONFLICT (project_id, user_id) WHERE deleted_at IS NULL DO NOTHING`,
		m.ID.String(), m.ProjectID.String(), m.UserID.String(), m.ProjectRoleID.String(),
	)
	if result.Error != nil {
		return fmt.Errorf("project repo: add member: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return projectdom.ErrMemberAlreadyAdded
	}
	return nil
}

// AddAgentMember inserts a project_members row for an AI agent.
func (r *ProjectRepository) AddAgentMember(ctx context.Context, memberID, projectID, agentID, roleID uuid.UUID) error {
	result := r.db.WithContext(ctx).Exec(`
		INSERT INTO project_members (id, project_id, agent_id, project_role_id, member_type, user_id, created_at, deleted_at)
		VALUES (?, ?, ?, ?, 'agent', NULL, NOW(), NULL)
		ON CONFLICT (project_id, agent_id) WHERE deleted_at IS NULL AND member_type = 'agent' DO NOTHING`,
		memberID.String(), projectID.String(), agentID.String(), roleID.String(),
	)
	if result.Error != nil {
		return fmt.Errorf("project repo: add agent member: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return projectdom.ErrMemberAlreadyAdded
	}
	return nil
}

// RemoveAgentMember soft-deletes the membership row for the given agent.
func (r *ProjectRepository) RemoveAgentMember(ctx context.Context, projectID, agentID uuid.UUID) error {
	result := r.db.WithContext(ctx).
		Where("project_id = ? AND agent_id = ? AND member_type = 'agent'", projectID.String(), agentID.String()).
		Delete(&projectMemberRecord{})
	if result.Error != nil {
		return fmt.Errorf("project repo: remove agent member: %w", result.Error)
	}
	return nil
}

// UpdateMemberRole changes the role of an existing active project member.
func (r *ProjectRepository) UpdateMemberRole(ctx context.Context, projectID, userID, roleID uuid.UUID) error {
	result := r.db.WithContext(ctx).
		Table("project_members").
		Where("project_id = ? AND user_id = ? AND deleted_at IS NULL", projectID.String(), userID.String()).
		Update("project_role_id", roleID.String())
	if result.Error != nil {
		return fmt.Errorf("project repo: update member role: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return projectdom.ErrMemberNotFound
	}
	return nil
}

// RemoveMember soft-deletes the membership row for the given project + user.
func (r *ProjectRepository) RemoveMember(ctx context.Context, projectID, userID uuid.UUID) error {
	result := r.db.WithContext(ctx).
		Where("project_id = ? AND user_id = ?", projectID.String(), userID.String()).
		Delete(&projectMemberRecord{})
	if result.Error != nil {
		return fmt.Errorf("project repo: remove member: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return projectdom.ErrMemberNotFound
	}
	return nil
}

// UpdateMemberRoleByMemberID changes the role of an existing active project member by member ID.
func (r *ProjectRepository) UpdateMemberRoleByMemberID(ctx context.Context, memberID, roleID uuid.UUID) error {
	result := r.db.WithContext(ctx).
		Table("project_members").
		Where("id = ? AND deleted_at IS NULL", memberID.String()).
		Update("project_role_id", roleID.String())
	if result.Error != nil {
		return fmt.Errorf("project repo: update member role: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return projectdom.ErrMemberNotFound
	}
	return nil
}

// RemoveMemberByMemberID soft-deletes the membership row for the given member ID.
func (r *ProjectRepository) RemoveMemberByMemberID(ctx context.Context, memberID uuid.UUID) error {
	result := r.db.WithContext(ctx).
		Where("id = ?", memberID.String()).
		Delete(&projectMemberRecord{})
	if result.Error != nil {
		return fmt.Errorf("project repo: remove member: %w", result.Error)
	}
	if result.RowsAffected == 0 {
		return projectdom.ErrMemberNotFound
	}
	return nil
}

// --- Mapping helpers --------------------------------------------------------

func toProjectEntity(rec *projectRecord) (*projectdom.Project, error) {
	id, err := uuid.Parse(rec.ID)
	if err != nil {
		return nil, fmt.Errorf("project repo: parse id: %w", err)
	}
	settings := map[string]any{}
	if len(rec.Settings) > 0 {
		if err := json.Unmarshal(rec.Settings, &settings); err != nil {
			return nil, fmt.Errorf("project repo: unmarshal settings: %w", err)
		}
	}
	var createdBy *uuid.UUID
	if rec.CreatedBy != nil {
		if uid, err := uuid.Parse(*rec.CreatedBy); err == nil {
			createdBy = &uid
		}
	}
	return &projectdom.Project{
		ID:           id,
		Name:         rec.Name,
		Description:  rec.Description,
		TaskIDPrefix: rec.TaskIDPrefix,
		IsPublic:     rec.IsPublic,
		Settings:     settings,
		CreatedBy:    createdBy,
		CreatedAt:    rec.CreatedAt,
	}, nil
}

func fromProjectEntity(p *projectdom.Project) (*projectRecord, error) {
	settings, err := json.Marshal(p.Settings)
	if err != nil {
		return nil, fmt.Errorf("project repo: marshal settings: %w", err)
	}
	var createdBy *string
	if p.CreatedBy != nil {
		s := p.CreatedBy.String()
		createdBy = &s
	}
	return &projectRecord{
		ID:           p.ID.String(),
		Name:         p.Name,
		Description:  p.Description,
		TaskIDPrefix: p.TaskIDPrefix,
		IsPublic:     p.IsPublic,
		Settings:     settings,
		CreatedBy:    createdBy,
		CreatedAt:    p.CreatedAt,
	}, nil
}

func toProjectRoleEntity(rec *projectRoleRecord) (*projectdom.ProjectRole, error) {
	id, err := uuid.Parse(rec.ID)
	if err != nil {
		return nil, fmt.Errorf("project role repo: parse id: %w", err)
	}
	perms := map[string]any{}
	if len(rec.Permissions) > 0 {
		if err := json.Unmarshal(rec.Permissions, &perms); err != nil {
			return nil, fmt.Errorf("project role repo: unmarshal permissions: %w", err)
		}
	}
	var projectID *uuid.UUID
	if rec.ProjectID != nil {
		if uid, err := uuid.Parse(*rec.ProjectID); err == nil {
			projectID = &uid
		}
	}
	return &projectdom.ProjectRole{
		ID:          id,
		ProjectID:   projectID,
		RoleName:    rec.RoleName,
		Permissions: perms,
		CreatedAt:   rec.CreatedAt,
		UpdatedAt:   rec.UpdatedAt,
	}, nil
}

func fromProjectRoleEntity(r *projectdom.ProjectRole) (*projectRoleRecord, error) {
	perms, err := json.Marshal(r.Permissions)
	if err != nil {
		return nil, fmt.Errorf("project role repo: marshal permissions: %w", err)
	}
	var projectID *string
	if r.ProjectID != nil {
		s := r.ProjectID.String()
		projectID = &s
	}
	return &projectRoleRecord{
		ID:          r.ID.String(),
		ProjectID:   projectID,
		RoleName:    r.RoleName,
		Permissions: perms,
		CreatedAt:   r.CreatedAt,
		UpdatedAt:   r.UpdatedAt,
	}, nil
}

func toMemberEntity(row *projectMemberReadRow) *projectdom.ProjectMember {
	id, _ := uuid.Parse(row.ID)
	projectID, _ := uuid.Parse(row.ProjectID)
	roleID, _ := uuid.Parse(row.ProjectRoleID)
	var deletedAt *time.Time
	if row.DeletedAt.Valid {
		deletedAt = &row.DeletedAt.Time
	}
	m := &projectdom.ProjectMember{
		ID:            id,
		ProjectID:     projectID,
		ProjectRoleID: roleID,
		Username:      row.Username,
		FullName:      row.FullName,
		RoleName:      row.RoleName,
		CreatedAt:     row.CreatedAt,
		DeletedAt:     deletedAt,
		MemberType:    row.MemberType,
		AgentName:     row.AgentName,
		AgentHandle:   row.AgentHandle,
	}
	if row.UserID != nil {
		userID, _ := uuid.Parse(*row.UserID)
		m.UserID = userID
	}
	if row.AgentID != nil {
		agentID, _ := uuid.Parse(*row.AgentID)
		m.AgentID = &agentID
	}
	return m
}
