import type { getDb } from '@/server/db'
import { resolveMediaUrl } from '@/lib/media/resolveMediaUrl'

export type UserGroupListItem = {
    id: string
    name: string
    avatar: string | null
    role: string
    membersCount: number
}

export async function fetchUserGroups(
    db: Awaited<ReturnType<typeof getDb>>,
    userId: string
): Promise<UserGroupListItem[]> {
    type UserGroupRow = {
        id: string
        name: string
        avatar: string | null
        role: string
        membersCount: number
    }

    const rows = await db
        .all<UserGroupRow>(
            `SELECT g.display_id AS id, g.name, g.avatar_url AS avatar, ug.role AS role,
                    (SELECT COUNT(1) FROM user_groups ug2 WHERE ug2.group_id = g.group_id) AS membersCount
             FROM groups g
             INNER JOIN user_groups ug ON g.group_id = ug.group_id
             WHERE ug.user_id = ?
             ORDER BY
                CASE ug.role
                    WHEN 'admin' THEN 1
                    WHEN 'artist' THEN 2
                    ELSE 3
                END,
                ug.join_at ASC`,
            [userId]
        )
        .catch(() => [] as UserGroupRow[])
    return (rows || []).map((row) => {
        const resolved = resolveMediaUrl(row.avatar)
        return {
            id: row.id,
            name: row.name,
            avatar: resolved || null,
            role: String(row.role ?? 'member').trim() || 'member',
            membersCount: Number(row.membersCount) || 0,
        }
    })
}