import SQL from 'sql-template-strings';
import { decodeTime } from 'ulid';

import { isGrantedForUser } from '#shared/helpers.ts';
import type { LocaleCode, PermissionAreas } from '#shared/helpers.ts';
import type { User } from '#shared/user.ts';
import type { LocaleStatsData, OverallStatsData } from '~~/server/buildStats.ts';
import type { Database } from '~~/server/db.ts';

export interface StatRow {
    id: string;
    locale: string;
    users: number;
    data: string;
}

export interface Stats {
    calculatedAt: number;
    overall: { users: number } & OverallStatsData;
    locales: Record<string, { users: number } & LocaleStatsData>;
}

export const fetchStats = async (db: Database): Promise<Stats | null> => {
    const maxId = (await db.get<{ maxId: StatRow['id'] | null }>('SELECT MAX(id) AS maxId FROM stats'))!.maxId;

    if (maxId === null) {
        return null;
    }

    let overall: ({ users: number } & OverallStatsData) | null = null;
    const locales: Record<string, { users: number } & LocaleStatsData> = {};

    for (const statsRow of await db.all<Pick<StatRow, 'locale' | 'users' | 'data'>>(SQL`
        SELECT locale, users, data FROM stats WHERE id = ${maxId}
    `)) {
        const stats = {
            users: statsRow.users,
            ...JSON.parse(statsRow.data),
        };
        if (statsRow.locale === '_') {
            overall = stats;
        } else {
            locales[statsRow.locale] = stats;
        }
    }

    return {
        calculatedAt: decodeTime(maxId) / 1000,
        overall: overall!,
        locales,
    };
};

type AdminUser = Pick<User, 'username' | 'email' | 'roles' | 'adminNotifications'>;
export const findAdmins = async (db: Database, locale: string, area: PermissionAreas): Promise<AdminUser[]> => {
    const admins = await db.all<AdminUser>('SELECT username, email, roles, adminNotifications FROM users WHERE (roles != \'\' AND roles != \'*-external\')');
    return admins.filter((admin) => isGrantedForUser(admin, locale as LocaleCode, area));
};