import SQL from 'sql-template-strings';

import type { Database } from '~/server/db.ts';
import { sortClearedLinkedText } from '~/src/helpers.ts';
import type { User } from '~/src/user.ts';

interface InclusiveRow {
    id: string;
    insteadOf: string;
    say: string;
    because: string;
    locale: string;
    approved: boolean;
    base_id: string | null;
    author_id: string | null;
    categories: string | null;
    links: string | null;
    deleted: boolean;
    clarification: string | null;
}

export type InclusiveRowWithAuthor = InclusiveRow & { author: User['username'] };

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

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