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

type AppDb = Awaited<ReturnType<typeof getDb>>

export type RankType = 'gift' | 'rich' | 'group' | 'star'
export type RankPeriod = 'day' | 'week' | 'month' | 'thisWeek' | 'lastWeek'

export function getRankingPeriodRange(period: RankPeriod): { since: number; until?: number } {
    const now = Math.floor(Date.now() / 1000)
    if (period === 'day') return { since: now - 86400 }
    if (period === 'week') return { since: now - 86400 * 7 }
    if (period === 'month') return { since: now - 86400 * 30 }

    const date = new Date()
    const day = date.getDay() || 7
    date.setHours(0, 0, 0, 0)
    date.setDate(date.getDate() - day + 1)
    const thisWeekStart = Math.floor(date.getTime() / 1000)

    if (period === 'lastWeek') {
        return { since: thisWeekStart - 86400 * 7, until: thisWeekStart }
    }
    return { since: thisWeekStart }
}

function mapUserGiftRow(row: Record<string, unknown>) {
    return {
        rank: pickRowNumber(row, 'rank'),
        userId: pickRowString(row, 'userId', 'user_id'),
        name: pickRowString(row, 'name'),
        avatar: pickRowString(row, 'avatar', 'avatar_url') || null,
        giftAmount: pickRowNumber(row, 'giftAmount', 'gift_amount'),
        level: pickRowNumber(row, 'level'),
    }
}

function mapGroupGiftRow(row: Record<string, unknown>) {
    return {
        rank: pickRowNumber(row, 'rank'),
        groupId: pickRowString(row, 'groupId', 'group_id'),
        name: pickRowString(row, 'name'),
        avatar: pickRowString(row, 'avatar', 'avatar_url') || null,
        giftAmount: pickRowNumber(row, 'giftAmount', 'gift_amount'),
    }
}

function mapStarRow(row: Record<string, unknown>) {
    return {
        rank: pickRowNumber(row, 'rank'),
        userId: pickRowString(row, 'userId', 'user_id'),
        name: pickRowString(row, 'name'),
        avatar: pickRowString(row, 'avatar', 'avatar_url') || null,
        giftCount: pickRowNumber(row, 'giftCount', 'gift_count'),
        contributorName: pickRowString(row, 'contributorName', 'contributor_name'),
        contributorId: pickRowString(row, 'contributorId', 'contributor_id'),
    }
}

export async function fetchRankingData(
    db: AppDb,
    type: RankType,
    period: RankPeriod
): Promise<unknown[]> {
    const { since, until } = getRankingPeriodRange(period)
    const untilStreamSql = until ? 'AND rs.created_at < ?' : ''
    const untilRichSql = until ? 'AND rr.created_at < ?' : ''
    const periodParams = until ? [since, until] : [since]

    if (type === 'gift' && (period === 'day' || period === 'week' || period === 'month')) {
        const rows = await db.all<Record<string, unknown>>(
            `SELECT ROW_NUMBER() OVER (ORDER BY agg.gift_amount DESC, agg.user_id ASC) AS rank,
                    agg.user_id AS userId,
                    agg.name,
                    agg.avatar,
                    agg.gift_amount AS giftAmount,
                    agg.level
             FROM (
                 SELECT rs.user_id,
                        MAX(COALESCE(u.display_name, rs.user_id)) AS name,
                        MAX(u.avatar_url) AS avatar,
                        SUM(rs.gift_count) AS gift_amount,
                        MAX(u.streamer_exp) AS level
                 FROM rank_stream rs
                 LEFT JOIN users u ON u.user_id = rs.user_id
                 WHERE rs.created_at >= ? ${untilStreamSql}
                 GROUP BY rs.user_id
             ) agg
             ORDER BY agg.gift_amount DESC, agg.user_id ASC
             LIMIT 10`,
            periodParams
        )
        return rows.map(mapUserGiftRow)
    }

    if (type === 'rich' && (period === 'day' || period === 'week' || period === 'month')) {
        const rows = await db.all<Record<string, unknown>>(
            `SELECT ROW_NUMBER() OVER (ORDER BY agg.gift_amount DESC, agg.user_id ASC) AS rank,
                    agg.user_id AS userId,
                    agg.name,
                    agg.avatar,
                    agg.gift_amount AS giftAmount,
                    agg.level
             FROM (
                 SELECT rr.user_id,
                        MAX(COALESCE(u.display_name, rr.user_id)) AS name,
                        MAX(u.avatar_url) AS avatar,
                        SUM(rr.gift_count) AS gift_amount,
                        MAX(u.rich_exp) AS level
                 FROM rank_rich rr
                 LEFT JOIN users u ON u.user_id = rr.user_id
                 WHERE rr.created_at >= ? ${untilRichSql}
                 GROUP BY rr.user_id
             ) agg
             ORDER BY agg.gift_amount DESC, agg.user_id ASC
             LIMIT 10`,
            periodParams
        )
        return rows.map(mapUserGiftRow)
    }

    if (type === 'group' && (period === 'day' || period === 'week' || period === 'month')) {
        const rows = await db.all<Record<string, unknown>>(
            `WITH ranked_users AS (
                SELECT user_id, gift_count, created_at FROM rank_stream
                UNION ALL
                SELECT target_id AS user_id, gift_count, created_at FROM rank_rich WHERE target_id IS NOT NULL
             )
             SELECT ROW_NUMBER() OVER (ORDER BY agg.gift_amount DESC, agg.group_id ASC) AS rank,
                    agg.group_id AS groupId,
                    agg.name,
                    agg.avatar,
                    agg.gift_amount AS giftAmount
             FROM (
                 SELECT g.group_id,
                        MAX(g.name) AS name,
                        MAX(g.avatar_url) AS avatar,
                        SUM(ru.gift_count) AS gift_amount
                 FROM ranked_users ru
                 JOIN user_groups ug ON ug.user_id = ru.user_id
                 JOIN groups g ON g.group_id = ug.group_id
                 WHERE ru.created_at >= ? ${until ? 'AND ru.created_at < ?' : ''}
                 GROUP BY g.group_id
             ) agg
             ORDER BY agg.gift_amount DESC, agg.group_id ASC
             LIMIT 10`,
            periodParams
        )
        return rows.map(mapGroupGiftRow)
    }

    if (type === 'star' && (period === 'thisWeek' || period === 'lastWeek')) {
        const rows = await db.all<Record<string, unknown>>(
            `SELECT ROW_NUMBER() OVER (
                    ORDER BY agg.gift_count DESC, agg.user_id ASC, agg.contributor_id ASC
                ) AS rank,
                agg.user_id AS userId,
                agg.name,
                agg.avatar,
                agg.gift_count AS giftCount,
                agg.contributor_name AS contributorName,
                agg.contributor_id AS contributorId
             FROM (
                 SELECT rs.user_id,
                        rs.target_id AS contributor_id,
                        MAX(COALESCE(receiver.display_name, rs.user_id)) AS name,
                        MAX(receiver.avatar_url) AS avatar,
                        SUM(rs.gift_count) AS gift_count,
                        MAX(COALESCE(contributor.display_name, rs.target_id, '')) AS contributor_name
                 FROM rank_stream rs
                 LEFT JOIN users receiver ON receiver.user_id = rs.user_id
                 LEFT JOIN users contributor ON contributor.user_id = rs.target_id
                 WHERE rs.created_at >= ? ${untilStreamSql}
                 GROUP BY rs.user_id, rs.target_id
             ) agg
             ORDER BY agg.gift_count DESC, agg.user_id ASC, agg.contributor_id ASC
             LIMIT 10`,
            periodParams
        )
        return rows.map(mapStarRow)
    }

    return []
}