import SQL from 'sql-template-strings';

import { now } from '#shared/helpers.ts';
import type { Admin } from '#shared/user.ts';
import locales from '~~/locale/locales.ts';
import avatar from '~~/server/avatar.ts';
import { getLocale } from '~~/server/data.ts';
import type { Database } from '~~/server/db.ts';
import type { ProfileRow } from '~~/server/express/profile.ts';
import type { AuthenticatorRow, UserRow } from '~~/server/express/user.ts';

const getAdminList = defineCachedFunction(async (db: Database, locale: string) => {
    const admins = await db.all<Pick<
        UserRow, 'username' | 'id' | 'email' | 'avatarSource'> &
        Pick<ProfileRow, 'teamName' | 'locale' | 'credentials' | 'credentialsLevel' | 'credentialsName'> &
        Pick<AuthenticatorRow, 'payload'>>(SQL` 
            SELECT u.username, u.id, u.email, u.avatarSource,
                p.teamName, p.locale, p.credentials, p.credentialsLevel, p.credentialsName,
                a.payload
            FROM users u
                LEFT JOIN profiles p ON p.userId = u.id
                LEFT JOIN authenticators a ON u.id = a.userId AND a.type = u.avatarSource
            WHERE p.teamName IS NOT NULL
                AND p.teamName != ''
                AND (a.validUntil IS NULL OR a.validUntil > ${now()})
            GROUP BY u.username, p.locale
            ORDER BY RANDOM()
        `);

    const adminsGroupped: Record<string, Admin[]> = Object.fromEntries(
        [
            [locale, []],
            ...locales.filter(({ code, published }) => code !== locale && published).map(({ code }) => [code, []]),
            ['', []],
        ],
    );
    for (const adminRow of admins) {
        const admin: Admin = {
            username: adminRow.username,
            teamName: adminRow.teamName!,
            locale: adminRow.locale,
            avatar: await avatar(db, adminRow),
            credentials: adminRow.credentials?.split('|') ?? null,
            credentialsLevel: adminRow.credentialsLevel,
            credentialsName: adminRow.credentialsName,
        };

        if (adminsGroupped[admin.locale!] !== undefined) {
            adminsGroupped[admin.locale!].push(admin);
        } else {
            adminsGroupped[''].push(admin);
        }
    }

    return adminsGroupped;
}, {
    name: 'admin-list',
    getKey: (db, locale) => locale,
    maxAge: 24 * 60 * 60,
});

export default defineEventHandler(async (event) => {
    const locale = getLocale(event);
    const db = useDatabase();
    return await getAdminList(db, locale);
});