import { resolveAvatarFrameAssetUrl } from '@/lib/avatarFrames/resolveUrl'
import { sortByAcquiredAtDesc } from '@/lib/avatarFrames/sortByAcquiredAt'
import type { AvatarFramePayload, UserAvatarFrameListItem } from '@/lib/avatarFrames/types'
import type { GpLiveDb } from '@/server/gpLiveDb'

type Db = GpLiveDb

export type DbAvatarFrameRow = {
    id: string
    image: string
    type: string | null
}

export function mapDbAvatarFrameRow(row: DbAvatarFrameRow | undefined | null): AvatarFramePayload | null {
    if (!row?.image) return null
    const rawType = String(row.type ?? 'IMG').toUpperCase()
    const type: AvatarFramePayload['type'] =
        rawType === 'LOTTIE' || rawType === 'LOTTERY' ? 'LOTTIE' : 'IMG'
    return {
        id: String(row.id),
        image: resolveAvatarFrameAssetUrl(row.image),
        type,
    }
}

export async function fetchUserActiveAvatarFrame(
    db: Db,
    userId: string
): Promise<AvatarFramePayload | null> {
    const row = await db.get<DbAvatarFrameRow>(
        `SELECT af.item_id AS id, af.img_url AS image,
                CASE WHEN af.type = 'LOTTERY' THEN 'LOTTIE' ELSE af.type END AS type
         FROM user_avatar_frames uaf
         JOIN config_avatar_frames af ON af.item_id = uaf.frame_id
         WHERE uaf.user_id = ? AND uaf.is_used = 1
         ORDER BY uaf.acquired_at DESC
         LIMIT 1`,
        [userId]
    )
    return mapDbAvatarFrameRow(row)
}

export type DbUserAvatarFrameListRow = {
    id: string
    image: string
    type: string | null
    name: string | null
    is_used: number
    acquired_at: number
}

export async function fetchUserAvatarFrames(
    db: Db,
    userId: string
): Promise<UserAvatarFrameListItem[]> {
    const rows = await db.all<DbUserAvatarFrameListRow>(
        `SELECT af.item_id AS id, af.name AS name, af.img_url AS image,
                CASE WHEN af.type = 'LOTTERY' THEN 'LOTTIE' ELSE af.type END AS type,
                uaf.is_used AS is_used,
                uaf.acquired_at AS acquired_at
         FROM user_avatar_frames uaf
         JOIN config_avatar_frames af ON af.item_id = uaf.frame_id
         WHERE uaf.user_id = ?`,
        [userId]
    )
    return sortByAcquiredAtDesc(rows ?? [])
        .map((row) => {
            const mapped = mapDbAvatarFrameRow(row)
            if (!mapped) return null
            return {
                id: mapped.id,
                image: mapped.image,
                name: String(row.name || row.id).trim() || mapped.id,
                isUsed: Number(row.is_used) !== 0,
            }
        })
        .filter((item): item is UserAvatarFrameListItem => item !== null)
}