package team

import (
	"database/sql"
	"time"

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

func timePtrFromNull(value sql.NullTime) *time.Time {
	if !value.Valid {
		return nil
	}
	t := value.Time.UTC()
	return &t
}

func nullableTime(value *time.Time) any {
	if value == nil {
		return nil
	}
	return value.UTC()
}

func teamSelectSQL() string {
	return `
SELECT id, workspace_id, handle, display_name, description, avatar_url,
       visibility, status, COALESCE(channel_id, ''), created_by_member_id, created_at, updated_at, archived_at
FROM workspace_teams`
}

func scanTeam(row coredb.Row) (model.WorkspaceTeam, error) {
	var team model.WorkspaceTeam
	var archivedAt sql.NullTime
	if err := row.Scan(
		&team.ID,
		&team.WorkspaceID,
		&team.Handle,
		&team.DisplayName,
		&team.Description,
		&team.AvatarURL,
		&team.Visibility,
		&team.Status,
		&team.ChannelID,
		&team.CreatedByMemberID,
		&team.CreatedAt,
		&team.UpdatedAt,
		&archivedAt,
	); err != nil {
		return model.WorkspaceTeam{}, err
	}
	team.ID = trim(team.ID)
	team.WorkspaceID = trim(team.WorkspaceID)
	team.Handle = trim(team.Handle)
	team.Visibility = trim(team.Visibility)
	team.Status = trim(team.Status)
	team.ChannelID = trim(team.ChannelID)
	team.CreatedByMemberID = trim(team.CreatedByMemberID)
	team.ArchivedAt = timePtrFromNull(archivedAt)
	return team, nil
}

func teamMemberSelectSQL() string {
	return `
SELECT id, workspace_id, team_id, actor_kind, actor_id,
       role,
       role_label, role_overlay_prompt, role_overlay_version,
       wake_on_team_mention, sort_order, added_by_member_id, added_at, updated_at, removed_at
FROM workspace_team_members`
}

func scanTeamMember(row coredb.Row) (model.WorkspaceTeamMember, error) {
	var member model.WorkspaceTeamMember
	var removedAt sql.NullTime
	if err := row.Scan(
		&member.ID,
		&member.WorkspaceID,
		&member.TeamID,
		&member.ActorKind,
		&member.ActorID,
		&member.Role,
		&member.RoleLabel,
		&member.RoleOverlayPrompt,
		&member.RoleOverlayVersion,
		&member.WakeOnTeamMention,
		&member.SortOrder,
		&member.AddedByMemberID,
		&member.AddedAt,
		&member.UpdatedAt,
		&removedAt,
	); err != nil {
		return model.WorkspaceTeamMember{}, err
	}
	member.ID = trim(member.ID)
	member.WorkspaceID = trim(member.WorkspaceID)
	member.TeamID = trim(member.TeamID)
	member.ActorKind = trim(member.ActorKind)
	member.ActorID = trim(member.ActorID)
	member.Role = trim(member.Role)
	if member.Role == "" {
		member.Role = MemberRoleMember
	}
	member.AddedByMemberID = trim(member.AddedByMemberID)
	member.RemovedAt = timePtrFromNull(removedAt)
	return member, nil
}