CREATE OR REPLACE FUNCTION public.fn_admin_delete_email_template(p_template_key character varying, p_language character varying)
 RETURNS jsonb
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_count integer;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM public.email_dispatch_rules
    WHERE template_key = p_template_key AND language = p_language;

    IF v_count > 0 THEN
        RETURN jsonb_build_object(
            'error', 'No se puede eliminar: la plantilla está en uso por ' || v_count || ' regla(s).'
        );
    END IF;

    DELETE FROM public.email_templates
    WHERE template_key = p_template_key AND language = p_language;

    IF NOT FOUND THEN
        RETURN jsonb_build_object('error', 'Plantilla no encontrada.');
    END IF;

    RETURN jsonb_build_object('success', true);
END;
$function$
;