/* eslint-disable camelcase */
import SQL from 'sql-template-strings';

import { PermissionAreas } from '#shared/helpers.ts';
import type { User } from '#shared/user.ts';
import type { Database } from '~~/server/db.ts';

interface NameRow {
    id: string;
    name: string;
    locale: string;
    origin: string | null;
    meaning: string | null;
    usage: string | null;
    legally: string | null;
    pros: string | null;
    cons: string | null;
    notablePeople: string | null;
    links: string | null;
    namedays: string | null;
    namedaysComment: string | null;
    deleted: boolean;
    approved: boolean;
    base_id: string | null;
    author_id: string | null;
}

type NameRowWithAuthor = NameRow & { author: User['username'] };

export const getNameEntries = defineCachedFunction(async (
    db: Database,
    isGranted: IsGrantedFn,
    locale: string,
) => {
    return await db.all<NameRowWithAuthor>(SQL`
            SELECT n.*, u.username AS author FROM names n
            LEFT JOIN users u ON n.author_id = u.id
            WHERE n.locale = ${locale}
            AND n.deleted = 0
            AND n.approved >= ${isGranted(PermissionAreas.Names) ? 0 : 1}
            ORDER BY n.approved, n.name
        `);
}, {
    name: 'names',
    getKey: (db, isGranted, locale) => locale,
    shouldBypassCache: (db, isGranted) => isGranted(PermissionAreas.Names),
    maxAge: 24 * 60 * 60,
});

export const approveNameEntry = async (db: Database, id: string, locale: string) => {
    const { base_id } = (await db.get<Pick<NameRow, 'base_id'>>(SQL`SELECT base_id FROM names WHERE id=${id}`))!;
    if (base_id) {
        await db.get(SQL`
            UPDATE names
            SET deleted=1
            WHERE id = ${base_id}
        `);
    }
    await db.get(SQL`
        UPDATE names
        SET approved = 1, base_id = NULL
        WHERE id = ${id}
    `);
    await invalidateCache('names', locale);
};