import type { getDb } from '@/server/db'
import { pickRowNumber, pickRowString } from '@/server/pgRow'

type AppDb = Awaited<ReturnType<typeof getDb>>

export type GroupListItem = {
    id: string
    name: string
    avatar: string | null
    isVerified: boolean
    artistsCount: number
    membersCount: number
}

/** 家族列表：ORDER BY 使用子查詢，避免 PG 對 camelCase 別名大小寫問題 */
export async function fetchGroupList(db: AppDb): Promise<GroupListItem[]> {
    const rows = await db.all<Record<string, unknown>>(
        `SELECT
            g.display_id AS id,
            g.name,
            g.avatar_url AS avatar,
            g.is_verified AS isVerified,
            (SELECT COUNT(1) FROM user_groups ug WHERE ug.group_id = g.group_id AND ug.role = 'artist') AS artistsCount,
            (SELECT COUNT(1) FROM user_groups ug WHERE ug.group_id = g.group_id) AS membersCount
         FROM groups g
         ORDER BY (SELECT COUNT(1) FROM user_groups ug WHERE ug.group_id = g.group_id) DESC,
                  (SELECT COUNT(1) FROM user_groups ug WHERE ug.group_id = g.group_id AND ug.role = 'artist') DESC`
    )

    return rows.map((row) => ({
        id: pickRowString(row, 'id', 'display_id'),
        name: pickRowString(row, 'name'),
        avatar: pickRowString(row, 'avatar', 'avatar_url') || null,
        isVerified: pickRowNumber(row, 'isVerified', 'is_verified') === 1,
        artistsCount: pickRowNumber(row, 'artistsCount', 'artists_count'),
        membersCount: pickRowNumber(row, 'membersCount', 'members_count'),
    }))
}