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

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

interface TermRow {
    id: string;
    term: string;
    original: string | null;
    definition: string;
    locale: string;
    approved: boolean;
    base_id: string;
    author_id: string | null;
    deleted: boolean;
    flags: string;
    category: string | null;
    images: string;
    key: string | null;
}

export type TermRowWithAuthor = TermRow & { author: User['username'] };

export const linkOtherVersions = async (
    db: Database,
    isGranted: IsGrantedFn,
    locale: string,
    terms: TermRowWithAuthor[],
) => {
    const keys = new Set(terms.filter((s) => !!s && s.key).map((s) => `'${clearKey(s.key)}'`));

    const otherVersions = await db.all<TermRowWithAuthor & { key: string }>(SQL`
        SELECT t.*, u.username AS author FROM terms t
        LEFT JOIN users u ON t.author_id = u.id
        WHERE t.locale != ${locale}
        AND t.deleted = 0
        AND t.approved >= ${isGranted(PermissionAreas.Terms) ? 0 : 1}
        AND t.key IN (`.append([...keys].join(',')).append(SQL`)
    `));

    const otherVersionsMap: Record<string, TermRowWithAuthor[]> = {};
    otherVersions.forEach((version) => {
        if (otherVersionsMap[version.key] === undefined) {
            otherVersionsMap[version.key] = [];
        }
        otherVersionsMap[version.key].push(version);
    });

    return terms.map((t) => ({
        ...t,
        versions: t.key ? otherVersionsMap[t.key] || [] : [],
    }));
};

export const getTermsEntries = defineCachedFunction(async (
    db: Database,
    isGranted: IsGrantedFn,
    locale: string,
) => {
    return await linkOtherVersions(
        db,
        isGranted,
        locale,
        sortClearedLinkedText(await db.all<TermRowWithAuthor>(SQL`
                SELECT i.*, u.username AS author FROM terms i
                LEFT JOIN users u ON i.author_id = u.id
                WHERE i.locale = ${locale}
                AND i.approved >= ${isGranted(PermissionAreas.Terms) ? 0 : 1}
                AND i.deleted = 0
            `), 'term'),
    );
}, {
    name: 'terms',
    getKey: (db, isGranted, locale) => locale,
    shouldBypassCache: (db, isGranted) => isGranted(PermissionAreas.Terms),
    maxAge: 24 * 60 * 60,
});

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