/** PostgreSQL 未加引號別名會變成小寫（如 displayname），對應回程式碼使用的 camelCase */
const LOWER_TO_CAMEL: Record<string, string> = {
    userid: 'userId',
    roomid: 'roomId',
    displayid: 'displayId',
    displayname: 'displayName',
    streamstatus: 'streamStatus',
    startedat: 'startedAt',
    followerscount: 'followersCount',
    isverified: 'isVerified',
    viplevel: 'vipLevel',
    ownerdisplayid: 'ownerDisplayId',
    liveinputidentifier: 'liveInputIdentifier',
    avatarurl: 'avatarUrl',
    bannerurl: 'bannerUrl',
    haschangednickname: 'hasChangedNickname',
    createdat: 'createdAt',
    updatedat: 'updatedAt',
    lastloginat: 'lastLoginAt',
    devicetype: 'deviceType',
    devicename: 'deviceName',
    streamtoken: 'streamToken',
    thumbnailurl: 'thumbnailUrl',
    profilepathsegment: 'profilePathSegment',
    passwordhash: 'passwordHash',
    memberscount: 'membersCount',
    artistscount: 'artistsCount',
    totalviewscount: 'totalViewsCount',
    giftamount: 'giftAmount',
    giftcount: 'giftCount',
}

function snakeToCamel(key: string): string {
    if (!key.includes('_')) return key
    return key.replace(/_([a-z])/g, (_, letter) => letter.toUpperCase())
}

function rowKeyVariants(key: string): string[] {
    const variants = new Set<string>()
    variants.add(key)
    variants.add(key.toLowerCase())
    variants.add(key.replace(/_/g, '').toLowerCase())
    const compact = key.replace(/_/g, '').toLowerCase()
    const camel = LOWER_TO_CAMEL[compact]
    if (camel) {
        variants.add(camel)
        variants.add(camel.toLowerCase())
    }
    if (key.includes('_')) {
        variants.add(snakeToCamel(key))
    }
    return [...variants]
}

export function normalizePostgresRow<T extends Record<string, unknown>>(row: T): T {
    const out = { ...row } as Record<string, unknown>
    for (const [key, value] of Object.entries(row)) {
        const compact = key.replace(/_/g, '').toLowerCase()
        const fromCompact = LOWER_TO_CAMEL[compact]
        if (fromCompact && out[fromCompact] === undefined) {
            out[fromCompact] = value
        }
        if (key.includes('_')) {
            const fromSnake = snakeToCamel(key)
            if (fromSnake !== key && out[fromSnake] === undefined) {
                out[fromSnake] = value
            }
        }
    }
    return out as T
}

export function normalizePostgresRows<T extends Record<string, unknown>>(rows: T[]): T[] {
    return rows.map((row) => normalizePostgresRow(row))
}

/** 從查詢列讀字串欄位（相容 camelCase、snake_case 與 PG 小寫別名） */
export function pickRowString(row: Record<string, unknown> | null | undefined, ...keys: string[]): string {
    if (!row) return ''
    for (const key of keys) {
        for (const variant of rowKeyVariants(key)) {
            const value = row[variant]
            if (value != null && String(value).trim()) {
                return String(value).trim()
            }
        }
    }
    return ''
}

export function pickRowNumber(row: Record<string, unknown> | null | undefined, ...keys: string[]): number {
    const raw = pickRowString(row, ...keys)
    if (!raw) return 0
    const n = Number(raw)
    return Number.isFinite(n) ? n : 0
}

export function readDisplayName(row: Record<string, unknown> | null | undefined): string {
    return pickRowString(row, 'displayName', 'display_name')
}

export function readUserId(row: Record<string, unknown> | null | undefined): string {
    return pickRowString(row, 'userId', 'user_id', 'id')
}